-- Database: email_service
CREATE DATABASE IF NOT EXISTS email_service;
USE email_service;

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    country_code VARCHAR(10) NOT NULL,
    country_name VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    email_verified BOOLEAN DEFAULT 0,
    verification_token VARCHAR(100),
    verification_expiry DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active'
);

-- Email pool table
CREATE TABLE email_pool (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email_address VARCHAR(100) UNIQUE NOT NULL,
    email_password VARCHAR(255) NOT NULL,
    status ENUM('available', 'assigned', 'used', 'blocked') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Services table
CREATE TABLE services (
    id INT PRIMARY KEY AUTO_INCREMENT,
    service_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    price_per_email DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User purchases table
CREATE TABLE user_purchases (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    service_id INT,
    email_pool_id INT,
    quantity INT NOT NULL,
    used_count INT DEFAULT 0,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiry_date DATETIME,
    status ENUM('active', 'expired', 'used') DEFAULT 'active',
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (service_id) REFERENCES services(id),
    FOREIGN KEY (email_pool_id) REFERENCES email_pool(id)
);

-- Email usage log
CREATE TABLE email_usage_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    email_pool_id INT,
    service_id INT,
    action_type VARCHAR(50),
    details TEXT,
    ip_address VARCHAR(45),
    used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (email_pool_id) REFERENCES email_pool(id),
    FOREIGN KEY (service_id) REFERENCES services(id)
);

-- Password reset tokens
CREATE TABLE password_resets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    token VARCHAR(100) UNIQUE,
    expires_at DATETIME,
    used BOOLEAN DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert default services
INSERT INTO services (service_name, description, price_per_email) VALUES
('Facebook Verification', 'Email accounts for Facebook verification', 0.50),
('Instagram Verification', 'Email accounts for Instagram verification', 0.45),
('LinkedIn Verification', 'Email accounts for LinkedIn verification', 0.60),
('X (Twitter) Verification', 'Email accounts for X/Twitter verification', 0.55),
('Snapchat Opening', 'Email accounts for Snapchat account creation', 0.40),
('TikTok Opening', 'Email accounts for TikTok account creation', 0.35);