package recruiting

import (
	"context"
	"encoding/json"
	"errors"
	"strings"
	"time"

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

	"github.com/missionworkforce/missionmatch/internal/platform/audit"
	"github.com/missionworkforce/missionmatch/internal/platform/tenant"
)

const (
	// Migration 0047 permits 300 characters in a requisition title, which is wider than the 200 used for
	// a person's job title, so it needs its own bound rather than borrowing maxTitle.
	maxReqTitle = 300
	maxReqDesc  = 8000
	maxReqNote  = 2000
)

var reqPriorities = map[string]bool{"low": true, "normal": true, "high": true, "critical": true}
var reqEmployment = map[string]bool{"contract": true, "contract_to_hire": true, "direct_hire": true}

// Money and the hold accumulator are cast to text in SQL and parsed into decimal in Go, rather than
// scanned as a driver numeric. It removes the pgx numeric type registration from the equation entirely
// and the round trip is exact: Postgres renders numeric as its full decimal representation and
// decimal.NewFromString reads it back without going near a float64.
const reqSelect = `
	r.id::text, r.client_id::text, cl.name, r.hiring_manager_id::text, cc.full_name,
	r.owner_user_id::text, uo.full_name, r.requirement_id::text,
	r.title, r.description, r.labor_category, r.openings, r.priority, r.employment_type, r.status,
	r.required_level, r.required_poly, r.work_location, r.remote_allowed,
	r.bill_rate_min::text, r.bill_rate_max::text, r.pay_rate_min::text, r.pay_rate_max::text,
	r.salary_min::text, r.salary_max::text,
	r.opened_at, r.target_fill_date, r.closed_at, r.held_seconds::text, r.held_since,
	r.created_at, r.updated_at,
	r.intake_source, r.vms_name, r.vms_requisition_ref, r.submission_deadline,
	r.competing_vendors, r.max_submissions`

const reqFrom = `
	FROM requisitions r
	LEFT JOIN clients        cl ON cl.organization_id = r.organization_id AND cl.id = r.client_id
	LEFT JOIN client_contacts cc ON cc.organization_id = r.organization_id AND cc.id = r.hiring_manager_id
	LEFT JOIN users          uo ON uo.organization_id = r.organization_id AND uo.id = r.owner_user_id`

type scannable interface{ Scan(dest ...any) error }

// scanRequisition reads one row and computes the three clocks.
//
// The ages are derived here rather than in SQL on purpose. Doing it in Go means the arithmetic lives in
// reqAge, which is a pure function with no database, so every edge case is proved by a unit test instead
// of only being reachable through a query. The database supplies facts; the derivation is testable.
func scanRequisition(row scannable, now time.Time) (Requisition, error) {
	var r Requisition
	var clientID, clientName, hmID, hmName, ownerID, ownerName, reqID *string
	var billMin, billMax, payMin, payMax, salMin, salMax *string
	var heldSeconds string
	var openedAt, createdAt, updatedAt time.Time
	var targetFill, closedAt, heldSince *time.Time
	var deadline *time.Time

	if err := row.Scan(
		&r.ID, &clientID, &clientName, &hmID, &hmName,
		&ownerID, &ownerName, &reqID,
		&r.Title, &r.Description, &r.LaborCategory, &r.Openings, &r.Priority, &r.EmploymentType, &r.Status,
		&r.RequiredLevel, &r.RequiredPoly, &r.WorkLocation, &r.RemoteAllowed,
		&billMin, &billMax, &payMin, &payMax, &salMin, &salMax,
		&openedAt, &targetFill, &closedAt, &heldSeconds, &heldSince,
		&createdAt, &updatedAt,
		&r.IntakeSource, &r.VMSName, &r.VMSRequisitionRef, &deadline,
		&r.CompetingVendors, &r.MaxSubmissions,
	); err != nil {
		return Requisition{}, err
	}

	// THE DEADLINE AND THE HOURS TO IT ARE BOTH SERVED. The hours are computed here, from the same clock the
	// rest of the row was read on, because a browser working out urgency from a timestamp is a browser in a
	// different time zone reaching a different answer about whether a submission window is still open.
	if deadline != nil {
		r.SubmissionDeadline = deadline.UTC().Format(time.RFC3339)
		h := deadline.Sub(now).Hours()
		r.HoursToDeadline = &h
	}

	r.ClientID, r.ClientName = str(clientID), str(clientName)
	r.HiringManagerID, r.HiringManagerName = str(hmID), str(hmName)
	r.OwnerUserID, r.OwnerName, r.RequirementID = str(ownerID), str(ownerName), str(reqID)
	r.BillRateMin, r.BillRateMax = dec(billMin), dec(billMax)
	r.PayRateMin, r.PayRateMax = dec(payMin), dec(payMax)
	r.SalaryMin, r.SalaryMax = dec(salMin), dec(salMax)

	r.OpenedAt, r.CreatedAt, r.UpdatedAt = ts(openedAt), ts(createdAt), ts(updatedAt)
	r.TargetFillDate = dateStr(targetFill)
	if closedAt != nil {
		r.ClosedAt = ts(*closedAt)
	}
	if heldSince != nil {
		r.OnHoldSince = ts(*heldSince)
	}

	held, err := decimal.NewFromString(heldSeconds)
	if err != nil {
		return Requisition{}, err
	}
	elapsed, adjusted := reqAge(openedAt, closedAt, held, heldSince, now)
	r.AgeDays = days(elapsed)
	r.WorkingDays = days(adjusted)
	r.HeldDays = days(elapsed.Sub(adjusted))

	if targetFill != nil {
		// Whole days between today and the target, computed on dates rather than instants so a target of
		// today reads as 0 regardless of the hour, and a passed target reads negative rather than as a
		// fraction that rounds to zero and looks on time.
		d := int(targetFill.UTC().Truncate(24*time.Hour).Sub(now.UTC().Truncate(24*time.Hour)).Hours() / 24)
		r.DaysToTarget = &d
	}
	return r, nil
}

func str(p *string) string {
	if p == nil {
		return ""
	}
	return *p
}

func dec(p *string) *decimal.Decimal {
	if p == nil {
		return nil
	}
	d, err := decimal.NewFromString(*p)
	if err != nil {
		return nil
	}
	return &d
}

// ---------------------------------------------------------------------------
// Reads
// ---------------------------------------------------------------------------

// ListRequisitions returns job orders, filtered server side.
//
// Every filter is applied in SQL before the limit. Filtering a page after it has been fetched would show
// a recruiter three of their own orders out of a page of fifty and call it their desk, which is the same
// defect the client list had.
func (s *Service) ListRequisitions(ctx context.Context, clientID, status, ownerFilter, search string, limit, offset int) (RequisitionList, error) {
	if s.db == nil {
		return RequisitionList{}, ErrNoDatabase
	}
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	if offset < 0 {
		offset = 0
	}

	var where []string
	var args []any
	add := func(clause string, v any) {
		args = append(args, v)
		where = append(where, strings.ReplaceAll(clause, "?", "$"+itoa(len(args))))
	}
	where = append(where, "r.deleted_at IS NULL")

	if status != "" {
		// "open" in the ordinary sense of the word means work in progress, which includes an order that
		// is temporarily paused. A recruiter asking for open roles does not mean to exclude the ones on
		// hold, and silently doing so would understate their desk.
		switch status {
		case "active":
			where = append(where, "r.status IN ('open', 'on_hold')")
		default:
			if !reqStatuses[status] {
				return RequisitionList{}, &ValidationError{Field: "status", Message: "is not a permitted status"}
			}
			add("r.status = ?", status)
		}
	}
	if clientID != "" {
		id, err := uuid.Parse(clientID)
		if err != nil {
			return RequisitionList{}, &ValidationError{Field: "client_id", Message: "must be a client id"}
		}
		add("r.client_id = ?", id)
	}
	if search != "" {
		// Both placeholders become the same $N and bind to the one argument, which is what is wanted: the
		// same search term is tested against two columns.
		add("(r.title ILIKE ? OR r.labor_category ILIKE ?)", "%"+search+"%")
	}
	switch ownerFilter {
	case "":
	case "me":
		actor := actorPtr(ctx)
		if actor == nil {
			return RequisitionList{}, tenant.ErrNoTenant
		}
		add("r.owner_user_id = ?", *actor)
	case "unassigned":
		where = append(where, "r.owner_user_id IS NULL")
	default:
		id, err := uuid.Parse(ownerFilter)
		if err != nil {
			// An unrecognised filter is refused rather than ignored. Silently returning everyone's orders
			// when the caller asked for one person's is worse than an error, because it looks like an answer.
			return RequisitionList{}, &ValidationError{Field: "owner", Message: "must be me, unassigned, or a user id"}
		}
		add("r.owner_user_id = ?", id)
	}

	cond := strings.Join(where, " AND ")
	out := RequisitionList{Requisitions: []Requisition{}, Limit: limit, Offset: offset}

	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if err := tx.QueryRow(ctx, `SELECT count(*)`+reqFrom+` WHERE `+cond, args...).Scan(&out.Total); err != nil {
			return translatePG(err)
		}
		// Ordering is total. Priority then age puts the most urgent stale work first, and the id tiebreak
		// makes paging deterministic: without it two rows with identical sort keys can swap between pages
		// and a record is shown twice or skipped.
		q := `SELECT` + reqSelect + reqFrom + ` WHERE ` + cond + `
			ORDER BY CASE r.priority WHEN 'critical' THEN 0 WHEN 'high' THEN 1 WHEN 'normal' THEN 2 ELSE 3 END,
			         r.opened_at ASC, r.id ASC
			LIMIT $` + itoa(len(args)+1) + ` OFFSET $` + itoa(len(args)+2)
		rows, err := tx.Query(ctx, q, append(args, limit, offset)...)
		if err != nil {
			return translatePG(err)
		}
		defer rows.Close()
		now := time.Now().UTC()
		for rows.Next() {
			r, err := scanRequisition(rows, now)
			if err != nil {
				return translatePG(err)
			}
			out.Requisitions = append(out.Requisitions, r)
		}
		return rows.Err()
	})
	if err != nil {
		return RequisitionList{}, err
	}
	return out, nil
}

// GetRequisition returns one order with its status history.
func (s *Service) GetRequisition(ctx context.Context, id uuid.UUID) (RequisitionDetail, error) {
	if s.db == nil {
		return RequisitionDetail{}, ErrNoDatabase
	}
	var out RequisitionDetail
	out.Events = []RequisitionEvent{}
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		now := time.Now().UTC()
		row := tx.QueryRow(ctx, `SELECT`+reqSelect+reqFrom+` WHERE r.id = $1 AND r.deleted_at IS NULL`, id)
		r, err := scanRequisition(row, now)
		if err != nil {
			if errors.Is(err, pgx.ErrNoRows) {
				return ErrNotFound
			}
			return translatePG(err)
		}
		out.Requisition = r

		rows, err := tx.Query(ctx, `
			SELECT e.id::text, e.from_status, e.to_status, e.seconds_in_from::text, e.note,
			       e.actor_user_id::text, NULLIF(e.actor_name, ''), e.occurred_at
			  FROM requisition_events e
			 WHERE e.requisition_id = $1
			 ORDER BY e.occurred_at ASC, e.id ASC`, id)
		if err != nil {
			return translatePG(err)
		}
		defer rows.Close()
		for rows.Next() {
			var ev RequisitionEvent
			var from, secs, actor, actorName *string
			var at time.Time
			if err := rows.Scan(&ev.ID, &from, &ev.ToStatus, &secs, &ev.Note, &actor, &actorName, &at); err != nil {
				return translatePG(err)
			}
			ev.FromStatus, ev.ActorUserID, ev.ActorUserName = str(from), str(actor), str(actorName)
			ev.OccurredAt = ts(at)
			if secs != nil {
				if d, e := decimal.NewFromString(*secs); e == nil {
					ev.DaysInFrom = days(d)
				}
			}
			out.Events = append(out.Events, ev)
		}
		return rows.Err()
	})
	if err != nil {
		return RequisitionDetail{}, err
	}
	return out, nil
}

// ---------------------------------------------------------------------------
// Validation
// ---------------------------------------------------------------------------

// money parses an optional monetary input. An empty string means absent, not zero: a rate that has not
// been agreed and a rate of zero dollars are different facts and conflating them would quietly turn an
// unknown into a giveaway.
func money(field, v string) (*decimal.Decimal, error) {
	v = strings.TrimSpace(v)
	if v == "" {
		return nil, nil
	}
	d, err := decimal.NewFromString(v)
	if err != nil {
		return nil, &ValidationError{Field: field, Message: "must be a number"}
	}
	if d.IsNegative() {
		return nil, &ValidationError{Field: field, Message: "cannot be negative"}
	}
	// numeric(12,4) and numeric(14,2) overflow rather than truncate, so the bound is checked here to
	// produce a named field instead of a database error the caller cannot act on.
	if d.GreaterThan(decimal.NewFromInt(99999999)) {
		return nil, &ValidationError{Field: field, Message: "is larger than this field can hold"}
	}
	return &d, nil
}

// rangeOK refuses a range whose floor exceeds its ceiling. A partially filled range stays legal, which
// is normal while an order is still being scoped.
func rangeOK(field string, lo, hi *decimal.Decimal) error {
	if lo != nil && hi != nil && lo.GreaterThan(*hi) {
		return &ValidationError{Field: field + "_min", Message: "cannot be greater than " + field + "_max"}
	}
	return nil
}

func optDate(field, v string) (*time.Time, error) {
	v = strings.TrimSpace(v)
	if v == "" {
		return nil, nil
	}
	t, err := time.ParseInLocation(dateLayout, v, time.UTC)
	if err != nil {
		return nil, &ValidationError{Field: field, Message: "must be a date as YYYY-MM-DD"}
	}
	return &t, nil
}

func optUUID(field, v string) (*uuid.UUID, error) {
	v = strings.TrimSpace(v)
	if v == "" {
		return nil, nil
	}
	id, err := uuid.Parse(v)
	if err != nil {
		return nil, &ValidationError{Field: field, Message: "must be an id"}
	}
	return &id, nil
}

// ---------------------------------------------------------------------------
// Writes
// ---------------------------------------------------------------------------

// CreateRequisition opens a job order.
//
// The creating recruiter takes ownership. An order that lands in the pool unowned is an order nobody is
// accountable for, and the person who just typed it in is the obvious owner. Releasing it afterwards is
// one click.
func (s *Service) CreateRequisition(ctx context.Context, in CreateRequisitionInput) (Requisition, error) {
	if s.db == nil {
		return Requisition{}, ErrNoDatabase
	}
	title, err := clean("title", in.Title, maxReqTitle)
	if err != nil {
		return Requisition{}, err
	}
	if title == "" {
		return Requisition{}, &ValidationError{Field: "title", Message: "is required"}
	}
	desc, err := clean("description", in.Description, maxReqDesc)
	if err != nil {
		return Requisition{}, err
	}
	if in.Openings == 0 {
		in.Openings = 1
	}
	if in.Openings < 1 || in.Openings > 9999 {
		return Requisition{}, &ValidationError{Field: "openings", Message: "must be between 1 and 9999"}
	}
	if in.Priority == "" {
		in.Priority = "normal"
	}
	if !reqPriorities[in.Priority] {
		return Requisition{}, &ValidationError{Field: "priority", Message: "is not a permitted value"}
	}
	if in.EmploymentType == "" {
		in.EmploymentType = "contract"
	}
	if !reqEmployment[in.EmploymentType] {
		return Requisition{}, &ValidationError{Field: "employment_type", Message: "is not a permitted value"}
	}
	// A new order may only start in draft or open. The other four describe an order that has already
	// ended, and creating one directly in a terminal state would demand a closed_at that never had a
	// corresponding open, corrupting every duration derived from it.
	if in.Status == "" {
		in.Status = reqOpen
	}
	if in.Status != reqDraft && in.Status != reqOpen {
		return Requisition{}, &ValidationError{Field: "status", Message: "a new requisition starts as draft or open"}
	}
	if in.RequiredLevel == "" {
		in.RequiredLevel = "none"
	}
	if in.RequiredPoly == "" {
		in.RequiredPoly = "none"
	}

	clientID, err := optUUID("client_id", in.ClientID)
	if err != nil {
		return Requisition{}, err
	}
	hmID, err := optUUID("hiring_manager_id", in.HiringManagerID)
	if err != nil {
		return Requisition{}, err
	}
	if hmID != nil && clientID == nil {
		return Requisition{}, &ValidationError{
			Field:   "hiring_manager_id",
			Message: "a hiring manager belongs to a client, so name the client too",
		}
	}
	reqID, err := optUUID("requirement_id", in.RequirementID)
	if err != nil {
		return Requisition{}, err
	}
	target, err := optDate("target_fill_date", in.TargetFillDate)
	if err != nil {
		return Requisition{}, err
	}
	// The deadline is a TIMESTAMP, not a date: a VMS requisition closes at a stated time of day, and rounding
	// it to a day would either give a desk an extra afternoon it does not have or take one it does.
	deadline, err := optTime("submission_deadline", in.SubmissionDeadline)
	if err != nil {
		return Requisition{}, err
	}
	if target != nil && target.Before(time.Now().UTC().Truncate(24*time.Hour)) {
		return Requisition{}, &ValidationError{Field: "target_fill_date", Message: "cannot be in the past"}
	}

	rates, err := parseRates(in.BillRateMin, in.BillRateMax, in.PayRateMin, in.PayRateMax, in.SalaryMin, in.SalaryMax)
	if err != nil {
		return Requisition{}, err
	}

	actor := actorPtr(ctx)
	if actor == nil {
		return Requisition{}, tenant.ErrNoTenant
	}

	// Scanned as a uuid.UUID, not as text and parsed back. See CreateApplication for the reasoning: a
	// database returned id must never be re parsed with uuid.MustParse in a request path, because the
	// only way that parse can fail is by panicking on a caller who did nothing wrong.
	var newID uuid.UUID
	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		err := tx.QueryRow(ctx, `
			INSERT INTO requisitions (
			  organization_id, client_id, hiring_manager_id, requirement_id, owner_user_id, created_by,
			  title, description, labor_category, openings, priority, employment_type, status,
			  required_level, required_poly, work_location, remote_allowed,
			  bill_rate_min, bill_rate_max, pay_rate_min, pay_rate_max, salary_min, salary_max,
			  target_fill_date,
			  intake_source, vms_name, vms_requisition_ref, submission_deadline,
			  competing_vendors, max_submissions)
			VALUES (
			  NULLIF(current_setting('app.current_org_id', true), '')::uuid,
			  $1, $2, $3, $4, $4,
			  $5, $6, $7, $8, $9, $10, $11,
			  $12, $13, $14, $15,
			  $16, $17, $18, $19, $20, $21,
			  $22,
			  coalesce(nullif($23,''),'direct'), $24, $25, $26, $27, $28)
			RETURNING id`,
			clientID, hmID, reqID, actor,
			title, desc, strings.TrimSpace(in.LaborCategory), in.Openings, in.Priority, in.EmploymentType, in.Status,
			in.RequiredLevel, in.RequiredPoly, strings.TrimSpace(in.WorkLocation), in.RemoteAllowed,
			rates[0], rates[1], rates[2], rates[3], rates[4], rates[5],
			target,
			in.IntakeSource, strings.TrimSpace(in.VMSName), strings.TrimSpace(in.VMSRequisitionRef),
			deadline, in.CompetingVendors, in.MaxSubmissions).Scan(&newID)
		if err != nil {
			return translatePG(err)
		}
		// The opening event. Every later duration is measured from a predecessor, so the chain needs a
		// first link or the first real transition would have nothing to close out.
		if err := insertReqEvent(ctx, tx, newID, "", in.Status, nil, "opened", actor); err != nil {
			return err
		}
		nv, _ := json.Marshal(map[string]any{"id": newID.String(), "title": title, "status": in.Status})
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "requisition.created", ActorUserID: actor,
			EntityType: "requisition", EntityID: newID.String(), NewValues: nv,
		})
	})
	if err != nil {
		return Requisition{}, err
	}
	// Re read so the response carries the resolved client and owner names and the computed clocks, and
	// therefore has exactly the same shape a list or a get returns.
	return s.requisitionRow(ctx, newID)
}

func parseRates(bMin, bMax, pMin, pMax, sMin, sMax string) ([6]*decimal.Decimal, error) {
	var out [6]*decimal.Decimal
	fields := [6]string{"bill_rate_min", "bill_rate_max", "pay_rate_min", "pay_rate_max", "salary_min", "salary_max"}
	vals := [6]string{bMin, bMax, pMin, pMax, sMin, sMax}
	for i := range vals {
		d, err := money(fields[i], vals[i])
		if err != nil {
			return out, err
		}
		out[i] = d
	}
	if err := rangeOK("bill_rate", out[0], out[1]); err != nil {
		return out, err
	}
	if err := rangeOK("pay_rate", out[2], out[3]); err != nil {
		return out, err
	}
	if err := rangeOK("salary", out[4], out[5]); err != nil {
		return out, err
	}
	return out, nil
}

// UpdateRequisition applies a partial update under optimistic concurrency and the ownership lock.
func (s *Service) UpdateRequisition(ctx context.Context, id uuid.UUID, in UpdateRequisitionInput) (Requisition, error) {
	if s.db == nil {
		return Requisition{}, ErrNoDatabase
	}
	expected, err := parseToken(in.ExpectedUpdatedAt)
	if err != nil {
		return Requisition{}, err
	}
	if in.Title != nil {
		t, e := clean("title", *in.Title, maxReqTitle)
		if e != nil {
			return Requisition{}, e
		}
		if t == "" {
			return Requisition{}, &ValidationError{Field: "title", Message: "is required"}
		}
		in.Title = &t
	}
	if in.Openings != nil && (*in.Openings < 1 || *in.Openings > 9999) {
		return Requisition{}, &ValidationError{Field: "openings", Message: "must be between 1 and 9999"}
	}
	if in.Priority != nil && !reqPriorities[*in.Priority] {
		return Requisition{}, &ValidationError{Field: "priority", Message: "is not a permitted value"}
	}
	if in.EmploymentType != nil && !reqEmployment[*in.EmploymentType] {
		return Requisition{}, &ValidationError{Field: "employment_type", Message: "is not a permitted value"}
	}

	var clientID, hmID *uuid.UUID
	if in.ClientID != nil {
		if clientID, err = optUUID("client_id", *in.ClientID); err != nil {
			return Requisition{}, err
		}
	}
	if in.HiringManagerID != nil {
		if hmID, err = optUUID("hiring_manager_id", *in.HiringManagerID); err != nil {
			return Requisition{}, err
		}
	}
	var target *time.Time
	if in.TargetFillDate != nil {
		if target, err = optDate("target_fill_date", *in.TargetFillDate); err != nil {
			return Requisition{}, err
		}
	}

	// Each rate is validated individually here. A range where only one end is being changed can only be
	// checked against the stored other end, so the database CHECK is the authority and translatePG turns
	// its failure into a named field.
	rate := map[string]*decimal.Decimal{}
	for field, v := range map[string]*string{
		"bill_rate_min": in.BillRateMin, "bill_rate_max": in.BillRateMax,
		"pay_rate_min": in.PayRateMin, "pay_rate_max": in.PayRateMax,
		"salary_min": in.SalaryMin, "salary_max": in.SalaryMax,
	} {
		if v == nil {
			continue
		}
		d, e := money(field, *v)
		if e != nil {
			return Requisition{}, e
		}
		rate[field] = d
	}

	var out Requisition
	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		claim, err := assertCanEditReq(ctx, tx, id)
		if err != nil {
			return err
		}
		if claim {
			if err := s.setReqOwner(ctx, tx, id, actorPtr(ctx), "requisition.claimed"); err != nil {
				return err
			}
		}
		// COALESCE handles present and absent uniformly for the scalar fields. The nullable reference and
		// money fields cannot use it, because for those an explicit null is a meaningful instruction to
		// clear the value, so each carries a separate boolean saying whether it was supplied at all.
		tag, err := tx.Exec(ctx, `
			UPDATE requisitions SET
			  client_id         = CASE WHEN $2  THEN $3  ELSE client_id         END,
			  hiring_manager_id = CASE WHEN $4  THEN $5  ELSE hiring_manager_id END,
			  title             = COALESCE($6, title),
			  description       = COALESCE($7, description),
			  labor_category    = COALESCE($8, labor_category),
			  openings          = COALESCE($9, openings),
			  priority          = COALESCE($10, priority),
			  employment_type   = COALESCE($11, employment_type),
			  required_level    = COALESCE($12, required_level),
			  required_poly     = COALESCE($13, required_poly),
			  work_location     = COALESCE($14, work_location),
			  remote_allowed    = COALESCE($15, remote_allowed),
			  bill_rate_min     = CASE WHEN $16 THEN $17 ELSE bill_rate_min END,
			  bill_rate_max     = CASE WHEN $18 THEN $19 ELSE bill_rate_max END,
			  pay_rate_min      = CASE WHEN $20 THEN $21 ELSE pay_rate_min  END,
			  pay_rate_max      = CASE WHEN $22 THEN $23 ELSE pay_rate_max  END,
			  salary_min        = CASE WHEN $24 THEN $25 ELSE salary_min    END,
			  salary_max        = CASE WHEN $26 THEN $27 ELSE salary_max    END,
			  target_fill_date  = CASE WHEN $28 THEN $29 ELSE target_fill_date END,
			  updated_at = now()
			WHERE id = $1 AND deleted_at IS NULL AND ($30::timestamptz IS NULL OR updated_at = $30)`,
			id,
			in.ClientID != nil, clientID,
			in.HiringManagerID != nil, hmID,
			in.Title, in.Description, in.LaborCategory, in.Openings, in.Priority, in.EmploymentType,
			in.RequiredLevel, in.RequiredPoly, in.WorkLocation, in.RemoteAllowed,
			in.BillRateMin != nil, rate["bill_rate_min"],
			in.BillRateMax != nil, rate["bill_rate_max"],
			in.PayRateMin != nil, rate["pay_rate_min"],
			in.PayRateMax != nil, rate["pay_rate_max"],
			in.SalaryMin != nil, rate["salary_min"],
			in.SalaryMax != nil, rate["salary_max"],
			in.TargetFillDate != nil, target,
			tokenOrNil(expected, claim))
		if err != nil {
			return translatePG(err)
		}
		if tag.RowsAffected() == 0 {
			var exists bool
			if e := tx.QueryRow(ctx, `SELECT true FROM requisitions WHERE id = $1 AND deleted_at IS NULL`, id).Scan(&exists); e != nil {
				return ErrNotFound
			}
			return ErrConflict
		}
		nv, _ := json.Marshal(in)
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "requisition.updated", ActorUserID: actorPtr(ctx),
			EntityType: "requisition", EntityID: id.String(), NewValues: nv,
		})
	})
	if err != nil {
		return Requisition{}, err
	}
	out, err = s.requisitionRow(ctx, id)
	return out, err
}

// ChangeStatus moves a job order through its lifecycle.
//
// This is the only path that writes status, and it does five things in one transaction: locks the row,
// consults the transition graph, moves the clocks the transition implies, records the event with the
// time spent in the state being left, and audits it. Splitting any of that out would allow a crash to
// leave the status changed with the clock unmoved, which would corrupt every duration derived from it
// with no way to detect it afterwards.
func (s *Service) ChangeStatus(ctx context.Context, id uuid.UUID, in ChangeStatusInput) (Requisition, error) {
	if s.db == nil {
		return Requisition{}, ErrNoDatabase
	}
	expected, err := parseToken(in.ExpectedUpdatedAt)
	if err != nil {
		return Requisition{}, err
	}
	note, err := clean("note", in.Note, maxReqNote)
	if err != nil {
		return Requisition{}, err
	}

	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		claim, err := assertCanEditReq(ctx, tx, id)
		if err != nil {
			return err
		}
		if claim {
			if err := s.setReqOwner(ctx, tx, id, actorPtr(ctx), "requisition.claimed"); err != nil {
				return err
			}
		}

		// The row is already locked FOR UPDATE by assertCanEditReq, so the status read here cannot be
		// stale and two concurrent transitions are serialized rather than both computing from the same
		// starting state.
		var from string
		var updatedAt time.Time
		if err := tx.QueryRow(ctx,
			`SELECT status, updated_at FROM requisitions WHERE id = $1 AND deleted_at IS NULL`, id).
			Scan(&from, &updatedAt); err != nil {
			if errors.Is(err, pgx.ErrNoRows) {
				return ErrNotFound
			}
			return translatePG(err)
		}
		if !claim && !expected.IsZero() && !updatedAt.Equal(expected) {
			return ErrConflict
		}

		effect, err := transitionFor(from, in.Status)
		if err != nil {
			return err
		}
		if effect.NoOp {
			return nil
		}

		// One statement moves status and all three clocks together, so no intermediate state exists in
		// which the database constraints could be violated. seconds_in_from is captured from the same
		// now() the update uses, so the event and the row cannot disagree about when this happened.
		var secondsInFrom string
		err = tx.QueryRow(ctx, `
			UPDATE requisitions SET
			  status       = $2,
			  closed_at    = CASE WHEN $3 THEN now() WHEN $4 THEN NULL ELSE closed_at END,
			  held_seconds = CASE WHEN $5 AND held_since IS NOT NULL
			                      THEN held_seconds + EXTRACT(EPOCH FROM (now() - held_since))
			                      ELSE held_seconds END,
			  held_since   = CASE WHEN $6 THEN now() WHEN $5 THEN NULL ELSE held_since END,
			  updated_at   = now()
			WHERE id = $1 AND deleted_at IS NULL
			RETURNING EXTRACT(EPOCH FROM (now() - COALESCE(
			    (SELECT max(occurred_at) FROM requisition_events WHERE requisition_id = $1), created_at)))::text`,
			id, in.Status, effect.SetClosedAt, effect.ClearClosedAt, effect.SettleHold, effect.StartHold).
			Scan(&secondsInFrom)
		if err != nil {
			return translatePG(err)
		}

		if err := insertReqEvent(ctx, tx, id, from, in.Status, &secondsInFrom, note, actorPtr(ctx)); err != nil {
			return err
		}
		nv, _ := json.Marshal(map[string]string{"from": from, "to": in.Status, "note": note})
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "requisition.status_changed", ActorUserID: actorPtr(ctx),
			EntityType: "requisition", EntityID: id.String(), NewValues: nv,
		})
	})
	if err != nil {
		return Requisition{}, err
	}
	return s.requisitionRow(ctx, id)
}

// insertReqEvent appends to the history. from empty means this is the first link in the chain.
func insertReqEvent(ctx context.Context, tx pgx.Tx, id uuid.UUID, from, to string, seconds *string, note string, actor *uuid.UUID) error {
	var fromArg any
	if from != "" {
		fromArg = from
	}
	var secArg any
	if from != "" && seconds != nil {
		secArg = *seconds
	}
	// actor_name is resolved and stored at write time, not joined at read time. The foreign key nulls
	// actor_user_id when a recruiter is deleted, so a joined name would turn their entire history into
	// "unknown" the day they leave. What the history should record is who acted then.
	_, err := tx.Exec(ctx, `
		INSERT INTO requisition_events
		  (organization_id, requisition_id, from_status, to_status, seconds_in_from, note,
		   actor_user_id, actor_name)
		VALUES (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1, $2, $3, $4::numeric, $5, $6,
		        COALESCE((SELECT full_name FROM users WHERE id = $6), ''))`,
		id, fromArg, to, secArg, note, actor)
	if err != nil {
		return translatePG(err)
	}
	return nil
}

// requisitionRow re reads a single order so every write returns the same shape a read does.
func (s *Service) requisitionRow(ctx context.Context, id uuid.UUID) (Requisition, error) {
	var out Requisition
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		row := tx.QueryRow(ctx, `SELECT`+reqSelect+reqFrom+` WHERE r.id = $1 AND r.deleted_at IS NULL`, id)
		r, err := scanRequisition(row, time.Now().UTC())
		if err != nil {
			if errors.Is(err, pgx.ErrNoRows) {
				return ErrNotFound
			}
			return translatePG(err)
		}
		out = r
		return nil
	})
	return out, err
}

// ---------------------------------------------------------------------------
// Ownership, the same rule clients use
// ---------------------------------------------------------------------------

func loadReqOwner(ctx context.Context, tx pgx.Tx, id uuid.UUID) (owner, error) {
	o := owner{entity: "job order"}
	var name *string
	err := tx.QueryRow(ctx, `
		SELECT r.owner_user_id, u.full_name
		  FROM requisitions r
		  LEFT JOIN users u ON u.organization_id = r.organization_id AND u.id = r.owner_user_id
		 WHERE r.id = $1 AND r.deleted_at IS NULL
		 FOR UPDATE OF r`, id).Scan(&o.userID, &name)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return owner{}, ErrNotFound
		}
		return owner{}, translatePG(err)
	}
	if name != nil {
		o.name = *name
	}
	return o, nil
}

func assertCanEditReq(ctx context.Context, tx pgx.Tx, id uuid.UUID) (bool, error) {
	o, err := loadReqOwner(ctx, tx, id)
	if err != nil {
		return false, err
	}
	actor := actorPtr(ctx)
	if actor == nil {
		return false, tenant.ErrNoTenant
	}
	return ownershipDecision(o, actor, canOverride(ctx))
}

func (s *Service) setReqOwner(ctx context.Context, tx pgx.Tx, id uuid.UUID, to *uuid.UUID, action string) error {
	if _, err := tx.Exec(ctx,
		`UPDATE requisitions SET owner_user_id = $2, updated_at = now() WHERE id = $1`, id, to); err != nil {
		return translatePG(err)
	}
	return s.auditOwnership(ctx, tx, "requisition", id, to, action)
}

// ClaimRequisition assigns an unclaimed job order to the calling recruiter.
func (s *Service) ClaimRequisition(ctx context.Context, id uuid.UUID) (Requisition, error) {
	if s.db == nil {
		return Requisition{}, ErrNoDatabase
	}
	actor := actorPtr(ctx)
	if actor == nil {
		return Requisition{}, tenant.ErrNoTenant
	}
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		o, err := loadReqOwner(ctx, tx, id)
		if err != nil {
			return err
		}
		if o.userID != nil {
			if *o.userID == *actor {
				return nil // already held by the caller
			}
			if !canOverride(ctx) {
				return &ErrNotOwner{OwnerUserID: o.userID.String(), OwnerName: o.name, Entity: o.entity}
			}
		}
		return s.setReqOwner(ctx, tx, id, actor, "requisition.claimed")
	})
	if err != nil {
		return Requisition{}, err
	}
	return s.requisitionRow(ctx, id)
}

// ReleaseRequisition returns a job order to the unassigned pool.
func (s *Service) ReleaseRequisition(ctx context.Context, id uuid.UUID) (Requisition, error) {
	if s.db == nil {
		return Requisition{}, ErrNoDatabase
	}
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if _, err := assertCanEditReq(ctx, tx, id); err != nil {
			return err
		}
		return s.setReqOwner(ctx, tx, id, nil, "requisition.released")
	})
	if err != nil {
		return Requisition{}, err
	}
	return s.requisitionRow(ctx, id)
}