from sqlalchemy import create_engine, Column, ForeignKey, Integer, String, Date, Float, DateTime
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.pool import NullPool
from sqlalchemy.exc import OperationalError

Base = declarative_base()

# Funktion zur Herstellung einer Verbindung zur Datenbank
def db_connect():
    try:
        engine = create_engine("postgresql://example:example@localhost/geotrack", poolclass=NullPool)
        # Überprüfe die Verbindung
        with engine.connect() as connection:
            print("connected to database!")
        return engine
    except OperationalError as e:
        print(f"database connection failed: {e}")
        return None

# Funktion zur Erstellung der Tabellen
def create_table(engine):
    try:
        if engine is not None:
            Base.metadata.create_all(engine)
            
            print("created tables")
        else:
            print("error no connection")
    except Exception as e:
        print(f"unexpected error during initial database creation: {e}")

# Track-Tabelle
class Track(Base):
    __tablename__ = 'track'
    id = Column(Integer, primary_key=True, autoincrement=True)
    trackName = Column(String(200), nullable=True)
    vehicle_id = Column(Integer, ForeignKey('vehicle.id'), nullable=False)
    driver_id = Column(Integer, ForeignKey('driver.id'), nullable=False)
    date = Column(Date, nullable=True)
    distance = Column(Float, nullable=False, default=0.0)
    speed = Column(Float, nullable=False, default=0.0)

    # Beziehungen
    driver = relationship("Driver", back_populates="tracks", foreign_keys=[driver_id])
    vehicle = relationship("Vehicle", back_populates="tracks", foreign_keys=[vehicle_id])
    waypoints = relationship('Waypoint', back_populates='track', lazy=True)

# Waypoint-Tabelle
class Waypoint(Base):
    __tablename__ = 'waypoint'
    id = Column(Integer, primary_key=True, autoincrement=True)
    lat = Column(Float, nullable=False)
    lon = Column(Float, nullable=False)
    ele = Column(Float, nullable=False)
    speed = Column(Float, nullable=True)
    time = Column(DateTime, nullable=True)
    track_id = Column(Integer, ForeignKey('track.id'), nullable=False)

    # Beziehung zu Track
    track = relationship("Track", back_populates="waypoints")

# Driver-Tabelle
class Driver(Base):
    __tablename__ = 'driver'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    # Beziehung zu Tracks
    tracks = relationship("Track", back_populates="driver", overlaps="vehicle_tracks")

# Vehicle-Tabelle
class Vehicle(Base):
    __tablename__ = 'vehicle'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    licenseplate = Column(String, nullable=True)

    # Beziehung zu Tracks
    tracks = relationship("Track", back_populates="vehicle", overlaps="driver_tracks")