package commandcenter

import (
	"context"
	"fmt"
	"sort"
	"time"

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

// ReqCoverage is one requirement's staffing coverage from the greedy assignment (the slate coverage
// logic). Unmet positions are Quantity minus Assigned.
type ReqCoverage struct {
	Quantity      int
	Assigned      int
	Qualified     int
	LaborCategory string
	KeyPersonnel  bool
}

// Coverage builds the per-requirement staffing coverage for the caller's org, reusing the exact same
// greedy assignment the Command Center uses (each available candidate consumed at most once). It also
// returns the eligibility edges (requirement id to the set of candidate ids that pass the hard gates:
// clearance, years, and all mandatory skills), so Win Impact gates on the SAME eligibility the
// assignment uses, not a softer band heuristic. The caller supplies an open tenant transaction
// (RLS-scoped). This is the single source of coverage truth shared by the dashboard and the candidate
// intelligence Win Impact.
func Coverage(ctx context.Context, tx pgx.Tx, asOf time.Time) (map[string]ReqCoverage, map[string]map[string]bool, error) {
	if err := buildTemps(ctx, tx, asOf.Format("2006-01-02")); err != nil {
		return nil, nil, err
	}
	assigned, qualified, reqs, edges, err := fetchAssignment(ctx, tx)
	if err != nil {
		return nil, nil, err
	}
	out := make(map[string]ReqCoverage, len(reqs))
	for _, r := range reqs {
		out[r.id] = ReqCoverage{
			Quantity: r.quantity, Assigned: assigned[r.id], Qualified: qualified[r.id],
			LaborCategory: r.lcat, KeyPersonnel: r.keyPersonnel,
		}
	}
	eligible := make(map[string]map[string]bool, len(edges))
	for rid, cids := range edges {
		m := make(map[string]bool, len(cids))
		for _, cid := range cids {
			m[cid] = true
		}
		eligible[rid] = m
	}
	return out, eligible, nil
}

// reqMeta is one active requirement's metadata for the assignment.
type reqMeta struct {
	id, oppID, lcat, level string
	quantity               int
	keyPersonnel           bool
}

// fetchAssignment computes a TRUE staffing assignment: each available candidate is consumed AT MOST
// ONCE across all active requirements (greedy, scarcest-requirement-first). This replaces the prior
// per-requirement qualification count that double-counted a candidate across every requirement they
// fit [review COV-1/COV-2]. Returns the per-requirement assigned headcount, the per-requirement
// qualified-pool size (distinct eligible candidates), and the requirement metadata.
func fetchAssignment(ctx context.Context, tx pgx.Tx) (assigned, qualified map[string]int, reqs []reqMeta, edges map[string][]string, err error) {
	mrows, err := tx.Query(ctx, `
		SELECT id::text, COALESCE(opportunity_id::text,''),
		       COALESCE(NULLIF(trim(labor_category),''),'Uncategorized'), quantity, key_personnel, required_level
		FROM cc_active_req ORDER BY id`)
	if err != nil {
		return nil, nil, nil, nil, fmt.Errorf("req meta: %w", err)
	}
	for mrows.Next() {
		var r reqMeta
		var q int32
		if err := mrows.Scan(&r.id, &r.oppID, &r.lcat, &q, &r.keyPersonnel, &r.level); err != nil {
			mrows.Close()
			return nil, nil, nil, nil, err
		}
		r.quantity = int(q)
		reqs = append(reqs, r)
	}
	mrows.Close()
	if err := mrows.Err(); err != nil {
		return nil, nil, nil, nil, err
	}

	// eligibility edges: which available candidate qualifies for which active req (clearance + years +
	// ALL mandatory skills). Ordered for deterministic greedy selection.
	erows, err := tx.Query(ctx, `
		SELECT ar.id::text, c.id::text
		FROM cc_active_req ar
		JOIN cc_cand c ON c.available
		  AND (ar.rlvl < 2 OR (c.effective AND c.lvl >= ar.rlvl AND c.poly >= ar.rpoly))
		  AND c.years_experience >= ar.required_years
		  AND NOT EXISTS (
		    SELECT 1 FROM unnest(COALESCE((SELECT array_agg(skill_id) FROM required_skills rs WHERE rs.requirement_id=ar.id AND rs.mandatory),'{}'::uuid[])) AS rsk
		    WHERE NOT EXISTS (SELECT 1 FROM candidate_skills cs WHERE cs.candidate_id=c.id AND cs.skill_id=rsk)
		  )
		ORDER BY ar.id, c.id`)
	if err != nil {
		return nil, nil, nil, nil, fmt.Errorf("eligibility edges: %w", err)
	}
	edges = map[string][]string{}
	for erows.Next() {
		var rid, cid string
		if err := erows.Scan(&rid, &cid); err != nil {
			erows.Close()
			return nil, nil, nil, nil, err
		}
		edges[rid] = append(edges[rid], cid)
	}
	erows.Close()
	if err := erows.Err(); err != nil {
		return nil, nil, nil, nil, err
	}

	qualified = make(map[string]int, len(reqs))
	for _, r := range reqs {
		qualified[r.id] = len(edges[r.id])
	}

	// greedy: fill the scarcest (fewest-eligible) requirements first; each candidate used at most once.
	order := make([]reqMeta, len(reqs))
	copy(order, reqs)
	sort.Slice(order, func(i, j int) bool {
		qi, qj := qualified[order[i].id], qualified[order[j].id]
		if qi != qj {
			return qi < qj
		}
		if order[i].quantity != order[j].quantity {
			return order[i].quantity > order[j].quantity
		}
		return order[i].id < order[j].id
	})
	assigned = make(map[string]int, len(reqs))
	used := make(map[string]bool)
	for _, r := range order {
		for _, cid := range edges[r.id] {
			if assigned[r.id] >= r.quantity {
				break
			}
			if !used[cid] {
				used[cid] = true
				assigned[r.id]++
			}
		}
	}
	return assigned, qualified, reqs, edges, nil
}

func oppSortVal(v *string) decimal.Decimal {
	if v == nil {
		return decimal.NewFromInt(-1)
	}
	d, err := decimal.NewFromString(*v)
	if err != nil {
		return decimal.NewFromInt(-1)
	}
	return d
}

// opportunitiesFromAssignment rolls the per-requirement assignment up to each open opportunity.
func opportunitiesFromAssignment(reqs []reqMeta, assigned map[string]int, opp map[string]oppInfo) ([]Opportunity, int) {
	byOpp := map[string][]reqMeta{}
	for _, r := range reqs {
		if r.oppID != "" {
			byOpp[r.oppID] = append(byOpp[r.oppID], r)
		}
	}
	out := make([]Opportunity, 0, len(byOpp))
	high := 0
	for oppID, rs := range byOpp {
		info := opp[oppID]
		total := len(rs)
		covered, hcNeed, hcCov, kpOpen := 0, 0, 0, 0
		kpOK := true
		for _, r := range rs {
			a := assigned[r.id]
			hcNeed += r.quantity
			hcCov += a
			if a >= r.quantity {
				covered++
			}
			if r.keyPersonnel && a < r.quantity {
				kpOpen++
				kpOK = false
			}
		}
		ratio := pct(int64(covered), int64(total))
		o := Opportunity{
			OpportunityID: oppID, Name: info.name, CustomerAgency: info.customer,
			SolicitationNumber: info.solicitation, DueDate: info.dueDate,
			ReadinessScore: ratio.StringFixed(2), TotalRequirements: total, CoveredRequirements: covered,
			HeadcountNeeded: hcNeed, HeadcountCovered: hcCov, KeyPersonnelOpen: kpOpen,
		}
		if info.value != nil {
			vs := info.value.StringFixed(2)
			o.Value = &vs
		}
		switch {
		case !kpOK || ratio.LessThan(decimal.NewFromInt(50)):
			o.StaffingRisk, o.StaffingRiskCode = "High", "high"
			high++
		case ratio.LessThan(decimal.NewFromInt(85)):
			o.StaffingRisk, o.StaffingRiskCode = "Medium", "medium"
		default:
			o.StaffingRisk, o.StaffingRiskCode = "Low", "low"
		}
		out = append(out, o)
	}
	sort.Slice(out, func(i, j int) bool {
		vi, vj := oppSortVal(out[i].Value), oppSortVal(out[j].Value)
		if !vi.Equal(vj) {
			return vi.GreaterThan(vj)
		}
		return out[i].OpportunityID < out[j].OpportunityID
	})
	return out, high
}

// gapsFromAssignment derives true unmet positions (quantity minus ASSIGNED) grouped by labor category
// + required level. Banner staffing_gaps == sum of these (incl Uncategorized) so they reconcile [COV-3].
func gapsFromAssignment(reqs []reqMeta, assigned map[string]int, opp map[string]oppInfo) ([]Gap, *string, int) {
	type key struct{ lcat, level string }
	type acc struct {
		unmet    int
		blocksKP bool
		oppIDs   map[string]bool
	}
	groups := map[key]*acc{}
	keyOrder := []key{}
	totalGaps := 0
	var suggestReq *string
	maxUnmet := -1
	for _, r := range reqs {
		unmet := r.quantity - assigned[r.id]
		if unmet < 0 {
			unmet = 0
		}
		totalGaps += unmet
		if unmet == 0 {
			continue
		}
		if unmet > maxUnmet { // reqs are in id order => stable lowest-id pick on ties
			maxUnmet = unmet
			id := r.id
			suggestReq = &id
		}
		k := key{r.lcat, r.level}
		g := groups[k]
		if g == nil {
			g = &acc{oppIDs: map[string]bool{}}
			groups[k] = g
			keyOrder = append(keyOrder, k)
		}
		g.unmet += unmet
		if r.keyPersonnel {
			g.blocksKP = true
		}
		if r.oppID != "" {
			g.oppIDs[r.oppID] = true
		}
	}
	out := make([]Gap, 0, len(groups))
	for _, k := range keyOrder {
		g := groups[k]
		ids := make([]string, 0, len(g.oppIDs))
		for id := range g.oppIDs {
			ids = append(ids, id)
		}
		sort.Strings(ids)
		names := []string{}
		var risk decimal.Decimal
		have := false
		for _, id := range ids {
			info := opp[id]
			if info.name != "" {
				names = append(names, info.name)
			}
			if info.value != nil {
				risk = risk.Add(*info.value)
				have = true
			}
		}
		sort.Strings(names)
		var riskStr *string
		if have {
			vs := risk.StringFixed(2)
			riskStr = &vs
		}
		out = append(out, Gap{
			LaborCategory: k.lcat, RequiredLevel: k.level, RequiredLevelLabel: levelLabel(k.level),
			Unmet: g.unmet, BlocksKeyPersonnel: g.blocksKP, ImpactedOpportunities: names, ValueAtRisk: riskStr,
			Headline:       fmt.Sprintf("NEED %d %s (%s)", g.unmet, pluralize(k.lcat, int64(g.unmet)), levelLabel(k.level)),
			Recommendation: fmt.Sprintf("Recruit %d %s %s%s.", g.unmet, levelLabel(k.level), pluralize(k.lcat, int64(g.unmet)), kpClause(g.blocksKP)),
		})
	}
	sort.SliceStable(out, func(i, j int) bool {
		if out[i].Unmet != out[j].Unmet {
			return out[i].Unmet > out[j].Unmet
		}
		if out[i].LaborCategory != out[j].LaborCategory {
			return out[i].LaborCategory < out[j].LaborCategory
		}
		return out[i].RequiredLevel < out[j].RequiredLevel
	})
	return out, suggestReq, totalGaps
}

// lcatCoverageFromAssignment is the true fill rate per labor category (assigned / demanded).
func lcatCoverageFromAssignment(reqs []reqMeta, assigned map[string]int) []LcatCoverage {
	type acc struct{ filled, demanded int }
	m := map[string]*acc{}
	order := []string{}
	for _, r := range reqs {
		a := m[r.lcat]
		if a == nil {
			a = &acc{}
			m[r.lcat] = a
			order = append(order, r.lcat)
		}
		a.filled += assigned[r.id]
		a.demanded += r.quantity
	}
	out := make([]LcatCoverage, 0, len(m))
	for _, lcat := range order {
		a := m[lcat]
		cov := pct(int64(a.filled), int64(a.demanded))
		out = append(out, LcatCoverage{lcat, a.filled, a.demanded, cov.StringFixed(2), colorFor(cov)})
	}
	sort.SliceStable(out, func(i, j int) bool { // ascending coverage via cross-multiply (exact)
		li := int64(out[i].Filled) * int64(out[j].Demanded)
		lj := int64(out[j].Filled) * int64(out[i].Demanded)
		if li != lj {
			return li < lj
		}
		return out[i].LaborCategory < out[j].LaborCategory
	})
	return out
}

// proposalFromAssignment buckets each open opportunity (KP-missing > LCAT-missing > at-risk > ready)
// and returns the proposal-readiness driver % (opportunities fully ASSIGNABLE / open opportunities).
func proposalFromAssignment(reqs []reqMeta, assigned, qualified map[string]int, opp map[string]oppInfo) (ProposalReadiness, decimal.Decimal) {
	byOpp := map[string][]reqMeta{}
	for _, r := range reqs {
		if r.oppID != "" {
			byOpp[r.oppID] = append(byOpp[r.oppID], r)
		}
	}
	pr := ProposalReadiness{
		ReadyOpportunities: []OppRef{}, AtRiskOpportunities: []OppRef{},
		KeyPersonnelMissingOpportunities: []OppRef{}, LaborCategoriesMissingOpportunities: []OppRef{},
	}
	oppIDs := make([]string, 0, len(byOpp))
	for id := range byOpp {
		oppIDs = append(oppIDs, id)
	}
	sort.Slice(oppIDs, func(i, j int) bool {
		ni, nj := opp[oppIDs[i]].name, opp[oppIDs[j]].name
		if ni != nj {
			return ni < nj
		}
		return oppIDs[i] < oppIDs[j]
	})
	ready := 0
	for _, oppID := range oppIDs {
		kpMissing, lcatMissing, fully := false, false, true
		for _, r := range byOpp[oppID] {
			if qualified[r.id] == 0 {
				if r.keyPersonnel {
					kpMissing = true
				} else {
					lcatMissing = true
				}
			}
			if assigned[r.id] < r.quantity {
				fully = false
			}
		}
		ref := OppRef{OpportunityID: oppID, Name: opp[oppID].name}
		switch {
		case kpMissing:
			pr.KeyPersonnelMissing++
			pr.KeyPersonnelMissingOpportunities = append(pr.KeyPersonnelMissingOpportunities, ref)
		case lcatMissing:
			pr.LaborCategoriesMissing++
			pr.LaborCategoriesMissingOpportunities = append(pr.LaborCategoriesMissingOpportunities, ref)
		case !fully:
			pr.AtRisk++
			pr.AtRiskOpportunities = append(pr.AtRiskOpportunities, ref)
		default:
			pr.Ready++
			pr.ReadyOpportunities = append(pr.ReadyOpportunities, ref)
			ready++
		}
	}
	return pr, pct(int64(ready), int64(len(oppIDs)))
}