-- OTP Selling System Database Schema

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    whatsapp_number VARCHAR(20),
    balance DECIMAL(10, 2) DEFAULT 0.00,
    is_admin INT DEFAULT 0,
    status INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS otp_numbers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone_number VARCHAR(20) NOT NULL,
    otp_link TEXT NOT NULL,
    service_type ENUM('whatsapp', 'facebook') NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    country VARCHAR(50) DEFAULT 'USA',
    is_available INT DEFAULT 1,
    purchased_by INT,
    purchased_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (purchased_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS deposits (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method VARCHAR(100),
    screenshot_path VARCHAR(255),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS purchase_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    otp_number_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    otp_link TEXT NOT NULL,
    service_type VARCHAR(50),
    price DECIMAL(10, 2) NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (otp_number_id) REFERENCES otp_numbers(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS deposit_methods (
    id INT PRIMARY KEY AUTO_INCREMENT,
    method_name VARCHAR(100) NOT NULL,
    method_details TEXT,
    is_active INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default admin user (email: admin@otp.com, password: admin123)
INSERT INTO users (email, password, is_admin, status) 
VALUES ('admin@otp.com', '$2y$10$YIjlrJ5Z5Z5Z5Z5Z5Z5Z5eYIjlrJ5Z5Z5Z5Z5Z5Z5Z5Z5Z5Z5Z5Z5Z', 1, 1);

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES 
('site_name', 'OTP Selling System'),
('site_url', 'http://localhost/otp_system/'),
('whatsapp_price', '5.00'),
('facebook_price', '3.00');
