package recruiting

import (
	"context"
	"fmt"

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

// DESK PERFORMANCE: what a recruiter PRODUCED, as distinct from what they DID.
//
// The analytics report already carries time to offer, time to start, worked time to start, fill rate, aging,
// the funnel, stage to stage conversion, source effectiveness, and per recruiter effort. Effort is calls
// logged and connect rate: it measures activity, which is honest and is not the same question as outcome. A
// desk that logs two hundred calls and places nobody and a desk that logs forty and places four look the same
// in an effort report.
//
// Three things were genuinely missing from the industry standard set, and all three are here:
//   placements per recruiter, the outcome half of the effort report;
//   the REDEPLOYMENT RATE, which nothing could compute until the redeployment arm existed;
//   and MARGIN, which nothing could compute honestly until the wrap rate landed in migration 0098.

// DeskOutcome is one recruiter's production over the window. Submissions and placements are counted from the
// stage event history rather than the current stage, so a candidate who was submitted and later rejected
// still counts as a submission: the work happened.
type DeskOutcome struct {
	UserID      string `json:"user_id,omitempty"`
	Name        string `json:"name"`
	Submissions int    `json:"submissions"`
	Placements  int    `json:"placements"`
	// PlacementRate is placements as a share of submissions, absent rather than zero when nothing was
	// submitted, because a zero rate on no attempts claims a failure that did not happen. This is the same
	// rule RecruiterEffort already applies to its connect rate.
	PlacementRate string `json:"placement_rate,omitempty"`
}

// RedeploymentRate is the share of contractors finishing an assignment in the window who were placed again.
//
// IT IS THE ONE STAFFING METRIC THAT MEASURES WHETHER A FIRM KEEPS WHAT IT ALREADY WON. A cleared contractor
// rolling off is the cheapest placement available: no sourcing, no clearance risk, a client who already knows
// them. Ended is the denominator and Redeployed the numerator.
type RedeploymentRate struct {
	Ended      int    `json:"ended"`
	Redeployed int    `json:"redeployed"`
	Rate       string `json:"rate,omitempty"`
	// Window names the days allowed between one assignment ending and the next starting, because a rate with
	// no window is not comparable to anything, including itself last quarter.
	WindowDays int `json:"window_days"`
}

// redeployGraceDays is how long after an assignment ends a new one still counts as a redeployment. Sixty days
// is a contract staffing quarter: long enough that a gap over a holiday or a slow contract action still counts
// as keeping the person, short enough that it is not simply "they came back eventually".
const redeployGraceDays = 60

const deskOutcomesSQL = `
WITH subs AS (
    SELECT a.owner_user_id AS uid, count(DISTINCT a.id) AS n
    FROM applications a
    JOIN application_stage_events e
      ON e.organization_id = a.organization_id AND e.application_id = a.id
    WHERE e.to_stage = 'submitted' AND e.occurred_at >= $1
    GROUP BY a.owner_user_id
), plcs AS (
    SELECT a.owner_user_id AS uid, count(DISTINCT p.id) AS n
    FROM placements p
    JOIN applications a ON a.organization_id = p.organization_id AND a.id = p.application_id
    WHERE p.created_at >= $1
    GROUP BY a.owner_user_id
)
-- THE KEY SET IS BUILT WITH A UNION, NOT A FULL OUTER JOIN.
--
-- The first version joined the two sides with FULL OUTER JOIN ... ON plcs.uid IS NOT DISTINCT FROM subs.uid,
-- which Postgres refuses outright: "FULL JOIN is only supported with merge-joinable or hash-joinable join
-- conditions". IS NOT DISTINCT FROM is neither. The whole analytics endpoint answered 500, and every Go test
-- stayed green because none of them touches a database. Only running the screen found it.
--
-- IS NOT DISTINCT FROM is still needed, because a NULL owner is a real group: work nobody owns is reported as
-- Unassigned rather than dropped. It is legal on a LEFT JOIN, which plans as a nested loop, and UNION already
-- treats two NULLs as the same key, so the key set collapses them correctly.
, keys AS (
    SELECT uid FROM subs
    UNION
    SELECT uid FROM plcs
)
SELECT
    coalesce(u.id::text, '')            AS user_id,
    coalesce(u.full_name, 'Unassigned') AS name,
    coalesce(subs.n, 0)::int            AS submissions,
    coalesce(plcs.n, 0)::int            AS placements
FROM keys
LEFT JOIN subs ON subs.uid IS NOT DISTINCT FROM keys.uid
LEFT JOIN plcs ON plcs.uid IS NOT DISTINCT FROM keys.uid
LEFT JOIN users u ON u.id = keys.uid
ORDER BY placements DESC, submissions DESC, name ASC`

// redeploymentRateSQL counts contractors whose assignment ENDED inside the window, and how many of them
// started another one within the grace period.
//
// A person is counted ONCE however many assignments they ended, because the question is about people kept,
// not rows closed. $1 is the window start, $2 the grace period in days.
const redeploymentRateSQL = `
WITH ended AS (
    SELECT DISTINCT a.candidate_id AS cid, max(p.end_date) AS last_end
    FROM placements p
    JOIN applications a ON a.organization_id = p.organization_id AND a.id = p.application_id
    WHERE p.status IN ('completed', 'terminated')
      AND p.end_date IS NOT NULL
      AND p.end_date >= $1
    GROUP BY a.candidate_id
)
SELECT
    count(*)::int AS ended,
    count(*) FILTER (WHERE EXISTS (
        SELECT 1
        FROM placements nxt
        JOIN applications na ON na.organization_id = nxt.organization_id AND na.id = nxt.application_id
        WHERE na.candidate_id = ended.cid
          AND nxt.start_date >= ended.last_end
          AND nxt.start_date <= ended.last_end + make_interval(days => $2)
    ))::int AS redeployed
FROM ended`

func (s *Service) readDeskOutcomes(ctx context.Context, tx pgx.Tx, since any) ([]DeskOutcome, error) {
	rows, err := tx.Query(ctx, deskOutcomesSQL, since)
	if err != nil {
		return nil, fmt.Errorf("recruiting: desk outcomes: %w", err)
	}
	defer rows.Close()
	out := make([]DeskOutcome, 0, 16)
	for rows.Next() {
		var d DeskOutcome
		if e := rows.Scan(&d.UserID, &d.Name, &d.Submissions, &d.Placements); e != nil {
			return nil, fmt.Errorf("recruiting: scan desk outcome: %w", e)
		}
		if d.Submissions > 0 {
			r := decimal.NewFromInt(int64(d.Placements)).
				Div(decimal.NewFromInt(int64(d.Submissions))).
				Mul(decimal.NewFromInt(100)).Round(2)
			d.PlacementRate = r.String()
		}
		out = append(out, d)
	}
	return out, rows.Err()
}

func (s *Service) readRedeploymentRate(ctx context.Context, tx pgx.Tx, since any) (RedeploymentRate, error) {
	var r RedeploymentRate
	r.WindowDays = redeployGraceDays
	if err := tx.QueryRow(ctx, redeploymentRateSQL, since, redeployGraceDays).
		Scan(&r.Ended, &r.Redeployed); err != nil {
		return r, fmt.Errorf("recruiting: redeployment rate: %w", err)
	}
	if r.Ended > 0 {
		r.Rate = decimal.NewFromInt(int64(r.Redeployed)).
			Div(decimal.NewFromInt(int64(r.Ended))).
			Mul(decimal.NewFromInt(100)).Round(2).String()
	}
	return r, nil
}