DO $$ BEGIN
  CREATE TYPE public.inventory_status AS ENUM ('available','assigned','maintenance','retired','lost');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE public.inventory_condition AS ENUM ('new','good','fair','poor','damaged');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE TABLE public.inventory_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  category text NOT NULL DEFAULT 'general',
  serial_number text,
  quantity integer NOT NULL DEFAULT 1,
  condition public.inventory_condition NOT NULL DEFAULT 'good',
  status public.inventory_status NOT NULL DEFAULT 'available',
  assigned_guard_id uuid REFERENCES public.security_guards(id) ON DELETE SET NULL,
  assigned_post_id uuid REFERENCES public.duty_posts(id) ON DELETE SET NULL,
  purchase_date date,
  notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

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

ALTER TABLE public.inventory_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated can view inventory" ON public.inventory_items
  FOR SELECT TO authenticated USING (true);
CREATE POLICY "Authenticated can insert inventory" ON public.inventory_items
  FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "Authenticated can update inventory" ON public.inventory_items
  FOR UPDATE TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "Authenticated can delete inventory" ON public.inventory_items
  FOR DELETE TO authenticated USING (true);

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

CREATE INDEX idx_inventory_items_status ON public.inventory_items(status);
CREATE INDEX idx_inventory_items_guard ON public.inventory_items(assigned_guard_id);