<?php
/**
 * Handles database migrations.
 *
 * @package StockGuardianPro
 */

declare(strict_types=1);

namespace StockGuardianPro\LowStock\Installations;

\defined('ABSPATH') || exit;

final class SchemaMigrator
{
    private const DB_VERSION = '1.2.0';

    public function migrate(): void
    {
        global $wpdb;

        $installed_version = get_option('woolsn_db_version');
        if ($installed_version === self::DB_VERSION) {
            return;
        }

        // Verify upgrade.php is available
        $upgrade_file = ABSPATH . 'wp-admin/includes/upgrade.php';
        if (! file_exists($upgrade_file)) {
            if (function_exists('do_action')) {
                do_action('woolsn_db_migration_error', 'upgrade.php not found');
            }
            return;
        }

        require_once $upgrade_file;

        // Verify dbDelta function exists
        if (! function_exists('dbDelta')) {
            if (function_exists('do_action')) {
                do_action('woolsn_db_migration_error', 'dbDelta function not available');
            }
            return;
        }

        $thresholds_table = $wpdb->prefix . 'low_stock_thresholds';
        $charset_collate = $wpdb->get_charset_collate();

        $thresholds_sql = "CREATE TABLE {$thresholds_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            variation_id bigint(20) unsigned DEFAULT 0,
            threshold int(11) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY product_key (product_id, variation_id)
        ) {$charset_collate};";

        $events_table = $wpdb->prefix . 'low_stock_events';

        $events_sql = "CREATE TABLE {$events_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            variation_id bigint(20) unsigned DEFAULT 0,
            product_name varchar(255) NOT NULL,
            stock int(11) NOT NULL DEFAULT 0,
            threshold int(11) NOT NULL DEFAULT 0,
            channel varchar(60) NOT NULL DEFAULT 'email',
            notified_at datetime NOT NULL,
            PRIMARY KEY (id),
            KEY product_lookup (product_id, variation_id),
            KEY notified_at (notified_at)
        ) {$charset_collate};";

        // Execute migrations with error handling
        dbDelta($thresholds_sql);
        dbDelta($events_sql);

        // Support Tickets System Tables
        $support_users_table = $wpdb->prefix . 'support_users';
        $support_users_sql = "CREATE TABLE {$support_users_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            email varchar(255) NOT NULL,
            first_name varchar(100) NOT NULL,
            last_name varchar(100) NOT NULL,
            password_hash varchar(255) NOT NULL,
            email_verified tinyint(1) NOT NULL DEFAULT 0,
            verification_token varchar(64) DEFAULT NULL,
            verification_expires datetime DEFAULT NULL,
            purchase_code varchar(64) DEFAULT NULL,
            envato_username varchar(100) DEFAULT NULL,
            product_id varchar(50) DEFAULT NULL,
            created_at datetime NOT NULL,
            last_login datetime DEFAULT NULL,
            status varchar(20) NOT NULL DEFAULT 'active',
            PRIMARY KEY (id),
            UNIQUE KEY email (email),
            KEY verification_token (verification_token),
            KEY purchase_code (purchase_code),
            KEY status (status)
        ) {$charset_collate};";

        $support_tickets_table = $wpdb->prefix . 'support_tickets';
        $support_tickets_sql = "CREATE TABLE {$support_tickets_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            ticket_number varchar(20) NOT NULL,
            user_id bigint(20) unsigned NOT NULL,
            product varchar(50) NOT NULL,
            purchase_code varchar(64) NOT NULL,
            subject varchar(255) NOT NULL,
            message longtext NOT NULL,
            priority varchar(20) NOT NULL DEFAULT 'normal',
            status varchar(20) NOT NULL DEFAULT 'open',
            assigned_to bigint(20) unsigned DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            closed_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY ticket_number (ticket_number),
            KEY user_id (user_id),
            KEY status (status),
            KEY priority (priority),
            KEY created_at (created_at),
            KEY assigned_to (assigned_to)
        ) {$charset_collate};";

        $support_replies_table = $wpdb->prefix . 'support_replies';
        $support_replies_sql = "CREATE TABLE {$support_replies_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            ticket_id bigint(20) unsigned NOT NULL,
            user_id bigint(20) unsigned NOT NULL,
            user_type varchar(20) NOT NULL DEFAULT 'client',
            message longtext NOT NULL,
            attachments text DEFAULT NULL,
            is_internal tinyint(1) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL,
            PRIMARY KEY (id),
            KEY ticket_id (ticket_id),
            KEY user_id (user_id),
            KEY created_at (created_at)
        ) {$charset_collate};";

        $purchase_codes_table = $wpdb->prefix . 'support_purchase_codes';
        $purchase_codes_sql = "CREATE TABLE {$purchase_codes_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            purchase_code varchar(64) NOT NULL,
            user_id bigint(20) unsigned NOT NULL,
            product varchar(50) NOT NULL,
            envato_username varchar(100) DEFAULT NULL,
            verified tinyint(1) NOT NULL DEFAULT 0,
            verified_at datetime DEFAULT NULL,
            verification_data text DEFAULT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY purchase_code (purchase_code),
            KEY user_id (user_id),
            KEY product (product),
            KEY verified (verified)
        ) {$charset_collate};";

        // Execute support system migrations
        dbDelta($support_users_sql);
        dbDelta($support_tickets_sql);
        dbDelta($support_replies_sql);
        dbDelta($purchase_codes_sql);

        // Verify tables were created/updated successfully
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $thresholds_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SHOW TABLES LIKE %s',
                $wpdb->esc_like($thresholds_table)
            )
        ) === $thresholds_table;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $events_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SHOW TABLES LIKE %s',
                $wpdb->esc_like($events_table)
            )
        ) === $events_table;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $support_users_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SHOW TABLES LIKE %s',
                $wpdb->esc_like($support_users_table)
            )
        ) === $support_users_table;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $support_tickets_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SHOW TABLES LIKE %s',
                $wpdb->esc_like($support_tickets_table)
            )
        ) === $support_tickets_table;

        if (! $thresholds_exists || ! $events_exists || ! $support_users_exists || ! $support_tickets_exists) {
            if (function_exists('do_action')) {
                do_action(
                    'woolsn_db_migration_error',
                    sprintf(
                        'Failed to create tables. Thresholds: %s, Events: %s, Support Users: %s, Support Tickets: %s',
                        $thresholds_exists ? 'OK' : 'FAILED',
                        $events_exists ? 'OK' : 'FAILED',
                        $support_users_exists ? 'OK' : 'FAILED',
                        $support_tickets_exists ? 'OK' : 'FAILED'
                    )
                );
            }
            return;
        }

        // Update version only if migration succeeded
        update_option('woolsn_db_version', self::DB_VERSION);
    }
}

