-- Slice 8Z-f · Batch 4 · Product Discovery findings.
--
-- When Procurement retires a PRODUCT_DISCOVERY LPO, they also capture what the cash bought:
-- supplier profiles + quotes + samples + a SHORTLIST/MAYBE/REJECT recommendation. These findings
-- are then seed data for the RFQ shortlist on the same PO (queried by shortlistForPo()).
--
-- One row per supplier profiled. Attaches to the retirement (not the LPO directly) so the
-- data captured mirrors the spend it's justifying — you get "what you spent on" AND "what you found".

CREATE TABLE IF NOT EXISTS lpo_retirement_findings (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    retirement_id INT UNSIGNED NOT NULL,
    supplier_name VARCHAR(200) NOT NULL,
    contact_name VARCHAR(160) NULL,
    phone VARCHAR(60) NULL,
    email VARCHAR(160) NULL,
    tin VARCHAR(30) NULL,
    country VARCHAR(80) NULL,
    capability_notes VARCHAR(1000) NULL,
    quote_received TINYINT(1) NOT NULL DEFAULT 0,
    quote_amount DECIMAL(20,2) NULL,
    quote_currency VARCHAR(8) NULL,
    quote_lead_time_days INT UNSIGNED NULL,
    sample_received TINYINT(1) NOT NULL DEFAULT 0,
    recommendation ENUM('SHORTLIST','MAYBE','REJECT') NOT NULL DEFAULT 'MAYBE',
    rationale VARCHAR(1000) NULL,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    KEY idx_retirement (retirement_id),
    KEY idx_recommendation (recommendation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Finding CRUD reuses the Retirements controller perms (already granted in slice8zd) —
-- only the requester of the PRODUCT_DISCOVERY LPO can edit their findings, same as spend lines.
-- No extra perm rows needed; controller enforces owner-or-reviewer at the action layer.
