-- Generic reminders — a due-date note attachable to any entity that already supports
-- attachments/activity (Digital Assets first; the entity_type set matches
-- AttachableEntityType.java, no FK, same polymorphic approach as fact_attachments/
-- fact_activities). A daily scheduler (ReminderNotificationScheduler) notifies the
-- creator once when due_at arrives.
-- STRUCTURE ONLY — no data inserted here.
-- Idempotent — safe to re-run.
-- Deploy (local): mysql -u root -p db_abos_v0.1 < db/add_reminders.sql

USE db_abos_v0.1;

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)
);