<?php
namespace Lean_CSNS;

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Database operations for cart storage and retrieval
 */
class Database {
    
    /**
     * Singleton instance
     * 
     * @var Database
     */
    private static $instance = null;
    
    /**
     * Get singleton instance
     * 
     * @return Database
     */
    public static function get_instance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Private constructor to prevent direct instantiation
     */
    private function __construct() {
        $this->init();
    }
    
    /**
     * Initialize database operations
     */
    private function init() {
        // Database initialization hooks if needed
    }
    
    /**
     * Create plugin database tables
     */
    public function create_tables() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'lean_csns_carts';
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NULL,
            cart_data longtext NOT NULL,
            share_token varchar(64) NOT NULL,
            save_name varchar(255) NULL,
            type varchar(20) NOT NULL DEFAULT 'share',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            expires_at datetime NULL,
            meta_data longtext NULL,
            PRIMARY KEY (id),
            UNIQUE KEY share_token (share_token),
            KEY user_id (user_id),
            KEY type (type),
            KEY expires_at (expires_at)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- Plugin activation table creation
        dbDelta($sql);
    }
    
    /**
     * Insert cart data into database
     * 
     * @param array $data Cart data to insert
     * @return int|false Insert result
     */
    public function insert_cart($data) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'lean_csns_carts';
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Safe: using $wpdb->insert() with prepared format specifiers, no user input in table name
        return $wpdb->insert(
            $table_name,
            $data,
            ['%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
        );
        // phpcs:enable
    }
    
    /**
     * Get cart by share token
     * 
     * @param string $token Share token
     * @return object|null Cart data or null if not found
     */
    public function get_cart_by_token($token) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'lean_csns_carts';
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Safe: $wpdb->prepare() used for user input, table name is hardcoded plugin constant
        return $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM $table_name WHERE share_token = %s", $token)
        );
        // phpcs:enable
    }
    
    /**
     * Get saved carts for a user
     * 
     * @param int $user_id User ID
     * @return array Array of cart objects
     */
    public function get_saved_carts_by_user($user_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'lean_csns_carts';
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Safe: $wpdb->prepare() protects user_id parameter, table name is secure plugin constant
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM $table_name WHERE user_id = %d AND type = 'save' ORDER BY created_at DESC",
                $user_id
            )
        );
        // phpcs:enable
    }
    
    /**
     * Get cart by ID
     * 
     * @param int $id Cart ID
     * @return object|null Cart data or null if not found
     */
    public function get_cart_by_id($id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'lean_csns_carts';
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Safe: ID parameter sanitized by $wpdb->prepare(), table name is hardcoded constant
        return $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", $id)
        );
        // phpcs:enable
    }
    
    /**
     * Verify cart ownership
     * 
     * @param int $cart_id Cart ID
     * @param int $user_id User ID
     * @return bool True if user owns the cart, false otherwise
     */
    public function verify_cart_ownership($cart_id, $user_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'lean_csns_carts';
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Safe: Both parameters protected by $wpdb->prepare() with %d formatters, table name is secure constant
        $count = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM $table_name WHERE id = %d AND user_id = %d",
                $cart_id,
                $user_id
            )
        );
        // phpcs:enable
        
        return $count > 0;
    }

    /**
     * Delete cart by ID
     * 
     * @param int $id Cart ID
     * @return int|false Number of rows affected or false on failure
     */
    public function delete_cart($id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'lean_csns_carts';
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table delete operation
        return $wpdb->delete($table_name, ['id' => $id], ['%d']);
    }
    
    /**
     * Update cart meta_data column by share token.
     * Used by pro features to persist advanced options (custom message, etc.)
     * without adding new columns for each feature.
     *
     * @param string $token     Share token
     * @param array  $meta_data Key-value pairs to merge into existing meta_data
     * @return int|false Rows affected or false on failure
     */
    public function update_cart_meta_by_token( $token, $meta_data ) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'lean_csns_carts';

        // Fetch existing meta so we merge rather than overwrite other keys.
        $row = $this->get_cart_by_token( $token );
        if ( ! $row ) {
            return false;
        }

        $existing = ! empty( $row->meta_data ) ? json_decode( $row->meta_data, true ) : [];
        $merged   = array_merge( (array) $existing, $meta_data );

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Safe: $wpdb->prepare() protects token, table name is hardcoded constant
        return $wpdb->update(
            $table_name,
            [ 'meta_data' => wp_json_encode( $merged ) ],
            [ 'share_token' => $token ],
            [ '%s' ],
            [ '%s' ]
        );
        // phpcs:enable
    }

    /**
     * Update cart metadata
     *
     * @param int $id Cart ID
     * @param array $meta_data Metadata to update
     * @return int|false Number of rows affected or false on failure
     */
    public function update_cart_meta($id, $meta_data) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'lean_csns_carts';
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Safe: using $wpdb->update() with format specifiers for data protection, table name is hardcoded constant
        return $wpdb->update(
            $table_name,
            ['meta_data' => wp_json_encode($meta_data)],
            ['id' => $id],
            ['%s'],
            ['%d']
        );
        // phpcs:enable
    }
}

// Initialize singleton instance
Database::get_instance();