# مدیریت اتصال به دیتابیس MySQL - پروژه فرنود

import re
import aiomysql
import pymysql
from config import DB_CONFIG, DB_CONFIG_SYNC

pool = None

# ==================== async (ربات) ====================

async def init_db():
    global pool
    pool = await aiomysql.create_pool(**DB_CONFIG, minsize=3, maxsize=25, pool_recycle=300)
    await ensure_tables_async()
    print("✅ اتصال به دیتابیس با موفقیت برقرار شد")

async def ensure_tables_async():
    import warnings
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            # سرکوب هشدارهای بی‌ضرر MySQL (Integer display width / Table already exists)
            with warnings.catch_warnings():
                warnings.simplefilter("ignore")
                await cur.execute("""
                    CREATE TABLE IF NOT EXISTS settings (
                        `key` VARCHAR(100) PRIMARY KEY,
                        `value` TEXT,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
                await cur.execute("""
                    INSERT IGNORE INTO settings (`key`, `value`)
                    VALUES ('welcome_message', 'سلام! به ربات فرنود خوش آمدید 👋')
                """)
                # TINYINT بدون display width تا هشدار deprecated MySQL 8 ندهد
                await cur.execute("""
                    CREATE TABLE IF NOT EXISTS vpn_panels (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(100) NOT NULL,
                        slug VARCHAR(100) NOT NULL UNIQUE,
                        panel_type VARCHAR(30) NOT NULL DEFAULT 'pasarguard',
                        base_url VARCHAR(500) NOT NULL,
                        username VARCHAR(150) NOT NULL,
                        password VARCHAR(255) NOT NULL,
                        is_active TINYINT NOT NULL DEFAULT 1,
                        last_status VARCHAR(50) DEFAULT NULL,
                        last_check_at TIMESTAMP NULL DEFAULT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)

async def close_db():
    global pool
    if pool:
        pool.close()
        await pool.wait_closed()
        print("🔌 اتصال دیتابیس بسته شد")

async def get_setting(key: str, default: str = "") -> str:
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT `value` FROM settings WHERE `key` = %s LIMIT 1", (key,))
            row = await cur.fetchone()
            if row:
                return row[0] if row[0] is not None else default
            return default

async def set_setting(key: str, value: str):
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("""
                INSERT INTO settings (`key`, `value`) VALUES (%s, %s)
                ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)
            """, (key, value))

# ==================== sync (پنل وب) ====================

# --- pool اتصال sync (وب‌پنل + مسیرهای sync ربات) ---
_sync_pool = None
_sync_pool_lock = None

def _get_sync_pool_lock():
    global _sync_pool_lock
    if _sync_pool_lock is None:
        import threading
        _sync_pool_lock = threading.Lock()
    return _sync_pool_lock

def _init_sync_pool():
    """ساخت یک‌باره pool سبک برای pymysql — کاهش overhead باز/بسته شدن اتصال."""
    global _sync_pool
    if _sync_pool is not None:
        return _sync_pool
    with _get_sync_pool_lock():
        if _sync_pool is not None:
            return _sync_pool
        try:
            from dbutils.pooled_db import PooledDB
            cfg = DB_CONFIG_SYNC.copy()
            _sync_pool = PooledDB(
                creator=pymysql,
                maxconnections=20,
                mincached=2,
                maxcached=8,
                blocking=True,
                ping=1,
                cursorclass=pymysql.cursors.DictCursor,
                **cfg,
            )
        except Exception as e:
            print(f"sync pool unavailable ({e}); fallback to direct connect")
            _sync_pool = False  # mark failed so we don't retry every call
        return _sync_pool

def get_sync_connection():
    """اتصال sync: از pool اگر موجود باشد، وگرنه اتصال مستقیم."""
    pool = _init_sync_pool()
    if pool and pool is not False:
        try:
            return pool.connection()
        except Exception as e:
            print(f"sync pool get failed: {e}")
    config = DB_CONFIG_SYNC.copy()
    config["cursorclass"] = pymysql.cursors.DictCursor
    return pymysql.connect(**config)

def _hash_password(password: str) -> str:
    from werkzeug.security import generate_password_hash
    return generate_password_hash(password)


def _verify_password(stored: str, password: str) -> bool:
    """پشتیبانی از هش werkzeug و پسوردهای قدیمی plaintext (مهاجرت نرم)."""
    if not stored or not password:
        return False
    if stored.startswith(("pbkdf2:", "scrypt:", "argon2:")):
        from werkzeug.security import check_password_hash
        return check_password_hash(stored, password)
    # سازگاری با نصب‌های قدیمی که پسورد plain داشتند
    return stored == password


def check_admin(username: str, password: str):
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            cursor.execute("SELECT * FROM admins WHERE username = %s LIMIT 1", (username,))
            admin = cursor.fetchone()
            if not admin:
                return None
            stored = admin.get("password") or ""
            if _verify_password(stored, password):
                # اگر پسورد هنوز plain بود، به هش ارتقا بده
                if not stored.startswith(("pbkdf2:", "scrypt:", "argon2:")):
                    try:
                        cursor.execute(
                            "UPDATE admins SET password=%s WHERE id=%s",
                            (_hash_password(password), admin["id"]),
                        )
                        connection.commit()
                    except Exception:
                        pass
                return admin
            return None
    except Exception as e:
        print(f"❌ خطا در بررسی ادمین: {e}")
        return None
    finally:
        if connection:
            connection.close()


def set_admin_password(password: str, admin_id: int = None) -> bool:
    """تنظیم/تغییر رمز ادمین با هش امن."""
    password = (password or "").strip()
    if not password:
        return False
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            hashed = _hash_password(password)
            if admin_id:
                cursor.execute("UPDATE admins SET password=%s WHERE id=%s", (hashed, admin_id))
            else:
                # آپدیت امن‌تر با ORDER BY + LIMIT (سازگار با MySQL/MariaDB)
                cursor.execute("UPDATE admins SET password=%s ORDER BY id LIMIT 1", (hashed,))
            connection.commit()
            return cursor.rowcount > 0
    except Exception as e:
        print(f"❌ خطا در تغییر رمز ادمین: {e}")
        return False
    finally:
        if connection:
            connection.close()

# کش سبک تنظیمات sync — جلوگیری از باز/بسته کردن اتصال برای هر خواندن
_settings_sync_cache: dict = {}
_settings_sync_cache_ts: float = 0.0
_SETTINGS_CACHE_TTL = 15.0  # ثانیه

def _invalidate_runtime_caches(key: str = ""):
    """کش‌های سبک هندلرها را بعد از تغییر تنظیمات تازه می‌کند."""
    if key in {
        "menu_buttons_json", "menu_buttons_per_row", "inline_main_menu",
        "miniapp_url", "miniapp_btn_enabled", "miniapp_btn_label"
    }:
        try:
            from handlers import wallet as _wallet
            _wallet._MAIN_KEYBOARD_CACHE.clear()
        except Exception:
            pass



def get_settings_sync(keys, defaults=None) -> dict:
    """خواندن چند تنظیم با یک اتصال؛ از کش مشترک هم استفاده می‌کند."""
    import time
    global _settings_sync_cache, _settings_sync_cache_ts
    keys = list(keys or [])
    defaults = defaults or {}
    if not keys:
        return {}
    now = time.monotonic()
    result = {}
    missing = []
    if now - _settings_sync_cache_ts < _SETTINGS_CACHE_TTL:
        for k in keys:
            if k in _settings_sync_cache:
                val = _settings_sync_cache[k]
                result[k] = val if val is not None else defaults.get(k, "")
            else:
                missing.append(k)
    else:
        missing = list(keys)
    if not missing:
        return result
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            placeholders = ",".join(["%s"] * len(missing))
            cursor.execute(
                f"SELECT `key`, `value` FROM settings WHERE `key` IN ({placeholders})",
                tuple(missing),
            )
            rows = cursor.fetchall() or []
        found = {row["key"]: row.get("value") for row in rows}
        if now - _settings_sync_cache_ts >= _SETTINGS_CACHE_TTL:
            _settings_sync_cache = {}
            _settings_sync_cache_ts = now
        for k in missing:
            val = found.get(k)
            _settings_sync_cache[k] = val
            result[k] = val if val is not None else defaults.get(k, "")
        return result
    except Exception as e:
        print(f"❌ خطا در خواندن تنظیمات: {e}")
        for k in missing:
            result[k] = defaults.get(k, "")
        return result
    finally:
        if connection:
            connection.close()


def get_setting_sync(key: str, default: str = "") -> str:
    import time
    global _settings_sync_cache, _settings_sync_cache_ts
    now = time.monotonic()
    if now - _settings_sync_cache_ts < _SETTINGS_CACHE_TTL and key in _settings_sync_cache:
        val = _settings_sync_cache[key]
        return val if val is not None else default
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            cursor.execute("SELECT `value` FROM settings WHERE `key` = %s LIMIT 1", (key,))
            row = cursor.fetchone()
            if row and row.get("value") is not None:
                val = row["value"]
            else:
                val = None
            # به‌روزرسانی کش
            if now - _settings_sync_cache_ts >= _SETTINGS_CACHE_TTL:
                _settings_sync_cache = {}
                _settings_sync_cache_ts = now
            _settings_sync_cache[key] = val
            return val if val is not None else default
    except Exception as e:
        print(f"❌ خطا در خواندن تنظیم: {e}")
        return default
    finally:
        if connection:
            connection.close()


def set_setting_sync(key: str, value: str) -> bool:
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            cursor.execute("""
                INSERT INTO settings (`key`, `value`) VALUES (%s, %s)
                ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)
            """, (key, value))
            connection.commit()
            # باطل کردن کش برای این کلید
            try:
                _settings_sync_cache[key] = value
            except Exception:
                pass
            _invalidate_runtime_caches(key)
            return True
    except Exception as e:
        print(f"❌ خطا در ذخیره تنظیم: {e}")
        return False
    finally:
        if connection:
            connection.close()

def ensure_tables_sync():
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS settings (
                    `key` VARCHAR(100) PRIMARY KEY,
                    `value` TEXT,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            cursor.execute("""
                INSERT IGNORE INTO settings (`key`, `value`)
                VALUES ('welcome_message', 'سلام! به ربات فرنود خوش آمدید 👋')
            """)
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS vpn_panels (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(100) NOT NULL,
                    slug VARCHAR(100) NOT NULL UNIQUE,
                    panel_type VARCHAR(30) NOT NULL DEFAULT 'pasarguard',
                    base_url VARCHAR(500) NOT NULL,
                    username VARCHAR(150) NOT NULL,
                    password VARCHAR(255) NOT NULL,
                    is_active TINYINT NOT NULL DEFAULT 1,
                    last_status VARCHAR(50) DEFAULT NULL,
                    last_check_at TIMESTAMP NULL DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            connection.commit()
    except Exception as e:
        print(f"❌ خطا در ساخت جداول: {e}")
    finally:
        if connection:
            connection.close()

def slugify(text: str) -> str:
    text = (text or "").strip().lower()
    text = re.sub(r"[^\w\s-]", "", text, flags=re.UNICODE)
    text = re.sub(r"[\s_-]+", "-", text)
    text = text.strip("-")
    return text[:80] or "panel"

_panels_cache = {"ts": 0.0, "data": None}
_PANELS_CACHE_TTL = 5.0

def invalidate_panels_cache():
    _panels_cache["ts"] = 0.0
    _panels_cache["data"] = None

def list_panels():
    import time
    now = time.monotonic()
    if _panels_cache["data"] is not None and now - _panels_cache["ts"] < _PANELS_CACHE_TTL:
        return list(_panels_cache["data"])
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            cursor.execute("SELECT * FROM vpn_panels ORDER BY id DESC")
            rows = cursor.fetchall() or []
        _panels_cache["data"] = rows
        _panels_cache["ts"] = now
        return list(rows)
    except Exception as e:
        print(f"❌ list_panels: {e}")
        return list(_panels_cache["data"] or [])
    finally:
        if connection:
            connection.close()

def get_panel_by_id(panel_id: int):
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            cursor.execute("SELECT * FROM vpn_panels WHERE id = %s LIMIT 1", (panel_id,))
            return cursor.fetchone()
    except Exception as e:
        print(f"❌ get_panel_by_id: {e}")
        return None
    finally:
        if connection:
            connection.close()

def get_panel_by_slug(slug: str):
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            cursor.execute("SELECT * FROM vpn_panels WHERE slug = %s LIMIT 1", (slug,))
            return cursor.fetchone()
    except Exception as e:
        print(f"❌ get_panel_by_slug: {e}")
        return None
    finally:
        if connection:
            connection.close()

def create_panel(name: str, panel_type: str, base_url: str, username: str, password: str, slug: str = None, api_key: str = None):
    """Returns (panel_id, slug) or (None, error_message)."""
    try:
        ensure_panel_max_sales()
    except Exception as e:
        print(f"ensure_panel_max_sales: {e}")
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            final_slug = slug or slugify(name)
            base_slug = final_slug
            n = 1
            while True:
                cursor.execute("SELECT id FROM vpn_panels WHERE slug = %s LIMIT 1", (final_slug,))
                if not cursor.fetchone():
                    break
                n += 1
                final_slug = f"{base_slug}-{n}"

            # try with api_key column
            try:
                cursor.execute("""
                    INSERT INTO vpn_panels (name, slug, panel_type, base_url, username, password, api_key, is_active, last_status)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, 1, 'connected')
                """, (name, final_slug, panel_type, base_url, username or "", password or "", api_key or None))
            except Exception as col_err:
                # fallback without api_key (old schema)
                print(f"create_panel api_key insert failed, fallback: {col_err}")
                cursor.execute("""
                    INSERT INTO vpn_panels (name, slug, panel_type, base_url, username, password, is_active, last_status)
                    VALUES (%s, %s, %s, %s, %s, %s, 1, 'connected')
                """, (name, final_slug, panel_type, base_url, username or "", password or ""))
            connection.commit()
            pid = cursor.lastrowid
            # if api_key exists and we fell back, try update
            if api_key and pid:
                try:
                    cursor.execute("UPDATE vpn_panels SET api_key=%s WHERE id=%s", (api_key, pid))
                    connection.commit()
                except Exception:
                    pass
            try:
                invalidate_panels_cache()
            except Exception:
                pass
            return pid, final_slug
    except Exception as e:
        print(f"❌ create_panel: {e}")
        return None, str(e)
    finally:
        if connection:
            connection.close()

def update_panel_status(panel_id: int, status: str):
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            cursor.execute("""
                UPDATE vpn_panels SET last_status = %s, last_check_at = NOW() WHERE id = %s
            """, (status, panel_id))
            connection.commit()
    except Exception as e:
        print(f"❌ update_panel_status: {e}")
    finally:
        if connection:
            connection.close()

def delete_panel(panel_id: int) -> bool:
    connection = None
    try:
        connection = get_sync_connection()
        with connection.cursor() as cursor:
            cursor.execute("DELETE FROM vpn_panels WHERE id = %s", (panel_id,))
            connection.commit()
            try:
                invalidate_panels_cache()
            except Exception:
                pass
            return cursor.rowcount > 0
    except Exception as e:
        print(f"❌ delete_panel: {e}")
        return False
    finally:
        if connection:
            connection.close()


def ensure_panel_max_sales():
    conn = get_sync_connection()
    try:
        with conn.cursor() as cur:
            for col, ddl in [
                ("max_sales", "INT DEFAULT NULL"),
                ("renew_mode", "VARCHAR(32) NOT NULL DEFAULT 'reset_both'"),
                ("api_key", "VARCHAR(512) DEFAULT NULL"),
                ("emoji", "VARCHAR(32) DEFAULT NULL"),
                ("premium_emoji", "VARCHAR(64) DEFAULT NULL"),
                ("button_color", "VARCHAR(20) DEFAULT 'none'"),
            ]:
                try:
                    cur.execute(f"ALTER TABLE vpn_panels ADD COLUMN {col} {ddl}")
                except Exception:
                    pass
            conn.commit()
    finally:
        conn.close()


def set_panel_field(panel_id: int, field: str, value) -> bool:
    allowed = {"max_sales", "renew_mode", "name", "is_active", "emoji", "premium_emoji", "button_color"}
    if field not in allowed:
        return False
    conn = get_sync_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(f"UPDATE vpn_panels SET `{field}`=%s WHERE id=%s", (value, panel_id))
            conn.commit()
            try:
                invalidate_panels_cache()
            except Exception:
                pass
            # حتی اگر مقدار عوض نشده باشد (rowcount=0) موفقیت است
            return True
    except Exception as e:
        print(f"set_panel_field: {e}")
        return False
    finally:
        conn.close()

def set_panel_max_sales(panel_id: int, max_sales):
    conn = get_sync_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("UPDATE vpn_panels SET max_sales=%s WHERE id=%s", (max_sales, panel_id))
            conn.commit()
    finally:
        conn.close()


def format_entity_label(entity: dict, for_miniapp: bool = False) -> str:
    """
    نام نمایشی پنل/دسته/محصول با ایموجی.
    رنگ دکمه فقط از طریق style تلگرام اعمال می‌شود — بدون 🔵🟢🔴 در متن.
    for_miniapp=True → فقط ایموجی عادی (پریمیوم در مینی‌اپ نیست)
    for_miniapp=False → اولویت با ایموجی پریمیوم (کد p_ یا شناسه)
    """
    if not entity:
        return ""
    name = (entity.get("name") or "").strip()
    emoji = (entity.get("emoji") or "").strip()
    prem = (entity.get("premium_emoji") or "").strip()
    if for_miniapp:
        if emoji:
            return f"{emoji} {name}".strip()
        return name
    if prem:
        return f"{prem} {name}".strip()
    if emoji:
        return f"{emoji} {name}".strip()
    return name


def inline_button_from_entity(entity: dict, callback_data: str, max_len: int = 64):
    """ساخت InlineKeyboardButton با پشتیبانی ایموجی پریمیوم و رنگ (style) بدون ایموجی رنگی در متن."""
    from telegram import InlineKeyboardButton
    label = format_entity_label(entity, for_miniapp=False)
    text = label
    eid = None
    try:
        from db_extras import extract_premium_from_label
        text, eid = extract_premium_from_label(label)
    except Exception:
        prem = (entity.get("premium_emoji") or "").strip()
        if prem.isdigit():
            eid = prem
            text = (entity.get("name") or "").strip() or "•"
        else:
            text = format_entity_label(entity, for_miniapp=False)
    text = (text or "•")[:max_len]
    color = (entity.get("button_color") or entity.get("color") or "none").strip().lower()
    style_map = {
        "blue": "primary", "primary": "primary",
        "green": "success", "success": "success",
        "red": "danger", "danger": "danger",
    }
    style = style_map.get(color)
    kwargs = {"text": text, "callback_data": callback_data}
    if eid:
        kwargs["icon_custom_emoji_id"] = str(eid)
    if style:
        kwargs["style"] = style
    try:
        return InlineKeyboardButton(**kwargs)
    except TypeError:
        kwargs.pop("style", None)
        try:
            return InlineKeyboardButton(**kwargs)
        except TypeError:
            return InlineKeyboardButton(text, callback_data=callback_data)


def payment_method_button(method: dict, callback_data: str, max_len: int = 64):
    """InlineKeyboardButton for card/variza (or any payment method) with optional premium emoji code in title."""
    from telegram import InlineKeyboardButton
    label = (method.get("title") or method.get("method_key") or "روش پرداخت")
    text = label
    eid = None
    try:
        from db_extras import extract_premium_from_label
        text, eid = extract_premium_from_label(label)
    except Exception:
        text = label
    text = (text or "روش پرداخت")[:max_len]
    kwargs = {"text": text, "callback_data": callback_data}
    if eid:
        kwargs["icon_custom_emoji_id"] = str(eid)
    try:
        return InlineKeyboardButton(**kwargs)
    except TypeError:
        return InlineKeyboardButton(text, callback_data=callback_data)