-- name: CreateUser :one
INSERT INTO users (
    fullname,
    nin,
    regno,
    phone,
    user_type,
    stamp,
    signature,
    email,
    password
 ) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8, $9
 ) RETURNING *;

-- name: GetUser :one
SELECT * FROM users
WHERE id = $1 LIMIT 1;

-- name: GetUserByEmail :one
SELECT * FROM users
WHERE email = $1 LIMIT 1;

-- name: GetInspector :one
SELECT * FROM users
WHERE id= $1;

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

-- name: ListQualityControllers :many
SELECT users.id as user_id, fullname,nin,regno,phone,email,user_type FROM user_exporter
LEFT JOIN users ON users.id = user_exporter.user_id
WHERE user_exporter.exportcompany_id= $3
LIMIT $1
OFFSET $2;

-- name: ListInspectors :many
SELECT users.id as user_id, fullname,nin,regno,phone,email,user_type FROM user_exporter
LEFT JOIN users ON users.id = user_exporter.user_id
WHERE users.user_type = $3 AND user_exporter.exportcompany_id= $4
LIMIT $1
OFFSET $2;

-- name: ListDcicAdmins :many
SELECT users.id as user_id, fullname,nin,regno,phone,email,status,created_at FROM users
WHERE user_type = $3
ORDER BY id LIMIT $1
OFFSET $2;


-- name: GetAllDCICAdmins :many
SELECT * from users
where user_type = 'DCIC-ADMIN';

-- name: ListSuperAdmins :many
SELECT users.id as user_id, fullname,nin,regno,phone,email,status,created_at FROM users
WHERE user_type = $3
ORDER BY id LIMIT $1
OFFSET $2;


-- name: UpdateUserPassword :one
UPDATE users
   set
   password  = $1
WHERE id = $2
RETURNING *;


-- name: UpdateUserPhoneAndFullname :one
UPDATE users
   set
   phone  = $1,
   fullname = $2
WHERE id = $3
RETURNING *;

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

-- name: UpdateUser :one
UPDATE users
  set
  fullname       = COALESCE(sqlc.narg(fullname), fullname),
  nin            = COALESCE(sqlc.narg(nin), nin),
  regno          = COALESCE(sqlc.narg(regno), regno),
  phone          = COALESCE(sqlc.narg(phone), phone),
  stamp          = COALESCE(sqlc.narg(stamp), stamp),
  signature      = COALESCE(sqlc.narg(signature), signature),
  email          = COALESCE(sqlc.narg(email), email),
  password       = COALESCE(sqlc.narg(password), password),
  user_type       = sqlc.arg(user_type)
WHERE id = sqlc.arg(id)
RETURNING *;

-- name: DeleteUser :exec
DELETE FROM users WHERE id= $1;

-- name: AttachUserExpoter :exec
INSERT INTO user_exporter (
    user_id,
    exportcompany_id
 ) VALUES (
    $1, $2
 );


-- name: CheckUserExistance :one
SELECT EXISTS (
    SELECT 1
    FROM  user_exporter
    WHERE user_id = $1
    AND   exportcompany_id = $2
) AS exists;

-- name: AttachUserPackhouse :exec
INSERT INTO user_packhouse (
    user_id,
    packhouse_id
 ) VALUES (
    $1, $2
 );

-- name: AttachUserZones :exec
INSERT INTO user_zones (
    user_id,
    zone_id
 ) VALUES (
    $1, $2
 );

-- name: ReturnUserExportCompanies :many
 SELECT expname,expregno,exprexno,exportcompanies.id exporter_id from user_exporter
 JOIN   users on users.id = user_exporter.user_id
 JOIN   exportcompanies on exportcompanies.id = user_exporter.exportcompany_id
 WHERE  user_exporter.exportcompany_id = $1;

-- name: ReturnUserPackhouses :many
 SELECT  phname,phregno,regstatus,users.created_at from user_packhouse
 JOIN    users on users.id = user_packhouse.user_id
 JOIN    packhouses on packhouses.id = user_packhouse.packhouse_id
 WHERE   user_packhouse.user_id = $1;

-- name: ReturnUserCompanies :many
 SELECT expname,expregno,exprexno,exportcompanies.id exporter_id from user_exporter
 JOIN   users  on users.id = user_exporter.user_id
 JOIN   exportcompanies on exportcompanies.id = user_exporter.exportcompany_id
 WHERE  user_exporter.user_id = $1;

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

-- name: ReturnQualityControllers :many
 SELECT fullname,users.id as user_id, users.nin,users.status,user_exporter.exportcompany_id,users.phone,users.user_type,users.email,users.created_at as created_at FROM users
 LEFT JOIN user_exporter on user_exporter.user_id = users.id
 WHERE user_type = $1 AND user_exporter.exportcompany_id = $2;

-- name: ReturnAllQualityControllers :many
 SELECT users.fullname,users.id as user_id,
 users.phone,users.user_type,users.email,
 users.created_at as created_at,users.status FROM users
 LEFT JOIN user_exporter on user_exporter.user_id = users.id
 WHERE user_type = $3 LIMIT $1
 OFFSET $2;

-- name: ReturnPackhouseInspectors :many
 SELECT users.id as user_id,users.status, fullname,phone,user_type,email,users.created_at as created_at FROM users
 WHERE user_type = 'PACKHOUSE-INSPECTOR' LIMIT $1
 OFFSET $2;

-- name: ReturnZonalPackhouseInspectors :many
 SELECT users.id as user_id,users.status, fullname,phone,user_type,email,users.created_at as created_at FROM users
 WHERE user_type = 'ZONAL-PACKHOUSE-INSPECTOR' LIMIT $1
 OFFSET $2;

-- name: ReturnZonalBorderInspectors :many
 SELECT users.id as user_id,users.status, fullname,phone,user_type,email,users.created_at as created_at FROM users
 WHERE user_type = 'ZONAL-BORDER-INSPECTOR' LIMIT $1
 OFFSET $2;

-- name: ReturnBorderInspectors :many
 SELECT users.id as user_id,users.status,fullname,phone,user_type,email,users.created_at as created_at FROM users
 WHERE user_type = 'BORDER-INSPECTOR'
 LIMIT $1
 OFFSET $2;

-- name: ReturnSupportAccounts :many
 SELECT users.id as user_id, users.status, fullname,phone,user_type,email,users.created_at as created_at FROM users
 LEFT JOIN user_exporter on user_exporter.user_id = users.id
 WHERE user_type = 'SUPPORT'
 LIMIT $1
 OFFSET $2;


-- name: ReturnExporterAdmins :many
 SELECT users.id as user_id, exportcompanies.id as exportcompany_id, users.status,users.regno, users.nin, fullname,phone,user_type,email,users.created_at as created_at FROM user_exporter
 LEFT JOIN exportcompanies on exportcompanies.id = user_exporter.exportcompany_id
 LEFT JOIN users on users.id = user_exporter.user_id
 WHERE exportcompanies.id = $3
 AND   users.user_type IN ('EXPORTER','MANAGER')
 ORDER BY users.created_at DESC
 LIMIT  $1
 OFFSET $2;


-- name: ReturnExporterAdmin :one
 SELECT users.id as user_id, users.status,users.regno, users.nin, users.fullname,users.phone,
 users.user_type,users.email,users.created_at as created_at, users.signature, users.stamp FROM users
 LEFT JOIN user_exporter on user_exporter.user_id = users.id
 WHERE user_type = 'EXPORTER' AND user_exporter.exportcompany_id = $1
 LIMIT 1;

-- name: ReturnAllBorderInspectors :many
SELECT user_id FROM roastercount
ORDER BY counter ASC LIMIT 2;

-- name: BorderInspectorCount :one
SELECT COUNT(id)
FROM  users
WHERE user_type = 'BORDER-INSPECTOR';

-- name: QualityControllerCount :one
SELECT COUNT(id)
FROM users
WHERE user_type = 'QUALITY-CONTROLLER';

-- name: SuperAdminCount :one
SELECT COUNT(id)
FROM users
WHERE user_type = 'SUPER-ADMIN';

-- name: ExporterCount :one
SELECT COUNT(id)
FROM users
WHERE user_type = 'EXPORTER';

-- name: PackhouseInspectorCount :one
SELECT COUNT(id)
FROM users
WHERE user_type = 'PACKHOUSE-INSPECTOR';

-- name: SupportAccountCount :one
SELECT COUNT(id)
FROM users
WHERE user_type = 'SUPPORT';

-- name: DICICCount :one
SELECT COUNT(id)
FROM users
WHERE user_type = 'DCIC-ADMIN';


-- name: ReturnQcpsPerExporter :many
SELECT users.user_type as usertype,users.id as user_id, users.fullname as fullname from user_exporter
INNER JOIN users on users.id = user_exporter.user_id
WHERE user_exporter.exportcompany_id = $1
AND   users.user_type = 'QUALITY-CONTROLLER';


-- name: ReturnQcpsByEmail :one
SELECT  * from users
WHERE  user_type = 'QUALITY-CONTROLLER'
AND    email = $1 LIMIT 1;

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

-- name: ExporterAdminManagerCount :one
SELECT COUNT(users.id)
FROM  users
LEFT JOIN user_exporter on user_exporter.user_id = users.id
WHERE users.user_type IN ('MANAGER', 'EXPORTER')
AND   user_exporter.exportcompany_id = $1;

-- name: ReturnPackHouseInspectorForExporter :one
SELECT users.fullname, users.phone, packhouses.phname, packhouses.contactphone, packhouses.streetaddress, exportcompanies.expname FROM users
INNER JOIN user_exporter on user_exporter.user_id = users.id
INNER JOIN packhouse_exporter on packhouse_exporter.exportcompany_id = user_exporter.exportcompany_id
INNER JOIN packhouses         on packhouses.id   = packhouse_exporter.packhouse_id
INNER JOIN exportcompanies    on exportcompanies.id = user_exporter.exportcompany_id
WHERE users.user_type = 'PACKHOUSE-INSPECTOR'
AND   user_exporter.exportcompany_id = $1;


-- name: ReturnBorderInspectorForExitPointDate :many
SELECT users.fullname, users.phone, users.id, exitpoints.expdescription, exitpoints.expcode FROM users
INNER  JOIN exitpointroaster on exitpointroaster.user_id = users.id
INNER  JOIN plannedconsignments_tpdetails on plannedconsignments_tpdetails.exitpoint_id = exitpointroaster.exitpoint_id
INNER  JOIN exitpoints on exitpoints.id   =  exitpointroaster.exitpoint_id
WHERE  users.user_type = 'BORDER-INSPECTOR'
AND    exitpointroaster.date = $1
AND    plannedconsignments_tpdetails.plannedconsignments_id = $2;


-- name: ReturnAllExporterUsers :many
SELECT users.fullname, users.phone, users.id FROM users
INNER JOIN user_exporter on user_exporter.user_id = users.id
WHERE  user_exporter.exportcompany_id = $1
AND    users.user_type IN ('QUALITY-CONTROLLER','EXPORTER','MANAGER');

-- name: UserSignature :one
SELECT  signature FROM users
WHERE  id = $1;


-- name: UpdateUserSignature :one
UPDATE users
  set
  signature  = $2
WHERE id = $1
RETURNING *;

-- name: UpdateExporterStamp :one
UPDATE users
  set
  stamp  = $2
WHERE id = $1
RETURNING *;

-- name: FetchExporterStamp :one
SELECT users.stamp FROM users
INNER  JOIN user_exporter on user_exporter.user_id = users.id
WHERE  users.user_type = 'EXPORTER'
AND    user_exporter.exportcompany_id = $1;

-- name: ToggleUserActiveStatus :one
UPDATE users
  set
  status  = $2
WHERE id  = $1
AND   user_type = $3
RETURNING *;

-- name: CheckQcExporterExistance :one
SELECT EXISTS (
   SELECT 1 from user_exporter
   WHERE exportcompany_id = $1
   AND   user_id = $2
);

-- name: SwitchInspectorRole :one
UPDATE users
  set
  user_type  = $2
WHERE id = $1
RETURNING *;

-- name: ReturnZonalInspectors :many
SELECT * from users
WHERE user_type = 'ZONAL-INSPECTOR';

-- name: ZonalInspectorsStats :one
SELECT count(id) from users
WHERE user_type = 'ZONAL-INSPECTOR';

-- name: ZonalPackhouseInspectorsStats :one
SELECT count(id) from users
WHERE user_type = 'ZONAL-PACKHOUSE-INSPECTOR';

-- name: ZonalBorderInspectorsStats :one
SELECT count(id) from users
WHERE user_type = 'ZONAL-BORDER-INSPECTOR';

-- name: NPPOInspectorsStats :one
SELECT count(id) from users
WHERE user_type = 'NPPO-INSPECTOR';

-- name: ReturnNPPOInspectors :many
SELECT * from users
WHERE user_type = 'NPPO-INSPECTOR';

-- name: ReturnPaginatedNPPOInspectors :many
SELECT * from users
WHERE user_type = 'NPPO-INSPECTOR'
LIMIT $1 OFFSET $2;

-- name: SearchPackhouseInspector :many
SELECT users.id as user_id,users.status,users.fullname,
users.phone,users.user_type,users.email,users.created_at
FROM  users
WHERE user_type = 'PACKHOUSE-INSPECTOR'
AND   LOWER(fullname) LIKE '%' || LOWER($1) || '%';


-- name: SearchZonalPackhouseInspector :many
SELECT users.id as user_id,users.status,users.fullname,
users.phone,users.user_type,users.email,users.created_at
FROM  users
WHERE user_type = 'ZONAL-PACKHOUSE-INSPECTOR'
AND   LOWER(fullname) LIKE '%' || LOWER($1) || '%';


-- name: SearchZonalBorderInspector :many
SELECT users.id as user_id,users.status,users.fullname,
users.phone,users.user_type,users.email,users.created_at
FROM  users
WHERE user_type = 'ZONAL-BORDER-INSPECTOR'
AND   LOWER(fullname) LIKE '%' || LOWER($1) || '%';


-- name: SearchQualityController :many
SELECT *
FROM  users
WHERE user_type = 'QUALITY-CONTROLLER'
AND   LOWER(fullname) LIKE '%' || LOWER($1) || '%';


-- name: SearchZonalInspectors :many
SELECT *
FROM  users
WHERE user_type = 'ZONAL-INSPECTOR'
AND   LOWER(fullname) LIKE '%' || LOWER($1) || '%';


-- name: SearchBorderInspectors :many
SELECT *
FROM  users
WHERE user_type = 'BORDER-INSPECTOR'
AND   LOWER(fullname) LIKE '%' || LOWER($1) || '%';


-- name: ReturnQualityControllersPerExporter :many
SELECT users.fullname,users.nin,users.regno,users.phone,
users.user_type,users.email,users.id,user_exporter.exportcompany_id from users
JOIN  user_exporter on user_exporter.user_id = users.id
WHERE users.user_type = 'QUALITY-CONTROLLER'
AND      user_exporter.exportcompany_id = $1 
ORDER BY users.created_at DESC;

-- name: AssignPackhouseInspectorToExporters :one
INSERT INTO user_exporter(
    "user_id",
    "exportcompany_id"
 ) VALUES (
     $1,
     $2
  )
RETURNING *;


-- name: CheckEmailExistance :one
SELECT EXISTS (
   SELECT 1 from users
   WHERE email = $1
);

-- name: GetPackhouseInspectorByID :one
SELECT * FROM users
WHERE user_type IN ('PACKHOUSE-INSPECTOR','ZONAL-PACKHOUSE-INSPECTOR')
AND id = $1;

-- name: GetDICUsers :many
SELECT users.phone,users.fullname,users.email from users
WHERE users.user_type = 'DCIC-ADMIN';

-- name: GetQCCompany :one
SELECT exportcompanies.expname from user_exporter
JOIN   users           on users.id           = user_exporter.user_id
JOIN   exportcompanies on exportcompanies.id = user_exporter.exportcompany_id
WHERE  users.user_type = 'QUALITY-CONTROLLER' AND users.id = $1
LIMIT  1;

-- name: GetPackHouseInspectorCompanies :many
SELECT exportcompanies.expname,exportcompanies.expregno,packhouses.phname,packhouses.id as packhouse_id,packhouses.created_at as packhouse_creation_date,
exportcompanies.created_at as exportcompanies_creation_date,user_exporter.created_at as assignment_date,
exportcompanies.id as exportcompany_id,packhouses.phregno,exportcompanies.expaddress1,exportcompanies.expcompanyphone,exportcompanies.expemail,
exportcompanies.registrationstatus,exportcompanies.validitydate from user_exporter
JOIN   users on users.id = user_exporter.user_id
JOIN   exportcompanies on exportcompanies.id = user_exporter.exportcompany_id
JOIN   packhouse_exporter on packhouse_exporter.exportcompany_id = exportcompanies.id
JOIN   packhouses on packhouses.id  = packhouse_exporter.packhouse_id
WHERE  users.user_type='PACKHOUSE-INSPECTOR' AND users.id = $1;

-- name: DetachPackhouseInspectorFromCompany :exec
DELETE FROM user_exporter where
exportcompany_id = $1 AND user_id = $2;

-- name: CreateUserLogs :exec
INSERT INTO userlogs (
     id,
     user_id,
     activity_type,
     activity_description,
     usertype,
     organisation,
     organisationid,
     ip_address,
     user_agent
 ) VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6,
    $7,
    $8,
    $9
 ) RETURNING *;


-- name: ReturnPaginatedLogs :many
SELECT userlogs.id,users.fullname,
userlogs.user_id,userlogs.activity_type,
userlogs.activity_description,userlogs.usertype,
userlogs.organisation,userlogs.organisationid,
userlogs.ip_address,userlogs.created_at,userlogs.status,
userlogs.user_agent from userlogs
JOIN users on users.id = userlogs.user_id
LIMIT $1 OFFSET $2;


-- name: FetchAllxporterLogs :many
SELECT userlogs.id,users.fullname,
userlogs.user_id,userlogs.activity_type,
userlogs.activity_description,userlogs.usertype,
userlogs.organisation,userlogs.organisationid,
userlogs.ip_address,userlogs.created_at,userlogs.status,userlogs.user_agent from userlogs
JOIN  users on users.id = userlogs.user_id
WHERE organisationid = $1 LIMIT $2 OFFSET $3;

-- name: GetFilteredLogs :many
SELECT userlogs.id,users.fullname,
userlogs.user_id,userlogs.activity_type,
userlogs.activity_description,userlogs.usertype,
userlogs.organisation,userlogs.organisationid,
userlogs.ip_address,userlogs.created_at,userlogs.status,userlogs.user_agent FROM userlogs
JOIN  users on users.id = userlogs.user_id
WHERE (COALESCE($1, '') = '' OR usertype = $1)
AND   (COALESCE($2::integer, 0) = 0 OR organisationid = $2::integer)
AND   (COALESCE($3, '') = '' OR activity_type = $3)
ORDER BY userlogs.created_at DESC LIMIT $4 OFFSET $5;


-- name: FetchDistinctType :many
SELECT DISTINCT activity_type
FROM userlogs;

-- name: GetAllUsers :many
SELECT * from users;

-- name: GetFetchUserByUserType :many
SELECT * from users where users.user_type = $1;


-- name: UpdateUserEmail :one
UPDATE users
  set
  email  = $2
WHERE id = $1
RETURNING *;


-- name: GetTodaysLogs :many
SELECT * 
FROM userlogs;


-- name: CountGlobalUsers :one
Select COUNT(DISTINCT id) AS unique_users from users;


-- name: GetTodayUserLogsByUserType :one
SELECT COUNT(DISTINCT user_id) AS unique_users
FROM userlogs
WHERE
  created_at >= CURRENT_DATE 
  AND usertype = $1;


-- name: TotalUsersToday :one
SELECT COUNT(DISTINCT user_id) AS unique_users
FROM userlogs
WHERE
  created_at >= CURRENT_DATE;