-- Add Persona Engine fields to leads table
ALTER TABLE leads 
ADD COLUMN IF NOT EXISTS tourist_persona JSONB,
ADD COLUMN IF NOT EXISTS persona_confidence DECIMAL(3,2) CHECK (persona_confidence >= 0 AND persona_confidence <= 1);

-- Create index for persona queries
CREATE INDEX IF NOT EXISTS idx_leads_persona_type ON leads ((tourist_persona->>'type'));
CREATE INDEX IF NOT EXISTS idx_leads_persona_confidence ON leads (persona_confidence);
CREATE INDEX IF NOT EXISTS idx_leads_spend_potential ON leads ((tourist_persona->>'spend_potential'));

-- Add comment for documentation
COMMENT ON COLUMN leads.tourist_persona IS 'AI-detected tourist persona with type, labels, emoji, description, spend_potential, key_signals, and recommended_services';
COMMENT ON COLUMN leads.persona_confidence IS 'Confidence score (0-1) for persona detection accuracy';

-- Create function to get leads with high-value personas
CREATE OR REPLACE FUNCTION get_high_value_leads()
RETURNS TABLE (
  id UUID,
  email TEXT,
  whatsapp TEXT,
  country TEXT,
  destination TEXT,
  start_date DATE,
  end_date DATE,
  number_of_travelers INTEGER,
  persona_type TEXT,
  spend_potential TEXT,
  persona_confidence DECIMAL,
  created_at TIMESTAMPTZ
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    l.id,
    l.email,
    l.whatsapp,
    l.country,
    l.destination,
    l.start_date,
    l.end_date,
    l.number_of_travelers,
    l.tourist_persona->>'type' AS persona_type,
    l.tourist_persona->>'spend_potential' AS spend_potential,
    l.persona_confidence,
    l.created_at
  FROM leads l
  WHERE 
    l.tourist_persona->>'spend_potential' IN ('high', 'very_high')
    AND l.persona_confidence >= 0.7
    AND l.status = 'new'
  ORDER BY 
    CASE l.tourist_persona->>'spend_potential'
      WHEN 'very_high' THEN 1
      WHEN 'high' THEN 2
      ELSE 3
    END,
    l.persona_confidence DESC,
    l.created_at DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION get_high_value_leads() TO authenticated;

-- Create function to get persona statistics
CREATE OR REPLACE FUNCTION get_persona_statistics()
RETURNS TABLE (
  persona_type TEXT,
  count BIGINT,
  avg_confidence DECIMAL,
  avg_travelers DECIMAL
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    l.tourist_persona->>'type' AS persona_type,
    COUNT(*) AS count,
    ROUND(AVG(l.persona_confidence)::NUMERIC, 2) AS avg_confidence,
    ROUND(AVG(l.number_of_travelers)::NUMERIC, 1) AS avg_travelers
  FROM leads l
  WHERE l.tourist_persona IS NOT NULL
  GROUP BY l.tourist_persona->>'type'
  ORDER BY count DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION get_persona_statistics() TO authenticated;
