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

-- 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
);

-- 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
);

-- Sample user password: JMKAi@2026