#!/usr/bin/env bash
# 图片索引（cache.db）迁移：为缓存表补唯一约束与查询索引。
#
# 设计约束：
#   * 默认 dry-run，只读检查；必须显式 --apply 才执行写入。
#   * 必须显式 --db 指定数据库文件，不读取任何运行时配置，也不推断生产路径。
#   * 写库用 sqlite3 -cmd '.bail on'，任一语句出错立即以非零码退出，事务不提交。
#   * 只以读写模式打开目标库（文件必须已存在，不会新建）；dry-run 全用 -readonly。
#   * 备份用 SQLite 自身的 .backup（online backup API），而不是 cp：
#     cp 会漏掉 WAL 里尚未 checkpoint 的已提交内容。
#   * 备份后立即校验备份可读且含两张缓存表，失败则中止并删除无效备份。
#   * 不健康（不完整/冲突）的 gid 整组索引会被删除，由下一次在线阅读重新抓取。
#   * 唯一约束建在唯一索引上：若清理后仍有重复，建索引会失败并整体回滚。
#
# 用法：
#   scripts/migrate_image_index_cache.sh --db /tmp/cache-copy.db            # dry-run（只读）
#   scripts/migrate_image_index_cache.sh --db /tmp/cache-copy.db --apply    # 执行迁移
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
SQL_FILE="$SCRIPT_DIR/migrate_image_index_cache.sql"

db_path=""
apply="no"

usage() {
  sed -n '2,30p' "${BASH_SOURCE[0]}" | sed 's/^# \{0,1\}//'
}

while [[ $# -gt 0 ]]; do
  case "$1" in
    --db) db_path="${2:-}"; shift 2 ;;
    --apply) apply="yes"; shift ;;
    -h|--help) usage; exit 0 ;;
    *)
      echo "未知参数: $1" >&2
      exit 2 ;;
  esac
done

if [[ -z "$db_path" ]]; then
  echo "必须显式指定缓存库: --db <cache.db>；本脚本不会自动推断数据库位置。" >&2
  exit 2
fi

if [[ ! -f "$db_path" ]]; then
  echo "数据库不存在: $db_path" >&2
  exit 2
fi

if [[ ! -f "$SQL_FILE" ]]; then
  echo "缺少 SQL: $SQL_FILE" >&2
  exit 2
fi

if ! command -v sqlite3 >/dev/null 2>&1; then
  echo "缺少 sqlite3 命令行工具" >&2
  exit 2
fi

if [[ "$(sqlite3 -readonly "$db_path" \
      "select count(*) from sqlite_master where type='table' and name in ('gidToKey','ImageKeyCache');")" != "2" ]]; then
  echo "缓存表缺失：$db_path 中找不到 gidToKey 与 ImageKeyCache。" >&2
  exit 2
fi

# 体检统计，与 SQL 里实际清理的范围一致：
#   bad_gids           将被整组删除的 gid 个数（去重），覆盖：
#                      重复头部 / 行数不符 / 页码去重后不足 / 页码区间异常 /
#                      空 imgkey / 空 key / 非法 pages
#   bad_gid_rows       这些 gid 占用的头部行数（重复头部的 gid 会多于 1 行）
#   duplicate_pages    重复的 (gid,page) 组数，仅用于展示，会随所属 gid 一并清理
#   orphan_gids        有页 key 但无头部记录的 gid 个数
read_stats() {
  sqlite3 -readonly "$db_path" "
    select 'header_rows',     count(*) from gidToKey;
    select 'page_rows',       count(*) from ImageKeyCache;
    select 'duplicate_gids',  count(*) from (select gid from gidToKey group by gid having count(*) > 1);
    select 'duplicate_pages', count(*) from (select gid,page from ImageKeyCache group by gid,page having count(*) > 1);
    select 'bad_gids',        count(distinct g.gid) from gidToKey g
      where g.pages is null or g.pages <= 0
         or g.key is null or trim(g.key) = ''
         or (select count(*) from ImageKeyCache i where i.gid = g.gid) <> g.pages
         or (select count(distinct i.page) from ImageKeyCache i where i.gid = g.gid) <> g.pages
         or (select min(i.page) from ImageKeyCache i where i.gid = g.gid) <> 1
         or (select max(i.page) from ImageKeyCache i where i.gid = g.gid) <> g.pages
         or exists (select 1 from ImageKeyCache i
                    where i.gid = g.gid and (i.imgkey is null or trim(i.imgkey) = ''));
    select 'bad_gid_rows',    coalesce(sum(c), 0) from (
      select count(*) as c from gidToKey g where g.gid in (
        select g2.gid from gidToKey g2 where g2.pages is null or g2.pages <= 0
           or g2.key is null or trim(g2.key) = ''
           or (select count(*) from ImageKeyCache i where i.gid = g2.gid) <> g2.pages
           or (select count(distinct i.page) from ImageKeyCache i where i.gid = g2.gid) <> g2.pages
           or (select min(i.page) from ImageKeyCache i where i.gid = g2.gid) <> 1
           or (select max(i.page) from ImageKeyCache i where i.gid = g2.gid) <> g2.pages
           or exists (select 1 from ImageKeyCache i
                      where i.gid = g2.gid and (i.imgkey is null or trim(i.imgkey) = '')))
      group by g.gid);
    select 'orphan_gids',     count(distinct gid) from ImageKeyCache
      where gid not in (select gid from gidToKey);
    select 'strict_index',    count(*) from sqlite_master
      where type='index' and name='ux_ImageKeyCache_gid_page';
  "
}

mode_label="dry-run（只读）"
if [[ "$apply" == "yes" ]]; then mode_label="apply（读写）"; fi

echo "=== 缓存库图片索引迁移（$mode_label） ==="
echo "数据库: $db_path"
echo "--- 迁移前体检 ---"
read_stats | while IFS='|' read -r name value; do
  printf '  %-16s %s\n' "$name" "$value"
done

if [[ "$apply" != "yes" ]]; then
  echo
  echo "dry-run：未做任何写入。确认无误后加 --apply 执行迁移。"
  echo "提示：请先在 SQLite 副本上演练，副本应这样生成（含 WAL 内容）："
  echo "  sqlite3 -readonly <生产库> \".backup '<副本路径>'\""
  exit 0
fi

# ---- 备份：SQLite online backup，包含 WAL 中已提交但未 checkpoint 的内容 ----
backup="$db_path.before-image-index.$(date -u +%Y%m%dT%H%M%SZ)"
if ! sqlite3 "$db_path" ".backup '$backup'"; then
  echo "备份失败，未做任何修改: $backup" >&2
  exit 3
fi
if [[ ! -s "$backup" ]]; then
  echo "备份文件为空，未做任何修改: $backup" >&2
  exit 3
fi
if [[ "$(sqlite3 -readonly "$backup" \
      "select count(*) from sqlite_master where type='table' and name in ('gidToKey','ImageKeyCache');")" != "2" ]]; then
  echo "备份校验失败（缺少缓存表），未做任何修改，已删除无效备份。" >&2
  rm -f -- "$backup"
  exit 3
fi
echo "已备份（SQLite .backup）: $backup"

# ---- 执行迁移：.bail on 保证任一语句失败即退出，事务不提交 ----
sqlite3 -cmd '.bail on' "$db_path" < "$SQL_FILE"

echo "--- 迁移后体检 ---"
read_stats | while IFS='|' read -r name value; do
  printf '  %-16s %s\n' "$name" "$value"
done

echo "图片索引迁移完成: $db_path"