namespace MMDAL.Query.ConfigVersion
{
    public static class ConfigVersionQB
    {
        // Required indexes (created by 20260609_AddConfigVersionMaster.sql):
        //   IX_MCONFIGVERSION_ACTIVE         (TENANTID, CONFIGTYPE, EFFECTIVEFROM DESC) WHERE STATUS=1
        //   IX_MCONFIGVERSION_MEMBER_VERSION (CONFIGVERSIONID, MEMBERTYPE, TENANTID)
        //   IX_MCONFIGVERSION_MEMBER_FORMULA (TENANTID, MEMBERTYPE, MEMBERID)

        // ── Get full config version with member list ───────────────────────────
        // splitOn: "ConfigVersionMemberId"
        public const string GET_CONFIGVERSION = @"
            SELECT
                cv.CONFIGVERSIONID,
                cv.CONFIGVERSIONNAME,
                cv.CONFIGTYPE,
                cv.STATUS,
                cv.EFFECTIVEFROM,
                cv.EFFECTIVETO,
                cv.REMARKS,
                cv.SORTORDER,
                cv.VERSION,
                cv.CREATEDBYID,
                cv.CREATEDON,
                cv.MODIFIEDBYID,
                cv.MODIFIEDON,
                cv.TENANTID,
                cvm.CONFIGVERSIONMEMBERID,
                cvm.CONFIGVERSIONID         AS MemberConfigVersionId,
                cvm.MEMBERID,
                cvm.MEMBERTYPE,
                cvm.SLNO,
                cvm.TENANTID                AS MemberTenantId,
                ISNULL(mf.FORMULACODE, '')  AS FormulaCode,
                ISNULL(mf.FORMULANAME, '')  AS FormulaName
            FROM   MCONFIGVERSION cv
            LEFT   JOIN MCONFIGVERSION_MEMBER cvm ON cvm.CONFIGVERSIONID = cv.CONFIGVERSIONID
                                                  AND cvm.TENANTID        = @TenantId
            LEFT   JOIN MFORMULA mf ON mf.FORMULAID = cvm.MEMBERID
                                    AND mf.TENANTID  = @TenantId
            WHERE  cv.CONFIGVERSIONID = @ConfigVersionId
            AND    cv.TENANTID        = @TenantId
            ORDER  BY cvm.SLNO;";

        // ── Picklist ───────────────────────────────────────────────────────────
        public const string GET_SELECTLIST_CONFIGVERSION = @"
            SELECT
                CONFIGVERSIONID  AS Id,
                CONFIGVERSIONNAME AS Name,
                STATUS,
                EFFECTIVEFROM
            FROM   MCONFIGVERSION
            WHERE  TENANTID = @TenantId
            AND    STATUS   <> 2
            ORDER  BY EFFECTIVEFROM DESC;";

        // ── Active version scalar ──────────────────────────────────────────────
        // Reuses the same filter as MMDocumentQB.GET_ACTIVE_CONFIGVERSION_ID.
        // configType 0 = Charge/Formula config.
        public const string GET_ACTIVE_CONFIGVERSION_ID = @"
            SELECT TOP 1 CONFIGVERSIONID
            FROM   MCONFIGVERSION
            WHERE  TENANTID      = @TenantId
            AND    CONFIGTYPE    = @ConfigType
            AND    STATUS        = 1
            AND    EFFECTIVEFROM <= @AsOfDate
            AND    (EFFECTIVETO IS NULL OR EFFECTIVETO >= @AsOfDate)
            ORDER  BY EFFECTIVEFROM DESC;";

        // ── Insert / Update ────────────────────────────────────────────────────
        public const string SAVE_CONFIGVERSION = @"
            INSERT INTO MCONFIGVERSION
            (
                CONFIGVERSIONID, CONFIGVERSIONNAME, CONFIGTYPE, STATUS,
                EFFECTIVEFROM, EFFECTIVETO, REMARKS, SORTORDER,
                VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
            )
            VALUES
            (
                @ConfigVersionId, @ConfigVersionName, @ConfigType, @Status,
                @EffectiveFrom, @EffectiveTo, @Remarks, @SortOrder,
                @Version, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @TenantId
            );";

        public const string UPDATE_CONFIGVERSION = @"
            UPDATE MCONFIGVERSION
            SET    CONFIGVERSIONNAME = @ConfigVersionName,
                   CONFIGTYPE        = @ConfigType,
                   STATUS            = @Status,
                   EFFECTIVEFROM     = @EffectiveFrom,
                   EFFECTIVETO       = @EffectiveTo,
                   REMARKS           = @Remarks,
                   SORTORDER         = @SortOrder,
                   VERSION           = @Version,
                   MODIFIEDBYID      = @ModifiedById,
                   MODIFIEDON        = @ModifiedOn
            WHERE  CONFIGVERSIONID   = @ConfigVersionId
            AND    TENANTID          = @TenantId;";

        // Idempotent: WHERE STATUS=0 means only Draft rows can be deleted.
        public const string DELETE_CONFIGVERSION = @"
            DELETE FROM MCONFIGVERSION
            WHERE  CONFIGVERSIONID = @ConfigVersionId
            AND    TENANTID        = @TenantId
            AND    STATUS          = 0;";

        // ── Archive previous active versions ──────────────────────────────────
        // Called before Publishing a new version.
        // Sets STATUS=2 (Archived) and EFFECTIVETO=today-1 on all other Active versions
        // for the same CONFIGTYPE so date ranges don't overlap.
        public const string ARCHIVE_PREVIOUS_ACTIVE = @"
            UPDATE MCONFIGVERSION
            SET    STATUS         = 2,
                   EFFECTIVETO    = CAST(DATEADD(DAY, -1, @EffectiveFrom) AS DATE),
                   MODIFIEDON     = GETUTCDATE(),
                   MODIFIEDBYID   = @ModifiedById,
                   VERSION        = VERSION + 1
            WHERE  TENANTID       = @TenantId
            AND    CONFIGTYPE     = @ConfigType
            AND    STATUS         = 1
            AND    CONFIGVERSIONID <> @ConfigVersionId;";

        // ── Members ───────────────────────────────────────────────────────────
        public const string SAVE_CONFIGVERSION_MEMBER = @"
            INSERT INTO MCONFIGVERSION_MEMBER
            (CONFIGVERSIONMEMBERID, CONFIGVERSIONID, MEMBERID, MEMBERTYPE, SLNO, TENANTID)
            VALUES
            (@ConfigVersionMemberId, @ConfigVersionId, @MemberId, @MemberType, @SlNo, @TenantId);";

        public const string DELETE_CONFIGVERSION_MEMBERS = @"
            DELETE FROM MCONFIGVERSION_MEMBER
            WHERE  CONFIGVERSIONID = @ConfigVersionId
            AND    TENANTID        = @TenantId;";

        // ── Auto-populate members from active BizTransactionType charge chain ─
        // Collects distinct FORMULAIDs from MCHARGEDETAIL rows linked to active
        // BizTransactionTypes via ADDITIONALCHARGEID / OTHERCHARGEID / TOTALCHARGEID.
        // Required index: MCHARGEDETAIL(CHARGEID, TENANTID); MCHARGE(STATUS, TENANTID)
        public const string GET_FORMULAS_FROM_ACTIVE_CHARGES = @"
            SELECT DISTINCT
                cd.FORMULAID            AS MemberId,
                cd.SLNO,
                ISNULL(mf.FORMULACODE, '') AS FormulaCode,
                ISNULL(mf.FORMULANAME, '') AS FormulaName
            FROM   MCHARGEDETAIL cd
            INNER  JOIN MCHARGE  c  ON  c.CHARGEID  = cd.CHARGEID
                                    AND c.TENANTID   = @TenantId
                                    AND c.STATUS     = 1
            INNER  JOIN MFORMULA mf ON mf.FORMULAID  = cd.FORMULAID
                                    AND mf.TENANTID   = @TenantId
            WHERE  cd.TENANTID = @TenantId
            AND    EXISTS (
                SELECT 1
                FROM   MBIZTRANSACTIONTYPE bt
                WHERE  bt.TENANTID  = @TenantId
                AND    bt.STATUS    = 1
                AND   (bt.ADDITIONALCHARGEID = c.CHARGEID
                    OR bt.OTHERCHARGEID      = c.CHARGEID
                    OR bt.TOTALCHARGEID      = c.CHARGEID)
            )
            ORDER  BY cd.SLNO;";
    }
}