/* CREATE TABLE */
CREATE TABLE USUARIO (
    CODIGO BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    NOME   VARCHAR(50),
    EMAIL  VARCHAR(255),
    SENHA  VARCHAR(255),
    CONSTRAINT USUARIO_PK PRIMARY KEY ("CODIGO")
);

CREATE TABLE ONIBUS (
    CODIGO BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    DESCRICAO VARCHAR(50),
    ITINERARIOIDA   VARCHAR(255),
    ITINERARIOVOLTA VARCHAR(255),
    COMPANHIA       CHAR(2),
    IDENTIFICADOR   CHAR(3),
    CONSTRAINT  ONIBUS_PK PRIMARY KEY (CODIGO)
);

CREATE TABLE AMIZADE (
CODIGO BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    CDUSUARIO BIGINT NOT NULL,
    CDAMIGO    BIGINT NOT NULL,
CONSTRAINT AMIZADE_PK PRIMARY KEY (CODIGO)
);

CREATE TABLE  ASSINATURA
(
CODIGO BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
CDONIBUS BIGINT NOT NULL,
CDUSUARIO BIGINT NOT NULL,
CONSTRAINT  ASSINATURA_PK PRIMARY KEY (CODIGO)
);

CREATE TABLE COMENTARIO (
CODIGO BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    CDONIBUS        BIGINT,
    CDUSUARIO       BIGINT,
    DTPUBLICACAO    DATE NOT NULL,
    DESCRICAO       VARCHAR(255),
    QTDAPROVACAO    INT,
    QTDDESAPROVACAO INT,
CONSTRAINT  COMENTARIO_PK PRIMARY KEY (CODIGO)
);


/* CONSTRAINTS */

ALTER TABLE  ASSINATURA
ADD CONSTRAINT ASSINATURA_FK FOREIGN KEY (CDONIBUS)
REFERENCES  ONIBUS (CODIGO);

ALTER TABLE  ASSINATURA
ADD CONSTRAINT ASSINATURA_FK2 FOREIGN KEY (CDUSUARIO)
REFERENCES  USUARIO (CODIGO);

ALTER TABLE AMIZADE ADD CONSTRAINT AMIZADE_FK
FOREIGN KEY (CDUSUARIO)
REFERENCES USUARIO (CODIGO);

ALTER TABLE AMIZADE ADD CONSTRAINT AMIZADE_FK2
FOREIGN KEY (CDAMIGO)
REFERENCES USUARIO (CODIGO);

ALTER TABLE COMENTARIO ADD CONSTRAINT COMENTARIO_FK
FOREIGN KEY (CDONIBUS)
REFERENCES ONIBUS (CODIGO);

ALTER TABLE COMENTARIO ADD CONSTRAINT COMENTARIO_FK2
FOREIGN KEY (CDUSUARIO)
REFERENCES USUARIO (CODIGO);


/* INSERTS */
INSERT INTO USUARIO (NOME,EMAIL,SENHA) VALUES ('ernane','ernane.sena@gmail.com','abc123');
INSERT INTO USUARIO (NOME,EMAIL,SENHA) VALUES ('atamy','atamytanoue@gmail.com','konoha');
INSERT INTO USUARIO (NOME,EMAIL,SENHA) VALUES ('eduardo','cefs18984@gmail.com','abc123');
INSERT INTO USUARIO (NOME,EMAIL,SENHA) VALUES ('vinicius','vinicius.salubri@gmail.com','abc123');