Runbook: HNR Membership Sales & Metrics Reporting

WITH totals AS (
    -- (re-use the Phase 2 query as a CTE or save to a TEMPORARY TABLE)
    SELECT /* Phase 2 SELECT without the GROUP BY period split */ ...
)
SELECT
    SUM(total_count)       AS total_registered,
    SUM(admin_count)       AS admin_added,
    SUM(online_count)      AS online_registrations,
    SUM(paid_count)        AS online_paid,
    SUM(unpaid_count)      AS online_unpaid,
    SUM(revenue_rand)      AS revenue_collected,
    SUM(outstanding_rand)  AS outstanding
FROM totals;

SELECT COUNT(*) AS membership_rows, COUNT(u.ID) AS resolved_users
FROM membership m
LEFT JOIN wp_users u ON u.ID = m.person_id
WHERE m.membership_period_id IN (@ANNUAL_PERIOD_ID, @ER_PERIOD_ID);

SELECT
    mc.type_selector                AS category,
    m.id                            AS membership_id,
    COALESCE(
        NULLIF(TRIM(CONCAT(COALESCE(um_first.meta_value,''),' ',COALESCE(um_last.meta_value,''))), ''),
        u.display_name
    )                               AS member_name,
    COALESCE(um_cell.meta_value,  um_parent_cell.meta_value,  '') AS cell,
    COALESCE(
        um_email.meta_value,
        -- user_email is only usable if it's not the fake postfix
        CASE WHEN u.user_email LIKE '%@member.wpcycling.com' THEN NULL ELSE u.user_email END,
        um_parent_email.meta_value,
        ''
    )                               AS email,
    oli.price                       AS amount_rand,
    o.id                            AS order_id,
    o.external_id                   AS wc_order_id
FROM membership m
JOIN membership_criteria mc    ON mc.id = m.criteria_id
JOIN order_line_item oli       ON oli.membership_id = m.id
JOIN sales_order o             ON o.id = oli.order_id
LEFT JOIN wp_users u           ON u.ID = m.person_id
LEFT JOIN wp_usermeta um_first        ON um_first.user_id        = u.ID AND um_first.meta_key        = 'first_name'
LEFT JOIN wp_usermeta um_last         ON um_last.user_id         = u.ID AND um_last.meta_key         = 'last_name'
LEFT JOIN wp_usermeta um_cell         ON um_cell.user_id         = u.ID AND um_cell.meta_key         = 'contact_number'
LEFT JOIN wp_usermeta um_email        ON um_email.user_id        = u.ID AND um_email.meta_key        = 'person_email'
LEFT JOIN wp_usermeta um_parent_cell  ON um_parent_cell.user_id  = u.ID AND um_parent_cell.meta_key  = 'parent_number'
LEFT JOIN wp_usermeta um_parent_email ON um_parent_email.user_id = u.ID AND um_parent_email.meta_key = 'parent_email'
WHERE m.membership_period_id IN (@ANNUAL_PERIOD_ID, @ER_PERIOD_ID)
  AND m.status IN ('D','P')                                    -- genuinely unpaid (membership not active)
  AND o.status IN ('D','U')                                    -- order also not paid
  AND o.external_id IS NOT NULL AND o.external_id <> 'UNPAID'  -- online order exists
ORDER BY mc.type_selector, member_name;

SELECT
    main.ID                              AS main_user_id,
    TRIM(CONCAT(COALESCE(mf.meta_value,''),' ',COALESCE(ml.meta_value,''))) AS main_name,
    mc.meta_value                        AS main_cell,
    COALESCE(me.meta_value,
        CASE WHEN main.user_email LIKE '%@member.wpcycling.com' THEN NULL ELSE main.user_email END
    )                                    AS main_email,
    -- Family members as a comma-separated list with their type_selector
    GROUP_CONCAT(
        CONCAT(
            TRIM(CONCAT(COALESCE(mem_f.meta_value,''),' ',COALESCE(mem_l.meta_value,''))),
            ' (', crit.type_selector, ')'
        )
        ORDER BY crit.type_selector, mem_l.meta_value SEPARATOR ', '
    )                                    AS family_members,
    SUM(oli.price)                       AS family_outstanding_rand
FROM membership m_main
JOIN membership_criteria c_main ON c_main.id = m_main.criteria_id AND c_main.type_selector = 'FAMILY_MAIN'
JOIN wp_users main              ON main.ID = m_main.person_id
LEFT JOIN wp_usermeta mf ON mf.user_id = main.ID AND mf.meta_key = 'first_name'
LEFT JOIN wp_usermeta ml ON ml.user_id = main.ID AND ml.meta_key = 'last_name'
LEFT JOIN wp_usermeta mc ON mc.user_id = main.ID AND mc.meta_key = 'contact_number'
LEFT JOIN wp_usermeta me ON me.user_id = main.ID AND me.meta_key = 'person_email'
-- Join the membership's own order line (unpaid order)
JOIN order_line_item oli_main ON oli_main.membership_id = m_main.id
JOIN sales_order o_main       ON o_main.id = oli_main.order_id
                             AND o_main.status IN ('D','U')
                             AND o_main.external_id IS NOT NULL AND o_main.external_id <> 'UNPAID'
-- Gather the family members via main_member_id meta
LEFT JOIN wp_usermeta mm        ON mm.meta_key = 'main_member_id' AND mm.meta_value = CAST(main.ID AS CHAR)
LEFT JOIN wp_users member       ON member.ID = mm.user_id
LEFT JOIN wp_usermeta mem_f     ON mem_f.user_id = member.ID AND mem_f.meta_key = 'first_name'
LEFT JOIN wp_usermeta mem_l     ON mem_l.user_id = member.ID AND mem_l.meta_key = 'last_name'
LEFT JOIN membership m_mem      ON m_mem.person_id = member.ID
                               AND m_mem.membership_period_id = @ANNUAL_PERIOD_ID
LEFT JOIN membership_criteria crit ON crit.id = m_mem.criteria_id
LEFT JOIN order_line_item oli   ON oli.membership_id = m_main.id
WHERE m_main.membership_period_id = @ANNUAL_PERIOD_ID
  AND m_main.status IN ('D','P')
GROUP BY main.ID, main_name, main_cell, main_email
ORDER BY main_name;

SELECT
    mc.name                    AS category,
    COUNT(DISTINCT m.id)       AS memberships_affected,
    COUNT(DISTINCT o.id)       AS stale_order_count,
    SUM(oli.price)             AS stale_order_value_rand
FROM membership m
JOIN membership_criteria mc ON mc.id = m.criteria_id
JOIN order_line_item oli    ON oli.membership_id = m.id
JOIN sales_order o          ON o.id = oli.order_id
WHERE m.membership_period_id IN (@ANNUAL_PERIOD_ID, @ER_PERIOD_ID)
  AND m.status = 'A'                               -- already active
  AND o.status IN ('D','U')                        -- order still open
  AND o.external_id IS NOT NULL AND o.external_id <> 'UNPAID'
GROUP BY mc.id, mc.name
ORDER BY mc.name;

SELECT
    m.id                       AS membership_id,
    mc.name                    AS category,
    COUNT(DISTINCT o.id)       AS pending_order_count,
    GROUP_CONCAT(DISTINCT o.id ORDER BY o.id)       AS order_ids,
    MIN(o.transaction_date_time) AS earliest,
    MAX(o.transaction_date_time) AS latest,
    SUM(oli.price)             AS duplicated_outstanding_rand
FROM membership m
JOIN membership_criteria mc ON mc.id = m.criteria_id
JOIN order_line_item oli    ON oli.membership_id = m.id
JOIN sales_order o          ON o.id = oli.order_id
WHERE m.membership_period_id IN (@ANNUAL_PERIOD_ID, @ER_PERIOD_ID)
  AND m.status IN ('D','P')
  AND o.status IN ('D','U')
GROUP BY m.id, mc.name
HAVING COUNT(DISTINCT o.id) > 1
ORDER BY pending_order_count DESC, m.id;

SELECT
    mc.type_selector,
    COUNT(*)                                          AS line_items,
    SUM(CASE WHEN oli.price = 450 THEN 1 ELSE 0 END)  AS lines_at_r450,
    SUM(CASE WHEN oli.price = 0   THEN 1 ELSE 0 END)  AS lines_at_r0,
    SUM(oli.price) - SUM(CASE WHEN oli.price > 0 AND mc.type_selector = 'FAMILY_MAIN'
                              THEN oli.price ELSE 0 END) AS overstated_rand
FROM membership m
JOIN membership_criteria mc ON mc.id = m.criteria_id
JOIN order_line_item oli    ON oli.membership_id = m.id
JOIN sales_order o          ON o.id = oli.order_id
WHERE m.membership_period_id IN (@ANNUAL_PERIOD_ID, @ER_PERIOD_ID)
  AND mc.type_selector LIKE 'FAMILY%'
  AND o.status = 'P'
GROUP BY mc.type_selector;

SELECT
    mc.type_selector,
    COUNT(DISTINCT m.id) AS unpaid_members,
    SUM(CASE WHEN COALESCE(um_cell.meta_value,  um_parent_cell.meta_value)  <> '' THEN 1 ELSE 0 END) AS with_cell,
    SUM(CASE WHEN COALESCE(
            um_email.meta_value,
            CASE WHEN u.user_email LIKE '%@member.wpcycling.com' THEN NULL ELSE u.user_email END,
            um_parent_email.meta_value
        ) <> '' THEN 1 ELSE 0 END) AS with_email,
    SUM(CASE WHEN COALESCE(um_cell.meta_value,  um_parent_cell.meta_value)  = ''
             AND  COALESCE(
                    um_email.meta_value,
                    CASE WHEN u.user_email LIKE '%@member.wpcycling.com' THEN NULL ELSE u.user_email END,
                    um_parent_email.meta_value
                 ) = ''
             THEN 1 ELSE 0 END) AS unreachable
FROM membership m
JOIN membership_criteria mc ON mc.id = m.criteria_id
JOIN order_line_item oli    ON oli.membership_id = m.id
JOIN sales_order o          ON o.id = oli.order_id
LEFT JOIN wp_users u        ON u.ID = m.person_id
LEFT JOIN wp_usermeta um_cell         ON um_cell.user_id  = u.ID AND um_cell.meta_key  = 'contact_number'
LEFT JOIN wp_usermeta um_email        ON um_email.user_id = u.ID AND um_email.meta_key = 'person_email'
LEFT JOIN wp_usermeta um_parent_cell  ON um_parent_cell.user_id  = u.ID AND um_parent_cell.meta_key  = 'parent_number'
LEFT JOIN wp_usermeta um_parent_email ON um_parent_email.user_id = u.ID AND um_parent_email.meta_key = 'parent_email'
WHERE m.membership_period_id IN (@ANNUAL_PERIOD_ID, @ER_PERIOD_ID)
  AND m.status IN ('D','P')
  AND o.status IN ('D','U')
  AND o.external_id IS NOT NULL AND o.external_id <> 'UNPAID'
GROUP BY mc.type_selector;