namespace FMDAL.Query.Device
{
    // Required index: IX_MDEVICE_OUID_STATUS (OUID, STATUS, TENANTID)
    public static class FMDeviceQB
    {
        public const string GET_DEVICE_SQL = @"
            SELECT d.DEVICEID                           AS DeviceId,
                   d.DEVICECODE                         AS DeviceCode,
                   d.DEVICENAME                         AS DeviceName,
                   d.DEVICETYPEID                       AS DeviceTypeId,
                   ISNULL(dt.DEVICETYPENAME, '')        AS DeviceTypeName,
                   ISNULL(d.DEVICEUNIQUEDETAIL, '')     AS DeviceUniqueDetail,
                   ISNULL(d.DEVICEMACHINEIP, '')        AS DeviceMachineIp,
                   ISNULL(d.DEVICEPORTNUMBER, 0)        AS DevicePortNumber,
                   d.DEVICEDEVICELOCATIONTYPE           AS DeviceLocationType,
                   d.DEVICEDEVICEUSERTYPE               AS DeviceUserType,
                   ISNULL(d.ORGANIZATIONUNITID, -1)     AS OUId,
                   ISNULL(ou.OUNAME, '')                AS OUName,
                   ISNULL(d.USERID, -1)                 AS UserId,
                   ISNULL(d.DEVICEISVERIFIED, 0)        AS IsVerified,
                   d.DEVICESTATUS                       AS Status,
                   d.DEVICEVERSION                      AS Version,
                   d.DEVICECREATEDBYID                  AS CreatedById,
                   d.DEVICECREATEDON                    AS CreatedOn,
                   d.DEVICEMODIFIEDBYID                 AS ModifiedById,
                   d.DEVICEMODIFIEDON                   AS ModifiedOn
            FROM   MDEVICE d
            LEFT JOIN MDEVICETYPE dt ON dt.DEVICETYPEID = d.DEVICETYPEID AND dt.TENANTID = d.TENANTID
            LEFT JOIN MORGANIZATIONUNIT ou ON ou.OUID    = d.ORGANIZATIONUNITID AND ou.TENANTID = d.TENANTID
            WHERE  d.DEVICEID  = @DeviceId
            AND    d.TENANTID  = @TenantId
            AND    d.DEVICESTATUS <> 2";

        public const string GET_DEVICE_PG = @"
            SELECT d.DEVICEID                           AS ""DeviceId"",
                   d.DEVICECODE                         AS ""DeviceCode"",
                   d.DEVICENAME                         AS ""DeviceName"",
                   d.DEVICETYPEID                       AS ""DeviceTypeId"",
                   COALESCE(dt.DEVICETYPENAME, '')      AS ""DeviceTypeName"",
                   COALESCE(d.DEVICEUNIQUEDETAIL, '')   AS ""DeviceUniqueDetail"",
                   COALESCE(d.DEVICEMACHINEIP, '')      AS ""DeviceMachineIp"",
                   COALESCE(d.DEVICEPORTNUMBER, 0)      AS ""DevicePortNumber"",
                   d.DEVICEDEVICELOCATIONTYPE           AS ""DeviceLocationType"",
                   d.DEVICEDEVICEUSERTYPE               AS ""DeviceUserType"",
                   COALESCE(d.ORGANIZATIONUNITID, -1)   AS ""OUId"",
                   COALESCE(ou.OUNAME, '')              AS ""OUName"",
                   COALESCE(d.USERID, -1)               AS ""UserId"",
                   COALESCE(d.DEVICEISVERIFIED, 0)      AS ""IsVerified"",
                   d.DEVICESTATUS                       AS ""Status"",
                   d.DEVICEVERSION                      AS ""Version"",
                   d.DEVICECREATEDBYID                  AS ""CreatedById"",
                   d.DEVICECREATEDON                    AS ""CreatedOn"",
                   d.DEVICEMODIFIEDBYID                 AS ""ModifiedById"",
                   d.DEVICEMODIFIEDON                   AS ""ModifiedOn""
            FROM   MDEVICE d
            LEFT JOIN MDEVICETYPE dt ON dt.DEVICETYPEID = d.DEVICETYPEID AND dt.TENANTID = d.TENANTID
            LEFT JOIN MORGANIZATIONUNIT ou ON ou.OUID    = d.ORGANIZATIONUNITID AND ou.TENANTID = d.TENANTID
            WHERE  d.DEVICEID  = :DeviceId
            AND    d.TENANTID  = :TenantId
            AND    d.DEVICESTATUS <> 2";

        public const string GET_DEVICE_LIST_SQL = @"
            SELECT d.DEVICEID                           AS DeviceId,
                   d.DEVICECODE                         AS DeviceCode,
                   d.DEVICENAME                         AS DeviceName,
                   ISNULL(dt.DEVICETYPENAME, '')        AS DeviceTypeName,
                   ISNULL(ou.OUNAME, '')                AS OUName,
                   ISNULL(d.DEVICEISVERIFIED, 0)        AS IsVerified,
                   d.DEVICESTATUS                       AS Status
            FROM   MDEVICE d
            LEFT JOIN MDEVICETYPE dt ON dt.DEVICETYPEID = d.DEVICETYPEID AND dt.TENANTID = d.TENANTID
            LEFT JOIN MORGANIZATIONUNIT ou ON ou.OUID    = d.ORGANIZATIONUNITID AND ou.TENANTID = d.TENANTID
            WHERE  d.TENANTID      = @TenantId
            AND    d.DEVICESTATUS <> 2
            ORDER BY d.DEVICENAME
            OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY";

        public const string GET_DEVICE_LIST_COUNT_SQL = @"
            SELECT COUNT(1) FROM MDEVICE d
            WHERE d.TENANTID = @TenantId AND d.DEVICESTATUS <> 2";

        public const string GET_DEVICE_LIST_PG = @"
            SELECT d.DEVICEID                           AS ""DeviceId"",
                   d.DEVICECODE                         AS ""DeviceCode"",
                   d.DEVICENAME                         AS ""DeviceName"",
                   COALESCE(dt.DEVICETYPENAME, '')      AS ""DeviceTypeName"",
                   COALESCE(ou.OUNAME, '')              AS ""OUName"",
                   COALESCE(d.DEVICEISVERIFIED, 0)      AS ""IsVerified"",
                   d.DEVICESTATUS                       AS ""Status""
            FROM   MDEVICE d
            LEFT JOIN MDEVICETYPE dt ON dt.DEVICETYPEID = d.DEVICETYPEID AND dt.TENANTID = d.TENANTID
            LEFT JOIN MORGANIZATIONUNIT ou ON ou.OUID    = d.ORGANIZATIONUNITID AND ou.TENANTID = d.TENANTID
            WHERE  d.TENANTID      = :TenantId
            AND    d.DEVICESTATUS <> 2
            ORDER BY d.DEVICENAME
            LIMIT :MaxResult OFFSET :FirstNumber";

        public const string GET_DEVICE_LIST_COUNT_PG = @"
            SELECT COUNT(1) FROM MDEVICE d
            WHERE d.TENANTID = :TenantId AND d.DEVICESTATUS <> 2";

        public const string VALIDATE_DEVICE_SQL = @"
            SELECT d.DEVICEID AS DeviceId, d.DEVICENAME AS DeviceName,
                   CAST(1 AS BIT) AS IsValid, '' AS Message
            FROM   MDEVICE d
            WHERE  d.DEVICEID           = @DeviceId
            AND    d.DEVICEISVERIFIED   = 1
            AND    d.DEVICESTATUS      <> 2
            AND    d.TENANTID           = @TenantId";

        public const string VALIDATE_DEVICE_PG = @"
            SELECT d.DEVICEID AS ""DeviceId"", d.DEVICENAME AS ""DeviceName"",
                   TRUE AS ""IsValid"", '' AS ""Message""
            FROM   MDEVICE d
            WHERE  d.DEVICEID           = :DeviceId
            AND    d.DEVICEISVERIFIED   = 1
            AND    d.DEVICESTATUS      <> 2
            AND    d.TENANTID           = :TenantId";

        public const string SAVE_DEVICE_SQL = @"
            INSERT INTO MDEVICE
                (DEVICECODE, DEVICENAME, DEVICETYPEID, DEVICEUNIQUEDETAIL, DEVICEMACHINEIP,
                 DEVICEPORTNUMBER, DEVICEDEVICELOCATIONTYPE, DEVICEDEVICEUSERTYPE,
                 ORGANIZATIONUNITID, USERID, DEVICEISVERIFIED,
                 DEVICESTATUS, DEVICEVERSION, DEVICECREATEDBYID, DEVICECREATEDON,
                 DEVICEMODIFIEDBYID, DEVICEMODIFIEDON, TENANTID)
            VALUES
                (@DeviceCode, @DeviceName, @DeviceTypeId, @DeviceUniqueDetail, @DeviceMachineIp,
                 @DevicePortNumber, @DeviceLocationType, @DeviceUserType,
                 @OUId, @UserId, @IsVerified,
                 1, 1, @CreatedById, GETUTCDATE(),
                 @ModifiedById, GETUTCDATE(), @TenantId);
            SELECT SCOPE_IDENTITY();";

        public const string SAVE_DEVICE_PG = @"
            INSERT INTO MDEVICE
                (DEVICECODE, DEVICENAME, DEVICETYPEID, DEVICEUNIQUEDETAIL, DEVICEMACHINEIP,
                 DEVICEPORTNUMBER, DEVICEDEVICELOCATIONTYPE, DEVICEDEVICEUSERTYPE,
                 ORGANIZATIONUNITID, USERID, DEVICEISVERIFIED,
                 DEVICESTATUS, DEVICEVERSION, DEVICECREATEDBYID, DEVICECREATEDON,
                 DEVICEMODIFIEDBYID, DEVICEMODIFIEDON, TENANTID)
            VALUES
                (:DeviceCode, :DeviceName, :DeviceTypeId, :DeviceUniqueDetail, :DeviceMachineIp,
                 :DevicePortNumber, :DeviceLocationType, :DeviceUserType,
                 :OUId, :UserId, :IsVerified,
                 1, 1, :CreatedById, NOW() AT TIME ZONE 'UTC',
                 :ModifiedById, NOW() AT TIME ZONE 'UTC', :TenantId)
            RETURNING DEVICEID;";

        public const string UPDATE_DEVICE_SQL = @"
            UPDATE MDEVICE SET
                DEVICENAME              = @DeviceName,
                DEVICETYPEID            = @DeviceTypeId,
                DEVICEUNIQUEDETAIL      = @DeviceUniqueDetail,
                DEVICEMACHINEIP         = @DeviceMachineIp,
                DEVICEPORTNUMBER        = @DevicePortNumber,
                DEVICEDEVICELOCATIONTYPE = @DeviceLocationType,
                DEVICEDEVICEUSERTYPE    = @DeviceUserType,
                ORGANIZATIONUNITID      = @OUId,
                USERID                  = @UserId,
                DEVICEISVERIFIED        = @IsVerified,
                DEVICEVERSION           = DEVICEVERSION + 1,
                DEVICEMODIFIEDBYID      = @ModifiedById,
                DEVICEMODIFIEDON        = GETUTCDATE()
            WHERE  DEVICEID  = @DeviceId
            AND    TENANTID  = @TenantId";

        public const string UPDATE_DEVICE_PG = @"
            UPDATE MDEVICE SET
                DEVICENAME              = :DeviceName,
                DEVICETYPEID            = :DeviceTypeId,
                DEVICEUNIQUEDETAIL      = :DeviceUniqueDetail,
                DEVICEMACHINEIP         = :DeviceMachineIp,
                DEVICEPORTNUMBER        = :DevicePortNumber,
                DEVICEDEVICELOCATIONTYPE = :DeviceLocationType,
                DEVICEDEVICEUSERTYPE    = :DeviceUserType,
                ORGANIZATIONUNITID      = :OUId,
                USERID                  = :UserId,
                DEVICEISVERIFIED        = :IsVerified,
                DEVICEVERSION           = DEVICEVERSION + 1,
                DEVICEMODIFIEDBYID      = :ModifiedById,
                DEVICEMODIFIEDON        = NOW() AT TIME ZONE 'UTC'
            WHERE  DEVICEID  = :DeviceId
            AND    TENANTID  = :TenantId";

        public const string UPDATE_LAST_SEEN_SQL = @"
            UPDATE MDEVICE SET DEVICEMODIFIEDON = GETUTCDATE()
            WHERE DEVICEID = @DeviceId AND TENANTID = @TenantId";

        public const string UPDATE_LAST_SEEN_PG = @"
            UPDATE MDEVICE SET DEVICEMODIFIEDON = NOW() AT TIME ZONE 'UTC'
            WHERE DEVICEID = :DeviceId AND TENANTID = :TenantId";

        public const string SOFT_DELETE_DEVICE_SQL = @"
            UPDATE MDEVICE SET DEVICESTATUS = 2, DEVICEMODIFIEDON = GETUTCDATE(), DEVICEMODIFIEDBYID = @ModifiedById
            WHERE  DEVICEID = @DeviceId AND TENANTID = @TenantId";

        public const string SOFT_DELETE_DEVICE_PG = @"
            UPDATE MDEVICE SET DEVICESTATUS = 2, DEVICEMODIFIEDON = NOW() AT TIME ZONE 'UTC', DEVICEMODIFIEDBYID = :ModifiedById
            WHERE  DEVICEID = :DeviceId AND TENANTID = :TenantId";


        public const string GET_DEVICETYPE_SELECTLIST = @"
                                                SELECT
                                                    DEVICETYPEID   AS Id,
                                                    DEVICETYPECODE AS Code,
                                                    DEVICETYPENAME AS Name
                                                FROM MDEVICETYPE
                                                WHERE STATUS = 1
                                                ORDER BY DEVICETYPENAME;";

        public const string GET_DEVICETYPE_SQL = @"
            SELECT DEVICETYPEID           AS DeviceTypeId,
                   DEVICETYPECODE         AS DeviceTypeCode,
                   DEVICETYPENAME         AS DeviceTypeName,
                   ISNULL(SCANTECHNOLOGY, '') AS DeviceTypeScanTechnology,
                   SORTORDER              AS DeviceTypeSortOrder,
                   STATUS                 AS DeviceTypeStatus,
                   VERSION                AS DeviceTypeVersion,
                   CREATEDBYID            AS DeviceTypeCreatedById,
                   CREATEDON              AS DeviceTypeCreatedOn,
                   MODIFIEDBYID           AS DeviceTypeModifiedById,
                   MODIFIEDON             AS DeviceTypeModifiedOn,
                   TENANTID               AS DeviceTypeTenantId
            FROM   MDEVICETYPE
            WHERE  DEVICETYPEID = @DeviceTypeId
            AND    TENANTID     = @TenantId
            AND    STATUS      <> 2";

        public const string GET_DEVICETYPE_PG = @"
            SELECT DEVICETYPEID           AS ""DeviceTypeId"",
                   DEVICETYPECODE         AS ""DeviceTypeCode"",
                   DEVICETYPENAME         AS ""DeviceTypeName"",
                   COALESCE(SCANTECHNOLOGY, '') AS ""DeviceTypeScanTechnology"",
                   SORTORDER              AS ""DeviceTypeSortOrder"",
                   STATUS                 AS ""DeviceTypeStatus"",
                   VERSION                AS ""DeviceTypeVersion"",
                   CREATEDBYID            AS ""DeviceTypeCreatedById"",
                   CREATEDON              AS ""DeviceTypeCreatedOn"",
                   MODIFIEDBYID           AS ""DeviceTypeModifiedById"",
                   MODIFIEDON             AS ""DeviceTypeModifiedOn"",
                   TENANTID               AS ""DeviceTypeTenantId""
            FROM   MDEVICETYPE
            WHERE  DEVICETYPEID = :DeviceTypeId
            AND    TENANTID     = :TenantId
            AND    STATUS      <> 2";

        public const string SAVE_DEVICETYPE_SQL = @"
            INSERT INTO MDEVICETYPE
                (DEVICETYPECODE, DEVICETYPENAME, SCANTECHNOLOGY,
                 SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON,
                 MODIFIEDBYID, MODIFIEDON, TENANTID)
            VALUES
                (@DeviceTypeCode, @DeviceTypeName, @DeviceTypeScanTechnology,
                 9999, 1, 1, @DeviceTypeCreatedById, GETUTCDATE(),
                 @DeviceTypeModifiedById, GETUTCDATE(), @DeviceTypeTenantId);
            SELECT SCOPE_IDENTITY();";

        public const string SAVE_DEVICETYPE_PG = @"
            INSERT INTO MDEVICETYPE
                (DEVICETYPECODE, DEVICETYPENAME, SCANTECHNOLOGY,
                 SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON,
                 MODIFIEDBYID, MODIFIEDON, TENANTID)
            VALUES
                (:DeviceTypeCode, :DeviceTypeName, :DeviceTypeScanTechnology,
                 9999, 1, 1, :DeviceTypeCreatedById, NOW() AT TIME ZONE 'UTC',
                 :DeviceTypeModifiedById, NOW() AT TIME ZONE 'UTC', :DeviceTypeTenantId)
            RETURNING DEVICETYPEID;";

        public const string UPDATE_DEVICETYPE_SQL = @"
            UPDATE MDEVICETYPE SET
                DEVICETYPENAME       = @DeviceTypeName,
                SCANTECHNOLOGY       = @DeviceTypeScanTechnology,
                VERSION              = VERSION + 1,
                MODIFIEDBYID         = @DeviceTypeModifiedById,
                MODIFIEDON           = GETUTCDATE()
            WHERE  DEVICETYPEID = @DeviceTypeId
            AND    TENANTID     = @DeviceTypeTenantId";

        public const string UPDATE_DEVICETYPE_PG = @"
            UPDATE MDEVICETYPE SET
                DEVICETYPENAME       = :DeviceTypeName,
                SCANTECHNOLOGY       = :DeviceTypeScanTechnology,
                VERSION              = VERSION + 1,
                MODIFIEDBYID         = :DeviceTypeModifiedById,
                MODIFIEDON           = NOW() AT TIME ZONE 'UTC'
            WHERE  DEVICETYPEID = :DeviceTypeId
            AND    TENANTID     = :DeviceTypeTenantId";

        public const string SOFT_DELETE_DEVICETYPE_SQL = @"
            UPDATE MDEVICETYPE SET STATUS = 2, MODIFIEDON = GETUTCDATE(), MODIFIEDBYID = @ModifiedById
            WHERE  DEVICETYPEID = @DeviceTypeId AND TENANTID = @TenantId";

        public const string SOFT_DELETE_DEVICETYPE_PG = @"
            UPDATE MDEVICETYPE SET STATUS = 2, MODIFIEDON = NOW() AT TIME ZONE 'UTC', MODIFIEDBYID = :ModifiedById
            WHERE  DEVICETYPEID = :DeviceTypeId AND TENANTID = :TenantId";

        public const string GET_DEVICE_SELECTLIST = @"
                                                SELECT
                                                    DEVICEID   AS Id,
                                                    DEVICECODE AS Code,
                                                    DEVICENAME AS Name
                                                FROM MDEVICE
                                                WHERE STATUS = 1
                                                ORDER BY DEVICENAME;";


    }
}