263 lines
7.5 KiB
PL/PgSQL
263 lines
7.5 KiB
PL/PgSQL
-- Add pricing fields to leads table
|
|
ALTER TABLE leads ADD COLUMN IF NOT EXISTS base_price INTEGER DEFAULT 20;
|
|
ALTER TABLE leads ADD COLUMN IF NOT EXISTS calculated_price INTEGER;
|
|
ALTER TABLE leads ADD COLUMN IF NOT EXISTS override_price INTEGER;
|
|
ALTER TABLE leads ADD COLUMN IF NOT EXISTS final_price INTEGER;
|
|
|
|
-- Add price_paid to lead_purchases to track actual price at purchase time
|
|
ALTER TABLE lead_purchases ADD COLUMN IF NOT EXISTS price_paid INTEGER;
|
|
|
|
-- Function to calculate lead price based on activities
|
|
CREATE OR REPLACE FUNCTION calculate_lead_price(
|
|
p_base_price INTEGER,
|
|
p_planned_activities JSONB
|
|
)
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
v_multiplier NUMERIC := 1.0;
|
|
v_activity JSONB;
|
|
v_activity_type TEXT;
|
|
v_activity_name TEXT;
|
|
BEGIN
|
|
-- Base price validation
|
|
IF p_base_price IS NULL OR p_base_price <= 0 THEN
|
|
p_base_price := 20;
|
|
END IF;
|
|
|
|
-- If no activities, return base price
|
|
IF p_planned_activities IS NULL OR jsonb_array_length(p_planned_activities) = 0 THEN
|
|
RETURN p_base_price;
|
|
END IF;
|
|
|
|
-- Loop through activities and apply multipliers
|
|
FOR v_activity IN SELECT * FROM jsonb_array_elements(p_planned_activities)
|
|
LOOP
|
|
-- Get activity type and name
|
|
v_activity_type := LOWER(COALESCE(v_activity->>'type', ''));
|
|
v_activity_name := LOWER(COALESCE(v_activity->>'name', ''));
|
|
|
|
-- Check for Hot Air Balloon (+100% = 2x multiplier)
|
|
IF v_activity_type LIKE '%balloon%' OR v_activity_name LIKE '%balloon%' OR
|
|
v_activity_type LIKE '%hot air%' OR v_activity_name LIKE '%hot air%' THEN
|
|
v_multiplier := v_multiplier * 2.0;
|
|
END IF;
|
|
|
|
-- Check for ATV or Horse Riding (+50% = 1.5x multiplier)
|
|
IF v_activity_type LIKE '%atv%' OR v_activity_name LIKE '%atv%' OR
|
|
v_activity_type LIKE '%horse%' OR v_activity_name LIKE '%horse%' OR
|
|
v_activity_type LIKE '%riding%' OR v_activity_name LIKE '%riding%' THEN
|
|
v_multiplier := v_multiplier * 1.5;
|
|
END IF;
|
|
|
|
-- Check for Guided Tour (+40% = 1.4x multiplier)
|
|
IF v_activity_type LIKE '%guided%' OR v_activity_name LIKE '%guided%' OR
|
|
v_activity_type LIKE '%tour%' OR v_activity_name LIKE '%tour%' THEN
|
|
v_multiplier := v_multiplier * 1.4;
|
|
END IF;
|
|
END LOOP;
|
|
|
|
-- Calculate final price (rounded to nearest integer)
|
|
RETURN ROUND(p_base_price * v_multiplier);
|
|
END;
|
|
$$ LANGUAGE plpgsql IMMUTABLE;
|
|
|
|
-- Function to update lead pricing (called on insert/update)
|
|
CREATE OR REPLACE FUNCTION update_lead_pricing()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Calculate price based on activities
|
|
NEW.calculated_price := calculate_lead_price(
|
|
COALESCE(NEW.base_price, 20),
|
|
NEW.planned_activities
|
|
);
|
|
|
|
-- Set final price (override takes precedence)
|
|
NEW.final_price := COALESCE(NEW.override_price, NEW.calculated_price);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Create trigger to automatically update pricing
|
|
DROP TRIGGER IF EXISTS trigger_update_lead_pricing ON leads;
|
|
CREATE TRIGGER trigger_update_lead_pricing
|
|
BEFORE INSERT OR UPDATE OF base_price, planned_activities, override_price
|
|
ON leads
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_lead_pricing();
|
|
|
|
-- Update existing leads with calculated prices
|
|
UPDATE leads
|
|
SET
|
|
base_price = COALESCE(base_price, 20),
|
|
calculated_price = calculate_lead_price(COALESCE(base_price, 20), planned_activities),
|
|
final_price = COALESCE(override_price, calculate_lead_price(COALESCE(base_price, 20), planned_activities));
|
|
|
|
-- Function for admin to override lead price
|
|
CREATE OR REPLACE FUNCTION admin_override_lead_price(
|
|
p_lead_id UUID,
|
|
p_override_price INTEGER,
|
|
p_admin_id UUID
|
|
)
|
|
RETURNS JSON AS $$
|
|
DECLARE
|
|
v_old_price INTEGER;
|
|
v_new_price INTEGER;
|
|
BEGIN
|
|
-- Verify admin role
|
|
IF NOT EXISTS (SELECT 1 FROM profiles WHERE id = p_admin_id AND role = 'admin') THEN
|
|
RAISE EXCEPTION 'Unauthorized: Admin access required';
|
|
END IF;
|
|
|
|
-- Validate price
|
|
IF p_override_price IS NOT NULL AND p_override_price < 0 THEN
|
|
RAISE EXCEPTION 'Price cannot be negative';
|
|
END IF;
|
|
|
|
-- Get old price
|
|
SELECT final_price INTO v_old_price FROM leads WHERE id = p_lead_id;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'Lead not found';
|
|
END IF;
|
|
|
|
-- Update override price (NULL removes override)
|
|
UPDATE leads
|
|
SET
|
|
override_price = p_override_price,
|
|
final_price = COALESCE(p_override_price, calculated_price),
|
|
updated_at = NOW()
|
|
WHERE id = p_lead_id
|
|
RETURNING final_price INTO v_new_price;
|
|
|
|
RETURN json_build_object(
|
|
'success', true,
|
|
'lead_id', p_lead_id,
|
|
'old_price', v_old_price,
|
|
'new_price', v_new_price,
|
|
'override_applied', p_override_price IS NOT NULL
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Update the purchase_lead function to use final_price and record it
|
|
CREATE OR REPLACE FUNCTION purchase_lead(
|
|
p_provider_id UUID,
|
|
p_lead_id UUID
|
|
)
|
|
RETURNS JSON AS $$
|
|
DECLARE
|
|
v_wallet_id UUID;
|
|
v_current_balance INTEGER;
|
|
v_lead_price INTEGER;
|
|
v_new_balance INTEGER;
|
|
v_purchase_id UUID;
|
|
BEGIN
|
|
-- Get lead price
|
|
SELECT final_price INTO v_lead_price
|
|
FROM leads
|
|
WHERE id = p_lead_id;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'Lead not found';
|
|
END IF;
|
|
|
|
-- Default to 20 if price not set
|
|
v_lead_price := COALESCE(v_lead_price, 20);
|
|
|
|
-- Check if already purchased
|
|
IF EXISTS (
|
|
SELECT 1 FROM lead_purchases
|
|
WHERE provider_id = p_provider_id AND lead_id = p_lead_id
|
|
) THEN
|
|
RAISE EXCEPTION 'Lead already purchased';
|
|
END IF;
|
|
|
|
-- Get wallet and lock row
|
|
SELECT id, credit_balance INTO v_wallet_id, v_current_balance
|
|
FROM provider_wallets
|
|
WHERE provider_id = p_provider_id
|
|
FOR UPDATE;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'Provider wallet not found';
|
|
END IF;
|
|
|
|
-- Check sufficient balance
|
|
IF v_current_balance < v_lead_price THEN
|
|
RAISE EXCEPTION 'Insufficient credits. Required: %, Available: %', v_lead_price, v_current_balance;
|
|
END IF;
|
|
|
|
-- Calculate new balance
|
|
v_new_balance := v_current_balance - v_lead_price;
|
|
|
|
-- Update wallet
|
|
UPDATE provider_wallets
|
|
SET
|
|
credit_balance = v_new_balance,
|
|
updated_at = NOW()
|
|
WHERE id = v_wallet_id;
|
|
|
|
-- Record purchase with price paid
|
|
INSERT INTO lead_purchases (provider_id, lead_id, credits_spent, price_paid)
|
|
VALUES (p_provider_id, p_lead_id, v_lead_price, v_lead_price)
|
|
RETURNING id INTO v_purchase_id;
|
|
|
|
-- Record transaction
|
|
INSERT INTO credit_transactions (
|
|
wallet_id,
|
|
transaction_type,
|
|
amount,
|
|
description,
|
|
balance_after
|
|
) VALUES (
|
|
v_wallet_id,
|
|
'purchase',
|
|
v_lead_price,
|
|
'Lead purchase: ' || p_lead_id::TEXT,
|
|
v_new_balance
|
|
);
|
|
|
|
RETURN json_build_object(
|
|
'success', true,
|
|
'purchase_id', v_purchase_id,
|
|
'credits_spent', v_lead_price,
|
|
'new_balance', v_new_balance
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Update admin_lead_stats view to include pricing information
|
|
DROP VIEW IF EXISTS admin_lead_stats;
|
|
CREATE OR REPLACE VIEW admin_lead_stats AS
|
|
SELECT
|
|
l.id,
|
|
l.destination,
|
|
l.country,
|
|
l.start_date,
|
|
l.end_date,
|
|
l.number_of_travelers,
|
|
l.interests,
|
|
l.email,
|
|
l.whatsapp,
|
|
l.planned_activities,
|
|
l.timeline_snapshot,
|
|
l.base_price,
|
|
l.calculated_price,
|
|
l.override_price,
|
|
l.final_price,
|
|
l.created_at,
|
|
COALESCE(COUNT(DISTINCT lp.provider_id), 0) as providers_unlocked,
|
|
COALESCE(SUM(lp.credits_spent), 0) as total_revenue,
|
|
CASE
|
|
WHEN COUNT(lp.id) = 0 THEN 'new'
|
|
WHEN l.end_date < CURRENT_DATE THEN 'expired'
|
|
ELSE 'sold'
|
|
END as status
|
|
FROM leads l
|
|
LEFT JOIN lead_purchases lp ON l.id = lp.lead_id
|
|
GROUP BY l.id;
|
|
|
|
-- Grant permissions
|
|
GRANT EXECUTE ON FUNCTION calculate_lead_price(INTEGER, JSONB) TO authenticated;
|
|
GRANT EXECUTE ON FUNCTION admin_override_lead_price(UUID, INTEGER, UUID) TO authenticated; |