-- ==============================================================================
-- 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);

-- Payments table: Track all payment transactions
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lead_id INTEGER NOT NULL REFERENCES leads(id) ON DELETE CASCADE,

  -- Stripe details
  stripe_session_id TEXT UNIQUE,
  stripe_payment_intent_id TEXT,
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT,

  -- Payment details
  amount INTEGER NOT NULL, -- Amount in cents
  currency TEXT DEFAULT 'BRL' CHECK(currency IN ('BRL', 'USD', 'ARS', 'EUR')),
  plan_type TEXT CHECK(plan_type IN ('basic', 'pro', 'enterprise', 'custom')),
  billing_cycle TEXT DEFAULT 'monthly' CHECK(billing_cycle IN ('monthly', 'quarterly', 'yearly', 'one_time')),

  -- Status tracking
  status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'processing', 'paid', 'failed', 'refunded', 'cancelled')),

  -- Payment method
  payment_method TEXT, -- 'card', 'boleto', 'pix', etc.
  last_four TEXT, -- Last 4 digits of card

  -- Dates
  paid_at DATETIME,
  refunded_at DATETIME,
  subscription_start_date DATETIME,
  subscription_end_date DATETIME,

  -- Metadata
  failure_reason TEXT,
  metadata TEXT, -- JSON string

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

-- Indexes for payments table
CREATE INDEX IF NOT EXISTS idx_payments_lead_id ON payments(lead_id);
CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status);
CREATE INDEX IF NOT EXISTS idx_payments_stripe_session_id ON payments(stripe_session_id);

-- 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);

-- Admin Users table: For dashboard access control
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  whatsapp_number TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  email TEXT,
  role TEXT DEFAULT 'agent' CHECK(role IN ('superadmin', 'admin', 'agent', 'viewer')),
  is_active BOOLEAN DEFAULT 1,

  -- Permissions
  can_view_leads BOOLEAN DEFAULT 1,
  can_edit_leads BOOLEAN DEFAULT 0,
  can_send_messages BOOLEAN DEFAULT 0,
  can_access_analytics BOOLEAN DEFAULT 0,

  -- Session management
  last_login_at DATETIME,
  password_hash TEXT, -- For web dashboard login

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

-- Indexes for admin_users table
CREATE INDEX IF NOT EXISTS idx_admin_users_whatsapp_number ON admin_users(whatsapp_number);

-- 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;

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

-- Trigger for admin_users table
CREATE TRIGGER IF NOT EXISTS update_admin_users_timestamp
AFTER UPDATE ON admin_users
BEGIN
  UPDATE admin_users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
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;

-- Conversion funnel view
CREATE VIEW IF NOT EXISTS v_conversion_funnel AS
SELECT
  COUNT(*) as total_leads,
  SUM(CASE WHEN budget != 'not_specified' THEN 1 ELSE 0 END) as budget_selected,
  SUM(CASE WHEN use_case != 'other' THEN 1 ELSE 0 END) as use_case_selected,
  SUM(CASE WHEN status = 'qualified' THEN 1 ELSE 0 END) as qualified_leads,
  SUM(CASE WHEN payment_link_sent = 1 THEN 1 ELSE 0 END) as payment_link_sent,
  SUM(CASE WHEN payment_completed = 1 THEN 1 ELSE 0 END) as payment_completed
FROM leads;

-- Daily analytics view
CREATE VIEW IF NOT EXISTS v_daily_analytics AS
SELECT
  DATE(created_at) as date,
  COUNT(*) as new_leads,
  SUM(CASE WHEN status = 'qualified' THEN 1 ELSE 0 END) as qualified_leads,
  SUM(CASE WHEN payment_completed = 1 THEN 1 ELSE 0 END) as conversions,
  AVG(CASE WHEN payment_completed = 1 THEN amount ELSE NULL END) / 100.0 as avg_payment_value
FROM leads
GROUP BY DATE(created_at)
ORDER BY date DESC;

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