-- Holidays table with weekend support
CREATE TABLE IF NOT EXISTS `holidays` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `holiday_date` DATE NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `type` ENUM('Holiday','Weekend') NOT NULL DEFAULT 'Holiday',
  `remarks` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_holidays_date_type` (`holiday_date`,`type`),
  KEY `idx_holidays_date` (`holiday_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Helper: seed weekends for a date range (MySQL 8+). Replace variables and run.
-- SET @start_date = '2025-01-01';
-- SET @end_date = '2025-12-31';
-- WITH RECURSIVE dates AS (
--   SELECT @start_date AS d
--   UNION ALL
--   SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM dates WHERE d < @end_date
-- )
-- INSERT IGNORE INTO holidays (holiday_date, title, type)
-- SELECT d, DATE_FORMAT(d, '%W'), 'Weekend'
-- FROM dates
-- WHERE DAYOFWEEK(d) IN (1,7); -- 1=Sunday,7=Saturday

-- Seed: All Weekends for the year 2025 (explicit, no recursion)
INSERT IGNORE INTO holidays (holiday_date, title, type) VALUES
('2025-01-04','Saturday','Weekend'),
('2025-01-05','Sunday','Weekend'),
('2025-01-11','Saturday','Weekend'),
('2025-01-12','Sunday','Weekend'),
('2025-01-18','Saturday','Weekend'),
('2025-01-19','Sunday','Weekend'),
('2025-01-25','Saturday','Weekend'),
('2025-01-26','Sunday','Weekend'),
('2025-02-01','Saturday','Weekend'),
('2025-02-02','Sunday','Weekend'),
('2025-02-08','Saturday','Weekend'),
('2025-02-09','Sunday','Weekend'),
('2025-02-15','Saturday','Weekend'),
('2025-02-16','Sunday','Weekend'),
('2025-02-22','Saturday','Weekend'),
('2025-02-23','Sunday','Weekend'),
('2025-03-01','Saturday','Weekend'),
('2025-03-02','Sunday','Weekend'),
('2025-03-08','Saturday','Weekend'),
('2025-03-09','Sunday','Weekend'),
('2025-03-15','Saturday','Weekend'),
('2025-03-16','Sunday','Weekend'),
('2025-03-22','Saturday','Weekend'),
('2025-03-23','Sunday','Weekend'),
('2025-03-29','Saturday','Weekend'),
('2025-03-30','Sunday','Weekend'),
('2025-04-05','Saturday','Weekend'),
('2025-04-06','Sunday','Weekend'),
('2025-04-12','Saturday','Weekend'),
('2025-04-13','Sunday','Weekend'),
('2025-04-19','Saturday','Weekend'),
('2025-04-20','Sunday','Weekend'),
('2025-04-26','Saturday','Weekend'),
('2025-04-27','Sunday','Weekend'),
('2025-05-03','Saturday','Weekend'),
('2025-05-04','Sunday','Weekend'),
('2025-05-10','Saturday','Weekend'),
('2025-05-11','Sunday','Weekend'),
('2025-05-17','Saturday','Weekend'),
('2025-05-18','Sunday','Weekend'),
('2025-05-24','Saturday','Weekend'),
('2025-05-25','Sunday','Weekend'),
('2025-05-31','Saturday','Weekend'),
('2025-06-01','Sunday','Weekend'),
('2025-06-07','Saturday','Weekend'),
('2025-06-08','Sunday','Weekend'),
('2025-06-14','Saturday','Weekend'),
('2025-06-15','Sunday','Weekend'),
('2025-06-21','Saturday','Weekend'),
('2025-06-22','Sunday','Weekend'),
('2025-06-28','Saturday','Weekend'),
('2025-06-29','Sunday','Weekend'),
('2025-07-05','Saturday','Weekend'),
('2025-07-06','Sunday','Weekend'),
('2025-07-12','Saturday','Weekend'),
('2025-07-13','Sunday','Weekend'),
('2025-07-19','Saturday','Weekend'),
('2025-07-20','Sunday','Weekend'),
('2025-07-26','Saturday','Weekend'),
('2025-07-27','Sunday','Weekend'),
('2025-08-02','Saturday','Weekend'),
('2025-08-03','Sunday','Weekend'),
('2025-08-09','Saturday','Weekend'),
('2025-08-10','Sunday','Weekend'),
('2025-08-16','Saturday','Weekend'),
('2025-08-17','Sunday','Weekend'),
('2025-08-23','Saturday','Weekend'),
('2025-08-24','Sunday','Weekend'),
('2025-08-30','Saturday','Weekend'),
('2025-08-31','Sunday','Weekend'),
('2025-09-06','Saturday','Weekend'),
('2025-09-07','Sunday','Weekend'),
('2025-09-13','Saturday','Weekend'),
('2025-09-14','Sunday','Weekend'),
('2025-09-20','Saturday','Weekend'),
('2025-09-21','Sunday','Weekend'),
('2025-09-27','Saturday','Weekend'),
('2025-09-28','Sunday','Weekend'),
('2025-10-04','Saturday','Weekend'),
('2025-10-05','Sunday','Weekend'),
('2025-10-11','Saturday','Weekend'),
('2025-10-12','Sunday','Weekend'),
('2025-10-18','Saturday','Weekend'),
('2025-10-19','Sunday','Weekend'),
('2025-10-25','Saturday','Weekend'),
('2025-10-26','Sunday','Weekend'),
('2025-11-01','Saturday','Weekend'),
('2025-11-02','Sunday','Weekend'),
('2025-11-08','Saturday','Weekend'),
('2025-11-09','Sunday','Weekend'),
('2025-11-15','Saturday','Weekend'),
('2025-11-16','Sunday','Weekend'),
('2025-11-22','Saturday','Weekend'),
('2025-11-23','Sunday','Weekend'),
('2025-11-29','Saturday','Weekend'),
('2025-11-30','Sunday','Weekend'),
('2025-12-06','Saturday','Weekend'),
('2025-12-07','Sunday','Weekend'),
('2025-12-13','Saturday','Weekend'),
('2025-12-14','Sunday','Weekend'),
('2025-12-20','Saturday','Weekend'),
('2025-12-21','Sunday','Weekend'),
('2025-12-27','Saturday','Weekend'),
('2025-12-28','Sunday','Weekend');

-- Seed: Common National Government Holidays for 2025 (India)
INSERT IGNORE INTO holidays (holiday_date, title, type, remarks) VALUES
('2025-01-01', 'New Year''s Day', 'Holiday', NULL),
('2025-01-26', 'Republic Day', 'Holiday', NULL),
('2025-05-01', 'Labour Day', 'Holiday', NULL),
('2025-08-15', 'Independence Day', 'Holiday', NULL),
('2025-10-02', 'Gandhi Jayanti', 'Holiday', NULL),
('2025-12-25', 'Christmas Day', 'Holiday', NULL);