CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,

    email VARCHAR(190) NOT NULL UNIQUE,
    phone VARCHAR(30) NOT NULL UNIQUE,

    password VARCHAR(255) NOT NULL,

    referral_code VARCHAR(50) UNIQUE NULL,
    referred_by VARCHAR(50) NULL,

    status ENUM(
        'active',
        'suspended',
        'pending'
    ) NOT NULL DEFAULT 'active',

    email_verified_at DATETIME NULL,

    last_login_at DATETIME NULL,
    last_login_ip VARCHAR(45) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_status (status),
    INDEX idx_referral_code (referral_code)
) ENGINE=InnoDB;


-- ============================================
-- WALLETS
-- ============================================

CREATE TABLE wallets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL UNIQUE,

    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    currency VARCHAR(10) NOT NULL DEFAULT 'NGN',

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_wallet_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;


-- ============================================
-- TRANSACTIONS
-- ============================================

CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,

    reference VARCHAR(100) NOT NULL UNIQUE,

    type ENUM(
        'deposit',
        'purchase',
        'refund',
        'bonus',
        'withdrawal',
        'adjustment'
    ) NOT NULL,

    amount DECIMAL(15,2) NOT NULL,

    balance_before DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    balance_after DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    description VARCHAR(255) NULL,

    status ENUM(
        'pending',
        'successful',
        'failed',
        'cancelled'
    ) NOT NULL DEFAULT 'pending',

    payment_method VARCHAR(50) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_transaction_user (user_id),
    INDEX idx_transaction_type (type),
    INDEX idx_transaction_status (status),

    CONSTRAINT fk_transaction_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;


-- ============================================
-- VIRTUAL NUMBERS
-- ============================================

CREATE TABLE virtual_numbers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    country VARCHAR(100) NOT NULL,
    country_code VARCHAR(10) NOT NULL,

    phone_number VARCHAR(50) NOT NULL,

    service VARCHAR(100) NULL,

    provider VARCHAR(100) NULL,

    provider_number_id VARCHAR(150) NULL,

    price DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    currency VARCHAR(10) NOT NULL DEFAULT 'NGN',

    status ENUM(
        'available',
        'sold',
        'reserved',
        'expired',
        'disabled'
    ) NOT NULL DEFAULT 'available',

    expires_at DATETIME NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_phone_number (phone_number),

    INDEX idx_country (country),
    INDEX idx_service (service),
    INDEX idx_status (status)
) ENGINE=InnoDB;


-- ============================================
-- NUMBER ORDERS
-- ============================================

CREATE TABLE number_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,

    number_id BIGINT UNSIGNED NULL,

    reference VARCHAR(100) NOT NULL UNIQUE,

    country VARCHAR(100) NOT NULL,
    service VARCHAR(100) NOT NULL,

    phone_number VARCHAR(50) NULL,

    amount DECIMAL(15,2) NOT NULL,

    status ENUM(
        'pending',
        'active',
        'completed',
        'expired',
        'cancelled',
        'refunded'
    ) NOT NULL DEFAULT 'pending',

    provider VARCHAR(100) NULL,
    provider_order_id VARCHAR(150) NULL,

    otp_code VARCHAR(20) NULL,

    otp_received_at DATETIME NULL,

    expires_at DATETIME NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_order_user (user_id),
    INDEX idx_order_status (status),
    INDEX idx_order_reference (reference),

    CONSTRAINT fk_order_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_order_number
        FOREIGN KEY (number_id)
        REFERENCES virtual_numbers(id)
        ON DELETE SET NULL
) ENGINE=InnoDB;


-- ============================================
-- NOTIFICATIONS
-- ============================================

CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,

    title VARCHAR(150) NOT NULL,
    message TEXT NOT NULL,

    type ENUM(
        'info',
        'success',
        'warning',
        'error'
    ) NOT NULL DEFAULT 'info',

    is_read TINYINT(1) NOT NULL DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_notification_user (user_id),
    INDEX idx_notification_read (is_read),

    CONSTRAINT fk_notification_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;


-- ============================================
-- ADMIN USERS
-- ============================================

CREATE TABLE admin_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(150) NOT NULL,

    email VARCHAR(190) NOT NULL UNIQUE,

    password VARCHAR(255) NOT NULL,

    role ENUM(
        'super_admin',
        'admin'
    ) NOT NULL DEFAULT 'admin',

    status ENUM(
        'active',
        'disabled'
    ) NOT NULL DEFAULT 'active',

    last_login_at DATETIME NULL,
    last_login_ip VARCHAR(45) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;


-- ============================================
-- SETTINGS
-- ============================================

CREATE TABLE settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    setting_key VARCHAR(100) NOT NULL UNIQUE,

    setting_value TEXT NULL,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;


-- ============================================
-- DEFAULT SETTINGS
-- ============================================

INSERT INTO settings
(setting_key, setting_value)
VALUES

('site_name', 'ArewaOTP'),

('site_currency', 'NGN'),

('support_email', ''),

('maintenance_mode', '0'),

('registration_enabled', '1'),

('minimum_deposit', '100'),

('default_number_expiry', '20');