-- Deal Intelligence Migration
-- Tracks objection outcomes and deal velocity for pattern analysis

-- ============================================
-- DEAL STAGES TABLE
-- Track lead progression through sales funnel
-- ============================================

CREATE TABLE IF NOT EXISTS deal_stages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  stage VARCHAR(50) NOT NULL,
  -- 'new', 'contacted', 'discovery', 'proposal', 'negotiation', 'won', 'lost'
  entered_at TIMESTAMPTZ DEFAULT NOW(),
  exited_at TIMESTAMPTZ,
  duration_hours INTEGER,
  notes TEXT,
  metadata JSONB DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS idx_deal_stages_lead ON deal_stages(lead_id);
CREATE INDEX IF NOT EXISTS idx_deal_stages_stage ON deal_stages(stage);
CREATE INDEX IF NOT EXISTS idx_deal_stages_entered ON deal_stages(entered_at DESC);

-- ============================================
-- DEAL OUTCOMES TABLE
-- Win/Loss recording with reasons
-- ============================================

CREATE TABLE IF NOT EXISTS deal_outcomes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE UNIQUE,
  outcome VARCHAR(20) NOT NULL, -- 'won', 'lost', 'stalled'
  primary_reason VARCHAR(100),
  secondary_reasons TEXT[],
  competitor_lost_to VARCHAR(100),
  deal_value DECIMAL(10,2),
  time_to_close_days INTEGER,
  touchpoints_count INTEGER,
  objections_encountered TEXT[],
  winning_response TEXT,
  rep_notes TEXT,
  recorded_at TIMESTAMPTZ DEFAULT NOW(),
  recorded_by UUID
);

CREATE INDEX IF NOT EXISTS idx_deal_outcomes_outcome ON deal_outcomes(outcome);
CREATE INDEX IF NOT EXISTS idx_deal_outcomes_reason ON deal_outcomes(primary_reason);
CREATE INDEX IF NOT EXISTS idx_deal_outcomes_recorded ON deal_outcomes(recorded_at DESC);

-- ============================================
-- EXTEND OBJECTION_RESPONSES TABLE
-- Add outcome tracking columns
-- ============================================

ALTER TABLE objection_responses
  ADD COLUMN IF NOT EXISTS led_to_conversion_bool BOOLEAN,
  ADD COLUMN IF NOT EXISTS lead_final_outcome VARCHAR(50),
  ADD COLUMN IF NOT EXISTS days_to_outcome INTEGER;

-- ============================================
-- OBJECTION IMPACT STATS VIEW
-- Pre-calculated view for fast queries
-- ============================================

CREATE OR REPLACE VIEW objection_impact_stats AS
SELECT
  objection_type,
  COUNT(*) as total_occurrences,
  SUM(CASE WHEN led_to_conversion_bool = true THEN 1 ELSE 0 END) as converted_count,
  ROUND(
    (SUM(CASE WHEN led_to_conversion_bool = true THEN 1 ELSE 0 END)::DECIMAL / NULLIF(COUNT(*), 0)) * 100,
    1
  ) as conversion_rate,
  CASE
    WHEN (SUM(CASE WHEN led_to_conversion_bool = true THEN 1 ELSE 0 END)::DECIMAL / NULLIF(COUNT(*), 0)) > 0.3
    THEN 'speed_bump'
    ELSE 'deal_killer'
  END as classification,
  AVG(days_to_outcome) as avg_days_to_outcome
FROM objection_responses
WHERE led_to_conversion_bool IS NOT NULL
GROUP BY objection_type;

-- ============================================
-- COMMON LOSS REASONS
-- Seed data for win/loss modal
-- ============================================

CREATE TABLE IF NOT EXISTS loss_reasons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  reason VARCHAR(100) NOT NULL UNIQUE,
  category VARCHAR(50),
  display_order INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true
);

INSERT INTO loss_reasons (reason, category, display_order) VALUES
  ('Price/Budget', 'financial', 1),
  ('Chose Competitor', 'competition', 2),
  ('Timing Not Right', 'timing', 3),
  ('No Response', 'engagement', 4),
  ('Not a Fit', 'qualification', 5),
  ('Internal Decision', 'internal', 6),
  ('Already Has Provider', 'competition', 7),
  ('Project Cancelled', 'external', 8),
  ('Contact Changed', 'engagement', 9),
  ('Other', 'other', 99)
ON CONFLICT (reason) DO NOTHING;

-- ============================================
-- RLS POLICIES
-- ============================================

ALTER TABLE deal_stages ENABLE ROW LEVEL SECURITY;
ALTER TABLE deal_outcomes ENABLE ROW LEVEL SECURITY;
ALTER TABLE loss_reasons ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to read/write deal data
CREATE POLICY "Users can view deal stages"
  ON deal_stages FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can insert deal stages"
  ON deal_stages FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Users can update deal stages"
  ON deal_stages FOR UPDATE
  TO authenticated
  USING (true);

CREATE POLICY "Users can view deal outcomes"
  ON deal_outcomes FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can insert deal outcomes"
  ON deal_outcomes FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Users can update deal outcomes"
  ON deal_outcomes FOR UPDATE
  TO authenticated
  USING (true);

CREATE POLICY "Users can view loss reasons"
  ON loss_reasons FOR SELECT
  TO authenticated
  USING (true);
