-- Budgets, credit notes and refunds

CREATE TABLE public.budgets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  period_month date NOT NULL,
  account_id uuid NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
  planned_amount numeric(18,2) NOT NULL DEFAULT 0,
  notes text,
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (period_month, account_id)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.budgets TO authenticated;
GRANT ALL ON public.budgets TO service_role;
ALTER TABLE public.budgets ENABLE ROW LEVEL SECURITY;
CREATE POLICY budgets_select ON public.budgets FOR SELECT TO authenticated USING (public.can_access('accounting'));
CREATE POLICY budgets_insert ON public.budgets FOR INSERT TO authenticated WITH CHECK (public.can_edit('accounting'));
CREATE POLICY budgets_update ON public.budgets FOR UPDATE TO authenticated USING (public.can_edit('accounting')) WITH CHECK (public.can_edit('accounting'));
CREATE POLICY budgets_delete ON public.budgets FOR DELETE TO authenticated USING (public.can_edit('accounting'));
CREATE TRIGGER budgets_updated_at BEFORE UPDATE ON public.budgets FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE TABLE public.credit_notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  credit_note_number text NOT NULL UNIQUE,
  invoice_id uuid NOT NULL REFERENCES public.invoices(id) ON DELETE RESTRICT,
  customer_id uuid REFERENCES public.customers(id) ON DELETE SET NULL,
  credit_date date NOT NULL DEFAULT CURRENT_DATE,
  amount numeric(18,2) NOT NULL,
  reason text,
  status text NOT NULL DEFAULT 'posted' CHECK (status IN ('posted','void')),
  journal_entry_id uuid REFERENCES public.journal_entries(id) ON DELETE SET NULL,
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.credit_notes TO authenticated;
GRANT ALL ON public.credit_notes TO service_role;
ALTER TABLE public.credit_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY credit_notes_select ON public.credit_notes FOR SELECT TO authenticated USING (public.can_access('invoices'));
CREATE POLICY credit_notes_insert ON public.credit_notes FOR INSERT TO authenticated WITH CHECK (public.can_edit('invoices'));
CREATE POLICY credit_notes_update ON public.credit_notes FOR UPDATE TO authenticated USING (public.can_edit('invoices')) WITH CHECK (public.can_edit('invoices'));
CREATE POLICY credit_notes_delete ON public.credit_notes FOR DELETE TO authenticated USING (public.can_edit('invoices'));

CREATE TABLE public.refunds (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  refund_number text NOT NULL UNIQUE,
  invoice_id uuid NOT NULL REFERENCES public.invoices(id) ON DELETE RESTRICT,
  customer_id uuid REFERENCES public.customers(id) ON DELETE SET NULL,
  refund_date date NOT NULL DEFAULT CURRENT_DATE,
  amount numeric(18,2) NOT NULL,
  method text NOT NULL DEFAULT 'cash',
  paid_from_account_id uuid NOT NULL REFERENCES public.accounts(id),
  reference text,
  notes text,
  journal_entry_id uuid REFERENCES public.journal_entries(id) ON DELETE SET NULL,
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.refunds TO authenticated;
GRANT ALL ON public.refunds TO service_role;
ALTER TABLE public.refunds ENABLE ROW LEVEL SECURITY;
CREATE POLICY refunds_select ON public.refunds FOR SELECT TO authenticated USING (public.can_access('payments'));
CREATE POLICY refunds_insert ON public.refunds FOR INSERT TO authenticated WITH CHECK (public.can_edit('payments'));
CREATE POLICY refunds_update ON public.refunds FOR UPDATE TO authenticated USING (public.can_edit('payments')) WITH CHECK (public.can_edit('payments'));
CREATE POLICY refunds_delete ON public.refunds FOR DELETE TO authenticated USING (public.can_edit('payments'));

ALTER TABLE public.quotes ADD COLUMN IF NOT EXISTS portal_visible boolean NOT NULL DEFAULT true;

INSERT INTO public.number_sequences (key, prefix) VALUES ('credit_note', 'CN-'), ('refund', 'RF-')
ON CONFLICT (key) DO NOTHING;

-- Credit note: reverses part or all of a posted invoice
CREATE OR REPLACE FUNCTION public.post_credit_note(_invoice_id uuid, _credit_date date, _amount numeric, _reason text)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  _inv public.invoices; _cn_id uuid; _num text; _je uuid; _lines jsonb := '[]'::jsonb;
  _credited numeric := 0; _tax numeric; _net numeric; _total_net numeric; _rev record;
  _allocated numeric := 0; _share numeric; _last uuid; _ar uuid; _vat uuid; _fallback uuid;
BEGIN
  IF NOT public.can_edit('invoices') THEN RAISE EXCEPTION 'You do not have permission to issue credit notes'; END IF;
  IF _amount IS NULL OR _amount <= 0 THEN RAISE EXCEPTION 'Credit amount must be greater than zero'; END IF;
  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 NOT IN ('posted','partial','paid') THEN RAISE EXCEPTION 'Only posted invoices can be credited'; END IF;

  SELECT COALESCE(SUM(amount),0) INTO _credited FROM public.credit_notes
   WHERE invoice_id = _invoice_id AND status = 'posted';
  IF round(_credited + _amount, 2) > round(_inv.total, 2) + 0.001 THEN
    RAISE EXCEPTION 'Credit notes cannot exceed the invoice total (already credited %)', _credited;
  END IF;

  _ar := public.account_id_by_code('1200');
  _vat := public.account_id_by_code('2100');
  _fallback := COALESCE(public.account_id_by_code('4300'), public.account_id_by_code('4100'));

  _tax := round(_amount * (CASE WHEN _inv.total > 0 THEN COALESCE(_inv.tax_total,0) / _inv.total ELSE 0 END), 2);
  _net := round(_amount - _tax, 2);

  SELECT COALESCE(SUM(i.line_total - i.tax_amount), 0) INTO _total_net
    FROM public.invoice_items i WHERE i.invoice_id = _invoice_id;

  _num := public.next_number('credit_note');

  INSERT INTO public.credit_notes (credit_note_number, invoice_id, customer_id, credit_date, amount, reason, created_by)
  VALUES (_num, _invoice_id, _inv.customer_id, COALESCE(_credit_date, CURRENT_DATE), round(_amount,2), _reason, auth.uid())
  RETURNING id INTO _cn_id;

  IF _total_net > 0 THEN
    FOR _rev IN
      SELECT COALESCE(i.revenue_account_id, _fallback) 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, _fallback)
        ORDER BY 2 DESC
    LOOP
      _share := round(_net * (_rev.net / _total_net), 2);
      _allocated := _allocated + _share;
      _last := _rev.acc;
      IF _share <> 0 THEN
        _lines := _lines || jsonb_build_object('account_id', _rev.acc, 'debit', _share, 'credit', 0,
          'description', 'Credit note ' || _num, 'customer_id', _inv.customer_id);
      END IF;
    END LOOP;
  END IF;

  -- Rounding residual (or a fully custom invoice with no lines) lands on the fallback revenue account.
  IF round(_allocated, 2) <> _net THEN
    _lines := _lines || jsonb_build_object('account_id', COALESCE(_last, _fallback),
      'debit', round(_net - _allocated, 2), 'credit', 0,
      'description', 'Credit note rounding ' || _num, 'customer_id', _inv.customer_id);
  END IF;

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

  _lines := _lines || jsonb_build_object('account_id', _ar, 'debit', 0, 'credit', round(_amount,2),
    'description', 'Credit note ' || _num || ' against ' || _inv.invoice_number, 'customer_id', _inv.customer_id);

  _je := public.post_journal_entry(COALESCE(_credit_date, CURRENT_DATE),
    'Credit note ' || _num || ' for invoice ' || _inv.invoice_number, 'credit_note', _cn_id, _lines);

  UPDATE public.credit_notes SET journal_entry_id = _je WHERE id = _cn_id;

  UPDATE public.invoices
     SET balance = GREATEST(round(total - amount_paid - (_credited + _amount), 2), 0),
         status = CASE
           WHEN round(total - amount_paid - (_credited + _amount), 2) <= 0.001 AND amount_paid <= 0.001 THEN 'void'
           WHEN round(total - amount_paid - (_credited + _amount), 2) <= 0.001 THEN 'paid'
           ELSE 'partial' END
   WHERE id = _invoice_id;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'create', 'credit_note', _cn_id, 'Credit note ' || _num || ' for invoice ' || _inv.invoice_number);
  RETURN _cn_id;
END;
$$;

REVOKE ALL ON FUNCTION public.post_credit_note(uuid, date, numeric, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.post_credit_note(uuid, date, numeric, text) TO authenticated;

-- Refund: money paid back to a customer against an invoice
CREATE OR REPLACE FUNCTION public.record_refund(
  _invoice_id uuid, _refund_date date, _amount numeric, _paid_from_account_id uuid,
  _method text, _reference text, _notes text)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE _inv public.invoices; _id uuid; _num text; _je uuid; _credited numeric := 0;
BEGIN
  IF NOT public.can_edit('payments') THEN RAISE EXCEPTION 'You do not have permission to record refunds'; END IF;
  IF _amount IS NULL OR _amount <= 0 THEN RAISE EXCEPTION 'Refund amount must be greater than zero'; END IF;
  IF _paid_from_account_id IS NULL THEN RAISE EXCEPTION 'Select the cash or bank account the refund is paid from'; END IF;
  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 round(_amount,2) > round(COALESCE(_inv.amount_paid,0),2) + 0.001 THEN
    RAISE EXCEPTION 'Refund cannot exceed the % already received on invoice %', _inv.amount_paid, _inv.invoice_number;
  END IF;

  SELECT COALESCE(SUM(amount),0) INTO _credited FROM public.credit_notes
   WHERE invoice_id = _invoice_id AND status = 'posted';

  _num := public.next_number('refund');

  INSERT INTO public.refunds (refund_number, invoice_id, customer_id, refund_date, amount,
    method, paid_from_account_id, reference, notes, created_by)
  VALUES (_num, _invoice_id, _inv.customer_id, COALESCE(_refund_date, CURRENT_DATE), round(_amount,2),
    COALESCE(_method,'cash'), _paid_from_account_id, _reference, _notes, auth.uid())
  RETURNING id INTO _id;

  _je := public.post_journal_entry(COALESCE(_refund_date, CURRENT_DATE),
    'Refund ' || _num || ' on invoice ' || _inv.invoice_number, 'refund', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', public.account_id_by_code('1200'), 'debit', round(_amount,2), 'credit', 0,
        'description', 'Refund ' || _num, 'customer_id', _inv.customer_id),
      jsonb_build_object('account_id', _paid_from_account_id, 'debit', 0, 'credit', round(_amount,2),
        'description', 'Refund ' || _num, 'customer_id', _inv.customer_id)
    ));

  UPDATE public.refunds SET journal_entry_id = _je WHERE id = _id;

  UPDATE public.invoices
     SET amount_paid = round(amount_paid - _amount, 2),
         balance = GREATEST(round(total - (amount_paid - _amount) - _credited, 2), 0),
         status = CASE
           WHEN round(total - (amount_paid - _amount) - _credited, 2) <= 0.001 THEN 'paid'
           WHEN round(amount_paid - _amount, 2) > 0.001 THEN 'partial'
           ELSE 'posted' END
   WHERE id = _invoice_id;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'create', 'refund', _id, 'Refund ' || _num || ' on invoice ' || _inv.invoice_number);
  RETURN _id;
END;
$$;

REVOKE ALL ON FUNCTION public.record_refund(uuid, date, numeric, uuid, text, text, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.record_refund(uuid, date, numeric, uuid, text, text, text) TO authenticated;