//go:build integration

package integration

import (
	"context"
	"encoding/json"
	"testing"
	"time"

	"github.com/google/uuid"
	"github.com/shopspring/decimal"

	"github.com/missionworkforce/missionmatch/internal/modules/ai"
	"github.com/missionworkforce/missionmatch/internal/platform/database/dbgen"
)

// seedAwardWithPopStart inserts one usaspending_awards row carrying a pop_start_date, the fiscal period anchor
// the G7 spend trend keys on (usaspending_awards has no award_date column). It is the migrator (the global
// award catalog the app role may only read) and keys only on the columns the award table actually carries.
func seedAwardWithPopStart(t *testing.T, genID, agency, naics, recipientUEI, recipientName, amount string, popStart time.Time) {
	t.Helper()
	if _, err := migrator.Exec(context.Background(),
		`INSERT INTO usaspending_awards
		   (generated_internal_id, award_id, recipient_uei, recipient_name, awarding_agency, naics_code,
		    award_amount, pop_start_date, pop_end_date, is_galapagos, raw)
		 VALUES ($1, $1, $2, $3, $4, $5, $6::numeric, $7, $7, false, '{}'::jsonb)
		 ON CONFLICT (generated_internal_id) DO NOTHING`,
		genID, recipientUEI, recipientName, agency, naics, amount, popStart); err != nil {
		t.Fatalf("seed award with pop start %s: %v", genID, err)
	}
}

// seedBuyerOpportunity inserts one opportunity_records slice carrying the full (naics, psc, set_aside,
// contract_vehicle, estimated_value) tuple the G7 detail intent reads, as the migrator. A nil estVal leaves the
// value band null (the priced count and the band exclude it), and an empty psc or set_aside leaves that slice
// column the schema default, so a slice with a missing piece still groups cleanly (the G9 NULL lesson the
// detail intent must survive on a live staging run).
func seedBuyerOpportunity(t *testing.T, srcID, agency, naics, psc, setAside, vehicle string, estVal *decimal.Decimal) {
	t.Helper()
	if err := dbgen.New(migrator).UpsertOpportunityRecord(context.Background(), dbgen.UpsertOpportunityRecordParams{
		SourceName: "SAM.gov", SourceRecordID: srcID, RecordKind: "active_solicitation",
		Title: "Buyer slice " + srcID, Agency: agency, Naics: naics, Psc: psc, SetAside: setAside,
		ContractVehicle: vehicle, EstimatedValue: estVal, ValueSource: "operator",
		FetchedAt: time.Now().UTC(),
	}); err != nil {
		t.Fatalf("seed buyer opportunity %s: %v", srcID, err)
	}
}

// TestG7AgencyBuyingPatternDetail is the live proof of the buyer detail against real Postgres. It seeds one
// agency with several opportunity_records slices (a priced one, a second priced one, and one with a NULL psc, a
// NULL set_aside, and a NULL estimated_value to exercise the G9 NULL discipline) plus matching award dollar
// totals, then proves the detail returns a distribution with the contributing record count as evidence, a
// decimal value band (the average and the median) on the priced slices, the award obligation totals keyed on
// (agency, naics), the NULL slice scanning cleanly (the empty string slice and a nil band), and byte stable
// ordering on a re run.
func TestG7AgencyBuyingPatternDetail(t *testing.T) {
	org := seedOrg(t)
	run := uuid.NewString()[:8]
	agency := "DEPT OF THE AIR FORCE " + run // unique per run so the buyer is isolated
	naics := "541512"

	// Two priced slices in the segment (a $4m and a $6m award value), so the median is a meaningful decimal.
	v4 := decimal.RequireFromString("4000000.00")
	v6 := decimal.RequireFromString("6000000.00")
	seedBuyerOpportunity(t, "G7-BP-A-"+run, agency, naics, "R425", "8A", "OASIS", &v4)
	seedBuyerOpportunity(t, "G7-BP-B-"+run, agency, naics, "R425", "8A", "OASIS", &v6)
	// The NULL slice: no psc, no set_aside, no estimated_value. It must still scan cleanly (the staging NULL
	// lesson): the slice columns COALESCE to the empty string and the value band is a nil decimal pointer.
	seedBuyerOpportunity(t, "G7-BP-NULL-"+run, agency, naics, "", "", "", nil)

	// Matching award dollar totals keyed on (agency, naics): one 9m award.
	recent := time.Now().UTC().AddDate(0, -3, 0)
	seedMarketAward(t, "G7-BP-AWD-"+run, "PIID-A", agency, naics, "UEIBUYERPRIME", "Buyer Prime LLC", "9000000.00", recent, false)

	// Double count regression: a sam_opportunities row whose hierarchy IS this agency makes agency_match non
	// empty, so the award's agency matches BOTH the direct predicate and the hierarchy EXISTS. The rollup must
	// count the 9m award ONCE, not twice. Without the disjoint OR EXISTS fix this returned 18m.
	if _, err := migrator.Exec(context.Background(),
		`INSERT INTO sam_opportunities (notice_id, department, full_parent_path, sub_agency, naics_code, raw)
		 VALUES ($1, $2, $2, '', $3, '{}'::jsonb)`,
		"G7-SAM-"+run, agency, naics); err != nil {
		t.Fatalf("seed sam_opportunities for the double count regression: %v", err)
	}

	rows, err := ai.AgencyBuyingPatternDetail(ctxOrg(org), appDB, agency, naics)
	if err != nil {
		t.Fatalf("agency buying pattern detail: %v", err)
	}
	if len(rows) == 0 {
		t.Fatal("the detail must return the buyer's slices")
	}

	var sawPriced, sawNull bool
	for _, r := range rows {
		if r.RecordCount < 1 {
			t.Errorf("every slice must carry the contributing record count as evidence, got %d", r.RecordCount)
		}
		// The award dollar total is keyed on (agency, naics), so every slice in this NAICS carries it as a decimal.
		if r.AwardDollars.LessThan(decimal.Zero) {
			t.Errorf("the award obligation total must be a non negative decimal, got %s", r.AwardDollars)
		}
		switch {
		case r.PSC == "R425" && r.SetAside == "8A" && r.ContractVehicle == "OASIS":
			sawPriced = true
			if r.PricedCount != 2 {
				t.Errorf("the priced slice must count both priced records, got %d", r.PricedCount)
			}
			// The value band: the average of 4m and 6m is 5m, and the median is 5m, both exact decimals.
			if r.AvgValue == nil || !r.AvgValue.Equal(decimal.RequireFromString("5000000.00")) {
				t.Errorf("the value band average must be the decimal 5000000.00, got %v", r.AvgValue)
			}
			if r.MedianValue == nil || !r.MedianValue.Equal(decimal.RequireFromString("5000000")) {
				t.Errorf("the value band median must be the decimal 5000000, got %v", r.MedianValue)
			}
		case r.PSC == "" && r.SetAside == "" && r.ContractVehicle == "":
			sawNull = true
			// The NULL slice scans cleanly: the slice columns are the empty string and the band is a nil pointer
			// (no priced record contributed), never a failed scan and never a fabricated zero band.
			if r.PricedCount != 0 {
				t.Errorf("the NULL slice has no priced record, got priced count %d", r.PricedCount)
			}
			if r.AvgValue != nil || r.MedianValue != nil {
				t.Errorf("the NULL slice must carry a nil value band, got avg %v median %v", r.AvgValue, r.MedianValue)
			}
		}
	}
	// The award obligation total must be the SINGLE 9m award, not double counted, even though the agency now
	// matches both the direct predicate and the SAM hierarchy EXISTS (the rollup overlap fix).
	expectAward := decimal.RequireFromString("9000000.00")
	if !rows[0].AwardDollars.Equal(expectAward) {
		t.Errorf("award_dollars must be the single counted 9000000.00, got %s (the double count regression)", rows[0].AwardDollars)
	}
	if !sawPriced {
		t.Error("the detail must include the priced R425 8A OASIS slice with a decimal value band")
	}
	if !sawNull {
		t.Error("the detail must include the NULL slice and scan it cleanly (the staging NULL lesson)")
	}

	// Byte stable on a re run (the determinism mandate); the ORDER BY ends with the full grouped slice key.
	again, err := ai.AgencyBuyingPatternDetail(ctxOrg(org), appDB, agency, naics)
	if err != nil {
		t.Fatalf("agency buying pattern detail re run: %v", err)
	}
	first, _ := json.Marshal(rows)
	second, _ := json.Marshal(again)
	if string(first) != string(second) {
		t.Errorf("the buying pattern detail must be byte stable on a re run.\nfirst:  %s\nsecond: %s", first, second)
	}
}

// TestG7AgencyVendorConcentration proves the vendor concentration returns the recency weighted recipient dollar
// share for one (agency, naics) award segment, ordered share descending with a UEI tiebreaker, the wired versus
// open read, with the tenant's own past performance labeled is_galapagos, the contributing award count as
// evidence, the shares exact decimals summing to 1, and byte stable on a re run. The segment keys ONLY on
// (agency, naics).
func TestG7AgencyVendorConcentration(t *testing.T) {
	org := seedOrg(t)
	run := uuid.NewString()[:8]
	agency := "DEPT OF DEFENSE " + run
	naics := "541330"
	recent := time.Now().UTC().AddDate(0, -3, 0) // inside the recency window

	// A dominant incumbent, a challenger, and the tenant's own award, all in the same (agency, naics).
	seedMarketAward(t, "G7-VC-"+run+"-1", "PIID-1", agency, naics, "UEIDOMINANT01", "Dominant Defense LLC", "8000000.00", recent, false)
	seedMarketAward(t, "G7-VC-"+run+"-2", "PIID-2", agency, naics, "UEICHALLENG02", "Challenger Two LLC", "2000000.00", recent, false)
	seedMarketAward(t, "G7-VC-"+run+"-3", "PIID-3", agency, naics, "UEIGALAPAGOS3", "Galapagos Federal", "1000000.00", recent, true)

	rows, err := ai.AgencyVendorConcentration(ctxOrg(org), appDB, agency, naics)
	if err != nil {
		t.Fatalf("agency vendor concentration: %v", err)
	}
	if len(rows) != 3 {
		t.Fatalf("the concentration must return the three distinct recipients in the segment, got %d", len(rows))
	}
	if rows[0].RecipientUEI != "UEIDOMINANT01" {
		t.Errorf("the dominant recipient must lead the concentration read, got %q", rows[0].RecipientUEI)
	}

	prev := decimal.NewFromInt(2) // larger than any valid share
	var total decimal.Decimal
	var sawGalapagos bool
	for _, r := range rows {
		if r.DollarShare.GreaterThan(prev) {
			t.Errorf("the concentration must be ordered share descending; %s share %s exceeds the previous %s", r.RecipientUEI, r.DollarShare, prev)
		}
		prev = r.DollarShare
		if r.DollarShare.LessThan(decimal.Zero) || r.DollarShare.GreaterThan(decimal.NewFromInt(1)) {
			t.Errorf("a dollar share must be a decimal in 0 to 1, got %s for %s", r.DollarShare, r.RecipientUEI)
		}
		if r.AwardCount < 1 {
			t.Errorf("the contributing award count (the evidence) must be at least 1, got %d for %s", r.AwardCount, r.RecipientUEI)
		}
		if r.RecipientUEI == "UEIGALAPAGOS3" {
			sawGalapagos = true
			if !r.IsGalapagos {
				t.Error("the tenant's own past performance row must be labeled is_galapagos")
			}
		}
		total = total.Add(r.DollarShare)
	}
	if !sawGalapagos {
		t.Error("the concentration must include the tenant's own past performance row")
	}
	// The shares sum to 1 within numeric rounding (the divided decimals carry a tiny residual, never a float).
	if total.Sub(decimal.NewFromInt(1)).Abs().GreaterThan(decimal.RequireFromString("0.0000001")) {
		t.Errorf("the dollar shares must sum to 1 within a tight epsilon in decimal, got %s", total)
	}

	again, err := ai.AgencyVendorConcentration(ctxOrg(org), appDB, agency, naics)
	if err != nil {
		t.Fatalf("agency vendor concentration re run: %v", err)
	}
	first, _ := json.Marshal(rows)
	second, _ := json.Marshal(again)
	if string(first) != string(second) {
		t.Errorf("the vendor concentration must be byte stable on a re run.\nfirst:  %s\nsecond: %s", first, second)
	}
}

// TestG7AgencySpendTrend proves the spend trend returns the agency's obligation total and award count per fiscal
// period in a NAICS over usaspending_awards, ordered by the period (so the trend reads forward in time), the
// obligation totals exact decimals, and byte stable on a re run. The fiscal period keys on pop_start_date (the
// award table has no award_date), and an award with a null pop_start_date is excluded rather than bucketed under
// a fabricated year.
func TestG7AgencySpendTrend(t *testing.T) {
	org := seedOrg(t)
	run := uuid.NewString()[:8]
	agency := "DEPT OF THE ARMY " + run
	naics := "541512"

	// Two periods of obligations: a year three back and a year one back, so the trend has two ordered periods.
	yearA := time.Date(time.Now().UTC().Year()-3, 5, 1, 0, 0, 0, 0, time.UTC)
	yearB := time.Date(time.Now().UTC().Year()-1, 5, 1, 0, 0, 0, 0, time.UTC)
	seedAwardWithPopStart(t, "G7-ST-A1-"+run, agency, naics, "UEIVENDORA001", "Vendor A LLC", "3000000.00", yearA)
	seedAwardWithPopStart(t, "G7-ST-A2-"+run, agency, naics, "UEIVENDORA002", "Vendor A Two LLC", "1000000.00", yearA)
	seedAwardWithPopStart(t, "G7-ST-B1-"+run, agency, naics, "UEIVENDORB001", "Vendor B LLC", "5000000.00", yearB)

	// An award with a null pop_start_date must NOT appear in the trend (no fabricated fiscal year).
	if _, err := migrator.Exec(context.Background(),
		`INSERT INTO usaspending_awards
		   (generated_internal_id, award_id, recipient_uei, recipient_name, awarding_agency, naics_code,
		    award_amount, pop_start_date, is_galapagos, raw)
		 VALUES ($1, $1, 'UEINULLDATE99', 'Null Date LLC', $2, $3, 9999999.00::numeric, NULL, false, '{}'::jsonb)
		 ON CONFLICT (generated_internal_id) DO NOTHING`,
		"G7-ST-NULL-"+run, agency, naics); err != nil {
		t.Fatalf("seed null pop start award: %v", err)
	}

	rows, err := ai.AgencySpendTrend(ctxOrg(org), appDB, agency, naics)
	if err != nil {
		t.Fatalf("agency spend trend: %v", err)
	}
	if len(rows) != 2 {
		t.Fatalf("the trend must return exactly the two periods with a pop start date, got %d", len(rows))
	}

	// Ordered by the fiscal period ascending; the totals are exact decimals.
	if rows[0].FiscalPeriod >= rows[1].FiscalPeriod {
		t.Errorf("the trend must be ordered by the fiscal period ascending, got %d then %d", rows[0].FiscalPeriod, rows[1].FiscalPeriod)
	}
	if rows[0].FiscalPeriod != yearA.Year() {
		t.Errorf("the first period must be the earlier year %d, got %d", yearA.Year(), rows[0].FiscalPeriod)
	}
	// Year A obligated 3m plus 1m equals 4m across two awards; year B obligated 5m across one award.
	if rows[0].AwardCount != 2 || !rows[0].ObligationDollars.Equal(decimal.RequireFromString("4000000.00")) {
		t.Errorf("the earlier period must total the decimal 4000000.00 across 2 awards, got %s across %d", rows[0].ObligationDollars, rows[0].AwardCount)
	}
	if rows[1].AwardCount != 1 || !rows[1].ObligationDollars.Equal(decimal.RequireFromString("5000000.00")) {
		t.Errorf("the later period must total the decimal 5000000.00 across 1 award, got %s across %d", rows[1].ObligationDollars, rows[1].AwardCount)
	}

	again, err := ai.AgencySpendTrend(ctxOrg(org), appDB, agency, naics)
	if err != nil {
		t.Fatalf("agency spend trend re run: %v", err)
	}
	first, _ := json.Marshal(rows)
	second, _ := json.Marshal(again)
	if string(first) != string(second) {
		t.Errorf("the spend trend must be byte stable on a re run.\nfirst:  %s\nsecond: %s", first, second)
	}
}