"""Helpers for the SDK tutorials; use a dedicated, already-created database."""

import os
from contextlib import contextmanager

import pandas as pd
from matrixone import Client
from sqlalchemy import text


@contextmanager
def tables(definitions):
    client = Client(sql_log_mode="off", pool_size=2, max_overflow=0)
    created = []
    options = dict(
        host=os.getenv("MO_HOST", "127.0.0.1"),
        port=int(os.getenv("MO_PORT", "6001")),
        user=os.environ["MO_USER"],
        password=os.environ["MO_PASSWORD"],
        database=os.environ["MO_DATABASE"],
        on_connect=[],
    )
    if os.getenv("MO_SSL_CA"):
        options.update(ssl_mode="required", ssl_ca=os.environ["MO_SSL_CA"])
    try:
        client.connect(**options)
        for name, columns in definitions:
            # Names and DDL come only from the fixed tutorial definitions.
            client.execute(f"CREATE TABLE {name}({columns})")
            created.append(name)
        yield client
    finally:
        try:
            for name in reversed(created):
                client.execute(f"DROP TABLE {name}")
        finally:
            client.disconnect()


def frame(session, sql, params=None):
    result = session.execute(text(sql), params or {})
    # Preserve Decimal and nullable large integers before choosing column dtypes.
    return pd.DataFrame(result.fetchall(), columns=list(result.keys()), dtype=object)
