package recruiting

import (
	"context"
	"fmt"
	"strings"
	"time"

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

// THE MANAGER'S VIEW, WHICH DID NOT EXIST.
//
// A manager running six recruiters opened this product and got the recruiter dashboard. Identical screen,
// identical numbers, org wide, one window, no comparison, nothing they could act on. Three things were
// missing and all three are reads over data already held:
//
//   SCOPE       teams shipped and nothing read them, so a manager could ask how the COMPANY was doing and
//               never how their own desk was doing.
//   COMPARISON  every figure was a single window. "Fill rate 62 percent" is not a management fact.
//               "62 percent, down from 71" is, and the difference is one extra query.
//   CAPACITY    the product knows how many open orders each person holds and never said it. The 2026
//               benchmark is fourteen requisitions per recruiter, and practitioners put the point where the
//               work changes shape at twenty to thirty: above that, intake meetings get skipped, sourcing
//               stops in favour of reviewing inbound, and screening goes shallow.
//
// NOTHING HERE IS A NEW TABLE. Capacity is measured against the published benchmark and is NAMED as a
// benchmark rather than a target, because a target belongs with a goals model and there is not one yet.
// Shipping a target without goals behind it would be inventing a number and calling it management.

// Load bands, from the published benchmarks rather than invented. They are the reason the number is worth
// showing at all: a count with no band is trivia.
const (
	// LoadBenchmark is the 2026 average requisitions per recruiter.
	LoadBenchmark = 14
	// LoadStrained is where practitioners say the work changes shape rather than merely gets busier.
	LoadStrained = 20
	// LoadOverloaded is the upper end of that same range.
	LoadOverloaded = 30
)

// RecruiterLoad is one person's open workload.
type RecruiterLoad struct {
	UserID string `json:"user_id,omitempty"`
	Name   string `json:"name"`
	// OpenRequisitions is what the band is computed from: orders still open and owned by this person.
	OpenRequisitions int `json:"open_requisitions"`
	// ActiveApplications is the pipeline underneath those orders, which is what the load actually feels like.
	ActiveApplications int `json:"active_applications"`
	// Band is one of ok, watch, strained, overloaded. A count without a band is trivia; the band is the part
	// a manager acts on.
	Band string `json:"band"`
}

func loadBand(open int) string {
	switch {
	case open >= LoadOverloaded:
		return "overloaded"
	case open >= LoadStrained:
		return "strained"
	case open > LoadBenchmark:
		return "watch"
	default:
		return "ok"
	}
}

// Comparison is one figure against the same figure in the window immediately before it.
//
// Direction is computed here rather than in the browser so every screen agrees about what "better" means:
// a rising fill rate is better and a rising time to fill is worse, and a component deciding that for itself
// gets it backwards on half the metrics.
type Comparison struct {
	Now      string `json:"now"`
	Previous string `json:"previous"`
	// Direction is up, down or flat. It describes the NUMBER, never whether the number is good.
	Direction string `json:"direction"`
	// Better says whether the movement is in the direction this organization wants, which is metric
	// specific and is the thing a screen actually colours on.
	Better string `json:"better,omitempty"` // yes | no | flat
}

// ManagerView is what the manager surface adds on top of the existing analytics report.
type ManagerView struct {
	// Scope names WHO this answer is about, echoed back so a filtered screen can say so rather than looking
	// like the whole company had a quiet month.
	Scope       string `json:"scope"`
	ScopeLabel  string `json:"scope_label"`
	PeriodDays  int    `json:"period_days"`
	PreviousEnd string `json:"previous_end"`

	Placements  Comparison      `json:"placements"`
	Submissions Comparison      `json:"submissions"`
	FillRate    Comparison      `json:"fill_rate"`
	TimeToFill  Comparison      `json:"time_to_fill_days"`
	Capacity    []RecruiterLoad `json:"capacity"`
	// Overloaded is the count a manager opens this screen to find, computed here so two panels cannot
	// disagree about how many people are underwater.
	Overloaded int `json:"overloaded"`
}

// resolveScope turns the caller's request into a set of owner ids and a label.
//
// AN EMPTY SCOPE MEANS THE WHOLE ORGANIZATION, which is what an owner or an administrator wants and is the
// behaviour that existed before this. A team narrows to its members. A user narrows to one person, which is
// the one to one preparation view.
func (s *Service) resolveScope(ctx context.Context, tx pgx.Tx, teamID, owner string) ([]uuid.UUID, string, string, error) {
	teamID, owner = strings.TrimSpace(teamID), strings.TrimSpace(owner)

	if owner != "" {
		id, err := uuid.Parse(owner)
		if err != nil {
			return nil, "", "", &ValidationError{Field: "owner", Message: "is not an identifier"}
		}
		var name string
		if e := tx.QueryRow(ctx, `SELECT full_name FROM users WHERE id = $1`, id).Scan(&name); e != nil {
			return nil, "", "", ErrNotFound
		}
		return []uuid.UUID{id}, "user", name, nil
	}

	if teamID != "" {
		id, err := uuid.Parse(teamID)
		if err != nil {
			return nil, "", "", &ValidationError{Field: "team_id", Message: "is not an identifier"}
		}
		var name string
		if e := tx.QueryRow(ctx, `SELECT name FROM teams WHERE id = $1`, id).Scan(&name); e != nil {
			return nil, "", "", ErrNotFound
		}
		rows, e := tx.Query(ctx, `SELECT user_id FROM team_members WHERE team_id = $1`, id)
		if e != nil {
			return nil, "", "", e
		}
		defer rows.Close()
		var ids []uuid.UUID
		for rows.Next() {
			var u uuid.UUID
			if e := rows.Scan(&u); e != nil {
				return nil, "", "", e
			}
			ids = append(ids, u)
		}
		if e := rows.Err(); e != nil {
			return nil, "", "", e
		}
		// AN EMPTY TEAM IS NOT THE WHOLE ORGANIZATION. Returning no filter here would silently widen a
		// manager's view from their own desk to everybody, which is the opposite of what they asked for and
		// is the kind of error nobody notices because the screen fills with numbers.
		if len(ids) == 0 {
			return []uuid.UUID{uuid.Nil}, "team", name + ", nobody on it yet", nil
		}
		return ids, "team", name, nil
	}

	return nil, "organization", "the whole organization", nil
}

// ManagerSummary builds the manager view for one window and scope.
func (s *Service) ManagerSummary(ctx context.Context, sinceRaw, teamID, owner string) (ManagerView, error) {
	var out ManagerView
	if s.db == nil {
		return out, ErrNoDatabase
	}
	since, err := optTime("since", sinceRaw)
	if err != nil {
		return out, err
	}
	if since == nil {
		t := time.Now().UTC().AddDate(0, 0, -30)
		since = &t
	}
	now := time.Now().UTC()
	days := int(now.Sub(*since).Hours() / 24)
	if days < 1 {
		days = 1
	}
	// THE PREVIOUS WINDOW IS THE SAME LENGTH, IMMEDIATELY BEFORE. Comparing a thirty day month against a
	// ninety day quarter would produce a decline every time and mean nothing.
	prevStart := since.AddDate(0, 0, -days)
	out.PeriodDays = days
	out.PreviousEnd = since.UTC().Format(time.RFC3339)

	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		ids, scope, label, e := s.resolveScope(ctx, tx, teamID, owner)
		if e != nil {
			return e
		}
		out.Scope, out.ScopeLabel = scope, label

		nowP, nowS, nowF, nowT, e := s.windowFigures(ctx, tx, *since, now, ids)
		if e != nil {
			return e
		}
		prvP, prvS, prvF, prvT, e := s.windowFigures(ctx, tx, prevStart, *since, ids)
		if e != nil {
			return e
		}
		out.Placements = compare(nowP, prvP, true)
		out.Submissions = compare(nowS, prvS, true)
		out.FillRate = compare(nowF, prvF, true)
		// TIME TO FILL IS THE ONE WHERE DOWN IS BETTER, which is exactly why Better is computed on the server
		// rather than left to a component to guess.
		out.TimeToFill = compare(nowT, prvT, false)

		caps, e := s.capacity(ctx, tx, ids)
		if e != nil {
			return e
		}
		out.Capacity = caps
		for _, c := range caps {
			if c.Band == "overloaded" || c.Band == "strained" {
				out.Overloaded++
			}
		}
		return nil
	})
	return out, err
}

// windowFigures returns placements, submissions, fill rate and median days to fill for one window and owner
// set, in ONE query, so the four figures cannot describe four slightly different populations.
func (s *Service) windowFigures(ctx context.Context, tx pgx.Tx, from, to time.Time, owners []uuid.UUID) (
	placements, submissions, fillRate, ttf decimal.Decimal, err error) {
	// A nil owner slice means no filter. The ANY comparison with a NULL array is never true, so the filter is
	// applied only when there is one, and the same SQL serves both.
	var arr any
	if owners != nil {
		arr = owners
	}
	row := tx.QueryRow(ctx, `
		WITH scoped_apps AS (
		    SELECT a.id, a.owner_user_id
		      FROM applications a
		     WHERE ($3::uuid[] IS NULL OR a.owner_user_id = ANY($3))
		), subs AS (
		    SELECT count(DISTINCT e.application_id)::int AS n
		      FROM application_stage_events e
		      JOIN scoped_apps a ON a.id = e.application_id
		     WHERE e.to_stage = 'submitted' AND e.occurred_at >= $1 AND e.occurred_at < $2
		), plcs AS (
		    SELECT count(DISTINCT p.id)::int AS n
		      FROM placements p
		      JOIN scoped_apps a ON a.id = p.application_id
		     WHERE p.created_at >= $1 AND p.created_at < $2
		), reqs AS (
		    SELECT count(*) FILTER (WHERE r.status = 'filled')::int AS filled,
		           count(*) FILTER (WHERE r.status IN ('filled','closed','cancelled'))::int AS resolved,
		           coalesce(percentile_cont(0.5) WITHIN GROUP (
		               ORDER BY EXTRACT(EPOCH FROM (r.closed_at - r.opened_at)) / 86400.0
		           ) FILTER (WHERE r.status = 'filled' AND r.closed_at IS NOT NULL), 0)::numeric AS med_days
		      FROM requisitions r
		     WHERE r.deleted_at IS NULL AND r.opened_at >= $1 AND r.opened_at < $2
		       AND ($3::uuid[] IS NULL OR r.owner_user_id = ANY($3))
		)
		SELECT plcs.n, subs.n,
		       CASE WHEN reqs.resolved > 0
		            THEN round(reqs.filled::numeric * 100 / reqs.resolved, 2) ELSE 0 END,
		       round(reqs.med_days, 1)
		  FROM plcs, subs, reqs`, from, to, arr)
	var p, sub int
	var fr, md decimal.Decimal
	if e := row.Scan(&p, &sub, &fr, &md); e != nil {
		return placements, submissions, fillRate, ttf, fmt.Errorf("recruiting: manager window: %w", e)
	}
	return decimal.NewFromInt(int64(p)), decimal.NewFromInt(int64(sub)), fr, md, nil
}

// capacity returns the open load per person.
func (s *Service) capacity(ctx context.Context, tx pgx.Tx, owners []uuid.UUID) ([]RecruiterLoad, error) {
	var arr any
	if owners != nil {
		arr = owners
	}
	// THE PIPELINE COUNT IS AGGREGATED ONCE, NOT PER ROW.
	//
	// The first version put it in a correlated subquery, which the planner ran once per REQUISITION rather
	// than once per owner: a sequential scan of applications with 996 loops on a small staging dataset, and
	// the loop count grows with the order book rather than with the headcount. Grouping first and joining
	// turns it into one pass over each table.
	rows, err := tx.Query(ctx, `
		WITH pipeline AS (
		    SELECT a.owner_user_id AS uid, count(*)::int AS n
		      FROM applications a
		     WHERE a.stage NOT IN ('placed','rejected','withdrawn')
		     GROUP BY a.owner_user_id
		), openreqs AS (
		    SELECT r.owner_user_id AS uid, count(DISTINCT r.id)::int AS n
		      FROM requisitions r
		     WHERE r.deleted_at IS NULL AND r.status = 'open'
		       AND ($1::uuid[] IS NULL OR r.owner_user_id = ANY($1))
		     GROUP BY r.owner_user_id
		)
		SELECT coalesce(u.id::text, ''), coalesce(u.full_name, 'Unassigned'),
		       openreqs.n, coalesce(pipeline.n, 0)
		  FROM openreqs
		  LEFT JOIN pipeline ON pipeline.uid IS NOT DISTINCT FROM openreqs.uid
		  LEFT JOIN users u ON u.id = openreqs.uid
		 ORDER BY openreqs.n DESC, 2 ASC`, arr)
	if err != nil {
		return nil, fmt.Errorf("recruiting: capacity: %w", err)
	}
	defer rows.Close()
	out := make([]RecruiterLoad, 0, 16)
	for rows.Next() {
		var l RecruiterLoad
		if e := rows.Scan(&l.UserID, &l.Name, &l.OpenRequisitions, &l.ActiveApplications); e != nil {
			return nil, e
		}
		l.Band = loadBand(l.OpenRequisitions)
		out = append(out, l)
	}
	return out, rows.Err()
}

// compare builds one figure against its predecessor.
//
// A PREVIOUS OF ZERO IS NOT A HUNDRED PERCENT RISE. A desk that placed nobody last month and two people this
// month has not improved by infinity, and rendering a percentage there is the most common way a comparison
// becomes a joke. The direction is still reported, because up from nothing is genuinely up.
func compare(now, prev decimal.Decimal, upIsBetter bool) Comparison {
	c := Comparison{Now: now.String(), Previous: prev.String()}
	switch {
	case now.Equal(prev):
		c.Direction, c.Better = "flat", "flat"
	case now.GreaterThan(prev):
		c.Direction = "up"
		c.Better = boolWord(upIsBetter)
	default:
		c.Direction = "down"
		c.Better = boolWord(!upIsBetter)
	}
	return c
}

func boolWord(b bool) string {
	if b {
		return "yes"
	}
	return "no"
}