database/schema_extended.sql

-- CX Platform - Extended Database Schema
-- Adds tickets, knowledge base, chat, and customer interaction tracking

-- =============================================================================
-- CUSTOMERS (Enhanced from contacts)
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    first_name TEXT,
    last_name TEXT,
    company TEXT,
    phone TEXT,

    -- Segmentation
    segment TEXT DEFAULT 'standard', -- vip, standard, at_risk, churned
    lifecycle_stage TEXT DEFAULT 'active', -- new, active, at_risk, churned

    -- Metrics
    lifetime_value REAL DEFAULT 0.0,
    satisfaction_score REAL DEFAULT 0.0, -- CSAT average
    nps_score INTEGER, -- Net Promoter Score
    sentiment TEXT DEFAULT 'neutral', -- positive, neutral, negative

    -- Tracking
    first_interaction_at TIMESTAMP,
    last_interaction_at TIMESTAMP,
    total_interactions INTEGER DEFAULT 0,
    total_tickets INTEGER DEFAULT 0,

    -- Metadata
    tags TEXT, -- JSON array
    custom_fields TEXT, -- JSON object
    notes TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_cx_customers_email ON cx_customers(email);
CREATE INDEX idx_cx_customers_segment ON cx_customers(segment);
CREATE INDEX idx_cx_customers_sentiment ON cx_customers(sentiment);

-- =============================================================================
-- TICKETS
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_tickets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,

    -- Core fields
    subject TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'new', -- new, open, pending, resolved, closed
    priority TEXT DEFAULT 'medium', -- low, medium, high, urgent
    category TEXT, -- technical, billing, feature_request, etc.

    -- Assignment
    assigned_to TEXT, -- agent name/id
    assigned_team TEXT,

    -- SLA
    sla_due_at TIMESTAMP,
    first_response_at TIMESTAMP,
    resolved_at TIMESTAMP,
    closed_at TIMESTAMP,

    -- Metrics
    response_time_minutes INTEGER,
    resolution_time_minutes INTEGER,
    reopened_count INTEGER DEFAULT 0,

    -- AI fields
    sentiment TEXT, -- detected from description
    ai_suggested_category TEXT,
    ai_confidence REAL,
    auto_resolved BOOLEAN DEFAULT 0,

    -- Metadata
    source TEXT DEFAULT 'manual', -- manual, email, chat, api, web_form
    tags TEXT, -- JSON array
    custom_fields TEXT, -- JSON

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (customer_id) REFERENCES cx_customers(id) ON DELETE CASCADE
);

CREATE INDEX idx_cx_tickets_customer ON cx_tickets(customer_id);
CREATE INDEX idx_cx_tickets_status ON cx_tickets(status);
CREATE INDEX idx_cx_tickets_priority ON cx_tickets(priority);
CREATE INDEX idx_cx_tickets_assigned_to ON cx_tickets(assigned_to);
CREATE INDEX idx_cx_tickets_sla_due ON cx_tickets(sla_due_at);

-- =============================================================================
-- TICKET MESSAGES
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_ticket_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticket_id INTEGER NOT NULL,

    -- Sender
    sender_type TEXT NOT NULL, -- customer, agent, system, ai_bot
    sender_id TEXT, -- customer_id, agent_id, or 'system'
    sender_name TEXT,

    -- Message
    message TEXT NOT NULL,
    message_html TEXT,
    is_internal BOOLEAN DEFAULT 0, -- internal note vs customer-visible

    -- AI fields
    sentiment TEXT,
    intent TEXT, -- question, complaint, praise, feedback

    -- Metadata
    meta_data TEXT, -- JSON

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (ticket_id) REFERENCES cx_tickets(id) ON DELETE CASCADE
);

CREATE INDEX idx_cx_ticket_messages_ticket ON cx_ticket_messages(ticket_id);
CREATE INDEX idx_cx_ticket_messages_created ON cx_ticket_messages(created_at);

-- =============================================================================
-- TICKET ATTACHMENTS
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_ticket_attachments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticket_id INTEGER NOT NULL,
    message_id INTEGER,

    filename TEXT NOT NULL,
    file_path TEXT NOT NULL,
    file_size INTEGER,
    mime_type TEXT,

    uploaded_by TEXT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (ticket_id) REFERENCES cx_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (message_id) REFERENCES cx_ticket_messages(id) ON DELETE SET NULL
);

-- =============================================================================
-- KNOWLEDGE BASE
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_kb_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    parent_id INTEGER,
    display_order INTEGER DEFAULT 0,
    icon TEXT,

    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (parent_id) REFERENCES cx_kb_categories(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS cx_kb_articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,

    -- Content
    title TEXT NOT NULL,
    summary TEXT,
    content TEXT NOT NULL,
    content_html TEXT,

    -- Status
    status TEXT DEFAULT 'draft', -- draft, published, archived
    visibility TEXT DEFAULT 'public', -- public, internal, private

    -- SEO
    slug TEXT UNIQUE,
    meta_description TEXT,

    -- Metrics
    view_count INTEGER DEFAULT 0,
    helpful_count INTEGER DEFAULT 0,
    not_helpful_count INTEGER DEFAULT 0,
    average_rating REAL DEFAULT 0.0,

    -- AI fields
    ai_generated BOOLEAN DEFAULT 0,
    ai_confidence REAL,
    keywords TEXT, -- JSON array for semantic search

    -- Versioning
    version INTEGER DEFAULT 1,

    -- Metadata
    tags TEXT, -- JSON array
    related_articles TEXT, -- JSON array of article IDs

    -- Authoring
    author TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP,

    FOREIGN KEY (category_id) REFERENCES cx_kb_categories(id) ON DELETE SET NULL
);

CREATE INDEX idx_cx_kb_articles_category ON cx_kb_articles(category_id);
CREATE INDEX idx_cx_kb_articles_status ON cx_kb_articles(status);
CREATE INDEX idx_cx_kb_articles_slug ON cx_kb_articles(slug);

-- =============================================================================
-- KB ARTICLE VERSIONS
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_kb_article_versions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id INTEGER NOT NULL,

    version INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,

    changed_by TEXT,
    change_note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (article_id) REFERENCES cx_kb_articles(id) ON DELETE CASCADE,
    UNIQUE(article_id, version)
);

-- =============================================================================
-- LIVE CHAT SESSIONS
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_chat_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,

    -- Session info
    session_id TEXT UNIQUE NOT NULL,
    status TEXT DEFAULT 'active', -- active, waiting, assigned, closed

    -- Routing
    assigned_to TEXT, -- agent name/id
    assigned_at TIMESTAMP,

    -- AI bot
    bot_active BOOLEAN DEFAULT 1,
    bot_handed_off BOOLEAN DEFAULT 0,
    bot_handoff_reason TEXT,

    -- Metrics
    wait_time_seconds INTEGER DEFAULT 0,
    response_time_seconds INTEGER DEFAULT 0,
    message_count INTEGER DEFAULT 0,

    -- Metadata
    page_url TEXT,
    referrer TEXT,
    user_agent TEXT,
    ip_address TEXT,

    -- Satisfaction
    rated BOOLEAN DEFAULT 0,
    rating INTEGER,
    feedback TEXT,

    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP,

    FOREIGN KEY (customer_id) REFERENCES cx_customers(id) ON DELETE SET NULL
);

CREATE INDEX idx_cx_chat_sessions_customer ON cx_chat_sessions(customer_id);
CREATE INDEX idx_cx_chat_sessions_status ON cx_chat_sessions(status);
CREATE INDEX idx_cx_chat_sessions_assigned_to ON cx_chat_sessions(assigned_to);

-- =============================================================================
-- CHAT MESSAGES
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_chat_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id INTEGER NOT NULL,

    -- Sender
    sender_type TEXT NOT NULL, -- customer, agent, bot, system
    sender_id TEXT,
    sender_name TEXT,

    -- Message
    message TEXT NOT NULL,
    message_type TEXT DEFAULT 'text', -- text, image, file, system_message

    -- AI fields
    is_bot_response BOOLEAN DEFAULT 0,
    bot_confidence REAL,
    intent TEXT,

    -- Status
    is_read BOOLEAN DEFAULT 0,
    read_at TIMESTAMP,

    -- Metadata
    meta_data TEXT, -- JSON

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (session_id) REFERENCES cx_chat_sessions(id) ON DELETE CASCADE
);

CREATE INDEX idx_cx_chat_messages_session ON cx_chat_messages(session_id);
CREATE INDEX idx_cx_chat_messages_created ON cx_chat_messages(created_at);

-- =============================================================================
-- AUTOMATION RULES
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_automation_rules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT 1,

    -- Trigger
    trigger_type TEXT NOT NULL, -- ticket_created, ticket_updated, time_based, etc.
    trigger_conditions TEXT NOT NULL, -- JSON

    -- Actions
    actions TEXT NOT NULL, -- JSON array of actions

    -- Execution
    execution_count INTEGER DEFAULT 0,
    last_executed_at TIMESTAMP,

    -- Priority
    priority INTEGER DEFAULT 0,

    created_by TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- CUSTOMER INTERACTIONS
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_interactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,

    type TEXT NOT NULL, -- ticket, chat, email, call, meeting
    channel TEXT, -- web, email, phone, chat, api

    summary TEXT,
    sentiment TEXT,
    intent TEXT,

    -- References
    reference_type TEXT, -- ticket, chat_session, email, etc.
    reference_id INTEGER,

    -- Metrics
    duration_seconds INTEGER,
    satisfaction_rating INTEGER,

    -- Agent
    handled_by TEXT,

    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (customer_id) REFERENCES cx_customers(id) ON DELETE CASCADE
);

CREATE INDEX idx_cx_interactions_customer ON cx_interactions(customer_id);
CREATE INDEX idx_cx_interactions_type ON cx_interactions(type);
CREATE INDEX idx_cx_interactions_occurred ON cx_interactions(occurred_at);

-- =============================================================================
-- ANALYTICS SNAPSHOTS (Enhanced)
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_analytics_daily (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date DATE NOT NULL UNIQUE,

    -- Ticket metrics
    tickets_created INTEGER DEFAULT 0,
    tickets_resolved INTEGER DEFAULT 0,
    tickets_reopened INTEGER DEFAULT 0,
    avg_resolution_time_minutes REAL DEFAULT 0.0,
    avg_first_response_minutes REAL DEFAULT 0.0,

    -- Chat metrics
    chats_started INTEGER DEFAULT 0,
    chats_completed INTEGER DEFAULT 0,
    avg_wait_time_seconds REAL DEFAULT 0.0,
    bot_resolution_rate REAL DEFAULT 0.0,

    -- Satisfaction
    avg_csat REAL DEFAULT 0.0,
    avg_nps INTEGER DEFAULT 0,

    -- KB metrics
    kb_views INTEGER DEFAULT 0,
    kb_helpful_votes INTEGER DEFAULT 0,
    kb_searches INTEGER DEFAULT 0,

    -- Sentiment
    positive_interactions INTEGER DEFAULT 0,
    neutral_interactions INTEGER DEFAULT 0,
    negative_interactions INTEGER DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_cx_analytics_daily_date ON cx_analytics_daily(date);

-- =============================================================================
-- CANNED RESPONSES (Templates)
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_canned_responses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL,
    shortcut TEXT UNIQUE, -- e.g., "/greeting"
    category TEXT,

    subject TEXT,
    content TEXT NOT NULL,

    -- Usage
    use_count INTEGER DEFAULT 0,
    last_used_at TIMESTAMP,

    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- AGENT PERFORMANCE
-- =============================================================================
CREATE TABLE IF NOT EXISTS cx_agent_stats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    agent_name TEXT NOT NULL,
    date DATE NOT NULL,

    -- Tickets
    tickets_handled INTEGER DEFAULT 0,
    tickets_resolved INTEGER DEFAULT 0,
    avg_resolution_time_minutes REAL DEFAULT 0.0,

    -- Chats
    chats_handled INTEGER DEFAULT 0,
    avg_chat_duration_minutes REAL DEFAULT 0.0,

    -- Quality
    avg_csat REAL DEFAULT 0.0,
    positive_feedbacks INTEGER DEFAULT 0,
    negative_feedbacks INTEGER DEFAULT 0,

    -- Efficiency
    avg_response_time_minutes REAL DEFAULT 0.0,
    first_contact_resolutions INTEGER DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(agent_id, date)
);

CREATE INDEX idx_cx_agent_stats_agent ON cx_agent_stats(agent_id);
CREATE INDEX idx_cx_agent_stats_date ON cx_agent_stats(date);