namespace FMDAL.Query.ScanSession
{
    // Confirmed against DB/Migrations/20260719_RAM_Phase2_Schema_SqlServer.sql (TSCANSESSION).
    // Full-CRUD master with a status lifecycle: OPEN(0) -> CONFIRMED(1) / CANCELLED(2).
    // SaveScanSession is create-only (mirrors AssetActivityQB's SAVE/UPDATE split, minus the
    // UPDATE half) — sessions are never resaved as a whole; ConfirmScanSession/CancelScanSession
    // are the only two mutation paths after insert, guarded by "SESSIONSTATUS = 0" in the WHERE
    // clause so a session can only be confirmed/cancelled once (rows-affected = 0 signals an
    // invalid transition back up to the BLL).
    public static class ScanSessionQB
    {
        public const string GET_SCANSESSION = @"SELECT
                SS.SCANSESSIONID           AS ScanSessionId,
                SS.SESSIONCODE             AS SessionCode,
                SS.SCANZONEID              AS ScanZoneId,
                SS.SCANDEVICEID            AS ScanDeviceId,
                SS.OPERATORID              AS OperatorId,
                SS.SESSIONTYPE             AS SessionType,
                SS.SCANINTENT              AS ScanIntent,
                SS.MODULEID                AS ModuleId,
                SS.BIZTRANSACTIONTYPEID    AS BizTransactionTypeId,
                SS.REFERENCETYPE           AS ReferenceType,
                SS.REFERENCEID             AS ReferenceId,
                SS.TOTALSCANNED            AS TotalScanned,
                SS.TOTALACCEPTED           AS TotalAccepted,
                SS.TOTALREJECTED           AS TotalRejected,
                SS.SESSIONSTATUS           AS SessionStatus,
                SS.ISOFFLINE               AS IsOffline,
                SS.SYNCEDON                AS SyncedOn,
                SS.STARTEDON               AS StartedOn,
                SS.CONFIRMEDON             AS ConfirmedOn,
                SS.SORTORDER               AS SortOrder,
                SS.STATUS                  AS Status,
                SS.VERSION                 AS Version,
                SS.CREATEDBYID             AS CreatedById,
                SS.CREATEDON               AS CreatedOn,
                SS.MODIFIEDBYID            AS ModifiedById,
                SS.MODIFIEDON              AS ModifiedOn,
                SS.SOURCETYPE              AS SourceType,
                SS.OUID                    AS OUId,
                SS.TENANTID                AS TenantId
            FROM TSCANSESSION SS
            WHERE SS.SCANSESSIONID = @ScanSessionId
            AND   SS.TENANTID = @TenantId;";

        public const string SAVE_SCANSESSION = @"INSERT INTO TSCANSESSION (
                SCANSESSIONID, SESSIONCODE, SCANZONEID, SCANDEVICEID, OPERATORID, SESSIONTYPE,
                SCANINTENT, MODULEID, BIZTRANSACTIONTYPEID, REFERENCETYPE, REFERENCEID,
                TOTALSCANNED, TOTALACCEPTED, TOTALREJECTED, SESSIONSTATUS, ISOFFLINE, SYNCEDON,
                STARTEDON, CONFIRMEDON, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON,
                MODIFIEDBYID, MODIFIEDON, SOURCETYPE, OUID, TENANTID)
            VALUES (
                @ScanSessionId, @SessionCode, @ScanZoneId, @ScanDeviceId, @OperatorId, @SessionType,
                @ScanIntent, @ModuleId, @BizTransactionTypeId, @ReferenceType, @ReferenceId,
                @TotalScanned, @TotalAccepted, @TotalRejected, @SessionStatus, @IsOffline, @SyncedOn,
                @StartedOn, @ConfirmedOn, @SortOrder, @Status, @Version, @CreatedById, @CreatedOn,
                @ModifiedById, @ModifiedOn, @SourceType, @OUId, @TenantId);";

        public const string CONFIRM_SCANSESSION = @"UPDATE TSCANSESSION SET
                SESSIONSTATUS  = 1,
                CONFIRMEDON    = GETUTCDATE(),
                TOTALSCANNED   = @TotalScanned,
                TOTALACCEPTED  = @TotalAccepted,
                TOTALREJECTED  = @TotalRejected,
                MODIFIEDBYID   = @ModifiedById,
                MODIFIEDON     = GETUTCDATE()
            WHERE SCANSESSIONID = @ScanSessionId
            AND   TENANTID      = @TenantId
            AND   SESSIONSTATUS = 0;";

        public const string CANCEL_SCANSESSION = @"UPDATE TSCANSESSION SET
                SESSIONSTATUS = 2,
                MODIFIEDBYID  = @ModifiedById,
                MODIFIEDON    = GETUTCDATE()
            WHERE SCANSESSIONID = @ScanSessionId
            AND   TENANTID      = @TenantId
            AND   SESSIONSTATUS = 0;";

        // Rollup aggregate used by ConfirmScanSession to recompute TOTALSCANNED/TOTALACCEPTED/
        // TOTALREJECTED from the actual TSCANEVENT rows captured under this session, rather than
        // trusting any client-supplied running counter. SCANRESULT: 0=ACCEPTED counts toward
        // TOTALACCEPTED; 1=REJECTED/2=DUPLICATE/3=UNRESOLVED all count toward TOTALREJECTED, and
        // every row (regardless of result) counts toward TOTALSCANNED. ISNULL guards the SUM()
        // aggregates for the zero-events case (COUNT(*) itself never returns NULL).
        public const string GET_SCANEVENT_COUNTS_BY_SESSION = @"SELECT
                COUNT(*)                                                       AS TotalScanned,
                ISNULL(SUM(CASE WHEN SE.SCANRESULT = 0  THEN 1 ELSE 0 END), 0)  AS TotalAccepted,
                ISNULL(SUM(CASE WHEN SE.SCANRESULT <> 0 THEN 1 ELSE 0 END), 0)  AS TotalRejected
            FROM TSCANEVENT SE
            WHERE SE.SCANSESSIONID = @ScanSessionId
            AND   SE.TENANTID = @TenantId;";
    }
}