database/db.py

import os
import logging
from datetime import datetime
from motor.motor_asyncio import AsyncIOMotorClient
from config import MONGO_URI, PRODUCTS

logger = logging.getLogger(__name__)

# MongoDB Client
client = AsyncIOMotorClient(MONGO_URI)
db = client.get_database("telegram_bot")


async def init_db():
    """MongoDB ulanishini tekshirish va dastlabki sozlashlarni bajarish."""
    try:
        # Indexlarni yaratish
        await db.users.create_index("id", unique=True)
        await db.products.create_index("id", unique=True)
        await db.gift_codes.create_index("id", unique=True)
        await db.gift_codes.create_index("gift_link", unique=True)
        await db.orders.create_index("id", unique=True)

        # Standart mahsulotlarni qo'shish (agar mavjud bo'lmasa)
        for p in PRODUCTS:
            await db.products.update_one(
                {"id": p["id"]},
                {"$setOnInsert": {
                    "id": p["id"],
                    "name": p["name"],
                    "description": p["description"],
                    "duration_months": p["duration_months"],
                    "stars_price": p["stars_price"],
                    "emoji": p["emoji"],
                    "is_active": 1
                }},
                upsert=True
            )
        logger.info("✅ MongoDB indekslari va boshlang'ich mahsulotlar tayyor.")
    except Exception as e:
        logger.error(f"❌ MongoDB init_db xatosi: {e}", exc_info=True)


async def get_next_sequence_value(sequence_name: str) -> int:
    """Auto-increment integer ID yaratish (SQLite o'rniga ishlatiladi)."""
    result = await db.counters.find_one_and_update(
        {"_id": sequence_name},
        {"$inc": {"sequence_value": 1}},
        upsert=True,
        return_document=True
    )
    return result["sequence_value"]


# ─────────────────────── USERS ───────────────────────

async def upsert_user(user_id: int, username: str | None, full_name: str):
    """Foydalanuvchini qo'shish yoki yangilash."""
    await db.users.update_one(
        {"id": user_id},
        {"$set": {
            "username": username,
            "full_name": full_name,
            "created_at": datetime.now().isoformat()
        }},
        upsert=True
    )


async def get_all_user_ids() -> list[int]:
    """Barcha foydalanuvchilar ID larini olish (broadcast uchun)."""
    cursor = db.users.find({}, {"id": 1})
    rows = await cursor.to_list(length=None)
    return [row["id"] for row in rows]


async def count_users() -> int:
    """Foydalanuvchilar sonini hisoblash."""
    return await db.users.count_documents({})


# ─────────────────────── PRODUCTS ───────────────────────

async def get_active_products() -> list[dict]:
    """Faol mahsulotlar ro'yxatini olish."""
    cursor = db.products.find({"is_active": 1}).sort("duration_months", 1)
    rows = await cursor.to_list(length=None)
    return rows


async def get_product_by_id(product_id: int) -> dict | None:
    """ID bo'yicha mahsulotni olish."""
    return await db.products.find_one({"id": product_id})


async def update_product_price(product_id: int, new_price: int):
    """Mahsulot narxini yangilash."""
    await db.products.update_one(
        {"id": product_id},
        {"$set": {"stars_price": new_price}}
    )


# ─────────────────────── GIFT CODES ───────────────────────

async def add_gift_code(product_id: int, gift_link: str) -> bool:
    """Yangi sovg'a linkini zaxiraga qo'shish. Agar mavjud bo'lsa False qaytaradi."""
    try:
        # O'xshash link borligini tekshiramiz (unique constraint)
        exists = await db.gift_codes.find_one({"gift_link": gift_link})
        if exists:
            return False

        new_id = await get_next_sequence_value("gift_code_id")
        await db.gift_codes.insert_one({
            "id": new_id,
            "product_id": product_id,
            "gift_link": gift_link,
            "is_used": 0,
            "used_by": None,
            "order_id": None,
            "added_at": datetime.now().isoformat(),
            "used_at": None
        })
        return True
    except Exception:
        return False


async def get_available_code(product_id: int) -> dict | None:
    """Berilgan mahsulot uchun birinchi ishlatilmagan linkni olish."""
    return await db.gift_codes.find_one({
        "product_id": product_id,
        "is_used": 0
    }, sort=[("id", 1)])


async def mark_code_used(code_id: int, user_id: int, order_id: int):
    """Linkni ishlatilgan deb belgilash."""
    await db.gift_codes.update_one(
        {"id": code_id},
        {"$set": {
            "is_used": 1,
            "used_by": user_id,
            "order_id": order_id,
            "used_at": datetime.now().isoformat()
        }}
    )


async def count_available_codes(product_id: int) -> int:
    """Mahsulot uchun mavjud linklarni sanash."""
    return await db.gift_codes.count_documents({
        "product_id": product_id,
        "is_used": 0
    })


async def get_stock_summary() -> list[dict]:
    """Har bir mahsulot uchun zaxira holati."""
    products = await get_active_products()
    summary = []
    for p in products:
        available = await db.gift_codes.count_documents({"product_id": p["id"], "is_used": 0})
        used = await db.gift_codes.count_documents({"product_id": p["id"], "is_used": 1})
        total = available + used
        summary.append({
            "name": p["name"],
            "emoji": p.get("emoji", "⭐"),
            "stars_price": p["stars_price"],
            "available": available,
            "used": used,
            "total": total
        })
    return summary


# ─────────────────────── ORDERS ───────────────────────

async def create_order(user_id: int, product_id: int, stars_amount: int) -> int:
    """Yangi buyurtma yaratish va uning ID sini qaytarish."""
    new_id = await get_next_sequence_value("order_id")
    await db.orders.insert_one({
        "id": new_id,
        "user_id": user_id,
        "product_id": product_id,
        "gift_code_id": None,
        "status": "pending",
        "charge_id": None,
        "stars_amount": stars_amount,
        "created_at": datetime.now().isoformat(),
        "completed_at": None
    })
    return new_id


async def complete_order(order_id: int, gift_code_id: int, charge_id: str):
    """Buyurtmani yakunlash."""
    await db.orders.update_one(
        {"id": order_id},
        {"$set": {
            "status": "completed",
            "gift_code_id": gift_code_id,
            "charge_id": charge_id,
            "completed_at": datetime.now().isoformat()
        }}
    )


async def fail_order(order_id: int, charge_id: str):
    """Buyurtmani (zaxira yo'qligi sababli) pending_delivery holatiga o'tkazish."""
    await db.orders.update_one(
        {"id": order_id},
        {"$set": {
            "status": "pending_delivery",
            "charge_id": charge_id
        }}
    )


async def get_user_orders(user_id: int) -> list[dict]:
    """Foydalanuvchi buyurtmalari tarixi (oxirgi 10 ta)."""
    cursor = db.orders.find({"user_id": user_id}).sort("created_at", -1).limit(10)
    orders = await cursor.to_list(length=None)

    result = []
    for o in orders:
        product = await db.products.find_one({"id": o["product_id"]})
        gift_link = None
        if o.get("gift_code_id"):
            code = await db.gift_codes.find_one({"id": o["gift_code_id"]})
            if code:
                gift_link = code["gift_link"]

        result.append({
            "id": o["id"],
            "status": o["status"],
            "stars_amount": o.get("stars_amount"),
            "created_at": o["created_at"],
            "product_name": product["name"] if product else "Noma'lum",
            "gift_link": gift_link
        })
    return result


async def get_total_stats() -> dict:
    """Umumiy statistika."""
    total_completed = await db.orders.count_documents({"status": "completed"})

    # Stars summasini hisoblash
    pipeline = [
        {"$match": {"status": "completed"}},
        {"$group": {"_id": None, "total": {"$sum": "$stars_amount"}}}
    ]
    cursor = db.orders.aggregate(pipeline)
    agg_result = await cursor.to_list(length=1)
    total_stars = agg_result[0]["total"] if agg_result else 0

    pending_delivery = await db.orders.count_documents({"status": "pending_delivery"})

    return {
        "total_completed": total_completed,
        "total_stars": total_stars,
        "pending_delivery": pending_delivery
    }


async def get_pending_delivery_orders() -> list[dict]:
    """Zaxira bo'lmaganligi sababli kutilayotgan buyurtmalar."""
    cursor = db.orders.find({"status": "pending_delivery"}).sort("created_at", 1)
    orders = await cursor.to_list(length=None)

    result = []
    for o in orders:
        product = await db.products.find_one({"id": o["product_id"]})
        user = await db.users.find_one({"id": o["user_id"]})
        result.append({
            "id": o["id"],
            "user_id": o["user_id"],
            "stars_amount": o.get("stars_amount"),
            "created_at": o["created_at"],
            "product_name": product["name"] if product else "Noma'lum",
            "product_id": o["product_id"],
            "username": user.get("username") if user else None,
            "full_name": user.get("full_name") if user else "Noma'lum"
        })
    return result