-- ============================================================
-- Inventory enum types
-- ============================================================

CREATE TYPE app.inventory_request_status AS ENUM ('submitted', 'approved', 'rejected', 'fulfilled', 'cancelled');

-- ============================================================
-- Inventory items
-- ============================================================

CREATE TABLE app.inventory_item (
  item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  unit TEXT NOT NULL DEFAULT 'unit',
  current_quantity NUMERIC NOT NULL DEFAULT 0,
  minimum_quantity NUMERIC NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_inventory_item_active ON app.inventory_item(is_active);

-- ============================================================
-- Inventory requests
-- ============================================================

CREATE TABLE app.inventory_request (
  request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  requested_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  item_id UUID REFERENCES app.inventory_item(item_id),
  item_name TEXT NOT NULL,
  quantity NUMERIC NOT NULL,
  unit TEXT NOT NULL,
  purpose TEXT,
  status app.inventory_request_status NOT NULL DEFAULT 'submitted',
  reviewed_by_user_id UUID REFERENCES app."user"(user_id),
  reviewed_at TIMESTAMPTZ,
  review_notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_inventory_request_status ON app.inventory_request(status);
CREATE INDEX idx_inventory_request_user ON app.inventory_request(requested_by_user_id);

-- Enable audit
SELECT app.enable_audit('inventory_item', 'item_id');
SELECT app.enable_audit('inventory_request', 'request_id');