package idear_test

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

	"github.com/carlosframework/rastrillo/db"
	"github.com/carlosframework/rastrillo/migrate"
	"github.com/carlosframework/rastrillo/sessions"

	"amadan.net/rastrillo/idear"
)

// openDB is a fresh, on-disk (t.TempDir) rastrillo db, matching the
// shape schema_test needs to apply migrations against — a real
// database, not an in-memory stand-in, per the brief.
func openDB(t *testing.T) *db.DB {
	t.Helper()
	d, err := db.Open(filepath.Join(t.TempDir(), "idear.db"), nil)
	if err != nil {
		t.Fatalf("db.Open: %v", err)
	}
	t.Cleanup(func() { d.Close() })
	return d
}

// tableExists reports whether name is a table in sqlite_master.
func tableExists(t *testing.T, sqlDB *sql.DB, name string) bool {
	t.Helper()
	var got string
	err := sqlDB.QueryRow(
		`SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?`, name,
	).Scan(&got)
	if err == sql.ErrNoRows {
		return false
	}
	if err != nil {
		t.Fatalf("querying sqlite_master for %s: %v", name, err)
	}
	return got == name
}

// TestSchema_Apply checks that merging idear.Schema in after
// sessions.Schema — the documented BootSchema order — and applying it
// to a fresh database creates both idear tables.
func TestSchema_Apply(t *testing.T) {
	d := openDB(t)
	full := migrate.Merge(sessions.Schema, idear.Schema)
	if _, err := migrate.Apply(context.Background(), d, full); err != nil {
		t.Fatalf("migrate.Apply: %v", err)
	}

	sqlDB := d.Writer()
	if !tableExists(t, sqlDB, "idear_members") {
		t.Errorf("idear_members table was not created")
	}
	if !tableExists(t, sqlDB, "idear_invitations") {
		t.Errorf("idear_invitations table was not created")
	}
}

// TestSchema_Apply_Twice checks that a second Apply is a no-op: the
// ledger records what already ran and refuses to re-run it.
func TestSchema_Apply_Twice(t *testing.T) {
	d := openDB(t)
	full := migrate.Merge(sessions.Schema, idear.Schema)

	first, err := migrate.Apply(context.Background(), d, full)
	if err != nil {
		t.Fatalf("first migrate.Apply: %v", err)
	}
	if len(first.Applied) == 0 {
		t.Fatalf("first migrate.Apply applied nothing; test cannot tell a no-op second run apart from a broken first one")
	}

	second, err := migrate.Apply(context.Background(), d, full)
	if err != nil {
		t.Fatalf("second migrate.Apply: %v", err)
	}
	if len(second.Applied) != 0 {
		t.Errorf("second migrate.Apply applied %v, want none (ledger should have skipped every migration)", second.Applied)
	}
}

// frozenIdearChecksum is migrate.Checksum of idear's shipped
// migrations/0001_init.sql, recorded the day it shipped.
//
// ==> THIS CONSTANT MAY NEVER BE UPDATED. <==
//
// It was re-recorded ONCE, on 2026-08-24, during task 3 and before
// idear had a v0.1.0 or a single consumer, because the migration as
// task 2 wrote it did not work: the timestamp columns were declared
// TEXT, and modernc.org/sqlite only decodes a text timestamp back into
// a time.Time when the column's DECLARED type is DATE, DATETIME or
// TIMESTAMP (rows.go, ColumnTypeDatabaseTypeName). Against TEXT
// columns every GORM read of a Member or an Invitation failed with
// "unsupported Scan, storing driver.Value type string into type
// *time.Time" — the tables could be written and never read. DATETIME
// is also what `rastrillo migration generate` emits for a GORM model's
// time.Time (cmd/rastrillo/new.go), so this is the framework's own
// spelling and not a local invention.
//
// That re-recording is the only one there will be. The rule this
// comment states is about migrations some app has in its ledger; on
// 2026-08-24 no ledger anywhere held this one. From the first tag
// onwards the sentence above is literal, and a failure here means an
// edit to revert — not a constant to refresh.
//
// A failure here does not mean the constant is stale — it means an
// edit to a shipped migration file changed its checksum, and that
// edit must be reverted. Every app that has applied this migration has
// this checksum in its ledger; migrate.Apply compares the two on every
// boot and refuses to start if they no longer match. The only
// legitimate way to change a shipped migration's effect is a new
// migration file beside it, with a new ID and a new entry here. See
// rastrillo's own migrate/frozen_checksums_test.go, which this test is
// deliberately shaped after.
const frozenIdearChecksum = "78c47fc344a9c69a564ff91205713baef3f298f6e008d827ca763668ab51941c"

func TestSchema_FrozenChecksum(t *testing.T) {
	for _, m := range idear.Schema.All() {
		if m.ID != "idear/0001_init" {
			t.Fatalf("unexpected migration id %q; if idear has grown a second migration, "+
				"this test must be extended, not just re-pointed at the new one", m.ID)
		}
		if got := migrate.Checksum(m.SQL); got != frozenIdearChecksum {
			t.Errorf("%s: checksum is now %s, was %s.\n"+
				"A shipped migration file was edited. Revert the edit — do NOT update the "+
				"constant. Every deployed app has the old checksum in its ledger and will "+
				"refuse to boot with \"applied with different SQL\". To change what the schema "+
				"becomes, add a new migration file instead.", m.ID, got, frozenIdearChecksum)
		}
	}
}

// TestSchema_RoundTripsAModel writes a Member through GORM and reads
// it back.
//
// It exists because TestSchema_Apply does not, and the difference is
// the whole of the defect this migration was corrected for. Apply
// proves the CREATE TABLE parses. It cannot prove the DECLARED COLUMN
// TYPES are usable, and they were not: with the timestamp columns
// declared TEXT, modernc.org/sqlite handed every stored timestamp back
// as a raw string, because it converts one to a time.Time only when
// the column's declared type is DATE, DATETIME or TIMESTAMP. Every
// read of a Member or an Invitation failed with
//
//	sql: Scan error on column index 5, name "created_at":
//	unsupported Scan, storing driver.Value type string into type *time.Time
//
// and the tables could be written and never read. A schema test that
// only parses the DDL is exactly the shape that let that through, so
// this one exercises a value instead: the assertion is that CreatedAt
// comes back as a real instant, which is only possible if the column
// type is right.
//
// Reverting migrations/0001_init.sql to TEXT must fail HERE, by name,
// and not only as collateral damage in thirty unrelated tests.
func TestSchema_RoundTripsAModel(t *testing.T) {
	d := openDB(t)
	if _, err := migrate.Apply(context.Background(), d,
		migrate.Merge(sessions.Schema, idear.Schema)); err != nil {
		t.Fatalf("migrate.Apply: %v", err)
	}

	m := &idear.Member{Subject: "s1", Email: "a@example.test", Name: "A", Role: idear.RoleOwner}
	if err := d.G.Create(m).Error; err != nil {
		t.Fatalf("writing a Member: %v", err)
	}
	if m.ID == 0 {
		t.Fatal("Create did not assign an id")
	}

	var got idear.Member
	if err := d.G.Where("id = ?", m.ID).Take(&got).Error; err != nil {
		t.Fatalf("reading the Member back: %v\n"+
			"An \"unsupported Scan ... into type *time.Time\" here means a timestamp column "+
			"is declared TEXT again; it must be DATETIME.", err)
	}
	if got.CreatedAt.IsZero() {
		t.Error("CreatedAt came back as the zero time; the column is not carrying an instant")
	}
	if got.UpdatedAt.IsZero() {
		t.Error("UpdatedAt came back as the zero time")
	}
	if got.DeactivatedAt != nil {
		t.Errorf("DeactivatedAt = %v, want nil — a NULL timestamp must scan as a nil *time.Time", got.DeactivatedAt)
	}
	if got.Subject != m.Subject || got.Role != idear.RoleOwner {
		t.Errorf("read back %+v, want subject %q at role owner", got, m.Subject)
	}

	// The nullable timestamp must round-trip when it is SET, too — a
	// deactivated member is read on every request the middleware
	// gates.
	when := time.Now().UTC().Truncate(time.Millisecond)
	if err := d.G.Model(&idear.Member{}).Where("id = ?", m.ID).
		Update("deactivated_at", when).Error; err != nil {
		t.Fatalf("deactivating: %v", err)
	}
	var off idear.Member
	if err := d.G.Where("id = ?", m.ID).Take(&off).Error; err != nil {
		t.Fatalf("reading the deactivated Member back: %v", err)
	}
	if off.DeactivatedAt == nil || !off.DeactivatedAt.Equal(when) {
		t.Errorf("DeactivatedAt = %v, want %v", off.DeactivatedAt, when)
	}
	if off.Active() {
		t.Error("the member reads as active after being deactivated")
	}
}