-- name: CreatePackHouse :one
INSERT INTO packhouses (
    phname,
    phregno,
    contactname,
    contactphone,
    contactemail,
    streetaddress,
    district_id,
    long,
    lat,
    expirydate,
    regstatus,
    parish_id,
    village_id
 ) VALUES (
      $1,$2,$3,$4,$5, $6,$7,$8,$9,$10,$11,$12,$13
 ) RETURNING *;


-- name: GetPackHouse :one
SELECT packhouses.phname,packhouses.phregno,
packhouses.contactname,packhouses.contactphone,
packhouses.contactemail,packhouses.streetaddress,
packhouses.district_id,packhouses.long,packhouses.lat,
packhouses.expirydate,packhouses.regstatus,
packhouses.parish_id,exportcompanies.expsubcounty_id,
packhouses.village_id,packhouses.created_at, packhouses.id FROM packhouses
JOIN  packhouse_exporter  on packhouse_exporter.packhouse_id = packhouses.id
JOIN  exportcompanies     on exportcompanies.id  =  packhouse_exporter.exportcompany_id
WHERE packhouses.id = $1 LIMIT 1;

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

-- name: UpdatePackHouse :one
UPDATE packhouses
  set
    phname= $2,
    phregno= $3,
    contactname= $4,
    contactphone= $5,
    contactemail= $6,
    streetaddress= $7,
    district_id= $8,
    parish_id= $9,
    village_id= $10,
    long = $11,
    lat = $12,
    expirydate = $13,
    regstatus  = $14
WHERE id = $1
RETURNING *;

-- name: DeletePackHouse :exec
DELETE FROM packhouses WHERE id= $1;

-- name: AttachExporterPackhouse :one
INSERT INTO packhouse_exporter (
   exportcompany_id,
   packhouse_id
)VALUES (
  $1,
  $2
 ) RETURNING *;

-- name: SearchPackhouse :many
SELECT *
FROM  packhouses
WHERE LOWER(phname) LIKE '%' || LOWER($1) || '%';

-- name: DeactivatePackhouse :one
UPDATE packhouses
  set
    regstatus = $2
WHERE id = $1
RETURNING *;

-- name: UpdatePackhouseGps :one
UPDATE packhouses
  set
    long = $2,
    lat  = $3
WHERE id = $1
RETURNING *;

-- name: UpdatePackhouseStatus :one
UPDATE packhouses
  set
    regstatus = $2
WHERE  id = $1
RETURNING *;

-- name: ExporterPackhouses :many
SELECT packhouses.phname,packhouses.phregno,packhouses.contactname,packhouses.contactphone,
packhouses.contactemail,packhouses.streetaddress FROM packhouse_exporter
LEFT JOIN packhouses on packhouses.id = packhouse_exporter.packhouse_id
WHERE packhouse_exporter.exportcompany_id = $1;

-- name: DetachPackhouse :exec
DELETE FROM packhouse_exporter WHERE exportcompany_id= $1;

-- name: GetExporterHouse :one
SELECT packhouse_id FROM packhouse_exporter
WHERE exportcompany_id = $1;

-- name: AllPackhouses :many
SELECT * FROM packhouses;

-- name: UserPackhouses :many
SELECT packhouses.phname,packhouses.phregno,packhouses.contactname,packhouses.contactphone,
packhouses.contactemail,packhouses.streetaddress FROM user_packhouse
LEFT JOIN packhouses on packhouses.id = user_packhouse.packhouse_id
WHERE  user_packhouse.packhouse_id = $1
AND    user_packhouse.user_id = $2;


-- name: GetGlobalPackhouseCount :one
SELECT COUNT(id) FROM packhouses
WHERE regstatus = $1;

-- name: GetCompanyPackhouses :one
SELECT packhouse_id from packhouse_exporter
WHERE  exportcompany_id = $1;
