# -*- coding: utf-8 -*-
"""
لایه‌ی دیتابیس (SQLite) برای ربات فروش پرمیوم تلگرام و استارز.
همان الگوهای wg_bot: تراکنش‌های اتمیک با WHERE status=... ، یونیک روی receipt برای
جلوگیری از رسید تکراری، و جدول settings برای مقادیر قابل‌تغییر از پنل ادمین (ضرایب قیمت).
"""

import json
import sqlite3
import time
from contextlib import contextmanager

import config

DB_PATH = config.DB_PATH


@contextmanager
def get_conn():
    conn = sqlite3.connect(DB_PATH, timeout=15)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def init_db():
    with get_conn() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS users (
                user_id     INTEGER PRIMARY KEY,
                username    TEXT,
                full_name   TEXT,
                created_at  INTEGER NOT NULL
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                id                    INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id               INTEGER NOT NULL,
                username              TEXT,
                full_name             TEXT,
                product_type          TEXT NOT NULL,      -- 'premium' | 'stars'
                package_key           TEXT NOT NULL,
                package_label         TEXT NOT NULL,
                base_usd              REAL NOT NULL,
                multiplier            REAL NOT NULL,
                usdt_rate             REAL,
                price_toman           INTEGER NOT NULL,
                target_username       TEXT,
                receipt_file_id       TEXT,
                receipt_unique_id     TEXT UNIQUE,
                status                TEXT NOT NULL DEFAULT 'awaiting_payment',
                                      -- awaiting_payment -> awaiting_username -> pending_admin -> approved/rejected/cancelled
                admin_message_ids     TEXT,                -- جیسون آیدی پیام‌هایی که برای هر ادمین فرستاده شده (برای ادیت بعد از تصمیم)
                created_at            INTEGER NOT NULL,
                decided_at            INTEGER,
                decided_by            INTEGER
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS settings (
                key    TEXT PRIMARY KEY,
                value  TEXT
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_state (
                user_id  INTEGER PRIMARY KEY,
                action   TEXT,
                data     TEXT
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS packages (
                id            INTEGER PRIMARY KEY AUTOINCREMENT,
                product_type  TEXT NOT NULL,      -- 'premium' | 'stars'
                key           TEXT NOT NULL,
                label         TEXT NOT NULL,
                amount        INTEGER NOT NULL,   -- تعداد ماه (پرمیوم) یا تعداد استارز
                base_usd      REAL NOT NULL,
                UNIQUE (product_type, key)
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS tickets (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id     INTEGER NOT NULL,
                status      TEXT DEFAULT 'open',   -- open / closed
                created_at  INTEGER,
                updated_at  INTEGER
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS ticket_messages (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                ticket_id   INTEGER NOT NULL,
                sender      TEXT NOT NULL,          -- 'user' یا 'admin'
                text        TEXT,
                created_at  INTEGER
            )
        """)
        conn.execute("CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_ticket_msgs ON ticket_messages(ticket_id)")

        # --- مهاجرت‌ها: کیف پول، رفرال و ضد اسپم (روی دیتابیس‌های قدیمی هم امن است) ---
        for col_def in (
            "balance INTEGER DEFAULT 0",            # موجودی آزاد کیف پول (تومان)
            "referral_balance INTEGER DEFAULT 0",   # اعتبار رفرال (تومان)
            "referred_by INTEGER",                  # آیدی کاربری که این کاربر را دعوت کرده
            "last_action_ts REAL DEFAULT 0",        # آخرین کلیک (برای rate limit)
        ):
            try:
                conn.execute(f"ALTER TABLE users ADD COLUMN {col_def}")
            except sqlite3.OperationalError:
                pass
        for col_def in (
            "payment_method TEXT",                  # 'card' | 'wallet'
            "referral_credited INTEGER DEFAULT 0",  # پورسانت رفرال این سفارش داده شده؟
            "wallet_refunded INTEGER DEFAULT 0",    # مبلغ سفارش (پرداخت‌شده با کیف پول) برگشت خورده؟
            "decided_by_name TEXT",                 # نام ادمینی که پرداخت را تایید/رد کرد
            "completed_by INTEGER",                 # ادمینی که سفارش را تکمیل کرد
            "completed_by_name TEXT",
            "completed_at INTEGER",
            "kyc_required INTEGER DEFAULT 0",       # این سفارش (مبلغ بالا) احراز هویت می‌خواست؟
            "kyc_files TEXT",                       # لیست JSON از [file_id, unique_id] عکس‌های مدارک
        ):
            try:
                conn.execute(f"ALTER TABLE orders ADD COLUMN {col_def}")
            except sqlite3.OperationalError:
                pass
        conn.execute("""
            CREATE TABLE IF NOT EXISTS wallet_transactions (
                id               INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id          INTEGER NOT NULL,
                kind             TEXT NOT NULL,   -- topup / purchase / refund / referral_bonus / adjust
                amount           INTEGER NOT NULL DEFAULT 0,   -- تغییر موجودی آزاد (منفی = کسر)
                referral_amount  INTEGER NOT NULL DEFAULT 0,   -- تغییر اعتبار رفرال (منفی = کسر)
                balance_after    INTEGER,
                referral_after   INTEGER,
                order_id         INTEGER,
                note             TEXT,
                created_at       INTEGER
            )
        """)
        conn.execute("CREATE INDEX IF NOT EXISTS idx_wallet_tx_user ON wallet_transactions(user_id, id)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_users_referred_by ON users(referred_by)")

        # بذرکاری اولیه‌ی بسته‌ها از config.py (فقط اگر جدول کاملاً خالی است؛ بعد از آن
        # بسته‌ها فقط از پنل ادمین مدیریت می‌شوند و config.py دیگر اثری ندارد)
        if conn.execute("SELECT COUNT(*) AS c FROM packages").fetchone()["c"] == 0:
            for p in config.PREMIUM_PACKAGES:
                conn.execute(
                    "INSERT OR IGNORE INTO packages (product_type, key, label, amount, base_usd) VALUES ('premium', ?, ?, ?, ?)",
                    (p["key"], p["label"], p["months"], p["base_usd"]),
                )
            for p in config.STARS_PACKAGES:
                conn.execute(
                    "INSERT OR IGNORE INTO packages (product_type, key, label, amount, base_usd) VALUES ('stars', ?, ?, ?, ?)",
                    (p["key"], p["label"], p["amount"], p["base_usd"]),
                )


# ---------------------------------------------------------------------------
# کاربران
# ---------------------------------------------------------------------------

def upsert_user(user_id, username, full_name):
    """کاربر را ثبت/به‌روزرسانی می‌کند. اگر کاربر همین الان برای اولین بار ثبت شد True برمی‌گرداند."""
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT OR IGNORE INTO users (user_id, username, full_name, created_at) VALUES (?, ?, ?, ?)",
            (user_id, username, full_name, int(time.time())),
        )
        is_new = cur.rowcount == 1
        if not is_new:
            conn.execute(
                "UPDATE users SET username = ?, full_name = ? WHERE user_id = ?",
                (username, full_name, user_id),
            )
        return is_new


def get_user(user_id):
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM users WHERE user_id = ?", (user_id,)).fetchone()
        return dict(row) if row else None


def find_user_by_username(username):
    username = (username or "").lstrip("@").strip()
    if not username:
        return None
    with get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM users WHERE LOWER(username) = LOWER(?) ORDER BY created_at DESC LIMIT 1", (username,)
        ).fetchone()
        return dict(row) if row else None


def list_all_user_ids():
    with get_conn() as conn:
        rows = conn.execute("SELECT user_id FROM users ORDER BY user_id").fetchall()
        return [r["user_id"] for r in rows]


def count_users():
    with get_conn() as conn:
        row = conn.execute("SELECT COUNT(*) AS c FROM users").fetchone()
        return row["c"] if row else 0


# ---------------------------------------------------------------------------
# تنظیمات (ضریب قیمت و غیره) — پنل ادمین این‌ها را عوض می‌کند
# ---------------------------------------------------------------------------

def get_setting(key, default=None):
    with get_conn() as conn:
        row = conn.execute("SELECT value FROM settings WHERE key = ?", (key,)).fetchone()
        return row["value"] if row else default


def set_setting(key, value):
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value=excluded.value",
            (key, str(value)),
        )


def get_premium_multiplier():
    val = get_setting("premium_multiplier")
    return float(val) if val is not None else config.PREMIUM_MULTIPLIER_DEFAULT


def get_stars_multiplier():
    val = get_setting("stars_multiplier")
    return float(val) if val is not None else config.STARS_MULTIPLIER_DEFAULT


def set_premium_multiplier(value):
    set_setting("premium_multiplier", value)


def set_stars_multiplier(value):
    set_setting("stars_multiplier", value)


def get_usdt_rate_override():
    val = get_setting("usdt_rate_override")
    return float(val) if val else None


def set_usdt_rate_override(value):
    set_setting("usdt_rate_override", value if value else "")


# ---------------------------------------------------------------------------
# وضعیت گفتگو (برای ادمین: گرفتن ضریب/دلیل رد/پاسخ تیکت/پیام همگانی...؛ برای کاربر: نوشتن تیکت)
# ---------------------------------------------------------------------------

def set_state(user_id, action, data=None):
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO user_state (user_id, action, data) VALUES (?, ?, ?) "
            "ON CONFLICT(user_id) DO UPDATE SET action=excluded.action, data=excluded.data",
            (user_id, action, json.dumps(data or {}, ensure_ascii=False)),
        )


def get_state(user_id):
    with get_conn() as conn:
        row = conn.execute("SELECT action, data FROM user_state WHERE user_id = ?", (user_id,)).fetchone()
        if not row:
            return None
        try:
            data = json.loads(row["data"] or "{}")
        except Exception:
            data = {}
        return {"action": row["action"], "data": data}


def clear_state(user_id):
    with get_conn() as conn:
        conn.execute("DELETE FROM user_state WHERE user_id = ?", (user_id,))


# ---------------------------------------------------------------------------
# سفارش‌ها
# ---------------------------------------------------------------------------

def create_order(user_id, username, full_name, product_type, package_key, package_label,
                  base_usd, multiplier, usdt_rate, price_toman):
    with get_conn() as conn:
        cur = conn.execute(
            """INSERT INTO orders
               (user_id, username, full_name, product_type, package_key, package_label,
                base_usd, multiplier, usdt_rate, price_toman, status, created_at)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'awaiting_payment', ?)""",
            (user_id, username, full_name, product_type, package_key, package_label,
             base_usd, multiplier, usdt_rate, price_toman, int(time.time())),
        )
        return cur.lastrowid


def get_order(order_id):
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM orders WHERE id = ?", (order_id,)).fetchone()
        return dict(row) if row else None


def get_latest_user_order_in_status(user_id, status):
    """آخرین سفارشِ کاربر که در وضعیت مشخص‌شده است را برمی‌گرداند (برای تشخیص این‌که
    عکس/متنی که تازه فرستاده مربوط به کدام سفارش در جریان است)."""
    with get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY id DESC LIMIT 1",
            (user_id, status),
        ).fetchone()
        return dict(row) if row else None


def find_order_by_receipt_unique_id(unique_id):
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM orders WHERE receipt_unique_id = ?", (unique_id,)).fetchone()
        return dict(row) if row else None


def attach_receipt(order_id, file_id, unique_id, kyc_required=False):
    """رسید را به سفارش وصل می‌کند. مسیر بعدی:
      - مبلغ بالاتر از حد احراز هویت (kyc_required): awaiting_kyc (تا ارسال مدارک)
      - سفارش خرید (پرمیوم/استارز): awaiting_username
      - سفارش شارژ کیف پول (بدون یوزرنیم مقصد): مستقیم pending_admin
    برمی‌گرداند: True اگر سفارش هنوز در انتظار پرداخت بود و رسید ثبت شد.
    اگر unique_id قبلاً برای سفارش دیگری ثبت شده باشد، sqlite3.IntegrityError می‌دهد؛
    caller باید این را بگیرد و پیام رسید تکراری بدهد."""
    kyc = 1 if kyc_required else 0
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET receipt_file_id = ?, receipt_unique_id = ?, kyc_required = ?, "
            "status = CASE WHEN ? = 1 THEN 'awaiting_kyc' "
            "              WHEN product_type = 'wallet_topup' THEN 'pending_admin' "
            "              ELSE 'awaiting_username' END "
            "WHERE id = ? AND status = 'awaiting_payment'",
            (file_id, unique_id, kyc, kyc, order_id),
        )
        return cur.rowcount == 1


# ---------------------------------------------------------------------------
# احراز هویت مبالغ بالا (عکس کارت بانکی + کارت ملی)
# ---------------------------------------------------------------------------

def get_kyc_files(order_id):
    """لیست مدارک احراز هویت سفارش: [(file_id, unique_id), ...]"""
    with get_conn() as conn:
        row = conn.execute("SELECT kyc_files FROM orders WHERE id = ?", (order_id,)).fetchone()
    try:
        items = json.loads(row["kyc_files"]) if row and row["kyc_files"] else []
    except ValueError:
        items = []
    return [(i[0], i[1] if len(i) > 1 else None) for i in items]


def add_kyc_file(order_id, file_id, unique_id=None, max_files=6):
    """یک عکس مدرک به سفارش اضافه می‌کند (فقط وقتی سفارش در awaiting_kyc است؛ عکس تکراری نادیده
    گرفته می‌شود) و تعداد کل را برمی‌گرداند."""
    items = get_kyc_files(order_id)
    if unique_id and any(u == unique_id for _, u in items):
        return len(items)
    if len(items) >= max_files:
        return len(items)
    items.append((file_id, unique_id))
    with get_conn() as conn:
        conn.execute(
            "UPDATE orders SET kyc_files = ? WHERE id = ? AND status = 'awaiting_kyc'",
            (json.dumps([list(i) for i in items], ensure_ascii=False), order_id),
        )
    return len(items)


def confirm_kyc_atomic(order_id):
    """کاربر «تایید و ارسال مدارک» را زده: awaiting_kyc -> (شارژ: pending_admin | خرید: awaiting_username).
    اتمیک است؛ با دوبار کلیک فقط یک‌بار موفق می‌شود."""
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = CASE WHEN product_type = 'wallet_topup' "
            "THEN 'pending_admin' ELSE 'awaiting_username' END "
            "WHERE id = ? AND status = 'awaiting_kyc'",
            (order_id,),
        )
        return cur.rowcount == 1


def list_awaiting_kyc_orders(limit=30):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE status = 'awaiting_kyc' ORDER BY id ASC LIMIT ?", (limit,)
        ).fetchall()
        return [dict(r) for r in rows]


def attach_target_username(order_id, target_username):
    with get_conn() as conn:
        conn.execute(
            "UPDATE orders SET target_username = ?, status = 'pending_admin' "
            "WHERE id = ? AND status = 'awaiting_username'",
            (target_username, order_id),
        )


def set_admin_message_ids(order_id, json_text):
    with get_conn() as conn:
        conn.execute("UPDATE orders SET admin_message_ids = ? WHERE id = ?", (json_text, order_id))


def decide_order_atomic(order_id, new_status, admin_id, from_status="pending_admin", admin_name=None):
    """تایید/رد اتمیک: فقط اگر سفارش هنوز در from_status باشد تغییر می‌کند.
    برمی‌گرداند: True اگر همین فراخوانی موفق به تغییر وضعیت شد (یعنی هیچ ادمین دیگری
    زودتر همین کار را نکرده)، False اگر سفارش قبلاً توسط ادمین دیگری تصمیم‌گیری شده بود."""
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = ?, decided_at = ?, decided_by = ?, decided_by_name = ? "
            "WHERE id = ? AND status = ?",
            (new_status, int(time.time()), admin_id, admin_name, order_id, from_status),
        )
        return cur.rowcount == 1


def reject_order_atomic(order_id, admin_id, admin_name=None):
    """رد سفارش: هم در مرحله‌ی «در انتظار تایید» و هم در مرحله‌ی «در حال انجام» ممکن است."""
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = 'rejected', decided_at = ?, decided_by = ?, decided_by_name = ? "
            "WHERE id = ? AND status IN ('pending_admin', 'processing')",
            (int(time.time()), admin_id, admin_name, order_id),
        )
        return cur.rowcount == 1


def complete_order_atomic(order_id, admin_id, admin_name=None):
    """مرحله‌ی دوم: processing -> approved (تکمیل شد). decided_at را به زمان تکمیل می‌برد تا گزارش فروش درست باشد."""
    now = int(time.time())
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = 'approved', decided_at = ?, completed_at = ?, completed_by = ?, "
            "completed_by_name = ? WHERE id = ? AND status = 'processing'",
            (now, now, admin_id, admin_name, order_id),
        )
        return cur.rowcount == 1


def expire_order_atomic(order_id):
    """سفارش را (فقط اگر هنوز در انتظار پرداخت است) به‌عنوان منقضی‌شده علامت می‌زند."""
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = 'expired', decided_at = ? WHERE id = ? AND status = 'awaiting_payment'",
            (int(time.time()), order_id),
        )
        return cur.rowcount == 1


def list_stale_awaiting_orders(cutoff, topup_cutoff=None):
    """سفارش‌های در انتظار پرداختی که بیش از حد مجاز طول کشیده و هنوز رسیدی نفرستاده‌اند.
    سفارش‌های شارژ کیف پول مهلت جدا (topup_cutoff) دارند."""
    if topup_cutoff is None:
        topup_cutoff = cutoff
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE status = 'awaiting_payment' AND "
            "((product_type != 'wallet_topup' AND created_at < ?) OR (product_type = 'wallet_topup' AND created_at < ?))",
            (cutoff, topup_cutoff),
        ).fetchall()
        return [dict(r) for r in rows]


def cancel_order_atomic(order_id, user_id):
    """کاربر فقط تا قبل از پندینگ‌شدن نزد ادمین می‌تواند سفارش را لغو کند."""
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = 'cancelled', decided_at = ? "
            "WHERE id = ? AND user_id = ? AND status IN ('awaiting_payment', 'awaiting_username')",
            (int(time.time()), order_id, user_id),
        )
        return cur.rowcount == 1


def list_user_orders(user_id, limit=10):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE user_id = ? ORDER BY id DESC LIMIT ?", (user_id, limit)
        ).fetchall()
        return [dict(r) for r in rows]


def list_user_orders_visible(user_id, limit=10):
    """سفارش‌هایی که در «سفارش‌های من» نشان داده می‌شوند (ناتمام/لغو/منقضی نمایش داده نمی‌شوند)."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE user_id = ? AND status IN "
            "('awaiting_kyc', 'awaiting_username', 'pending_admin', 'processing', 'approved', 'rejected') ORDER BY id DESC LIMIT ?",
            (user_id, limit),
        ).fetchall()
        return [dict(r) for r in rows]


def list_pending_orders(limit=30):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE status = 'pending_admin' ORDER BY id ASC LIMIT ?", (limit,)
        ).fetchall()
        return [dict(r) for r in rows]


def list_processing_orders(limit=30):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE status = 'processing' ORDER BY id ASC LIMIT ?", (limit,)
        ).fetchall()
        return [dict(r) for r in rows]


def count_orders_by_status():
    with get_conn() as conn:
        rows = conn.execute("SELECT status, COUNT(*) AS c FROM orders GROUP BY status").fetchall()
        return {r["status"]: r["c"] for r in rows}


def list_user_orders_by_type(user_id, product_type, limit=10, statuses=None):
    with get_conn() as conn:
        sql = "SELECT * FROM orders WHERE user_id = ? AND product_type = ?"
        args = [user_id, product_type]
        if statuses:
            sql += " AND status IN (%s)" % ",".join("?" * len(statuses))
            args += list(statuses)
        sql += " ORDER BY id DESC LIMIT ?"
        args.append(limit)
        rows = conn.execute(sql, args).fetchall()
        return [dict(r) for r in rows]


# ---------------------------------------------------------------------------
# بسته‌ها (قابل افزودن/حذف/ویرایش از پنل ادمین)
# ---------------------------------------------------------------------------

def list_packages(product_type):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM packages WHERE product_type = ? ORDER BY amount ASC", (product_type,)
        ).fetchall()
        return [dict(r) for r in rows]


def get_package(product_type, key):
    with get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM packages WHERE product_type = ? AND key = ?", (product_type, key)
        ).fetchone()
        return dict(row) if row else None


def get_package_by_id(package_id):
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM packages WHERE id = ?", (package_id,)).fetchone()
        return dict(row) if row else None


def add_package(product_type, key, label, amount, base_usd):
    """بسته‌ی جدید اضافه می‌کند. اگر بسته‌ای با همین مقدار از قبل هست، None برمی‌گرداند."""
    try:
        with get_conn() as conn:
            cur = conn.execute(
                "INSERT INTO packages (product_type, key, label, amount, base_usd) VALUES (?, ?, ?, ?, ?)",
                (product_type, key, label, amount, base_usd),
            )
            return cur.lastrowid
    except sqlite3.IntegrityError:
        return None


def update_package_price(package_id, base_usd):
    with get_conn() as conn:
        conn.execute("UPDATE packages SET base_usd = ? WHERE id = ?", (base_usd, package_id))


def delete_package(package_id):
    with get_conn() as conn:
        conn.execute("DELETE FROM packages WHERE id = ?", (package_id,))


# ---------------------------------------------------------------------------
# تیکت پشتیبانی
# ---------------------------------------------------------------------------

def create_ticket(user_id):
    now = int(time.time())
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO tickets (user_id, status, created_at, updated_at) VALUES (?, 'open', ?, ?)",
            (user_id, now, now),
        )
        return cur.lastrowid


def get_open_ticket_for_user(user_id):
    with get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM tickets WHERE user_id = ? AND status = 'open' ORDER BY id DESC LIMIT 1",
            (user_id,),
        ).fetchone()
        return dict(row) if row else None


def get_ticket(ticket_id):
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM tickets WHERE id = ?", (ticket_id,)).fetchone()
        return dict(row) if row else None


def close_ticket(ticket_id):
    now = int(time.time())
    with get_conn() as conn:
        conn.execute("UPDATE tickets SET status = 'closed', updated_at = ? WHERE id = ?", (now, ticket_id))


def add_ticket_message(ticket_id, sender, text):
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO ticket_messages (ticket_id, sender, text, created_at) VALUES (?, ?, ?, ?)",
            (ticket_id, sender, text, now),
        )
        conn.execute("UPDATE tickets SET updated_at = ? WHERE id = ?", (now, ticket_id))


def list_ticket_messages(ticket_id, limit=50):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM ticket_messages WHERE ticket_id = ? ORDER BY id ASC LIMIT ?",
            (ticket_id, limit),
        ).fetchall()
        return [dict(r) for r in rows]


def ticket_has_admin_reply(ticket_id):
    with get_conn() as conn:
        row = conn.execute(
            "SELECT 1 FROM ticket_messages WHERE ticket_id = ? AND sender = 'admin' LIMIT 1", (ticket_id,)
        ).fetchone()
        return bool(row)


def ticket_has_user_message(ticket_id):
    """آیا مشتری تا الان حداقل یک پیام در این تیکت فرستاده؟ (اگر نه، یعنی گفتگو را ادمین شروع کرده)"""
    with get_conn() as conn:
        row = conn.execute(
            "SELECT 1 FROM ticket_messages WHERE ticket_id = ? AND sender = 'user' LIMIT 1", (ticket_id,)
        ).fetchone()
        return row is not None


def list_tickets_for_user(user_id, limit=5):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM tickets WHERE user_id = ? ORDER BY id DESC LIMIT ?", (user_id, limit)
        ).fetchall()
        return [dict(r) for r in rows]


def list_idle_tickets_awaiting_user(cutoff_ts):
    """تیکت‌های بازی که آخرین پیامشان از طرف پشتیبانی بوده و از آخرین فعالیتشان قبل از cutoff_ts گذشته."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT t.* FROM tickets t WHERE t.status = 'open' AND t.updated_at < ? "
            "AND (SELECT m.sender FROM ticket_messages m WHERE m.ticket_id = t.id ORDER BY m.id DESC LIMIT 1) = 'admin'",
            (cutoff_ts,),
        ).fetchall()
        return [dict(r) for r in rows]


def list_open_tickets():
    """فقط تیکت‌هایی که حداقل یک پیام دارند (تیکت خالی برای ادمین بی‌معنی است)."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM tickets WHERE status = 'open' "
            "AND EXISTS (SELECT 1 FROM ticket_messages m WHERE m.ticket_id = tickets.id) "
            "ORDER BY updated_at DESC"
        ).fetchall()
        return [dict(r) for r in rows]


# ---------------------------------------------------------------------------
# گزارش فروش (سفارش‌های تایید‌شده)
# ---------------------------------------------------------------------------

def get_sales_stats(start_ts, end_ts):
    with get_conn() as conn:
        row = conn.execute(
            "SELECT COUNT(*) AS cnt, COALESCE(SUM(price_toman), 0) AS total FROM orders "
            "WHERE status = 'approved' AND product_type IN ('premium', 'stars') AND decided_at BETWEEN ? AND ?",
            (start_ts, end_ts),
        ).fetchone()
        by_type = conn.execute(
            "SELECT product_type, COUNT(*) AS cnt, COALESCE(SUM(price_toman), 0) AS total FROM orders "
            "WHERE status = 'approved' AND product_type IN ('premium', 'stars') AND decided_at BETWEEN ? AND ? GROUP BY product_type",
            (start_ts, end_ts),
        ).fetchall()
        top = conn.execute(
            "SELECT package_label, product_type, COUNT(*) AS cnt FROM orders "
            "WHERE status = 'approved' AND product_type IN ('premium', 'stars') AND decided_at BETWEEN ? AND ? "
            "GROUP BY product_type, package_label ORDER BY cnt DESC LIMIT 1",
            (start_ts, end_ts),
        ).fetchone()
        return {
            "count": row["cnt"] if row else 0,
            "total": row["total"] if row else 0,
            "by_type": [dict(r) for r in by_type],
            "top_package": dict(top) if top else None,
        }


# ---------------------------------------------------------------------------
# کیف پول (دفتر تراکنش + موجودی آزاد + اعتبار رفرال)
# ---------------------------------------------------------------------------

def _ensure_user(conn, user_id):
    conn.execute(
        "INSERT OR IGNORE INTO users (user_id, created_at) VALUES (?, ?)", (user_id, int(time.time()))
    )


def _log_wallet_tx(conn, user_id, kind, amount=0, referral_amount=0, order_id=None, note=None):
    """باید داخل همان اتصالی صدا زده شود که موجودی را تغییر داده (بعد از UPDATE موجودی)."""
    row = conn.execute(
        "SELECT COALESCE(balance, 0) AS b, COALESCE(referral_balance, 0) AS r FROM users WHERE user_id = ?",
        (user_id,),
    ).fetchone()
    conn.execute(
        "INSERT INTO wallet_transactions "
        "(user_id, kind, amount, referral_amount, balance_after, referral_after, order_id, note, created_at) "
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
        (user_id, kind, amount, referral_amount,
         row["b"] if row else 0, row["r"] if row else 0, order_id, note, int(time.time())),
    )


def get_wallet(user_id):
    """(موجودی آزاد, اعتبار رفرال) کاربر."""
    with get_conn() as conn:
        row = conn.execute(
            "SELECT COALESCE(balance, 0) AS b, COALESCE(referral_balance, 0) AS r FROM users WHERE user_id = ?",
            (user_id,),
        ).fetchone()
        return (row["b"], row["r"]) if row else (0, 0)


def add_user_balance(user_id, amount, kind="adjust", order_id=None, note=None):
    """به موجودی آزاد اضافه (یا با مقدار منفی کم) می‌کند و موجودی جدید را برمی‌گرداند."""
    with get_conn() as conn:
        _ensure_user(conn, user_id)
        conn.execute("UPDATE users SET balance = COALESCE(balance, 0) + ? WHERE user_id = ?", (amount, user_id))
        if amount:
            _log_wallet_tx(conn, user_id, kind, amount=amount, order_id=order_id, note=note)
        return conn.execute("SELECT COALESCE(balance, 0) AS b FROM users WHERE user_id = ?", (user_id,)).fetchone()["b"]


def list_wallet_transactions(user_id, limit=10):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM wallet_transactions WHERE user_id = ? ORDER BY id DESC LIMIT ?", (user_id, limit)
        ).fetchall()
        return [dict(r) for r in rows]


def approve_topup_atomic(order_id, admin_id, admin_name=None):
    """تایید سفارش شارژ: تغییر وضعیت + افزایش موجودی در یک تراکنش (شارژ دوبل ممکن نیست).
    برمی‌گرداند (user_id, amount, new_balance) یا None اگر سفارش دیگر در انتظار تایید نبود."""
    with get_conn() as conn:
        conn.execute("BEGIN IMMEDIATE")
        order = conn.execute(
            "SELECT * FROM orders WHERE id = ? AND product_type = 'wallet_topup' AND status = 'pending_admin'",
            (order_id,),
        ).fetchone()
        if not order:
            return None
        conn.execute(
            "UPDATE orders SET status = 'approved', decided_at = ?, decided_by = ?, decided_by_name = ? WHERE id = ?",
            (int(time.time()), admin_id, admin_name, order_id),
        )
        _ensure_user(conn, order["user_id"])
        conn.execute(
            "UPDATE users SET balance = COALESCE(balance, 0) + ? WHERE user_id = ?",
            (order["price_toman"], order["user_id"]),
        )
        _log_wallet_tx(conn, order["user_id"], "topup", amount=order["price_toman"], order_id=order_id)
        new_balance = conn.execute(
            "SELECT COALESCE(balance, 0) AS b FROM users WHERE user_id = ?", (order["user_id"],)
        ).fetchone()["b"]
        return order["user_id"], order["price_toman"], new_balance


def pay_order_with_wallet(order_id, user_id):
    """پرداخت اتمیک سفارش از کیف پول (اول اعتبار رفرال، بعد موجودی آزاد).
    برمی‌گرداند:
      ("ok", (موجودی_جدید, رفرال_جدید))
      ("insufficient", (قابل_استفاده, مبلغ_سفارش))
      ("unavailable", None)   # سفارش پیدا نشد / مال این کاربر نیست / دیگر در انتظار پرداخت نیست"""
    with get_conn() as conn:
        conn.execute("BEGIN IMMEDIATE")
        order = conn.execute(
            "SELECT * FROM orders WHERE id = ? AND user_id = ? AND status = 'awaiting_payment' "
            "AND product_type != 'wallet_topup'",
            (order_id, user_id),
        ).fetchone()
        if not order:
            return "unavailable", None
        price = order["price_toman"]
        row = conn.execute(
            "SELECT COALESCE(balance, 0) AS b, COALESCE(referral_balance, 0) AS r FROM users WHERE user_id = ?",
            (user_id,),
        ).fetchone()
        balance, referral = (row["b"], row["r"]) if row else (0, 0)
        if balance + referral < price:
            return "insufficient", (balance + referral, price)
        from_ref = min(referral, price)
        from_bal = price - from_ref
        conn.execute(
            "UPDATE users SET balance = COALESCE(balance, 0) - ?, referral_balance = COALESCE(referral_balance, 0) - ? "
            "WHERE user_id = ?",
            (from_bal, from_ref, user_id),
        )
        _log_wallet_tx(conn, user_id, "purchase", amount=-from_bal, referral_amount=-from_ref, order_id=order_id)
        conn.execute(
            "UPDATE orders SET status = 'awaiting_username', payment_method = 'wallet' WHERE id = ?", (order_id,)
        )
        new = conn.execute(
            "SELECT COALESCE(balance, 0) AS b, COALESCE(referral_balance, 0) AS r FROM users WHERE user_id = ?",
            (user_id,),
        ).fetchone()
        return "ok", (new["b"], new["r"])


def refund_wallet_order(order_id):
    """اگر سفارش با کیف پول پرداخت شده بود (و هنوز برگشت نخورده)، مبلغ را به موجودی آزاد برمی‌گرداند.
    برمی‌گرداند: مبلغ برگشتی (0 یعنی چیزی برگشت نخورد). فقط یک‌بار برای هر سفارش کار می‌کند."""
    with get_conn() as conn:
        conn.execute("BEGIN IMMEDIATE")
        cur = conn.execute(
            "UPDATE orders SET wallet_refunded = 1 WHERE id = ? AND payment_method = 'wallet' "
            "AND COALESCE(wallet_refunded, 0) = 0",
            (order_id,),
        )
        if cur.rowcount != 1:
            return 0
        order = conn.execute("SELECT user_id, price_toman FROM orders WHERE id = ?", (order_id,)).fetchone()
        _ensure_user(conn, order["user_id"])
        conn.execute(
            "UPDATE users SET balance = COALESCE(balance, 0) + ? WHERE user_id = ?",
            (order["price_toman"], order["user_id"]),
        )
        _log_wallet_tx(conn, order["user_id"], "refund", amount=order["price_toman"], order_id=order_id)
        return order["price_toman"]


# ---------------------------------------------------------------------------
# رفرال (دعوت دوستان)
# ---------------------------------------------------------------------------

def get_referral_percent():
    val = get_setting("referral_percent")
    if val is None:
        return float(config.REFERRAL_PERCENT_DEFAULT)
    try:
        return float(val)
    except (TypeError, ValueError):
        return float(config.REFERRAL_PERCENT_DEFAULT)


def set_referral_percent(percent):
    set_setting("referral_percent", percent)


def set_referred_by(user_id, referrer_id):
    """معرف را ثبت می‌کند، فقط اگر: کاربر خودش را دعوت نکرده، معرف قبلی ندارد، هنوز هیچ خریدی
    نکرده (تا کاربران قدیمی/خریدار به‌صورت بعدی به کسی نسبت داده نشوند) و معرف واقعاً در ربات هست."""
    if user_id == referrer_id:
        return False
    with get_conn() as conn:
        conn.execute("BEGIN IMMEDIATE")
        if not conn.execute("SELECT 1 FROM users WHERE user_id = ?", (referrer_id,)).fetchone():
            return False
        row = conn.execute("SELECT referred_by FROM users WHERE user_id = ?", (user_id,)).fetchone()
        if not row or row["referred_by"]:
            return False
        if conn.execute(
            "SELECT 1 FROM orders WHERE user_id = ? AND status NOT IN ('cancelled', 'expired') LIMIT 1", (user_id,)
        ).fetchone():
            return False
        conn.execute("UPDATE users SET referred_by = ? WHERE user_id = ?", (referrer_id, user_id))
        return True


def get_referred_by(user_id):
    with get_conn() as conn:
        row = conn.execute("SELECT referred_by FROM users WHERE user_id = ?", (user_id,)).fetchone()
        return row["referred_by"] if row and row["referred_by"] else None


def count_referrals(user_id):
    with get_conn() as conn:
        row = conn.execute("SELECT COUNT(*) AS c FROM users WHERE referred_by = ?", (user_id,)).fetchone()
        return row["c"] if row else 0


def credit_referral_once(order_id, referrer_id, bonus):
    """پورسانت رفرال یک سفارش را دقیقاً یک‌بار به اعتبار رفرال معرف اضافه می‌کند. برمی‌گرداند True/False."""
    with get_conn() as conn:
        conn.execute("BEGIN IMMEDIATE")
        cur = conn.execute(
            "UPDATE orders SET referral_credited = 1 WHERE id = ? AND COALESCE(referral_credited, 0) = 0", (order_id,)
        )
        if cur.rowcount != 1:
            return False
        _ensure_user(conn, referrer_id)
        conn.execute(
            "UPDATE users SET referral_balance = COALESCE(referral_balance, 0) + ? WHERE user_id = ?",
            (bonus, referrer_id),
        )
        _log_wallet_tx(conn, referrer_id, "referral_bonus", referral_amount=bonus, order_id=order_id)
        return True


# ---------------------------------------------------------------------------
# ضد اسپم کلیک روی دکمه‌ها
# ---------------------------------------------------------------------------

def check_and_update_rate_limit(user_id, min_interval_seconds):
    """اگر کمتر از min_interval_seconds از آخرین اکشن کاربر گذشته باشد False (یعنی نادیده بگیر)."""
    now = time.time()
    with get_conn() as conn:
        row = conn.execute("SELECT COALESCE(last_action_ts, 0) AS t FROM users WHERE user_id = ?", (user_id,)).fetchone()
        if row and (now - row["t"]) < min_interval_seconds:
            return False
        _ensure_user(conn, user_id)
        conn.execute("UPDATE users SET last_action_ts = ? WHERE user_id = ?", (now, user_id))
        return True


# ===========================================================================
# نسخه‌ی جدید: کد تخفیف، توقف فروش، «خبرم کن»، هدیه‌ی شارژ، ابزار ادمین
# ===========================================================================
_DEAD_STATUSES = "('cancelled', 'expired', 'rejected')"


def init_extra():
    with get_conn() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS discount_codes (
                code          TEXT PRIMARY KEY,
                kind          TEXT NOT NULL,            -- 'percent' | 'fixed'
                value         INTEGER NOT NULL,
                max_uses      INTEGER DEFAULT 0,        -- 0 = نامحدود
                expires_at    INTEGER DEFAULT 0,        -- 0 = بدون انقضا
                product       TEXT DEFAULT 'all',       -- all | premium | stars
                once_per_user INTEGER DEFAULT 1,
                active        INTEGER DEFAULT 1,
                created_at    INTEGER
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS discount_uses (
                order_id  INTEGER PRIMARY KEY,
                code      TEXT NOT NULL,
                user_id   INTEGER NOT NULL,
                amount    INTEGER NOT NULL
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS notify_me (
                user_id  INTEGER NOT NULL,
                product  TEXT NOT NULL,
                PRIMARY KEY (user_id, product)
            )
        """)
        for col_def in (
            "discount_code TEXT",
            "discount_amount INTEGER DEFAULT 0",
            "original_price INTEGER",
            "screen_mid INTEGER",
        ):
            try:
                conn.execute(f"ALTER TABLE orders ADD COLUMN {col_def}")
            except sqlite3.OperationalError:
                pass


# ----------------------------- کد تخفیف -----------------------------
def create_discount(code, kind, value, max_uses=0, expires_at=0, product="all", once_per_user=1):
    with get_conn() as conn:
        try:
            conn.execute(
                "INSERT INTO discount_codes (code, kind, value, max_uses, expires_at, product, once_per_user, active, created_at) "
                "VALUES (?, ?, ?, ?, ?, ?, ?, 1, ?)",
                (code.upper(), kind, value, max_uses, expires_at, product, once_per_user, int(time.time())),
            )
            return True
        except sqlite3.IntegrityError:
            return False


def _used_count(conn, code, exclude_order=None):
    return conn.execute(
        "SELECT COUNT(*) AS c FROM discount_uses u JOIN orders o ON o.id = u.order_id "
        f"WHERE u.code = ? AND u.order_id != ? AND o.status NOT IN {_DEAD_STATUSES}",
        (code, exclude_order or 0),
    ).fetchone()["c"]


def list_discounts(limit=15):
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM discount_codes ORDER BY created_at DESC LIMIT ?", (limit,)).fetchall()
        out = []
        for r in rows:
            d = dict(r)
            d["used"] = _used_count(conn, d["code"])
            out.append(d)
        return out


def toggle_discount(code):
    with get_conn() as conn:
        conn.execute("UPDATE discount_codes SET active = 1 - COALESCE(active, 1) WHERE code = ?", (code.upper(),))


def delete_discount(code):
    with get_conn() as conn:
        conn.execute("DELETE FROM discount_codes WHERE code = ?", (code.upper(),))


def apply_discount(order_id, user_id, code):
    """برمی‌گرداند (نتیجه, اطلاعات): ok / order / invalid / expired / product / exhausted / used"""
    code = (code or "").strip().upper()
    with get_conn() as conn:
        conn.execute("BEGIN IMMEDIATE")
        order = conn.execute(
            "SELECT * FROM orders WHERE id = ? AND user_id = ? AND status = 'awaiting_payment' "
            "AND product_type != 'wallet_topup'", (order_id, user_id)).fetchone()
        if not order:
            return "order", None
        d = conn.execute("SELECT * FROM discount_codes WHERE code = ?", (code,)).fetchone()
        if not d or not d["active"]:
            return "invalid", None
        if d["expires_at"] and d["expires_at"] < time.time():
            return "expired", None
        if d["product"] not in ("all", order["product_type"]):
            return "product", d["product"]
        if d["max_uses"] and _used_count(conn, code, order_id) >= d["max_uses"]:
            return "exhausted", None
        if d["once_per_user"]:
            dup = conn.execute(
                "SELECT 1 FROM discount_uses u JOIN orders o ON o.id = u.order_id "
                f"WHERE u.code = ? AND u.user_id = ? AND u.order_id != ? AND o.status NOT IN {_DEAD_STATUSES}",
                (code, user_id, order_id)).fetchone()
            if dup:
                return "used", None
        base = order["original_price"] or order["price_toman"]
        amount = base * d["value"] // 100 if d["kind"] == "percent" else d["value"]
        amount = max(0, min(amount, base - 1000))
        conn.execute(
            "UPDATE orders SET original_price = ?, price_toman = ?, discount_code = ?, discount_amount = ? WHERE id = ?",
            (base, base - amount, code, amount, order_id))
        conn.execute("INSERT OR REPLACE INTO discount_uses (order_id, code, user_id, amount) VALUES (?, ?, ?, ?)",
                     (order_id, code, user_id, amount))
        return "ok", {"amount": amount, "price": base - amount, "original": base}


def remove_discount(order_id, user_id):
    with get_conn() as conn:
        conn.execute("BEGIN IMMEDIATE")
        cur = conn.execute(
            "UPDATE orders SET price_toman = original_price, discount_code = NULL, discount_amount = 0, "
            "original_price = NULL WHERE id = ? AND user_id = ? AND status = 'awaiting_payment' "
            "AND original_price IS NOT NULL", (order_id, user_id))
        if cur.rowcount:
            conn.execute("DELETE FROM discount_uses WHERE order_id = ?", (order_id,))
        return cur.rowcount == 1


# ----------------------------- توقف فروش / خبرم کن -----------------------------
def get_pause(product):
    v = get_setting(f"pause:{product}", "")
    return v or None


def set_pause(product, text):
    set_setting(f"pause:{product}", text or "")


def add_notify(user_id, product):
    with get_conn() as conn:
        conn.execute("INSERT OR IGNORE INTO notify_me (user_id, product) VALUES (?, ?)", (user_id, product))


def list_notify(product):
    with get_conn() as conn:
        return [r["user_id"] for r in conn.execute("SELECT user_id FROM notify_me WHERE product = ?", (product,))]


def del_notify(user_id, product):
    with get_conn() as conn:
        conn.execute("DELETE FROM notify_me WHERE user_id = ? AND product = ?", (user_id, product))


# ----------------------------- هدیه‌ی شارژ -----------------------------
def get_topup_bonus():
    def _i(k):
        try:
            return int(get_setting(k, "0") or 0)
        except ValueError:
            return 0
    return _i("bonus_threshold"), _i("bonus_amount")


def set_topup_bonus(threshold, amount):
    set_setting("bonus_threshold", threshold)
    set_setting("bonus_amount", amount)


# ----------------------------- ابزار ادمین -----------------------------
def set_order_screen(order_id, mid):
    if not mid:
        return
    with get_conn() as conn:
        conn.execute("UPDATE orders SET screen_mid = ? WHERE id = ?", (mid, order_id))


def get_last_target_username(user_id):
    with get_conn() as conn:
        r = conn.execute(
            "SELECT target_username FROM orders WHERE user_id = ? AND target_username IS NOT NULL "
            "AND target_username != '' ORDER BY id DESC LIMIT 1", (user_id,)).fetchone()
        return r["target_username"] if r else None


def set_user_balance(user_id, new_balance, note=None):
    """موجودی آزاد را مستقیم روی مقدار نهایی می‌گذارد و اختلاف را در تاریخچه ثبت می‌کند."""
    with get_conn() as conn:
        conn.execute("BEGIN IMMEDIATE")
        _ensure_user(conn, user_id)
        old = conn.execute("SELECT COALESCE(balance, 0) AS b FROM users WHERE user_id = ?", (user_id,)).fetchone()["b"]
        conn.execute("UPDATE users SET balance = ? WHERE user_id = ?", (new_balance, user_id))
        if new_balance != old:
            _log_wallet_tx(conn, user_id, "adjust", amount=new_balance - old, note=note)
        return old


def admin_cancel_order(order_id):
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = 'cancelled', decided_at = ? WHERE id = ? AND status IN "
            "('awaiting_payment', 'awaiting_kyc', 'awaiting_username', 'pending_admin')",
            (int(time.time()), order_id))
        return cur.rowcount == 1


def list_audience_ids(aud="all"):
    with get_conn() as conn:
        if aud in ("premium", "stars"):
            rows = conn.execute(
                "SELECT DISTINCT user_id FROM orders WHERE product_type = ? AND status = 'approved'", (aud,))
        elif aud == "never":
            rows = conn.execute(
                "SELECT user_id FROM users WHERE user_id NOT IN (SELECT user_id FROM orders WHERE "
                "status = 'approved' AND product_type IN ('premium', 'stars'))")
        else:
            rows = conn.execute("SELECT user_id FROM users")
        return sorted(r["user_id"] for r in rows)


def check_discount(user_id, product, base_price, code):
    """پیش‌نمایش کد تخفیف بدون ثبت (برای مینی‌اپ). برمی‌گرداند (نتیجه, اطلاعات)."""
    code = (code or "").strip().upper()
    with get_conn() as conn:
        d = conn.execute("SELECT * FROM discount_codes WHERE code = ?", (code,)).fetchone()
        if not d or not d["active"]:
            return "invalid", None
        if d["expires_at"] and d["expires_at"] < time.time():
            return "expired", None
        if d["product"] not in ("all", product):
            return "product", d["product"]
        if d["max_uses"] and _used_count(conn, code) >= d["max_uses"]:
            return "exhausted", None
        if d["once_per_user"]:
            dup = conn.execute(
                "SELECT 1 FROM discount_uses u JOIN orders o ON o.id = u.order_id "
                f"WHERE u.code = ? AND u.user_id = ? AND o.status NOT IN {_DEAD_STATUSES}", (code, user_id)).fetchone()
            if dup:
                return "used", None
        amount = base_price * d["value"] // 100 if d["kind"] == "percent" else d["value"]
        amount = max(0, min(amount, base_price - 1000))
        return "ok", {"amount": amount, "price": base_price - amount}