-- Migration 013: lead_notes
-- Stores freeform notes attached to a lead (from the detail page).
-- Kept separate from audit_logs to allow soft-delete and editing.

CREATE TABLE IF NOT EXISTS lead_notes (
    id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    lead_id    BIGINT UNSIGNED  NOT NULL,
    org_id     BIGINT UNSIGNED  NOT NULL,
    user_id    BIGINT UNSIGNED  NULL,
    body       TEXT             NOT NULL,
    created_at DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_ln_lead   (lead_id),
    KEY idx_ln_org    (org_id),

    CONSTRAINT fk_ln_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    CONSTRAINT fk_ln_org  FOREIGN KEY (org_id)  REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
