import asyncio
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
import os
import json
from dotenv import load_dotenv

load_dotenv()
DATABASE_URL = os.getenv("DATABASE_URL")

engine = create_async_engine(DATABASE_URL)
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def check_analysis():
    from app.services.stock_ledger_service import StockLedgerService
    
    async with async_session() as session:
        print("Fetching Lot Analysis...")
        analysis = await StockLedgerService.get_lot_analysis(session)
        
        print(f"\nTotal items in analysis: {len(analysis)}")
        
        # Sort by mirchi_name and lot_number for easier reading
        analysis.sort(key=lambda x: (x['mirchi_name'], x['lot_number']))
        
        print("\n--- Regular Transactions Analysis (is_patti=False) ---")
        regular = [item for item in analysis if not item.get('is_patti')]
        for item in regular[:20]: # Show first 20
            print(f"Mirchi: {item['mirchi_name']:10} | Lot: {str(item['lot_number']):15} | Farmer: {item['farmer_name']:20} | In: {item['buy_qty']:8.2f} | Out: {item['sell_qty']:8.2f} | Bal: {item['buy_qty'] - item['sell_qty']:8.2f}")

        print("\n--- Patti Transactions Analysis (is_patti=True) ---")
        patti = [item for item in analysis if item.get('is_patti')]
        for item in patti[:20]: # Show first 20
            print(f"Mirchi: {item['mirchi_name']:10} | Lot: {str(item['lot_number']):15} | Farmer: {item['farmer_name']:20} | In: {item['buy_qty']:8.2f} | Out: {item['sell_qty']:8.2f} | Bal: {item['buy_qty'] - item['sell_qty']:8.2f}")

if __name__ == "__main__":
    asyncio.run(check_analysis())