-- +goose Up
-- opportunity_documents is the solicitation package for a tracked opportunity: the solicitation, its
-- amendments, the statement of work, pricing templates, evaluation criteria, and any attachment. The bytes
-- live in object storage keyed by storage_key; this row is the metadata and lifecycle. sha256 gives
-- duplicate detection within an opportunity, and extraction_status tracks the text extraction pipeline
-- (pending, running, done, failed, where failed is retryable). The malware scan integration point sits
-- between the upload and extraction_status pending. Tenant data, so organization_id, forced RLS, a
-- composite index leading with organization_id, composite foreign keys, and UNIQUE (organization_id, id)
-- as a composite FK target for the chunks. A superseded document keeps is_current false rather than being
-- deleted, so the package history is retained.

CREATE TABLE opportunity_documents (
    organization_id        uuid NOT NULL REFERENCES organizations(id),
    id                     uuid NOT NULL DEFAULT gen_random_uuid(),
    opportunity_id         uuid NOT NULL,
    opportunity_version_id uuid,                                -- the version this document belongs to, nullable
    document_type          text NOT NULL DEFAULT 'other'
                             CHECK (document_type IN ('solicitation','amendment','sow','pws','soo','labor_categories',
                               'pricing_template','evaluation_criteria','security','q_and_a','past_performance',
                               'subcontract_scope','customer_scope','attachment','other')),
    file_name              text NOT NULL DEFAULT '',
    media_type             text NOT NULL DEFAULT '',
    storage_key            text NOT NULL DEFAULT '',            -- object storage key for the retained bytes
    size_bytes             bigint NOT NULL DEFAULT 0,
    sha256                 text NOT NULL DEFAULT '',            -- integrity stamp and duplicate detection key
    source_url             text NOT NULL DEFAULT '',
    source_type            text NOT NULL DEFAULT ''
                             CHECK (source_type IN ('', 'upload','sam_attachment','url_import','manual')),
    uploaded_by_user_id    uuid,
    extraction_status      text NOT NULL DEFAULT 'pending'
                             CHECK (extraction_status IN ('pending','running','done','failed')),
    extraction_error_code  text NOT NULL DEFAULT '',
    supersedes_document_id uuid,                                -- the prior document this one replaces, nullable
    is_current             boolean NOT NULL DEFAULT true,
    created_at             timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id),
    CONSTRAINT uq_opp_docs_org_id UNIQUE (organization_id, id),
    FOREIGN KEY (organization_id, opportunity_id) REFERENCES tracked_opportunities (organization_id, id),
    FOREIGN KEY (organization_id, opportunity_version_id) REFERENCES opportunity_versions (organization_id, id)
        ON DELETE SET NULL (opportunity_version_id),
    FOREIGN KEY (organization_id, uploaded_by_user_id) REFERENCES users (organization_id, id)
        ON DELETE SET NULL (uploaded_by_user_id),
    FOREIGN KEY (organization_id, supersedes_document_id) REFERENCES opportunity_documents (organization_id, id)
        ON DELETE SET NULL (supersedes_document_id)
);

-- The document list per opportunity, filtered by extraction status; the composite index leads with org.
CREATE INDEX idx_opp_docs_org_opp ON opportunity_documents (organization_id, opportunity_id, extraction_status);
-- Duplicate detection: the same bytes are not accepted twice as a current document of one opportunity.
CREATE UNIQUE INDEX uq_opp_docs_sha ON opportunity_documents (organization_id, opportunity_id, sha256)
    WHERE is_current AND sha256 <> '';

ALTER TABLE opportunity_documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE opportunity_documents FORCE  ROW LEVEL SECURITY;
CREATE POLICY opp_docs_isolation ON opportunity_documents
    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 app uploads documents, advances extraction_status, and supersedes; it never hard deletes, so the
-- grant is SELECT, INSERT, UPDATE with no DELETE.
GRANT SELECT, INSERT, UPDATE ON opportunity_documents TO missionmatch_app;

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