package console

import (
	"context"
	"crypto/sha256"
	"fmt"
	"net/http"
	"slices"
	"sort"
	"strings"
	"testing"
)

func claimProbeApps(t *testing.T, cx *claimCase) []claimAppRef {
	t.Helper()
	rows, err := cx.st.AppRows()
	if err != nil {
		t.Fatal(err)
	}
	refs := make([]claimAppRef, 0, len(rows))
	for _, row := range rows {
		refs = append(refs, claimAppRef{Account: row.Sqid, App: row.App})
	}
	return refs
}

func TestClaimProbeDetectsSQLMutationOutsideDefaultApps(t *testing.T) {
	cx := newClaimCase(t)
	const path = "/claim-witness/third-app"
	p := claimProbe{setup: func(t *testing.T, cx *claimCase) {
		id := cx.enroll(t, "bab", "third")
		cx.srv.registerRoute("POST "+path, claimWitnessHandler(cx, func(r *http.Request) error {
			scopes := []claimSQLScope{{AccountID: OpsAccountID, App: "third", AppID: id, Mode: claimModeEffect}}
			return cx.st.withClaimSQL(gateCtx(t), scopes, allowAllClaimSQL, func(st *Store) error {
				_, err := st.db.Exec(`UPDATE apps SET claim_op='ungated' WHERE account_id=? AND app=?`, OpsAccountID, "third")
				return err
			})
		}))
	}, do: claimAPIReply(http.MethodPost, path, `{}`), effect: func(*testing.T, *claimCase, claimReply) string { return "" }}
	problems := runClaimProbe(t, cx, "route POST "+path, p)
	if !slices.ContainsFunc(problems, func(p string) bool { return strings.Contains(p, "SQL rows of bab/third changed") }) {
		t.Fatalf("third app mutation escaped census: %v", problems)
	}
}

// Digest every app-scoped table directly, including rows whose parent app
// or account no longer exists. Per-app diagnostics remain useful but cannot
// establish that orphan SQL rows were unchanged.
func claimProbeAllSQL(t *testing.T, cx *claimCase) map[string]string {
	t.Helper()
	out := map[string]string{}
	for name, tb := range claimSQLTables {
		if tb.NonApp != "" {
			continue
		}
		query := "SELECT * FROM " + claimQ(name) + " t"
		if tb.FreeWhenNoApp {
			query += " WHERE COALESCE(" + tb.col("t", tb.App) + ", '') != ''"
		}
		rows, err := cx.st.db.QueryContext(context.Background(), query)
		if err != nil {
			if strings.Contains(err.Error(), "no such table") {
				continue
			}
			t.Fatal(err)
		}
		cols, err := rows.Columns()
		if err != nil {
			rows.Close()
			t.Fatal(err)
		}
		var lines []string
		for rows.Next() {
			vals := make([]any, len(cols))
			ptrs := make([]any, len(cols))
			for i := range vals {
				ptrs[i] = &vals[i]
			}
			if err := rows.Scan(ptrs...); err != nil {
				rows.Close()
				t.Fatal(err)
			}
			// Typed values and quoted strings keep distinct SQL rows distinct.
			var parts []string
			for _, v := range vals {
				parts = append(parts, fmt.Sprintf("%T:%#v", v, v))
			}
			lines = append(lines, strings.Join(parts, "\x1f"))
		}
		err = rows.Err()
		rows.Close()
		if err != nil {
			t.Fatal(err)
		}
		sort.Strings(lines)
		out[name] = fmt.Sprintf("%x", sha256.Sum256([]byte(strings.Join(lines, "\x1e"))))
	}
	return out
}

func TestClaimProbeDetectsOrphanSQLMutation(t *testing.T) {
	cx := newClaimCase(t)
	const path = "/claim-witness/orphan-audit"
	p := claimProbe{setup: func(t *testing.T, cx *claimCase) {
		scopes := []claimSQLScope{{AccountID: OpsAccountID, App: "orphan", AppID: "old", Mode: claimModeEffect}}
		if err := cx.st.withClaimSQL(gateCtx(t), scopes, allowAllClaimSQL, func(st *Store) error {
			_, err := st.db.Exec(`INSERT INTO audit (app,address,action,created_at,account_id) VALUES ('orphan','test','old',1,?)`, OpsAccountID)
			return err
		}); err != nil {
			t.Fatal(err)
		}
		cx.srv.registerRoute("POST "+path, claimWitnessHandler(cx, func(r *http.Request) error {
			return cx.st.withClaimSQL(gateCtx(t), scopes, allowAllClaimSQL, func(st *Store) error { _, err := st.db.Exec(`DELETE FROM audit WHERE app='orphan'`); return err })
		}))
	}, do: claimAPIReply(http.MethodPost, path, `{}`), effect: func(*testing.T, *claimCase, claimReply) string { return "" }}
	problems := runClaimProbe(t, cx, "route POST "+path, p)
	if !slices.ContainsFunc(problems, func(s string) bool { return strings.Contains(s, "app-scoped SQL tables changed: [audit]") }) {
		t.Fatalf("orphan write escaped: %v", problems)
	}
}