package recruitmatch

import (
	"context"
	"fmt"

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

// buildTemps materializes the per organization RLS scoped working sets once per request. ON COMMIT DROP
// cleans them up when the transaction ends, and the rm_ prefix keeps them from ever colliding with the
// commandcenter cc_ temps if both ever run in one transaction.
//
// rm_cand carries the funnel membership as three booleans computed in one pass. That is what makes the
// funnel monotonic BY CONSTRUCTION rather than by convention: approved_on_slate implies on_slate, and
// on_slate implies the screened predicate, so every stage is a strict subset of the one before it and no
// arrangement of data can make a later stage larger. Computing the stages as four independent queries
// would lose that guarantee, because a candidate can sit on a slate with no candidate_evaluations row
// (slate generation scores its own members), which would let submitted exceed screened.
func buildTemps(ctx context.Context, tx pgx.Tx, asOf string) error {
	// Defense in depth: confirm the tenant GUC is set before any aggregate runs, so a wrong pool or role
	// fails closed here instead of silently aggregating another tenant. On a platform sold to many
	// clients this check is the difference between a bug and a cross tenant disclosure.
	var guc string
	if err := tx.QueryRow(ctx, `SELECT COALESCE(current_setting('app.current_org_id', true),'')`).Scan(&guc); err != nil {
		return fmt.Errorf("tenant guc check: %w", err)
	}
	if guc == "" {
		return fmt.Errorf("recruitmatch: tenant GUC not set, refusing to aggregate")
	}

	// Live bench only. anonymized_at is excluded alongside deleted_at because an anonymized record is a
	// GDPR erasure tombstone, not a person who can be staffed.
	if _, err := tx.Exec(ctx, `
		CREATE TEMP TABLE rm_cand ON COMMIT DROP AS
		SELECT c.id,
		       c.full_name,
		       c.clearance_level,
		       c.available,
		       EXISTS (SELECT 1 FROM candidate_evaluations e WHERE e.candidate_id = c.id) AS has_eval,
		       EXISTS (SELECT 1 FROM slate_members sm WHERE sm.candidate_id = c.id) AS on_slate,
		       EXISTS (SELECT 1 FROM slate_members sm
		                WHERE sm.candidate_id = c.id AND sm.decision = 'approved') AS approved_on_slate
		FROM candidates c
		WHERE c.deleted_at IS NULL AND c.anonymized_at IS NULL`); err != nil {
		return fmt.Errorf("rm_cand: %w", err)
	}
	if _, err := tx.Exec(ctx, `CREATE INDEX ON rm_cand (clearance_level)`); err != nil {
		return fmt.Errorf("rm_cand index: %w", err)
	}

	// Open requirements. A requirement with no opportunity is open by definition, matching the
	// ListOpenRequirements contract the rest of the app already uses.
	if _, err := tx.Exec(ctx, `
		CREATE TEMP TABLE rm_active_req ON COMMIT DROP AS
		SELECT r.id, r.title, COALESCE(r.customer_agency,'') AS customer_agency, r.required_level
		FROM requirements r
		LEFT JOIN opportunities o ON o.id = r.opportunity_id
		WHERE r.deleted_at IS NULL AND (o.id IS NULL OR o.status = 'open')`); err != nil {
		return fmt.Errorf("rm_active_req: %w", err)
	}

	// Submissions, restricted to the live bench so a deleted candidate cannot inflate a requirement's
	// submission count. Joined to slates because slate_members carries no requirement of its own.
	if _, err := tx.Exec(ctx, `
		CREATE TEMP TABLE rm_sub ON COMMIT DROP AS
		SELECT s.requirement_id, sm.candidate_id, sm.decision, sm.decided_by
		FROM slate_members sm
		JOIN slates s ON s.id = sm.slate_id
		JOIN rm_cand c ON c.id = sm.candidate_id`); err != nil {
		return fmt.Errorf("rm_sub: %w", err)
	}
	if _, err := tx.Exec(ctx, `CREATE INDEX ON rm_sub (requirement_id)`); err != nil {
		return fmt.Errorf("rm_sub requirement index: %w", err)
	}
	if _, err := tx.Exec(ctx, `CREATE INDEX ON rm_sub (decided_by)`); err != nil {
		return fmt.Errorf("rm_sub decider index: %w", err)
	}

	_ = asOf // asOf is reserved for the dated panels; the current set is point in time by construction.
	return nil
}

// headline computes the whole funnel and the stat tiles in ONE pass over rm_cand. Because every stage is
// a FILTER over the same rows, the returned counts are guaranteed non increasing.
func headline(ctx context.Context, tx pgx.Tx) (Headline, error) {
	var h Headline
	err := tx.QueryRow(ctx, `
		SELECT count(*),
		       count(*) FILTER (WHERE has_eval OR on_slate),
		       count(*) FILTER (WHERE on_slate),
		       count(*) FILTER (WHERE approved_on_slate),
		       count(*) FILTER (WHERE available)
		FROM rm_cand`).
		Scan(&h.Identified, &h.Screened, &h.Submitted, &h.Approved, &h.OnBench)
	if err != nil {
		return h, fmt.Errorf("headline: %w", err)
	}
	if err := tx.QueryRow(ctx, `SELECT count(*) FROM rm_active_req`).Scan(&h.ActiveJobs); err != nil {
		return h, fmt.Errorf("active jobs: %w", err)
	}
	return h, nil
}

// decisionCounts totals slate membership decisions. These count MEMBERSHIPS, not people, which is why
// they are reported apart from the funnel.
func decisionCounts(ctx context.Context, tx pgx.Tx) (approved, rejected, pending int64, err error) {
	e := tx.QueryRow(ctx, `
		SELECT count(*) FILTER (WHERE decision = 'approved'),
		       count(*) FILTER (WHERE decision = 'rejected'),
		       count(*) FILTER (WHERE decision = 'pending')
		FROM rm_sub`).Scan(&approved, &rejected, &pending)
	if e != nil {
		return 0, 0, 0, fmt.Errorf("decision counts: %w", e)
	}
	return approved, rejected, pending, nil
}

// bandMix is the stored band split of persisted evaluations. recruitMATCH reports the band the frozen
// engine already assigned and never recomputes one.
func bandMix(ctx context.Context, tx pgx.Tx) (BandMix, error) {
	var b BandMix
	err := tx.QueryRow(ctx, `
		SELECT count(*) FILTER (WHERE band = 'green'),
		       count(*) FILTER (WHERE band = 'yellow'),
		       count(*) FILTER (WHERE band = 'red'),
		       count(*)
		FROM candidate_evaluations`).Scan(&b.Green, &b.Yellow, &b.Red, &b.Total)
	if err != nil {
		return b, fmt.Errorf("band mix: %w", err)
	}
	return b, nil
}

// clearanceRows returns the live bench split by clearance level. It drives from clearance_levels so every
// level appears even at zero, which keeps the chart shape stable as an organization fills up, and orders
// by the reference ladder so the axis reads least to most cleared regardless of what the org holds.
func clearanceRows(ctx context.Context, tx pgx.Tx) ([]ClearanceRow, error) {
	rows, err := tx.Query(ctx, `
		SELECT cl.code, cl.label, count(c.id)
		FROM clearance_levels cl
		LEFT JOIN rm_cand c ON c.clearance_level = cl.code
		GROUP BY cl.code, cl.label, cl.rank
		ORDER BY cl.rank, cl.code`)
	if err != nil {
		return nil, fmt.Errorf("clearance rows: %w", err)
	}
	defer rows.Close()

	out := []ClearanceRow{}
	var total int64
	for rows.Next() {
		var r ClearanceRow
		if err := rows.Scan(&r.Code, &r.Label, &r.Count); err != nil {
			return nil, fmt.Errorf("clearance scan: %w", err)
		}
		total += r.Count
		out = append(out, r)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("clearance iterate: %w", err)
	}
	// Percentages are computed after the full total is known, so they are shares of the bench rather than
	// of a running subtotal. Rounded shares are not guaranteed to sum to exactly 100.00; that is inherent
	// to rounding and is not corrected here, because inventing a residual to force the sum would misstate
	// a category count.
	for i := range out {
		out[i].Pct = pct(out[i].Count, total).StringFixed(2)
	}
	return out, nil
}

// topJobs ranks open requirements by DISTINCT candidates submitted. One set based query with no per row
// lookup. The ordering carries a full tiebreak chain ending in the id so the result is byte identical
// across runs for identical data.
func topJobs(ctx context.Context, tx pgx.Tx, limit int) ([]TopJob, error) {
	rows, err := tx.Query(ctx, `
		SELECT r.id::text, r.title, r.customer_agency, r.required_level,
		       count(DISTINCT s.candidate_id) AS submitted
		FROM rm_active_req r
		LEFT JOIN rm_sub s ON s.requirement_id = r.id
		GROUP BY r.id, r.title, r.customer_agency, r.required_level
		ORDER BY count(DISTINCT s.candidate_id) DESC, r.title ASC, r.id ASC
		LIMIT $1`, limit)
	if err != nil {
		return nil, fmt.Errorf("top jobs: %w", err)
	}
	defer rows.Close()

	out := []TopJob{}
	for rows.Next() {
		var j TopJob
		if err := rows.Scan(&j.RequirementID, &j.Title, &j.CustomerAgency, &j.RequiredLevel, &j.Submitted); err != nil {
			return nil, fmt.Errorf("top jobs scan: %w", err)
		}
		out = append(out, j)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("top jobs iterate: %w", err)
	}
	return out, nil
}

// recruiters attributes screening and slate decisions to active users in this organization. users is RLS
// scoped, so this cannot see another tenant's people. It works unchanged for a one recruiter shop and a
// twenty recruiter shop.
func recruiters(ctx context.Context, tx pgx.Tx) ([]RecruiterRow, error) {
	rows, err := tx.Query(ctx, `
		SELECT u.id::text, u.full_name,
		       (SELECT count(*) FROM candidate_evaluations e WHERE e.evaluated_by = u.id),
		       (SELECT count(*) FROM rm_sub s WHERE s.decided_by = u.id),
		       (SELECT count(*) FROM rm_sub s WHERE s.decided_by = u.id AND s.decision = 'approved')
		FROM users u
		WHERE u.status = 'active'
		ORDER BY u.full_name ASC, u.id ASC`)
	if err != nil {
		return nil, fmt.Errorf("recruiters: %w", err)
	}
	defer rows.Close()

	out := []RecruiterRow{}
	for rows.Next() {
		var r RecruiterRow
		if err := rows.Scan(&r.UserID, &r.FullName, &r.Screened, &r.Decided, &r.Approved); err != nil {
			return nil, fmt.Errorf("recruiters scan: %w", err)
		}
		out = append(out, r)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("recruiters iterate: %w", err)
	}
	return out, nil
}