# -*- coding: utf-8 -*-
"""
لایه‌ی دیتابیس (SQLite) برای پلن‌ها، سفارش‌ها و کاربران.
"""

import sqlite3
import json
import time
from contextlib import contextmanager

from config import DB_PATH


def _connect():
    conn = sqlite3.connect(DB_PATH, timeout=10)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL;")
    return conn


@contextmanager
def get_conn():
    conn = _connect()
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()


def init_db():
    with get_conn() as conn:
        conn.execute("""
        CREATE TABLE IF NOT EXISTS plans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price INTEGER NOT NULL,
            description TEXT DEFAULT '',
            active INTEGER DEFAULT 1,
            category_id INTEGER
        )
        """)
        # مهاجرت برای دیتابیس‌هایی که قبلاً بدون ستون category_id ساخته شده‌اند
        try:
            conn.execute("ALTER TABLE plans ADD COLUMN category_id INTEGER")
        except sqlite3.OperationalError:
            pass  # ستون از قبل وجود دارد
        conn.execute("""
        CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            active INTEGER DEFAULT 1
        )
        """)
        try:
            conn.execute("ALTER TABLE categories ADD COLUMN duration_days INTEGER")
        except sqlite3.OperationalError:
            pass  # ستون از قبل وجود دارد
        conn.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            username TEXT,
            full_name TEXT,
            plan_id INTEGER,
            plan_name TEXT,
            price INTEGER,
            status TEXT DEFAULT 'pending_receipt',  -- pending_receipt / awaiting_review / approved / rejected / delivered / expired / cancelled
            receipt_file_id TEXT,
            receipt_type TEXT DEFAULT 'photo',  -- 'photo' یا 'document'
            admin_note TEXT,
            created_at INTEGER,
            updated_at INTEGER
        )
        """)
        # مهاجرت برای دیتابیس‌هایی که قبلاً بدون ستون receipt_type ساخته شده‌اند
        try:
            conn.execute("ALTER TABLE orders ADD COLUMN receipt_type TEXT DEFAULT 'photo'")
        except sqlite3.OperationalError:
            pass  # ستون از قبل وجود دارد
        # مهاجرت برای احراز هویت مبالغ بالا (kyc_files: لیست JSON از [file_id, unique_id])
        for col_def in (
            "kyc_required INTEGER DEFAULT 0",
            "kyc_files TEXT",
            "approved_at INTEGER",   # زمان تایید سفارش (برای گزارش فروش)
            "wait_msg_id INTEGER",   # پیام ایموجی «منتظر بمانید» که بعد از انجام کار پاک می‌شود
            "recv_msg_id INTEGER",   # پیام «رسید دریافت شد» که بعد از انجام کار پاک می‌شود
            "pay_msg_id INTEGER",    # پیام اطلاعات پرداخت (با دکمه‌ی لغو) که بعد از ارسال رسید پاک می‌شود
        ):
            try:
                conn.execute(f"ALTER TABLE orders ADD COLUMN {col_def}")
            except sqlite3.OperationalError:
                pass  # ستون از قبل وجود دارد
        # مهاجرت برای ستون‌های مربوط به کد تخفیف
        for col_def in (
            "original_price INTEGER",
            "discount_code TEXT",
            "discount_amount INTEGER DEFAULT 0",
        ):
            try:
                conn.execute(f"ALTER TABLE orders ADD COLUMN {col_def}")
            except sqlite3.OperationalError:
                pass  # ستون از قبل وجود دارد
        conn.execute("""
        CREATE TABLE IF NOT EXISTS discount_codes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code TEXT NOT NULL UNIQUE,
            type TEXT NOT NULL,      -- 'percent' یا 'amount'
            value INTEGER NOT NULL,  -- درصد (۱ تا ۱۰۰) یا مبلغ تومان
            active INTEGER DEFAULT 1,
            created_at INTEGER
        )
        """)
        conn.execute("""
        CREATE TABLE IF NOT EXISTS uc_products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,       -- مثلاً "60 UC"
            factor REAL NOT NULL,     -- ضریب ضرب در نرخ تتر
            active INTEGER DEFAULT 1
        )
        """)
        # سبد خرید یوسی: هر ردیف = یک آیتم یوسی و تعداد آن برای یک کاربر
        conn.execute("""
        CREATE TABLE IF NOT EXISTS uc_cart (
            user_id INTEGER NOT NULL,
            uc_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL,
            updated_at INTEGER,
            PRIMARY KEY (user_id, uc_id)
        )
        """)
        # ستون‌های مربوط به نرخ تتر در سفارش‌های یوسی
        for col_def in (
            "order_type TEXT DEFAULT 'plan'",  # 'plan' یا 'uc' یا 'wallet_topup'
            "usdt_rate REAL",                  # نرخ تتری که هنگام ساخت سفارش استفاده شد
            "pubg_info TEXT",                  # آیدی و نام اکانت پابجی (فقط برای سفارش‌های uc)
            "payment_method TEXT DEFAULT 'card'",  # 'card' یا 'wallet'
            "expires_at INTEGER",              # تاریخ انقضای اشتراک (فقط پلن‌های تحویل‌شده با مدت مشخص)
            "renewal_reminded INTEGER DEFAULT 0",  # آیا یادآوری تمدید فرستاده شده
            "referral_credited INTEGER DEFAULT 0",  # آیا پورسانت رفرال این سفارش قبلاً به دعوت‌کننده داده شده
        ):
            try:
                conn.execute(f"ALTER TABLE orders ADD COLUMN {col_def}")
            except sqlite3.OperationalError:
                pass  # ستون از قبل وجود دارد
        # شناسه‌ی یکتای فایل رسید در تلگرام (file_unique_id) برای تشخیص رسید تکراری
        try:
            conn.execute("ALTER TABLE orders ADD COLUMN receipt_unique_id TEXT")
        except sqlite3.OperationalError:
            pass  # ستون از قبل وجود دارد
        conn.execute("""
        CREATE TABLE IF NOT EXISTS admin_state (
            admin_id INTEGER PRIMARY KEY,
            action TEXT,
            order_id INTEGER,
            data TEXT
        )
        """)
        # مهاجرت برای دیتابیس‌هایی که قبلاً بدون ستون data ساخته شده‌اند
        try:
            conn.execute("ALTER TABLE admin_state ADD COLUMN data TEXT")
        except sqlite3.OperationalError:
            pass  # ستون از قبل وجود دارد

        conn.execute("""
        CREATE TABLE IF NOT EXISTS users (
            user_id INTEGER PRIMARY KEY,
            username TEXT,
            full_name TEXT,
            first_seen INTEGER,
            last_seen INTEGER
        )
        """)
        # مهاجرت برای ستون موجودی کیف پول کاربران
        try:
            conn.execute("ALTER TABLE users ADD COLUMN balance INTEGER DEFAULT 0")
        except sqlite3.OperationalError:
            pass  # ستون از قبل وجود دارد

        # ستون‌های سیستم رفرال و محدودیت نرخ درخواست
        for col_def in (
            "referred_by INTEGER",                    # آیدی کاربری که این کاربر را دعوت کرده
            "referral_balance INTEGER DEFAULT 0",      # اعتبار رفرال - فقط قابل خرج در خرید پلن/کانفیگ
            "last_action_ts INTEGER DEFAULT 0",        # آخرین زمان کلیک روی دکمه (برای rate limit)
            "blocked INTEGER DEFAULT 0",               # ۱ = کاربر ربات را بلاک کرده (از پیام همگانی کنار گذاشته می‌شود)
            "blocked_at INTEGER",
        ):
            try:
                conn.execute(f"ALTER TABLE users ADD COLUMN {col_def}")
            except sqlite3.OperationalError:
                pass  # ستون از قبل وجود دارد

        conn.execute("""
        CREATE TABLE IF NOT EXISTS settings (
            key TEXT PRIMARY KEY,
            value TEXT
        )
        """)

        conn.execute("""
        CREATE TABLE IF NOT EXISTS sales_notify (
            user_id INTEGER NOT NULL,
            kind TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            PRIMARY KEY (user_id, kind)
        )
        """)

        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
        )
        """)

        # بذرکاری اولیه‌ی محصولات یوسی (فقط اگر جدول کاملاً خالی است، تا با تغییرات دستی ادمین تداخل نکند)
        existing = conn.execute("SELECT COUNT(*) AS c FROM uc_products").fetchone()
        if existing and existing["c"] == 0:
            default_uc = [
                ("60 UC", 0.955),
                ("325 UC", 4.85),
                ("660 UC", 9.62),
                ("1800 UC", 23.9),
                ("3850 UC", 47.12),
                ("8100 UC", 94.62),
            ]
            for name, factor in default_uc:
                conn.execute(
                    "INSERT INTO uc_products (name, factor, active) VALUES (?, ?, 1)",
                    (name, factor),
                )

        # دفتر تراکنش کیف پول: هر تغییر موجودی (شارژ، خرید، پورسانت رفرال، ...) یک ردیف دارد
        conn.execute("""
        CREATE TABLE IF NOT EXISTS wallet_transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            kind TEXT NOT NULL,                        -- opening / topup / purchase / 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("""
            INSERT INTO wallet_transactions
                (user_id, kind, amount, referral_amount, balance_after, referral_after, note, created_at)
            SELECT u.user_id, 'opening', COALESCE(u.balance, 0), COALESCE(u.referral_balance, 0),
                   COALESCE(u.balance, 0), COALESCE(u.referral_balance, 0),
                   'مانده‌ی اولیه هنگام فعال‌سازی دفتر تراکنش', CAST(strftime('%s', 'now') AS INTEGER)
            FROM users u
            WHERE (COALESCE(u.balance, 0) != 0 OR COALESCE(u.referral_balance, 0) != 0)
              AND NOT EXISTS (SELECT 1 FROM wallet_transactions w WHERE w.user_id = u.user_id)
        """)
        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_orders_receipt_uid ON orders(receipt_unique_id)")

        # ایندکس‌ها: کارهای دوره‌ای (انقضا/یادآوری) و «سفارش‌های من» با رشد جدول سفارش‌ها
        # کند نشوند (بدون ایندکس، هر بار کل جدول اسکن می‌شود).
        conn.execute("CREATE INDEX IF NOT EXISTS idx_orders_status_created ON orders(status, created_at)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id, id)")


# ---------------- Categories (مجموعه‌ها) ----------------

def add_category(name, duration_days=None):
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO categories (name, active, duration_days) VALUES (?, 1, ?)",
            (name, duration_days),
        )
        return cur.lastrowid


def update_category(category_id, name=None, duration_days=None):
    """فیلدهایی که None نباشند به‌روزرسانی می‌شوند."""
    with get_conn() as conn:
        if name is not None:
            conn.execute("UPDATE categories SET name = ? WHERE id = ?", (name, category_id))
        if duration_days is not None:
            # duration_days == 0 یعنی «یادآوری تمدید غیرفعال» -> در دیتابیس NULL ذخیره می‌شود
            value = duration_days if duration_days > 0 else None
            conn.execute("UPDATE categories SET duration_days = ? WHERE id = ?", (value, category_id))


def list_active_categories():
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM categories WHERE active = 1 ORDER BY id").fetchall()
        return [dict(r) for r in rows]


def list_all_categories():
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM categories ORDER BY id").fetchall()
        return [dict(r) for r in rows]


def get_category(category_id):
    if category_id is None:
        return None
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM categories WHERE id = ?", (category_id,)).fetchone()
        return dict(row) if row else None


def deactivate_category(category_id):
    with get_conn() as conn:
        conn.execute("UPDATE categories SET active = 0 WHERE id = ?", (category_id,))


def activate_category(category_id):
    with get_conn() as conn:
        conn.execute("UPDATE categories SET active = 1 WHERE id = ?", (category_id,))


def delete_category(category_id):
    """حذف کامل و دائمی یک مجموعه. پلن‌های داخل آن حذف نمی‌شوند، فقط بدون‌دسته می‌شوند."""
    with get_conn() as conn:
        conn.execute("UPDATE plans SET category_id = NULL WHERE category_id = ?", (category_id,))
        conn.execute("DELETE FROM categories WHERE id = ?", (category_id,))


def count_active_plans_in_category(category_id):
    with get_conn() as conn:
        if category_id is None:
            row = conn.execute(
                "SELECT COUNT(*) AS c FROM plans WHERE active = 1 AND category_id IS NULL"
            ).fetchone()
        else:
            row = conn.execute(
                "SELECT COUNT(*) AS c FROM plans WHERE active = 1 AND category_id = ?", (category_id,)
            ).fetchone()
        return row["c"] if row else 0


def count_all_plans_in_category(category_id):
    with get_conn() as conn:
        if category_id is None:
            row = conn.execute(
                "SELECT COUNT(*) AS c FROM plans WHERE category_id IS NULL"
            ).fetchone()
        else:
            row = conn.execute(
                "SELECT COUNT(*) AS c FROM plans WHERE category_id = ?", (category_id,)
            ).fetchone()
        return row["c"] if row else 0


# ---------------- Plans ----------------

def add_plan(name, price, description="", category_id=None):
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO plans (name, price, description, active, category_id) VALUES (?, ?, ?, 1, ?)",
            (name, price, description, category_id),
        )
        return cur.lastrowid


def update_plan(plan_id, name=None, price=None, description=None):
    """فیلدهایی که None نباشند به‌روزرسانی می‌شوند."""
    with get_conn() as conn:
        if name is not None:
            conn.execute("UPDATE plans SET name = ? WHERE id = ?", (name, plan_id))
        if price is not None:
            conn.execute("UPDATE plans SET price = ? WHERE id = ?", (price, plan_id))
        if description is not None:
            conn.execute("UPDATE plans SET description = ? WHERE id = ?", (description, plan_id))


def list_active_plans():
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM plans WHERE active = 1 ORDER BY id").fetchall()
        return [dict(r) for r in rows]


def list_active_plans_by_category(category_id):
    with get_conn() as conn:
        if category_id is None:
            rows = conn.execute(
                "SELECT * FROM plans WHERE active = 1 AND category_id IS NULL ORDER BY price, id"
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT * FROM plans WHERE active = 1 AND category_id = ? ORDER BY price, id", (category_id,)
            ).fetchall()
        return [dict(r) for r in rows]


def list_plans_by_category(category_id):
    with get_conn() as conn:
        if category_id is None:
            rows = conn.execute(
                "SELECT * FROM plans WHERE category_id IS NULL ORDER BY id"
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT * FROM plans WHERE category_id = ? ORDER BY id", (category_id,)
            ).fetchall()
        return [dict(r) for r in rows]


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


def deactivate_plan(plan_id):
    with get_conn() as conn:
        conn.execute("UPDATE plans SET active = 0 WHERE id = ?", (plan_id,))


def activate_plan(plan_id):
    with get_conn() as conn:
        conn.execute("UPDATE plans SET active = 1 WHERE id = ?", (plan_id,))


def delete_plan(plan_id):
    """حذف کامل و دائمی پلن از دیتابیس. سفارش‌های قبلی که با این پلن ثبت شده‌اند
    دست‌نخورده می‌مانند، چون نام و قیمت پلن در خود جدول orders هم ذخیره شده."""
    with get_conn() as conn:
        conn.execute("DELETE FROM plans WHERE id = ?", (plan_id,))


def list_all_plans():
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM plans ORDER BY id").fetchall()
        return [dict(r) for r in rows]


# ---------------- UC Products (یوسی پابجی) ----------------

def add_uc_product(name, factor):
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO uc_products (name, factor, active) VALUES (?, ?, 1)",
            (name, factor),
        )
        return cur.lastrowid


def update_uc_factor(uc_id, factor):
    with get_conn() as conn:
        conn.execute("UPDATE uc_products SET factor = ? WHERE id = ?", (factor, uc_id))


def list_active_uc_products():
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM uc_products WHERE active = 1 ORDER BY factor").fetchall()
        return [dict(r) for r in rows]


def list_all_uc_products():
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM uc_products ORDER BY factor").fetchall()
        return [dict(r) for r in rows]


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


def deactivate_uc_product(uc_id):
    with get_conn() as conn:
        conn.execute("UPDATE uc_products SET active = 0 WHERE id = ?", (uc_id,))


def activate_uc_product(uc_id):
    with get_conn() as conn:
        conn.execute("UPDATE uc_products SET active = 1 WHERE id = ?", (uc_id,))


def delete_uc_product(uc_id):
    with get_conn() as conn:
        conn.execute("DELETE FROM uc_products WHERE id = ?", (uc_id,))
        conn.execute("DELETE FROM uc_cart WHERE uc_id = ?", (uc_id,))  # از سبد خرید کاربران هم حذف شود


# ---------------- UC shopping cart ----------------

def cart_get(user_id):
    """آیتم‌های سبد خرید یوسی کاربر: لیست دیکشنری {uc_id, quantity} به ترتیب ضریب (مثل لیست قیمت)."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT c.uc_id AS uc_id, c.quantity AS quantity FROM uc_cart c "
            "LEFT JOIN uc_products p ON p.id = c.uc_id WHERE c.user_id = ? ORDER BY p.factor, c.uc_id",
            (user_id,),
        ).fetchall()
        return [dict(r) for r in rows]


def cart_get_quantity(user_id, uc_id):
    with get_conn() as conn:
        row = conn.execute(
            "SELECT quantity FROM uc_cart WHERE user_id = ? AND uc_id = ?", (user_id, uc_id)
        ).fetchone()
        return row["quantity"] if row else 0


def cart_set_quantity(user_id, uc_id, quantity):
    """تعداد یک آیتم را مستقیماً تنظیم می‌کند؛ صفر یا کمتر یعنی حذف از سبد."""
    now = int(time.time())
    with get_conn() as conn:
        if quantity <= 0:
            conn.execute("DELETE FROM uc_cart WHERE user_id = ? AND uc_id = ?", (user_id, uc_id))
            return
        conn.execute(
            "INSERT INTO uc_cart (user_id, uc_id, quantity, updated_at) VALUES (?, ?, ?, ?) "
            "ON CONFLICT(user_id, uc_id) DO UPDATE SET quantity = excluded.quantity, updated_at = excluded.updated_at",
            (user_id, uc_id, quantity, now),
        )


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


def cart_purge_older_than(ts):
    """سبدهای رهاشده (آخرین تغییر قدیمی‌تر از ts) را پاک می‌کند؛ تعداد ردیف‌های حذف‌شده را برمی‌گرداند."""
    with get_conn() as conn:
        cur = conn.execute("DELETE FROM uc_cart WHERE updated_at < ?", (ts,))
        return cur.rowcount


# ---------------- Discount Codes ----------------

def add_discount_code(code, dtype, value):
    """dtype: 'percent' یا 'amount'"""
    now = int(time.time())
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO discount_codes (code, type, value, active, created_at) VALUES (?, ?, ?, 1, ?)",
            (code.strip().upper(), dtype, value, now),
        )
        return cur.lastrowid


def update_discount_code_value(code_id, value):
    with get_conn() as conn:
        conn.execute("UPDATE discount_codes SET value = ? WHERE id = ?", (value, code_id))


def get_discount_code_by_text(code):
    """جستجوی کد تخفیف بدون توجه به بزرگی/کوچکی حروف."""
    if not code:
        return None
    with get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM discount_codes WHERE code = ?", (code.strip().upper(),)
        ).fetchone()
        return dict(row) if row else None


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


def list_all_discount_codes():
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM discount_codes ORDER BY id DESC").fetchall()
        return [dict(r) for r in rows]


def deactivate_discount_code(code_id):
    with get_conn() as conn:
        conn.execute("UPDATE discount_codes SET active = 0 WHERE id = ?", (code_id,))


def activate_discount_code(code_id):
    with get_conn() as conn:
        conn.execute("UPDATE discount_codes SET active = 1 WHERE id = ?", (code_id,))


def delete_discount_code(code_id):
    with get_conn() as conn:
        conn.execute("DELETE FROM discount_codes WHERE id = ?", (code_id,))


# ---------------- Orders ----------------

def create_order(user_id, username, full_name, plan_id, plan_name, price,
                  original_price=None, discount_code=None, discount_amount=0,
                  order_type="plan", usdt_rate=None):
    now = int(time.time())
    if original_price is None:
        original_price = price
    with get_conn() as conn:
        # هر کاربر در هر لحظه فقط یک سفارش پرداخت‌نشده از هر نوع (پلن / یوسی / شارژ کیف پول) دارد؛
        # سفارش‌های ناتمام قبلی همان نوع خودکار لغو می‌شوند تا رسید به سفارش اشتباهی نچسبد.
        conn.execute(
            "UPDATE orders SET status = 'cancelled', updated_at = ? "
            "WHERE user_id = ? AND status = 'pending_receipt' AND COALESCE(order_type, 'plan') = ?",
            (now, user_id, order_type),
        )
        cur = conn.execute(
            """INSERT INTO orders
               (user_id, username, full_name, plan_id, plan_name, price,
                original_price, discount_code, discount_amount,
                order_type, usdt_rate,
                status, created_at, updated_at)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending_receipt', ?, ?)""",
            (user_id, username, full_name, plan_id, plan_name, price,
             original_price, discount_code, discount_amount,
             order_type, usdt_rate, now, now),
        )
        return cur.lastrowid


def set_order_receipt(order_id, file_id, receipt_type="photo", unique_id=None,
                      status="awaiting_review", kyc_required=0):
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "UPDATE orders SET receipt_file_id = ?, receipt_type = ?, receipt_unique_id = ?, "
            "status = ?, kyc_required = ?, updated_at = ? WHERE id = ?",
            (file_id, receipt_type, unique_id, status, 1 if kyc_required else 0, now, order_id),
        )


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):
    """یک عکس مدرک به سفارش اضافه می‌کند (عکس تکراری نادیده گرفته می‌شود) و تعداد کل را برمی‌گرداند."""
    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 = ?, updated_at = ? WHERE id = ?",
            (json.dumps([list(i) for i in items], ensure_ascii=False), int(time.time()), order_id),
        )
    return len(items)


def find_duplicate_receipt(unique_id, exclude_order_id):
    """اگر همین فایل رسید (file_unique_id) قبلاً برای سفارش دیگری ثبت شده باشد، آن سفارش را برمی‌گرداند."""
    if not unique_id:
        return None
    with get_conn() as conn:
        row = conn.execute(
            "SELECT id, user_id, username, status FROM orders "
            "WHERE receipt_unique_id = ? AND id != ? ORDER BY id LIMIT 1",
            (unique_id, exclude_order_id),
        ).fetchone()
        return dict(row) if row else None


def set_order_pubg_info(order_id, text):
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "UPDATE orders SET pubg_info = ?, updated_at = ? WHERE id = ?",
            (text, now, order_id),
        )


def set_order_status(order_id, status, admin_note=None):
    now = int(time.time())
    with get_conn() as conn:
        approved_at = now if status == "approved" else None
        if admin_note is not None:
            conn.execute(
                "UPDATE orders SET status = ?, admin_note = ?, updated_at = ?, "
                "approved_at = COALESCE(?, approved_at) WHERE id = ?",
                (status, admin_note, now, approved_at, order_id),
            )
        else:
            conn.execute(
                "UPDATE orders SET status = ?, updated_at = ?, approved_at = COALESCE(?, approved_at) WHERE id = ?",
                (status, now, approved_at, order_id),
            )


def set_order_payment_method(order_id, method):
    """method: 'card' یا 'wallet'"""
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "UPDATE orders SET payment_method = ?, updated_at = ? WHERE id = ?",
            (method, now, order_id),
        )


def mark_order_expired(order_id):
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "UPDATE orders SET status = 'expired', updated_at = ? WHERE id = ?",
            (now, order_id),
        )


def claim_order(order_id, from_status, to_status):
    """تغییر وضعیت به‌صورت اتمیک: فقط اگر سفارش هنوز در from_status باشد به to_status می‌رود.
    اگر هم‌زمان دو درخواست (مثلاً دو بار زدن دکمه‌ی پرداخت یا پرداخت + لغو) برسند، فقط یکی True می‌گیرد."""
    now = int(time.time())
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = ?, updated_at = ?, approved_at = COALESCE(?, approved_at) "
            "WHERE id = ? AND status = ?",
            (to_status, now, now if to_status == "approved" else None, order_id, from_status),
        )
        return cur.rowcount > 0


def cancel_pending_order(order_id, user_id):
    """لغو سفارشی که هنوز پرداخت/رسیدی برایش ثبت نشده. اگر سفارش مال همین کاربر نباشد یا
    دیگر در وضعیت «در انتظار پرداخت» نباشد، False برمی‌گرداند."""
    now = int(time.time())
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = 'cancelled', updated_at = ? "
            "WHERE id = ? AND user_id = ? AND status = 'pending_receipt'",
            (now, order_id, user_id),
        )
        return cur.rowcount > 0


def cancel_stale_unpaid_orders(cutoff_ts, order_types=("plan", "wallet_topup")):
    """سفارش‌های پلن/شارژ کیف پول که هنوز رسیدی نفرستاده‌اند و از cutoff_ts قدیمی‌ترند را لغو می‌کند
    (بدون پیام به کاربر). تعداد سفارش‌های لغوشده را برمی‌گرداند."""
    now = int(time.time())
    marks = ",".join("?" for _ in order_types)
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = 'cancelled', updated_at = ? "
            f"WHERE status = 'pending_receipt' AND created_at <= ? AND COALESCE(order_type, 'plan') IN ({marks})",
            (now, cutoff_ts, *order_types),
        )
        return cur.rowcount


def purge_cancelled_orders(older_than_ts):
    """سفارش‌های «لغو شده» که هیچ رسیدی نداشته‌اند (یعنی هرگز پولی جابه‌جا نشده) و قدیمی‌ترند را
    برای سبک ماندن دیتابیس پاک می‌کند. سفارش‌های پرداخت‌شده/رد‌شده/منقضی‌شده هرگز حذف نمی‌شوند."""
    with get_conn() as conn:
        cur = conn.execute(
            "DELETE FROM orders WHERE status = 'cancelled' AND receipt_file_id IS NULL AND updated_at <= ?",
            (older_than_ts,),
        )
        return cur.rowcount


def list_stale_pending_uc_orders(cutoff_ts):
    """سفارش‌های یوسی که هنوز در انتظار رسید هستند (یعنی هنوز عکس رسید نفرستاده‌اند)
    و از زمان ثبتشان بیشتر از مهلت مجاز (cutoff_ts) گذشته است."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE order_type = 'uc' AND status = 'pending_receipt' AND created_at <= ? "
            "ORDER BY id",
            (cutoff_ts,),
        ).fetchall()
        return [dict(r) for r in rows]


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_open_order_for_user(user_id):
    """آخرین سفارش کاربر که هنوز تحویل داده نشده یا رد نشده (یا تازه منقضی شده، تا پیام مناسب نشانش بدهیم)."""
    with get_conn() as conn:
        row = conn.execute(
            """SELECT * FROM orders
               WHERE user_id = ? AND status IN ('pending_receipt', 'awaiting_kyc', 'awaiting_review', 'expired')
               ORDER BY id DESC LIMIT 1""",
            (user_id,),
        ).fetchone()
        return dict(row) if row else None


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


def list_unpaid_orders(limit=30):
    """سفارش‌های در انتظار پرداخت/رسید (هنوز رسیدی نفرستاده‌اند)؛ جدیدترین‌ها اول."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE status = 'pending_receipt' ORDER BY id DESC LIMIT ?", (limit,)
        ).fetchall()
        total = conn.execute("SELECT COUNT(*) AS c FROM orders WHERE status = 'pending_receipt'").fetchone()["c"]
        return [dict(r) for r in rows], total


def list_approved_awaiting_delivery():
    """سفارش‌هایی که تایید شده‌اند ولی هنوز فایل کانفیگ برایشان ارسال نشده."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE status = 'approved' ORDER BY id"
        ).fetchall()
        return [dict(r) for r in rows]


# ---------------- Admin temporary state (e.g. waiting to upload a config file, or plan wizard) ----------------

def set_admin_state(admin_id, action, order_id=None, data=None):
    """
    data: دیکشنری پایتون که به‌صورت JSON ذخیره می‌شود (برای نگهداری موقت مراحل ویزارد افزودن پلن).
    """
    data_json = json.dumps(data, ensure_ascii=False) if data is not None else None
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO admin_state (admin_id, action, order_id, data) VALUES (?, ?, ?, ?) "
            "ON CONFLICT(admin_id) DO UPDATE SET action = excluded.action, "
            "order_id = excluded.order_id, data = excluded.data",
            (admin_id, action, order_id, data_json),
        )


def get_admin_state(admin_id):
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM admin_state WHERE admin_id = ?", (admin_id,)).fetchone()
        if not row:
            return None
        result = dict(row)
        result["data"] = json.loads(result["data"]) if result.get("data") else {}
        return result


def clear_admin_state(admin_id):
    with get_conn() as conn:
        conn.execute("DELETE FROM admin_state WHERE admin_id = ?", (admin_id,))


# ---------------- Users (برای پیام همگانی / Broadcast) ----------------

def upsert_user(user_id, username=None, full_name=None):
    """هر کاربری که با ربات تعامل داشته (پیام یا دکمه) را ثبت یا به‌روزرسانی می‌کند."""
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO users (user_id, username, full_name, first_seen, last_seen) "
            "VALUES (?, ?, ?, ?, ?) "
            "ON CONFLICT(user_id) DO UPDATE SET "
            "username = excluded.username, full_name = excluded.full_name, last_seen = excluded.last_seen, "
            "blocked = 0",
            (user_id, username, full_name, now, now),
        )


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


def mark_user_blocked(user_id):
    """کاربری که ربات را بلاک کرده را ثبت می‌کند تا از پیام‌های همگانی بعدی کنار گذاشته شود.
    با اولین پیام/کلیک بعدی خودِ کاربر (upsert_user) دوباره فعال می‌شود. True یعنی تازه ثبت شد."""
    try:
        uid = int(user_id)
    except (TypeError, ValueError):
        return False
    if uid <= 0:
        return False
    with get_conn() as conn:
        cur = conn.execute(
            "UPDATE users SET blocked = 1, blocked_at = ? WHERE user_id = ? AND COALESCE(blocked, 0) = 0",
            (int(time.time()), uid),
        )
        return cur.rowcount > 0


def count_blocked_users():
    with get_conn() as conn:
        row = conn.execute("SELECT COUNT(*) AS c FROM users WHERE COALESCE(blocked, 0) = 1").fetchone()
        return row["c"] if row else 0


BROADCAST_SEGMENTS = ("all", "gaming", "uc", "never")


def list_user_ids_by_segment(segment):
    """آیدی کاربران یک دسته‌ی پیام همگانی:
    all    = همه‌ی کاربرانی که ربات را استارت کرده‌اند
    gaming = خریداران کانفیگ (سفارش پلنِ تایید/تحویل‌شده)
    uc     = خریداران یوسی (سفارش یوسیِ تایید/تحویل‌شده)
    never  = استارت‌کرده‌ها که تا حالا هیچ خریدی (کانفیگ یا یوسی) نداشته‌اند
    شارژ کیف پول «خرید» حساب نمی‌شود."""
    if segment not in BROADCAST_SEGMENTS:
        return []
    with get_conn() as conn:
        if segment == "all":
            rows = conn.execute(
                "SELECT user_id FROM users WHERE COALESCE(blocked, 0) = 0 ORDER BY user_id"
            ).fetchall()
        elif segment in ("gaming", "uc"):
            order_type = "plan" if segment == "gaming" else "uc"
            rows = conn.execute(
                "SELECT DISTINCT user_id FROM orders "
                "WHERE COALESCE(order_type, 'plan') = ? AND status IN ('approved', 'delivered') "
                "AND user_id NOT IN (SELECT user_id FROM users WHERE COALESCE(blocked, 0) = 1) "
                "ORDER BY user_id",
                (order_type,),
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT u.user_id FROM users u WHERE COALESCE(u.blocked, 0) = 0 AND NOT EXISTS ("
                "  SELECT 1 FROM orders o WHERE o.user_id = u.user_id "
                "  AND COALESCE(o.order_type, 'plan') IN ('plan', 'uc') "
                "  AND o.status IN ('approved', 'delivered')"
                ") ORDER BY u.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


# ---------------- Wallet (کیف پول کاربران) ----------------

def _log_wallet_tx(conn, user_id, kind, amount=0, referral_amount=0, order_id=None, note=None):
    """یک ردیف در دفتر تراکنش می‌نویسد. باید داخل همان اتصالی صدا زده شود که موجودی را تغییر داده
    (تا تغییر موجودی و ثبت در دفتر با هم commit شوند) و بعد از UPDATE موجودی."""
    row = conn.execute(
        "SELECT balance, referral_balance 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["balance"] or 0) if row else 0, (row["referral_balance"] or 0) if row else 0,
         order_id, note, int(time.time())),
    )


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 get_wallet_ledger_summary(user_id):
    """جمع دفتر تراکنش در برابر موجودی فعلی؛ اگر برابر نباشند یعنی تغییری بدون ثبت در دفتر رخ داده."""
    with get_conn() as conn:
        led = conn.execute(
            "SELECT COALESCE(SUM(amount), 0) AS a, COALESCE(SUM(referral_amount), 0) AS r, COUNT(*) AS n "
            "FROM wallet_transactions WHERE user_id = ?", (user_id,)
        ).fetchone()
        cur = conn.execute(
            "SELECT COALESCE(balance, 0) AS b, COALESCE(referral_balance, 0) AS r FROM users WHERE user_id = ?",
            (user_id,),
        ).fetchone()
        return {
            "ledger_balance": led["a"], "ledger_referral": led["r"], "count": led["n"],
            "balance": cur["b"] if cur else 0, "referral": cur["r"] if cur else 0,
        }


def get_user_row(user_id):
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM users WHERE user_id = ?", (int(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 last_seen DESC LIMIT 1", (username,)
        ).fetchone()
        return dict(row) if row else None


def admin_adjust_balance(user_id, delta, note=None, order_id=None):
    """تعدیل دستی موجودی توسط ادمین (delta مثبت = افزایش، منفی = کاهش) در یک تراکنش اتمیک.
    اگر کاهش باعث منفی‌شدن موجودی شود چیزی تغییر نمی‌کند و None برمی‌گردد؛ وگرنه موجودی جدید."""
    delta = int(delta)
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO users (user_id, first_seen, last_seen, balance) VALUES (?, ?, ?, 0) "
            "ON CONFLICT(user_id) DO NOTHING",
            (user_id, now, now),
        )
        row = conn.execute("SELECT COALESCE(balance, 0) AS b FROM users WHERE user_id = ?", (user_id,)).fetchone()
        if row["b"] + delta < 0:
            return None
        conn.execute("UPDATE users SET balance = COALESCE(balance, 0) + ? WHERE user_id = ?", (delta, user_id))
        if delta:
            _log_wallet_tx(conn, user_id, "adjust", amount=delta, order_id=order_id, note=note)
        return row["b"] + delta


def list_orders_with_receipt(user_id, limit=5, offset=0):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM orders WHERE user_id = ? AND receipt_file_id IS NOT NULL "
            "ORDER BY id DESC LIMIT ? OFFSET ?",
            (int(user_id), limit, offset),
        ).fetchall()
        total = conn.execute(
            "SELECT COUNT(*) AS c FROM orders WHERE user_id = ? AND receipt_file_id IS NOT NULL", (int(user_id),)
        ).fetchone()["c"]
        return [dict(r) for r in rows], total


def has_topup_reversal(order_id):
    """آیا مبلغِ این شارژ قبلاً با «کاهش دستی» برگردانده شده است؟"""
    with get_conn() as conn:
        row = conn.execute(
            "SELECT 1 FROM wallet_transactions WHERE order_id = ? AND kind = 'adjust' AND amount < 0", (order_id,)
        ).fetchone()
        return bool(row)


def get_user_balance(user_id):
    """موجودی کیف پول کاربر را برمی‌گرداند (اگر کاربر هنوز ثبت نشده باشد، صفر)."""
    with get_conn() as conn:
        row = conn.execute("SELECT balance FROM users WHERE user_id = ?", (user_id,)).fetchone()
        return row["balance"] if row and row["balance"] is not None else 0


def add_user_balance(user_id, amount, kind="adjust", order_id=None, note=None):
    """مبلغ داده‌شده را به موجودی کیف پول کاربر اضافه می‌کند (برای کسر، amount منفی بدهید)
    و موجودی جدید را برمی‌گرداند. اگر کاربر هنوز در جدول users ثبت نشده باشد، ابتدا ساخته می‌شود.
    هر تغییر با kind/order_id در دفتر تراکنش (wallet_transactions) هم ثبت می‌شود."""
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO users (user_id, first_seen, last_seen, balance) VALUES (?, ?, ?, 0) "
            "ON CONFLICT(user_id) DO NOTHING",
            (user_id, now, now),
        )
        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)
        row = conn.execute("SELECT balance FROM users WHERE user_id = ?", (user_id,)).fetchone()
        return row["balance"] if row else amount


# ---------------- Settings (تنظیمات کلی، مثل درصد رفرال) ----------------

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_referral_percent():
    """درصد پورسانت رفرال؛ اگر ادمین تنظیم نکرده باشد یا 0 باشد، سیستم رفرال غیرفعال است."""
    val = get_setting("referral_percent", "0")
    try:
        return float(val)
    except (TypeError, ValueError):
        return 0.0


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
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO users (user_id, first_seen, last_seen) VALUES (?, ?, ?) "
            "ON CONFLICT(user_id) DO NOTHING",
            (user_id, now, now),
        )
        row = conn.execute("SELECT referred_by FROM users WHERE user_id = ?", (user_id,)).fetchone()
        if row and row["referred_by"]:
            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 get_referral_balance(user_id):
    with get_conn() as conn:
        row = conn.execute("SELECT referral_balance FROM users WHERE user_id = ?", (user_id,)).fetchone()
        return row["referral_balance"] if row and row["referral_balance"] is not None else 0


def add_referral_balance(user_id, amount, order_id=None):
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO users (user_id, first_seen, last_seen, referral_balance) VALUES (?, ?, ?, 0) "
            "ON CONFLICT(user_id) DO NOTHING",
            (user_id, now, now),
        )
        conn.execute(
            "UPDATE users SET referral_balance = COALESCE(referral_balance, 0) + ? WHERE user_id = ?",
            (amount, user_id),
        )
        if amount:
            _log_wallet_tx(conn, user_id, "referral_bonus", referral_amount=amount, order_id=order_id)
        row = conn.execute("SELECT referral_balance FROM users WHERE user_id = ?", (user_id,)).fetchone()
        return row["referral_balance"] if row else amount


def add_topup_bonus(user_id, amount, order_id=None):
    """هدیه‌ی شارژ کیف پول: به «اعتبار محدود» (همان ستون referral_balance) اضافه می‌شود؛
    فقط در خرید پلن/کانفیگ خرج می‌شود و برای خرید یوسی قابل استفاده نیست."""
    now = int(time.time())
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO users (user_id, first_seen, last_seen, referral_balance) VALUES (?, ?, ?, 0) "
            "ON CONFLICT(user_id) DO NOTHING",
            (user_id, now, now),
        )
        conn.execute(
            "UPDATE users SET referral_balance = COALESCE(referral_balance, 0) + ? WHERE user_id = ?",
            (amount, user_id),
        )
        if amount:
            _log_wallet_tx(conn, user_id, "topup_bonus", referral_amount=amount, order_id=order_id,
                           note="هدیه‌ی شارژ کیف پول")


def reverse_topup_bonus(order_id):
    """وقتی شارژِ یک سفارش (اشتباهی) برگردانده می‌شود، هدیه‌ی همان شارژ هم کسر می‌شود (حداکثر تا
    اعتبار باقی‌مانده‌ی کاربر؛ اگر خرجش کرده باشد بیشتر از آن کسر نمی‌شود). مبلغ کسرشده را برمی‌گرداند."""
    with get_conn() as conn:
        bonus = conn.execute(
            "SELECT user_id, referral_amount AS r FROM wallet_transactions WHERE order_id = ? AND kind = 'topup_bonus'",
            (order_id,),
        ).fetchone()
        if not bonus:
            return 0
        done = conn.execute(
            "SELECT 1 FROM wallet_transactions WHERE order_id = ? AND kind = 'topup_bonus_reversal'", (order_id,)
        ).fetchone()
        if done:
            return 0
        uid = bonus["user_id"]
        cur = conn.execute("SELECT COALESCE(referral_balance, 0) AS r FROM users WHERE user_id = ?", (uid,)).fetchone()
        take = min(bonus["r"] or 0, cur["r"] if cur else 0)
        if take <= 0:
            return 0
        conn.execute("UPDATE users SET referral_balance = referral_balance - ? WHERE user_id = ?", (take, uid))
        _log_wallet_tx(conn, uid, "topup_bonus_reversal", referral_amount=-take, order_id=order_id,
                       note="کسر هدیه‌ی شارژِ برگشت‌خورده")
        return take


def refund_order_to_wallet(user_id, order_id, price, note=None):
    """برگشت وجه یک سفارش لغوشده: سهمی از سفارش که با «اعتبار محدود» (هدیه/رفرال) پرداخت شده بود به همان
    اعتبار محدود برمی‌گردد و بقیه به موجودی آزاد؛ تا هدیه‌ی فقط-کانفیگ با لغو و برگشت تبدیل به پول آزاد
    (قابل خرج در یوسی) نشود. برمی‌گرداند: موجودی آزاد جدید."""
    with get_conn() as conn:
        row = conn.execute(
            "SELECT COALESCE(SUM(-referral_amount), 0) AS r FROM wallet_transactions "
            "WHERE order_id = ? AND kind = 'purchase'", (order_id,)
        ).fetchone()
        from_ref = min(max(row["r"] or 0, 0), price)
        free = price - from_ref
        conn.execute(
            "UPDATE users SET balance = COALESCE(balance, 0) + ?, referral_balance = COALESCE(referral_balance, 0) + ? "
            "WHERE user_id = ?", (free, from_ref, user_id),
        )
        _log_wallet_tx(conn, user_id, "refund", amount=free, referral_amount=from_ref, order_id=order_id, note=note)
        new = conn.execute("SELECT balance FROM users WHERE user_id = ?", (user_id,)).fetchone()
        return new["balance"] if new else free


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 spend_for_plan_purchase(user_id, amount, order_id=None):
    """برای خرید پلن/کانفیگ: ابتدا از اعتبار رفرال (محدود) و باقی از موجودی آزاد کیف پول کسر می‌شود.
    برمی‌گرداند: (موجودی جدید کیف پول, اعتبار رفرال جدید)."""
    with get_conn() as conn:
        row = conn.execute(
            "SELECT balance, referral_balance FROM users WHERE user_id = ?", (user_id,)
        ).fetchone()
        balance = (row["balance"] or 0) if row else 0
        referral = (row["referral_balance"] or 0) if row else 0
        from_referral = min(referral, amount)
        from_balance = amount - from_referral
        conn.execute(
            "UPDATE users SET balance = balance - ?, referral_balance = referral_balance - ? WHERE user_id = ?",
            (from_balance, from_referral, user_id),
        )
        _log_wallet_tx(conn, user_id, "purchase", amount=-from_balance, referral_amount=-from_referral,
                       order_id=order_id)
        new_row = conn.execute(
            "SELECT balance, referral_balance FROM users WHERE user_id = ?", (user_id,)
        ).fetchone()
        return new_row["balance"], new_row["referral_balance"]


def get_combined_plan_balance(user_id):
    """موجودی قابل استفاده برای خرید پلن/کانفیگ (کیف پول آزاد + اعتبار رفرال محدود)."""
    with get_conn() as conn:
        row = conn.execute(
            "SELECT balance, referral_balance FROM users WHERE user_id = ?", (user_id,)
        ).fetchone()
        if not row:
            return 0
        return (row["balance"] or 0) + (row["referral_balance"] or 0)


def mark_order_referral_credited(order_id):
    with get_conn() as conn:
        conn.execute("UPDATE orders SET referral_credited = 1 WHERE id = ?", (order_id,))


# ---------------- تاریخچه‌ی سفارش‌ها ----------------

def list_orders_for_user(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_orders_for_user_by_type(user_id, order_type, limit=10, statuses=None):
    """سفارش‌های کاربر از یک نوع مشخص ('plan' یا 'uc'). سفارش‌های قدیمی بدون نوع، پلن حساب می‌شوند.
    اگر statuses (لیست/تاپل وضعیت‌ها) داده شود، فقط سفارش‌هایی با آن وضعیت‌ها برگردانده می‌شوند."""
    sql = "SELECT * FROM orders WHERE user_id = ? AND COALESCE(order_type, 'plan') = ?"
    params = [user_id, order_type]
    if statuses:
        sql += " AND status IN (%s)" % ",".join("?" for _ in statuses)
        params += list(statuses)
    sql += " ORDER BY id DESC LIMIT ?"
    params.append(limit)
    with get_conn() as conn:
        rows = conn.execute(sql, params).fetchall()
        return [dict(r) for r in rows]


# ---------------- یادآوری تمدید اشتراک ----------------

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


def list_orders_due_for_renewal_reminder(now_ts, window_seconds):
    """سفارش‌های پلنی که تحویل داده شده‌اند، تاریخ انقضایشان مشخص است، هنوز یادآوری نگرفته‌اند
    و در بازه‌ی [الان, الان+window_seconds] منقضی می‌شوند."""
    with get_conn() as conn:
        rows = conn.execute(
            """SELECT * FROM orders
               WHERE order_type = 'plan' AND status = 'delivered'
                 AND expires_at IS NOT NULL AND renewal_reminded = 0
                 AND expires_at <= ?""",
            (now_ts + window_seconds,),
        ).fetchall()
        return [dict(r) for r in rows]


def mark_renewal_reminded(order_id):
    with get_conn() as conn:
        conn.execute("UPDATE orders SET renewal_reminded = 1 WHERE id = ?", (order_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 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 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]


_REVIEW_TABLE_READY = False


def _ensure_review_cards_table(conn):
    global _REVIEW_TABLE_READY
    conn.execute("""
        CREATE TABLE IF NOT EXISTS order_review_cards (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            admin_id INTEGER NOT NULL,
            message_id INTEGER NOT NULL
        )
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_review_cards_order ON order_review_cards(order_id)")
    if _REVIEW_TABLE_READY:
        return
    # مهاجرت: متن اصلی کارت و نوع پیام (متن یا کپشن عکس/فایل) هم نگه داشته می‌شود تا بشود کارت را ویرایش کرد
    for col_def in (
        "kind TEXT DEFAULT 'text'",        # text = پیام متنی، caption = عکس/فایل با کپشن
        "base_text TEXT DEFAULT ''",       # متن/کپشن اصلی کارت (بدون خط وضعیت)
        "status_text TEXT DEFAULT ''",     # خط‌های وضعیت اضافه‌شده زیر کارت (تایید شد توسط ...)
        "created_at INTEGER DEFAULT 0",
    ):
        try:
            conn.execute(f"ALTER TABLE order_review_cards ADD COLUMN {col_def}")
        except sqlite3.OperationalError:
            pass  # ستون از قبل وجود دارد
    _REVIEW_TABLE_READY = True


def add_review_card(order_id, admin_id, message_id, kind="text", base_text=""):
    """پیام دارای دکمه‌ی تایید/رد یک سفارش را که برای یک ادمین فرستاده شد ثبت می‌کند
    (به‌همراه متن اصلی‌اش تا بعد از تصمیم بشود همان کارت را ویرایش کرد)."""
    with get_conn() as conn:
        _ensure_review_cards_table(conn)
        conn.execute(
            "INSERT INTO order_review_cards (order_id, admin_id, message_id, kind, base_text, status_text, created_at) "
            "VALUES (?, ?, ?, ?, ?, '', ?)",
            (order_id, admin_id, message_id, kind, base_text or "", int(time.time())),
        )


def list_review_cards(order_id):
    """کارت‌های ثبت‌شده‌ی یک سفارش (بدون پاک‌کردن)."""
    with get_conn() as conn:
        _ensure_review_cards_table(conn)
        rows = conn.execute(
            "SELECT admin_id, message_id, kind, base_text, status_text FROM order_review_cards "
            "WHERE order_id = ? ORDER BY id", (order_id,)
        ).fetchall()
        return [dict(r) for r in rows]


def append_review_status(order_id, line):
    """یک خط وضعیت به همه‌ی کارت‌های این سفارش اضافه می‌کند و تعداد کارت‌ها را برمی‌گرداند."""
    with get_conn() as conn:
        _ensure_review_cards_table(conn)
        cur = conn.execute(
            "UPDATE order_review_cards SET status_text = "
            "CASE WHEN COALESCE(status_text, '') = '' THEN ? ELSE status_text || char(10) || ? END "
            "WHERE order_id = ?",
            (line, line, order_id),
        )
        return cur.rowcount


def delete_review_cards(order_id):
    with get_conn() as conn:
        _ensure_review_cards_table(conn)
        conn.execute("DELETE FROM order_review_cards WHERE order_id = ?", (order_id,))


def purge_review_cards_older_than(ts):
    """کارت‌های قدیمی (سفارش‌هایی که تکلیفشان مدت‌هاست روشن شده) را از جدول پاک می‌کند."""
    with get_conn() as conn:
        _ensure_review_cards_table(conn)
        cur = conn.execute("DELETE FROM order_review_cards WHERE COALESCE(created_at, 0) < ?", (ts,))
        return cur.rowcount


def pop_review_cards(order_id):
    """همه‌ی پیام‌های ثبت‌شده‌ی تایید/رد این سفارش را برمی‌گرداند و از جدول پاک می‌کند."""
    with get_conn() as conn:
        _ensure_review_cards_table(conn)
        rows = conn.execute(
            "SELECT admin_id, message_id FROM order_review_cards WHERE order_id = ?", (order_id,)
        ).fetchall()
        conn.execute("DELETE FROM order_review_cards WHERE order_id = ?", (order_id,))
        return [(r["admin_id"], r["message_id"]) for r in rows]


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 get_wallet_transaction(tx_id, user_id):
    """یک ردیف دفتر کیف پول؛ فقط اگر متعلق به همان کاربر باشد."""
    with get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM wallet_transactions WHERE id = ? AND user_id = ?", (tx_id, user_id)
        ).fetchone()
        return dict(row) if row else None


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 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' ORDER BY updated_at DESC"
        ).fetchall()
        return [dict(r) for r in rows]


# ---------------- محدودیت نرخ درخواست (rate limit) ----------------

def check_and_update_rate_limit(user_id, min_interval_seconds):
    """اگر کمتر از min_interval_seconds از آخرین اکشن این کاربر گذشته باشد، False برمی‌گرداند
    (یعنی درخواست باید نادیده گرفته شود). در غیر این صورت زمان را به‌روزرسانی و True برمی‌گرداند."""
    now = time.time()
    with get_conn() as conn:
        row = conn.execute("SELECT last_action_ts FROM users WHERE user_id = ?", (user_id,)).fetchone()
        last_ts = row["last_action_ts"] if row and row["last_action_ts"] else 0
        if (now - last_ts) < min_interval_seconds:
            return False
        conn.execute(
            "INSERT INTO users (user_id, first_seen, last_seen, last_action_ts) VALUES (?, ?, ?, ?) "
            "ON CONFLICT(user_id) DO UPDATE SET last_action_ts = excluded.last_action_ts",
            (user_id, int(now), int(now), now),
        )
        return True


# ---------------- گزارش فروش ----------------

_ORDER_MSG_FIELDS = ("wait_msg_id", "recv_msg_id", "pay_msg_id")


def set_order_msg(order_id, field, message_id):
    if field not in _ORDER_MSG_FIELDS:
        raise ValueError(field)
    with get_conn() as conn:
        conn.execute(f"UPDATE orders SET {field} = ? WHERE id = ?", (message_id, order_id))


def pop_order_msg(order_id, field):
    """شناسه‌ی پیام ذخیره‌شده را برمی‌گرداند و پاک می‌کند (فقط یک‌بار)."""
    if field not in _ORDER_MSG_FIELDS:
        raise ValueError(field)
    with get_conn() as conn:
        row = conn.execute(f"SELECT {field} AS m FROM orders WHERE id = ?", (order_id,)).fetchone()
        mid = row["m"] if row else None
        if mid:
            conn.execute(f"UPDATE orders SET {field} = NULL WHERE id = ?", (order_id,))
        return mid


def set_order_wait_msg(order_id, message_id):
    with get_conn() as conn:
        conn.execute("UPDATE orders SET wait_msg_id = ? WHERE id = ?", (message_id, order_id))


def pop_order_wait_msg(order_id):
    """شناسه‌ی پیام ایموجی انتظار را برمی‌گرداند و پاک می‌کند (فقط یک‌بار)."""
    with get_conn() as conn:
        row = conn.execute("SELECT wait_msg_id FROM orders WHERE id = ?", (order_id,)).fetchone()
        mid = row["wait_msg_id"] if row else None
        if mid:
            conn.execute("UPDATE orders SET wait_msg_id = NULL WHERE id = ?", (order_id,))
        return mid


def get_sales_stats(start_ts, end_ts):
    """آمار فروش‌های تاییدشده (وضعیت approved یا delivered) در بازه‌ی زمانی داده‌شده، بر اساس زمان تایید.
    سفارش‌های رد/لغوشده/در انتظار حساب نمی‌شوند. شارژ کیف پول فروش نیست و جداگانه برگردانده می‌شود
    (وگرنه وقتی کاربر با همان موجودی خرید کند، پول دو بار شمرده می‌شد)."""
    when = "COALESCE(approved_at, updated_at) BETWEEN ? AND ?"
    sale = f"status IN ('approved', 'delivered') AND COALESCE(order_type, 'plan') != 'wallet_topup' AND {when}"
    with get_conn() as conn:
        row = conn.execute(
            f"SELECT COUNT(*) AS cnt, COALESCE(SUM(price), 0) AS total FROM orders WHERE {sale}",
            (start_ts, end_ts),
        ).fetchone()
        by_type = conn.execute(
            f"SELECT COALESCE(order_type, 'plan') AS order_type, COUNT(*) AS cnt, COALESCE(SUM(price), 0) AS total "
            f"FROM orders WHERE {sale} GROUP BY COALESCE(order_type, 'plan')",
            (start_ts, end_ts),
        ).fetchall()
        top_plan = conn.execute(
            f"SELECT plan_name, COUNT(*) AS cnt FROM orders WHERE {sale} AND COALESCE(order_type, 'plan') = 'plan' "
            f"GROUP BY plan_name ORDER BY cnt DESC LIMIT 1",
            (start_ts, end_ts),
        ).fetchone()
        topup = conn.execute(
            "SELECT COUNT(*) AS cnt, COALESCE(SUM(price), 0) AS total FROM orders "
            "WHERE order_type = 'wallet_topup' AND status IN ('approved', 'delivered') "
            "AND COALESCE(approved_at, updated_at) BETWEEN ? AND ?",
            (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_plan": dict(top_plan) if top_plan else None,
            "topup_count": topup["cnt"] if topup else 0,
            "topup_total": topup["total"] if topup else 0,
        }


# ---------------------------------------------------------------------------
# لیست «وقتی فروش شروع شد خبرم کن» (به‌ازای هر دسته: gaming / uc)
# ---------------------------------------------------------------------------

def add_sales_notify(user_id, kind):
    with get_conn() as conn:
        conn.execute(
            "INSERT OR IGNORE INTO sales_notify (user_id, kind, created_at) VALUES (?, ?, ?)",
            (int(user_id), kind, int(time.time())),
        )


def has_sales_notify(user_id, kind):
    with get_conn() as conn:
        row = conn.execute(
            "SELECT 1 FROM sales_notify WHERE user_id = ? AND kind = ?", (int(user_id), kind)
        ).fetchone()
        return bool(row)


def pop_sales_notify(kind):
    """همه‌ی کاربرانِ منتظر این دسته را برمی‌گرداند و از لیست پاک می‌کند."""
    with get_conn() as conn:
        rows = conn.execute("SELECT user_id FROM sales_notify WHERE kind = ?", (kind,)).fetchall()
        conn.execute("DELETE FROM sales_notify WHERE kind = ?", (kind,))
        return [r["user_id"] for r in rows]



# ---------------- فایل‌های تحویل‌شده به مشتری ----------------

def _ensure_delivered_files_table(conn):
    conn.execute("""
        CREATE TABLE IF NOT EXISTS delivered_files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            file_name TEXT,
            file_type TEXT,
            file_id TEXT NOT NULL,
            sent_at INTEGER
        )
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_delivered_files_user ON delivered_files(user_id)")


def log_delivered_file(order_id, user_id, file_name, file_type, file_id):
    with get_conn() as conn:
        _ensure_delivered_files_table(conn)
        conn.execute(
            "INSERT INTO delivered_files (order_id, user_id, file_name, file_type, file_id, sent_at) "
            "VALUES (?, ?, ?, ?, ?, ?)",
            (order_id, user_id, file_name, file_type, file_id, int(time.time())),
        )


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