-- name: InsertAnnotation :one
INSERT INTO annotations (
    transaction_id, kind, actor_type, actor_id, actor_name,
    session_id, payload, tag_id, rule_id
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING *;

-- name: ListAnnotationsByTransaction :many
SELECT * FROM annotations
WHERE transaction_id = $1
ORDER BY created_at ASC;

-- name: ListAnnotationsWithActorByTransaction :many
-- Carries the actor user's updated_at as a unix-timestamp avatar version so
-- the activity timeline can cache-bust avatar URLs (`?v=<ts>`) and pick up
-- new uploads immediately rather than serving stale bytes within the avatar
-- handler's 24h max-age window. annotations.actor_id can hold either a
-- users.id or an auth_accounts.id depending on the call site; resolve both
-- by joining auth_accounts and falling back to a direct users join.
--
-- Also surfaces the joined users.name so the service layer can prefer the
-- user's profile name over the value frozen into annotations.actor_name at
-- write time (which is whatever Actor.Name carried — typically the
-- auth_accounts.username/email login). Falls back to the stored actor_name
-- when the join misses or the profile name is blank.
--
-- Compare in text space (aa.id::text = a.actor_id) rather than casting
-- a.actor_id::uuid, because actor_id can also hold a non-UUID short_id for
-- system-actor rows (e.g. rule_applied writes the rule's short_id) and
-- PostgreSQL does not guarantee short-circuit evaluation of conjunctions in
-- JOIN conditions — a regex guard alone is not enough to prevent SQLSTATE
-- 22P02 on the ::uuid cast.
--
-- deleted_at is surfaced so the service layer can flag tombstoned comments
-- without filtering them out — the activity timeline still shows
-- "<Actor> deleted a comment" rows for audit value.
SELECT
    a.id, a.short_id, a.transaction_id, a.kind, a.actor_type,
    a.actor_id, a.actor_name, a.session_id, a.payload, a.tag_id,
    a.rule_id, a.created_at, a.deleted_at,
    COALESCE(u_via_account.updated_at, u_direct.updated_at) AS actor_updated_at,
    -- Empty-string-coalesced join of the actor's profile name. The service
    -- layer prefers this over annotations.actor_name (which is whatever
    -- Actor.Name carried at write time — typically the auth_accounts
    -- username/email login) and falls back to actor_name when this is "".
    -- Non-user actors (system, agent) miss both joins and resolve to "".
    COALESCE(u_via_account.name, u_direct.name, '')::text AS actor_user_name
FROM annotations a
LEFT JOIN auth_accounts aa
    ON a.actor_type = 'user'
   AND aa.id::text = a.actor_id
LEFT JOIN users u_via_account
    ON aa.user_id = u_via_account.id
LEFT JOIN users u_direct
    ON a.actor_type = 'user'
   AND aa.id IS NULL
   AND u_direct.id::text = a.actor_id
WHERE a.transaction_id = $1
ORDER BY a.created_at ASC;

-- name: CountAnnotationsByTransactionAndKind :one
SELECT COUNT(*) FROM annotations
WHERE transaction_id = $1 AND kind = $2;

-- name: GetAnnotationByID :one
SELECT * FROM annotations WHERE id = $1;

-- name: GetAnnotationUUIDByShortID :one
SELECT id FROM annotations WHERE short_id = $1;

-- name: UpdateAnnotationPayload :one
UPDATE annotations
SET payload = $2
WHERE id = $1
RETURNING *;

-- name: DeleteAnnotation :exec
DELETE FROM annotations WHERE id = $1;

-- name: SoftDeleteAnnotation :exec
-- Flags an annotation as deleted (sets deleted_at = NOW()) without removing
-- the row. Used by DeleteComment so the activity timeline preserves the
-- tombstone — actor + timestamp survive, only the comment payload is
-- semantically retired. Idempotent: re-soft-deleting an already-tombstoned
-- row is a no-op since the WHERE clause filters out non-tombstoned rows.
UPDATE annotations
SET deleted_at = NOW()
WHERE id = $1
  AND deleted_at IS NULL;
