
CREATE TABLE public.staff_salary_payments (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  staff_id UUID NOT NULL,
  pay_month INTEGER NOT NULL,
  pay_year INTEGER NOT NULL,
  basic_salary NUMERIC NOT NULL DEFAULT 0,
  bonus NUMERIC NOT NULL DEFAULT 0,
  deduction NUMERIC NOT NULL DEFAULT 0,
  net_amount NUMERIC NOT NULL DEFAULT 0,
  payment_method TEXT NOT NULL DEFAULT 'cash',
  paid_date DATE,
  status TEXT NOT NULL DEFAULT 'paid',
  note TEXT NOT NULL DEFAULT '',
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.staff_salary_payments ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view salary payments" ON public.staff_salary_payments FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Admins can insert salary payments" ON public.staff_salary_payments FOR INSERT TO authenticated WITH CHECK (has_role(auth.uid(), 'admin'::app_role));
CREATE POLICY "Admins can update salary payments" ON public.staff_salary_payments FOR UPDATE TO authenticated USING (has_role(auth.uid(), 'admin'::app_role));
CREATE POLICY "Admins can delete salary payments" ON public.staff_salary_payments FOR DELETE TO authenticated USING (has_role(auth.uid(), 'admin'::app_role));

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

CREATE INDEX idx_staff_salary_payments_staff ON public.staff_salary_payments(staff_id);
CREATE INDEX idx_staff_salary_payments_period ON public.staff_salary_payments(pay_year, pay_month);
