-- Migration: Create daily_reports and daily_report_confirmations tables
-- Date: 2025-01-XX
-- Description: Creates tables for daily reports and confirmations

-- ===== Daily Reports =====
CREATE TABLE IF NOT EXISTS daily_reports (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    project_id VARCHAR(36) NOT NULL,
    report_date DATE NOT NULL,
    content TEXT NOT NULL,
    additional_comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, report_date),
    INDEX idx_project_date (project_id, report_date),
    INDEX idx_report_date (report_date),
    UNIQUE KEY unique_user_project_date (user_id, project_id, report_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Daily Report Confirmations =====
CREATE TABLE IF NOT EXISTS daily_report_confirmations (
    id VARCHAR(36) PRIMARY KEY,
    daily_report_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL COMMENT '確認したユーザーID',
    confirmed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (daily_report_id) REFERENCES daily_reports(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_daily_report (daily_report_id),
    INDEX idx_user (user_id),
    UNIQUE KEY unique_report_user (daily_report_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
