-- +goose Up
-- opportunity_document_chunks is the exact extraction provenance of a document: one row per extracted span
-- with its page, section label, the extracted text, a content hash, the extraction method, and a
-- confidence. Every scope item the model or a human later derives cites a chunk, so a requirement traces
-- back to the precise place in the solicitation it came from. Tenant data (the text is the tenant's
-- uploaded document content), so organization_id, forced RLS, and a composite index leading with
-- organization_id. Chunks are write once extraction output tied to a document.

CREATE TABLE opportunity_document_chunks (
    organization_id    uuid NOT NULL REFERENCES organizations(id),
    id                 uuid NOT NULL DEFAULT gen_random_uuid(),
    document_id        uuid NOT NULL,
    page_number        integer,                              -- 1 based page, nullable for a non paginated source
    section_label      text NOT NULL DEFAULT '',
    text_content       text NOT NULL DEFAULT '',
    content_hash       text NOT NULL DEFAULT '',
    extraction_method  text NOT NULL DEFAULT '',             -- for example pdf_text, ocr, table, heading
    confidence         real NOT NULL DEFAULT 0,              -- extraction confidence, never a scoring input
    created_at         timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id),
    FOREIGN KEY (organization_id, document_id) REFERENCES opportunity_documents (organization_id, id) ON DELETE CASCADE
);

-- Chunks are read per document in page then section order; the composite index leads with organization_id.
CREATE INDEX idx_opp_chunks_org_doc ON opportunity_document_chunks (organization_id, document_id, page_number);

ALTER TABLE opportunity_document_chunks ENABLE ROW LEVEL SECURITY;
ALTER TABLE opportunity_document_chunks FORCE  ROW LEVEL SECURITY;
CREATE POLICY opp_chunks_isolation ON opportunity_document_chunks
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- The extraction pipeline inserts chunks and the app reads them; chunks are not edited in place, so the
-- grant is SELECT, INSERT with no UPDATE or DELETE.
GRANT SELECT, INSERT ON opportunity_document_chunks TO missionmatch_app;

-- +goose Down
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
  SELECT count(*) INTO n FROM opportunity_document_chunks;
  IF n > 0 THEN
    RAISE EXCEPTION 'refusing to drop opportunity_document_chunks: % extraction rows present', n;
  END IF;
END $$;
-- +goose StatementEnd
DROP TABLE IF EXISTS opportunity_document_chunks;