CREATE TABLE `calendar` (
  `date_id` DATE NOT NULL PRIMARY KEY,
  `is_weekend` TINYINT(1) DEFAULT 0,
  `is_holiday` TINYINT(1) DEFAULT 0,
  `is_working_day` TINYINT(1) AS (IF(is_weekend = 0 AND is_holiday = 0, 1, 0)) VIRTUAL
);



-- Set the start date for the year. Change 2025 to your desired year.
SET @start_date = '2025-01-01';

-- Determine the number of days in the year by checking for a leap year
SET @end_date = DATE_ADD(@start_date, INTERVAL 1 YEAR);
SET @days_in_year = DATEDIFF(@end_date, @start_date);

-- Create a temporary table to store the sequence of numbers (0 to 365)
CREATE TEMPORARY TABLE IF NOT EXISTS day_numbers (
    day_number INT
);

-- Generate the number sequence and insert it into the temporary table
INSERT INTO day_numbers (day_number)
SELECT
    (t1.i + (10 * t2.i) + (100 * t3.i))
FROM
    (SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as t1
CROSS JOIN
    (SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as t2
CROSS JOIN
    (SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as t3
LIMIT 366;

-- Now, use a prepared statement to dynamically create and execute the query.
-- This is necessary to use a variable in the LIMIT clause in older MySQL versions.
SET @sql_query = CONCAT('
    INSERT INTO `calendar` (`date_id`, `is_weekend`, `is_holiday`)
    SELECT
        DATE_ADD(\'', @start_date, '\', INTERVAL day_number DAY),
        IF(DAYOFWEEK(DATE_ADD(\'', @start_date, '\', INTERVAL day_number DAY)) IN (1, 7), 1, 0),
        0
    FROM
        day_numbers
    LIMIT ', @days_in_year, ';'
);

PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Clean up by dropping the temporary table
DROP TEMPORARY TABLE day_numbers;






-- Example to mark a holiday
UPDATE `calendar` SET `is_holiday` = 1 WHERE `date_id` = '2025-08-15';

-- Count number of working days since the given date till current date
SELECT
    t1.order_id,
    t1.actual_clearance_date,
    COUNT(c.date_id) AS working_days_since_clearance
FROM
    orders t1
JOIN
    calendar c
ON
    c.date_id > t1.actual_clearance_date
    AND c.date_id <= CURDATE()
    AND c.is_working_day = 1
GROUP BY
    t1.order_id, t1.actual_clearance_date
HAVING
    COUNT(c.date_id) >= 5;