-- name: CreateParish :one
INSERT INTO parishes (           
     parish,
     subcounty_id,
     code
 ) VALUES (
    $1, $2, $3
) 
RETURNING *;

-- name: GetParish :one
SELECT * FROM parishes
WHERE id = $1 LIMIT 1;

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

-- name: UpdateParish :one
UPDATE parishes
  set 
     parish      = COALESCE(sqlc.narg(parish), parish),
     code        = COALESCE(sqlc.narg(code), code),
     subcounty_id = COALESCE(sqlc.narg(subcounty_id), subcounty_id)
WHERE id = sqlc.arg(id)
RETURNING *;

-- name: AllParishesForDistrict :many
SELECT * FROM parishes
WHERE subcounty_id = $1;

-- name: AllParishes :many
SELECT parish,subcounties.code subcounty_code,
subcounties.name subcounty_name,
subcounties.id subcounty_id,
parishes.id parish_id,
parishes.code parish_code, 
parishes.created_at,districts.name, districts.id as district_id FROM parishes
JOIN  subcounties ON subcounties.id = parishes.subcounty_id
JOIN  districts   on districts.id   = subcounties.district_id;

-- name: GetParishIdByName :one
SELECT id FROM parishes
WHERE parish = $1 LIMIT 1;

-- name: GetParishByName :one
SELECT * FROM parishes
WHERE parish = $1 LIMIT 1;

-- name: GetParishBySubCounty :many
SELECT * FROM parishes
WHERE subcounty_id = $1;

-- name: GetAdminUnits :many
SELECT parishes.parish parish_name,subcounties.code subcounty_code,
subcounties.name subcounty_name,subcounties.id subcounty_id,
parishes.id parish_id,districts.name district_name,
districts.id district_id,parishes.id,parishes.code parish_code,
districts.created_at,villages.village,villages.id village_id FROM districts
LEFT JOIN  subcounties   on subcounties.district_id    =  districts.id
LEFT JOIN  parishes      on parishes.subcounty_id      = subcounties.id
LEFT JOIN  villages      on villages.parish_id         = parishes.id
LIMIT $1 OFFSET $2;


-- name: ListAdminUnits :many
SELECT parishes.parish parish_name,subcounties.code subcounty_code,
subcounties.name subcounty_name,subcounties.id subcounty_id,
parishes.id parish_id,districts.name district_name,
districts.id district_id,parishes.id,parishes.code parish_code,
districts.created_at,villages.village,villages.id village_id FROM districts
LEFT JOIN  subcounties   on subcounties.district_id    =  districts.id
LEFT JOIN  parishes      on parishes.subcounty_id      = subcounties.id
LEFT JOIN  villages      on villages.parish_id         = parishes.id;