#!/usr/bin/env python3
"""
Simple script to diagnose the current state of tables
"""
import os
import sys
import psycopg2
from psycopg2.extras import RealDictCursor

# Add the backend directory to the path
sys.path.append(os.path.join(os.path.dirname(__file__), 'backend'))

def get_db_connection():
    """Create a database connection"""
    try:
        conn = psycopg2.connect(
            host="localhost",
            database="bookbeach",
            user="postgres",
            password="F@f@k0s!!",  # Correct password from setup script
            port=5432
        )
        return conn
    except Exception as e:
        print(f"Error connecting to database: {e}")
        return None

def check_table_columns(conn, table_name):
    """Check the columns in a table"""
    try:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT column_name, data_type 
            FROM information_schema.columns 
            WHERE table_name = %s 
            ORDER BY ordinal_position
        """, (table_name,))
        
        columns = cursor.fetchall()
        print(f"\nTable: {table_name}")
        for column in columns:
            print(f"  {column[0]} ({column[1]})")
        
        cursor.close()
        return columns
    except Exception as e:
        print(f"Error checking table {table_name}: {e}")
        return []

def check_constraints(conn, table_name):
    """Check the constraints in a table"""
    try:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT constraint_name, constraint_type
            FROM information_schema.table_constraints
            WHERE table_name = %s
        """, (table_name,))
        
        constraints = cursor.fetchall()
        print(f"\nConstraints for table: {table_name}")
        for constraint in constraints:
            print(f"  {constraint[0]} ({constraint[1]})")
        
        cursor.close()
        return constraints
    except Exception as e:
        print(f"Error checking constraints for table {table_name}: {e}")
        return []

def main():
    print("Diagnosing database tables...")
    
    # Get database connection
    conn = get_db_connection()
    if not conn:
        print("Failed to connect to database")
        return
    
    try:
        # Check key tables
        key_tables = [
            'users', 'companies', 'beach_places', 'restaurants', 
            'restaurant_items', 'markets', 'market_items', 
            'adventures', 'bookings', 'reviews'
        ]
        
        for table in key_tables:
            check_table_columns(conn, table)
            check_constraints(conn, table)
        
    except Exception as e:
        print(f"Error during diagnosis: {e}")
        import traceback
        traceback.print_exc()
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    main()