-- ===== 技能统计字段（下载数 / 点赞数 / 点踩数） =====
ALTER TABLE skills ADD COLUMN download_count INTEGER NOT NULL DEFAULT 0;
ALTER TABLE skills ADD COLUMN like_count     INTEGER NOT NULL DEFAULT 0;
ALTER TABLE skills ADD COLUMN dislike_count  INTEGER NOT NULL DEFAULT 0;

-- ===== 用户投票记录（防重投，同一用户每个 skill 只能有一条） =====
CREATE TABLE IF NOT EXISTS skill_votes (
  id         TEXT PRIMARY KEY,
  skill_id   TEXT NOT NULL,
  user_key   TEXT NOT NULL,  -- 登录邮箱 / 手机号，匿名则用 'anonymous'
  vote       TEXT NOT NULL,  -- 'like' | 'dislike'
  created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
  UNIQUE(skill_id, user_key),
  FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_skill_votes_skill ON skill_votes(skill_id);
