import mysql.connector
import os
from dotenv import load_dotenv

load_dotenv()

# DB 연결 정보
config = {
    'host': os.getenv('DB_HOST', 'localhost'),
    'user': os.getenv('DB_USER', 'root'),
    'password': os.getenv('DB_PASSWORD', ''),
    'database': os.getenv('DB_NAME', 'app_master'),
    'charset': 'utf8mb4'
}

# 변경할 이메일 목록 (old -> new)
OLD_EMAILS = [
    'brosister.dev@gmail.com',
    'support@brosister.com',
    'contact@brosister.com',
    'help@brosister.com',
    'info@brosister.com',
]
NEW_EMAIL = 'admin@officialsite.kr'

def migrate():
    try:
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()

        print("DB connected")

        # 1. app_policies 테이블에서 이메일 변경
        print("\n[1] Updating app_policies table...")
        for old_email in OLD_EMAILS:
            cursor.execute("""
                UPDATE app_policies
                SET content = REPLACE(content, %s, %s)
                WHERE content LIKE %s
            """, (old_email, NEW_EMAIL, f'%{old_email}%'))
            if cursor.rowcount > 0:
                print(f"  - Replaced '{old_email}' -> '{NEW_EMAIL}' in {cursor.rowcount} rows")

        # 모든 gmail.com 이메일도 변경 (회사 관련)
        cursor.execute("""
            UPDATE app_policies
            SET content = REPLACE(content, 'brosister.dev@gmail.com', %s)
            WHERE content LIKE '%brosister.dev@gmail.com%'
        """, (NEW_EMAIL,))
        if cursor.rowcount > 0:
            print(f"  - Replaced 'brosister.dev@gmail.com' -> '{NEW_EMAIL}' in {cursor.rowcount} rows")

        conn.commit()

        # 2. 변경 결과 확인
        print("\n[2] Checking remaining old emails...")
        cursor.execute("""
            SELECT id, app_name, policy_type, language_code
            FROM app_policies
            WHERE content LIKE '%@gmail.com%'
               OR content LIKE '%@brosister%'
        """)
        remaining = cursor.fetchall()
        if remaining:
            print(f"  WARNING: {len(remaining)} rows still contain old emails:")
            for row in remaining:
                print(f"    - ID: {row[0]}, App: {row[1]}, Type: {row[2]}, Lang: {row[3]}")
        else:
            print("  All emails updated successfully!")

        # 3. 현재 약관에 있는 이메일 목록 확인
        print("\n[3] Current emails in policies:")
        cursor.execute("""
            SELECT DISTINCT
                SUBSTRING_INDEX(SUBSTRING_INDEX(content, '@', -1), ' ', 1) as domain,
                COUNT(*) as count
            FROM app_policies
            WHERE content LIKE '%@%'
            GROUP BY domain
        """)

        cursor.close()
        conn.close()
        print("\n[OK] Migration complete!")

    except mysql.connector.Error as err:
        print(f"[ERROR] DB: {err}")
    except Exception as e:
        print(f"[ERROR]: {e}")

if __name__ == "__main__":
    migrate()