-- 本番環境用データベース最適化設定
-- MySQL 8.0+ 用設定

-- InnoDB設定の最適化
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1GB (サーバーメモリの70-80%に調整)
SET GLOBAL innodb_log_file_size = 268435456; -- 256MB
SET GLOBAL innodb_log_buffer_size = 16777216; -- 16MB
SET GLOBAL innodb_flush_log_at_trx_commit = 1; -- 最高の耐久性
SET GLOBAL innodb_file_per_table = 1;
SET GLOBAL innodb_open_files = 300;

-- クエリキャッシュ設定（MySQL 8.0では非推奨だが、参考として記載）
-- SET GLOBAL query_cache_type = 1;
-- SET GLOBAL query_cache_size = 268435456; -- 256MB

-- 接続設定
SET GLOBAL max_connections = 200;
SET GLOBAL max_user_connections = 50;
SET GLOBAL connect_timeout = 10;
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

-- テーブルキャッシュ設定
SET GLOBAL table_open_cache = 2000;
SET GLOBAL table_definition_cache = 1400;

-- ソート・グループ化設定
SET GLOBAL sort_buffer_size = 2097152; -- 2MB
SET GLOBAL read_buffer_size = 131072; -- 128KB
SET GLOBAL read_rnd_buffer_size = 262144; -- 256KB
SET GLOBAL join_buffer_size = 262144; -- 256KB

-- 一時テーブル設定
SET GLOBAL tmp_table_size = 67108864; -- 64MB
SET GLOBAL max_heap_table_size = 67108864; -- 64MB

-- スレッド設定
SET GLOBAL thread_cache_size = 50;
SET GLOBAL thread_stack = 262144; -- 256KB

-- バイナリログ設定（レプリケーション用）
SET GLOBAL binlog_cache_size = 1048576; -- 1MB
SET GLOBAL max_binlog_cache_size = 4294967296; -- 4GB
SET GLOBAL binlog_stmt_cache_size = 32768; -- 32KB

-- スロークエリログ設定
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5; -- 0.5秒以上のクエリをログ
SET GLOBAL log_queries_not_using_indexes = 1;

-- パフォーマンススキーマ設定
SET GLOBAL performance_schema = 1;

-- 自動統計情報更新設定
SET GLOBAL innodb_stats_auto_recalc = 1;
SET GLOBAL innodb_stats_persistent = 1;
SET GLOBAL innodb_stats_persistent_sample_pages = 20;

-- デッドロック検出設定
SET GLOBAL innodb_deadlock_detect = 1;
SET GLOBAL innodb_lock_wait_timeout = 50;

-- パーティション設定用のSQL（必要に応じて実行）
-- 監査ログテーブルのパーティション（月次）
/*
ALTER TABLE audit_logs PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503),
    PARTITION p202503 VALUES LESS THAN (202504),
    PARTITION p202504 VALUES LESS THAN (202505),
    PARTITION p202505 VALUES LESS THAN (202506),
    PARTITION p202506 VALUES LESS THAN (202507),
    PARTITION p202507 VALUES LESS THAN (202508),
    PARTITION p202508 VALUES LESS THAN (202509),
    PARTITION p202509 VALUES LESS THAN (202510),
    PARTITION p202510 VALUES LESS THAN (202511),
    PARTITION p202511 VALUES LESS THAN (202512),
    PARTITION p202512 VALUES LESS THAN (202601),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
*/

-- 契約履歴テーブルのパーティション（月次）
/*
ALTER TABLE subscription_history PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503),
    PARTITION p202503 VALUES LESS THAN (202504),
    PARTITION p202504 VALUES LESS THAN (202505),
    PARTITION p202505 VALUES LESS THAN (202506),
    PARTITION p202506 VALUES LESS THAN (202507),
    PARTITION p202507 VALUES LESS THAN (202508),
    PARTITION p202508 VALUES LESS THAN (202509),
    PARTITION p202509 VALUES LESS THAN (202510),
    PARTITION p202510 VALUES LESS THAN (202511),
    PARTITION p202511 VALUES LESS THAN (202512),
    PARTITION p202512 VALUES LESS THAN (202601),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
*/

-- 定期メンテナンス用のイベントスケジューラー設定
SET GLOBAL event_scheduler = ON;

-- 週次テーブル最適化イベント
DELIMITER $$
CREATE EVENT IF NOT EXISTS weekly_table_optimization
ON SCHEDULE EVERY 1 WEEK
STARTS '2025-01-12 02:00:00'
DO
BEGIN
    OPTIMIZE TABLE users;
    OPTIMIZE TABLE admin_users;
    OPTIMIZE TABLE user_subscriptions;
    OPTIMIZE TABLE subscription_history;
    OPTIMIZE TABLE plans;
    OPTIMIZE TABLE audit_logs;
    OPTIMIZE TABLE roles;
    OPTIMIZE TABLE permissions;
    OPTIMIZE TABLE model_has_roles;
    OPTIMIZE TABLE role_has_permissions;
END$$
DELIMITER ;

-- 日次統計情報更新イベント
DELIMITER $$
CREATE EVENT IF NOT EXISTS daily_stats_update
ON SCHEDULE EVERY 1 DAY
STARTS '2025-01-01 01:00:00'
DO
BEGIN
    ANALYZE TABLE users;
    ANALYZE TABLE admin_users;
    ANALYZE TABLE user_subscriptions;
    ANALYZE TABLE subscription_history;
    ANALYZE TABLE plans;
    ANALYZE TABLE audit_logs;
END$$
DELIMITER ;

-- 古いログデータのクリーンアップイベント（月次）
DELIMITER $$
CREATE EVENT IF NOT EXISTS monthly_log_cleanup
ON SCHEDULE EVERY 1 MONTH
STARTS '2025-01-01 03:00:00'
DO
BEGIN
    -- 90日以上古い監査ログを削除（本番環境では要件に応じて調整）
    DELETE FROM audit_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
    
    -- 1年以上古いセッションデータを削除
    DELETE FROM sessions WHERE last_activity < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR));
    
    -- 失敗したジョブの古いデータを削除
    DELETE FROM failed_jobs WHERE failed_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END$$
DELIMITER ;