#!/usr/bin/env python3
"""
Check the database schema for arc_puzzles table
"""

import os
import sys
import pymysql
from dotenv import load_dotenv

def load_env_config():
    """Load database configuration from .env file"""
    load_dotenv()

    config = {
        'host': os.getenv('DB_HOST'),
        'user': os.getenv('DB_USER'),
        'password': os.getenv('DB_PASSWORD'),
        'database': os.getenv('DB_NAME'),
        'port': int(os.getenv('DB_PORT', 3306)),
        'charset': 'utf8mb4'
    }

    return config

def main():
    print("Checking Database Schema")
    print("=" * 50)

    # Load configuration
    try:
        config = load_env_config()
        print(f"✓ Loaded configuration from .env")
        print(f"  Host: {config['host']}")
        print(f"  Database: {config['database']}")
        print(f"  User: {config['user']}")
    except Exception as e:
        print(f"✗ Error loading configuration: {e}")
        return 1

    # Connect to database
    try:
        print(f"\nConnecting to database...")
        connection = pymysql.connect(**config)
        print(f"✓ Connected successfully")
    except Exception as e:
        print(f"✗ Database connection failed: {e}")
        return 1

    try:
        cursor = connection.cursor()

        # Show all tables
        print("\n" + "=" * 50)
        print("TABLES IN DATABASE:")
        print("=" * 50)
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()
        for table in tables:
            print(f"  - {table[0]}")

        # Check for arc_puzzles table
        cursor.execute("SHOW TABLES LIKE 'arc_puzzles'")
        if cursor.fetchone():
            print("\n" + "=" * 50)
            print("SCHEMA FOR 'arc_puzzles' TABLE:")
            print("=" * 50)
            cursor.execute("DESCRIBE arc_puzzles")
            columns = cursor.fetchall()
            for col in columns:
                print(f"  {col[0]:<20} {col[1]:<20} Null:{col[2]} Key:{col[3]} Default:{col[4]}")

            # Get sample data
            cursor.execute("SELECT * FROM arc_puzzles LIMIT 3")
            print("\n" + "=" * 50)
            print("SAMPLE DATA (first 3 rows):")
            print("=" * 50)
            rows = cursor.fetchall()
            if rows:
                # Get column names
                cursor.execute("DESCRIBE arc_puzzles")
                columns = [col[0] for col in cursor.fetchall()]
                print("  Columns:", ", ".join(columns))
                for i, row in enumerate(rows, 1):
                    print(f"\n  Row {i}:")
                    for col_name, value in zip(columns, row):
                        if col_name == 'json':
                            print(f"    {col_name}: [JSON data, length={len(str(value))}]")
                        else:
                            print(f"    {col_name}: {value}")
            else:
                print("  (No data in table)")
        else:
            print("\n✗ Table 'arc_puzzles' does not exist")

        # Check for arc_jsons table
        cursor.execute("SHOW TABLES LIKE 'arc_jsons'")
        if cursor.fetchone():
            print("\n" + "=" * 50)
            print("SCHEMA FOR 'arc_jsons' TABLE:")
            print("=" * 50)
            cursor.execute("DESCRIBE arc_jsons")
            columns = cursor.fetchall()
            for col in columns:
                print(f"  {col[0]:<20} {col[1]:<20} Null:{col[2]} Key:{col[3]} Default:{col[4]}")

    except Exception as e:
        print(f"\n✗ Error querying database: {e}")
        return 1
    finally:
        connection.close()
        print(f"\n✓ Database connection closed")

    return 0

if __name__ == '__main__':
    sys.exit(main())