-- 使用shorturl_analytics数据库
USE shorturl_analytics;

-- 删除已存在的shorturl表
DROP TABLE IF EXISTS shorturl_analytics.shorturl;

-- 创建shorturl表
CREATE TABLE IF NOT EXISTS shorturl_analytics.shorturl (
  -- 短链接基本信息（来源于resources表）
  id String COMMENT '资源ID (resources.id)',
  external_id String COMMENT '外部ID (resources.external_id)',
  type String COMMENT '类型，值为shorturl',
  slug String COMMENT '短链接slug (存储在attributes中)',
  original_url String COMMENT '原始URL (存储在attributes中)',
  title String COMMENT '标题 (存储在attributes中)',
  description String COMMENT '描述 (存储在attributes中)',
  attributes String DEFAULT '{}' COMMENT '资源属性JSON',
  schema_version Int32 COMMENT 'Schema版本',
  -- 创建者信息
  creator_id String COMMENT '创建者ID (resources.creator_id)',
  creator_email String COMMENT '创建者邮箱 (来自users表)',
  creator_name String COMMENT '创建者名称 (来自users表)',
  -- 时间信息
  created_at DateTime64(3) COMMENT '创建时间 (resources.created_at)',
  updated_at DateTime64(3) COMMENT '更新时间 (resources.updated_at)',
  deleted_at Nullable(DateTime64(3)) COMMENT '删除时间 (resources.deleted_at)',
  -- 项目关联 (project_resources表)
  projects String DEFAULT '[]' COMMENT '项目关联信息数组。结构: [{project_id: String, project_name: String, project_description: String, assigned_at: DateTime64}]',
  -- 团队关联 (通过项目关联到团队)
  teams String DEFAULT '[]' COMMENT '团队关联信息数组。结构: [{team_id: String, team_name: String, team_description: String, via_project_id: String}]',
  -- 标签关联 (resource_tags表)
  tags String DEFAULT '[]' COMMENT '标签关联信息数组。结构: [{tag_id: String, tag_name: String, tag_type: String, created_at: DateTime64}]',
  -- QR码关联 (qr_code表)
  qr_codes String DEFAULT '[]' COMMENT 'QR码信息数组。结构: [{qr_id: String, scan_count: Int32, url: String, template_name: String, created_at: DateTime64}]',
  -- 渠道关联 (channel表)
  channels String DEFAULT '[]' COMMENT '渠道信息数组。结构: [{channel_id: String, channel_name: String, channel_path: String, is_user_created: Boolean}]',
  -- 收藏关联 (favorite表)
  favorites String DEFAULT '[]' COMMENT '收藏信息数组。结构: [{favorite_id: String, user_id: String, user_name: String, created_at: DateTime64}]',
  -- 自定义过期时间 (存储在attributes中)
  expires_at Nullable(DateTime64(3)) COMMENT '过期时间',
  -- 统计信息 (分析时聚合计算)
  click_count UInt32 DEFAULT 0 COMMENT '点击次数',
  unique_visitors UInt32 DEFAULT 0 COMMENT '唯一访问者数'
) ENGINE = MergeTree() PARTITION BY toYYYYMM(created_at)
ORDER BY
  (id, created_at) SETTINGS index_granularity = 8192 COMMENT '用于存储所有shorturl类型资源的统一表，集成了相关联的项目、团队、标签、QR码、渠道和收藏信息';