package backthink

import (
	"context"
	"database/sql"
	"encoding/json"
	"path/filepath"
	"strings"
	"testing"
	"time"

	"git.yeij.top/AskaEth/Cyrene_v2/internal/db"
	"git.yeij.top/AskaEth/Cyrene_v2/internal/urgency"
)

// 本文件守住 X2a 的**落库面**：每一轮实际生效的紧急度档位要能被持久化并按原样读回，
// 既有库要能被幂等地补上该列，补列前的旧行必须读成「未知」而不是某个档位，
// 且实时帧靠的 JSON 键名就是 `urgency`。摘掉加列、把空串当成 routine、或改掉 JSON
// 键名，这里即变红。

// legacyRecordsDDL 是本列出现**之前**的建表语句（逐字照抄旧版 store.go 的
// createRecordsSQL），把「既有部署的库」搬进测试：新代码必须能给这样的表补列、
// 让补列前落下的旧行读成「未知」，而不是让整个思考存档写不进去。
const legacyRecordsDDL = `
CREATE TABLE backthink_records (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    trigger      TEXT NOT NULL,
    at           TEXT NOT NULL,
    conclusion   TEXT NOT NULL,
    should_push  INTEGER NOT NULL,
    push_pushed  INTEGER NOT NULL DEFAULT 0,
    push_route   TEXT NOT NULL,
    push_detail  TEXT NOT NULL DEFAULT '',
    push_error   TEXT NOT NULL DEFAULT '',
    created_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
)`

// insertLegacyRecordSQL 按旧形状（无 urgency 列）插入一行。
const insertLegacyRecordSQL = `
INSERT INTO backthink_records
    (trigger, at, conclusion, should_push, push_pushed, push_route, push_detail, push_error)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`

// newLegacyRecordsDB 建一个「旧版形状 + 一行旧记录」的库。
func newLegacyRecordsDB(t *testing.T) *sql.DB {
	t.Helper()
	h := newTestDB(t)
	ctx := context.Background()
	if _, err := h.ExecContext(ctx, legacyRecordsDDL); err != nil {
		t.Fatalf("create legacy table: %v", err)
	}
	if _, err := h.ExecContext(ctx, insertLegacyRecordSQL,
		"heartbeat", "2026-09-06T10:00:00Z", "晚上好", boolInt(false), boolInt(false),
		string(RouteNone), "held", "",
	); err != nil {
		t.Fatalf("insert legacy row: %v", err)
	}
	return h
}

// hasColumn 直接查 PRAGMA 判列是否已存在。
func hasColumn(t *testing.T, db *sql.DB, name string) bool {
	t.Helper()
	rows, err := db.QueryContext(context.Background(), tableInfoSQL, recordsTableName)
	if err != nil {
		t.Fatalf("inspect columns: %v", err)
	}
	defer rows.Close()
	for rows.Next() {
		var col string
		if err := rows.Scan(&col); err != nil {
			t.Fatalf("scan column: %v", err)
		}
		if col == name {
			return true
		}
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("iterate columns: %v", err)
	}
	return false
}

// TestStore_Urgency_RoundTripEveryLevel 覆盖验收①：写入→回读逐档往返（routine /
// warning / critical 各一条），三档互不塌缩。
func TestStore_Urgency_RoundTripEveryLevel(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	s, err := NewStore(ctx, newTestDB(t), discardLog())
	if err != nil {
		t.Fatalf("NewStore: %v", err)
	}
	levels := []urgency.Level{urgency.LevelRoutine, urgency.LevelWarning, urgency.LevelCritical}
	base := time.Date(2026, 10, 5, 12, 0, 0, 0, time.UTC)
	for i, lv := range levels {
		if _, err := s.Append(ctx, Record{
			Trigger:    "iot.device_update",
			At:         base.Add(time.Duration(i) * time.Minute),
			Conclusion: "结论",
			ShouldPush: i > 0,
			PushResult: PushResult{Route: RouteNone, Detail: "held"},
			Urgency:    lv.String(),
		}); err != nil {
			t.Fatalf("Append(%s): %v", lv, err)
		}
	}

	recs, err := s.List(ctx, 0)
	if err != nil {
		t.Fatalf("List: %v", err)
	}
	if len(recs) != len(levels) {
		t.Fatalf("len = %d, want %d", len(recs), len(levels))
	}
	// 最新在前：第 i 条写入的记录在 len-1-i 位上。
	for i, lv := range levels {
		got := recs[len(levels)-1-i].Urgency
		if got != lv.String() {
			t.Fatalf("round-trip %s = %q, want %q", lv, got, lv.String())
		}
	}
}

// TestStore_Urgency_LegacyDatabaseUpgraded 覆盖验收②③：既有库（旧形状 + 一行旧记录）
// 能被补上 urgency 列、同一库再跑一次 NewStore 依旧成功、数据不丢，且**旧行读成
// 空串「未知」而不是 routine**。
func TestStore_Urgency_LegacyDatabaseUpgraded(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	db := newLegacyRecordsDB(t)
	if hasColumn(t, db, "urgency") {
		t.Fatalf("fixture: legacy table must not have the urgency column yet")
	}

	s, err := NewStore(ctx, db, discardLog())
	if err != nil {
		t.Fatalf("NewStore on a legacy database: %v", err)
	}
	if !hasColumn(t, db, "urgency") {
		t.Fatalf("NewStore did not add backthink_records.urgency")
	}
	// 幂等：同一库再来一次（既有表 + 已有列）不得报错。
	if _, err := NewStore(ctx, db, discardLog()); err != nil {
		t.Fatalf("second NewStore on the same database: %v", err)
	}

	recs, err := s.List(ctx, 0)
	if err != nil {
		t.Fatalf("List: %v", err)
	}
	if len(recs) != 1 {
		t.Fatalf("len = %d, want 1 (旧行必须还在)", len(recs))
	}
	if recs[0].Trigger != "heartbeat" || recs[0].Conclusion != "晚上好" {
		t.Fatalf("legacy row = %+v, want it preserved verbatim", recs[0])
	}
	if recs[0].Urgency != "" {
		t.Fatalf("legacy row urgency = %q, want 空串（未知）", recs[0].Urgency)
	}
	if recs[0].Urgency == urgency.LevelRoutine.String() {
		t.Fatalf("legacy row was back-filled as %q; 未知绝不能编成 routine", recs[0].Urgency)
	}

	// 补列之后，新行带档位写入并回读，与旧行并存。
	if _, err := s.Append(ctx, Record{
		Trigger: "heartbeat", At: time.Date(2026, 10, 5, 12, 0, 0, 0, time.UTC),
		Conclusion: "新行", Urgency: urgency.LevelCritical.String(),
	}); err != nil {
		t.Fatalf("Append after upgrade: %v", err)
	}
	recs, err = s.List(ctx, 0)
	if err != nil {
		t.Fatalf("List after upgrade: %v", err)
	}
	if len(recs) != 2 {
		t.Fatalf("len = %d, want 2", len(recs))
	}
	if recs[0].Urgency != urgency.LevelCritical.String() {
		t.Fatalf("new row urgency = %q, want %q", recs[0].Urgency, urgency.LevelCritical.String())
	}
	if recs[1].Urgency != "" {
		t.Fatalf("legacy row urgency after a new append = %q, want 空串", recs[1].Urgency)
	}
}

// TestEnsureRecordsUrgencyColumn_OnlyToleratesDuplicateColumn 守住幂等守卫的判据本身：
// 「列已存在」这条错误被容忍（那是一次成功重放），**其它错误一律不被容忍**——吞掉
// 真错误会让缺列静默过关，随后每条 INSERT 都失败。
func TestEnsureRecordsUrgencyColumn_OnlyToleratesDuplicateColumn(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	db := newTestDB(t)
	if _, err := NewStore(ctx, db, discardLog()); err != nil {
		t.Fatalf("NewStore: %v", err)
	}

	// 列已在：再执行一次加列语句必须失败，且措辞被判为「重复列」。
	_, dup := db.ExecContext(ctx, alterRecordsAddUrgencySQL)
	if dup == nil {
		t.Fatalf("adding an existing column succeeded; 幂等守卫的判据不再成立")
	}
	if !isDuplicateColumnErr(dup) {
		t.Fatalf("second ALTER error = %v, want duplicate-column classification", dup)
	}
	// 负向对照：表不存在这类错误绝不能被当成「重复列」。
	_, other := db.ExecContext(ctx, `ALTER TABLE backthink_no_such_table ADD COLUMN urgency TEXT NOT NULL DEFAULT ''`)
	if other == nil {
		t.Fatalf("ALTER on a missing table succeeded unexpectedly")
	}
	if isDuplicateColumnErr(other) {
		t.Fatalf("missing-table error %v misclassified as a duplicate column", other)
	}
	// 列已在时 ensure 本身是空操作（不报错、不重复 ALTER）。
	if err := ensureRecordsUrgencyColumn(ctx, db, discardLog()); err != nil {
		t.Fatalf("ensure on an upgraded database: %v", err)
	}
}

// TestStore_Urgency_LegacyFileDatabaseUpgraded 用**真文件库**（生产部署的形态：WAL +
// 单写连接 + 跨进程重开）再走一遍旧库补列：主人手上的 data/cyrene.db 正是这个形态，
// 内存库覆盖不到它（WAL、DDL 落盘、关掉再开）。
func TestStore_Urgency_LegacyFileDatabaseUpgraded(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	path := filepath.Join(t.TempDir(), "cyrene.db")

	// 第一次「进程」：建旧形状的表并落一行旧记录。
	first, err := db.Open(ctx, path, nil)
	if err != nil {
		t.Fatalf("open file db: %v", err)
	}
	if _, err := first.ExecContext(ctx, legacyRecordsDDL); err != nil {
		t.Fatalf("create legacy table: %v", err)
	}
	if _, err := first.ExecContext(ctx, insertLegacyRecordSQL,
		"heartbeat", "2026-09-06T10:00:00Z", "晚上好", boolInt(false), boolInt(false),
		string(RouteNone), "held", "",
	); err != nil {
		t.Fatalf("insert legacy row: %v", err)
	}
	if err := first.Close(); err != nil {
		t.Fatalf("close first handle: %v", err)
	}

	// 第二次「进程」：新版本起来，必须自己把列补上。
	second, err := db.Open(ctx, path, nil)
	if err != nil {
		t.Fatalf("reopen file db: %v", err)
	}
	t.Cleanup(func() { _ = second.Close() })
	s, err := NewStore(ctx, second, discardLog())
	if err != nil {
		t.Fatalf("NewStore on the upgraded file database: %v", err)
	}
	if !hasColumn(t, second, "urgency") {
		t.Fatalf("NewStore did not add backthink_records.urgency to a file database")
	}
	// 同一库再来一次：幂等。
	if _, err := NewStore(ctx, second, discardLog()); err != nil {
		t.Fatalf("second NewStore on the same file database: %v", err)
	}
	recs, err := s.List(ctx, 0)
	if err != nil {
		t.Fatalf("List: %v", err)
	}
	if len(recs) != 1 || recs[0].Conclusion != "晚上好" || recs[0].Urgency != "" {
		t.Fatalf("legacy file row = %+v, want it preserved with an empty (unknown) urgency", recs)
	}

	// 补列之后新行可写可读。
	if _, err := s.Append(ctx, Record{
		Trigger: "heartbeat", At: time.Date(2026, 10, 5, 12, 0, 0, 0, time.UTC),
		Conclusion: "新行", Urgency: urgency.LevelWarning.String(),
	}); err != nil {
		t.Fatalf("Append after file upgrade: %v", err)
	}
	recs, err = s.List(ctx, 0)
	if err != nil {
		t.Fatalf("List after file upgrade: %v", err)
	}
	if len(recs) != 2 || recs[0].Urgency != urgency.LevelWarning.String() {
		t.Fatalf("records after file upgrade = %+v, want the new round carrying warning", recs)
	}
}

// TestEnsureRecordsUrgencyColumn_ReportsRealFailures 守住「不许吞掉加列错误」：列**真的
// 加不上**时（这里让 backthink_records 恰好是一个视图 ⇒ ALTER 必失败），ensure 必须把
// 错误报出来，而不是假装成功——假装成功的后果是启动日志干净、之后每条 INSERT 都失败。
func TestEnsureRecordsUrgencyColumn_ReportsRealFailures(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	db := newTestDB(t)
	if _, err := db.ExecContext(ctx, `CREATE VIEW backthink_records AS SELECT 1 AS id`); err != nil {
		t.Fatalf("create view fixture: %v", err)
	}
	if hasColumn(t, db, "urgency") {
		t.Fatalf("fixture: the view must not expose an urgency column")
	}
	err := ensureRecordsUrgencyColumn(ctx, db, discardLog())
	if err == nil {
		t.Fatalf("ensure reported success although the column cannot be added")
	}
	if isDuplicateColumnErr(err) {
		t.Fatalf("view-alter error %v misclassified as a duplicate column", err)
	}
}

// TestStore_Urgency_InsertAndSelectNameTheColumn 是一处结构性守卫：INSERT 与 SELECT
// 的列清单必须都含 urgency（列表顺序由列名对齐，靠扫描位置对不上就会读到错列的错——
// 这里用「同一条记录两次读取逐字段相等」把两个语句绑在一起）。
func TestStore_Urgency_InsertAndSelectNameTheColumn(t *testing.T) {
	t.Parallel()
	if !strings.Contains(insertRecordSQL, "urgency") {
		t.Fatalf("insertRecordSQL 未列出 urgency 列：\n%s", insertRecordSQL)
	}
	if !strings.Contains(selectRecordsSQL, "urgency") {
		t.Fatalf("selectRecordsSQL 未取 urgency 列：\n%s", selectRecordsSQL)
	}
	if !strings.Contains(createRecordsSQL, "urgency") {
		t.Fatalf("createRecordsSQL 未含 urgency 列：\n%s", createRecordsSQL)
	}
}

// TestRecord_UrgencyJSONKeyIsUrgency 覆盖验收⑤：序列化后的键名**就是** `urgency`
// （WS 实时帧发的是同一个结构体，前端按 `r.urgency` 读），且「未知」保持键缺席，
// 好与前端的「例常」区分开。
func TestRecord_UrgencyJSONKeyIsUrgency(t *testing.T) {
	t.Parallel()
	raw, err := json.Marshal(Record{
		ID: 7, Trigger: "heartbeat", At: time.Date(2026, 10, 5, 12, 0, 0, 0, time.UTC),
		Conclusion: "结论", Urgency: urgency.LevelCritical.String(),
	})
	if err != nil {
		t.Fatalf("Marshal: %v", err)
	}
	if !strings.Contains(string(raw), `"urgency":"critical"`) {
		t.Fatalf("marshalled record = %s, want the `urgency` key", raw)
	}
	if strings.Contains(string(raw), `"Urgency"`) {
		t.Fatalf("marshalled record = %s, want ONLY the lowercase `urgency` key", raw)
	}

	unknown, err := json.Marshal(Record{ID: 8, Trigger: "heartbeat"})
	if err != nil {
		t.Fatalf("Marshal: %v", err)
	}
	if strings.Contains(string(unknown), "urgency") {
		t.Fatalf("empty urgency must omit the key entirely, got %s", unknown)
	}
}