#!/usr/bin/env python3
"""
Simple script to check notebook assets status using psycopg2 directly.
"""
import os
import sys
import json
from datetime import datetime

try:
    import psycopg2
    from psycopg2.extras import RealDictCursor

    # Connect to database
    conn = psycopg2.connect(
        host="127.0.0.1",
        port=5432,
        database="jadwa",
        user="postgres",
        password=""  # Try with no password first
    )

    print("=" * 80)
    print("NOTEBOOK ASSETS FOR REPORT 35")
    print("=" * 80)

    cursor = conn.cursor(cursor_factory=RealDictCursor)
    cursor.execute("""
        SELECT id, report_id, asset_type, status, file_path, file_format, error_message, created_at, completed_at
        FROM notebook_assets
        WHERE report_id = 35
        ORDER BY created_at
    """)

    assets = cursor.fetchall()

    if not assets:
        print("No assets found in database for report 35")
    else:
        print(f"\nFound {len(assets)} assets in database:\n")

        for asset in assets:
            print(f"Asset ID: {asset['id']}")
            print(f"  Type: {asset['asset_type']}")
            print(f"  Status: {asset['status']}")
            print(f"  File Path: {asset['file_path']}")
            print(f"  File Format: {asset['file_format']}")
            print(f"  Error: {asset['error_message']}")
            print(f"  Created: {asset['created_at']}")
            print(f"  Completed: {asset['completed_at']}")
            print("  " + "-" * 60)

    print("\n" + "=" * 80)
    print("FILES ON DISK FOR REPORT 35")
    print("=" * 80)

    upload_dir = '/home/ashraffarid2010/jadwaai.com/app/static/uploads/notebooks/35'

    if os.path.exists(upload_dir):
        files = os.listdir(upload_dir)
        print(f"\nFound {len(files)} files in {upload_dir}:\n")

        for filename in sorted(files):
            filepath = os.path.join(upload_dir, filename)
            size = os.path.getsize(filepath)
            size_mb = size / (1024 * 1024)
            mtime = datetime.fromtimestamp(os.path.getmtime(filepath))
            print(f"  {filename}")
            print(f"    Size: {size_mb:.2f} MB")
            print(f"    Modified: {mtime}")
            print()
    else:
        print(f"Upload directory does not exist: {upload_dir}")

    print("\n" + "=" * 80)
    print("MISMATCH ANALYSIS")
    print("=" * 80)

    # Check for files without database records
    if os.path.exists(upload_dir):
        files_on_disk = set(os.listdir(upload_dir))
        db_files = set()

        for asset in assets:
            if asset['file_path']:
                # Extract filename from path
                filename = os.path.basename(asset['file_path'])
                db_files.add(filename)

        files_without_db = files_on_disk - db_files

        if files_without_db:
            print(f"\n⚠️  Files on disk WITHOUT database records:")
            for f in files_without_db:
                print(f"  - {f}")
        else:
            print("\n✓ All files on disk have database records")

        # Check for database records without files
        db_records_without_files = []
        for asset in assets:
            if asset['file_path']:
                full_path = f"/home/ashraffarid2010/jadwaai.com/app/static/{asset['file_path']}"
                if not os.path.exists(full_path):
                    db_records_without_files.append(asset)

        if db_records_without_files:
            print(f"\n⚠️  Database records WITHOUT files on disk:")
            for asset in db_records_without_files:
                print(f"  - Type: {asset['asset_type']}, Path: {asset['file_path']}")
        else:
            print("\n✓ All database records have files on disk")

    # Analyze generating vs ready assets
    print("\n" + "=" * 80)
    print("STATUS ANALYSIS")
    print("=" * 80)

    generating_assets = [a for a in assets if a['status'] == 'generating']
    failed_assets = [a for a in assets if a['status'] == 'failed']
    ready_assets = [a for a in assets if a['status'] == 'ready']

    print(f"\nGenerating assets: {len(generating_assets)}")
    for asset in generating_assets:
        age = datetime.now() - asset['created_at']
        age_minutes = age.total_seconds() / 60
        print(f"  - {asset['asset_type']}: {age_minutes:.1f} minutes old")

    print(f"\nFailed assets: {len(failed_assets)}")
    for asset in failed_assets:
        print(f"  - {asset['asset_type']}: {asset['error_message']}")

    print(f"\nReady assets: {len(ready_assets)}")
    for asset in ready_assets:
        print(f"  - {asset['asset_type']}")

    cursor.close()
    conn.close()

    print("\n" + "=" * 80)

except ImportError:
    print("Error: psycopg2 not available. Trying with subprocess...")
    import subprocess
    result = subprocess.run([
        'python3', '-c',
        """
import os
import json
from datetime import datetime

print("=" * 80)
print("FILES ON DISK FOR REPORT 35")
print("=" * 80)

upload_dir = '/home/ashraffarid2010/jadwaai.com/app/static/uploads/notebooks/35'

if os.path.exists(upload_dir):
    files = os.listdir(upload_dir)
    print(f"\\nFound {len(files)} files in {upload_dir}:\\n")

    for filename in sorted(files):
        filepath = os.path.join(upload_dir, filename)
        size = os.path.getsize(filepath)
        size_mb = size / (1024 * 1024)
        mtime = datetime.fromtimestamp(os.path.getmtime(filepath))
        print(f"  {filename}")
        print(f"    Size: {size_mb:.2f} MB")
        print(f"    Modified: {mtime}")
        print()
else:
    print(f"Upload directory does not exist: {upload_dir}")

print("=" * 80)
        """
    ], capture_output=True, text=True)
    print(result.stdout)
    if result.stderr:
        print("Errors:", result.stderr)

except Exception as e:
    print(f"Error: {e}")
    import traceback
    traceback.print_exc()