-- Migration: Add AI document limits to companies and projects tables
-- Date: 2025-01-XX
-- Description: Adds monthly usage limits (company-level) and storage limits (project-level) for AI document creation feature

-- ===== Add AI Document Monthly Limits to Companies Table =====
-- Monthly usage limits are company-level (shared across all projects in the company)
-- Only base limit and reset date are stored here. Actual limits per month are in ai_document_usage_history
ALTER TABLE companies 
ADD COLUMN ai_document_monthly_limit INT DEFAULT 20 COMMENT '基本の月間利用制限（回数、会社単位、デフォルト値）',
ADD COLUMN ai_document_limit_reset_date DATE DEFAULT NULL COMMENT '月間制限のリセット日（次回リセット予定日）';

-- Add indexes for performance
ALTER TABLE companies
ADD INDEX idx_ai_document_limit_reset (ai_document_limit_reset_date);

-- ===== Add File Size to AI Document Files Table =====
-- Add file_size column to track individual file sizes
ALTER TABLE ai_document_files
ADD COLUMN file_size BIGINT DEFAULT 0 COMMENT 'ファイルサイズ（バイト）';

-- Add index for file size queries
ALTER TABLE ai_document_files
ADD INDEX idx_file_size (file_size);

-- ===== AI Document Usage History Table =====
-- Table to store monthly usage history for companies
-- This table stores both historical and current month's usage data and limits
CREATE TABLE IF NOT EXISTS ai_document_usage_history (
    id VARCHAR(36) PRIMARY KEY,
    company_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    year INT NOT NULL COMMENT '年',
    month INT NOT NULL COMMENT '月（1-12）',
    monthly_usage INT DEFAULT 0 COMMENT 'その月の使用回数（リアルタイム更新）',
    monthly_limit INT DEFAULT 20 COMMENT 'その月の利用制限（回数、基本制限+増加分）',
    total_storage_used BIGINT DEFAULT 0 COMMENT 'その月の合計使用容量（バイト、会社全体、リアルタイム更新）',
    storage_limit BIGINT DEFAULT 2147483648 COMMENT 'その月の容量制限（バイト、デフォルト2GB）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '記録作成日時',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最終更新日時',
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_company_year_month (company_id, year, month),
    INDEX idx_company_year_month (company_id, year, month),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

