#!/usr/bin/env python3
"""Find best selling books."""

import asyncio
import os
from mcp import ClientSession, StdioServerParameters
from mcp.client.stdio import stdio_client


async def main():
    server_params = StdioServerParameters(
        command="python3",
        args=["pg_mcp_server/server.py"],
        env=os.environ.copy(),
    )
    
    async with stdio_client(server_params) as (read, write):
        async with ClientSession(read, write) as session:
            await session.initialize()
            
            print("=" * 70)
            print("📚 BEST SELLING BOOKS ANALYSIS")
            print("=" * 70)
            
            # Query: Best sellers by quantity sold
            query_qty = """
                SELECT 
                    p.name as book_name,
                    p.price,
                    SUM(oi.quantity) as total_sold,
                    COUNT(DISTINCT oi.order_id) as orders,
                    SUM(oi.quantity * oi.unit_price) as total_revenue
                FROM products p
                JOIN order_items oi ON p.id = oi.product_id
                WHERE p.category = 'Books'
                GROUP BY p.id, p.name, p.price
                ORDER BY total_sold DESC
            """
            
            result = await session.call_tool("execute_query", {
                "query": query_qty,
                "limit": 10
            })
            print("\n🏆 By Quantity Sold:")
            print(result.content[0].text)
            
            # Query: Best sellers by revenue
            query_revenue = """
                SELECT 
                    p.name as book_name,
                    p.price,
                    SUM(oi.quantity) as total_sold,
                    SUM(oi.quantity * oi.unit_price) as total_revenue,
                    ROUND(AVG(oi.unit_price), 2) as avg_selling_price
                FROM products p
                JOIN order_items oi ON p.id = oi.product_id
                WHERE p.category = 'Books'
                GROUP BY p.id, p.name, p.price
                ORDER BY total_revenue DESC
            """
            
            result = await session.call_tool("execute_query", {
                "query": query_revenue,
                "limit": 10
            })
            print("\n💰 By Revenue:")
            print(result.content[0].text)
            
            # Overall books category stats
            query_stats = """
                SELECT 
                    p.category,
                    COUNT(DISTINCT p.id) as num_products,
                    SUM(oi.quantity) as total_units_sold,
                    SUM(oi.quantity * oi.unit_price) as total_revenue,
                    ROUND(AVG(oi.quantity), 1) as avg_qty_per_order
                FROM products p
                JOIN order_items oi ON p.id = oi.product_id
                GROUP BY p.category
                ORDER BY total_revenue DESC
            """
            
            result = await session.call_tool("execute_query", {
                "query": query_stats,
                "limit": 10
            })
            print("\n📊 Category Comparison:")
            print(result.content[0].text)


if __name__ == "__main__":
    os.environ["PG_CONNECTION_STRING"] = "postgresql://postgres:demo@localhost:5432/shop"
    asyncio.run(main())