INSERT INTO public.number_sequences (key, prefix, next_value)
VALUES ('booking_cost', 'BCO-', 1)
ON CONFLICT (key) DO NOTHING;

CREATE TABLE IF NOT EXISTS public.booking_costs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  cost_number text NOT NULL UNIQUE,
  booking_id uuid NOT NULL REFERENCES public.bookings(id) ON DELETE CASCADE,
  cost_date date NOT NULL DEFAULT CURRENT_DATE,
  cost_type text NOT NULL DEFAULT 'other',
  cost_account_id uuid NOT NULL REFERENCES public.accounts(id),
  paid_from_account_id uuid REFERENCES public.accounts(id),
  vendor_id uuid REFERENCES public.vendors(id),
  amount numeric NOT NULL DEFAULT 0,
  is_paid boolean NOT NULL DEFAULT true,
  method text NOT NULL DEFAULT 'cash',
  reference text,
  description text,
  journal_entry_id uuid REFERENCES public.journal_entries(id),
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.booking_costs TO authenticated;
GRANT ALL ON public.booking_costs TO service_role;

ALTER TABLE public.booking_costs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "booking_costs_select" ON public.booking_costs
  FOR SELECT TO authenticated USING (public.can_access('bookings'));

CREATE POLICY "booking_costs_insert" ON public.booking_costs
  FOR INSERT TO authenticated WITH CHECK (public.can_edit('bookings'));

CREATE POLICY "booking_costs_update" ON public.booking_costs
  FOR UPDATE TO authenticated USING (public.can_edit('bookings')) WITH CHECK (public.can_edit('bookings'));

CREATE POLICY "booking_costs_delete" ON public.booking_costs
  FOR DELETE TO authenticated USING (public.is_admin());

CREATE TRIGGER booking_costs_set_updated_at
  BEFORE UPDATE ON public.booking_costs
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE INDEX IF NOT EXISTS booking_costs_booking_idx ON public.booking_costs(booking_id);
CREATE INDEX IF NOT EXISTS booking_costs_date_idx ON public.booking_costs(cost_date);

-- Records a direct cost against a booking and posts the balanced ledger entry.
CREATE OR REPLACE FUNCTION public.record_booking_cost(
  _booking_id uuid,
  _cost_date date,
  _cost_account_id uuid,
  _amount numeric,
  _cost_type text DEFAULT 'other',
  _is_paid boolean DEFAULT true,
  _paid_from_account_id uuid DEFAULT NULL,
  _vendor_id uuid DEFAULT NULL,
  _method text DEFAULT 'cash',
  _reference text DEFAULT NULL,
  _description text DEFAULT NULL
) RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _id uuid; _num text; _je uuid; _credit uuid; _bk record;
BEGIN
  PERFORM public.require_access('bookings');
  IF _amount <= 0 THEN RAISE EXCEPTION 'Cost amount must be greater than zero'; END IF;

  SELECT * INTO _bk FROM public.bookings WHERE id = _booking_id;
  IF _bk IS NULL THEN RAISE EXCEPTION 'Booking not found'; END IF;

  IF _is_paid THEN
    IF _paid_from_account_id IS NULL THEN
      RAISE EXCEPTION 'Choose the account the cost was paid from';
    END IF;
    _credit := _paid_from_account_id;
  ELSE
    _credit := public.account_id_by_code('2000');
    IF _credit IS NULL THEN RAISE EXCEPTION 'Accounts Payable account (2000) is missing'; END IF;
  END IF;

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

  INSERT INTO public.booking_costs (
    cost_number, booking_id, cost_date, cost_type, cost_account_id, paid_from_account_id,
    vendor_id, amount, is_paid, method, reference, description, created_by
  ) VALUES (
    _num, _booking_id, _cost_date, COALESCE(_cost_type, 'other'), _cost_account_id,
    CASE WHEN _is_paid THEN _paid_from_account_id ELSE NULL END,
    _vendor_id, _amount, _is_paid, COALESCE(_method, 'cash'), _reference, _description, auth.uid()
  ) RETURNING id INTO _id;

  _je := public.post_journal_entry(
    _cost_date,
    'Booking cost ' || _num || ' · ' || _bk.booking_number,
    'booking_cost',
    _id,
    jsonb_build_array(
      jsonb_build_object('account_id', _cost_account_id, 'debit', _amount, 'credit', 0,
        'description', COALESCE(_description, 'Direct cost for booking ' || _bk.booking_number),
        'vendor_id', _vendor_id),
      jsonb_build_object('account_id', _credit, 'debit', 0, 'credit', _amount,
        'description', CASE WHEN _is_paid THEN 'Paid for booking cost ' || _num
                            ELSE 'Payable for booking cost ' || _num END,
        'vendor_id', _vendor_id)
    )
  );

  UPDATE public.booking_costs SET journal_entry_id = _je WHERE id = _id;
  RETURN _id;
END; $$;

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

-- Revenue, direct cost, gross profit and net profit for a period (ledger based).
CREATE OR REPLACE FUNCTION public.calculate_gross_profit(_start date, _end date)
RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
DECLARE _rev numeric := 0; _cos numeric := 0; _opex numeric := 0; _bcost numeric := 0;
BEGIN
  SELECT
    COALESCE(SUM(CASE WHEN t.category = 'revenue' THEN l.credit - l.debit ELSE 0 END), 0),
    COALESCE(SUM(CASE WHEN t.code = 'COS' THEN l.debit - l.credit ELSE 0 END), 0),
    COALESCE(SUM(CASE WHEN t.code = 'EXPENSE' THEN l.debit - l.credit ELSE 0 END), 0)
  INTO _rev, _cos, _opex
  FROM public.journal_lines l
  JOIN public.journal_entries e ON e.id = l.journal_entry_id AND e.status <> 'draft'
  JOIN public.accounts a ON a.id = l.account_id
  JOIN public.account_types t ON t.id = a.account_type_id
  WHERE e.entry_date BETWEEN _start AND _end;

  SELECT COALESCE(SUM(amount), 0) INTO _bcost
  FROM public.booking_costs WHERE cost_date BETWEEN _start AND _end;

  RETURN jsonb_build_object(
    'revenue', _rev,
    'cost_of_sales', _cos,
    'booking_cost', _bcost,
    'operating_expenses', _opex,
    'gross_profit', _rev - _cos,
    'net_profit', _rev - _cos - _opex,
    'gross_margin', CASE WHEN _rev > 0 THEN round(((_rev - _cos) / _rev) * 100, 2) ELSE 0 END,
    'net_margin', CASE WHEN _rev > 0 THEN round(((_rev - _cos - _opex) / _rev) * 100, 2) ELSE 0 END
  );
END; $$;

REVOKE ALL ON FUNCTION public.calculate_gross_profit(date, date) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.calculate_gross_profit(date, date) TO authenticated;