# File: migrations/env.py

import os
import sys
import json
import importlib

from logging.config import fileConfig
from sqlalchemy import create_engine, pool
from alembic import context

# ─── Ensure we can load .env and app code ────────────────────────────────────
project_root = os.path.abspath(os.path.join(os.path.dirname(__file__), ".."))
sys.path.insert(0, project_root)

# ─── Load .env (so MYSQL_* and other vars are available) ─────────────────────
from dotenv import load_dotenv, find_dotenv
dotenv_path = find_dotenv()  # looks in project root or parents
if dotenv_path:
    load_dotenv(dotenv_path, override=True)

# ─── Alembic Config & Logging ────────────────────────────────────────────────
config = context.config
fileConfig(config.config_file_name)

# ─── Import your app’s metadata for 'autogenerate' support ─────────────────
from app import db
target_metadata = db.metadata

# ─── Dynamically import all plugin models listed in plugin.json ─────────────
plugins_dir = os.path.join(project_root, "plugins")
for plugin in sorted(os.listdir(plugins_dir)):
    manifest = os.path.join(plugins_dir, plugin, "plugin.json")
    if not os.path.isfile(manifest):
        continue
    try:
        meta = json.load(open(manifest))
    except Exception:
        continue

    for model_mod in meta.get("models", []):
        try:
            importlib.import_module(model_mod)
        except ImportError:
            pass
    for sp in meta.get("subplugins", []):
        for model_mod in sp.get("models", []):
            try:
                importlib.import_module(model_mod)
            except ImportError:
                pass

# ─── Build or retrieve the database URL ──────────────────────────────────────
def get_database_url():
    # 1) alembic.ini setting
    url = config.get_main_option("sqlalchemy.url")
    if url:
        return url

    # 2) Generic DATABASE_URL env var
    url = os.environ.get("DATABASE_URL")
    if url:
        return url

    # 3) MySQL env vars (from .env or docker-compose)
    user = os.environ.get("MYSQL_USER")
    pwd  = os.environ.get("MYSQL_PASSWORD")
    host = os.environ.get("MYSQL_HOST", "db")
    port = os.environ.get("MYSQL_PORT", "3306")
    dbn  = os.environ.get("MYSQL_DATABASE")
    if user and pwd and dbn:
        return f"mysql+pymysql://{user}:{pwd}@{host}:{port}/{dbn}"

    raise RuntimeError(
        "Database URL not configured for Alembic migrations; "
        "set 'sqlalchemy.url' in alembic.ini, or DATABASE_URL, "
        "or MYSQL_USER/MYSQL_PASSWORD/MYSQL_DATABASE in the environment"
    )

# ─── Offline migration ───────────────────────────────────────────────────────
def run_migrations_offline():
    url = get_database_url()
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()

# ─── Online migration ────────────────────────────────────────────────────────
def run_migrations_online():
    url = get_database_url()
    connectable = create_engine(url, poolclass=pool.NullPool)
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True,
            compare_server_default=True,
        )
        with context.begin_transaction():
            context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()