89 lines
2.7 KiB
PL/PgSQL
89 lines
2.7 KiB
PL/PgSQL
-- 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;
|