84 lines
2.3 KiB
PL/PgSQL
84 lines
2.3 KiB
PL/PgSQL
-- Create leads table for qualified trip leads
|
|
CREATE TABLE IF NOT EXISTS leads (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
trip_id UUID REFERENCES trips(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
|
|
-- Trip details snapshot
|
|
destination TEXT,
|
|
start_date DATE,
|
|
end_date DATE,
|
|
number_of_travelers INTEGER,
|
|
interests TEXT[], -- Array of interest tags
|
|
planned_activities JSONB, -- Snapshot of activities
|
|
timeline_snapshot JSONB, -- Full timeline data
|
|
|
|
-- Contact information
|
|
email TEXT NOT NULL,
|
|
whatsapp TEXT NOT NULL,
|
|
country TEXT NOT NULL,
|
|
|
|
-- Consent and status
|
|
consent_given BOOLEAN DEFAULT false NOT NULL,
|
|
status TEXT DEFAULT 'new' CHECK (status IN ('new', 'contacted', 'converted', 'closed')),
|
|
|
|
-- Metadata
|
|
trigger_source TEXT, -- 'save_modal', 'planner_cta', 'contextual_banner'
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Create index for faster queries
|
|
CREATE INDEX IF NOT EXISTS idx_leads_trip_id ON leads(trip_id);
|
|
CREATE INDEX IF NOT EXISTS idx_leads_user_id ON leads(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status);
|
|
CREATE INDEX IF NOT EXISTS idx_leads_created_at ON leads(created_at DESC);
|
|
|
|
-- Enable RLS
|
|
ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Policy: Users can view their own leads
|
|
CREATE POLICY "Users can view own leads"
|
|
ON leads FOR SELECT
|
|
USING (auth.uid() = user_id OR user_id IS NULL);
|
|
|
|
-- Policy: Anyone can create leads (with consent)
|
|
CREATE POLICY "Anyone can create leads"
|
|
ON leads FOR INSERT
|
|
WITH CHECK (consent_given = true);
|
|
|
|
-- Policy: Admins can view all leads
|
|
CREATE POLICY "Admins can view all leads"
|
|
ON leads FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM profiles
|
|
WHERE profiles.id = auth.uid()
|
|
AND profiles.role = 'admin'
|
|
)
|
|
);
|
|
|
|
-- Policy: Admins can update leads
|
|
CREATE POLICY "Admins can update leads"
|
|
ON leads FOR UPDATE
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM profiles
|
|
WHERE profiles.id = auth.uid()
|
|
AND profiles.role = 'admin'
|
|
)
|
|
);
|
|
|
|
-- Add updated_at trigger
|
|
CREATE OR REPLACE FUNCTION update_leads_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER leads_updated_at
|
|
BEFORE UPDATE ON leads
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_leads_updated_at(); |