-- v2026.05.18.1 — Fund PDF Evidence Layer
-- Run once against the live database.

ALTER TABLE funds
  ADD COLUMN pdf_filename       VARCHAR(255)      NULL,
  ADD COLUMN pdf_uploaded_at    DATETIME          NULL,
  ADD COLUMN pdf_checksum       CHAR(64)          NULL,
  ADD COLUMN pdf_storage_path   VARCHAR(500)      NULL,
  ADD COLUMN pdf_extracted_text MEDIUMTEXT        NULL,
  ADD COLUMN pdf_last_verified  DATETIME          NULL,
  ADD COLUMN pdf_confidence_score DECIMAL(5,2)    NULL;

CREATE TABLE IF NOT EXISTS fund_pdf_ingestions (
    id                BIGINT          PRIMARY KEY AUTO_INCREMENT,
    fund_id           BIGINT          NULL,
    filename          VARCHAR(255)    NOT NULL,
    original_filename VARCHAR(255)    NOT NULL,
    checksum          CHAR(64)        NOT NULL,
    status            VARCHAR(50)     NOT NULL DEFAULT 'pending',
    confidence_score  DECIMAL(5,2)   NULL,
    extraction_json   LONGTEXT        NULL,
    failure_reason    TEXT            NULL,
    processed_at      DATETIME        NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status        (status),
    INDEX idx_fund_id       (fund_id),
    INDEX idx_checksum      (checksum),
    INDEX idx_processed_at  (processed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;