-- ============================================================
--  SMS Gateway - MySQL Schema
--  Run:  mysql -u root -p < schema.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS sms_gateway
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sms_gateway;

-- ------------------------------------------------------------
--  sms_queue : the message queue the phone drains
--  PROCESSING = claimed by the phone (locked so it is not sent twice)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sms_queue (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id     INT UNSIGNED   NOT NULL,
    phone_number  VARCHAR(20)    NOT NULL,
    message       VARCHAR(640)   NOT NULL,                  -- up to ~4 SMS segments
    status        ENUM('PENDING','PROCESSING','SENT','FAILED')
                                 NOT NULL DEFAULT 'PENDING',
    attempts      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    error_reason  VARCHAR(255)   DEFAULT NULL,
    created_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
                                 ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_queue_client FOREIGN KEY (client_id)
        REFERENCES clients(id) ON DELETE CASCADE,
    KEY idx_status_created (status, created_at),            -- fast "next PENDING" fetch
    KEY idx_client_created (client_id, created_at)          -- fast rate-limit count
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
