CREATE TABLE public.incident_reports (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  reporter_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  guard_id uuid REFERENCES public.security_guards(id) ON DELETE SET NULL,
  duty_post_id uuid REFERENCES public.duty_posts(id) ON DELETE SET NULL,
  title text NOT NULL,
  description text NOT NULL,
  severity text NOT NULL DEFAULT 'low',
  status text NOT NULL DEFAULT 'open',
  location text,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  resolution_notes text,
  resolved_at timestamptz,
  resolved_by uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

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

ALTER TABLE public.incident_reports ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can insert their own incident reports"
ON public.incident_reports FOR INSERT TO authenticated
WITH CHECK (auth.uid() = reporter_id);

CREATE POLICY "Users can view their own incident reports"
ON public.incident_reports FOR SELECT TO authenticated
USING (auth.uid() = reporter_id);

CREATE POLICY "Supervisors can view all incident reports"
ON public.incident_reports FOR SELECT TO authenticated
USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[]));

CREATE POLICY "Supervisors can update incident reports"
ON public.incident_reports FOR UPDATE TO authenticated
USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[]))
WITH CHECK (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[]));

CREATE POLICY "Admins can delete incident reports"
ON public.incident_reports FOR DELETE TO authenticated
USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin']::app_role[]));

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

CREATE INDEX idx_incident_reports_created_at ON public.incident_reports(created_at DESC);
CREATE INDEX idx_incident_reports_status ON public.incident_reports(status);