-- ==============================================================================
-- AI-FLOW DATABASE SCHEMA
-- ==============================================================================
-- SQLite schema (PostgreSQL-compatible for easy migration)
-- ==============================================================================

-- Leads table: Main table for storing lead information
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS leads (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  whatsapp_number TEXT UNIQUE NOT NULL,
  name TEXT,
  email TEXT,
  language TEXT CHECK(language IN ('pt', 'es', 'en')) DEFAULT 'pt',
  budget TEXT CHECK(budget IN ('0-500', '500-2000', '2000+', 'not_specified')) DEFAULT 'not_specified',
  use_case TEXT CHECK(use_case IN ('design_ai', 'esim_iot', 'sales_support', 'customer_support', 'other')) DEFAULT 'other',
  status TEXT DEFAULT 'new' CHECK(status IN ('new', 'contacted', 'qualified', 'converted', 'lost', 'archived')),

  -- CRM Integration
  hubspot_contact_id TEXT,
  hubspot_deal_id TEXT,

  -- Session tracking
  session_started_at DATETIME,
  last_message_at DATETIME,
  session_active BOOLEAN DEFAULT 1,

  -- Lead source tracking
  utm_source TEXT,
  utm_medium TEXT,
  utm_campaign TEXT,
  utm_content TEXT,
  ad_id TEXT,

  -- Follow-up tracking
  follow_up_24h_sent BOOLEAN DEFAULT 0,
  follow_up_48h_sent BOOLEAN DEFAULT 0,
  follow_up_count INTEGER DEFAULT 0,

  -- Payment tracking
  payment_link_sent BOOLEAN DEFAULT 0,
  payment_completed BOOLEAN DEFAULT 0,

  -- Notes and metadata
  notes TEXT,
  metadata TEXT, -- JSON string for additional data

  -- Timestamps
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for leads table
CREATE INDEX IF NOT EXISTS idx_leads_whatsapp_number ON leads(whatsapp_number);
CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status);
CREATE INDEX IF NOT EXISTS idx_leads_session_active ON leads(session_active);
CREATE INDEX IF NOT EXISTS idx_leads_created_at ON leads(created_at);

-- Messages table: Store all chat interactions
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lead_id INTEGER NOT NULL REFERENCES leads(id) ON DELETE CASCADE,

  -- Message details
  direction TEXT CHECK(direction IN ('inbound', 'outbound')) NOT NULL,
  message_text TEXT,
  message_type TEXT DEFAULT 'text' CHECK(message_type IN ('text', 'template', 'button', 'image', 'document', 'audio', 'video')),

  -- WhatsApp specific
  whatsapp_message_id TEXT,
  template_name TEXT, -- If using WhatsApp template

  -- Message status
  status TEXT DEFAULT 'sent' CHECK(status IN ('pending', 'sent', 'delivered', 'read', 'failed')),
  error_message TEXT,

  -- Automation tracking
  is_automated BOOLEAN DEFAULT 1,
  triggered_by TEXT, -- 'welcome', 'follow_up_24h', 'follow_up_48h', 'manual'

  -- Metadata
  metadata TEXT, -- JSON string

  -- Timestamps
  sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  delivered_at DATETIME,
  read_at DATETIME
);

-- Indexes for messages table
CREATE INDEX IF NOT EXISTS idx_messages_lead_id ON messages(lead_id);
CREATE INDEX IF NOT EXISTS idx_messages_direction ON messages(direction);
CREATE INDEX IF NOT EXISTS idx_messages_sent_at ON messages(sent_at);

-- Chatbot States table: Track conversation state machine
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS chatbot_states (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lead_id INTEGER NOT NULL REFERENCES leads(id) ON DELETE CASCADE,

  -- State machine
  current_state TEXT DEFAULT 'welcome' CHECK(current_state IN (
    'welcome',
    'awaiting_budget',
    'awaiting_use_case',
    'awaiting_confirmation',
    'awaiting_payment_decision',
    'completed',
    'expired',
    'transferred_to_agent'
  )),
  previous_state TEXT,

  -- Collected data (temporary storage during conversation)
  collected_data TEXT, -- JSON string

  -- State timestamps
  state_changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for chatbot_states table
CREATE INDEX IF NOT EXISTS idx_chatbot_states_lead_id ON chatbot_states(lead_id);
CREATE INDEX IF NOT EXISTS idx_chatbot_states_current_state ON chatbot_states(current_state);

-- Analytics Events table: Track all important events for reporting
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS analytics_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lead_id INTEGER REFERENCES leads(id) ON DELETE SET NULL,

  -- Event details
  event_type TEXT NOT NULL CHECK(event_type IN (
    'ad_click',
    'chat_initiated',
    'budget_selected',
    'use_case_selected',
    'lead_qualified',
    'payment_link_clicked',
    'payment_completed',
    'session_expired',
    'transferred_to_agent',
    'follow_up_sent',
    'follow_up_opened'
  )),
  event_category TEXT, -- 'acquisition', 'engagement', 'conversion', 'retention'

  -- Event metadata
  event_data TEXT, -- JSON string with event-specific data

  -- Attribution
  utm_source TEXT,
  utm_medium TEXT,
  utm_campaign TEXT,

  -- Timestamp
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for analytics_events table
CREATE INDEX IF NOT EXISTS idx_analytics_events_event_type ON analytics_events(event_type);
CREATE INDEX IF NOT EXISTS idx_analytics_events_lead_id ON analytics_events(lead_id);
CREATE INDEX IF NOT EXISTS idx_analytics_events_created_at ON analytics_events(created_at);

-- System Settings table: Store application configuration
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS system_settings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  setting_key TEXT UNIQUE NOT NULL,
  setting_value TEXT,
  setting_type TEXT DEFAULT 'string' CHECK(setting_type IN ('string', 'number', 'boolean', 'json')),
  description TEXT,

  -- Timestamps
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for system_settings table
CREATE INDEX IF NOT EXISTS idx_system_settings_setting_key ON system_settings(setting_key);

-- Insert default system settings
INSERT OR IGNORE INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
  ('app_version', '1.0.0', 'string', 'Current application version'),
  ('whatsapp_connected', 'false', 'boolean', 'WhatsApp connection status'),
  ('hubspot_connected', 'false', 'boolean', 'HubSpot connection status'),
  ('stripe_connected', 'false', 'boolean', 'Stripe connection status'),
  ('auto_follow_up_enabled', 'true', 'boolean', 'Enable automatic follow-up messages'),
  ('session_timeout_hours', '72', 'number', 'Hours before chat session expires'),
  ('max_follow_ups', '3', 'number', 'Maximum number of follow-up messages'),
  ('default_language', 'pt', 'string', 'Default conversation language');

-- ==============================================================================
-- TRIGGERS FOR UPDATED_AT TIMESTAMPS
-- ==============================================================================

-- Trigger for leads table
CREATE TRIGGER IF NOT EXISTS update_leads_timestamp
AFTER UPDATE ON leads
BEGIN
  UPDATE leads SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;
END;
END;

-- Trigger for system_settings table
CREATE TRIGGER IF NOT EXISTS update_system_settings_timestamp
AFTER UPDATE ON system_settings
BEGIN
  UPDATE system_settings SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- ==============================================================================
-- VIEWS FOR ANALYTICS
-- ==============================================================================

-- Active leads view (session not expired)
CREATE VIEW IF NOT EXISTS v_active_leads AS
SELECT
  l.*,
  (julianday('now') - julianday(l.session_started_at)) * 24 as hours_since_start,
  (julianday('now') - julianday(l.last_message_at)) * 24 as hours_since_last_message
FROM leads l
WHERE l.session_active = 1
  AND ((julianday('now') - julianday(l.session_started_at)) * 24) <= 72;

-- ==============================================================================
-- END OF SCHEMA
-- ==============================================================================
