-- cfw-attachment 初始 schema：附件、分类、标签、业务关联、审计
CREATE TABLE attachments (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  status TEXT NOT NULL,
  filename TEXT NOT NULL,
  content_type TEXT NOT NULL,
  byte_size INTEGER,
  checksum_sha256 TEXT,
  visibility TEXT NOT NULL DEFAULT 'private',
  category_id TEXT,
  description TEXT,
  object_key TEXT,
  etag TEXT,
  created_by_user_id TEXT NOT NULL,
  updated_by_user_id TEXT NOT NULL,
  deleted_by_user_id TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  deleted_at TEXT
);

CREATE TABLE attachment_categories (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE(user_id, slug)
);

CREATE TABLE attachment_tags (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE(user_id, slug)
);

CREATE TABLE attachment_tag_links (
  attachment_id TEXT NOT NULL,
  tag_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  PRIMARY KEY (attachment_id, tag_id),
  FOREIGN KEY (attachment_id) REFERENCES attachments(id),
  FOREIGN KEY (tag_id) REFERENCES attachment_tags(id)
);

CREATE TABLE attachment_links (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  attachment_id TEXT NOT NULL,
  owner_type TEXT NOT NULL,
  owner_id TEXT NOT NULL,
  purpose TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_by_user_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE(user_id, owner_type, owner_id, purpose, attachment_id)
);

CREATE TABLE attachment_audit_events (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  attachment_id TEXT,
  actor_user_id TEXT NOT NULL,
  action TEXT NOT NULL,
  detail_json TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE INDEX idx_attachments_user_status ON attachments(user_id, status, created_at);
CREATE INDEX idx_attachments_user_category ON attachments(user_id, category_id, created_at);
CREATE INDEX idx_attachment_tags_user_slug ON attachment_tags(user_id, slug);
CREATE INDEX idx_attachment_tag_links_tag ON attachment_tag_links(user_id, tag_id);
CREATE INDEX idx_attachment_links_owner ON attachment_links(user_id, owner_type, owner_id, purpose);
CREATE INDEX idx_attachment_audit_user_attachment ON attachment_audit_events(user_id, attachment_id, created_at);