-- schema.sql
-- Run once to initialize a fresh database.
-- CREATE DATABASE sms_platform CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE sms_platform;

CREATE TABLE IF NOT EXISTS users (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    balance     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    plan        ENUM('free','pro') DEFAULT 'free',
    status      ENUM('active','banned') DEFAULT 'active',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- twilio_sid stores the Twilio IncomingPhoneNumber SID for programmatic release
CREATE TABLE IF NOT EXISTS numbers (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    number       VARCHAR(20) NOT NULL UNIQUE,
    country_code CHAR(2) NOT NULL,
    country_name VARCHAR(100),
    price        DECIMAL(6,4) NOT NULL DEFAULT 0.50,
    status       ENUM('available','assigned','disabled') DEFAULT 'available',
    assigned_to  INT NULL,
    assigned_at  DATETIME NULL,
    twilio_sid   VARCHAR(64) NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    number_id   INT NOT NULL,
    user_id     INT NOT NULL,
    from_number VARCHAR(30),
    body        TEXT NOT NULL,
    otp_code    VARCHAR(8) NULL,
    received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at  DATETIME NOT NULL,
    FOREIGN KEY (number_id) REFERENCES numbers(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS transactions (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    amount      DECIMAL(10,2) NOT NULL,
    type        ENUM('credit','debit') NOT NULL,
    description VARCHAR(255),
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS deposits (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    amount      DECIMAL(10,2) NOT NULL,
    method      VARCHAR(50) DEFAULT 'crypto',
    currency    VARCHAR(20) NULL,
    payment_id  VARCHAR(255) NULL,
    pay_address VARCHAR(255) NULL,
    status      ENUM('pending','completed','failed') DEFAULT 'pending',
    credited    TINYINT(1) NOT NULL DEFAULT 0,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rate_limits (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    ip           VARCHAR(45) NOT NULL,
    action       VARCHAR(50) NOT NULL,
    attempts     INT DEFAULT 1,
    last_attempt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ip_action (ip, action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    password   VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_numbers_status   ON numbers(status);
CREATE INDEX IF NOT EXISTS idx_messages_number  ON messages(number_id, received_at);
CREATE INDEX IF NOT EXISTS idx_txn_user         ON transactions(user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_deposits_payment ON deposits(payment_id);

-- Dedup index to prevent duplicate message inserts from Twilio retries
CREATE UNIQUE INDEX IF NOT EXISTS uq_message_dedup ON messages(number_id, from_number, body(255));