package integration

import (
	"bytes"
	"context"
	"fmt"
	"io/fs"
	"log/slog"
	"os"
	"regexp"
	"strings"
	"testing"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/TaruLab/keluargamart-backend/internal/dbmigrate"
	"github.com/TaruLab/keluargamart-backend/migrations"
)

var migrationNameRe = regexp.MustCompile(`^\d{6}_.+\.sql$`)

func embeddedRevisionCount(t *testing.T) int {
	t.Helper()
	n := 0
	err := fs.WalkDir(migrations.FS, ".", func(_ string, d fs.DirEntry, err error) error {
		if err != nil {
			return err
		}
		if !d.IsDir() && migrationNameRe.MatchString(d.Name()) {
			n++
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	return n
}

// TestDbMigrate runs internal/dbmigrate against real PostgreSQL inside an
// isolated schema (search_path-scoped, dropped afterwards) so it never touches
// the suite's shared tables. Covers the three startup scenarios: fresh empty
// database applies everything, a legacy pre-runner database (users sentinel)
// baselines without executing anything, and a rerun is a no-op.
func TestDbMigrate(t *testing.T) {
	databaseURL := os.Getenv("DATABASE_URL")
	if databaseURL == "" {
		t.Skip("DATABASE_URL is not set")
	}
	ctx := context.Background()
	want := int64(embeddedRevisionCount(t))
	if want == 0 {
		t.Fatal("no embedded migrations found")
	}

	admin, err := pgxpool.New(ctx, databaseURL)
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(admin.Close)

	schema := fmt.Sprintf("dbmigrate_it_%d", time.Now().UnixNano())
	if _, err := admin.Exec(ctx, "DROP SCHEMA IF EXISTS "+schema+" CASCADE"); err != nil {
		t.Fatal(err)
	}
	if _, err := admin.Exec(ctx, "CREATE SCHEMA "+schema); err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { _, _ = admin.Exec(context.Background(), "DROP SCHEMA IF EXISTS "+schema+" CASCADE") })

	newSchemaPool := func(t *testing.T) *pgxpool.Pool {
		t.Helper()
		cfg, err := pgxpool.ParseConfig(databaseURL)
		if err != nil {
			t.Fatal(err)
		}
		cfg.ConnConfig.RuntimeParams["search_path"] = schema
		pool, err := pgxpool.NewWithConfig(ctx, cfg)
		if err != nil {
			t.Fatal(err)
		}
		t.Cleanup(pool.Close)
		return pool
	}

	trackingCount := func(t *testing.T, pool *pgxpool.Pool) int64 {
		t.Helper()
		var n int64
		if err := pool.QueryRow(ctx, "SELECT count(*) FROM schema_migrations").Scan(&n); err != nil {
			t.Fatal(err)
		}
		return n
	}

	tableExists := func(t *testing.T, pool *pgxpool.Pool, name string) bool {
		t.Helper()
		var exists bool
		if err := pool.QueryRow(ctx,
			`SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = $1)`,
			name).Scan(&exists); err != nil {
			t.Fatal(err)
		}
		return exists
	}

	appTableCountExcluding := func(t *testing.T, pool *pgxpool.Pool, keep ...string) int64 {
		t.Helper()
		var n int64
		var args []any
		conds := "table_name <> 'schema_migrations'"
		for _, k := range keep {
			args = append(args, k)
			conds += fmt.Sprintf(" AND table_name <> $%d", len(args))
		}
		if err := pool.QueryRow(ctx,
			`SELECT count(*) FROM information_schema.tables WHERE table_schema = current_schema() AND `+conds,
			args...).Scan(&n); err != nil {
			t.Fatal(err)
		}
		return n
	}

	run := func(t *testing.T, pool *pgxpool.Pool) string {
		t.Helper()
		var buf bytes.Buffer
		log := slog.New(slog.NewTextHandler(&buf, &slog.HandlerOptions{Level: slog.LevelInfo}))
		if err := dbmigrate.Run(ctx, pool, log); err != nil {
			t.Fatalf("dbmigrate.Run: %v", err)
		}
		return buf.String()
	}

	t.Run("fresh empty database applies all revisions", func(t *testing.T) {
		pool := newSchemaPool(t)
		run(t, pool)

		if got := trackingCount(t, pool); got != want {
			t.Errorf("schema_migrations rows = %d, want %d (all embedded revisions)", got, want)
		}
		if !tableExists(t, pool, "users") || !tableExists(t, pool, "schema_migrations") {
			t.Error("expected users and schema_migrations to exist after full apply")
		}
		// A late revision's object must exist: everything ran, in order.
		if !tableExists(t, pool, "product_barcodes") {
			t.Error("expected product_barcodes (000010) to exist after full apply")
		}
		// 000101 is the newest revision and the one with a backfill + a named
		// unique constraint, so it is the one most likely to fail silently
		// (or to be skipped) on a full apply.
		for _, tbl := range []string{"lhpp_groups", "lhpp_group_items", "lhpp_group_history"} {
			if !tableExists(t, pool, tbl) {
				t.Errorf("expected %s (000101) to exist after full apply", tbl)
			}
		}
		// The invariant must exist as a NAMED TABLE CONSTRAINT in this schema,
		// so the intent is recorded as a rule rather than as a bare performance
		// index, and so it is visible in pg_constraint for schema review. (The
		// name, not this shape, is what internal/lhpp matches on — but a bare
		// index would not appear here at all.) Scoped through pg_class /
		// pg_namespace because this pool's search_path is an isolated schema and
		// the same constraint also exists in public.
		var uniqueCount int
		if err := pool.QueryRow(ctx, `
			SELECT count(*)
			FROM pg_constraint c
			JOIN pg_class t ON t.oid = c.conrelid
			JOIN pg_namespace n ON n.oid = t.relnamespace
			WHERE c.conname = 'lhpp_group_items_do_unique'
			  AND c.contype = 'u'
			  AND n.nspname = current_schema()`,
		).Scan(&uniqueCount); err != nil {
			t.Fatal(err)
		}
		if uniqueCount != 1 {
			t.Errorf("lhpp_group_items_do_unique unique constraints = %d, want 1 (the DO-is-in-at-most-one-group invariant)", uniqueCount)
		}

		// Rerun on a fully migrated database: no-op.
		before := trackingCount(t, pool)
		logs := run(t, pool)
		if got := trackingCount(t, pool); got != before {
			t.Errorf("rerun changed tracking rows: %d -> %d", before, got)
		}
		if !strings.Contains(logs, "up to date") {
			t.Errorf("rerun logs = %q, want up-to-date notice", logs)
		}
	})

	t.Run("legacy database baselines without executing anything", func(t *testing.T) {
		// Simulate a deployment whose schema predates the runner: only the
		// users sentinel exists (compose db-seed / psql history).
		if _, err := admin.Exec(ctx, "DROP SCHEMA "+schema+" CASCADE"); err != nil {
			t.Fatal(err)
		}
		if _, err := admin.Exec(ctx, "CREATE SCHEMA "+schema); err != nil {
			t.Fatal(err)
		}
		if _, err := admin.Exec(ctx, "CREATE TABLE "+schema+".users (id BIGSERIAL PRIMARY KEY)"); err != nil {
			t.Fatal(err)
		}

		pool := newSchemaPool(t)
		logs := run(t, pool)

		if !strings.Contains(logs, fmt.Sprintf("dbmigrate: baselined %d revisions on existing schema", want)) {
			t.Errorf("logs = %q, want baseline notice for %d revisions", logs, want)
		}
		if got := trackingCount(t, pool); got != want {
			t.Errorf("schema_migrations rows = %d, want %d (all revisions recorded)", got, want)
		}
		// Nothing was executed: besides the sentinel users table and the
		// tracking table itself, the schema must be untouched.
		if got := appTableCountExcluding(t, pool, "users"); got != 0 {
			t.Errorf("%d unexpected table(s) created during baseline", got)
		}
		if tableExists(t, pool, "product_barcodes") {
			t.Error("migration files were executed during baseline; want zero executions")
		}

		// Rerun after baseline: no-op.
		before := trackingCount(t, pool)
		logs = run(t, pool)
		if got := trackingCount(t, pool); got != before {
			t.Errorf("rerun changed tracking rows: %d -> %d", before, got)
		}
		if !strings.Contains(logs, "up to date") {
			t.Errorf("rerun logs = %q, want up-to-date notice", logs)
		}
		if got := appTableCountExcluding(t, pool, "users"); got != 0 {
			t.Errorf("rerun executed migration files (%d unexpected table(s))", got)
		}
	})
}