<?php
/**
 * Rank Booster Database Operations
 *
 * Handles table creation and all CRUD for rank_booster_jobs.
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

class Rank_Booster_DB {

    const TABLE_NAME = 'rank_booster_jobs';

    /* ───────────────────── table helpers ───────────────────── */

    public static function table_name() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_NAME;
    }

    /**
     * Create or upgrade the table. Bumps rank_booster_db_version on changes.
     * Called once per admin page load (non-AJAX) when the version doesn't match.
     */
    public static function maybe_upgrade() {
        $db_version = get_option( 'rank_booster_db_version', '0' );
        if ( $db_version === '1.4' ) {
            return;
        }
        self::create_table(); // dbDelta handles additive changes
        update_option( 'rank_booster_db_version', '1.4' );
    }

    public static function create_table() {
        global $wpdb;
        $table   = self::table_name();
        $charset = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            session_id VARCHAR(36) NOT NULL,
            post_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            object_type VARCHAR(10) NOT NULL DEFAULT 'post',
            url TEXT NOT NULL,
            editor_type VARCHAR(20) DEFAULT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'pending',
            mode VARCHAR(20) NOT NULL DEFAULT 'automatic',
            selected_checks TEXT DEFAULT NULL,
            manual_progress LONGTEXT DEFAULT NULL,
            focus_keywords TEXT DEFAULT NULL,
            custom_prompt TEXT DEFAULT NULL,
            api_job_id VARCHAR(100) DEFAULT NULL,
            draft_title TEXT DEFAULT NULL,
            draft_meta TEXT DEFAULT NULL,
            draft_h1 TEXT DEFAULT NULL,
            draft_title_tag TEXT DEFAULT NULL,
            draft_content LONGTEXT DEFAULT NULL,
            summary_html TEXT DEFAULT NULL,
            analysis_data LONGTEXT DEFAULT NULL,
            credits_used INT DEFAULT 0,
            update_publish_date TINYINT(1) DEFAULT 0,
            log_entries LONGTEXT DEFAULT NULL,
            preview_nonce VARCHAR(100) DEFAULT NULL,
            pushed_live_at DATETIME DEFAULT NULL,
            error_message TEXT DEFAULT NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY  (id),
            KEY idx_session (session_id),
            KEY idx_post (post_id),
            KEY idx_status (status)
        ) $charset;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );
    }

    public static function table_exists() {
        global $wpdb;
        $table = self::table_name();
        return $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $table ) ) === $table;
    }

    /* ───────────────────── insert / update ───────────────────── */

    /**
     * Insert a single job row.
     *
     * @param array $data Associative array of column => value.
     * @return int|false Insert ID on success, false on failure.
     */
    public static function insert_job( $data ) {
        global $wpdb;
        $now = current_time( 'mysql' );
        $data['created_at'] = $now;
        $data['updated_at'] = $now;
        $result = $wpdb->insert( self::table_name(), $data );
        if ( ! $result ) {
            error_log( 'Rank Booster DB insert failed: ' . $wpdb->last_error );
            return false;
        }
        return $wpdb->insert_id;
    }

    /**
     * Update a job row by its ID.
     *
     * @param int   $id   Job ID.
     * @param array $data Column => value pairs to update.
     * @return bool
     */
    public static function update_job( $id, $data ) {
        global $wpdb;
        $data['updated_at'] = current_time( 'mysql' );
        return $wpdb->update(
            self::table_name(),
            $data,
            array( 'id' => $id ),
            null,
            array( '%d' )
        ) !== false;
    }

    /* ───────────────────── read queries ───────────────────── */

    /**
     * Get a single job by ID.
     */
    public static function get_job( $id ) {
        global $wpdb;
        $table = self::table_name();
        return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $table WHERE id = %d", $id ) );
    }

    /**
     * Get all jobs for a session, ordered by status (ready first) then id.
     */
    public static function get_jobs_by_session( $session_id ) {
        global $wpdb;
        $table = self::table_name();
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM $table WHERE session_id = %s ORDER BY id ASC",
            $session_id
        ) );
    }

    /**
     * Get the most recent session that still has queued or processing jobs.
     *
     * Uses a two-step approach: first find which session has active jobs (fast,
     * uses idx_status index on the few active rows), then get full counts for
     * that session only (uses idx_session on ~20 rows).
     *
     * @return object|null  Row with session_id, counts, etc.
     */
    public static function get_active_session() {
        global $wpdb;
        $table = self::table_name();

        // Step 1: Find the session_id that has active jobs (uses idx_status)
        $active_session = $wpdb->get_var(
            "SELECT session_id FROM $table
             WHERE status IN ('queued','processing')
             ORDER BY created_at DESC
             LIMIT 1"
        );

        if ( ! $active_session ) {
            return null;
        }

        // Step 2: Get full counts for just that session (uses idx_session)
        return $wpdb->get_row( $wpdb->prepare(
            "SELECT session_id,
                    COUNT(*) AS total_jobs,
                    SUM( status IN ('queued','processing') ) AS active_jobs,
                    SUM( status = 'completed' ) AS completed_jobs,
                    SUM( status = 'failed' ) AS failed_jobs,
                    MAX(created_at) AS started_at
             FROM $table
             WHERE session_id = %s",
            $active_session
        ) );
    }

    /**
     * Get the most recent completed session (no active jobs).
     *
     * Finds the most recently updated session that has at least one
     * completed/pushed_live job and no active jobs.
     */
    public static function get_latest_completed_session() {
        global $wpdb;
        $table = self::table_name();

        // Find the most recently updated completed/pushed_live job's session
        $candidate = $wpdb->get_var(
            "SELECT session_id FROM $table
             WHERE status IN ('completed','pushed_live')
             ORDER BY updated_at DESC
             LIMIT 1"
        );

        if ( ! $candidate ) {
            return null;
        }

        // Verify this session has no active jobs, and get counts
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT session_id,
                    COUNT(*) AS total_jobs,
                    SUM( status = 'completed' ) AS completed_jobs,
                    SUM( status = 'pushed_live' ) AS pushed_live_jobs,
                    SUM( status IN ('queued','processing') ) AS active_jobs,
                    MAX(updated_at) AS finished_at
             FROM $table
             WHERE session_id = %s",
            $candidate
        ) );

        // If this session still has active jobs, it's not "completed"
        if ( $row && (int) $row->active_jobs > 0 ) {
            return null;
        }

        return $row;
    }

    /**
     * Paginated list of completed jobs (for "Pages Optimized" sub-tab).
     *
     * @param int $page     1-based page number.
     * @param int $per_page Items per page.
     * @return array        [ 'data' => [...], 'total' => int ]
     */
    public static function get_optimized_pages( $page = 1, $per_page = 20 ) {
        global $wpdb;
        $table  = self::table_name();
        $offset = ( $page - 1 ) * $per_page;

        $total = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM $table WHERE status IN ('completed','pushed_live')"
        );

        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM $table WHERE status IN ('completed','pushed_live') ORDER BY updated_at DESC LIMIT %d OFFSET %d",
            $per_page,
            $offset
        ) );

        return array( 'data' => $rows, 'total' => $total );
    }

    /**
     * Get ALL completed/pushed_live jobs (no pagination). Used for CSV export.
     *
     * @return array
     */
    public static function get_all_optimized_pages() {
        global $wpdb;
        $table = self::table_name();

        return $wpdb->get_results(
            "SELECT * FROM $table WHERE status IN ('completed','pushed_live') ORDER BY updated_at DESC"
        );
    }

    /* ───────────────────── bulk operations ───────────────────── */

    /**
     * Mark all queued / processing jobs in a session as aborted.
     */
    public static function abort_session( $session_id ) {
        global $wpdb;
        $table = self::table_name();
        return $wpdb->query( $wpdb->prepare(
            "UPDATE $table SET status = 'aborted' WHERE session_id = %s AND status IN ('queued','processing','pending')",
            $session_id
        ) );
    }

    /**
     * Delete all jobs in a session (admin cleanup).
     */
    public static function delete_session( $session_id ) {
        global $wpdb;
        return $wpdb->delete( self::table_name(), array( 'session_id' => $session_id ), array( '%s' ) );
    }

    /* ───────────────────── log helpers ───────────────────── */

    /**
     * Append a log entry to a job's log_entries JSON array.
     *
     * Uses a single SQL query with JSON_ARRAY_APPEND (MySQL 5.7+) to avoid
     * the old SELECT→decode→push→encode→UPDATE round-trip and race conditions.
     * Falls back to the read-modify-write pattern if JSON functions are unavailable.
     *
     * @param int    $job_id  Job ID.
     * @param string $message Log message text.
     */
    public static function add_log_entry( $job_id, $message ) {
        global $wpdb;
        $table = self::table_name();

        $entry_json = wp_json_encode( array(
            'time'    => gmdate( 'H:i:s' ) . 'Z',
            'message' => $message,
        ) );

        // Single-query atomic append using MySQL JSON functions.
        // COALESCE handles NULL log_entries by starting with an empty array.
        $result = $wpdb->query( $wpdb->prepare(
            "UPDATE $table SET log_entries = JSON_ARRAY_APPEND( COALESCE(log_entries, '[]'), '$', CAST(%s AS JSON) ), updated_at = %s WHERE id = %d",
            $entry_json,
            current_time( 'mysql' ),
            $job_id
        ) );

        // Fallback for MySQL < 5.7 or MariaDB without JSON support
        if ( $result === false ) {
            $row     = $wpdb->get_row( $wpdb->prepare( "SELECT log_entries FROM $table WHERE id = %d", $job_id ) );
            $entries = $row && $row->log_entries ? json_decode( $row->log_entries, true ) : array();
            $entries[] = array(
                'time'    => gmdate( 'H:i:s' ) . 'Z',
                'message' => $message,
            );
            $wpdb->update(
                $table,
                array(
                    'log_entries' => wp_json_encode( $entries ),
                    'updated_at'  => current_time( 'mysql' ),
                ),
                array( 'id' => $job_id ),
                array( '%s', '%s' ),
                array( '%d' )
            );
        }
    }
}
