-- ============================================================
-- 055 — Cola de recordatorios WhatsApp / SMS para citas
-- ============================================================
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS appointment_reminders (
    id               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id       INT UNSIGNED    NOT NULL,
    appointment_id   INT UNSIGNED    NOT NULL,
    patient_id       INT UNSIGNED    DEFAULT NULL,
    phone            VARCHAR(30)     NOT NULL,
    channel          ENUM('whatsapp','sms')   NOT NULL DEFAULT 'whatsapp',
    reminder_type    VARCHAR(20)     NOT NULL DEFAULT '24h'
                     COMMENT '24h | 2h | manual | custom',
    scheduled_at     DATETIME        NOT NULL COMMENT 'Cuándo debe enviarse',
    sent_at          DATETIME        DEFAULT NULL,
    status           ENUM('pending','sending','sent','failed','skipped','cancelled')
                     NOT NULL DEFAULT 'pending',
    message_text     TEXT            DEFAULT NULL,
    provider_response TEXT           DEFAULT NULL,
    error_message    VARCHAR(500)    DEFAULT NULL,
    attempts         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_company      (company_id),
    INDEX idx_appointment  (appointment_id),
    INDEX idx_pending      (status, scheduled_at),
    INDEX idx_patient      (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;