CREATE TABLE IF NOT EXISTS assets (
  id TEXT PRIMARY KEY,
  owner_key TEXT NOT NULL,
  checksum TEXT NOT NULL,
  ext TEXT NOT NULL,
  kind TEXT NOT NULL,
  mime_type TEXT,
  size_bytes BIGINT NOT NULL DEFAULT 0,
  object_key TEXT NOT NULL,
  is_public BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (owner_key, checksum, ext),
  UNIQUE (owner_key, object_key)
);

CREATE TABLE IF NOT EXISTS feature_asset_links (
  feature_id TEXT NOT NULL REFERENCES features(id) ON DELETE CASCADE,
  asset_id TEXT NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
  name TEXT,
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (feature_id, asset_id)
);

CREATE INDEX IF NOT EXISTS idx_assets_owner ON assets(owner_key);
CREATE INDEX IF NOT EXISTS idx_assets_owner_public ON assets(owner_key, is_public);
CREATE INDEX IF NOT EXISTS idx_feature_asset_links_asset ON feature_asset_links(asset_id);