"""Database connection helper for remote MySQL staging database."""

import os
from contextlib import contextmanager
from dotenv import load_dotenv
import pymysql
import pymysql.cursors

load_dotenv()


def get_connection(cursorclass=pymysql.cursors.DictCursor):
    """Establish and return a PyMySQL connection using .env credentials."""
    host = os.getenv("DB_HOST")
    user = os.getenv("DB_USER")
    password = os.getenv("DB_PASSWORD")
    database = os.getenv("DB_NAME")
    port = int(os.getenv("DB_PORT", 3306))

    if not all([host, user, password, database]):
        raise ValueError("Missing database credentials in .env file")

    return pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        port=port,
        ssl={"check_hostname": False},
        cursorclass=cursorclass,
        autocommit=False,
    )


@contextmanager
def get_cursor(cursorclass=pymysql.cursors.DictCursor, commit: bool = False):
    """Context manager for managing connection and cursor lifecycles."""
    conn = get_connection(cursorclass=cursorclass)
    try:
        with conn.cursor() as cursor:
            yield cursor
        if commit:
            conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()
