import pymysql
from datetime import datetime, timedelta
import random
import uuid

# DB 연결 설정
conn = pymysql.connect(
    host='officialsite.kr',
    port=23306,
    user='admin',
    password='dlgks~123',
    database='app_master',
    charset='utf8mb4'
)

cursor = conn.cursor(pymysql.cursors.DictCursor)

def get_all_games():
    """모든 게임 조회"""
    cursor.execute("SELECT id, title, play_count, like_count, share_count FROM balancepick_games")
    return cursor.fetchall()

def get_questions_for_game(game_id):
    """게임의 질문 목록 조회"""
    cursor.execute(
        "SELECT id, question_text FROM balancepick_questions WHERE game_id = %s ORDER BY order_index",
        (game_id,)
    )
    return cursor.fetchall()

def update_game_counts(game_id, play_count, like_count, share_count):
    """게임 카운트 업데이트"""
    cursor.execute(
        """UPDATE balancepick_games
           SET play_count = %s, like_count = %s, share_count = %s
           WHERE id = %s""",
        (play_count, like_count, share_count, game_id)
    )

def create_fake_play_and_answers(game_id, questions, device_serial):
    """가짜 플레이 기록과 답변 생성"""
    play_id = str(uuid.uuid4())

    # 플레이 기록 생성
    cursor.execute(
        """INSERT INTO balancepick_game_plays (id, game_id, device_serial, total_questions, created_at)
           VALUES (%s, %s, %s, %s, %s)""",
        (play_id, game_id, device_serial, len(questions), datetime.now() - timedelta(days=random.randint(0, 30)))
    )

    # 각 질문에 대한 답변 생성 (비율을 다양하게)
    for question in questions:
        # A 또는 B 중 하나 선택 (가중치 적용)
        choice = random.choices(['A', 'B'], weights=[random.randint(30, 70), random.randint(30, 70)])[0]
        time_taken = random.randint(1, 3)

        cursor.execute(
            """INSERT INTO balancepick_play_answers (id, play_id, question_id, selected_choice, time_taken)
               VALUES (%s, %s, %s, %s, %s)""",
            (str(uuid.uuid4()), play_id, question['id'], choice, time_taken)
        )

    return play_id

def create_fake_likes(game_id, count):
    """가짜 좋아요 생성"""
    for i in range(count):
        device_serial = f"fake_device_{i}_{uuid.uuid4().hex[:8]}"
        try:
            cursor.execute(
                """INSERT INTO balancepick_likes (id, game_id, device_serial, created_at)
                   VALUES (%s, %s, %s, %s)""",
                (str(uuid.uuid4()), game_id, device_serial, datetime.now() - timedelta(days=random.randint(0, 30)))
            )
        except:
            pass  # 중복 무시

def main():
    print("=" * 60)
    print("Balance Pick 데이터 조작 스크립트")
    print("=" * 60)

    games = get_all_games()
    print(f"\n총 {len(games)}개 게임 발견")

    for game in games[:10]:  # 최대 10개 게임만 처리
        game_id = game['id']
        title = game['title']

        print(f"\n게임: {title} (ID: {game_id})")

        questions = get_questions_for_game(game_id)
        if not questions:
            print("  - 질문이 없음, 스킵")
            continue

        print(f"  - 질문 수: {len(questions)}")

        # 랜덤 카운트 생성
        play_count = random.randint(50, 500)
        like_count = random.randint(10, 100)
        share_count = random.randint(5, 50)

        # 게임 카운트 업데이트
        update_game_counts(game_id, play_count, like_count, share_count)
        print(f"  - 카운트 업데이트: 플레이={play_count}, 좋아요={like_count}, 공유={share_count}")

        # 가짜 플레이 및 답변 생성 (20~50개)
        fake_plays_count = random.randint(20, 50)
        for i in range(fake_plays_count):
            device_serial = f"fake_user_{i}_{uuid.uuid4().hex[:8]}"
            create_fake_play_and_answers(game_id, questions, device_serial)

        print(f"  - 가짜 플레이 기록 {fake_plays_count}개 생성")

        # 가짜 좋아요 생성
        create_fake_likes(game_id, like_count)
        print(f"  - 가짜 좋아요 {like_count}개 생성")

    conn.commit()
    print("\n" + "=" * 60)
    print("데이터 조작 완료!")
    print("=" * 60)

    # 결과 확인
    print("\n[결과 확인]")
    cursor.execute(
        """SELECT g.id, g.title, g.play_count, g.like_count, g.share_count,
                  (SELECT COUNT(*) FROM balancepick_game_plays WHERE game_id = g.id) as actual_plays,
                  (SELECT COUNT(*) FROM balancepick_likes WHERE game_id = g.id) as actual_likes
           FROM balancepick_games g
           LIMIT 10"""
    )
    results = cursor.fetchall()

    for r in results:
        print(f"  {r['title'][:20]:20} | 플레이: {r['play_count']:3} (실제: {r['actual_plays']:3}) | 좋아요: {r['like_count']:3} (실제: {r['actual_likes']:3}) | 공유: {r['share_count']:3}")

    # 통계 샘플 확인
    print("\n[질문별 통계 샘플]")
    cursor.execute(
        """SELECT q.question_text,
                  SUM(CASE WHEN a.selected_choice = 'A' THEN 1 ELSE 0 END) as count_a,
                  SUM(CASE WHEN a.selected_choice = 'B' THEN 1 ELSE 0 END) as count_b,
                  COUNT(*) as total
           FROM balancepick_questions q
           LEFT JOIN balancepick_play_answers a ON q.id = a.question_id
           WHERE q.game_id = (SELECT id FROM balancepick_games LIMIT 1)
           GROUP BY q.id
           LIMIT 5"""
    )
    stats = cursor.fetchall()

    for s in stats:
        total = s['total'] or 1
        percent_a = round((s['count_a'] or 0) / total * 100)
        percent_b = round((s['count_b'] or 0) / total * 100)
        print(f"  {s['question_text'][:30]:30} | A: {percent_a:2}% | B: {percent_b:2}%")

if __name__ == "__main__":
    try:
        main()
    except Exception as e:
        print(f"오류 발생: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()