//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)
		}
	}
}

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)
	}
}