-- ============================================================
--  SMS Gateway - MySQL Schema
--  Run:  mysql -u root -p < schema.sql
-- ============================================================
SET NAMES utf8mb4;
SET time_zone = '+00:00';

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

-- ------------------------------------------------------------
--  clients : each website that is allowed to queue SMS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS clients (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_name   VARCHAR(120)      NOT NULL,
    api_key     CHAR(64)          NOT NULL,                 -- send to us in X-API-KEY
    status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at  TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_api_key (api_key),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ------------------------------------------------------------
--  Seed one demo client.
--  IMPORTANT: replace this api_key before going live.
--  Generate a real one:  php -r "echo bin2hex(random_bytes(32));"
-- ------------------------------------------------------------
INSERT INTO clients (site_name, api_key, status) VALUES
('Demo Site A',
 'DEMO_REPLACE_ME_0000000000000000000000000000000000000000000000000000',
 'active')
ON DUPLICATE KEY UPDATE site_name = VALUES(site_name);