server/db_schema.sql

-- Customer Agent Database Schema

-- Users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email VARCHAR UNIQUE NOT NULL,
    name VARCHAR,
    hashed_password VARCHAR NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Websites table
CREATE TABLE websites (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    url VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    industry VARCHAR,
    tone VARCHAR DEFAULT 'friendly',
    is_verified BOOLEAN DEFAULT 0,
    owner_id INTEGER NOT NULL,
    widget_config JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_scraped DATETIME,
    FOREIGN KEY (owner_id) REFERENCES users(id)
);

-- Website content table
CREATE TABLE website_content (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    website_id INTEGER NOT NULL,
    page_url VARCHAR NOT NULL,
    content TEXT NOT NULL,
    embedding TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (website_id) REFERENCES websites(id)
);

-- Chat sessions table
CREATE TABLE chat_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id VARCHAR(255) UNIQUE NOT NULL,
    website_id INTEGER NOT NULL,
    ip_address VARCHAR(50),
    user_agent TEXT,
    visitor_name VARCHAR(255),
    visitor_email VARCHAR(255),
    country VARCHAR(100),
    city VARCHAR(100),
    messages JSON,
    message_count INTEGER DEFAULT 0,
    language_detected VARCHAR(10),
    industry VARCHAR(50),
    first_query TEXT,
    last_query TEXT,
    is_active BOOLEAN DEFAULT 1,
    ended_reason VARCHAR(100),
    avg_response_time INTEGER,
    satisfaction_rating INTEGER,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_activity_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME,
    FOREIGN KEY (website_id) REFERENCES websites(id)
);

-- Chat messages table
CREATE TABLE chat_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id INTEGER NOT NULL,
    message TEXT NOT NULL,
    is_from_visitor BOOLEAN DEFAULT 1,
    is_from_ai BOOLEAN DEFAULT 0,
    sender_name VARCHAR,
    analysis JSON,
    confidence_score FLOAT,
    escalated BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES chat_sessions(id)
);

-- User settings table
CREATE TABLE user_settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER UNIQUE NOT NULL,
    business_name VARCHAR(255),
    business_email VARCHAR(255),
    ai_tone VARCHAR(50) DEFAULT 'friendly',
    notifications BOOLEAN DEFAULT 1,
    auto_response BOOLEAN DEFAULT 1
);

-- FAQ table
CREATE TABLE faqs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    website_id INTEGER NOT NULL,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (website_id) REFERENCES websites(id)
);

-- Unanswered questions table
CREATE TABLE unanswered_questions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    website_id INTEGER NOT NULL,
    question TEXT NOT NULL,
    count INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (website_id) REFERENCES websites(id)
);

-- Contact requests table
CREATE TABLE contact_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    website_id INTEGER NOT NULL,
    visitor_name VARCHAR(255),
    visitor_email VARCHAR(255),
    message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (website_id) REFERENCES websites(id)
);

-- User query limits table
CREATE TABLE user_query_limits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    website_id INTEGER NOT NULL,
    query_count INTEGER DEFAULT 0,
    reset_date DATETIME,
    FOREIGN KEY (website_id) REFERENCES websites(id)
);

-- Indexes for performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_websites_owner ON websites(owner_id);
CREATE INDEX idx_website_content_website ON website_content(website_id);
CREATE INDEX idx_chat_sessions_website ON chat_sessions(website_id);
CREATE INDEX idx_chat_sessions_ip ON chat_sessions(ip_address);
CREATE INDEX idx_chat_sessions_active ON chat_sessions(is_active);
CREATE INDEX idx_chat_sessions_started ON chat_sessions(started_at);
CREATE INDEX idx_chat_messages_session ON chat_messages(session_id);
CREATE INDEX idx_user_settings_user ON user_settings(user_id);
CREATE INDEX idx_faqs_website ON faqs(website_id);
CREATE INDEX idx_unanswered_questions_website ON unanswered_questions(website_id);
CREATE INDEX idx_contact_requests_website ON contact_requests(website_id);
CREATE INDEX idx_user_query_limits_website ON user_query_limits(website_id);