-- Application-wide email and SMS alert support.

CREATE TABLE IF NOT EXISTS alert_delivery_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_key VARCHAR(120) NOT NULL,
    channel VARCHAR(30) NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    recipient VARCHAR(255) NULL,
    subject VARCHAR(255) NULL,
    message TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    provider_response TEXT NULL,
    payload_json TEXT NULL,
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    next_attempt_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    reference_type VARCHAR(80) NULL,
    reference_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_alert_logs_event (event_key),
    KEY idx_alert_logs_user (user_id),
    KEY idx_alert_logs_status (status),
    KEY idx_alert_logs_retry (status, next_attempt_at),
    KEY idx_alert_logs_reference (reference_type, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE alert_delivery_logs
    ADD COLUMN IF NOT EXISTS payload_json TEXT NULL,
    ADD COLUMN IF NOT EXISTS attempts INT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS next_attempt_at TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS delivered_at TIMESTAMP NULL,
    ADD INDEX IF NOT EXISTS idx_alert_logs_retry (status, next_attempt_at);

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS phone VARCHAR(30) NULL,
    ADD COLUMN IF NOT EXISTS email_alerts_enabled TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS sms_alerts_enabled TINYINT(1) NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS alert_settings (
    setting_key VARCHAR(120) NOT NULL,
    setting_value TEXT NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;