namespace FMDAL.Query.AssetState
{
    // Confirmed against DB/Migrations/20260719_RAM_Phase2_Schema_SqlServer.sql (MASSETSTATE, step 7).
    // Unique constraint UK_MASSETSTATE_TYPESTATECODE(ASSETTYPEID, STATECODE, TENANTID) is the DB
    // backstop for duplicate STATECODE per asset type; AssetStateDAL catches the 2627/2601
    // violation and rethrows a clear DatabaseOperationException.
    // Soft delete mirrors FM Meter convention: STATUS = 2 (CK_MASSETSTATE_STATUS allows 0-5).
    // Recommended covering index: IX_MASSETSTATE_ASSETTYPEID_SORTORDER (ASSETTYPEID, TENANTID) INCLUDE (SORTORDER)
    // — supports GetAssetStateListByType, the primary query the future IScanEventHandler will call.
    public static class AssetStateQB
    {
        public const string GET_ASSETSTATE = @"SELECT
                AS_.ASSETSTATEID           AS AssetStateId,
                AS_.ASSETTYPEID            AS AssetTypeId,
                AS_.STATECODE              AS AssetStateCode,
                AS_.STATENAME              AS AssetStateName,
                AS_.STATEDESCRIPTION       AS AssetStateDescription,
                AS_.ISREQUIRESSCAN         AS AssetStateIsRequiresScan,
                AS_.ISALLOWSSTOCK          AS AssetStateIsAllowsStock,
                AS_.ISTERMINAL             AS AssetStateIsTerminal,
                AS_.ALLOWEDTRANSITIONS     AS AssetStateAllowedTransitions,
                AS_.TRIGGERZONEID          AS AssetStateTriggerZoneId,
                AS_.TRIGGERBIZTRANSACTIONCLASSID AS AssetStateTriggerBizTransactionClassId,
                AS_.ISLEDGERENTRYREQUIRED  AS AssetStateIsLedgerEntryRequired,
                AS_.SORTORDER              AS AssetStateSortOrder,
                AS_.STATUS                 AS AssetStateStatus,
                AS_.VERSION                AS AssetStateVersion,
                AS_.CREATEDBYID            AS AssetStateCreatedById,
                AS_.CREATEDON              AS AssetStateCreatedOn,
                AS_.MODIFIEDBYID           AS AssetStateModifiedById,
                AS_.MODIFIEDON             AS AssetStateModifiedOn,
                AS_.OUID                   AS OrganizationUnitId,
                AS_.TENANTID               AS TenantId
            FROM MASSETSTATE AS_
            WHERE AS_.ASSETSTATEID = @AssetStateId
            AND   AS_.TENANTID     = @TenantId;";

        public const string GET_ASSETSTATE_LIST_BY_TYPE = @"SELECT
                AS_.ASSETSTATEID           AS AssetStateId,
                AS_.ASSETTYPEID            AS AssetTypeId,
                AS_.STATECODE              AS AssetStateCode,
                AS_.STATENAME              AS AssetStateName,
                AS_.STATEDESCRIPTION       AS AssetStateDescription,
                AS_.ISREQUIRESSCAN         AS AssetStateIsRequiresScan,
                AS_.ISALLOWSSTOCK          AS AssetStateIsAllowsStock,
                AS_.ISTERMINAL             AS AssetStateIsTerminal,
                AS_.ALLOWEDTRANSITIONS     AS AssetStateAllowedTransitions,
                AS_.TRIGGERZONEID          AS AssetStateTriggerZoneId,
                AS_.TRIGGERBIZTRANSACTIONCLASSID AS AssetStateTriggerBizTransactionClassId,
                AS_.ISLEDGERENTRYREQUIRED  AS AssetStateIsLedgerEntryRequired,
                AS_.SORTORDER              AS AssetStateSortOrder,
                AS_.STATUS                 AS AssetStateStatus,
                AS_.VERSION                AS AssetStateVersion,
                AS_.CREATEDBYID            AS AssetStateCreatedById,
                AS_.CREATEDON              AS AssetStateCreatedOn,
                AS_.MODIFIEDBYID           AS AssetStateModifiedById,
                AS_.MODIFIEDON             AS AssetStateModifiedOn,
                AS_.OUID                   AS OrganizationUnitId,
                AS_.TENANTID               AS TenantId
            FROM MASSETSTATE AS_
            WHERE AS_.ASSETTYPEID = @AssetTypeId
            AND   AS_.TENANTID    = @TenantId
            ORDER BY AS_.SORTORDER;";

        public const string SAVE_ASSETSTATE = @"INSERT INTO MASSETSTATE (
                ASSETSTATEID, ASSETTYPEID, STATECODE, STATENAME, STATEDESCRIPTION,
                ISREQUIRESSCAN, ISALLOWSSTOCK, ISTERMINAL, ALLOWEDTRANSITIONS, TRIGGERZONEID,
                TRIGGERBIZTRANSACTIONCLASSID, ISLEDGERENTRYREQUIRED,
                SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
                OUID, TENANTID)
            VALUES (
                @AssetStateId, @AssetTypeId, @AssetStateCode, @AssetStateName, @AssetStateDescription,
                @AssetStateIsRequiresScan, @AssetStateIsAllowsStock, @AssetStateIsTerminal, @AssetStateAllowedTransitions, @AssetStateTriggerZoneId,
                @AssetStateTriggerBizTransactionClassId, @AssetStateIsLedgerEntryRequired,
                @AssetStateSortOrder, @AssetStateStatus, @AssetStateVersion, @AssetStateCreatedById, @AssetStateCreatedOn, @AssetStateModifiedById, @AssetStateModifiedOn,
                @OrganizationUnitId, @TenantId);";

        public const string UPDATE_ASSETSTATE = @"UPDATE MASSETSTATE SET
                ASSETTYPEID = @AssetTypeId, STATECODE = @AssetStateCode, STATENAME = @AssetStateName,
                STATEDESCRIPTION = @AssetStateDescription, ISREQUIRESSCAN = @AssetStateIsRequiresScan,
                ISALLOWSSTOCK = @AssetStateIsAllowsStock, ISTERMINAL = @AssetStateIsTerminal,
                ALLOWEDTRANSITIONS = @AssetStateAllowedTransitions, TRIGGERZONEID = @AssetStateTriggerZoneId,
                TRIGGERBIZTRANSACTIONCLASSID = @AssetStateTriggerBizTransactionClassId,
                ISLEDGERENTRYREQUIRED = @AssetStateIsLedgerEntryRequired,
                SORTORDER = @AssetStateSortOrder, STATUS = @AssetStateStatus, VERSION = VERSION + 1,
                MODIFIEDBYID = @AssetStateModifiedById, MODIFIEDON = @AssetStateModifiedOn,
                OUID = @OrganizationUnitId
            WHERE ASSETSTATEID = @AssetStateId
            AND   TENANTID     = @TenantId;";

        // Soft delete — MASSET.CURRENTASSETSTATEID and TASSETLEDGER.FROMSTATEID/TOSTATEID FK-reference
        // this table, so rows are never hard-deleted; STATUS = 2 marks the state inactive.
        public const string DELETE_ASSETSTATE = @"UPDATE MASSETSTATE SET
                STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
            WHERE ASSETSTATEID = @AssetStateId
            AND   TENANTID     = @TenantId;";
    }
}