package commandcenter

import (
	"context"
	"fmt"

	"github.com/jackc/pgx/v5"
	"github.com/shopspring/decimal"
)

// upcomingForecastLimit caps the Upcoming Opportunities panel.
const upcomingForecastLimit = 12

// planningNote is the standing label on every forecast row, so the panel always reads as planning data.
const planningNote = "Planning data subject to change"

// upcomingOpportunities builds the Upcoming Opportunities panel from the global forecast catalog
// (record_kind forecast) and grounds each row in REAL signals from the org's own bench: the count of
// available, effectively cleared candidates in the forecast's place of performance, and the engine
// backed coverage of the org's comparable requirements for the same agency (reusing the eligibility in
// cc_qual, the same hard gates the engine uses, so there is no parallel scoring path). Forecasts are
// planning data and never alter the current readiness or Revenue at Risk numbers. The forecast catalog
// is public reference data; it is read here inside the tenant transaction only so the bench counts
// beside it stay RLS scoped to the caller's org.
func (s *Service) upcomingOpportunities(ctx context.Context, tx pgx.Tx) ([]UpcomingOpportunity, error) {
	rows, err := tx.Query(ctx, `
		SELECT id::text, title, agency, COALESCE(naics,''), COALESCE(pop_state,''),
		       COALESCE(set_aside,''), estimated_value,
		       COALESCE(to_char(estimated_award_date,'YYYY-MM-DD'),''), COALESCE(incumbent_name,'')
		FROM opportunity_records
		WHERE record_kind='forecast'
		ORDER BY estimated_award_date NULLS LAST, title, id
		LIMIT $1`, upcomingForecastLimit)
	if err != nil {
		return nil, fmt.Errorf("upcoming forecasts: %w", err)
	}
	type fc struct {
		id, title, agency, naics, state, setAside, awardDate, incumbent string
		value                                                           *decimal.Decimal
	}
	var forecasts []fc
	for rows.Next() {
		var f fc
		if err := rows.Scan(&f.id, &f.title, &f.agency, &f.naics, &f.state, &f.setAside, &f.value, &f.awardDate, &f.incumbent); err != nil {
			rows.Close()
			return nil, err
		}
		forecasts = append(forecasts, f)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return nil, err
	}

	out := make([]UpcomingOpportunity, 0, len(forecasts))
	for _, f := range forecasts {
		u := UpcomingOpportunity{
			RecordID: f.id, Title: f.title, Agency: f.agency, NAICS: f.naics,
			PlaceOfPerformance: f.state, State: f.state, SetAside: f.setAside,
			EstimatedAwardDate: f.awardDate, IncumbentName: f.incumbent,
			PlanningNote: planningNote, Adoptable: true,
		}
		if f.value != nil {
			vs := f.value.StringFixed(2)
			u.EstimatedValue = &vs
		}

		// Real signal: available, effectively cleared candidates whose location names the state. The
		// state code is matched on a word boundary so HI does not match CHICAGO.
		if err := tx.QueryRow(ctx, `
			SELECT count(*) FROM cc_cand
			WHERE available AND effective AND lvl>=2
			  AND ($1='' OR location ~* ('\m'||$1||'\M'))`, f.state).Scan(&u.AvailableClearedInState); err != nil {
			return nil, fmt.Errorf("upcoming bench count: %w", err)
		}

		// Engine backed estimate: coverage of the org's comparable open requirements for this agency
		// (cc_qual is the same eligibility the engine gates on, so no new scoring path). The agency match
		// is a substring both directions so DEPT OF DEFENSE and Department of Defense align.
		if f.agency != "" {
			if err := tx.QueryRow(ctx, `
				SELECT count(*), count(*) FILTER (WHERE COALESCE(q.n,0)>0)
				FROM cc_active_req ar
				JOIN requirements r ON r.id = ar.id
				LEFT JOIN cc_qual q ON q.req_id = ar.id
				WHERE r.customer_agency IS NOT NULL AND r.customer_agency <> ''
				  AND (upper(r.customer_agency) LIKE '%'||upper($1)||'%' OR upper($1) LIKE '%'||upper(r.customer_agency)||'%')`,
				f.agency).Scan(&u.ComparableRequirements, &u.ComparableCovered); err != nil {
				return nil, fmt.Errorf("upcoming comparable coverage: %w", err)
			}
		}
		u.FitNote = upcomingFitNote(u)
		out = append(out, u)
	}
	return out, nil
}

// incumbentLandscapeLimit caps the incumbent context panel.
const incumbentLandscapeLimit = 12

// incumbentLandscape surfaces the incumbent and award context from the contract award feed (SAM Contract
// Awards and the USASpending fallback): who holds the largest contracts in the org's domain, so a matched
// candidate is read next to the incumbent they would compete against on a recompete. All figures are real
// award data with their provenance (value_source). The award catalog is public reference data, read here
// inside the tenant transaction only for one round trip.
func (s *Service) incumbentLandscape(ctx context.Context, tx pgx.Tx) ([]IncumbentRef, error) {
	rows, err := tx.Query(ctx, `
		SELECT id::text, incumbent_name, COALESCE(incumbent_uei,''), agency, COALESCE(naics,''),
		       estimated_value, COALESCE(value_source,''),
		       COALESCE(to_char(award_date,'YYYY-MM-DD'),''), source_name
		FROM opportunity_records
		WHERE record_kind='contract_award' AND incumbent_name <> ''
		ORDER BY estimated_value DESC NULLS LAST, id
		LIMIT $1`, incumbentLandscapeLimit)
	if err != nil {
		return nil, fmt.Errorf("incumbent landscape: %w", err)
	}
	defer rows.Close()
	out := []IncumbentRef{}
	for rows.Next() {
		var r IncumbentRef
		var value *decimal.Decimal
		if err := rows.Scan(&r.RecordID, &r.Incumbent, &r.IncumbentUEI, &r.Agency, &r.NAICS, &value, &r.ValueSource, &r.AwardDate, &r.Source); err != nil {
			return nil, err
		}
		if value != nil {
			vs := value.StringFixed(2)
			r.Value = &vs
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// upcomingFitNote builds the clearly labeled, honest fit line. It never asserts a precise match against a
// bare forecast (a forecast carries no skills); it states the real bench presence and, when the org has
// comparable requirements, the engine backed coverage of those, labeled as an estimate.
func upcomingFitNote(u UpcomingOpportunity) string {
	where := u.State
	if where == "" {
		where = "the place of performance"
	}
	base := fmt.Sprintf("%d available cleared in %s", u.AvailableClearedInState, where)
	if u.ComparableRequirements > 0 {
		return fmt.Sprintf("%s. Estimated from your %d %s requirement(s): %d currently covered.",
			base, u.ComparableRequirements, u.Agency, u.ComparableCovered)
	}
	return base + ". No comparable requirement yet; adopt it to score the bench against it."
}