-- Engagement by Plan Query
-- This query gets the count of active subscriptions grouped by package_category_type
-- Similar to the original query pattern you provided

SELECT 
    tp.package_category_type,
    COUNT(DISTINCT tcs.subscriber_id) AS total_subscribers
FROM tbl_customer_subscriptions AS tcs
INNER JOIN tbl_packages AS tp
    ON tp.id = tcs.package_id
    AND tp.app_id = tcs.app_id  -- Ensure both tables have same app_id
WHERE 
    tcs.status = 2  -- Active subscriptions
    AND tcs.app_id = 1003  -- Replace with actual app_id
    AND tp.app_id = 1003  -- Replace with actual app_id
    AND tp.package_category_type IN ('Basic', 'Premium', 'Standard')
    AND tcs.start_date IS NOT NULL
    AND tcs.end_date IS NOT NULL
    AND DATE(tcs.start_date) <= CURRENT_DATE  -- Current date >= start_date
    AND DATE(tcs.end_date) >= CURRENT_DATE    -- Current date <= end_date
GROUP BY 
    tp.package_category_type
ORDER BY 
    total_subscribers DESC;

-- Note: The actual implementation in buildEngagementByPlan() also:
-- 1. Gets the LATEST subscription for each subscriber (by start_date DESC, created DESC)
-- 2. Groups subscribers by their latest plan category type
-- 3. Calculates engagement scores for each group based on content_watched data

-- For getting LATEST subscription per subscriber (current implementation logic):
-- This requires a subquery or window function to get the latest subscription

WITH LatestSubscriptions AS (
    SELECT DISTINCT ON (tcs.subscriber_id)
        tcs.subscriber_id,
        tp.package_category_type
    FROM tbl_customer_subscriptions AS tcs
    INNER JOIN tbl_packages AS tp
        ON tp.id = tcs.package_id
        AND tp.app_id = tcs.app_id
    WHERE 
        tcs.status = 2
        AND tcs.app_id = 1003
        AND tp.app_id = 1003
        AND tp.package_category_type IN ('Basic', 'Premium', 'Standard')
        AND tcs.start_date IS NOT NULL
        AND tcs.end_date IS NOT NULL
        AND DATE(tcs.start_date) <= CURRENT_DATE
        AND DATE(tcs.end_date) >= CURRENT_DATE
    ORDER BY 
        tcs.subscriber_id,
        tcs.start_date DESC,
        tcs.created DESC
)
SELECT 
    package_category_type,
    COUNT(subscriber_id) AS total_subscribers
FROM LatestSubscriptions
GROUP BY 
    package_category_type
ORDER BY 
    total_subscribers DESC;

