import sqlite3, json, random, string, datetime
from contextlib import contextmanager
from config import DB_PATH


@contextmanager
def db():
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(DB_PATH)
    con.row_factory = sqlite3.Row
    con.execute('PRAGMA foreign_keys=ON')
    con.execute('PRAGMA journal_mode=WAL')
    try:
        yield con
        con.commit()
    except Exception:
        con.rollback(); raise
    finally:
        con.close()


def _col_exists(c, table, col):
    return any(r['name'] == col for r in c.execute(f'PRAGMA table_info({table})').fetchall())


def _migrate_categories_table(c):
    """دسته‌بندی‌ها دیگر نباید نام‌شان سراسری یکتا باشد (چون زیردسته‌های هم‌نام زیر والدهای
    مختلف مجازند، مثلاً «فالوور» هم زیر تلگرام و هم زیر اینستاگرام). این تابع جدول قدیمی
    (با UNIQUE(name)) را در صورت وجود، بدون از دست رفتن داده، به شکل جدید بازسازی می‌کند."""
    exists = c.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='categories'").fetchone()
    if not exists:
        return
    idx = c.execute('PRAGMA index_list(categories)').fetchall()
    if not any(row['unique'] for row in idx):
        return  # از قبل مهاجرت شده یا هیچ محدودیت یکتایی قدیمی‌ای ندارد
    has_parent = _col_exists(c, 'categories', 'parent_id')
    c.execute('''CREATE TABLE categories_new(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        parent_id INTEGER,
        active INTEGER DEFAULT 1
    )''')
    if has_parent:
        c.execute('INSERT INTO categories_new(id,name,parent_id,active) SELECT id,name,parent_id,active FROM categories')
    else:
        c.execute('INSERT INTO categories_new(id,name,active) SELECT id,name,active FROM categories')
    c.execute('DROP TABLE categories')
    c.execute('ALTER TABLE categories_new RENAME TO categories')


def today_str():
    return datetime.date.today().isoformat()


def init_db():
    with db() as c:
        c.execute('PRAGMA foreign_keys=OFF')  # needed only for the categories rebuild below
        _migrate_categories_table(c)
        c.executescript('''
        CREATE TABLE IF NOT EXISTS settings(key TEXT PRIMARY KEY, value TEXT NOT NULL);
        CREATE TABLE IF NOT EXISTS users(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            telegram_id INTEGER UNIQUE NOT NULL,
            username TEXT, first_name TEXT, last_name TEXT,
            balance REAL DEFAULT 0,
            banned INTEGER DEFAULT 0,
            verified INTEGER DEFAULT 0,
            referrer_tid INTEGER,
            last_challenge_date TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS categories(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            parent_id INTEGER,
            active INTEGER DEFAULT 1
        );
        CREATE TABLE IF NOT EXISTS products(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            service_id TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL,
            category_id INTEGER,
            rate REAL DEFAULT 0,
            min_qty INTEGER DEFAULT 1,
            max_qty INTEGER DEFAULT 1,
            type TEXT,
            description TEXT,
            active INTEGER DEFAULT 1,
            manual INTEGER DEFAULT 0,
            raw_json TEXT,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(category_id) REFERENCES categories(id)
        );
        CREATE TABLE IF NOT EXISTS orders(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            display_no INTEGER,
            user_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            link TEXT, quantity INTEGER, total REAL,
            status TEXT DEFAULT 'pending',
            provider_order_id TEXT,
            note TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(user_id) REFERENCES users(id),
            FOREIGN KEY(product_id) REFERENCES products(id)
        );
        CREATE TABLE IF NOT EXISTS charges(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            amount REAL NOT NULL,
            method TEXT,
            receipt_text TEXT,
            receipt_file_id TEXT,
            status TEXT DEFAULT 'pending',
            admin_note TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(user_id) REFERENCES users(id)
        );
        CREATE TABLE IF NOT EXISTS payment_methods(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            details TEXT NOT NULL,
            active INTEGER DEFAULT 1,
            sort_order INTEGER DEFAULT 0
        );
        CREATE TABLE IF NOT EXISTS channels(id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT UNIQUE, active INTEGER DEFAULT 1);
        CREATE TABLE IF NOT EXISTS gift_codes(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code TEXT UNIQUE NOT NULL,
            amount REAL NOT NULL,
            capacity INTEGER NOT NULL,
            used_count INTEGER DEFAULT 0,
            active INTEGER DEFAULT 1,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS gift_redemptions(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            redeemed_at TEXT DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(code_id, user_id)
        );
        CREATE TABLE IF NOT EXISTS verifications(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            full_name TEXT, phone TEXT, national_id TEXT,
            status TEXT DEFAULT 'pending',
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(user_id) REFERENCES users(id)
        );
        ''')
        # --- migration safety net for DBs created by older schema versions ---
        if not _col_exists(c, 'users', 'balance'):
            c.execute('ALTER TABLE users ADD COLUMN balance REAL DEFAULT 0')
        if not _col_exists(c, 'products', 'manual'):
            c.execute('ALTER TABLE products ADD COLUMN manual INTEGER DEFAULT 0')
        if not _col_exists(c, 'orders', 'note'):
            c.execute('ALTER TABLE orders ADD COLUMN note TEXT')
        if not _col_exists(c, 'orders', 'display_no'):
            c.execute('ALTER TABLE orders ADD COLUMN display_no INTEGER')
        if not _col_exists(c, 'users', 'verified'):
            c.execute('ALTER TABLE users ADD COLUMN verified INTEGER DEFAULT 0')
        if not _col_exists(c, 'users', 'referrer_tid'):
            c.execute('ALTER TABLE users ADD COLUMN referrer_tid INTEGER')
        if not _col_exists(c, 'users', 'last_challenge_date'):
            c.execute('ALTER TABLE users ADD COLUMN last_challenge_date TEXT')
        if not _col_exists(c, 'categories', 'parent_id'):
            c.execute('ALTER TABLE categories ADD COLUMN parent_id INTEGER')
        if not _col_exists(c, 'charges', 'method'):
            c.execute('ALTER TABLE charges ADD COLUMN method TEXT')
        # backfill display_no for any pre-existing orders that predate this column
        missing = c.execute('SELECT id FROM orders WHERE display_no IS NULL ORDER BY id').fetchall()
        if missing:
            row = c.execute("SELECT value FROM settings WHERE key='order_seq_next'").fetchone()
            nxt = int(row['value']) if row else 1
            for r in missing:
                c.execute('UPDATE orders SET display_no=? WHERE id=?', (nxt, r['id']))
                nxt += 1
            c.execute("INSERT INTO settings(key,value) VALUES('order_seq_next',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value", (str(nxt),))

        defaults = {
            'api_url': '', 'api_key': '',
            'force_join_enabled': '0',
            'welcome_text': 'سلام 👋\nبه فروشگاه خوش آمدید.',
            'guide_text': 'به فروشگاه خوش آمدید.\nمحصول مورد نظر را انتخاب و طبق راهنما سفارش خود را ثبت کنید.',
            'support_username': '',
            'card_number': '', 'card_holder': '',
            'charge_info_text': '',
            'order_channel_public': '', 'order_channel_private': '',
            'order_seq_next': '1',
            'referral_percent': '0', 'referral_banner_path': '',
            'referral_signup_bonus': '0',
            'emoji_category': '🗂', 'emoji_subcategory': '📁', 'emoji_product': '🛒',
            'btn_shop': '🛍 فروشگاه', 'btn_guide': '📖 راهنما',
            'btn_support': '🎧 پشتیبانی', 'btn_account': '👤 حساب من',
            'btn_track': '🔍 پیگیری سفارش', 'btn_all_products': '📋 همه محصولات', 'btn_referral': '🤝 رفرال',
        }
        for k, v in defaults.items():
            c.execute('INSERT OR IGNORE INTO settings(key,value) VALUES(?,?)', (k, v))


# ---------------- settings ----------------
def get_setting(key, default=''):
    with db() as c:
        r = c.execute('SELECT value FROM settings WHERE key=?', (key,)).fetchone()
        return r['value'] if r else default


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


def set_setting_rich(key, text, entities):
    """مثل set_setting ولی موجودیت‌های پیام (از جمله اموجی‌های سفارشی/پرمیوم تلگرام) را هم
    همراه متن ذخیره می‌کند تا هنگام نمایش، دقیقاً همان‌طور که ادمین فرستاده بازتولید شود.
    توجه: این فقط برای متن‌های پیام (خوش‌آمد، راهنما، شارژ) کار می‌کند، نه برچسب دکمه‌ها —
    دکمه‌های شیشه‌ای تلگرام اصلاً از موجودیت/اموجی سفارشی پشتیبانی نمی‌کنند (محدودیت خود تلگرام)."""
    import pickle, base64
    set_setting(key, text)
    blob = base64.b64encode(pickle.dumps(entities)).decode('ascii') if entities else ''
    set_setting(f'{key}__entities', blob)


def get_setting_rich(key, default=''):
    """خروجی: (text, entities_or_None) — entities برای پاس دادن به پارامتر formatting_entities تلگرام."""
    import pickle, base64
    text = get_setting(key, default)
    blob = get_setting(f'{key}__entities', '')
    entities = None
    if blob:
        try:
            entities = pickle.loads(base64.b64decode(blob)) or None
        except Exception:
            entities = None
    return text, entities


def get_settings(keys):
    with db() as c:
        q = 'SELECT key,value FROM settings WHERE key IN (%s)' % ','.join('?' * len(keys))
        rows = c.execute(q, keys).fetchall()
        return {r['key']: r['value'] for r in rows}


def next_order_no():
    """شماره نمایشی بعدی سفارش را برمی‌گرداند و شمارنده را افزایش می‌دهد (اتمیک)."""
    with db() as c:
        r = c.execute("SELECT value FROM settings WHERE key='order_seq_next'").fetchone()
        cur = int(r['value']) if r else 1
        c.execute("INSERT INTO settings(key,value) VALUES('order_seq_next',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value", (str(cur + 1),))
        return cur


def set_order_seq_start(n):
    set_setting('order_seq_next', int(n))


# ---------------- users ----------------
def upsert_user(sender, referrer_tid=None):
    with db() as c:
        c.execute('''INSERT INTO users(telegram_id,username,first_name,last_name,referrer_tid) VALUES(?,?,?,?,?)
        ON CONFLICT(telegram_id) DO UPDATE SET username=excluded.username, first_name=excluded.first_name, last_name=excluded.last_name''',
                  (sender.id, getattr(sender, 'username', None), getattr(sender, 'first_name', None), getattr(sender, 'last_name', None), referrer_tid))


def get_user(telegram_id):
    with db() as c:
        return c.execute('SELECT * FROM users WHERE telegram_id=?', (telegram_id,)).fetchone()


def get_user_by_id(uid):
    with db() as c:
        return c.execute('SELECT * FROM users WHERE id=?', (uid,)).fetchone()


def list_users(page=0, per_page=10, search=None):
    off = page * per_page
    with db() as c:
        if search:
            like = f'%{search}%'
            rows = c.execute('SELECT * FROM users WHERE username LIKE ? OR CAST(telegram_id AS TEXT) LIKE ? ORDER BY id DESC LIMIT ? OFFSET ?',
                              (like, like, per_page, off)).fetchall()
            total = c.execute('SELECT COUNT(*) n FROM users WHERE username LIKE ? OR CAST(telegram_id AS TEXT) LIKE ?', (like, like)).fetchone()['n']
        else:
            rows = c.execute('SELECT * FROM users ORDER BY id DESC LIMIT ? OFFSET ?', (per_page, off)).fetchall()
            total = c.execute('SELECT COUNT(*) n FROM users').fetchone()['n']
        return rows, total


def set_banned(telegram_id, banned: bool):
    with db() as c:
        c.execute('UPDATE users SET banned=? WHERE telegram_id=?', (1 if banned else 0, telegram_id))


def set_verified(telegram_id, verified: bool):
    with db() as c:
        c.execute('UPDATE users SET verified=? WHERE telegram_id=?', (1 if verified else 0, telegram_id))


def adjust_balance(telegram_id, delta):
    with db() as c:
        c.execute('UPDATE users SET balance=balance+? WHERE telegram_id=?', (delta, telegram_id))
        row = c.execute('SELECT balance FROM users WHERE telegram_id=?', (telegram_id,)).fetchone()
        return row['balance'] if row else None


def try_charge(telegram_id, amount):
    """Atomically deduct `amount` if balance is sufficient. Returns True/False."""
    with db() as c:
        row = c.execute('SELECT balance FROM users WHERE telegram_id=?', (telegram_id,)).fetchone()
        if not row or row['balance'] < amount:
            return False
        c.execute('UPDATE users SET balance=balance-? WHERE telegram_id=?', (amount, telegram_id))
        return True


def transfer_balance(from_tid, to_tid, amount):
    """انتقال اتمیک موجودی بین دو کاربر. خروجی: (ok, error_message_or_None)."""
    if amount <= 0:
        return False, 'مبلغ باید بزرگتر از صفر باشد.'
    with db() as c:
        src = c.execute('SELECT balance FROM users WHERE telegram_id=?', (from_tid,)).fetchone()
        dst = c.execute('SELECT id FROM users WHERE telegram_id=?', (to_tid,)).fetchone()
        if not dst:
            return False, 'کاربر مقصد یافت نشد (باید قبلاً ربات را استارت کرده باشد).'
        if not src or src['balance'] < amount:
            return False, 'موجودی کیف پول شما کافی نیست.'
        c.execute('UPDATE users SET balance=balance-? WHERE telegram_id=?', (amount, from_tid))
        c.execute('UPDATE users SET balance=balance+? WHERE telegram_id=?', (amount, to_tid))
        return True, None


def check_challenge_available(telegram_id):
    u = get_user(telegram_id)
    return bool(u) and u['last_challenge_date'] != today_str()


def mark_challenge_used(telegram_id):
    with db() as c:
        c.execute('UPDATE users SET last_challenge_date=? WHERE telegram_id=?', (today_str(), telegram_id))


# ---------------- categories (2-level: top-level + subcategories) ----------------
def categories(parent_id=-1, active_only=True):
    """parent_id=-1 (پیش‌فرض) یعنی همه؛ None یعنی فقط دسته‌های اصلی؛ عدد یعنی زیردسته‌های همان دسته."""
    with db() as c:
        q = 'SELECT * FROM categories WHERE 1=1'
        args = []
        if parent_id != -1:
            if parent_id is None:
                q += ' AND parent_id IS NULL'
            else:
                q += ' AND parent_id=?'; args.append(parent_id)
        if active_only:
            q += ' AND active=1'
        q += ' ORDER BY name'
        return c.execute(q, args).fetchall()


def get_category(cid):
    with db() as c:
        return c.execute('SELECT * FROM categories WHERE id=?', (cid,)).fetchone()


def _get_or_create_category(c, name, parent_id=None):
    if parent_id is None:
        row = c.execute('SELECT id FROM categories WHERE name=? AND parent_id IS NULL', (name,)).fetchone()
    else:
        row = c.execute('SELECT id FROM categories WHERE name=? AND parent_id=?', (name, parent_id)).fetchone()
    if row:
        return row['id']
    cur = c.execute('INSERT INTO categories(name,parent_id) VALUES(?,?)', (name, parent_id))
    return cur.lastrowid


def add_category(name, parent_id=None):
    with db() as c:
        return _get_or_create_category(c, name, parent_id)


def rename_category(cid, name):
    with db() as c:
        c.execute('UPDATE categories SET name=? WHERE id=?', (name, cid))


def set_category_parent(cid, new_parent_id):
    with db() as c:
        c.execute('UPDATE categories SET parent_id=? WHERE id=?', (new_parent_id, cid))


def delete_category(cid):
    with db() as c:
        c.execute('UPDATE categories SET parent_id=NULL WHERE parent_id=?', (cid,))
        c.execute('UPDATE products SET category_id=NULL WHERE category_id=?', (cid,))
        c.execute('DELETE FROM categories WHERE id=?', (cid,))


# ---------------- products ----------------
def products(category_id=None, active_only=True, include_inactive_admin=False):
    with db() as c:
        q = 'SELECT p.*,c.name category FROM products p LEFT JOIN categories c ON c.id=p.category_id WHERE 1=1'
        args = []
        if not include_inactive_admin and active_only:
            q += ' AND p.active=1'
        if category_id is not None:
            q += ' AND p.category_id=?'; args.append(category_id)
        q += ' ORDER BY p.id DESC'
        return c.execute(q, args).fetchall()


def product(pid):
    with db() as c:
        return c.execute('SELECT p.*,c.name category FROM products p LEFT JOIN categories c ON c.id=p.category_id WHERE p.id=?', (pid,)).fetchone()


def services_upsert(data):
    if not isinstance(data, list):
        return 0
    count = 0
    with db() as c:
        for s in data:
            if not isinstance(s, dict) or s.get('service') is None:
                continue
            sid = str(s['service']); name = str(s.get('name') or sid); cat = str(s.get('category') or 'عمومی')
            cid = _get_or_create_category(c, cat, None)
            rate = float(s.get('rate') or s.get('price') or 0)
            mn = int(s.get('min') or s.get('min_qty') or 1); mx = int(s.get('max') or s.get('max_qty') or 1)
            c.execute('''INSERT INTO products(service_id,name,category_id,rate,min_qty,max_qty,type,description,raw_json,updated_at)
            VALUES(?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)
            ON CONFLICT(service_id) DO UPDATE SET name=excluded.name,category_id=excluded.category_id,rate=excluded.rate,min_qty=excluded.min_qty,max_qty=excluded.max_qty,type=excluded.type,description=excluded.description,raw_json=excluded.raw_json,updated_at=CURRENT_TIMESTAMP''',
                      (sid, name, cid, rate, mn, mx, str(s.get('type') or ''), str(s.get('description') or s.get('desc') or ''), json.dumps(s, ensure_ascii=False)))
            count += 1
    return count


def add_manual_product(name, category_id, rate, min_qty, max_qty, description):
    import time
    sid = f'manual-{int(time.time() * 1000)}'
    with db() as c:
        cur = c.execute('''INSERT INTO products(service_id,name,category_id,rate,min_qty,max_qty,type,description,manual)
        VALUES(?,?,?,?,?,?,?,?,1)''', (sid, name, category_id, rate, min_qty, max_qty, 'manual', description))
        return cur.lastrowid


def update_product(pid, **fields):
    if not fields:
        return
    cols = ', '.join(f'{k}=?' for k in fields)
    with db() as c:
        c.execute(f'UPDATE products SET {cols}, updated_at=CURRENT_TIMESTAMP WHERE id=?', (*fields.values(), pid))


def delete_product(pid):
    with db() as c:
        c.execute('DELETE FROM products WHERE id=?', (pid,))


def toggle_product_active(pid):
    with db() as c:
        c.execute('UPDATE products SET active = 1-active, updated_at=CURRENT_TIMESTAMP WHERE id=?', (pid,))


def count_products():
    with db() as c:
        return c.execute('SELECT COUNT(*) n FROM products').fetchone()['n']


# ---------------- orders ----------------
def create_order(user_id, product_id, link, quantity, total, status='processing'):
    disp = next_order_no()
    with db() as c:
        cur = c.execute('INSERT INTO orders(display_no,user_id,product_id,link,quantity,total,status) VALUES(?,?,?,?,?,?,?)',
                         (disp, user_id, product_id, link, quantity, total, status))
        return cur.lastrowid


def get_order(oid):
    with db() as c:
        return c.execute('''SELECT o.*, p.name product_name, p.service_id, u.telegram_id user_telegram_id, u.username
                             FROM orders o JOIN products p ON p.id=o.product_id JOIN users u ON u.id=o.user_id
                             WHERE o.id=?''', (oid,)).fetchone()


def get_order_by_display_no(display_no, user_id=None):
    with db() as c:
        q = '''SELECT o.*, p.name product_name, p.service_id, u.telegram_id user_telegram_id, u.username
               FROM orders o JOIN products p ON p.id=o.product_id JOIN users u ON u.id=o.user_id
               WHERE o.display_no=?'''
        args = [display_no]
        if user_id is not None:
            q += ' AND o.user_id=?'; args.append(user_id)
        return c.execute(q, args).fetchone()


def list_orders(status=None, user_id=None, page=0, per_page=10):
    off = page * per_page
    with db() as c:
        q = '''SELECT o.*, p.name product_name, u.telegram_id user_telegram_id, u.username
               FROM orders o JOIN products p ON p.id=o.product_id JOIN users u ON u.id=o.user_id WHERE 1=1'''
        args = []
        if status:
            q += ' AND o.status=?'; args.append(status)
        if user_id:
            q += ' AND o.user_id=?'; args.append(user_id)
        q += ' ORDER BY o.id DESC LIMIT ? OFFSET ?'; args += [per_page, off]
        rows = c.execute(q, args).fetchall()
        cq = 'SELECT COUNT(*) n FROM orders o WHERE 1=1'; cargs = []
        if status:
            cq += ' AND o.status=?'; cargs.append(status)
        if user_id:
            cq += ' AND o.user_id=?'; cargs.append(user_id)
        total = c.execute(cq, cargs).fetchone()['n']
        return rows, total


def update_order_status(oid, status, provider_order_id=None, note=None):
    with db() as c:
        if provider_order_id is not None:
            c.execute('UPDATE orders SET status=?, provider_order_id=?, updated_at=CURRENT_TIMESTAMP WHERE id=?', (status, provider_order_id, oid))
        elif note is not None:
            c.execute('UPDATE orders SET status=?, note=?, updated_at=CURRENT_TIMESTAMP WHERE id=?', (status, note, oid))
        else:
            c.execute('UPDATE orders SET status=?, updated_at=CURRENT_TIMESTAMP WHERE id=?', (status, oid))


# ---------------- payment methods (admin-configurable: card, wallet address, ...) ----------------
def create_payment_method(name, details):
    with db() as c:
        cur = c.execute('INSERT INTO payment_methods(name,details) VALUES(?,?)', (name, details))
        return cur.lastrowid


def get_payment_method(pmid):
    with db() as c:
        return c.execute('SELECT * FROM payment_methods WHERE id=?', (pmid,)).fetchone()


def list_payment_methods(active_only=True):
    with db() as c:
        q = 'SELECT * FROM payment_methods'
        if active_only:
            q += ' WHERE active=1'
        q += ' ORDER BY sort_order, id'
        return c.execute(q).fetchall()


def toggle_payment_method(pmid):
    with db() as c:
        c.execute('UPDATE payment_methods SET active = 1-active WHERE id=?', (pmid,))


def update_payment_method(pmid, **fields):
    if not fields:
        return
    cols = ', '.join(f'{k}=?' for k in fields)
    with db() as c:
        c.execute(f'UPDATE payment_methods SET {cols} WHERE id=?', (*fields.values(), pmid))


def delete_payment_method(pmid):
    with db() as c:
        c.execute('DELETE FROM payment_methods WHERE id=?', (pmid,))


# ---------------- charges (wallet top-up requests) ----------------
def create_charge(user_id, amount, method=None, receipt_text=None, receipt_file_id=None):
    with db() as c:
        cur = c.execute('INSERT INTO charges(user_id,amount,method,receipt_text,receipt_file_id) VALUES(?,?,?,?,?)',
                         (user_id, amount, method, receipt_text, receipt_file_id))
        return cur.lastrowid


def get_charge(chid):
    with db() as c:
        return c.execute('''SELECT ch.*, u.telegram_id user_telegram_id, u.username
                             FROM charges ch JOIN users u ON u.id=ch.user_id WHERE ch.id=?''', (chid,)).fetchone()


def list_charges(status='pending', user_id=None, page=0, per_page=10):
    off = page * per_page
    with db() as c:
        q = '''SELECT ch.*, u.telegram_id user_telegram_id, u.username
               FROM charges ch JOIN users u ON u.id=ch.user_id WHERE ch.status=?'''
        args = [status]
        if user_id is not None:
            q += ' AND ch.user_id=?'; args.append(user_id)
        q += ' ORDER BY ch.id DESC LIMIT ? OFFSET ?'; args += [per_page, off]
        rows = c.execute(q, args).fetchall()
        cq = 'SELECT COUNT(*) n FROM charges WHERE status=?'; cargs = [status]
        if user_id is not None:
            cq += ' AND user_id=?'; cargs.append(user_id)
        total = c.execute(cq, cargs).fetchone()['n']
        return rows, total


def update_charge_status(chid, status):
    with db() as c:
        c.execute('UPDATE charges SET status=?, updated_at=CURRENT_TIMESTAMP WHERE id=?', (status, chid))


def get_user_financial_summary(user_id, telegram_id):
    with db() as c:
        balance = c.execute('SELECT balance FROM users WHERE id=?', (user_id,)).fetchone()['balance']
        topped_up = c.execute("SELECT COALESCE(SUM(amount),0) s FROM charges WHERE user_id=? AND status='approved'", (user_id,)).fetchone()['s']
        spent = c.execute("SELECT COALESCE(SUM(total),0) s FROM orders WHERE user_id=? AND status!='failed'", (user_id,)).fetchone()['s']
        order_counts = c.execute('''SELECT status, COUNT(*) n FROM orders WHERE user_id=? GROUP BY status''', (user_id,)).fetchall()
        return {
            'balance': balance, 'topped_up': topped_up, 'spent': spent,
            'order_counts': {r['status']: r['n'] for r in order_counts},
        }


# ---------------- gift codes ----------------
def _gen_code(length=8):
    alphabet = string.ascii_uppercase + string.digits
    return ''.join(random.choice(alphabet) for _ in range(length))


def create_gift_code(amount, capacity, code=None):
    with db() as c:
        code = (code or '').strip().upper() or _gen_code()
        for _ in range(5):
            try:
                cur = c.execute('INSERT INTO gift_codes(code,amount,capacity) VALUES(?,?,?)', (code, amount, capacity))
                return code, cur.lastrowid
            except sqlite3.IntegrityError:
                code = _gen_code()
        raise RuntimeError('امکان ساخت کد یکتا وجود نداشت.')


def get_gift_code(code):
    with db() as c:
        return c.execute('SELECT * FROM gift_codes WHERE code=?', (code.strip().upper(),)).fetchone()


def get_gift_code_by_id(gid):
    with db() as c:
        return c.execute('SELECT * FROM gift_codes WHERE id=?', (gid,)).fetchone()


def list_gift_codes(page=0, per_page=10):
    off = page * per_page
    with db() as c:
        rows = c.execute('SELECT * FROM gift_codes ORDER BY id DESC LIMIT ? OFFSET ?', (per_page, off)).fetchall()
        total = c.execute('SELECT COUNT(*) n FROM gift_codes').fetchone()['n']
        return rows, total


def toggle_gift_code(gid):
    with db() as c:
        c.execute('UPDATE gift_codes SET active = 1-active WHERE id=?', (gid,))


def delete_gift_code(gid):
    with db() as c:
        c.execute('DELETE FROM gift_redemptions WHERE code_id=?', (gid,))
        c.execute('DELETE FROM gift_codes WHERE id=?', (gid,))


def redeem_gift_code(user_id, telegram_id, code):
    """خروجی: (ok, message_or_amount)"""
    with db() as c:
        gc = c.execute('SELECT * FROM gift_codes WHERE code=?', (code.strip().upper(),)).fetchone()
        if not gc:
            return False, 'کد هدیه نامعتبر است.'
        if not gc['active']:
            return False, 'این کد هدیه غیرفعال شده است.'
        if gc['used_count'] >= gc['capacity']:
            return False, 'ظرفیت این کد هدیه تکمیل شده است.'
        already = c.execute('SELECT 1 FROM gift_redemptions WHERE code_id=? AND user_id=?', (gc['id'], user_id)).fetchone()
        if already:
            return False, 'شما قبلاً از این کد هدیه استفاده کرده‌اید.'
        c.execute('INSERT INTO gift_redemptions(code_id,user_id) VALUES(?,?)', (gc['id'], user_id))
        c.execute('UPDATE gift_codes SET used_count=used_count+1 WHERE id=?', (gc['id'],))
        c.execute('UPDATE users SET balance=balance+? WHERE telegram_id=?', (gc['amount'], telegram_id))
        return True, gc['amount']


# ---------------- identity verification (KYC) ----------------
def create_verification(user_id, full_name, phone, national_id):
    with db() as c:
        cur = c.execute('INSERT INTO verifications(user_id,full_name,phone,national_id) VALUES(?,?,?,?)',
                         (user_id, full_name, phone, national_id))
        return cur.lastrowid


def get_verification(vid):
    with db() as c:
        return c.execute('''SELECT v.*, u.telegram_id user_telegram_id, u.username
                             FROM verifications v JOIN users u ON u.id=v.user_id WHERE v.id=?''', (vid,)).fetchone()


def get_pending_verification(user_id):
    with db() as c:
        return c.execute("SELECT * FROM verifications WHERE user_id=? AND status='pending' ORDER BY id DESC LIMIT 1", (user_id,)).fetchone()


def update_verification_status(vid, status):
    with db() as c:
        c.execute('UPDATE verifications SET status=?, updated_at=CURRENT_TIMESTAMP WHERE id=?', (status, vid))


# ---------------- stats ----------------
def get_stats():
    with db() as c:
        users_n = c.execute('SELECT COUNT(*) n FROM users').fetchone()['n']
        banned_n = c.execute('SELECT COUNT(*) n FROM users WHERE banned=1').fetchone()['n']
        products_n = c.execute('SELECT COUNT(*) n FROM products WHERE active=1').fetchone()['n']
        orders_n = c.execute('SELECT COUNT(*) n FROM orders').fetchone()['n']
        pending_n = c.execute("SELECT COUNT(*) n FROM orders WHERE status IN ('processing','pending')").fetchone()['n']
        completed_n = c.execute("SELECT COUNT(*) n FROM orders WHERE status='completed'").fetchone()['n']
        revenue = c.execute("SELECT COALESCE(SUM(total),0) s FROM orders WHERE status!='failed'").fetchone()['s']
        wallet_total = c.execute('SELECT COALESCE(SUM(balance),0) s FROM users').fetchone()['s']
        pending_charges = c.execute("SELECT COUNT(*) n FROM charges WHERE status='pending'").fetchone()['n']
        top = c.execute('''SELECT p.name, COUNT(*) c FROM orders o JOIN products p ON p.id=o.product_id
                            GROUP BY o.product_id ORDER BY c DESC LIMIT 5''').fetchall()
        return {
            'users': users_n, 'banned': banned_n, 'products': products_n,
            'orders': orders_n, 'pending': pending_n, 'completed': completed_n,
            'revenue': revenue, 'wallet_total': wallet_total, 'pending_charges': pending_charges,
            'top': top,
        }