package db

import (
	"context"
	"database/sql"
	"path/filepath"
	"sort"
	"testing"

	"github.com/bluesky-social/indigo/atproto/syntax"
	_ "github.com/mattn/go-sqlite3"
	"tangled.org/core/eventstream"
)

func openLegacyDB(t *testing.T) *sql.DB {
	t.Helper()
	path := filepath.Join(t.TempDir(), "knot.db")
	d, err := sql.Open("sqlite3", path+"?_foreign_keys=1")
	if err != nil {
		t.Fatalf("open: %v", err)
	}
	t.Cleanup(func() { d.Close() })

	if _, err := d.Exec(`
		create table events (
			rkey text not null,
			nsid text not null,
			event text not null,
			created integer not null default (strftime('%s', 'now')),
			primary key (rkey, nsid)
		);
		create table migrations (
			id integer primary key autoincrement,
			name text unique
		);
	`); err != nil {
		t.Fatalf("legacy schema: %v", err)
	}
	return d
}

func runEventsMigration(t *testing.T, d *sql.DB) {
	t.Helper()
	tx, err := d.Begin()
	if err != nil {
		t.Fatalf("begin: %v", err)
	}
	if err := eventstream.MigrateAddTID(context.Background(), tx); err != nil {
		tx.Rollback()
		t.Fatalf("migrate: %v", err)
	}
	if err := tx.Commit(); err != nil {
		t.Fatalf("commit: %v", err)
	}
}

func TestMigrateEventsAddTID_PreservesData(t *testing.T) {
	d := openLegacyDB(t)

	seed := []struct {
		rkey, nsid, event string
		created           int64
	}{
		{"r1", "sh.tangled.test", `{"i":1}`, 1_700_000_000_000_000_000},
		{"r2", "sh.tangled.test", `{"i":2}`, 1_700_000_000_000_001_000},
		{"r3", "sh.tangled.test", `{"i":3}`, 1_700_000_000_000_002_000},
		{"r4", "sh.tangled.other", `{"i":4}`, 1_700_000_000_000_002_000},
	}
	for _, s := range seed {
		if _, err := d.Exec(
			`insert into events (rkey, nsid, event, created) values (?, ?, ?, ?)`,
			s.rkey, s.nsid, s.event, s.created,
		); err != nil {
			t.Fatalf("seed insert: %v", err)
		}
	}

	runEventsMigration(t, d)

	var count int
	if err := d.QueryRow(`select count(*) from events`).Scan(&count); err != nil {
		t.Fatalf("count: %v", err)
	}
	if count != len(seed) {
		t.Fatalf("row count = %d, want %d", count, len(seed))
	}

	var hasTid int
	if err := d.QueryRow(
		`select count(*) from pragma_table_info('events') where name = 'tid'`,
	).Scan(&hasTid); err != nil {
		t.Fatalf("pragma: %v", err)
	}
	if hasTid != 1 {
		t.Fatal("tid column missing after migration")
	}

	rows, err := d.Query(`select tid, rkey, created from events order by tid asc`)
	if err != nil {
		t.Fatalf("read back: %v", err)
	}
	defer rows.Close()

	type row struct {
		tid, rkey string
		created   int64
	}
	var got []row
	for rows.Next() {
		var r row
		if err := rows.Scan(&r.tid, &r.rkey, &r.created); err != nil {
			t.Fatalf("scan: %v", err)
		}
		got = append(got, r)
	}

	for _, r := range got {
		if _, err := syntax.ParseTID(r.tid); err != nil {
			t.Fatalf("row %s has invalid TID %q: %v", r.rkey, r.tid, err)
		}
	}

	sorted := append([]row(nil), got...)
	sort.Slice(sorted, func(i, j int) bool { return sorted[i].created < sorted[j].created })
	for i := range got {
		if got[i].rkey != sorted[i].rkey && got[i].created != sorted[i].created {
			t.Errorf("TID order diverges from created order at %d: %+v vs %+v", i, got[i], sorted[i])
		}
	}

	for i := 1; i < len(got); i++ {
		if got[i].tid <= got[i-1].tid {
			t.Errorf("TID not strictly monotonic: %s <= %s", got[i].tid, got[i-1].tid)
		}
	}
}

func TestMigrateEventsAddTID_Idempotent(t *testing.T) {
	d := openLegacyDB(t)
	if _, err := d.Exec(
		`insert into events (rkey, nsid, event, created) values ('r1', 'n', '{}', 1)`,
	); err != nil {
		t.Fatalf("seed: %v", err)
	}

	runEventsMigration(t, d)

	var firstTid string
	if err := d.QueryRow(`select tid from events where rkey = 'r1'`).Scan(&firstTid); err != nil {
		t.Fatalf("get first: %v", err)
	}

	runEventsMigration(t, d)

	var secondTid string
	if err := d.QueryRow(`select tid from events where rkey = 'r1'`).Scan(&secondTid); err != nil {
		t.Fatalf("get second: %v", err)
	}
	if firstTid != secondTid {
		t.Fatalf("second migration mutated TID: %q != %q", firstTid, secondTid)
	}
}

func TestSetupBootsLegacyDB(t *testing.T) {
	path := filepath.Join(t.TempDir(), "knot.db")
	d, err := sql.Open("sqlite3", path+"?_foreign_keys=1")
	if err != nil {
		t.Fatalf("open: %v", err)
	}
	if _, err := d.Exec(`
		create table events (
			rkey text not null,
			nsid text not null,
			event text not null,
			created integer not null default (strftime('%s', 'now')),
			primary key (rkey, nsid)
		);
		insert into events (rkey, nsid, event, created) values ('r1', 'n', '{}', 1700000000000000000);
	`); err != nil {
		d.Close()
		t.Fatalf("seed legacy schema: %v", err)
	}
	d.Close()

	db, err := Setup(context.Background(), path)
	if err != nil {
		t.Fatalf("Setup on legacy DB: %v", err)
	}
	t.Cleanup(func() { db.db.Close() })

	var hasTid int
	if err := db.db.QueryRow(
		`select count(*) from pragma_table_info('events') where name = 'tid'`,
	).Scan(&hasTid); err != nil {
		t.Fatalf("pragma: %v", err)
	}
	if hasTid != 1 {
		t.Fatal("tid column missing after Setup")
	}

	var hasIndex int
	if err := db.db.QueryRow(
		`select count(*) from sqlite_master where type='index' and name='idx_events_tid'`,
	).Scan(&hasIndex); err != nil {
		t.Fatalf("pragma index: %v", err)
	}
	if hasIndex != 1 {
		t.Fatal("idx_events_tid missing after Setup")
	}
}

func TestSetupIdempotentOnFreshDB(t *testing.T) {
	path := filepath.Join(t.TempDir(), "knot.db")
	db, err := Setup(context.Background(), path)
	if err != nil {
		t.Fatalf("first Setup: %v", err)
	}
	db.db.Close()

	db, err = Setup(context.Background(), path)
	if err != nil {
		t.Fatalf("second Setup: %v", err)
	}
	t.Cleanup(func() { db.db.Close() })

	var hasIndex int
	if err := db.db.QueryRow(
		`select count(*) from sqlite_master where type='index' and name='idx_events_tid'`,
	).Scan(&hasIndex); err != nil {
		t.Fatalf("pragma index: %v", err)
	}
	if hasIndex != 1 {
		t.Fatal("idx_events_tid missing on fresh DB")
	}
}

func TestMigrateEventsAddTID_HandlesTiedCreated(t *testing.T) {
	d := openLegacyDB(t)
	if _, err := d.Exec(`
		insert into events (rkey, nsid, event, created) values
			('a', 'n', '{}', 100),
			('b', 'n', '{}', 100),
			('c', 'n', '{}', 100);
	`); err != nil {
		t.Fatalf("seed: %v", err)
	}

	runEventsMigration(t, d)

	rows, err := d.Query(`select tid from events order by rkey asc`)
	if err != nil {
		t.Fatalf("read: %v", err)
	}
	defer rows.Close()
	var tids []string
	for rows.Next() {
		var t string
		if err := rows.Scan(&t); err != nil {
			break
		}
		tids = append(tids, t)
	}
	if len(tids) != 3 {
		t.Fatalf("got %d tids, want 3", len(tids))
	}
	uniq := map[string]struct{}{}
	for _, tt := range tids {
		uniq[tt] = struct{}{}
	}
	if len(uniq) != 3 {
		t.Fatalf("TIDs not unique for tied created: %v", tids)
	}
}
