import os
import sys

import mysql.connector
from dotenv import load_dotenv


def main():
    load_dotenv()

    conn = mysql.connector.connect(
        host=os.getenv("DB_HOST"),
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        port=int(os.getenv("DB_PORT", "3306")),
        database=os.getenv("DB_NAME"),
        charset="utf8mb4",
    )

    try:
        cursor = conn.cursor()

        cursor.execute(
            """
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'babynote_insight_comments'
              AND COLUMN_NAME = 'parent_comment_id'
            """
        )
        if cursor.fetchone() is None:
            cursor.execute(
                """
                ALTER TABLE babynote_insight_comments
                ADD COLUMN parent_comment_id VARCHAR(36) DEFAULT NULL
                COMMENT '부모 댓글 ID (답글인 경우)' AFTER user_id
                """
            )
            print("Added parent_comment_id column to babynote_insight_comments")
        else:
            print("parent_comment_id column already exists")

        cursor.execute(
            """
            SELECT INDEX_NAME
            FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'babynote_insight_comments'
              AND INDEX_NAME = 'idx_parent_comment_id'
            """
        )
        if cursor.fetchone() is None:
            cursor.execute(
                """
                ALTER TABLE babynote_insight_comments
                ADD INDEX idx_parent_comment_id (parent_comment_id)
                """
            )
            print("Added idx_parent_comment_id index to babynote_insight_comments")
        else:
            print("idx_parent_comment_id index already exists")

        conn.commit()
        print("Migration completed successfully")
    finally:
        conn.close()


if __name__ == "__main__":
    try:
        main()
    except Exception as exc:
        print(f"Migration failed: {exc}")
        sys.exit(1)