<?php
/**
 * MainWP Database Controller
 *
 * This file handles all interactions with the DB.
 *
 * @package MainWP/Dashboard
 */

namespace MainWP\Dashboard;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class MainWP_DB
 *
 * @package MainWP\Dashboard
 *
 * @uses \MainWP\Dashboard\MainWP_DB
 */
class MainWP_Auto_Updates_DB extends MainWP_DB { // phpcs:ignore Generic.Classes.OpeningBraceSameLine.ContentAfterBrace -- NOSONAR.

    // phpcs:disable WordPress.DB.RestrictedFunctions, WordPress.DB.PreparedSQL.NotPrepared, Generic.Metrics.CyclomaticComplexity -- This is the only way to achieve desired results, pull request solutions appreciated.

    /**
     * Private static variable to hold the single instance of the class.
     *
     * @static
     *
     * @var mixed Default null
     */
    private static $instance = null;

    /**
     * Create public static instance.
     *
     * @static
     *
     * @return MainWP_DB
     */
    public static function instance() {
        if ( null === static::$instance ) {
            static::$instance = new self();
        }

        static::$instance->test_connection();

        return static::$instance;
    }


    /**
     * Get child sites check updates.
     *
     * @param int  $limit Query limit.
     * @param int  $lasttime_start Lasttime start automatic update.
     * @param bool $connected Requires sites connected.
     * @param bool $not_suspended Requires sites unsuspended.
     *
     * @return object|null Database query result or null on failure.
     */
    public function get_websites_check_updates( $limit, $lasttime_start, $connected = false, $not_suspended = false ) {
        global $wpdb;

        // do not use cache for this function because it is used for automatic updates and it needs to be accurate, and the result is not used in other places, so it will not cause performance issues.

        $where = '';
        if ( true === $connected ) {
            $where .= ' wp_sync.sync_errors = "" AND';
        }
        if ( true === $not_suspended ) {
            $where .= ' wp.suspended = 0 AND';
        }

        $view_selects = '';
        $view_joins   = '';

        $opts_view = $this->get_wp_options_join( array( 'favi_icon' ) );

        if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
            $view_selects = ',' . $opts_view['selects'];
            $view_joins   = $opts_view['joins'];
        }

        $sql = $wpdb->prepare(
            'SELECT wp.*,wp_sync.*' . $view_selects . ' FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid ' . $view_joins . ' WHERE ' . $where . ' ( wp_sync.dtsAutomaticSyncStart = 0 OR  wp_sync.dtsAutomaticSyncStart < %d ) GROUP BY wp.id ORDER BY wp_sync.dtsAutomaticSyncStart ASC LIMIT  %d',
            $lasttime_start,
            $limit
        );

        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql is prepared by $wpdb->prepare() with parameterized values; direct query required for complex joins.
        $result = $wpdb->get_results( $sql, OBJECT );

        return $result;
    }


    /**
     * Get child sites to start updates.
     *
     * @param bool $connected Requires sites connected.
     * @param bool $not_suspended Requires sites unsuspended.
     *
     * @return object|null Database query result or null on failure.
     */
    public function get_websites_to_start_updates( $connected = false, $not_suspended = false ) {

        $where = '';
        if ( true === $connected ) {
            $where .= ' wp_sync.sync_errors = "" AND';
        }
        if ( true === $not_suspended ) {
            $where .= ' wp.suspended = 0 AND';
        }

        $where  = rtrim( $where, 'AND' );
        $params = array(
            'view'  => 'updates_view',
            'where' => $where,
        );

        return MainWP_DB::instance()->query( MainWP_DB::instance()->get_sql_websites_for_current_user_by_params( $params ) );
    }


    /**
     * Get child sites to continue updates.
     *
     * @param int  $limit limit sites to continue updates.
     * @param int  $lasttime_start Lasttime start automatic update.
     * @param bool $connected Requires sites connected.
     * @param bool $not_suspended Requires sites unsuspended.
     *
     * @return object|null Database query result or null on failure.
     */
    public function get_websites_to_continue_updates( $limit, $lasttime_start, $connected = false, $not_suspended = false ) {

        if ( empty( $lasttime_start ) ) {
            return false;
        }

        $where = ' ( wp_sync.dtsAutomaticSyncStart > 0 AND wp_sync.dtsAutomaticSync < wp_sync.dtsAutomaticSyncStart AND wp_sync.dtsAutomaticSyncStart <= ' . intval( $lasttime_start ) . ' ) AND'; // less than to sure that exactly trigger start updates timestamp.
        if ( true === $connected ) {
            $where .= ' wp_sync.sync_errors = "" AND';
        }
        if ( true === $not_suspended ) {
            $where .= ' wp.suspended = 0 AND';
        }

        $orderby = ' wp_sync.dtsAutomaticSyncStart ASC ';

        $where = rtrim( $where, 'AND' );

        $params = array(
            'view'    => 'updates_view',
            'where'   => $where,
            'limit'   => $limit,
            'orderby' => $orderby, // to fix.
        );

        $sql = MainWP_DB::instance()->get_sql_websites_for_current_user_by_params( $params );

        $results = MainWP_DB::instance()->query( $sql );

        $websites = array();
        while ( $results && $website = static::fetch_object( $results ) ) {
            $websites[] = $website;
        }

        if ( $results ) {
            static::free_result( $results );
        }

        return $websites;
    }


    /**
     * Get child sites to start updates.
     *
     * @param int $limit limit sites to continue updates.
     * @param int $lasttime_start Lasttime start automatic update.
     *
     * @return object|null Database query result or null on failure.
     */
    public function get_websites_to_continue_individual_updates( $limit = 4, $lasttime_start = false ) {

        if ( empty( $lasttime_start ) ) {
            return false;
        }

        $where  = ' wp_sync.sync_errors = "" AND ';
        $where .= ' wp.suspended = 0 AND ';
        $where .= '  owp_batch_individual_queue_time.value >= ' . intval( $lasttime_start ) . ' '; // owp_batch_individual_queue_time left join alias.

        $params = array(
            'view'          => 'updates_view',
            'where'         => $where,
            'limit'         => $limit,
            'others_fields' => array( 'batch_individual_queue_time' ),
        );

        $results = MainWP_DB::instance()->query( MainWP_DB::instance()->get_sql_websites_for_current_user_by_params( $params ) );

        $websites = array();
        while ( $results && $website = static::fetch_object( $results ) ) {
            $websites[] = $website;
        }

        if ( $results ) {
            static::free_result( $results );
        }

        return $websites;
    }


    /**
     * Get websites check updates count.
     *
     * @param int $lasttime_start Lasttime start automatic update.
     *
     * @return int Child sites update count.
     */
    public function get_websites_check_updates_count( $lasttime_start ) {
        global $wpdb;

        $cache_identifier = array( 'lasttime_start' => absint( $lasttime_start ) );
        $cache_key        = 'mainwp_auto_updates_count_' . md5( wp_json_encode( $cache_identifier ) ); // NOSONAR - MD5 for cache key only.
        $cached           = wp_cache_get( $cache_key );
        if ( false !== $cached ) {
            return $cached;
        }

        $where = $this->get_sql_where_allow_access_sites( 'wp' );

        $sql = $wpdb->prepare(
            'SELECT count(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE ( wp_sync.dtsAutomaticSyncStart = 0 OR wp_sync.dtsAutomaticSyncStart < %d)' . $where,
            $lasttime_start
        );

        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery -- $sql is prepared by $wpdb->prepare() with parameterized values; direct query required for complex joins.
        $result = $wpdb->get_var( $sql );
        wp_cache_set( $cache_key, $result, '', HOUR_IN_SECONDS );

        return $result;
    }

    /**
     * Get child site count where date & time Session sync is smaller then start.
     *
     * @param int $lasttime_start Last time start automatic.
     *
     * @return int Returned child site count.
     */
    public function get_websites_count_where_dts_automatic_sync_smaller_then_start( $lasttime_start ) {
        global $wpdb;

        $cache_identifier = array( 'lasttime_start' => absint( $lasttime_start ) );
        $cache_key        = 'mainwp_auto_updates_sync_smaller_' . md5( wp_json_encode( $cache_identifier ) ); // NOSONAR - MD5 for cache key only.
        $cached           = wp_cache_get( $cache_key );
        if ( false !== $cached ) {
            return $cached;
        }

        $sql = $wpdb->prepare(
            'SELECT count(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE wp.suspended = 0 AND ( ( wp_sync.dtsAutomaticSync < wp_sync.dtsAutomaticSyncStart AND wp_sync.dtsAutomaticSyncStart > %d) OR (wp_sync.dtsAutomaticSyncStart = 0) ) ',
            $lasttime_start
        );

        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery -- $sql is prepared by $wpdb->prepare() with parameterized values; direct query required for complex joins.
        $result = $wpdb->get_var( $sql );
        wp_cache_set( $cache_key, $result, '', HOUR_IN_SECONDS );

        return $result;
    }

    /**
     * Get child site last automatic sync date & time.
     *
     * @return string Date and time of last automatic sync.
     */
    public function get_websites_last_automatic_sync() {
        global $wpdb;

        $cache_key = 'mainwp_auto_updates_last_sync';
        $cached    = wp_cache_get( $cache_key );
        if ( false !== $cached ) {
            return $cached;
        }

        $sql = 'SELECT MAX(wp_sync.dtsAutomaticSync) FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid';

        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery -- Table names come from safe $this->table_name() methods only, no user input in this query; direct query required for complex joins.
        $result = $wpdb->get_var( $sql );
        wp_cache_set( $cache_key, $result, '', HOUR_IN_SECONDS );

        return $result;
    }
}