package integration

// 000104's cashflow outlet scoping, proved against a real PostgreSQL.
//
// THE BACKFILL IS SQL, AND A FAKE CANNOT PROVE IT. The interesting part of
// migration 000104 is an UPDATE whose correctness is a property of the data —
// which creator was bound to one store, which to several, which to none. A unit
// test with three hardcoded rows restates the predicate; it does not show that
// the predicate is right for the shapes the backfill can actually meet. So this
// file follows the DATABASE_URL-gated scratch-SCHEMA pattern already established
// in authz_migration_test.go, and the migration is read from the EMBEDDED FS
// (migrations.FS) so the test exercises the file that will actually ship.
//
// The scratch schema holds only the four tables 000104 touches — cashflows,
// stores, store_users, users — created from the real migrations' DDL verbatim.
// That is a complete environment for this migration: 000104 reads and writes
// nothing else, so the test never touches the developer's own data or a
// partial-migration snapshot.

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

	"github.com/jackc/pgx/v5/pgtype"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/shopspring/decimal"

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

// ddlFixture is the minimum schema 000104 needs, transcribed from the
// migrations it depends on:
//
//   - cashflows  : 000001_init_schema.sql:361, plus the account_id column
//     added by the accounting-hardening migration
//   - stores     : 000030_multi_tenant_foundation.sql:5-14
//   - store_users: 000030_multi_tenant_foundation.sql:16-21, composite PK and
//     the user_id index kept, because the backfill's `= 1` guard
//     depends on the row COUNT and the PK is what makes
//     store_users.id meaningless as a key
//   - users      : id only, the target of cashflows.created_by
//
// Simplified deliberately in one respect: the cashflows CHECK constraints and
// the cashflows_contract_guard trigger are omitted because 000104 must not
// disturb them and no assertion here depends on them. The account_id column IS
// kept, because AccountMovementTotals filters on it and dropping it would mean
// testing a different query.
const ddlFixture = `
CREATE TABLE users (id bigserial PRIMARY KEY);

CREATE TABLE stores (
  id bigserial PRIMARY KEY,
  code text NOT NULL UNIQUE,
  name text NOT NULL,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- ON DELETE CASCADE on the store reference, verbatim from 000030. It matters
-- here: a store delete cascades to store_users first, and without it the delete
-- in TestMigration104StoreDeleteDowngradesNotCascades would be refused by this
-- foreign key before the cashflows rule was ever reached.
CREATE TABLE store_users (
  store_id bigint NOT NULL REFERENCES stores (id) ON DELETE CASCADE,
  user_id  bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  PRIMARY KEY (store_id, user_id)
);
CREATE INDEX idx_store_users_user ON store_users (user_id);

CREATE TABLE cashflows (
  id                 bigserial PRIMARY KEY,
  transaction_number varchar(50) NOT NULL UNIQUE,
  type               text NOT NULL,
  category           text NOT NULL,
  transaction_date   date NOT NULL,
  amount             numeric(15,2) NOT NULL DEFAULT 0,
  description        text,
  reference_type     text,
  reference_id       bigint,
  created_by         bigint REFERENCES users (id),
  reversal_of        bigint REFERENCES cashflows (id),
  account_id         bigint,
  created_at         timestamptz NOT NULL DEFAULT now()
);

-- The export query is a three-arm UNION and the other two arms read payments and
-- purchase_orders. They are declared EMPTY and minimal: the point of the export
-- test is the cashflows arm's predicate, and an empty first arm means any row
-- that appears in the result came from cashflows. Creating the tables at all is
-- only so the query resolves.
CREATE TABLE customers (id bigserial PRIMARY KEY, name text);
CREATE TABLE invoices (id bigserial PRIMARY KEY, customer_id bigint);
CREATE TABLE sales_orders (id bigserial PRIMARY KEY, customer_id bigint);
CREATE TABLE suppliers (id bigserial PRIMARY KEY, name text);
CREATE TABLE purchase_orders (
  id bigserial PRIMARY KEY,
  po_number text,
  supplier_id bigint,
  order_date date NOT NULL,
  status text,
  due_date date,
  paid_amount numeric(15,2) NOT NULL DEFAULT 0,
  remaining_amount numeric(15,2) NOT NULL DEFAULT 0
);
CREATE TABLE payments (
  id bigserial PRIMARY KEY,
  payment_number text,
  payment_date date NOT NULL,
  amount numeric(15,2) NOT NULL DEFAULT 0,
  payable_type text,
  payable_id bigint
);
`

// outletEnv is one test's scratch schema with the fixture loaded, the tenant
// graph seeded, and 000104 applied. applyCount lets the idempotency test run
// the migration twice, which proves re-run safety instead of asserting it.
type outletEnv struct {
	pool   *pgxpool.Pool
	schema string
}

// storeSpec is one row for the stores table, and optionally the users bound to
// it. The tenants are seeded by the tests rather than by a fixture constant
// because what matters is the SHAPES of the creator bindings — one store, many
// stores, none — and each test picks the ones it is about.
type tenant struct {
	users []int64 // user ids bound to this store
}

func newOutletEnv(t *testing.T, applyCount int) *outletEnv {
	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("outlet_104_%d", time.Now().UnixNano())
	// Drop first: a 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, ddlFixture); err != nil {
		t.Fatalf("create fixture: %v", err)
	}
	if applyCount > 0 {
		sql := migration104SQL(t)
		for i := 0; i < applyCount; i++ {
			if _, err := pool.Exec(ctx, sql); err != nil {
				t.Fatalf("apply 000104 (pass %d): %v", i+1, err)
			}
		}
	}
	return &outletEnv{pool: pool, schema: schema}
}

// migration104SQL reads 000104 out of the embedded migration directory, so the
// test runs the file that ships rather than a copy that could drift from it.
func migration104SQL(t *testing.T) string {
	t.Helper()
	b, err := migrations.FS.ReadFile("000104_cashflow_outlet_scope.sql")
	if err != nil {
		t.Fatalf("read embedded 000104: %v", err)
	}
	return string(b)
}

// store creates a store and returns its id.
func (e *outletEnv) store(t *testing.T, code string) int64 {
	t.Helper()
	var id int64
	if err := e.pool.QueryRow(context.Background(),
		`INSERT INTO stores (code, name) VALUES ($1, $1) RETURNING id`, code).Scan(&id); err != nil {
		t.Fatal(err)
	}
	return id
}

// user creates a user and returns its id.
func (e *outletEnv) user(t *testing.T) int64 {
	t.Helper()
	var id int64
	if err := e.pool.QueryRow(context.Background(),
		`INSERT INTO users DEFAULT VALUES RETURNING id`).Scan(&id); err != nil {
		t.Fatal(err)
	}
	return id
}

// bind attaches a user to a store. The composite primary key makes a second
// bind to the same store a no-op conflict rather than a duplicate row, which is
// what keeps the backfill's `= 1` count honest.
func (e *outletEnv) bind(t *testing.T, storeID, userID int64) {
	t.Helper()
	if _, err := e.pool.Exec(context.Background(),
		`INSERT INTO store_users (store_id, user_id) VALUES ($1, $2) ON CONFLICT DO NOTHING`,
		storeID, userID); err != nil {
		t.Fatal(err)
	}
}

// cashflow creates a cashflow row with a creator and NO outlet. It is the
// pre-migration insert: the store_id column does not exist until 000104 runs, so
// naming it here would not compile against the real schema and would be a false
// claim about when a row can carry an outlet.
func (e *outletEnv) cashflow(t *testing.T, number string, creatorID *int64) int64 {
	t.Helper()
	var id int64
	if err := e.pool.QueryRow(context.Background(),
		`INSERT INTO cashflows (transaction_number, type, category, transaction_date, amount, created_by)
		 VALUES ($1, 'expense', 'operational', '2026-08-01', 10, $2) RETURNING id`,
		number, creatorID).Scan(&id); err != nil {
		t.Fatalf("insert cashflow %s: %v", number, err)
	}
	return id
}

// setOutlet writes a row's outlet directly. This is how the "already set" case
// is produced: not at insert time, because the column does not exist before the
// migration, but afterwards by hand — which is the only way the case occurs in
// production.
func (e *outletEnv) setOutlet(t *testing.T, id int64, storeID *int64) {
	t.Helper()
	if _, err := e.pool.Exec(context.Background(),
		`UPDATE cashflows SET store_id = $2 WHERE id = $1`, id, storeID); err != nil {
		t.Fatal(err)
	}
}

// outletOf reads a row's store_id. A NULL is reported as (nil, false) so a test
// can distinguish "company-level" from "no such row", which are different
// answers and both of which a naive lookup would call "not found".
func (e *outletEnv) outletOf(t *testing.T, id int64) (*int64, bool) {
	t.Helper()
	var out *int64
	if err := e.pool.QueryRow(context.Background(),
		`SELECT store_id FROM cashflows WHERE id = $1`, id).Scan(&out); err != nil {
		return nil, false
	}
	return out, true
}

// The backfill's whole rule, as data. Three creator shapes, because those are
// the three the migration claims to handle and the one nobody thinks about —
// the multi-store creator — is the one that would look like a bug in the join.
func TestMigration104Backfill(t *testing.T) {
	e := newOutletEnv(t, 0) // fixture only; the test applies the migration itself

	store1 := e.store(t, "S1")
	store2 := e.store(t, "S2")

	till := e.user(t)
	e.bind(t, store1, till)

	unbound := e.user(t) // no store_users row at all

	multi := e.user(t)
	e.bind(t, store1, multi)
	e.bind(t, store2, multi)

	fromTill := e.cashflow(t, "CF-TILL", &till)
	fromUnbound := e.cashflow(t, "CF-UNBOUND", &unbound)
	fromMulti := e.cashflow(t, "CF-MULTI", &multi)
	noCreator := e.cashflow(t, "CF-NOCREATOR", nil)

	if _, err := e.pool.Exec(context.Background(), migration104SQL(t)); err != nil {
		t.Fatalf("apply 000104: %v", err)
	}

	// The "already set" case is a RE-APPLY scenario, not a first-apply one: the
	// column does not exist until the migration runs, so a row cannot arrive
	// with an outlet beforehand. It is asserted in
	// TestMigration104ReApplyKeepsAnOperatorCorrection, where the sequence is
	// the real one — apply, correct by hand, re-apply.

	// A single-store creator: the till's rows belong to the till's outlet. This
	// is the case the whole ERP screen depends on.
	if got, ok := e.outletOf(t, fromTill); !ok || got == nil || *got != store1 {
		t.Errorf("a till's row got outlet %v (exists=%v), want %d", got, ok, store1)
	}
	// A creator with no store binding: company-level, which means NULL and is
	// invisible to a restricted caller. Deliberate, not an accident of the join.
	if got, ok := e.outletOf(t, fromUnbound); !ok || got != nil {
		t.Errorf("an unbound creator's row got outlet %v (exists=%v), want NULL (company-level)", got, ok)
	}
	// A MULTI-store creator: NULL, because min(store_id) would attribute a
	// company's cash to whichever outlet sorted first. Guessing here is worse
	// than admitting we do not know.
	if got, ok := e.outletOf(t, fromMulti); !ok || got != nil {
		t.Errorf("a multi-store creator's row got outlet %v (exists=%v), want NULL — guessing an outlet "+
			"would attribute company cash to the lowest-sorted store", got, ok)
	}
	// No creator at all: same reading, same reason.
	if got, ok := e.outletOf(t, noCreator); !ok || got != nil {
		t.Errorf("a creatorless row got outlet %v (exists=%v), want NULL", got, ok)
	}
}

// THE FIVE READ PATHS, PROVED AGAINST REAL SQL.
//
// The service-level tests in internal/finance and internal/reports use a fake
// store, and a fake store proves that a scope was COMPUTED and HANDED OVER. It
// cannot prove the SQL honours it — and the SQL is where the two behaviours this
// design turns on actually live:
//
//  1. a NULL store_ids array matches every row, INCLUDING company-level ones
//     (platform-wide must still see the books);
//  2. a NON-NULL array, including an EMPTY one, never matches a NULL store_id
//     (a restricted caller must not see company cash).
//
// Both are properties of PostgreSQL's `IS NULL` and `= ANY()` semantics, not of
// any Go code, so a fake asserting them would be asserting its own
// reimplementation. These run the real queries from internal/db against the
// scratch schema.
func TestCashflowReadPathsAreScopedInSQL(t *testing.T) {
	e := newOutletEnv(t, 1)
	store1 := e.store(t, "S1")
	store2 := e.store(t, "S2")

	ctx := context.Background()
	// One row per outlet plus a company-level row, the three shapes the
	// backfill can produce. Inserted directly rather than through the backfill so
	// the assertion is about the READ path and not about the backfill (which
	// TestMigration104Backfill covers).
	// account_id 0 so AccountMovementTotals' account predicate (which the
	// handler drives with a literal 0 meaning "unspecified") matches, leaving
	// the STORE predicate as the only thing under test on that path.
	if _, err := e.pool.Exec(ctx, `
		INSERT INTO cashflows (transaction_number, type, category, transaction_date, amount, account_id, store_id) VALUES
		  ('CF-S1',  'income',  'sales',      '2026-08-01', 100, 0, $1),
		  ('CF-S2',  'expense', 'purchase',   '2026-08-01', 200, 0, $2),
		  ('CF-CO',  'income',  'operational','2026-08-01', 400, 0, NULL)`,
		store1, store2); err != nil {
		t.Fatal(err)
	}

	q := db.New(e.pool)

	// READ PATH 1 — GET /cashflows (ListCashflows), plus the count and the
	// summary aggregate. The count and the summary are asserted alongside the
	// rows on purpose: a scoped list with an unscoped count still tells a till
	// operator the size of the company, and an unscoped summary hands over the
	// company's totals while showing no rows at all.
	t.Run("list, count and summary follow the caller's scope", func(t *testing.T) {
		rows, err := q.ListCashflows(ctx, db.ListCashflowsParams{
			Limit: 100, StoreIds: []int64{store1},
		})
		if err != nil {
			t.Fatal(err)
		}
		if len(rows) != 1 || rows[0].TransactionNumber != "CF-S1" {
			t.Errorf("outlet 1 list = %v, want only CF-S1", names(rows))
		}
		if rows[0].StoreID.Int64 != store1 || !rows[0].StoreID.Valid {
			t.Errorf("CF-S1 store_id = %v, want %d", rows[0].StoreID, store1)
		}

		n, err := q.CountCashflows(ctx, db.CountCashflowsParams{StoreIds: []int64{store1}})
		if err != nil {
			t.Fatal(err)
		}
		if n != 1 {
			t.Errorf("outlet 1 count = %d, want 1 — an unscoped count reports the company's row count", n)
		}

		sum, err := q.SummarizeCashflows(ctx, db.SummarizeCashflowsParams{StoreIds: []int64{store1}})
		if err != nil {
			t.Fatal(err)
		}
		// 100 of outlet 1's own, and NOT the 400 of the company-level row. This
		// is the aggregate leak: no individual company row is shown, yet its
		// amount is in the total.
		if !sum.Income.Equal(decimal.NewFromInt(100)) {
			t.Errorf("outlet 1 income = %s, want 100 — an unscoped SUM is the company's movement "+
				"even when no company row is displayed", sum.Income)
		}
		if !sum.Expense.Equal(decimal.Zero) {
			t.Errorf("outlet 1 expense = %s, want 0", sum.Expense)
		}
	})

	// READ PATH 2 — GET /cashflows/{id}. The one that decides whether the list
	// means anything: a scoped list with an unscoped id fetch is a list with a
	// hole in it, because ids are small sequential integers.
	t.Run("get by id is scoped", func(t *testing.T) {
		var ownID, otherID, companyID int64
		if err := e.pool.QueryRow(ctx,
			`SELECT id FROM cashflows WHERE transaction_number='CF-S1'`).Scan(&ownID); err != nil {
			t.Fatal(err)
		}
		if err := e.pool.QueryRow(ctx,
			`SELECT id FROM cashflows WHERE transaction_number='CF-S2'`).Scan(&otherID); err != nil {
			t.Fatal(err)
		}
		if err := e.pool.QueryRow(ctx,
			`SELECT id FROM cashflows WHERE transaction_number='CF-CO'`).Scan(&companyID); err != nil {
			t.Fatal(err)
		}

		if _, err := q.GetCashflowByID(ctx, db.GetCashflowByIDParams{
			ID: ownID, StoreIds: []int64{store1},
		}); err != nil {
			t.Errorf("their own cashflow = %v, want readable", err)
		}
		// pgx returns ErrNoRows, which the repository maps to ErrNotFound — the
		// predicate makes the row INVISIBLE, not forbidden, so a caller cannot
		// even learn that the id exists.
		for name, id := range map[string]int64{"another outlet": otherID, "company level": companyID} {
			if _, err := q.GetCashflowByID(ctx, db.GetCashflowByIDParams{
				ID: id, StoreIds: []int64{store1},
			}); err == nil {
				t.Errorf("GetCashflowByID(%s) succeeded, want no rows — a till operator can walk ids", name)
			}
		}
		// The lock, which is the same predicate and is what the mutation paths use.
		tx, err := e.pool.Begin(ctx)
		if err != nil {
			t.Fatal(err)
		}
		defer tx.Rollback(ctx)
		if _, err := db.New(tx).GetCashflowByIDForUpdate(ctx, db.GetCashflowByIDForUpdateParams{
			ID: otherID, StoreIds: []int64{store1},
		}); err == nil {
			t.Error("GetCashflowByIDForUpdate locked another outlet's row, so an update could overwrite it")
		}
	})

	// READ PATH 3 — GET /cashflow-balances. Four SUMS and no rows at all, which
	// is why scoping only the list would look like it had worked.
	t.Run("balances aggregate follows the caller's scope", func(t *testing.T) {
		// AccountID 0, not NULL: the handler passes 0 for "no account filter" and
		// the query's first predicate is `(@account_id::bigint IS NULL OR
		// account_id = @account_id)`. A 0 therefore matches only rows with
		// account_id = 0, which is NOT the same as no filter — so the fixture
		// rows carry account_id 0 and this test is about the STORE predicate, not
		// about the account one.
		totals, err := q.AccountMovementTotals(ctx, db.AccountMovementTotalsParams{
			DateFrom:  pgtype.Date{Time: time.Date(2026, 1, 1, 0, 0, 0, 0, time.UTC), Valid: true},
			AccountID: 0,
			StoreIds:  []int64{store1},
		})
		if err != nil {
			t.Fatal(err)
		}
		// opening is 0 because the date range starts in January; the point is
		// that period_in is 100 and NOT 500.
		if !totals.PeriodIn.Equal(decimal.NewFromInt(100)) {
			t.Errorf("outlet 1 period_in = %s, want 100 — the company's cash must not be summed in", totals.PeriodIn)
		}
		if !totals.PeriodOut.Equal(decimal.Zero) {
			t.Errorf("outlet 1 period_out = %s, want 0", totals.PeriodOut)
		}
	})

	// READ PATH 5 — GET /reports/.../cashflow. The CSV, which leaves as a FILE.
	t.Run("export follows the caller's scope", func(t *testing.T) {
		rows, err := q.CashflowCSVRows(ctx, db.CashflowCSVRowsParams{StoreIds: []int64{store1}})
		if err != nil {
			t.Fatal(err)
		}
		for _, r := range rows {
			if r.TransactionNumber == "CF-CO" {
				t.Error("the export included the company-level row for a restricted caller")
			}
		}
	})

	// THE NIL-VERSUS-EMPTY DISCRIMINATOR, in SQL. This is the property the whole
	// fail-closed design rests on and the one most likely to be a silent
	// coincidence of Go behaviour rather than a real guarantee, so it is
	// asserted here rather than inferred from the Go tests.
	t.Run("nil array is platform-wide, empty array matches nothing", func(t *testing.T) {
		// nil -> NULL -> matches everything, company-level rows INCLUDED. The
		// roles that reconcile the company books must keep that.
		all, err := q.ListCashflows(ctx, db.ListCashflowsParams{Limit: 100, StoreIds: nil})
		if err != nil {
			t.Fatal(err)
		}
		if len(all) != 3 {
			t.Errorf("platform-wide list = %v, want all 3 rows including the company-level one", names(all))
		}
		foundCompany := false
		for _, r := range all {
			if r.TransactionNumber == "CF-CO" {
				foundCompany = true
			}
		}
		if !foundCompany {
			t.Error("platform-wide caller cannot see company-level rows: finance and admin would lose " +
				"the ability to reconcile the books, which is the half of this change that must not regress")
		}

		// empty (non-nil) -> '{}' -> `= ANY('{}')` is false for every row, and
		// a NULL store_id can never satisfy it. A restricted scope resolving to
		// zero stores therefore sees NOTHING, which is fail-closed by a different
		// route than the refusal for an absent scope, and the same outcome.
		none, err := q.ListCashflows(ctx, db.ListCashflowsParams{Limit: 100, StoreIds: []int64{}})
		if err != nil {
			t.Fatal(err)
		}
		if len(none) != 0 {
			t.Errorf("empty-scope list = %v, want nothing", names(none))
		}
		// And the same on the id path, since that is the one that would otherwise
		// make the list meaningless.
		var companyID int64
		if err := e.pool.QueryRow(ctx,
			`SELECT id FROM cashflows WHERE transaction_number='CF-CO'`).Scan(&companyID); err != nil {
			t.Fatal(err)
		}
		if _, err := q.GetCashflowByID(ctx, db.GetCashflowByIDParams{
			ID: companyID, StoreIds: []int64{},
		}); err == nil {
			t.Error("an empty scope read a company-level row by id")
		}
	})
}

// names renders rows for a failure message, so a report says WHICH row leaked.
func names(rows []db.ListCashflowsRow) []string {
	out := make([]string, 0, len(rows))
	for _, r := range rows {
		out = append(out, r.TransactionNumber)
	}
	return out
}

// The indexes the scoped list depends on. Checked because an index that does not
// exist is a performance regression on every cashflow page and nothing else in
// the suite would notice.
func TestMigration104IndexesExist(t *testing.T) {
	e := newOutletEnv(t, 1)
	for _, want := range []struct {
		name, definition string
	}{
		// Leading store_id then id DESC: the scoped list's predicate AND its
		// ORDER BY id DESC, so the index serves the sort and not just the filter.
		{"cashflows_store_id_id_idx", "store_id, id DESC"},
		// Partial on company-level rows only, because a platform-wide caller
		// scans them and they are expected to be the small minority.
		{"cashflows_company_level_idx", "WHERE (store_id IS NULL)"},
	} {
		var def string
		if err := e.pool.QueryRow(context.Background(),
			`SELECT indexdef FROM pg_indexes WHERE schemaname = $1 AND indexname = $2`,
			e.schema, want.name).Scan(&def); err != nil {
			t.Errorf("index %s is missing: %v", want.name, err)
			continue
		}
		if !containsAll(def, want.definition) {
			t.Errorf("index %s = %q, want it to contain %q", want.name, def, want.definition)
		}
	}
}

// ON DELETE SET NULL, not CASCADE: deleting a store must not delete the money
// records. A cashflow that outlives its outlet becomes company-level — a
// downgrade in visibility and a loss of nothing, since the cash was real.
func TestMigration104StoreDeleteDowngradesNotCascades(t *testing.T) {
	e := newOutletEnv(t, 1)
	store1 := e.store(t, "S1")
	creator := e.user(t)
	e.bind(t, store1, creator)
	id := e.cashflow(t, "CF-DEL", &creator)

	if _, err := e.pool.Exec(context.Background(), `DELETE FROM stores WHERE id = $1`, store1); err != nil {
		t.Fatalf("delete store: %v", err)
	}
	got, ok := e.outletOf(t, id)
	if !ok {
		t.Fatal("the cashflow row was DELETED with its store; ON DELETE CASCADE where it must be SET NULL")
	}
	if got != nil {
		t.Errorf("outlet = %v after the store was deleted, want NULL (company-level)", *got)
	}
}

// The column is nullable and defaults to NULL, and NULL is a MEANINGFUL value:
// it marks a company-level row. A NOT NULL column would force the backfill to
// invent an outlet for every row it cannot attribute, which is the one outcome
// the migration refuses to produce.
func TestMigration104ColumnIsNullable(t *testing.T) {
	e := newOutletEnv(t, 1)
	var nullable string
	if err := e.pool.QueryRow(context.Background(),
		`SELECT is_nullable FROM information_schema.columns
		 WHERE table_schema = $1 AND table_name = 'cashflows' AND column_name = 'store_id'`,
		e.schema).Scan(&nullable); err != nil {
		t.Fatalf("read store_id nullability: %v", err)
	}
	if nullable != "YES" {
		t.Errorf("cashflows.store_id is_nullable = %s, want YES (NULL is company-level, a real value)", nullable)
	}
	var fk string
	if err := e.pool.QueryRow(context.Background(),
		`SELECT delete_rule FROM information_schema.referential_constraints rc
		 JOIN information_schema.table_constraints tc USING (constraint_name, constraint_schema)
		 WHERE tc.table_schema = $1 AND tc.constraint_name LIKE 'cashflows%store_id%'`,
		e.schema).Scan(&fk); err != nil {
		t.Fatalf("read the store_id foreign key: %v", err)
	}
	if fk != "SET NULL" {
		t.Errorf("store_id FK delete rule = %s, want SET NULL", fk)
	}
}

// The "already set" case, in the only sequence where it can exist: the column
// appears when the migration runs, so a row cannot carry an outlet before the
// first apply. The real scenario is apply → an operator corrects a row by hand
// → re-apply, and the correction must survive.
//
// This is also the ONLY way to test re-run safety against a broken backfill. A
// plain "apply twice" test passes for a migration that does nothing on the
// second pass, including one whose UPDATE is missing the `store_id IS NULL`
// guard and would therefore clobber a correction.
func TestMigration104ReApplyKeepsAnOperatorCorrection(t *testing.T) {
	e := newOutletEnv(t, 0) // fixture only: the row must exist BEFORE the first
	store1 := e.store(t, "S1")
	store2 := e.store(t, "S2")
	creator := e.user(t)
	e.bind(t, store1, creator)
	id := e.cashflow(t, "CF-CORRECTED", &creator)
	// apply, because a row can only be corrected once the column exists.
	if _, err := e.pool.Exec(context.Background(), migration104SQL(t)); err != nil {
		t.Fatalf("first apply 000104: %v", err)
	}

	// The backfill attributed it correctly on the first apply...
	if got, ok := e.outletOf(t, id); !ok || got == nil || *got != store1 {
		t.Fatalf("after the first apply outlet = %v (exists=%v), want %d", got, ok, store1)
	}
	// ...and an operator then says no, it was booked against the other outlet.
	e.setOutlet(t, id, &store2)

	if _, err := e.pool.Exec(context.Background(), migration104SQL(t)); err != nil {
		t.Fatalf("re-apply 000104: %v", err)
	}

	if got, ok := e.outletOf(t, id); !ok || got == nil || *got != store2 {
		t.Errorf("a hand-corrected outlet was overwritten to %v (exists=%v), want %d — the UPDATE's "+
			"store_id IS NULL guard is what makes a re-apply safe", got, ok, store2)
	}
}

func containsAll(haystack, needle string) bool {
	return len(needle) == 0 || (len(haystack) >= len(needle) && indexOf(haystack, needle) >= 0)
}

func indexOf(haystack, needle string) int {
	for i := 0; i+len(needle) <= len(haystack); i++ {
		if haystack[i:i+len(needle)] == needle {
			return i
		}
	}
	return -1
}