import re

# Read DB config
with open('/home2/haeyulhome/blog-haeyul-co-kr/db/db.php', 'r') as f:
    db_php = f.read()

# Extract db credentials
host = re.search(r'\$host\s*=\s*['"]([^'"]+)['"]', db_php).group(1)
user = re.search(r'\$user\s*=\s*['"]([^'"]+)['"]', db_php).group(1)
passwd = re.search(r'\$passwd\s*=\s*['"]([^'"]+)['"]', db_php).group(1)
dataname = re.search(r'\$dataname\s*=\s*['"]([^'"]+)['"]', db_php).group(1)

print(f"Connecting to DB {dataname} at {host} with user {user}...")

import mysql.connector

db = mysql.connector.connect(
    host=host,
    user=user,
    password=passwd,
    database=dataname
)
cursor = db.cursor()

# Find all tables
cursor.execute("SHOW TABLES")
tables = [t[0] for t in cursor.fetchall()]

total_replaced = 0
for table in tables:
    # Find columns in table
    cursor.execute(f"SHOW COLUMNS FROM `{table}`")
    columns = [c[0] for c in cursor.fetchall() if 'varchar' in c[1].lower() or 'text' in c[1].lower() or 'mediumtext' in c[1].lower() or 'longtext' in c[1].lower()]
    
    for col in columns:
        # Check if contains http://haeyul.co.kr/ko_finder/
        query = f"SELECT COUNT(*) FROM `{table}` WHERE `{col}` LIKE '%http://haeyul.co.kr/ko_finder/%'"
        try:
            cursor.execute(query)
            count = cursor.fetchone()[0]
            if count > 0:
                print(f"Table {table}, Column {col}: {count} rows found with http://haeyul.co.kr/ko_finder/")
                update_q = f"UPDATE `{table}` SET `{col}` = REPLACE(`{col}`, 'http://haeyul.co.kr/ko_finder/', '/ko_finder/') WHERE `{col}` LIKE '%http://haeyul.co.kr/ko_finder/%'"
                cursor.execute(update_q)
                db.commit()
                print(f"Updated {cursor.rowcount} rows in {table}.{col}")
                total_replaced += cursor.rowcount
        except Exception as e:
            pass

print(f"TOTAL REPLACED: {total_replaced}")

cursor.close()
db.close()