CREATE TABLE public.daily_cash_closings (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  branch_id UUID,
  closing_date DATE NOT NULL DEFAULT CURRENT_DATE,
  opening_cash NUMERIC NOT NULL DEFAULT 0,
  total_sales NUMERIC NOT NULL DEFAULT 0,
  cash_sales NUMERIC NOT NULL DEFAULT 0,
  due_collection NUMERIC NOT NULL DEFAULT 0,
  expense_amount NUMERIC NOT NULL DEFAULT 0,
  supplier_payment NUMERIC NOT NULL DEFAULT 0,
  bank_deposit NUMERIC NOT NULL DEFAULT 0,
  withdraw_amount NUMERIC NOT NULL DEFAULT 0,
  closing_cash NUMERIC NOT NULL DEFAULT 0,
  expected_cash NUMERIC NOT NULL DEFAULT 0,
  difference NUMERIC NOT NULL DEFAULT 0,
  note TEXT NOT NULL DEFAULT '',
  is_locked BOOLEAN NOT NULL DEFAULT false,
  closed_by UUID,
  closed_at TIMESTAMP WITH TIME ZONE,
  reopened_by UUID,
  reopened_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  UNIQUE(branch_id, closing_date)
);

CREATE INDEX idx_daily_cash_closings_branch_date ON public.daily_cash_closings(branch_id, closing_date DESC);
CREATE INDEX idx_daily_cash_closings_date ON public.daily_cash_closings(closing_date DESC);

ALTER TABLE public.daily_cash_closings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view daily cash closings"
ON public.daily_cash_closings FOR SELECT
TO authenticated
USING (is_staff(auth.uid()));

CREATE POLICY "Staff can insert daily cash closings"
ON public.daily_cash_closings FOR INSERT
TO authenticated
WITH CHECK (is_staff(auth.uid()));

CREATE POLICY "Staff can update unlocked closings; admins always"
ON public.daily_cash_closings FOR UPDATE
TO authenticated
USING (
  has_role(auth.uid(), 'admin'::app_role)
  OR (is_staff(auth.uid()) AND is_locked = false)
);

CREATE POLICY "Admins can delete daily cash closings"
ON public.daily_cash_closings FOR DELETE
TO authenticated
USING (has_role(auth.uid(), 'admin'::app_role));

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