-- ============================================
-- ONE-SHOT CRM SETUP
-- Safe to run multiple times - TRULY idempotent
-- ============================================
-- Copy-paste this ENTIRE file into Supabase SQL Editor
-- Preserves all existing data - safe to re-run anytime
-- ============================================
-- Version: 2.6 (Local-First Sync Architecture)
-- Last updated: 2025-11-04
-- ============================================
--
-- WHAT'S NEW IN v2.6:
-- ✅ Added timestamp columns for local-first sync (0-1ms operations)
-- ✅ Field-level conflict resolution (last-write-wins with timestamps)
-- ✅ Sync status tracking for MCP background sync
-- ✅ Compatible with ThetaCoach CRM MCP v11.2.0+
--
-- Previous versions:
-- v2.5: Removed ALL DROP TABLE commands (truly idempotent)
--
-- Previous versions:
-- v2.4: Added RLS policies for anon/authenticated roles
-- v2.3: Added DROP TRIGGER IF EXISTS before all CREATE TRIGGER
-- v2.2: Added call_history field for practice call tracking
-- v2.1: Added JSONB structured data columns
-- ============================================
--
-- ✅ SAFE FOR ALL USERS:
--   - New installations: Creates fresh schema
--   - Existing databases: Preserves all data, adds missing pieces
--   - Run multiple times: No side effects, no data loss
--
-- ============================================

-- ============================================
-- STEP 1: EXTENSIONS
-- ============================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ============================================
-- STEP 2: MAIN TABLES
-- ============================================

-- 2.1 LEADS TABLE (Battle Cards - UI Primary Table)
-- This is what /crm/cards reads from
-- Safe to run multiple times - preserves existing data
CREATE TABLE IF NOT EXISTS leads (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

  -- Basic Info
  email TEXT,
  name TEXT,
  phone TEXT,
  company TEXT,
  role TEXT,

  -- Card Status
  card_status TEXT DEFAULT 'new', -- 'new', 'in_progress', 'contacted', 'closed'

  -- Challenger Methodology
  stage TEXT DEFAULT 'awareness',
  lead_score INTEGER DEFAULT 50 CHECK (lead_score >= 0 AND lead_score <= 100),

  -- Section Notes (Auto-save on blur in UI)
  discovery_notes TEXT,
  rational_notes TEXT,
  emotional_notes TEXT,
  solution_notes TEXT,
  commitment_notes TEXT,
  notes TEXT, -- General notes/transcript

  -- Section Checklists (1-3 actionable items per section for live calls)
  discovery_checklist JSONB DEFAULT '[]',
  rational_checklist JSONB DEFAULT '[]',
  emotional_checklist JSONB DEFAULT '[]',
  solution_checklist JSONB DEFAULT '[]',
  commitment_checklist JSONB DEFAULT '[]',

  -- Structured Data (JSONB for rich data alongside text notes)
  discovery_data JSONB DEFAULT '{}'::jsonb,
  rational_data JSONB DEFAULT '{}'::jsonb,
  emotional_data JSONB DEFAULT '{}'::jsonb,
  solution_data JSONB DEFAULT '{}'::jsonb,
  commitment_data JSONB DEFAULT '{}'::jsonb,

  -- Product & Outreach Fields
  product_positioning TEXT, -- What we're selling, outcome they get
  outreach_a TEXT, -- LinkedIn DM version A (direct)
  outreach_b TEXT, -- LinkedIn DM version B (story)

  -- Practice Call History (KEY FEATURE for Claude analysis)
  call_history JSONB DEFAULT '[]'::jsonb,

  -- Timestamps
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP

  -- Note: Field-level timestamps and sync columns added via ALTER TABLE below (Step 2.5)
  -- This ensures they're added even if table already exists
);

-- ============================================
-- STEP 2.5: ADD SYNC COLUMNS (v2.6 - Safe for existing tables)
-- ============================================
-- These columns enable local-first sync with 0-1ms operations
-- Safe to run multiple times (IF NOT EXISTS)

ALTER TABLE leads ADD COLUMN IF NOT EXISTS discovery_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS rational_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS emotional_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS solution_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS commitment_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS product_positioning_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS outreach_a_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS outreach_b_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS notes_updated_at BIGINT;

ALTER TABLE leads ADD COLUMN IF NOT EXISTS _sync_status TEXT DEFAULT 'synced';
ALTER TABLE leads ADD COLUMN IF NOT EXISTS _last_synced_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS _sync_error TEXT;

-- Initialize timestamps for existing records
UPDATE leads
SET
  discovery_notes_updated_at = COALESCE(discovery_notes_updated_at, EXTRACT(EPOCH FROM updated_at)::BIGINT * 1000),
  rational_notes_updated_at = COALESCE(rational_notes_updated_at, EXTRACT(EPOCH FROM updated_at)::BIGINT * 1000),
  emotional_notes_updated_at = COALESCE(emotional_notes_updated_at, EXTRACT(EPOCH FROM updated_at)::BIGINT * 1000),
  solution_notes_updated_at = COALESCE(solution_notes_updated_at, EXTRACT(EPOCH FROM updated_at)::BIGINT * 1000),
  commitment_notes_updated_at = COALESCE(commitment_notes_updated_at, EXTRACT(EPOCH FROM updated_at)::BIGINT * 1000),
  product_positioning_updated_at = COALESCE(product_positioning_updated_at, EXTRACT(EPOCH FROM updated_at)::BIGINT * 1000),
  outreach_a_updated_at = COALESCE(outreach_a_updated_at, EXTRACT(EPOCH FROM updated_at)::BIGINT * 1000),
  outreach_b_updated_at = COALESCE(outreach_b_updated_at, EXTRACT(EPOCH FROM updated_at)::BIGINT * 1000),
  notes_updated_at = COALESCE(notes_updated_at, EXTRACT(EPOCH FROM updated_at)::BIGINT * 1000),
  _last_synced_at = COALESCE(_last_synced_at, EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
WHERE discovery_notes_updated_at IS NULL;

-- ============================================
-- STEP 2.6: INDEXES
-- ============================================

-- Indexes for leads (cards table) - Basic columns only
CREATE INDEX IF NOT EXISTS idx_leads_email ON leads(email) WHERE email IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_leads_company ON leads(company) WHERE company IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_leads_card_status ON leads(card_status);
CREATE INDEX IF NOT EXISTS idx_leads_score ON leads(lead_score DESC);
CREATE INDEX IF NOT EXISTS idx_leads_sync_status ON leads(_sync_status) WHERE _sync_status = 'pending';

-- Note: GIN indexes for JSONB columns created AFTER auto-repair (Step 8.5)

-- Update trigger for leads
CREATE OR REPLACE FUNCTION update_leads_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_leads_timestamp ON leads;
CREATE TRIGGER update_leads_timestamp
  BEFORE UPDATE ON leads
  FOR EACH ROW
  EXECUTE FUNCTION update_leads_updated_at();

-- Comments (only for columns that ALWAYS exist)
COMMENT ON TABLE leads IS 'Battle cards shown in CRM cards inbox at /crm/cards';
COMMENT ON COLUMN leads.card_status IS 'Card status in inbox: new, in_progress, contacted, closed';
COMMENT ON COLUMN leads.lead_score IS 'Lead score 0-100 for prioritization';
COMMENT ON COLUMN leads.discovery_notes IS 'Discovery section - auto-saves on blur';
COMMENT ON COLUMN leads.rational_notes IS 'Rational Drowning section - auto-saves on blur';
COMMENT ON COLUMN leads.emotional_notes IS 'Emotional Impact section - auto-saves on blur';
COMMENT ON COLUMN leads.solution_notes IS 'Solution section - auto-saves on blur';
COMMENT ON COLUMN leads.commitment_notes IS 'Commitment/Pilot section - auto-saves on blur';
COMMENT ON COLUMN leads.notes IS 'General notes and transcript - auto-saves on blur';
COMMENT ON COLUMN leads.product_positioning IS 'Product positioning - outcome, differentiation, transformation - auto-saves on blur';
COMMENT ON COLUMN leads.outreach_a IS 'LinkedIn outreach version A (direct approach) - auto-saves on blur';
COMMENT ON COLUMN leads.outreach_b IS 'LinkedIn outreach version B (story approach) - auto-saves on blur';

-- Note: Comments for JSONB columns added in Step 8.6 (after columns are guaranteed to exist)

-- ============================================
-- STEP 3: BLAND AI CALLS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS crm_calls (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

  -- Lead reference
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,

  -- Bland AI call details
  bland_call_id TEXT UNIQUE,
  phone_number TEXT NOT NULL,

  -- Call metadata
  direction TEXT NOT NULL, -- 'inbound' or 'outbound'
  scenario TEXT, -- 'practice-call', 'crm-inbound-practice', 'crm-outbound'
  script_used TEXT, -- The script/prompt used

  -- Call status and results
  status TEXT DEFAULT 'initiated', -- 'initiated', 'ringing', 'in-progress', 'completed', 'failed', 'no-answer'
  duration INTEGER, -- Call duration in seconds
  transcript TEXT, -- Full call transcript
  recording_url TEXT, -- URL to call recording

  -- Bland AI response details
  answered BOOLEAN,
  answered_by TEXT, -- 'human' or 'voicemail'
  end_reason TEXT, -- Why the call ended

  -- Analysis and context
  analysis JSONB, -- AI analysis of the call
  context JSONB, -- Additional context (stages, questions, etc.)

  -- Timestamps
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_crm_calls_lead_id ON crm_calls(lead_id);
CREATE INDEX IF NOT EXISTS idx_crm_calls_bland_call_id ON crm_calls(bland_call_id);
CREATE INDEX IF NOT EXISTS idx_crm_calls_status ON crm_calls(status);
CREATE INDEX IF NOT EXISTS idx_crm_calls_created_at ON crm_calls(created_at DESC);

-- Update trigger for updated_at
CREATE OR REPLACE FUNCTION update_crm_calls_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_crm_calls_timestamp ON crm_calls;
CREATE TRIGGER update_crm_calls_timestamp
  BEFORE UPDATE ON crm_calls
  FOR EACH ROW
  EXECUTE FUNCTION update_crm_calls_updated_at();

-- Comments for documentation
COMMENT ON TABLE crm_calls IS 'Stores Bland AI practice calls initiated through the CRM battle cards';
COMMENT ON COLUMN crm_calls.direction IS 'Direction of call: inbound (AI calls user) or outbound (AI calls lead)';
COMMENT ON COLUMN crm_calls.scenario IS 'Type of practice scenario being run';
COMMENT ON COLUMN crm_calls.analysis IS 'AI-generated analysis of call performance and areas for improvement';
COMMENT ON COLUMN crm_calls.context IS 'Full battle card context at time of call (stages, questions, beliefs, etc.)';

-- ============================================
-- STEP 4: PRACTICE CALL TRANSCRIPTS
-- ============================================

CREATE TABLE IF NOT EXISTS practice_call_transcripts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Foreign keys
  lead_id UUID NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  practice_call_id UUID REFERENCES crm_calls(id) ON DELETE SET NULL,

  -- Bland AI metadata
  bland_call_id TEXT UNIQUE,
  call_status TEXT DEFAULT 'completed',

  -- Transcript data
  raw_transcript TEXT NOT NULL,
  formatted_transcript TEXT,

  -- Call metadata
  duration_seconds INTEGER,
  recording_url TEXT,
  answered_by TEXT, -- 'human', 'voicemail', 'no_answer'

  -- Practice context
  practice_mode TEXT, -- 'ai-as-lead', 'ai-as-coach'
  script_used TEXT,

  -- AI Analysis
  analysis JSONB DEFAULT '{}'::jsonb,

  -- Extracted insights
  key_insights JSONB DEFAULT '{}'::jsonb,
  objections_raised TEXT[],
  questions_asked TEXT[],
  commitments_gained TEXT[],

  -- Performance metrics
  talk_ratio DECIMAL(3,2), -- 0.00 to 1.00 (salesperson talk time ratio)
  question_count INTEGER DEFAULT 0,
  objection_count INTEGER DEFAULT 0,

  -- Timestamps
  call_started_at TIMESTAMPTZ,
  call_completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for fast queries
CREATE INDEX IF NOT EXISTS idx_practice_transcripts_lead ON practice_call_transcripts(lead_id);
CREATE INDEX IF NOT EXISTS idx_practice_transcripts_bland ON practice_call_transcripts(bland_call_id);
CREATE INDEX IF NOT EXISTS idx_practice_transcripts_mode ON practice_call_transcripts(practice_mode);
CREATE INDEX IF NOT EXISTS idx_practice_transcripts_created ON practice_call_transcripts(created_at DESC);

-- Trigger to update updated_at
CREATE OR REPLACE FUNCTION update_practice_transcript_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS practice_transcript_updated_at ON practice_call_transcripts;
CREATE TRIGGER practice_transcript_updated_at
  BEFORE UPDATE ON practice_call_transcripts
  FOR EACH ROW
  EXECUTE FUNCTION update_practice_transcript_updated_at();

-- Comments
COMMENT ON TABLE practice_call_transcripts IS 'Stores Bland AI practice call transcripts with analysis and insights for sales coaching';
COMMENT ON COLUMN practice_call_transcripts.analysis IS 'AI-generated analysis including sentiment, objections, key moments, and Challenger methodology coverage';
COMMENT ON COLUMN practice_call_transcripts.talk_ratio IS 'Ratio of salesperson talk time to total call duration (ideal: 0.30-0.43)';

-- ============================================
-- STEP 5: BATTLE CARD DETAIL TABLES
-- ============================================

-- 6.1 Discovery Section
CREATE TABLE IF NOT EXISTS card_discovery (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id UUID REFERENCES leads(id) ON DELETE CASCADE,

  -- Questions to ask
  questions JSONB DEFAULT '[]'::jsonb,

  -- Findings from discovery
  findings JSONB DEFAULT '{}'::jsonb,

  -- Progress tracking
  discovery_complete BOOLEAN DEFAULT false,
  completion_date TIMESTAMP WITH TIME ZONE,

  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_card_discovery_card ON card_discovery(card_id);

-- 6.2 Rational Drowning Section
CREATE TABLE IF NOT EXISTS card_rational (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id UUID REFERENCES leads(id) ON DELETE CASCADE,

  -- Data points to present
  data_points JSONB DEFAULT '[]'::jsonb,

  -- ROI calculation
  roi_calculation JSONB DEFAULT '{}'::jsonb,

  -- Complexity/scale awareness
  scale_reframe JSONB DEFAULT '{}'::jsonb,

  -- Progress tracking
  rational_complete BOOLEAN DEFAULT false,
  completion_date TIMESTAMP WITH TIME ZONE,

  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_card_rational_card ON card_rational(card_id);

-- 6.3 Emotional Impact Section
CREATE TABLE IF NOT EXISTS card_emotional (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id UUID REFERENCES leads(id) ON DELETE CASCADE,

  -- Stories to share
  stories JSONB DEFAULT '[]'::jsonb,

  -- Personal concerns identified
  personal_stakes JSONB DEFAULT '{}'::jsonb,

  -- Emotional response tracking
  reactions JSONB DEFAULT '[]'::jsonb,

  -- Progress tracking
  emotional_complete BOOLEAN DEFAULT false,
  completion_date TIMESTAMP WITH TIME ZONE,

  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_card_emotional_card ON card_emotional(card_id);

-- 6.4 Solution/Decision Section
CREATE TABLE IF NOT EXISTS card_solution (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id UUID REFERENCES leads(id) ON DELETE CASCADE,

  -- New way presentation
  solution_benefits JSONB DEFAULT '[]'::jsonb,

  -- Proof points
  proof_points JSONB DEFAULT '[]'::jsonb,

  -- Objections and responses
  objections JSONB DEFAULT '[]'::jsonb,

  -- Progress tracking
  solution_complete BOOLEAN DEFAULT false,
  completion_date TIMESTAMP WITH TIME ZONE,

  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_card_solution_card ON card_solution(card_id);

-- 6.5 Commitment/Action Section
CREATE TABLE IF NOT EXISTS card_commitment (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id UUID REFERENCES leads(id) ON DELETE CASCADE,

  -- Next steps
  next_actions JSONB DEFAULT '[]'::jsonb,

  -- Decision criteria
  decision_factors JSONB DEFAULT '{}'::jsonb,

  -- Commitment level
  commitment_score INTEGER DEFAULT 0, -- 0-100
  commitment_signals JSONB DEFAULT '[]'::jsonb,

  -- Progress tracking
  commitment_complete BOOLEAN DEFAULT false,
  completion_date TIMESTAMP WITH TIME ZONE,

  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_card_commitment_card ON card_commitment(card_id);

-- 6.6 Card Notes
CREATE TABLE IF NOT EXISTS card_notes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  section TEXT, -- 'discovery', 'rational', 'emotional', 'solution', 'commitment', 'general'
  note_type TEXT, -- 'observation', 'action_item', 'insight', 'reminder'
  content TEXT NOT NULL,
  created_by TEXT, -- 'claude', 'user', 'system'
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_card_notes_card ON card_notes(card_id);
CREATE INDEX IF NOT EXISTS idx_card_notes_section ON card_notes(card_id, section);

-- ============================================
-- STEP 6: UPDATE TRIGGERS FOR CARD TABLES
-- ============================================

-- Shared update trigger function for all card tables
CREATE OR REPLACE FUNCTION update_card_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply triggers to all card tables (with DROP IF EXISTS for safe re-runs)
DROP TRIGGER IF EXISTS card_discovery_updated_at ON card_discovery;
CREATE TRIGGER card_discovery_updated_at BEFORE UPDATE ON card_discovery
  FOR EACH ROW EXECUTE FUNCTION update_card_updated_at();

DROP TRIGGER IF EXISTS card_rational_updated_at ON card_rational;
CREATE TRIGGER card_rational_updated_at BEFORE UPDATE ON card_rational
  FOR EACH ROW EXECUTE FUNCTION update_card_updated_at();

DROP TRIGGER IF EXISTS card_emotional_updated_at ON card_emotional;
CREATE TRIGGER card_emotional_updated_at BEFORE UPDATE ON card_emotional
  FOR EACH ROW EXECUTE FUNCTION update_card_updated_at();

DROP TRIGGER IF EXISTS card_solution_updated_at ON card_solution;
CREATE TRIGGER card_solution_updated_at BEFORE UPDATE ON card_solution
  FOR EACH ROW EXECUTE FUNCTION update_card_updated_at();

DROP TRIGGER IF EXISTS card_commitment_updated_at ON card_commitment;
CREATE TRIGGER card_commitment_updated_at BEFORE UPDATE ON card_commitment
  FOR EACH ROW EXECUTE FUNCTION update_card_updated_at();

DROP TRIGGER IF EXISTS card_notes_updated_at ON card_notes;
CREATE TRIGGER card_notes_updated_at BEFORE UPDATE ON card_notes
  FOR EACH ROW EXECUTE FUNCTION update_card_updated_at();

-- ============================================
-- STEP 7: UTILITY FUNCTIONS
-- ============================================

-- Drop existing exec_sql function if it exists (prevents return type conflict)
-- Use CASCADE to handle any dependencies
DROP FUNCTION IF EXISTS exec_sql(TEXT) CASCADE;
DROP FUNCTION IF EXISTS exec_sql(text) CASCADE;

-- exec_sql function for MCP crm-sql tool
CREATE OR REPLACE FUNCTION exec_sql(sql_query TEXT)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result JSONB;
BEGIN
  EXECUTE sql_query;
  RETURN jsonb_build_object('success', true);
EXCEPTION
  WHEN OTHERS THEN
    RETURN jsonb_build_object(
      'success', false,
      'error', SQLERRM
    );
END;
$$;

COMMENT ON FUNCTION exec_sql IS 'Execute arbitrary SQL queries (used by MCP crm-sql tool). WARNING: SECURITY DEFINER - only expose to service_role';

-- ============================================
-- STEP 8: AUTO-REPAIR JSONB COLUMNS (CRITICAL - Prevents loss)
-- ============================================
-- This ensures JSONB columns ALWAYS exist, even if upgrading from v1.x
-- These were added in v2.1 and v2.2 - safe to run on any version

-- Structured Data columns (v2.1)
ALTER TABLE leads ADD COLUMN IF NOT EXISTS discovery_data JSONB DEFAULT '{}'::jsonb;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS rational_data JSONB DEFAULT '{}'::jsonb;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS emotional_data JSONB DEFAULT '{}'::jsonb;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS solution_data JSONB DEFAULT '{}'::jsonb;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS commitment_data JSONB DEFAULT '{}'::jsonb;

-- Checklist columns (v2.0)
ALTER TABLE leads ADD COLUMN IF NOT EXISTS discovery_checklist JSONB DEFAULT '[]';
ALTER TABLE leads ADD COLUMN IF NOT EXISTS rational_checklist JSONB DEFAULT '[]';
ALTER TABLE leads ADD COLUMN IF NOT EXISTS emotional_checklist JSONB DEFAULT '[]';
ALTER TABLE leads ADD COLUMN IF NOT EXISTS solution_checklist JSONB DEFAULT '[]';
ALTER TABLE leads ADD COLUMN IF NOT EXISTS commitment_checklist JSONB DEFAULT '[]';

-- Call history (v2.2)
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_history JSONB DEFAULT '[]';

-- ============================================
-- STEP 8.5: GIN INDEXES FOR JSONB COLUMNS
-- ============================================
-- Create these AFTER auto-repair to ensure columns exist

CREATE INDEX IF NOT EXISTS idx_leads_discovery_data_gin ON leads USING GIN (discovery_data);
CREATE INDEX IF NOT EXISTS idx_leads_rational_data_gin ON leads USING GIN (rational_data);
CREATE INDEX IF NOT EXISTS idx_leads_emotional_data_gin ON leads USING GIN (emotional_data);
CREATE INDEX IF NOT EXISTS idx_leads_solution_data_gin ON leads USING GIN (solution_data);
CREATE INDEX IF NOT EXISTS idx_leads_commitment_data_gin ON leads USING GIN (commitment_data);
CREATE INDEX IF NOT EXISTS idx_leads_call_history_gin ON leads USING GIN (call_history);
CREATE INDEX IF NOT EXISTS idx_leads_discovery_checklist_gin ON leads USING GIN (discovery_checklist);
CREATE INDEX IF NOT EXISTS idx_leads_rational_checklist_gin ON leads USING GIN (rational_checklist);
CREATE INDEX IF NOT EXISTS idx_leads_emotional_checklist_gin ON leads USING GIN (emotional_checklist);
CREATE INDEX IF NOT EXISTS idx_leads_solution_checklist_gin ON leads USING GIN (solution_checklist);
CREATE INDEX IF NOT EXISTS idx_leads_commitment_checklist_gin ON leads USING GIN (commitment_checklist);

-- ============================================
-- STEP 8.6: COMMENTS FOR JSONB COLUMNS
-- ============================================
-- Add comments AFTER columns are guaranteed to exist (Step 8)

COMMENT ON COLUMN leads.discovery_data IS 'Structured discovery data in JSONB format - complements discovery_notes text field. Example: {"pain_points": [], "current_state": {}, "goals": []}';
COMMENT ON COLUMN leads.rational_data IS 'Structured rational/ROI data in JSONB format - complements rational_notes text field. Example: {"roi_calculation": {}, "data_points": [], "scale_metrics": {}}';
COMMENT ON COLUMN leads.emotional_data IS 'Structured emotional impact data in JSONB format - complements emotional_notes text field. Example: {"personal_stakes": {}, "fears": [], "aspirations": [], "stories": []}';
COMMENT ON COLUMN leads.solution_data IS 'Structured solution data in JSONB format - complements solution_notes text field. Example: {"benefits": [], "proof_points": [], "objections": [], "differentiators": []}';
COMMENT ON COLUMN leads.commitment_data IS 'Structured commitment/action data in JSONB format - complements commitment_notes text field. Example: {"next_steps": [], "decision_criteria": {}, "timeline": {}, "commitment_signals": []}';
COMMENT ON COLUMN leads.call_history IS 'Practice call history array. Each call: {call_id, bland_call_id, date, duration, transcript, recording_url, scenario}. Users can copy this to Claude for AI-powered call analysis vs battle card notes.';

-- ============================================
-- STEP 9: ROW-LEVEL SECURITY (For Web GUI)
-- ============================================
-- RLS policies enable the web GUI (/crm/cards) to function with the anon key.
--
-- SECURITY MODEL:
-- - Local MCP/Claude: Uses service role key (stored in .env.local) - bypasses RLS
-- - Web GUI: Uses anon key (RLS enforced) - users see only their data
-- - Service role key never transmitted to web servers - stays local only
--
-- Note: Since each user has their own Supabase database (not shared),
-- we can safely allow full access to anon role. No data leakage between users.

-- Enable RLS on leads table
ALTER TABLE leads ENABLE ROW LEVEL SECURITY;

-- RLS Policy: Anon role can see/edit all leads (single-user mode - for web GUI)
-- Since each user has their own database (not shared), we safely allow full access
-- This enables the web GUI at /crm/cards to function with the anon key
DROP POLICY IF EXISTS "anon_full_access" ON leads;
CREATE POLICY "anon_full_access" ON leads
  FOR ALL
  TO anon, authenticated
  USING (true)
  WITH CHECK (true);

-- Also enable RLS-friendly access for related tables (crm_calls, practice_call_transcripts)
ALTER TABLE crm_calls ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "anon_crm_calls_access" ON crm_calls;
CREATE POLICY "anon_crm_calls_access" ON crm_calls
  FOR ALL
  TO anon, authenticated
  USING (true)
  WITH CHECK (true);

ALTER TABLE practice_call_transcripts ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "anon_practice_transcripts_access" ON practice_call_transcripts;
CREATE POLICY "anon_practice_transcripts_access" ON practice_call_transcripts
  FOR ALL
  TO anon, authenticated
  USING (true)
  WITH CHECK (true);

DO $$
BEGIN
  RAISE NOTICE '';
  RAISE NOTICE '🔐 RLS SETUP COMPLETE';
  RAISE NOTICE '==============================================';
  RAISE NOTICE 'Enabled RLS on: leads, crm_calls, practice_call_transcripts';
  RAISE NOTICE 'Policies: anon and authenticated roles have full access';
  RAISE NOTICE '';
  RAISE NOTICE '✅ Security model:';
  RAISE NOTICE '   - Local MCP: Uses service role key from .env.local';
  RAISE NOTICE '   - Web GUI: Uses anon key (RLS protected)';
  RAISE NOTICE '   - Your data stays in YOUR database';
  RAISE NOTICE '';
END $$;

-- ============================================
-- STEP 10: VALIDATION & SUCCESS MESSAGE
-- ============================================

-- Check all tables exist (run this to verify setup)
DO $$
DECLARE
  table_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO table_count
  FROM information_schema.tables
  WHERE table_schema = 'public'
    AND table_name IN (
      'leads',
      'crm_calls',
      'practice_call_transcripts',
      'card_discovery',
      'card_rational',
      'card_emotional',
      'card_solution',
      'card_commitment',
      'card_notes'
    );

  RAISE NOTICE '✅ Tables created: %', table_count;
  RAISE NOTICE 'Expected: 9 tables';

  IF table_count = 9 THEN
    RAISE NOTICE '✅ SETUP COMPLETE!';
  ELSE
    RAISE WARNING '⚠️  Expected 9 tables, found %', table_count;
  END IF;
END $$;

-- List all created tables with column counts
SELECT
  table_name,
  (SELECT COUNT(*) FROM information_schema.columns WHERE table_name = t.table_name) as column_count
FROM information_schema.tables t
WHERE table_schema = 'public'
  AND table_name IN (
    'leads',
    'crm_calls',
    'practice_call_transcripts',
    'card_discovery',
    'card_rational',
    'card_emotional',
    'card_solution',
    'card_commitment',
    'card_notes'
  )
ORDER BY table_name;

-- ============================================
-- SUCCESS MESSAGE
-- ============================================

DO $$
BEGIN
  RAISE NOTICE '';
  RAISE NOTICE '🎉 ONE-SHOT CRM SETUP COMPLETE!';
  RAISE NOTICE '================================';
  RAISE NOTICE 'Tables Created:';
  RAISE NOTICE '  ✅ leads (battle cards inbox)';
  RAISE NOTICE '  ✅ crm_calls (Bland AI integration)';
  RAISE NOTICE '  ✅ practice_call_transcripts (practice call storage)';
  RAISE NOTICE '  ✅ card_discovery';
  RAISE NOTICE '  ✅ card_rational';
  RAISE NOTICE '  ✅ card_emotional';
  RAISE NOTICE '  ✅ card_solution';
  RAISE NOTICE '  ✅ card_commitment';
  RAISE NOTICE '  ✅ card_notes';
  RAISE NOTICE '';
  RAISE NOTICE '🔧 Key Fixes & Features:';
  RAISE NOTICE '  ✅ v2.6: Local-first sync - 0-1ms operations with background sync';
  RAISE NOTICE '  ✅ v2.6: Field-level timestamps for conflict resolution';
  RAISE NOTICE '  ✅ v2.5: TRULY IDEMPOTENT - no DROP TABLE, preserves all data';
  RAISE NOTICE '  ✅ v2.4: Added RLS policies for anon/authenticated roles';
  RAISE NOTICE '  ✅ v2.2: Added call_history field - copy practice calls to Claude for AI analysis!';
  RAISE NOTICE '  ✅ v2.1: Added JSONB structured data columns (discovery_data, rational_data, etc.)';
  RAISE NOTICE '';
  RAISE NOTICE 'Next Steps:';
  RAISE NOTICE '  1. Copy MCP server credentials to .env.local';
  RAISE NOTICE '  2. Paste LinkedIn URL into Claude Code';
  RAISE NOTICE '  3. Click Practice Call to test Bland AI';
  RAISE NOTICE '';
  RAISE NOTICE '🚀 Your CRM is ready!';
END $$;
