-- Migration: Create company holidays and holiday settings tables
-- Date: 2025-01-XX
-- Description: Creates tables to store company-specific holidays and holiday settings (including national holiday handling)

-- ===== Company Holiday Settings =====
CREATE TABLE IF NOT EXISTS company_holiday_settings (
    id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,
    company_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    national_holiday_handling ENUM('holiday', 'workday') DEFAULT 'holiday' COMMENT '祝日の扱い（holiday=休日、workday=出勤日）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_company (company_id),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Company Holidays =====
CREATE TABLE IF NOT EXISTS company_holidays (
    id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,
    company_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    holiday_date DATE NOT NULL COMMENT '休日（日付）',
    holiday_name VARCHAR(255) NOT NULL COMMENT '休日名',
    is_national_holiday BOOLEAN DEFAULT FALSE COMMENT '日本の祝日かどうか',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_company_date (company_id, holiday_date),
    INDEX idx_company (company_id),
    INDEX idx_holiday_date (holiday_date),
    INDEX idx_is_national_holiday (is_national_holiday)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

