# coding: utf-8
from sqlalchemy import create_engine, MetaData, Column, ForeignKey, Enum, UniqueConstraint, Integer, String, Text, DateTime, PickleType, BigInteger, Index, Float
from sqlalchemy.orm import relationship as Relationship, sessionmaker
from sqlalchemy.sql import func
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
import os

if not os.environ.get('SQLALCHEMY_DATABASE_URI'):
	database_uri = 'sqlite:///test.db'
#	database_uri = 'postgresql+psycopg2://docker:docker@db/docker'
else:
	database_uri = os.environ.get('SQLALCHEMY_DATABASE_URI')

engine = create_engine(database_uri, echo=False)
Session = sessionmaker(bind=engine, autocommit=False, autoflush=True)
db = Session()

Base = declarative_base()

def init_db(tdb, engine):
	try:
		tdb.query(Vehiculo).first()
	except:
		import traceback
		traceback.format_exc()
		Base.metadata.create_all(bind=engine)
		tdb.commit()

class Registros(Base):
    __tablename__ = 'registros'
    __table_args__ = { 'schema': 'gtfsrt' }
    id        = Column(Integer, primary_key=True, autoincrement=True)

    filename  = Column(String) #PATH/ccp/2022/01/01/ccp_gtfs_20220101_246060_00.proto
    status    = Column(Integer, default=0)
    timestamp = Column(DateTime(timezone=True), default=func.now())

class Vehiculo(Base):
    __tablename__ = 'vehicle'
    __table_args__ = { 'schema': 'gtfsrt' }
    id        = Column(Integer, primary_key=True, autoincrement=True)

    patente   = Column(String(8))
    timestamp = Column(BigInteger)

class Viaje(Base):
    __tablename__ = 'trip'
    __table_args__ = { 'schema': 'gtfsrt' }
    id        = Column(Integer, primary_key=True, autoincrement=True)

    trip_id      = Column(String(50))
    route_id     = Column(Integer())
    direction_id = Column(Integer())

    start_time   = Column(String(8))
    start_date   = Column(String(8))
    timestamp  = Column(BigInteger)

class Entidades(Base):
    __tablename__ = 'entity'
    __table_args__ = { 'schema': 'gtfsrt' }
    id         = Column(Integer, primary_key=True, autoincrement=True)
    entity     = Column(String(50))

    vehicleid  = Column(Integer, ForeignKey('gtfsrt.vehicle.id'), nullable=False)
    tripid     = Column(Integer, ForeignKey('gtfsrt.trip.id'), nullable=True)
    timestamp  = Column(BigInteger)

class Posicion(Base):
    __tablename__ = 'datapoint'
    __table_args__ = { 'schema': 'gtfsrt' }
    id        = Column(Integer, primary_key=True, autoincrement=True)

    latitude  = Column(Float)
    longitude = Column(Float)

    bearing   = Column(Integer())
    odometer  = Column(Float)
    speed     = Column(Float)

    vehicleid = Column(Integer, ForeignKey('gtfsrt.vehicle.id'), nullable=False)
    tripid    = Column(Integer, ForeignKey('gtfsrt.trip.id'), nullable=True)

    timestamp = Column(BigInteger)