import sqlite3
from datetime import datetime
from config import DB_FILE

def get_db():
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()
    c = conn.cursor()

    c.execute('''CREATE TABLE IF NOT EXISTS users (
        user_id INTEGER PRIMARY KEY,
        username TEXT,
        full_name TEXT,
        phone TEXT,
        lang TEXT DEFAULT 'fa',
        is_verified INTEGER DEFAULT 0,
        is_vip INTEGER DEFAULT 0,
        is_blacklisted INTEGER DEFAULT 0,
        is_reseller INTEGER DEFAULT 0,
        reseller_discount INTEGER DEFAULT 0,
        wallet INTEGER DEFAULT 0,
        points INTEGER DEFAULT 0,
        referral_code TEXT UNIQUE,
        referred_by INTEGER,
        total_spent INTEGER DEFAULT 0,
        free_trial_used INTEGER DEFAULT 0,
        joined_at TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        service TEXT,
        duration TEXT,
        amount INTEGER,
        original_amount INTEGER,
        discount_code TEXT,
        payment_method TEXT DEFAULT 'card',
        status TEXT DEFAULT 'pending',
        receipt_file_id TEXT,
        account_info TEXT,
        expires_at TEXT,
        notified_7d INTEGER DEFAULT 0,
        notified_3d INTEGER DEFAULT 0,
        notified_1d INTEGER DEFAULT 0,
        created_at TEXT,
        updated_at TEXT,
        admin_note TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        service TEXT,
        duration TEXT,
        price INTEGER,
        bulk_price INTEGER,
        description TEXT,
        stock INTEGER DEFAULT -1,
        notify_waitlist INTEGER DEFAULT 0,
        is_active INTEGER DEFAULT 1
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS discount_codes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE,
        percent INTEGER,
        max_uses INTEGER DEFAULT -1,
        used_count INTEGER DEFAULT 0,
        expires_at TEXT,
        is_active INTEGER DEFAULT 1,
        created_at TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS tickets (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        subject TEXT,
        status TEXT DEFAULT 'open',
        created_at TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS ticket_messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ticket_id INTEGER,
        sender_id INTEGER,
        is_admin INTEGER DEFAULT 0,
        message TEXT,
        created_at TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS reviews (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        order_id INTEGER,
        stars INTEGER,
        comment TEXT,
        is_shown INTEGER DEFAULT 1,
        created_at TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS flash_sales (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_id INTEGER,
        discount_percent INTEGER,
        expires_at TEXT,
        is_active INTEGER DEFAULT 1,
        created_at TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS waitlist (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        product_id INTEGER,
        created_at TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS subscriptions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        product_id INTEGER,
        is_active INTEGER DEFAULT 1,
        next_renewal TEXT,
        created_at TEXT
    )''')

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

    # پیش‌فرض‌ها
    defaults = [
        ('work_hours_start', '9'),
        ('work_hours_end', '23'),
        ('maintenance', '0'),
        ('bot_lang', 'fa'),
    ]
    for k, v in defaults:
        c.execute("INSERT OR IGNORE INTO settings VALUES (?,?)", (k, v))

    default_products = [
        ('تلگرام پرمیوم', '1 ماهه', 85000, 75000, 'اکانت تلگرام پرمیوم یک ماهه'),
        ('تلگرام پرمیوم', '3 ماهه', 230000, 200000, 'اکانت تلگرام پرمیوم سه ماهه'),
        ('تلگرام پرمیوم', '6 ماهه', 420000, 380000, 'اکانت تلگرام پرمیوم شش ماهه'),
        ('تلگرام پرمیوم', '12 ماهه', 750000, 680000, 'اکانت تلگرام پرمیوم یک ساله'),
        ('اسپاتیفای پرمیوم', '1 ماهه', 65000, 55000, 'اکانت اسپاتیفای پرمیوم یک ماهه'),
        ('اسپاتیفای پرمیوم', '3 ماهه', 170000, 150000, 'اکانت اسپاتیفای پرمیوم سه ماهه'),
        ('نتفلیکس', '1 ماهه', 95000, 85000, 'اکانت نتفلیکس یک ماهه'),
        ('نتفلیکس', '3 ماهه', 260000, 230000, 'اکانت نتفلیکس سه ماهه'),
    ]
    for p in default_products:
        c.execute("INSERT OR IGNORE INTO products (service,duration,price,bulk_price,description) VALUES (?,?,?,?,?)", p)

    conn.commit()
    conn.close()

# ===== USERS =====
def add_user(user_id, username, full_name, referred_by=None):
    import random, string
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT user_id FROM users WHERE user_id=?", (user_id,))
    if c.fetchone():
        conn.close()
        return False
    ref_code = ''.join(random.choices(string.ascii_uppercase + string.digits, k=8))
    c.execute("INSERT INTO users (user_id,username,full_name,referral_code,referred_by,joined_at) VALUES (?,?,?,?,?,?)",
              (user_id, username, full_name, ref_code, referred_by, datetime.now().isoformat()))
    conn.commit()
    conn.close()
    return True

def get_user(user_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM users WHERE user_id=?", (user_id,))
    row = c.fetchone()
    conn.close()
    return dict(row) if row else None

def get_user_by_referral(code):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM users WHERE referral_code=?", (code,))
    row = c.fetchone()
    conn.close()
    return dict(row) if row else None

def set_user_phone(user_id, phone):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE users SET phone=?, is_verified=1 WHERE user_id=?", (phone, user_id))
    conn.commit()
    conn.close()

def set_user_lang(user_id, lang):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE users SET lang=? WHERE user_id=?", (lang, user_id))
    conn.commit()
    conn.close()

def blacklist_user(user_id, bl=True):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE users SET is_blacklisted=? WHERE user_id=?", (1 if bl else 0, user_id))
    conn.commit()
    conn.close()

def update_wallet(user_id, amount):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE users SET wallet = wallet + ? WHERE user_id=?", (amount, user_id))
    conn.commit()
    conn.close()

def add_points(user_id, pts):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE users SET points = points + ? WHERE user_id=?", (pts, user_id))
    conn.commit()
    conn.close()

def check_vip_upgrade(user_id):
    from config import VIP_THRESHOLD
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT total_spent, is_vip FROM users WHERE user_id=?", (user_id,))
    row = c.fetchone()
    if row and row['total_spent'] >= VIP_THRESHOLD and not row['is_vip']:
        c.execute("UPDATE users SET is_vip=1 WHERE user_id=?", (user_id,))
        conn.commit()
        conn.close()
        return True
    conn.close()
    return False

def get_all_users():
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM users WHERE is_blacklisted=0")
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def set_reseller(user_id, discount):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE users SET is_reseller=1, reseller_discount=? WHERE user_id=?", (discount, user_id))
    conn.commit()
    conn.close()

def use_free_trial(user_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE users SET free_trial_used=1 WHERE user_id=?", (user_id,))
    conn.commit()
    conn.close()

# ===== ORDERS =====
def create_order(user_id, service, duration, amount, original_amount=None, discount_code=None, payment_method='card'):
    conn = get_db()
    c = conn.cursor()
    now = datetime.now().isoformat()
    c.execute("""INSERT INTO orders (user_id,service,duration,amount,original_amount,discount_code,payment_method,status,created_at,updated_at)
                 VALUES (?,?,?,?,?,?,?,?,?,?)""",
              (user_id, service, duration, amount, original_amount or amount, discount_code, payment_method, 'pending', now, now))
    oid = c.lastrowid
    conn.commit()
    conn.close()
    return oid

def update_order_receipt(order_id, file_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE orders SET receipt_file_id=?, status='waiting_confirm', updated_at=? WHERE id=?",
              (file_id, datetime.now().isoformat(), order_id))
    conn.commit()
    conn.close()

def update_order_status(order_id, status, admin_note=None):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE orders SET status=?, admin_note=?, updated_at=? WHERE id=?",
              (status, admin_note, datetime.now().isoformat(), order_id))
    conn.commit()
    conn.close()

def set_order_account(order_id, account_info, expires_at):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE orders SET account_info=?, expires_at=?, status='delivered', updated_at=? WHERE id=?",
              (account_info, expires_at, datetime.now().isoformat(), order_id))
    conn.commit()
    conn.close()

def get_order(order_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM orders WHERE id=?", (order_id,))
    row = c.fetchone()
    conn.close()
    return dict(row) if row else None

def get_user_orders(user_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM orders WHERE user_id=? ORDER BY created_at DESC LIMIT 10", (user_id,))
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_pending_orders():
    conn = get_db()
    c = conn.cursor()
    c.execute("""SELECT o.*, u.username, u.full_name FROM orders o
                 LEFT JOIN users u ON o.user_id=u.user_id
                 WHERE o.status='waiting_confirm' ORDER BY o.created_at""")
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_all_orders(limit=20):
    conn = get_db()
    c = conn.cursor()
    c.execute("""SELECT o.*, u.username, u.full_name FROM orders o
                 LEFT JOIN users u ON o.user_id=u.user_id
                 ORDER BY o.created_at DESC LIMIT ?""", (limit,))
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_expiring_orders(days):
    from datetime import timedelta
    conn = get_db()
    c = conn.cursor()
    target = (datetime.now() + timedelta(days=days)).date().isoformat()
    col = f'notified_{days}d'
    c.execute(f"SELECT * FROM orders WHERE status='delivered' AND expires_at LIKE ? AND {col}=0", (f"{target}%",))
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def mark_notified(order_id, days):
    conn = get_db()
    c = conn.cursor()
    c.execute(f"UPDATE orders SET notified_{days}d=1 WHERE id=?", (order_id,))
    conn.commit()
    conn.close()

# ===== PRODUCTS =====
def get_products():
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM products WHERE is_active=1 ORDER BY service, price")
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_services():
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT DISTINCT service FROM products WHERE is_active=1")
    rows = c.fetchall()
    conn.close()
    return [r['service'] for r in rows]

def get_product(product_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM products WHERE id=?", (product_id,))
    row = c.fetchone()
    conn.close()
    return dict(row) if row else None

def update_product_price(product_id, price):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE products SET price=? WHERE id=?", (price, product_id))
    conn.commit()
    conn.close()

def get_flash_sale(product_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM flash_sales WHERE product_id=? AND is_active=1 AND expires_at > ?",
              (product_id, datetime.now().isoformat()))
    row = c.fetchone()
    conn.close()
    return dict(row) if row else None

def create_flash_sale(product_id, discount, expires_at):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE flash_sales SET is_active=0 WHERE product_id=?", (product_id,))
    c.execute("INSERT INTO flash_sales (product_id,discount_percent,expires_at,created_at) VALUES (?,?,?,?)",
              (product_id, discount, expires_at, datetime.now().isoformat()))
    conn.commit()
    conn.close()

# ===== DISCOUNT CODES =====
def create_discount_code(code, percent, max_uses=-1, expires_at=None):
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT OR IGNORE INTO discount_codes (code,percent,max_uses,expires_at,created_at) VALUES (?,?,?,?,?)",
              (code.upper(), percent, max_uses, expires_at, datetime.now().isoformat()))
    conn.commit()
    conn.close()

def get_discount_code(code):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM discount_codes WHERE code=? AND is_active=1", (code.upper(),))
    row = c.fetchone()
    conn.close()
    if not row:
        return None
    d = dict(row)
    if d['max_uses'] != -1 and d['used_count'] >= d['max_uses']:
        return None
    if d['expires_at'] and d['expires_at'] < datetime.now().isoformat():
        return None
    return d

def use_discount_code(code):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE discount_codes SET used_count=used_count+1 WHERE code=?", (code.upper(),))
    conn.commit()
    conn.close()

def get_all_discount_codes():
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM discount_codes ORDER BY created_at DESC")
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

# ===== TICKETS =====
def create_ticket(user_id, subject):
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO tickets (user_id,subject,created_at) VALUES (?,?,?)",
              (user_id, subject, datetime.now().isoformat()))
    tid = c.lastrowid
    conn.commit()
    conn.close()
    return tid

def add_ticket_message(ticket_id, sender_id, message, is_admin=False):
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO ticket_messages (ticket_id,sender_id,is_admin,message,created_at) VALUES (?,?,?,?,?)",
              (ticket_id, sender_id, 1 if is_admin else 0, message, datetime.now().isoformat()))
    conn.commit()
    conn.close()

def get_open_tickets():
    conn = get_db()
    c = conn.cursor()
    c.execute("""SELECT t.*, u.username, u.full_name FROM tickets t
                 LEFT JOIN users u ON t.user_id=u.user_id
                 WHERE t.status='open' ORDER BY t.created_at DESC""")
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_ticket(ticket_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM tickets WHERE id=?", (ticket_id,))
    row = c.fetchone()
    conn.close()
    return dict(row) if row else None

def get_ticket_messages(ticket_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM ticket_messages WHERE ticket_id=? ORDER BY created_at", (ticket_id,))
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def close_ticket(ticket_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE tickets SET status='closed' WHERE id=?", (ticket_id,))
    conn.commit()
    conn.close()

# ===== REVIEWS =====
def add_review(user_id, order_id, stars, comment):
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO reviews (user_id,order_id,stars,comment,created_at) VALUES (?,?,?,?,?)",
              (user_id, order_id, stars, comment, datetime.now().isoformat()))
    conn.commit()
    conn.close()

def get_reviews(limit=10):
    conn = get_db()
    c = conn.cursor()
    c.execute("""SELECT r.*, u.full_name FROM reviews r
                 LEFT JOIN users u ON r.user_id=u.user_id
                 WHERE r.is_shown=1 ORDER BY r.created_at DESC LIMIT ?""", (limit,))
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

# ===== WAITLIST =====
def add_to_waitlist(user_id, product_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT OR IGNORE INTO waitlist (user_id,product_id,created_at) VALUES (?,?,?)",
              (user_id, product_id, datetime.now().isoformat()))
    conn.commit()
    conn.close()

def get_waitlist(product_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM waitlist WHERE product_id=?", (product_id,))
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

# ===== SETTINGS =====
def get_setting(key, default=None):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT value FROM settings WHERE key=?", (key,))
    row = c.fetchone()
    conn.close()
    return row['value'] if row else default

def set_setting(key, value):
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT OR REPLACE INTO settings VALUES (?,?)", (key, str(value)))
    conn.commit()
    conn.close()

# ===== STATS =====
def get_stats():
    conn = get_db()
    c = conn.cursor()
    s = {}
    c.execute("SELECT COUNT(*) as cnt FROM users")
    s['total_users'] = c.fetchone()['cnt']
    c.execute("SELECT COUNT(*) as cnt FROM users WHERE is_verified=1")
    s['verified_users'] = c.fetchone()['cnt']
    c.execute("SELECT COUNT(*) as cnt FROM users WHERE is_vip=1")
    s['vip_users'] = c.fetchone()['cnt']
    c.execute("SELECT COUNT(*) as cnt FROM orders")
    s['total_orders'] = c.fetchone()['cnt']
    c.execute("SELECT COUNT(*) as cnt FROM orders WHERE status='delivered'")
    s['delivered_orders'] = c.fetchone()['cnt']
    c.execute("SELECT COUNT(*) as cnt FROM orders WHERE status='waiting_confirm'")
    s['pending_orders'] = c.fetchone()['cnt']
    c.execute("SELECT COALESCE(SUM(amount),0) as t FROM orders WHERE status IN ('confirmed','delivered')")
    s['total_revenue'] = c.fetchone()['t']
    # آمار امروز
    today = datetime.now().date().isoformat()
    c.execute("SELECT COUNT(*) as cnt FROM orders WHERE created_at LIKE ? AND status IN ('confirmed','delivered')", (f"{today}%",))
    s['today_orders'] = c.fetchone()['cnt']
    c.execute("SELECT COALESCE(SUM(amount),0) as t FROM orders WHERE created_at LIKE ? AND status IN ('confirmed','delivered')", (f"{today}%",))
    s['today_revenue'] = c.fetchone()['t']
    conn.close()
    return s

init_db()

# ===== CONFIG / VPN =====
def init_configs_table():
    conn = get_db()
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS configs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        protocol TEXT DEFAULT 'vless',
        config_link TEXT,
        config_text TEXT,
        traffic_limit_gb INTEGER DEFAULT 0,
        traffic_used_gb REAL DEFAULT 0,
        duration_days INTEGER DEFAULT 30,
        price INTEGER DEFAULT 0,
        is_assigned INTEGER DEFAULT 0,
        assigned_to INTEGER,
        assigned_order INTEGER,
        assigned_at TEXT,
        expires_at TEXT,
        is_active INTEGER DEFAULT 1,
        note TEXT,
        created_at TEXT
    )''')
    conn.commit()
    conn.close()

def add_config(name, protocol, config_link, config_text, traffic_gb, duration_days, price, note=''):
    conn = get_db()
    c = conn.cursor()
    c.execute("""INSERT INTO configs (name,protocol,config_link,config_text,traffic_limit_gb,duration_days,price,note,created_at)
                 VALUES (?,?,?,?,?,?,?,?,?)""",
              (name, protocol, config_link, config_text, traffic_gb, duration_days, price, note, datetime.now().isoformat()))
    cid = c.lastrowid
    conn.commit()
    conn.close()
    return cid

def get_free_configs(protocol=None, traffic_gb=None):
    conn = get_db()
    c = conn.cursor()
    if protocol:
        c.execute("SELECT * FROM configs WHERE is_assigned=0 AND is_active=1 AND protocol=? ORDER BY created_at", (protocol,))
    else:
        c.execute("SELECT * FROM configs WHERE is_assigned=0 AND is_active=1 ORDER BY created_at")
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def assign_config(config_id, user_id, order_id):
    from datetime import timedelta
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT duration_days FROM configs WHERE id=?", (config_id,))
    row = c.fetchone()
    duration = row['duration_days'] if row else 30
    expires = (datetime.now() + timedelta(days=duration)).isoformat()
    c.execute("""UPDATE configs SET is_assigned=1, assigned_to=?, assigned_order=?,
                 assigned_at=?, expires_at=? WHERE id=?""",
              (user_id, order_id, datetime.now().isoformat(), expires, config_id))
    conn.commit()
    conn.close()
    return expires

def get_user_configs(user_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM configs WHERE assigned_to=? AND is_active=1 ORDER BY assigned_at DESC", (user_id,))
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_config(config_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM configs WHERE id=?", (config_id,))
    row = c.fetchone()
    conn.close()
    return dict(row) if row else None

def get_all_configs(limit=50):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM configs ORDER BY created_at DESC LIMIT ?", (limit,))
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]

def deactivate_config(config_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE configs SET is_active=0 WHERE id=?", (config_id,))
    conn.commit()
    conn.close()

def get_config_stats():
    conn = get_db()
    c = conn.cursor()
    stats = {}
    c.execute("SELECT COUNT(*) as cnt FROM configs WHERE is_active=1")
    stats['total'] = c.fetchone()['cnt']
    c.execute("SELECT COUNT(*) as cnt FROM configs WHERE is_assigned=0 AND is_active=1")
    stats['free'] = c.fetchone()['cnt']
    c.execute("SELECT COUNT(*) as cnt FROM configs WHERE is_assigned=1 AND is_active=1")
    stats['assigned'] = c.fetchone()['cnt']
    conn.close()
    return stats

init_configs_table()

# ===== THEME / UI SETTINGS =====
def get_theme():
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT key, value FROM settings WHERE key LIKE 'theme_%'")
    rows = c.fetchall()
    conn.close()
    theme = {}
    for r in rows:
        theme[r['key'].replace('theme_','')] = r['value']
    # مقادیر پیش‌فرض
    defaults = {
        'btn_buy':      '🛒 خرید اکانت',
        'btn_orders':   '📦 سفارش‌های من',
        'btn_configs':  '⚙️ کانفیگ‌های من',
        'btn_wallet':   '👛 کیف پول',
        'btn_support':  '🎧 پشتیبانی',
        'btn_referral': '🔗 لینک رفرال',
        'btn_reviews':  '⭐ نظرات',
        'btn_payment':  '💳 روش پرداخت',
        'btn_about':    'ℹ️ درباره ما',
        'btn_admin':    '👤 پنل ادمین',
        'color_main':   'green',   # green, blue, red, purple, default
        'color_secondary': 'default',
        'welcome_emoji': '👋',
        'bot_name':     'ربات پرمیوم',
        'bot_version':  '1.0.0',
    }
    for k, v in defaults.items():
        if k not in theme:
            theme[k] = v
    return theme

def save_theme(key, value):
    set_setting(f'theme_{key}', value)

# ===== PRODUCT MANAGEMENT (FULL CRUD) =====
def add_product(service, duration, price, bulk_price=0, description=''):
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO products (service,duration,price,bulk_price,description,is_active) VALUES (?,?,?,?,?,1)",
              (service, duration, int(price), int(bulk_price), description))
    pid = c.lastrowid
    conn.commit()
    conn.close()
    return pid

def delete_product(product_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("DELETE FROM products WHERE id=?", (product_id,))
    conn.commit()
    conn.close()

def toggle_product(product_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE products SET is_active = CASE WHEN is_active=1 THEN 0 ELSE 1 END WHERE id=?", (product_id,))
    conn.commit()
    conn.close()

def update_product(product_id, service, duration, price, bulk_price, description):
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE products SET service=?,duration=?,price=?,bulk_price=?,description=? WHERE id=?",
              (service, duration, int(price), int(bulk_price), description, product_id))
    conn.commit()
    conn.close()

def get_all_products_admin():
    """همه محصولات (فعال و غیرفعال) برای ادمین"""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM products ORDER BY service, price")
    rows = c.fetchall()
    conn.close()
    return [dict(r) for r in rows]