#!/usr/bin/env python3
"""
Foreign Key Constraint Investigation
CRITICAL: Check if foreign key constraints actually exist in database
"""

import os
import psycopg2
from dotenv import load_dotenv

def check_foreign_keys():
    """Check actual foreign key constraints in database"""
    
    load_dotenv()
    database_url = os.getenv('DATABASE_URL')
    
    print("=" * 60)
    print("FOREIGN KEY CONSTRAINT INVESTIGATION")
    print("=" * 60)
    
    try:
        conn = psycopg2.connect(database_url)
        cursor = conn.cursor()
        
        # Check jobs table structure
        print("JOBS TABLE STRUCTURE:")
        cursor.execute("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns 
            WHERE table_name = 'jobs'
            ORDER BY ordinal_position;
        """)
        
        job_columns = cursor.fetchall()
        
        if job_columns:
            for column, data_type, nullable in job_columns:
                print(f"  - {column}: {data_type} ({'NULL' if nullable == 'YES' else 'NOT NULL'})")
        else:
            print("  JOBS TABLE NOT FOUND!")
        
        # Check if category_id column exists
        category_id_exists = any(col[0] == 'category_id' for col in job_columns)
        print(f"\nCATEGORY_ID COLUMN EXISTS: {category_id_exists}")
        
        # Check foreign key constraints on jobs table
        print("\nFOREIGN KEY CONSTRAINTS ON JOBS TABLE:")
        cursor.execute("""
            SELECT 
                tc.constraint_name,
                tc.table_name,
                kcu.column_name,
                ccu.table_name AS foreign_table_name,
                ccu.column_name AS foreign_column_name 
            FROM information_schema.table_constraints AS tc 
            JOIN information_schema.key_column_usage AS kcu
                ON tc.constraint_name = kcu.constraint_name
                AND tc.table_schema = kcu.table_schema
            JOIN information_schema.constraint_column_usage AS ccu
                ON ccu.constraint_name = tc.constraint_name
                AND ccu.table_schema = tc.table_schema
            WHERE tc.constraint_type = 'FOREIGN KEY' 
            AND tc.table_name='jobs';
        """)
        
        foreign_keys = cursor.fetchall()
        
        if foreign_keys:
            for constraint_name, table_name, column_name, foreign_table, foreign_column in foreign_keys:
                print(f"  ✓ {constraint_name}: {table_name}.{column_name} -> {foreign_table}.{foreign_column}")
        else:
            print("  ✗ NO FOREIGN KEY CONSTRAINTS FOUND ON JOBS TABLE!")
        
        # Check category_id specifically
        category_fk_exists = any('category' in fk[0].lower() for fk in foreign_keys)
        print(f"\nCATEGORY FOREIGN KEY EXISTS: {category_fk_exists}")
        
        # Check if we can create a test foreign key
        print("\nTEST FOREIGN KEY CREATION:")
        if category_id_exists and not category_fk_exists:
            print("category_id column exists but foreign key constraint missing!")
            print("This explains the SQLAlchemy error!")
            
            # Show the SQL to create the missing foreign key
            print("\nSQL TO FIX THE ISSUE:")
            print("ALTER TABLE jobs ADD CONSTRAINT fk_jobs_category_id")
            print("  FOREIGN KEY (category_id) REFERENCES categories(id);")
        
        # Check jobs table row count
        cursor.execute("SELECT COUNT(*) FROM jobs;")
        job_count = cursor.fetchone()[0]
        print(f"\nJOBS TABLE ROW COUNT: {job_count}")
        
        # If jobs exist, check category_id values
        if job_count > 0 and category_id_exists:
            cursor.execute("""
                SELECT category_id, COUNT(*) 
                FROM jobs 
                GROUP BY category_id 
                ORDER BY category_id;
            """)
            category_usage = cursor.fetchall()
            print(f"CATEGORY_ID USAGE IN JOBS:")
            for cat_id, count in category_usage:
                print(f"  - Category ID {cat_id}: {count} jobs")
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"ERROR: {e}")

if __name__ == "__main__":
    check_foreign_keys()