CREATE TABLE public.salary_sheets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  guard_id uuid NOT NULL REFERENCES public.security_guards(id) ON DELETE CASCADE,
  month text NOT NULL,
  basic numeric NOT NULL DEFAULT 0,
  ot numeric NOT NULL DEFAULT 0,
  deduction numeric NOT NULL DEFAULT 0,
  notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (guard_id, month)
);

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

ALTER TABLE public.salary_sheets ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Ops can view salary"
  ON public.salary_sheets FOR SELECT TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager']::app_role[]));

CREATE POLICY "Ops can insert salary"
  ON public.salary_sheets FOR INSERT TO authenticated
  WITH CHECK (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager']::app_role[]));

CREATE POLICY "Ops can update salary"
  ON public.salary_sheets FOR UPDATE TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager']::app_role[]))
  WITH CHECK (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager']::app_role[]));

CREATE POLICY "Ops can delete salary"
  ON public.salary_sheets FOR DELETE TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager']::app_role[]));

CREATE TRIGGER trg_salary_sheets_updated_at
  BEFORE UPDATE ON public.salary_sheets
  FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();