-- Slice 7C · Handoff notifications
-- Lightweight, generic. Any module can drop rows via Notifications::notify().

CREATE TABLE IF NOT EXISTS notifications (
    c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    recipient_user_id INT NOT NULL,
    kind              VARCHAR(60) NOT NULL,           -- e.g. TENDER_WON, TENDER_AWARDED, PROJECT_PROVISIONED, STARTER_TASK_ASSIGNED
    subject           VARCHAR(200) NOT NULL,
    body              VARCHAR(500) NOT NULL DEFAULT '',
    target_url        VARCHAR(255) NOT NULL DEFAULT '',
    entity_type       VARCHAR(60) NOT NULL DEFAULT '', -- optional: 'tender_tracker', 'c_projects', etc
    entity_id         INT NOT NULL DEFAULT 0,
    created_by        INT NOT NULL DEFAULT 0,
    created_by_name   VARCHAR(120) NOT NULL DEFAULT '',
    created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_on           TIMESTAMP NULL,
    deleted           TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_recip_unread (recipient_user_id, read_on),
    KEY ix_recip_created (recipient_user_id, created_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
