-- Nexora SMS Gateway - Database Schema
-- Import this file first via phpMyAdmin or: mysql -u USER -p DBNAME < schema.sql

SET FOREIGN_KEY_CHECKS=0;

-- ------------------------------------------------------------
-- Admins
-- ------------------------------------------------------------
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Default admin -> username: admin / password: ChangeMe123!
-- (hash generated with PHP password_hash, change immediately after install)
INSERT INTO admins (username, password) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- ------------------------------------------------------------
-- Plans
-- ------------------------------------------------------------
CREATE TABLE plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    duration_days INT NOT NULL DEFAULT 30,
    sms_limit INT NOT NULL DEFAULT 0,
    device_limit INT NOT NULL DEFAULT 1,
    api_access TINYINT(1) NOT NULL DEFAULT 1,
    features TEXT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO plans (name, price, duration_days, sms_limit, device_limit, api_access, features) VALUES
('Starter', 99.00, 30, 1000, 1, 1, 'Basic API access'),
('Business', 499.00, 30, 20000, 10, 1, 'Priority support, load balancing');

-- ------------------------------------------------------------
-- Users
-- ------------------------------------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20) NULL,
    password VARCHAR(255) NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1, -- 1 active, 0 blocked
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Subscriptions
-- ------------------------------------------------------------
CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    sms_used INT NOT NULL DEFAULT 0,
    sms_limit INT NOT NULL DEFAULT 0,
    starts_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1, -- 1 active, 0 expired
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Gateway accounts (a user can group devices under a gateway id)
-- ------------------------------------------------------------
CREATE TABLE gateway_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    gateway_id VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- API keys (one primary key per user, can extend to multiple later)
-- ------------------------------------------------------------
CREATE TABLE api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    api_key VARCHAR(64) NOT NULL UNIQUE,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Devices
-- ------------------------------------------------------------
CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    gateway_id VARCHAR(50) NOT NULL,
    device_id VARCHAR(50) NOT NULL UNIQUE, -- e.g. NEX-SAM123456
    device_model VARCHAR(100) NULL,
    android_version VARCHAR(50) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'offline', -- online / offline / busy
    last_active DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SMS Queue (tasks waiting to be picked up by a device)
-- ------------------------------------------------------------
CREATE TABLE sms_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id VARCHAR(50) NULL, -- NULL until a device is assigned
    number VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending / sent / failed
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    picked_at DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SMS Logs (final outcome, used for API logs page)
-- ------------------------------------------------------------
CREATE TABLE sms_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id VARCHAR(50) NULL,
    number VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    status VARCHAR(20) NOT NULL, -- success / failed
    response TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Payments
-- ------------------------------------------------------------
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    gateway VARCHAR(50) NULL, -- razorpay / stripe / manual etc, wire up later
    txn_id VARCHAR(100) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending / success / failed
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Settings (key/value store, e.g. apk_download_link, apk_version)
-- ------------------------------------------------------------
CREATE TABLE settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NULL
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
('apk_download_link', ''),
('apk_version', '1.0.0'),
('company_prefix', 'NEX');

SET FOREIGN_KEY_CHECKS=1;