<?php
/**
 * Database Management
 *
 * @package Spin_Wheel
 */

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

/**
 * SWP_Database Class
 *
 * Dedicated data-access layer for the plugin's custom swp_entries / swp_winners
 * tables. Table names come from $wpdb->prefix (never user input) and dynamic
 * values are passed through $wpdb->prepare(); results are request-scoped and not
 * cached. The DB sniffs below are intentionally disabled for this class.
 */
// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL, WordPress.DB.PreparedSQLPlaceholders, PluginCheck.Security.DirectDB
class SWP_Database {
    
    /**
     * Instance
     *
     * @var SWP_Database
     */
    private static $instance = null;
    
    /**
     * Table names
     */
    private $entries_table;
    private $winners_table;
    
    /**
     * Get instance
     *
     * @return SWP_Database
     */
    public static function instance() {
        if (is_null(self::$instance)) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Constructor
     */
    private function __construct() {
        global $wpdb;
        $this->entries_table = $wpdb->prefix . 'swp_entries';
        $this->winners_table = $wpdb->prefix . 'swp_winners';
    }
    
    /**
     * Create database tables
     */
    public static function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $entries_table = $wpdb->prefix . 'swp_entries';
        $winners_table = $wpdb->prefix . 'swp_winners';
        
        // Entries table
        $sql_entries = "CREATE TABLE IF NOT EXISTS $entries_table (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            wheel_id bigint(20) NOT NULL,
            wheel_type varchar(50) NOT NULL,
            user_name varchar(255) NOT NULL,
            user_email varchar(255) NOT NULL,
            user_phone varchar(50) DEFAULT NULL,
            prize_label varchar(255) NOT NULL,
            prize_code varchar(100) DEFAULT NULL,
            ip_address varchar(100) NOT NULL,
            user_agent text DEFAULT NULL,
            is_winner tinyint(1) DEFAULT 0,
            is_selected_winner tinyint(1) DEFAULT 0,
            spin_data longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY wheel_id (wheel_id),
            KEY user_email (user_email),
            KEY is_winner (is_winner),
            KEY created_at (created_at)
        ) $charset_collate;";
        
        // Winners table (for event type wheels)
        $sql_winners = "CREATE TABLE IF NOT EXISTS $winners_table (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            wheel_id bigint(20) NOT NULL,
            entry_id bigint(20) NOT NULL,
            selected_by bigint(20) NOT NULL,
            prize_label varchar(255) NOT NULL,
            selected_at datetime DEFAULT CURRENT_TIMESTAMP,
            notified tinyint(1) DEFAULT 0,
            notified_at datetime DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY wheel_id (wheel_id),
            KEY entry_id (entry_id),
            KEY selected_at (selected_at)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql_entries);
        dbDelta($sql_winners);
    }
    
    /**
     * Save entry
     *
     * @param array $data Entry data
     * @return int|false Entry ID or false on failure
     */
    public function save_entry($data) {
        global $wpdb;
        
        $result = $wpdb->insert(
            $this->entries_table,
            [
                'wheel_id'      => $data['wheel_id'],
                'wheel_type'    => $data['wheel_type'],
                'user_name'     => $data['user_name'],
                'user_email'    => $data['user_email'],
                'user_phone'    => isset($data['user_phone']) ? $data['user_phone'] : null,
                'prize_label'   => $data['prize_label'],
                'prize_code'    => isset($data['prize_code']) ? $data['prize_code'] : null,
                'ip_address'    => $data['ip_address'],
                'user_agent'    => isset($data['user_agent']) ? $data['user_agent'] : null,
                'is_winner'     => isset($data['is_winner']) ? $data['is_winner'] : 0,
                'spin_data'     => isset($data['spin_data']) ? json_encode($data['spin_data']) : null,
            ],
            [
                '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s'
            ]
        );
        
        return $result ? $wpdb->insert_id : false;
    }
    
    /**
     * Get entries by wheel ID
     *
     * @param int $wheel_id Wheel ID
     * @param array $args Query arguments
     * @return array Entries
     */
    public function get_entries($wheel_id, $args = []) {
        global $wpdb;
        
        $defaults = [
            'limit'     => 100,
            'offset'    => 0,
            'orderby'   => 'created_at',
            'order'     => 'DESC',
            'is_winner' => null,
        ];
        
        $args = wp_parse_args($args, $defaults);
        
        $where = $wpdb->prepare('WHERE wheel_id = %d', $wheel_id);
        
        if ($args['is_winner'] !== null) {
            $where .= $wpdb->prepare(' AND is_winner = %d', $args['is_winner']);
        }
        
        // Whitelist orderby column and order direction to prevent SQL injection.
        $allowed_orderby = array('created_at', 'id', 'user_name', 'user_email', 'is_winner');
        $orderby = in_array($args['orderby'], $allowed_orderby, true) ? $args['orderby'] : 'created_at';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';

        // Build query - allow unlimited entries by setting limit to 0 or -1
        $limit_clause = '';
        if ($args['limit'] > 0 && $args['limit'] < 999999) {
            $limit_clause = $wpdb->prepare('LIMIT %d, %d', $args['offset'], $args['limit']);
        }

        $query = "SELECT * FROM {$this->entries_table}
                  $where
                  ORDER BY {$orderby} {$order}
                  $limit_clause";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- orderby/order whitelisted, where + limit use $wpdb->prepare(), table name is internal
        return $wpdb->get_results($query);
    }
    
    /**
     * Get all entries for a wheel (no limit)
     * Useful for admin interfaces and exports
     *
     * @param int $wheel_id Wheel ID
     * @param string $orderby Order by column
     * @param string $order Order direction (ASC/DESC)
     * @return array All entries
     */
    public function get_all_entries($wheel_id, $orderby = 'created_at', $order = 'DESC') {
        return $this->get_entries($wheel_id, [
            'limit' => 999999, // Effectively unlimited
            'offset' => 0,
            'orderby' => $orderby,
            'order' => $order
        ]);
    }
    
    /**
     * Get total entries count
     *
     * @param int $wheel_id Wheel ID
     * @param array $args Query arguments
     * @return int Count
     */
    public function count_entries($wheel_id, $args = []) {
        global $wpdb;
        
        $where = $wpdb->prepare('WHERE wheel_id = %d', $wheel_id);
        
        if (isset($args['is_winner']) && $args['is_winner'] !== null) {
            $where .= $wpdb->prepare(' AND is_winner = %d', $args['is_winner']);
        }
        
        $query = "SELECT COUNT(*) FROM {$this->entries_table} $where";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $where uses $wpdb->prepare(), table name is internal
        return (int) $wpdb->get_var($query);
    }
    
    /**
     * Mark entry as selected winner
     *
     * @param int $entry_id Entry ID
     * @param int $selected_by User ID who selected
     * @return bool Success
     */
    public function mark_as_winner($entry_id, $selected_by) {
        global $wpdb;
        
        // Get entry
        $entry = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$this->entries_table} WHERE id = %d",
            $entry_id
        ));
        
        if (!$entry) {
            return false;
        }
        
        // Update entry
        $wpdb->update(
            $this->entries_table,
            ['is_selected_winner' => 1],
            ['id' => $entry_id],
            ['%d'],
            ['%d']
        );
        
        // Insert into winners table
        $result = $wpdb->insert(
            $this->winners_table,
            [
                'wheel_id'    => $entry->wheel_id,
                'entry_id'    => $entry_id,
                'selected_by' => $selected_by,
                'prize_label' => $entry->prize_label,
            ],
            ['%d', '%d', '%d', '%s']
        );
        
        return $result !== false;
    }
    
    /**
     * Get winners
     *
     * @param int $wheel_id Wheel ID
     * @return array Winners
     */
    public function get_winners($wheel_id) {
        global $wpdb;
        
        $query = $wpdb->prepare(
            "SELECT w.*, e.user_name, e.user_email, e.user_phone 
             FROM {$this->winners_table} w 
             LEFT JOIN {$this->entries_table} e ON w.entry_id = e.id 
             WHERE w.wheel_id = %d 
             ORDER BY w.selected_at DESC",
            $wheel_id
        );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- built with $wpdb->prepare(), table names are internal
        return $wpdb->get_results($query);
    }

    /**
     * Check if email already participated
     *
     * @param int $wheel_id Wheel ID
     * @param string $email Email address
     * @return bool Already participated
     */
    public function has_participated($wheel_id, $email) {
        global $wpdb;
        
        $count = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->entries_table} 
             WHERE wheel_id = %d AND user_email = %s",
            $wheel_id,
            $email
        ));
        
        return $count > 0;
    }
    
    /**
     * Check if IP has exceeded rate limit
     *
     * @param string $ip_address IP address to check
     * @param int $max_attempts Maximum allowed attempts
     * @param int $period Time period in minutes
     * @return bool True if rate limit exceeded
     */
    public function is_rate_limited($ip_address, $max_attempts, $period) {
        global $wpdb;
        
        $time_ago = gmdate('Y-m-d H:i:s', strtotime("-$period minutes"));
        
        $count = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->entries_table} 
             WHERE ip_address = %s 
             AND created_at >= %s",
            $ip_address,
            $time_ago
        ));
        
        return $count >= $max_attempts;
    }
    
    /**
     * Delete old entries based on retention period
     *
     * @param int $days Number of days to keep entries (entries older than this will be deleted)
     * @return int|false Number of entries deleted or false on error
     */
    public function cleanup_old_entries($days) {
        global $wpdb;
        
        // Validate days parameter
        $days = intval($days);
        if ($days <= 0) {
            return false; // Don't delete if days is 0 or negative
        }
        
        // Calculate the cutoff date
        $cutoff_date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
        
        // Delete old entries
        $deleted = $wpdb->query($wpdb->prepare(
            "DELETE FROM {$this->entries_table} WHERE created_at < %s",
            $cutoff_date
        ));
        
        // Also delete corresponding winners if any
        if ($deleted > 0) {
            $wpdb->query(
                "DELETE FROM {$this->winners_table} 
                 WHERE entry_id NOT IN (SELECT id FROM {$this->entries_table})"
            );
        }
        
        return $deleted;
    }
    
    /**
     * Get count of entries older than specified days
     *
     * @param int $days Number of days
     * @return int Count of old entries
     */
    public function count_old_entries($days) {
        global $wpdb;
        
        $days = intval($days);
        if ($days <= 0) {
            return 0;
        }
        
        $cutoff_date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
        
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->entries_table} WHERE created_at < %s",
            $cutoff_date
        ));
    }
}

// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL, WordPress.DB.PreparedSQLPlaceholders, PluginCheck.Security.DirectDB
