from sqlalchemy import (
    Column, String, Integer, Boolean, DateTime, Text, ForeignKey, create_engine
)
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from datetime import datetime, timezone
import os

DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://cardsync:changeme@localhost:5432/cardsync")

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


class SynologyConfig(Base):
    """Zentrale Synology-Serverkonfiguration (Admin-seitig)"""
    __tablename__ = "synology_config"

    id = Column(Integer, primary_key=True, index=True)
    server_url = Column(String(500), nullable=False)
    username = Column(String(255), nullable=False)
    password = Column(Text, nullable=False)
    updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))


class AzureGroupConfig(Base):
    """Konfiguration für Azure AD Gruppen-basiertes User-Management"""
    __tablename__ = "azure_group_config"

    id = Column(Integer, primary_key=True, index=True)
    group_id = Column(String(255), nullable=False)            # Azure AD Group Object ID
    group_name = Column(String(255))                          # Anzeigename (cached)
    auto_sync_members = Column(Boolean, default=True)         # Mitglieder regelmäßig abgleichen
    member_sync_interval_hours = Column(Integer, default=6)   # Wie oft Gruppenmitgliedschaft prüfen
    default_carddav_folder = Column(String(500))              # Default für neue User
    default_sync_interval_minutes = Column(Integer, default=60)
    default_sync_enabled = Column(Boolean, default=True)      # Neue User direkt aktiviert?
    last_member_sync_at = Column(DateTime)
    last_member_sync_status = Column(String(50))
    last_member_sync_message = Column(Text)
    updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))


class User(Base):
    """Benutzer (per OAuth oder per Azure-AD-Gruppen-Import angelegt)"""
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    ms_user_id = Column(String(255), unique=True, nullable=False, index=True)
    email = Column(String(255), unique=True, nullable=False, index=True)
    display_name = Column(String(255))

    # Delegated-Mode (OAuth) Tokens — optional, nur wenn User sich selbst angemeldet hat
    ms_access_token = Column(Text)
    ms_refresh_token = Column(Text)
    token_expires_at = Column(DateTime)

    # Wie wurde der User angelegt? "oauth" oder "group_import"
    source = Column(String(50), default="oauth")

    sync_enabled = Column(Boolean, default=False)
    carddav_folder = Column(String(500))
    sync_interval_minutes = Column(Integer, default=60)
    last_sync_at = Column(DateTime)
    last_sync_status = Column(String(50))
    last_sync_message = Column(Text)
    created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))

    sync_logs = relationship("SyncLog", back_populates="user", cascade="all, delete-orphan")


class SyncLog(Base):
    """Protokoll aller Sync-Vorgänge"""
    __tablename__ = "sync_logs"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    started_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    finished_at = Column(DateTime)
    status = Column(String(50))
    contacts_synced = Column(Integer, default=0)
    contacts_created = Column(Integer, default=0)
    contacts_updated = Column(Integer, default=0)
    contacts_deleted = Column(Integer, default=0)
    error_message = Column(Text)

    user = relationship("User", back_populates="sync_logs")


def create_tables():
    Base.metadata.create_all(bind=engine)

    # Migration für bestehende DBs: neue Spalte "source" ergänzen falls sie fehlt
    from sqlalchemy import inspect, text
    inspector = inspect(engine)
    if "users" in inspector.get_table_names():
        cols = [c["name"] for c in inspector.get_columns("users")]
        if "source" not in cols:
            with engine.begin() as conn:
                conn.execute(text("ALTER TABLE users ADD COLUMN source VARCHAR(50) DEFAULT 'oauth'"))