from sqlalchemy.sql import func
from sqlalchemy.dialects import postgresql
from webinterface import db

class ArchivosGTFS(db.Model):
    __tablename__   = 'archivos'
    __bind_key__    = 'gtfs_work'
    id              = db.Column(db.Integer,  primary_key=True, nullable=False)

    analisis        = db.Column(db.Integer, default=0)
    terminado       = db.Column(db.Integer, default=0)
    activo          = db.Column(db.Integer, default=0)

class Agencia(db.Model):
    __tablename__   = 'agency'
    __bind_key__    = 'gtfs_work'
    id              = db.Column(db.Integer,  primary_key=True, nullable=False, autoincrement=True)

    agency_id       = db.Column(db.String, unique=True)
    agency_name     = db.Column(db.String)
    agency_url      = db.Column(db.String)
    agency_timezone = db.Column(db.String)
    agency_lang     = db.Column(db.String)
    agency_phone    = db.Column(db.String)
    agency_fare_url = db.Column(db.String)

class Calendario(db.Model):
    __tablename__   = 'calendars'
    __bind_key__    = 'gtfs_work'
    id              = db.Column(db.Integer,  primary_key=True, nullable=False, autoincrement=True)

    label           = db.Column(db.String, unique=True)
    monday          = db.Column(db.Integer, default=0)
    tuesday         = db.Column(db.Integer, default=0)
    wednesday       = db.Column(db.Integer, default=0)
    thursday        = db.Column(db.Integer, default=0)
    friday          = db.Column(db.Integer, default=0)
    saturday        = db.Column(db.Integer, default=0)
    sunday          = db.Column(db.Integer, default=0)


#shape_id,shape_pt_lat,shape_pt_lon,shape_pt_sequence,shape_dist_traveled
#1136979693,-36.843,-73.00984,1
#1136979693,-36.843,-73.00984,2
class Formas(db.Model):
    __tablename__       = 'shapes'
    __bind_key__        = 'gtfs_work'

    id                  = db.Column(db.Integer,  primary_key=True, nullable=False, autoincrement=True)
    shape_1pt_lat       = db.Column(db.Double, nullable=False)
    shape_1pt_lon       = db.Column(db.Double, nullable=False)

    shape_num_pt        = db.Column(db.Integer, nullable=False)
    shape_length        = db.Column(db.Double)

class Segmentos(db.Model):
    __tablename__       = 'shapes_point'
    __bind_key__        = 'gtfs_work'

    id                  = db.Column(db.Integer,  primary_key=True, nullable=False, autoincrement=True)
    pt_start_lat        = db.Column(db.Double, nullable=False)
    pt_start_lon        = db.Column(db.Double, nullable=False)
    pt_end_lat          = db.Column(db.Double, nullable=False)
    pt_end_lon          = db.Column(db.Double, nullable=False)

    shape_pt_sequence   = db.Column(db.Integer, nullable=False)
    shape_dist_traveled = db.Column(db.Double)
    shape_id            = db.Column(db.Integer, db.ForeignKey('shapes.id'), nullable=False)

    __table_args__ = (db.UniqueConstraint('shape_id', 'shape_pt_sequence', name='_shapeid_sequence_uc'), )


class Comunas(db.Model):
    __tablename__  = 'comuna'
    __bind_key__   = 'gtfs_work'

    id             = db.Column(db.Integer,  primary_key=True, nullable=False, autoincrement=True)
    name           = db.Column(db.String, unique=True)


#stop_id,stop_code,stop_name,stop_desc,stop_lat,stop_lon,zone_id,stop_url,location_type,parent_station,wheelchair_boarding
#40921,,Pque Zoologico Concepcion  Poniente,,-36.8400453,-73.00696914,,,,,
#40808,,Cno. Nonguen esq Las Vertientes,,-36.83675878,-73.00343935,,,,,
class Paradas(db.Model):
    __tablename__       = 'stops'
    __bind_key__        = 'gtfs_work'

    id                  = db.Column(db.BigInteger,  primary_key=True, nullable=False, autoincrement=True)

    stop_code           = db.Column(db.String, unique=True, nullable=True)
    stop_name           = db.Column(db.String)
    stop_desc           = db.Column(db.String)
    stop_lat            = db.Column(db.Double, nullable=False)
    stop_lon            = db.Column(db.Double, nullable=False)

    comuna_id           = db.Column(db.Integer, db.ForeignKey('comuna.id'), nullable=False)
    stop_url            = db.Column(db.String)
    parent_station      = db.Column(db.BigInteger)
    wheelchair_boarding = db.Column(db.Integer, default=0)


#route_id,agency_id,route_short_name,route_long_name,route_desc,route_type,route_url,route_color,route_text_color
#625,DTPR,70KH,Nonguén - Parque Tumbes,,3,,0d7215,ffffff
#600,DTPR,41CR,Parque Empresarial Biobío - Terminal Collao,,3,,ad0101,ffffff
class Lineas(db.Model):
    __tablename__       = 'routes'
    __bind_key__        = 'gtfs_work'

    id                  = db.Column(db.Integer,  primary_key=True, nullable=False, autoincrement=True)

    agency_id           = db.Column(db.Integer, db.ForeignKey('agency.id'), nullable=False)
    route_short_name    = db.Column(db.String)
    route_long_name     = db.Column(db.String)
    route_desc          = db.Column(db.String)
    route_url           = db.Column(db.String)
    route_color         = db.Column(db.Integer, default=0)
    route_text_color    = db.Column(db.Integer, default=0)


#route_id,service_id,trip_id,trip_headsign,                direction_id,block_id,shape_id,wheelchair_accessible,bikes_allowed
#route_id,service_id,trip_id,trip_headsign,trip_short_name,direction_id,block_id,shape_id,wheelchair_accessible,bikes_allowed
#625,S,c8b17d5f-4-76aabf89-b,Nonguén,,1,,1136979694,,
#625,S,4d018d35-7-76aabf89-b,Parque Tumbes,,0,,1136979693,,
#ac48288b-1-56195eaf-8,b1910f5b,1,0,1168259538,,0,0,1-1,1
#d9e94be9-f-56195eaf-8,b1910f5b,1,0,1168259538,,0,0,1-1,2
class Servicio(db.Model):
    __tablename__           = 'trips'
    __bind_key__            = 'gtfs_work'

    id                      = db.Column(db.Integer,  primary_key=True, nullable=False)

    route_id                = db.Column(db.Integer, db.ForeignKey('routes.id'), nullable=False) # A que linea corresponde
    calendar_id             = db.Column(db.Integer, db.ForeignKey('calendars.id'), nullable=False)
    gtfs_id                 = db.Column(db.Integer, db.ForeignKey('archivos.id'), nullable=False) # Identifiador unico del servicio
    shape_id                = db.Column(db.Integer, db.ForeignKey('shapes.id'), nullable=False) # shapes.id
    direction_id            = db.Column(db.Integer, nullable=False) # 0=ida, 1=vuelta

    trip_headsign           = db.Column(db.String)
    trip_short_name         = db.Column(db.String)
    wheelchair_accessible   = db.Column(db.Integer, default=0)
    bikes_allowed           = db.Column(db.Integer, default=0)

#trip_id,arrival_time,departure_time,stop_id,stop_sequence,                                        timepoint,shape_dist_traveled
#trip_id,arrival_time,departure_time,stop_id,stop_sequence,stop_headsign,pickup_type,drop_off_type,timepoint
#c8b17d5f-4-76aabf89-b,05:01:00,05:01:00,40439,1,,,,1
#c8b17d5f-4-76aabf89-b,05:02:00,05:02:00,40440,2,,,,0
class Detenciones(db.Model):
    __tablename__   = 'stop_times'
    __bind_key__    = 'gtfs_work'

    id              = db.Column(db.Integer,  primary_key=True, nullable=False, autoincrement=True)

    trip_id         = db.Column(db.Integer, db.ForeignKey('trips.id'), nullable=False)
    stop_sequence   = db.Column(db.Integer, nullable=False)
    stop_id         = db.Column(db.BigInteger, db.ForeignKey('stops.id'), nullable=False)

    departure_time  = db.Column(db.String(9), index=True)
    arrival_time    = db.Column(db.String(9))
    dist_traveled   = db.Column(db.Integer, default=0)

    stop_headsign   = db.Column(db.String)
    pickup_type     = db.Column(db.Integer, default=0)
    drop_off_type   = db.Column(db.Integer, default=0)
    timepoint       = db.Column(db.Integer, default=1)

    __table_args__ = (db.UniqueConstraint('trip_id', 'stop_sequence', name='_trip_sequence_uc'), )

# 4cc08782-c-76aabf89-b