import { Sequelize, WhereOptions, FindOptions, literal, fn, col, Transaction, Op } from "sequelize";
import Models, { sequelize } from "../models";
import { LocalizedContent } from "../../utils/contentGenerator";
import { AppError } from "../../utils/errors";
import Moment from "moment-timezone";
import { Common } from "../../utils/common"
import _ from 'lodash';
import { PAYMENT } from "../config/constants";

const subscriptionAttributes: AttributeElement[] = ["id", "planId", "priceId", "externalSubscriptionId", "gateway", "currentPeriodStart", "currentPeriodEnd", "status", "createdAt", "updatedAt", "planData"];

const transactionAttributes: AttributeElement[] = ["id", "subscriptionId", "externalOrderId", "externalPaymentId", "userId", "amount", "currency", "status", "paidAt", "planData", "gateway", "createdAt", "updatedAt"];

const innertransactionAttributes: AttributeElement[] = ["id", "subscriptionId", "externalOrderId", "externalPaymentId", "userId", "amount", "currency", "status", "paidAt", "gateway", "createdAt", "updatedAt"]

const authorAttributes: AttributeElement[] = [
    'id', 'onlineStatus',
    [literal('`author->userProfile`.`name`'), 'name'],
    [
        literal(`(
              SELECT JSON_OBJECT(
                'id', a.id,
                'fileName', a.file_name,
                'uniqueName', a.unique_name,
                'filePath', CONCAT('${process.env.PROTOCOL}://', '${process.env.API_HOST}', '/attachment/', a.unique_name),
                'cdnUrl', CONCAT('${process.env.CDN_PATH}', '/attachment/', a.unique_name)
              )
              FROM user_profile as up
              JOIN attachments as a ON up.profile_image_id = a.id
              WHERE up.user_id = author.id
              LIMIT 1
            )`),
        'profileImage'
    ]
];

export class PaymentDao {
    private accountId: number | null;
    private userId: number | null;
    private language: string;
    private scope: string[] | null;
    private config: userConfig | null;
    constructor(
        private options: {
            language: string;
            scope: string[] | null;
            accountId: number | null;
            userId: number | null;
            config: userConfig | null;
        } = {
                language: process.env.DEFAULT_LANGUAGE_CODE!,
                scope: null,
                config: null,
                userId: null,
                accountId: null,
            }
    ) {
        this.language = options.language;
        this.scope = options.scope ?? [];
        this.config = options.config ?? null;
        this.userId = options.userId ?? null
        this.accountId = options.accountId ?? null
    }

    // get association for the entity
    private includeAssociations = (fullInfo: boolean = false): IncludeOption[] => {
        const includeModels: IncludeOption[] = [
            {
                attributes: innertransactionAttributes,
                model: Models.Transaction,
                as: 'transactions'
            }
        ];
        if (fullInfo) {
            includeModels.push(
                {
                    attributes: authorAttributes,
                    model: Models.User,
                    as: 'author',
                    include: [{ attributes: [], model: Models.UserProfile, as: 'userProfile', include: [{ model: Models.Attachment, as: "profileImage" }] }]
                }
            );
        }
        return includeModels;
    }



    createTransaction = async (transactionObject: TransactionInterface): Promise<number> => {
        try {
            const transactionCreated = await Models.Transaction.create(transactionObject);
            if (transactionCreated) {
                return transactionCreated.id;
            } else {
                throw new AppError(400, 'ERROR_CREATING_TRANSACTION', {});
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    updateSubscriptionById = async (subscriptionId: number, subscriptionObject: Partial<SubscriptionInterface>) => {
        try {
            await Models.Subscription.update(subscriptionObject, { where: { id: subscriptionId } });
            return true;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    updateTransactionById = async (transactionId: number, transactionObject: Partial<TransactionInterface>) => {
        try {
            await Models.Transaction.update(transactionObject, { where: { id: transactionId } });
            return true;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    getTransactionByExternalOrderId = async (externalOrderId: string) => {
        try {
            return await Models.Transaction.findOne({ where: { externalOrderId } });
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    getTransactionByExternalPaymentId = async (externalPaymentId: string) => {
        try {
            return await Models.Transaction.findOne({ where: { externalPaymentId } });
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    getSubscriptionByExternalSubscriptionId = async (externalSubscriptionId: string) => {
        try {
            return await Models.Subscription.findOne({ where: { externalSubscriptionId } });
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create a new customer
    createCustomer = async (userId: number, customerId: string): Promise<string | null> => {
        const transaction = await sequelize.transaction();
        try {
            const customer = await Models.Customer.create({ userId: userId, customerId: customerId }, { transaction });
            if (customer) {
                await transaction.commit();
                return customer.customerId;
            } else {
                throw new AppError(400, 'ERROR_CREATING_CUSTOMER', {});
            }
        } catch (err) {
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get activity id from activity code
    getCustomerByUserId = async (userId: number): Promise<string | null> => {
        try {
            let customer = await Models.Customer.findOne({ attributes: ['customerId'], where: { userId } });
            if (customer) {
                return customer.customerId;
            } else {
                return null;
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get activity id from activity code
    getTransactionById = async (id: number): Promise<TransactionInterface> => {
        try {
            let transaction = await Models.Transaction.findOne({ where: { id } });
            return transaction
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create subscription for user
    buySubscription = async (subscriptionObj: SubscriptionInterface) => {
        const transaction = await sequelize.transaction();
        try {
            const subscription = await Models.Subscription.create({ ...subscriptionObj, accountId: this.accountId }, {
                include: [{ model: Models.Transaction, as: "transaction" }],
                transaction
            });
            if (subscription) {
                await transaction.commit();
                return JSON.parse(JSON.stringify(subscription))
            } else {
                throw new AppError(400, 'ERROR_CREATING_SUBSCRIPTION', {});
            }
            // return subscription;
        } catch (err) {
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create subscription for user
    updateSubscription = async (subscriptionObj: SubscriptionInterface) => {
        const transaction = await sequelize.transaction();
        try {
            const subscription = await Models.Subscription.update({ ...subscriptionObj }, {
                where: { id: subscriptionObj.id },
                transaction
            });
            if (subscription) {
                await transaction.commit();
                return JSON.parse(JSON.stringify(subscription))
            } else {
                throw new AppError(400, 'ERROR_CREATING_SUBSCRIPTION', {});
            }
            // return subscription;
        } catch (err) {
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create subscription for user
    getExternalSubscriptionById = async (id: number) => {
        try {
            const subscription = await Models.Subscription.findOne({
                where: { id: id }
            });
            if (subscription?.externalSubscriptionId) {
                return subscription.externalSubscriptionId;
            } else {
                throw new AppError(404, 'SUBSCRIPTION_NOT_FOUND', {});
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create subscription for user
    getSubscriptionById = async (id: number) => {
        try {
            const subscription = await Models.Subscription.findOne({
                attributes: subscriptionAttributes,
                where: { id: id },
                include: [
                    {
                        model: Models.Transaction, as: "transactions",
                        attributes: innertransactionAttributes
                    }
                ]
            });
            if (subscription?.id) {
                return JSON.parse(JSON.stringify(subscription));
            } else {
                throw new AppError(404, 'SUBSCRIPTION_NOT_FOUND', {});
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create subscription for user
    getActiveSubscriptionByUserId = async (userId: number) => {
        try {
            const subscription = await Models.Subscription.findOne({
                attributes: subscriptionAttributes,
                where: {
                    status: { [Op.in]: [PAYMENT.SUBSCRIPTION_STATUS.ACTIVE, PAYMENT.SUBSCRIPTION_STATUS.CANCELLED] },
                    currentPeriodEnd: { [Op.gte]: new Date() },
                    userId: userId
                },
                include: [
                    {
                        model: Models.Transaction, as: "transactions",
                        attributes: innertransactionAttributes
                    }
                ]
            });

            return JSON.parse(JSON.stringify(subscription));

        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    /**
     * Returns the most recent subscription for a user that is in a
     * PENDING or AUTHENTICATED state AND has an externalSubscriptionId.
     * Used to detect "stuck" subscriptions where Razorpay already charged
     * the user but the DB was not yet promoted to ACTIVE (e.g. webhook delay).
     */
    getLatestNonActiveSubscriptionByUserId = async (userId: number) => {
        try {
            const subscription = await Models.Subscription.findOne({
                where: {
                    status: { [Op.in]: [PAYMENT.SUBSCRIPTION_STATUS.PENDING, PAYMENT.SUBSCRIPTION_STATUS.AUTHENTICATED] },
                    externalSubscriptionId: { [Op.ne]: null },
                    userId: userId
                },
                order: [['id', 'DESC']]
            });
            return subscription ? JSON.parse(JSON.stringify(subscription)) : null;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create subscription for user
    cancelSubscription = async (id: number) => {
        try {
            const subscription = await Models.Subscription.findOne({
                where: { id: id }
            });
            if (subscription?.id) {
                const updatedSubscription = await subscription.update({ status: PAYMENT.SUBSCRIPTION_STATUS.CANCELLED });
                return updatedSubscription;
            } else {
                throw new AppError(404, 'SUBSCRIPTION_NOT_FOUND', {});
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // list subscriptions with pagination
    getSubscriptionList = async (listRequest: SubscriptionListRequestObject, forUser: boolean): Promise<SubscriptionPaginatedData> => {
        try {
            // const { page, perPage, searchText, sortBy, sortDirection } = listRequest;
            let { page, perPage, userId } = listRequest;
            let offset = (page - 1) * perPage;
            let where: WhereOptions = {};

            if (forUser) {
                userId = this.userId;
            }

            if (userId) {
                where = { ...where, userId: userId }
            }

            // let applyfilter = this.buildFilter(where, searchText);
            const subscriptions = await Models.Subscription.findAndCountAll({
                attributes: subscriptionAttributes,
                where: where,
                include: this.includeAssociations(false),
                offset: offset,
                limit: perPage,
                order: [["id", "desc"]],
                subQuery: false
            });
            return JSON.parse(JSON.stringify(subscriptions)) as unknown as SubscriptionPaginatedData;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }






    // list subscriptions with pagination
    getTransactionList = async (listRequest: TranasctionListRequestObject, forUser: boolean): Promise<TransactionPaginatedData> => {
        try {
            // const { page, perPage, searchText, sortBy, sortDirection } = listRequest;
            let { page, perPage, subscriptionId, userId, transactionType } = listRequest;
            let offset = (page - 1) * perPage;
            let where: WhereOptions = {};

            if (forUser) {
                userId = this.userId;
            }

            if (userId) {
                where = { ...where, userId: userId }
            }

            if (subscriptionId) {
                where = { ...where, subscriptionId: subscriptionId }
            }

            // if(transactionType === "subscription") {
            //     where = { ...where, 
            //         subscriptionId: { [Op.ne]: null }, 
            //         giftCardId: { [Op.eq]: null } 
            //     }
            // } else if(transactionType === "giftCard") {
            //     where = { ...where, 
            //         subscriptionId: { [Op.eq]: null }, 
            //         giftCardId: { [Op.ne]: null } 
            //     }
            // }
            // let applyfilter = this.buildFilter(where, searchText);
            const transactions = await Models.Transaction.findAndCountAll({
                attributes: transactionAttributes,
                include: [
                    {
                        attributes: authorAttributes,
                        model: Models.User,
                        as: "author",
                        paranoid: false,
                        include: [
                            {
                                attributes: [],
                                model: Models.UserProfile,
                                as: "userProfile",
                                include: [{ model: Models.Attachment, as: "profileImage" }]
                            }
                        ]
                    }
                ],
                where: where,
                offset: offset,
                limit: perPage,
                order: [["id", "desc"]],
                subQuery: false
            });

            return JSON.parse(JSON.stringify(transactions)) as unknown as TransactionPaginatedData;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create subscription for user
    getSubscriptionObjectById = async (id: number) => {
        try {
            const subscription = await Models.Subscription.findOne({
                where: { id: id }
            });
            if (subscription?.id) {
                return JSON.parse(JSON.stringify(subscription));
            } else {
                throw new AppError(404, 'SUBSCRIPTION_NOT_FOUND', {});
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    createSubscriptionTransaction = async (data: SubscriptionWebhookObject) => {
        const transaction = await sequelize.transaction();
        try {

            const { subscriptionId, metaData, externalSubscriptionId, currentPeriodStart, currentPeriodEnd, planData, userId, amount, currency } = data;

            if (subscriptionId) {
                await Models.Subscription.update({
                    status: PAYMENT.SUBSCRIPTION_STATUS.PENDING,
                    externalSubscriptionId,
                    currentPeriodStart,
                    currentPeriodEnd
                }, { where: { id: subscriptionId }, transaction });
            }

            const transactionObject = {
                subscriptionId: subscriptionId!,
                userId: userId!,
                amount: amount!,
                currency: currency!,
                planData,
                metaData: metaData,
                status: PAYMENT.SUBSCRIPTION_STATUS.PENDING,
                gateway: process.env.PAYMENT_GATEWAY
            };

            await Models.Transaction.create(transactionObject, { transaction });

            await transaction.commit();
            return true;
        } catch (err) {
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    subscriptionStatus = async (data: SubscriptionWebhookObject) => {
        const transaction = await sequelize.transaction();
        try {

            const { subscriptionId, currentPeriodStart, currentPeriodEnd, subscriptionStatus, transactionStatus, oldSubscriptionId } = data

            if (oldSubscriptionId) {
                await Models.Subscription.update({
                    status: PAYMENT.SUBSCRIPTION_STATUS.CANCELLED,
                }, { where: { id: oldSubscriptionId }, transaction });
            }

            await Models.Subscription.update({
                status: subscriptionStatus,
                currentPeriodStart,
                currentPeriodEnd
            }, { where: { id: subscriptionId }, transaction });

            await Models.Transaction.update({ status: transactionStatus }, { where: { subscriptionId: subscriptionId }, transaction });

            await transaction.commit();
            return true;
        } catch (err) {
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    activateSubscription = async (subscriptionObject: SubscriptionPaymentObject, transactionObject: TransactionPaymentObject, oldSubscriptionId: number | null = null) => {
        const transaction = await sequelize.transaction();
        try {

            if (oldSubscriptionId) {
                await Models.Subscription.update({ status: PAYMENT.SUBSCRIPTION_STATUS.CANCELLED, currentPeriodEnd: subscriptionObject.currentPeriodStart }, { where: { id: oldSubscriptionId }, transaction });
            }

            await Models.Subscription.update(subscriptionObject, { where: { id: subscriptionObject.subscriptionId }, transaction });

            await Models.Transaction.update(transactionObject, { where: { id: transactionObject.transactionId }, transaction });

            await transaction.commit();
            return true;
        } catch (err) {
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    updateTransaction = async (transactionObject: TransactionPaymentObject) => {
        try {
            await Models.Transaction.update(transactionObject, { where: { id: transactionObject.transactionId } });
            return true;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    stripeWebhook = async (object: any, type: string) => {
        await Models.WebhookDump.create({
            data: object,
            type: type
        })

        return true;
    }

    revenueStats = async (startDate: string, endDate: string) => {
        //         -- Define date range
        // SET @start_date = '2025-09-01';
        // SET @end_date = '2025-10-01';

        const [revenueSummary, revenue_summary_fields] = await sequelize.query(
            `
                SELECT
                    JSON_UNQUOTE(JSON_EXTRACT(plan_data, '$.currency')) AS currency,
                    ROUND(SUM(
                        CASE 
                            WHEN status = 1 THEN 
                                JSON_EXTRACT(plan_data, '$.amount') *
                                    CASE JSON_UNQUOTE(JSON_EXTRACT(plan_data, '$.interval'))
                                        WHEN 'day' THEN DATEDIFF('${endDate}', '${startDate}')
                                        WHEN 'week' THEN DATEDIFF('${endDate}', '${startDate}')/7
                                        WHEN 'month' THEN TIMESTAMPDIFF(MONTH, '${startDate}', '${endDate}')
                                        WHEN 'year' THEN TIMESTAMPDIFF(YEAR, '${startDate}', '${endDate}')
                                    ELSE 0
                                END
                            ELSE 0
                        END
                    ), 2) AS expected,
    
                    ROUND(SUM(
                        CASE
                            WHEN status = 1 AND created_at >= '${startDate}' AND created_at <= '${endDate}'
                            THEN JSON_EXTRACT(plan_data, '$.amount')
                            ELSE 0
                        END
                    ), 2) AS collected,
    
                    ROUND(SUM(
                        CASE 
                            WHEN status = 3 AND created_at >= '${startDate}' AND created_at <= '${endDate}'
                            THEN JSON_EXTRACT(plan_data, '$.amount')
                            ELSE 0
                        END
                    ), 2) AS overdue_amount
    
                    FROM
                        subscriptions
                    WHERE
                        plan_data IS NOT NULL
                        AND JSON_EXTRACT(plan_data, '$.amount') IS NOT NULL
                        AND JSON_EXTRACT(plan_data, '$.currency') IS NOT NULL
                    GROUP BY
                        JSON_UNQUOTE(JSON_EXTRACT(plan_data, '$.currency'));
                            
                `
        );

        const [dailyRevenue, daily_revenue_fields] = await sequelize.query(
            `
            WITH RECURSIVE date_range AS (
                        SELECT '${startDate}' AS day
                                            UNION ALL
                SELECT day + INTERVAL 1 DAY
                FROM date_range
                WHERE day + INTERVAL 1 DAY <= '${endDate}'
            )
            SELECT
                dr.day,
                IFNULL(JSON_UNQUOTE(JSON_EXTRACT(s.plan_data, '$.currency')), 'N/A') AS currency,
            
                -- Expected revenue for future days only
                ROUND(SUM(
                    CASE
                        WHEN s.status = 1 AND dr.day >= CURRENT_DATE THEN
                            JSON_EXTRACT(s.plan_data, '$.amount') *
                            CASE JSON_UNQUOTE(JSON_EXTRACT(s.plan_data, '$.interval'))
                                WHEN 'day' THEN 1
                                WHEN 'week' THEN 1/7
                                WHEN 'month' THEN 1/30
                                WHEN 'year' THEN 1/365
                                ELSE 0
                            END
                        ELSE 0
                    END
                ), 2) AS expected_revenue,
            
                -- Collected revenue (past)
                ROUND(SUM(
                    CASE
                        WHEN s.status = 1 AND s.created_at >= dr.day AND s.created_at < dr.day + INTERVAL 1 DAY
                        THEN JSON_EXTRACT(s.plan_data, '$.amount')
                        ELSE 0
                    END
                ), 2) AS collected_revenue,
            
                -- Overdue amounts (past)
                ROUND(SUM(
                    CASE
                        WHEN s.status = 3 AND s.created_at >= dr.day AND s.created_at < dr.day + INTERVAL 1 DAY
                        THEN JSON_EXTRACT(s.plan_data, '$.amount')
                        ELSE 0
                    END
                ), 2) AS overdue_amount
            
            FROM
                date_range dr
            LEFT JOIN
                subscriptions s
                ON s.plan_data IS NOT NULL
                AND JSON_EXTRACT(s.plan_data, '$.amount') IS NOT NULL
                AND JSON_EXTRACT(s.plan_data, '$.currency') IS NOT NULL
            GROUP BY
                dr.day,
                currency
            ORDER BY
                dr.day ASC,
                currency;
                            
                `
        );

        const [planUsers, plan_user_field] = await sequelize.query(
            `
            SELECT 
            COALESCE(JSON_UNQUOTE(JSON_EXTRACT(s.plan_data, '$.name')), 'No Plan') AS name,
            COUNT(DISTINCT u.id) AS users
            FROM users u
            LEFT JOIN subscriptions s ON u.id = s.user_id
            where u.deleted_at is null and s.status = 1
            GROUP BY name;
        `
        );

        const [planUsersInRange, plan_user_range_field] = await sequelize.query(
            `
            SELECT 
            COALESCE(JSON_UNQUOTE(JSON_EXTRACT(s.plan_data, '$.name')), 'No Plan') AS name,
            COUNT(DISTINCT u.id) AS users
            FROM users u
            LEFT JOIN subscriptions s ON u.id = s.user_id
            where u.deleted_at is null and s.status = 1
            AND u.created_at >= '${startDate}' AND u.created_at <= '${endDate}'
            GROUP BY name;
        `
        );

        return { revenueSummary, dailyRevenue, planUsers, planUsersInRange };
    };

    subscriptionStats = async (startDate: string, endDate: string, interval: string) => {
        try {
            const [summaryReport, revenue_summary_fields] = await sequelize.query(
                `
                WITH recursive dateRange AS (select '${startDate}' as dateRange union all select dateRange + interval 1 ${interval} from dateRange where dateRange < '${endDate}')
                select DATE(CONVERT_TZ(dr.dateRange,'+00:00','+00:00')) as dateRange,
                cast(sum(if((us.current_period_end >= (dr.dateRange + interval 1 ${interval}) or us.current_period_end is null) and us.current_period_start <= (dr.dateRange + interval 1 ${interval}) ,1,0)) as unsigned) as activeSubscriptions,
                cast(sum(if((us.status=2 and us.current_period_end <= (dr.dateRange + interval 1 ${interval})) or (us.current_period_end <= (dr.dateRange + interval 1 ${interval})) ,1,0)) as unsigned) as expiredSubscriptions  
                from dateRange as dr
                LEFT JOIN subscriptions as us on (us.status!=0)
                LEFT JOIN subscriptions as us2 on (us2.user_id=us.user_id and us2.status!=0 and us2.id>us.id)
                    where us.current_period_start is not null and us.id is not null group by (dr.dateRange) order by dr.dateRange ASC                   
                    `
            );

            return { summaryReport };
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    };

    subscriptionUserStats = async (startDate: string, endDate: string, interval: string) => {
        try {
            const [summaryReport, revenue_summary_fields] = await sequelize.query(
                `
                WITH recursive dateRange AS (select '${startDate}' as dateRange union all select dateRange + interval 1 ${interval} from dateRange where dateRange < '${endDate}')select DATE(CONVERT_TZ(dr.dateRange,'+00:00','+00:00')) as createdAt,
                sum(if((us.current_period_end>=(dr.dateRange + interval 1 ${interval}) or us.current_period_end is null) and us.current_period_start<=(dr.dateRange + interval 1 ${interval}),1,0)) as activeMembers,
                sum(if((us.current_period_end>=dr.dateRange or us.current_period_end is null) and us.current_period_start<=(dr.dateRange + interval 1 ${interval}) and (select min(subscriptions.id) from subscriptions where subscriptions.user_id=us.user_id and subscriptions.id<us.id and subscriptions.status=2) is not null,1,0)) as returningCustomers,
                sum(if((us.current_period_end>=dr.dateRange or us.current_period_end is null) and us.current_period_start<=(dr.dateRange + interval 1 ${interval}) and (select min(subscriptions.id) from subscriptions where subscriptions.user_id=us.user_id and subscriptions.id<us.id and subscriptions.status=2) is null,1,0)) as newCustomers,
                sum(if(us.id is null or (us.current_period_end<=(dr.dateRange + interval 1 ${interval}) and us.status=1) or (us.current_period_end<=dr.dateRange and us.status=2),1,0)) as freeMembers
                from users as u
                LEFT JOIN subscriptions as us on (us.user_id = u.id and us.status!=0)
                LEFT JOIN subscriptions as us1 on (us1.user_id=us.user_id and us1.status!=0 and us1.id>us.id)
                LEFT join dateRange as dr on (us.status!=0)
                where u.created_at<='${endDate}' and (us.current_period_end>'${startDate}' or us.id is null) and dr.dateRange is not null and u.deleted_at is null 
                and us.status!=0 and us1.status!=0 and us.current_period_start is not null
                group by createdAt order by createdAt ASC
                `
            );

            return { summaryReport };
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    };

    updateEndedSubscriptions = async () => {
        const transaction = await sequelize.transaction();
        try {
            // 1. Find all subscriptions with status in [ACTIVE, DUE] that have expired.
            const expiredActiveSubscriptions = await Models.Subscription.findAll({
                attributes: ["id"],
                where: {
                    status: { [Op.in]: [PAYMENT.SUBSCRIPTION_STATUS.ACTIVE, PAYMENT.SUBSCRIPTION_STATUS.DUE] },
                    currentPeriodEnd: { [Op.lte]: new Date() }
                },
                transaction
            });

            if (expiredActiveSubscriptions.length) {
                const expiredSubIds = expiredActiveSubscriptions.map((s: any) => s.id);
                await Models.Subscription.update(
                    { status: PAYMENT.SUBSCRIPTION_STATUS.CANCELLED },
                    { where: { id: expiredSubIds }, transaction }
                );
            }

            // 2. Sync premium status for users who currently have isPremium: true.
            const premiumUserSettings = await Models.UserSetting.findAll({
                attributes: ["userId"],
                where: { isPremium: true },
                transaction
            });

            if (premiumUserSettings.length) {
                const userIds = premiumUserSettings.map((us: any) => us.userId);

                // For these users, check if they have any active/cancelled subscription whose current period is still active
                const usersWithActiveSubscriptions = await Models.Subscription.findAll({
                    attributes: ["userId"],
                    where: {
                        userId: { [Op.in]: userIds },
                        status: { [Op.in]: [PAYMENT.SUBSCRIPTION_STATUS.ACTIVE, PAYMENT.SUBSCRIPTION_STATUS.CANCELLED] },
                        currentPeriodEnd: { [Op.gt]: new Date() }
                    },
                    group: ["userId"],
                    transaction
                });

                const activeUserIdsSet = new Set(usersWithActiveSubscriptions.map((s: any) => s.userId));
                const userIdsToDemote = userIds.filter((userId: number) => !activeUserIdsSet.has(userId));

                if (userIdsToDemote.length) {
                    await Models.UserSetting.update(
                        { isPremium: false },
                        { where: { userId: userIdsToDemote }, transaction }
                    );
                }
            }

            await transaction.commit();
            return true;
        } catch (err) {
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

}