import pymysql
import os
from dotenv import load_dotenv

# .env 파일 로드
load_dotenv()

# 데이터베이스 연결 설정
connection = pymysql.connect(
    host=os.getenv('DB_HOST'),
    user=os.getenv('DB_USER'),
    password=os.getenv('DB_PASSWORD'),
    database=os.getenv('DB_NAME'),
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with connection.cursor() as cursor:
        # FCM 토큰 테이블 존재 여부 확인
        cursor.execute("SHOW TABLES LIKE 'fcm_tokens'")
        table_exists = cursor.fetchone()

        if table_exists:
            print("===== FCM 토큰 테이블 구조 =====")
            cursor.execute("DESCRIBE fcm_tokens")
            columns = cursor.fetchall()
            for col in columns:
                print(f"{col['Field']}: {col['Type']} (Null: {col['Null']}, Default: {col['Default']})")

            print("\n===== FCM 토큰 통계 =====")
            cursor.execute("SELECT COUNT(*) as total FROM fcm_tokens")
            total = cursor.fetchone()
            print(f"전체 FCM 토큰 수: {total['total']}")

            cursor.execute("SELECT COUNT(*) as total FROM fcm_tokens WHERE is_active = 1")
            active = cursor.fetchone()
            print(f"활성 FCM 토큰 수: {active['total']}")

            print("\n===== 사용자별 FCM 토큰 =====")
            cursor.execute("""
                SELECT
                    u.id,
                    u.name,
                    u.email,
                    u.app_name,
                    COUNT(ft.id) as token_count,
                    SUM(CASE WHEN ft.is_active = 1 THEN 1 ELSE 0 END) as active_token_count
                FROM users u
                LEFT JOIN fcm_tokens ft ON u.id = ft.user_id
                WHERE u.app_name = 'babynote'
                GROUP BY u.id
                ORDER BY token_count DESC
                LIMIT 10
            """)
            users = cursor.fetchall()
            for user in users:
                print(f"사용자: {user['name']} ({user['email']}) - 토큰: {user['token_count']}, 활성: {user['active_token_count']}")

            print("\n===== 최근 FCM 토큰 샘플 (최대 5개) =====")
            cursor.execute("""
                SELECT
                    ft.id,
                    ft.user_id,
                    u.name as user_name,
                    u.email,
                    ft.device_id,
                    ft.platform,
                    ft.is_active,
                    ft.created_at,
                    ft.updated_at,
                    SUBSTRING(ft.fcm_token, 1, 30) as token_preview
                FROM fcm_tokens ft
                JOIN users u ON ft.user_id = u.id
                WHERE u.app_name = 'babynote'
                ORDER BY ft.created_at DESC
                LIMIT 5
            """)
            tokens = cursor.fetchall()
            if tokens:
                for token in tokens:
                    print(f"\nID: {token['id']}")
                    print(f"사용자: {token['user_name']} ({token['email']})")
                    print(f"디바이스: {token['device_id']} ({token['platform']})")
                    print(f"활성: {token['is_active']}")
                    print(f"토큰 프리뷰: {token['token_preview']}...")
                    print(f"생성: {token['created_at']}, 업데이트: {token['updated_at']}")
            else:
                print("등록된 FCM 토큰이 없습니다.")

        else:
            print("❌ FCM 토큰 테이블이 존재하지 않습니다!")
            print("데이터베이스 마이그레이션이 필요할 수 있습니다.")

finally:
    connection.close()