CREATE DATABASE trens;

USE trens;

CREATE TABLE Engenheiro (
    codigo INTEGER NOT NULL AUTO_INCREMENT,
    nome VARCHAR(30) NOT NULL,
    PRIMARY KEY(codigo)
);

CREATE TABLE TremLocal (
    id INTEGER NOT NULL AUTO_INCREMENT,
    engenheiro INTEGER NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(engenheiro) REFERENCES Engenheiro(codigo)
);

CREATE TABLE TremExpresso (
    id INTEGER NOT NULL AUTO_INCREMENT,
    engenheiro INTEGER NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(engenheiro) REFERENCES Engenheiro(codigo)
);

CREATE TABLE Estacao (
    nome VARCHAR(30) NOT NULL,
    endereco VARCHAR(30) NOT NULL,
    tipo ENUM('L', 'E'),
    PRIMARY KEY(nome)
);

CREATE TABLE EstacaoLocal (
    nome VARCHAR(30) NOT NULL,
    PRIMARY KEY(nome),
    FOREIGN KEY(nome) REFERENCES Estacao(nome)
);

CREATE TABLE EstacaoExpressa (
    nome VARCHAR(30) NOT NULL,
    PRIMARY KEY(nome),
    FOREIGN KEY(nome) REFERENCES Estacao(nome)
);

CREATE TABLE ParadaTremLocal (
    trem INTEGER NOT NULL,
    estacao VARCHAR(30) NOT NULL,
    HoraIda TIME NOT NULL,
    HoraVolta TIME NOT NULL,
    PRIMARY KEY(trem, estacao),
    FOREIGN KEY(trem) REFERENCES TremLocal(id),
    FOREIGN KEY(estacao) REFERENCES Estacao(nome)
);

CREATE TABLE ParadaTremExpresso (
    trem INTEGER NOT NULL,
    estacao VARCHAR(30) NOT NULL,
    horaida TIME NOT NULL,
    horavolta TIME NOT NULL,
    PRIMARY KEY(trem, estacao),
    FOREIGN KEY(trem) REFERENCES TremExpresso(id),
    FOREIGN KEY(estacao) REFERENCES EstacaoExpressa(nome)
);

-- POPULANDO AS TABELAS

INSERT INTO Engenheiro(nome) VALUES
('Rafael Vales'),
('Gustavo Felix'),
('Bill Gates');

INSERT INTO TremLocal(engenheiro) VALUES
('2'), ('1'), ('2'), ('3'), ('2');

INSERT INTO TremExpresso(engenheiro) VALUES
('3'), ('1'), ('2'), ('1');

INSERT INTO Estacao(nome, endereco, tipo) VALUES
('Estacao Local Um', 'Rua Vermelha', 'L'),
('Estacao Local Dois', 'Rua Branca', 'L'),
('Estacao Expressa Um', 'Rua Verde', 'E'),
('Estacao Local Tres', 'Rua Amarela', 'L'),
('Estacao Expressa Dois', 'Rua Laranja', 'E');

INSERT INTO EstacaoLocal(nome) VALUES
('Estacao Local Um'),
('Estacao Local Dois'),
('Estacao Local Tres');

INSERT INTO EstacaoExpressa(nome) VALUES
('Estacao Expressa Um'),
('Estacao Expressa Dois');

INSERT INTO ParadaTremLocal(trem, estacao, horaida, horavolta) VALUES
('1', 'Estacao Local Tres', '08:00', '10:30'),
('2', 'Estacao Local Dois', '10:00', '12:00'),
('3', 'Estacao Expressa Dois', '14:30', '16:15'),
('1', 'Estacao Expressa Um', '16:00', '18:30'),
('5', 'Estacao Local Um', '17:15', '19:45');

INSERT INTO ParadaTremExpresso(trem, estacao, horaida, horavolta) VALUES
('2', 'Estacao Expressa Um', '04:30', '11:00'),
('4', 'Estacao Expressa Dois', '12:26', '14:00'),
('1', 'Estacao Expressa Um', '18:00', '23:00');

SELECT * FROM ParadaTremExpresso;