import psycopg
from psycopg.rows import dict_row

try:
    # Check database connection
    conn = psycopg.connect(
        host="127.0.0.1",
        port=5432,
        dbname="njts_accounting",
        user="postgres",
        password="postgres",
        connect_timeout=2
    )
    
    with conn:
        with conn.cursor(row_factory=dict_row) as cur:
            # 1. Check is_latest column exists
            print("1. Checking if is_latest column exists...")
            cur.execute("SELECT COUNT(*) as cnt FROM information_schema.columns WHERE table_name = 'journal_entries' AND column_name = 'is_latest'")
            has_col = cur.fetchone()['cnt'] > 0
            print(f"   is_latest column exists: {has_col}")
            
            # 2. Check entries 431 and 432
            print("\n2. Checking entries 431 and 432...")
            cur.execute("SELECT journal_entry_id, is_latest FROM journal_entries WHERE journal_entry_id IN (431, 432) ORDER BY journal_entry_id")
            for row in cur.fetchall():
                print(f"   ID={row['journal_entry_id']}: is_latest={row['is_latest']}")
            
            # 3. Check 701 account by is_latest
            print("\n3. Checking 701 account details by is_latest...")
            cur.execute("""
                SELECT 
                    j.is_latest,
                    COUNT(*) as entry_count,
                    COALESCE(SUM(l.debit), 0) as total_debit,
                    COALESCE(SUM(l.credit), 0) as total_credit
                FROM journal_lines l
                JOIN journal_entries j ON j.journal_entry_id = l.journal_entry_id
                JOIN accounts a ON a.account_id = l.account_id
                WHERE a.account_code = '701'
                AND j.is_deleted = false
                GROUP BY j.is_latest
                ORDER BY j.is_latest DESC
            """)
            for row in cur.fetchall():
                status = "Latest" if row['is_latest'] else "Old"
                print(f"   {status}: {row['entry_count']} entries | Debit: {row['total_debit']}, Credit: {row['total_credit']}")
                
    conn.close()
    
except Exception as e:
    print(f"Error: {e}")
    import traceback
    traceback.print_exc()