import mysql.connector
import os
import sys

# Add parent dir to path to import db
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
from db import DB_CONFIG

def reset_and_seed():
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        
        print("Dropping tables...")
        cursor.execute("SET FOREIGN_KEY_CHECKS = 0;")
        cursor.execute("DROP TABLE IF EXISTS materials;")
        cursor.execute("DROP TABLE IF EXISTS services;")
        # cursor.execute("DROP TABLE IF EXISTS order_files;") # Avoid dropping if not needed, but they depend on materials? No, orders depend on materials?
        cursor.execute("SET FOREIGN_KEY_CHECKS = 1;")
        
        print("Running schema.sql...")
        schema_path = os.path.join(os.path.dirname(__file__), "schema.sql")
        with open(schema_path, "r", encoding="utf-8") as f:
            # We need to split by semicolon, but be careful with multi-line statements
            sql = f.read()
            # A simple split by ; works if there are no semicolons inside strings
            sql_commands = sql.split(";")
            
        for command in sql_commands:
            cmd = command.strip()
            if cmd:
                try:
                    cursor.execute(cmd)
                except Exception as e:
                    # Skip database creation if exists
                    if "database exists" not in str(e).lower():
                        print(f"Error in command: {cmd[:50]}... -> {e}")
        
        conn.commit()
        print("Database reset and seeded successfully.")
        
    except Exception as e:
        print(f"Error: {e}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    reset_and_seed()