<?php

declare(strict_types=1);
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange -- This file performs trusted plugin installation, upgrade, and uninstall schema operations against known WordPress tables.
defined('ABSPATH') || exit;

class DSProofwp_DB
{

    public static function activate(): void
    {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        self::maybe_cleanup_old_tables();
        self::create_or_update_tables();
    }

    private static function maybe_cleanup_old_tables(): void
    {
        global $wpdb;
        $p = $wpdb->prefix;
        $table_name = $p . 'dsproofwp_form_settings';

        // Check if table exists
        if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") !== $table_name) {
            return;
        }

        $installed_version = get_option('dsproofwp_new_schema_version', '1.0.0');
        $is_old_version = version_compare($installed_version, '1.2.0', '<');

        // Check for deprecated column 'text_required' (part of the old schema)
        $has_deprecated_column = (bool) $wpdb->get_results("SHOW COLUMNS FROM `$table_name` LIKE 'text_required'");

        if ($is_old_version || $has_deprecated_column) {
            $wpdb->query("DROP TABLE IF EXISTS $table_name");
        }
    }

    /**
     * Create or update all plugin-owned tables via dbDelta.
     */
    public static function create_or_update_tables(): void
    {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        global $wpdb;

        $charset = $wpdb->get_charset_collate();
        $p       = $wpdb->prefix;

        // ------------------------------------------------------------------
        // REVIEW_MEDIA — one review can have many image / video attachments
        // ------------------------------------------------------------------
        $sql_review_media = "CREATE TABLE {$p}dsproofwp_review_media (
            id            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            review_id     BIGINT UNSIGNED  NOT NULL,
            attachment_id BIGINT UNSIGNED  DEFAULT NULL,
            media_type    VARCHAR(10)      NOT NULL COMMENT 'image | video',
            url           VARCHAR(500)     NOT NULL,
            created_at    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_review_id    (review_id)
        ) $charset;";

        // ------------------------------------------------------------------
        // WIDGETS — each widget is a named, typed display container
        // ------------------------------------------------------------------
        $sql_widgets = "CREATE TABLE {$p}dsproofwp_widgets (
            id               BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            title            VARCHAR(255)     NOT NULL,
            widget_type      VARCHAR(50)      NOT NULL,
            settings         LONGTEXT         DEFAULT NULL,
            review_selection VARCHAR(50)      NOT NULL DEFAULT 'custom',
            status           VARCHAR(20)      NOT NULL DEFAULT 'active',
            created_at       DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at       DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_status (status)
        ) $charset;";

        // ------------------------------------------------------------------
        // WIDGET_REVIEWS — pivot: one widget ↔ many reviews (and vice-versa)
        // ------------------------------------------------------------------
        $sql_widget_reviews = "CREATE TABLE {$p}dsproofwp_widget_reviews (
            id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            widget_id  BIGINT UNSIGNED  NOT NULL,
            review_id  BIGINT UNSIGNED  NOT NULL,
            sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            added_at   DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_widget_review  (widget_id, review_id),
            KEY idx_widget_order         (widget_id, sort_order),
            KEY idx_review_widgets       (review_id)
        ) $charset;";

        // ------------------------------------------------------------------
        // FORM_SETTINGS — singleton row (id always = 1)
        // ------------------------------------------------------------------
        $sql_form_settings = "CREATE TABLE {$p}dsproofwp_form_settings (
            id                   TINYINT UNSIGNED NOT NULL DEFAULT 1,
            form_settings        LONGTEXT         DEFAULT NULL,
            created_at           DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at           DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset;";

        // ------------------------------------------------------------------
        // REVIEW_FIELDS — stores source and is_visual, keyed by comment_ID
        // ------------------------------------------------------------------
        $sql_review_fields = "CREATE TABLE {$p}dsproofwp_review_fields (
            comment_id BIGINT UNSIGNED  NOT NULL,
            source     VARCHAR(100)     DEFAULT NULL,
            is_visual  TINYINT(1)       NOT NULL DEFAULT 0,
            PRIMARY KEY (comment_id),
            KEY idx_source    (source),
            KEY idx_is_visual (is_visual),
            KEY idx_source_visual (source, is_visual)
        ) $charset;";

        /**
         * REVIEW_REQUEST_SETTINGS
         */
        $table_settings = $p . 'dsproofwp_review_request_settings';
        $sql_settings = "CREATE TABLE {$table_settings} (
            id INT NOT NULL AUTO_INCREMENT,
            first_request_enabled TINYINT(1) NOT NULL DEFAULT 0,
            first_request_delay_days INT NOT NULL,
            followup_enabled TINYINT(1) NOT NULL DEFAULT 0,
            followup_delay_days INT NOT NULL,
            followup_max_count INT DEFAULT 1,
            PRIMARY KEY (id)
        ) $charset;";

        /**
         * REVIEW_REQUEST_EMAIL_TEMPLATES
         */
        $table_templates = $p . 'dsproofwp_review_request_email_templates';
        $sql_templates = "CREATE TABLE {$table_templates} (
            id BIGINT NOT NULL AUTO_INCREMENT,
            type VARCHAR(50) NOT NULL,
            subject TEXT,
            body LONGTEXT,
            button_text VARCHAR(255),
            PRIMARY KEY (id)
        ) $charset;";

        /**
         * REVIEW_REQUESTS (Core Table)
         */
        $table_requests = $p . 'dsproofwp_review_requests';
        $sql_requests = "CREATE TABLE {$table_requests} (
            id BIGINT NOT NULL AUTO_INCREMENT,
            order_id BIGINT NOT NULL,
            token VARCHAR(64) DEFAULT NULL,
            customer_email VARCHAR(255),
            customer_name VARCHAR(255),
            scheduled_at DATETIME,
            sent_at DATETIME NULL,
            type VARCHAR(50) NOT NULL,
            status VARCHAR(50) DEFAULT 'scheduled',
            reminder_count INT DEFAULT 0,
            PRIMARY KEY (id),
            UNIQUE KEY uq_token (token),
            KEY idx_status (status),
            KEY idx_schedule_at (scheduled_at)
        ) $charset;";

        // Run all table creations / updates
        dbDelta($sql_review_fields);
        dbDelta($sql_review_media);
        dbDelta($sql_widgets);
        dbDelta($sql_widget_reviews);
        dbDelta($sql_form_settings);
        dbDelta($sql_settings);
        dbDelta($sql_templates);
        dbDelta($sql_requests);

        // Initialize the form_settings singleton row if it does not exist yet
        $default_settings = [
            'enable_anonymous'            => false,
            'anonymous_username'          => false,
            'consent_enabled'             => false,
            'consent_text'                => 'I agree to the terms and conditions.',
            'image_upload_enabled'        => true,
            'video_upload_enabled'        => true,
            'image_max_size_mb'           => 5,
            'video_max_size_mb'           => 50,
            'image_max_count'             => 3,
            'video_max_count'             => 1,
            'allowed_image_types'         => 'jpg,jpeg,png,webp',
            'allowed_video_types'         => 'mp4,mov,webm',
            'recaptcha_enabled'           => false,
            'show_name_field'             => true,
            'name_field_required'         => true,
            'show_email_field'            => true,
            'email_field_required'        => true,
        ];

        $wpdb->query(
            $wpdb->prepare(
                "INSERT IGNORE INTO {$p}dsproofwp_form_settings (id, form_settings) VALUES (1, %s)",
                wp_json_encode($default_settings)
            )
        );

        self::seed_review_reminder_data();
        self::run_migrations();

        update_option('dsproofwp_new_schema_version', '1.4.0');
    }

    private static function run_migrations(): void
    {
        global $wpdb;
        $installed_version = get_option('dsproofwp_new_schema_version', '1.0.0');

        if (version_compare($installed_version, '1.4.0', '<')) {
            $p = $wpdb->prefix;
            $table_requests = $p . 'dsproofwp_review_requests';
            $table_settings = $p . 'dsproofwp_review_request_settings';

            // 1. Add token column if it doesn't exist
            $has_token = $wpdb->get_results("SHOW COLUMNS FROM {$table_requests} LIKE 'token'");
            if (empty($has_token)) {
                $wpdb->query("ALTER TABLE {$table_requests} ADD COLUMN token VARCHAR(64) DEFAULT NULL AFTER order_id");
                $wpdb->query("ALTER TABLE {$table_requests} ADD UNIQUE KEY uq_token (token)");
            }

            // 2. Add reminder_count column if it doesn't exist
            $has_reminder_count = $wpdb->get_results("SHOW COLUMNS FROM {$table_requests} LIKE 'reminder_count'");
            if (empty($has_reminder_count)) {
                $wpdb->query("ALTER TABLE {$table_requests} ADD COLUMN reminder_count INT DEFAULT 0 AFTER type");
            }

            // 3. Add followup_max_count column to settings if it doesn't exist
            $has_max_count = $wpdb->get_results("SHOW COLUMNS FROM {$table_settings} LIKE 'followup_max_count'");
            if (empty($has_max_count)) {
                $wpdb->query("ALTER TABLE {$table_settings} ADD COLUMN followup_max_count INT DEFAULT 1 AFTER followup_delay_days");
            }

            // 4. Drop product_id column if it exists
            $has_product_id = $wpdb->get_results("SHOW COLUMNS FROM {$table_requests} LIKE 'product_id'");
            if (!empty($has_product_id)) {
                $wpdb->query("ALTER TABLE {$table_requests} DROP COLUMN product_id");
            }

            // 5. Cancel all scheduled reminder requests
            $wpdb->query("UPDATE {$table_requests} SET status = 'cancelled' WHERE status = 'scheduled'");
        }
    }

    private static function seed_review_reminder_data(): void
    {
        global $wpdb;
        $p = $wpdb->prefix;

        /**
         * 1. Seed Default Settings
         */
        $table_settings = $p . 'dsproofwp_review_request_settings';
        $settings_count = $wpdb->get_var("SELECT COUNT(*) FROM $table_settings");

        if ('0' == $settings_count) {
            $wpdb->insert($table_settings, [
                'first_request_enabled'    => 0,
                'first_request_delay_days' => 1,
                'followup_enabled'         => 0,
                'followup_delay_days'      => 3,
                'followup_max_count'       => 1,
            ]);
        }

        /**
         * 2. Seed Default Email Templates
         */
        $table_templates = $p . 'dsproofwp_review_request_email_templates';
        $template_count = $wpdb->get_var("SELECT COUNT(*) FROM $table_templates");

        if ('0' == $template_count) {
            // Default First Request Template
            $wpdb->insert($table_templates, [
                'type'        => 'first',
                'subject'     => 'How was your shopping experience? We appreciate your feedback.',
                'body'        => '<p>Hey [customer_name],</p><p>How Do You Feel About Our Service? Your Feedback Will Be Greatly Appreciated.</p><p>Thank You.</p>',
                'button_text' => 'Write a Review',
            ]);

            // Default Follow-up Template
            $wpdb->insert($table_templates, [
                'type'        => 'followup',
                'subject'     => 'How was your shopping experience? We appreciate your feedback.',
                'body'        => '<p>Hey [customer_name],</p>
									<p>How Do You Feel About Our Service? Your Feedback Will Be Greatly Appreciated :)</p>
									<p>Thank You.</p>',
                'button_text' => 'Write a Review',
            ]);
        }
    }

    /**
     * Drop all plugin-owned tables and remove added columns on uninstall.
     * Called from uninstall.php — never on deactivation.
     */
    public static function uninstall()
    {
        global $wpdb;

        $p = $wpdb->prefix;

        // Drop plugin tables (order respects FK-style dependencies)
        $tables = [
            "{$p}dsproofwp_review_fields",
            "{$p}dsproofwp_widget_reviews",
            "{$p}dsproofwp_review_media",
            "{$p}dsproofwp_widgets",
            "{$p}dsproofwp_form_settings",
            "{$p}dsproofwp_review_request_settings",
            "{$p}dsproofwp_review_request_email_templates",
            "{$p}dsproofwp_review_requests",
        ];

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