-- ====================================================================
-- AWC Creches Monthly Data Export Query
-- Description: Fetches Anganwadi center data with children counts from 3 tables
-- Usage: Replace @month_number with the desired month (e.g., 9 for September)
-- ====================================================================

-- Set the month number here (1-12)
SET @month_number = 9;

-- Main Query
SELECT 
    an.awc_center_code AS 'AWC Center Code',
    an.name AS 'Anganwadi Name',
    @month_number AS 'Month',
    
    -- Count from children_0m_6m table
    (SELECT COUNT(*) 
     FROM children_0m_6m c1 
     WHERE c1.awc_code = an.awc_center_code 
     AND c1.monthupdated = @month_number) AS 'Children 0-6 Months Count',
    
    -- Count from children_6m_3y table
    (SELECT COUNT(*) 
     FROM children_6m_3y c2 
     WHERE c2.awc_code = an.awc_center_code 
     AND c2.monthupdated = @month_number) AS 'Children 6 Months-3 Years Count',
    
    -- Count from children_3y_6y table
    (SELECT COUNT(*) 
     FROM children_3y_6y c3 
     WHERE c3.awc_code = an.awc_center_code 
     AND c3.monthupdated = @month_number) AS 'Children 3-6 Years Count',
    
    -- Total count from all three tables
    (SELECT COUNT(*) 
     FROM children_0m_6m c1 
     WHERE c1.awc_code = an.awc_center_code 
     AND c1.monthupdated = @month_number) +
    (SELECT COUNT(*) 
     FROM children_6m_3y c2 
     WHERE c2.awc_code = an.awc_center_code 
     AND c2.monthupdated = @month_number) +
    (SELECT COUNT(*) 
     FROM children_3y_6y c3 
     WHERE c3.awc_code = an.awc_center_code 
     AND c3.monthupdated = @month_number) AS 'Total Children Count'
    
FROM anganwadi_names an
ORDER BY an.awc_center_code;

-- ====================================================================
-- Alternative: Export directly to CSV using MySQL command line
-- ====================================================================
-- Run this in MySQL command line or terminal:
/*
mysql -u root -p awc_creches_monthly_data -e "
SET @month_number = 9;
SELECT 
    an.awc_center_code AS 'AWC Center Code',
    an.name AS 'Anganwadi Name',
    @month_number AS 'Month',
    (SELECT COUNT(*) FROM children_0m_6m c1 WHERE c1.awc_code = an.awc_center_code AND c1.monthupdated = @month_number) AS 'Children 0-6 Months Count',
    (SELECT COUNT(*) FROM children_6m_3y c2 WHERE c2.awc_code = an.awc_center_code AND c2.monthupdated = @month_number) AS 'Children 6 Months-3 Years Count',
    (SELECT COUNT(*) FROM children_3y_6y c3 WHERE c3.awc_code = an.awc_center_code AND c3.monthupdated = @month_number) AS 'Children 3-6 Years Count',
    (SELECT COUNT(*) FROM children_0m_6m c1 WHERE c1.awc_code = an.awc_center_code AND c1.monthupdated = @month_number) + (SELECT COUNT(*) FROM children_6m_3y c2 WHERE c2.awc_code = an.awc_center_code AND c2.monthupdated = @month_number) + (SELECT COUNT(*) FROM children_3y_6y c3 WHERE c3.awc_code = an.awc_center_code AND c3.monthupdated = @month_number) AS 'Total Children Count'
FROM anganwadi_names an
ORDER BY an.awc_center_code;
" > awc_children_data_month_9.csv
*/

-- ====================================================================
-- Using SELECT INTO OUTFILE (if MySQL has file permissions)
-- ====================================================================
/*
SET @month_number = 9;

SELECT 
    an.awc_center_code,
    an.name,
    @month_number,
    (SELECT COUNT(*) FROM children_0m_6m c1 WHERE c1.awc_code = an.awc_center_code AND c1.monthupdated = @month_number),
    (SELECT COUNT(*) FROM children_6m_3y c2 WHERE c2.awc_code = an.awc_center_code AND c2.monthupdated = @month_number),
    (SELECT COUNT(*) FROM children_3y_6y c3 WHERE c3.awc_code = an.awc_center_code AND c3.monthupdated = @month_number),
    (SELECT COUNT(*) FROM children_0m_6m c1 WHERE c1.awc_code = an.awc_center_code AND c1.monthupdated = @month_number) + 
    (SELECT COUNT(*) FROM children_6m_3y c2 WHERE c2.awc_code = an.awc_center_code AND c2.monthupdated = @month_number) + 
    (SELECT COUNT(*) FROM children_3y_6y c3 WHERE c3.awc_code = an.awc_center_code AND c3.monthupdated = @month_number)
INTO OUTFILE 'C:/wamp64/tmp/awc_children_data_month_9.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM anganwadi_names an
ORDER BY an.awc_center_code;
*/
