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 clear_existing_fake_data():
    """기존 가짜 데이터 삭제"""
    print("기존 가짜 데이터 삭제 중...")

    # fake_로 시작하는 device_serial의 플레이 답변 삭제
    cursor.execute("""
        DELETE pa FROM balancepick_play_answers pa
        INNER JOIN balancepick_game_plays gp ON pa.play_id = gp.id
        WHERE gp.device_serial LIKE 'fake_%'
    """)

    # fake_로 시작하는 플레이 기록 삭제
    cursor.execute("DELETE FROM balancepick_game_plays WHERE device_serial LIKE 'fake_%'")

    # fake_로 시작하는 좋아요 삭제
    cursor.execute("DELETE FROM balancepick_likes WHERE device_serial LIKE 'fake_%'")

    conn.commit()
    print("기존 가짜 데이터 삭제 완료!")

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, 60)))
    )

    # 각 질문에 대한 답변 생성 (다양한 비율로)
    for question in questions:
        # A 또는 B 중 하나 선택 (30~70% 사이 랜덤 가중치)
        weight_a = random.randint(30, 70)
        choice = 'A' if random.randint(1, 100) <= weight_a else 'B'
        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):
    """가짜 좋아요 생성"""
    created = 0
    for i in range(count):
        device_serial = f"fake_like_{game_id}_{i}_{uuid.uuid4().hex[:6]}"
        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, 60)))
            )
            created += 1
        except:
            pass  # 중복 무시
    return created

def main():
    print("=" * 60)
    print("Balance Pick 데이터 대량 조작 스크립트 v2")
    print("범위: 2,000 ~ 18,000")
    print("=" * 60)

    # 기존 가짜 데이터 삭제
    clear_existing_fake_data()

    games = get_all_games()
    print(f"\n총 {len(games)}개 게임 처리 시작...")

    for idx, game in enumerate(games):
        game_id = game['id']
        title = game['title']

        print(f"\n[{idx+1}/{len(games)}] {title}")

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

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

        # 랜덤 카운트 생성 (플레이 > 좋아요 > 공유 순서)
        play_count = random.randint(8000, 18000)  # 플레이가 가장 많음
        like_count = random.randint(2000, int(play_count * 0.6))  # 플레이의 최대 60%
        share_count = random.randint(500, int(like_count * 0.5))  # 좋아요의 최대 50%

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

        # 가짜 플레이 및 답변 생성 (100~200개 - 통계가 다양하게 나오도록)
        fake_plays_count = random.randint(100, 200)
        for i in range(fake_plays_count):
            device_serial = f"fake_play_{game_id}_{i}"
            create_fake_play_and_answers(game_id, questions, device_serial)

        print(f"  - 플레이 기록 {fake_plays_count}개 생성 (질문당 {fake_plays_count}개 답변)")

        # 가짜 좋아요 생성 (실제 like_count와 맞추기 위해 일부만)
        fake_likes = min(like_count, 500)  # 최대 500개만 실제 레코드 생성
        created_likes = create_fake_likes(game_id, fake_likes)
        print(f"  - 좋아요 레코드 {created_likes}개 생성")

        # 중간 커밋 (메모리 관리)
        if (idx + 1) % 5 == 0:
            conn.commit()
            print(f"  [중간 저장 완료]")

    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"""
    )
    results = cursor.fetchall()

    print(f"\n{'게임 제목':<25} | {'플레이':>7} | {'좋아요':>7} | {'공유':>7} | {'실제플레이':>7} | {'실제좋아요':>7}")
    print("-" * 90)
    for r in results:
        title_short = r['title'][:22] + "..." if len(r['title']) > 25 else r['title']
        print(f"{title_short:<25} | {r['play_count']:>7,} | {r['like_count']:>7,} | {r['share_count']:>7,} | {r['actual_plays']:>7,} | {r['actual_likes']:>7,}")

    # 질문별 통계 샘플 확인
    print("\n[질문별 A vs B 통계 샘플 (첫 번째 게임)]")
    first_game_id = results[0]['id'] if results else None
    if first_game_id:
        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 = %s
               GROUP BY q.id
               LIMIT 10""",
            (first_game_id,)
        )
        stats = cursor.fetchall()

        for s in stats:
            total = s['total'] or 1
            count_a = s['count_a'] or 0
            count_b = s['count_b'] or 0
            percent_a = round(count_a / total * 100)
            percent_b = round(count_b / total * 100)
            q_text = s['question_text'][:35] + "..." if s['question_text'] and len(s['question_text']) > 35 else (s['question_text'] or "")
            print(f"  {q_text:<38} | A: {percent_a:2}% ({count_a:3}) | B: {percent_b:2}% ({count_b:3})")

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