-- name: CreateClearingAgent :one
INSERT INTO clearing_agents (name, nin, exihandler_id)
VALUES ($1, $2, $3)
RETURNING id, name, nin, exihandler_id, created_at, updated_at;


-- name: GetAllClearingAgents :many
SELECT clearing_agents.id, clearing_agents.name, clearing_agents.nin, clearing_agents.exihandler_id, 
clearing_agents.created_at, clearing_agents.updated_at, 
exithandlers.exhcode, exithandlers.exhdescription
FROM clearing_agents
JOIN exithandlers on exithandlers.id = clearing_agents.exihandler_id;


-- name: GetClearingAgentByID :one
SELECT id, name, nin, exihandler_id, created_at, updated_at
FROM clearing_agents
WHERE id = $1;


-- name: UpdateClearingAgent :one
UPDATE clearing_agents
SET name = $1, nin = $2, exihandler_id = $3, updated_at = NOW()
WHERE id = $4
RETURNING id, name, nin, exihandler_id, created_at, updated_at;


-- name: DeleteClearingAgent :exec
DELETE FROM clearing_agents
WHERE id = $1;


-- name: GetClearingAgentsByExihandlerID :many
SELECT id, name, nin, exihandler_id, created_at, updated_at
FROM clearing_agents
WHERE exihandler_id = $1;


-- name: CheckIfNINExists :one
SELECT id, name, nin, exihandler_id, created_at, updated_at
FROM clearing_agents
WHERE nin = $1;


