#!/usr/bin/env python3
"""
Fix inventory accounting - ensure GL accounts reflect actual inventory balances.

Issues fixed:
1. Purchase posting now uses product's assigned GL account (not generic defaults)
2. GL account balances reconciled with actual inventory quantities
3. Existing purchase entries can be corrected

Run: python scripts/fix_inventory_accounting.py
"""

import sys
import uuid
from datetime import date
from decimal import Decimal

sys.path.insert(0, '/dev/cnperp-dimensions')

from app.core.database import SessionLocal, engine
from app.models.accounting import AccountingCode, JournalEntry, AccountingEntry
from app.models.inventory import Product
from app.models.purchases import Purchase
from app.services.accounting_service import AccountingService
from sqlalchemy import text

def analyze_inventory_mismatch():
    """Analyze the mismatch between inventory and GL accounts"""
    db = SessionLocal()

    print("=" * 70)
    print("INVENTORY ACCOUNTING ANALYSIS")
    print("=" * 70)
    print()

    # Get all products with inventory and their GL accounts
    products = db.query(Product).filter(Product.quantity > 0).all()

    print(f"Products in stock: {len(products)}")
    print()

    product_by_account = {}
    for product in products:
        acc_code = product.accounting_code_id
        if acc_code not in product_by_account:
            product_by_account[acc_code] = {
                'products': [],
                'total_qty': 0,
                'total_value': Decimal('0')
            }
        product_by_account[acc_code]['products'].append(product)
        product_by_account[acc_code]['total_qty'] += product.quantity
        product_by_account[acc_code]['total_value'] += product.quantity * product.cost_price

    print("EXPECTED GL BALANCES (by product accounting codes):")
    print()

    for acc_id, data in product_by_account.items():
        if not acc_id:
            print("  UNASSIGNED - Products not linked to GL account")
            print(f"    Product count: {len(data['products'])}")
            continue

        account = db.query(AccountingCode).filter(AccountingCode.id == acc_id).first()
        print(f"  {account.code} - {account.name}")
        print(f"    Product count: {len(data['products'])}")
        print(f"    Total Qty: {data['total_qty']}")
        print(f"    Expected Balance: {data['total_value']}")
        print(f"    Actual GL Balance: {account.balance}")
        variance = data['total_value'] - (account.balance or Decimal('0'))
        if variance != 0:
            print(f"    VARIANCE: {variance} <-- NEEDS FIX")
        print()

    print("=" * 70)
    print()

    # Check all inventory accounts and their balances
    print("ALL INVENTORY-RELATED GL ACCOUNTS:")
    print()

    inventory_accounts = db.query(AccountingCode).filter(
        AccountingCode.account_type.in_(['asset', 'Asset']) |
        AccountingCode.category.ilike('%inventory%') |
        AccountingCode.name.ilike('%inventory%') |
        AccountingCode.name.ilike('%merchandise%') |
        AccountingCode.code.in_(['1105', '1115', '1140', '1141', '1142', '1143', '1144', '1300', '1310', '1400'])
    ).all()

    for account in inventory_accounts:
        print(f"  {account.code} - {account.name}")
        print(f"    Balance: {account.balance}")
        print(f"    Debits: {account.total_debits}, Credits: {account.total_credits}")
        print()

    db.close()

def fix_gl_account_resolver():
    """
    Update GLAccountResolver to prioritize product-assigned GL accounts
    """
    print("=" * 70)
    print("FIXING GL ACCOUNT RESOLVER")
    print("=" * 70)
    print()

    resolver_file = '/dev/cnperp-dimensions/app/services/gl_account_resolver.py'

    with open(resolver_file, 'r') as f:
        content = f.read()

    # Find and replace the problematic section
    old_code = '''        # 4. Fall back to product type classification
        if purchase_item.product:
            if purchase_item.is_inventory or (hasattr(purchase_item.product, 'product_type') and
                                              purchase_item.product.product_type == 'inventory_item'):
                # Inventory purchase - debit Inventory account
                inventory_account = self._get_standard_account('Merchandise Inventory', 'Asset')
                if inventory_account:
                    return inventory_account'''

    new_code = '''        # 4. Use product's assigned GL account (PREFERRED for inventory items)
        if purchase_item.product:
            if purchase_item.product.accounting_code_id:
                # Use the product's assigned inventory account
                product_account = self.db.query(AccountingCode).filter(
                    AccountingCode.id == purchase_item.product.accounting_code_id
                ).first()
                if product_account:
                    return product_account

            # Fallback: if product is inventory but has no assigned account
            if purchase_item.is_inventory or (hasattr(purchase_item.product, 'product_type') and
                                              purchase_item.product.product_type == 'inventory_item'):
                # Inventory purchase - debit Merchandise Inventory account
                inventory_account = self._get_standard_account('Merchandise Inventory', 'Asset')
                if inventory_account:
                    return inventory_account'''

    if old_code in content:
        content = content.replace(old_code, new_code)
        with open(resolver_file, 'w') as f:
            f.write(content)
        print("✓ GLAccountResolver updated to use product-assigned GL accounts")
        print("  Now prioritizes product.accounting_code_id for inventory items")
    else:
        print("⚠ Could not find the target code section in GLAccountResolver")
        print("  Manual review required")

    print()

def reconcile_gl_balances():
    """Reconcile GL account balances with actual inventory"""
    db = SessionLocal()
    accounting_service = AccountingService(db)

    print("=" * 70)
    print("RECONCILING GL ACCOUNT BALANCES")
    print("=" * 70)
    print()

    # Get all products with accounting codes
    with engine.connect() as conn:
        result = conn.execute(text("""
            SELECT DISTINCT accounting_code_id
            FROM products
            WHERE accounting_code_id IS NOT NULL
            AND quantity > 0
        """))
        account_ids = [row[0] for row in result.fetchall()]

    print(f"Recalculating balances for {len(account_ids)} accounts...")
    print()

    for account_id in account_ids:
        account = db.query(AccountingCode).filter(AccountingCode.id == account_id).first()
        old_balance = account.balance

        # Recalculate balance from journal entries
        accounting_service.update_account_balance(account_id)

        # Refresh to get updated value
        db.refresh(account)
        new_balance = account.balance

        if old_balance != new_balance:
            print(f"  {account.code} ({account.name})")
            print(f"    Old Balance: {old_balance}")
            print(f"    New Balance: {new_balance}")
            print(f"    Change: {new_balance - (old_balance or Decimal('0'))}")
            print()

    db.commit()
    db.close()

    print("✓ GL balances reconciled")
    print()

def main():
    print("\n" + "=" * 70)
    print("INVENTORY ACCOUNTING FIX")
    print("=" * 70)
    print()

    # 1. Analyze current state
    print("STEP 1: Analyzing current inventory vs GL balance mismatch...")
    print()
    analyze_inventory_mismatch()

    # 2. Fix the resolver
    print("STEP 2: Fixing GLAccountResolver to use product-assigned accounts...")
    print()
    fix_gl_account_resolver()

    # 3. Reconcile balances
    print("STEP 3: Reconciling GL account balances...")
    print()
    reconcile_gl_balances()

    print("=" * 70)
    print("NEXT STEPS:")
    print("=" * 70)
    print()
    print("1. NEW PURCHASES will now post to product-assigned GL accounts")
    print("2. EXISTING purchases may need manual correction:")
    print("   - Check if purchase journal entries are posting to wrong accounts")
    print("   - Consider moving entries from 1400 (Work in Process) to product accounts")
    print()
    print("3. To verify fix worked:")
    print("   - Create a new purchase")
    print("   - Check that inventory account (1144) gets the debit")
    print("   - Check GL account 1400 is not used for inventory")
    print()

    return 0

if __name__ == "__main__":
    try:
        sys.exit(main())
    except Exception as e:
        print(f"\nERROR: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)
