-- ============================================
-- BATTLE CARDS SCHEMA FOR /crm/cards
-- Download URL: https://thetacoach.biz/crm-schemas/battle-cards-schema.sql
-- ============================================

-- IMPORTANT: The CRM uses a TWO-TABLE structure:
--   1. LEADS table = Basic contact info (email, name, company, role)
--   2. BATTLE_CARDS table = Actual cards shown in inbox (one lead → many cards)
--
-- The GUI ONLY shows battle_cards, NEVER raw leads!

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

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

-- ============================================
-- STEP 2: LEADS TABLE (Contact Information)
-- ============================================

-- Stores basic contact information - NOT shown directly in GUI
CREATE TABLE IF NOT EXISTS leads (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

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

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

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

-- Update trigger
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
COMMENT ON TABLE leads IS 'Lead contact information - battle cards reference this table';

-- ============================================
-- STEP 3: BATTLE_CARDS TABLE (What Shows in GUI)
-- ============================================

-- This is what the CRM inbox displays - one lead can have MANY battle cards
CREATE TABLE IF NOT EXISTS battle_cards (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

  -- Foreign key to leads
  lead_id UUID NOT NULL REFERENCES leads(id) ON DELETE CASCADE,

  -- Card metadata
  title TEXT NOT NULL,
  description TEXT,
  card_type TEXT DEFAULT 'general', -- 'general', 'scenario', 'practice-call', 'learning'

  -- Card Status (shown in inbox)
  card_status TEXT DEFAULT 'new', -- 'new', 'in_progress', 'contacted', 'closed'

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

  -- Challenger Section Notes (Auto-save on blur)
  discovery_notes TEXT,
  rational_notes TEXT,
  emotional_notes TEXT,
  solution_notes TEXT,
  commitment_notes TEXT,
  notes TEXT,

  -- Challenger Section Checklists (JSON arrays with {id, text, completed})
  discovery_checklist JSONB DEFAULT '[]'::jsonb,
  rational_checklist JSONB DEFAULT '[]'::jsonb,
  emotional_checklist JSONB DEFAULT '[]'::jsonb,
  solution_checklist JSONB DEFAULT '[]'::jsonb,
  commitment_checklist JSONB DEFAULT '[]'::jsonb,

  -- Additional Fields
  product_positioning TEXT,
  outreach_a TEXT,
  outreach_b TEXT,

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

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_battle_cards_lead_id ON battle_cards(lead_id);
CREATE INDEX IF NOT EXISTS idx_battle_cards_card_status ON battle_cards(card_status);
CREATE INDEX IF NOT EXISTS idx_battle_cards_stage ON battle_cards(stage);
CREATE INDEX IF NOT EXISTS idx_battle_cards_score ON battle_cards(score DESC);
CREATE INDEX IF NOT EXISTS idx_battle_cards_created_at ON battle_cards(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_battle_cards_call_history_gin ON battle_cards USING GIN (call_history);

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

DROP TRIGGER IF EXISTS update_battle_cards_timestamp ON battle_cards;
CREATE TRIGGER update_battle_cards_timestamp
  BEFORE UPDATE ON battle_cards
  FOR EACH ROW
  EXECUTE FUNCTION update_battle_cards_updated_at();

-- Comments
COMMENT ON TABLE battle_cards IS 'Battle cards shown in CRM inbox - one lead can have many cards';
COMMENT ON COLUMN battle_cards.lead_id IS 'References leads(id) - which contact this card belongs to';
COMMENT ON COLUMN battle_cards.title IS 'Card title shown in inbox (e.g., "Enterprise Security Journey")';
COMMENT ON COLUMN battle_cards.discovery_checklist IS 'Discovery checklist JSON: [{id, text, completed}]';
COMMENT ON COLUMN battle_cards.rational_checklist IS 'Rational Drowning checklist JSON: [{id, text, completed}]';
COMMENT ON COLUMN battle_cards.emotional_checklist IS 'Emotional Impact checklist JSON: [{id, text, completed}]';
COMMENT ON COLUMN battle_cards.solution_checklist IS 'Solution checklist JSON: [{id, text, completed}]';
COMMENT ON COLUMN battle_cards.commitment_checklist IS 'Commitment checklist JSON: [{id, text, completed}]';
COMMENT ON COLUMN battle_cards.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 4: HELPER VIEW (Cards with Lead Info)
-- ============================================

-- Drop existing view to avoid column name conflicts when updating
DROP VIEW IF EXISTS battle_cards_with_leads;

CREATE VIEW battle_cards_with_leads AS
SELECT
  -- Card fields (use card.id as main id for the API)
  bc.id,
  bc.lead_id,
  bc.title,
  bc.description,
  bc.card_type,
  bc.card_status,
  bc.stage,
  bc.score,

  -- Challenger section notes
  bc.discovery_notes,
  bc.rational_notes,
  bc.emotional_notes,
  bc.solution_notes,
  bc.commitment_notes,
  bc.notes,

  -- Challenger checklists
  bc.discovery_checklist,
  bc.rational_checklist,
  bc.emotional_checklist,
  bc.solution_checklist,
  bc.commitment_checklist,

  -- Additional fields
  bc.product_positioning,
  bc.outreach_a,
  bc.outreach_b,

  -- Practice call history
  bc.call_history,

  -- Card timestamps
  bc.created_at,
  bc.updated_at,

  -- Lead info (for display in inbox)
  l.name,
  l.email,
  l.company,
  l.role,
  l.phone
FROM battle_cards bc
JOIN leads l ON bc.lead_id = l.id
ORDER BY bc.card_status ASC, bc.score DESC, bc.updated_at DESC;

COMMENT ON VIEW battle_cards_with_leads IS 'Battle cards joined with lead info - used by /crm/cards inbox';

-- ============================================
-- STEP 5: CRM_CALLS TABLE (Bland AI Integration)
-- ============================================

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

  -- Lead reference (nullable - practice calls don't require a lead)
  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
CREATE OR REPLACE FUNCTION update_crm_calls_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

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

-- ============================================
-- STEP 4: VALIDATION
-- ============================================

-- Check tables exist
DO $$
DECLARE
  leads_exists BOOLEAN;
  cards_exists BOOLEAN;
  calls_exists BOOLEAN;
BEGIN
  SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_schema = 'public' AND table_name = 'leads'
  ) INTO leads_exists;

  SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_schema = 'public' AND table_name = 'battle_cards'
  ) INTO cards_exists;

  SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_schema = 'public' AND table_name = 'crm_calls'
  ) INTO calls_exists;

  IF leads_exists AND cards_exists AND calls_exists THEN
    RAISE NOTICE '✅ SETUP COMPLETE! All tables exist.';
    RAISE NOTICE '   - leads table: Contact information';
    RAISE NOTICE '   - battle_cards table: CRM inbox cards';
    RAISE NOTICE '   - crm_calls table: Bland AI integration';
  ELSE
    IF NOT leads_exists THEN
      RAISE WARNING '⚠️  leads table is missing!';
    END IF;
    IF NOT cards_exists THEN
      RAISE WARNING '⚠️  battle_cards table is missing!';
    END IF;
    IF NOT calls_exists THEN
      RAISE WARNING '⚠️  crm_calls table is missing!';
    END IF;
  END IF;
END $$;

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

DO $$
BEGIN
  RAISE NOTICE '';
  RAISE NOTICE '🎉 BATTLE CARDS SCHEMA READY!';
  RAISE NOTICE '================================';
  RAISE NOTICE '';
  RAISE NOTICE 'IMPORTANT - TWO-TABLE STRUCTURE:';
  RAISE NOTICE '  📁 leads = Contact info (email, name, company)';
  RAISE NOTICE '  📋 battle_cards = Cards shown in GUI (one lead → many cards)';
  RAISE NOTICE '';
  RAISE NOTICE '⚠️  GUI ONLY shows battle_cards, NEVER raw leads!';
  RAISE NOTICE '';
  RAISE NOTICE 'Next Steps:';
  RAISE NOTICE '  1. Go to https://thetacoach.biz/crm/cards';
  RAISE NOTICE '  2. Click "Remind Claude" button';
  RAISE NOTICE '  3. Paste the prompt into Claude Code';
  RAISE NOTICE '';
  RAISE NOTICE '🚀 Your battle cards are ready!';
END $$;
