namespace FMDAL.Query.AssetPool
{
    // MASSETPOOL / MASSETPOOLDETAIL — column names confirmed against
    // DB/Migrations/20260719_RAM_Phase2_Schema_SqlServer.sql (authoritative DDL for this feature).
    // MASSETPOOLDETAIL carries no OUID/TENANTID — tenancy is inherited via ASSETPOOLID -> MASSETPOOL,
    // confirmed absent in the DDL.
    public static class AssetPoolQB
    {
        // Header + currently-active members only (VALIDTO IS NULL), for the detail edit screen.
        // Split marker is AssetPoolDetailId — the first column of the child block below.
        public const string GET_ASSETPOOL = @"SELECT
                AP.ASSETPOOLID              AS AssetPoolId,
                AP.POOLCODE                  AS PoolCode,
                AP.POOLNAME                  AS PoolName,
                AP.PARTYBRANCHLOCATIONID     AS PartyBranchLocationId,
                L.PARTYBRANCHLOCATIONCODE    AS PartyBranchLocationCode,
                L.PARTYBRANCHLOCATIONNAME    AS PartyBranchLocationName,
                AP.TARGETCOUNT               AS TargetCount,
                AP.DESCRIPTION               AS Description,
                AP.SORTORDER                 AS SortOrder,
                AP.STATUS                    AS Status,
                AP.VERSION                   AS Version,
                AP.CREATEDBYID               AS CreatedById,
                AP.CREATEDON                 AS CreatedOn,
                AP.MODIFIEDBYID              AS ModifiedById,
                AP.MODIFIEDON                AS ModifiedOn,
                AP.OUID                      AS OUId,
                AP.TENANTID                  AS TenantId,

                PD.ASSETPOOLDETAILID         AS AssetPoolDetailId,
                PD.ASSETPOOLID               AS AssetPoolId,
                PD.SLNO                      AS AssetPoolDetailSlNo,
                PD.ASSETID                   AS AssetId,
                A.ASSETCODE                  AS AssetCode,
                A.ASSETNAME                  AS AssetName,
                PD.VALIDFROM                 AS ValidFrom,
                PD.VALIDTO                   AS ValidTo,
                PD.STATUS                    AS Status,
                PD.VERSION                   AS Version,
                PD.CREATEDBYID               AS CreatedById,
                PD.CREATEDON                 AS CreatedOn,
                PD.MODIFIEDBYID              AS ModifiedById,
                PD.MODIFIEDON                AS ModifiedOn
            FROM MASSETPOOL AP
            LEFT JOIN MPARTYBRANCHLOCATION L  ON L.PARTYBRANCHLOCATIONID = AP.PARTYBRANCHLOCATIONID
            LEFT JOIN MASSETPOOLDETAIL PD     ON PD.ASSETPOOLID = AP.ASSETPOOLID AND PD.VALIDTO IS NULL
            LEFT JOIN MASSET A                ON A.ASSETID = PD.ASSETID
            WHERE AP.ASSETPOOLID = @AssetPoolId
            AND   AP.TENANTID    = @TenantId
            ORDER BY PD.SLNO;";

        // Currently-active membership rows only — used by the BLL to diff a submitted
        // AssetPoolDetailArray against what's actually active in the DB before deciding
        // which rows to insert (new members) vs close out (removed members).
        public const string GET_ACTIVE_ASSETPOOLDETAIL = @"SELECT
                PD.ASSETPOOLDETAILID        AS AssetPoolDetailId,
                PD.ASSETPOOLID              AS AssetPoolId,
                PD.SLNO                     AS AssetPoolDetailSlNo,
                PD.ASSETID                  AS AssetId,
                A.ASSETCODE                 AS AssetCode,
                A.ASSETNAME                 AS AssetName,
                PD.VALIDFROM                AS ValidFrom,
                PD.VALIDTO                  AS ValidTo,
                PD.STATUS                   AS Status,
                PD.VERSION                  AS Version,
                PD.CREATEDBYID              AS CreatedById,
                PD.CREATEDON                AS CreatedOn,
                PD.MODIFIEDBYID             AS ModifiedById,
                PD.MODIFIEDON               AS ModifiedOn
            FROM MASSETPOOLDETAIL PD
            LEFT JOIN MASSET A ON A.ASSETID = PD.ASSETID
            WHERE PD.ASSETPOOLID = @AssetPoolId
            AND   PD.VALIDTO IS NULL
            ORDER BY PD.SLNO;";

        // Lightweight header list — active pools only. ActiveMemberCount is a correlated
        // subquery over currently-active membership rows, for utilization display alongside
        // TargetCount without joining the full detail set (avoids N x M row explosion).
        public const string GET_ASSETPOOL_LIST = @"SELECT
                AP.ASSETPOOLID               AS AssetPoolId,
                AP.POOLCODE                   AS PoolCode,
                AP.POOLNAME                   AS PoolName,
                AP.PARTYBRANCHLOCATIONID      AS PartyBranchLocationId,
                L.PARTYBRANCHLOCATIONCODE     AS PartyBranchLocationCode,
                L.PARTYBRANCHLOCATIONNAME     AS PartyBranchLocationName,
                AP.TARGETCOUNT                AS TargetCount,
                AP.SORTORDER                   AS SortOrder,
                AP.STATUS                      AS Status,
                (SELECT COUNT(*) FROM MASSETPOOLDETAIL PD
                  WHERE PD.ASSETPOOLID = AP.ASSETPOOLID AND PD.VALIDTO IS NULL) AS ActiveMemberCount
            FROM MASSETPOOL AP
            LEFT JOIN MPARTYBRANCHLOCATION L ON L.PARTYBRANCHLOCATIONID = AP.PARTYBRANCHLOCATIONID
            WHERE AP.STATUS   = 1
            AND   AP.TENANTID = @TenantId
            ORDER BY AP.SORTORDER, AP.POOLNAME;";

        public const string SAVE_ASSETPOOL = @"INSERT INTO MASSETPOOL (
                ASSETPOOLID, POOLCODE, POOLNAME, PARTYBRANCHLOCATIONID, TARGETCOUNT, DESCRIPTION,
                SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, OUID, TENANTID)
            VALUES (
                @AssetPoolId, @PoolCode, @PoolName, @PartyBranchLocationId, @TargetCount, @Description,
                @SortOrder, @Status, @Version, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @OUId, @TenantId);";

        public const string UPDATE_ASSETPOOL = @"UPDATE MASSETPOOL SET
                POOLCODE               = @PoolCode,
                POOLNAME               = @PoolName,
                PARTYBRANCHLOCATIONID  = @PartyBranchLocationId,
                TARGETCOUNT            = @TargetCount,
                DESCRIPTION            = @Description,
                SORTORDER              = @SortOrder,
                VERSION                = VERSION + 1,
                MODIFIEDBYID           = @ModifiedById,
                MODIFIEDON             = @ModifiedOn
            WHERE ASSETPOOLID = @AssetPoolId
            AND   TENANTID    = @TenantId;";

        // Soft delete — set STATUS inactive, never a hard DELETE.
        public const string DELETE_ASSETPOOL = @"UPDATE MASSETPOOL SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE ASSETPOOLID = @AssetPoolId
            AND   TENANTID    = @TenantId;";

        // New membership row — one INSERT per new member added to the pool.
        public const string SAVE_ASSETPOOLDETAIL = @"INSERT INTO MASSETPOOLDETAIL (
                ASSETPOOLDETAILID, ASSETPOOLID, SLNO, ASSETID, VALIDFROM, VALIDTO,
                STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES (
                @AssetPoolDetailId, @AssetPoolId, @AssetPoolDetailSlNo, @AssetId, @ValidFrom, @ValidTo,
                @Status, @Version, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);";

        // Ends a member's active membership row — sets VALIDTO only. Never touches VALIDFROM,
        // never deletes the row: this is what preserves pool-membership history.
        public const string CLOSE_ASSETPOOLDETAIL = @"UPDATE MASSETPOOLDETAIL SET
                VALIDTO      = @ValidTo,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE ASSETPOOLDETAILID = @AssetPoolDetailId
            AND   VALIDTO IS NULL;";
    }
}