-- name: CreateExitPoint :one
INSERT INTO exitpoints (
    expcode,     
    modeoftransport_id,                             
    expdescription,
    village_id,
    parish_id,
    district_id,
    longtitude,
    latitude,
    expwhatsapp
 ) VALUES (
    $1, 
    $2, 
    $3, 
    $4, 
    $5, 
    $6, 
    $7,
    $8,
    $9
 ) RETURNING *;

-- name: GetExitPoint :one
SELECT * FROM exitpoints
WHERE id = $1 LIMIT 1;

-- name: ListExitPoints :many
SELECT * FROM exitpoints
ORDER BY id LIMIT $1
OFFSET $2;

-- name: AllExitPoints :many
SELECT expcode,exitpoints.id,expdescription,village_id,parish_id,district_id,expwhatsapp
motdescription,modeoftransport_id mot_id, exitpoints.created_at created_at FROM exitpoints
JOIN modesoftransport on modesoftransport.id = exitpoints.modeoftransport_id
LIMIT $1
OFFSET $2;

-- name: ListAllExitPoints :many
SELECT expcode,exitpoints.id,expdescription,village,parish,districts.name,expwhatsapp,
motdescription,modeoftransport_id mot_id, exitpoints.created_at created_at FROM exitpoints
JOIN modesoftransport on modesoftransport.id = exitpoints.modeoftransport_id
JOIN villages  on villages.id  = exitpoints.village_id
JOIN parishes  on parishes.id  = exitpoints.parish_id
JOIN districts on districts.id = exitpoints.district_id;

-- name: UpdateExitPoint :one
UPDATE exitpoints
  set 
    expcode = $2,     
    modeoftransport_id = $3,                             
    expdescription = $4,
    village_id = $5,
    parish_id  = $6,
    district_id = $7,
    expwhatsapp = $8
WHERE id = $1
RETURNING *;

-- name: DeleteExitPoint :exec
DELETE FROM exitpoints WHERE id= $1;


-- name: GetExitPointIDByName :one
SELECT id FROM exitpoints
WHERE  expdescription = $1 LIMIT 1;

-- name: GetExitPointIDByCode :one
SELECT id FROM exitpoints
WHERE  expcode = $1 LIMIT 1;

-- name: ListAllExitPointIDs :many
SELECT id from exitpoints;

-- name: UpdateExitPointGps :one
UPDATE exitpoints
     SET 
       latitude = $2,
       longtitude = $3
WHERE id = $1
RETURNING *;