-- Create fee exclusions table for unit-level late fee / interest overrides
CREATE TYPE public.fee_exclusion_type AS ENUM ('late_fee', 'interest');
CREATE TYPE public.fee_exclusion_mode AS ENUM ('waive', 'override_amount');

CREATE TABLE public.unit_fee_exclusions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  association_id UUID NOT NULL,
  unit_id UUID NOT NULL,
  fee_type public.fee_exclusion_type NOT NULL,
  mode public.fee_exclusion_mode NOT NULL,
  override_amount NUMERIC(10,2),
  reason TEXT,
  created_by UUID,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  UNIQUE (unit_id, fee_type)
);

CREATE INDEX idx_unit_fee_exclusions_unit ON public.unit_fee_exclusions(unit_id);
CREATE INDEX idx_unit_fee_exclusions_association ON public.unit_fee_exclusions(association_id);

ALTER TABLE public.unit_fee_exclusions ENABLE ROW LEVEL SECURITY;

-- Staff (admin/manager) can manage all; association members can read their own
CREATE POLICY "Staff manage all fee exclusions"
  ON public.unit_fee_exclusions
  FOR ALL
  USING (
    public.has_role(auth.uid(), 'admin'::public.app_role)
    OR public.has_role(auth.uid(), 'manager'::public.app_role)
  )
  WITH CHECK (
    public.has_role(auth.uid(), 'admin'::public.app_role)
    OR public.has_role(auth.uid(), 'manager'::public.app_role)
  );

CREATE POLICY "Association members can view their fee exclusions"
  ON public.unit_fee_exclusions
  FOR SELECT
  USING (public.user_belongs_to_association(auth.uid(), association_id));

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