-- Step 34: Overtime holiday calendar.
-- Run after step33_manual_overtime_entries.sql.
-- Sundays are handled automatically by application code; this table is for extra declared holidays.

CREATE TABLE IF NOT EXISTS overtime_holidays (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  holiday_date DATE NOT NULL,
  title VARCHAR(150) NOT NULL DEFAULT 'Holiday',
  reason TEXT DEFAULT NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_by INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_overtime_holidays_date (holiday_date),
  KEY idx_overtime_holidays_active_date (is_active, holiday_date),
  KEY idx_overtime_holidays_created_by (created_by),
  CONSTRAINT fk_overtime_holidays_created_by
    FOREIGN KEY (created_by) REFERENCES users(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;