-- Magic Star duplicated household + member counts
SELECT
  COUNT(*) AS duplicated_households,
  SUM(COALESCE(ch.case_count, 1)) AS duplicated_members
FROM assistance_assistance aa
JOIN assistance_category ac ON aa.category_id = ac.id
JOIN cases_case hoh ON hoh.id = aa.case_id
LEFT JOIN cases_household ch ON ch.id = hoh.household_id
WHERE (ac.name LIKE '%Magic Star%' OR ac.name LIKE '%Magic Star%'
    OR ac.name LIKE '%Door Dash%' OR ac.name LIKE '%DoorDash%')
  AND aa.date >= '{{start_date}}' AND aa.date < '{{end_exclusive}}';

-- Market (Purple Unicorn) duplicated household + member counts
SELECT
  COUNT(*) AS duplicated_households,
  SUM(COALESCE(ch.case_count, 1)) AS duplicated_members
FROM assistance_assistance aa
JOIN assistance_category ac ON aa.category_id = ac.id
JOIN cases_case hoh ON hoh.id = aa.case_id
LEFT JOIN cases_household ch ON ch.id = hoh.household_id
WHERE ac.name LIKE '%Purple Unicorn%'
  AND aa.date >= '{{start_date}}' AND aa.date < '{{end_exclusive}}';

-- Rocket Cart duplicated household + member counts
SELECT
  COUNT(*) AS duplicated_households,
  SUM(COALESCE(ch.case_count, 1)) AS duplicated_members
FROM assistance_assistance aa
JOIN assistance_category ac ON aa.category_id = ac.id
JOIN cases_case hoh ON hoh.id = aa.case_id
LEFT JOIN cases_household ch ON ch.id = hoh.household_id
WHERE ac.name = 'Rocket Cart'
  AND aa.date >= '{{start_date}}' AND aa.date < '{{end_exclusive}}';

-- Rocket Cart by county with/without children
SELECT
  COALESCE(dc.name, 'Other') AS county,
  SUM(CASE WHEN ch.max_dob > DATE_SUB(CURDATE(), INTERVAL 18 YEAR) THEN 1 ELSE 0 END) AS with_children,
  SUM(CASE WHEN ch.max_dob IS NULL OR ch.max_dob <= DATE_SUB(CURDATE(), INTERVAL 18 YEAR) THEN 1 ELSE 0 END) AS without_children,
  COUNT(*) AS duplicated_households
FROM assistance_assistance aa
JOIN assistance_category ac ON aa.category_id = ac.id
JOIN cases_case hoh ON hoh.id = aa.case_id
JOIN cases_household ch ON ch.id = hoh.household_id
LEFT JOIN details_county dc ON dc.id = ch.county_id
WHERE ac.name = 'Rocket Cart'
  AND aa.date >= '{{start_date}}' AND aa.date < '{{end_exclusive}}'
GROUP BY county
ORDER BY duplicated_households DESC;

-- Duplicated member age counts (each visit x each household member)
WITH visits AS (
  SELECT aa.id, aa.case_id
  FROM assistance_assistance aa
  JOIN assistance_category ac ON ac.id = aa.category_id
  WHERE (ac.name LIKE '%Magic Star%' OR ac.name LIKE '%Magic Star%'
      OR ac.name LIKE '%Door Dash%' OR ac.name LIKE '%DoorDash%'
      OR ac.name LIKE '%Purple Unicorn%' OR ac.name = 'Rocket Cart')
    AND aa.date >= '{{start_date}}' AND aa.date < '{{end_exclusive}}'
)
SELECT
  SUM(CASE WHEN TIMESTAMPDIFF(YEAR, c2.date_of_birth, CURDATE()) < 18 THEN 1 ELSE 0 END)  AS dup_members_0_17,
  SUM(CASE WHEN TIMESTAMPDIFF(YEAR, c2.date_of_birth, CURDATE()) BETWEEN 18 AND 59 THEN 1 ELSE 0 END) AS dup_members_18_59,
  SUM(CASE WHEN TIMESTAMPDIFF(YEAR, c2.date_of_birth, CURDATE()) >= 60 THEN 1 ELSE 0 END) AS dup_members_60_plus,
  SUM(CASE WHEN c2.date_of_birth IS NULL THEN 1 ELSE 0 END) AS dup_members_unknown
FROM visits v
JOIN cases_case hoh ON hoh.id = v.case_id
JOIN cases_case c2 ON c2.household_id = hoh.household_id AND c2.deceased = 0;

-- FoodLink events and unique agencies
SELECT
  COUNT(*) AS frh_events,
  COUNT(DISTINCT ee.location_id) AS unique_agencies
FROM events_event ee
JOIN events_eventtype et ON ee.event_type_id = et.id
WHERE et.name = 'FoodLink'
  AND ee.date >= '{{start_date}}' AND ee.date < '{{end_exclusive}}';

-- Bouncy Castle + Green Dragon event totals
SELECT
  et.name,
  COUNT(*) AS events
FROM events_event ee
JOIN events_eventtype et ON ee.event_type_id = et.id
WHERE et.name IN ('Pop up Pantry', 'Senior Pop Up', 'Green Dragon/Re-Start Kitchen')
  AND ee.date >= '{{start_date}}' AND ee.date < '{{end_exclusive}}'
GROUP BY et.name
ORDER BY events DESC;

-- YTD master summary aligned to Oasis assistance report logic
SELECT
  COUNT(*) AS duplicated_household_count,
  COUNT(DISTINCT aa.case_id) AS household_count,
  SUM(COALESCE(ch.case_count, 1)) AS duplicated_member_count,
  ROUND(SUM(CASE WHEN aa.unit_id = 2 THEN aa.amount ELSE 0 END)) AS pounds_distributed,
  ROUND(SUM(CASE WHEN aa.unit_id = 3 THEN aa.amount ELSE 0 END)) AS boxes_bags_distributed
FROM assistance_assistance aa
JOIN assistance_category ac ON ac.id = aa.category_id
JOIN cases_case hoh ON hoh.id = aa.case_id
LEFT JOIN cases_household ch ON ch.id = hoh.household_id
WHERE aa.date >= '{{start_date}}' AND aa.date < '{{end_exclusive}}'
  AND (ac.name LIKE '%Magic Star%' OR ac.name LIKE '%Magic Star%'
    OR ac.name LIKE '%Door Dash%' OR ac.name LIKE '%DoorDash%'
    OR ac.name LIKE '%Purple Unicorn%'
    OR ac.name = 'Rocket Cart'
    OR ac.name LIKE '%Green Dragon%' OR ac.name LIKE '%Golden Bear%');