-- 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;