-- Slice 8T-SubmissionAttachments · schema
-- Proof-of-submission attachments per tender. Distinct from tender_required_documents (the checklist)
-- and tender_contract_receipts (post-signature payment receipts). Captures things like:
--   RECEIPT               — physical receipt from the tender box (photo/scan)
--   COURIER_WAYBILL       — courier tracking / airway bill
--   PORTAL_SCREENSHOT     — screenshot from PPRA/NeST/e-tender portal
--   SEALED_ENVELOPE_PHOTO — photo of sealed envelope before drop-off
--   ACKNOWLEDGMENT_EMAIL  — client's acknowledgment email
--   OTHER                 — anything else that proves submission happened

CREATE TABLE IF NOT EXISTS tender_submission_attachments (
    c0d               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    tender_id         INT UNSIGNED    NOT NULL,
    kind              ENUM('RECEIPT','COURIER_WAYBILL','PORTAL_SCREENSHOT','SEALED_ENVELOPE_PHOTO','ACKNOWLEDGMENT_EMAIL','OTHER') NOT NULL DEFAULT 'OTHER',
    label             VARCHAR(200)    NOT NULL,
    original_filename VARCHAR(255)    NOT NULL,
    stored_path       VARCHAR(500)    NOT NULL,
    mime_type         VARCHAR(120)    NULL,
    size_bytes        INT UNSIGNED    NOT NULL DEFAULT 0,
    notes             VARCHAR(500)    NULL,
    deleted           TINYINT(1)      NOT NULL DEFAULT 0,
    uploaded_by       INT UNSIGNED    NULL,
    uploaded_by_name  VARCHAR(160)    NULL,
    uploaded_on       TIMESTAMP       NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (c0d),
    KEY idx_tsa_tender (tender_id, deleted, uploaded_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
