query-orchestration/internal/database/queries/labels.sql

-- name: ApplyLabel :one
INSERT INTO documentLabels (documentId, label, appliedBy)
VALUES ($1, $2, $3)
RETURNING *;

-- name: GetDocumentLabels :many
SELECT * FROM documentLabels
WHERE documentId = $1
ORDER BY appliedAt DESC;

-- name: GetDocumentLabelsForClient :many
-- Chatbot-scoped variant of GetDocumentLabels. Joins documents to enforce
-- the clientId filter so cross-client lookups return an empty slice
-- rather than another tenant's labels. Returns the same documentLabels
-- column shape as GetDocumentLabels (not the joined documents row).
-- Plan §5.
SELECT dl.*
FROM documentLabels dl
JOIN documents d ON d.id = dl.documentId
WHERE dl.documentId = @document_id
  AND d.clientId    = @client_id
ORDER BY dl.appliedAt DESC;

-- name: GetMostRecentLabel :one
SELECT * FROM documentLabels
WHERE documentId = $1 AND label = $2
ORDER BY appliedAt DESC
LIMIT 1;

-- name: GetDocumentsByLabel :many
SELECT DISTINCT d.*
FROM documents d
INNER JOIN documentLabels dl ON d.id = dl.documentId
WHERE d.clientId = $1 AND dl.label = $2
ORDER BY d.id;

-- name: GetDocumentsByLabelAndFolder :many
SELECT DISTINCT d.*
FROM documents d
INNER JOIN documentLabels dl ON d.id = dl.documentId
WHERE d.folderId = $1 AND dl.label = $2
ORDER BY d.id;

-- name: GetAllLabels :many
SELECT * FROM labels
ORDER BY label;

-- name: CreateLabel :one
INSERT INTO labels (label, description)
VALUES ($1, $2)
RETURNING *;

-- name: GetDocumentLabelHistory :many
SELECT dl.*, d.filename
FROM documentLabels dl
INNER JOIN documents d ON dl.documentId = d.id
WHERE d.clientId = $1
ORDER BY dl.appliedAt DESC
LIMIT $2 OFFSET $3;