-- Migration 011: Item File Attachments
--
-- Adds an item_files table for multiple file attachments per item (not tied to revisions),
-- and a thumbnail_key column on items for item-level thumbnails.

CREATE TABLE item_files (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
  filename TEXT NOT NULL,
  content_type TEXT NOT NULL DEFAULT 'application/octet-stream',
  size BIGINT NOT NULL DEFAULT 0,
  object_key TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_item_files_item ON item_files(item_id);

ALTER TABLE items ADD COLUMN IF NOT EXISTS thumbnail_key TEXT;