-- RateRight Growth Engine Database Schema
-- Run this in Supabase SQL Editor

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Leads table
CREATE TABLE IF NOT EXISTS leads (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(255),
  phone VARCHAR(20),
  company VARCHAR(255),
  source VARCHAR(50) DEFAULT 'manual',
  status VARCHAR(50) DEFAULT 'new',
  score INTEGER DEFAULT 0,
  assigned_to UUID,
  last_contact_at TIMESTAMPTZ,
  last_contact_type VARCHAR(50),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

-- Communications table (SMS, calls, emails)
CREATE TABLE IF NOT EXISTS communications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id),
  type VARCHAR(50) NOT NULL,
  direction VARCHAR(20) NOT NULL,
  phone VARCHAR(20),
  content TEXT,
  external_id VARCHAR(100),
  delivery_status VARCHAR(50),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Lead notes
CREATE TABLE IF NOT EXISTS lead_notes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id),
  content TEXT NOT NULL,
  created_by VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Platform activity (synced from RateRight)
CREATE TABLE IF NOT EXISTS platform_activity (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id),
  activity_type VARCHAR(50) NOT NULL,
  description TEXT,
  activity_at TIMESTAMPTZ NOT NULL,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Callbacks (pending follow-ups)
CREATE TABLE IF NOT EXISTS callbacks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id),
  phone VARCHAR(20),
  reason TEXT,
  priority VARCHAR(20) DEFAULT 'medium',
  status VARCHAR(20) DEFAULT 'pending',
  source_type VARCHAR(50),
  source_id UUID,
  notes TEXT,
  scheduled_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  handled_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Conversions (win/loss tracking)
CREATE TABLE IF NOT EXISTS conversions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id),
  type VARCHAR(50) NOT NULL,
  value DECIMAL(10,2),
  notes TEXT,
  converted_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Daily metrics snapshots
CREATE TABLE IF NOT EXISTS daily_metrics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  date DATE NOT NULL UNIQUE,
  calls_made INTEGER DEFAULT 0,
  sms_sent INTEGER DEFAULT 0,
  sms_received INTEGER DEFAULT 0,
  new_leads INTEGER DEFAULT 0,
  conversions INTEGER DEFAULT 0,
  hot_leads INTEGER DEFAULT 0,
  pending_callbacks INTEGER DEFAULT 0,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Team members
CREATE TABLE IF NOT EXISTS team_members (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  role VARCHAR(50),
  phone VARCHAR(20),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- SMS templates
CREATE TABLE IF NOT EXISTS sms_templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  slug VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  description TEXT,
  variables JSONB DEFAULT '[]',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Sequences (automation)
CREATE TABLE IF NOT EXISTS sequences (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL,
  description TEXT,
  trigger_type VARCHAR(50),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Sequence steps
CREATE TABLE IF NOT EXISTS sequence_steps (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  sequence_id UUID REFERENCES sequences(id),
  step_order INTEGER NOT NULL,
  action_type VARCHAR(50) NOT NULL,
  delay_hours INTEGER DEFAULT 0,
  template_id UUID REFERENCES sms_templates(id),
  config JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Sequence enrollments
CREATE TABLE IF NOT EXISTS sequence_enrollments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  sequence_id UUID REFERENCES sequences(id),
  lead_id UUID REFERENCES leads(id),
  current_step INTEGER DEFAULT 0,
  status VARCHAR(20) DEFAULT 'active',
  enrolled_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  paused_at TIMESTAMPTZ
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_leads_phone ON leads(phone);
CREATE INDEX IF NOT EXISTS idx_leads_email ON leads(email);
CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status);
CREATE INDEX IF NOT EXISTS idx_leads_score ON leads(score);
CREATE INDEX IF NOT EXISTS idx_leads_deleted_at ON leads(deleted_at);
CREATE INDEX IF NOT EXISTS idx_communications_lead_id ON communications(lead_id);
CREATE INDEX IF NOT EXISTS idx_communications_external_id ON communications(external_id);
CREATE INDEX IF NOT EXISTS idx_callbacks_lead_id ON callbacks(lead_id);
CREATE INDEX IF NOT EXISTS idx_callbacks_status ON callbacks(status);
CREATE INDEX IF NOT EXISTS idx_platform_activity_lead_id ON platform_activity(lead_id);

-- Insert team members
INSERT INTO team_members (name, email, role, phone) VALUES
  ('Tony McCabe', 'tony@rateright.com.au', 'Sales Director', '+61400000001'),
  ('Angelica', 'angelica@rateright.com.au', 'GM Remote Operations', '+61400000002'),
  ('Michael', 'michael@rateright.com.au', 'Founder', '+61400000003')
ON CONFLICT (email) DO NOTHING;

-- Insert default SMS templates
INSERT INTO sms_templates (slug, name, content, variables) VALUES
  ('welcome', 'Welcome Message', 'Hi {{first_name}}, thanks for joining RateRight! Ready to find quality workers for your business? Reply YES to chat with our team.', '["first_name"]'),
  ('follow_up_1', 'First Follow-up', 'Hi {{first_name}}, just checking in from RateRight. Have you had a chance to post your first job? Our team can help - reply CALL for a quick chat.', '["first_name"]'),
  ('job_posted', 'Job Posted Congrats', 'Great news {{first_name}}! Your job is now live on RateRight. Workers are being notified. Questions? Reply here anytime.', '["first_name"]')
ON CONFLICT (slug) DO NOTHING;

-- Enable Row Level Security (optional, for production)
-- ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE communications ENABLE ROW LEVEL SECURITY;
