SAAS Tables

CREATE TABLE saas_ai_bots (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    api_key VARCHAR(100) NOT NULL,

    provider VARCHAR(50) DEFAULT 'openai',
    provider_api_key TEXT,
    model VARCHAR(100) DEFAULT 'gpt-4o-mini',
    temperature DECIMAL(3,2) DEFAULT 0.50,
    max_tokens INT DEFAULT 1024,

    prompt_persona TEXT,
    prompt_task TEXT,
    prompt_context TEXT,
    prompt_format TEXT,

    ui_title VARCHAR(100) DEFAULT 'AI Assistant',
    ui_welcome_msg VARCHAR(255) DEFAULT 'Hello! How can I help you today?',
    ui_placeholder VARCHAR(100) DEFAULT 'Type a message...',

    ui_color VARCHAR(20) DEFAULT '#1677ff',
    ui_bg_color VARCHAR(20) DEFAULT '#FFFFFF',
    ui_text_color VARCHAR(20) DEFAULT '#333333',

    ui_btn_text VARCHAR(50) DEFAULT 'Send',

    ui_pos_bottom VARCHAR(20) DEFAULT '20px',
    ui_pos_right VARCHAR(20) DEFAULT '20px',

    ui_trigger_icon VARCHAR(255) DEFAULT '💬',
    ui_trigger_bg_transparent TINYINT(1) DEFAULT 0,
    ui_trigger_border_radius VARCHAR(20) DEFAULT '50%',

    ui_clear_on_close TINYINT(1) DEFAULT 0,

    ui_pre_chat_form TINYINT(1) DEFAULT 0,
    ui_pre_chat_msg VARCHAR(255),
    ui_pre_chat_name_label VARCHAR(100),
    ui_pre_chat_phone_label VARCHAR(100),

    admin_timeout_mins INT DEFAULT 15,
    history_limit INT DEFAULT 5,

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

    PRIMARY KEY (id),
    UNIQUE KEY uk_api_key (api_key),
    KEY idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE saas_ai_channels (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    bot_id BIGINT UNSIGNED NOT NULL,

    channel_type VARCHAR(50) NOT NULL,
    channel_name VARCHAR(255) NOT NULL,
    is_active TINYINT(1) DEFAULT 1,

    config JSON NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_bot (bot_id),
    KEY idx_channel_type (channel_type),

    CONSTRAINT fk_channels_bot
        FOREIGN KEY (bot_id)
        REFERENCES saas_ai_bots(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE saas_ai_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    bot_id BIGINT UNSIGNED NOT NULL,

    session_id VARCHAR(100) NOT NULL,
    role ENUM('user','bot','admin') NOT NULL,
    content LONGTEXT NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_bot (bot_id),
    KEY idx_session (session_id),
    KEY idx_created (created_at),

    CONSTRAINT fk_logs_bot
        FOREIGN KEY (bot_id)
        REFERENCES saas_ai_bots(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE saas_ai_rag (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    bot_id BIGINT UNSIGNED NOT NULL,

    title VARCHAR(255) NOT NULL,
    source_type VARCHAR(50) NOT NULL,
    content LONGTEXT NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_bot (bot_id),

    CONSTRAINT fk_rag_bot
        FOREIGN KEY (bot_id)
        REFERENCES saas_ai_bots(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE saas_ai_session_stats (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    bot_id BIGINT UNSIGNED NOT NULL,

    session_id VARCHAR(100) NOT NULL,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    first_admin_time TIMESTAMP NULL,
    last_admin_time TIMESTAMP NULL,

    admin_msg_count INT DEFAULT 0,
    is_emailed TINYINT(1) DEFAULT 0,

    PRIMARY KEY (id),
    UNIQUE KEY uk_session (session_id),
    KEY idx_bot (bot_id),

    CONSTRAINT fk_stats_bot
        FOREIGN KEY (bot_id)
        REFERENCES saas_ai_bots(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE saas_ai_leads (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    bot_id BIGINT UNSIGNED NOT NULL,

    session_id VARCHAR(100) NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(50) NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_bot (bot_id),
    KEY idx_phone (customer_phone),

    CONSTRAINT fk_leads_bot
        FOREIGN KEY (bot_id)
        REFERENCES saas_ai_bots(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;