-- name: CreateExportCompany :one
INSERT INTO exportcompanies (
    expname,
    expregno,
    expaddress1,
    expdistrict_id,
    expvillage_id,
    expparish_id,
    expsubcounty_id,
    expcompanyphone,
    expemail,
    registrationstatus,
    validitydate
 ) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8, $9,$10,$11
 ) RETURNING *;

-- name: GetExportCompany :one
SELECT exportcompanies.id,exportcompanies.expname,exportcompanies.expregno,exportcompanies.logo,
exportcompanies.expaddress1,exportcompanies.expdistrict_id,exportcompanies.expvillage_id,
exportcompanies.expparish_id,exportcompanies.expcompanyphone,exportcompanies.expemail,
exportcompanies.expsubcounty_id,exportcompanies.registrationstatus,exportcompanies.validitydate,packhouses.phname,
packhouses.phregno,packhouses.id packhouse_id,packhouses.contactname,packhouses.contactphone,packhouses.contactemail,
packhouses.streetaddress,packhouses.district_id,packhouses.long,packhouses.lat,packhouses.expirydate,packhouses.regstatus FROM exportcompanies
LEFT JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
LEFT JOIN packhouses         on packhouses.id   = packhouse_exporter.packhouse_id
WHERE exportcompanies.id = $1;

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


-- name: GetExporterCompanyID :one
SELECT id FROM exportcompanies
WHERE expname = $1;



-- name: UpdateExportCompany :one
UPDATE exportcompanies
  set
    expname= $2,
    expregno = $3,
    expaddress1 = $4,
    expdistrict_id = $5,
    expvillage_id = $6,
    expparish_id = $7,
    expcompanyphone = $8,
    expemail = $9,
    registrationstatus = $10,
    validitydate = $11
WHERE id = $1
RETURNING *;

-- name: DeactivateExportCompany :one
UPDATE exportcompanies
  set
    registrationstatus = $2
WHERE id = $1
RETURNING *;

-- name: UploadCompanyLogo :one
UPDATE exportcompanies
  set
    logo = $2
WHERE id = $1
RETURNING *;

-- name: DeleteExportCompany :exec
DELETE FROM exportcompanies WHERE id= $1;

-- name: SearchCompanyName :many
SELECT *
FROM  exportcompanies
WHERE LOWER(expname) LIKE '%' || LOWER($1) || '%';


-- name: AllExporters :many
SELECT * FROM exportcompanies;


-- name: FetchCompanyLogo :one
Select logo from exportcompanies
where id = $1 LIMIT 1;

-- name: CountQualityControllers :one
SELECT COUNT(user_exporter.user_id) from user_exporter
JOIN   users on users.id = user_exporter.user_id
WHERE  user_exporter.exportcompany_id=$1
AND    users.user_type = 'QUALITY-CONTROLLER';

-- name: CountExporterManagers :one
SELECT COUNT(users.id) from user_exporter
JOIN users on users.id = user_exporter.exportcompany_id
WHERE user_exporter.exportcompany_id = $1
AND   users.user_type = 'EXPORTER' OR users.user_type= 'MANAGER';

-- name: CountReceptionForms :one
SELECT COUNT(id) from receptionform
where receptionform.exportcompany_id = $1;

-- name: CountOrders :one
SELECT COUNT(id) from orders
where orders.exportcompany_id = $1;


-- name: CountManagers :one
SELECT COUNT(id) from users
where users.user_type = 'MANAGER';


-- name: CountConsignmentForms :one
SELECT COUNT(id) from plannedconsignments
where plannedconsignments.exportcompany_id = $1;

-- name: CountExporterFarmersWithStatus :one
SELECT COUNT(farmers.id) FROM farmers
INNER JOIN farmer_exporter on farmer_exporter.farmer_id = farmers.id
WHERE farmer_exporter.exportcompany_id = $1
AND   farmers.status = $2;


-- name: ExportCompaniesPastExpiry :many
SELECT * from exportcompanies where
validitydate < $1;

-- name: CountExporterFarmers :one
SELECT COUNT(id) from farmer_exporter
where farmer_exporter.exportcompany_id = $1;

-- name: CountExporterClients :one
SELECT COUNT(id) from clients
where clients.exportcompany_id = $1;

-- name: CountCompletedQcp1s :one
SELECT COUNT(id) from qcp1
where exportcompany_id = $1
AND status='Passed';

-- name: CountCompletedQcp2s :one
SELECT COUNT(id) from qcp2
where exportcompany_id = $1
AND   status ='Passed';

-- name: CountCompletedQcp3s :one
SELECT COUNT(id) from qcp3
where exportcompany_id = $1
AND status='FitForExport';

-- name: CountExporterReceptionForms :one
SELECT COUNT(id) from receptionform
where exportcompany_id = $1
AND  status= 'Submitted';

-- name: CountExporterPackinglists :one
SELECT COUNT(id) from packinglist
where exportcompany_id = $1
AND  status= 'Submitted';

-- name: DetachQcFromExporter :exec
DELETE from user_exporter
where exportcompany_id = $1
AND   user_id  = $2
RETURNING *;

-- name: TotalExportCompanies :one
SELECT COUNT(id) from exportcompanies;

-- name: TotalQualityControllers :one
SELECT COUNT(id) from users
where user_type = 'QUALITY-CONTROLLER';

-- name: TotalPackhouseInspectors :one
SELECT COUNT(id) from users
where user_type = 'PACKHOUSE-INSPECTOR';

-- name: TotalBorderInspectors :one
SELECT COUNT(id) from users
where user_type = 'PACKHOUSE-INSPECTOR';

-- name: TotalZonalInspectors :one
SELECT COUNT(id) from users
where user_type = 'PACKHOUSE-INSPECTOR';