<?php
namespace WebshoptigerCatalogSync;

defined( 'ABSPATH' ) || exit;

class Installer {

    public static function activate(): void {
        if ( version_compare( PHP_VERSION, '7.4', '<' ) ) {
            deactivate_plugins( plugin_basename( WEBSHOPTIGERCATALOGSYNC_FILE ) );
            wp_die( esc_html( WEBSHOPTIGERCATALOGSYNC_PLUGIN_NAME ) . ' requires PHP 7.4 or higher. Your server is running PHP ' . PHP_VERSION . '.' );
        }
        self::create_tables();
        self::upgrade_tables();
        self::set_defaults();
        Scheduler::schedule();
    }

    public static function deactivate(): void {
        Scheduler::unschedule();
    }

    public static function create_tables(): void {
        global $wpdb;
        $charset = $wpdb->get_charset_collate();

        $sql_sites = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}webshoptigercatalogsync_sites (
            id                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            label                VARCHAR(255)    NOT NULL,
            site_url             VARCHAR(500)    NOT NULL,
            api_key              VARCHAR(255)    NOT NULL,
            api_secret           VARCHAR(255)    NOT NULL,
            categories           LONGTEXT,
            enabled              TINYINT(1)      NOT NULL DEFAULT 1,
            sync_description     TINYINT(1)      NOT NULL DEFAULT 1,
            sync_interval        VARCHAR(20)     NOT NULL DEFAULT 'global',
            next_sync_at         DATETIME        DEFAULT NULL,
            price_adjustment     VARCHAR(20)     NOT NULL DEFAULT 'none',
            price_value          DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
            sync_fields          LONGTEXT        DEFAULT NULL,
            report_email         VARCHAR(255)    DEFAULT NULL,
            created_at           DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset;";

        $sql_desc = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}webshoptigercatalogsync_descriptions (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id      BIGINT UNSIGNED NOT NULL,
            source_hash     CHAR(32)        NOT NULL,
            rewritten       LONGTEXT        NOT NULL,
            model           VARCHAR(100)    NOT NULL DEFAULT 'gpt-4o-mini',
            generated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_product (product_id),
            INDEX idx_hash (source_hash)
        ) $charset;";

        $sql_log = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}webshoptigercatalogsync_log (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            site_id       BIGINT UNSIGNED NOT NULL,
            product_id    BIGINT UNSIGNED NOT NULL,
            remote_id     BIGINT UNSIGNED,
            action        VARCHAR(50)     NOT NULL,
            status        VARCHAR(50)     NOT NULL,
            message       TEXT,
            synced_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            INDEX idx_site_product (site_id, product_id),
            INDEX idx_synced_at (synced_at)
        ) $charset;";

        $sql_queue = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}webshoptigercatalogsync_queue (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            job_id        CHAR(36)        NOT NULL,
            site_id       BIGINT UNSIGNED NOT NULL,
            product_id    BIGINT UNSIGNED NOT NULL,
            status        VARCHAR(20)     NOT NULL DEFAULT 'pending',
            attempts      TINYINT         NOT NULL DEFAULT 0,
            error_message TEXT,
            queued_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            processed_at  DATETIME,
            PRIMARY KEY (id),
            INDEX idx_job_status (job_id, status),
            INDEX idx_site (site_id),
            INDEX idx_pending (status, queued_at)
        ) $charset;";

        $sql_jobs = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}webshoptigercatalogsync_jobs (
            job_id        CHAR(36)        NOT NULL,
            site_id       BIGINT UNSIGNED NOT NULL DEFAULT 0,
            total         INT UNSIGNED    NOT NULL DEFAULT 0,
            done          INT UNSIGNED    NOT NULL DEFAULT 0,
            errors        INT UNSIGNED    NOT NULL DEFAULT 0,
            status        VARCHAR(20)     NOT NULL DEFAULT 'running',
            started_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            finished_at   DATETIME,
            PRIMARY KEY (job_id),
            INDEX idx_status (status)
        ) $charset;";



        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        dbDelta( $sql_sites );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        dbDelta( $sql_desc );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        dbDelta( $sql_log );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        dbDelta( $sql_queue );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        dbDelta( $sql_jobs );
    }

    /**
     * Add new columns to existing installs via ALTER TABLE.
     * dbDelta does not reliably add new columns to existing tables.
     */
    public static function upgrade_tables(): void {
        global $wpdb;
        $table = $wpdb->prefix . 'webshoptigercatalogsync_sites';

        $columns = [
            'sync_description' => "TINYINT(1) NOT NULL DEFAULT 1",
            'sync_interval'    => "VARCHAR(20) NOT NULL DEFAULT 'global'",
            'next_sync_at'     => "DATETIME DEFAULT NULL",
            'price_adjustment' => "VARCHAR(20) NOT NULL DEFAULT 'none'",
            'price_value'      => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
            'sync_fields'      => "LONGTEXT DEFAULT NULL",
        ];

        // phpcs:ignore // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $existing = $wpdb->get_col( "DESCRIBE {$table}", 0 );

        foreach ( $columns as $col => $definition ) {
            if ( ! in_array( $col, $existing, true ) ) {
                // phpcs:ignore // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->query( "ALTER TABLE {$table} ADD COLUMN {$col} {$definition}" );
            }
        }
    }

    private static function set_defaults(): void {
        $defaults = [
            'webshoptigercatalogsync_openai_key'            => '',
            'webshoptigercatalogsync_openai_prompt'         => 'Rewrite the following product description in English. Keep all relevant product information but use unique, sales-oriented copy. Only return the rewritten description, with no extra explanation.',
            'webshoptigercatalogsync_interval'              => 'hourly',
            'webshoptigercatalogsync_rewrite_descriptions'  => '0',
            'webshoptigercatalogsync_db_version'            => WEBSHOPTIGERCATALOGSYNC_VERSION,
            'webshoptigercatalogsync_batch_size'            => '10',
            'webshoptigercatalogsync_report_email'          => get_option( 'admin_email', '' ),
            'webshoptigercatalogsync_report_frequency'      => 'daily',
        ];
        foreach ( $defaults as $key => $value ) {
            if ( get_option( $key ) === false ) {
                add_option( $key, $value );
            }
        }
    }
}
