//go:build integration

// Package integration runs the data layer against a REAL Postgres 16 + pgvector. It proves at
// runtime the properties the adversarial review could only check statically: tenant isolation,
// default deny, the composite FK cross tenant injection block, numeric(7,4) round trip, audit
// immutability, the EEO privilege boundary, and the hardened pgvector/RRF search path.
//
// Run: go test -tags=integration ./internal/integration/ -v
// Env: MM_APP_DSN, MM_MIGRATOR_DSN (required), MM_COMPLIANCE_DSN (optional).
package integration

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

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/pgvector/pgvector-go"
	"github.com/shopspring/decimal"

	"github.com/missionworkforce/missionmatch/internal/modules/commandcenter"
	"github.com/missionworkforce/missionmatch/internal/modules/matching"
	"github.com/missionworkforce/missionmatch/internal/platform/database"
	"github.com/missionworkforce/missionmatch/internal/platform/database/dbgen"
	"github.com/missionworkforce/missionmatch/internal/platform/embeddings"
	"github.com/missionworkforce/missionmatch/internal/platform/tenant"
)

var (
	appDB      *tenant.DB
	migrator   *pgxpool.Pool
	compliance *pgxpool.Pool
)

func TestMain(m *testing.M) {
	ctx := context.Background()
	appDSN, migDSN, compDSN := os.Getenv("MM_APP_DSN"), os.Getenv("MM_MIGRATOR_DSN"), os.Getenv("MM_COMPLIANCE_DSN")
	if appDSN == "" || migDSN == "" {
		fmt.Println("integration: MM_APP_DSN / MM_MIGRATOR_DSN not set, skipping integration tests")
		os.Exit(0)
	}
	pool, err := database.NewPool(ctx, appDSN)
	if err != nil {
		panic("app pool: " + err.Error())
	}
	appDB = tenant.New(pool)
	if migrator, err = database.NewPool(ctx, migDSN); err != nil {
		panic("migrator pool: " + err.Error())
	}
	if compDSN != "" {
		if compliance, err = database.NewPool(ctx, compDSN); err != nil {
			panic("compliance pool: " + err.Error())
		}
	}
	os.Exit(m.Run())
}

// ---------- helpers (seed via the BYPASSRLS migrator so we can write across tenants) ----------

func seedOrg(t *testing.T) uuid.UUID {
	t.Helper()
	id := uuid.New()
	slug := "org-" + id.String()[:12]
	if _, err := migrator.Exec(context.Background(),
		"INSERT INTO organizations (id, name, slug) VALUES ($1,$2,$3)", id, slug, slug); err != nil {
		t.Fatalf("seed org: %v", err)
	}
	return id
}

func seedCandidate(t *testing.T, org uuid.UUID, level, status string, available bool) dbgen.Candidate {
	t.Helper()
	c, err := dbgen.New(migrator).CreateCandidate(context.Background(), dbgen.CreateCandidateParams{
		OrganizationID: org, FullName: "Cand " + level, ClearanceLevel: level, ClearancePoly: "none",
		ClearanceStatus: status, Available: available,
	})
	if err != nil {
		t.Fatalf("seed candidate: %v", err)
	}
	return c
}

func ctxOrg(org uuid.UUID) context.Context { return tenant.WithOrg(context.Background(), org.String()) }

func pgCode(err error) string {
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) {
		return pgErr.Code
	}
	return ""
}

// ---------- tests ----------

func TestCrossTenantReadIsolation(t *testing.T) {
	orgA, orgB := seedOrg(t), seedOrg(t)
	candA := seedCandidate(t, orgA, "secret", "active", true)
	candB := seedCandidate(t, orgB, "secret", "active", true)

	var listed []dbgen.Candidate
	if err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
		var e error
		listed, e = dbgen.New(tx).ListCandidates(ctx, dbgen.ListCandidatesParams{Limit: 1000})
		return e
	}); err != nil {
		t.Fatalf("list: %v", err)
	}
	sawA, sawB := false, false
	for _, c := range listed {
		sawA = sawA || c.ID == candA.ID
		sawB = sawB || c.ID == candB.ID
	}
	if !sawA {
		t.Fatal("org A must see its own candidate")
	}
	if sawB {
		t.Fatal("ISOLATION BREACH: org A saw org B's candidate")
	}

	// Direct read of B's candidate under org A's session must return zero rows (RLS hides it).
	err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
		_, e := dbgen.New(tx).GetCandidate(ctx, candB.ID)
		return e
	})
	if !errors.Is(err, pgx.ErrNoRows) {
		t.Fatalf("cross tenant GetCandidate should be ErrNoRows, got %v", err)
	}
}

func TestUnsetGUCDefaultDeny(t *testing.T) {
	orgA := seedOrg(t)
	seedCandidate(t, orgA, "secret", "active", true)

	// App role, NO tenant transaction => GUC unset => current_setting is NULL => policy denies => 0 rows.
	var n int
	if err := appDB.Pool.QueryRow(context.Background(), "SELECT count(*) FROM candidates").Scan(&n); err != nil {
		t.Fatalf("count: %v", err)
	}
	if n != 0 {
		t.Fatalf("default deny breached: unscoped app query saw %d rows (want 0)", n)
	}
}

func TestCompositeFKBlocksCrossTenantInjection(t *testing.T) {
	orgA, orgB := seedOrg(t), seedOrg(t)
	candB := seedCandidate(t, orgB, "secret", "active", true) // victim parent in org B
	skill, err := dbgen.New(migrator).UpsertSkill(context.Background(), dbgen.UpsertSkillParams{
		Name: "FKProbe-" + uuid.NewString(),
	})
	if err != nil {
		t.Fatalf("seed skill: %v", err)
	}

	// As org A, try to attach a skill row (org=A) to org B's candidate. RLS WITH CHECK passes (row
	// org == session org), but the COMPOSITE FK (organization_id, candidate_id) -> candidates has no
	// matching (A, candB) parent, so the write MUST fail with a foreign key violation (23503).
	err = appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
		return dbgen.New(tx).AddCandidateSkill(ctx, dbgen.AddCandidateSkillParams{
			OrganizationID: orgA, CandidateID: candB.ID, SkillID: skill.ID,
		})
	})
	if code := pgCode(err); code != "23503" {
		t.Fatalf("cross tenant injection NOT blocked: want FK violation 23503, got err=%v code=%q", err, code)
	}
}

func TestPerfectScorePersistsViaService(t *testing.T) {
	orgA := seedOrg(t)
	// Requirement with nothing required => every component scores 100 => total exactly 100.
	req, err := dbgen.New(migrator).CreateRequirement(context.Background(), dbgen.CreateRequirementParams{
		OrganizationID: orgA, Title: "Open Role", RequiredLevel: "none", RequiredPoly: "none",
	})
	if err != nil {
		t.Fatalf("seed requirement: %v", err)
	}
	cand := seedCandidate(t, orgA, "secret", "active", true)

	svc := matching.NewService(appDB, nil, nil) // exact-only matcher
	out, err := svc.GenerateSlate(ctxOrg(orgA), matching.GenerateSlateInput{RequirementID: req.ID, Name: "Perfect Slate"})
	if err != nil {
		t.Fatalf("GenerateSlate (numeric overflow would surface here): %v", err)
	}
	if len(out.Members) != 1 || out.Members[0].CandidateID != cand.ID.String() {
		t.Fatalf("unexpected members: %+v", out.Members)
	}
	if out.Members[0].Total != "100.00" || out.Members[0].Band != "green" {
		t.Fatalf("perfect candidate should be 100.00/green, got %s/%s", out.Members[0].Total, out.Members[0].Band)
	}

	// Read back the frozen decision record, proves numeric(7,4) stored 100.0000 (the SQLC-1 fix).
	var members []dbgen.SlateMember
	if err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
		var e error
		members, e = dbgen.New(tx).ListSlateMembers(ctx, out.SlateID)
		return e
	}); err != nil {
		t.Fatalf("read back: %v", err)
	}
	if len(members) != 1 || !members[0].TotalScore.Equal(decimal.NewFromInt(100)) {
		t.Fatalf("persisted total should be 100, got %+v", members)
	}
}

func TestAuditAppendOnly(t *testing.T) {
	orgA := seedOrg(t)
	if err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
		_, e := dbgen.New(tx).InsertAuditLog(ctx, dbgen.InsertAuditLogParams{OrganizationID: &orgA, Action: "test.event"})
		return e
	}); err != nil {
		t.Fatalf("insert audit: %v", err)
	}

	// App role has no UPDATE/DELETE grant on audit_logs => permission denied (42501).
	if _, err := appDB.Pool.Exec(context.Background(), "UPDATE audit_logs SET action='tamper' WHERE organization_id=$1", orgA); pgCode(err) != "42501" {
		t.Fatalf("app UPDATE on audit_logs should be permission-denied (42501), got %v", err)
	}
	// Even the owner (migrator) is stopped by the append-only trigger (raise_exception P0001).
	_, err := migrator.Exec(context.Background(), "UPDATE audit_logs SET action='tamper' WHERE organization_id=$1", orgA)
	if err == nil {
		t.Fatal("append-only trigger failed: owner UPDATE on audit_logs succeeded")
	}
	if pgCode(err) != "P0001" {
		t.Fatalf("owner UPDATE should raise the append-only trigger (P0001), got %v", err)
	}
}

func TestEEOPrivilegeBoundary(t *testing.T) {
	// The matching app role must have ZERO access to the eeo schema (permission denied 42501).
	_, err := appDB.Pool.Exec(context.Background(), "SELECT 1 FROM eeo.candidate_demographics LIMIT 1")
	if pgCode(err) != "42501" {
		t.Fatalf("app role must be denied EEO access (42501), got %v", err)
	}
	if compliance != nil {
		// The compliance role CAN reach it (no permission error; RLS may legitimately return 0 rows).
		if _, err := compliance.Exec(context.Background(), "SELECT 1 FROM eeo.candidate_demographics LIMIT 1"); err != nil {
			t.Fatalf("compliance role should access EEO, got %v", err)
		}
	}
}

func TestHybridSearchPathExecutes(t *testing.T) {
	orgA := seedOrg(t)
	emb := embeddings.NewDeterministic(384)
	names := []string{"Cloud Security Engineer", "Kubernetes Platform Lead", "Network Defense Analyst"}
	for _, name := range names {
		vec, _ := emb.Embed(context.Background(), name)
		pv := pgvector.NewVector(vec)
		hn := name
		if _, err := dbgen.New(migrator).CreateCandidate(context.Background(), dbgen.CreateCandidateParams{
			OrganizationID: orgA, FullName: name, Headline: &hn, ClearanceLevel: "secret",
			ClearancePoly: "none", ClearanceStatus: "active", Available: true, ResumeEmbedding: &pv,
		}); err != nil {
			t.Fatalf("seed search candidate: %v", err)
		}
	}

	qv := pgvector.NewVector(mustEmbed(emb, "cloud security"))
	var rows []dbgen.SearchCandidatesHybridRow
	err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
		if _, e := tx.Exec(ctx, "SET LOCAL hnsw.ef_search = 200"); e != nil { // PGV-3
			return e
		}
		var e error
		rows, e = dbgen.New(tx).SearchCandidatesHybrid(ctx, dbgen.SearchCandidatesHybridParams{
			Column1: qv, Limit: 10, WebsearchToTsquery: "cloud security",
		})
		return e
	})
	if err != nil {
		t.Fatalf("hybrid search executed against live pgvector failed: %v", err)
	}
	if len(rows) == 0 {
		t.Fatal("hybrid search returned no rows (expected the seeded candidates)")
	}
	for _, r := range rows {
		if r.OrganizationID != orgA {
			t.Fatalf("hybrid search leaked a foreign-tenant row: %s", r.OrganizationID)
		}
	}
}

// TestHybridSearchMissionScopeExecutes proves the WS6 mission scoped query (SearchCandidatesHybridMission)
// against live pgvector + full text: when a mission keyword is supplied, only candidates whose full_name,
// headline, or location text matches that mission are returned, and the RLS boundary still holds so a foreign tenant's SIGINT
// candidate never surfaces. The mission filter is text match only (the documented WS6 Option A limit).
func TestHybridSearchMissionScopeExecutes(t *testing.T) {
	orgA, orgB := seedOrg(t), seedOrg(t)
	emb := embeddings.NewDeterministic(384)

	seed := func(org uuid.UUID, name, headline string) {
		vec, _ := emb.Embed(context.Background(), name+" "+headline)
		pv := pgvector.NewVector(vec)
		hn := headline
		if _, err := dbgen.New(migrator).CreateCandidate(context.Background(), dbgen.CreateCandidateParams{
			OrganizationID: org, FullName: name, Headline: &hn, ClearanceLevel: "top_secret",
			ClearancePoly: "none", ClearanceStatus: "active", Available: true, ResumeEmbedding: &pv,
		}); err != nil {
			t.Fatalf("seed mission candidate %q: %v", name, err)
		}
	}
	// org A: two SIGINT candidates (text contains SIGINT) and two off mission candidates.
	seed(orgA, "Alice Analyst", "SIGINT collection and analysis")
	seed(orgA, "Bob Signals", "SIGINT systems engineer")
	seed(orgA, "Carol Cloud", "Kubernetes and AWS platform")
	seed(orgA, "Dave Defender", "network defense operations")
	// org B: a SIGINT candidate that must NOT leak into org A's scoped retrieval.
	seed(orgB, "Eve Foreign", "SIGINT foreign tenant")

	qv := pgvector.NewVector(mustEmbed(emb, "intelligence analyst"))
	var rows []dbgen.SearchCandidatesHybridMissionRow
	err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
		if _, e := tx.Exec(ctx, "SET LOCAL hnsw.ef_search = 200"); e != nil {
			return e
		}
		var e error
		rows, e = dbgen.New(tx).SearchCandidatesHybridMission(ctx, dbgen.SearchCandidatesHybridMissionParams{
			QueryVector: qv, RowLimit: 10, TextQuery: "intelligence analyst", MissionQuery: "SIGINT",
		})
		return e
	})
	if err != nil {
		t.Fatalf("mission scoped hybrid search failed: %v", err)
	}
	if len(rows) != 2 {
		names := make([]string, len(rows))
		for i, r := range rows {
			names[i] = r.FullName
		}
		t.Fatalf("mission scope returned %d rows %v, want exactly the 2 SIGINT candidates", len(rows), names)
	}
	for _, r := range rows {
		if r.OrganizationID != orgA {
			t.Fatalf("mission scope leaked a foreign-tenant row: %s (%s)", r.FullName, r.OrganizationID)
		}
		if r.FullName != "Alice Analyst" && r.FullName != "Bob Signals" {
			t.Fatalf("mission scope returned an off-mission candidate: %s", r.FullName)
		}
	}
}

func mustEmbed(e embeddings.Embedder, s string) []float32 {
	v, _ := e.Embed(context.Background(), s)
	return v
}

func ptrStr(s string) *string { return &s }

// TestCommandCenterRLSIsolation proves the Workforce Command Center never mixes tenants: org A's
// dashboard counts only org A's workforce/opportunities, even though org B has its own seeded data on
// the same cluster. This is the design's #1 risk (RLS boundary escape on an analytics endpoint).
func TestCommandCenterRLSIsolation(t *testing.T) {
	ctx := context.Background()
	orgA, orgB := seedOrg(t), seedOrg(t)

	// org A: exactly 3 cleared, available candidates; NO opportunities.
	for i := 0; i < 3; i++ {
		seedCandidate(t, orgA, "secret", "active", true)
	}
	// org B: 5 cleared candidates + a uniquely-named opportunity with one requirement.
	for i := 0; i < 5; i++ {
		seedCandidate(t, orgB, "top_secret", "active", true)
	}
	decoyOpp := uuid.New()
	if _, err := migrator.Exec(ctx,
		`INSERT INTO opportunities (id, organization_id, name, status, value) VALUES ($1,$2,'DECOY-ONLY-OPP','open',1000000)`,
		decoyOpp, orgB); err != nil {
		t.Fatalf("seed decoy opp: %v", err)
	}
	if _, err := migrator.Exec(ctx,
		`INSERT INTO requirements (organization_id, opportunity_id, title, labor_category, quantity, required_level, required_poly)
		 VALUES ($1,$2,'Decoy Engineer','Decoy Engineer',2,'secret','none')`,
		orgB, decoyOpp); err != nil {
		t.Fatalf("seed decoy req: %v", err)
	}

	svc := commandcenter.NewService(appDB)
	now := time.Now()

	va, err := svc.CommandCenter(ctxOrg(orgA), now)
	if err != nil {
		t.Fatalf("command center org A: %v", err)
	}
	if va.Banner.TotalClearedWorkforce != 3 {
		t.Fatalf("org A cleared workforce = %d, want 3 (leak of org B's 5?)", va.Banner.TotalClearedWorkforce)
	}
	if va.Banner.ActiveRequirements != 0 || len(va.ActiveOpportunities) != 0 {
		t.Fatalf("org A saw foreign opportunities/requirements: reqs=%d opps=%d", va.Banner.ActiveRequirements, len(va.ActiveOpportunities))
	}
	for _, o := range va.ActiveOpportunities {
		if o.Name == "DECOY-ONLY-OPP" {
			t.Fatal("ISOLATION BREACH: org A saw org B's DECOY-ONLY-OPP")
		}
	}

	vb, err := svc.CommandCenter(ctxOrg(orgB), now)
	if err != nil {
		t.Fatalf("command center org B: %v", err)
	}
	if vb.Banner.TotalClearedWorkforce != 5 {
		t.Fatalf("org B cleared workforce = %d, want 5", vb.Banner.TotalClearedWorkforce)
	}
	if vb.Banner.ActiveRequirements != 1 || len(vb.ActiveOpportunities) != 1 || vb.ActiveOpportunities[0].Name != "DECOY-ONLY-OPP" {
		t.Fatalf("org B should see exactly its own opportunity, got reqs=%d opps=%d", vb.Banner.ActiveRequirements, len(vb.ActiveOpportunities))
	}
}

func TestScreenIntelligenceAndOpportunityFit(t *testing.T) {
	ctx := context.Background()
	orgA := seedOrg(t)

	req, err := dbgen.New(migrator).CreateRequirement(ctx, dbgen.CreateRequirementParams{
		OrganizationID: orgA, Title: "Cyber Engineer", CustomerAgency: ptrStr("DISA"),
		RequiredLevel: "secret", RequiredPoly: "none", RequiredYears: 5, KeyPersonnel: true,
	})
	if err != nil {
		t.Fatalf("seed requirement: %v", err)
	}

	cand := seedCandidate(t, orgA, "ts_sci", "active", true)
	if _, err := migrator.Exec(ctx,
		"UPDATE candidates SET clearance_poly='full_scope', years_experience=10, clearance_granted_at='2023-01-15', clearance_expires_at='2029-01-15' WHERE id=$1",
		cand.ID); err != nil {
		t.Fatalf("enrich candidate: %v", err)
	}
	if err := dbgen.New(migrator).AddCandidateCustomerExperience(ctx, dbgen.AddCandidateCustomerExperienceParams{
		OrganizationID: orgA, CandidateID: cand.ID, Agency: "DISA",
	}); err != nil {
		t.Fatalf("add customer experience: %v", err)
	}

	svc := matching.NewService(appDB, nil, nil)

	view, err := svc.Screen(ctxOrg(orgA), cand.ID, req.ID)
	if err != nil {
		t.Fatalf("Screen: %v", err)
	}
	if view.MissionReadiness.Status != "Mission Ready" || view.MissionReadiness.Score != "100.00" {
		t.Fatalf("mission readiness = %+v", view.MissionReadiness)
	}
	if len(view.MissionReadiness.Categories) != 6 {
		t.Fatalf("expected 6 readiness categories, got %d", len(view.MissionReadiness.Categories))
	}
	if !view.ClearanceIntel.SCIEligible || !view.ClearanceIntel.FullScopePoly || !view.ClearanceIntel.RecentClassifiedSupport {
		t.Fatalf("clearance intel = %+v", view.ClearanceIntel)
	}
	if view.ClearanceIntel.InvestigationAgeMonths == nil || *view.ClearanceIntel.InvestigationAgeMonths < 30 {
		t.Fatalf("expected an investigation age ~41mo, got %v", view.ClearanceIntel.InvestigationAgeMonths)
	}
	if view.WinImpact.LevelCode != "high" {
		t.Fatalf("win impact = %+v", view.WinImpact)
	}
	keyPersonnel := false
	for _, rsn := range view.WinImpact.Reasons {
		if rsn == "Matches Key Personnel requirements" {
			keyPersonnel = true
		}
	}
	if !keyPersonnel {
		t.Fatalf("expected Key Personnel reason, got %v", view.WinImpact.Reasons)
	}

	fits, err := svc.OpportunitiesForCandidate(ctxOrg(orgA), cand.ID, 10)
	if err != nil {
		t.Fatalf("OpportunitiesForCandidate: %v", err)
	}
	if len(fits) == 0 || fits[0].RequirementID != req.ID || fits[0].Status != "Mission Ready" {
		t.Fatalf("opportunity fit = %+v", fits)
	}
}