from __future__ import annotations
import json
import sqlite3
import threading
from contextlib import contextmanager
from datetime import datetime
from typing import Any, Generator

import config

_local = threading.local()


def _get_conn() -> sqlite3.Connection:
    if not hasattr(_local, "conn") or _local.conn is None:
        _local.conn = sqlite3.connect(config.DB_PATH, check_same_thread=False)
        _local.conn.row_factory = sqlite3.Row
        _local.conn.execute("PRAGMA journal_mode=WAL")
        _local.conn.execute("PRAGMA foreign_keys=ON")
    return _local.conn


@contextmanager
def db() -> Generator[sqlite3.Connection, None, None]:
    conn = _get_conn()
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise


def _fix_orders_product_id_nullable(conn: sqlite3.Connection) -> None:
    try:
        cols = conn.execute("PRAGMA table_info(orders)").fetchall()
        pid_col = next((c for c in cols if c["name"] == "product_id"), None)
        if pid_col is None or pid_col["notnull"] == 0:
            return

        conn.executescript("""
            CREATE TABLE IF NOT EXISTS orders_new (
                order_id     INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id      INTEGER NOT NULL REFERENCES users(user_id),
                product_id   INTEGER REFERENCES products(product_id),
                product_name_snapshot TEXT,
                amount_irt   INTEGER NOT NULL,
                quantity     INTEGER NOT NULL DEFAULT 1,
                status       TEXT NOT NULL DEFAULT 'pending',
                delivery     TEXT,
                created_at   TEXT NOT NULL DEFAULT (datetime('now','localtime')),
                updated_at   TEXT NOT NULL DEFAULT (datetime('now','localtime'))
            );

            INSERT INTO orders_new
                (order_id, user_id, product_id, product_name_snapshot,
                 amount_irt, quantity, status, delivery, created_at, updated_at)
            SELECT
                order_id, user_id, product_id, product_name_snapshot,
                amount_irt, 1 as quantity, status, delivery, created_at, updated_at
            FROM orders;

            DROP TABLE orders;
            ALTER TABLE orders_new RENAME TO orders;
        """)
    except Exception as e:
        import logging as _log
        _log.getLogger(__name__).warning("[migration] fix_orders_nullable: %s", e)

def _safe_alter(conn: sqlite3.Connection, sql: str) -> None:
    try:
        conn.execute(sql)
    except Exception:
        pass


def init_db() -> None:
    with db() as conn:
        conn.executescript("""
        CREATE TABLE IF NOT EXISTS users (
            user_id        INTEGER PRIMARY KEY,
            username       TEXT,
            full_name      TEXT,
            balance        INTEGER NOT NULL DEFAULT 0,
            level          INTEGER NOT NULL DEFAULT 0,
            total_spent    INTEGER NOT NULL DEFAULT 0,
            referrer_id    INTEGER,
            is_verified    INTEGER NOT NULL DEFAULT 0,
            is_banned      INTEGER NOT NULL DEFAULT 0,
            joined_at      TEXT NOT NULL DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS categories (
            cat_id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name        TEXT NOT NULL UNIQUE,
            emoji       TEXT NOT NULL DEFAULT '📦',
            is_active   INTEGER NOT NULL DEFAULT 1,
            created_at  TEXT NOT NULL DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS products (
            product_id   INTEGER PRIMARY KEY AUTOINCREMENT,
            cat_id       INTEGER NOT NULL REFERENCES categories(cat_id),
            name         TEXT NOT NULL,
            description  TEXT NOT NULL DEFAULT '',
            price_usd    REAL NOT NULL,
            stock        INTEGER NOT NULL DEFAULT -1,
            is_active    INTEGER NOT NULL DEFAULT 1,
            created_at   TEXT NOT NULL DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS orders (
            order_id     INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id      INTEGER NOT NULL REFERENCES users(user_id),
            product_id   INTEGER REFERENCES products(product_id),
            product_name_snapshot TEXT,
            amount_irt   INTEGER NOT NULL,
            quantity     INTEGER NOT NULL DEFAULT 1,
            status       TEXT NOT NULL DEFAULT 'pending',
            delivery     TEXT,
            created_at   TEXT NOT NULL DEFAULT (datetime('now','localtime')),
            updated_at   TEXT NOT NULL DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS deposits (
            deposit_id   INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id      INTEGER NOT NULL REFERENCES users(user_id),
            method       TEXT NOT NULL,
            amount_irt   INTEGER NOT NULL,
            tx_ref       TEXT,
            status       TEXT NOT NULL DEFAULT 'pending',
            created_at   TEXT NOT NULL DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS settings (
            key   TEXT PRIMARY KEY,
            value TEXT NOT NULL
        );

        INSERT OR IGNORE INTO settings VALUES ('card_number',   '');
        INSERT OR IGNORE INTO settings VALUES ('card_holder',   '');
        INSERT OR IGNORE INTO settings VALUES ('wallet_usdt',   '');
        INSERT OR IGNORE INTO settings VALUES ('wallet_trx',    '');
        INSERT OR IGNORE INTO settings VALUES ('wallet_ton',    '');
        INSERT OR IGNORE INTO settings VALUES ('wallet_btc',    '');
        INSERT OR IGNORE INTO settings VALUES ('active_crypto', 'usdt');
        INSERT OR IGNORE INTO settings VALUES ('forced_channel','');
        INSERT OR IGNORE INTO settings VALUES ('fee_percent',   '0');
        INSERT OR IGNORE INTO settings VALUES ('kyc_threshold', '350000');
        INSERT OR IGNORE INTO settings VALUES ('premium_fee_irt','50000');
        INSERT OR IGNORE INTO settings VALUES ('stars_fee_percent','5');

        CREATE TABLE IF NOT EXISTS kyc_requests (
            kyc_id       INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id      INTEGER NOT NULL REFERENCES users(user_id),
            photo_file_id TEXT NOT NULL,
            card_number  TEXT NOT NULL DEFAULT '',
            card_last4   TEXT NOT NULL DEFAULT '',
            status       TEXT NOT NULL DEFAULT 'pending',
            created_at   TEXT NOT NULL DEFAULT (datetime('now','localtime')),
            reviewed_at  TEXT
        );

        CREATE TABLE IF NOT EXISTS discount_codes (
            code_id     INTEGER PRIMARY KEY AUTOINCREMENT,
            code        TEXT NOT NULL UNIQUE,
            type        TEXT NOT NULL DEFAULT 'percent',
            value       INTEGER NOT NULL DEFAULT 0,
            max_uses    INTEGER NOT NULL DEFAULT 0,
            used_count  INTEGER NOT NULL DEFAULT 0,
            expires_at  TEXT,
            is_active   INTEGER NOT NULL DEFAULT 1,
            created_at  TEXT NOT NULL DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS bot_admins (
            admin_id    INTEGER PRIMARY KEY,
            added_by    INTEGER,
            added_at    TEXT NOT NULL DEFAULT (datetime('now','localtime')),
            notify_registration INTEGER NOT NULL DEFAULT 1,
            notify_kyc          INTEGER NOT NULL DEFAULT 1,
            notify_deposit      INTEGER NOT NULL DEFAULT 1,
            notify_support      INTEGER NOT NULL DEFAULT 1,
            notify_order        INTEGER NOT NULL DEFAULT 1
        );
        """)
        _safe_alter(conn, "ALTER TABLE kyc_requests ADD COLUMN card_last4 TEXT NOT NULL DEFAULT ''")
        _safe_alter(conn, "ALTER TABLE users ADD COLUMN referrer_reward_paid INTEGER NOT NULL DEFAULT 0")
        _safe_alter(conn, "ALTER TABLE orders ADD COLUMN product_name_snapshot TEXT")
        _safe_alter(conn, "ALTER TABLE orders ADD COLUMN quantity INTEGER NOT NULL DEFAULT 1")

        _fix_orders_product_id_nullable(conn)
        for _k, _v in (
            ("deposit_network_usdt", ""),
            ("deposit_network_trx", ""),
            ("deposit_network_ton", ""),
            ("deposit_network_btc", ""),
            ("section_shop",         "1"),  
            ("section_topup",        "1"),  
            ("section_topup_card",   "1"),  
            ("section_topup_crypto", "1"), 
            ("section_topup_gateway","1"), 
            ("section_support",      "1"), 
            ("card_kyc_always",      "1"),
            ("wallet_usdt_bep20",    ""),
            ("wallet_usdt_trc20",    ""),
            ("wallet_trx_trc20",     ""),
            ("gateway_title",        "پرداخت درگاهی"),
            ("gateway_instructions", "پس از پرداخت، تصویر رسید را ارسال کنید."),
        ):
            conn.execute("INSERT OR IGNORE INTO settings VALUES (?, ?)", (_k, _v))

        for col in (
            "notify_registration", "notify_kyc", "notify_deposit",
            "notify_support", "notify_order",
        ):
            _safe_alter(conn, f"ALTER TABLE bot_admins ADD COLUMN {col} INTEGER NOT NULL DEFAULT 1")

        old_usdt = conn.execute(
            "SELECT value FROM settings WHERE key='wallet_usdt'"
        ).fetchone()
        trc20 = conn.execute(
            "SELECT value FROM settings WHERE key='wallet_usdt_trc20'"
        ).fetchone()
        if old_usdt and old_usdt["value"] and (not trc20 or not trc20["value"]):
            conn.execute(
                "INSERT OR REPLACE INTO settings (key, value) VALUES ('wallet_usdt_trc20', ?)",
                (old_usdt["value"],),
            )
        old_trx = conn.execute(
            "SELECT value FROM settings WHERE key='wallet_trx'"
        ).fetchone()
        trx_new = conn.execute(
            "SELECT value FROM settings WHERE key='wallet_trx_trc20'"
        ).fetchone()
        if old_trx and old_trx["value"] and (not trx_new or not trx_new["value"]):
            conn.execute(
                "INSERT OR REPLACE INTO settings (key, value) VALUES ('wallet_trx_trc20', ?)",
                (old_trx["value"],),
            )


def upsert_user(user_id: int, username: str | None, full_name: str,
                referrer_id: int | None = None) -> sqlite3.Row:
    with db() as conn:
        conn.execute("""
            INSERT INTO users (user_id, username, full_name, referrer_id)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(user_id) DO UPDATE SET
                username  = excluded.username,
                full_name = excluded.full_name
        """, (user_id, username, full_name, referrer_id))
    return get_user(user_id)


def get_user(user_id: int) -> sqlite3.Row | None:
    with db() as conn:
        return conn.execute("SELECT * FROM users WHERE user_id=?", (user_id,)).fetchone()


def update_balance(user_id: int, delta: int) -> int:
    with db() as conn:
        conn.execute(
            "UPDATE users SET balance = balance + ? WHERE user_id=?", (delta, user_id)
        )
        row = conn.execute("SELECT balance FROM users WHERE user_id=?", (user_id,)).fetchone()
        return row["balance"]


def update_level(user_id: int) -> int:
    user = get_user(user_id)
    if not user:
        return 0
    spent = user["total_spent"]
    new_level = 0
    for lvl, info in config.LEVEL_THRESHOLDS.items():
        if info["max"] is None or spent < info["max"]:
            new_level = lvl
            break
        new_level = lvl
    with db() as conn:
        conn.execute("UPDATE users SET level=? WHERE user_id=?", (new_level, user_id))
    return new_level


def get_referral_count(user_id: int) -> int:
    with db() as conn:
        row = conn.execute(
            "SELECT COUNT(*) as c FROM users WHERE referrer_id=?", (user_id,)
        ).fetchone()
        return row["c"]


def claim_referrer_bonus_for_referee(referee_id: int) -> int | None:
    with db() as conn:
        cur = conn.execute(
            "UPDATE users SET referrer_reward_paid=1 "
            "WHERE user_id=? AND referrer_reward_paid=0 AND referrer_id IS NOT NULL "
            "RETURNING referrer_id",
            (referee_id,),
        )
        row = cur.fetchone()
        return int(row["referrer_id"]) if row else None


def get_all_user_ids() -> list[int]:
    with db() as conn:
        rows = conn.execute("SELECT user_id FROM users WHERE is_banned=0").fetchall()
        return [r["user_id"] for r in rows]


def get_all_users(limit: int = 50, offset: int = 0) -> list[sqlite3.Row]:
    with db() as conn:
        return conn.execute(
            "SELECT * FROM users ORDER BY joined_at DESC LIMIT ? OFFSET ?",
            (limit, offset)
        ).fetchall()


def get_users_count() -> int:
    with db() as conn:
        return conn.execute("SELECT COUNT(*) as c FROM users").fetchone()["c"]


def ban_user(user_id: int) -> None:
    with db() as conn:
        conn.execute("UPDATE users SET is_banned=1 WHERE user_id=?", (user_id,))


def unban_user(user_id: int) -> None:
    with db() as conn:
        conn.execute("UPDATE users SET is_banned=0 WHERE user_id=?", (user_id,))


def search_user(query: str) -> list[sqlite3.Row]:
    with db() as conn:
        if query.lstrip("-").isdigit():
            rows = conn.execute(
                "SELECT * FROM users WHERE user_id=?", (int(query),)
            ).fetchall()
            if rows:
                return rows
        like = f"%{query.lstrip('@')}%"
        return conn.execute(
            "SELECT * FROM users WHERE username LIKE ? OR full_name LIKE ? LIMIT 10",
            (like, like)
        ).fetchall()


def is_section_active(section: str) -> bool:
    val = get_setting(f"section_{section}")
    return val != "0"


def toggle_section(section: str) -> bool:
    current = is_section_active(section)
    set_setting(f"section_{section}", "0" if current else "1")
    return not current

def add_category(name: str, emoji: str = "📦") -> int:
    with db() as conn:
        cur = conn.execute(
            "INSERT INTO categories (name, emoji) VALUES (?, ?)", (name, emoji)
        )
        return cur.lastrowid


def get_categories(active_only: bool = True) -> list[sqlite3.Row]:
    with db() as conn:
        q = "SELECT * FROM categories"
        if active_only:
            q += " WHERE is_active=1"
        q += " ORDER BY cat_id"
        return conn.execute(q).fetchall()


def get_category(cat_id: int) -> sqlite3.Row | None:
    with db() as conn:
        return conn.execute(
            "SELECT * FROM categories WHERE cat_id=?", (cat_id,)
        ).fetchone()


def delete_category(cat_id: int) -> int:
    conn = _get_conn()
    try:
        conn.execute("PRAGMA foreign_keys = OFF")
        products = conn.execute(
            "SELECT product_id, name FROM products WHERE cat_id=?", (cat_id,)
        ).fetchall()
        for prod in products:
            conn.execute("""
                UPDATE orders
                SET product_id = NULL,
                    product_name_snapshot = COALESCE(
                        CASE WHEN (product_name_snapshot IS NULL OR product_name_snapshot = '')
                             THEN ? ELSE product_name_snapshot END,
                        ?
                    )
                WHERE product_id = ?
            """, (prod["name"], prod["name"], prod["product_id"]))
        cur = conn.execute("DELETE FROM products WHERE cat_id=?", (cat_id,))
        deleted_products = cur.rowcount
        conn.execute("DELETE FROM categories WHERE cat_id=?", (cat_id,))
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.execute("PRAGMA foreign_keys = ON")
    return deleted_products


def delete_product(product_id: int) -> None:
    conn = _get_conn()
    try:
        conn.execute("PRAGMA foreign_keys = OFF")
        prod = conn.execute(
            "SELECT name FROM products WHERE product_id=?", (product_id,)
        ).fetchone()
        prod_name = prod["name"] if prod else ""
        conn.execute("""
            UPDATE orders
            SET product_id = NULL,
                product_name_snapshot = COALESCE(
                    CASE WHEN (product_name_snapshot IS NULL OR product_name_snapshot = '')
                         THEN ? ELSE product_name_snapshot END,
                    ?
                )
            WHERE product_id=?
        """, (prod_name, prod_name, product_id))
        conn.execute("DELETE FROM products WHERE product_id=?", (product_id,))
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.execute("PRAGMA foreign_keys = ON")


def purge_soft_deleted() -> dict:
    conn = _get_conn()
    try:
        conn.execute("PRAGMA foreign_keys = OFF")

        inactive_cats = conn.execute(
            "SELECT cat_id FROM categories WHERE is_active=0"
        ).fetchall()
        cat_ids = [r["cat_id"] for r in inactive_cats]

        products_to_delete = []
        for cat_id in cat_ids:
            rows = conn.execute(
                "SELECT product_id, name FROM products WHERE cat_id=?", (cat_id,)
            ).fetchall()
            products_to_delete.extend(rows)
        rows_inactive = conn.execute(
            "SELECT product_id, name FROM products WHERE is_active=0"
        ).fetchall()
        products_to_delete.extend(rows_inactive)

        seen_ids: set[int] = set()
        unique_products = []
        for r in products_to_delete:
            if r["product_id"] not in seen_ids:
                seen_ids.add(r["product_id"])
                unique_products.append(r)

        for prod in unique_products:
            conn.execute("""
                UPDATE orders
                SET product_id = NULL,
                    product_name_snapshot = COALESCE(
                        CASE WHEN (product_name_snapshot IS NULL OR product_name_snapshot = '')
                             THEN ? ELSE product_name_snapshot END,
                        ?
                    )
                WHERE product_id = ?
            """, (prod["name"], prod["name"], prod["product_id"]))

        deleted_prods = 0

        for cat_id in cat_ids:
            cur = conn.execute("DELETE FROM products WHERE cat_id=?", (cat_id,))
            deleted_prods += cur.rowcount

        conn.execute("DELETE FROM categories WHERE is_active=0")

        cur2 = conn.execute("DELETE FROM products WHERE is_active=0")
        deleted_prods += cur2.rowcount

        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.execute("PRAGMA foreign_keys = ON")
    return {"deleted_cats": len(cat_ids), "deleted_products": deleted_prods}

def add_product(cat_id: int, name: str, description: str, price_usd: float,
                stock: int = -1) -> int:
    with db() as conn:
        cur = conn.execute("""
            INSERT INTO products (cat_id, name, description, price_usd, stock)
            VALUES (?, ?, ?, ?, ?)
        """, (cat_id, name, description, price_usd, stock))
        return cur.lastrowid


def get_products_by_category(cat_id: int) -> list[sqlite3.Row]:
    with db() as conn:
        return conn.execute(
            "SELECT * FROM products WHERE cat_id=? AND is_active=1 ORDER BY product_id",
            (cat_id,)
        ).fetchall()


def get_product(product_id: int) -> sqlite3.Row | None:
    with db() as conn:
        return conn.execute(
            "SELECT * FROM products WHERE product_id=?", (product_id,)
        ).fetchone()

def create_order(user_id: int, product_id: int, amount_irt: int,
                 product_name: str = "", quantity: int = 1) -> int:
    with db() as conn:
        cur = conn.execute("""
            INSERT INTO orders (user_id, product_id, amount_irt, product_name_snapshot, quantity)
            VALUES (?, ?, ?, ?, ?)
        """, (user_id, product_id, amount_irt, product_name, max(1, int(quantity))))
        return cur.lastrowid


def get_order(order_id: int) -> sqlite3.Row | None:
    with db() as conn:
        return conn.execute("SELECT * FROM orders WHERE order_id=?", (order_id,)).fetchone()


def get_user_orders(user_id: int) -> list[sqlite3.Row]:
    with db() as conn:
        return conn.execute(
            "SELECT * FROM orders WHERE user_id=? ORDER BY created_at DESC",
            (user_id,)
        ).fetchall()


def get_pending_orders(limit: int = 30) -> list[sqlite3.Row]:
    with db() as conn:
        return conn.execute(
            """SELECT o.*, u.full_name, u.username
               FROM orders o
               LEFT JOIN users u ON o.user_id = u.user_id
               WHERE o.status IN ('pending_review', 'pending')
               ORDER BY o.created_at ASC
               LIMIT ?""",
            (limit,),
        ).fetchall()


def update_order_status(order_id: int, status: str, delivery: str | None = None) -> None:
    with db() as conn:
        conn.execute("""
            UPDATE orders SET status=?, delivery=?, updated_at=datetime('now','localtime')
            WHERE order_id=?
        """, (status, delivery, order_id))

def create_deposit(user_id: int, method: str, amount_irt: int, tx_ref: str = "") -> int:
    with db() as conn:
        cur = conn.execute("""
            INSERT INTO deposits (user_id, method, amount_irt, tx_ref)
            VALUES (?, ?, ?, ?)
        """, (user_id, method, amount_irt, tx_ref))
        return cur.lastrowid


def get_deposit(deposit_id: int) -> sqlite3.Row | None:
    with db() as conn:
        return conn.execute(
            "SELECT * FROM deposits WHERE deposit_id=?", (deposit_id,)
        ).fetchone()


def confirm_deposit(deposit_id: int) -> None:
    with db() as conn:
        dep = conn.execute(
            "SELECT * FROM deposits WHERE deposit_id=?", (deposit_id,)
        ).fetchone()
        if dep and dep["status"] == "pending":
            conn.execute(
                "UPDATE deposits SET status='confirmed' WHERE deposit_id=?", (deposit_id,)
            )
            conn.execute(
                "UPDATE users SET balance=balance+? WHERE user_id=?",
                (dep["amount_irt"], dep["user_id"])
            )

def get_setting(key: str) -> str:
    with db() as conn:
        row = conn.execute("SELECT value FROM settings WHERE key=?", (key,)).fetchone()
        return row["value"] if row else ""


def set_setting(key: str, value: str) -> None:
    with db() as conn:
        conn.execute(
            "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)", (key, value)
        )

def create_kyc_request(user_id: int, photo_file_id: str, card_number: str = "") -> int:
    with db() as conn:
        cur = conn.execute("""
            INSERT INTO kyc_requests (user_id, photo_file_id, card_number)
            VALUES (?, ?, ?)
        """, (user_id, photo_file_id, card_number))
        return cur.lastrowid


def get_kyc_request(kyc_id: int) -> sqlite3.Row | None:
    with db() as conn:
        return conn.execute(
            "SELECT * FROM kyc_requests WHERE kyc_id=?", (kyc_id,)
        ).fetchone()


def get_pending_kyc_by_user(user_id: int) -> sqlite3.Row | None:
    with db() as conn:
        return conn.execute(
            "SELECT * FROM kyc_requests WHERE user_id=? AND status='pending' ORDER BY kyc_id DESC LIMIT 1",
            (user_id,)
        ).fetchone()


def get_approved_kyc_card(user_id: int) -> str | None:
    with db() as conn:
        row = conn.execute(
            "SELECT card_number FROM kyc_requests WHERE user_id=? AND status='approved' ORDER BY kyc_id DESC LIMIT 1",
            (user_id,)
        ).fetchone()
        return row["card_number"] if row else None


def approve_kyc(kyc_id: int) -> None:
    with db() as conn:
        req = conn.execute("SELECT user_id FROM kyc_requests WHERE kyc_id=?", (kyc_id,)).fetchone()
        if req:
            conn.execute("""
                UPDATE kyc_requests SET status='approved', reviewed_at=datetime('now','localtime')
                WHERE kyc_id=?
            """, (kyc_id,))
            conn.execute("UPDATE users SET is_verified=1 WHERE user_id=?", (req["user_id"],))


def reject_kyc(kyc_id: int) -> None:
    with db() as conn:
        conn.execute("""
            UPDATE kyc_requests SET status='rejected', reviewed_at=datetime('now','localtime')
            WHERE kyc_id=?
        """, (kyc_id,))

def get_stats() -> dict[str, Any]:
    with db() as conn:
        users       = conn.execute("SELECT COUNT(*) as c FROM users").fetchone()["c"]
        banned      = conn.execute("SELECT COUNT(*) as c FROM users WHERE is_banned=1").fetchone()["c"]
        verified    = conn.execute("SELECT COUNT(*) as c FROM users WHERE is_verified=1").fetchone()["c"]
        pending_kyc = conn.execute(
            "SELECT COUNT(*) as c FROM kyc_requests WHERE status='pending'"
        ).fetchone()["c"]
        orders      = conn.execute("SELECT COUNT(*) as c FROM orders").fetchone()["c"]
        paid        = conn.execute("SELECT COUNT(*) as c FROM orders WHERE status='delivered'").fetchone()["c"]
        revenue     = conn.execute(
            "SELECT COALESCE(SUM(amount_irt),0) as s FROM orders WHERE status='delivered'"
        ).fetchone()["s"]
        products    = conn.execute("SELECT COUNT(*) as c FROM products WHERE is_active=1").fetchone()["c"]
        cats        = conn.execute("SELECT COUNT(*) as c FROM categories WHERE is_active=1").fetchone()["c"]
    return {
        "users": users, "banned": banned, "verified": verified,
        "pending_kyc": pending_kyc,
        "orders": orders, "paid": paid,
        "revenue": revenue, "products": products, "categories": cats,
    }

def get_bot_admins_from_db() -> list[sqlite3.Row]:
    with db() as conn:
        return conn.execute("""
            SELECT b.admin_id, b.added_by, b.added_at,
                   u.username, u.full_name
            FROM bot_admins b
            LEFT JOIN users u ON b.admin_id = u.user_id
            ORDER BY b.added_at
        """).fetchall()


def get_all_admins() -> list[int]:
    db_ids = [r["admin_id"] for r in get_bot_admins_from_db()]
    return list(set(config.ADMIN_IDS + db_ids))


def add_admin(admin_id: int, added_by: int) -> bool:
    try:
        with db() as conn:
            conn.execute(
                "INSERT INTO bot_admins (admin_id, added_by) VALUES (?, ?)",
                (admin_id, added_by)
            )
        return True
    except sqlite3.IntegrityError:
        return False


def remove_admin(admin_id: int) -> bool:
    if admin_id in config.ADMIN_IDS:
        return False
    with db() as conn:
        conn.execute("DELETE FROM bot_admins WHERE admin_id=?", (admin_id,))
    return True


NOTIF_KEYS = (
    "notify_registration", "notify_kyc", "notify_deposit",
    "notify_support", "notify_order",
)


def get_admin_notification_prefs(admin_id: int) -> dict[str, bool]:
    if admin_id in config.ADMIN_IDS:
        return {k: True for k in NOTIF_KEYS}
    with db() as conn:
        row = conn.execute(
            f"SELECT {', '.join(NOTIF_KEYS)} FROM bot_admins WHERE admin_id=?",
            (admin_id,),
        ).fetchone()
    if not row:
        return {k: False for k in NOTIF_KEYS}
    return {k: bool(row[k]) for k in NOTIF_KEYS}


def set_admin_notification(admin_id: int, key: str, enabled: bool) -> bool:
    if key not in NOTIF_KEYS or admin_id in config.ADMIN_IDS:
        return False
    with db() as conn:
        conn.execute(
            f"UPDATE bot_admins SET {key}=? WHERE admin_id=?",
            (1 if enabled else 0, admin_id),
        )
    return True


def get_admins_for_notification(notif_key: str) -> list[int]:
    if notif_key not in NOTIF_KEYS:
        return []
    result: list[int] = list(config.ADMIN_IDS)
    with db() as conn:
        rows = conn.execute(
            f"SELECT admin_id, {notif_key} FROM bot_admins"
        ).fetchall()
    for r in rows:
        if r[notif_key] and r["admin_id"] not in result:
            result.append(r["admin_id"])
    return result


def encode_checkout_ref(checkout: dict) -> str:
    return "checkout:" + json.dumps(checkout, ensure_ascii=False)


def decode_checkout_ref(tx_ref: str) -> dict | None:
    if not tx_ref or not str(tx_ref).startswith("checkout:"):
        return None
    try:
        return json.loads(str(tx_ref)[9:])
    except (json.JSONDecodeError, TypeError):
        return None

def create_discount_code(
    code: str,
    disc_type: str,
    value: int,
    max_uses: int = 0,
    expires_at: str | None = None,
) -> int:
    with db() as conn:
        cur = conn.execute("""
            INSERT INTO discount_codes (code, type, value, max_uses, expires_at)
            VALUES (?, ?, ?, ?, ?)
        """, (code.upper(), disc_type, value, max_uses, expires_at))
        return cur.lastrowid


def get_all_discount_codes() -> list[sqlite3.Row]:
    with db() as conn:
        return conn.execute(
            "SELECT * FROM discount_codes ORDER BY code_id DESC"
        ).fetchall()


def validate_discount(code: str, user_id: int = 0, amount_irt: int = 0) -> tuple[int, str]:
    with db() as conn:
        row = conn.execute(
            "SELECT * FROM discount_codes WHERE code=? AND is_active=1",
            (code.upper().strip(),)
        ).fetchone()

    if not row:
        return 0, "❌ کد تخفیف نامعتبر یا غیرفعال است."

    if row["expires_at"]:
        try:
            exp = datetime.strptime(str(row["expires_at"]), "%Y-%m-%d %H:%M:%S")
            if datetime.now() > exp:
                return 0, "❌ کد تخفیف منقضی شده است."
        except ValueError:
            pass

    if row["max_uses"] > 0 and row["used_count"] >= row["max_uses"]:
        return 0, "❌ ظرفیت استفاده از این کد تمام شده است."

    if row["type"] == "percent":
        discount = int(amount_irt * row["value"] / 100) if amount_irt > 0 else row["value"]
    else:
        discount = row["value"]

    if discount <= 0:
        return 0, "❌ مقدار تخفیف معتبر نیست."

    return discount, ""


def use_discount_code(code: str, user_id: int = 0,
                      order_id: int = 0, discount_irt: int = 0) -> bool:
    with db() as conn:
        cur = conn.execute(
            "UPDATE discount_codes SET used_count = used_count + 1 WHERE code=?",
            (code.upper().strip(),)
        )
        return cur.rowcount > 0


def toggle_discount_code(code_id: int) -> bool:
    with db() as conn:
        row = conn.execute(
            "SELECT is_active FROM discount_codes WHERE code_id=?", (code_id,)
        ).fetchone()
        if not row:
            return False
        new_val = 0 if row["is_active"] else 1
        conn.execute(
            "UPDATE discount_codes SET is_active=? WHERE code_id=?",
            (new_val, code_id)
        )
        return bool(new_val)


def delete_discount_code(code_id: int) -> None:
    with db() as conn:
        conn.execute("DELETE FROM discount_codes WHERE code_id=?", (code_id,))