/* 
   This query calculates the total invoiced amount for each month based on the 'recurrent' query.
   
   It generates a list of months starting from the earliest 'nextDue' date and extends it by a 
   specified number of months.
   
   For each month, it sums the 'total' from the 'recurrent' query where the month matches the
   billing cycle defined by 'nextDue' and 'monthly'.

   This will run for 12 months starting from the earliest 'nextDue' date, but you can adjust
   the number of months by changing the INTERVAL in the 'params' CTE.
*/
WITH RECURSIVE recurrent AS (
   SELECT
      id AS id,
      description AS description,
      salesorder.total AS total,
      debtor.name AS client,
      days AS days,
      monthly AS monthly,
      last_sent AS lastSent,
      begin AS begin,
      DATE_ADD(IF(last_sent='0000-00-00', begin, last_sent), INTERVAL invoices.monthly MONTH) AS nextDue,
      MONTH(DATE_ADD(IF(last_sent='0000-00-00', begin, last_sent), INTERVAL invoices.monthly MONTH)) AS nextMonthDue
   FROM
      0_recurrent_invoices invoices
      JOIN 0_sales_orders salesorder USING (order_no)
      JOIN 0_debtors_master debtor ON (invoices.debtor_no=debtor.debtor_no)
),
params AS (
   SELECT
      DATE_FORMAT(MIN(nextDue), '%Y-%m-15') AS start_month,
      DATE_ADD(DATE_FORMAT(MIN(nextDue), '%Y-%m-15'), INTERVAL 12 MONTH) AS end_month
   FROM recurrent
),
months AS (
   SELECT start_month AS month_start, end_month
   FROM params
   UNION ALL
   SELECT DATE_ADD(month_start, INTERVAL 1 MONTH), end_month
   FROM months
   WHERE month_start < end_month
)
SELECT
   month_start,
   SUM(
      CASE
         WHEN month_start >= DATE_FORMAT(nextDue, '%Y-%m-15')
          AND MOD(
             PERIOD_DIFF(DATE_FORMAT(month_start, '%Y%m'), DATE_FORMAT(nextDue, '%Y%m')),
             monthly
          ) = 0
         THEN total
         ELSE 0
      END
   ) AS invoiced_total
FROM months
JOIN recurrent
GROUP BY month_start
ORDER BY month_start;