<?php
/**
 * Database schema for the unified AILinkMaster link management system.
 *
 * Manages two core tables:
 * - wp_ailm_link_rules: Injection rules (what the plugin SHOULD inject)
 * - wp_ailm_links: Scanned link reality (what ACTUALLY exists in HTML)
 *
 * @since 2.0
 * @package AILinkMaster
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

class AILM_Link_DB {

    /**
     * Get the link rules table name.
     *
     * @return string Full table name with prefix.
     */
    public static function get_rules_table() {
        global $wpdb;
        return $wpdb->prefix . 'ailm_link_rules';
    }

    /**
     * Get the links (reality) table name.
     *
     * @return string Full table name with prefix.
     */
    public static function get_links_table() {
        global $wpdb;
        return $wpdb->prefix . 'ailm_links';
    }

    /**
     * Create both tables using dbDelta for safe upgrades.
     *
     * Called on plugin activation and version upgrade.
     *
     * @return void
     */
    public static function create_tables() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // Table 1: Link Rules (Intent — what links the plugin should inject)
        $rules_table = self::get_rules_table();
        $rules_sql   = "CREATE TABLE {$rules_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            donor_id bigint(20) unsigned NOT NULL,
            target_id bigint(20) unsigned NOT NULL DEFAULT 0,
            target_url varchar(2048) NOT NULL,
            anchor varchar(512) NOT NULL,
            occurrence tinyint(3) unsigned NOT NULL DEFAULT 1,
            source varchar(20) NOT NULL DEFAULT 'ai',
            status varchar(20) NOT NULL DEFAULT 'active',
            in_html tinyint(1) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY idx_donor (donor_id),
            KEY idx_target (target_id),
            KEY idx_status (status),
            KEY idx_in_html (in_html)
        ) {$charset_collate};";

        dbDelta( $rules_sql );

        // Ensure the in_html column exists for upgrades from pre-2.1 versions.
        // dbDelta does not always add new columns to existing tables reliably,
        // so we verify and add it explicitly if missing.
        self::ensure_in_html_column();
    }

    /**
     * Ensure the in_html column exists on the rules table.
     *
     * Called after dbDelta to handle upgrades from versions that did not have
     * the column. Safe to call multiple times.
     *
     * @return void
     */
    private static function ensure_in_html_column() {
        global $wpdb;
        $rules_table = self::get_rules_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $column_exists = $wpdb->get_results(
            $wpdb->prepare(
                'SHOW COLUMNS FROM %i LIKE %s',
                $rules_table,
                'in_html'
            )
        );

        if (empty($column_exists)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query(
                $wpdb->prepare(
                    'ALTER TABLE %i ADD COLUMN in_html tinyint(1) NOT NULL DEFAULT 0 AFTER status, ADD INDEX idx_in_html (in_html)', // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange
                    $rules_table
                )
            );
        }

        // Table 2: Links (Reality — what Scanner found in HTML)
        $links_table = self::get_links_table();
        $links_sql   = "CREATE TABLE {$links_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            donor_id bigint(20) unsigned NOT NULL,
            target_id bigint(20) unsigned NOT NULL DEFAULT 0,
            url varchar(2048) NOT NULL,
            anchor text,
            origin varchar(20) NOT NULL DEFAULT 'manual',
            link_type varchar(20) NOT NULL DEFAULT 'internal',
            position float NOT NULL DEFAULT 0.5,
            status_code int(5) NOT NULL DEFAULT 0,
            last_checked datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
            rule_id bigint(20) unsigned DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY idx_donor (donor_id),
            KEY idx_target (target_id),
            KEY idx_type_origin (link_type, origin),
            KEY idx_status (status_code),
            KEY idx_rule (rule_id)
        ) {$charset_collate};";

        dbDelta( $links_sql );

        // Table 3: Sync Queue (Phase 5 — async replication to SaaS).
        // Stores pending rule-change events that the cron worker pushes to
        // the SaaS server. Never blocks the main plugin flow.
        $queue_table = self::get_sync_queue_table();
        $queue_sql   = "CREATE TABLE {$queue_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            action varchar(20) NOT NULL DEFAULT 'upsert',
            rule_id bigint(20) unsigned NOT NULL DEFAULT 0,
            payload longtext,
            status varchar(20) NOT NULL DEFAULT 'pending',
            attempts tinyint(3) unsigned NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY idx_status (status),
            KEY idx_rule (rule_id)
        ) {$charset_collate};";

        dbDelta( $queue_sql );
    }

    /**
     * Get the sync queue table name.
     *
     * @return string Full table name with prefix.
     */
    public static function get_sync_queue_table() {
        global $wpdb;
        return $wpdb->prefix . 'ailm_sync_queue';
    }

    /**
     * Check if the new tables exist.
     *
     * @return bool True if both tables exist.
     */
    public static function tables_exist() {
        global $wpdb;

        $rules_table = self::get_rules_table();
        $links_table = self::get_links_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rules_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $rules_table )
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $links_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $links_table )
        );

        return ( $rules_exists === $rules_table && $links_exists === $links_table );
    }
}