namespace FrameworkDAL.Query.Promotion
{
    // Indexes required: UX_MPROMOTIONASSETLINK_ORIGIN on
    // (OriginEnvironmentCode, EntityTypeCode, OriginEntityId) — see
    // 20260820_MetadataPromotion_Phase1_Schema_SqlServer.sql. GET_BY_ORIGIN is
    // the dedup lookup that runs on every ingest, so this index is load-bearing.
    public static class PromotionAssetLinkQB
    {
        public const string GET_BY_ORIGIN = @"
            SELECT PROMOTIONASSETLINKID  AS PromotionAssetLinkId,
                   ORIGINENVIRONMENTCODE AS OriginEnvironmentCode,
                   ENTITYTYPECODE        AS EntityTypeCode,
                   ORIGINENTITYID        AS OriginEntityId,
                   DESTINATIONENTITYID   AS DestinationEntityId,
                   LASTGOPEXECUTIONID    AS LastGopExecutionId,
                   LASTPROMOTEDON        AS LastPromotedOn,
                   CREATEDBYID           AS CreatedById,
                   CREATEDON             AS CreatedOn,
                   MODIFIEDBYID          AS ModifiedById,
                   MODIFIEDON            AS ModifiedOn
            FROM   MPROMOTIONASSETLINK
            WHERE  ORIGINENVIRONMENTCODE = @OriginEnvironmentCode
            AND    ENTITYTYPECODE        = @EntityTypeCode
            AND    ORIGINENTITYID        = @OriginEntityId";

        public const string INSERT = @"
            INSERT INTO MPROMOTIONASSETLINK
                (ORIGINENVIRONMENTCODE, ENTITYTYPECODE, ORIGINENTITYID, DESTINATIONENTITYID,
                 LASTGOPEXECUTIONID, LASTPROMOTEDON, CREATEDBYID, MODIFIEDBYID)
            VALUES
                (@OriginEnvironmentCode, @EntityTypeCode, @OriginEntityId, @DestinationEntityId,
                 @LastGopExecutionId, @LastPromotedOn, @UserId, @UserId);
            SELECT SCOPE_IDENTITY();";

        public const string UPDATE_AFTER_PROMOTION = @"
            UPDATE MPROMOTIONASSETLINK
            SET    DESTINATIONENTITYID = @DestinationEntityId,
                   LASTGOPEXECUTIONID  = @LastGopExecutionId,
                   LASTPROMOTEDON      = @LastPromotedOn,
                   MODIFIEDBYID        = @UserId,
                   MODIFIEDON          = SYSUTCDATETIME()
            WHERE  PROMOTIONASSETLINKID = @PromotionAssetLinkId";
    }
}