<?php
/**
 * Database setup class
 * Handles database table creation and upgrades
 * 
 * @package Unlockafe_addons
 */

namespace Unlockafe_addons\Classes;

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

class DB_Setup {
    
    /**
     * @var object Instance of this class
     */
    private static $instance = null;
    
    /**
     * Get instance of this class
     * 
     * @return object
     */
    public static function getInstance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Constructor
     */
    private function __construct() {
        add_action('init', array($this, 'check_db_version'));
        // Use plugin file basename instead of undefined constant
        register_activation_hook(UNLOCKAFE_ADDONS_PATH . 'unlock-addons-for-elementor.php', array($this, 'create_tables'));
        // Also create tables on init to ensure they exist
        add_action('init', array($this, 'create_tables'), 15);
    }
    
    /**
     * Check database version and update if needed
     * 
     * @return void
     */
    public function check_db_version() {
        $db_version = get_option('unlockafe_db_version', '1.0');
        
        if (version_compare($db_version, '1.1', '<')) {
            $this->create_tables();
            update_option('unlockafe_db_version', '1.1');
        }
    }
    
    /**
     * Create database tables
     * 
     * @return void
     */
    public function create_tables() {
        global $wpdb;
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        $charset_collate = $wpdb->get_charset_collate();
        
        // Email capture table
        $table_name = $wpdb->prefix . 'unlockafe_email_capture';
        
        // First, create the basic table without indexes
        $sql = "CREATE TABLE IF NOT EXISTS `$table_name` (
            `id` bigint(20) NOT NULL AUTO_INCREMENT,
            `name` varchar(100) NOT NULL,
            `email` varchar(100) NOT NULL,
            `page_url` text NOT NULL,
            `submitted_at` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            `status` varchar(20) DEFAULT 'subscribed',
            `ip_address` varchar(45) DEFAULT '',
            `user_agent` text,
            `form_id` varchar(50) DEFAULT '',
            `meta` longtext,
            PRIMARY KEY (`id`)
        ) $charset_collate";
        
        // Create the table
        dbDelta($sql);
        
        // Add the unique email index if it doesn't exist
        $email_index = $wpdb->get_var("SHOW INDEX FROM $table_name WHERE Key_name = 'email'");
        if (is_null($email_index)) {
            $wpdb->query("ALTER TABLE `$table_name` ADD INDEX `email` (`email`(100))");
        }
        
        // Add the status index if it doesn't exist
        $status_index = $wpdb->get_var("SHOW INDEX FROM $table_name WHERE Key_name = 'status'");
        if (is_null($status_index)) {
            $wpdb->query("ALTER TABLE `$table_name` ADD INDEX `status` (`status`)");
        }
        
        // Add the form_id index if it doesn't exist
        $form_id_index = $wpdb->get_var("SHOW INDEX FROM $table_name WHERE Key_name = 'form_id'");
        if (is_null($form_id_index)) {
            $wpdb->query("ALTER TABLE `$table_name` ADD INDEX `form_id` (`form_id`)");
        }

        // --- CATEGORY TABLE ---
        $cat_table = $wpdb->prefix . 'unlockafe_email_categories';
        $cat_sql = "CREATE TABLE IF NOT EXISTS `$cat_table` (
            `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            `name` varchar(100) NOT NULL,
            `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        ) $charset_collate";
        dbDelta($cat_sql);

        // --- CATEGORY_ID COLUMN ---
        $columns = $wpdb->get_results("SHOW COLUMNS FROM `$table_name` LIKE 'category_id'");
        if (empty($columns)) {
            $wpdb->query("ALTER TABLE `$table_name` ADD COLUMN `category_id` bigint(20) UNSIGNED DEFAULT NULL");
            // Add foreign key if supported (optional, MySQL 5.6+)
            // $wpdb->query("ALTER TABLE `$table_name` ADD CONSTRAINT `fk_email_category` FOREIGN KEY (`category_id`) REFERENCES `$cat_table`(`id`) ON DELETE SET NULL");
        }
    }
}

// Initialize the class
DB_Setup::getInstance();
