<?php

declare(strict_types=1);

namespace CrocoAI\DataBridge\Infrastructure;

// The plugin owns and migrates these custom tables; trusted identifiers cannot be SQL value placeholders.
// Migration locking and schema upgrades must read and write current database state directly.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

final class Database
{
    public const VERSION = '1.8.0';
    private const MIGRATION_LOCK = 'crocoai_dbridge_migration_lock';

    public static function table(string $name): string
    {
        global $wpdb;
        return $wpdb->prefix . 'crocoai_dbridge_' . $name;
    }

    public static function activate(): void
    {
        IdentifierMigration::migrate();
        self::install();
        $role = get_role('administrator');
        $role?->add_cap(IdentifierMigration::CAPABILITY);
    }

    public static function maybeUpgrade(): void
    {
        IdentifierMigration::migrate();
        if (get_option('crocoai_dbridge_db_version') === self::VERSION || !self::acquireMigrationLock()) {
            return;
        }
        try {
            if (get_option('crocoai_dbridge_db_version') !== self::VERSION) {
                self::install();
            }
        } finally {
            delete_option(self::MIGRATION_LOCK);
        }
    }

    private static function acquireMigrationLock(): bool
    {
        $expires = time() + 300;
        if (add_option(self::MIGRATION_LOCK, $expires, '', false)) {
            return true;
        }
        $current = (int) get_option(self::MIGRATION_LOCK, 0);
        if ($current >= time()) {
            return false;
        }
        delete_option(self::MIGRATION_LOCK);
        return add_option(self::MIGRATION_LOCK, $expires, '', false);
    }

    private static function install(): void
    {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $wpdb->get_charset_collate();

        $sql = [];
        $sql[] = 'CREATE TABLE ' . self::table('sources') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            name varchar(190) NOT NULL,
            type varchar(20) NOT NULL,
            location text NULL,
            config longtext NULL,
            profile_id bigint unsigned NULL,
            schedule_seconds int unsigned NULL,
            enabled tinyint(1) NOT NULL DEFAULT 1,
            last_run_id bigint unsigned NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY enabled (enabled)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('profiles') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            name varchar(190) NOT NULL,
            version int unsigned NOT NULL DEFAULT 1,
            mapping longtext NOT NULL,
            ownership longtext NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('runs') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            source_id bigint unsigned NOT NULL,
            status varchar(20) NOT NULL,
            stage varchar(30) NOT NULL,
            full_feed tinyint(1) NOT NULL DEFAULT 1,
            stats longtext NULL,
            error longtext NULL,
            started_at datetime NOT NULL,
            finished_at datetime NULL,
            PRIMARY KEY  (id),
            KEY source_status (source_id,status)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('records') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            run_id bigint unsigned NOT NULL,
            source_id bigint unsigned NOT NULL,
            external_id varchar(190) NOT NULL,
            raw longtext NOT NULL,
            canonical longtext NULL,
            content_hash char(64) NULL,
            status varchar(20) NOT NULL,
            error longtext NULL,
            product_id bigint unsigned NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY run_external (run_id,external_id),
            KEY source_external (source_id,external_id),
            KEY run_status (run_id,status)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('links') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            source_id bigint unsigned NOT NULL,
            external_id varchar(190) NOT NULL,
            product_id bigint unsigned NOT NULL,
            canonical_hash char(64) NULL,
            last_seen_run bigint unsigned NULL,
            missing_count int unsigned NOT NULL DEFAULT 0,
            managed_snapshot longtext NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY source_external (source_id,external_id),
            KEY product_id (product_id)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('changes') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            run_id bigint unsigned NOT NULL,
            record_id bigint unsigned NULL,
            product_id bigint unsigned NULL,
            action varchar(20) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            priority tinyint unsigned NOT NULL DEFAULT 0,
            diff longtext NULL,
            previous_data longtext NULL,
            error text NULL,
            approved_by bigint unsigned NULL,
            approved_at datetime NULL,
            published_at datetime NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY run_status (run_id,status),
            KEY run_queue (run_id,status,priority,id),
            KEY product_id (product_id)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('aliases') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            source_id bigint unsigned NULL,
            alias_type varchar(30) NOT NULL,
            source_value varchar(255) NOT NULL,
            canonical_value varchar(255) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY source_type (source_id,alias_type),
            KEY status (status)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('jobs') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            job_type varchar(30) NOT NULL,
            run_id bigint unsigned NULL,
            status varchar(30) NOT NULL,
            stage varchar(30) NOT NULL,
            total int unsigned NOT NULL DEFAULT 0,
            processed int unsigned NOT NULL DEFAULT 0,
            succeeded int unsigned NOT NULL DEFAULT 0,
            failed int unsigned NOT NULL DEFAULT 0,
            media_total int unsigned NOT NULL DEFAULT 0,
            media_processed int unsigned NOT NULL DEFAULT 0,
            media_failed int unsigned NOT NULL DEFAULT 0,
            payload longtext NULL,
            error longtext NULL,
            created_by bigint unsigned NULL,
            started_at datetime NULL,
            finished_at datetime NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY status (status),
            KEY run_status (run_id,status)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('media_queue') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            job_id bigint unsigned NOT NULL,
            change_id bigint unsigned NOT NULL,
            product_id bigint unsigned NOT NULL,
            source_url text NOT NULL,
            url_hash char(64) NOT NULL,
            position int unsigned NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'pending',
            attempts int unsigned NOT NULL DEFAULT 0,
            available_at datetime NOT NULL,
            attachment_id bigint unsigned NULL,
            error text NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY change_position (change_id,position),
            KEY job_status_available (job_id,status,available_at),
            KEY product_id (product_id)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('remote_releases') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            client_key varchar(190) NOT NULL,
            release_key varchar(190) NOT NULL,
            source_id bigint unsigned NOT NULL,
            run_id bigint unsigned NOT NULL,
            expected_items int unsigned NOT NULL DEFAULT 0,
            received_items int unsigned NOT NULL DEFAULT 0,
            selected_release_protocol int unsigned NOT NULL DEFAULT 1,
            family_manifest longtext NULL,
            manifest_hash char(64) NULL,
            status varchar(20) NOT NULL DEFAULT 'receiving',
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY release_key (release_key),
            KEY status (status)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('attribute_ops') . " (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint unsigned NOT NULL DEFAULT 0,
            operations int unsigned NOT NULL DEFAULT 0,
            products_affected int unsigned NOT NULL DEFAULT 0,
            payload longtext NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY created_at (created_at)
        ) {$charset};";
        $sql[] = 'CREATE TABLE ' . self::table('media_assets') . " (
            url_hash char(64) NOT NULL,
            source_url text NOT NULL,
            attachment_id bigint unsigned NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (url_hash),
            KEY attachment_id (attachment_id)
        ) {$charset};";

        dbDelta($sql);
        $releases = self::table('remote_releases');
        $wpdb->query($wpdb->prepare(
            "UPDATE %i
             SET manifest_hash=SHA2(family_manifest, 256)
             WHERE (manifest_hash IS NULL OR manifest_hash='')
               AND family_manifest IS NOT NULL
               AND family_manifest<>''",
            $releases
        ));
        update_option('crocoai_dbridge_db_version', self::VERSION, false);
        if (get_option('crocoai_dbridge_settings', null) === null) {
            add_option('crocoai_dbridge_settings', [
                'missing_threshold' => 2,
                'retention_days' => 30,
                'notification_email' => (string) get_option('admin_email'),
                'publication_batch_size' => 5,
                'worker_time_budget' => 15,
                'worker_delay' => 10,
                'media_worker_delay' => 0,
                'lookup_defer_threshold' => 100,
                'lookup_batch_size' => 5,
                'lookup_worker_delay' => 15,
                'image_timeout' => 10,
                'image_max_bytes' => 5 * MB_IN_BYTES,
                'image_max_attempts' => 3,
                'image_batch_size' => 3,
            ], '', false);
        }
    }
}