-- Wave 1 / Milestone A: read-only legacy audit run/finding persistence.
-- Additive tables only; no legacy business data is modified by this migration.

CREATE TABLE IF NOT EXISTS DOC.doc_legacy_audit_run (
    id UUID PRIMARY KEY,
    status VARCHAR(32) NOT NULL,
    requested_limit INTEGER,
    page_size INTEGER NOT NULL,
    scanned_packages INTEGER NOT NULL DEFAULT 0,
    scanned_legacy_documents INTEGER NOT NULL DEFAULT 0,
    finding_count INTEGER NOT NULL DEFAULT 0,
    info_count INTEGER NOT NULL DEFAULT 0,
    warning_count INTEGER NOT NULL DEFAULT 0,
    error_count INTEGER NOT NULL DEFAULT 0,
    started_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ,
    summary_text TEXT,
    failure_message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_doc_legacy_audit_run_status
    ON DOC.doc_legacy_audit_run(status);

CREATE INDEX IF NOT EXISTS idx_doc_legacy_audit_run_started
    ON DOC.doc_legacy_audit_run(started_at DESC);

CREATE TABLE IF NOT EXISTS DOC.doc_legacy_audit_finding (
    id UUID PRIMARY KEY,
    run_id UUID NOT NULL REFERENCES DOC.doc_legacy_audit_run(id) ON DELETE CASCADE,
    severity VARCHAR(16) NOT NULL,
    finding_type VARCHAR(64) NOT NULL,
    package_identifier VARCHAR(20),
    legacy_procurement_document_id UUID,
    document_id UUID,
    ted_notice_projection_id UUID,
    reference_key VARCHAR(255),
    message TEXT NOT NULL,
    details_text TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_doc_legacy_audit_find_run
    ON DOC.doc_legacy_audit_finding(run_id);

CREATE INDEX IF NOT EXISTS idx_doc_legacy_audit_find_type
    ON DOC.doc_legacy_audit_finding(finding_type);

CREATE INDEX IF NOT EXISTS idx_doc_legacy_audit_find_severity
    ON DOC.doc_legacy_audit_finding(severity);

CREATE INDEX IF NOT EXISTS idx_doc_legacy_audit_find_legacy_doc
    ON DOC.doc_legacy_audit_finding(legacy_procurement_document_id);

CREATE INDEX IF NOT EXISTS idx_doc_legacy_audit_find_document
    ON DOC.doc_legacy_audit_finding(document_id);
