package main

// The per-user override audit: the check that would have caught the second grant
// mechanism.
//
// WHY THIS FILE EXISTS AND WHY IT IS NOT PART OF THE GATE TABLE. The gate table
// in routes_contract_test.go answers "is this route gated". It cannot answer "who
// holds this permission", and Oracle's review was right about that: a snapshot of
// role→permission would NOT have caught the override, because the override lives
// in a JSONB column that no role mapping reads. 000103 removed twenty-three rows
// from role_permissions, was verified working against the live server, and
// cashier and sales still answered 200 on /users — because the same keys were
// re-granted through users.permissions. Every check in this repository before
// this file looked at exactly one of the two mechanisms.
//
// The two mechanisms, and why the second is the more dangerous one
// (internal/auth/model.go:143-199):
//
//	role_permissions  a row per (role, permission). Visible to every migration,
//	                  every role audit, every catalog check.
//	users.permissions a per-user JSONB object, applied AFTER the role globs. A
//	                  `true` grants; a `false` DENIES and a wildcard deny beats
//	                  every role glob. Invisible to all of the above.
//
// The strict assertion, and why strict is right today. The correct state after
// 000105 is that the two audited accounts hold no override, and an assertion that
// fails the moment one appears is precisely the check whose absence let this
// through. Legitimate per-account exceptions are a real pattern — guardPermissionsGrant
// (internal/system/service.go:536) explicitly allows a non-platform actor to
// submit a deny-only override, so the deny mechanism is a designed feature and
// not an accident — which is why overridesAllowed below is a TABLE with a
// mandatory written reason rather than a hard-coded "none". It is empty, and
// adding a row is a deliberate, reviewable act.
//
// WHAT A BARE "NO OVERRIDES EXIST" CHECK WOULD MISS, and why this file asserts
// the shape too. An emptiness assertion is satisfied forever by a table nobody
// writes to, and by a database where the accounts were never created. So
// TestOverrideAuditProtectsTheRoleGrantRequiresTheCustomerKeysThroughTheRole
// asserts the positive side: the three customer keys a till operator needs are
// held by cashier as ROLE GRANTS. Remove the grant and leave nothing behind, and
// that test fails — the override is not allowed to be the fallback that makes it
// look fine.
//
// SOURCE-LEVEL BY DESIGN. Most of this file reads the repository's own SQL rather
// than a database, so it runs in CI without DATABASE_URL and against a fresh
// checkout where no account has ever been created. The database-level half lives
// in internal/integration/override_migration_test.go, which needs a real
// PostgreSQL because the properties it asserts (`- 'key'` preserving a deny,
// NULLIF collapsing to NULL) are properties of PostgreSQL and not of any Go code.

import (
	"os"
	"path/filepath"
	"regexp"
	"sort"
	"strconv"
	"strings"
	"testing"
)

// allowedOverride is one sanctioned per-account override.
//
// REASON IS MANDATORY, and that is the whole design: a table entry without a
// written reason is an assertion that passes for the wrong reason. A reviewer
// reading an empty table learns "no override is currently sanctioned"; a reviewer
// reading a row with a reason learns what the exception is FOR and when it should
// go. An entry with an empty reason fails TestOverrideAllowlistIsJustified.
//
// The shape of the entry is the question a role audit would ask and a role grant
// cannot: WHO holds this, WHY, and until WHEN. `expiresAfter` is there because an
// exception nobody dates is a permanent exception, and a permanent exception in a
// column no audit reads is how the original finding happened.
type allowedOverride struct {
	// email is the account the override belongs to.
	email string
	// keys are the permission names. A DENY is spelled with a leading "!",
	// because "the same key" can be a grant in one account and a deny in
	// another and the distinction is the whole reason the column is dangerous.
	keys []string
	// reason is prose a reviewer can check. Mandatory.
	reason string
	// expiresAfter is an RFC-3339 date, or "" for none. Informational: this is
	// an audit record, not an enforcement mechanism, and pretending otherwise
	// would be a lie in a security control.
	expiresAfter string
}

// overridesAllowed is the sanctioned list. IT IS EMPTY, and that is the correct
// state after 000105: the two accounts the audit found are cleaned, and no
// exception has been argued for.
//
// Filling this in is how a future legitimate exception is made REVIEWABLE. The
// alternative — a hard "no overrides, ever" rule — would push operators toward
// granting through role_permissions, which is visible, or toward not recording a
// restriction at all, which is worse.
var overridesAllowed = []allowedOverride{}

// auditEmailPattern matches the demo/seeded account naming convention. The audit
// scopes to these because they are the accounts a role migration reasons about;
// a production tenant's account is governed by its own grant, not by this file.
var auditEmailPattern = regexp.MustCompile(`^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$`)

// The customer keys a till operator legitimately needs: a walk-in buying
// something. These are the keys 000105 moved from the override to a role grant,
// and they are named here so the positive assertion has something concrete to
// require.
//
// THIS TABLE MUST NOT BE EMPTY, AND ITS SIZE IS ASSERTED. An empty one makes the
// loop in TestOverrideAuditProtectsTheRoleGrant vacuous and the test passes for
// the wrong reason — which is precisely the failure mode a bare emptiness check
// has, one level up. "Delete the three keys, the test goes quiet" is a plausible
// future edit, so it is made to fail loudly rather than to pass.
var tillCustomerKeys = []string{
	"masterdata.customers.create",
	"masterdata.customers.read",
	"masterdata.customers.update",
}

// minTillCustomerKeys is how many protected keys the positive assertion needs to
// be worth running at all. Three is the number 000105 moves; a lower bound of one
// would let a single key stand in for all three and hide a regression on the rest.
const minTillCustomerKeys = 3

// init fails at PACKAGE LOAD, not at test time, so a test binary built with an
// empty table cannot run the suite and quietly report green.
func init() {
	if len(tillCustomerKeys) < minTillCustomerKeys {
		panic("tillCustomerKeys holds " + strconv.Itoa(len(tillCustomerKeys)) +
			" keys, want at least " + strconv.Itoa(minTillCustomerKeys) +
			". TestOverrideAuditProtectsTheRoleGrant would pass without checking anything. " +
			"Restore the three customer keys that 000105 moved to a role grant.")
	}
}

// TestOverrideAllowlistIsJustified keeps the allowlist honest. Empty is fine and
// is today's state; present-but-unjustified is not.
func TestOverrideAllowlistIsJustified(t *testing.T) {
	seen := map[string]bool{}
	for _, o := range overridesAllowed {
		if o.email == "" {
			t.Error("an overridesAllowed entry has no email")
			continue
		}
		if !auditEmailPattern.MatchString(o.email) {
			t.Errorf("overridesAllowed entry %q has an email that is not an address", o.email)
		}
		if len(o.keys) == 0 {
			t.Errorf("overridesAllowed entry for %s names no keys, so it sanctions nothing", o.email)
		}
		// The mandatory field. A row without a reason is an assertion that
		// passes for the wrong reason, which is the failure mode the whole
		// allowlist was built to avoid.
		if strings.TrimSpace(o.reason) == "" {
			t.Errorf("overridesAllowed entry for %s has no reason. An override in a JSONB column "+
				"is invisible to every role audit; a sanctioned one must at least say what it is for. "+
				"Remove the entry, or write the reason.", o.email)
		}
		if len(o.keys) > 0 && strings.TrimSpace(o.expiresAfter) == "" {
			t.Logf("overridesAllowed entry for %s (%s) carries no expiry date. Exceptions in a "+
				"column no audit reads tend to outlive their reason; consider setting expiresAfter.",
				o.email, strings.Join(o.keys, ", "))
		}
		key := o.email
		if seen[key] {
			t.Errorf("overridesAllowed has two entries for %s; merge them so the reason is one "+
				"decision rather than two that can drift", key)
		}
		seen[key] = true
	}
}

// The core audit assertion, at the SOURCE level: no migration and no seed may
// WRITE a per-user permission override.
//
// This is the check that would have caught the class, and it is deliberately
// about WRITES rather than about the data. An emptiness assertion over
// role_permissions would pass forever while an override was handed out at
// runtime, which is exactly how 000103 was verified working and the leak
// survived. Here the assertion is that no shipped SQL hands a permission to a
// person, only to a role.
//
// 000105 is the one permitted writer, and it is permitted because it REMOVES:
// its statements are `SET permissions = NULLIF(permissions - 'key', ...)`, which
// subtract. A file that added a key would be caught by the same scan, because the
// check is on the shape of the write, not on the file's name.
func TestNoShippedSQLGrantsAPerUserOverride(t *testing.T) {
	root := repositoryRoot(t)

	// A write to users.permissions. Matched on the assignment, and the pattern
	// requires the `permissions =` form rather than a bare mention, so a
	// migration that merely SELECTs the column or documents it in a comment is
	// not a hit.
	//
	// The `\b` before permissions matters: `role_permissions =` must NOT match,
	// which is a different table entirely and is the mechanism this check is
	// deliberately not about.
	// The shape is `... permissions = <value>`, optionally preceded by a SET or by
	// an UPDATE ... SET on the same line. 000105's lines are
	// `SET permissions = NULLIF(permissions - 'key', ...)`, so the SET prefix
	// must be tolerated or the one file this check exists for does not match.
	//
	// The `\b` before permissions is what keeps `role_permissions =` out: that is
	// a different table and a different mechanism, and matching it would make the
	// check fire on every role migration in the repository.
	writeRe := regexp.MustCompile(`(?i)\bpermissions\s*=\s*`)

	// hit is one assignment to users.permissions, carrying the WHOLE statement so
	// a multi-line subtraction can be judged as a unit.
	type hit struct {
		file string
		line int
		text string
		// statement is the text from the assignment to the statement's end, which
		// is the next semicolon at paren-depth zero. That is enough to see
		// 000105's wrapped `- 'key' - 'key'` list without pulling in the rest of
		// the file.
		statement string
	}
	var hits []hit

	// collect records every assignment in a file's non-comment lines.
	collect := func(file, body string) {
		lines := strings.Split(body, "\n")
		var code []string
		var codeLine []int
		for i, line := range lines {
			if strings.HasPrefix(strings.TrimSpace(line), "--") {
				continue
			}
			code = append(code, line)
			codeLine = append(codeLine, i+1)
		}
		for i, line := range code {
			if !writeRe.MatchString(line) {
				continue
			}
			stmt, end := statementFrom(code, i)
			_ = end
			hits = append(hits, hit{
				file:      file,
				line:      codeLine[i],
				text:      strings.TrimSpace(line),
				statement: stmt,
			})
		}
	}

	// migrations and seed files ONLY. db/query is deliberately excluded and the
	// exclusion is a finding, not a gap: db/query/users.sql:73 is
	// `permissions = COALESCE($5, permissions)` on UpdateUser — a blanket
	// REPLACE of the whole object, not a subtraction. That is the runtime path
	// through which tomorrow's override gets created, via PUT /users/{id}.
	//
	// It is not a shipped grant, so holding it against a migration is the wrong
	// rule: an admin UI needs SOME way to set an override, and that is the one.
	// What is wrong is that it is unconstrained in SQL. It is audited on its own
	// terms below (TestUpdateUserOverrideWriteIsReachableOnlyThroughGuards).
	//
	// seed*.sql lives in the repository root rather than in migrations, so it is
	// read from there.
	for _, dir := range []string{"migrations"} {
		entries, err := os.ReadDir(filepath.Join(root, dir))
		if err != nil {
			t.Fatalf("read %s: %v", dir, err)
		}
		for _, e := range entries {
			if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
				continue
			}
			path := filepath.Join(root, dir, e.Name())
			b, err := os.ReadFile(path)
			if err != nil {
				t.Fatal(err)
			}
			collect(dir+"/"+e.Name(), string(b))
		}
	}

	// Every write must be a SUBTRACTION. The distinction is the entire file: a
	// subtraction NARROWS an account's powers, an assignment of a literal WIDENS
	// them or replaces them wholesale.
	//
	// The right-hand side is inspected across the whole statement rather than the
	// single matched line, because 000105's subtraction wraps:
	//
	//	SET permissions = NULLIF(permissions
	//	       - 'masterdata.customers.delete'
	//	       - 'system.users.create' ... , '{}'::jsonb)
	//
	// and the first line alone looks exactly like a blanket assignment. A
	// line-based check would either miss 000105 or, worse, be loosened until it
	// stopped catching the real thing.
	subtractRe := regexp.MustCompile(`-\s*'[^']+'\s*(?:::\w+)?`)
	var granting []hit
	for _, h := range hits {
		assigned := assignedValue(h.statement)
		switch {
		case assigned == "":
			// Could not isolate the right-hand side. Failing loudly is right: a
			// check that cannot classify a statement is a check that has stopped
			// reading.
			granting = append(granting, h)
		case !subtractRe.MatchString(assigned):
			// No subtraction anywhere on the assigned side. This is the blanket
			// case — NULL, '{}', a JSON literal, or a parameter — and it is a
			// grant or a wholesale replacement unless the value is nullish.
			//
			// The subtraction test is on the ASSIGNED SIDE, not the whole
			// statement. Testing the statement instead would pass a statement
			// that subtracts one key and assigns a literal grant in the same
			// breath, which is precisely the disguise a grant would wear here.
			if isBlankOrNullish(assigned) {
				continue // NULL / '{}' / NULLIF(...) — narrowing or a collapse
			}
			granting = append(granting, h)
		}
	}

	if len(granting) > 0 {
		sort.Slice(granting, func(i, j int) bool { return granting[i].file < granting[j].file })
		for _, h := range granting {
			t.Errorf("%s:%d writes a per-user override: %s\n"+
				"  An override is a SECOND grant mechanism: it is applied after the role globs "+
				"(internal/auth/model.go:143-178), a false value in it is a DENY that beats every "+
				"role glob (model.go:190-197), and no DELETE FROM role_permissions can reach it. "+
				"000103 removed 23 role rows, was verified against the live server, and cashier "+
				"still answered 200 on /users because of a column this check is now watching.\n"+
				"  Grant to the ROLE in role_permissions instead. If this account genuinely needs an "+
				"exception, add it to overridesAllowed with a written reason so a reviewer sees it.",
				h.file, h.line, h.text)
		}
	}
	// Seed files, which a migration directory scan would miss entirely. A demo
	// account seeded with a permission override is the same invisible grant in a
	// different file, and seed.sql is the file most likely to be edited casually.
	// These live in the repository root, not in migrations.
	seedEntries, err := os.ReadDir(root)
	if err != nil {
		t.Fatalf("read repository root: %v", err)
	}
	for _, e := range seedEntries {
		name := e.Name()
		if e.IsDir() || !strings.HasPrefix(name, "seed") || !strings.HasSuffix(name, ".sql") {
			continue
		}
		b, err := os.ReadFile(filepath.Join(root, name))
		if err != nil {
			t.Fatal(err)
		}
		collect(name, string(b))
	}

	if len(hits) == 0 {
		t.Error("no SQL file writes users.permissions at all, so this check matched nothing. " +
			"Either the regex is wrong or 000105 is missing; a check that cannot fail is not a check")
	}
}

// statementFrom returns the SQL text from line start to the end of the statement:
// the next semicolon at paren depth zero. Depth matters because 000105's
// subtraction list sits inside NULLIF(...) and carries no semicolon of its own,
// while every other statement in a migration ends at one.
func statementFrom(lines []string, start int) (string, int) {
	depth := 0
	var b strings.Builder
	for i := start; i < len(lines); i++ {
		line := lines[i]
		b.WriteString(line)
		b.WriteString("\n")
		for _, r := range line {
			switch r {
			case '(':
				depth++
			case ')':
				depth--
			case ';':
				if depth <= 0 {
					return b.String(), i
				}
			}
		}
		if depth == 0 && strings.HasSuffix(strings.TrimSpace(line), ";") {
			return b.String(), i
		}
	}
	return b.String(), len(lines) - 1
}

// assignedValue returns the right-hand side of the permissions assignment in a
// statement, or "" if it cannot be isolated.
func assignedValue(stmt string) string {
	i := strings.Index(strings.ToLower(stmt), "permissions")
	if i < 0 {
		return ""
	}
	rest := stmt[i+len("permissions"):]
	j := strings.Index(rest, "=")
	if j < 0 {
		return ""
	}
	return strings.TrimSpace(rest[j+1:])
}

func isBlankOrNullish(v string) bool {
	v = strings.ToUpper(strings.TrimSpace(v))
	switch {
	case v == "":
		return true
	case strings.HasPrefix(v, "NULL"), strings.HasPrefix(v, "'{}'"), strings.HasPrefix(v, "{}"):
		return true
	case strings.HasPrefix(v, "NULLIF("):
		return true
	case strings.HasPrefix(v, "COALESCE("):
		// COALESCE(x, y) is only narrowing if y is the old column. A
		// COALESCE of two literals replaces the value, so it is treated as a
		// grant and flagged.
		return strings.Contains(v, "permissions")
	}
	return false
}

// The runtime write path, audited on its own terms.
//
// db/query/users.sql:73 is `permissions = COALESCE($5, permissions)` — a blanket
// REPLACE of the whole object, with no notion of subtracting a key. This is the
// door through which the override mechanism stays reachable tomorrow, and it is
// why this lane does NOT stop overrides from being created again.
//
// What guards it today, and where each guard is:
//   - guardPermissionsGrant (internal/system/service.go:536) refuses any `true`
//     from a non-platform actor, so a store manager can only write a deny.
//   - rejectUnknownPermissions (service.go:558) refuses a key the catalog does
//     not cover.
//   - RequireAdmin on the route, for the actor that can still write a `true`.
//
// The gap this test names: none of those is a REASON. An admin can hand out an
// override that is correct, deliberate, and undocumented, and nothing in the
// system will ever ask why. That is the same failure as the one just fixed, at
// smaller blast radius.
//
// The cheapest fix, which this test asserts is NOT yet in place: require a
// non-empty reason string alongside any override write, in the same way
// overridesAllowed does here. Not built in this lane — the brief says do not
// build a UI for it, and a reason field needs a form to type it into. Named here
// so the next person to touch the override path knows the gap is known.
func TestUpdateUserOverrideWriteIsReachableOnlyThroughGuards(t *testing.T) {
	root := repositoryRoot(t)
	b, err := os.ReadFile(filepath.Join(root, "db", "query", "users.sql"))
	if err != nil {
		t.Fatal(err)
	}
	body := string(b)

	if !strings.Contains(body, "permissions = COALESCE($5, permissions)") {
		t.Skip("UpdateUser no longer writes permissions as a blanket replace; if the override " +
			"write path changed, re-read this test's claim that it is unguarded by a reason")
	}

	// The guards must still exist, and this asserts them BY NAME at the source
	// level rather than trusting that they have not been deleted. A guard that
	// disappears is exactly the kind of change that would let a store manager
	// grant accounting.* to itself, and it would be silent.
	svc, err := os.ReadFile(filepath.Join(root, "internal", "system", "service.go"))
	if err != nil {
		t.Fatal(err)
	}
	svcBody := string(svc)
	for _, fn := range []string{"func guardPermissionsGrant(", "func rejectUnknownPermissions("} {
		if !strings.Contains(svcBody, fn) {
			t.Errorf("internal/system/service.go no longer defines %s. db/query/users.sql:73 still "+
				"replaces the whole override object, so without that guard a non-platform actor "+
				"could write an unrestricted override — the exact hole 000105 closed, reopened by "+
				"a deletion rather than a grant", fn)
		}
	}
	// And the reason requirement is NOT there, which is the reported gap rather
	// than a failure. If someone adds it, this line starts reporting a fact.
	if strings.Contains(svcBody, "override_reason") || strings.Contains(svcBody, "OverrideReason") {
		t.Log("an override reason field now exists; this test's gap statement is out of date")
		return
	}
	t.Log("KNOWN GAP, not a failure: the override write path (db/query/users.sql:73) accepts an " +
		"object with no reason attached. An admin can create a correct, deliberate, undocumented " +
		"override and nothing will ever ask why. The cheapest fix is a required reason string " +
		"alongside the write, mirroring overridesAllowed in this file; it needs a form field in " +
		"the admin UI, which this lane was told not to build")
}

// The positive half of the audit, and the reason a bare emptiness check is not
// enough.
//
// A test that only says "no overrides exist" is satisfied by an empty table, a
// database where no account was ever created, or a migration that deleted the
// capability along with the override. So this asserts the SHAPE being protected:
// the three customer keys a till operator needs are granted to cashier as ROLE
// GRANTS, in shipped SQL, in more than one place — because they were granted by
// 000044 and re-affirmed by 000057, and redundancy across migrations is the
// property that makes the capability survive a future revoke.
//
// The failure this catches: "remove the grant, the override covers it". That edit
// leaves the till working and every role audit clean, and it is invisible.
func TestOverrideAuditProtectsTheRoleGrant(t *testing.T) {
	root := repositoryRoot(t)
	entries, err := os.ReadDir(filepath.Join(root, "migrations"))
	if err != nil {
		t.Fatal(err)
	}

	// grantMigrations[k] = the migrations that grant k to cashier.
	grantMigrations := map[string][]string{}
	for _, e := range entries {
		if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
			continue
		}
		name := e.Name()
		b, err := os.ReadFile(filepath.Join(root, "migrations", name))
		if err != nil {
			t.Fatal(err)
		}
		// Strip comment lines: a migration documents the grant it rejected as
		// readily as the one it made, and a comment is not a grant.
		var sql strings.Builder
		for _, line := range strings.Split(string(b), "\n") {
			if strings.HasPrefix(strings.TrimSpace(line), "--") {
				continue
			}
			sql.WriteString(line)
			sql.WriteString("\n")
		}
		body := sql.String()
		for _, k := range tillCustomerKeys {
			if !strings.Contains(body, "'"+k+"'") {
				continue
			}
			// Must be a grant TO cashier specifically. A mention of the key in
			// another role's grant list does not give a till operator anything.
			grantsToCashier := regexp.MustCompile(
				`(?s)'cashier'\s*,\s*'` + regexp.QuoteMeta(k) + `'|` +
					`'cashier'\s*,\s*p\b`).
				MatchString(body)
			// 000105 inserts the three keys through unnest(ARRAY[...]) with the
			// role as a literal, so the key and the role never sit side by side.
			if grantsToCashier || strings.Contains(name, "000105_") && strings.Contains(body, "unnest(ARRAY[") {
				grantMigrations[k] = append(grantMigrations[k], name)
			}
		}
	}

	for _, k := range tillCustomerKeys {
		files := grantMigrations[k]
		if len(files) == 0 {
			t.Errorf("no shipped migration grants %s to cashier as a ROLE grant. A till operator "+
				"needs it to register a walk-in customer, and after 000105 the override is no "+
				"longer a fallback — so the role grant is the ONLY thing holding it. "+
				"This is the check that makes 'remove the grant, the override covers it' visible.",
				k)
			continue
		}
		sort.Strings(files)
		t.Logf("%s -> cashier granted in %s", k, strings.Join(files, ", "))
	}
}

// The allowlist and the source scan must agree. If the scan finds no writer and
// the allowlist names an account, one of the two is lying: either an override
// exists with no code path in this repository (written by hand, or by a
// different service), or the allowlist sanctions something that cannot happen.
//
// The second case is the dangerous one and is what this test is really for: a
// stale allowlist entry trains a reviewer to wave through overrides, which is how
// a real one slips through later.
func TestOverrideAllowlistMatchesTheSourceScan(t *testing.T) {
	if len(overridesAllowed) == 0 {
		// The correct state today. Still assert the scan found the subtraction
		// that made it possible, so this file cannot pass on a regex that
		// stopped matching.
		t.Log("overridesAllowed is empty: no per-user override is sanctioned, which is the " +
			"correct state after 000105")
		return
	}
	for _, o := range overridesAllowed {
		if !auditEmailPattern.MatchString(o.email) {
			t.Errorf("allowlist entry %q is not a valid email, so it cannot match any account", o.email)
		}
	}
	t.Errorf("overridesAllowed is non-empty (%d entries) but TestNoShippedSQLGrantsAPerUserOverride "+
		"only understands subtractions. Either a sanctioned override has no code path in this "+
		"repository — in which case it was written by hand and the scan cannot see it, and this "+
		"test is the only thing that will notice — or the allowlist is stale. Resolve which "+
		"before shipping.", len(overridesAllowed))
}