-- Lead Dossier: Structured living document per lead
-- Accumulates intel from all interactions (calls, SMS, research)

CREATE TABLE IF NOT EXISTS lead_dossier (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID NOT NULL REFERENCES leads(id) ON DELETE CASCADE,

  -- Key facts learned about the lead
  key_facts JSONB DEFAULT '[]'::jsonb,
  -- Array of: { fact: "Has 3 kids", source: "call", learned_at: timestamp }

  -- Business/professional info
  business_intel JSONB DEFAULT '{}'::jsonb,
  -- { company_size, industry, pain_points: [], budget_range, decision_timeline }

  -- Personal rapport info
  personal_intel JSONB DEFAULT '{}'::jsonb,
  -- { family, hobbies: [], interests: [], conversation_starters: [] }

  -- Call history summaries (not full transcripts, just key points)
  call_summaries JSONB DEFAULT '[]'::jsonb,
  -- Array of: { date, duration, outcome, key_points: [], next_steps, mood }

  -- Objections encountered and how they were handled
  objections JSONB DEFAULT '[]'::jsonb,
  -- Array of: { objection, response_used, effective: true/false, date }

  -- What's worked with this lead
  what_works JSONB DEFAULT '[]'::jsonb,
  -- Array of: { tactic, result, date }

  -- Current status and next steps
  current_status JSONB DEFAULT '{}'::jsonb,
  -- { stage, temperature, blockers: [], next_action, follow_up_date }

  -- AI-generated overall summary (regenerated periodically)
  ai_summary TEXT,
  ai_summary_updated_at TIMESTAMPTZ,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- One dossier per lead
CREATE UNIQUE INDEX IF NOT EXISTS idx_lead_dossier_lead_id ON lead_dossier(lead_id);

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

DROP TRIGGER IF EXISTS lead_dossier_updated_at ON lead_dossier;
CREATE TRIGGER lead_dossier_updated_at
  BEFORE UPDATE ON lead_dossier
  FOR EACH ROW
  EXECUTE FUNCTION update_lead_dossier_updated_at();

-- Enable RLS
ALTER TABLE lead_dossier ENABLE ROW LEVEL SECURITY;

-- Policy: authenticated users can read/write
CREATE POLICY "Authenticated users can manage lead dossiers"
  ON lead_dossier
  FOR ALL
  TO authenticated
  USING (true)
  WITH CHECK (true);
