from sqlalchemy import create_engine, event, text
from sqlalchemy.orm import DeclarativeBase, sessionmaker

from config import DATABASE_URL, DB_PATH


class Base(DeclarativeBase):
    pass


DB_PATH.parent.mkdir(parents=True, exist_ok=True)

engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False},
    echo=False,
)


@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.execute("PRAGMA busy_timeout=5000")
    cursor.close()


SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


def init_db():
    import models  # noqa: F401 – alle Models registrieren
    Base.metadata.create_all(bind=engine)

    with engine.connect() as conn:
        # FTS5 Tabelle separat erstellen (SQLAlchemy kann kein FTS5)
        conn.execute(text("""
            CREATE VIRTUAL TABLE IF NOT EXISTS search_index USING fts5(
                entity_type,
                entity_id UNINDEXED,
                titel,
                untertitel,
                inhalt,
                tags,
                tokenize='unicode61 remove_diacritics 2'
            )
        """))
        conn.commit()

    # Neue Spalten sicher hinzufuegen (ALTER TABLE ignoriert Fehler wenn Spalte schon existiert)
    with engine.connect() as conn:
        try:
            conn.execute(text("ALTER TABLE kontakt ADD COLUMN zuletzt_aufgeraeumt DATETIME"))
            conn.commit()
        except Exception:
            pass  # Spalte existiert bereits

    # Indizes fuer haeufige Abfragen (sicher auf bestehenden DBs)
    with engine.connect() as conn:
        idxs = [
            # Kontakt: die am haeufigsten gefilterten/sortierten Spalten
            "CREATE INDEX IF NOT EXISTS ix_kontakt_typ              ON kontakt(typ)",
            "CREATE INDEX IF NOT EXISTS ix_kontakt_blacklisted      ON kontakt(ist_blacklisted)",
            "CREATE INDEX IF NOT EXISTS ix_kontakt_nachname         ON kontakt(nachname)",
            "CREATE INDEX IF NOT EXISTS ix_kontakt_firmenname       ON kontakt(firmenname)",
            "CREATE INDEX IF NOT EXISTS ix_kontakt_ort              ON kontakt(ort)",
            "CREATE INDEX IF NOT EXISTS ix_kontakt_email1           ON kontakt(email1)",
            "CREATE INDEX IF NOT EXISTS ix_kontakt_betreuer         ON kontakt(betreuer)",
            "CREATE INDEX IF NOT EXISTS ix_kontakt_eigentuemer      ON kontakt(eigentuemer)",
            "CREATE INDEX IF NOT EXISTS ix_kontakt_aktualisiert     ON kontakt(aktualisiert_am)",
            # Composite: typischer Query-Kern (typ + blacklist + sort)
            "CREATE INDEX IF NOT EXISTS ix_kontakt_typ_bl_name      ON kontakt(typ, ist_blacklisted, nachname)",
            "CREATE INDEX IF NOT EXISTS ix_kontakt_typ_bl_firma     ON kontakt(typ, ist_blacklisted, firmenname)",
            # Beziehung: JOINs fuer Org-Mitarbeiter
            "CREATE INDEX IF NOT EXISTS ix_beziehung_a              ON beziehung(kontakt_a_id)",
            "CREATE INDEX IF NOT EXISTS ix_beziehung_b              ON beziehung(kontakt_b_id)",
            # Aktivitaet
            "CREATE INDEX IF NOT EXISTS ix_aktivitaet_kontakt       ON aktivitaet(kontakt_id)",
            "CREATE INDEX IF NOT EXISTS ix_aktivitaet_datum         ON aktivitaet(datum DESC)",
            "CREATE INDEX IF NOT EXISTS ix_aktivitaet_status        ON aktivitaet(status)",
            "CREATE INDEX IF NOT EXISTS ix_aktivitaet_zugewiesen    ON aktivitaet(zugewiesen_an)",
            # Gruppe-Zuordnung
            "CREATE INDEX IF NOT EXISTS ix_kontaktgruppe_gruppe     ON kontakt_gruppe(gruppe_id)",
            "CREATE INDEX IF NOT EXISTS ix_kontaktgruppe_kontakt    ON kontakt_gruppe(kontakt_id)",
        ]
        for idx in idxs:
            conn.execute(text(idx))
        conn.commit()
