namespace FMDAL.Query.ScanZone
{
    // Confirmed against DB/Migrations/20260719_RAM_Phase2_Schema_SqlServer.sql (this session's own
    // MSCANZONE design, same migration as MASSETLEDGER/MASSETACTIVITY siblings). PK(SCANZONEID),
    // UNIQUE(ZONECODE, TENANTID). PARTYBRANCHLOCATIONID is nullable — LEFT JOIN so external zones
    // (vendor/partner premises with no MPARTYBRANCHLOCATION row) still return.
    // Required indexes: IX_MSCANZONE_TENANTID_STATUS (TENANTID, STATUS) for the active-list query;
    // UX_MSCANZONE_ZONECODE_TENANTID (ZONECODE, TENANTID) already enforced by the UNIQUE constraint.
    public static class ScanZoneQB
    {
        public const string GET_SCANZONE = @"SELECT
                SZ.SCANZONEID               AS ScanZoneId,
                SZ.ZONECODE                 AS ZoneCode,
                SZ.ZONENAME                 AS ZoneName,
                SZ.PARTYBRANCHLOCATIONID    AS PartyBranchLocationId,
                PBL.PARTYBRANCHLOCATIONCODE AS PartyBranchLocationCode,
                PBL.PARTYBRANCHLOCATIONNAME AS PartyBranchLocationName,
                SZ.DEFAULTSCANINTENT        AS DefaultScanIntent,
                SZ.TXPOWERDBM               AS TxPowerDbm,
                SZ.RSSIMINIMUM              AS RssiMinimum,
                SZ.REQUIRECONFIRM           AS RequireConfirm,
                SZ.SORTORDER                AS SortOrder,
                SZ.STATUS                   AS Status,
                SZ.VERSION                  AS Version,
                SZ.CREATEDBYID              AS CreatedById,
                SZ.CREATEDON                AS CreatedOn,
                SZ.MODIFIEDBYID             AS ModifiedById,
                SZ.MODIFIEDON               AS ModifiedOn,
                SZ.SOURCETYPE               AS SourceType,
                SZ.OUID                     AS OUId,
                SZ.TENANTID                 AS TenantId
            FROM MSCANZONE SZ
            LEFT JOIN MPARTYBRANCHLOCATION PBL ON PBL.PARTYBRANCHLOCATIONID = SZ.PARTYBRANCHLOCATIONID
            WHERE SZ.SCANZONEID = @ScanZoneId
            AND   SZ.TENANTID = @TenantId;";

        // Active-zone list for the zone-configuration screen and reference by other RAM entities
        // (e.g. scan-session zone pickers). STATUS=1 only, ordered for display.
        public const string GET_SCANZONE_LIST = @"SELECT
                SZ.SCANZONEID               AS ScanZoneId,
                SZ.ZONECODE                 AS ZoneCode,
                SZ.ZONENAME                 AS ZoneName,
                SZ.PARTYBRANCHLOCATIONID    AS PartyBranchLocationId,
                PBL.PARTYBRANCHLOCATIONCODE AS PartyBranchLocationCode,
                PBL.PARTYBRANCHLOCATIONNAME AS PartyBranchLocationName,
                SZ.DEFAULTSCANINTENT        AS DefaultScanIntent,
                SZ.TXPOWERDBM               AS TxPowerDbm,
                SZ.RSSIMINIMUM              AS RssiMinimum,
                SZ.REQUIRECONFIRM           AS RequireConfirm,
                SZ.SORTORDER                AS SortOrder,
                SZ.STATUS                   AS Status,
                SZ.VERSION                  AS Version,
                SZ.CREATEDBYID              AS CreatedById,
                SZ.CREATEDON                AS CreatedOn,
                SZ.MODIFIEDBYID             AS ModifiedById,
                SZ.MODIFIEDON               AS ModifiedOn,
                SZ.SOURCETYPE               AS SourceType,
                SZ.OUID                     AS OUId,
                SZ.TENANTID                 AS TenantId
            FROM MSCANZONE SZ
            LEFT JOIN MPARTYBRANCHLOCATION PBL ON PBL.PARTYBRANCHLOCATIONID = SZ.PARTYBRANCHLOCATIONID
            WHERE SZ.STATUS = 1
            AND   SZ.TENANTID = @TenantId
            ORDER BY SZ.SORTORDER, SZ.ZONENAME;";

        public const string SAVE_SCANZONE = @"INSERT INTO MSCANZONE (
                SCANZONEID, ZONECODE, ZONENAME, PARTYBRANCHLOCATIONID, DEFAULTSCANINTENT,
                TXPOWERDBM, RSSIMINIMUM, REQUIRECONFIRM, SORTORDER, STATUS, VERSION,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, OUID, TENANTID)
            VALUES (
                @ScanZoneId, @ZoneCode, @ZoneName, @PartyBranchLocationId, @DefaultScanIntent,
                @TxPowerDbm, @RssiMinimum, @RequireConfirm, @SortOrder, @Status, @Version,
                @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @OUId, @TenantId);";

        public const string UPDATE_SCANZONE = @"UPDATE MSCANZONE SET
                ZONECODE = @ZoneCode, ZONENAME = @ZoneName, PARTYBRANCHLOCATIONID = @PartyBranchLocationId,
                DEFAULTSCANINTENT = @DefaultScanIntent, TXPOWERDBM = @TxPowerDbm, RSSIMINIMUM = @RssiMinimum,
                REQUIRECONFIRM = @RequireConfirm, SORTORDER = @SortOrder, STATUS = @Status,
                VERSION = @Version, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn,
                SOURCETYPE = @SourceType, OUID = @OUId
            WHERE SCANZONEID = @ScanZoneId
            AND   TENANTID = @TenantId;";

        // Soft delete only — TSCANSESSION/TSCANEVENT FK-reference MSCANZONE; STATUS=0 marks inactive.
        public const string DELETE_SCANZONE = @"UPDATE MSCANZONE SET
                STATUS = 0, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
            WHERE SCANZONEID = @ScanZoneId
            AND   TENANTID = @TenantId;";
    }
}