-- docs/migrations/2026-04-24-search-rebuild.sql
-- Safe to run on live site. All changes are additive.

-- 1. New table: drives delta mode (file-level checksum skip)
CREATE TABLE IF NOT EXISTS site_files_index (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_path       VARCHAR(500)  NOT NULL,
    checksum        CHAR(40)      NOT NULL,
    last_indexed_at DATETIME      NOT NULL,
    UNIQUE KEY uq_file_path (file_path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Add link_key (stable identity for upsert safety) and is_broken
ALTER TABLE site_links_index
    ADD COLUMN link_key  CHAR(40)   NOT NULL DEFAULT '' AFTER id,
    ADD COLUMN is_broken TINYINT(1) NOT NULL DEFAULT 0  AFTER context;

-- 3. Populate link_key from existing rows
UPDATE site_links_index
SET link_key = SHA1(CONCAT(page_url, '|', link_url))
WHERE link_key = '';

-- 4. Deduplicate so UNIQUE constraint does not fail
DELETE s1 FROM site_links_index s1
INNER JOIN site_links_index s2
    ON s1.link_key = s2.link_key AND s1.id > s2.id;

-- 5. Add constraints (run AFTER populating link_key)
ALTER TABLE site_links_index
    ADD UNIQUE KEY uq_link_key (link_key),
    ADD INDEX      idx_is_broken (is_broken);

-- 5b. Index on page_url — upsertLinks() DELETEs by page_url on every crawl
ALTER TABLE site_links_index
    ADD INDEX idx_page_url (page_url(255));

-- 6. FULLTEXT index for search (replaces LIKE scoring)
ALTER TABLE site_links_index
    ADD FULLTEXT INDEX ft_search (link_text, page_title, context);

-- 7. UNIQUE on site_pages_index.url — enables ON DUPLICATE KEY UPDATE upsert
ALTER TABLE site_pages_index
    ADD UNIQUE KEY uq_url (url);
