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 openLegacyEventsDB(t *testing.T) *sql.DB {
	t.Helper()
	path := filepath.Join(t.TempDir(), "spindle.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
		);
		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 := openLegacyEventsDB(t)

	seed := []struct {
		rkey, nsid, event string
		created           int64
	}{
		{"r1", "sh.tangled.pipeline.status", `{"i":1}`, 1_700_000_000_000_000_000},
		{"r2", "sh.tangled.pipeline.status", `{"i":2}`, 1_700_000_000_000_001_000},
		{"r3", "sh.tangled.pipeline.status", `{"i":3}`, 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))
	}

	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_AddsPrimaryKey(t *testing.T) {
	d := openLegacyEventsDB(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)

	if _, err := d.Exec(
		`insert into events (tid, rkey, nsid, event, created) values ('zzzzzzzzzzzzz', 'r1', 'n', '{}', 2)`,
	); err == nil {
		t.Fatal("expected duplicate (rkey, nsid) to fail after migration")
	}
}

func TestMigrateEventsAddTID_ToleratesLegacyDuplicates(t *testing.T) {
	d := openLegacyEventsDB(t)
	if _, err := d.Exec(`
		insert into events (rkey, nsid, event, created) values
			('dup-rk', 'sh.tangled.pipeline.status', '{"i":1}', 100),
			('dup-rk', 'sh.tangled.pipeline.status', '{"i":2}', 200),
			('other',  'sh.tangled.pipeline.status', '{"i":3}', 300);
	`); err != nil {
		t.Fatalf("seed: %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 != 2 {
		t.Fatalf("row count after migration = %d, want 2", count)
	}

	var dupEvent string
	if err := d.QueryRow(
		`select event from events where rkey = 'dup-rk'`,
	).Scan(&dupEvent); err != nil {
		t.Fatalf("read dup: %v", err)
	}
	if dupEvent != `{"i":1}` {
		t.Fatalf("kept event = %q, want first by rowid {\"i\":1}", dupEvent)
	}
}

func TestMakeBootsLegacyDB(t *testing.T) {
	path := filepath.Join(t.TempDir(), "spindle.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
		);
		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 := Make(context.Background(), path)
	if err != nil {
		t.Fatalf("Make on legacy DB: %v", err)
	}
	t.Cleanup(func() { db.Close() })

	var hasTid int
	if err := 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 Make")
	}

	var hasIndex int
	if err := 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 Make")
	}
}

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

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

	var hasIndex int
	if err := 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")
	}
}
