# [NG] よくある初心者の統合失敗パターン
「とりあえず全部APIサーバーで作ろう」  # → 過剰設計
「後で統合すればいいでしょ」        # → 技術的負債
「セキュリティは最後に考える」      # → 設計やり直し
# → 結果：プロジェクト炎上・予算超過・品質問題

# [OK] プロの統合設計アプローチ
適材適所_設計 = {
    "学習者ポータル": "Client-side",    # 個人用途→木造住宅
    "コース管理": "Edge Functions",     # 外部連携→プレハブ工法  
    "管理機能": "API Server",          # 企業用途→鉄筋コンクリート
    "共通インフラ": "Supabase"          # 全棟共通設備
}
# → 結果：適切なコスト・最高の性能・保守しやすい設計

# backend/app/core/auth.py
from typing import Optional, List
from fastapi import HTTPException, Depends
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from supabase import create_client
import jwt
from pydantic import BaseModel

class UserContext(BaseModel):
    id: str
    email: str
    organization_id: str
    role: str
    permissions: List[str]

class AuthManager:
    def __init__(self, supabase_url: str, supabase_key: str):
        self.supabase = create_client(supabase_url, supabase_key)
        self.security = HTTPBearer()
    
    async def get_current_user(
        self, 
        credentials: HTTPAuthorizationCredentials = Depends(HTTPBearer())
    ) -> UserContext:
        """統合認証：全パターンで共通利用"""
        
        try:
            # JWTトークン検証
            token = credentials.credentials
            user_response = await self.supabase.auth.get_user(token)
            
            if user_response.user is None:
                raise HTTPException(status_code=401, detail="Invalid token")
            
            # ユーザープロファイル取得
            profile_response = await self.supabase.table('user_profiles')\
                .select('*')\
                .eq('id', user_response.user.id)\
                .single()\
                .execute()
            
            if not profile_response.data:
                raise HTTPException(status_code=404, detail="User profile not found")
            
            profile = profile_response.data
            
            # ロール・権限取得
            permissions = await self._get_user_permissions(
                profile['role'], 
                profile['organization_id']
            )
            
            return UserContext(
                id=profile['id'],
                email=profile['email'],
                organization_id=profile['organization_id'],
                role=profile['role'],
                permissions=permissions
            )
            
        except Exception as e:
            raise HTTPException(status_code=401, detail=f"Authentication failed: {str(e)}")
    
    async def _get_user_permissions(self, role: str, org_id: str) -> List[str]:
        """ロールベース権限管理"""
        
        role_permissions = {
            'student': [
                'courses:read',
                'progress:read',
                'progress:write'
            ],
            'instructor': [
                'courses:read',
                'courses:write',
                'progress:read',
                'students:read'
            ],
            'admin': [
                'courses:*',
                'users:*',
                'analytics:*',
                'settings:*'
            ]
        }
        
        return role_permissions.get(role, [])

# 権限チェックデコレータ
def require_permission(permission: str):
    def decorator(func):
        async def wrapper(
            *args, 
            current_user: UserContext = Depends(AuthManager.get_current_user),
            **kwargs
        ):
            if permission not in current_user.permissions and '*' not in current_user.permissions:
                raise HTTPException(status_code=403, detail="Insufficient permissions")
            return await func(*args, current_user=current_user, **kwargs)
        return wrapper
    return decorator

# frontend/src/main.py
import flet as ft
import asyncio
from typing import Optional
from dataclasses import dataclass

from services.auth_service import AuthService
from services.course_service import CourseService
from services.progress_service import ProgressService
from services.realtime_service import RealtimeService
from ui.pages.login_page import LoginPage
from ui.pages.dashboard_page import DashboardPage
from ui.pages.course_page import CoursePage

@dataclass
class AppState:
    current_user: Optional[dict] = None
    current_page: str = "login"
    courses: list = None
    progress: dict = None

class EduConnectApp:
    def __init__(self):
        self.state = AppState()
        self.auth_service = AuthService()
        self.course_service = CourseService()
        self.progress_service = ProgressService()
        self.realtime_service = RealtimeService()
        
    async def main(self, page: ft.Page):
        """メインアプリケーション"""
        
        page.title = "EduConnect - 学習ポータル"
        page.theme_mode = ft.ThemeMode.LIGHT
        page.window_width = 1200
        page.window_height = 800
        
        # ページ状態管理
        self.page = page
        
        # 認証状態確認
        await self._check_auth_status()
        
        # 初期ページ表示
        await self._render_current_page()
        
        # リアルタイム機能初期化
        if self.state.current_user:
            await self._setup_realtime()
    
    async def _check_auth_status(self):
        """認証状態確認"""
        try:
            user = await self.auth_service.get_current_user()
            if user:
                self.state.current_user = user
                self.state.current_page = "dashboard"
                await self._load_user_data()
        except Exception as e:
            print(f"Auth check failed: {e}")
            self.state.current_page = "login"
    
    async def _load_user_data(self):
        """ユーザーデータ読み込み"""
        try:
            # コース一覧取得
            courses = await self.course_service.get_enrolled_courses(
                self.state.current_user['id']
            )
            self.state.courses = courses
            
            # 進捗情報取得
            progress = await self.progress_service.get_user_progress(
                self.state.current_user['id']
            )
            self.state.progress = progress
            
        except Exception as e:
            print(f"Data loading failed: {e}")
    
    async def _render_current_page(self):
        """現在ページのレンダリング"""
        self.page.controls.clear()
        
        if self.state.current_page == "login":
            login_page = LoginPage(self._on_login_success)
            self.page.add(login_page.build())
            
        elif self.state.current_page == "dashboard":
            dashboard_page = DashboardPage(
                user=self.state.current_user,
                courses=self.state.courses,
                progress=self.state.progress,
                on_course_select=self._on_course_select,
                on_logout=self._on_logout
            )
            self.page.add(dashboard_page.build())
            
        elif self.state.current_page == "course":
            course_page = CoursePage(
                course=self.state.selected_course,
                user=self.state.current_user,
                on_back=self._on_back_to_dashboard
            )
            self.page.add(course_page.build())
        
        await self.page.update_async()
    
    async def _setup_realtime(self):
        """リアルタイム機能セットアップ"""
        
        def on_progress_update(payload):
            """進捗更新のリアルタイム同期"""
            asyncio.create_task(self._handle_progress_update(payload))
        
        def on_course_update(payload):
            """コース更新のリアルタイム同期"""
            asyncio.create_task(self._handle_course_update(payload))
        
        # 進捗更新の監視
        await self.realtime_service.subscribe_to_progress_updates(
            self.state.current_user['id'],
            on_progress_update
        )
        
        # コース更新の監視
        await self.realtime_service.subscribe_to_course_updates(
            self.state.current_user['organization_id'],
            on_course_update
        )
    
    async def _handle_progress_update(self, payload):
        """進捗更新ハンドラ"""
        # 進捗データ更新
        await self._load_user_data()
        
        # UI更新
        if self.state.current_page == "dashboard":
            await self._render_current_page()
    
    async def _on_login_success(self, user):
        """ログイン成功ハンドラ"""
        self.state.current_user = user
        self.state.current_page = "dashboard"
        await self._load_user_data()
        await self._setup_realtime()
        await self._render_current_page()
    
    async def _on_course_select(self, course):
        """コース選択ハンドラ"""
        self.state.selected_course = course
        self.state.current_page = "course"
        await self._render_current_page()
    
    async def _on_logout(self):
        """ログアウトハンドラ"""
        await self.auth_service.sign_out()
        await self.realtime_service.cleanup()
        self.state = AppState()
        await self._render_current_page()

# アプリケーション起動
if __name__ == "__main__":
    app = EduConnectApp()
    ft.app(target=app.main, view=ft.AppView.FLET_APP)

# backend/app/api/v1/analytics.py
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from typing import List, Optional
from datetime import datetime, timedelta

from app.core.auth import AuthManager, UserContext, require_permission
from app.core.database import get_db
from app.services.analytics_service import AnalyticsService
from app.schemas.analytics import (
    CourseAnalytics,
    UserEngagementReport,
    LearningTrendReport
)

router = APIRouter()
auth_manager = AuthManager()

@router.get("/course-performance", response_model=List[CourseAnalytics])
@require_permission("analytics:read")
async def get_course_performance(
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None,
    current_user: UserContext = Depends(auth_manager.get_current_user),
    db: Session = Depends(get_db)
):
    """コースパフォーマンス分析"""
    
    analytics_service = AnalyticsService(db)
    
    # デフォルト期間設定（過去30日）
    if not start_date:
        start_date = datetime.now() - timedelta(days=30)
    if not end_date:
        end_date = datetime.now()
    
    try:
        analytics = await analytics_service.get_course_performance(
            organization_id=current_user.organization_id,
            start_date=start_date,
            end_date=end_date
        )
        
        return analytics
        
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Analytics generation failed: {str(e)}")

@router.get("/engagement-report", response_model=UserEngagementReport)
@require_permission("analytics:read")
async def get_engagement_report(
    period: str = "30d",
    current_user: UserContext = Depends(auth_manager.get_current_user),
    db: Session = Depends(get_db)
):
    """ユーザーエンゲージメントレポート"""
    
    analytics_service = AnalyticsService(db)
    
    period_mapping = {
        "7d": timedelta(days=7),
        "30d": timedelta(days=30),
        "90d": timedelta(days=90)
    }
    
    if period not in period_mapping:
        raise HTTPException(status_code=400, detail="Invalid period")
    
    try:
        report = await analytics_service.generate_engagement_report(
            organization_id=current_user.organization_id,
            period=period_mapping[period]
        )
        
        return report
        
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Report generation failed: {str(e)}")

# services/analytics_service.py
import pandas as pd
from sqlalchemy import text
from typing import Dict, List
from datetime import datetime, timedelta

class AnalyticsService:
    def __init__(self, db_session):
        self.db = db_session
    
    async def get_course_performance(
        self, 
        organization_id: str,
        start_date: datetime,
        end_date: datetime
    ) -> List[Dict]:
        """高度なコース分析"""
        
        # 複雑な分析クエリ
        query = text("""
        WITH course_metrics AS (
            SELECT 
                c.id,
                c.title,
                COUNT(DISTINCT lp.user_id) as total_enrollments,
                AVG(lp.completion_percentage) as avg_completion,
                PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lp.completion_percentage) as median_completion,
                SUM(lp.time_spent_minutes) as total_time_spent,
                COUNT(CASE WHEN lp.completion_percentage = 100 THEN 1 END) as completions,
                
                -- 学習速度分析
                AVG(
                    CASE 
                        WHEN lp.completion_percentage > 0 
                        THEN lp.time_spent_minutes / lp.completion_percentage * 100
                    END
                ) as avg_time_to_complete,
                
                -- エンゲージメント指標
                COUNT(DISTINCT DATE(lp.last_accessed_at)) as active_days,
                AVG(
                    EXTRACT(days FROM lp.updated_at - lp.created_at)
                ) as avg_learning_duration_days
                
            FROM courses c
            LEFT JOIN learning_progress lp ON c.id = lp.course_id
            WHERE c.organization_id = :org_id
                AND lp.created_at BETWEEN :start_date AND :end_date
            GROUP BY c.id, c.title
        ),
        benchmark_data AS (
            SELECT 
                AVG(avg_completion) as industry_avg_completion,
                AVG(total_time_spent / NULLIF(total_enrollments, 0)) as industry_avg_time_per_user
            FROM course_metrics
        )
        SELECT 
            cm.*,
            bd.industry_avg_completion,
            bd.industry_avg_time_per_user,
            
            -- パフォーマンス評価
            CASE 
                WHEN cm.avg_completion > bd.industry_avg_completion * 1.2 THEN 'excellent'
                WHEN cm.avg_completion > bd.industry_avg_completion THEN 'good'
                WHEN cm.avg_completion > bd.industry_avg_completion * 0.8 THEN 'average'
                ELSE 'needs_improvement'
            END as performance_rating,
            
            -- リスク指標
            CASE 
                WHEN cm.avg_completion < 30 AND cm.total_enrollments > 10 THEN 'high_dropout_risk'
                WHEN cm.avg_time_to_complete > bd.industry_avg_time_per_user * 2 THEN 'engagement_risk'
                ELSE 'normal'
            END as risk_indicator
            
        FROM course_metrics cm
        CROSS JOIN benchmark_data bd
        ORDER BY cm.total_enrollments DESC, cm.avg_completion DESC
        """)
        
        result = await self.db.execute(query, {
            'org_id': organization_id,
            'start_date': start_date,
            'end_date': end_date
        })
        
        return [dict(row) for row in result.fetchall()]
    
    async def generate_engagement_report(
        self,
        organization_id: str,
        period: timedelta
    ) -> Dict:
        """ユーザーエンゲージメント詳細分析"""
        
        end_date = datetime.now()
        start_date = end_date - period
        
        # エンゲージメント指標計算
        engagement_query = text("""
        WITH user_activity AS (
            SELECT 
                up.id as user_id,
                up.full_name,
                up.role,
                COUNT(DISTINCT lp.course_id) as courses_enrolled,
                COUNT(DISTINCT DATE(lp.last_accessed_at)) as active_days,
                SUM(lp.time_spent_minutes) as total_time_spent,
                AVG(lp.completion_percentage) as avg_completion,
                MAX(lp.last_accessed_at) as last_activity,
                
                -- セッション分析
                COUNT(DISTINCT 
                    CASE 
                        WHEN lp.last_accessed_at >= :start_date 
                        THEN DATE(lp.last_accessed_at)
                    END
                ) as recent_active_days
                
            FROM user_profiles up
            LEFT JOIN learning_progress lp ON up.id = lp.user_id
            WHERE up.organization_id = :org_id
                AND up.role = 'student'
            GROUP BY up.id, up.full_name, up.role
        ),
        engagement_segments AS (
            SELECT 
                *,
                CASE 
                    WHEN recent_active_days >= 20 AND avg_completion > 70 THEN 'highly_engaged'
                    WHEN recent_active_days >= 10 AND avg_completion > 40 THEN 'moderately_engaged'
                    WHEN recent_active_days >= 3 AND avg_completion > 20 THEN 'low_engaged'
                    ELSE 'at_risk'
                END as engagement_level
            FROM user_activity
        )
        SELECT 
            engagement_level,
            COUNT(*) as user_count,
            AVG(total_time_spent) as avg_time_spent,
            AVG(avg_completion) as avg_completion_rate,
            AVG(courses_enrolled) as avg_courses_enrolled
        FROM engagement_segments
        GROUP BY engagement_level
        ORDER BY 
            CASE engagement_level
                WHEN 'highly_engaged' THEN 1
                WHEN 'moderately_engaged' THEN 2
                WHEN 'low_engaged' THEN 3
                WHEN 'at_risk' THEN 4
            END
        """)
        
        result = await self.db.execute(engagement_query, {
            'org_id': organization_id,
            'start_date': start_date
        })
        
        engagement_data = [dict(row) for row in result.fetchall()]
        
        # 推奨アクション生成
        recommendations = self._generate_engagement_recommendations(engagement_data)
        
        return {
            "period": f"{period.days} days",
            "generated_at": datetime.now().isoformat(),
            "engagement_segments": engagement_data,
            "recommendations": recommendations,
            "summary": {
                "total_users": sum(seg["user_count"] for seg in engagement_data),
                "highly_engaged_percentage": next(
                    (seg["user_count"] for seg in engagement_data if seg["engagement_level"] == "highly_engaged"), 0
                ) / sum(seg["user_count"] for seg in engagement_data) * 100
            }
        }
    
    def _generate_engagement_recommendations(self, engagement_data: List[Dict]) -> List[Dict]:
        """エンゲージメント改善推奨事項生成"""
        
        recommendations = []
        
        for segment in engagement_data:
            level = segment["engagement_level"]
            count = segment["user_count"]
            
            if level == "at_risk" and count > 5:
                recommendations.append({
                    "priority": "high",
                    "target_segment": "at_risk",
                    "action": "implement_re_engagement_campaign",
                    "description": f"{count}名のリスクユーザーに対して再エンゲージメントキャンペーンを実施",
                    "expected_impact": "20〜30%のユーザー復帰率"
                })
            
            if level == "low_engaged" and count > 10:
                recommendations.append({
                    "priority": "medium",
                    "target_segment": "low_engaged",
                    "action": "personalized_learning_paths",
                    "description": f"{count}名の低エンゲージメントユーザーに個別化学習パスを提供",
                    "expected_impact": "エンゲージメント40%向上"
                })
        
        return recommendations

# tests/integration/test_full_flow.py
import pytest
import asyncio
from fastapi.testclient import TestClient
from unittest.mock import Mock, patch

from backend.app.main import app
from frontend.src.services.auth_service import AuthService

class TestEduConnectIntegration:
    """エンドツーエンド統合テスト"""
    
    @pytest.fixture
    def client(self):
        return TestClient(app)
    
    @pytest.fixture
    async def authenticated_user(self):
        """テスト用認証ユーザー"""
        auth_service = AuthService()
        return await auth_service.create_test_user({
            "email": "test@example.com",
            "password": "testpassword123",
            "organization_id": "test-org-id",
            "role": "student"
        })
    
    async def test_complete_learning_journey(self, client, authenticated_user):
        """完全な学習ジャーニーテスト"""
        
        # 1. ユーザー登録・ログイン
        login_response = client.post("/api/v1/auth/login", json={
            "email": "test@example.com",
            "password": "testpassword123"
        })
        assert login_response.status_code == 200
        token = login_response.json()["access_token"]
        headers = {"Authorization": f"Bearer {token}"}
        
        # 2. コース一覧取得
        courses_response = client.get("/api/v1/courses", headers=headers)
        assert courses_response.status_code == 200
        courses = courses_response.json()
        assert len(courses) > 0
        
        course_id = courses[0]["id"]
        
        # 3. コース登録 (Edge Function)
        enrollment_response = client.post(
            "/functions/v1/course-management",
            headers=headers,
            json={
                "action": "enroll",
                "courseId": course_id
            }
        )
        assert enrollment_response.status_code == 200
        assert enrollment_response.json()["success"] is True
        
        # 4. 学習進捗更新
        progress_response = client.post(
            "/functions/v1/course-management", 
            headers=headers,
            json={
                "action": "progress",
                "courseId": course_id,
                "lessonId": "lesson-1",
                "data": {
                    "completionPercentage": 50,
                    "timeSpentMinutes": 30
                }
            }
        )
        assert progress_response.status_code == 200
        
        # 5. 進捗確認 (FastAPI)
        progress_check = client.get(f"/api/v1/progress/{course_id}", headers=headers)
        assert progress_check.status_code == 200
        progress_data = progress_check.json()
        assert progress_data["completion_percentage"] == 50
        
        # 6. コース完了
        completion_response = client.post(
            "/functions/v1/course-management",
            headers=headers, 
            json={
                "action": "complete",
                "courseId": course_id,
                "data": {
                    "completionPercentage": 100,
                    "timeSpentMinutes": 120
                }
            }
        )
        assert completion_response.status_code == 200
        
        # 7. 証明書生成
        certificate_response = client.post(
            "/functions/v1/course-management",
            headers=headers,
            json={
                "action": "certificate", 
                "courseId": course_id
            }
        )
        assert certificate_response.status_code == 200
        certificate = certificate_response.json()
        assert "certificate" in certificate
        
        # 8. 分析データ確認 (管理者権限必要)
        admin_token = await self._get_admin_token()
        admin_headers = {"Authorization": f"Bearer {admin_token}"}
        
        analytics_response = client.get(
            "/api/v1/analytics/course-performance",
            headers=admin_headers
        )
        assert analytics_response.status_code == 200
        analytics = analytics_response.json()
        
        # コース完了が分析に反映されているか確認
        course_analytics = next(
            (a for a in analytics if a["id"] == course_id), None
        )
        assert course_analytics is not None
        assert course_analytics["completions"] >= 1
    
    async def test_realtime_synchronization(self, client, authenticated_user):
        """リアルタイム同期テスト"""
        
        # WebSocket接続シミュレーション
        with patch('frontend.src.services.realtime_service.RealtimeService') as mock_realtime:
            mock_client = Mock()
            mock_realtime.return_value = mock_client
            
            # 進捗更新イベント発生
            token = await self._get_user_token(authenticated_user)
            headers = {"Authorization": f"Bearer {token}"}
            
            progress_response = client.post(
                "/functions/v1/course-management",
                headers=headers,
                json={
                    "action": "progress",
                    "courseId": "test-course-id",
                    "data": {"completionPercentage": 75}
                }
            )
            
            # リアルタイムイベントが発火されたか確認
            assert progress_response.status_code == 200
            
            # モックでイベント受信確認
            mock_client.send.assert_called()
            sent_payload = mock_client.send.call_args[0][0]
            assert sent_payload["event"] == "progress_update"
            assert sent_payload["payload"]["progress"]["completion_percentage"] == 75

# パフォーマンステスト
class TestPerformance:
    
    @pytest.mark.performance
    async def test_concurrent_user_load(self):
        """同時接続負荷テスト"""
        
        async def simulate_user_session():
            """ユーザーセッションシミュレーション"""
            # ログイン -> コース閲覧 -> 進捗更新のフロー
            # 実装省略
            pass
        
        # 100同時ユーザーのシミュレーション
        tasks = [simulate_user_session() for _ in range(100)]
        start_time = asyncio.get_event_loop().time()
        
        await asyncio.gather(*tasks)
        
        elapsed_time = asyncio.get_event_loop().time() - start_time
        
        # 性能要件: 100ユーザーセッション完了が30秒以内
        assert elapsed_time < 30.0
        
    @pytest.mark.performance
    async def test_database_query_performance(self, client):
        """データベースクエリ性能テスト"""
        
        admin_token = await self._get_admin_token()
        headers = {"Authorization": f"Bearer {admin_token}"}
        
        start_time = asyncio.get_event_loop().time()
        
        # 重い分析クエリの実行
        response = client.get(
            "/api/v1/analytics/course-performance",
            headers=headers
        )
        
        elapsed_time = asyncio.get_event_loop().time() - start_time
        
        assert response.status_code == 200
        # 性能要件: 分析クエリが2秒以内
        assert elapsed_time < 2.0