#!/usr/bin/env python3

import os
import mysql.connector


def load_env_from_credentials(file_path: str = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'db_credentials.txt')):
    if not os.path.exists(file_path):
        # Fallback to CWD if running from project root
        alt_path = os.path.join(os.getcwd(), 'db_credentials.txt')
        if os.path.exists(alt_path):
            file_path = alt_path
        else:
            raise FileNotFoundError(f"Credentials file not found: {file_path}")
    creds = {}
    with open(file_path, 'r') as f:
        for line in f:
            line = line.strip()
            if not line or line.startswith('#'):
                continue
            if '=' in line:
                key, val = line.split('=', 1)
                creds[key.strip()] = val.strip()
    return creds


def read_schema_sql(schema_path: str):
    with open(schema_path, 'r', encoding='utf-8') as f:
        return f.read()


def execute_sql_script(conn, script: str):
    # Execute statements separated by semicolons, skipping SQL comments starting with --
    cursor = conn.cursor()
    buffer = []
    for raw_line in script.splitlines():
        line = raw_line.strip()
        if not line or line.startswith('--'):
            continue
        buffer.append(line)
        if line.endswith(';'):
            statement = ' '.join(buffer)
            # Remove trailing semicolon for execution
            if statement.endswith(';'):
                statement = statement[:-1]
            cursor.execute(statement)
            buffer = []
    # Execute any remaining statement without semicolon
    if buffer:
        statement = ' '.join(buffer)
        cursor.execute(statement)
    conn.commit()


def main():
    creds = load_env_from_credentials()
    host = creds.get('DB_HOST', 'localhost')
    port = int(creds.get('DB_PORT', '3306'))
    user = creds.get('DB_USER', 'root')
    password = creds.get('DB_PASSWORD', '')
    database = creds.get('DB_NAME')

    schema_path = os.path.join(os.path.dirname(__file__), 'schema.sql')
    schema_sql = read_schema_sql(schema_path)

    # Initial connection without specifying database for CREATE DATABASE
    admin_conn = mysql.connector.connect(host=host, port=port, user=user, password=password)
    try:
        execute_sql_script(admin_conn, '\n'.join(schema_sql.splitlines()[0:5]))  # CREATE DB + USE
    finally:
        admin_conn.close()

    # Connect to target DB and run rest of statements
    conn = mysql.connector.connect(host=host, port=port, user=user, password=password, database=database)
    try:
        # skip first 5 lines (CREATE DB/USE) since we're already in DB
        remaining_sql = '\n'.join(schema_sql.splitlines()[5:])
        execute_sql_script(conn, remaining_sql)
        print('Schema applied successfully')
        # Verify customers table exists
        cur = conn.cursor()
        cur.execute("SHOW TABLES LIKE 'customers'")
        exists = cur.fetchone() is not None
        print(f"customers table present: {exists}")
        if exists:
            cur.execute("DESCRIBE customers")
            cols = cur.fetchall()
            print(f"columns: {[c[0] for c in cols]}")
    finally:
        conn.close()


if __name__ == '__main__':
    main()