-- REVIEW AND RUN MANUALLY ONLY. Never executed by APP/API.
-- Back up schema before installation. No historical backfill or attendance changes.
CREATE TABLE app_notification_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    event_key VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    event_type VARCHAR(32) NOT NULL,
    employee_code VARCHAR(64) NULL,
    work_date DATE NULL,
    title VARCHAR(500) NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_notification_event (event_key),
    KEY idx_notification_created (created_at,id),
    KEY idx_notification_employee (employee_code,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE app_notification_reads (
    user_id BIGINT UNSIGNED NOT NULL,
    event_key VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    read_at DATETIME NOT NULL,
    PRIMARY KEY (user_id,event_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Rollback: stop publishing/polling first. Keep these tables to preserve history.
-- Do not drop attendance/account tables. No automatic DROP is provided.