"""
Database connection helper for the Scheduler service.

Mirrors the PHP DBConnectionHelper: reads DB_MASTER_* credentials from the
environment and creates per-request connections to the organisation-specific
database whose name arrives in the X-DB-Name header.
"""

import os
from contextlib import contextmanager

import pymysql
from dotenv import load_dotenv

# Load the Laravel .env that sits one directory above scheduler-service/
_ENV_PATH = os.path.join(os.path.dirname(__file__), "..", ".env")
load_dotenv(_ENV_PATH)

# Master DB credentials (same as Laravel's DB_MASTER_* variables)
DB_MASTER_HOST = os.getenv("DB_MASTER_HOST", "127.0.0.1")
DB_MASTER_PORT = int(os.getenv("DB_MASTER_PORT", "3306"))
DB_MASTER_USERNAME = os.getenv("DB_MASTER_USERNAME", "")
DB_MASTER_PASSWORD = os.getenv("DB_MASTER_PASSWORD", "")
DB_MASTER_DATABASE = os.getenv("DB_MASTER_DATABASE", "MYL_MASTER")


def _get_connection(database: str) -> pymysql.Connection:
    """Return a new pymysql connection to *database* on the master server."""
    return pymysql.connect(
        host=DB_MASTER_HOST,
        port=DB_MASTER_PORT,
        user=DB_MASTER_USERNAME,
        password=DB_MASTER_PASSWORD,
        database=database,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )


@contextmanager
def get_dynamic_connection(db_name: str):
    """
    Context manager that yields a pymysql connection to the organisation
    database identified by *db_name*.  The connection is closed on exit.
    """
    conn = _get_connection(db_name)
    try:
        yield conn
    finally:
        conn.close()


@contextmanager
def get_master_connection():
    """
    Context manager that yields a pymysql connection to MYL_MASTER.
    Used for auth lookups (users table).
    """
    conn = _get_connection(DB_MASTER_DATABASE)
    try:
        yield conn
    finally:
        conn.close()