-- Migration: Add project leaders, last login tracking, and batch settings
-- Date: 2025-01-XX
-- Description: Adds project leaders table, last_login_at to users, and batch_settings table

-- ===== Project Leaders (Many-to-Many relationship) =====
CREATE TABLE IF NOT EXISTS project_leaders (
    project_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (project_id, user_id),
    INDEX idx_project (project_id),
    INDEX idx_user (user_id),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Add last_login_at to users =====
ALTER TABLE users 
ADD COLUMN last_login_at TIMESTAMP NULL DEFAULT NULL COMMENT '最終ログイン日時';

-- Add index for better query performance
ALTER TABLE users
ADD INDEX idx_last_login_at (last_login_at);

-- ===== Batch Settings =====
CREATE TABLE IF NOT EXISTS batch_settings (
    id VARCHAR(36) PRIMARY KEY,
    setting_key VARCHAR(255) NOT NULL UNIQUE,
    setting_value TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default batch execution time (1:00 AM)
INSERT INTO batch_settings (id, setting_key, setting_value, description)
VALUES (UUID(), 'inactive_user_notification_time', '01:00', '未ログインユーザー通知バッチの実行時間 (HH:mm形式)')
ON DUPLICATE KEY UPDATE setting_value = '01:00';

