// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.31.1
// source: candidates.sql

package dbgen

import (
	"context"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgtype"
	pgvector "github.com/pgvector/pgvector-go"
)

const addCandidateCertification = `-- name: AddCandidateCertification :exec
INSERT INTO candidate_certifications (organization_id, candidate_id, name)
VALUES ($1,$2,$3)
ON CONFLICT (candidate_id, name) DO NOTHING
`

type AddCandidateCertificationParams struct {
	OrganizationID uuid.UUID `json:"organization_id"`
	CandidateID    uuid.UUID `json:"candidate_id"`
	Name           string    `json:"name"`
}

func (q *Queries) AddCandidateCertification(ctx context.Context, arg AddCandidateCertificationParams) error {
	_, err := q.db.Exec(ctx, addCandidateCertification, arg.OrganizationID, arg.CandidateID, arg.Name)
	return err
}

const addCandidateCustomerExperience = `-- name: AddCandidateCustomerExperience :exec
INSERT INTO candidate_customer_experience (organization_id, candidate_id, agency)
VALUES ($1,$2,$3)
ON CONFLICT (candidate_id, agency) DO NOTHING
`

type AddCandidateCustomerExperienceParams struct {
	OrganizationID uuid.UUID `json:"organization_id"`
	CandidateID    uuid.UUID `json:"candidate_id"`
	Agency         string    `json:"agency"`
}

func (q *Queries) AddCandidateCustomerExperience(ctx context.Context, arg AddCandidateCustomerExperienceParams) error {
	_, err := q.db.Exec(ctx, addCandidateCustomerExperience, arg.OrganizationID, arg.CandidateID, arg.Agency)
	return err
}

const addCandidateSkill = `-- name: AddCandidateSkill :exec
INSERT INTO candidate_skills (organization_id, candidate_id, skill_id)
VALUES ($1,$2,$3)
ON CONFLICT (candidate_id, skill_id) DO NOTHING
`

type AddCandidateSkillParams struct {
	OrganizationID uuid.UUID `json:"organization_id"`
	CandidateID    uuid.UUID `json:"candidate_id"`
	SkillID        uuid.UUID `json:"skill_id"`
}

func (q *Queries) AddCandidateSkill(ctx context.Context, arg AddCandidateSkillParams) error {
	_, err := q.db.Exec(ctx, addCandidateSkill, arg.OrganizationID, arg.CandidateID, arg.SkillID)
	return err
}

const anonymizeCandidate = `-- name: AnonymizeCandidate :execrows
UPDATE candidates SET
    full_name        = '[erased]',
    email            = NULL,
    headline         = NULL,
    location         = NULL,
    resume_embedding = NULL,
    external_id      = NULL,
    available        = false,
    anonymized_at    = now(),
    updated_at       = now()
WHERE id = $1 AND anonymized_at IS NULL
`

// Right to erasure as anonymize in place: wipe the identifying PII while KEEPING the row so the de
// identified scored outcomes (slate_members) and the demographic bucket survive for the adverse impact
// record. Once the name, email, contact, and resume vector are gone the row is no longer personal data,
// so erasure is satisfied and the compliance history is preserved. Idempotent on anonymized_at.
func (q *Queries) AnonymizeCandidate(ctx context.Context, id uuid.UUID) (int64, error) {
	result, err := q.db.Exec(ctx, anonymizeCandidate, id)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const countCandidateMissionTags = `-- name: CountCandidateMissionTags :one
SELECT count(*) FROM candidate_mission_tags
`

// A tiny RLS scoped probe: how many candidate_mission_tags rows exist for the caller's tenant. The WS7
// retrieval branch uses it to decide between the structured tagged query (when the tenant has been backfilled
// by the tagging pass) and the WS6 text filter fallback (when it has not), so a not yet tagged tenant keeps
// exact WS6 behavior. RLS scopes the count to the caller's org, so it never observes another tenant's tags.
func (q *Queries) CountCandidateMissionTags(ctx context.Context) (int64, error) {
	row := q.db.QueryRow(ctx, countCandidateMissionTags)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const createCandidate = `-- name: CreateCandidate :one
INSERT INTO candidates (
    organization_id, full_name, email, headline,
    clearance_level, clearance_poly, clearance_status,
    years_experience, location, remote, relocatable, available, resume_embedding
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
RETURNING id, organization_id, full_name, email, headline, clearance_level, clearance_poly, clearance_status, clearance_granted_at, clearance_expires_at, reinvestigation_due_at, years_experience, location, remote, relocatable, available, resume_embedding, search_tsv, created_at, updated_at, deleted_at, source, external_id, anonymized_at, clearance_provenance, clearance_verified_at, clearance_compartments, source_first_touch, source_last_touch, source_detail
`

type CreateCandidateParams struct {
	OrganizationID  uuid.UUID        `json:"organization_id"`
	FullName        string           `json:"full_name"`
	Email           *string          `json:"email"`
	Headline        *string          `json:"headline"`
	ClearanceLevel  string           `json:"clearance_level"`
	ClearancePoly   string           `json:"clearance_poly"`
	ClearanceStatus string           `json:"clearance_status"`
	YearsExperience int32            `json:"years_experience"`
	Location        *string          `json:"location"`
	Remote          bool             `json:"remote"`
	Relocatable     bool             `json:"relocatable"`
	Available       bool             `json:"available"`
	ResumeEmbedding *pgvector.Vector `json:"resume_embedding"`
}

func (q *Queries) CreateCandidate(ctx context.Context, arg CreateCandidateParams) (Candidate, error) {
	row := q.db.QueryRow(ctx, createCandidate,
		arg.OrganizationID,
		arg.FullName,
		arg.Email,
		arg.Headline,
		arg.ClearanceLevel,
		arg.ClearancePoly,
		arg.ClearanceStatus,
		arg.YearsExperience,
		arg.Location,
		arg.Remote,
		arg.Relocatable,
		arg.Available,
		arg.ResumeEmbedding,
	)
	var i Candidate
	err := row.Scan(
		&i.ID,
		&i.OrganizationID,
		&i.FullName,
		&i.Email,
		&i.Headline,
		&i.ClearanceLevel,
		&i.ClearancePoly,
		&i.ClearanceStatus,
		&i.ClearanceGrantedAt,
		&i.ClearanceExpiresAt,
		&i.ReinvestigationDueAt,
		&i.YearsExperience,
		&i.Location,
		&i.Remote,
		&i.Relocatable,
		&i.Available,
		&i.ResumeEmbedding,
		&i.SearchTsv,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.DeletedAt,
		&i.Source,
		&i.ExternalID,
		&i.AnonymizedAt,
		&i.ClearanceProvenance,
		&i.ClearanceVerifiedAt,
		&i.ClearanceCompartments,
		&i.SourceFirstTouch,
		&i.SourceLastTouch,
		&i.SourceDetail,
	)
	return i, err
}

const findCandidateByEmail = `-- name: FindCandidateByEmail :one
SELECT id FROM candidates
WHERE organization_id = $1 AND deleted_at IS NULL AND email IS NOT NULL AND lower(email) = lower($2)
ORDER BY created_at LIMIT 1
`

type FindCandidateByEmailParams struct {
	OrganizationID uuid.UUID `json:"organization_id"`
	Lower          string    `json:"lower"`
}

// Identity resolution blocking key 1: an exact (case insensitive) email match in the same org. Email is
// the strongest identity signal, so a sourced lead with a known email merges into the existing person.
func (q *Queries) FindCandidateByEmail(ctx context.Context, arg FindCandidateByEmailParams) (uuid.UUID, error) {
	row := q.db.QueryRow(ctx, findCandidateByEmail, arg.OrganizationID, arg.Lower)
	var id uuid.UUID
	err := row.Scan(&id)
	return id, err
}

const findCandidateByNameLocation = `-- name: FindCandidateByNameLocation :one
SELECT id FROM candidates
WHERE organization_id = $1 AND deleted_at IS NULL
  AND lower(full_name) = lower($2) AND lower(COALESCE(location, '')) = lower($3)
ORDER BY created_at LIMIT 1
`

type FindCandidateByNameLocationParams struct {
	OrganizationID uuid.UUID `json:"organization_id"`
	Lower          string    `json:"lower"`
	Lower_2        string    `json:"lower_2"`
}

// Identity resolution blocking key 2: same normalized full name AND same normalized location in the org.
// Name alone is too weak (two John Smiths); name plus location is a defensible second signal. The caller
// only invokes this with a non empty location.
func (q *Queries) FindCandidateByNameLocation(ctx context.Context, arg FindCandidateByNameLocationParams) (uuid.UUID, error) {
	row := q.db.QueryRow(ctx, findCandidateByNameLocation, arg.OrganizationID, arg.Lower, arg.Lower_2)
	var id uuid.UUID
	err := row.Scan(&id)
	return id, err
}

const getCandidate = `-- name: GetCandidate :one
SELECT id, organization_id, full_name, email, headline, clearance_level, clearance_poly, clearance_status, clearance_granted_at, clearance_expires_at, reinvestigation_due_at, years_experience, location, remote, relocatable, available, resume_embedding, search_tsv, created_at, updated_at, deleted_at, source, external_id, anonymized_at, clearance_provenance, clearance_verified_at, clearance_compartments, source_first_touch, source_last_touch, source_detail FROM candidates WHERE id = $1 AND deleted_at IS NULL
`

func (q *Queries) GetCandidate(ctx context.Context, id uuid.UUID) (Candidate, error) {
	row := q.db.QueryRow(ctx, getCandidate, id)
	var i Candidate
	err := row.Scan(
		&i.ID,
		&i.OrganizationID,
		&i.FullName,
		&i.Email,
		&i.Headline,
		&i.ClearanceLevel,
		&i.ClearancePoly,
		&i.ClearanceStatus,
		&i.ClearanceGrantedAt,
		&i.ClearanceExpiresAt,
		&i.ReinvestigationDueAt,
		&i.YearsExperience,
		&i.Location,
		&i.Remote,
		&i.Relocatable,
		&i.Available,
		&i.ResumeEmbedding,
		&i.SearchTsv,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.DeletedAt,
		&i.Source,
		&i.ExternalID,
		&i.AnonymizedAt,
		&i.ClearanceProvenance,
		&i.ClearanceVerifiedAt,
		&i.ClearanceCompartments,
		&i.SourceFirstTouch,
		&i.SourceLastTouch,
		&i.SourceDetail,
	)
	return i, err
}

const linkSourcedCandidate = `-- name: LinkSourcedCandidate :exec
UPDATE candidates SET
  external_id      = COALESCE(external_id, $2),
  headline         = COALESCE(headline, $3),
  years_experience = GREATEST(years_experience, $4),
  updated_at       = now()
WHERE id = $1
`

type LinkSourcedCandidateParams struct {
	ID              uuid.UUID `json:"id"`
	ExternalID      *string   `json:"external_id"`
	Headline        *string   `json:"headline"`
	YearsExperience int32     `json:"years_experience"`
}

// Identity resolution merge: link a sourced lead to an existing candidate. Sets the external id and
// backfills headline and years ONLY where the existing row is empty or lower, never clobbering curated
// bench data. Source is intentionally left unchanged so a verified bench person stays a bench person.
func (q *Queries) LinkSourcedCandidate(ctx context.Context, arg LinkSourcedCandidateParams) error {
	_, err := q.db.Exec(ctx, linkSourcedCandidate,
		arg.ID,
		arg.ExternalID,
		arg.Headline,
		arg.YearsExperience,
	)
	return err
}

const listCandidateCertifications = `-- name: ListCandidateCertifications :many
SELECT name FROM candidate_certifications WHERE candidate_id = $1 ORDER BY name
`

func (q *Queries) ListCandidateCertifications(ctx context.Context, candidateID uuid.UUID) ([]string, error) {
	rows, err := q.db.Query(ctx, listCandidateCertifications, candidateID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []string{}
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		items = append(items, name)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listCandidateCustomerExperience = `-- name: ListCandidateCustomerExperience :many
SELECT agency FROM candidate_customer_experience WHERE candidate_id = $1 ORDER BY agency
`

func (q *Queries) ListCandidateCustomerExperience(ctx context.Context, candidateID uuid.UUID) ([]string, error) {
	rows, err := q.db.Query(ctx, listCandidateCustomerExperience, candidateID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []string{}
	for rows.Next() {
		var agency string
		if err := rows.Scan(&agency); err != nil {
			return nil, err
		}
		items = append(items, agency)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listCandidateSkills = `-- name: ListCandidateSkills :many
SELECT s.id, s.name
FROM candidate_skills cs
JOIN skills s ON s.id = cs.skill_id
WHERE cs.candidate_id = $1
ORDER BY s.name
`

type ListCandidateSkillsRow struct {
	ID   uuid.UUID `json:"id"`
	Name string    `json:"name"`
}

func (q *Queries) ListCandidateSkills(ctx context.Context, candidateID uuid.UUID) ([]ListCandidateSkillsRow, error) {
	rows, err := q.db.Query(ctx, listCandidateSkills, candidateID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []ListCandidateSkillsRow{}
	for rows.Next() {
		var i ListCandidateSkillsRow
		if err := rows.Scan(&i.ID, &i.Name); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listCandidates = `-- name: ListCandidates :many
SELECT id, organization_id, full_name, email, headline, clearance_level, clearance_poly, clearance_status, clearance_granted_at, clearance_expires_at, reinvestigation_due_at, years_experience, location, remote, relocatable, available, resume_embedding, search_tsv, created_at, updated_at, deleted_at, source, external_id, anonymized_at, clearance_provenance, clearance_verified_at, clearance_compartments, source_first_touch, source_last_touch, source_detail FROM candidates
WHERE deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT $1 OFFSET $2
`

type ListCandidatesParams struct {
	Limit  int32 `json:"limit"`
	Offset int32 `json:"offset"`
}

// id is a unique trailing sort key so created_at ties give a total order and stable LIMIT/OFFSET paging.
func (q *Queries) ListCandidates(ctx context.Context, arg ListCandidatesParams) ([]Candidate, error) {
	rows, err := q.db.Query(ctx, listCandidates, arg.Limit, arg.Offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []Candidate{}
	for rows.Next() {
		var i Candidate
		if err := rows.Scan(
			&i.ID,
			&i.OrganizationID,
			&i.FullName,
			&i.Email,
			&i.Headline,
			&i.ClearanceLevel,
			&i.ClearancePoly,
			&i.ClearanceStatus,
			&i.ClearanceGrantedAt,
			&i.ClearanceExpiresAt,
			&i.ReinvestigationDueAt,
			&i.YearsExperience,
			&i.Location,
			&i.Remote,
			&i.Relocatable,
			&i.Available,
			&i.ResumeEmbedding,
			&i.SearchTsv,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.DeletedAt,
			&i.Source,
			&i.ExternalID,
			&i.AnonymizedAt,
			&i.ClearanceProvenance,
			&i.ClearanceVerifiedAt,
			&i.ClearanceCompartments,
			&i.SourceFirstTouch,
			&i.SourceLastTouch,
			&i.SourceDetail,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listCandidatesForIntel = `-- name: ListCandidatesForIntel :many
SELECT id, organization_id, full_name, email, headline, clearance_level, clearance_poly, clearance_status, clearance_granted_at, clearance_expires_at, reinvestigation_due_at, years_experience, location, remote, relocatable, available, resume_embedding, search_tsv, created_at, updated_at, deleted_at, source, external_id, anonymized_at, clearance_provenance, clearance_verified_at, clearance_compartments, source_first_touch, source_last_touch, source_detail FROM candidates
WHERE deleted_at IS NULL
ORDER BY available DESC, updated_at DESC, created_at DESC, id
LIMIT $1
`

// Available (staffable) candidates first, most recently updated next, so the workforce intelligence
// surface leads with the talent you can deploy and newly staffed or sourced people are always scored.
// The trailing id makes the order total, so the same set is selected before the LIMIT cut every run.
func (q *Queries) ListCandidatesForIntel(ctx context.Context, limit int32) ([]Candidate, error) {
	rows, err := q.db.Query(ctx, listCandidatesForIntel, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []Candidate{}
	for rows.Next() {
		var i Candidate
		if err := rows.Scan(
			&i.ID,
			&i.OrganizationID,
			&i.FullName,
			&i.Email,
			&i.Headline,
			&i.ClearanceLevel,
			&i.ClearancePoly,
			&i.ClearanceStatus,
			&i.ClearanceGrantedAt,
			&i.ClearanceExpiresAt,
			&i.ReinvestigationDueAt,
			&i.YearsExperience,
			&i.Location,
			&i.Remote,
			&i.Relocatable,
			&i.Available,
			&i.ResumeEmbedding,
			&i.SearchTsv,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.DeletedAt,
			&i.Source,
			&i.ExternalID,
			&i.AnonymizedAt,
			&i.ClearanceProvenance,
			&i.ClearanceVerifiedAt,
			&i.ClearanceCompartments,
			&i.SourceFirstTouch,
			&i.SourceLastTouch,
			&i.SourceDetail,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const searchCandidatesHybrid = `-- name: SearchCandidatesHybrid :many
WITH vec_raw AS MATERIALIZED (
    SELECT id, (resume_embedding <=> $1::vector) AS distance
    FROM candidates
    WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL
    ORDER BY resume_embedding <=> $1::vector, id
    LIMIT $2
),
vec AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk
    FROM vec_raw
),
fts AS (
    SELECT id, ROW_NUMBER() OVER (
               ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $3)) DESC, id
           ) AS rnk
    FROM candidates
    WHERE deleted_at IS NULL AND search_tsv @@ websearch_to_tsquery('english', $3)
    ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $3)) DESC, id
    LIMIT $2
)
SELECT c.id, c.organization_id, c.full_name, c.email, c.headline, c.clearance_level, c.clearance_poly, c.clearance_status, c.clearance_granted_at, c.clearance_expires_at, c.reinvestigation_due_at, c.years_experience, c.location, c.remote, c.relocatable, c.available, c.resume_embedding, c.search_tsv, c.created_at, c.updated_at, c.deleted_at, c.source, c.external_id, c.anonymized_at, c.clearance_provenance, c.clearance_verified_at, c.clearance_compartments, c.source_first_touch, c.source_last_touch, c.source_detail,
       (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score,
       -- snippet on the hit: the matched text span, over the SAME text the search_tsv is built from
       -- (migration 0002: full_name, headline, location) and the SAME websearch_to_tsquery the full text
       -- leg already binds ($3), so the snippet cites exactly WHY the candidate matched, never a divergent
       -- query. Conservative options keep it short and plain: no markup wrappers, one fragment, a tight
       -- word window. The CASE guard degrades a pure semantic or empty query (an empty tsquery, which
       -- ts_headline would otherwise answer with the unhighlighted document head) to an empty snippet,
       -- which the caller treats as a valid no highlight result, never an error.
       CASE WHEN websearch_to_tsquery('english', $3) = ''::tsquery THEN ''
            ELSE ts_headline('english',
                coalesce(c.full_name,'') || ' ' || coalesce(c.headline,'') || ' ' || coalesce(c.location,''),
                websearch_to_tsquery('english', $3),
                'StartSel="", StopSel="", MaxFragments=1, MaxWords=12, MinWords=4, ShortWord=2, FragmentDelimiter=" "')
       END::text AS snippet
FROM candidates c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN fts ON fts.id = c.id
WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL
ORDER BY rrf_score DESC, c.created_at DESC, c.id
LIMIT $2
`

type SearchCandidatesHybridParams struct {
	Column1            pgvector.Vector `json:"column_1"`
	Limit              int32           `json:"limit"`
	WebsearchToTsquery string          `json:"websearch_to_tsquery"`
}

type SearchCandidatesHybridRow struct {
	ID                    uuid.UUID        `json:"id"`
	OrganizationID        uuid.UUID        `json:"organization_id"`
	FullName              string           `json:"full_name"`
	Email                 *string          `json:"email"`
	Headline              *string          `json:"headline"`
	ClearanceLevel        string           `json:"clearance_level"`
	ClearancePoly         string           `json:"clearance_poly"`
	ClearanceStatus       string           `json:"clearance_status"`
	ClearanceGrantedAt    pgtype.Date      `json:"clearance_granted_at"`
	ClearanceExpiresAt    pgtype.Date      `json:"clearance_expires_at"`
	ReinvestigationDueAt  pgtype.Date      `json:"reinvestigation_due_at"`
	YearsExperience       int32            `json:"years_experience"`
	Location              *string          `json:"location"`
	Remote                bool             `json:"remote"`
	Relocatable           bool             `json:"relocatable"`
	Available             bool             `json:"available"`
	ResumeEmbedding       *pgvector.Vector `json:"resume_embedding"`
	SearchTsv             interface{}      `json:"search_tsv"`
	CreatedAt             time.Time        `json:"created_at"`
	UpdatedAt             time.Time        `json:"updated_at"`
	DeletedAt             *time.Time       `json:"deleted_at"`
	Source                string           `json:"source"`
	ExternalID            *string          `json:"external_id"`
	AnonymizedAt          *time.Time       `json:"anonymized_at"`
	ClearanceProvenance   string           `json:"clearance_provenance"`
	ClearanceVerifiedAt   *time.Time       `json:"clearance_verified_at"`
	ClearanceCompartments []string         `json:"clearance_compartments"`
	SourceFirstTouch      string           `json:"source_first_touch"`
	SourceLastTouch       string           `json:"source_last_touch"`
	SourceDetail          string           `json:"source_detail"`
	RrfScore              float64          `json:"rrf_score"`
	Snippet               string           `json:"snippet"`
}

// Hybrid retrieval: pgvector cosine kNN fused with full-text rank via Reciprocal Rank Fusion (k=60).
// RLS already scopes both legs to the caller's org. $1=query embedding, $2=limit, $3=text query.
// The vector arm is MATERIALIZED so the relaxed_order HNSW output is re-sorted by TRUE cosine distance
// before it is ranked: under hnsw.iterative_scan = relaxed_order the index returns rows slightly out of
// distance order, and ranking them without the re-sort would mis-rank the nearest neighbors at scale
// (pgvector 0.8 README; AWS pgvector 0.8 Aurora guidance). Every ORDER BY carries a unique tiebreaker
// (id) so results are byte-stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs
// LOCAL hnsw.ef_search and hnsw.iterative_scan first.
func (q *Queries) SearchCandidatesHybrid(ctx context.Context, arg SearchCandidatesHybridParams) ([]SearchCandidatesHybridRow, error) {
	rows, err := q.db.Query(ctx, searchCandidatesHybrid, arg.Column1, arg.Limit, arg.WebsearchToTsquery)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []SearchCandidatesHybridRow{}
	for rows.Next() {
		var i SearchCandidatesHybridRow
		if err := rows.Scan(
			&i.ID,
			&i.OrganizationID,
			&i.FullName,
			&i.Email,
			&i.Headline,
			&i.ClearanceLevel,
			&i.ClearancePoly,
			&i.ClearanceStatus,
			&i.ClearanceGrantedAt,
			&i.ClearanceExpiresAt,
			&i.ReinvestigationDueAt,
			&i.YearsExperience,
			&i.Location,
			&i.Remote,
			&i.Relocatable,
			&i.Available,
			&i.ResumeEmbedding,
			&i.SearchTsv,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.DeletedAt,
			&i.Source,
			&i.ExternalID,
			&i.AnonymizedAt,
			&i.ClearanceProvenance,
			&i.ClearanceVerifiedAt,
			&i.ClearanceCompartments,
			&i.SourceFirstTouch,
			&i.SourceLastTouch,
			&i.SourceDetail,
			&i.RrfScore,
			&i.Snippet,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const searchCandidatesHybridMission = `-- name: SearchCandidatesHybridMission :many
WITH vec_raw AS MATERIALIZED (
    SELECT id, (resume_embedding <=> $3::vector) AS distance
    FROM candidates
    WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL
      AND search_tsv @@ websearch_to_tsquery('english', $4)
    ORDER BY resume_embedding <=> $3::vector, id
    LIMIT $2
),
vec AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk
    FROM vec_raw
),
fts AS (
    SELECT id, ROW_NUMBER() OVER (
               ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $1)) DESC, id
           ) AS rnk
    FROM candidates
    WHERE deleted_at IS NULL
      AND search_tsv @@ websearch_to_tsquery('english', $1)
      AND search_tsv @@ websearch_to_tsquery('english', $4)
    ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $1)) DESC, id
    LIMIT $2
)
SELECT c.id, c.organization_id, c.full_name, c.email, c.headline, c.clearance_level, c.clearance_poly, c.clearance_status, c.clearance_granted_at, c.clearance_expires_at, c.reinvestigation_due_at, c.years_experience, c.location, c.remote, c.relocatable, c.available, c.resume_embedding, c.search_tsv, c.created_at, c.updated_at, c.deleted_at, c.source, c.external_id, c.anonymized_at, c.clearance_provenance, c.clearance_verified_at, c.clearance_compartments, c.source_first_touch, c.source_last_touch, c.source_detail,
       (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score,
       -- snippet on the hit: the matched text span, over the SAME text the search_tsv is built from and the
       -- SAME websearch_to_tsquery the full text leg already binds (sqlc.arg(text_query), reused, never a
       -- second divergent tsquery). The mission gate is unchanged, the snippet is purely an added SELECT
       -- expression. The CASE guard degrades an empty text query (an empty tsquery) to an empty snippet, a
       -- valid no highlight result, never an error.
       CASE WHEN websearch_to_tsquery('english', $1) = ''::tsquery THEN ''
            ELSE ts_headline('english',
                coalesce(c.full_name,'') || ' ' || coalesce(c.headline,'') || ' ' || coalesce(c.location,''),
                websearch_to_tsquery('english', $1),
                'StartSel="", StopSel="", MaxFragments=1, MaxWords=12, MinWords=4, ShortWord=2, FragmentDelimiter=" "')
       END::text AS snippet
FROM candidates c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN fts ON fts.id = c.id
WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL
ORDER BY rrf_score DESC, c.created_at DESC, c.id
LIMIT $2
`

type SearchCandidatesHybridMissionParams struct {
	TextQuery    string          `json:"text_query"`
	RowLimit     int32           `json:"row_limit"`
	QueryVector  pgvector.Vector `json:"query_vector"`
	MissionQuery string          `json:"mission_query"`
}

type SearchCandidatesHybridMissionRow struct {
	ID                    uuid.UUID        `json:"id"`
	OrganizationID        uuid.UUID        `json:"organization_id"`
	FullName              string           `json:"full_name"`
	Email                 *string          `json:"email"`
	Headline              *string          `json:"headline"`
	ClearanceLevel        string           `json:"clearance_level"`
	ClearancePoly         string           `json:"clearance_poly"`
	ClearanceStatus       string           `json:"clearance_status"`
	ClearanceGrantedAt    pgtype.Date      `json:"clearance_granted_at"`
	ClearanceExpiresAt    pgtype.Date      `json:"clearance_expires_at"`
	ReinvestigationDueAt  pgtype.Date      `json:"reinvestigation_due_at"`
	YearsExperience       int32            `json:"years_experience"`
	Location              *string          `json:"location"`
	Remote                bool             `json:"remote"`
	Relocatable           bool             `json:"relocatable"`
	Available             bool             `json:"available"`
	ResumeEmbedding       *pgvector.Vector `json:"resume_embedding"`
	SearchTsv             interface{}      `json:"search_tsv"`
	CreatedAt             time.Time        `json:"created_at"`
	UpdatedAt             time.Time        `json:"updated_at"`
	DeletedAt             *time.Time       `json:"deleted_at"`
	Source                string           `json:"source"`
	ExternalID            *string          `json:"external_id"`
	AnonymizedAt          *time.Time       `json:"anonymized_at"`
	ClearanceProvenance   string           `json:"clearance_provenance"`
	ClearanceVerifiedAt   *time.Time       `json:"clearance_verified_at"`
	ClearanceCompartments []string         `json:"clearance_compartments"`
	SourceFirstTouch      string           `json:"source_first_touch"`
	SourceLastTouch       string           `json:"source_last_touch"`
	SourceDetail          string           `json:"source_detail"`
	RrfScore              float64          `json:"rrf_score"`
	Snippet               string           `json:"snippet"`
}

// Mission scoped hybrid retrieval (WS6 Option A): identical to SearchCandidatesHybrid, but BOTH arms are
// additionally constrained to candidates whose search_tsv matches the mission keyword(s) in mission_query,
// so a question can be scoped to a mission area (for example SIGINT) using only candidate text match. The
// search_tsv is built from full_name, headline, and location (migration 0002), NOT the resume body. This is
// the only mission filter available without structured mission tags: those are WS7 work. It filters on that
// TEXT, never on a semantic mission alignment, and that limit is documented in docs/AI_RESEARCH.md.
// RLS still scopes both legs to the caller's org; every ORDER BY keeps the unique id tiebreaker so the
// output is byte stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs LOCAL
// hnsw.ef_search and hnsw.iterative_scan first, exactly as for the unscoped query.
func (q *Queries) SearchCandidatesHybridMission(ctx context.Context, arg SearchCandidatesHybridMissionParams) ([]SearchCandidatesHybridMissionRow, error) {
	rows, err := q.db.Query(ctx, searchCandidatesHybridMission,
		arg.TextQuery,
		arg.RowLimit,
		arg.QueryVector,
		arg.MissionQuery,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []SearchCandidatesHybridMissionRow{}
	for rows.Next() {
		var i SearchCandidatesHybridMissionRow
		if err := rows.Scan(
			&i.ID,
			&i.OrganizationID,
			&i.FullName,
			&i.Email,
			&i.Headline,
			&i.ClearanceLevel,
			&i.ClearancePoly,
			&i.ClearanceStatus,
			&i.ClearanceGrantedAt,
			&i.ClearanceExpiresAt,
			&i.ReinvestigationDueAt,
			&i.YearsExperience,
			&i.Location,
			&i.Remote,
			&i.Relocatable,
			&i.Available,
			&i.ResumeEmbedding,
			&i.SearchTsv,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.DeletedAt,
			&i.Source,
			&i.ExternalID,
			&i.AnonymizedAt,
			&i.ClearanceProvenance,
			&i.ClearanceVerifiedAt,
			&i.ClearanceCompartments,
			&i.SourceFirstTouch,
			&i.SourceLastTouch,
			&i.SourceDetail,
			&i.RrfScore,
			&i.Snippet,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const searchCandidatesHybridMissionTagged = `-- name: SearchCandidatesHybridMissionTagged :many
WITH vec_raw AS MATERIALIZED (
    SELECT id, (resume_embedding <=> $3::vector) AS distance
    FROM candidates
    WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL
      AND EXISTS (SELECT 1 FROM candidate_mission_tags t
                  WHERE t.candidate_id = candidates.id AND t.mission_code = $4)
    ORDER BY resume_embedding <=> $3::vector, id
    LIMIT $2
),
vec AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk
    FROM vec_raw
),
fts AS (
    SELECT id, ROW_NUMBER() OVER (
               ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $1)) DESC, id
           ) AS rnk
    FROM candidates
    WHERE deleted_at IS NULL
      AND search_tsv @@ websearch_to_tsquery('english', $1)
      AND EXISTS (SELECT 1 FROM candidate_mission_tags t
                  WHERE t.candidate_id = candidates.id AND t.mission_code = $4)
    ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $1)) DESC, id
    LIMIT $2
)
SELECT c.id, c.organization_id, c.full_name, c.email, c.headline, c.clearance_level, c.clearance_poly, c.clearance_status, c.clearance_granted_at, c.clearance_expires_at, c.reinvestigation_due_at, c.years_experience, c.location, c.remote, c.relocatable, c.available, c.resume_embedding, c.search_tsv, c.created_at, c.updated_at, c.deleted_at, c.source, c.external_id, c.anonymized_at, c.clearance_provenance, c.clearance_verified_at, c.clearance_compartments, c.source_first_touch, c.source_last_touch, c.source_detail,
       (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score,
       -- snippet on the hit: the matched text span, over the SAME text the search_tsv is built from and the
       -- SAME websearch_to_tsquery the full text leg already binds (sqlc.arg(text_query), reused). The
       -- structured mission gate is unchanged; only this SELECT expression is added. The CASE guard degrades
       -- an empty text query (an empty tsquery) to an empty snippet, a valid no highlight result, never an
       -- error.
       CASE WHEN websearch_to_tsquery('english', $1) = ''::tsquery THEN ''
            ELSE ts_headline('english',
                coalesce(c.full_name,'') || ' ' || coalesce(c.headline,'') || ' ' || coalesce(c.location,''),
                websearch_to_tsquery('english', $1),
                'StartSel="", StopSel="", MaxFragments=1, MaxWords=12, MinWords=4, ShortWord=2, FragmentDelimiter=" "')
       END::text AS snippet
FROM candidates c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN fts ON fts.id = c.id
WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL
ORDER BY rrf_score DESC, c.created_at DESC, c.id
LIMIT $2
`

type SearchCandidatesHybridMissionTaggedParams struct {
	TextQuery   string          `json:"text_query"`
	RowLimit    int32           `json:"row_limit"`
	QueryVector pgvector.Vector `json:"query_vector"`
	MissionCode string          `json:"mission_code"`
}

type SearchCandidatesHybridMissionTaggedRow struct {
	ID                    uuid.UUID        `json:"id"`
	OrganizationID        uuid.UUID        `json:"organization_id"`
	FullName              string           `json:"full_name"`
	Email                 *string          `json:"email"`
	Headline              *string          `json:"headline"`
	ClearanceLevel        string           `json:"clearance_level"`
	ClearancePoly         string           `json:"clearance_poly"`
	ClearanceStatus       string           `json:"clearance_status"`
	ClearanceGrantedAt    pgtype.Date      `json:"clearance_granted_at"`
	ClearanceExpiresAt    pgtype.Date      `json:"clearance_expires_at"`
	ReinvestigationDueAt  pgtype.Date      `json:"reinvestigation_due_at"`
	YearsExperience       int32            `json:"years_experience"`
	Location              *string          `json:"location"`
	Remote                bool             `json:"remote"`
	Relocatable           bool             `json:"relocatable"`
	Available             bool             `json:"available"`
	ResumeEmbedding       *pgvector.Vector `json:"resume_embedding"`
	SearchTsv             interface{}      `json:"search_tsv"`
	CreatedAt             time.Time        `json:"created_at"`
	UpdatedAt             time.Time        `json:"updated_at"`
	DeletedAt             *time.Time       `json:"deleted_at"`
	Source                string           `json:"source"`
	ExternalID            *string          `json:"external_id"`
	AnonymizedAt          *time.Time       `json:"anonymized_at"`
	ClearanceProvenance   string           `json:"clearance_provenance"`
	ClearanceVerifiedAt   *time.Time       `json:"clearance_verified_at"`
	ClearanceCompartments []string         `json:"clearance_compartments"`
	SourceFirstTouch      string           `json:"source_first_touch"`
	SourceLastTouch       string           `json:"source_last_touch"`
	SourceDetail          string           `json:"source_detail"`
	RrfScore              float64          `json:"rrf_score"`
	Snippet               string           `json:"snippet"`
}

// Structured mission scoped hybrid retrieval (WS7 upgrade of the WS6 Option A text filter). It is identical
// in shape to SearchCandidatesHybridMission, but the mission constraint on BOTH arms is a STRUCTURED
// predicate on candidate_mission_tags (the deterministic classifier output) instead of the search_tsv
// mission keyword text match. A candidate is in scope only when a candidate_mission_tags row binds it to the
// requested mission_code, so the scope is the real, classified mission of the candidate, not a name or
// headline string coincidence. This lifts the WS6 limit (a candidate whose mission is in the resume body but
// not in the full_name, headline, or location text is now reachable once the tenant is tagged). The text
// query ($text_query) still drives the FTS arm and the embedding arm still drives recall, so only the mission
// gate changed. candidate_mission_tags is RLS scoped to the caller's org, so the EXISTS subquery cannot cross
// a tenant boundary; the candidates table is RLS scoped too. Every ORDER BY keeps the unique id tiebreaker so
// the output is byte stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs LOCAL
// hnsw.ef_search and hnsw.iterative_scan first, exactly as for the unscoped and the WS6 text scoped queries.
func (q *Queries) SearchCandidatesHybridMissionTagged(ctx context.Context, arg SearchCandidatesHybridMissionTaggedParams) ([]SearchCandidatesHybridMissionTaggedRow, error) {
	rows, err := q.db.Query(ctx, searchCandidatesHybridMissionTagged,
		arg.TextQuery,
		arg.RowLimit,
		arg.QueryVector,
		arg.MissionCode,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []SearchCandidatesHybridMissionTaggedRow{}
	for rows.Next() {
		var i SearchCandidatesHybridMissionTaggedRow
		if err := rows.Scan(
			&i.ID,
			&i.OrganizationID,
			&i.FullName,
			&i.Email,
			&i.Headline,
			&i.ClearanceLevel,
			&i.ClearancePoly,
			&i.ClearanceStatus,
			&i.ClearanceGrantedAt,
			&i.ClearanceExpiresAt,
			&i.ReinvestigationDueAt,
			&i.YearsExperience,
			&i.Location,
			&i.Remote,
			&i.Relocatable,
			&i.Available,
			&i.ResumeEmbedding,
			&i.SearchTsv,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.DeletedAt,
			&i.Source,
			&i.ExternalID,
			&i.AnonymizedAt,
			&i.ClearanceProvenance,
			&i.ClearanceVerifiedAt,
			&i.ClearanceCompartments,
			&i.SourceFirstTouch,
			&i.SourceLastTouch,
			&i.SourceDetail,
			&i.RrfScore,
			&i.Snippet,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const setCandidateClearanceProvenance = `-- name: SetCandidateClearanceProvenance :execrows
UPDATE candidates
SET clearance_provenance = $2, clearance_verified_at = $3, updated_at = now()
WHERE id = $1 AND deleted_at IS NULL
`

type SetCandidateClearanceProvenanceParams struct {
	ID                  uuid.UUID  `json:"id"`
	ClearanceProvenance string     `json:"clearance_provenance"`
	ClearanceVerifiedAt *time.Time `json:"clearance_verified_at"`
}

// An org FSO records HOW a candidate's clearance is known. RLS scopes this to the caller's own org.
// verified_at is the date the FSO confirmed it; it is NULL for a self reported indicator. The platform
// never verifies a clearance itself, it records the org's authoritative assertion.
func (q *Queries) SetCandidateClearanceProvenance(ctx context.Context, arg SetCandidateClearanceProvenanceParams) (int64, error) {
	result, err := q.db.Exec(ctx, setCandidateClearanceProvenance, arg.ID, arg.ClearanceProvenance, arg.ClearanceVerifiedAt)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const softDeleteCandidate = `-- name: SoftDeleteCandidate :exec
UPDATE candidates SET deleted_at = now(), updated_at = now() WHERE id = $1
`

func (q *Queries) SoftDeleteCandidate(ctx context.Context, id uuid.UUID) error {
	_, err := q.db.Exec(ctx, softDeleteCandidate, id)
	return err
}

const updateCandidateEmbedding = `-- name: UpdateCandidateEmbedding :exec
UPDATE candidates SET resume_embedding = $1::vector, updated_at = now() WHERE id = $2
`

type UpdateCandidateEmbeddingParams struct {
	Embedding pgvector.Vector `json:"embedding"`
	ID        uuid.UUID       `json:"id"`
}

// Writes a precomputed resume embedding onto a candidate, used by the bench import path AFTER the embed
// network call has already run in the caller, so the migrator write transaction never holds open across
// the inference round trip (the embed lock window rule). The vector is composed from name free structured
// tokens only, never raw resume PII. A candidate with a NULL embedding is left embeddable by the
// embed-backfill only-missing fallback, so a skipped embed never wedges a row.
func (q *Queries) UpdateCandidateEmbedding(ctx context.Context, arg UpdateCandidateEmbeddingParams) error {
	_, err := q.db.Exec(ctx, updateCandidateEmbedding, arg.Embedding, arg.ID)
	return err
}