#!/usr/bin/env python3
"""
Create/import the Daneshyar MySQL database using database/schema.sql + database/seed.sql.

Requirements:
  pip install pymysql

This script reads DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME and DB_PASSWORD from .env.
It intentionally does not print the password.
"""

from __future__ import annotations

import re
import sys
from pathlib import Path


ROOT = Path(__file__).resolve().parents[1]


def read_env() -> dict[str, str]:
    env_path = ROOT / ".env"
    values: dict[str, str] = {}

    if not env_path.exists():
        raise SystemExit(".env not found. Create it first.")

    for raw_line in env_path.read_text(encoding="utf-8").splitlines():
        line = raw_line.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        key, value = line.split("=", 1)
        values[key.strip()] = value.strip().strip('"').strip("'")

    return values


def split_sql(sql: str) -> list[str]:
    sql = re.sub(r"^\s*--.*$", "", sql, flags=re.MULTILINE)
    statements: list[str] = []
    buffer: list[str] = []
    in_single = False
    in_double = False
    escape = False

    for char in sql:
        buffer.append(char)

        if escape:
            escape = False
            continue

        if char == "\\":
            escape = True
            continue

        if char == "'" and not in_double:
            in_single = not in_single
        elif char == '"' and not in_single:
            in_double = not in_double
        elif char == ";" and not in_single and not in_double:
            statement = "".join(buffer).strip()
            if statement:
                statements.append(statement[:-1].strip())
            buffer = []

    tail = "".join(buffer).strip()
    if tail:
        statements.append(tail)

    return [statement for statement in statements if statement]


def main() -> int:
    try:
        import pymysql
    except ModuleNotFoundError:
        print("Missing pymysql. Install it with: pip install pymysql", file=sys.stderr)
        return 2

    env = read_env()
    database = env.get("DB_DATABASE", "daneshyar_dev")

    connection = pymysql.connect(
        host=env.get("DB_HOST", "127.0.0.1"),
        port=int(env.get("DB_PORT", "3306")),
        user=env.get("DB_USERNAME", "root"),
        password=env.get("DB_PASSWORD", ""),
        charset="utf8mb4",
        autocommit=True,
        connect_timeout=15,
        read_timeout=60,
        write_timeout=60,
    )

    try:
        with connection.cursor() as cursor:
            cursor.execute(
                f"CREATE DATABASE IF NOT EXISTS `{database}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
            )
            cursor.execute(f"USE `{database}`")

            for sql_file in [ROOT / "database" / "schema.sql", ROOT / "database" / "seed.sql"]:
                print(f"Importing {sql_file.relative_to(ROOT)} into {database}...")
                for statement in split_sql(sql_file.read_text(encoding="utf-8")):
                    cursor.execute(statement)

        print(f"Database import completed: {database}")
        return 0
    finally:
        connection.close()


if __name__ == "__main__":
    raise SystemExit(main())