package alerts

// repo.go holds the parameterized SQL the service runs under RunInTenantTx (RLS scoped). The statements are
// raw and parameterized rather than sqlc generated so the alert engine is self contained and does not require
// regenerating the dbgen package for one new table; this mirrors the bidoutcomes ledger and the AI graph
// layer, which author their reviewed SQL as static constants. Every statement runs as the NOBYPASSRLS app
// role inside a tenant transaction, so the organization_id is the session GUC and a row in another org is
// never visible or writable (the RLS policy plus the composite indexes both hold). The app role has SELECT,
// INSERT, UPDATE on alerts and no DELETE, so an alert is never destroyed in app code.

// clearanceExpiringSQL is trigger (a): the owner's own candidates whose clearance lapses within the window.
// RLS scopes the read to the caller's bench, the soft delete and anonymize filters exclude an erased
// candidate (an anonymized in place row keeps its candidate id, so without the anonymized_at filter an erased
// person would still surface in the alert title). $1 is the window cutoff date (today plus the window in
// days, computed in Go so the SQL binds a plain date). The days remaining is computed in SQL as the integer
// day difference so the title can state it. Deterministic ORDER BY on the expiry date then the id tiebreaker,
// a total order. Only the owner's own candidate name and the integer days remaining ever leave the read; the
// title is the owner's own data, never another tenant's.
const clearanceExpiringSQL = `
SELECT
    c.id::text                                       AS candidate_id,
    c.full_name                                      AS full_name,
    (c.clearance_expires_at - CURRENT_DATE)::int     AS days_remaining
FROM candidates c
WHERE c.deleted_at IS NULL
  AND c.anonymized_at IS NULL
  AND c.clearance_expires_at IS NOT NULL
  AND c.clearance_expires_at <= $1
ORDER BY c.clearance_expires_at ASC, c.id ASC`

// recompeteWinnableSQL is trigger (b), the MARKET EVENT arm: a recently posted federal opportunity in an
// agency the owner already shows interest in, whose period of performance ends inside the forward recompete
// window. opportunity_records is GLOBAL PUBLIC reference data (no organization_id, outside RLS), so reading it
// is fine; the org INTEREST read (a requirement with that customer_agency, or a bid_outcome linked to a
// requirement or opportunity in that agency) is RLS scoped, so a foreign org's interest never widens this
// org's market alerts. The agency match is on the normalized lower(trim(agency)) so a SAM agency string and
// the owner's stored customer_agency match despite casing or padding. $1 is the posted cutoff date (today
// minus the recent window), $2 is the pop end cutoff date (today plus the recompete window), so an
// opportunity qualifies when it was posted on or after $1 and its pop_end_date falls between today and $2.
// A row with a null pop_end_date is excluded (the recompete window is undefined without an end date).
// Deterministic ORDER BY on the posted date descending then the id tiebreaker. The title names only the
// agency and naics the owner already tracks, the owner's own market context, never another tenant's data.
const recompeteWinnableSQL = `
SELECT
    opr.id::text  AS opportunity_record_id,
    opr.agency    AS agency,
    opr.naics     AS naics
FROM opportunity_records opr
WHERE opr.agency <> ''
  AND opr.posted_date IS NOT NULL
  AND opr.posted_date >= $1
  AND opr.pop_end_date IS NOT NULL
  AND opr.pop_end_date > CURRENT_DATE
  AND opr.pop_end_date <= $2
  AND lower(btrim(opr.agency)) IN (
        SELECT lower(btrim(r.customer_agency))
        FROM requirements r
        WHERE r.customer_agency IS NOT NULL
          AND r.customer_agency <> ''
          AND r.deleted_at IS NULL
        UNION
        SELECT lower(btrim(ri.customer_agency))
        FROM bid_outcomes bo
        JOIN requirements ri
          ON ri.organization_id = bo.organization_id
         AND ri.id = bo.requirement_id
        WHERE ri.customer_agency IS NOT NULL
          AND ri.customer_agency <> ''
        UNION
        SELECT lower(btrim(oi.customer_agency))
        FROM bid_outcomes bo2
        JOIN opportunities oi
          ON oi.organization_id = bo2.organization_id
         AND oi.id = bo2.opportunity_id
        WHERE oi.customer_agency IS NOT NULL
          AND oi.customer_agency <> ''
      )
ORDER BY opr.posted_date DESC, opr.id ASC`

// gapFillableSQL is trigger (c): an OPEN requirement (not deleted, with no green candidate_evaluation) that
// the bench NOW structurally covers, meaning at least one candidate holds at least the required clearance
// level by the rank ladder AND at least four fifths of the required skills. This is the SAME structural test
// as ai.benchGapForRequirementSQL: the skill fraction is compared in INTEGER arithmetic (held matched times
// five is at least the required total times four, equivalent to held over total at least four fifths) so NO
// float touches the test, and a requirement with no required skills is vacuously met on the skills arm. It is
// a STRUCTURAL coverage test to detect a newly fillable gap, explicitly NOT a match score: the per candidate
// score and ranking remain the frozen engine, which is untouched. RLS scopes the read to the caller's bench
// and requirements. The open predicate mirrors the Command Center and ListOpenRequirements
// (deleted_at IS NULL AND the opportunity is null or open). The no green evaluation predicate is the gap
// condition: a requirement that already has a proposal ready candidate is not an unfilled gap. The EXISTS over
// the bench finds at least one structurally covering candidate, the moment a gap becomes fillable.
// Deterministic ORDER BY on the requirement title then the id tiebreaker. The title names only the owner's own
// requirement, the owner's own data.
const gapFillableSQL = `
SELECT
    r.id::text  AS requirement_id,
    r.title     AS title
FROM requirements r
LEFT JOIN opportunities o
  ON o.organization_id = r.organization_id
 AND o.id = r.opportunity_id
WHERE r.deleted_at IS NULL
  AND (o.id IS NULL OR o.status = 'open')
  AND NOT EXISTS (
        SELECT 1 FROM candidate_evaluations ce
        WHERE ce.organization_id = r.organization_id
          AND ce.requirement_id = r.id
          AND ce.band = 'green'
      )
  AND EXISTS (
        SELECT 1
        FROM candidates c
        JOIN clearance_levels cl_c ON cl_c.code = c.clearance_level
        JOIN clearance_levels cl_r ON cl_r.code = r.required_level
        WHERE c.deleted_at IS NULL
          AND c.anonymized_at IS NULL
          AND cl_c.rank >= cl_r.rank
          AND (
                (SELECT count(*) FROM required_skills rs_total WHERE rs_total.requirement_id = r.id) = 0
                OR
                (SELECT count(*) FROM required_skills rs_held
                   JOIN candidate_skills cs
                     ON cs.organization_id = c.organization_id
                    AND cs.candidate_id = c.id
                    AND cs.skill_id = rs_held.skill_id
                  WHERE rs_held.requirement_id = r.id) * 5
                >=
                (SELECT count(*) FROM required_skills rs_req WHERE rs_req.requirement_id = r.id) * 4
              )
      )
ORDER BY r.title ASC, r.id ASC`

// collisionSQL is trigger (d), CROSS OPPORTUNITY COLLISION DECONFLICTION: a candidate proposed or approved on
// the slates of two or more DISTINCT OPEN opportunities at once, the double commit of a scarce cleared person
// across competing bids. It joins slate_members up to opportunities by the composite org keys (defense in depth
// with RLS), keeps only pending and approved members (a rejected member is out of play) tied to open
// opportunities, and groups to the candidates on more than one distinct opportunity. member_opps collapses to
// one row per (candidate, opportunity) carrying the candidate's best fit score there and whether they are
// approved there; ranked orders each collided candidate's opportunities by best fit then earliest due date then
// id, so pos 1 is the DETERMINISTIC suggested primary (commit them where they fit best, tie broken by the most
// urgent deadline). The final row per candidate carries the opportunity count, the approved count (a candidate
// approved on two or more is truly double committed, the worst case), a scarce cleared flag (a self reported
// TS SCI active indicator, never a verified check, used only for severity), the suggested primary name, and the
// ordered opportunity names for the detail. RLS scopes every table to the caller's org; a standalone
// requirement (null opportunity_id) is excluded by the inner join, never a false collision. Deterministic ORDER
// BY scarce first, then the most contested, then the name, then the id. Only the owner's own candidate and
// opportunity names leave the read, the owner's own data, exactly like the other triggers; the audit stays
// counts only.
const collisionSQL = `
WITH member_opps AS (
    SELECT
        sm.organization_id                 AS organization_id,
        sm.candidate_id                    AS candidate_id,
        o.id                               AS opportunity_id,
        o.name                             AS opportunity_name,
        o.due_date                         AS due_date,
        max(sm.total_score)                AS best_score,
        bool_or(sm.decision = 'approved')  AS approved_here
    FROM slate_members sm
    JOIN slates sl       ON sl.organization_id = sm.organization_id AND sl.id = sm.slate_id
    JOIN requirements r  ON r.organization_id  = sl.organization_id AND r.id = sl.requirement_id
    JOIN opportunities o ON o.organization_id  = r.organization_id  AND o.id = r.opportunity_id
    WHERE sm.decision IN ('pending', 'approved')
      AND o.status = 'open'
    GROUP BY sm.organization_id, sm.candidate_id, o.id, o.name, o.due_date
),
collided AS (
    SELECT organization_id, candidate_id
    FROM member_opps
    GROUP BY organization_id, candidate_id
    HAVING count(*) > 1
),
ranked AS (
    SELECT
        mo.organization_id, mo.candidate_id, mo.opportunity_id, mo.opportunity_name, mo.approved_here,
        row_number() OVER (
            PARTITION BY mo.candidate_id
            ORDER BY mo.best_score DESC, mo.due_date ASC NULLS LAST, mo.opportunity_id ASC
        ) AS pos
    FROM member_opps mo
    JOIN collided cd ON cd.organization_id = mo.organization_id AND cd.candidate_id = mo.candidate_id
)
SELECT
    c.id::text                                                        AS candidate_id,
    c.full_name                                                       AS full_name,
    (c.clearance_level = 'ts_sci' AND c.clearance_status = 'active')  AS scarce_cleared,
    count(*)::int                                                     AS opp_count,
    (count(*) FILTER (WHERE ranked.approved_here))::int               AS approved_count,
    (array_agg(ranked.opportunity_name ORDER BY ranked.pos))[1]      AS suggested_primary,
    string_agg(ranked.opportunity_name, ', ' ORDER BY ranked.pos)    AS opportunity_names
FROM ranked
JOIN candidates c ON c.organization_id = ranked.organization_id AND c.id = ranked.candidate_id
WHERE c.deleted_at IS NULL AND c.anonymized_at IS NULL
GROUP BY c.id, c.full_name, c.clearance_level, c.clearance_status
ORDER BY scarce_cleared DESC, opp_count DESC, c.full_name ASC, c.id ASC`

// upsertAlertSQL records one alert, or refreshes the existing one keyed on (organization_id, dedupe_key). The
// organization_id is taken from the session GUC (never a caller argument), so RLS WITH CHECK confirms the row
// belongs to the caller; the ON CONFLICT update only ever touches a row already visible to this tenant. A re
// sweep of the same event refreshes the type, severity, subject, title, detail, and updated_at and leaves the
// status untouched (an alert the analyst already read or dismissed stays read or dismissed across sweeps), so
// the sweep is idempotent and never resurfaces a handled alert. created_at is preserved on conflict.
const upsertAlertSQL = `
INSERT INTO alerts
    (organization_id, alert_type, severity, subject_type, subject_id, title, detail, dedupe_key)
VALUES
    (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (organization_id, dedupe_key) DO UPDATE SET
    alert_type   = EXCLUDED.alert_type,
    severity     = EXCLUDED.severity,
    subject_type = EXCLUDED.subject_type,
    subject_id   = EXCLUDED.subject_id,
    title        = EXCLUDED.title,
    detail       = EXCLUDED.detail,
    updated_at   = now()`

// listAlertsSQL lists the caller's alerts newest first. RLS scopes it to the caller's org. The optional status
// filter is applied with a sentinel: when $1 is the empty string every status is returned, otherwise only the
// matching status. Deterministic ORDER BY on created_at descending then the id tiebreaker, a total order so
// the list is byte stable when two alerts share a created_at.
const listAlertsSQL = `
SELECT
    id::text, alert_type, severity, subject_type, COALESCE(subject_id::text, ''), title, detail, status, created_at, updated_at
FROM alerts
WHERE ($1 = '' OR status = $1)
ORDER BY created_at DESC, id ASC`

// setAlertStatusSQL flips one alert's status (the UI mark read or dismiss). RLS scopes the UPDATE, so an id in
// another org is invisible and matches zero rows (the not found path). The status value is validated in Go
// against the closed set before the bind, so a bad value never reaches the DB CHECK.
const setAlertStatusSQL = `
UPDATE alerts SET
    status     = $2,
    updated_at = now()
WHERE id = $1
RETURNING id::text, alert_type, severity, subject_type, COALESCE(subject_id::text, ''), title, detail, status, created_at, updated_at`