#!/usr/bin/env python3
"""
Create Missing GL Accounts using Raw SQL

Uses SQLAlchemy raw SQL to bypass ORM initialization issues.
Creates the 21 missing GL account codes needed for the item type routing system.
"""

import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app.core.database import engine
from sqlalchemy import text
import uuid
from datetime import datetime


def main():
    print("\n" + "="*80)
    print("CREATE MISSING GL ACCOUNTS (SQL-Based)")
    print("="*80 + "\n")

    # Define the missing GL accounts
    missing_accounts = [
        # Inventory accounts
        ("1105", "Raw Materials", "Asset", "Inventory"),
        ("1115", "Finished Goods", "Asset", "Inventory"),

        # Fixed Asset accounts
        ("1150", "Prepaid Expenses", "Asset", "Current Asset"),
        ("1155", "Rental Deposits", "Asset", "Current Asset"),
        ("1201", "Buildings & Leasehold", "Asset", "Fixed Asset"),
        ("1205", "Machinery", "Asset", "Fixed Asset"),
        ("1215", "Furniture & Fixtures", "Asset", "Fixed Asset"),
        ("1230", "Computers & Equipment", "Asset", "Fixed Asset"),

        # Intangible Asset accounts
        ("1301", "Patents & Trademarks", "Asset", "Intangible Assets"),
        ("1305", "Licenses & Franchises", "Asset", "Intangible Assets"),
        ("1315", "Software", "Asset", "Intangible Assets"),

        # Expense accounts
        ("5010", "General Expenses", "Expense", "Supplies"),
        ("5020", "Office Supplies", "Expense", "Supplies"),
        ("5030", "Consumables", "Expense", "Supplies"),
        ("5040", "Maintenance & Repairs", "Expense", "Maintenance"),
        ("5050", "Professional Services", "Expense", "Service"),
        ("5060", "Travel Expenses", "Expense", "Travel"),
        ("5070", "Insurance Expense", "Expense", "Insurance"),
        ("5080", "Rent and Lease", "Expense", "Rent"),
        ("5090", "Advertising & Marketing", "Expense", "Marketing"),
        ("5900", "Miscellaneous Expense", "Expense", "Miscellaneous"),
    ]

    with engine.connect() as conn:
        # First check which accounts exist
        existing_codes = set()
        result = conn.execute(text("SELECT code FROM accounting_codes"))
        for row in result:
            existing_codes.add(row[0])

        print(f"Current GL accounts in database: {len(existing_codes)}\n")

        created_count = 0
        failed_count = 0

        for code, name, account_type, category in missing_accounts:
            if code in existing_codes:
                print(f"[EXIST] {code:6} - {name:35}")
                continue

            try:
                # Insert the account
                new_id = str(uuid.uuid4())
                now = datetime.utcnow()

                insert_sql = text("""
                    INSERT INTO accounting_codes (
                        id, code, name, account_type, category,
                        total_debits, total_credits, balance, currency,
                        is_parent, is_dimensional_template, sort_order,
                        created_at, updated_at
                    ) VALUES (
                        :id, :code, :name, :account_type, :category,
                        0, 0, 0, 'PHP',
                        false, false, :sort_order,
                        :created_at, :updated_at
                    )
                """)

                conn.execute(insert_sql, {
                    "id": new_id,
                    "code": code,
                    "name": name,
                    "account_type": account_type,
                    "category": category,
                    "sort_order": int(code),
                    "created_at": now,
                    "updated_at": now,
                })

                print(f"[OK   ] {code:6} - {name:35}")
                created_count += 1

            except Exception as e:
                print(f"[ERROR] {code:6} - {name:35} - {str(e)}")
                failed_count += 1

        # Commit all changes
        conn.commit()

    # Summary
    print("\n" + "="*80)
    print(f"SUMMARY: Created {created_count} GL accounts, {failed_count} failed")

    # Verify final count
    with engine.connect() as conn:
        result = conn.execute(text("SELECT COUNT(*) as count FROM accounting_codes"))
        final_count = result.fetchone()[0]
        print(f"Total GL accounts in database: {final_count}")

    print("\n" + "="*80)
    print("Next: Run 'python scripts/complete_seed_item_types.py' to link item types")
    print("="*80 + "\n")

    return failed_count == 0


if __name__ == "__main__":
    try:
        success = main()
        sys.exit(0 if success else 1)
    except Exception as e:
        print(f"\n[FATAL] {str(e)}")
        import traceback
        traceback.print_exc()
        sys.exit(1)
