-- Create Database
CREATE DATABASE IF NOT EXISTS ai_erp_platform;
USE ai_erp_platform;

-- Users Table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_name VARCHAR(255),
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    logo VARCHAR(500),
    plan VARCHAR(50) DEFAULT 'free',
    status VARCHAR(20) DEFAULT 'active',
    role VARCHAR(20) DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Chat Widgets Table
CREATE TABLE chat_widgets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    widget_name VARCHAR(255),
    widget_key VARCHAR(100) UNIQUE,
    ai_type ENUM('sales', 'support', 'both') DEFAULT 'both',
    training_method ENUM('url', 'text', 'pdf', 'none') DEFAULT 'none',
    training_content TEXT,
    website_url VARCHAR(500),
    status VARCHAR(20) DEFAULT 'inactive',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Leads Table
CREATE TABLE leads (
    id INT PRIMARY KEY AUTO_INCREMENT,
    widget_id INT,
    user_id INT,
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    source VARCHAR(50) DEFAULT 'chat',
    status ENUM('new', 'processing', 'converted', 'lost') DEFAULT 'new',
    chat_history TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (widget_id) REFERENCES chat_widgets(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Chat Messages Table
CREATE TABLE chat_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    widget_id INT,
    session_id VARCHAR(100),
    sender VARCHAR(20),
    message TEXT,
    is_ai BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (widget_id) REFERENCES chat_widgets(id) ON DELETE CASCADE
);

-- Live Chat Sessions Table
CREATE TABLE live_chat_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    widget_id INT,
    session_id VARCHAR(100),
    admin_id INT,
    status VARCHAR(20) DEFAULT 'waiting',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (widget_id) REFERENCES chat_widgets(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Subscriptions Table
CREATE TABLE subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    plan VARCHAR(50),
    amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'active',
    start_date DATE,
    end_date DATE,
    payment_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- AI Usage Logs Table
CREATE TABLE ai_usage_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    widget_id INT,
    tokens_used INT,
    request_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (widget_id) REFERENCES chat_widgets(id) ON DELETE SET NULL
);

-- Super Admin Table
CREATE TABLE super_admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert Super Admin
INSERT INTO super_admins (email, password) 
VALUES ('rudrahiremath.works@gmail.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- Sample user password: JMKAi@2026