-- ChoffySMS Database Schema
-- Import this into MySQL/MariaDB before running the app.
-- Example: mysql -u root -p choffysms < schema.sql

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

-- ---------------------------------------------------------------
-- Users
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    full_name       VARCHAR(150)  NOT NULL,
    email           VARCHAR(150)  NOT NULL UNIQUE,
    phone           VARCHAR(30)   DEFAULT NULL,
    password_hash   VARCHAR(255)  NOT NULL,
    wallet_balance  DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    role            ENUM('user','admin') NOT NULL DEFAULT 'user',
    status          ENUM('active','suspended') NOT NULL DEFAULT 'active',
    referral_code   VARCHAR(20)   DEFAULT NULL UNIQUE,
    referred_by     INT           DEFAULT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Services (WhatsApp, Telegram, Instagram, etc.)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS services (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,          -- Display name e.g. "WhatsApp"
    provider_code   VARCHAR(50)  NOT NULL,          -- Code used by SMS provider e.g. "wa"
    icon            VARCHAR(255) DEFAULT NULL,
    base_price      DECIMAL(10,2) NOT NULL,         -- Your selling price (NGN)
    is_active       TINYINT(1)   NOT NULL DEFAULT 1,
    sort_order      INT          NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Countries available per provider
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS countries (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    provider_code   VARCHAR(20)  NOT NULL,          -- Country code used by SMS provider
    flag_emoji      VARCHAR(10)  DEFAULT NULL,
    is_active       TINYINT(1)   NOT NULL DEFAULT 1
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Wallet transactions (funding, purchases, refunds)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS transactions (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    type            ENUM('fund','purchase','refund') NOT NULL,
    amount          DECIMAL(12,2) NOT NULL,
    balance_after   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    reference       VARCHAR(120)  NOT NULL UNIQUE,   -- Paystack ref or internal ref
    channel         VARCHAR(30)   DEFAULT NULL,      -- paystack, wallet, etc
    status          ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
    meta            TEXT DEFAULT NULL,               -- JSON blob for extra info
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Purchased virtual numbers
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS numbers (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    user_id             INT NOT NULL,
    service_id          INT NOT NULL,
    country_id          INT DEFAULT NULL,
    phone_number        VARCHAR(30)  DEFAULT NULL,
    provider_order_id   VARCHAR(100) DEFAULT NULL,   -- id returned by SMS provider
    otp_code            VARCHAR(20)  DEFAULT NULL,
    price               DECIMAL(10,2) NOT NULL,
    status              ENUM('waiting','received','expired','cancelled') NOT NULL DEFAULT 'waiting',
    expires_at          DATETIME DEFAULT NULL,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Seed a few starter services (edit prices/provider codes as needed)
-- ---------------------------------------------------------------
INSERT INTO services (name, provider_code, base_price, sort_order) VALUES
('WhatsApp', 'wa', 350.00, 1),
('Telegram', 'tg', 300.00, 2),
('Instagram', 'ig', 400.00, 3),
('Facebook', 'fb', 400.00, 4),
('Twitter/X', 'tw', 400.00, 5),
('TikTok', 'tt', 400.00, 6)
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO countries (name, provider_code, flag_emoji) VALUES
('Nigeria', '18', '🇳🇬'),
('United States', '187', '🇺🇸'),
('United Kingdom', '16', '🇬🇧')
ON DUPLICATE KEY UPDATE name = VALUES(name);