-- name: CreateSubCounty :one
INSERT INTO subcounties (
     name,
     district_id,
     code
 ) VALUES (
    $1, $2, $3
)
RETURNING *;

-- name: GetSubCounty :one
SELECT * FROM subcounties
WHERE id = $1 LIMIT 1;

-- name: ListSubCounties :many
SELECT subcounties.name,subcounties.id as subcounty_id,districts.id as district_id, parishes.code, parishes.parish FROM subcounties
JOIN   parishes  ON parishes.subcounty_id = subcounties.id
JOIN   districts ON districts.id          = subcounties.district_id
LIMIT $1 OFFSET $2;

-- name: UpdateSubCounty :one
UPDATE subcounties
  set
     name         = COALESCE(sqlc.narg(name), name),
     district_id    = COALESCE(sqlc.narg(district_id), district_id),
     code         = COALESCE(sqlc.narg(code), code)

WHERE id = sqlc.arg(id)
RETURNING *;

-- name: AllSubcounties :many
SELECT subcounties.name subcounty_name,subcounties.id as subcounty_id,
subcounties.district_id, districts.name district_name,
subcounties.code from subcounties
JOIN   districts on districts.id = subcounties.district_id;

-- name: AllSubcountiesForDistrict :many
SELECT * FROM subcounties
WHERE district_id = $1;

-- name: GetSubcountyIdByName :one
SELECT id FROM subcounties
WHERE name = $1;

-- name: GetSubCountyByName :one
SELECT * FROM subcounties
WHERE name = $1;

-- name: GetSubcountyPerDistrict :many
SELECT * FROM subcounties
WHERE district_id = $1;
