import os
import json
import mysql.connector
from mysql.connector import errorcode

def get_db_connection(host, port, user, password, db_name=None):
    """Establishes connection to MySQL. Optionally connects to a specific database."""
    config = {
        'host': host,
        'port': port,
        'user': user,
        'password': password,
    }
    if db_name:
        config['database'] = db_name
    return mysql.connector.connect(**config)

def init_db(host, port, user, password, db_name):
    """Initializes the database and the item_bases table."""
    try:
        # Check if database exists by attempting connection
        conn = get_db_connection(host, port, user, password, db_name)
        conn.close()
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_BAD_DB_ERROR:
            # Database doesn't exist, connect without database name to create it
            print(f"Database '{db_name}' not found. Creating database...")
            conn = get_db_connection(host, port, user, password)
            cursor = conn.cursor()
            cursor.execute(f"CREATE DATABASE `{db_name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            cursor.close()
            conn.close()
        else:
            raise err

    # Connect to the target database to verify / create table
    conn = get_db_connection(host, port, user, password, db_name)
    cursor = conn.cursor()
    
    # Create main item_bases table with JSON support
    table_query = """
    CREATE TABLE IF NOT EXISTS item_bases (
        id VARCHAR(255) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        item_class VARCHAR(100),
        inherits_from VARCHAR(255),
        domain VARCHAR(50),
        drop_level INT,
        inventory_width INT,
        inventory_height INT,
        release_state VARCHAR(50),
        req_level INT,
        req_strength INT,
        req_dexterity INT,
        req_intelligence INT,
        visual_identity_id VARCHAR(255),
        visual_identity_dds VARCHAR(255),
        properties JSON,
        tags JSON,
        implicits JSON,
        skills_granted JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    cursor.execute(table_query)
    
    # Create skill_gems table with JSON support
    skill_table_query = """
    CREATE TABLE IF NOT EXISTS skill_gems (
        id VARCHAR(255) PRIMARY KEY,
        display_name VARCHAR(255),
        release_state VARCHAR(50),
        color VARCHAR(50),
        crafting_level INT,
        gem_type VARCHAR(50),
        icon_dds_file VARCHAR(255),
        skill_name VARCHAR(255),
        support_name VARCHAR(255),
        support_text TEXT,
        tutorial_video VARCHAR(255),
        ui_image VARCHAR(255),
        req_weight_dexterity INT,
        req_weight_intelligence INT,
        req_weight_strength INT,
        crafting_types JSON,
        grants_skills JSON,
        recommended_supports JSON,
        tags JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    cursor.execute(skill_table_query)
    
    # Create item_classes table with JSON support
    classes_table_query = """
    CREATE TABLE IF NOT EXISTS item_classes (
        id VARCHAR(255) PRIMARY KEY,
        category VARCHAR(100),
        category_id VARCHAR(100),
        name VARCHAR(255) NOT NULL,
        influence_tags JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    cursor.execute(classes_table_query)
    
    # Create item_uniques table
    uniques_table_query = """
    CREATE TABLE IF NOT EXISTS item_uniques (
        id VARCHAR(255) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        unique_id VARCHAR(255),
        item_class VARCHAR(100),
        inventory_width INT,
        inventory_height INT,
        is_alternate_art BOOLEAN,
        visual_identity_id VARCHAR(255),
        visual_identity_dds VARCHAR(255),
        renamed_version VARCHAR(255),
        base_version VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    cursor.execute(uniques_table_query)
    
    conn.commit()
    cursor.close()
    print("Database tables 'item_bases', 'skill_gems', 'item_classes', and 'item_uniques' verified/created successfully.")
    return conn

def bulk_upsert_items(conn, items_data):
    """Upserts list of items into database using batch processing."""
    if not items_data:
        return 0

    query = """
        INSERT INTO item_bases (
            id, name, item_class, inherits_from, domain, drop_level, 
            inventory_width, inventory_height, release_state,
            req_level, req_strength, req_dexterity, req_intelligence,
            visual_identity_id, visual_identity_dds,
            properties, tags, implicits, skills_granted
        ) VALUES (
            %s, %s, %s, %s, %s, %s,
            %s, %s, %s,
            %s, %s, %s, %s,
            %s, %s,
            %s, %s, %s, %s
        ) ON DUPLICATE KEY UPDATE
            name = VALUES(name),
            item_class = VALUES(item_class),
            inherits_from = VALUES(inherits_from),
            domain = VALUES(domain),
            drop_level = VALUES(drop_level),
            inventory_width = VALUES(inventory_width),
            inventory_height = VALUES(inventory_height),
            release_state = VALUES(release_state),
            req_level = VALUES(req_level),
            req_strength = VALUES(req_strength),
            req_dexterity = VALUES(req_dexterity),
            req_intelligence = VALUES(req_intelligence),
            visual_identity_id = VALUES(visual_identity_id),
            visual_identity_dds = VALUES(visual_identity_dds),
            properties = VALUES(properties),
            tags = VALUES(tags),
            implicits = VALUES(implicits),
            skills_granted = VALUES(skills_granted)
    """

    cursor = conn.cursor()
    batch_size = 500
    total_upserted = len(items_data)
    
    print(f"Upserting {total_upserted} items in batches of {batch_size}...")
    for i in range(0, total_upserted, batch_size):
        chunk = items_data[i:i + batch_size]
        cursor.executemany(query, chunk)
        conn.commit()
        print(f"Upserted {min(i + batch_size, total_upserted)} / {total_upserted} items...")
        
    cursor.close()
    return total_upserted

def clear_items_table(conn):
    """Truncates the item_bases table to empty all existing items."""
    cursor = conn.cursor()
    cursor.execute("TRUNCATE TABLE item_bases")
    conn.commit()
    cursor.close()
    print("Table 'item_bases' cleared successfully.")

def clear_skills_table(conn):
    """Truncates the skill_gems table to empty all existing skill gems."""
    cursor = conn.cursor()
    cursor.execute("TRUNCATE TABLE skill_gems")
    conn.commit()
    cursor.close()
    print("Table 'skill_gems' cleared successfully.")

def bulk_upsert_skills(conn, skills_data):
    """Upserts list of skill gems into database using batch processing."""
    if not skills_data:
        return 0

    query = """
        INSERT INTO skill_gems (
            id, display_name, release_state, color, crafting_level, gem_type,
            icon_dds_file, skill_name, support_name, support_text,
            tutorial_video, ui_image,
            req_weight_dexterity, req_weight_intelligence, req_weight_strength,
            crafting_types, grants_skills, recommended_supports, tags
        ) VALUES (
            %s, %s, %s, %s, %s, %s,
            %s, %s, %s, %s,
            %s, %s,
            %s, %s, %s,
            %s, %s, %s, %s
        ) ON DUPLICATE KEY UPDATE
            display_name = VALUES(display_name),
            release_state = VALUES(release_state),
            color = VALUES(color),
            crafting_level = VALUES(crafting_level),
            gem_type = VALUES(gem_type),
            icon_dds_file = VALUES(icon_dds_file),
            skill_name = VALUES(skill_name),
            support_name = VALUES(support_name),
            support_text = VALUES(support_text),
            tutorial_video = VALUES(tutorial_video),
            ui_image = VALUES(ui_image),
            req_weight_dexterity = VALUES(req_weight_dexterity),
            req_weight_intelligence = VALUES(req_weight_intelligence),
            req_weight_strength = VALUES(req_weight_strength),
            crafting_types = VALUES(crafting_types),
            grants_skills = VALUES(grants_skills),
            recommended_supports = VALUES(recommended_supports),
            tags = VALUES(tags)
    """

    cursor = conn.cursor()
    batch_size = 500
    total_upserted = len(skills_data)
    
    print(f"Upserting {total_upserted} skill gems in batches of {batch_size}...")
    for i in range(0, total_upserted, batch_size):
        chunk = skills_data[i:i + batch_size]
        cursor.executemany(query, chunk)
        conn.commit()
        print(f"Upserted {min(i + batch_size, total_upserted)} / {total_upserted} skill gems...")
        
    cursor.close()
    return total_upserted

def clear_item_classes_table(conn):
    """Truncates the item_classes table to empty all existing item classes."""
    cursor = conn.cursor()
    cursor.execute("TRUNCATE TABLE item_classes")
    conn.commit()
    cursor.close()
    print("Table 'item_classes' cleared successfully.")

def bulk_upsert_item_classes(conn, classes_data):
    """Upserts list of item classes into database using batch processing."""
    if not classes_data:
        return 0

    query = """
        INSERT INTO item_classes (
            id, category, category_id, name, influence_tags
        ) VALUES (
            %s, %s, %s, %s, %s
        ) ON DUPLICATE KEY UPDATE
            category = VALUES(category),
            category_id = VALUES(category_id),
            name = VALUES(name),
            influence_tags = VALUES(influence_tags)
    """

    cursor = conn.cursor()
    batch_size = 500
    total_upserted = len(classes_data)
    
    print(f"Upserting {total_upserted} item classes in batches of {batch_size}...")
    for i in range(0, total_upserted, batch_size):
        chunk = classes_data[i:i + batch_size]
        cursor.executemany(query, chunk)
        conn.commit()
        print(f"Upserted {min(i + batch_size, total_upserted)} / {total_upserted} item classes...")
        
    cursor.close()
    return total_upserted

def clear_uniques_table(conn):
    """Truncates the item_uniques table to empty all existing uniques."""
    cursor = conn.cursor()
    cursor.execute("TRUNCATE TABLE item_uniques")
    conn.commit()
    cursor.close()
    print("Table 'item_uniques' cleared successfully.")

def bulk_upsert_uniques(conn, uniques_data):
    """Upserts list of unique items into database using batch processing."""
    if not uniques_data:
        return 0

    query = """
        INSERT INTO item_uniques (
            id, name, unique_id, item_class, 
            inventory_width, inventory_height, is_alternate_art,
            visual_identity_id, visual_identity_dds,
            renamed_version, base_version
        ) VALUES (
            %s, %s, %s, %s,
            %s, %s, %s,
            %s, %s,
            %s, %s
        ) ON DUPLICATE KEY UPDATE
            name = VALUES(name),
            unique_id = VALUES(unique_id),
            item_class = VALUES(item_class),
            inventory_width = VALUES(inventory_width),
            inventory_height = VALUES(inventory_height),
            is_alternate_art = VALUES(is_alternate_art),
            visual_identity_id = VALUES(visual_identity_id),
            visual_identity_dds = VALUES(visual_identity_dds),
            renamed_version = VALUES(renamed_version),
            base_version = VALUES(base_version)
    """

    cursor = conn.cursor()
    batch_size = 500
    total_upserted = len(uniques_data)
    
    print(f"Upserting {total_upserted} uniques in batches of {batch_size}...")
    for i in range(0, total_upserted, batch_size):
        chunk = uniques_data[i:i + batch_size]
        cursor.executemany(query, chunk)
        conn.commit()
        print(f"Upserted {min(i + batch_size, total_upserted)} / {total_upserted} uniques...")
        
    cursor.close()
    return total_upserted