/* AREA DE DESARROLLO */
CREATE SCHEMA desarrollo1;
SET SEARCH_PATH TO desarrollo1;

/* REQUERIDO PARA TIPO GEOMETRY */
CREATE EXTENSION IF NOT EXISTS postgis;

/*==============================================================*/
/* Table: APLICACION                                            */
/*==============================================================*/
create table if not exists APLICACION (
   ID_APLICACION        INT4                 not null,
   NOMBRE_APP           varchar(100)         null,
   VIGENTE              BOOL                 null,
   constraint PK_APLICACION primary key (ID_APLICACION)
);

/*==============================================================*/
/* Table: COMUNA                                                */
/*==============================================================*/
create table if not exists COMUNA (
   ID_COMUNA            INT4                 not null,
   ID_REGION            INT4                 null,
   NOMBRE_COMUNA        varchar(100)         null,
   constraint PK_COMUNA primary key (ID_COMUNA)
);

/*==============================================================*/
/* Table: CONDUCTOR                                             */
/*==============================================================*/
create table if not exists CONDUCTOR (
   PATENTE              VARCHAR(10)          null,
   RUT                  NUMERIC(12)          null,
   VIGENTE              BOOL                 null
);

/*==============================================================*/
/* Table: DISPOSITIVO                                           */
/*==============================================================*/
create table if not exists DISPOSITIVO (
   ID_DISPOSITIVO       INT4                 not null,
   ID_PARADERO          INT4                 null,
   VIGENTE              BOOL                 null,
   ULTIMA_CONEXION      DATE                 null,
   constraint PK_DISPOSITIVO primary key (ID_DISPOSITIVO)
);

/*==============================================================*/
/* Table: FUNCIONARIO                                           */
/*==============================================================*/
create table if not exists FUNCIONARIO (
   RUT                  NUMERIC(12)          null,
   ID_OPERADOR          INT4                 null,
   DESDE                DATE                 null,
   HASTA                DATE                 null
);

/*==============================================================*/
/* Table: GTFS_CALENDAR                                         */
/*==============================================================*/
create table if not exists GTFS_CALENDAR (
   ID_LINEA             INT4                 not null,
   MONDAY               BOOL                 null,
   TUESDAY              BOOL                 null,
   WEDNESDAY            BOOL                 null,
   THURSDAY             BOOL                 null,
   FRIDAY               BOOL                 null,
   SALURDAY             BOOL                 null,
   SUNDAY               BOOL                 null,
   constraint PK_GTFS_CALENDAR primary key (ID_LINEA)
);

/*==============================================================*/
/* Table: GTFS_FREQUENCIE                                       */
/*==============================================================*/
create table if not exists GTFS_FREQUENCIE (
   ID_TRIPS             INT4                 null,
   START_TIME           TIME                 null,
   END_TIME             TIME                 null,
   HEADWAY_SECS         int                  null,
   EXACT_TIME           int                  null
);

/*==============================================================*/
/* Index: INDEX_FREQUENCIE                                      */
/*==============================================================*/
create  index INDEX_FREQUENCIE on GTFS_FREQUENCIE (
ID_TRIPS
);

/*==============================================================*/
/* Table: GTFS_ROUTES                                           */
/*==============================================================*/
create table if not exists GTFS_ROUTES (
   ID_ROUTES            INT4                 not null,
   ID_OPERADOR          INT4                 null,
   ID_ROUTE_TYPE        int                  null,
   SHORT_NAME           varchar(100)         null,
   LONG_NAME            varchar(300)         null,
   DESCRIPCION          varchar(500)         null,
   ROUTE_COLOR          VARCHAR(6)           null,
   ROUTE_TEXT_COLOR     VARCHAR(6)           null,
   ROUTE_SORT_ORDER     int                  null,
   constraint PK_GTFS_ROUTES primary key (ID_ROUTES)
);

/*==============================================================*/
/* Table: GTFS_ROUTE_TYPE                                       */
/*==============================================================*/
create table if not exists GTFS_ROUTE_TYPE (
   ID_ROUTE_TYPE        int                  not null,
   DESCRIPCION          varchar(100)         null,
   constraint PK_GTFS_ROUTE_TYPE primary key (ID_ROUTE_TYPE)
);

/*==============================================================*/
/* Table: GTFS_SHAPE                                            */
/*==============================================================*/
create table if not exists GTFS_SHAPE (
   ID_SHAPES            INT4                 not null,
   SHAPE_PT_LAT         geometry             null,
   SHAPE_PT_LON         geometry             null,
   SHAPE_PT_SEQUENCE    int                  null,
   SHAOE_DIST_TRAVELED  float                null,
   constraint PK_GTFS_SHAPE primary key (ID_SHAPES)
);

/*==============================================================*/
/* Table: GTFS_STOP_TIMES                                       */
/*==============================================================*/
create table if not exists GTFS_STOP_TIMES (
   ID_PARADERO          INT4                 not null,
   ID_TRIPS             INT4                 not null,
   ARRIVAL_TIME         TIME                 null,
   STOP_SEQUENCE        int                  null,
   STOP_HEADSIGN        varchar(100)         null,
   constraint PK_GTFS_STOP_TIMES primary key (ID_PARADERO, ID_TRIPS)
);

/*==============================================================*/
/* Table: GTFS_TRIPS                                            */
/*==============================================================*/
create table if not exists GTFS_TRIPS (
   ID_TRIPS             INT4                 not null,
   ID_ROUTES            INT4                 null,
   ID_LINEA             INT4                 null,
   ID_SHAPES            INT4                 null,
   ID_TRIPS_REGRESO     INT4                 null,
   TRIP_HEADSIGN        varchar(100)         null,
   SHORT_NAME           varchar(100)         null,
   DIRECCION_ID         int                  null,
   constraint PK_GTFS_TRIPS primary key (ID_TRIPS)
);

/*==============================================================*/
/* Table: LINEA                                                 */
/*==============================================================*/
create table if not exists LINEA (
   ID_LINEA             INT4                 not null,
   ID_OPERADOR          INT4                 null,
   IID_TIPO_TRANSPORTE  INT4                 null,
   ID_REGION            INT4                 null,
   VIGENTE              BOOL                 null,
   NOMBRE               VARCHAR(100)         null,
   URL                  varchar(300)         null,
   constraint PK_LINEA primary key (ID_LINEA)
);

/*==============================================================*/
/* Table: OPERADOR                                              */
/*==============================================================*/
create table if not exists OPERADOR (
   ID_OPERADOR          INT4                 not null,
   ID_REGION            INT4                 null,
   VIGENTE              BOOL                 null,
   constraint PK_OPERADOR primary key (ID_OPERADOR)
);

/*==============================================================*/
/* Table: PARADERO                                              */
/*==============================================================*/
create table if not exists PARADERO (
   ID_PARADERO          INT4                 not null,
   ID_COMUNA            INT4                 null,
   ID_TIPO_PARADERO     INT4                 null,
   VIGENTE              BOOL                 null,
   STOP_CODE            varchar(100)         null,
   STOP_NAME            varchar(100)         null,
   STOP_DESC            varchar(300)         null,
   STOP_LAT             geometry             null,
   STOP_LON             geometry             null,
   constraint PK_PARADERO primary key (ID_PARADERO)
);

/*==============================================================*/
/* Table: PARADERO_IMAGEN                                       */
/*==============================================================*/
create table if not exists PARADERO_IMAGEN (
   ID_PARADERO          INT4                 not null,
   IMAGEN               BYTEA                null,
   constraint PK_PARADERO_IMAGEN primary key (ID_PARADERO)
);

/*==============================================================*/
/* Table: PERSONA                                               */
/*==============================================================*/
create table if not exists PERSONA (
   RUT                  NUMERIC(12)          not null,
   ID_TIPO_TRATAMIENTO  INT4                 null,
   ID_COMUNA            INT4                 null,
   DV                   CHAR(1)              null,
   NOMBRES              VARCHAR(100)         null,
   APELLIDO_A           VARCHAR(100)         null,
   APELLIDO_B           VARCHAR(100)         null,
   FONO                 VARCHAR(100)         null,
   EMAIL                VARCHAR(100)         null,
   FECHA_NACIMIENTO     DATE                 null,
   DIRECCION            VARCHAR(100)         null,
   constraint PK_PERSONA primary key (RUT)
);

/*==============================================================*/
/* Table: REGION                                                */
/*==============================================================*/
create table if not exists REGION (
   ID_REGION            INT4                 not null,
   NOMBRE_REGION        VARCHAR(100)         not null,
   constraint PK_REGION primary key (ID_REGION)
);

/*==============================================================*/
/* Table: ROL                                                   */
/*==============================================================*/
create table if not exists ROL (
   ID_ROL               INT4                 not null,
   NOMBRE_ROL           varchar(100)         not null,
   constraint PK_ROL primary key (ID_ROL)
);

/*==============================================================*/
/* Table: ROL_APLICACION                                        */
/*==============================================================*/
create table if not exists ROL_APLICACION (
   ID_APLICACION        INT4                 not null,
   ID_ROL               INT4                 not null,
   SOLO_VISUALIZAR      BOOL                 null,
   ID_ROL_APP           SERIAL4              not null,
	CONSTRAINT rol_aplicacion_pk PRIMARY KEY (ID_ROL_APP)
);

CREATE UNIQUE INDEX rol_aplicacion_id_aplicacion_idx ON ROL_APLICACION USING btree (ID_APLICACION, ID_ROL);

/*==============================================================*/
/* Table: TIPO_DISPOSITIVO                                      */
/*==============================================================*/
create table if not exists TIPO_DISPOSITIVO (
   ID_DISPOSITIVO       INT4                 null,
   ID_TIPO_DISPOSITIVO  INT4                 null
);

/*==============================================================*/
/* Table: TIPO_PARADERO                                         */
/*==============================================================*/
create table if not exists TIPO_PARADERO (
   ID_TIPO_PARADERO     INT4                 not null,
   DESCRIPCION          VARCHAR(100)         null,
   constraint PK_TIPO_PARADERO primary key (ID_TIPO_PARADERO)
);

/*==============================================================*/
/* Table: TIPO_TRANSPORTE                                       */
/*==============================================================*/
create table if not exists TIPO_TRANSPORTE (
   IID_TIPO_TRANSPORTE  INT4                 not null,
   DESCRIPCION          VARCHAR(50)          null,
   constraint PK_TIPO_TRANSPORTE primary key (IID_TIPO_TRANSPORTE)
);

/*==============================================================*/
/* Table: TIPO_TRATAMIENTO_PERSONA                              */
/*==============================================================*/
create table if not exists TIPO_TRATAMIENTO_PERSONA (
   ID_TIPO_TRATAMIENTO  INT4                 not null,
   TRATAMIENTO          varchar(50)          null,
   constraint PK_TIPO_TRATAMIENTO_PERSONA primary key (ID_TIPO_TRATAMIENTO)
);

comment on table TIPO_TRATAMIENTO_PERSONA is
'Establece el tratamiento de como dirigirse hacia una persona:
Ejemplo
Señor
Señora
Srta';

/*==============================================================*/
/* Table: TIPO_VEHICULO                                         */
/*==============================================================*/
create table if not exists TIPO_VEHICULO (
   ID_TIPO_VEHICULO     int                  not null,
   DESCRIPCION          varchar(100)         null,
   constraint PK_TIPO_VEHICULO primary key (ID_TIPO_VEHICULO)
);

/*==============================================================*/
/* Table: USUARIO                                               */
/*==============================================================*/
create table if not exists USUARIO (
   LOGIN                VARCHAR(20)          not null,
   RUT                  NUMERIC(12)          null,
   CLAVE                VARCHAR(20)          null,
   VIGENTE              bool                 null,
   constraint PK_USUARIO primary key (LOGIN)
);

/*==============================================================*/
/* Table: USUARIO_ROL                                           */
/*==============================================================*/
create table if not exists USUARIO_ROL (
   LOGIN                VARCHAR(20)          null,
   ID_ROL               INT4                 null,
   VIGENTE              BOOL                 null
);

/*==============================================================*/
/* Table: VEHICULO                                              */
/*==============================================================*/
create table if not exists VEHICULO (
   PPU                  VARCHAR(10)          not null,
   ID_TIPO_VEHICULO     int                  null,
   VIGENTE              BOOL                 null,
   constraint PK_VEHICULO primary key (PPU)
);

/*==============================================================*/
/* Table: VEHICULO_LINEA                                        */
/*==============================================================*/
create table if not exists VEHICULO_LINEA (
   PATENTE              VARCHAR(10)          null,
   ID_LINEA             INT4                 null,
   VIGENTE              BOOL                 null
);

/*==============================================================*/
/* View: VW_PARADERO_LINEA                                      */
/*==============================================================*/
create or replace view VW_PARADERO_LINEA as
select;

alter table COMUNA
   add constraint FK_COMUNA_REFERENCE_REGION foreign key (ID_REGION)
      references REGION (ID_REGION)
      on delete restrict on update restrict;

alter table CONDUCTOR
   add constraint FK_CONDUCTO_REFERENCE_VEHICULO foreign key (PATENTE)
      references VEHICULO (PPU)
      on delete restrict on update restrict;

alter table CONDUCTOR
   add constraint FK_CONDUCTO_REFERENCE_PERSONA foreign key (RUT)
      references PERSONA (RUT)
      on delete restrict on update restrict;

alter table DISPOSITIVO
   add constraint FK_DISPOSIT_REFERENCE_PARADERO foreign key (ID_PARADERO)
      references PARADERO (ID_PARADERO)
      on delete restrict on update restrict;

alter table FUNCIONARIO
   add constraint FK_FUNCIONA_REFERENCE_PERSONA foreign key (RUT)
      references PERSONA (RUT)
      on delete restrict on update restrict;

alter table FUNCIONARIO
   add constraint FK_FUNCIONA_REFERENCE_OPERADOR foreign key (ID_OPERADOR)
      references OPERADOR (ID_OPERADOR)
      on delete restrict on update restrict;

alter table GTFS_CALENDAR
   add constraint FK_GTFS_CAL_REFERENCE_LINEA foreign key (ID_LINEA)
      references LINEA (ID_LINEA)
      on delete restrict on update restrict;

alter table GTFS_FREQUENCIE
   add constraint FK_GTFS_FRE_REFERENCE_GTFS_TRI foreign key (ID_TRIPS)
      references GTFS_TRIPS (ID_TRIPS)
      on delete restrict on update restrict;

alter table GTFS_ROUTES
   add constraint FK_GTFS_ROU_REFERENCE_OPERADOR foreign key (ID_OPERADOR)
      references OPERADOR (ID_OPERADOR)
      on delete restrict on update restrict;

alter table GTFS_ROUTES
   add constraint FK_GTFS_ROU_REFERENCE_GTFS_ROU foreign key (ID_ROUTE_TYPE)
      references GTFS_ROUTE_TYPE (ID_ROUTE_TYPE)
      on delete restrict on update restrict;

alter table GTFS_STOP_TIMES
   add constraint FK_GTFS_STO_REFERENCE_PARADERO foreign key (ID_PARADERO)
      references PARADERO (ID_PARADERO)
      on delete restrict on update restrict;

alter table GTFS_STOP_TIMES
   add constraint FK_GTFS_STO_REFERENCE_GTFS_TRI foreign key (ID_TRIPS)
      references GTFS_TRIPS (ID_TRIPS)
      on delete restrict on update restrict;

alter table GTFS_TRIPS
   add constraint FK_GTFS_TRI_REFERENCE_GTFS_ROU foreign key (ID_ROUTES)
      references GTFS_ROUTES (ID_ROUTES)
      on delete restrict on update restrict;

alter table GTFS_TRIPS
   add constraint FK_GTFS_TRI_REFERENCE_LINEA foreign key (ID_LINEA)
      references LINEA (ID_LINEA)
      on delete restrict on update restrict;

alter table GTFS_TRIPS
   add constraint FK_GTFS_TRI_REFERENCE_GTFS_SHA foreign key (ID_SHAPES)
      references GTFS_SHAPE (ID_SHAPES)
      on delete restrict on update restrict;

alter table GTFS_TRIPS
   add constraint FK_GTFS_TRI_REFERENCE_GTFS_TRI foreign key (ID_TRIPS_REGRESO)
      references GTFS_TRIPS (ID_TRIPS)
      on delete restrict on update restrict;

alter table LINEA
   add constraint FK_LINEA_REFERENCE_OPERADOR foreign key (ID_OPERADOR)
      references OPERADOR (ID_OPERADOR)
      on delete restrict on update restrict;

alter table LINEA
   add constraint FK_LINEA_REFERENCE_TIPO_TRA foreign key (IID_TIPO_TRANSPORTE)
      references TIPO_TRANSPORTE (IID_TIPO_TRANSPORTE)
      on delete restrict on update restrict;

alter table LINEA
   add constraint FK_LINEA_REFERENCE_REGION foreign key (ID_REGION)
      references REGION (ID_REGION)
      on delete restrict on update restrict;

alter table OPERADOR
   add constraint FK_OPERADOR_REFERENCE_REGION foreign key (ID_REGION)
      references REGION (ID_REGION)
      on delete restrict on update restrict;

alter table PARADERO
   add constraint FK_PARADERO_REFERENCE_COMUNA foreign key (ID_COMUNA)
      references COMUNA (ID_COMUNA)
      on delete restrict on update restrict;

alter table PARADERO
   add constraint FK_PARADERO_REFERENCE_TIPO_PAR foreign key (ID_TIPO_PARADERO)
      references TIPO_PARADERO (ID_TIPO_PARADERO)
      on delete restrict on update restrict;

alter table PARADERO_IMAGEN
   add constraint FK_PARADERO_REFERENCE_PARADERO foreign key (ID_PARADERO)
      references PARADERO (ID_PARADERO)
      on delete restrict on update restrict;

alter table PERSONA
   add constraint FK_PERSONA_REFERENCE_COMUNA foreign key (ID_COMUNA)
      references COMUNA (ID_COMUNA)
      on delete restrict on update restrict;

alter table PERSONA
   add constraint FK_PERSONA_REFERENCE_TIPO_TRA foreign key (ID_TIPO_TRATAMIENTO)
      references TIPO_TRATAMIENTO_PERSONA (ID_TIPO_TRATAMIENTO)
      on delete restrict on update restrict;

alter table ROL_APLICACION
   add constraint FK_ROL_APLI_REFERENCE_APLICACI foreign key (ID_APLICACION)
      references APLICACION (ID_APLICACION)
      on delete restrict on update restrict;

alter table ROL_APLICACION
   add constraint FK_ROL_APLI_REFERENCE_ROL foreign key (ID_ROL)
      references ROL (ID_ROL)
      on delete restrict on update restrict;

alter table TIPO_DISPOSITIVO
   add constraint FK_TIPO_DIS_REFERENCE_DISPOSIT foreign key (ID_DISPOSITIVO)
      references DISPOSITIVO (ID_DISPOSITIVO)
      on delete restrict on update restrict;

alter table USUARIO
   add constraint FK_USUARIO_REFERENCE_PERSONA foreign key (RUT)
      references PERSONA (RUT)
      on delete restrict on update restrict;

alter table USUARIO_ROL
   add constraint FK_USUARIO__REFERENCE_USUARIO foreign key (LOGIN)
      references USUARIO (LOGIN)
      on delete restrict on update restrict;

alter table USUARIO_ROL
   add constraint FK_USUARIO__REFERENCE_ROL foreign key (ID_ROL)
      references ROL (ID_ROL)
      on delete restrict on update restrict;

alter table VEHICULO
   add constraint FK_VEHICULO_REFERENCE_TIPO_VEH foreign key (ID_TIPO_VEHICULO)
      references TIPO_VEHICULO (ID_TIPO_VEHICULO)
      on delete restrict on update restrict;

alter table VEHICULO_LINEA
   add constraint FK_VEHICULO_REFERENCE_VEHICULO foreign key (PATENTE)
      references VEHICULO (PPU)
      on delete restrict on update restrict;

alter table VEHICULO_LINEA
   add constraint FK_VEHICULO_REFERENCE_LINEA foreign key (ID_LINEA)
      references LINEA (ID_LINEA)
      on delete restrict on update restrict;