import os
import logging
import aiomysql

logger = logging.getLogger(__name__)

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"),
    "db": os.environ.get("DB_NAME", "autotrading"),
    "charset": "utf8mb4",
    "autocommit": True,
}

_pool: aiomysql.Pool | None = None


async def init_db():
    """커넥션 풀 생성 + 테이블 초기화"""
    global _pool
    _pool = await aiomysql.create_pool(
        minsize=1, maxsize=3, **DB_CONFIG
    )

    async with _pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("""
                CREATE TABLE IF NOT EXISTS trading_session (
                    id INT PRIMARY KEY DEFAULT 1,
                    running TINYINT NOT NULL DEFAULT 0,
                    total_budget DOUBLE NOT NULL DEFAULT 0,
                    compound TINYINT NOT NULL DEFAULT 0,
                    total_realized DOUBLE NOT NULL DEFAULT 0,
                    total_trades INT NOT NULL DEFAULT 0,
                    started_at VARCHAR(30),
                    paper_mode TINYINT NULL DEFAULT NULL,
                    virtual_krw DOUBLE NULL DEFAULT NULL,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )
            """)
            for column_sql in (
                "ADD COLUMN paper_mode TINYINT NULL DEFAULT NULL",
                "ADD COLUMN virtual_krw DOUBLE NULL DEFAULT NULL",
            ):
                try:
                    await cur.execute(f"ALTER TABLE trading_session {column_sql}")
                except Exception as e:
                    err = str(e).lower()
                    if "duplicate" not in err and "exists" not in err:
                        raise
            await cur.execute("""
                CREATE TABLE IF NOT EXISTS positions (
                    ticker VARCHAR(20) PRIMARY KEY,
                    coin VARCHAR(10) NOT NULL,
                    status VARCHAR(10) NOT NULL,
                    buy_price DOUBLE,
                    buy_volume DOUBLE,
                    bought_at VARCHAR(20),
                    target_profit DOUBLE NOT NULL,
                    stop_loss DOUBLE NOT NULL,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )
            """)
            await cur.execute("""
                CREATE TABLE IF NOT EXISTS alerts (
                    id INT PRIMARY KEY,
                    chat_id BIGINT NOT NULL,
                    symbol VARCHAR(20) NOT NULL,
                    target_price DOUBLE NOT NULL,
                    direction VARCHAR(10) NOT NULL,
                    price_unit VARCHAR(10) NOT NULL DEFAULT 'USD'
                )
            """)
            try:
                await cur.execute("""
                    ALTER TABLE alerts
                    ADD COLUMN price_unit VARCHAR(10) NOT NULL DEFAULT 'USD'
                """)
            except Exception as e:
                err = str(e).lower()
                if "duplicate" not in err and "exists" not in err:
                    raise
            await cur.execute("""
                CREATE TABLE IF NOT EXISTS bot_state (
                    name VARCHAR(50) PRIMARY KEY,
                    value VARCHAR(255) NOT NULL,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )
            """)
            await cur.execute("""
                CREATE TABLE IF NOT EXISTS app_event_deliveries (
                    event_key VARCHAR(191) PRIMARY KEY,
                    event_type VARCHAR(30) NOT NULL,
                    app_key VARCHAR(80) NOT NULL,
                    event_time DATETIME NULL,
                    delivered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_app_event_delivered_at (delivered_at)
                )
            """)
            await cur.execute("""
                DELETE FROM app_event_deliveries
                WHERE delivered_at < NOW() - INTERVAL 90 DAY
            """)
            await cur.execute("""
                CREATE TABLE IF NOT EXISTS notification_subscribers (
                    chat_id BIGINT PRIMARY KEY,
                    display_name VARCHAR(100) NOT NULL DEFAULT '',
                    username VARCHAR(100),
                    subscribed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                )
            """)
            await cur.execute("""
                CREATE TABLE IF NOT EXISTS notification_invites (
                    token_hash CHAR(64) PRIMARY KEY,
                    created_by BIGINT NOT NULL,
                    expires_at DATETIME NOT NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_notification_invites_expires_at (expires_at)
                )
            """)
            await cur.execute("""
                DELETE FROM notification_invites WHERE expires_at < NOW()
            """)
    logger.info("DB initialized")


async def close_db():
    global _pool
    if _pool:
        _pool.close()
        await _pool.wait_closed()
        _pool = None


async def save_session(trader) -> None:
    """트레이딩 세션 저장 (upsert)"""
    if not _pool:
        return
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("""
                    INSERT INTO trading_session (
                        id, running, total_budget, compound, total_realized,
                        total_trades, started_at, paper_mode, virtual_krw
                    )
                    VALUES (1, %s, %s, %s, %s, %s, %s, %s, %s)
                    ON DUPLICATE KEY UPDATE
                        running=%s, total_budget=%s, compound=%s,
                        total_realized=%s, total_trades=%s, started_at=%s,
                        paper_mode=%s, virtual_krw=%s
                """, (
                    int(trader.running), trader.total_budget, int(trader.compound),
                    trader.total_realized, trader.total_trades, trader.started_at,
                    int(trader.paper_mode), trader._virtual_krw,
                    int(trader.running), trader.total_budget, int(trader.compound),
                    trader.total_realized, trader.total_trades, trader.started_at,
                    int(trader.paper_mode), trader._virtual_krw,
                ))
    except Exception as e:
        logger.error(f"save_session error: {e}")


async def save_position(pos: dict) -> None:
    if not _pool:
        return
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("""
                    INSERT INTO positions (ticker, coin, status, buy_price, buy_volume, bought_at, target_profit, stop_loss)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                    ON DUPLICATE KEY UPDATE
                        status=%s, buy_price=%s, buy_volume=%s, bought_at=%s,
                        target_profit=%s, stop_loss=%s
                """, (
                    pos["ticker"], pos["coin"], pos["status"],
                    pos.get("buy_price"), pos.get("buy_volume"), pos.get("bought_at"),
                    pos["target_profit"], pos["stop_loss"],
                    pos["status"], pos.get("buy_price"), pos.get("buy_volume"),
                    pos.get("bought_at"), pos["target_profit"], pos["stop_loss"],
                ))
    except Exception as e:
        logger.error(f"save_position error: {e}")


async def delete_position(ticker: str) -> None:
    if not _pool:
        return
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("DELETE FROM positions WHERE ticker = %s", (ticker,))
    except Exception as e:
        logger.error(f"delete_position error: {e}")


async def clear_positions() -> None:
    if not _pool:
        return
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("DELETE FROM positions")
    except Exception as e:
        logger.error(f"clear_positions error: {e}")


async def load_session() -> dict | None:
    if not _pool:
        return None
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute("SELECT * FROM trading_session WHERE id = 1 AND running = 1")
                return await cur.fetchone()
    except Exception as e:
        logger.error(f"load_session error: {e}")
        return None


async def load_positions() -> list[dict]:
    if not _pool:
        return []
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute("SELECT * FROM positions")
                return await cur.fetchall()
    except Exception as e:
        logger.error(f"load_positions error: {e}")
        return []


async def save_alerts(alerts: dict) -> None:
    if not _pool:
        return
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("DELETE FROM alerts")
                for chat_id, alert_list in alerts.items():
                    for a in alert_list:
                        await cur.execute(
                            "INSERT INTO alerts (id, chat_id, symbol, target_price, direction, price_unit) VALUES (%s,%s,%s,%s,%s,%s)",
                            (
                                a["id"],
                                chat_id,
                                a["symbol"],
                                a["target_price"],
                                a["direction"],
                                a.get("price_unit", "USD"),
                            )
                        )
    except Exception as e:
        logger.error(f"save_alerts error: {e}")


async def load_alerts() -> tuple[dict, int]:
    """Returns (alerts_dict, max_alert_id)"""
    if not _pool:
        return {}, 0
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute("SELECT * FROM alerts")
                rows = await cur.fetchall()
        alerts = {}
        max_id = 0
        for r in rows:
            chat_id = r["chat_id"]
            alerts.setdefault(chat_id, []).append({
                "id": r["id"], "symbol": r["symbol"],
                "target_price": r["target_price"],
                "direction": r["direction"],
                "price_unit": r.get("price_unit") or "USD",
            })
            max_id = max(max_id, r["id"])
        return alerts, max_id
    except Exception as e:
        logger.error(f"load_alerts error: {e}")
        return {}, 0


async def save_bot_state(name: str, value: str) -> None:
    if not _pool:
        return
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("""
                    INSERT INTO bot_state (name, value)
                    VALUES (%s, %s)
                    ON DUPLICATE KEY UPDATE value = %s
                """, (name, value, value))
    except Exception as e:
        logger.error(f"save_bot_state error [{name}]: {e}")


async def load_bot_state(name: str) -> str | None:
    if not _pool:
        return None
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute("SELECT value FROM bot_state WHERE name = %s", (name,))
                row = await cur.fetchone()
                if row:
                    return row["value"]
                return None
    except Exception as e:
        logger.error(f"load_bot_state error [{name}]: {e}")
        return None


async def create_notification_invite(token_hash: str, created_by: str) -> None:
    if not _pool:
        raise RuntimeError("DB is not initialized")
    async with _pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "DELETE FROM notification_invites WHERE created_by = %s OR expires_at < NOW()",
                (created_by,),
            )
            await cur.execute(
                """
                INSERT INTO notification_invites (token_hash, created_by, expires_at)
                VALUES (%s, %s, DATE_ADD(NOW(), INTERVAL 15 MINUTE))
                """,
                (token_hash, created_by),
            )


async def consume_notification_invite(token_hash: str) -> bool:
    if not _pool:
        return False
    async with _pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                """
                DELETE FROM notification_invites
                WHERE token_hash = %s AND expires_at >= NOW()
                """,
                (token_hash,),
            )
            return cur.rowcount > 0


async def save_notification_subscriber(
    chat_id: str,
    display_name: str,
    username: str | None,
) -> None:
    if not _pool:
        raise RuntimeError("DB is not initialized")
    async with _pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                """
                INSERT INTO notification_subscribers (chat_id, display_name, username)
                VALUES (%s, %s, %s)
                ON DUPLICATE KEY UPDATE display_name = %s, username = %s
                """,
                (chat_id, display_name, username, display_name, username),
            )


async def load_notification_subscribers() -> list[dict]:
    if not _pool:
        return []
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute(
                    """
                    SELECT chat_id, display_name, username, subscribed_at
                    FROM notification_subscribers
                    ORDER BY subscribed_at
                    """
                )
                return await cur.fetchall()
    except Exception as e:
        logger.error(f"load_notification_subscribers error: {e}")
        return []


async def delete_notification_subscriber(chat_id: str) -> bool:
    if not _pool:
        return False
    async with _pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "DELETE FROM notification_subscribers WHERE chat_id = %s",
                (chat_id,),
            )
            return cur.rowcount > 0


async def has_app_event_delivery(event_key: str) -> bool:
    if not _pool:
        return False
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(
                    "SELECT 1 FROM app_event_deliveries WHERE event_key = %s LIMIT 1",
                    (event_key,),
                )
                return await cur.fetchone() is not None
    except Exception as e:
        logger.error(f"has_app_event_delivery error [{event_key}]: {e}")
        raise


async def mark_app_event_delivered(event: dict) -> None:
    if not _pool:
        return
    try:
        async with _pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(
                    """
                    INSERT IGNORE INTO app_event_deliveries
                      (event_key, event_type, app_key, event_time)
                    VALUES (%s, %s, %s, %s)
                    """,
                    (
                        str(event.get("event_key") or "")[:191],
                        str(event.get("event_type") or "unknown")[:30],
                        str(event.get("app_key") or "unknown")[:80],
                        event.get("event_time"),
                    ),
                )
    except Exception as e:
        logger.error(
            "mark_app_event_delivered error [%s]: %s",
            event.get("event_key"),
            e,
        )
        raise