<?php
if (!defined('ABSPATH')) { exit; }

/** Persistent same-site short-link storage. */
final class Calliope_SP_Short_Link_Schema {
    public const DB_VERSION = '1';
    public const DB_VERSION_OPTION = 'calliope_sp_short_links_db_version';
    public const UPGRADE_LOCK_OPTION = 'calliope_sp_short_links_db_upgrade_lock';
    public const TABLE_SUFFIX = 'calliope_sp_short_links';
    private const UPGRADE_LOCK_TTL = 120;

    public static function table_name(): string {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_SUFFIX;
    }

    public static function activate(bool $network_wide = false): void {
        if (is_multisite() && $network_wide) {
            $offset = 0;
            $batch = 100;
            do {
                $ids = get_sites(['fields'=>'ids','number'=>$batch,'offset'=>$offset,'orderby'=>'id','order'=>'ASC']);
                foreach ((array) $ids as $site_id) {
                    switch_to_blog((int) $site_id);
                    try { self::install_schema(); } finally { restore_current_blog(); }
                }
                $count = is_array($ids) ? count($ids) : 0;
                $offset += $count;
            } while ($count === $batch);
            return;
        }
        self::install_schema();
    }

    public static function initialize_new_site($new_site): void {
        if (!is_multisite()) { return; }
        $site_id = $new_site instanceof WP_Site ? (int) $new_site->blog_id : absint($new_site);
        if ($site_id <= 0) { return; }
        if (!function_exists('is_plugin_active_for_network')) {
            require_once ABSPATH . 'wp-admin/includes/plugin.php';
        }
        if (!is_plugin_active_for_network('calliope-social-publisher/calliope-social-publisher.php')) { return; }
        switch_to_blog($site_id);
        try { self::install_schema(); } finally { restore_current_blog(); }
    }

    public static function maybe_upgrade(): void {
        if ((string) get_option(self::DB_VERSION_OPTION, '') === self::DB_VERSION) { return; }
        $now = time();
        $lock = get_option(self::UPGRADE_LOCK_OPTION, []);
        $created = is_array($lock) ? absint($lock['created_at'] ?? 0) : 0;
        if ($created > 0 && $created >= ($now - self::UPGRADE_LOCK_TTL)) { return; }
        delete_option(self::UPGRADE_LOCK_OPTION);
        if (!add_option(self::UPGRADE_LOCK_OPTION, ['created_at'=>$now], '', 'no')) { return; }
        try {
            if ((string) get_option(self::DB_VERSION_OPTION, '') !== self::DB_VERSION) { self::install_schema(); }
        } finally {
            delete_option(self::UPGRADE_LOCK_OPTION);
        }
    }

    private static function install_schema(): void {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $table = self::table_name();
        $charset = $wpdb->get_charset_collate();
        $sql = "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            uuid char(36) NOT NULL,
            code varchar(10) NOT NULL,
            target_url text NOT NULL,
            target_hash char(64) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'active',
            created_at_utc datetime NOT NULL,
            updated_at_utc datetime NOT NULL,
            last_used_at_utc datetime NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY uuid (uuid),
            UNIQUE KEY code (code),
            UNIQUE KEY target_hash (target_hash),
            KEY status (status)
        ) {$charset};";
        dbDelta($sql);
        update_option(self::DB_VERSION_OPTION, self::DB_VERSION, false);
    }
}
