-- 024_ocr_words.sql
-- Documentation only — applied via internal/storage/ocr_words.go
-- (Store.initOCRWordsSchema), wired into Store.initSchema in documents.go.
--
-- Phase 2 of the OCR text-highlight/overlay feature (Phase 1: internal/ocr
-- gained Result.Words []WordBox, coordinates already mapped back into the
-- original uploaded file's coordinate space — see internal/ocr/coords.go).
-- This migration adds the table that persists those word boxes per
-- document, so a future Phase 3 read endpoint can serve them for a
-- search-highlight/overlay UI without re-running OCR.
--
-- No tenant_id column: access is always mediated through document_id (join
-- or verify against documents.tenant_id), never queried directly by tenant.
--
-- ON DELETE CASCADE on document_id: word boxes are a derived index over a
-- document's OCR text/original file, not a GoBD document themselves, so
-- cascading on hard-delete (trash workflow, see 007_trash.sql) is correct
-- and does not affect WORM retention of the document's own storage_path/
-- content_hash.
--
-- Every OCR (re)run (upload async job, manual reprocess endpoint, and the
-- `documents reprocess-all` CLI, which shares ReprocessDocument with the
-- endpoint) deletes this document's existing rows and re-inserts the fresh
-- set (storage.ReplaceOCRWords), so re-OCR never accumulates duplicates.

CREATE TABLE IF NOT EXISTS ocr_words (
    id          BIGSERIAL PRIMARY KEY,
    document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    page        INTEGER NOT NULL DEFAULT 1,
    block       INTEGER NOT NULL DEFAULT 0,
    par         INTEGER NOT NULL DEFAULT 0,
    line        INTEGER NOT NULL DEFAULT 0,
    word_text   TEXT NOT NULL,
    "left"      INTEGER NOT NULL,
    top         INTEGER NOT NULL,
    width       INTEGER NOT NULL,
    height      INTEGER NOT NULL,
    confidence  DOUBLE PRECISION NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_ocr_words_document ON ocr_words (document_id);

-- Plain btree, not pg_trgm/GIN: initSchema cannot assume CREATE EXTENSION
-- privileges on the live server. Revisit if Phase 3 needs fuzzy/substring
-- prefiltering (would require enabling pg_trgm out-of-band first).
CREATE INDEX IF NOT EXISTS idx_ocr_words_word_text ON ocr_words (word_text);
