CREATE OR REPLACE FUNCTION public.distribute_company_profit(p_profit_id uuid)
 RETURNS jsonb
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  v_profit public.company_profits%ROWTYPE;
  v_total_active NUMERIC := 0;
  v_pool NUMERIC := 0;
  v_count INT := 0;
  v_fixed_count INT := 0;
  inv RECORD;
  fx RECORD;
  v_ratio NUMERIC;
  v_amount NUMERIC;
BEGIN
  SELECT * INTO v_profit FROM public.company_profits WHERE id = p_profit_id FOR UPDATE;
  IF v_profit.id IS NULL THEN RAISE EXCEPTION 'Profit record not found'; END IF;
  IF v_profit.status = 'distributed' THEN RAISE EXCEPTION 'Already distributed'; END IF;
  IF v_profit.status <> 'locked' THEN RAISE EXCEPTION 'Profit must be locked before distribution'; END IF;

  v_pool := COALESCE(v_profit.distributable_amount, 0);

  -- Pool eligibility: only profit_sharing investments
  SELECT COALESCE(SUM(i.amount), 0) INTO v_total_active
    FROM public.investments i
    JOIN public.investors inv2 ON inv2.id = i.investor_id
   WHERE i.status = 'active' AND inv2.status = 'active'
     AND COALESCE(i.investment_type, 'profit_sharing') = 'profit_sharing';

  DELETE FROM public.investor_profit_distributions WHERE company_profit_id = p_profit_id;

  -- Profit sharing distribution
  IF v_total_active > 0 AND v_pool > 0 THEN
    FOR inv IN
      SELECT i.investor_id, SUM(i.amount) AS amt
        FROM public.investments i
        JOIN public.investors inv2 ON inv2.id = i.investor_id
       WHERE i.status = 'active' AND inv2.status = 'active'
         AND COALESCE(i.investment_type, 'profit_sharing') = 'profit_sharing'
       GROUP BY i.investor_id
    LOOP
      v_ratio := inv.amt / v_total_active;
      v_amount := ROUND(v_pool * v_ratio, 2);

      INSERT INTO public.investor_profit_distributions
        (company_profit_id, investor_id, investment_amount, investment_ratio, distributed_amount, status)
      VALUES (p_profit_id, inv.investor_id, inv.amt, v_ratio, v_amount, 'paid');

      INSERT INTO public.investor_wallets (investor_id, total_earned, balance)
      VALUES (inv.investor_id, v_amount, v_amount)
      ON CONFLICT (investor_id) DO UPDATE
        SET total_earned = public.investor_wallets.total_earned + v_amount,
            balance = public.investor_wallets.balance + v_amount,
            updated_at = now();

      v_count := v_count + 1;
    END LOOP;
  END IF;

  -- Fixed-rate distribution: amount * fixed_rate% for the month
  FOR fx IN
    SELECT i.investor_id, i.id AS investment_id, i.amount, COALESCE(i.fixed_rate, 0) AS rate
      FROM public.investments i
      JOIN public.investors inv2 ON inv2.id = i.investor_id
     WHERE i.status = 'active' AND inv2.status = 'active'
       AND i.investment_type = 'fixed'
       AND COALESCE(i.fixed_rate, 0) > 0
  LOOP
    v_amount := ROUND(fx.amount * fx.rate / 100.0, 2);
    IF v_amount > 0 THEN
      INSERT INTO public.investor_profit_distributions
        (company_profit_id, investor_id, investment_amount, investment_ratio, distributed_amount, status, adjustment_reason)
      VALUES (p_profit_id, fx.investor_id, fx.amount, 0, v_amount, 'paid', 'fixed_rate');

      INSERT INTO public.investor_wallets (investor_id, total_earned, balance)
      VALUES (fx.investor_id, v_amount, v_amount)
      ON CONFLICT (investor_id) DO UPDATE
        SET total_earned = public.investor_wallets.total_earned + v_amount,
            balance = public.investor_wallets.balance + v_amount,
            updated_at = now();
      v_fixed_count := v_fixed_count + 1;
    END IF;
  END LOOP;

  IF v_count = 0 AND v_fixed_count = 0 THEN
    RAISE EXCEPTION 'No eligible investments to distribute';
  END IF;

  UPDATE public.company_profits
     SET status = 'distributed', distributed_at = now()
   WHERE id = p_profit_id;

  RETURN jsonb_build_object('ok', true, 'investors', v_count, 'fixed', v_fixed_count, 'pool', v_pool, 'total_active', v_total_active);
END;
$function$;