-- name: CreateUser :one
INSERT INTO users (organization_id, email, full_name, role, status)
VALUES ($1,$2,$3,$4,$5)
RETURNING *;

-- name: GetUserByEmail :one
SELECT * FROM users WHERE lower(email) = lower($1);

-- name: GetUser :one
SELECT * FROM users WHERE id = $1;

-- name: ListUsers :many
SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2;

-- name: TouchUserLogin :exec
UPDATE users SET last_login_at = now(), updated_at = now() WHERE id = $1;

-- name: GetOrganization :one
SELECT * FROM organizations WHERE id = $1;

-- name: InsertAuditLog :one
-- audit_logs is append-only (enforced by trigger); only INSERT/SELECT are granted.
INSERT INTO audit_logs (
    organization_id, actor_user_id, action, entity_type, entity_id,
    old_values, new_values, ip_address, user_agent
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
RETURNING id, created_at;

-- name: ListAuditLogsForOrg :many
-- audit_logs has NO RLS policy by design. Bind the read to the validated session GUC (NOT a
-- caller-supplied org param) so it inherits the same fail-closed default-deny as every RLS table —
-- a missing/wrong org can no longer leak another tenant's trail. Run inside RunInTenantTx so the GUC
-- is set [review AUDIT-2/RLS-3].
SELECT * FROM audit_logs
WHERE organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid
ORDER BY created_at DESC
LIMIT $1 OFFSET $2;

-- name: ListSystemAuditLogs :many
-- Platform/security events with NO org (e.g. failed logins before org resolution). Privileged read
-- path only — never the per-tenant app path — so these events are not a black hole [review AUDIT-3].
SELECT * FROM audit_logs
WHERE organization_id IS NULL
ORDER BY created_at DESC
LIMIT $1 OFFSET $2;