CREATE TABLE public.unit_timeline_events (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  unit_id UUID REFERENCES public.units(id) ON DELETE CASCADE NOT NULL,
  association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
  event_type TEXT NOT NULL DEFAULT 'general',
  event_tag TEXT[] DEFAULT '{}',
  title TEXT NOT NULL,
  description TEXT,
  related_entity_id UUID,
  related_entity_type TEXT,
  metadata JSONB DEFAULT '{}',
  created_by UUID,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

ALTER TABLE public.unit_timeline_events ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated users can view timeline events"
ON public.unit_timeline_events FOR SELECT TO authenticated USING (true);

CREATE POLICY "Authenticated users can create timeline events"
ON public.unit_timeline_events FOR INSERT TO authenticated WITH CHECK (true);

CREATE POLICY "Authenticated users can update timeline events"
ON public.unit_timeline_events FOR UPDATE TO authenticated USING (true);

CREATE POLICY "Authenticated users can delete timeline events"
ON public.unit_timeline_events FOR DELETE TO authenticated USING (true);

CREATE INDEX idx_unit_timeline_unit_id ON public.unit_timeline_events(unit_id);
CREATE INDEX idx_unit_timeline_event_type ON public.unit_timeline_events(event_type);
CREATE INDEX idx_unit_timeline_tags ON public.unit_timeline_events USING GIN(event_tag);
CREATE INDEX idx_unit_timeline_created_at ON public.unit_timeline_events(created_at DESC);

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