#!/usr/bin/env python3
"""
Check Oracle packages and database structure
"""

import oracledb
import os
from dotenv import load_dotenv

# Load environment
load_dotenv('.env')

# Oracle configuration
user = os.environ['ORACLE_USER']
password = os.environ['ORACLE_PASSWORD']
dsn = os.environ['ORACLE_DSN']

# Initialize Oracle client (thick mode)
try:
    instantclient_path = os.environ.get('INSTANTCLIENTPATH', '/opt/oracle/instantclient_23_9')
    oracledb.init_oracle_client(lib_dir=instantclient_path)
    print(f"✅ Oracle thick mode initialized: {instantclient_path}")
except Exception as e:
    print(f"⚠️ Oracle thick mode failed, using thin mode: {e}")

def check_packages():
    """Check available packages in Oracle"""
    print("\n🔍 Checking Oracle Packages...")
    
    try:
        with oracledb.connect(user=user, password=password, dsn=dsn) as conn:
            with conn.cursor() as cur:
                
                # Check user packages
                cur.execute("""
                    SELECT object_name, object_type, status
                    FROM user_objects 
                    WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
                    ORDER BY object_name, object_type
                """)
                
                packages = cur.fetchall()
                if packages:
                    print(f"Found {len(packages)} package objects:")
                    for pkg in packages:
                        print(f"  - {pkg[0]} ({pkg[1]}) - {pkg[2]}")
                else:
                    print("❌ No packages found in current schema")
                
                # Check if specific packages exist
                print("\n🔍 Checking specific packages...")
                for pkg_name in ['IMPORT_PARTENERI', 'IMPORT_COMENZI']:
                    cur.execute("""
                        SELECT COUNT(*) FROM user_objects 
                        WHERE object_name = ? AND object_type = 'PACKAGE'
                    """, [pkg_name])
                    
                    exists = cur.fetchone()[0] > 0
                    print(f"  - {pkg_name}: {'✅ EXISTS' if exists else '❌ NOT FOUND'}")
                
    except Exception as e:
        print(f"❌ Check packages failed: {e}")

def check_tables():
    """Check available tables"""
    print("\n🔍 Checking Oracle Tables...")
    
    try:
        with oracledb.connect(user=user, password=password, dsn=dsn) as conn:
            with conn.cursor() as cur:
                
                # Check main tables
                tables_to_check = ['ARTICOLE_TERTI', 'PARTENERI', 'COMENZI', 'NOM_ARTICOLE']
                
                for table_name in tables_to_check:
                    cur.execute("""
                        SELECT COUNT(*) FROM user_tables 
                        WHERE table_name = ?
                    """, [table_name])
                    
                    exists = cur.fetchone()[0] > 0
                    
                    if exists:
                        cur.execute(f"SELECT COUNT(*) FROM {table_name}")
                        count = cur.fetchone()[0]
                        print(f"  - {table_name}: ✅ EXISTS ({count} records)")
                    else:
                        print(f"  - {table_name}: ❌ NOT FOUND")
                
    except Exception as e:
        print(f"❌ Check tables failed: {e}")

def main():
    """Run all checks"""
    print("🔍 Oracle Database Structure Check")
    print("=" * 50)
    
    check_packages()
    check_tables()

if __name__ == "__main__":
    main()