from sqlalchemy import text
from sqlalchemy.exc import IntegrityError, SQLAlchemyError
from sqlalchemy.ext.asyncio import AsyncSession

from app.repositories import system_repository
from app.schemas.system import NumberFormatConfig, TruncateGroup

# Map each group to tables in correct DELETE order (leaf → parent to respect FK constraints)
_TRUNCATE_ORDER: dict[str, list[str]] = {
    "penjualan": ["penjualan_nozzle", "laporan_shift"],
    "stock": [
        "stock_adjustment_item_foto",
        "stock_adjustment_item",
        "stock_adjustment",
    ],
    "penebusan_penerimaan": [
        "penerimaan_foto",
        "penerimaan_item",
        "penerimaan",
        "penebusan_item",
        "penebusan",
    ],
    "expenses_penyetoran": ["expenses", "penyetoran"],
    "end_to_end": ["end_to_end_cycle"],
}

_ALL_ORDER: list[str] = (
    _TRUNCATE_ORDER["penjualan"]
    + _TRUNCATE_ORDER["stock"]
    + _TRUNCATE_ORDER["penebusan_penerimaan"]
    + _TRUNCATE_ORDER["expenses_penyetoran"]
    + _TRUNCATE_ORDER["end_to_end"]
)


async def get_maintenance_mode(db: AsyncSession) -> bool:
    return await system_repository.is_maintenance_mode(db)


async def set_maintenance_mode(
    db: AsyncSession, enabled: bool, user_id: int
) -> None:
    try:
        await system_repository.set_config(
            db, system_repository.MAINTENANCE_KEY, str(enabled).lower(), user_id
        )
        await db.commit()
    except SQLAlchemyError:
        await db.rollback()
        raise


async def get_env_mode(db: AsyncSession) -> str:
    """Return current environment mode: 'development' or 'production'."""
    config = await system_repository.get_config(db, system_repository.ENV_MODE_KEY)
    return config.value if config else "production"


async def set_env_mode(db: AsyncSession, mode: str, user_id: int) -> None:
    if mode not in ("development", "production"):
        raise ValueError("mode must be 'development' or 'production'")
    try:
        await system_repository.set_config(
            db, system_repository.ENV_MODE_KEY, mode, user_id
        )
        await db.commit()
    except SQLAlchemyError:
        await db.rollback()
        raise


async def get_number_format(db: AsyncSession) -> NumberFormatConfig:
    r = system_repository
    thousand = await r.get_config(db, r.NUMBER_THOUSAND_SEP_KEY)
    decimal = await r.get_config(db, r.NUMBER_DECIMAL_SEP_KEY)
    places = await r.get_config(db, r.NUMBER_DECIMAL_PLACES_KEY)
    neg_fmt = await r.get_config(db, r.NUMBER_NEGATIVE_FORMAT_KEY)
    neg_color = await r.get_config(db, r.NUMBER_NEGATIVE_COLOR_KEY)
    return NumberFormatConfig(
        thousand_separator=thousand.value if thousand else ",",
        decimal_separator=decimal.value if decimal else ".",
        decimal_places=int(places.value) if places else 0,
        negative_format=neg_fmt.value if neg_fmt else "prefix",
        negative_color=(neg_color.value == "true") if neg_color else False,
    )


async def set_number_format(
    db: AsyncSession, fmt: NumberFormatConfig, user_id: int
) -> None:
    if fmt.thousand_separator == fmt.decimal_separator and fmt.thousand_separator != "":
        raise ValueError("Thousand separator and decimal separator must be different")
    if fmt.decimal_separator not in (".", ","):
        raise ValueError("Decimal separator must be '.' or ','")
    if not (0 <= fmt.decimal_places <= 4):
        raise ValueError("Decimal places must be between 0 and 4")
    if fmt.negative_format not in ("prefix", "suffix", "parentheses"):
        raise ValueError("negative_format must be 'prefix', 'suffix', or 'parentheses'")
    r = system_repository
    try:
        await r.set_config(db, r.NUMBER_THOUSAND_SEP_KEY, fmt.thousand_separator, user_id)
        await r.set_config(db, r.NUMBER_DECIMAL_SEP_KEY, fmt.decimal_separator, user_id)
        await r.set_config(db, r.NUMBER_DECIMAL_PLACES_KEY, str(fmt.decimal_places), user_id)
        await r.set_config(db, r.NUMBER_NEGATIVE_FORMAT_KEY, fmt.negative_format, user_id)
        await r.set_config(db, r.NUMBER_NEGATIVE_COLOR_KEY, str(fmt.negative_color).lower(), user_id)
        await db.commit()
    except SQLAlchemyError:
        await db.rollback()
        raise


async def truncate_group(db: AsyncSession, group: TruncateGroup) -> int:
    """Delete all rows from the given operational table group.

    Returns total number of rows deleted across all affected tables.
    Only callable when environment_mode == 'development' (enforced by router).
    """
    tables = _ALL_ORDER if group == "all" else _TRUNCATE_ORDER[group]
    total = 0
    try:
        for table in tables:
            result = await db.execute(text(f"DELETE FROM {table}"))  # noqa: S608
            total += result.rowcount
        await db.commit()
    except SQLAlchemyError:
        await db.rollback()
        raise
    return total