-- Combined Migrations for Sales Tools APIs
-- Run in Supabase SQL Editor in one go
-- Generated: 2026-02-02
-- Updated: 2026-02-02 - Added job_locks (PRIORITY)

-- =====================================================
-- MIGRATION 0 (PRIORITY): Job Locks Table
-- Prevents duplicate job execution (sequence processor etc.)
-- =====================================================

-- Create job_locks table to prevent duplicate job execution
CREATE TABLE IF NOT EXISTS job_locks (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  job_name TEXT NOT NULL UNIQUE,
  lock_id TEXT NOT NULL,
  acquired_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL,
  status TEXT NOT NULL DEFAULT 'acquired',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_job_locks_job_name ON job_locks(job_name);
CREATE INDEX IF NOT EXISTS idx_job_locks_expires_at ON job_locks(expires_at);

-- Add RLS policies
ALTER TABLE job_locks ENABLE ROW LEVEL SECURITY;

-- Only service role can manage job locks (drop if exists first)
DROP POLICY IF EXISTS "Service role full access" ON job_locks;
CREATE POLICY "Service role full access" ON job_locks
  FOR ALL USING (auth.role() = 'service_role');

-- Function to update updated_at timestamp (safe to replace)
CREATE OR REPLACE FUNCTION update_job_locks_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger to auto-update updated_at
DROP TRIGGER IF EXISTS update_job_locks_updated_at ON job_locks;
CREATE TRIGGER update_job_locks_updated_at BEFORE UPDATE
  ON job_locks FOR EACH ROW EXECUTE FUNCTION update_job_locks_updated_at_column();

-- =====================================================
-- MIGRATION 1: Trade Tags System
-- =====================================================

-- Trade tags table
CREATE TABLE IF NOT EXISTS trade_tags (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE, -- 'plumber', 'electrician'
  color TEXT DEFAULT '#6B7280', -- UI color
  lead_count INTEGER DEFAULT 0, -- Denormalized count
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Lead-tag junction table
CREATE TABLE IF NOT EXISTS lead_trade_tags (
  lead_id UUID NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  tag_id UUID NOT NULL REFERENCES trade_tags(id) ON DELETE CASCADE,
  assigned_at TIMESTAMPTZ DEFAULT NOW(),
  assigned_by UUID,
  PRIMARY KEY (lead_id, tag_id)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_lead_trade_tags_tag ON lead_trade_tags(tag_id);
CREATE INDEX IF NOT EXISTS idx_lead_trade_tags_lead ON lead_trade_tags(lead_id);
CREATE INDEX IF NOT EXISTS idx_trade_tags_slug ON trade_tags(slug);

-- Function to update lead count when tags are assigned/removed
CREATE OR REPLACE FUNCTION update_trade_tag_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE trade_tags SET lead_count = lead_count + 1, updated_at = NOW() WHERE id = NEW.tag_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE trade_tags SET lead_count = GREATEST(0, lead_count - 1), updated_at = NOW() WHERE id = OLD.tag_id;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Trigger for automatic count updates
DROP TRIGGER IF EXISTS trigger_update_trade_tag_count ON lead_trade_tags;
CREATE TRIGGER trigger_update_trade_tag_count
  AFTER INSERT OR DELETE ON lead_trade_tags
  FOR EACH ROW
  EXECUTE FUNCTION update_trade_tag_count();

-- Seed common trade tags
INSERT INTO trade_tags (name, slug, color) VALUES
  ('Plumber', 'plumber', '#3B82F6'),
  ('Electrician', 'electrician', '#F59E0B'),
  ('Carpenter', 'carpenter', '#8B5CF6'),
  ('Roofer', 'roofer', '#EF4444'),
  ('HVAC', 'hvac', '#10B981'),
  ('Painter', 'painter', '#EC4899'),
  ('Landscaper', 'landscaper', '#22C55E'),
  ('Bricklayer', 'bricklayer', '#F97316'),
  ('Tiler', 'tiler', '#06B6D4'),
  ('Concreter', 'concreter', '#6B7280'),
  ('Plasterer', 'plasterer', '#A855F7'),
  ('Glazier', 'glazier', '#14B8A6'),
  ('Fencer', 'fencer', '#84CC16'),
  ('General Builder', 'general-builder', '#0EA5E9')
ON CONFLICT (slug) DO NOTHING;

-- =====================================================
-- MIGRATION 2: Sales Playbook Sections
-- =====================================================

CREATE TABLE IF NOT EXISTS playbook_sections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category TEXT NOT NULL, -- 'opener', 'discovery', 'value_prop', 'close', 'voicemail', 'objection_handler'
  name TEXT NOT NULL,
  content TEXT NOT NULL,
  lead_type TEXT, -- 'worker', 'contractor', NULL for all
  stage TEXT, -- funnel stage when applicable
  effectiveness_score INTEGER DEFAULT 0, -- Calculated from success rate
  times_used INTEGER DEFAULT 0,
  times_successful INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_playbook_sections_category ON playbook_sections(category);
CREATE INDEX IF NOT EXISTS idx_playbook_sections_lead_type ON playbook_sections(lead_type);
CREATE INDEX IF NOT EXISTS idx_playbook_sections_active ON playbook_sections(is_active) WHERE is_active = true;

-- Function to calculate effectiveness score
CREATE OR REPLACE FUNCTION calculate_effectiveness_score(used INTEGER, successful INTEGER)
RETURNS INTEGER AS $$
BEGIN
  IF used = 0 THEN
    RETURN 0;
  END IF;
  RETURN ROUND((successful::NUMERIC / used::NUMERIC) * 100)::INTEGER;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Trigger to update effectiveness score on times_used/times_successful change
CREATE OR REPLACE FUNCTION update_playbook_section_score()
RETURNS TRIGGER AS $$
BEGIN
  NEW.effectiveness_score := calculate_effectiveness_score(NEW.times_used, NEW.times_successful);
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_playbook_section_score ON playbook_sections;
CREATE TRIGGER trigger_update_playbook_section_score
  BEFORE UPDATE OF times_used, times_successful ON playbook_sections
  FOR EACH ROW
  EXECUTE FUNCTION update_playbook_section_score();

-- RLS policies
ALTER TABLE playbook_sections ENABLE ROW LEVEL SECURITY;

CREATE POLICY "playbook_sections_authenticated_read"
  ON playbook_sections FOR SELECT TO authenticated
  USING (true);

CREATE POLICY "playbook_sections_authenticated_write"
  ON playbook_sections FOR INSERT TO authenticated
  WITH CHECK (true);

CREATE POLICY "playbook_sections_authenticated_update"
  ON playbook_sections FOR UPDATE TO authenticated
  USING (true) WITH CHECK (true);

CREATE POLICY "playbook_sections_authenticated_delete"
  ON playbook_sections FOR DELETE TO authenticated
  USING (true);

CREATE POLICY "playbook_sections_service_all"
  ON playbook_sections FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Seed data with proven sales content
INSERT INTO playbook_sections (category, name, content, lead_type) VALUES
  -- Openers (for workers)
  ('opener', 'Direct intro', 'Hey mate, it''s Rocky from RateRight. We connect quality tradies with jobs - contractors pay a flat $50 per hire - workers keep 100%. Got a minute?', 'worker'),
  ('opener', 'Referral angle', 'G''day, one of the other {trade} boys mentioned you might be looking for more work. Rocky from RateRight here - reckon I can help.', 'worker'),
  ('opener', 'Shortage angle', 'Hey mate, Rocky from RateRight. We''ve got contractors crying out for {trade} guys in your area. You looking for work?', 'worker'),

  -- Openers (for contractors)
  ('opener', 'Direct intro contractor', 'Hey, Rocky from RateRight here. We help contractors find quality tradies - completely free to use. Got tradies giving you grief?', 'contractor'),
  ('opener', 'Problem focused', 'G''day mate, hearing a lot of contractors struggling to find reliable {trade} guys. Rocky from RateRight - reckon we can help.', 'contractor'),

  -- Discovery questions
  ('discovery', 'Current work situation', 'So what''s your work situation like at the moment? Busy, quiet, somewhere in between?', NULL),
  ('discovery', 'Finding work challenges', 'What''s the hardest part about finding good jobs right now for you?', 'worker'),
  ('discovery', 'Finding tradies challenges', 'What''s been your biggest headache when it comes to finding reliable tradies?', 'contractor'),
  ('discovery', 'Ideal client/job', 'If you could pick your perfect job, what would it look like? Location, type of work, size?', 'worker'),
  ('discovery', 'Previous platforms', 'Have you tried any other platforms or services to find work/tradies? What worked, what didn''t?', NULL),

  -- Value propositions
  ('value_prop', 'No upfront cost worker', 'The beauty of RateRight is workers keep 100% of their pay. Contractors pay a flat $50 hire fee - that''s it. No percentage cuts, no hidden costs.', 'worker'),
  ('value_prop', 'Free for contractors', 'Best part? It''s completely free for contractors. You only pay the tradie directly - we don''t take a cut from your end.', 'contractor'),
  ('value_prop', 'Quality tradies', 'We vet every tradie on the platform. You''re not going to get ghosted or have someone rock up who doesn''t know what they''re doing.', 'contractor'),
  ('value_prop', 'Jobs in your area', 'We''ve got contractors specifically in your area looking for {trade} guys. Real jobs, not tyre kickers.', 'worker'),

  -- Closes
  ('close', 'Soft close worker', 'Look, why don''t you sign up, have a look around - costs you nothing. If you see a job you like, put your hand up. Simple as that.', 'worker'),
  ('close', 'Soft close contractor', 'Tell you what, sign up for free, post a job, see what comes back. If the tradies aren''t any good, no harm done. Fair?', 'contractor'),
  ('close', 'Direct close', 'Ready to give it a crack? I can get you signed up in 2 minutes.', NULL),
  ('close', 'Urgency close', 'We''ve got a few jobs going right now that would suit you. Want me to put you forward?', 'worker'),

  -- Objection handlers
  ('objection_handler', 'Already busy', 'That''s actually the best time to sign up - line up work for when things slow down. What tradies tell me is feast or famine, yeah?', 'worker'),
  ('objection_handler', 'Too expensive', 'Good news - workers actually pay nothing on RateRight. The contractor pays a flat $50 per hire. You keep 100% of your pay.', 'worker'),
  ('objection_handler', 'Bad experience with platforms', 'Yeah, I hear that a lot. That''s exactly why we do things differently - we actually vet the tradies/jobs. Not just anyone can sign up.', NULL),
  ('objection_handler', 'Need to think about it', 'No worries at all. What specifically did you want to have a think about? Maybe I can clear something up now.', NULL),

  -- Voicemail scripts
  ('voicemail', 'First contact VM', 'G''day mate, Rocky from RateRight here. We connect tradies with contractors in your area - just had a few jobs come through that might suit you. Give us a bell back when you get a chance - look forward to chatting.', 'worker'),
  ('voicemail', 'Follow up VM', 'Hey mate, Rocky again from RateRight. Just following up on that message I left. Got some work going in your area if you''re interested. Cheers.', 'worker'),
  ('voicemail', 'Contractor first VM', 'G''day, Rocky from RateRight here. We help contractors like yourself find quality tradies - completely free to use. Give us a call back when you get a sec.', 'contractor')
ON CONFLICT DO NOTHING;

-- =====================================================
-- MIGRATION 3: Team & Permissions System (RBAC)
-- =====================================================

-- Roles table
CREATE TABLE IF NOT EXISTS roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  permissions JSONB NOT NULL DEFAULT '{}', -- { "leads.create": true, "leads.delete": false, ... }
  is_system BOOLEAN DEFAULT false, -- System roles can't be deleted
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- User roles junction table
CREATE TABLE IF NOT EXISTS user_roles (
  user_id UUID NOT NULL,
  role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  assigned_by UUID,
  assigned_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_user_roles_user ON user_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_user_roles_role ON user_roles(role_id);
CREATE INDEX IF NOT EXISTS idx_roles_name ON roles(name);

-- Extend user_xp with permissions column for caching
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS permissions JSONB DEFAULT '{}';

-- Function to get merged permissions for a user
CREATE OR REPLACE FUNCTION get_user_permissions(p_user_id UUID)
RETURNS JSONB AS $$
DECLARE
  result JSONB := '{}';
  role_perms JSONB;
BEGIN
  -- Get all role permissions for this user
  FOR role_perms IN
    SELECT r.permissions
    FROM user_roles ur
    JOIN roles r ON r.id = ur.role_id
    WHERE ur.user_id = p_user_id
  LOOP
    result := result || role_perms;
  END LOOP;

  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Trigger to update cached permissions when roles change
CREATE OR REPLACE FUNCTION update_user_cached_permissions()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    UPDATE user_xp
    SET permissions = get_user_permissions(NEW.user_id),
        updated_at = NOW()
    WHERE user_id = NEW.user_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE user_xp
    SET permissions = get_user_permissions(OLD.user_id),
        updated_at = NOW()
    WHERE user_id = OLD.user_id;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_cached_permissions ON user_roles;
CREATE TRIGGER trigger_update_cached_permissions
  AFTER INSERT OR UPDATE OR DELETE ON user_roles
  FOR EACH ROW
  EXECUTE FUNCTION update_user_cached_permissions();

-- RLS policies
ALTER TABLE roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "roles_authenticated_read"
  ON roles FOR SELECT TO authenticated
  USING (true);

CREATE POLICY "roles_service_all"
  ON roles FOR ALL TO service_role
  USING (true) WITH CHECK (true);

CREATE POLICY "user_roles_authenticated_read"
  ON user_roles FOR SELECT TO authenticated
  USING (true);

CREATE POLICY "user_roles_service_all"
  ON user_roles FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Seed default system roles
INSERT INTO roles (name, description, permissions, is_system) VALUES
  ('admin', 'Full system access - can manage everything', '{
    "*": true
  }', true),

  ('manager', 'Team management + all caller permissions', '{
    "team.view": true,
    "team.manage": true,
    "reports.view": true,
    "reports.export": true,
    "challenges.create": true,
    "leads.view": true,
    "leads.create": true,
    "leads.update": true,
    "leads.delete": true,
    "leads.assign": true,
    "leads.import": true,
    "leads.export": true,
    "calls.view": true,
    "calls.make": true,
    "calls.log": true,
    "sms.view": true,
    "sms.send": true,
    "sms.bulk": true,
    "sequences.view": true,
    "sequences.manage": true,
    "playbook.view": true,
    "playbook.manage": true,
    "settings.view": true
  }', true),

  ('closer', 'Senior sales rep with additional permissions', '{
    "team.view": true,
    "reports.view": true,
    "leads.view": true,
    "leads.create": true,
    "leads.update": true,
    "leads.assign": true,
    "calls.view": true,
    "calls.make": true,
    "calls.log": true,
    "sms.view": true,
    "sms.send": true,
    "sms.bulk": true,
    "sequences.view": true,
    "sequences.enroll": true,
    "playbook.view": true,
    "settings.view": true
  }', true),

  ('caller', 'Standard sales rep - basic operations', '{
    "leads.view": true,
    "leads.create": true,
    "leads.update": true,
    "calls.view": true,
    "calls.make": true,
    "calls.log": true,
    "sms.view": true,
    "sms.send": true,
    "sequences.view": true,
    "sequences.enroll": true,
    "playbook.view": true
  }', true),

  ('viewer', 'Read-only access for observers', '{
    "leads.view": true,
    "calls.view": true,
    "sms.view": true,
    "reports.view": true,
    "sequences.view": true,
    "playbook.view": true
  }', true)
ON CONFLICT (name) DO UPDATE SET
  permissions = EXCLUDED.permissions,
  description = EXCLUDED.description;

-- Assign default roles to existing users based on their current role
INSERT INTO user_roles (user_id, role_id)
SELECT ux.user_id, r.id
FROM user_xp ux
JOIN roles r ON r.name = COALESCE(ux.role, 'caller')
WHERE ux.is_active = true
ON CONFLICT (user_id, role_id) DO NOTHING;

-- =====================================================
-- MIGRATION 4: Competitor Tracking System
-- =====================================================

CREATE TABLE IF NOT EXISTS competitors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  website TEXT,
  description TEXT,
  strengths TEXT[] DEFAULT '{}', -- Array of strength points
  weaknesses TEXT[] DEFAULT '{}', -- Array of weakness points
  pricing_info TEXT,
  battle_card JSONB DEFAULT '{}', -- Full battle card content
  is_active BOOLEAN DEFAULT true,
  mention_count INTEGER DEFAULT 0, -- Denormalized count
  last_mentioned_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS competitor_mentions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  competitor_id UUID REFERENCES competitors(id) ON DELETE CASCADE,
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,
  communication_id UUID REFERENCES communications(id) ON DELETE SET NULL,
  mentioned_by UUID, -- User who recorded the mention
  mention_type TEXT NOT NULL, -- 'call', 'sms', 'note', 'research'
  context TEXT, -- What was said
  sentiment TEXT DEFAULT 'neutral', -- 'positive', 'negative', 'neutral'
  outcome TEXT, -- 'won', 'lost', 'pending'
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_competitors_name ON competitors(name);
CREATE INDEX IF NOT EXISTS idx_competitors_active ON competitors(is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_competitor_mentions_competitor ON competitor_mentions(competitor_id);
CREATE INDEX IF NOT EXISTS idx_competitor_mentions_lead ON competitor_mentions(lead_id);
CREATE INDEX IF NOT EXISTS idx_competitor_mentions_created ON competitor_mentions(created_at DESC);

-- Trigger to update mention count and last_mentioned_at
CREATE OR REPLACE FUNCTION update_competitor_mention_stats()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE competitors
    SET mention_count = mention_count + 1,
        last_mentioned_at = NEW.created_at,
        updated_at = NOW()
    WHERE id = NEW.competitor_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE competitors
    SET mention_count = GREATEST(0, mention_count - 1),
        updated_at = NOW()
    WHERE id = OLD.competitor_id;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_competitor_mention_stats ON competitor_mentions;
CREATE TRIGGER trigger_update_competitor_mention_stats
  AFTER INSERT OR DELETE ON competitor_mentions
  FOR EACH ROW
  EXECUTE FUNCTION update_competitor_mention_stats();

-- RLS policies
ALTER TABLE competitors ENABLE ROW LEVEL SECURITY;
ALTER TABLE competitor_mentions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "competitors_authenticated_all"
  ON competitors FOR ALL TO authenticated
  USING (true) WITH CHECK (true);

CREATE POLICY "competitors_service_all"
  ON competitors FOR ALL TO service_role
  USING (true) WITH CHECK (true);

CREATE POLICY "competitor_mentions_authenticated_all"
  ON competitor_mentions FOR ALL TO authenticated
  USING (true) WITH CHECK (true);

CREATE POLICY "competitor_mentions_service_all"
  ON competitor_mentions FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Seed common competitors in Australian trades space
INSERT INTO competitors (name, description, strengths, weaknesses, pricing_info, battle_card) VALUES
  ('Hipages', 'Large Australian trade services marketplace',
   ARRAY['Brand recognition', 'Large customer base', 'Established platform'],
   ARRAY['High commission rates', 'Competitive bidding', 'Job quality varies'],
   '20-30% commission on jobs, monthly subscription options',
   '{"key_objection": "Hipages is more expensive and jobs are competitive", "our_advantage": "$50 flat fee per hire, no subscription fees", "talk_track": "A lot of tradies come to us frustrated with Hipages rates. We charge a flat $50 per hire - no upfront costs, no subscriptions, no percentage cuts."}'::jsonb),

  ('Airtasker', 'General task marketplace including trades',
   ARRAY['Broad task categories', 'Consumer awareness', 'Mobile app'],
   ARRAY['Not trades-focused', 'Race to bottom on pricing', 'Unvetted providers'],
   'Variable commission, typically 15-20%',
   '{"key_objection": "I already use Airtasker", "our_advantage": "We focus on quality trades only - no handymen undercutting professionals", "talk_track": "Airtasker is great for odd jobs but for proper trade work, you want a platform that understands the industry. We only work with qualified tradies."}'::jsonb),

  ('ServiceSeeking', 'Trade services lead generation',
   ARRAY['Quality leads', 'Good matching'],
   ARRAY['Pay per lead model', 'Lead quality varies', 'Expensive for tradies'],
   'Pay per lead, $10-50 per lead depending on job type',
   '{"key_objection": "ServiceSeeking has better leads", "our_advantage": "You only pay when you get paid - no paying for leads that go nowhere", "talk_track": "With us, you dont pay a cent until the jobs done and youve been paid. No more wasting money on leads that dont convert."}'::jsonb),

  ('Word of Mouth', 'Traditional referrals from existing customers',
   ARRAY['Free', 'High trust', 'Warm leads'],
   ARRAY['Unreliable volume', 'Feast or famine', 'Hard to scale'],
   'Free',
   '{"key_objection": "I get all my work from referrals", "our_advantage": "We complement referrals - fill gaps when things are quiet", "talk_track": "Referrals are gold, no doubt. We just help fill the gaps when the phones not ringing. Think of us as backup work."}'::jsonb),

  ('Direct Advertising', 'Google Ads, Facebook Ads, local advertising',
   ARRAY['Control over messaging', 'Brand building'],
   ARRAY['Expensive', 'Requires marketing expertise', 'Time consuming'],
   'Variable, often $500-2000/month',
   '{"key_objection": "I do my own advertising", "our_advantage": "No ad spend, no marketing needed - we bring the jobs to you", "talk_track": "Advertising is expensive and time consuming. We bring you qualified leads without you having to spend on ads or manage campaigns."}'::jsonb)
ON CONFLICT (name) DO NOTHING;

-- =====================================================
-- MIGRATION 5: Import/Export Job Tracking
-- =====================================================

CREATE TABLE IF NOT EXISTS import_jobs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  filename TEXT NOT NULL,
  file_type TEXT NOT NULL, -- 'csv', 'json'
  status TEXT DEFAULT 'pending', -- 'pending', 'processing', 'completed', 'failed'
  total_rows INTEGER,
  processed_rows INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  error_count INTEGER DEFAULT 0,
  errors JSONB DEFAULT '[]', -- Array of error details
  field_mapping JSONB DEFAULT '{}', -- Column mapping config
  options JSONB DEFAULT '{}', -- Import options (skip duplicates, etc.)
  created_at TIMESTAMPTZ DEFAULT NOW(),
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS export_jobs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  export_type TEXT NOT NULL, -- 'leads', 'communications', 'analytics', 'activity'
  filters JSONB DEFAULT '{}', -- Applied filters
  columns TEXT[] DEFAULT '{}', -- Columns to include
  format TEXT DEFAULT 'csv', -- 'csv', 'json'
  status TEXT DEFAULT 'pending', -- 'pending', 'processing', 'completed', 'failed'
  row_count INTEGER,
  file_url TEXT, -- Download URL (could be presigned)
  file_size_bytes INTEGER,
  error_message TEXT,
  expires_at TIMESTAMPTZ, -- When the download link expires
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_import_jobs_user ON import_jobs(user_id);
CREATE INDEX IF NOT EXISTS idx_import_jobs_status ON import_jobs(status);
CREATE INDEX IF NOT EXISTS idx_import_jobs_created ON import_jobs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_export_jobs_user ON export_jobs(user_id);
CREATE INDEX IF NOT EXISTS idx_export_jobs_status ON export_jobs(status);
CREATE INDEX IF NOT EXISTS idx_export_jobs_created ON export_jobs(created_at DESC);

-- RLS policies
ALTER TABLE import_jobs ENABLE ROW LEVEL SECURITY;
ALTER TABLE export_jobs ENABLE ROW LEVEL SECURITY;

-- Users can only see their own jobs
CREATE POLICY "import_jobs_user_access"
  ON import_jobs FOR ALL TO authenticated
  USING (user_id = auth.uid() OR auth.uid() IN (SELECT user_id FROM user_xp WHERE role = 'admin'))
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "import_jobs_service_all"
  ON import_jobs FOR ALL TO service_role
  USING (true) WITH CHECK (true);

CREATE POLICY "export_jobs_user_access"
  ON export_jobs FOR ALL TO authenticated
  USING (user_id = auth.uid() OR auth.uid() IN (SELECT user_id FROM user_xp WHERE role = 'admin'))
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "export_jobs_service_all"
  ON export_jobs FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Function to clean up old export jobs (run via cron)
CREATE OR REPLACE FUNCTION cleanup_old_export_jobs()
RETURNS void AS $$
BEGIN
  -- Delete completed exports older than 7 days
  DELETE FROM export_jobs
  WHERE status = 'completed'
    AND completed_at < NOW() - INTERVAL '7 days';

  -- Delete failed exports older than 30 days
  DELETE FROM export_jobs
  WHERE status = 'failed'
    AND created_at < NOW() - INTERVAL '30 days';

  -- Delete old import jobs (completed older than 30 days)
  DELETE FROM import_jobs
  WHERE status IN ('completed', 'failed')
    AND created_at < NOW() - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql;

-- =====================================================
-- MIGRATION 6: Outbound Webhooks System
-- =====================================================

CREATE TABLE IF NOT EXISTS webhook_endpoints (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  url TEXT NOT NULL,
  secret TEXT, -- For signing payloads (HMAC)
  events TEXT[] NOT NULL, -- ['lead.created', 'lead.converted', 'call.completed']
  is_active BOOLEAN DEFAULT true,
  retry_config JSONB DEFAULT '{"max_attempts": 3, "backoff_seconds": [60, 300, 900]}',
  headers JSONB DEFAULT '{}', -- Custom headers to include
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS webhook_deliveries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  endpoint_id UUID REFERENCES webhook_endpoints(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  payload JSONB NOT NULL,
  status TEXT DEFAULT 'pending', -- 'pending', 'success', 'failed', 'retrying'
  attempts INTEGER DEFAULT 0,
  max_attempts INTEGER DEFAULT 3,
  response_code INTEGER,
  response_body TEXT,
  error_message TEXT,
  next_retry_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_webhook_endpoints_active ON webhook_endpoints(is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_webhook_endpoints_created_by ON webhook_endpoints(created_by);
CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_endpoint ON webhook_deliveries(endpoint_id);
CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_status ON webhook_deliveries(status);
CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_pending ON webhook_deliveries(next_retry_at)
  WHERE status IN ('pending', 'retrying');
CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_created ON webhook_deliveries(created_at DESC);

-- RLS policies
ALTER TABLE webhook_endpoints ENABLE ROW LEVEL SECURITY;
ALTER TABLE webhook_deliveries ENABLE ROW LEVEL SECURITY;

CREATE POLICY "webhook_endpoints_authenticated_all"
  ON webhook_endpoints FOR ALL TO authenticated
  USING (true) WITH CHECK (true);

CREATE POLICY "webhook_endpoints_service_all"
  ON webhook_endpoints FOR ALL TO service_role
  USING (true) WITH CHECK (true);

CREATE POLICY "webhook_deliveries_authenticated_read"
  ON webhook_deliveries FOR SELECT TO authenticated
  USING (true);

CREATE POLICY "webhook_deliveries_service_all"
  ON webhook_deliveries FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Function to clean up old deliveries
CREATE OR REPLACE FUNCTION cleanup_old_webhook_deliveries()
RETURNS void AS $$
BEGIN
  -- Delete successful deliveries older than 7 days
  DELETE FROM webhook_deliveries
  WHERE status = 'success'
    AND completed_at < NOW() - INTERVAL '7 days';

  -- Delete failed deliveries older than 30 days
  DELETE FROM webhook_deliveries
  WHERE status = 'failed'
    AND completed_at < NOW() - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql;

-- =====================================================
-- MIGRATION 7: Apollo Enrichment System
-- =====================================================

-- Enrichment cache table
CREATE TABLE IF NOT EXISTS enrichment_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  source TEXT NOT NULL, -- 'apollo', 'clearbit', etc.
  enriched_data JSONB NOT NULL,
  confidence_score NUMERIC(3,2), -- 0.00-1.00
  credits_used INTEGER DEFAULT 1,
  enriched_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Unique constraint to prevent duplicate enrichments per source
CREATE UNIQUE INDEX IF NOT EXISTS idx_enrichment_cache_lead_source
  ON enrichment_cache(lead_id, source);

-- Index for finding stale enrichments
CREATE INDEX IF NOT EXISTS idx_enrichment_cache_expires
  ON enrichment_cache(expires_at) WHERE expires_at IS NOT NULL;

-- Enrichment usage tracking (for credit management)
CREATE TABLE IF NOT EXISTS enrichment_usage (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  source TEXT NOT NULL,
  credits_used INTEGER NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for usage queries
CREATE INDEX IF NOT EXISTS idx_enrichment_usage_period
  ON enrichment_usage(source, period_start, period_end);

-- Enrichment config table (API keys, limits, etc.)
CREATE TABLE IF NOT EXISTS enrichment_config (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source TEXT NOT NULL UNIQUE,
  api_key_encrypted TEXT, -- Encrypted API key
  is_enabled BOOLEAN DEFAULT false,
  monthly_limit INTEGER DEFAULT 1000,
  credits_used_this_month INTEGER DEFAULT 0,
  auto_enrich BOOLEAN DEFAULT false, -- Auto-enrich new leads
  enrich_on_events TEXT[] DEFAULT '{}', -- Events that trigger enrichment
  cache_ttl_days INTEGER DEFAULT 90, -- How long to cache enrichments
  settings JSONB DEFAULT '{}', -- Source-specific settings
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Seed default config for Apollo
INSERT INTO enrichment_config (source, is_enabled, monthly_limit, cache_ttl_days, settings) VALUES
  ('apollo', false, 1000, 90, '{"endpoint": "https://api.apollo.io/v1", "enrichFields": ["person", "company"]}')
ON CONFLICT (source) DO NOTHING;

-- Function to reset monthly credits on first of month
CREATE OR REPLACE FUNCTION reset_enrichment_credits()
RETURNS void AS $$
BEGIN
  UPDATE enrichment_config
  SET credits_used_this_month = 0, updated_at = NOW()
  WHERE date_trunc('month', updated_at) < date_trunc('month', NOW());
END;
$$ LANGUAGE plpgsql;

-- RLS policies
ALTER TABLE enrichment_cache ENABLE ROW LEVEL SECURITY;
ALTER TABLE enrichment_usage ENABLE ROW LEVEL SECURITY;
ALTER TABLE enrichment_config ENABLE ROW LEVEL SECURITY;

-- Allow read access to enrichment data for authenticated users
CREATE POLICY "read_enrichment_cache" ON enrichment_cache
  FOR SELECT USING (auth.role() = 'authenticated');

CREATE POLICY "read_enrichment_usage" ON enrichment_usage
  FOR SELECT USING (auth.role() = 'authenticated');

-- Only admins can modify config
CREATE POLICY "read_enrichment_config" ON enrichment_config
  FOR SELECT USING (auth.role() = 'authenticated');

-- Service role full access for all enrichment tables
CREATE POLICY "enrichment_cache_service_all" ON enrichment_cache
  FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "enrichment_usage_service_all" ON enrichment_usage
  FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "enrichment_config_service_all" ON enrichment_config
  FOR ALL TO service_role USING (true) WITH CHECK (true);

-- =====================================================
-- DONE! All 8 migrations applied (including job_locks).
-- =====================================================
