-- name: CreateClient :one
INSERT INTO clients (           
     companyname,
     country_id,
     exportcompany_id,
     contactphone,
     contactemail,
     contactname
 ) VALUES (
    $1, $2, $3, $4, $5, $6
) 
RETURNING *;

-- name: GetClient :one
SELECT * FROM clients
WHERE id = $1 LIMIT 1;

-- name: ListClients :many
SELECT companyname,expname,country_id,countryname,exportcompany_id,clients.created_at created_at,
contactphone,contactemail,contactname,clients.id as client_id,clients.status client_status FROM clients
JOIN countries on countries.id = clients.country_id
JOIN exportcompanies on exportcompanies.id = clients.exportcompany_id
WHERE clients.exportcompany_id = $3 
AND clients.status = $4 
ORDER BY clients.id desc
LIMIT $1 OFFSET $2 ;

-- name: UpdateClient :one
UPDATE clients
  set 
     companyname = COALESCE(sqlc.narg(companyname), companyname),
     country_id = COALESCE(sqlc.narg(country_id), country_id),
     exportcompany_id = COALESCE(sqlc.narg(exportcompany_id), exportcompany_id),
     contactphone = COALESCE(sqlc.narg(contactphone), contactphone),
     contactemail = COALESCE(sqlc.narg(contactemail), contactemail),
     contactname =  COALESCE(sqlc.narg(contactname), contactname)
WHERE id = sqlc.arg(id)
RETURNING *;

-- name: DeleteClient :exec
DELETE FROM clients WHERE id= $1;


-- name: ListExportCompanyClients :many
SELECT companyname,expname,country_id,
countryname,clients.exportcompany_id exporter_id,
clients.created_at created_at,contactphone,clients.id client_id,
contactemail,contactname,clients.status client_status FROM clients
JOIN countries on countries.id = clients.country_id
JOIN exportcompanies on exportcompanies.id = clients.exportcompany_id
WHERE clients.exportcompany_id = $1 ORDER BY clients.created_at DESC;


-- name: ClientsCount :one
SELECT COUNT(id) from clients
Where status = $1 
AND   exportcompany_id = $2;

-- name: UpdateClientStatus :one
UPDATE clients
  set 
     status = $2
WHERE id = $1
RETURNING *;


-- name: DeactivateClient :one
UPDATE clients
  set 
     status = $2
WHERE id = $1
RETURNING *;