package db

import (
	"context"
	"database/sql"
	"errors"
	"os"
	"path/filepath"
	"regexp"
	"sort"
	"strings"
	"testing"
	"time"

	"sourcecraft.dev/bigbes/sr-ht-spec/core"
)

// These tests need no database. They cover the logic that decides things —
// transition legality, batch duplicate detection — plus the agreement between
// schema.sql and the migrations, which is otherwise only discovered on a fresh
// install.

func TestProposalBranch(t *testing.T) {
	for _, tc := range []struct {
		id   int
		want string
	}{
		{1, "proposals/1"},
		{42, "proposals/42"},
		{1000000, "proposals/1000000"},
	} {
		got, err := ProposalBranch(tc.id)
		if err != nil {
			t.Errorf("ProposalBranch(%d): %v", tc.id, err)
			continue
		}
		if got != tc.want {
			t.Errorf("ProposalBranch(%d) = %q, want %q", tc.id, got, tc.want)
		}
	}
	seven, err := ProposalBranch(7)
	if err != nil {
		t.Fatalf("ProposalBranch(7): %v", err)
	}
	if !strings.HasPrefix(seven, BranchPrefix) {
		t.Errorf("ProposalBranch must stay under the refs-rule prefix %q", BranchPrefix)
	}
	// An id no row can have names no branch. "proposals/0" would otherwise be
	// created, pushed and looked for.
	for _, id := range []int{0, -1} {
		if _, err := ProposalBranch(id); !errors.Is(err, core.ErrInvalidProposalID) {
			t.Errorf("ProposalBranch(%d) err = %v, want ErrInvalidProposalID", id, err)
		}
	}
	// One derivation: db and gitx must not be able to disagree.
	fromCore, err := core.ProposalBranch(42)
	if err != nil {
		t.Fatalf("core.ProposalBranch(42): %v", err)
	}
	mine, err := ProposalBranch(42)
	if err != nil {
		t.Fatalf("ProposalBranch(42): %v", err)
	}
	if mine != fromCore {
		t.Errorf("db derives %q where core derives %q", mine, fromCore)
	}
}

func TestDuplicateDocIDs(t *testing.T) {
	ref := func(id, path string) DocRef {
		return DocRef{ID: docID(t, id), Path: path}
	}
	for _, tc := range []struct {
		name string
		in   []DocRef
		want []string
	}{
		{"empty", nil, nil},
		{"unique", []DocRef{ref("SPEC-1", "a.md"), ref("SPEC-2", "b.md")}, nil},
		{
			"same id twice at different paths",
			[]DocRef{ref("SPEC-1", "a.md"), ref("SPEC-1", "b.md")},
			[]string{"SPEC-1"},
		},
		{
			"leading zeros are a different id",
			[]DocRef{ref("SPEC-7", "a.md"), ref("SPEC-0007", "b.md")},
			nil,
		},
		{
			"several duplicates, sorted",
			[]DocRef{
				ref("SPEC-2", "a.md"), ref("SPEC-1", "b.md"),
				ref("SPEC-2", "c.md"), ref("SPEC-1", "d.md"),
			},
			[]string{"SPEC-1", "SPEC-2"},
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			got := DuplicateDocIDs(tc.in)
			if len(got) != len(tc.want) {
				t.Fatalf("DuplicateDocIDs = %v, want %v", got, tc.want)
			}
			for i, id := range got {
				if id.String() != tc.want[i] {
					t.Fatalf("DuplicateDocIDs[%d] = %s, want %s", i, id, tc.want[i])
				}
			}
		})
	}
}

func TestCollisionErrorWrapsErrDocIDTaken(t *testing.T) {
	err := &CollisionError{Collisions: []Collision{{
		DocID:    docID(t, "SPEC-7"),
		Existing: &Document{ID: docID(t, "SPEC-7"), SpaceID: 3, Path: "specs/0007.md"},
	}}}
	if !errors.Is(err, ErrDocIDTaken) {
		t.Fatal("CollisionError must match ErrDocIDTaken")
	}
	msg := err.Error()
	for _, want := range []string{"SPEC-7", "space 3", "specs/0007.md"} {
		if !strings.Contains(msg, want) {
			t.Errorf("collision message %q does not name %q", msg, want)
		}
	}
}

// fakeQuerier satisfies Querier but not beginner, standing in for a Store that
// is already bound to a transaction.
type fakeQuerier struct{}

func (fakeQuerier) ExecContext(context.Context, string, ...any) (sql.Result, error) {
	return nil, errors.New("unexpected Exec")
}
func (fakeQuerier) QueryContext(context.Context, string, ...any) (*sql.Rows, error) {
	return nil, errors.New("unexpected Query")
}
func (fakeQuerier) QueryRowContext(context.Context, string, ...any) *sql.Row { return nil }

func TestInTxRefusesToNest(t *testing.T) {
	s := NewStore(fakeQuerier{})
	called := false
	err := s.InTx(context.Background(), func(*Store) error {
		called = true
		return nil
	})
	if !errors.Is(err, ErrNoTransaction) {
		t.Fatalf("InTx on a non-beginner = %v, want ErrNoTransaction", err)
	}
	if called {
		t.Fatal("InTx ran the body without a transaction")
	}
}

// TestResolveArgumentsValidated covers the guards that reject before any SQL is
// issued; the fake Querier would error if a query were attempted.
func TestResolveArgumentsValidated(t *testing.T) {
	s := NewStore(fakeQuerier{})
	ctx := context.Background()

	if err := s.MarkProposalMerged(ctx, 1, core.Approval("bogus"), "abc"); !errors.Is(err, core.ErrInvalidApproval) {
		t.Fatalf("merge with a bogus approval = %v, want ErrInvalidApproval", err)
	}
	if err := s.MarkProposalMerged(ctx, 1, core.ApprovalHuman, ""); err == nil {
		t.Fatal("merge without a merged rev must fail")
	}
	if err := s.MergeProposal(ctx, Merge{ProposalID: 1, Approval: core.ApprovalPolicy}); err == nil {
		t.Fatal("MergeProposal without a merged rev must fail")
	}
	if _, err := s.ListProposalsByState(ctx, core.ProposalState("closed"), 0); !errors.Is(err, core.ErrInvalidState) {
		t.Fatalf("list with a bogus state = %v, want ErrInvalidState", err)
	}
	if _, err := s.OpenProposal(ctx, &Proposal{SpaceID: 1, Title: "t", BaseRev: "abc"}); err == nil {
		t.Fatal("OpenProposal without provenance must fail")
	}
	if _, err := s.OpenProposal(ctx, &Proposal{
		SpaceID: 1, Title: "t", Agent: "a", AgentSession: "s",
	}); err == nil {
		t.Fatal("OpenProposal without a base rev must fail")
	}
	if _, err := s.CreateSpace(ctx, core.SpaceRef{Owner: "bigbes", Name: "../etc"}); !errors.Is(err, core.ErrInvalidName) {
		t.Fatalf("CreateSpace with a traversing name = %v, want ErrInvalidName", err)
	}
	if _, err := s.CreateProject(ctx, core.ProjectRef{Owner: "bigbes", Name: "../etc"}); !errors.Is(err, core.ErrInvalidName) {
		t.Fatalf("CreateProject with a traversing name = %v, want ErrInvalidName", err)
	}
	// The meta-project is an address that resolves to a filter excluding
	// nothing. A row claiming its name could only shadow it, so no door
	// creates one.
	if _, err := s.CreateProject(ctx, core.ProjectRef{
		Owner: "bigbes", Name: core.MetaProjectName,
	}); !errors.Is(err, core.ErrReservedName) {
		t.Fatalf("CreateProject of the meta-project = %v, want ErrReservedName", err)
	}
	if err := s.SetDigestMark(ctx, "", time.Now()); err == nil {
		t.Fatal("SetDigestMark without an owner must fail")
	}
	if err := s.SetDigestMark(ctx, "bigbes", time.Time{}); err == nil {
		t.Fatal("SetDigestMark with a zero timestamp must fail")
	}
	if _, err := s.SetIndexStamp(ctx, 1, ""); err == nil {
		t.Fatal("SetIndexStamp without a rev must fail")
	}
	if err := s.UpsertDocIDs(ctx, 1, []DocRef{
		{ID: docID(t, "SPEC-1"), Path: "a.md"},
		{ID: docID(t, "SPEC-1"), Path: "b.md"},
	}, "abc"); !errors.Is(err, ErrDocIDDuplicate) {
		t.Fatalf("upsert with an in-batch duplicate = %v, want ErrDocIDDuplicate", err)
	}
	if err := s.UpsertDocIDs(ctx, 1, []DocRef{
		{ID: docID(t, "SPEC-1"), Path: "../escape.md"},
	}, "abc"); !errors.Is(err, core.ErrInvalidPath) {
		t.Fatalf("upsert with a traversing path = %v, want ErrInvalidPath", err)
	}
	// An empty batch is a legal no-op and must not reach SQL.
	if err := s.UpsertDocIDs(ctx, 1, nil, "abc"); err != nil {
		t.Fatalf("empty upsert = %v, want nil", err)
	}
	if c, err := s.CheckDocIDCollisions(ctx, 1, nil); err != nil || c != nil {
		t.Fatalf("empty collision check = %v, %v; want nil, nil", c, err)
	}
}

// TestTransitionGuardMatchesCore pins the guard this package relies on: the
// UPDATE ... WHERE state = 'open' clause is only correct because open is the
// sole state anything may leave.
func TestTransitionGuardMatchesCore(t *testing.T) {
	legal := map[[2]core.ProposalState]bool{
		{core.StateOpen, core.StateMerged}:   true,
		{core.StateOpen, core.StateRejected}: true,
	}
	for _, from := range core.ProposalStates() {
		for _, to := range core.ProposalStates() {
			want := legal[[2]core.ProposalState{from, to}]
			if got := core.ValidTransition(from, to); got != want {
				t.Errorf("ValidTransition(%s, %s) = %v, want %v", from, to, got, want)
			}
		}
	}
	if err := core.StateOpen.CanTransitionTo(core.StateOpen); !errors.Is(err, core.ErrInvalidTransition) {
		t.Fatalf("open->open = %v, want ErrInvalidTransition", err)
	}
	if err := core.StateMerged.CanTransitionTo(core.StateRejected); !errors.Is(err, core.ErrInvalidTransition) {
		t.Fatalf("merged->rejected = %v, want ErrInvalidTransition", err)
	}
}

func TestNullable(t *testing.T) {
	if nullable("") != nil {
		t.Error("empty string must become SQL NULL")
	}
	if nullable("x") != any("x") {
		t.Error("non-empty string must pass through")
	}
}

// TestSchemaMatchesMigration is the check a fresh install would otherwise fail:
// schema.sql is the authoritative DDL, the migrations under migrations/ build
// the same objects in the same order, and the two drifting apart means a
// migrated database and a freshly created one are different databases.
//
// Every migration is replayed, not just the first: a new table added to
// schema.sql and to 0002 must appear in both, and appending it to only one of
// them is exactly the drift this test exists to catch.
func TestSchemaMatchesMigration(t *testing.T) {
	schema, err := os.ReadFile("../schema.sql")
	if err != nil {
		t.Fatalf("read schema.sql: %v", err)
	}
	files, err := filepath.Glob("../migrations/*.sql")
	if err != nil {
		t.Fatalf("glob migrations: %v", err)
	}
	if len(files) == 0 {
		t.Fatal("no migrations found")
	}
	sort.Strings(files) // brant applies them in filename order

	var ups, downs []string
	for _, f := range files {
		migration, err := os.ReadFile(f)
		if err != nil {
			t.Fatalf("read %s: %v", f, err)
		}
		up, down, ok := splitBrant(string(migration))
		if !ok {
			t.Fatalf("%s is missing a `-- +brant Up` / `-- +brant Down` pair", f)
		}
		ups = append(ups, normalizeStatements(up)...)
		downs = append(downs, normalizeStatements(down)...)
	}

	fromSchema := normalizeStatements(string(schema))
	fromMigration := applyDrops(t, ups)
	if len(fromSchema) != len(fromMigration) {
		t.Fatalf("schema.sql has %d statements, migration Up has %d:\n%v\n%v",
			len(fromSchema), len(fromMigration), fromSchema, fromMigration)
	}
	for i := range fromSchema {
		if fromSchema[i] != fromMigration[i] {
			t.Errorf("statement %d differs:\n schema.sql: %s\n migration : %s",
				i, fromSchema[i], fromMigration[i])
		}
	}

	// Every table created must be dropped, so a Down actually undoes the Up.
	created := tableNames(fromSchema, "CREATE TABLE ")
	dropped := tableNames(downs, "DROP TABLE ")
	if len(created) == 0 {
		t.Fatal("no CREATE TABLE statements found in schema.sql")
	}
	for _, name := range created {
		if !contains(dropped, name) {
			t.Errorf("table %q is created by Up but not dropped by Down", name)
		}
	}

	// `comment` was on an absent-list here through v1, so that adding it needed
	// a design change rather than a quiet migration. Phase 5b is that design
	// change: the anchoring model was settled against the built review UI, so
	// the table is now required present like any other.
	for _, want := range []string{"space", "document_id", "proposal",
		"index_stamp", "digest_mark", "project", "project_space", "comment"} {
		if !contains(created, want) {
			t.Errorf("table %q is missing from schema.sql", want)
		}
	}

	// agent_token is required *absent*: agent credentials are issued by
	// tokens.sr.ht and validated by signature, so a service that still had the
	// table would still have a second door into the write plane.
	if contains(created, "agent_token") {
		t.Error("agent_token is back in schema.sql; agent credentials come from tokens.sr.ht")
	}
}

// applyDrops folds a migration history the way Postgres does: a DROP TABLE
// removes the CREATE TABLE it names, the indexes on it, and itself.
//
// Without the fold the comparison above could only hold for an append-only
// history, and the first migration to remove a table — agent_token, when agent
// issuance moved to tokens.sr.ht — would have had to weaken the check instead of
// being checked by it. A statement that still names a dropped table after the
// fold is a fatal error rather than a silent pass: it means the history does
// something (an ALTER, a backfill) that this small folder does not model, and
// guessing would make the agreement test lie.
func applyDrops(t *testing.T, stmts []string) []string {
	t.Helper()
	var out []string
	for _, stmt := range stmts {
		name, dropped := droppedTable(stmt)
		if !dropped {
			out = append(out, stmt)
			continue
		}
		kept := out[:0]
		for _, prev := range out {
			if createsTable(prev, name) || indexesTable(prev, name) {
				continue
			}
			if mentions(prev, name) {
				t.Fatalf("migration drops %q, but this earlier statement still names it "+
					"and is not a CREATE TABLE or CREATE INDEX:\n  %s", name, prev)
			}
			kept = append(kept, prev)
		}
		out = kept
	}
	return out
}

var (
	dropTableRe  = regexp.MustCompile(`^DROP TABLE (?:IF EXISTS )?"?(\w+)"?$`)
	createIndex  = regexp.MustCompile(`^CREATE (?:UNIQUE )?INDEX \w+ ON "?(\w+)"?[ (]`)
	identifierRe = func(name string) *regexp.Regexp { return regexp.MustCompile(`\b` + name + `\b`) }
)

func droppedTable(stmt string) (string, bool) {
	m := dropTableRe.FindStringSubmatch(strings.TrimSpace(stmt))
	if m == nil {
		return "", false
	}
	return m[1], true
}

func createsTable(stmt, name string) bool {
	rest, ok := strings.CutPrefix(stmt, "CREATE TABLE ")
	if !ok {
		return false
	}
	return strings.HasPrefix(rest, name+"(") || strings.HasPrefix(rest, name+" ") ||
		strings.HasPrefix(rest, `"`+name+`"`)
}

func indexesTable(stmt, name string) bool {
	m := createIndex.FindStringSubmatch(stmt)
	return m != nil && m[1] == name
}

func mentions(stmt, name string) bool { return identifierRe(name).MatchString(stmt) }

func splitBrant(src string) (up, down string, ok bool) {
	i := strings.Index(src, "-- +brant Up")
	j := strings.Index(src, "-- +brant Down")
	if i < 0 || j < 0 || j < i {
		return "", "", false
	}
	return src[i+len("-- +brant Up") : j], src[j+len("-- +brant Down"):], true
}

var (
	lineComment = regexp.MustCompile(`(?m)--.*$`)
	whitespace  = regexp.MustCompile(`\s+`)
)

// normalizeStatements strips line comments, collapses whitespace and splits on
// ';', so two DDL files that differ only in commentary and indentation compare
// equal.
func normalizeStatements(src string) []string {
	src = lineComment.ReplaceAllString(src, " ")
	var out []string
	for _, stmt := range strings.Split(src, ";") {
		stmt = strings.TrimSpace(whitespace.ReplaceAllString(stmt, " "))
		stmt = strings.ReplaceAll(stmt, "( ", "(")
		stmt = strings.ReplaceAll(stmt, " )", ")")
		if stmt != "" {
			out = append(out, stmt)
		}
	}
	return out
}

func tableNames(stmts []string, prefix string) []string {
	var out []string
	for _, s := range stmts {
		if !strings.HasPrefix(s, prefix) {
			continue
		}
		rest := strings.TrimPrefix(s, prefix)
		if i := strings.IndexAny(rest, " ("); i >= 0 {
			rest = rest[:i]
		}
		out = append(out, strings.Trim(rest, `"`))
	}
	return out
}

func contains(hay []string, needle string) bool {
	for _, h := range hay {
		if h == needle {
			return true
		}
	}
	return false
}