CREATE TABLE public.expense_claims (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  claim_number text NOT NULL UNIQUE,
  claim_date date NOT NULL DEFAULT CURRENT_DATE,
  category_id uuid REFERENCES public.expense_categories(id),
  expense_account_id uuid NOT NULL REFERENCES public.accounts(id),
  paid_from_account_id uuid NOT NULL REFERENCES public.accounts(id),
  vendor_id uuid REFERENCES public.vendors(id),
  amount numeric NOT NULL DEFAULT 0,
  method text NOT NULL DEFAULT 'cash',
  reference text,
  description text,
  attachment_url text,
  status text NOT NULL DEFAULT 'pending',
  rejection_reason text,
  submitted_by uuid,
  submitted_at timestamptz NOT NULL DEFAULT now(),
  approved_by uuid,
  approved_at timestamptz,
  expense_id uuid REFERENCES public.expenses(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.expense_claims TO authenticated;
GRANT ALL ON public.expense_claims TO service_role;
ALTER TABLE public.expense_claims ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Expense module users can read claims" ON public.expense_claims
  FOR SELECT TO authenticated USING (public.can_access('expenses'));
CREATE POLICY "Admins manage claims" ON public.expense_claims
  FOR ALL TO authenticated USING (public.is_admin()) WITH CHECK (public.is_admin());

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

CREATE TABLE public.expense_claim_approvals (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  claim_id uuid NOT NULL REFERENCES public.expense_claims(id) ON DELETE CASCADE,
  action text NOT NULL,
  actor_id uuid,
  note text,
  created_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT ON public.expense_claim_approvals TO authenticated;
GRANT ALL ON public.expense_claim_approvals TO service_role;
ALTER TABLE public.expense_claim_approvals ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Expense module users can read claim history" ON public.expense_claim_approvals
  FOR SELECT TO authenticated USING (public.can_access('expenses'));

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

CREATE OR REPLACE FUNCTION public.submit_expense_claim(
  _claim_date date,
  _expense_account_id uuid,
  _paid_from_account_id uuid,
  _amount numeric,
  _category_id uuid DEFAULT NULL,
  _vendor_id uuid DEFAULT NULL,
  _method text DEFAULT 'cash',
  _reference text DEFAULT NULL,
  _description text DEFAULT NULL,
  _attachment_url text DEFAULT NULL
) RETURNS uuid
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _id uuid; _num text;
BEGIN
  PERFORM public.require_access('expenses');
  IF _amount <= 0 THEN RAISE EXCEPTION 'Claim amount must be greater than zero'; END IF;
  _num := public.next_number('expense_claim');
  INSERT INTO public.expense_claims (claim_number, claim_date, category_id, expense_account_id,
    paid_from_account_id, vendor_id, amount, method, reference, description, attachment_url,
    status, submitted_by, submitted_at)
  VALUES (_num, _claim_date, _category_id, _expense_account_id, _paid_from_account_id, _vendor_id,
    _amount, COALESCE(_method,'cash'), _reference, _description, _attachment_url,
    'pending', auth.uid(), now())
  RETURNING id INTO _id;

  INSERT INTO public.expense_claim_approvals (claim_id, action, actor_id, note)
  VALUES (_id, 'submitted', auth.uid(), _description);
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'submit', 'expense_claim', _id, 'Submitted expense claim ' || _num);
  RETURN _id;
END;
$$;

CREATE OR REPLACE FUNCTION public.approve_expense_claim(_claim_id uuid, _note text DEFAULT NULL, _post boolean DEFAULT true)
RETURNS uuid
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _c public.expense_claims; _exp uuid;
BEGIN
  IF NOT public.is_admin() THEN RAISE EXCEPTION 'Only an administrator can approve expense claims'; END IF;
  SELECT * INTO _c FROM public.expense_claims WHERE id = _claim_id FOR UPDATE;
  IF _c.id IS NULL THEN RAISE EXCEPTION 'Expense claim not found'; END IF;
  IF _c.status NOT IN ('pending','rejected') THEN RAISE EXCEPTION 'Claim is not awaiting approval'; END IF;

  IF _post THEN
    _exp := public.create_expense(_c.claim_date, _c.expense_account_id, _c.paid_from_account_id,
      _c.amount, _c.category_id, _c.vendor_id, _c.method, _c.reference,
      COALESCE(_c.description, 'Expense claim ' || _c.claim_number), _c.attachment_url);
    UPDATE public.expense_claims SET status = 'posted', approved_by = auth.uid(), approved_at = now(),
      rejection_reason = NULL, expense_id = _exp WHERE id = _claim_id;
    INSERT INTO public.expense_claim_approvals (claim_id, action, actor_id, note)
    VALUES (_claim_id, 'approved', auth.uid(), _note),
           (_claim_id, 'posted', auth.uid(), NULL);
  ELSE
    UPDATE public.expense_claims SET status = 'approved', approved_by = auth.uid(), approved_at = now(),
      rejection_reason = NULL WHERE id = _claim_id;
    INSERT INTO public.expense_claim_approvals (claim_id, action, actor_id, note)
    VALUES (_claim_id, 'approved', auth.uid(), _note);
  END IF;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'approve', 'expense_claim', _claim_id, 'Approved expense claim ' || _c.claim_number);
  RETURN _exp;
END;
$$;

CREATE OR REPLACE FUNCTION public.reject_expense_claim(_claim_id uuid, _reason text)
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _c public.expense_claims;
BEGIN
  IF NOT public.is_admin() THEN RAISE EXCEPTION 'Only an administrator can reject expense claims'; END IF;
  SELECT * INTO _c FROM public.expense_claims WHERE id = _claim_id FOR UPDATE;
  IF _c.id IS NULL THEN RAISE EXCEPTION 'Expense claim not found'; END IF;
  IF _c.status NOT IN ('pending','approved') THEN RAISE EXCEPTION 'Claim cannot be sent back'; END IF;

  UPDATE public.expense_claims SET status = 'rejected', rejection_reason = _reason WHERE id = _claim_id;
  INSERT INTO public.expense_claim_approvals (claim_id, action, actor_id, note)
  VALUES (_claim_id, 'rejected', auth.uid(), _reason);
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'reject', 'expense_claim', _claim_id, 'Sent back expense claim ' || _c.claim_number);
END;
$$;