namespace FMDAL.Query.AssetLedger
{
    // Confirmed against DB/Migrations/20260719_RAM_Phase2_Schema_SqlServer.sql (this session's own
    // TASSETLEDGER design). Insert-only + soft-void (STATUS=2) — no UPDATE, matching the
    // "immutable after insert" ledger principle from RAM_Modernization_Design_Reference.md §4.9.
    public static class AssetLedgerQB
    {
        public const string GET_ASSETLEDGER = @"SELECT
                AL.ASSETLEDGERID           AS AssetLedgerId,
                AL.OUID                    AS OUId,
                AL.ASSETID                 AS AssetId,
                AL.BIZTRANSACTIONTYPEID    AS BizTransactionTypeId,
                AL.ASSETLEDGERNUMBER       AS AssetLedgerNumber,
                AL.ASSETLEDGERDATE         AS AssetLedgerDate,
                AL.FROMSTATEID             AS FromStateId,
                AL.TOSTATEID               AS ToStateId,
                AL.FROMCUSTODIANTYPE       AS FromCustodianType,
                AL.FROMCUSTODIANID         AS FromCustodianId,
                AL.TOCUSTODIANTYPE         AS ToCustodianType,
                AL.TOCUSTODIANID           AS ToCustodianId,
                AL.FROMLOCATIONID          AS FromLocationId,
                AL.TOLOCATIONID            AS ToLocationId,
                AL.SCANEVENTID             AS ScanEventId,
                AL.SCANSESSIONID           AS ScanSessionId,
                AL.REFERENCETYPE           AS ReferenceType,
                AL.REFERENCEID             AS ReferenceId,
                AL.REFERENCENUMBER         AS ReferenceNumber,
                AL.REFERENCEDATE           AS ReferenceDate,
                AL.EXPECTEDRETURNDATE      AS ExpectedReturnDate,
                AL.ACTUALRETURNDATE        AS ActualReturnDate,
                AL.CYCLECOUNT              AS CycleCount,
                AL.OPERATORID              AS OperatorId,
                AL.REMARKS                 AS Remarks,
                AL.STATUS                  AS Status,
                AL.VERSION                 AS Version,
                AL.CREATEDBYID             AS CreatedById,
                AL.CREATEDON               AS CreatedOn,
                AL.EVENTTIME               AS EventTime,
                AL.TENANTID                AS TenantId
            FROM TASSETLEDGER AL
            WHERE AL.ASSETLEDGERID = @AssetLedgerId
            AND   AL.TENANTID = @TenantId;";

        public const string GET_ASSETLEDGER_LIST_BY_ASSET = @"SELECT
                AL.ASSETLEDGERID           AS AssetLedgerId,
                AL.OUID                    AS OUId,
                AL.ASSETID                 AS AssetId,
                AL.BIZTRANSACTIONTYPEID    AS BizTransactionTypeId,
                AL.ASSETLEDGERNUMBER       AS AssetLedgerNumber,
                AL.ASSETLEDGERDATE         AS AssetLedgerDate,
                AL.FROMSTATEID             AS FromStateId,
                AL.TOSTATEID               AS ToStateId,
                AL.FROMCUSTODIANTYPE       AS FromCustodianType,
                AL.FROMCUSTODIANID         AS FromCustodianId,
                AL.TOCUSTODIANTYPE         AS ToCustodianType,
                AL.TOCUSTODIANID           AS ToCustodianId,
                AL.FROMLOCATIONID          AS FromLocationId,
                AL.TOLOCATIONID            AS ToLocationId,
                AL.SCANEVENTID             AS ScanEventId,
                AL.SCANSESSIONID           AS ScanSessionId,
                AL.REFERENCETYPE           AS ReferenceType,
                AL.REFERENCEID             AS ReferenceId,
                AL.REFERENCENUMBER         AS ReferenceNumber,
                AL.REFERENCEDATE           AS ReferenceDate,
                AL.EXPECTEDRETURNDATE      AS ExpectedReturnDate,
                AL.ACTUALRETURNDATE        AS ActualReturnDate,
                AL.CYCLECOUNT              AS CycleCount,
                AL.OPERATORID              AS OperatorId,
                AL.REMARKS                 AS Remarks,
                AL.STATUS                  AS Status,
                AL.VERSION                 AS Version,
                AL.CREATEDBYID             AS CreatedById,
                AL.CREATEDON               AS CreatedOn,
                AL.EVENTTIME               AS EventTime,
                AL.TENANTID                AS TenantId
            FROM TASSETLEDGER AL
            WHERE AL.ASSETID = @AssetId
            AND   AL.TENANTID = @TenantId
            ORDER BY AL.EVENTTIME DESC;";

        // Powers "Load from Reference": reconstruct which assets were last posted against a given
        // external document (ReferenceType/ReferenceId) so a bulk return/receipt can pre-populate
        // its expected bin list instead of requiring an individual scan per bin.
        public const string GET_ASSETLEDGER_LIST_BY_REFERENCE = @"SELECT
                AL.ASSETLEDGERID           AS AssetLedgerId,
                AL.OUID                    AS OUId,
                AL.ASSETID                 AS AssetId,
                AL.BIZTRANSACTIONTYPEID    AS BizTransactionTypeId,
                AL.ASSETLEDGERNUMBER       AS AssetLedgerNumber,
                AL.ASSETLEDGERDATE         AS AssetLedgerDate,
                AL.FROMSTATEID             AS FromStateId,
                AL.TOSTATEID               AS ToStateId,
                AL.FROMCUSTODIANTYPE       AS FromCustodianType,
                AL.FROMCUSTODIANID         AS FromCustodianId,
                AL.TOCUSTODIANTYPE         AS ToCustodianType,
                AL.TOCUSTODIANID           AS ToCustodianId,
                AL.FROMLOCATIONID          AS FromLocationId,
                AL.TOLOCATIONID            AS ToLocationId,
                AL.SCANEVENTID             AS ScanEventId,
                AL.SCANSESSIONID           AS ScanSessionId,
                AL.REFERENCETYPE           AS ReferenceType,
                AL.REFERENCEID             AS ReferenceId,
                AL.REFERENCENUMBER         AS ReferenceNumber,
                AL.REFERENCEDATE           AS ReferenceDate,
                AL.EXPECTEDRETURNDATE      AS ExpectedReturnDate,
                AL.ACTUALRETURNDATE        AS ActualReturnDate,
                AL.CYCLECOUNT              AS CycleCount,
                AL.OPERATORID              AS OperatorId,
                AL.REMARKS                 AS Remarks,
                AL.STATUS                  AS Status,
                AL.VERSION                 AS Version,
                AL.CREATEDBYID             AS CreatedById,
                AL.CREATEDON               AS CreatedOn,
                AL.EVENTTIME               AS EventTime,
                AL.TENANTID                AS TenantId
            FROM TASSETLEDGER AL
            WHERE AL.REFERENCETYPE = @ReferenceType
            AND   AL.REFERENCEID   = @ReferenceId
            AND   AL.TENANTID      = @TenantId
            ORDER BY AL.EVENTTIME DESC;";

        public const string SAVE_ASSETLEDGER = @"INSERT INTO TASSETLEDGER (
                ASSETLEDGERID, OUID, ASSETID, BIZTRANSACTIONTYPEID, ASSETLEDGERNUMBER, ASSETLEDGERDATE,
                FROMSTATEID, TOSTATEID, FROMCUSTODIANTYPE, FROMCUSTODIANID, TOCUSTODIANTYPE, TOCUSTODIANID,
                FROMLOCATIONID, TOLOCATIONID, SCANEVENTID, SCANSESSIONID, REFERENCETYPE, REFERENCEID,
                REFERENCENUMBER, REFERENCEDATE,
                EXPECTEDRETURNDATE, ACTUALRETURNDATE, CYCLECOUNT, OPERATORID, REMARKS,
                STATUS, VERSION, CREATEDBYID, CREATEDON, EVENTTIME, TENANTID)
            VALUES (
                @AssetLedgerId, @OUId, @AssetId, @BizTransactionTypeId, @AssetLedgerNumber, @AssetLedgerDate,
                @FromStateId, @ToStateId, @FromCustodianType, @FromCustodianId, @ToCustodianType, @ToCustodianId,
                @FromLocationId, @ToLocationId, @ScanEventId, @ScanSessionId, @ReferenceType, @ReferenceId,
                @ReferenceNumber, @ReferenceDate,
                @ExpectedReturnDate, @ActualReturnDate, @CycleCount, @OperatorId, @Remarks,
                @Status, @Version, @CreatedById, @CreatedOn, @EventTime, @TenantId);";

        public const string VOID_ASSETLEDGER = @"UPDATE TASSETLEDGER SET STATUS = 2
            WHERE ASSETLEDGERID = @AssetLedgerId
            AND   TENANTID = @TenantId;";
    }
}