namespace GB5Shared.ScanIdentifier
{
    // Builds on MSCANIDENTIFIER (RAM Phase 2 — DB/Migrations/20260719_RAM_Phase2_Schema_SqlServer.sql)
    // and the print-lifecycle columns + MSCANTARGETCONFIG added by
    // DB/Migrations/20260822_ScanIdentifier_Platform_Phase1_SqlServer.sql. Mirrors
    // FM/FMDAL/Query/ScanIdentifier/ScanIdentifierQB.cs's confirmed column list/conventions
    // (VALIDTO IS NULL = currently active; soft-retire, never hard DELETE) rather than
    // reinventing them, plus the new PrintCount/LastPrintedOn and MSCANTARGETCONFIG lookup
    // this platform needs that FM's own flow never did.
    internal static class ScanIdentifierEngineQB
    {
        public const string GET_TARGET_CONFIG = @"
SELECT TOP 1
    SCANTARGETCONFIGID     AS ScanTargetConfigId,
    ENTITYTYPE              AS EntityType,
    DEFAULTIDENTIFIERTYPE   AS DefaultIdentifierType,
    VALUESCHEME             AS ValueScheme,
    PREFIX                  AS Prefix,
    ALLOWMULTIQTY           AS AllowMultiQty,
    MAXQTY                  AS MaxQty,
    DEFAULTLABELTYPEID      AS DefaultLabelTypeId,
    DEFAULTPRINTERTYPEID    AS DefaultPrinterTypeId,
    TENANTID                AS TenantId
FROM   MSCANTARGETCONFIG
WHERE  ENTITYTYPE = @EntityType
AND    TENANTID   IN (@TenantId, -1)
ORDER BY CASE WHEN TENANTID = @TenantId THEN 0 ELSE 1 END;";

        public const string INSERT_SCANIDENTIFIER = @"
INSERT INTO MSCANIDENTIFIER (
    SCANIDENTIFIERID, IDENTIFIERTYPE, IDENTIFIERVALUE, ENTITYTYPE, ENTITYID, ISPRIMARY,
    VALIDFROM, VALIDTO, SORTORDER, STATUS, VERSION, PRINTCOUNT, LASTPRINTEDON,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, OUID, TENANTID)
VALUES (
    @ScanIdentifierId, @IdentifierType, @IdentifierValue, @EntityType, @EntityId, 1,
    GETDATE(), NULL, 9999, 1, 0, 0, NULL,
    @UserId, GETDATE(), @UserId, GETDATE(), 5, @OUId, @TenantId);";

        private const string SELECT_COLUMNS = @"
    SI.SCANIDENTIFIERID    AS ScanIdentifierId,
    SI.IDENTIFIERTYPE      AS IdentifierType,
    SI.IDENTIFIERVALUE     AS IdentifierValue,
    SI.ENTITYTYPE          AS EntityType,
    SI.ENTITYID            AS EntityId,
    SI.VALIDFROM           AS ValidFrom,
    SI.VALIDTO             AS ValidTo,
    SI.STATUS              AS Status,
    SI.PRINTCOUNT          AS PrintCount,
    SI.LASTPRINTEDON       AS LastPrintedOn,
    SI.TENANTID            AS TenantId";

        public const string GET_BY_ID = "SELECT" + SELECT_COLUMNS + @"
FROM MSCANIDENTIFIER SI
WHERE SI.SCANIDENTIFIERID = @ScanIdentifierId
AND   SI.TENANTID = @TenantId;";

        public const string GET_BY_VALUE = "SELECT" + SELECT_COLUMNS + @"
FROM MSCANIDENTIFIER SI
WHERE SI.IDENTIFIERVALUE = @IdentifierValue
AND   SI.TENANTID = @TenantId
AND   SI.VALIDTO IS NULL;";

        // Q-SCANID-LIST-1: filtered/paged list backing the Print/List facilities. Plain single
        // SELECT (no CTE) — QueryPagedAsync wraps this whole statement in a COUNT(*) subquery,
        // which a CTE-based query would break (confirmed gotcha elsewhere in this codebase).
        public const string GET_LIST_PAGED = "SELECT" + SELECT_COLUMNS + @"
FROM MSCANIDENTIFIER SI
WHERE SI.TENANTID = @TenantId
AND   (@EntityType = '' OR SI.ENTITYTYPE = @EntityType)
AND   (@StatusFilter = -1
       OR (@StatusFilter = 1 AND SI.VALIDTO IS NULL)
       OR (@StatusFilter = 0 AND SI.VALIDTO IS NOT NULL))
AND   (@SearchTerm = '' OR SI.IDENTIFIERVALUE LIKE '%' + @SearchTerm + '%' OR SI.ENTITYID LIKE '%' + @SearchTerm + '%')
AND   SI.VALIDFROM >= @GeneratedFrom
ORDER BY SI.VALIDFROM DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string RETIRE_SCANIDENTIFIER = @"
UPDATE MSCANIDENTIFIER
SET    VALIDTO      = GETDATE(),
       MODIFIEDBYID = @UserId,
       MODIFIEDON   = GETDATE()
WHERE  SCANIDENTIFIERID = @ScanIdentifierId
AND    TENANTID          = @TenantId
AND    VALIDTO IS NULL;";

        public const string REACTIVATE_SCANIDENTIFIER = @"
UPDATE MSCANIDENTIFIER
SET    VALIDTO      = NULL,
       MODIFIEDBYID = @UserId,
       MODIFIEDON   = GETDATE()
WHERE  SCANIDENTIFIERID = @ScanIdentifierId
AND    TENANTID          = @TenantId
AND    VALIDTO IS NOT NULL;";

        public const string MARK_PRINTED = @"
UPDATE MSCANIDENTIFIER
SET    PRINTCOUNT    = PRINTCOUNT + 1,
       LASTPRINTEDON = GETDATE(),
       MODIFIEDBYID  = @UserId,
       MODIFIEDON    = GETDATE()
WHERE  SCANIDENTIFIERID = @ScanIdentifierId
AND    TENANTID          = @TenantId;";

        // Q-SCANID-GCM-1: reads MGCM directly — WI has no reason to own its own copy of Printer
        // Type / Label Type master data. Label Type is inner-joined to MLABELTEMPLATE deliberately:
        // a BARLABEL row with no real dimensions yet simply doesn't appear as a printable option.
        public const string GET_PRINTER_TYPES = @"
SELECT GCMID AS GcmId, GCMCODE AS Code, GCMNAME AS Name
FROM   MGCM
WHERE  GCMTYPEID = -1399999909
AND    STATUS    = 1
ORDER BY GCMNAME;";

        public const string GET_LABEL_TYPES = @"
SELECT G.GCMID AS GcmId, G.GCMCODE AS Code, G.GCMNAME AS Name
FROM   MGCM G
JOIN   MLABELTEMPLATE LT ON LT.GCMID = G.GCMID
WHERE  G.GCMTYPEID = -1399999908
AND    G.STATUS    = 1
ORDER BY G.GCMNAME;";

        public const string GET_LABEL_TEMPLATE = @"
SELECT LABELTEMPLATEID AS LabelTemplateId, GCMID AS GcmId, WIDTHMM AS WidthMm,
       HEIGHTMM AS HeightMm, LABELSPERSHEET AS LabelsPerSheet
FROM   MLABELTEMPLATE
WHERE  GCMID = @GcmId;";
    }
}