-- Migration: Create hijriah_masehi table
-- Date: 2026-01-05
-- Usage: mysql -u <user> -p <database> < 20260105_create_hijriah_masehi.sql

START TRANSACTION;

/*
 Option A: Use generated columns for year/month (MySQL 5.7+)
 If your MySQL < 5.7, use Option B (comment out Option A and uncomment Option B).
*/

CREATE TABLE IF NOT EXISTS `hijriah_masehi` (
  `date` DATE NOT NULL,
  `hijriah_date` VARCHAR(32) NOT NULL,
  `day_name` VARCHAR(32) NOT NULL,
  `source` VARCHAR(100) NOT NULL DEFAULT 'muhammadiyah',
  `year` SMALLINT GENERATED ALWAYS AS (YEAR(`date`)) STORED,
  `month` TINYINT GENERATED ALWAYS AS (MONTH(`date`)) STORED,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`date`),
  KEY `idx_hijmasehi_year_month` (`year`, `month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Option B: If your MySQL does NOT support generated columns, use the following instead:
--
-- CREATE TABLE IF NOT EXISTS `hijriah_masehi` (
--   `date` DATE NOT NULL,
--   `hijriah_date` VARCHAR(32) NOT NULL,
--   `day_name` VARCHAR(32) NOT NULL,
--   `source` VARCHAR(100) NOT NULL DEFAULT 'muhammadiyah',
--   `year` SMALLINT NOT NULL,
--   `month` TINYINT NOT NULL,
--   `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
--   `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
--   PRIMARY KEY (`date`),
--   KEY `idx_hijmasehi_year_month` (`year`, `month`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- If you use Option B, consider a trigger to auto-fill year/month:
-- DELIMITER $$
-- CREATE TRIGGER trg_hijmasehi_before_insert BEFORE INSERT ON hijriah_masehi
-- FOR EACH ROW BEGIN
--   SET NEW.year = YEAR(NEW.date);
--   SET NEW.month = MONTH(NEW.date);
-- END$$
-- CREATE TRIGGER trg_hijmasehi_before_update BEFORE UPDATE ON hijriah_masehi
-- FOR EACH ROW BEGIN
--   SET NEW.year = YEAR(NEW.date);
--   SET NEW.month = MONTH(NEW.date);
-- END$$
-- DELIMITER ;

-- Insert existing data from `hijriah_calendar` if it exists (keeps rows idempotent)
DELIMITER $$
DROP PROCEDURE IF EXISTS migrate_hijmasehi_data$$
CREATE PROCEDURE migrate_hijmasehi_data()
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'hijriah_calendar') THEN
    INSERT INTO hijriah_masehi (`date`, `hijriah_date`, `day_name`, `source`, `year`, `month`)
    SELECT `date`, `hijriah_date`, `day_name`, 'migrated', YEAR(`date`), MONTH(`date`)
    FROM hijriah_calendar
    ON DUPLICATE KEY UPDATE
      hijriah_date = VALUES(hijriah_date),
      day_name = VALUES(day_name),
      source = VALUES(source),
      year = VALUES(year),
      month = VALUES(month),
      updated_at = CURRENT_TIMESTAMP;
  END IF;
END$$

CALL migrate_hijmasehi_data()$$
DROP PROCEDURE IF EXISTS migrate_hijmasehi_data$$
DELIMITER ;

COMMIT;

-- Down: rollback
-- To drop the table use:
-- DROP TABLE IF EXISTS `hijriah_masehi`;

-- Notes:
-- - If you used Option B (no generated columns), be sure to populate `year` and `month` for new inserts (via triggers or application logic).
-- - To re-run this migration safely, keep the ON DUPLICATE KEY UPDATE clause (it will upsert existing dates).
-- - Run: mysql -u <user> -p <database> < migrations/20260105_create_hijriah_masehi.sql
