database/schema.sql

-- CX AI Agent - Enterprise Database Schema
-- SQLite Schema for Campaign Management, Contact Tracking, and Analytics

-- =============================================================================
-- COMPANIES
-- =============================================================================
CREATE TABLE IF NOT EXISTS companies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    domain TEXT UNIQUE,
    industry TEXT,
    size TEXT,
    revenue TEXT,
    location TEXT,
    description TEXT,
    pain_points TEXT, -- JSON array
    website TEXT,
    linkedin_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_companies_domain ON companies(domain);
CREATE INDEX idx_companies_industry ON companies(industry);

-- =============================================================================
-- CONTACTS
-- =============================================================================
CREATE TABLE IF NOT EXISTS contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    company_id INTEGER,
    first_name TEXT,
    last_name TEXT,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    job_title TEXT,
    department TEXT,
    seniority_level TEXT, -- C-Level, VP, Director, Manager, Individual Contributor
    linkedin_url TEXT,
    twitter_url TEXT,
    location TEXT,
    timezone TEXT,

    -- Scoring
    fit_score REAL DEFAULT 0.0,
    engagement_score REAL DEFAULT 0.0,
    intent_score REAL DEFAULT 0.0,
    overall_score REAL DEFAULT 0.0,

    -- Status & Lifecycle
    status TEXT DEFAULT 'new', -- new, contacted, responded, meeting_scheduled, qualified, lost, customer
    lifecycle_stage TEXT DEFAULT 'lead', -- lead, mql, sql, opportunity, customer, churned

    -- Tracking
    source TEXT, -- discovery_agent, manual_import, api, referral
    first_contacted_at TIMESTAMP,
    last_contacted_at TIMESTAMP,
    last_activity_at TIMESTAMP,

    -- Metadata
    tags TEXT, -- JSON array
    notes TEXT,
    custom_fields TEXT, -- JSON object for extensibility
    is_suppressed BOOLEAN DEFAULT 0,
    suppression_reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
);

CREATE INDEX idx_contacts_email ON contacts(email);
CREATE INDEX idx_contacts_company ON contacts(company_id);
CREATE INDEX idx_contacts_status ON contacts(status);
CREATE INDEX idx_contacts_lifecycle_stage ON contacts(lifecycle_stage);
CREATE INDEX idx_contacts_overall_score ON contacts(overall_score);

-- =============================================================================
-- CAMPAIGNS
-- =============================================================================
CREATE TABLE IF NOT EXISTS campaigns (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'draft', -- draft, active, paused, completed, archived

    -- Targeting
    target_industries TEXT, -- JSON array
    target_company_sizes TEXT, -- JSON array
    target_locations TEXT, -- JSON array
    target_job_titles TEXT, -- JSON array

    -- Configuration
    sequence_id INTEGER,
    goal_contacts INTEGER,
    goal_response_rate REAL,
    goal_meetings INTEGER,

    -- Tracking
    contacts_discovered INTEGER DEFAULT 0,
    contacts_enriched INTEGER DEFAULT 0,
    contacts_scored INTEGER DEFAULT 0,
    contacts_contacted INTEGER DEFAULT 0,
    contacts_responded INTEGER DEFAULT 0,
    meetings_booked INTEGER DEFAULT 0,

    -- Dates
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT,

    FOREIGN KEY (sequence_id) REFERENCES sequences(id) ON DELETE SET NULL
);

CREATE INDEX idx_campaigns_status ON campaigns(status);

-- =============================================================================
-- CAMPAIGN CONTACTS (Many-to-Many with Stage Tracking)
-- =============================================================================
CREATE TABLE IF NOT EXISTS campaign_contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id INTEGER NOT NULL,
    contact_id INTEGER NOT NULL,
    stage TEXT DEFAULT 'discovery', -- discovery, enrichment, scoring, outreach, responded, meeting, closed_won, closed_lost
    stage_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,

    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    UNIQUE(campaign_id, contact_id)
);

CREATE INDEX idx_campaign_contacts_campaign ON campaign_contacts(campaign_id);
CREATE INDEX idx_campaign_contacts_contact ON campaign_contacts(contact_id);
CREATE INDEX idx_campaign_contacts_stage ON campaign_contacts(stage);

-- =============================================================================
-- EMAIL SEQUENCES
-- =============================================================================
CREATE TABLE IF NOT EXISTS sequences (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    category TEXT DEFAULT 'outbound', -- outbound, nurture, re-engagement
    is_active BOOLEAN DEFAULT 1,
    is_template BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT
);

-- =============================================================================
-- SEQUENCE EMAILS (Steps in a sequence)
-- =============================================================================
CREATE TABLE IF NOT EXISTS sequence_emails (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sequence_id INTEGER NOT NULL,
    step_number INTEGER NOT NULL,
    wait_days INTEGER DEFAULT 0, -- Days to wait after previous email
    subject TEXT NOT NULL,
    body TEXT NOT NULL,
    send_time_preference TEXT, -- morning, afternoon, evening, or specific time
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (sequence_id) REFERENCES sequences(id) ON DELETE CASCADE,
    UNIQUE(sequence_id, step_number)
);

CREATE INDEX idx_sequence_emails_sequence ON sequence_emails(sequence_id);

-- =============================================================================
-- EMAIL ACTIVITIES (Tracking email interactions)
-- =============================================================================
CREATE TABLE IF NOT EXISTS email_activities (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    contact_id INTEGER NOT NULL,
    campaign_id INTEGER,
    sequence_email_id INTEGER,
    type TEXT NOT NULL, -- sent, delivered, opened, clicked, replied, bounced, unsubscribed, complained
    subject TEXT,
    preview TEXT,
    link_url TEXT, -- For click tracking
    meta_data TEXT, -- JSON for additional data
    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    FOREIGN KEY (sequence_email_id) REFERENCES sequence_emails(id) ON DELETE SET NULL
);

CREATE INDEX idx_email_activities_contact ON email_activities(contact_id);
CREATE INDEX idx_email_activities_campaign ON email_activities(campaign_id);
CREATE INDEX idx_email_activities_type ON email_activities(type);
CREATE INDEX idx_email_activities_occurred ON email_activities(occurred_at);

-- =============================================================================
-- MEETINGS
-- =============================================================================
CREATE TABLE IF NOT EXISTS meetings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    contact_id INTEGER NOT NULL,
    campaign_id INTEGER,
    title TEXT NOT NULL,
    description TEXT,
    scheduled_at TIMESTAMP NOT NULL,
    duration_minutes INTEGER DEFAULT 30,
    meeting_url TEXT,
    location TEXT,
    status TEXT DEFAULT 'scheduled', -- scheduled, completed, cancelled, no_show, rescheduled
    outcome TEXT, -- interested, not_interested, needs_follow_up, closed_won
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL
);

CREATE INDEX idx_meetings_contact ON meetings(contact_id);
CREATE INDEX idx_meetings_campaign ON meetings(campaign_id);
CREATE INDEX idx_meetings_scheduled ON meetings(scheduled_at);
CREATE INDEX idx_meetings_status ON meetings(status);

-- =============================================================================
-- ACTIVITIES (General activity log)
-- =============================================================================
CREATE TABLE IF NOT EXISTS activities (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    contact_id INTEGER,
    campaign_id INTEGER,
    meeting_id INTEGER,
    type TEXT NOT NULL, -- discovery, enrichment, email_sent, email_opened, reply_received, meeting_scheduled, meeting_completed, note_added, status_changed
    description TEXT,
    meta_data TEXT, -- JSON for additional context
    performed_by TEXT, -- agent_name or 'user'
    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE SET NULL
);

CREATE INDEX idx_activities_contact ON activities(contact_id);
CREATE INDEX idx_activities_campaign ON activities(campaign_id);
CREATE INDEX idx_activities_type ON activities(type);
CREATE INDEX idx_activities_occurred ON activities(occurred_at);

-- =============================================================================
-- AB TESTS (for email sequences)
-- =============================================================================
CREATE TABLE IF NOT EXISTS ab_tests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id INTEGER NOT NULL,
    sequence_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    test_type TEXT NOT NULL, -- subject_line, body, send_time, from_name
    variant_a TEXT NOT NULL, -- JSON configuration
    variant_b TEXT NOT NULL, -- JSON configuration
    winner TEXT, -- 'a', 'b', or null if test ongoing
    status TEXT DEFAULT 'running', -- running, completed, cancelled
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,

    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (sequence_id) REFERENCES sequences(id) ON DELETE CASCADE
);

-- =============================================================================
-- AB TEST RESULTS
-- =============================================================================
CREATE TABLE IF NOT EXISTS ab_test_results (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ab_test_id INTEGER NOT NULL,
    variant TEXT NOT NULL, -- 'a' or 'b'
    emails_sent INTEGER DEFAULT 0,
    emails_delivered INTEGER DEFAULT 0,
    emails_opened INTEGER DEFAULT 0,
    emails_clicked INTEGER DEFAULT 0,
    emails_replied INTEGER DEFAULT 0,
    meetings_booked INTEGER DEFAULT 0,

    FOREIGN KEY (ab_test_id) REFERENCES ab_tests(id) ON DELETE CASCADE,
    UNIQUE(ab_test_id, variant)
);

-- =============================================================================
-- TEMPLATES (Email templates)
-- =============================================================================
CREATE TABLE IF NOT EXISTS templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT, -- cold_outreach, follow_up, meeting_request, thank_you
    subject TEXT NOT NULL,
    body TEXT NOT NULL,
    variables TEXT, -- JSON array of variable names
    is_active BOOLEAN DEFAULT 1,
    usage_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- ANALYTICS SNAPSHOTS (Daily/hourly aggregated metrics)
-- =============================================================================
CREATE TABLE IF NOT EXISTS analytics_snapshots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id INTEGER,
    date DATE NOT NULL,
    hour INTEGER, -- null for daily snapshots

    -- Metrics
    contacts_discovered INTEGER DEFAULT 0,
    contacts_enriched INTEGER DEFAULT 0,
    emails_sent INTEGER DEFAULT 0,
    emails_opened INTEGER DEFAULT 0,
    emails_clicked INTEGER DEFAULT 0,
    emails_replied INTEGER DEFAULT 0,
    meetings_booked INTEGER DEFAULT 0,

    -- Rates
    open_rate REAL DEFAULT 0.0,
    click_rate REAL DEFAULT 0.0,
    response_rate REAL DEFAULT 0.0,
    meeting_rate REAL DEFAULT 0.0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    UNIQUE(campaign_id, date, hour)
);

CREATE INDEX idx_analytics_campaign ON analytics_snapshots(campaign_id);
CREATE INDEX idx_analytics_date ON analytics_snapshots(date);

-- =============================================================================
-- SETTINGS (Application configuration)
-- =============================================================================
CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT OR IGNORE INTO settings (key, value, description) VALUES
    ('company_name', 'Your Company', 'Company name for email footers'),
    ('company_address', '123 Main St, City, State 12345', 'Physical address for CAN-SPAM compliance'),
    ('sender_name', 'Sales Team', 'Default sender name for emails'),
    ('sender_email', '[email protected]', 'Default sender email'),
    ('daily_email_limit', '1000', 'Maximum emails to send per day'),
    ('enable_tracking', '1', 'Enable email open and click tracking'),
    ('auto_pause_on_low_score', '1', 'Automatically pause contacts with low engagement'),
    ('min_engagement_score', '0.3', 'Minimum engagement score before auto-pause');