#!/usr/bin/env python
"""
Create all Django system tables directly in the database
"""
import pymysql
import os
from dotenv import load_dotenv

load_dotenv()

# Database connection
connection = pymysql.connect(
    host=os.getenv('DB_HOST', 'localhost'),
    user=os.getenv('DB_USER'),
    password=os.getenv('DB_PASSWORD'),
    database=os.getenv('DB_NAME'),
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

print("=" * 80)
print("CREATING DJANGO SYSTEM TABLES")
print("=" * 80)

try:
    with connection.cursor() as cursor:
        # Create django_session table
        print("\n1. Creating django_session table...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS `django_session` (
              `session_key` varchar(40) NOT NULL,
              `session_data` longtext NOT NULL,
              `expire_date` datetime(6) NOT NULL,
              PRIMARY KEY (`session_key`),
              KEY `django_session_expire_date_a5c62663` (`expire_date`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        print("   ✓ django_session table created")
        
        # Create django_content_type table
        print("\n2. Creating django_content_type table...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS `django_content_type` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `app_label` varchar(100) NOT NULL,
              `model` varchar(100) NOT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `django_content_type_app_label_model_76bd3d3b_uniq` (`app_label`,`model`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        print("   ✓ django_content_type table created")
        
        # Create django_migrations table
        print("\n3. Creating django_migrations table...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS `django_migrations` (
              `id` bigint(20) NOT NULL AUTO_INCREMENT,
              `app` varchar(255) NOT NULL,
              `name` varchar(255) NOT NULL,
              `applied` datetime(6) NOT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        print("   ✓ django_migrations table created")
        
        # Create django_admin_log table
        print("\n4. Creating django_admin_log table...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS `django_admin_log` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `action_time` datetime(6) NOT NULL,
              `object_id` longtext,
              `object_repr` varchar(200) NOT NULL,
              `action_flag` smallint(5) unsigned NOT NULL,
              `change_message` longtext NOT NULL,
              `content_type_id` int(11) DEFAULT NULL,
              `user_id` char(32) NOT NULL,
              PRIMARY KEY (`id`),
              KEY `django_admin_log_content_type_id_c4bce8eb` (`content_type_id`),
              KEY `django_admin_log_user_id_c564eba6` (`user_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        print("   ✓ django_admin_log table created")
        
        connection.commit()
        
        # Verify tables exist
        print("\n" + "=" * 80)
        print("VERIFICATION")
        print("=" * 80)
        
        cursor.execute("SHOW TABLES LIKE 'django_%'")
        tables = cursor.fetchall()
        
        print("\nDjango tables in database:")
        for table in tables:
            table_name = list(table.values())[0]
            print(f"  ✓ {table_name}")
        
        print("\n" + "=" * 80)
        print("✓ SUCCESS - All Django system tables created!")
        print("=" * 80)
        print("\nYou can now login to the system.")
        
except Exception as e:
    print("\n" + "=" * 80)
    print("✗ ERROR")
    print("=" * 80)
    print(f"Error: {str(e)}")
    connection.rollback()
finally:
    connection.close()