<?php

namespace PloverCRM\Modules\ContactEnrichment\Database;

/**
 * Database schema for background post-create jobs.
 */
class PostCreateJobSchema
{
    public const TABLE_NAME = 'plover_crm_post_create_jobs';
    private const SCHEMA_VERSION_OPTION = 'plover_crm_post_create_jobs_schema_version';
    private const SCHEMA_VERSION = '2';
    private const LEGACY_PENDING_JOBS_OPTION = 'plover_crm_pending_enrichment_jobs';
    private const LEGACY_ACTION_HOOK = 'plover_crm_run_contact_enrichment_job';

    public static function getTableName(): string
    {
        global $wpdb;

        return $wpdb->prefix . self::TABLE_NAME;
    }

    public static function maybeCreateTable(): void
    {
        global $wpdb;

        $storedVersion = (string) get_option(self::SCHEMA_VERSION_OPTION, '0');
        $tableName = self::getTableName();
        $charsetCollate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $tableName (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            job_id char(36) NOT NULL,
            job_key varchar(191) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            dispatch_token varchar(64) NOT NULL,
            payload_json longtext NOT NULL,
            rerun_requested tinyint(1) NOT NULL DEFAULT 0,
            attempt_count int(10) unsigned NOT NULL DEFAULT 0,
            last_error text DEFAULT NULL,
            queued_at datetime DEFAULT NULL,
            started_at datetime DEFAULT NULL,
            completed_at datetime DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY job_id (job_id),
            UNIQUE KEY job_key (job_key),
            KEY status (status),
            KEY updated_at (updated_at)
        ) $charsetCollate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);

        if (version_compare($storedVersion, self::SCHEMA_VERSION, '<')) {
            self::cleanupLegacyScheduler();
        }

        update_option(self::SCHEMA_VERSION_OPTION, self::SCHEMA_VERSION, false);
    }

    /**
     * Remove the deprecated option and Action Scheduler rows from the old
     * delayed enrichment implementation.
     */
    private static function cleanupLegacyScheduler(): void
    {
        delete_option(self::LEGACY_PENDING_JOBS_OPTION);
        wp_clear_scheduled_hook(self::LEGACY_ACTION_HOOK);
        self::deleteLegacyActionSchedulerRows();
    }

    private static function deleteLegacyActionSchedulerRows(): void
    {
        global $wpdb;

        $actionsTable = $wpdb->prefix . 'actionscheduler_actions';
        $logsTable = $wpdb->prefix . 'actionscheduler_logs';

        if (!self::tableExists($actionsTable)) {
            return;
        }

        $actionIds = $wpdb->get_col($wpdb->prepare(
            "SELECT action_id FROM $actionsTable WHERE hook = %s",
            self::LEGACY_ACTION_HOOK
        ));

        if (!is_array($actionIds) || empty($actionIds)) {
            return;
        }

        $actionIds = array_map('intval', $actionIds);
        $placeholders = implode(', ', array_fill(0, count($actionIds), '%d'));

        if (self::tableExists($logsTable)) {
            $wpdb->query($wpdb->prepare(
                "DELETE FROM $logsTable WHERE action_id IN ($placeholders)",
                ...$actionIds
            ));
        }

        $wpdb->query($wpdb->prepare(
            "DELETE FROM $actionsTable WHERE action_id IN ($placeholders)",
            ...$actionIds
        ));
    }

    private static function tableExists(string $tableName): bool
    {
        global $wpdb;

        return $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tableName)) === $tableName;
    }
}
