#!/usr/bin/env python3
"""
Local Database Table Investigation
CRITICAL: Check if tables exist and their actual names
"""

import os
import psycopg2
from dotenv import load_dotenv

def check_local_database():
    """Check what tables exist in local database"""
    
    load_dotenv()
    database_url = os.getenv('DATABASE_URL')
    
    print("=" * 60)
    print("LOCAL DATABASE TABLE INVESTIGATION")
    print(f"Database URL: {database_url[:50]}...")
    print("=" * 60)
    
    try:
        conn = psycopg2.connect(database_url)
        cursor = conn.cursor()
        
        # Check for category-related tables
        print("\nSEARCHING FOR CATEGORY TABLES:")
        cursor.execute("""
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_schema = 'public' 
            AND table_name LIKE '%categor%';
        """)
        
        category_tables = cursor.fetchall()
        
        if category_tables:
            print("FOUND CATEGORY TABLES:")
            for table in category_tables:
                table_name = table[0]
                print(f"  - {table_name}")
                
                # Get row count for each
                cursor.execute(f"SELECT COUNT(*) FROM {table_name};")
                count = cursor.fetchone()[0]
                print(f"    Row count: {count}")
                
                # Get sample data if exists
                if count > 0:
                    cursor.execute(f"SELECT * FROM {table_name} LIMIT 3;")
                    samples = cursor.fetchall()
                    
                    # Get column names
                    cursor.execute(f"""
                        SELECT column_name 
                        FROM information_schema.columns 
                        WHERE table_name = '{table_name}' 
                        ORDER BY ordinal_position;
                    """)
                    columns = [row[0] for row in cursor.fetchall()]
                    print(f"    Columns: {', '.join(columns)}")
                    print(f"    Sample data (first 3 rows):")
                    for row in samples:
                        print(f"      {dict(zip(columns, row))}")
        else:
            print("NO CATEGORY TABLES FOUND!")
        
        # Check ALL tables
        print("\nALL TABLES IN LOCAL DATABASE:")
        cursor.execute("""
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_schema = 'public' 
            ORDER BY table_name;
        """)
        
        all_tables = cursor.fetchall()
        
        for table in all_tables:
            table_name = table[0]
            cursor.execute(f"SELECT COUNT(*) FROM {table_name};")
            count = cursor.fetchone()[0]
            print(f"  - {table_name}: {count} rows")
        
        print(f"\nTOTAL TABLES: {len(all_tables)}")
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"ERROR CONNECTING TO LOCAL DATABASE: {e}")
        
        # Try alternative connection methods
        print("\nTRYING ALTERNATIVE CONNECTION...")
        try:
            # Try simpler connection
            import subprocess
            result = subprocess.run(['psql', database_url, '-c', 
                "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';"],
                capture_output=True, text=True, timeout=10)
            
            if result.returncode == 0:
                print("ALTERNATIVE CONNECTION SUCCESS:")
                print(result.stdout)
            else:
                print(f"ALTERNATIVE CONNECTION FAILED: {result.stderr}")
                
        except Exception as e2:
            print(f"ALTERNATIVE CONNECTION ERROR: {e2}")

if __name__ == "__main__":
    check_local_database()