#!/usr/bin/env python3
"""
Initialize ERP database: reads init_db.sql and executes against MySQL.
"""
import pymysql
import sys
import os

# Database connection config
DB_HOST = os.getenv("DB_HOST", "192.168.50.139")
DB_PORT = int(os.getenv("DB_PORT", "3306"))
DB_USER = os.getenv("DB_USER", "root")
DB_PASSWORD = os.getenv("DB_PASSWORD")

SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
SQL_FILE = os.path.join(SCRIPT_DIR, "init_db.sql")


def main():
    if DB_PASSWORD is None:
        print("ERROR: DB_PASSWORD must be set in the environment.")
        sys.exit(1)

    # Read SQL file
    if not os.path.exists(SQL_FILE):
        print(f"ERROR: SQL file not found: {SQL_FILE}")
        sys.exit(1)

    with open(SQL_FILE, "r", encoding="utf-8") as f:
        sql_content = f.read()

    # Connect to MySQL (no database selected yet)
    print(f"Connecting to MySQL at {DB_HOST}:{DB_PORT}...")
    conn = pymysql.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASSWORD,
        charset="utf8mb4",
        autocommit=True,
    )
    print("Connected.")

    try:
        cursor = conn.cursor()
        # Split into statements and execute one by one
        statements = []
        current = ""
        for line in sql_content.split("\n"):
            stripped = line.strip()
            # Skip comment lines and empty lines for processing
            if stripped.startswith("--") or stripped.startswith("#"):
                continue
            current += line + "\n"
            if stripped.endswith(";"):
                stmt = current.strip()
                if stmt:
                    statements.append(stmt)
                current = ""

        # Execute each statement
        for stmt in statements:
            try:
                cursor.execute(stmt)
                # Print first 60 chars for feedback
                preview = stmt.split("\n")[0][:60]
                print(f"  OK: {preview}...")
            except pymysql.err.OperationalError as e:
                if e.args[0] == 1049:  # Unknown database - this is expected on first run
                    print(f"  INFO: {e.args[1]}")
                else:
                    print(f"  WARN: {e.args[1]}")
            except pymysql.err.IntegrityError as e:
                if e.args[0] == 1062:  # Duplicate entry - expected for seed data
                    print(f"  INFO: Duplicate entry (already exists): {stmt[:40]}...")
                else:
                    print(f"  WARN: {e.args[1]}")
            except Exception as e:
                print(f"  WARN: {e}")

        cursor.close()
        conn.close()
        print("\nDatabase initialization complete.")

        # Verify tables
        conn2 = pymysql.connect(
            host=DB_HOST,
            port=DB_PORT,
            user=DB_USER,
            password=DB_PASSWORD,
            database="erp",
            charset="utf8mb4",
        )
        cur2 = conn2.cursor()
        cur2.execute("SHOW TABLES;")
        tables = [row[0] for row in cur2.fetchall()]
        cur2.close()
        conn2.close()
        print(f"ERP database tables ({len(tables)}): {', '.join(tables)}")

    except Exception as e:
        print(f"ERROR: {e}")
        sys.exit(1)


if __name__ == "__main__":
    main()