ALTER VIEW public.v_account_balances SET (security_invoker = on);
ALTER VIEW public.v_general_ledger SET (security_invoker = on);
REVOKE EXECUTE ON FUNCTION public.next_number(text) FROM anon, public;
REVOKE EXECUTE ON FUNCTION public.post_journal_entry(date, text, text, uuid, jsonb) FROM anon, public;
REVOKE EXECUTE ON FUNCTION public.reverse_journal_entry(uuid, date, text) FROM anon, public;

CREATE OR REPLACE FUNCTION public.account_id_by_code(_code text) RETURNS uuid
LANGUAGE sql STABLE SET search_path = public AS $$ SELECT id FROM public.accounts WHERE code = _code LIMIT 1 $$;

-- ============ INVOICE POSTING ============
CREATE OR REPLACE FUNCTION public.post_invoice(_invoice_id uuid)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _inv public.invoices; _lines jsonb := '[]'::jsonb; _je uuid; _ar uuid; _vat uuid; _rev record;
BEGIN
  SELECT * INTO _inv FROM public.invoices WHERE id = _invoice_id FOR UPDATE;
  IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found'; END IF;
  IF _inv.status <> 'draft' THEN RAISE EXCEPTION 'Only draft invoices can be posted'; END IF;
  IF _inv.total <= 0 THEN RAISE EXCEPTION 'Invoice total must be greater than zero'; END IF;

  _ar := public.account_id_by_code('1200');
  _vat := public.account_id_by_code('2100');

  _lines := _lines || jsonb_build_object('account_id', _ar, 'debit', _inv.total, 'credit', 0,
      'description', 'Invoice ' || _inv.invoice_number, 'customer_id', _inv.customer_id);

  FOR _rev IN
    SELECT COALESCE(i.revenue_account_id, public.account_id_by_code('4100')) AS acc,
           SUM(i.line_total - i.tax_amount) AS net
    FROM public.invoice_items i WHERE i.invoice_id = _invoice_id
    GROUP BY COALESCE(i.revenue_account_id, public.account_id_by_code('4100'))
  LOOP
    IF round(_rev.net,2) <> 0 THEN
      _lines := _lines || jsonb_build_object('account_id', _rev.acc, 'debit', 0, 'credit', round(_rev.net,2),
        'description', 'Revenue - ' || _inv.invoice_number, 'customer_id', _inv.customer_id);
    END IF;
  END LOOP;

  IF _inv.tax_total > 0 THEN
    _lines := _lines || jsonb_build_object('account_id', _vat, 'debit', 0, 'credit', _inv.tax_total,
      'description', 'VAT on ' || _inv.invoice_number, 'customer_id', _inv.customer_id);
  END IF;

  _je := public.post_journal_entry(_inv.invoice_date, 'Invoice ' || _inv.invoice_number, 'invoice', _invoice_id, _lines);

  UPDATE public.invoices SET status = 'posted', journal_entry_id = _je, posted_at = now(),
    balance = total - amount_paid WHERE id = _invoice_id;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'post', 'invoice', _invoice_id, 'Posted invoice ' || _inv.invoice_number);
  RETURN _je;
END; $$;

CREATE OR REPLACE FUNCTION public.void_invoice(_invoice_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _inv public.invoices;
BEGIN
  SELECT * INTO _inv FROM public.invoices WHERE id = _invoice_id FOR UPDATE;
  IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found'; END IF;
  IF _inv.amount_paid > 0 THEN RAISE EXCEPTION 'Cannot void an invoice with payments applied'; END IF;
  IF _inv.journal_entry_id IS NOT NULL THEN
    PERFORM public.reverse_journal_entry(_inv.journal_entry_id, CURRENT_DATE, 'Void invoice ' || _inv.invoice_number);
  END IF;
  UPDATE public.invoices SET status = 'void', balance = 0 WHERE id = _invoice_id;
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'void', 'invoice', _invoice_id, 'Voided invoice ' || _inv.invoice_number);
END; $$;

-- ============ CUSTOMER PAYMENT ============
CREATE OR REPLACE FUNCTION public.record_customer_payment(
  _customer_id uuid, _payment_date date, _amount numeric, _deposit_account_id uuid,
  _method text, _reference text, _notes text, _allocations jsonb
) RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _pmt_id uuid; _num text; _je uuid; _alloc jsonb; _sum numeric := 0; _inv public.invoices; _amt numeric;
BEGIN
  IF _amount <= 0 THEN RAISE EXCEPTION 'Payment amount must be greater than zero'; END IF;
  _num := public.next_number('payment');

  SELECT COALESCE(SUM((a->>'amount')::numeric),0) INTO _sum
  FROM jsonb_array_elements(COALESCE(_allocations,'[]'::jsonb)) a;
  IF round(_sum,2) > round(_amount,2) THEN RAISE EXCEPTION 'Allocated amount exceeds payment amount'; END IF;

  INSERT INTO public.payments (payment_number, customer_id, payment_date, amount, unapplied_amount,
    method, deposit_account_id, reference, notes)
  VALUES (_num, _customer_id, _payment_date, _amount, round(_amount - _sum,2), COALESCE(_method,'cash'),
    _deposit_account_id, _reference, _notes)
  RETURNING id INTO _pmt_id;

  FOR _alloc IN SELECT * FROM jsonb_array_elements(COALESCE(_allocations,'[]'::jsonb)) LOOP
    _amt := round((_alloc->>'amount')::numeric, 2);
    IF _amt <= 0 THEN CONTINUE; END IF;
    SELECT * INTO _inv FROM public.invoices WHERE id = (_alloc->>'invoice_id')::uuid FOR UPDATE;
    IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found for allocation'; END IF;
    IF _inv.status NOT IN ('posted','partial') THEN RAISE EXCEPTION 'Invoice % is not open for payment', _inv.invoice_number; END IF;
    IF _amt > _inv.balance + 0.001 THEN RAISE EXCEPTION 'Allocation exceeds balance of invoice %', _inv.invoice_number; END IF;

    INSERT INTO public.payment_allocations (payment_id, invoice_id, amount) VALUES (_pmt_id, _inv.id, _amt);
    UPDATE public.invoices SET amount_paid = amount_paid + _amt, balance = total - (amount_paid + _amt),
      status = CASE WHEN total - (amount_paid + _amt) <= 0.001 THEN 'paid' ELSE 'partial' END
    WHERE id = _inv.id;
  END LOOP;

  _je := public.post_journal_entry(_payment_date, 'Customer payment ' || _num, 'payment', _pmt_id,
    jsonb_build_array(
      jsonb_build_object('account_id', _deposit_account_id, 'debit', _amount, 'credit', 0, 'description', 'Payment ' || _num, 'customer_id', _customer_id),
      jsonb_build_object('account_id', public.account_id_by_code('1200'), 'debit', 0, 'credit', _amount, 'description', 'Payment ' || _num, 'customer_id', _customer_id)
    ));
  UPDATE public.payments SET journal_entry_id = _je WHERE id = _pmt_id;
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'create', 'payment', _pmt_id, 'Recorded payment ' || _num);
  RETURN _pmt_id;
END; $$;

-- allocate an existing (unapplied) payment
CREATE OR REPLACE FUNCTION public.allocate_payment(_payment_id uuid, _allocations jsonb)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _pmt public.payments; _alloc jsonb; _amt numeric; _inv public.invoices; _sum numeric := 0;
BEGIN
  SELECT * INTO _pmt FROM public.payments WHERE id = _payment_id FOR UPDATE;
  IF _pmt.id IS NULL THEN RAISE EXCEPTION 'Payment not found'; END IF;
  SELECT COALESCE(SUM((a->>'amount')::numeric),0) INTO _sum FROM jsonb_array_elements(_allocations) a;
  IF round(_sum,2) > round(_pmt.unapplied_amount,2) + 0.001 THEN RAISE EXCEPTION 'Allocation exceeds unapplied amount'; END IF;

  FOR _alloc IN SELECT * FROM jsonb_array_elements(_allocations) LOOP
    _amt := round((_alloc->>'amount')::numeric,2);
    IF _amt <= 0 THEN CONTINUE; END IF;
    SELECT * INTO _inv FROM public.invoices WHERE id = (_alloc->>'invoice_id')::uuid FOR UPDATE;
    IF _amt > _inv.balance + 0.001 THEN RAISE EXCEPTION 'Allocation exceeds balance of invoice %', _inv.invoice_number; END IF;
    INSERT INTO public.payment_allocations (payment_id, invoice_id, amount) VALUES (_payment_id, _inv.id, _amt);
    UPDATE public.invoices SET amount_paid = amount_paid + _amt, balance = total - (amount_paid + _amt),
      status = CASE WHEN total - (amount_paid + _amt) <= 0.001 THEN 'paid' ELSE 'partial' END
    WHERE id = _inv.id;
  END LOOP;
  UPDATE public.payments SET unapplied_amount = unapplied_amount - round(_sum,2) WHERE id = _payment_id;
END; $$;

-- ============ EXPENSE ============
CREATE OR REPLACE FUNCTION public.create_expense(
  _expense_date date, _expense_account_id uuid, _paid_from_account_id uuid, _amount numeric,
  _category_id uuid, _vendor_id uuid, _method text, _reference text, _description text, _attachment_url text
) RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _id uuid; _num text; _je uuid;
BEGIN
  IF _amount <= 0 THEN RAISE EXCEPTION 'Expense amount must be greater than zero'; END IF;
  _num := public.next_number('expense');
  INSERT INTO public.expenses (expense_number, expense_date, category_id, expense_account_id,
     paid_from_account_id, vendor_id, amount, method, reference, description, attachment_url)
  VALUES (_num, _expense_date, _category_id, _expense_account_id, _paid_from_account_id, _vendor_id,
     _amount, COALESCE(_method,'cash'), _reference, _description, _attachment_url)
  RETURNING id INTO _id;

  _je := public.post_journal_entry(_expense_date, 'Expense ' || _num, 'expense', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', _expense_account_id, 'debit', _amount, 'credit', 0, 'description', COALESCE(_description, _num), 'vendor_id', _vendor_id),
      jsonb_build_object('account_id', _paid_from_account_id, 'debit', 0, 'credit', _amount, 'description', COALESCE(_description, _num))
    ));
  UPDATE public.expenses SET journal_entry_id = _je WHERE id = _id;
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'create', 'expense', _id, 'Recorded expense ' || _num);
  RETURN _id;
END; $$;

-- ============ BILLS ============
CREATE OR REPLACE FUNCTION public.post_bill(_bill_id uuid)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _bill public.bills; _lines jsonb := '[]'::jsonb; _je uuid; _row record;
BEGIN
  SELECT * INTO _bill FROM public.bills WHERE id = _bill_id FOR UPDATE;
  IF _bill.id IS NULL THEN RAISE EXCEPTION 'Bill not found'; END IF;
  IF _bill.status <> 'draft' THEN RAISE EXCEPTION 'Only draft bills can be posted'; END IF;
  IF _bill.total <= 0 THEN RAISE EXCEPTION 'Bill total must be greater than zero'; END IF;

  FOR _row IN SELECT account_id, SUM(line_total - tax_amount) AS net FROM public.bill_items
              WHERE bill_id = _bill_id GROUP BY account_id LOOP
    IF round(_row.net,2) <> 0 THEN
      _lines := _lines || jsonb_build_object('account_id', _row.account_id, 'debit', round(_row.net,2), 'credit', 0,
        'description', 'Bill ' || _bill.bill_number, 'vendor_id', _bill.vendor_id);
    END IF;
  END LOOP;
  IF _bill.tax_total > 0 THEN
    _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('2100'), 'debit', _bill.tax_total, 'credit', 0,
      'description', 'Input VAT ' || _bill.bill_number, 'vendor_id', _bill.vendor_id);
  END IF;
  _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('2000'), 'debit', 0, 'credit', _bill.total,
    'description', 'Bill ' || _bill.bill_number, 'vendor_id', _bill.vendor_id);

  _je := public.post_journal_entry(_bill.bill_date, 'Bill ' || _bill.bill_number, 'bill', _bill_id, _lines);
  UPDATE public.bills SET status='posted', journal_entry_id=_je, posted_at=now(), balance = total - amount_paid WHERE id=_bill_id;
  RETURN _je;
END; $$;

CREATE OR REPLACE FUNCTION public.pay_bill(_bill_id uuid, _payment_date date, _amount numeric,
  _paid_from_account_id uuid, _method text, _reference text)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _bill public.bills; _id uuid; _num text; _je uuid;
BEGIN
  SELECT * INTO _bill FROM public.bills WHERE id = _bill_id FOR UPDATE;
  IF _bill.id IS NULL THEN RAISE EXCEPTION 'Bill not found'; END IF;
  IF _bill.status NOT IN ('posted','partial') THEN RAISE EXCEPTION 'Bill is not open for payment'; END IF;
  IF _amount <= 0 OR _amount > _bill.balance + 0.001 THEN RAISE EXCEPTION 'Invalid payment amount'; END IF;
  _num := public.next_number('billpay');

  INSERT INTO public.bill_payments (payment_number, bill_id, vendor_id, payment_date, amount, paid_from_account_id, method, reference)
  VALUES (_num, _bill_id, _bill.vendor_id, _payment_date, _amount, _paid_from_account_id, COALESCE(_method,'cash'), _reference)
  RETURNING id INTO _id;

  _je := public.post_journal_entry(_payment_date, 'Bill payment ' || _num, 'bill_payment', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', public.account_id_by_code('2000'), 'debit', _amount, 'credit', 0, 'description', _num, 'vendor_id', _bill.vendor_id),
      jsonb_build_object('account_id', _paid_from_account_id, 'debit', 0, 'credit', _amount, 'description', _num, 'vendor_id', _bill.vendor_id)
    ));
  UPDATE public.bill_payments SET journal_entry_id = _je WHERE id = _id;
  UPDATE public.bills SET amount_paid = amount_paid + _amount, balance = total - (amount_paid + _amount),
    status = CASE WHEN total - (amount_paid + _amount) <= 0.001 THEN 'paid' ELSE 'partial' END
  WHERE id = _bill_id;
  RETURN _id;
END; $$;

-- ============ TRANSFER ============
CREATE OR REPLACE FUNCTION public.create_transfer(_transfer_date date, _from_account_id uuid, _to_account_id uuid, _amount numeric, _memo text)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _id uuid; _num text; _je uuid;
BEGIN
  IF _amount <= 0 THEN RAISE EXCEPTION 'Transfer amount must be greater than zero'; END IF;
  IF _from_account_id = _to_account_id THEN RAISE EXCEPTION 'Source and destination must differ'; END IF;
  _num := public.next_number('transfer');
  INSERT INTO public.transfers (transfer_number, transfer_date, from_account_id, to_account_id, amount, memo)
  VALUES (_num, _transfer_date, _from_account_id, _to_account_id, _amount, _memo) RETURNING id INTO _id;
  _je := public.post_journal_entry(_transfer_date, 'Transfer ' || _num, 'transfer', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', _to_account_id, 'debit', _amount, 'credit', 0, 'description', COALESCE(_memo,_num)),
      jsonb_build_object('account_id', _from_account_id, 'debit', 0, 'credit', _amount, 'description', COALESCE(_memo,_num))
    ));
  UPDATE public.transfers SET journal_entry_id = _je WHERE id = _id;
  RETURN _id;
END; $$;

-- ============ OWNERS ============
CREATE OR REPLACE FUNCTION public.record_owner_investment(_owner_id uuid, _date date, _amount numeric, _deposit_account_id uuid, _notes text)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _id uuid; _je uuid; _cap uuid;
BEGIN
  IF _amount <= 0 THEN RAISE EXCEPTION 'Amount must be greater than zero'; END IF;
  SELECT COALESCE(capital_account_id, public.account_id_by_code('3000')) INTO _cap FROM public.owners WHERE id = _owner_id;
  IF _cap IS NULL THEN RAISE EXCEPTION 'Owner not found'; END IF;
  INSERT INTO public.owner_investments (owner_id, investment_date, amount, deposit_account_id, notes)
  VALUES (_owner_id, _date, _amount, _deposit_account_id, _notes) RETURNING id INTO _id;
  _je := public.post_journal_entry(_date, 'Owner investment', 'owner_investment', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', _deposit_account_id, 'debit', _amount, 'credit', 0, 'description', 'Owner investment', 'owner_id', _owner_id),
      jsonb_build_object('account_id', _cap, 'debit', 0, 'credit', _amount, 'description', 'Owner investment', 'owner_id', _owner_id)
    ));
  UPDATE public.owner_investments SET journal_entry_id = _je WHERE id = _id;
  RETURN _id;
END; $$;

CREATE OR REPLACE FUNCTION public.record_owner_withdrawal(_owner_id uuid, _date date, _amount numeric, _paid_from_account_id uuid, _notes text)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _id uuid; _je uuid; _draw uuid;
BEGIN
  IF _amount <= 0 THEN RAISE EXCEPTION 'Amount must be greater than zero'; END IF;
  SELECT COALESCE(drawings_account_id, public.account_id_by_code('3100')) INTO _draw FROM public.owners WHERE id = _owner_id;
  IF _draw IS NULL THEN RAISE EXCEPTION 'Owner not found'; END IF;
  INSERT INTO public.owner_withdrawals (owner_id, withdrawal_date, amount, paid_from_account_id, notes)
  VALUES (_owner_id, _date, _amount, _paid_from_account_id, _notes) RETURNING id INTO _id;
  _je := public.post_journal_entry(_date, 'Owner withdrawal', 'owner_withdrawal', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', _draw, 'debit', _amount, 'credit', 0, 'description', 'Owner withdrawal', 'owner_id', _owner_id),
      jsonb_build_object('account_id', _paid_from_account_id, 'debit', 0, 'credit', _amount, 'description', 'Owner withdrawal', 'owner_id', _owner_id)
    ));
  UPDATE public.owner_withdrawals SET journal_entry_id = _je WHERE id = _id;
  RETURN _id;
END; $$;

-- ============ PROFIT & SAFE DISTRIBUTION ============
CREATE OR REPLACE FUNCTION public.calculate_monthly_profit(_start date, _end date)
RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
DECLARE _rev numeric := 0; _cos numeric := 0; _exp numeric := 0;
BEGIN
  SELECT COALESCE(SUM(l.credit - l.debit),0) INTO _rev
  FROM public.journal_lines l JOIN public.journal_entries e ON e.id = l.journal_entry_id
  JOIN public.accounts a ON a.id = l.account_id JOIN public.account_types t ON t.id = a.account_type_id
  WHERE t.category = 'revenue' AND e.status <> 'draft' AND e.entry_date BETWEEN _start AND _end;

  SELECT COALESCE(SUM(l.debit - l.credit),0) INTO _cos
  FROM public.journal_lines l JOIN public.journal_entries e ON e.id = l.journal_entry_id
  JOIN public.accounts a ON a.id = l.account_id JOIN public.account_types t ON t.id = a.account_type_id
  WHERE t.code = 'COS' AND e.status <> 'draft' AND e.entry_date BETWEEN _start AND _end;

  SELECT COALESCE(SUM(l.debit - l.credit),0) INTO _exp
  FROM public.journal_lines l JOIN public.journal_entries e ON e.id = l.journal_entry_id
  JOIN public.accounts a ON a.id = l.account_id JOIN public.account_types t ON t.id = a.account_type_id
  WHERE t.code = 'EXPENSE' AND e.status <> 'draft' AND e.entry_date BETWEEN _start AND _end;

  RETURN jsonb_build_object('revenue', round(_rev,2), 'cost_of_sales', round(_cos,2),
    'operating_expenses', round(_exp,2), 'net_profit', round(_rev - _cos - _exp,2));
END; $$;

CREATE OR REPLACE FUNCTION public.calculate_safe_distribution(_start date, _end date)
RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
DECLARE _cash numeric := 0; _reserve numeric := 0; _obl numeric := 0; _profit jsonb; _safe numeric;
BEGIN
  _profit := public.calculate_monthly_profit(_start, _end);
  SELECT COALESCE(SUM(b.balance),0) INTO _cash FROM public.v_account_balances b WHERE b.normal_balance='debit'
   AND b.account_id IN (SELECT a.id FROM public.accounts a JOIN public.account_types t ON t.id=a.account_type_id WHERE t.code='CASH');
  SELECT COALESCE((value->>'minimum_cash_reserve')::numeric,0) INTO _reserve FROM public.settings WHERE key='finance';
  SELECT COALESCE(SUM(balance),0) INTO _obl FROM public.bills WHERE status IN ('posted','partial');
  _safe := GREATEST(round(_cash - COALESCE(_reserve,0) - _obl, 2), 0);
  RETURN _profit || jsonb_build_object('available_cash', round(_cash,2), 'required_reserve', round(COALESCE(_reserve,0),2),
    'upcoming_obligations', round(_obl,2), 'max_safe_distribution', _safe,
    'distributable_profit', LEAST(GREATEST((_profit->>'net_profit')::numeric,0), _safe));
END; $$;

CREATE OR REPLACE FUNCTION public.create_distribution_proposal(_start date, _end date, _proposed numeric)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _calc jsonb; _id uuid; _safe numeric; _o record;
BEGIN
  _calc := public.calculate_safe_distribution(_start, _end);
  _safe := (_calc->>'max_safe_distribution')::numeric;
  IF _proposed > _safe + 0.001 THEN RAISE EXCEPTION 'Proposed amount exceeds the maximum safe distribution of %', _safe; END IF;

  INSERT INTO public.profit_distributions (period_start, period_end, net_profit, required_reserve, available_cash,
    upcoming_obligations, max_safe_distribution, proposed_amount, status)
  VALUES (_start, _end, (_calc->>'net_profit')::numeric, (_calc->>'required_reserve')::numeric,
    (_calc->>'available_cash')::numeric, (_calc->>'upcoming_obligations')::numeric, _safe, _proposed, 'draft')
  ON CONFLICT (period_start, period_end) DO UPDATE SET
    net_profit = EXCLUDED.net_profit, required_reserve = EXCLUDED.required_reserve,
    available_cash = EXCLUDED.available_cash, upcoming_obligations = EXCLUDED.upcoming_obligations,
    max_safe_distribution = EXCLUDED.max_safe_distribution, proposed_amount = EXCLUDED.proposed_amount,
    updated_at = now()
  RETURNING id INTO _id;

  IF (SELECT status FROM public.profit_distributions WHERE id=_id) <> 'draft' THEN
    RAISE EXCEPTION 'This period distribution is already approved or paid';
  END IF;

  DELETE FROM public.profit_distribution_items WHERE distribution_id = _id;
  FOR _o IN SELECT id, ownership_percentage FROM public.owners WHERE is_active LOOP
    INSERT INTO public.profit_distribution_items (distribution_id, owner_id, ownership_percentage, amount)
    VALUES (_id, _o.id, _o.ownership_percentage, round(_proposed * _o.ownership_percentage / 100, 2));
  END LOOP;
  RETURN _id;
END; $$;

CREATE OR REPLACE FUNCTION public.approve_distribution(_id uuid, _approved numeric)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _d public.profit_distributions; _o record;
BEGIN
  SELECT * INTO _d FROM public.profit_distributions WHERE id = _id FOR UPDATE;
  IF _d.id IS NULL THEN RAISE EXCEPTION 'Distribution not found'; END IF;
  IF _d.status <> 'draft' THEN RAISE EXCEPTION 'Only draft distributions can be approved'; END IF;
  IF _approved > _d.max_safe_distribution + 0.001 THEN
    RAISE EXCEPTION 'Approved amount exceeds the maximum safe distribution of %', _d.max_safe_distribution;
  END IF;
  UPDATE public.profit_distributions SET approved_amount = _approved, status = 'approved', approved_at = now() WHERE id = _id;
  FOR _o IN SELECT id, owner_id, ownership_percentage FROM public.profit_distribution_items WHERE distribution_id = _id LOOP
    UPDATE public.profit_distribution_items SET amount = round(_approved * _o.ownership_percentage / 100, 2) WHERE id = _o.id;
  END LOOP;
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'approve', 'profit_distribution', _id, 'Approved distribution');
END; $$;

CREATE OR REPLACE FUNCTION public.execute_distribution(_id uuid, _paid_from_account_id uuid, _pay_date date)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _d public.profit_distributions; _lines jsonb := '[]'::jsonb; _je uuid; _it record; _cash numeric;
BEGIN
  SELECT * INTO _d FROM public.profit_distributions WHERE id = _id FOR UPDATE;
  IF _d.id IS NULL THEN RAISE EXCEPTION 'Distribution not found'; END IF;
  IF _d.status <> 'approved' THEN RAISE EXCEPTION 'Only approved distributions can be paid'; END IF;
  SELECT balance INTO _cash FROM public.v_account_balances WHERE account_id = _paid_from_account_id;
  IF COALESCE(_cash,0) < _d.approved_amount THEN RAISE EXCEPTION 'Insufficient balance in the selected account'; END IF;

  FOR _it IN SELECT i.owner_id, i.amount, COALESCE(o.drawings_account_id, public.account_id_by_code('3100')) AS draw
             FROM public.profit_distribution_items i JOIN public.owners o ON o.id = i.owner_id
             WHERE i.distribution_id = _id AND i.amount > 0 LOOP
    _lines := _lines || jsonb_build_object('account_id', _it.draw, 'debit', _it.amount, 'credit', 0,
      'description', 'Profit distribution', 'owner_id', _it.owner_id);
  END LOOP;
  _lines := _lines || jsonb_build_object('account_id', _paid_from_account_id, 'debit', 0, 'credit', _d.approved_amount,
    'description', 'Profit distribution payout');

  _je := public.post_journal_entry(_pay_date, 'Profit distribution ' || _d.period_start || ' to ' || _d.period_end,
    'profit_distribution', _id, _lines);
  UPDATE public.profit_distributions SET status='paid', paid_amount = approved_amount, paid_at = now(), journal_entry_id = _je WHERE id = _id;
  UPDATE public.profit_distribution_items SET paid = true WHERE distribution_id = _id;
  RETURN _je;
END; $$;

-- booking -> invoice
CREATE OR REPLACE FUNCTION public.create_invoice_from_booking(_booking_id uuid)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _b public.bookings; _inv uuid; _num text; _net numeric;
BEGIN
  SELECT * INTO _b FROM public.bookings WHERE id = _booking_id FOR UPDATE;
  IF _b.id IS NULL THEN RAISE EXCEPTION 'Booking not found'; END IF;
  IF _b.invoice_id IS NOT NULL THEN RAISE EXCEPTION 'This booking already has an invoice'; END IF;
  IF _b.customer_id IS NULL THEN RAISE EXCEPTION 'Booking has no customer'; END IF;
  _num := public.next_number('invoice');
  _net := _b.price - _b.discount;
  INSERT INTO public.invoices (invoice_number, customer_id, invoice_date, due_date, subtotal, discount_total, tax_total, total, balance, status, booking_id, notes)
  VALUES (_num, _b.customer_id, _b.booking_date, _b.booking_date + 15, _b.price, _b.discount, _b.tax, _net + _b.tax, _net + _b.tax, 'draft', _b.id, 'Booking ' || _b.booking_number)
  RETURNING id INTO _inv;
  INSERT INTO public.invoice_items (invoice_id, item_type, service_id, description, quantity, unit_price, discount, tax_amount, line_total, revenue_account_id)
  VALUES (_inv, 'service', _b.service_id, 'Studio booking ' || _b.booking_number, 1, _b.price, _b.discount, _b.tax, _net + _b.tax, public.account_id_by_code('4000'));
  UPDATE public.bookings SET invoice_id = _inv WHERE id = _booking_id;
  RETURN _inv;
END; $$;

-- ============ views ============
CREATE OR REPLACE VIEW public.v_customer_balances
WITH (security_invoker = on) AS
SELECT c.id AS customer_id, c.customer_code, c.name,
  COALESCE(s.total_sales,0) AS total_sales,
  COALESCE(p.total_payments,0) AS total_payments,
  COALESCE(s.outstanding,0) AS outstanding
FROM public.customers c
LEFT JOIN (SELECT customer_id, SUM(total) total_sales, SUM(balance) outstanding FROM public.invoices
           WHERE status IN ('posted','partial','paid') GROUP BY customer_id) s ON s.customer_id = c.id
LEFT JOIN (SELECT customer_id, SUM(amount) total_payments FROM public.payments GROUP BY customer_id) p ON p.customer_id = c.id;
GRANT SELECT ON public.v_customer_balances TO authenticated;

CREATE OR REPLACE VIEW public.v_vendor_balances
WITH (security_invoker = on) AS
SELECT v.id AS vendor_id, v.vendor_code, v.name,
  COALESCE(b.total_bills,0) AS total_bills,
  COALESCE(b.outstanding,0) AS outstanding,
  COALESCE(bp.total_paid,0) AS total_paid
FROM public.vendors v
LEFT JOIN (SELECT vendor_id, SUM(total) total_bills, SUM(balance) outstanding FROM public.bills
           WHERE status IN ('posted','partial','paid') GROUP BY vendor_id) b ON b.vendor_id = v.id
LEFT JOIN (SELECT vendor_id, SUM(amount) total_paid FROM public.bill_payments GROUP BY vendor_id) bp ON bp.vendor_id = v.id;
GRANT SELECT ON public.v_vendor_balances TO authenticated;

CREATE OR REPLACE VIEW public.v_owner_summary
WITH (security_invoker = on) AS
SELECT o.id AS owner_id, o.name, o.ownership_percentage,
  COALESCE(i.total_investment,0) AS total_investment,
  COALESCE(w.total_withdrawal,0) AS total_withdrawal,
  COALESCE(i.total_investment,0) - COALESCE(w.total_withdrawal,0) AS current_capital,
  COALESCE(d.total_distributed,0) AS total_distributed
FROM public.owners o
LEFT JOIN (SELECT owner_id, SUM(amount) total_investment FROM public.owner_investments GROUP BY owner_id) i ON i.owner_id = o.id
LEFT JOIN (SELECT owner_id, SUM(amount) total_withdrawal FROM public.owner_withdrawals GROUP BY owner_id) w ON w.owner_id = o.id
LEFT JOIN (SELECT owner_id, SUM(amount) total_distributed FROM public.profit_distribution_items WHERE paid GROUP BY owner_id) d ON d.owner_id = o.id;
GRANT SELECT ON public.v_owner_summary TO authenticated;

-- restrict function execution to signed-in users
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT p.oid::regprocedure AS sig FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
           WHERE n.nspname='public' AND p.prosecdef LOOP
    EXECUTE format('REVOKE EXECUTE ON FUNCTION %s FROM anon, public', r.sig);
    EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO authenticated', r.sig);
  END LOOP;
END $$;
REVOKE EXECUTE ON FUNCTION public.handle_new_user() FROM authenticated;

-- ============ seed defaults ============
INSERT INTO public.cash_accounts (name, account_id, kind)
VALUES ('Main Cash', public.account_id_by_code('1010'), 'cash'),
       ('Petty Cash', public.account_id_by_code('1020'), 'petty');
INSERT INTO public.bank_accounts (name, account_id, kind, bank_name)
VALUES ('Primary Bank', public.account_id_by_code('1100'), 'bank', 'Bank');

INSERT INTO public.expense_categories (name, account_id)
SELECT a.name, a.id FROM public.accounts a WHERE a.code IN ('6000','6100','6200','6300','6400','6500','6600','6700');

INSERT INTO public.owners (name, ownership_percentage, capital_account_id, drawings_account_id, is_primary)
VALUES ('Owner', 100, public.account_id_by_code('3000'), public.account_id_by_code('3100'), true);

INSERT INTO public.studios (name) VALUES ('Main Studio');
INSERT INTO public.services (name, unit, rate, tax_rate, revenue_account_id)
VALUES ('Studio Recording (Hourly)', 'hour', 2000, 15, public.account_id_by_code('4000')),
       ('Mixing & Mastering', 'job', 8000, 15, public.account_id_by_code('4100'));