-- Aggie-server counterpart of add_reminders.sql (structure only).
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p 'stacie_Aggie_v1.0' < db/aggie_add_reminders.sql

USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS fact_reminders (
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT NOT NULL,
    entity_type  VARCHAR(30) NOT NULL,
    entity_id    BIGINT NOT NULL,
    title        VARCHAR(255) NOT NULL,
    message      TEXT NULL,
    due_at       DATE NOT NULL,
    is_completed TINYINT(1) NOT NULL DEFAULT 0,
    notified_at  TIMESTAMP NULL,
    created_at   TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by   INT NULL,
    updated_at   TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by   INT NULL,
    is_deleted   TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_reminders_entity (tenant_id, entity_type, entity_id, is_deleted),
    KEY idx_reminders_due_sweep (due_at, is_completed, notified_at, is_deleted)
);