namespace FMDAL.Query.ScanIdentifier
{
    // Confirmed against DB/Migrations/20260719_RAM_Phase2_Schema_SqlServer.sql — MSCANIDENTIFIER.
    // GET_SCANIDENTIFIER_BY_VALUE is the scan-resolution lookup path: it filters to the single
    // currently-active row per (IDENTIFIERVALUE, TENANTID) via VALIDTO IS NULL. UPDATE_SCANIDENTIFIER
    // bumps VERSION for optimistic-concurrency visibility. DELETE_SCANIDENTIFIER is a soft end-date
    // (VALIDTO = GETDATE()), not a hard DELETE — matches VALIDTO's role as the retirement marker;
    // the WHERE VALIDTO IS NULL guard makes repeat deletes a no-op instead of clobbering history.
    public static class ScanIdentifierQB
    {
        public const string GET_SCANIDENTIFIER = @"SELECT
                SI.SCANIDENTIFIERID    AS ScanIdentifierId,
                SI.IDENTIFIERTYPE      AS IdentifierType,
                SI.IDENTIFIERVALUE     AS IdentifierValue,
                SI.ENTITYTYPE          AS EntityType,
                SI.ENTITYID            AS EntityId,
                SI.ISPRIMARY           AS IsPrimary,
                SI.VALIDFROM           AS ValidFrom,
                SI.VALIDTO             AS ValidTo,
                SI.SORTORDER           AS SortOrder,
                SI.STATUS              AS Status,
                SI.VERSION             AS Version,
                SI.CREATEDBYID         AS CreatedById,
                SI.CREATEDON           AS CreatedOn,
                SI.MODIFIEDBYID        AS ModifiedById,
                SI.MODIFIEDON          AS ModifiedOn,
                SI.SOURCETYPE          AS SourceType,
                SI.OUID                AS OUId,
                SI.TENANTID            AS TenantId
            FROM MSCANIDENTIFIER SI
            WHERE SI.SCANIDENTIFIERID = @ScanIdentifierId
            AND   SI.TENANTID = @TenantId;";

        public const string GET_SCANIDENTIFIER_BY_VALUE = @"SELECT
                SI.SCANIDENTIFIERID    AS ScanIdentifierId,
                SI.IDENTIFIERTYPE      AS IdentifierType,
                SI.IDENTIFIERVALUE     AS IdentifierValue,
                SI.ENTITYTYPE          AS EntityType,
                SI.ENTITYID            AS EntityId,
                SI.ISPRIMARY           AS IsPrimary,
                SI.VALIDFROM           AS ValidFrom,
                SI.VALIDTO             AS ValidTo,
                SI.SORTORDER           AS SortOrder,
                SI.STATUS              AS Status,
                SI.VERSION             AS Version,
                SI.CREATEDBYID         AS CreatedById,
                SI.CREATEDON           AS CreatedOn,
                SI.MODIFIEDBYID        AS ModifiedById,
                SI.MODIFIEDON          AS ModifiedOn,
                SI.SOURCETYPE          AS SourceType,
                SI.OUID                AS OUId,
                SI.TENANTID            AS TenantId
            FROM MSCANIDENTIFIER SI
            WHERE SI.IDENTIFIERVALUE = @IdentifierValue
            AND   SI.TENANTID = @TenantId
            AND   SI.VALIDTO IS NULL;";

        public const string SAVE_SCANIDENTIFIER = @"INSERT INTO MSCANIDENTIFIER (
                SCANIDENTIFIERID, IDENTIFIERTYPE, IDENTIFIERVALUE, ENTITYTYPE, ENTITYID, ISPRIMARY,
                VALIDFROM, VALIDTO, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON,
                MODIFIEDBYID, MODIFIEDON, SOURCETYPE, OUID, TENANTID)
            VALUES (
                @ScanIdentifierId, @IdentifierType, @IdentifierValue, @EntityType, @EntityId, @IsPrimary,
                @ValidFrom, @ValidTo, @SortOrder, @Status, @Version, @CreatedById, @CreatedOn,
                @ModifiedById, @ModifiedOn, @SourceType, @OUId, @TenantId);";

        public const string UPDATE_SCANIDENTIFIER = @"UPDATE MSCANIDENTIFIER SET
                IDENTIFIERTYPE = @IdentifierType, IDENTIFIERVALUE = @IdentifierValue,
                ENTITYTYPE = @EntityType, ENTITYID = @EntityId, ISPRIMARY = @IsPrimary,
                VALIDFROM = @ValidFrom, VALIDTO = @ValidTo, SORTORDER = @SortOrder,
                STATUS = @Status, VERSION = VERSION + 1,
                MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn,
                SOURCETYPE = @SourceType, OUID = @OUId
            WHERE SCANIDENTIFIERID = @ScanIdentifierId
            AND   TENANTID = @TenantId;";

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