CREATE TABLE IF NOT EXISTS public.expenses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
  user_id UUID NOT NULL,
  expense_date DATE NOT NULL DEFAULT CURRENT_DATE,
  category TEXT NOT NULL,
  description TEXT,
  amount NUMERIC NOT NULL CHECK (amount >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected')),
  reviewed_by UUID,
  reviewed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_expenses_company_user_date ON public.expenses(company_id, user_id, expense_date DESC);

ALTER TABLE public.expenses ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Employees view own expenses or admin views all"
ON public.expenses FOR SELECT
USING (
  user_id = auth.uid()
  OR public.has_role(auth.uid(), 'company_admin')
  OR public.is_super_admin(auth.uid())
);

CREATE POLICY "Employees create own expenses"
ON public.expenses FOR INSERT
WITH CHECK (
  user_id = auth.uid()
  AND company_id = public.get_user_company(auth.uid())
);

CREATE POLICY "Employees update own pending expenses or admin updates any"
ON public.expenses FOR UPDATE
USING (
  (user_id = auth.uid() AND status = 'pending')
  OR public.has_role(auth.uid(), 'company_admin')
  OR public.is_super_admin(auth.uid())
);

CREATE POLICY "Employees delete own pending expenses or admin deletes any"
ON public.expenses FOR DELETE
USING (
  (user_id = auth.uid() AND status = 'pending')
  OR public.has_role(auth.uid(), 'company_admin')
  OR public.is_super_admin(auth.uid())
);

CREATE TRIGGER update_expenses_updated_at
BEFORE UPDATE ON public.expenses
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();