136 lines
3.1 KiB
PL/PgSQL
136 lines
3.1 KiB
PL/PgSQL
-- Function to purchase a lead (atomic transaction)
|
|
CREATE OR REPLACE FUNCTION purchase_lead(
|
|
p_provider_id UUID,
|
|
p_lead_id UUID,
|
|
p_credits_cost INTEGER
|
|
)
|
|
RETURNS JSON AS $$
|
|
DECLARE
|
|
v_current_balance INTEGER;
|
|
v_purchase_id UUID;
|
|
v_transaction_id UUID;
|
|
BEGIN
|
|
-- Get current balance
|
|
SELECT credit_balance INTO v_current_balance
|
|
FROM provider_wallets
|
|
WHERE provider_id = p_provider_id
|
|
FOR UPDATE; -- Lock the row
|
|
|
|
-- Check if wallet exists
|
|
IF v_current_balance IS NULL THEN
|
|
RETURN json_build_object(
|
|
'success', false,
|
|
'error', 'Wallet not found'
|
|
);
|
|
END IF;
|
|
|
|
-- Check sufficient balance
|
|
IF v_current_balance < p_credits_cost THEN
|
|
RETURN json_build_object(
|
|
'success', false,
|
|
'error', 'Insufficient credits'
|
|
);
|
|
END IF;
|
|
|
|
-- Check if already purchased
|
|
IF EXISTS (
|
|
SELECT 1 FROM lead_purchases
|
|
WHERE provider_id = p_provider_id AND lead_id = p_lead_id
|
|
) THEN
|
|
RETURN json_build_object(
|
|
'success', false,
|
|
'error', 'Lead already purchased'
|
|
);
|
|
END IF;
|
|
|
|
-- Create purchase record
|
|
INSERT INTO lead_purchases (provider_id, lead_id, credits_spent)
|
|
VALUES (p_provider_id, p_lead_id, p_credits_cost)
|
|
RETURNING id INTO v_purchase_id;
|
|
|
|
-- Deduct credits
|
|
UPDATE provider_wallets
|
|
SET credit_balance = credit_balance - p_credits_cost
|
|
WHERE provider_id = p_provider_id;
|
|
|
|
-- Record transaction
|
|
INSERT INTO credit_transactions (
|
|
provider_id,
|
|
amount,
|
|
transaction_type,
|
|
description,
|
|
reference_id,
|
|
balance_after
|
|
)
|
|
VALUES (
|
|
p_provider_id,
|
|
-p_credits_cost,
|
|
'lead_purchase',
|
|
'Lead purchase',
|
|
v_purchase_id,
|
|
v_current_balance - p_credits_cost
|
|
)
|
|
RETURNING id INTO v_transaction_id;
|
|
|
|
RETURN json_build_object(
|
|
'success', true,
|
|
'purchase_id', v_purchase_id,
|
|
'transaction_id', v_transaction_id,
|
|
'new_balance', v_current_balance - p_credits_cost
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Function to add credits to wallet
|
|
CREATE OR REPLACE FUNCTION add_credits(
|
|
p_provider_id UUID,
|
|
p_amount INTEGER,
|
|
p_description TEXT DEFAULT 'Credit purchase'
|
|
)
|
|
RETURNS JSON AS $$
|
|
DECLARE
|
|
v_current_balance INTEGER;
|
|
v_new_balance INTEGER;
|
|
v_transaction_id UUID;
|
|
BEGIN
|
|
-- Get or create wallet
|
|
INSERT INTO provider_wallets (provider_id, credit_balance)
|
|
VALUES (p_provider_id, 0)
|
|
ON CONFLICT (provider_id) DO NOTHING;
|
|
|
|
-- Get current balance
|
|
SELECT credit_balance INTO v_current_balance
|
|
FROM provider_wallets
|
|
WHERE provider_id = p_provider_id
|
|
FOR UPDATE;
|
|
|
|
-- Add credits
|
|
UPDATE provider_wallets
|
|
SET credit_balance = credit_balance + p_amount
|
|
WHERE provider_id = p_provider_id
|
|
RETURNING credit_balance INTO v_new_balance;
|
|
|
|
-- Record transaction
|
|
INSERT INTO credit_transactions (
|
|
provider_id,
|
|
amount,
|
|
transaction_type,
|
|
description,
|
|
balance_after
|
|
)
|
|
VALUES (
|
|
p_provider_id,
|
|
p_amount,
|
|
'credit_purchase',
|
|
p_description,
|
|
v_new_balance
|
|
)
|
|
RETURNING id INTO v_transaction_id;
|
|
|
|
RETURN json_build_object(
|
|
'success', true,
|
|
'transaction_id', v_transaction_id,
|
|
'new_balance', v_new_balance
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER; |