import logging
import os
from pathlib import Path

import mysql.connector
from dotenv import load_dotenv

# database.py の場所を起点に .env を明示的に指定（Apache経由で呼ばれても必ず見つかる）
load_dotenv(dotenv_path=Path(__file__).resolve().parents[2] / '.env')

logger = logging.getLogger(__name__)


def get_connection() -> mysql.connector.MySQLConnection:
    """カゴヤDB（管理系）への接続を返す。
    用途: clients / media_settings / tool_settings /
         monitor_settings / notices / error_logs 等
    """
    try:
        conn = mysql.connector.connect(
            host=os.getenv("DB_HOST", "localhost"),
            port=int(os.getenv("DB_PORT", 3306)),
            database=os.getenv("DB_NAME"),
            user=os.getenv("DB_USER"),
            password=os.getenv("DB_PASSWORD"),
            charset="utf8mb4",
            collation="utf8mb4_unicode_ci",
        )
        return conn
    except mysql.connector.Error as e:
        logger.error("カゴヤDB接続エラー: %s", e)
        raise


def get_local_connection() -> mysql.connector.MySQLConnection:
    """VPS内DB（データ系）への接続を返す。
    用途: scraping_results / analysis_results /
         analysis_suggestions / update_histories
    """
    try:
        conn = mysql.connector.connect(
            host=os.getenv("DB_LOCAL_HOST", "localhost"),
            port=int(os.getenv("DB_LOCAL_PORT", 3306)),
            database=os.getenv("DB_LOCAL_NAME"),
            user=os.getenv("DB_LOCAL_USER"),
            password=os.getenv("DB_LOCAL_PASSWORD"),
            charset="utf8mb4",
            collation="utf8mb4_unicode_ci",
        )
        return conn
    except mysql.connector.Error as e:
        logger.error("VPS内DB接続エラー: %s", e)
        raise


def execute_query(sql: str, params: tuple = (), local: bool = False) -> list[dict]:
    """SELECTクエリを実行して結果を辞書のリストで返す。

    Args:
        sql: 実行するSQLクエリ
        params: バインドパラメータ
        local: Trueの場合はVPS内DB（データ系）に接続
    """
    conn = None
    try:
        conn = get_local_connection() if local else get_connection()
        with conn.cursor(dictionary=True) as cursor:
            cursor.execute(sql, params)
            return cursor.fetchall()
    except mysql.connector.Error as e:
        logger.error("クエリ実行エラー: %s | SQL: %s | params: %s", e, sql, params)
        raise
    finally:
        if conn and conn.is_connected():
            conn.close()


def execute_update(sql: str, params: tuple = (), local: bool = False) -> int:
    """INSERT / UPDATE / DELETE を実行して影響行数を返す。

    Args:
        sql: 実行するSQLクエリ
        params: バインドパラメータ
        local: Trueの場合はVPS内DB（データ系）に接続
    """
    conn = None
    try:
        conn = get_local_connection() if local else get_connection()
        with conn.cursor() as cursor:
            cursor.execute(sql, params)
            conn.commit()
            return cursor.rowcount
    except mysql.connector.Error as e:
        if conn and conn.is_connected():
            conn.rollback()
        logger.error("更新実行エラー: %s | SQL: %s | params: %s", e, sql, params)
        raise
    finally:
        if conn and conn.is_connected():
            conn.close()