import mysql.connector
import csv
from datetime import datetime

# Database connection details
DB_CONFIG = {
    'host': 'mysql.theaken.com',
    'port': 33306,
    'database': 'db_A024',
    'user': 'A024',
    'password': 'p1D37ddnIJCN'
}

CSV_FILE = 'D:\\_PANJIT\\_Trae\\Lydia_practice\\ver1_ext\\files\\FINAL_整併後分機表_converted.csv'
TABLE_NAME = 'EXT'

def create_table_and_import_data():
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()

        # Drop table if it exists (for clean run, remove in production)
        cursor.execute(f"DROP TABLE IF EXISTS {TABLE_NAME}")
        print(f"Dropped table {TABLE_NAME} if it existed.")

        # Create table
        create_table_sql = f"""
        CREATE TABLE {TABLE_NAME} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            plant VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
            department VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
            csv_id VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
            position VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
            extension VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
            createdate DATETIME DEFAULT CURRENT_TIMESTAMP
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
        """
        cursor.execute(create_table_sql)
        print(f"Table {TABLE_NAME} created successfully.")

        # Import data from CSV
        with open(CSV_FILE, 'r', encoding='Big5', errors='ignore') as f:
            reader = csv.reader(f)
            next(reader)  # Skip header row

            insert_sql = f"""
            INSERT INTO {TABLE_NAME} (plant, department, csv_id, position, extension)
            VALUES (%s, %s, %s, %s, %s)
            """
            
            for row in reader:
                # Ensure row has enough columns, handle potential empty strings
                if len(row) >= 5:
                    # MySQL Connector/Python expects a tuple for execute()
                    data = (row[0], row[1], row[2], row[3], row[4])
                    cursor.execute(insert_sql, data)
                else:
                    print(f"Skipping malformed row: {row}")

        conn.commit()
        print(f"Data imported successfully into {TABLE_NAME}.")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()
            print("MySQL connection closed.")

if __name__ == "__main__":
    create_table_and_import_data()