<?php
defined('ABSPATH') || exit;

class FixLinks_DB {

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

        dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}fixlinks_broken (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            broken_url TEXT NOT NULL,
            source_page TEXT NOT NULL,
            anchor_text VARCHAR(500) DEFAULT '',
            http_status INT DEFAULT 0,
            ai_suggestion TEXT DEFAULT NULL,
            confidence VARCHAR(10) DEFAULT NULL,
            status ENUM('pending','applied','ignored') DEFAULT 'pending',
            found_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            applied_at DATETIME DEFAULT NULL
        ) {$c};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}fixlinks_redirects (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            from_path VARCHAR(1000) NOT NULL,
            to_url TEXT NOT NULL,
            link_id BIGINT UNSIGNED DEFAULT NULL,
            hit_count INT UNSIGNED DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY idx_from_path (from_path(191))
        ) {$c};");

        dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}fixlinks_crawl_queue (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            url TEXT NOT NULL,
            source_page TEXT NOT NULL,
            anchor_text VARCHAR(500) DEFAULT '',
            status ENUM('pending','done','broken') DEFAULT 'pending',
            http_status INT DEFAULT NULL,
            added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_status (status)
        ) {$c};");

        update_option('fixlinks_db_version', FIXLINKS_VERSION);
    }

    public static function get_broken_links(?string $status = null, int $limit = 50, int $offset = 0): array {
        global $wpdb;
        $t = $wpdb->prefix . 'fixlinks_broken';
        if ($status) {
            return $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM {$t} WHERE status=%s ORDER BY found_at DESC LIMIT %d OFFSET %d",
                $status, $limit, $offset
            ));
        }
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$t} ORDER BY found_at DESC LIMIT %d OFFSET %d",
            $limit, $offset
        ));
    }

    public static function count_broken(?string $status = null): int {
        global $wpdb;
        $t = $wpdb->prefix . 'fixlinks_broken';
        if ($status) {
            return (int)$wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$t} WHERE status=%s", $status));
        }
        return (int)$wpdb->get_var("SELECT COUNT(*) FROM {$t}");
    }

    public static function upsert_link(string $bu, string $sp, string $at, int $hs): int {
        global $wpdb;
        $t  = $wpdb->prefix . 'fixlinks_broken';
        $ex = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$t} WHERE broken_url=%s AND source_page=%s", $bu, $sp));
        if ($ex) {
            $wpdb->update($t, ['http_status' => $hs, 'found_at' => current_time('mysql')], ['id' => $ex]);
            return (int)$ex;
        }
        $wpdb->insert($t, ['broken_url' => $bu, 'source_page' => $sp, 'anchor_text' => $at, 'http_status' => $hs]);
        return (int)$wpdb->insert_id;
    }

    public static function update_suggestion(int $id, ?string $s, string $c): void {
        global $wpdb;
        $wpdb->update($wpdb->prefix . 'fixlinks_broken', ['ai_suggestion' => $s, 'confidence' => $c], ['id' => $id]);
    }

    public static function mark_applied(int $id): void {
        global $wpdb;
        $wpdb->update($wpdb->prefix . 'fixlinks_broken', ['status' => 'applied', 'applied_at' => current_time('mysql')], ['id' => $id]);
    }

    public static function get_all_redirects(): array {
        global $wpdb;
        return $wpdb->get_results("SELECT * FROM {$wpdb->prefix}fixlinks_redirects ORDER BY created_at DESC") ?: [];
    }

    public static function clear_crawl_queue(): void {
        global $wpdb;
        $wpdb->query("TRUNCATE TABLE {$wpdb->prefix}fixlinks_crawl_queue");
    }

    public static function insert_queue_batch(array $items): void {
        global $wpdb;
        if (empty($items)) return;
        $t    = $wpdb->prefix . 'fixlinks_crawl_queue';
        $ph   = [];
        $vals = [];
        foreach ($items as $i) {
            $ph[]   = '(%s,%s,%s)';
            $vals[] = $i['url'];
            $vals[] = $i['source_page'];
            $vals[] = $i['anchor_text'];
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $wpdb->query($wpdb->prepare(
            "INSERT IGNORE INTO {$t} (url,source_page,anchor_text) VALUES " . implode(',', $ph),
            $vals
        ));
    }

    public static function get_pending_queue(int $limit = 15): array {
        global $wpdb;
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}fixlinks_crawl_queue WHERE status='pending' LIMIT %d",
            $limit
        )) ?: [];
    }

    public static function count_queue(?string $status = null): int {
        global $wpdb;
        $t = $wpdb->prefix . 'fixlinks_crawl_queue';
        if ($status) {
            return (int)$wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$t} WHERE status=%s", $status));
        }
        return (int)$wpdb->get_var("SELECT COUNT(*) FROM {$t}");
    }

    public static function mark_queue_item(int $id, string $status, int $hs): void {
        global $wpdb;
        $wpdb->update($wpdb->prefix . 'fixlinks_crawl_queue', ['status' => $status, 'http_status' => $hs], ['id' => $id]);
    }

    public static function drop_tables(): void {
        global $wpdb;
        foreach (['fixlinks_broken', 'fixlinks_redirects', 'fixlinks_crawl_queue'] as $t) {
            $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}{$t}");
        }
    }
}