import os
import mysql.connector


OLD_HOST = "https://uscppbpkqffl28953595.gcdn.ntruss.com"
NEW_HOST = "https://o5pyylks14941.edge.naverncp.com"
DRY_RUN = os.environ.get("DRY_RUN", "").strip().lower() in {"1", "true", "yes", "y"}

DB_CONFIG = {
    "host": os.environ.get("DB_HOST", "officialsite.kr"),
    "port": int(os.environ.get("DB_PORT", "23306")),
    "user": os.environ.get("DB_USER", "admin"),
    "password": os.environ.get("DB_PASSWORD", "dlgks~123"),
    "database": os.environ.get("DB_NAME", "app_master"),
}

TEXT_TYPES = ("char", "varchar", "tinytext", "text", "mediumtext", "longtext")


def quote_identifier(name: str) -> str:
    return f"`{name.replace('`', '``')}`"


def fetch_babynote_columns(cursor):
    cursor.execute(
        """
        SELECT TABLE_NAME, COLUMN_NAME
          FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA = %s
           AND TABLE_NAME LIKE 'babynote\\_%%'
           AND DATA_TYPE IN (%s, %s, %s, %s, %s, %s)
         ORDER BY TABLE_NAME, ORDINAL_POSITION
        """,
        (DB_CONFIG["database"], *TEXT_TYPES),
    )
    return cursor.fetchall()


def count_matches(cursor, table_name: str, column_name: str) -> int:
    sql = (
        f"SELECT COUNT(*) FROM {quote_identifier(table_name)} "
        f"WHERE {quote_identifier(column_name)} LIKE %s"
    )
    cursor.execute(sql, (f"%{OLD_HOST}%",))
    return int(cursor.fetchone()[0] or 0)


def replace_matches(cursor, table_name: str, column_name: str) -> int:
    sql = (
        f"UPDATE {quote_identifier(table_name)} "
        f"SET {quote_identifier(column_name)} = REPLACE({quote_identifier(column_name)}, %s, %s) "
        f"WHERE {quote_identifier(column_name)} LIKE %s"
    )
    cursor.execute(sql, (OLD_HOST, NEW_HOST, f"%{OLD_HOST}%"))
    return cursor.rowcount


def update_shared_tables(cursor):
    updates = []

    cursor.execute(
        """
        SELECT COUNT(*)
          FROM users
         WHERE app_name = 'babynote'
           AND profile_image LIKE %s
        """,
        (f"%{OLD_HOST}%",),
    )
    user_count = int(cursor.fetchone()[0] or 0)
    if user_count:
        if not DRY_RUN:
            cursor.execute(
                """
                UPDATE users
                   SET profile_image = REPLACE(profile_image, %s, %s)
                 WHERE app_name = 'babynote'
                   AND profile_image LIKE %s
                """,
                (OLD_HOST, NEW_HOST, f"%{OLD_HOST}%"),
            )
        updates.append(("users.profile_image (app_name=babynote)", user_count))

    cursor.execute(
        """
        SELECT COUNT(*)
          FROM app_settings
         WHERE app_name = 'babynote'
           AND setting_value LIKE %s
        """,
        (f"%{OLD_HOST}%",),
    )
    setting_count = int(cursor.fetchone()[0] or 0)
    if setting_count:
        if not DRY_RUN:
            cursor.execute(
                """
                UPDATE app_settings
                   SET setting_value = REPLACE(setting_value, %s, %s)
                 WHERE app_name = 'babynote'
                   AND setting_value LIKE %s
                """,
                (OLD_HOST, NEW_HOST, f"%{OLD_HOST}%"),
            )
        updates.append(("app_settings.setting_value (app_name=babynote)", setting_count))

    return updates


def run():
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()
    changed = []

    try:
        print("DB connected")
        print(f"Target replace: {OLD_HOST} -> {NEW_HOST}")
        print(f"Mode: {'DRY_RUN' if DRY_RUN else 'APPLY'}")

        for table_name, column_name in fetch_babynote_columns(cursor):
            match_count = count_matches(cursor, table_name, column_name)
            if match_count <= 0:
                continue

            if not DRY_RUN:
                affected = replace_matches(cursor, table_name, column_name)
            else:
                affected = match_count

            changed.append((f"{table_name}.{column_name}", match_count, affected))
            print(f"{table_name}.{column_name}: matched={match_count}, affected={affected}")

        for label, count in update_shared_tables(cursor):
            changed.append((label, count, count))
            print(f"{label}: matched={count}, affected={count}")

        if DRY_RUN:
            conn.rollback()
            print("Dry run complete. Rolled back.")
        else:
            conn.commit()
            print("Migration committed.")

        print(f"Total updated targets: {len(changed)}")
    finally:
        cursor.close()
        conn.close()
        print("DB connection closed")


if __name__ == "__main__":
    run()