package integration

// 000103's authorization changes, proved against a real PostgreSQL.
//
// A grant table is SQL, and the harness for it is a fake store that never
// interprets SQL — a fake could only re-state the predicate back at us. The
// DATABASE_URL-gated scratch-SCHEMA pattern from internal/finance and
// internal/sales/reconciliation_test.go is this repo's answer to the same
// problem, and it is what this file follows.
//
// The migration is read from the EMBEDDED FS (migrations.FS), not from a
// hand-copied string, so the test exercises the file that will actually ship.
// A test that carried its own copy of the SQL would keep passing after someone
// edited the migration, which is the entire failure mode this file exists to
// prevent.
//
// The scratch schema holds ONLY role_permissions, created from 000030's DDL
// verbatim. That is the whole dependency surface: 000103 reads and writes
// nothing else, so this is a complete environment for it and the test never
// touches the developer's own data or a partial-migration snapshot.

import (
	"context"
	"fmt"
	"os"
	"regexp"
	"strings"
	"testing"
	"time"

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

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

// rolePermissionsDDL is 000030_multi_tenant_foundation.sql, verbatim. The
// composite primary key is what makes the migration's ON CONFLICT-shaped
// NOT EXISTS guards and its re-run behaviour meaningful, so it is not
// simplified here.
const rolePermissionsDDL = `CREATE TABLE role_permissions (
  "role" text NOT NULL,
  "permission" text NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("role", "permission")
)`

// grants is one (role, permission) pair the test asserts about.
type grant struct {
	role, permission string
}

// revokedGrants is every pair 000103 removes, transcribed from the migration's
// DELETE list. It is duplicated here ON PURPOSE and cross-checked against the
// file by TestMigration103RevocationListMatchesTheFile: a hand-written copy
// that silently drifted from the SQL would be a test that passes while the
// migration does something else. That cross-check is what makes the
// duplication safe rather than dangerous.
var revokedGrants = []grant{
	{"cashier", "accounting.ledger.view"},
	{"store_manager", "accounting.ledger.view"},
	{"sales", "accounting.ledger.view"},
	{"warehouse", "accounting.ledger.view"},
	{"spv", "accounting.ledger.view"},
	{"bm", "accounting.ledger.view"},
	{"gm", "accounting.ledger.view"},
	{"cashier", "accounting.journals.view"},
	{"cashier", "accounting.journals.create"},
	{"cashier", "accounting.journals.update"},
	{"cashier", "accounting.journals.delete"},
	{"store_manager", "accounting.journals.delete"},
	{"sales", "accounting.journals.delete"},
	{"warehouse", "accounting.journals.delete"},
	{"spv", "accounting.journals.delete"},
	{"bm", "accounting.journals.delete"},
	{"gm", "accounting.journals.delete"},
	{"buyer", "sales.sales-orders.view"},
	{"cashier", "sales.sales-orders.view"},
	{"cashier", "system.users.view"},
	{"store_manager", "system.users.view"},
	{"sales", "system.users.view"},
	{"cashier", "system.settings.view"},
}

// retainedGrants must SURVIVE the migration. Each is here because the
// migration is surgical: a DELETE written as a range or a subquery is the
// obvious way to over-revoke, and "the roles that still need it" is only
// checkable by naming what must remain. hr_staff in particular is a near
// neighbour of hr_manager and the restore touches the same table.
var retainedGrants = []grant{
	// The till keeps its own money surface. finance.cashflows.* for cashier is
	// explicitly NOT touched by 000103 (pending a business decision), so
	// asserting it here is what stops a later edit from quietly taking it.
	{"cashier", "finance.cashflows.view"},
	{"cashier", "finance.cashflows.create"},
	// sales keeps hr.employees.view — also explicitly not touched.
	{"sales", "hr.employees.view"},
	// hr_staff's concrete keys are untouched by the hr_manager restore.
	{"hr_staff", "hr.employees.view"},
	{"hr_staff", "hr.attendance.view"},
	// The accounting roles keep everything: they are the ones the revocations
	// route AROUND.
	{"finance", "accounting.ledger.view"},
	{"finance", "accounting.journals.view"},
	{"finance", "accounting.journals.delete"},
	{"finance_head", "accounting.ledger.view"},
	{"finance_head", "accounting.journals.post"},
	// A role that legitimately reads orders keeps them.
	{"sales", "masterdata.products.read"},
	{"store_manager", "inventory.stock.view"},
	{"warehouse", "inventory.stock.view"},
	{"bm", "inventory.stock.view"},
	{"spv", "inventory.stock.view"},
	{"gm", "inventory.stock.view"},
	{"buyer", "cart.add"},
	{"buyer", "cart.checkout"},
}

// preMigrationState is the grant table as the accreted migrations left it: the
// 23 pairs 000103 removes, plus the retained rows, plus hr_manager with
// nothing (the 000061 wipe). Seeding exactly this and asserting the exact
// post-state is what makes the test a specification rather than a smoke test.
func preMigrationState() []grant {
	out := make([]grant, 0, len(revokedGrants)+len(retainedGrants))
	out = append(out, revokedGrants...)
	out = append(out, retainedGrants...)
	return out
}

// migrationEnv is one test's scratch schema with the migration applied.
type migrationEnv struct {
	pool   *pgxpool.Pool
	schema string
}

// newMigrationEnv creates a throwaway schema holding only role_permissions,
// seeds it with the pre-000103 state, and applies 000103's SQL to it.
// applyCount lets a test run the migration twice, which is how idempotency is
// proved rather than asserted.
func newMigrationEnv(t *testing.T, seed []grant, applyCount int) *migrationEnv {
	t.Helper()
	databaseURL := os.Getenv("DATABASE_URL")
	if databaseURL == "" {
		t.Skip("DATABASE_URL is not set")
	}
	ctx := context.Background()

	admin, err := pgxpool.New(ctx, databaseURL)
	if err != nil {
		t.Fatal(err)
	}
	schema := fmt.Sprintf("authz_103_%d", time.Now().UnixNano())
	// Drop first: a previous run that died before cleanup leaves the schema
	// behind and CREATE SCHEMA would fail on the name.
	if _, err := admin.Exec(ctx, "DROP SCHEMA IF EXISTS "+schema+" CASCADE"); err != nil {
		admin.Close()
		t.Fatal(err)
	}
	if _, err := admin.Exec(ctx, "CREATE SCHEMA "+schema); err != nil {
		admin.Close()
		t.Fatal(err)
	}

	cfg, err := pgxpool.ParseConfig(databaseURL)
	if err != nil {
		admin.Close()
		t.Fatal(err)
	}
	cfg.ConnConfig.RuntimeParams["search_path"] = schema
	cfg.MaxConns = 2
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		admin.Close()
		t.Fatal(err)
	}
	t.Cleanup(func() {
		pool.Close()
		if _, err := admin.Exec(context.Background(), "DROP SCHEMA IF EXISTS "+schema+" CASCADE"); err != nil {
			t.Logf("drop schema %s: %v", schema, err)
		}
		admin.Close()
	})

	if _, err := pool.Exec(ctx, rolePermissionsDDL); err != nil {
		t.Fatalf("create role_permissions: %v", err)
	}
	for _, g := range seed {
		if _, err := pool.Exec(ctx,
			`INSERT INTO role_permissions (role, permission) VALUES ($1, $2)`, g.role, g.permission); err != nil {
			t.Fatalf("seed (%s,%s): %v", g.role, g.permission, err)
		}
	}
	sql := migrationSQL(t)
	for i := 0; i < applyCount; i++ {
		if _, err := pool.Exec(ctx, sql); err != nil {
			t.Fatalf("apply 000103 (pass %d): %v", i+1, err)
		}
	}
	return &migrationEnv{pool: pool, schema: schema}
}

// migrationSQL reads 000103 out of the embedded migration directory, so the
// test runs the file that ships.
func migrationSQL(t *testing.T) string {
	t.Helper()
	b, err := migrations.FS.ReadFile("000103_revoke_operator_accounting_and_restore_hr.sql")
	if err != nil {
		t.Fatalf("read embedded 000103: %v", err)
	}
	return string(b)
}

// grants reads the whole table back.
func (e *migrationEnv) grants(t *testing.T) map[string]bool {
	t.Helper()
	rows, err := e.pool.Query(context.Background(),
		`SELECT role, permission FROM role_permissions`)
	if err != nil {
		t.Fatal(err)
	}
	defer rows.Close()
	out := map[string]bool{}
	for rows.Next() {
		var role, perm string
		if err := rows.Scan(&role, &perm); err != nil {
			t.Fatal(err)
		}
		out[role+"|"+perm] = true
	}
	if err := rows.Err(); err != nil {
		t.Fatal(err)
	}
	return out
}

func (e *migrationEnv) has(t *testing.T, role, perm string) bool {
	t.Helper()
	return e.grants(t)[role+"|"+perm]
}

// THE REVOCATION. Every pair the migration claims to remove is gone afterwards.
// Seeded from the exact pre-state, so a DELETE written as a range or a
// subquery that over-reaches cannot pass this.
func TestMigration103RevokesTheLeakedGrants(t *testing.T) {
	env := newMigrationEnv(t, preMigrationState(), 1)
	got := env.grants(t)
	for _, g := range revokedGrants {
		if got[g.role+"|"+g.permission] {
			t.Errorf("(%s, %s) survived 000103 but should have been revoked", g.role, g.permission)
		}
	}
}

// ...AND NOTHING ELSE WAS TOUCHED. The same table, asserting the rows that had
// to stay. Over-revoking is the failure mode a DELETE-heavy migration has and
// the one that produces a 3am page; this is the assertion that catches it.
func TestMigration103RetainsEverythingElse(t *testing.T) {
	env := newMigrationEnv(t, preMigrationState(), 1)
	got := env.grants(t)
	for _, g := range retainedGrants {
		if !got[g.role+"|"+g.permission] {
			t.Errorf("(%s, %s) was removed by 000103 but had to be retained", g.role, g.permission)
		}
	}
}

// THE RESTORE. hr_manager gets BOTH 000057 pairs back: hr.* (000057:559-560)
// and dashboard.view (000057:555-556). Both were wiped by 000061 and neither
// was re-granted, so this is a restoration of an accidental deletion rather
// than a new grant. /employees needs hr.employees.view, which only hr.* carries;
// the dashboard is where an HR manager lands.
//
// The absence assertions are the other half: the restore is hr_manager's alone,
// so a "while we are in here" grant to another role fails here.
func TestMigration103RestoresHrManager(t *testing.T) {
	env := newMigrationEnv(t, preMigrationState(), 1)
	got := env.grants(t)

	if !got["hr_manager|hr.*"] {
		t.Error("hr_manager did not get hr.* back: /employees would still 403")
	}
	if !got["hr_manager|dashboard.view"] {
		t.Error("hr_manager did not get dashboard.view back: it is the same 000061 wipe, " +
			"so restoring hr.* while leaving it out locks the role out through a second door")
	}
	// Exactly those two, and nothing else for hr_manager.
	for key := range got {
		if !strings.HasPrefix(key, "hr_manager|") {
			continue
		}
		if key != "hr_manager|hr.*" && key != "hr_manager|dashboard.view" {
			t.Errorf("hr_manager holds %q, want only the two 000057 pairs", key)
		}
	}
	// No hr.* for anyone else: a wildcard grant is the one thing here that
	// would reach far beyond its role.
	for key := range got {
		if strings.HasSuffix(key, "|hr.*") && !strings.HasPrefix(key, "hr_manager|") {
			t.Errorf("hr.* granted to %s, want hr_manager only", strings.SplitN(key, "|", 2)[0])
		}
	}
}

// costViewRoles is the reviewed list 000103 grants inventory.cost.view to: the
// roles that VALUE the stock they own. Transcribed from the migration's VALUES
// list and cross-checked against the file by
// TestMigration103CostGrantListMatchesTheFile, so this copy cannot rot.
var costViewRoles = []string{"store_manager", "spv", "bm", "gm"}

// costDeniedRoles are the roles that must NOT see cost. Named explicitly
// because an omission is invisible: a grant list is read for what it contains,
// and the roles that were considered and rejected only show up in the absence.
var costDeniedRoles = []string{"sales", "warehouse", "cashier", "buyer"}

// THE COST KEY AND ITS GRANTS SHIP TOGETHER, and cost visibility follows
// VALUATION rather than audit. An earlier draft correlated the grant with
// accounting.ledger.view, which handed it to finance and denied it to the four
// roles that actually run stock valuation — blanking the ERP's Nilai Stok card,
// the Posisi Stok columns, the stock export and the Dashboard for all of them.
//
// So the assertion is the reviewed list in both directions: the four valuation
// roles hold the key, and the rejected ones do not.
func TestMigration103GrantsCostViewToValuationRoles(t *testing.T) {
	env := newMigrationEnv(t, preMigrationState(), 1)
	got := env.grants(t)

	for _, role := range costViewRoles {
		if !got[role+"|inventory.cost.view"] {
			t.Errorf("%s does not hold inventory.cost.view; the ERP stock valuation "+
				"screens (Nilai Stok card, Posisi Stok, Excel export, Dashboard) would render blank",
				role)
		}
	}
	// sales is called out with the others but the reason differs: it is
	// WITHHELD BY DECISION, and the day the business decides otherwise this is
	// the assertion that has to change.
	for _, role := range costDeniedRoles {
		if got[role+"|inventory.cost.view"] {
			t.Errorf("%s holds inventory.cost.view; that role was reviewed and rejected", role)
		}
	}
	// Nothing outside the list at all: the grant is exactly four roles.
	holders := 0
	for key := range got {
		if !strings.HasSuffix(key, "|inventory.cost.view") {
			continue
		}
		holders++
		if !containsRole(costViewRoles, strings.SplitN(key, "|", 2)[0]) {
			t.Errorf("inventory.cost.view granted to %s, which is not on the reviewed list",
				strings.SplitN(key, "|", 2)[0])
		}
	}
	if holders != len(costViewRoles) {
		t.Errorf("inventory.cost.view held by %d roles, want %d", holders, len(costViewRoles))
	}
	// admin/owner bypass Can() outright and are never seeded in this repo's
	// migration series, so the migration must not seed them either.
	if got["admin|inventory.cost.view"] {
		t.Error("admin was seeded inventory.cost.view; it bypasses Can() and is never seeded")
	}
	if got["owner|inventory.cost.view"] {
		t.Error("owner was seeded inventory.cost.view; it bypasses Can() and is never seeded")
	}
}

// IDEMPOTENCY, PROVED BY RUNNING IT TWICE. atlas applies a revision exactly
// once, so this is not about atlas — it is about a human re-running a file
// against a database that already has it, which is what happens when someone
// runs migrations by hand during an incident. The second run must not error and
// must not change a single row.
func TestMigration103IsIdempotent(t *testing.T) {
	env := newMigrationEnv(t, preMigrationState(), 2)
	got := env.grants(t)

	if !env.has(t, "hr_manager", "hr.*") {
		t.Error("hr.* missing after two applications")
	}
	if !env.has(t, "hr_manager", "dashboard.view") {
		t.Error("dashboard.view missing after two applications")
	}
	for _, role := range costViewRoles {
		if !env.has(t, role, "inventory.cost.view") {
			t.Errorf("inventory.cost.view missing for %s after two applications", role)
		}
	}
	for _, g := range revokedGrants {
		if got[g.role+"|"+g.permission] {
			t.Errorf("(%s, %s) present after two applications", g.role, g.permission)
		}
	}
	for _, g := range retainedGrants {
		if !got[g.role+"|"+g.permission] {
			t.Errorf("(%s, %s) missing after two applications", g.role, g.permission)
		}
	}
	// Row COUNT is the real idempotency assertion: a re-run that inserted a
	// duplicate would still satisfy the membership checks above, because the
	// composite primary key would have rejected it — but a re-run that
	// deleted-and-reinserted, or granted to one more role, would not. The count
	// is what catches a widening.
	var n int
	if err := env.pool.QueryRow(context.Background(),
		`SELECT count(*) FROM role_permissions`).Scan(&n); err != nil {
		t.Fatal(err)
	}
	// 2 for the hr_manager restore (hr.* + dashboard.view) and one per
	// reviewed cost.view role.
	want := len(retainedGrants) + 2 + len(costViewRoles)
	if n != want {
		t.Errorf("role_permissions rows = %d, want %d after two applications (a re-run changed the table size)", n, want)
	}
}

// The grant list and the file must agree. costViewRoles is a hand-written copy
// of the migration's VALUES list; without this check it would drift silently
// and every assertion above would keep passing while the migration granted a
// different set. It also fails if the VALUES list is replaced by a correlation
// again, which is the specific mistake this lane made once.
func TestMigration103CostGrantListMatchesTheFile(t *testing.T) {
	sql := migrationSQL(t)
	start := strings.Index(sql, "SELECT r.role, 'inventory.cost.view'")
	if start < 0 {
		t.Fatal("000103 has no inventory.cost.view grant statement")
	}
	valuesAt := strings.Index(sql[start:], "FROM (VALUES")
	if valuesAt < 0 {
		t.Fatal("000103's cost grant is not an explicit VALUES list; it is a correlation " +
			"again, which conflates who audits the books with who values the stock")
	}
	end := strings.Index(sql[start+valuesAt:], ") AS r(role)")
	if end < 0 {
		t.Fatal("000103's cost grant VALUES list is unterminated")
	}
	list := stripLineComments(sql[start+valuesAt : start+valuesAt+end])

	inFile := roleTupleRE.FindAllStringSubmatch(list, -1)
	if len(inFile) != len(costViewRoles) {
		t.Errorf("000103 grants cost.view to %d roles, costViewRoles lists %d: one is stale",
			len(inFile), len(costViewRoles))
	}
	for _, m := range inFile {
		if !containsRole(costViewRoles, m[1]) {
			t.Errorf("000103 grants inventory.cost.view to %q, which costViewRoles does not list", m[1])
		}
	}
	// The correlation must be GONE, not merely supplemented: the premise it
	// encoded — that cost visibility follows ledger visibility — is the wrong
	// one, and a lingering reference would let a later reader reinstate it.
	if strings.Contains(list, "accounting.ledger.view") {
		t.Error("000103's cost grant still references accounting.ledger.view")
	}
}

// roleTupleRE matches one ('role', ...) tuple in the cost grant's VALUES list.
var roleTupleRE = regexp.MustCompile(`\(\s*'([^']*)'\s*\)`)

func containsRole(list []string, role string) bool {
	for _, r := range list {
		if r == role {
			return true
		}
	}
	return false
}

// THE TRANSCRIPT. 000103 opens with DELETE ... RETURNING so applying it PRINTS
// what it changed. A revocation nobody can see in the apply log is a revocation
// nobody will notice silently failing to take effect, and the log is the only
// artifact a post-deploy reviewer reads.
func TestMigration103ReportsWhatItChanged(t *testing.T) {
	sql := migrationSQL(t)
	// The rows the migration deletes are surfaced by a RETURNING clause read
	// back through a CTE, and pairs it found already absent are surfaced too,
	// so "nothing was there" is distinguishable from "nothing was printed".
	if !strings.Contains(sql, "RETURNING role, permission") {
		t.Error("000103 does not RETURN the revoked rows, so applying it prints nothing")
	}
	if !strings.Contains(sql, "'REVOKED'") {
		t.Error("000103 does not label the rows it reports as REVOKED")
	}
	if !strings.Contains(sql, "UNCHANGED (absent)") {
		t.Error("000103 does not report pairs that were already absent, so a no-op is indistinguishable from silence")
	}
}

// THE TEST AND THE FILE AGREE. revokedGrants is a hand-written transcription of
// the migration's DELETE list. If someone edits the SQL and forgets this test,
// the transcription rots and every assertion above keeps passing while the
// migration does something else. So the transcription is checked against the
// file: every pair the test expects revoked must appear in the migration's
// DELETE, and every pair the migration deletes must be in the test's list.
//
// This is the check that makes the duplication above safe rather than a second
// source of truth that can quietly diverge.
func TestMigration103RevocationListMatchesTheFile(t *testing.T) {
	sql := migrationSQL(t)
	// The DELETE's own tuple list, not the whole file: the retained-grants
	// section and this test both mention keys the DELETE does not touch.
	start := strings.Index(sql, "WHERE (role, permission) IN (")
	if start < 0 {
		t.Fatal("000103 has no (role, permission) IN (...) DELETE list")
	}
	end := strings.Index(sql[start:], ")\n  RETURNING")
	if end < 0 {
		t.Fatal("000103's DELETE list has no RETURNING clause")
	}
	// Normalise the interior whitespace away. The migration aligns its columns
	// for readability, so the literal in the file is ('cashier',      'key')
	// with padding, and matching the padded form would make this test fail on a
	// reformat rather than on a real change. Whitespace inside a SQL tuple list
	// carries no meaning, so collapsing it here is not weakening the check.
	// PARSE the list rather than string-match it. The migration aligns its
	// columns, and the alignment is not uniform — a 13-character role name
	// gets no padding, so the literal in the file is ('store_manager','key')
	// with no space while a 7-character one is ('cashier',      'key') with
	// several. Any check that assumes a fixed separator therefore breaks on a
	// reformat instead of on a real change, and any check that just squashes
	// whitespace still misses the unpadded form. Extracting the two quoted
	// operands from each tuple is the only comparison that means what it says.
	inFile := deleteTupleRE.FindAllStringSubmatch(stripLineComments(sql[start:start+end]), -1)
	inFileSet := map[string]bool{}
	for _, m := range inFile {
		inFileSet[m[1]+"|"+m[2]] = true
	}
	for _, g := range revokedGrants {
		if !inFileSet[g.role+"|"+g.permission] {
			t.Errorf("revokedGrants lists (%s, %s) but 000103's DELETE does not contain that pair",
				g.role, g.permission)
		}
	}
	// The reverse direction: the file must not delete a pair the test does not
	// enumerate, or the test would not notice a widening at all.
	for _, m := range inFile {
		g := grant{role: m[1], permission: m[2]}
		if !containsGrant(revokedGrants, g) {
			t.Errorf("000103's DELETE removes (%s, %s) but revokedGrants does not list it: "+
				"the test would not notice this revocation", g.role, g.permission)
		}
	}
	if len(inFile) != len(revokedGrants) {
		t.Errorf("000103's DELETE list holds %d pairs, revokedGrants holds %d: one of the two is stale",
			len(inFile), len(revokedGrants))
	}
}

// deleteTupleRE matches one ('role', 'permission') tuple. \s* around the comma
// is what makes the migration's non-uniform column alignment irrelevant: the
// regex reads the two quoted operands, not the text between the parens.
var deleteTupleRE = regexp.MustCompile(`\(\s*'([^']*)'\s*,\s*'([^']*)'\s*\)`)

func containsGrant(list []grant, g grant) bool {
	for _, c := range list {
		if c.role == g.role && c.permission == g.permission {
			return true
		}
	}
	return false
}

// stripLineComments removes `--` comments, which is not cosmetic here: the
// migration's DELETE list is interleaved with prose that contains apostrophes
// (e.g. "000081's operator set"), and readQuoted would run past a tuple's real
// closing quote into the next line's comment and return a permission string
// spanning several comment lines. The result was a parse that silently dropped
// tuples rather than erroring, which is the worst shape for this check.
func stripLineComments(s string) string {
	var b strings.Builder
	for _, line := range strings.Split(s, "\n") {
		if i := strings.Index(line, "--"); i >= 0 {
			line = line[:i]
		}
		if b.Len() > 0 {
			b.WriteByte('\n')
		}
		b.WriteString(line)
	}
	return b.String()
}