database.py

"""
MTXtyle - Database operatsiyalari (SQLite + aiosqlite)
"""
import aiosqlite
import time
from config import DATABASE_PATH, CATEGORIES


async def init_db():
    """Jadvallarni yaratish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        # Kategoriyalar jadvali
        await db.execute("""
            CREATE TABLE IF NOT EXISTS categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                slug TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                emoji TEXT DEFAULT ''
            )
        """)

        # Mahsulotlar jadvali
        await db.execute("""
            CREATE TABLE IF NOT EXISTS products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT DEFAULT '',
                price INTEGER NOT NULL,
                category_id INTEGER,
                photo_id TEXT DEFAULT '',
                in_stock INTEGER DEFAULT 1,
                created_at REAL DEFAULT 0,
                FOREIGN KEY (category_id) REFERENCES categories(id)
            )
        """)

        # Savat jadvali
        await db.execute("""
            CREATE TABLE IF NOT EXISTS cart_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                quantity INTEGER DEFAULT 1,
                FOREIGN KEY (product_id) REFERENCES products(id)
            )
        """)

        # Buyurtmalar jadvali
        await db.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                full_name TEXT NOT NULL,
                phone TEXT NOT NULL,
                address TEXT NOT NULL,
                total_price INTEGER DEFAULT 0,
                status TEXT DEFAULT 'yangi',
                created_at REAL DEFAULT 0
            )
        """)

        # Buyurtma elementlari
        await db.execute("""
            CREATE TABLE IF NOT EXISTS order_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                order_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                product_name TEXT NOT NULL,
                quantity INTEGER DEFAULT 1,
                price INTEGER DEFAULT 0,
                FOREIGN KEY (order_id) REFERENCES orders(id),
                FOREIGN KEY (product_id) REFERENCES products(id)
            )
        """)

        # Users table for broadcast
        await db.execute("""
            CREATE TABLE IF NOT EXISTS users (
                user_id INTEGER PRIMARY KEY,
                username TEXT,
                full_name TEXT,
                joined_at REAL
            )
        """)

        await db.commit()

        # Default kategoriyalarni qo'shish
        for slug, info in CATEGORIES.items():
            try:
                await db.execute(
                    "INSERT OR IGNORE INTO categories (slug, name, emoji) VALUES (?, ?, ?)",
                    (slug, info["name"], info["emoji"])
                )
            except Exception:
                pass
        await db.commit()


# ==================== KATEGORIYALAR ====================

async def get_categories():
    """Barcha kategoriyalarni olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("SELECT * FROM categories ORDER BY id")
        rows = await cursor.fetchall()
        return [dict(row) for row in rows]


async def get_category_by_slug(slug):
    """Slug bo'yicha kategoriya olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("SELECT * FROM categories WHERE slug = ?", (slug,))
        row = await cursor.fetchone()
        return dict(row) if row else None


async def get_category_by_id(category_id):
    """ID bo'yicha kategoriya olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("SELECT * FROM categories WHERE id = ?", (category_id,))
        row = await cursor.fetchone()
        return dict(row) if row else None


# ==================== MAHSULOTLAR ====================

async def add_product(name, description, price, category_id, photo_id=""):
    """Yangi mahsulot qo'shish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        cursor = await db.execute(
            """INSERT INTO products (name, description, price, category_id, photo_id, created_at)
               VALUES (?, ?, ?, ?, ?, ?)""",
            (name, description, price, category_id, photo_id, time.time())
        )
        await db.commit()
        return cursor.lastrowid


async def get_products_by_category(category_id, page=0, per_page=5):
    """Kategoriya bo'yicha mahsulotlarni olish (pagination bilan)"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        offset = page * per_page

        # Umumiy sonni olish
        cursor = await db.execute(
            "SELECT COUNT(*) as cnt FROM products WHERE category_id = ? AND in_stock = 1",
            (category_id,)
        )
        row = await cursor.fetchone()
        total = row["cnt"]

        # Mahsulotlarni olish
        cursor = await db.execute(
            """SELECT * FROM products WHERE category_id = ? AND in_stock = 1
               ORDER BY created_at DESC LIMIT ? OFFSET ?""",
            (category_id, per_page, offset)
        )
        rows = await cursor.fetchall()
        products = [dict(r) for r in rows]

        return products, total


async def get_product_by_id(product_id):
    """ID bo'yicha mahsulot olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("SELECT * FROM products WHERE id = ?", (product_id,))
        row = await cursor.fetchone()
        return dict(row) if row else None


async def get_all_products():
    """Barcha mahsulotlarni olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id ORDER BY p.created_at DESC"
        )
        rows = await cursor.fetchall()
        return [dict(r) for r in rows]


async def update_product(product_id, **kwargs):
    """Mahsulotni yangilash"""
    allowed_fields = {"name", "description", "price", "category_id", "photo_id", "in_stock"}
    fields = {k: v for k, v in kwargs.items() if k in allowed_fields}
    if not fields:
        return False

    set_clause = ", ".join(f"{k} = ?" for k in fields)
    values = list(fields.values()) + [product_id]

    async with aiosqlite.connect(DATABASE_PATH) as db:
        await db.execute(f"UPDATE products SET {set_clause} WHERE id = ?", values)
        await db.commit()
        return True


async def delete_product(product_id):
    """Mahsulotni o'chirish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        await db.execute("DELETE FROM products WHERE id = ?", (product_id,))
        await db.commit()


async def search_products(query):
    """Mahsulotlarni qidirish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM products WHERE in_stock = 1 AND (name LIKE ? OR description LIKE ?) ORDER BY created_at DESC",
            (f"%{query}%", f"%{query}%")
        )
        rows = await cursor.fetchall()
        return [dict(r) for r in rows]


# ==================== SAVAT (CART) ====================

async def add_to_cart(user_id, product_id, quantity=1):
    """Savatga mahsulot qo'shish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        # Mavjudligini tekshirish
        cursor = await db.execute(
            "SELECT * FROM cart_items WHERE user_id = ? AND product_id = ?",
            (user_id, product_id)
        )
        existing = await cursor.fetchone()

        if existing:
            new_qty = existing[3] + quantity
            if new_qty <= 0:
                await db.execute(
                    "DELETE FROM cart_items WHERE user_id = ? AND product_id = ?",
                    (user_id, product_id)
                )
            else:
                await db.execute(
                    "UPDATE cart_items SET quantity = ? WHERE user_id = ? AND product_id = ?",
                    (new_qty, user_id, product_id)
                )
        else:
            if quantity > 0:
                await db.execute(
                    "INSERT INTO cart_items (user_id, product_id, quantity) VALUES (?, ?, ?)",
                    (user_id, product_id, quantity)
                )
        await db.commit()


async def get_cart(user_id):
    """Foydalanuvchi savatini olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            """SELECT ci.*, p.name, p.price, p.photo_id
               FROM cart_items ci
               JOIN products p ON ci.product_id = p.id
               WHERE ci.user_id = ?""",
            (user_id,)
        )
        rows = await cursor.fetchall()
        return [dict(r) for r in rows]


async def get_cart_total(user_id):
    """Savat umumiy summasini olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        cursor = await db.execute(
            """SELECT COALESCE(SUM(ci.quantity * p.price), 0) as total
               FROM cart_items ci
               JOIN products p ON ci.product_id = p.id
               WHERE ci.user_id = ?""",
            (user_id,)
        )
        row = await cursor.fetchone()
        return row[0]


async def update_cart_quantity(user_id, product_id, quantity):
    """Savatdagi mahsulot miqdorini o'zgartirish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        if quantity <= 0:
            await db.execute(
                "DELETE FROM cart_items WHERE user_id = ? AND product_id = ?",
                (user_id, product_id)
            )
        else:
            await db.execute(
                "UPDATE cart_items SET quantity = ? WHERE user_id = ? AND product_id = ?",
                (quantity, user_id, product_id)
            )
        await db.commit()


async def remove_from_cart(user_id, product_id):
    """Savatdan mahsulotni o'chirish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        await db.execute(
            "DELETE FROM cart_items WHERE user_id = ? AND product_id = ?",
            (user_id, product_id)
        )
        await db.commit()


async def clear_cart(user_id):
    """Savatni tozalash"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        await db.execute("DELETE FROM cart_items WHERE user_id = ?", (user_id,))
        await db.commit()


# ==================== USERS & BROADCAST ====================

async def add_user(user_id, username, full_name):
    """Yangi foydalanuvchini qo'shish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        await db.execute(
            "INSERT OR IGNORE INTO users (user_id, username, full_name, joined_at) VALUES (?, ?, ?, ?)",
            (user_id, username, full_name, time.time())
        )
        await db.commit()

async def get_all_users():
    """Barcha foydalanuvchilarni olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("SELECT user_id FROM users")
        rows = await cursor.fetchall()
        return [row["user_id"] for row in rows]

async def get_all_orders_with_items():
    """Excel export uchun barcha buyurtmalar va mahsulotlar"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("""
            SELECT o.id, o.user_id, o.full_name, o.phone, o.address, o.total_price, o.status, o.created_at,
                   oi.product_name, oi.quantity, oi.price
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            ORDER BY o.created_at DESC
        """)
        rows = await cursor.fetchall()
        return [dict(r) for r in rows]

# ==================== BUYURTMALAR ====================

async def create_order(user_id, full_name, phone, address):
    """Yangi buyurtma yaratish"""
    cart = await get_cart(user_id)
    if not cart:
        return None

    total = await get_cart_total(user_id)

    async with aiosqlite.connect(DATABASE_PATH) as db:
        cursor = await db.execute(
            """INSERT INTO orders (user_id, full_name, phone, address, total_price, status, created_at)
               VALUES (?, ?, ?, ?, ?, 'yangi', ?)""",
            (user_id, full_name, phone, address, total, time.time())
        )
        order_id = cursor.lastrowid

        # Savat elementlarini buyurtmaga ko'chirish
        for item in cart:
            await db.execute(
                """INSERT INTO order_items (order_id, product_id, product_name, quantity, price)
                   VALUES (?, ?, ?, ?, ?)""",
                (order_id, item["product_id"], item["name"], item["quantity"], item["price"])
            )

        await db.commit()

    # Savatni tozalash
    await clear_cart(user_id)
    return order_id


async def get_order(order_id):
    """Buyurtmani olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("SELECT * FROM orders WHERE id = ?", (order_id,))
        row = await cursor.fetchone()
        return dict(row) if row else None


async def get_order_items(order_id):
    """Buyurtma elementlarini olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM order_items WHERE order_id = ?", (order_id,)
        )
        rows = await cursor.fetchall()
        return [dict(r) for r in rows]


async def get_user_orders(user_id):
    """Foydalanuvchi buyurtmalarini olish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC",
            (user_id,)
        )
        rows = await cursor.fetchall()
        return [dict(r) for r in rows]


async def update_order_status(order_id, status):
    """Buyurtma statusini o'zgartirish"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        await db.execute(
            "UPDATE orders SET status = ? WHERE id = ?",
            (status, order_id)
        )
        await db.commit()


async def get_all_orders(status=None):
    """Barcha buyurtmalarni olish (admin uchun)"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        if status:
            cursor = await db.execute(
                "SELECT * FROM orders WHERE status = ? ORDER BY created_at DESC",
                (status,)
            )
        else:
            cursor = await db.execute(
                "SELECT * FROM orders ORDER BY created_at DESC LIMIT 50"
            )
        rows = await cursor.fetchall()
        return [dict(r) for r in rows]


# ==================== STATISTIKA ====================

async def get_stats():
    """Umumiy statistika (admin uchun)"""
    async with aiosqlite.connect(DATABASE_PATH) as db:
        stats = {}

        cursor = await db.execute("SELECT COUNT(*) FROM products WHERE in_stock = 1")
        row = await cursor.fetchone()
        stats["products_count"] = row[0]

        cursor = await db.execute("SELECT COUNT(*) FROM orders")
        row = await cursor.fetchone()
        stats["orders_count"] = row[0]

        cursor = await db.execute("SELECT COUNT(*) FROM orders WHERE status = 'yangi'")
        row = await cursor.fetchone()
        stats["new_orders"] = row[0]

        cursor = await db.execute(
            "SELECT COALESCE(SUM(total_price), 0) FROM orders WHERE status != 'bekor_qilindi'"
        )
        row = await cursor.fetchone()
        stats["total_revenue"] = row[0]

        cursor = await db.execute("SELECT COUNT(DISTINCT user_id) FROM orders")
        row = await cursor.fetchone()
        stats["unique_customers"] = row[0]

        return stats