import csv
import mysql.connector

def get_connection_details():
    details = {}
    with open('setting.txt', 'r') as f:
        for line in f:
            key, value = line.strip().split(':', 1)
            details[key.strip()] = value.strip()
    return details

def create_and_populate_table():
    try:
        config = get_connection_details()
        conn = mysql.connector.connect(
            host=config.get('Host'),
            port=config.get('Port'),
            user=config.get('Username'),
            password=config.get('Password'),
            database=config.get('Database'),
            charset='utf8mb4' # Changed to utf8mb4 for better Chinese support
        )
        cursor = conn.cursor()

        # Create table (if it doesn't exist, or if it was truncated)
        # Explicitly setting CHARACTER SET and COLLATE for proper Chinese display
        create_table_query = """
        CREATE TABLE IF NOT EXISTS extension_data (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `plant` VARCHAR(255),
            `department` VARCHAR(255),
            `name` VARCHAR(255),
            `position` VARCHAR(255),
            `extension` VARCHAR(255)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
        """
        cursor.execute(create_table_query)

        # Read from CSV and insert into table
        with open('files/FINAL_整併後分機表.csv', 'r', encoding='cp950') as csvfile:
            reader = csv.reader(csvfile)
            next(reader)  # Skip header row
            for row in reader:
                # Ensure row has at least 5 columns (plant, department, name, position, extension)
                if len(row) >= 5:
                    insert_query = """
                    INSERT INTO extension_data (plant, department, name, position, extension)
                    VALUES (%s, %s, %s, %s, %s)
                    """
                    # Map CSV columns to database columns
                    # CSV: [unit, department, name, position, extension, source_file, sheet]
                    # DB:  [plant, department, name, position, extension]
                    data_to_insert = (row[0], row[1], row[2], row[3], row[4])
                    cursor.execute(insert_query, data_to_insert)

        conn.commit()
        print("Table 'extension_data' populated successfully with 'plant' data.")

    except FileNotFoundError:
        print("Error: The CSV file was not found.")
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    create_and_populate_table()