-- ═══════════════════════════════════════════════════════════════════════
-- WhatsApp Communications  (add-on: whatsapp_comms)
-- Meta Cloud API send log, 24h-window sessions, and pending free-form queue.
-- Idempotent. The app also self-heals via includes/whatsapp_lib.php.
-- Credentials live in the `settings` table (wa_token, wa_phone_id,
-- wa_verify_token, wa_report_recipients) with .env fallback.
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `whatsapp_log` (
    `id`              INT AUTO_INCREMENT PRIMARY KEY,
    `to_number`       VARCHAR(20)  NOT NULL,
    `message_type`    VARCHAR(20)  NOT NULL,
    `template_name`   VARCHAR(80)  NULL,
    `message_preview` VARCHAR(500) NULL,
    `message_id`      VARCHAR(120) NULL,
    `status`          VARCHAR(12)  NOT NULL,
    `error_message`   VARCHAR(255) NULL,
    `sent_by`         INT          NULL,
    `sent_at`         DATETIME     DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_to` (`to_number`), KEY `idx_sent` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `whatsapp_sessions` (
    `phone`           VARCHAR(20) PRIMARY KEY,
    `last_inbound_at` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `whatsapp_pending` (
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `phone`      VARCHAR(20)  NOT NULL,
    `body`       TEXT         NOT NULL,
    `subject`    VARCHAR(160) NULL,
    `created_at` DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `sent_at`    DATETIME     NULL,
    KEY `idx_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
