import os
import logging

from sqlalchemy import inspect
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker, declarative_base
from dotenv import load_dotenv

load_dotenv()


# Строка подключения для SQLite (в Docker задайте DATABASE_URL, см. docker-compose)
DATABASE_URL = os.getenv("DATABASE_URL", "sqlite+aiosqlite:///./database.db")
# echo=True печатает каждый SQL и легко вешает потоки (deadlock с FileHandler).
# Включить при отладке БД: SQLALCHEMY_ECHO=true
_SQL_ECHO = os.getenv("SQLALCHEMY_ECHO", "false").strip().lower() in (
    "1",
    "true",
    "yes",
    "on",
)

engine = create_async_engine(DATABASE_URL, echo=_SQL_ECHO)
Base = declarative_base()
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)


async def recreate_db():
    async with engine.begin() as conn:
        # Удаляем все таблицы
        await conn.run_sync(Base.metadata.drop_all)
        # Создаем все таблицы заново (включая новое поле spam)
        await conn.run_sync(Base.metadata.create_all)


def _ensure_filteruser_schedule_columns(connection):
    """Мягкая миграция: колонки расписания в filterUser (SQLite)."""
    inspector = inspect(connection)
    if "filterUser" not in inspector.get_table_names():
        return
    existing = {col["name"] for col in inspector.get_columns("filterUser")}
    statements = []
    if "run_mode" not in existing:
        statements.append(
            "ALTER TABLE filterUser ADD COLUMN run_mode VARCHAR(20) DEFAULT 'manual'"
        )
    if "work_from" not in existing:
        statements.append(
            "ALTER TABLE filterUser ADD COLUMN work_from VARCHAR(5) DEFAULT '09:00'"
        )
    if "work_to" not in existing:
        statements.append(
            "ALTER TABLE filterUser ADD COLUMN work_to VARCHAR(5) DEFAULT '20:00'"
        )
    if "work_days" not in existing:
        # JSON как TEXT в SQLite; значение по умолчанию проставим в CRUD
        statements.append("ALTER TABLE filterUser ADD COLUMN work_days JSON")
    for sql in statements:
        connection.exec_driver_sql(sql)
        logging.info("DB migrate: %s", sql)


def _ensure_pending_sms_unique_phone(connection):
    """Мягкая миграция: уникальный partial index — один pending на номер."""
    inspector = inspect(connection)
    if "pending_sms" not in inspector.get_table_names():
        return
    existing = {idx["name"] for idx in inspector.get_indexes("pending_sms")}
    if "uq_pending_sms_phone_pending" in existing:
        return
    connection.exec_driver_sql(
        "CREATE UNIQUE INDEX IF NOT EXISTS uq_pending_sms_phone_pending "
        "ON pending_sms(phone_number) WHERE status = 'pending'"
    )
    logging.info("DB migrate: unique index uq_pending_sms_phone_pending")


async def init_db():
    # Регистрируем модели на Base.metadata до create_all
    from app.database import models as _models  # noqa: F401

    async with engine.begin() as conn:
        # Создаем все таблицы, если их еще нет
        await conn.run_sync(Base.metadata.create_all)
        await conn.run_sync(_ensure_filteruser_schedule_columns)
        await conn.run_sync(_ensure_pending_sms_unique_phone)

        def get_table_names(connection):
            inspector = inspect(connection)
            return inspector.get_table_names()

        tables = await conn.run_sync(get_table_names)

        if "autoinfo" in tables:
            logging.info("Таблица 'autoinfo' создана или уже существует!")
        else:
            logging.info("Таблица 'autoinfo' не найдена!")