<?php
/**
 * Content Plan Database Service
 *
 * Handles all database operations for content plans
 */

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

class CPEPAI_Database_Service
{
    /**
     * Table name for content plan items
     */
    private $plan_table_name;

    /**
     * Table name for tasks
     */
    private $tasks_table_name;

    /**
     * Table name for logs
     */
    private $logs_table_name;

    /**
     * WordPress database instance
     */
    private $wpdb;

    /**
     * The logging service instance.
     *
     * @var CPEPAI_Logging_Service|null
     */
    private ?CPEPAI_Logging_Service $logging_service = null;

    /**
     * Get the logging service instance.
     *
     * @return CPEPAI_Logging_Service
     */
    private function get_logging_service(): CPEPAI_Logging_Service
    {
        if ($this->logging_service === null) {
            $this->logging_service = new CPEPAI_Logging_Service($this);
        }
        return $this->logging_service;
    }

    /**
     * Current schema version
     */
    const SCHEMA_VERSION = '1.6';

    /**
     * Table identifiers
     */
    const TABLE_CPEPAI_ITEMS = 'cpepai_items';
    const TABLE_TASKS = 'cpepai_tasks';
    const TABLE_LOGS = 'cpepai_logs';

    /**
     * Constructor
     */
    public function __construct()
    {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->plan_table_name = $wpdb->prefix . self::TABLE_CPEPAI_ITEMS;
        $this->tasks_table_name = $wpdb->prefix . self::TABLE_TASKS;
        $this->logs_table_name = $wpdb->prefix . self::TABLE_LOGS;
    }

    /**
     * Get table name by identifier
     *
     * @param string $table_identifier Table identifier constant
     * @return string Full table name with prefix
     */
    private function get_table_name($table_identifier)
    {
        return $this->wpdb->prefix . $table_identifier;
    }

    /**
     * Register the database service (called during plugin initialization)
     * Ensures all tables exist
     *
     * @return void
     */
    public function register()
    {
        $this->ensure_tables_exist();

        // Update schemas if needed
        $this->update_schema_if_needed(self::TABLE_CPEPAI_ITEMS);
        $this->update_schema_if_needed(self::TABLE_TASKS);
        $this->update_schema_if_needed(self::TABLE_LOGS);
    }

    /**
     * Ensure all tables exist (called during plugin initialization)
     *
     * @return void
     */
    public function ensure_tables_exist()
    {
        // Create content plan items table if it doesn't exist
        if (!$this->table_exists(self::TABLE_CPEPAI_ITEMS)) {
            $this->create_table(self::TABLE_CPEPAI_ITEMS);
        }

        // Create tasks table if it doesn't exist
        if (!$this->table_exists(self::TABLE_TASKS)) {
            $this->create_table(self::TABLE_TASKS);
        }

        // Create logs table if it doesn't exist
        if (!$this->table_exists(self::TABLE_LOGS)) {
            $this->create_table(self::TABLE_LOGS);
        }
    }

    /**
     * Get table schema registry
     *
     * @param string $table_identifier Table identifier
     * @return array|null Table schema configuration or null if not found
     */
    private function get_table_schema_registry($table_identifier)
    {
        $schemas = [
            self::TABLE_CPEPAI_ITEMS => [
                'schema' => [
                    'id' => [
                        'type' => 'int(11)',
                        'null' => 'NOT NULL',
                        'auto_increment' => true,
                        'key' => 'PRIMARY KEY'
                    ],
                    'title' => [
                        'type' => 'varchar(255)',
                        'null' => 'NOT NULL'
                    ],
                    'structure' => [
                        'type' => 'text',
                        'null' => 'NULL'
                    ],
                    'keywords' => [
                        'type' => 'text',
                        'null' => 'NULL'
                    ],
                    'status' => [
                        'type' => 'varchar(20)',
                        'null' => 'NOT NULL',
                        'default' => 'planned'
                    ],
                    'scheduled_on' => [
                        'type' => 'datetime',
                        'null' => 'NULL'
                    ],
                    'post' => [
                        'type' => 'varchar(255)',
                        'null' => 'NULL'
                    ],
                    'lang' => [
                        'type' => 'varchar(10)',
                        'null' => 'NULL'
                    ],
                    'created_at' => [
                        'type' => 'datetime',
                        'null' => 'NULL',
                        'default' => 'CURRENT_TIMESTAMP'
                    ],
                    'updated_at' => [
                        'type' => 'datetime',
                        'null' => 'NULL',
                        'default' => 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
                    ]
                ],
                'column_positions' => [
                    'title' => 'id',
                    'structure' => 'title',
                    'keywords' => 'structure',
                    'status' => 'keywords',
                    'scheduled_on' => 'status',
                    'post' => 'scheduled_on',
                    'lang' => 'post',
                    'created_at' => 'lang',
                    'updated_at' => 'created_at'
                ],
                'composite_indexes' => []
            ],
            self::TABLE_TASKS => [
                'schema' => [
                    'id' => [
                        'type' => 'bigint(20)',
                        'null' => 'NOT NULL',
                        'auto_increment' => true,
                        'key' => 'PRIMARY KEY'
                    ],
                    'task_id' => [
                        'type' => 'varchar(36)',
                        'null' => 'NOT NULL',
                        'key' => 'UNIQUE'
                    ],
                    'type' => [
                        'type' => 'varchar(50)',
                        'null' => 'NOT NULL',
                        'default' => 'content_plan_generation'
                    ],
                    'status' => [
                        'type' => 'varchar(20)',
                        'null' => 'NOT NULL',
                        'default' => 'queued'
                    ],
                    'watched' => [
                        'type' => 'tinyint(1)',
                        'null' => 'NOT NULL',
                        'default' => '0'
                    ],
                    'result' => [
                        'type' => 'longtext',
                        'null' => 'NULL'
                    ],
                    'created_at' => [
                        'type' => 'datetime',
                        'null' => 'NULL',
                        'default' => 'CURRENT_TIMESTAMP'
                    ],
                    'updated_at' => [
                        'type' => 'datetime',
                        'null' => 'NULL',
                        'default' => 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
                    ]
                ],
                'column_positions' => [
                    'task_id' => 'id',
                    'type' => 'task_id',
                    'status' => 'type',
                    'watched' => 'status',
                    'result' => 'watched',
                    'created_at' => 'result',
                    'updated_at' => 'created_at'
                ],
                'composite_indexes' => ['KEY status_created_at (status, created_at)']
            ],
            self::TABLE_LOGS => [
                'schema' => [
                    'id' => [
                        'type' => 'bigint(20)',
                        'null' => 'NOT NULL',
                        'auto_increment' => true,
                        'key' => 'PRIMARY KEY'
                    ],
                    'created_at' => [
                        'type' => 'datetime',
                        'null' => 'NULL',
                        'default' => 'CURRENT_TIMESTAMP'
                    ],
                    'level' => [
                        'type' => 'varchar(20)',
                        'null' => 'NOT NULL',
                        'default' => 'info'
                    ],
                    'event' => [
                        'type' => 'varchar(100)',
                        'null' => 'NULL'
                    ],
                    'message' => [
                        'type' => 'text',
                        'null' => 'NULL'
                    ],
                    'context' => [
                        'type' => 'longtext',
                        'null' => 'NULL'
                    ],
                    'task_id' => [
                        'type' => 'varchar(36)',
                        'null' => 'NULL'
                    ],
                    'correlation_id' => [
                        'type' => 'varchar(100)',
                        'null' => 'NULL'
                    ],
                    'error_code' => [
                        'type' => 'varchar(50)',
                        'null' => 'NULL'
                    ],
                    'tokens_spent' => [
                        'type' => 'int(11)',
                        'null' => 'NULL'
                    ]
                ],
                'column_positions' => [
                    'created_at' => 'id',
                    'level' => 'created_at',
                    'event' => 'level',
                    'message' => 'event',
                    'context' => 'message',
                    'task_id' => 'context',
                    'correlation_id' => 'task_id',
                    'error_code' => 'correlation_id',
                    'tokens_spent' => 'error_code'
                ],
                'composite_indexes' => [
                    'KEY level_created_at (level, created_at)',
                    'KEY task_id (task_id)',
                    'KEY correlation_id (correlation_id)'
                ]
            ]
        ];

        return isset($schemas[$table_identifier]) ? $schemas[$table_identifier] : null;
    }

    /**
     * Universal table creation method
     *
     * @param string $table_identifier Table identifier constant
     * @return bool|WP_Error
     */
    public function create_table($table_identifier)
    {
        $table_name = $this->get_table_name($table_identifier);

        // Check if table already exists
        if ($this->table_exists($table_identifier)) {
            return true;
        }

        $schema_config = $this->get_table_schema_registry($table_identifier);
        if (!$schema_config) {
            return new WP_Error('invalid_table', "Unknown table identifier: {$table_identifier}");
        }

        $sql = $this->get_create_table_sql($table_identifier, $table_name);
        if (empty($sql)) {
            return new WP_Error('invalid_table', "Unknown table identifier: {$table_identifier}");
        }

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // Try dbDelta first
        $queries = dbDelta($sql);

        // Verify table was created
        $table_exists = $this->table_exists($table_identifier);

        if (!$table_exists) {
            // Retry dbDelta once; avoid direct SQL fallback to keep table creation
            // aligned with WordPress coding standards and schema handling.
            $this->wpdb->last_error = '';
            dbDelta($sql);

            $table_exists_after_retry = $this->table_exists($table_identifier);
            if (!$table_exists_after_retry) {
                $error_msg = $this->wpdb->last_error ?: 'Table was not created by dbDelta';
                return new WP_Error('db_error', "Failed to create table {$table_identifier}: " . $error_msg);
            }
        }

        return true;
    }

    /**
     * Build dbDelta-compatible CREATE TABLE SQL for known tables.
     *
     * @param string $table_identifier Table identifier constant
     * @param string $table_name Fully qualified table name
     * @return string|null SQL statement or null for unknown tables
     */
    private function get_create_table_sql($table_identifier, $table_name)
    {
        $charset_collate = $this->wpdb->get_charset_collate();

        if ($table_identifier === self::TABLE_CPEPAI_ITEMS) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name is internally generated from known constants.
            return "CREATE TABLE {$table_name} (
  id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  structure text NULL,
  keywords text NULL,
  status varchar(20) NOT NULL DEFAULT 'planned',
  scheduled_on datetime NULL,
  post varchar(255) NULL,
  lang varchar(10) NULL,
  created_at datetime NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) {$charset_collate};";
        }

        if ($table_identifier === self::TABLE_TASKS) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name is internally generated from known constants.
            return "CREATE TABLE {$table_name} (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  task_id varchar(36) NOT NULL,
  type varchar(50) NOT NULL DEFAULT 'content_plan_generation',
  status varchar(20) NOT NULL DEFAULT 'queued',
  watched tinyint(1) NOT NULL DEFAULT '0',
  result longtext NULL,
  created_at datetime NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY task_id (task_id),
  KEY status_created_at (status, created_at)
) {$charset_collate};";
        }

        if ($table_identifier === self::TABLE_LOGS) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name is internally generated from known constants.
            return "CREATE TABLE {$table_name} (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  created_at datetime NULL DEFAULT CURRENT_TIMESTAMP,
  level varchar(20) NOT NULL DEFAULT 'info',
  event varchar(100) NULL,
  message text NULL,
  context longtext NULL,
  task_id varchar(36) NULL,
  correlation_id varchar(100) NULL,
  error_code varchar(50) NULL,
  tokens_spent int(11) NULL,
  PRIMARY KEY (id),
  KEY level_created_at (level, created_at),
  KEY task_id (task_id),
  KEY correlation_id (correlation_id)
) {$charset_collate};";
        }

        return null;
    }

    /**
     * Create the content plan items table (backward compatibility wrapper)
     *
     * @deprecated Use create_table(self::TABLE_CPEPAI_ITEMS) instead
     */
    public function create_plan_table()
    {
        return $this->create_table(self::TABLE_CPEPAI_ITEMS);
    }

    /**
     * Legacy method for backward compatibility
     *
     * @deprecated Use create_table(self::TABLE_CPEPAI_ITEMS) instead
     */
    public function create_table_legacy()
    {
        return $this->create_table(self::TABLE_CPEPAI_ITEMS);
    }

    /**
     * Universal table drop method
     *
     * @param string $table_identifier Table identifier constant
     * @return bool
     */
    public function drop_table($table_identifier)
    {
        global $wpdb;
        $table_name = $this->get_table_name($table_identifier);
        return $wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', $table_name)) !== false;
    }

    /**
     * Drop all content plan tables (plan items, tasks, and logs)
     */
    public function drop_tables()
    {
        $this->drop_table(self::TABLE_CPEPAI_ITEMS);
        $this->drop_table(self::TABLE_TASKS);
        $this->drop_table(self::TABLE_LOGS);
    }

    /**
     * Delete all plugin data: custom tables and schema version options.
     *
     * @return void
     */
    public function delete_all_plugin_data(): void
    {
        $this->drop_tables();

        delete_option('cpepai_db_schema_version_' . self::TABLE_CPEPAI_ITEMS);
        delete_option('cpepai_db_schema_version_' . self::TABLE_TASKS);
        delete_option('cpepai_db_schema_version_' . self::TABLE_LOGS);
    }

    /**
     * Get all content plan items
     *
     * @return array
     */
    public function get_content_plan_items()
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_CPEPAI_ITEMS)) {
            $result = $this->create_table(self::TABLE_CPEPAI_ITEMS);
            if (is_wp_error($result)) {
                return [];
            }
        }

        global $wpdb;
        $table = $this->plan_table_name;
        $results = $wpdb->get_results($wpdb->prepare('SELECT * FROM %i ORDER BY created_at DESC', $table), ARRAY_A);

        return $results;
    }

    /**
     * Get the latest scheduled date from content plan items
     *
     * @return string|null Latest scheduled date in format 'YYYY-MM-DD HH:MM:SS' or null if no items
     */
    public function get_latest_scheduled_date()
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_CPEPAI_ITEMS)) {
            $this->create_table(self::TABLE_CPEPAI_ITEMS);
        }

        global $wpdb;
        $table = $this->plan_table_name;
        $result = $wpdb->get_var(
            $wpdb->prepare('SELECT MAX(scheduled_on) FROM %i WHERE scheduled_on IS NOT NULL', $table)
        );

        return $result;
    }

    /**
     * Get a single content plan item by ID
     *
     * @param int $id
     * @return array|null
     */
    public function get_content_plan_item($id)
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_CPEPAI_ITEMS)) {
            $this->create_table(self::TABLE_CPEPAI_ITEMS);
        }

        global $wpdb;
        $table = $this->plan_table_name;
        return $wpdb->get_row($wpdb->prepare('SELECT * FROM %i WHERE id = %d', $table, $id), ARRAY_A);
    }

    /**
     * Create a new content plan item
     *
     * @param array $data
     * @return int|false The ID of the created content plan item or false on failure
     */
    public function create_content_plan_item($data)
    {
        $defaults = [
            'title' => '',
            'structure' => '',
            'keywords' => '',
            'status' => 'planned',
            'scheduled_on' => null,
            'post' => null,
            'lang' => null
        ];

        $data = wp_parse_args($data, $defaults);

        // Validate required fields
        if (empty($data['title'])) {
            return new WP_Error('missing_title', 'Title is required');
        }

        if (empty($data['structure'])) {
            return new WP_Error('missing_structure', 'Structure is required');
        }

        // Sanitize data
        $data['title'] = sanitize_text_field($data['title']);
        $data['structure'] = sanitize_textarea_field($data['structure']);
        $data['keywords'] = sanitize_textarea_field($data['keywords']);

        if (!empty($data['post'])) {
            $data['post'] = sanitize_text_field($data['post']);
        } else {
            $data['post'] = null;
        }

        if (!empty($data['lang'])) {
            $data['lang'] = sanitize_text_field($data['lang']);
        } else {
            $data['lang'] = null;
        }

        // Validate status
        $valid_statuses = ['planned', 'draft_ready_ai', 'draft_ready_manual', 'published'];
        $data['status'] = sanitize_text_field($data['status']);
        if (!in_array($data['status'], $valid_statuses)) {
            return new WP_Error('invalid_status', 'Invalid status value');
        }

        if (!empty($data['scheduled_on'])) {
            $data['scheduled_on'] = sanitize_text_field($data['scheduled_on']);
        } else {
            $data['scheduled_on'] = null;
        }

        // Check if table exists
        if (!$this->table_exists(self::TABLE_CPEPAI_ITEMS)) {
            $table_created = $this->create_table(self::TABLE_CPEPAI_ITEMS);
            if (!$table_created) {
                return new WP_Error('db_error', 'Failed to create database table');
            }
        } else {
            // Update existing table schema if needed
            $schema_updated = $this->update_schema_if_needed(self::TABLE_CPEPAI_ITEMS);
            if (is_wp_error($schema_updated)) {
                return $schema_updated;
            }
        }

        // Format datetime for scheduled_on using WordPress timezone
        if (!empty($data['scheduled_on'])) {
            $scheduled_input = $data['scheduled_on'];

            // Check if the input has a time component
            // If it's just a date (YYYY-MM-DD), add 10:00:00 AM
            if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $scheduled_input)) {
                // Only date provided, add 10:00:00 AM
                $scheduled_input .= ' 10:00:00';
            }

            // Parse the input datetime string and convert to WordPress timezone
            $timestamp = strtotime($scheduled_input);
            if ($timestamp !== false) {
                // Use WordPress timezone-aware date formatting
                // wp_date() respects WordPress timezone and date format settings
                $data['scheduled_on'] = wp_date('Y-m-d H:i:s', $timestamp);
            }
        }

        // Set WordPress timezone-aware timestamps for created_at and updated_at
        $data['created_at'] = current_time('mysql');
        $data['updated_at'] = current_time('mysql');

        $result = $this->wpdb->insert($this->plan_table_name, $data, [
            '%s',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s'
        ]);

        if ($result === false) {
            return new WP_Error('db_error', 'Failed to create content plan item: ' . $this->wpdb->last_error);
        }

        return $this->wpdb->insert_id;
    }

    /**
     * Update an existing content plan item
     *
     * @param int $id
     * @param array $data
     * @return bool|WP_Error
     */
    public function update_content_plan_item($id, $data)
    {
        if (empty($id) || !is_numeric($id)) {
            return new WP_Error('invalid_id', 'Invalid content plan item ID');
        }

        // Check if content plan item exists
        $existing = $this->get_content_plan_item($id);
        if (!$existing) {
            return new WP_Error('not_found', 'Content plan item not found');
        }

        // Update table schema if needed
        $schema_updated = $this->update_schema_if_needed(self::TABLE_CPEPAI_ITEMS);
        if (is_wp_error($schema_updated)) {
            return $schema_updated;
        }

        // Sanitize data
        if (isset($data['title'])) {
            $data['title'] = sanitize_text_field($data['title']);
            if (empty($data['title'])) {
                return new WP_Error('missing_title', 'Title is required');
            }
        }

        if (isset($data['structure'])) {
            $data['structure'] = sanitize_textarea_field($data['structure']);
        }

        if (isset($data['keywords'])) {
            $data['keywords'] = sanitize_textarea_field($data['keywords']);
        }

        if (isset($data['scheduled_on'])) {
            if (!empty($data['scheduled_on'])) {
                $scheduled_input = $data['scheduled_on'];

                // Check if the input has a time component
                // If it's just a date (YYYY-MM-DD), add 10:00:00 AM
                if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $scheduled_input)) {
                    // Only date provided, add 10:00:00 AM
                    $scheduled_input .= ' 10:00:00';
                }

                // Format datetime for scheduled_on using WordPress timezone
                $timestamp = strtotime($scheduled_input);
                if ($timestamp !== false) {
                    // Use WordPress timezone-aware date formatting
                    // wp_date() respects WordPress timezone and date format settings
                    $data['scheduled_on'] = wp_date('Y-m-d H:i:s', $timestamp);
                }
            } else {
                $data['scheduled_on'] = null;
            }
        }

        if (isset($data['post'])) {
            if (!empty($data['post'])) {
                $data['post'] = sanitize_text_field($data['post']);
            } else {
                $data['post'] = null;
            }
        }

        if (isset($data['lang'])) {
            if (!empty($data['lang'])) {
                $data['lang'] = sanitize_text_field($data['lang']);
            } else {
                $data['lang'] = null;
            }
        }

        // Set WordPress timezone-aware updated_at timestamp
        $data['updated_at'] = current_time('mysql');

        // Define format types for each possible field
        $format_types = [
            'title' => '%s',
            'structure' => '%s',
            'keywords' => '%s',
            'status' => '%s',
            'scheduled_on' => '%s',
            'post' => '%s',
            'lang' => '%s',
            'updated_at' => '%s'
        ];

        // Build format array based on fields present in $data
        $formats = [];
        foreach ($data as $field => $value) {
            if (isset($format_types[$field])) {
                $formats[] = $format_types[$field];
            }
        }

        $result = $this->wpdb->update($this->plan_table_name, $data, ['id' => $id], $formats, ['%d']);

        if ($result === false) {
            return new WP_Error('db_error', 'Failed to update content plan item: ' . $this->wpdb->last_error);
        }

        return true;
    }

    /**
     * Delete a content plan item
     *
     * @param int $id
     * @return bool|WP_Error
     */
    public function delete_content_plan_item($id)
    {
        if (empty($id) || !is_numeric($id)) {
            return new WP_Error('invalid_id', 'Invalid content plan item ID');
        }

        // Check if content plan item exists
        $existing = $this->get_content_plan_item($id);
        if (!$existing) {
            return new WP_Error('not_found', 'Content plan item not found');
        }

        $result = $this->wpdb->delete($this->plan_table_name, ['id' => $id], ['%d']);

        if ($result === false) {
            return new WP_Error('db_error', 'Failed to delete content plan item');
        }

        return true;
    }

    /**
     * Universal table existence check
     *
     * @param string $table_identifier Table identifier constant
     * @return bool
     */
    public function table_exists($table_identifier)
    {
        global $wpdb;
        $table_name = $this->get_table_name($table_identifier);
        return $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table_name)) === $table_name;
    }

    /**
     * Check if plan table exists (backward compatibility wrapper)
     *
     * @return bool
     */
    public function plan_table_exists()
    {
        return $this->table_exists(self::TABLE_CPEPAI_ITEMS);
    }

    /**
     * Universal method to get expected schema
     *
     * @param string $table_identifier Table identifier constant
     * @return array|null
     */
    private function get_expected_schema($table_identifier)
    {
        $schema_config = $this->get_table_schema_registry($table_identifier);
        $schema = $schema_config ? $schema_config['schema'] : null;

        return $schema;
    }

    /**
     * Universal method to get current table schema
     *
     * @param string $table_identifier Table identifier constant
     * @return array
     */
    private function get_current_schema($table_identifier)
    {
        global $wpdb;
        $table_name = $this->get_table_name($table_identifier);
        $columns = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra
                 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s
                 ORDER BY ORDINAL_POSITION",
                $table_name
            ),
            ARRAY_A
        );

        $schema = [];
        foreach ($columns as $column) {
            $schema[$column['Field']] = [
                'type' => $column['Type'],
                'null' => $column['Null'],
                'key' => $column['Key'],
                'default' => $column['Default'],
                'extra' => $column['Extra']
            ];
        }
        return $schema;
    }

    /**
     * Universal method to update table schema
     *
     * @param string $table_identifier Table identifier constant
     * @return bool|WP_Error
     */
    public function update_table_schema($table_identifier)
    {
        $table_name = $this->get_table_name($table_identifier);
        $expected_schema = $this->get_expected_schema($table_identifier);

        if (!$expected_schema) {
            return new WP_Error('invalid_table', "Unknown table identifier: {$table_identifier}");
        }
        $current_schema = $this->get_current_schema($table_identifier);

        // Check for missing columns
        $missing_columns = [];
        foreach ($expected_schema as $column_name => $column_config) {
            if (!isset($current_schema[$column_name])) {
                $missing_columns[] = $column_name;
            }
        }

        if (empty($missing_columns)) {
            return true;
        }
        foreach ($expected_schema as $column_name => $column_config) {
            if (!isset($current_schema[$column_name])) {
                $prepared = $this->build_alter_column_sql(
                    $column_name,
                    $column_config,
                    true,
                    $table_name,
                    $table_identifier
                );

                if ($prepared) {
                    global $wpdb;
                    if (count($prepared['args']) === 3) {
                        $sql = $wpdb->prepare(
                            'ALTER TABLE %i ADD COLUMN %s AFTER %i',
                            $prepared['args'][0],
                            $prepared['args'][1],
                            $prepared['args'][2]
                        );
                        $result = $wpdb->query(
                            $wpdb->prepare(
                                'ALTER TABLE %i ADD COLUMN %s AFTER %i',
                                $prepared['args'][0],
                                $prepared['args'][1],
                                $prepared['args'][2]
                            )
                        );
                    } else {
                        $sql = $wpdb->prepare(
                            'ALTER TABLE %i ADD COLUMN %s',
                            $prepared['args'][0],
                            $prepared['args'][1]
                        );
                        $result = $wpdb->query(
                            $wpdb->prepare('ALTER TABLE %i ADD COLUMN %s', $prepared['args'][0], $prepared['args'][1])
                        );
                    }
                    if ($result === false) {
                        return new WP_Error(
                            'db_error',
                            "Failed to add column {$column_name}: " . $this->wpdb->last_error
                        );
                    }
                }
            }
        }

        return true;
    }

    /**
     * Build column definition for CREATE TABLE (without keys)
     *
     * @param string $column_name
     * @param array $column_config
     * @return string
     */
    private function build_column_definition($column_name, $column_config)
    {
        $sql_parts = [];

        // Column name
        $sql_parts[] = $column_name;

        // Column type
        $sql_parts[] = $column_config['type'];

        // NULL/NOT NULL
        if (isset($column_config['null'])) {
            $sql_parts[] = $column_config['null'];
        }

        // DEFAULT value and ON UPDATE clause
        if (isset($column_config['default'])) {
            $default_value = $column_config['default'];

            // Check if it contains ON UPDATE
            if (strpos($default_value, 'ON UPDATE') !== false) {
                // For "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", we need DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                $sql_parts[] = "DEFAULT {$default_value}";
            } elseif ($default_value === 'CURRENT_TIMESTAMP') {
                $sql_parts[] = "DEFAULT {$default_value}";
            } elseif ($default_value !== null) {
                $sql_parts[] = "DEFAULT '" . esc_sql($default_value) . "'";
            }
        }

        // AUTO_INCREMENT
        if (isset($column_config['auto_increment']) && $column_config['auto_increment']) {
            $sql_parts[] = 'AUTO_INCREMENT';
        }

        return implode(' ', $sql_parts);
    }

    /**
     * Build ALTER TABLE SQL for adding or modifying columns
     *
     * @param string $column_name
     * @param array $column_config
     * @param bool $is_add
     * @param string $table_name Table name
     * @param string|null $table_identifier Optional table identifier for column positioning
     * @return array{format: string, args: array}|null Format string and args for wpdb->prepare(), or null
     */
    private function build_alter_column_sql(
        $column_name,
        $column_config,
        $is_add = false,
        $table_name,
        $table_identifier = null
    ) {
        // Build column definition (without PRIMARY KEY in column definition for ALTER)
        $column_definition = $this->build_column_definition($column_name, $column_config);
        if ($is_add) {
            // Determine position for new column
            $after_column = $table_identifier
                ? $this->determine_column_position($column_name, $table_identifier)
                : null;
            if ($after_column) {
                return [
                    'format' => 'ALTER TABLE %i ADD COLUMN %s AFTER %i',
                    'args' => [$table_name, $column_definition, $after_column]
                ];
            }
            return [
                'format' => 'ALTER TABLE %i ADD COLUMN %s',
                'args' => [$table_name, $column_definition]
            ];
        }

        return [
            'format' => 'ALTER TABLE %i MODIFY COLUMN %s',
            'args' => [$table_name, $column_definition]
        ];
    }

    /**
     * Determine where to position a new column
     *
     * @param string $column_name
     * @param string $table_identifier Table identifier constant
     * @return string|null
     */
    private function determine_column_position($column_name, $table_identifier)
    {
        $schema_config = $this->get_table_schema_registry($table_identifier);
        if (!$schema_config || !isset($schema_config['column_positions'])) {
            return null;
        }

        return isset($schema_config['column_positions'][$column_name])
            ? $schema_config['column_positions'][$column_name]
            : null;
    }

    /**
     * Universal method to check if schema needs updating
     *
     * @param string $table_identifier Table identifier constant
     * @return bool
     */
    public function needs_schema_update($table_identifier)
    {
        $current_version = $this->get_current_schema_version($table_identifier);
        $needs_update = version_compare($current_version, self::SCHEMA_VERSION, '<');
        return $needs_update;
    }

    /**
     * Universal method to complete schema update process
     *
     * @param string $table_identifier Table identifier constant
     * @return bool|WP_Error
     */
    public function update_schema_if_needed($table_identifier)
    {
        // Check version first
        $version_needs_update = $this->needs_schema_update($table_identifier);

        // Also check if columns are actually missing (in case version was updated but columns weren't added)
        $has_missing_columns = false;
        if ($this->table_exists($table_identifier)) {
            $expected_schema = $this->get_expected_schema($table_identifier);
            $current_schema = $this->get_current_schema($table_identifier);

            if ($expected_schema) {
                foreach ($expected_schema as $column_name => $column_config) {
                    if (!isset($current_schema[$column_name])) {
                        $has_missing_columns = true;
                        break;
                    }
                }
            }
        }

        if ($version_needs_update || $has_missing_columns) {
            $result = $this->update_table_schema($table_identifier);

            if (!is_wp_error($result)) {
                $this->update_schema_version(self::SCHEMA_VERSION, $table_identifier);
            } else {
            }

            return $result;
        }
        return true;
    }

    /**
     * Get current schema version from database
     *
     * @param string $table_identifier Table identifier constant
     * @return string
     */
    private function get_current_schema_version($table_identifier)
    {
        $option_name = 'cpepai_db_schema_version_' . $table_identifier;
        $version = get_option($option_name, '1.0');
        return $version;
    }

    /**
     * Update schema version in database
     *
     * @param string $version
     * @param string $table_identifier Table identifier constant
     * @return void
     */
    private function update_schema_version($version, $table_identifier)
    {
        $option_name = 'cpepai_db_schema_version_' . $table_identifier;
        update_option($option_name, $version);
        $verify = get_option($option_name);
    }

    /**
     * Create the tasks table (backward compatibility wrapper)
     */
    public function create_tasks_table()
    {
        return $this->create_table(self::TABLE_TASKS);
    }

    /**
     * Drop the tasks table (backward compatibility wrapper)
     */
    public function drop_tasks_table()
    {
        return $this->drop_table(self::TABLE_TASKS);
    }

    /**
     * Check if tasks table exists (backward compatibility wrapper)
     *
     * @return bool
     */
    public function tasks_table_exists()
    {
        return $this->table_exists(self::TABLE_TASKS);
    }

    /**
     * Create a new task
     *
     * @param array $data
     * @return int|WP_Error The ID of the created task or WP_Error on failure
     */
    public function create_task($data)
    {
        $defaults = [
            'task_id' => '',
            'type' => 'content_plan_generation',
            'status' => 'queued',
            'result' => null
        ];

        $data = wp_parse_args($data, $defaults);

        // Validate required fields
        if (empty($data['task_id'])) {
            return new WP_Error('missing_task_id', 'Task ID is required');
        }

        // Sanitize data
        $data['task_id'] = sanitize_text_field($data['task_id']);
        $data['type'] = sanitize_text_field($data['type']);

        // Validate status
        $valid_statuses = ['queued', 'processing', 'completed', 'failed'];
        $data['status'] = sanitize_text_field($data['status']);
        if (!in_array($data['status'], $valid_statuses)) {
            return new WP_Error('invalid_status', 'Invalid status value');
        }

        // Serialize result if it's an array
        if (is_array($data['result']) || is_object($data['result'])) {
            $data['result'] = wp_json_encode($data['result']);
        } elseif (!empty($data['result'])) {
            $data['result'] = sanitize_textarea_field($data['result']);
        } else {
            $data['result'] = null;
        }

        // Check if table exists
        if (!$this->table_exists(self::TABLE_TASKS)) {
            $table_created = $this->create_table(self::TABLE_TASKS);
            if (!$table_created) {
                return new WP_Error('db_error', 'Failed to create tasks database table');
            }
        } else {
            // Update existing table schema if needed
            $schema_updated = $this->update_schema_if_needed(self::TABLE_TASKS);
            if (is_wp_error($schema_updated)) {
                return $schema_updated;
            }
        }

        // Set WordPress timezone-aware timestamps
        $data['created_at'] = current_time('mysql');
        $data['updated_at'] = current_time('mysql');

        // Define format types for each field
        $format_types = [
            'task_id' => '%s',
            'type' => '%s',
            'status' => '%s',
            'result' => '%s',
            'created_at' => '%s',
            'updated_at' => '%s'
        ];

        // Build format array based on fields present in $data
        $formats = [];
        foreach ($data as $field => $value) {
            if (isset($format_types[$field])) {
                $formats[] = $format_types[$field];
            }
        }

        $result = $this->wpdb->insert($this->tasks_table_name, $data, $formats);

        if ($result === false) {
            return new WP_Error('db_error', 'Failed to create task: ' . $this->wpdb->last_error);
        }

        return $this->wpdb->insert_id;
    }

    /**
     * Get a task by ID
     *
     * @param int $id
     * @return array|null
     */
    public function get_task($id)
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_TASKS)) {
            $result = $this->create_table(self::TABLE_TASKS);
            if (is_wp_error($result)) {
                return null;
            }
        }

        global $wpdb;
        $table = $this->tasks_table_name;
        $task = $wpdb->get_row($wpdb->prepare('SELECT * FROM %i WHERE id = %d', $table, $id), ARRAY_A);

        if ($task && !empty($task['result'])) {
            $decoded = json_decode($task['result'], true);
            if (json_last_error() === JSON_ERROR_NONE) {
                $task['result'] = $decoded;
            }
        }

        return $task;
    }

    /**
     * Get a task by task_id (UUID)
     *
     * @param string $task_id
     * @return array|null
     */
    public function get_task_by_task_id($task_id)
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_TASKS)) {
            $this->create_table(self::TABLE_TASKS);
        }

        global $wpdb;
        $table = $this->tasks_table_name;
        $task = $wpdb->get_row($wpdb->prepare('SELECT * FROM %i WHERE task_id = %s', $table, $task_id), ARRAY_A);

        if ($task && !empty($task['result'])) {
            $decoded = json_decode($task['result'], true);
            if (json_last_error() === JSON_ERROR_NONE) {
                $task['result'] = $decoded;
            }
        }

        return $task;
    }

    /**
     * Get tasks by status
     *
     * @param string $status
     * @param int $limit Optional limit
     * @return array
     */
    public function get_tasks_by_status($status, $limit = null)
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_TASKS)) {
            $this->create_table(self::TABLE_TASKS);
        }

        $valid_statuses = ['queued', 'processing', 'completed', 'failed'];
        if (!in_array($status, $valid_statuses)) {
            return [];
        }

        global $wpdb;
        $table = $this->tasks_table_name;
        if ($limit) {
            $tasks = $wpdb->get_results(
                $wpdb->prepare(
                    'SELECT * FROM %i WHERE status = %s ORDER BY created_at DESC LIMIT %d',
                    $table,
                    $status,
                    $limit
                ),
                ARRAY_A
            );
        } else {
            $tasks = $wpdb->get_results(
                $wpdb->prepare('SELECT * FROM %i WHERE status = %s ORDER BY created_at DESC', $table, $status),
                ARRAY_A
            );
        }

        // Decode JSON results
        foreach ($tasks as &$task) {
            if (!empty($task['result'])) {
                $decoded = json_decode($task['result'], true);
                if (json_last_error() === JSON_ERROR_NONE) {
                    $task['result'] = $decoded;
                }
            }
        }

        return $tasks;
    }

    /**
     * Get all tasks
     *
     * @param array $args Optional query arguments
     * @return array
     */
    public function get_tasks($args = [])
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_TASKS)) {
            $result = $this->create_table(self::TABLE_TASKS);
            if (is_wp_error($result)) {
                return [];
            }
        } else {
            // Update existing table schema if needed
            $schema_updated = $this->update_schema_if_needed(self::TABLE_TASKS);
            if (is_wp_error($schema_updated)) {
            }
        }

        $defaults = [
            'status' => null,
            'type' => null,
            'limit' => null,
            'offset' => 0,
            'orderby' => 'created_at',
            'order' => 'DESC'
        ];

        $args = wp_parse_args($args, $defaults);

        $status = !empty($args['status']) ? $args['status'] : null;
        $type = !empty($args['type']) ? $args['type'] : null;

        global $wpdb;
        $table = $this->tasks_table_name;
        $allowed_orderby = ['id', 'task_id', 'status', 'created_at', 'updated_at'];
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'created_at';
        $order = in_array(strtoupper($args['order']), ['ASC', 'DESC']) ? strtoupper($args['order']) : 'DESC';
        $limit = !empty($args['limit']) ? intval($args['limit']) : null;
        $offset = !empty($args['offset']) ? intval($args['offset']) : 0;
        $has_limit = $limit !== null;
        $has_offset = $offset > 0;
        $has_status = $status !== null;
        $has_type = $type !== null;

        if ($order === 'ASC') {
            if ($has_status && $has_type) {
                if ($has_limit && $has_offset) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s AND type = %s ORDER BY %i ASC LIMIT %d OFFSET %d',
                            $table,
                            $status,
                            $type,
                            $orderby,
                            $limit,
                            $offset
                        ),
                        ARRAY_A
                    );
                } elseif ($has_limit) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s AND type = %s ORDER BY %i ASC LIMIT %d',
                            $table,
                            $status,
                            $type,
                            $orderby,
                            $limit
                        ),
                        ARRAY_A
                    );
                } else {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s AND type = %s ORDER BY %i ASC',
                            $table,
                            $status,
                            $type,
                            $orderby
                        ),
                        ARRAY_A
                    );
                }
            } elseif ($has_status) {
                if ($has_limit && $has_offset) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s ORDER BY %i ASC LIMIT %d OFFSET %d',
                            $table,
                            $status,
                            $orderby,
                            $limit,
                            $offset
                        ),
                        ARRAY_A
                    );
                } elseif ($has_limit) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s ORDER BY %i ASC LIMIT %d',
                            $table,
                            $status,
                            $orderby,
                            $limit
                        ),
                        ARRAY_A
                    );
                } else {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare('SELECT * FROM %i WHERE status = %s ORDER BY %i ASC', $table, $status, $orderby),
                        ARRAY_A
                    );
                }
            } elseif ($has_type) {
                if ($has_limit && $has_offset) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE type = %s ORDER BY %i ASC LIMIT %d OFFSET %d',
                            $table,
                            $type,
                            $orderby,
                            $limit,
                            $offset
                        ),
                        ARRAY_A
                    );
                } elseif ($has_limit) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE type = %s ORDER BY %i ASC LIMIT %d',
                            $table,
                            $type,
                            $orderby,
                            $limit
                        ),
                        ARRAY_A
                    );
                } else {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare('SELECT * FROM %i WHERE type = %s ORDER BY %i ASC', $table, $type, $orderby),
                        ARRAY_A
                    );
                }
            } elseif ($has_limit && $has_offset) {
                $tasks = $wpdb->get_results(
                    $wpdb->prepare(
                        'SELECT * FROM %i ORDER BY %i ASC LIMIT %d OFFSET %d',
                        $table,
                        $orderby,
                        $limit,
                        $offset
                    ),
                    ARRAY_A
                );
            } elseif ($has_limit) {
                $tasks = $wpdb->get_results(
                    $wpdb->prepare('SELECT * FROM %i ORDER BY %i ASC LIMIT %d', $table, $orderby, $limit),
                    ARRAY_A
                );
            } else {
                $tasks = $wpdb->get_results(
                    $wpdb->prepare('SELECT * FROM %i ORDER BY %i ASC', $table, $orderby),
                    ARRAY_A
                );
            }
        } else {
            if ($has_status && $has_type) {
                if ($has_limit && $has_offset) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s AND type = %s ORDER BY %i DESC LIMIT %d OFFSET %d',
                            $table,
                            $status,
                            $type,
                            $orderby,
                            $limit,
                            $offset
                        ),
                        ARRAY_A
                    );
                } elseif ($has_limit) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s AND type = %s ORDER BY %i DESC LIMIT %d',
                            $table,
                            $status,
                            $type,
                            $orderby,
                            $limit
                        ),
                        ARRAY_A
                    );
                } else {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s AND type = %s ORDER BY %i DESC',
                            $table,
                            $status,
                            $type,
                            $orderby
                        ),
                        ARRAY_A
                    );
                }
            } elseif ($has_status) {
                if ($has_limit && $has_offset) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s ORDER BY %i DESC LIMIT %d OFFSET %d',
                            $table,
                            $status,
                            $orderby,
                            $limit,
                            $offset
                        ),
                        ARRAY_A
                    );
                } elseif ($has_limit) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s ORDER BY %i DESC LIMIT %d',
                            $table,
                            $status,
                            $orderby,
                            $limit
                        ),
                        ARRAY_A
                    );
                } else {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE status = %s ORDER BY %i DESC',
                            $table,
                            $status,
                            $orderby
                        ),
                        ARRAY_A
                    );
                }
            } elseif ($has_type) {
                if ($has_limit && $has_offset) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE type = %s ORDER BY %i DESC LIMIT %d OFFSET %d',
                            $table,
                            $type,
                            $orderby,
                            $limit,
                            $offset
                        ),
                        ARRAY_A
                    );
                } elseif ($has_limit) {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE type = %s ORDER BY %i DESC LIMIT %d',
                            $table,
                            $type,
                            $orderby,
                            $limit
                        ),
                        ARRAY_A
                    );
                } else {
                    $tasks = $wpdb->get_results(
                        $wpdb->prepare('SELECT * FROM %i WHERE type = %s ORDER BY %i DESC', $table, $type, $orderby),
                        ARRAY_A
                    );
                }
            } elseif ($has_limit && $has_offset) {
                $tasks = $wpdb->get_results(
                    $wpdb->prepare(
                        'SELECT * FROM %i ORDER BY %i DESC LIMIT %d OFFSET %d',
                        $table,
                        $orderby,
                        $limit,
                        $offset
                    ),
                    ARRAY_A
                );
            } elseif ($has_limit) {
                $tasks = $wpdb->get_results(
                    $wpdb->prepare('SELECT * FROM %i ORDER BY %i DESC LIMIT %d', $table, $orderby, $limit),
                    ARRAY_A
                );
            } else {
                $tasks = $wpdb->get_results(
                    $wpdb->prepare('SELECT * FROM %i ORDER BY %i DESC', $table, $orderby),
                    ARRAY_A
                );
            }
        }

        // Log raw SQL result columns for debugging
        if (!empty($tasks)) {
            $first_task = reset($tasks);
        }

        // Decode JSON results
        foreach ($tasks as &$task) {
            // Always check the database directly for the result field
            // WordPress's get_results might return NULL as empty or missing
            // So we always fetch it separately to be sure we have the latest value
            if (isset($task['id'])) {
                global $wpdb;
                $table = $this->tasks_table_name;
                $task_id_val = $task['id'];
                $result_value = $wpdb->get_var(
                    $wpdb->prepare('SELECT result FROM %i WHERE id = %d', $table, $task_id_val)
                );

                // If we got a value from the separate query, use it (even if it was in the original result)
                // This ensures we always have the latest value, especially important for race conditions
                if ($result_value !== null && $result_value !== '') {
                    $task['result'] = $result_value;
                } else {
                    // Result is NULL/empty - set it explicitly
                    // For failed tasks without result, frontend polling will catch it once webhook updates
                    $task['result'] = null;
                    if (isset($task['status']) && $task['status'] === 'failed') {
                    } else {
                    }
                }
            }

            // Now decode if we have a result
            if (isset($task['result']) && !empty($task['result'])) {
                $decoded = json_decode($task['result'], true);
                if (json_last_error() === JSON_ERROR_NONE) {
                    $task['result'] = $decoded;
                } else {
                }
            }
        }

        return $tasks;
    }

    /**
     * Update an existing task
     *
     * @param int|string $id_or_task_id Task ID (numeric) or task_id (UUID string)
     * @param array $data
     * @return bool|WP_Error
     */
    public function update_task($id_or_task_id, $data)
    {
        // Determine if we're using id or task_id
        $is_numeric = is_numeric($id_or_task_id);
        $where_field = $is_numeric ? 'id' : 'task_id';
        $where_value = $is_numeric ? intval($id_or_task_id) : sanitize_text_field($id_or_task_id);

        // Check if task exists
        $existing = $is_numeric ? $this->get_task($id_or_task_id) : $this->get_task_by_task_id($id_or_task_id);

        if (!$existing) {
            return new WP_Error('not_found', 'Task not found');
        }

        // Update table schema if needed
        $schema_updated = $this->update_schema_if_needed(self::TABLE_TASKS);
        if (is_wp_error($schema_updated)) {
            return $schema_updated;
        }

        // Sanitize and validate data
        if (isset($data['status'])) {
            $valid_statuses = ['queued', 'processing', 'completed', 'failed'];
            $data['status'] = sanitize_text_field($data['status']);
            if (!in_array($data['status'], $valid_statuses)) {
                return new WP_Error('invalid_status', 'Invalid status value');
            }
        }

        if (isset($data['task_id'])) {
            $data['task_id'] = sanitize_text_field($data['task_id']);
        }

        if (isset($data['type'])) {
            $data['type'] = sanitize_text_field($data['type']);
        }

        // Serialize result if it's an array
        if (isset($data['result'])) {
            $original_result = $data['result'];
            if (is_array($data['result']) || is_object($data['result'])) {
                // Even if array/object is empty, serialize it (empty array becomes "[]", empty object becomes "{}")
                $data['result'] = wp_json_encode($data['result']);
            } elseif (is_string($data['result']) && $data['result'] !== '') {
                // Only sanitize non-empty strings
                $data['result'] = sanitize_textarea_field($data['result']);
            } elseif ($data['result'] === null) {
                // Explicitly keep null as null - BUT THIS SHOULD NOT HAPPEN IF OUR SAFEGUARDS WORK
                $data['result'] = null;
            } else {
                // For empty strings or other falsy values, set to null
                $data['result'] = null;
            }
        } else {
        }

        // Handle watched field
        if (isset($data['watched'])) {
            $data['watched'] = (bool) $data['watched'] ? 1 : 0;
        }

        // Set WordPress timezone-aware updated_at timestamp
        $data['updated_at'] = current_time('mysql');

        // Define format types for each possible field
        $format_types = [
            'task_id' => '%s',
            'type' => '%s',
            'status' => '%s',
            'watched' => '%d',
            'result' => '%s',
            'updated_at' => '%s'
        ];

        // Build format array based on fields present in $data
        $formats = [];
        foreach ($data as $field => $value) {
            if (isset($format_types[$field])) {
                $formats[] = $format_types[$field];
            }
        }

        $where_format = $is_numeric ? '%d' : '%s';

        // Log what we're about to update
        if (isset($data['result'])) {
        } else {
        }

        $result = $this->wpdb->update($this->tasks_table_name, $data, [$where_field => $where_value], $formats, [
            $where_format
        ]);

        if ($result === false) {
            return new WP_Error('db_error', 'Failed to update task: ' . $this->wpdb->last_error);
        }

        // Check if any rows were actually updated
        if ($result === 0) {
            // No rows were updated - this could mean:
            // 1. The WHERE clause didn't match any rows
            // 2. All values are the same as what's already in the database
            // Log this for debugging but don't treat it as an error
        } else {
        }

        // Verify what was actually saved
        $saved_task = $is_numeric ? $this->get_task($id_or_task_id) : $this->get_task_by_task_id($id_or_task_id);
        if ($saved_task) {
            // Verify status if it was updated
            if (isset($data['status'])) {
                $expected_status = $data['status'];
                $actual_status = $saved_task['status'] ?? null;
                if ($actual_status !== $expected_status) {
                    // Return error if status didn't update correctly
                    return new WP_Error(
                        'status_update_failed',
                        'Status update failed - expected ' . $expected_status . ' but got ' . ($actual_status ?? 'null')
                    );
                } else {
                }
            }

            // Verify result if it was updated
            if (isset($data['result'])) {
            }
        } else {
        }

        return true;
    }

    /**
     * Delete a task
     *
     * @param int|string $id_or_task_id Task ID (numeric) or task_id (UUID string)
     * @return bool|WP_Error
     */
    public function delete_task($id_or_task_id)
    {
        // Determine if we're using id or task_id
        $is_numeric = is_numeric($id_or_task_id);
        $where_field = $is_numeric ? 'id' : 'task_id';
        $where_value = $is_numeric ? intval($id_or_task_id) : sanitize_text_field($id_or_task_id);
        $where_format = $is_numeric ? '%d' : '%s';

        // Check if task exists
        $existing = $is_numeric ? $this->get_task($id_or_task_id) : $this->get_task_by_task_id($id_or_task_id);

        if (!$existing) {
            return new WP_Error('not_found', 'Task not found');
        }

        $result = $this->wpdb->delete($this->tasks_table_name, [$where_field => $where_value], [$where_format]);

        if ($result === false) {
            return new WP_Error('db_error', 'Failed to delete task');
        }

        return true;
    }

    /**
     * Update tasks table schema (backward compatibility wrapper)
     *
     * @return bool|WP_Error
     */
    public function update_tasks_table_schema()
    {
        if (!$this->table_exists(self::TABLE_TASKS)) {
            return $this->create_table(self::TABLE_TASKS);
        }

        return $this->update_table_schema(self::TABLE_TASKS);
    }

    /**
     * Check if tasks schema needs updating (backward compatibility wrapper)
     *
     * @return bool
     */
    public function needs_tasks_schema_update()
    {
        return $this->needs_schema_update(self::TABLE_TASKS);
    }

    /**
     * Complete tasks schema update process (backward compatibility wrapper)
     *
     * @return bool|WP_Error
     */
    public function update_tasks_schema_if_needed()
    {
        return $this->update_schema_if_needed(self::TABLE_TASKS);
    }

    /**
     * Get tasks table name
     *
     * @return string
     */
    public function get_tasks_table_name()
    {
        return $this->tasks_table_name;
    }

    /**
     * Create the logs table (backward compatibility wrapper)
     */
    public function create_logs_table()
    {
        return $this->create_table(self::TABLE_LOGS);
    }

    /**
     * Drop the logs table (backward compatibility wrapper)
     */
    public function drop_logs_table()
    {
        return $this->drop_table(self::TABLE_LOGS);
    }

    /**
     * Check if logs table exists (backward compatibility wrapper)
     *
     * @return bool
     */
    public function logs_table_exists()
    {
        return $this->table_exists(self::TABLE_LOGS);
    }

    /**
     * Get logs table name
     *
     * @return string
     */
    public function get_logs_table_name()
    {
        return $this->logs_table_name;
    }

    /**
     * Get logs
     *
     * @param array $args Optional query arguments
     * @return array
     */
    public function get_logs($args = [])
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_LOGS)) {
            $result = $this->create_table(self::TABLE_LOGS);
            if (is_wp_error($result)) {
                return [];
            }
        } else {
            // Update existing table schema if needed
            $schema_updated = $this->update_schema_if_needed(self::TABLE_LOGS);
            if (is_wp_error($schema_updated)) {
            }
        }

        $defaults = [
            'level' => null,
            'limit' => null,
            'offset' => 0,
            'orderby' => 'created_at',
            'order' => 'DESC'
        ];

        $args = wp_parse_args($args, $defaults);

        $level = !empty($args['level']) ? $args['level'] : null;

        global $wpdb;
        $table = $this->logs_table_name;
        $allowed_orderby = ['id', 'created_at', 'level', 'event'];
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'created_at';
        $order = in_array(strtoupper($args['order']), ['ASC', 'DESC']) ? strtoupper($args['order']) : 'DESC';
        $limit = !empty($args['limit']) ? intval($args['limit']) : null;
        $offset = !empty($args['offset']) ? intval($args['offset']) : 0;
        $has_limit = $limit !== null;
        $has_offset = $offset > 0;
        $has_level = $level !== null;

        if ($order === 'ASC') {
            if ($has_level) {
                if ($has_limit && $has_offset) {
                    $logs = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE level = %s ORDER BY %i ASC LIMIT %d OFFSET %d',
                            $table,
                            $level,
                            $orderby,
                            $limit,
                            $offset
                        ),
                        ARRAY_A
                    );
                } elseif ($has_limit) {
                    $logs = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE level = %s ORDER BY %i ASC LIMIT %d',
                            $table,
                            $level,
                            $orderby,
                            $limit
                        ),
                        ARRAY_A
                    );
                } else {
                    $logs = $wpdb->get_results(
                        $wpdb->prepare('SELECT * FROM %i WHERE level = %s ORDER BY %i ASC', $table, $level, $orderby),
                        ARRAY_A
                    );
                }
            } elseif ($has_limit && $has_offset) {
                $logs = $wpdb->get_results(
                    $wpdb->prepare(
                        'SELECT * FROM %i ORDER BY %i ASC LIMIT %d OFFSET %d',
                        $table,
                        $orderby,
                        $limit,
                        $offset
                    ),
                    ARRAY_A
                );
            } elseif ($has_limit) {
                $logs = $wpdb->get_results(
                    $wpdb->prepare('SELECT * FROM %i ORDER BY %i ASC LIMIT %d', $table, $orderby, $limit),
                    ARRAY_A
                );
            } else {
                $logs = $wpdb->get_results(
                    $wpdb->prepare('SELECT * FROM %i ORDER BY %i ASC', $table, $orderby),
                    ARRAY_A
                );
            }
        } else {
            if ($has_level) {
                if ($has_limit && $has_offset) {
                    $logs = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE level = %s ORDER BY %i DESC LIMIT %d OFFSET %d',
                            $table,
                            $level,
                            $orderby,
                            $limit,
                            $offset
                        ),
                        ARRAY_A
                    );
                } elseif ($has_limit) {
                    $logs = $wpdb->get_results(
                        $wpdb->prepare(
                            'SELECT * FROM %i WHERE level = %s ORDER BY %i DESC LIMIT %d',
                            $table,
                            $level,
                            $orderby,
                            $limit
                        ),
                        ARRAY_A
                    );
                } else {
                    $logs = $wpdb->get_results(
                        $wpdb->prepare('SELECT * FROM %i WHERE level = %s ORDER BY %i DESC', $table, $level, $orderby),
                        ARRAY_A
                    );
                }
            } elseif ($has_limit && $has_offset) {
                $logs = $wpdb->get_results(
                    $wpdb->prepare(
                        'SELECT * FROM %i ORDER BY %i DESC LIMIT %d OFFSET %d',
                        $table,
                        $orderby,
                        $limit,
                        $offset
                    ),
                    ARRAY_A
                );
            } elseif ($has_limit) {
                $logs = $wpdb->get_results(
                    $wpdb->prepare('SELECT * FROM %i ORDER BY %i DESC LIMIT %d', $table, $orderby, $limit),
                    ARRAY_A
                );
            } else {
                $logs = $wpdb->get_results(
                    $wpdb->prepare('SELECT * FROM %i ORDER BY %i DESC', $table, $orderby),
                    ARRAY_A
                );
            }
        }

        return $logs ? $logs : [];
    }

    /**
     * Mark tasks older than 1 hour as failed
     * Only updates tasks in 'queued' or 'processing' status
     *
     * @param int $hours_old Optional. Number of hours old (default: 1)
     * @return int|WP_Error Number of tasks updated or WP_Error on failure
     */
    public function mark_old_tasks_as_failed($hours_old = 1)
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_TASKS)) {
            return new WP_Error('table_not_exists', 'Tasks table does not exist');
        }

        // Calculate the cutoff time using WordPress timezone
        // Use a longer window (default 1 hour, but check updated_at instead of created_at)
        // This ensures we only mark tasks as failed if they haven't been updated recently
        $current_timestamp = current_time('timestamp');
        $cutoff_timestamp = $current_timestamp - $hours_old * HOUR_IN_SECONDS;
        $cutoff_time = wp_date('Y-m-d H:i:s', $cutoff_timestamp);

        // Get WordPress timezone-aware current time for updated_at
        $updated_at = current_time('mysql');

        // Build SQL to find truly stuck tasks:
        // - Must be in queued or processing status
        // - Must NOT have been updated recently (updated_at older than cutoff)
        // This ensures we only mark tasks as failed if they're truly stuck and inactive,
        // not if they're actively being processed (webhook updates would update updated_at)
        // Note: We check updated_at, not created_at, because a task might be old but still
        // actively processing (webhooks update updated_at when they arrive)
        global $wpdb;
        $table = $this->tasks_table_name;
        $result = $wpdb->query(
            $wpdb->prepare(
                'UPDATE %i SET status = %s, updated_at = %s WHERE status IN (%s, %s) AND (updated_at < %s OR updated_at IS NULL)',
                $table,
                'failed',
                $updated_at,
                'queued',
                'processing',
                $cutoff_time
            )
        );

        if ($result === false) {
            return new WP_Error('db_error', 'Failed to update old tasks: ' . $this->wpdb->last_error);
        }

        // Return the number of affected rows
        return (int) $result;
    }

    /**
     * Get count of future content plan items (items with scheduled_on in the future).
     *
     * @return int Number of future items
     */
    public function get_future_items_count()
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_CPEPAI_ITEMS)) {
            return 0;
        }

        // Get WordPress timezone-aware current time
        $current_time = current_time('mysql');

        global $wpdb;
        $table = $this->plan_table_name;
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM %i WHERE scheduled_on IS NOT NULL AND scheduled_on > %s',
                $table,
                $current_time
            )
        );
    }

    /**
     * Recalculate scheduled dates for future content plan items.
     * Only affects items with scheduled_on date in the future.
     * Recalculates dates based on the new publish interval, maintaining relative order.
     *
     * @param int $publish_interval_days New publish interval in days
     * @return array|WP_Error Array with 'updated_count' key or WP_Error on failure
     */
    public function recalculate_future_scheduled_dates($publish_interval_days)
    {
        // Ensure table exists before querying
        if (!$this->table_exists(self::TABLE_CPEPAI_ITEMS)) {
            return new WP_Error('table_not_exists', 'Content plan items table does not exist');
        }

        // Validate interval
        if (!is_numeric($publish_interval_days) || $publish_interval_days < 1) {
            return new WP_Error('invalid_interval', 'Invalid publish interval');
        }

        // Get WordPress timezone-aware current time
        $current_time = current_time('mysql');

        // Get all future items ordered by scheduled_on (ascending)
        global $wpdb;
        $table = $this->plan_table_name;
        $future_items = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT id, scheduled_on FROM %i WHERE scheduled_on IS NOT NULL AND scheduled_on > %s ORDER BY scheduled_on ASC',
                $table,
                $current_time
            ),
            ARRAY_A
        );

        if (empty($future_items)) {
            return [
                'updated_count' => 0
            ];
        }

        // Get the nearest (first) future item's scheduled date - this will be kept unchanged
        $nearest_item = $future_items[0];
        $base_timestamp = strtotime($nearest_item['scheduled_on']);

        if ($base_timestamp === false) {
            return new WP_Error('invalid_base_date', 'Failed to parse nearest item scheduled date');
        }

        // Recalculate dates for items after the first one
        // Skip index 0 (nearest item) and recalculate from index 1 onwards
        $updated_count = 0;
        $errors = [];

        foreach ($future_items as $index => $item) {
            // Keep the nearest (first) item's date unchanged
            if ($index === 0) {
                continue; // Skip the first item - keep its date
            }

            // Calculate new scheduled date: base (nearest item date) + (index) * interval days
            // Index 1: base + 1 * interval, Index 2: base + 2 * interval, etc.
            $days_to_add = $index * (int) $publish_interval_days;
            $new_timestamp = strtotime('+' . $days_to_add . ' days', $base_timestamp);

            if ($new_timestamp === false) {
                $errors[] = [
                    'item_id' => $item['id'],
                    'error' => 'Failed to calculate new scheduled date'
                ];
                continue;
            }

            // Format new date using WordPress timezone and set time to 10:00:00 AM
            $date_only = wp_date('Y-m-d', $new_timestamp);
            $new_scheduled_date = $date_only . ' 10:00:00';

            // Update the item
            $update_result = $this->update_content_plan_item($item['id'], [
                'scheduled_on' => $new_scheduled_date
            ]);

            if (is_wp_error($update_result)) {
                $errors[] = [
                    'item_id' => $item['id'],
                    'error' => $update_result->get_error_message()
                ];
            } else {
                $updated_count++;
            }
        }

        // If there were errors but some items were updated, return partial success
        if (!empty($errors) && $updated_count > 0) {
        } elseif (!empty($errors)) {
            // All updates failed
            return new WP_Error(
                'recalculation_failed',
                'Failed to recalculate scheduled dates: ' . $errors[0]['error']
            );
        }

        return [
            'updated_count' => $updated_count,
            'errors' => $errors
        ];
    }
}
