-- 本番環境用データベースインデックス最適化SQL
-- English Service User Management System

-- ユーザーテーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);
CREATE INDEX IF NOT EXISTS idx_users_last_activity_at ON users(last_activity_at);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);

-- 管理者ユーザーテーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_admin_users_email ON admin_users(email);
CREATE INDEX IF NOT EXISTS idx_admin_users_is_active ON admin_users(is_active);
CREATE INDEX IF NOT EXISTS idx_admin_users_last_login_at ON admin_users(last_login_at);

-- ユーザー契約テーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_user_subscriptions_user_id ON user_subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_subscriptions_plan_id ON user_subscriptions(plan_id);
CREATE INDEX IF NOT EXISTS idx_user_subscriptions_status ON user_subscriptions(status);
CREATE INDEX IF NOT EXISTS idx_user_subscriptions_expires_at ON user_subscriptions(expires_at);
CREATE INDEX IF NOT EXISTS idx_user_subscriptions_started_at ON user_subscriptions(started_at);
CREATE INDEX IF NOT EXISTS idx_user_subscriptions_auto_renewal ON user_subscriptions(auto_renewal);

-- 複合インデックス（よく使用されるクエリ用）
CREATE INDEX IF NOT EXISTS idx_user_subscriptions_user_status ON user_subscriptions(user_id, status);
CREATE INDEX IF NOT EXISTS idx_user_subscriptions_status_expires ON user_subscriptions(status, expires_at);
CREATE INDEX IF NOT EXISTS idx_user_subscriptions_plan_status ON user_subscriptions(plan_id, status);

-- プランテーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_plans_is_active ON plans(is_active);
CREATE INDEX IF NOT EXISTS idx_plans_billing_cycle ON plans(billing_cycle);
CREATE INDEX IF NOT EXISTS idx_plans_sort_order ON plans(sort_order);

-- 契約履歴テーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_subscription_history_subscription_id ON subscription_history(subscription_id);
CREATE INDEX IF NOT EXISTS idx_subscription_history_action ON subscription_history(action);
CREATE INDEX IF NOT EXISTS idx_subscription_history_created_at ON subscription_history(created_at);
CREATE INDEX IF NOT EXISTS idx_subscription_history_admin_id ON subscription_history(performed_by_admin_id);

-- 監査ログテーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_audit_logs_auditable_type_id ON audit_logs(auditable_type, auditable_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_type_id ON audit_logs(user_type, user_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_event ON audit_logs(event);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at);

-- 複合インデックス（監査ログ用）
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_event ON audit_logs(user_id, event);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_event ON audit_logs(created_at, event);

-- ロール・権限テーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_roles_guard_name ON roles(guard_name);
CREATE INDEX IF NOT EXISTS idx_permissions_guard_name ON permissions(guard_name);
CREATE INDEX IF NOT EXISTS idx_permissions_route_pattern ON permissions(route_pattern);

-- セッションテーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_last_activity ON sessions(last_activity);

-- キャッシュテーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_cache_expiration ON cache(expiration);

-- ジョブテーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_jobs_queue ON jobs(queue);
CREATE INDEX IF NOT EXISTS idx_jobs_available_at ON jobs(available_at);
CREATE INDEX IF NOT EXISTS idx_jobs_created_at ON jobs(created_at);

-- 失敗したジョブテーブルのインデックス
CREATE INDEX IF NOT EXISTS idx_failed_jobs_failed_at ON failed_jobs(failed_at);

-- パフォーマンス向上のための統計情報更新
ANALYZE TABLE users;
ANALYZE TABLE admin_users;
ANALYZE TABLE user_subscriptions;
ANALYZE TABLE subscription_history;
ANALYZE TABLE plans;
ANALYZE TABLE audit_logs;
ANALYZE TABLE roles;
ANALYZE TABLE permissions;
ANALYZE TABLE model_has_roles;
ANALYZE TABLE role_has_permissions;