<?php
/**
 * Database schema and migrations
 *
 * @package WahraRentalsBookings
 */

defined('ABSPATH') || exit;

/**
 * Database class
 */
class WAHRB_Database {

    /**
     * Single instance
     *
     * @var WAHRB_Database|null
     */
    private static ?WAHRB_Database $instance = null;

    /**
     * WordPress database object
     *
     * @var wpdb
     */
    private wpdb $wpdb;

    /**
     * Table prefix
     *
     * @var string
     */
    private string $prefix;

    /**
     * Get single instance
     *
     * @return WAHRB_Database
     */
    public static function instance(): WAHRB_Database {
        if (is_null(self::$instance)) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Constructor
     */
    private function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->prefix = $wpdb->prefix . 'wahrb_';
    }

    /**
     * Get table name
     *
     * @param string $table Table name without prefix
     * @return string Full table name
     */
    public function table(string $table): string {
        return $this->prefix . $table;
    }

    /**
     * Create all tables
     */
    public function create_tables(): void {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $this->wpdb->get_charset_collate();

        // Buffer output from dbDelta which can sometimes output messages
        ob_start();

        // Create each table
        $this->create_bookings_table($charset_collate);
        $this->create_availability_index_table($charset_collate);
        $this->create_price_rules_table($charset_collate);
        $this->create_holds_table($charset_collate);
        $this->create_payment_schedule_table($charset_collate);
        $this->create_blackout_dates_table($charset_collate);
        $this->create_wizard_drafts_table($charset_collate);
        $this->create_logs_table($charset_collate);
        $this->create_daily_availability_table($charset_collate); // NEW: Daily Index
        $this->create_booking_notes_table($charset_collate);
        $this->create_email_logs_table($charset_collate);
        $this->create_sms_logs_table($charset_collate);
        $this->create_form_templates_table($charset_collate);
        $this->create_coupons_table($charset_collate);
        $this->create_coupon_usage_table($charset_collate);

        // Discard any output
        ob_end_clean();

        // Update database version
        update_option('wahrb_db_version', WAHRB_DB_VERSION);
    }

    /**
     * Create bookings table
     *
     * @param string $charset_collate
     */
    private function create_bookings_table(string $charset_collate): void {
        $table_name = $this->table('bookings');

        $sql = "CREATE TABLE {$table_name} (
            booking_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            booking_number VARCHAR(50) NOT NULL,
            vendor_id BIGINT UNSIGNED NOT NULL,
            product_id BIGINT UNSIGNED NOT NULL,
            variation_id BIGINT UNSIGNED NULL,
            customer_id BIGINT UNSIGNED NULL,
            customer_order_id BIGINT UNSIGNED NULL,
            customer_order2_id BIGINT UNSIGNED NULL,
            booking_mode ENUM('day', 'slot', 'service') NOT NULL DEFAULT 'day',
            start_dt DATETIME NOT NULL,
            end_dt DATETIME NOT NULL,
            qty INT UNSIGNED DEFAULT 1,
            status ENUM('hold', 'pending', 'confirmed', 'checked_in', 'checked_out', 'completed', 'cancelled', 'failed') NOT NULL DEFAULT 'pending',
            customer_name VARCHAR(255) NULL,
            customer_email VARCHAR(255) NULL,
            customer_phone VARCHAR(50) NULL,
            notes_customer TEXT NULL,
            notes_vendor TEXT NULL,
            pricing_snapshot LONGTEXT NULL,
            checkin_dt DATETIME NULL,
            checkin_notes TEXT NULL,
            checkout_dt DATETIME NULL,
            checkout_notes TEXT NULL,
            cancellation_reason VARCHAR(255) NULL,
            cancellation_policy ENUM('flexible', 'moderate', 'strict') DEFAULT 'flexible',
            refund_amount DECIMAL(10, 2) NULL,
            meta_data LONGTEXT NULL,
            version_hash VARCHAR(64) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            deleted_at TIMESTAMP NULL,
            PRIMARY KEY (booking_id),
            UNIQUE KEY uk_booking_number (booking_number),
            KEY idx_vendor_status (vendor_id, status),
            KEY idx_product_dates (product_id, start_dt, end_dt),
            KEY idx_customer_email (customer_email),
            KEY idx_customer_id (customer_id),
            KEY idx_status (status),
            KEY idx_created_at (created_at),
            KEY idx_status_start_dt (status, start_dt),
            KEY idx_status_end_dt (status, end_dt),
            KEY idx_start_dt (start_dt),
            KEY idx_end_dt (end_dt)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create availability index table
     *
     * @param string $charset_collate
     */
    private function create_availability_index_table(string $charset_collate): void {
        $table_name = $this->table('availability_index');

        $sql = "CREATE TABLE {$table_name} (
            avail_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            vendor_id BIGINT UNSIGNED NOT NULL,
            product_id BIGINT UNSIGNED NOT NULL,
            variation_id BIGINT UNSIGNED NULL,
            resource_id BIGINT UNSIGNED NULL,
            date_start DATE NOT NULL,
            date_end DATE NOT NULL,
            time_start TIME NULL,
            time_end TIME NULL,
            capacity INT UNSIGNED DEFAULT 1,
            is_bookable TINYINT(1) DEFAULT 1,
            recurrence_days LONGTEXT NULL,
            recurrence_hours LONGTEXT NULL,
            constraints_data LONGTEXT NULL,
            version_hash VARCHAR(64) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (avail_id),
            KEY idx_product_dates (product_id, date_start, date_end),
            KEY idx_vendor_product (vendor_id, product_id),
            KEY idx_bookable (is_bookable),
            KEY idx_date_start (date_start)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create price rules table
     *
     * @param string $charset_collate
     */
    private function create_price_rules_table(string $charset_collate): void {
        $table_name = $this->table('price_rules');

        $sql = "CREATE TABLE {$table_name} (
            rule_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            vendor_id BIGINT UNSIGNED NOT NULL,
            product_id BIGINT UNSIGNED NOT NULL,
            variation_id BIGINT UNSIGNED NULL,
            rule_name VARCHAR(255) NOT NULL,
            rule_type ENUM('date_range', 'weekday', 'duration', 'guests') NOT NULL,
            date_start DATE NULL,
            date_end DATE NULL,
            weekday TINYINT(1) UNSIGNED NULL,
            weekdays LONGTEXT NULL,
            min_duration_days INT UNSIGNED NULL,
            max_duration_days INT UNSIGNED NULL,
            min_guests INT UNSIGNED NULL,
            max_guests INT UNSIGNED NULL,
            modifier_type ENUM('fixed', 'percent') NOT NULL,
            modifier_value DECIMAL(10, 2) NOT NULL,
            priority INT DEFAULT 0,
            active TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (rule_id),
            KEY idx_product_active (product_id, active),
            KEY idx_vendor_product (vendor_id, product_id),
            KEY idx_priority (priority),
            KEY idx_dates (date_start, date_end)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create holds table (anti double-booking)
     *
     * @param string $charset_collate
     */
    private function create_holds_table(string $charset_collate): void {
        $table_name = $this->table('holds');

        $sql = "CREATE TABLE {$table_name} (
            hold_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            session_token VARCHAR(255) NOT NULL,
            product_id BIGINT UNSIGNED NOT NULL,
            variation_id BIGINT UNSIGNED NULL,
            start_dt DATETIME NOT NULL,
            end_dt DATETIME NOT NULL,
            qty INT UNSIGNED DEFAULT 1,
            customer_id BIGINT UNSIGNED NULL,
            customer_email VARCHAR(255) NULL,
            hold_payload_hash VARCHAR(64) NULL,
            expires_at DATETIME NOT NULL,
            is_active TINYINT(1) DEFAULT 1,
            converted_to_booking TINYINT(1) DEFAULT 0,
            booking_id BIGINT UNSIGNED NULL,
            order_id BIGINT UNSIGNED NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (hold_id),
            KEY idx_session_product (session_token, product_id),
            KEY idx_expires (expires_at),
            KEY idx_product_dates (product_id, start_dt, end_dt),
            KEY idx_active (is_active)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create payment schedule table
     *
     * @param string $charset_collate
     */
    private function create_payment_schedule_table(string $charset_collate): void {
        $table_name = $this->table('payment_schedule');

        $sql = "CREATE TABLE {$table_name} (
            payment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            booking_id BIGINT UNSIGNED NOT NULL,
            vendor_id BIGINT UNSIGNED NOT NULL,
            product_id BIGINT UNSIGNED NOT NULL,
            deposit_enabled TINYINT(1) DEFAULT 0,
            deposit_type ENUM('fixed', 'percent') NULL,
            deposit_value DECIMAL(10, 2) NULL,
            deposit_amount_final DECIMAL(10, 2) NULL,
            deposit_status ENUM('not_due', 'pending', 'paid', 'failed', 'refunded') DEFAULT 'not_due',
            deposit_paid_at DATETIME NULL,
            deposit_order_id BIGINT UNSIGNED NULL,
            deposit_refundable TINYINT(1) DEFAULT 1,
            security_deposit_enabled TINYINT(1) DEFAULT 0,
            security_deposit_type ENUM('fixed', 'percent') NULL,
            security_deposit_value DECIMAL(10, 2) NULL,
            security_deposit_amount_final DECIMAL(10, 2) NULL,
            security_mode ENUM('capture', 'authorization') DEFAULT 'capture',
            security_status ENUM('not_due', 'pending', 'authorized', 'captured', 'released', 'forfeited', 'partial_forfeited') DEFAULT 'not_due',
            security_captured_at DATETIME NULL,
            security_released_at DATETIME NULL,
            stripe_intent_id VARCHAR(255) NULL,
            security_damage_claim DECIMAL(10, 2) NULL,
            security_damage_notes TEXT NULL,
            balance_enabled TINYINT(1) DEFAULT 1,
            balance_amount_final DECIMAL(10, 2) NULL,
            balance_due_at DATETIME NULL,
            balance_due_days_before INT DEFAULT 3,
            balance_status ENUM('not_due', 'pending', 'paid', 'failed', 'partial') DEFAULT 'not_due',
            balance_paid_at DATETIME NULL,
            balance_order_id BIGINT UNSIGNED NULL,
            balance_allow_cod TINYINT(1) DEFAULT 0,
            balance_payment_method VARCHAR(100) NULL,
            subtotal_amount DECIMAL(10, 2) NOT NULL,
            total_amount DECIMAL(10, 2) NOT NULL,
            currency_code CHAR(3) DEFAULT 'TND',
            tax_amount DECIMAL(10, 2) DEFAULT 0,
            tax_rate DECIMAL(5, 2) DEFAULT 0,
            notes TEXT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (payment_id),
            UNIQUE KEY uk_booking_id (booking_id),
            KEY idx_vendor_id (vendor_id),
            KEY idx_balance_due (balance_due_at),
            KEY idx_deposit_status (deposit_status),
            KEY idx_security_status (security_status),
            KEY idx_balance_status (balance_status)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create blackout dates table
     *
     * @param string $charset_collate
     */
    private function create_blackout_dates_table(string $charset_collate): void {
        $table_name = $this->table('blackout_dates');

        $sql = "CREATE TABLE {$table_name} (
            blackout_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            vendor_id BIGINT UNSIGNED NOT NULL,
            product_id BIGINT UNSIGNED NOT NULL,
            variation_id BIGINT UNSIGNED NULL,
            date_start DATE NOT NULL,
            date_end DATE NOT NULL,
            reason VARCHAR(255) NULL,
            is_recurring TINYINT(1) DEFAULT 0,
            recurring_pattern LONGTEXT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            deleted_at TIMESTAMP NULL DEFAULT NULL,
            PRIMARY KEY (blackout_id),
            KEY idx_product_dates (product_id, date_start, date_end),
            KEY idx_vendor_product (vendor_id, product_id),
            KEY idx_date_range (date_start, date_end),
            KEY idx_deleted_at (deleted_at)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create wizard drafts table
     *
     * @param string $charset_collate
     */
    private function create_wizard_drafts_table(string $charset_collate): void {
        $table_name = $this->table('wizard_drafts');

        $sql = "CREATE TABLE {$table_name} (
            draft_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            vendor_id BIGINT UNSIGNED NOT NULL,
            current_step INT DEFAULT 0,
            product_id BIGINT UNSIGNED NULL,
            offer_type VARCHAR(50) DEFAULT 'day',
            booking_mode ENUM('day', 'slot', 'service') NULL,
            wizard_data LONGTEXT NULL,
            status ENUM('in_progress', 'published', 'abandoned') DEFAULT 'in_progress',
            is_complete TINYINT(1) DEFAULT 0,
            version_hash VARCHAR(64) NULL,
            draft_expires_at DATETIME NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (draft_id),
            KEY idx_vendor (vendor_id),
            KEY idx_status (status, vendor_id),
            KEY idx_expires (draft_expires_at),
            KEY idx_incomplete (is_complete, vendor_id)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create logs table (audit trail)
     *
     * @param string $charset_collate
     */
    private function create_logs_table(string $charset_collate): void {
        $table_name = $this->table('logs');

        $sql = "CREATE TABLE {$table_name} (
            log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NULL,
            user_role VARCHAR(100) NULL,
            action VARCHAR(100) NOT NULL,
            subject_type VARCHAR(100) NULL,
            subject_id BIGINT UNSIGNED NULL,
            details LONGTEXT NULL,
            ip_address VARCHAR(45) NULL,
            user_agent TEXT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (log_id),
            KEY idx_user_action (user_id, action),
            KEY idx_action (action),
            KEY idx_created (created_at)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create daily availability table for fast lookups
     *
     * @param string $charset_collate
     */
    private function create_daily_availability_table(string $charset_collate): void {
        $table_name = $this->table('daily_availability');

        $sql = "CREATE TABLE {$table_name} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id BIGINT UNSIGNED NOT NULL,
            variation_id BIGINT UNSIGNED NULL,
            availability_date DATE NOT NULL,
            total_units INT UNSIGNED DEFAULT 1,
            booked_units INT UNSIGNED DEFAULT 0,
            held_units INT UNSIGNED DEFAULT 0,
            available_units INT UNSIGNED DEFAULT 1,
            is_blackout TINYINT(1) DEFAULT 0,
            is_weekend TINYINT(1) DEFAULT 0,
            base_price DECIMAL(10, 2) NULL,
            computed_price DECIMAL(10, 2) NULL,
            min_stay_override INT UNSIGNED NULL,
            last_sync_at TIMESTAMP NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uk_product_date (product_id, variation_id, availability_date),
            KEY idx_product_available (product_id, availability_date, available_units),
            KEY idx_date (availability_date),
            KEY idx_blackout (is_blackout)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create booking notes table
     *
     * @param string $charset_collate
     */
    private function create_booking_notes_table(string $charset_collate): void {
        $table_name = $this->table('booking_notes');

        $sql = "CREATE TABLE {$table_name} (
            note_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            booking_id BIGINT UNSIGNED NOT NULL,
            author_id BIGINT UNSIGNED NULL,
            content TEXT NOT NULL,
            is_private TINYINT(1) DEFAULT 0,
            note_type ENUM('manual', 'system', 'email', 'sms') DEFAULT 'manual',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (note_id),
            KEY idx_booking_id (booking_id),
            KEY idx_author_id (author_id),
            KEY idx_note_type (note_type),
            KEY idx_created_at (created_at)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create email logs table
     *
     * @param string $charset_collate
     */
    private function create_email_logs_table(string $charset_collate): void {
        $table_name = $this->table('email_logs');

        $sql = "CREATE TABLE {$table_name} (
            log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            booking_id BIGINT UNSIGNED NULL,
            email_type VARCHAR(100) NOT NULL,
            recipient VARCHAR(255) NOT NULL,
            subject VARCHAR(500) NOT NULL,
            body LONGTEXT NULL,
            status ENUM('sent', 'failed') DEFAULT 'sent',
            error_message TEXT NULL,
            sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (log_id),
            KEY idx_booking_id (booking_id),
            KEY idx_email_type (email_type),
            KEY idx_recipient (recipient),
            KEY idx_sent_at (sent_at)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create SMS logs table
     *
     * @param string $charset_collate
     */
    private function create_sms_logs_table(string $charset_collate): void {
        $table_name = $this->table('sms_logs');

        $sql = "CREATE TABLE {$table_name} (
            log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            booking_id BIGINT UNSIGNED NULL,
            phone VARCHAR(50) NOT NULL,
            message TEXT NOT NULL,
            sms_type VARCHAR(100) NULL,
            provider VARCHAR(50) DEFAULT 'twilio',
            provider_message_id VARCHAR(255) NULL,
            status ENUM('sent', 'delivered', 'failed', 'queued') DEFAULT 'sent',
            error_message TEXT NULL,
            sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (log_id),
            KEY idx_booking_id (booking_id),
            KEY idx_phone (phone),
            KEY idx_status (status),
            KEY idx_sent_at (sent_at)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create form templates table
     *
     * @param string $charset_collate
     */
    private function create_form_templates_table(string $charset_collate): void {
        $table_name = $this->table('form_templates');

        $sql = "CREATE TABLE {$table_name} (
            template_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            template_name VARCHAR(255) NOT NULL,
            description TEXT NULL,
            scope ENUM('global', 'category', 'product', 'vendor') DEFAULT 'global',
            category_slug VARCHAR(100) DEFAULT '',
            product_id BIGINT UNSIGNED DEFAULT 0,
            vendor_id BIGINT UNSIGNED DEFAULT 0,
            fields_json LONGTEXT NOT NULL,
            is_active TINYINT(1) DEFAULT 1,
            sort_order INT UNSIGNED DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (template_id),
            KEY idx_scope (scope),
            KEY idx_category (category_slug),
            KEY idx_product (product_id),
            KEY idx_vendor (vendor_id),
            KEY idx_active (is_active)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create coupons table
     *
     * @param string $charset_collate
     */
    private function create_coupons_table(string $charset_collate): void {
        $table_name = $this->table('coupons');

        $sql = "CREATE TABLE {$table_name} (
            coupon_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            code VARCHAR(50) NOT NULL,
            description TEXT NULL,
            discount_type ENUM('percent', 'fixed') DEFAULT 'percent',
            discount_value DECIMAL(10, 2) NOT NULL DEFAULT 0,
            min_order_amount DECIMAL(10, 2) DEFAULT 0,
            max_discount_amount DECIMAL(10, 2) DEFAULT 0,
            usage_limit INT UNSIGNED DEFAULT 0,
            usage_count INT UNSIGNED DEFAULT 0,
            per_user_limit INT UNSIGNED DEFAULT 0,
            valid_from DATETIME NULL,
            valid_until DATETIME NULL,
            category_slugs TEXT NULL,
            product_ids TEXT NULL,
            vendor_id BIGINT UNSIGNED DEFAULT 0,
            created_by BIGINT UNSIGNED DEFAULT 0,
            is_active TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (coupon_id),
            UNIQUE KEY idx_code (code),
            KEY idx_vendor (vendor_id),
            KEY idx_active (is_active),
            KEY idx_valid (valid_from, valid_until)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Create coupon usage table
     *
     * @param string $charset_collate
     */
    private function create_coupon_usage_table(string $charset_collate): void {
        $table_name = $this->table('coupon_usage');

        $sql = "CREATE TABLE {$table_name} (
            usage_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            coupon_id BIGINT UNSIGNED NOT NULL,
            booking_id BIGINT UNSIGNED NOT NULL,
            customer_email VARCHAR(255) NOT NULL,
            discount_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
            used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (usage_id),
            KEY idx_coupon (coupon_id),
            KEY idx_booking (booking_id),
            KEY idx_email (customer_email)
        ) {$charset_collate};";

        dbDelta($sql);
    }

    /**
     * Drop all tables
     */
    public function drop_tables(): void {
        $tables = [
            'bookings',
            'availability_index',
            'price_rules',
            'holds',
            'payment_schedule',
            'blackout_dates',
            'wizard_drafts',
            'logs',
            'daily_availability',
            'booking_notes',
            'email_logs',
            'sms_logs',
            'form_templates',
            'coupons',
            'coupon_usage',
        ];

        foreach ($tables as $table) {
            $table_name = $this->table($table);
            $this->wpdb->query("DROP TABLE IF EXISTS {$table_name}");
        }

        delete_option('wahrb_db_version');
    }

    /**
     * Check if database needs upgrade
     *
     * @return bool
     */
    public function needs_upgrade(): bool {
        $installed_version = get_option('wahrb_db_version', '0');
        return version_compare($installed_version, WAHRB_DB_VERSION, '<');
    }

    /**
     * Upgrade database if needed
     */
    public function maybe_upgrade(): void {
        if ($this->needs_upgrade()) {
            $this->create_tables();
        }
    }

    /**
     * Get WPDB instance
     *
     * @return wpdb
     */
    public function wpdb(): wpdb {
        return $this->wpdb;
    }

    /**
     * Log an action
     *
     * @param string $action Action name
     * @param string|null $subject_type Subject type
     * @param int|null $subject_id Subject ID
     * @param array $details Additional details
     */
    public function log(string $action, ?string $subject_type = null, ?int $subject_id = null, array $details = []): void {
        $user_id = get_current_user_id();
        $user = $user_id ? get_user_by('id', $user_id) : null;

        $this->wpdb->insert(
            $this->table('logs'),
            [
                'user_id' => $user_id ?: null,
                'user_role' => $user ? implode(', ', $user->roles) : null,
                'action' => $action,
                'subject_type' => $subject_type,
                'subject_id' => $subject_id,
                'details' => !empty($details) ? wp_json_encode($details) : null,
                'ip_address' => $this->get_client_ip(),
                'user_agent' => isset($_SERVER['HTTP_USER_AGENT']) ? sanitize_text_field(wp_unslash($_SERVER['HTTP_USER_AGENT'])) : null,
            ],
            ['%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s']
        );
    }

    /**
     * Get client IP address
     *
     * @return string|null
     */
    private function get_client_ip(): ?string {
        $ip_keys = ['HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_X_REAL_IP', 'REMOTE_ADDR'];

        foreach ($ip_keys as $key) {
            if (!empty($_SERVER[$key])) {
                $ip = sanitize_text_field(wp_unslash($_SERVER[$key]));
                // Handle comma-separated list (X-Forwarded-For)
                if (strpos($ip, ',') !== false) {
                    $ip = trim(explode(',', $ip)[0]);
                }
                if (filter_var($ip, FILTER_VALIDATE_IP)) {
                    return $ip;
                }
            }
        }

        return null;
    }
}
