minstrel/internal/db/queries/lifecycle.sql

-- The download lifecycle (M502 #5523): the intervention ledger, giving a
-- download up, and seeding after import.

-- name: RaiseIntervention :one
-- A condition seen, or seen again: a second pass lands on the open row, so
-- a download stuck for a week is one row that says how long. The name only
-- improves, and the detail is the latest word.
INSERT INTO download_interventions (client, info_hash, grab_id, name, trigger, detail)
VALUES (sqlc.arg(client), sqlc.arg(info_hash), sqlc.narg(grab_id), sqlc.arg(name),
        sqlc.arg(trigger), sqlc.arg(detail))
ON CONFLICT (client, info_hash, trigger) WHERE outcome = 'open' DO UPDATE SET
    observations = download_interventions.observations + 1,
    last_seen    = now(),
    detail       = EXCLUDED.detail,
    grab_id      = COALESCE(EXCLUDED.grab_id, download_interventions.grab_id),
    name         = CASE WHEN EXCLUDED.name <> '' THEN EXCLUDED.name ELSE download_interventions.name END
RETURNING *;

-- name: SettleIntervention :execrows
-- The condition cleared.
UPDATE download_interventions
   SET outcome = 'resolved', settled_at = now(), last_seen = now()
 WHERE client = sqlc.arg(client) AND info_hash = sqlc.arg(info_hash)
   AND trigger = sqlc.arg(trigger) AND outcome = 'open';

-- name: SettleInterventionsFor :execrows
-- Everything open about one download is over: it is gone from the client.
UPDATE download_interventions
   SET outcome = 'resolved', settled_at = now(), last_seen = now()
 WHERE client = sqlc.arg(client) AND info_hash = sqlc.arg(info_hash) AND outcome = 'open';

-- name: SettleEndedGrabs :execrows
-- What was wrong with a download whose grab has ended (imported, refused,
-- given up) is over, except a download left in the client to remove.
UPDATE download_interventions di
   SET outcome = 'resolved', settled_at = now(), last_seen = now()
  FROM grabs g
 WHERE g.id = di.grab_id AND g.ended_at IS NOT NULL
   AND di.outcome = 'open' AND di.trigger <> 'not_removed';

-- name: CountRemediation :one
-- Minstrel acted on an open condition. A retry climbs the ladder; giving up
-- or removing ends the condition, since what it was about is gone. Written
-- before the act, so a retry that happened is never uncounted.
UPDATE download_interventions
   SET remediations = remediations + 1,
       action       = sqlc.arg(action),
       last_seen    = now(),
       outcome      = CASE WHEN sqlc.arg(action)::text IN ('given_up', 'removed') THEN 'resolved' ELSE outcome END,
       settled_at   = CASE WHEN sqlc.arg(action)::text IN ('given_up', 'removed') THEN now() ELSE settled_at END
 WHERE client = sqlc.arg(client) AND info_hash = sqlc.arg(info_hash)
   AND trigger = sqlc.arg(trigger) AND outcome = 'open'
RETURNING remediations;

-- name: ListLeftovers :many
-- Downloads given up on that the client would not remove; each pass asks
-- again.
SELECT * FROM download_interventions
 WHERE trigger = 'not_removed' AND outcome = 'open' AND info_hash <> ''
 ORDER BY first_seen;

-- name: ListClientsDown :many
-- Download clients not answering now, for the Activity tab.
SELECT client, detail, first_seen FROM download_interventions
 WHERE trigger = 'client_unreachable' AND outcome = 'open'
 ORDER BY client;

-- name: LiveGrabOnHash :one
SELECT EXISTS (SELECT 1 FROM grabs WHERE info_hash = sqlc.arg(info_hash) AND ended_at IS NULL)::bool;

-- name: GiveUpGrab :one
-- Ends a running grab, given up on. Only from the states named: the update
-- is the lock against an import that claimed the download meanwhile.
UPDATE grabs
   SET ended_at = now(), end_reason = sqlc.arg(end_reason), end_detail = sqlc.arg(end_detail),
       import_updated_at = now()
 WHERE id = sqlc.arg(id) AND ended_at IS NULL AND import_state = ANY(sqlc.arg(states)::text[])
RETURNING *;

-- name: CountGoneGrabs :one
-- How many of a release's grabs ended gone since a time: a release that keeps
-- leaving its client is held back rather than taken on every pass (#6176).
SELECT count(*) FROM grabs
 WHERE info_hash = sqlc.arg(info_hash) AND end_reason = 'gone' AND ended_at > sqlc.arg(since);

-- name: ListSeedingGrabs :many
-- Imported downloads still sharing, oldest import first.
SELECT id, client, info_hash, name, imported_at FROM grabs
 WHERE seeding AND seed_released_at IS NULL
 ORDER BY imported_at, id;

-- name: SetGrabRatio :exec
UPDATE grabs SET ratio = sqlc.arg(ratio) WHERE id = sqlc.arg(id);

-- name: ReleaseSeed :exec
-- The seeding download was removed, or found gone.
UPDATE grabs SET seed_released_at = now() WHERE id = $1 AND seed_released_at IS NULL;

-- name: DeleteExpiredBlocks :execrows
-- A release given up on is held back for a while; after that the search may
-- take it again.
DELETE FROM release_blocks WHERE expires_at IS NOT NULL AND expires_at <= now();