-- name: CreateVillage :one
INSERT INTO villages (
     village,
     parish_id,
     code
 ) VALUES (
    $1, $2, $3
)
RETURNING *;

-- name: GetVillage :one
SELECT * FROM villages
WHERE id = $1 LIMIT 1;

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

-- name: UpdateVillage :one
UPDATE villages
  set
     village      = COALESCE(sqlc.narg(village), village),
     parish_id    = COALESCE(sqlc.narg(parish_id), parish_id),
     code         = COALESCE(sqlc.narg(code), code)
WHERE id = sqlc.arg(id)
RETURNING *;

-- name: AllVillages :many
SELECT parishes.parish, parishes.code,districts.name district_name,districts.code district_code,
village,villages.created_at,villages.id,districts.id as district_id,parishes.id as parish_id,
subcounties.id as subcounty_id, villages.id as villages_id FROM villages
JOIN parishes    on parishes.id = villages.parish_id
JOIN subcounties on subcounties.id = parishes.subcounty_id
JOIN districts   on districts.id = subcounties.district_id;

-- name: AllVillagesForDistrict :many
SELECT * FROM villages
WHERE parish_id = $1;

-- name: GetVillageIdByName :one
SELECT id FROM villages
WHERE village = $1;
