-- Caminhos de Ferro Nacionais (CFN) - Ficha 8
-- 3. Fase Fisica - Script DDL (SQLite)

PRAGMA foreign_keys = ON;

CREATE TABLE codigo_postal (
    codigo_postal CHAR(8) PRIMARY KEY CHECK (codigo_postal LIKE '____-___'),
    localidade    VARCHAR(60) NOT NULL
);

CREATE TABLE estacao (
    id_estacao      INTEGER PRIMARY KEY AUTOINCREMENT,
    nome            VARCHAR(80) NOT NULL UNIQUE,
    morada          VARCHAR(120) NOT NULL,
    codigo_postal   CHAR(8) NOT NULL,
    num_plataformas TINYINT NOT NULL,
    FOREIGN KEY (codigo_postal) REFERENCES codigo_postal(codigo_postal)
);

CREATE TABLE comboio (
    id_comboio   INTEGER PRIMARY KEY AUTOINCREMENT,
    numero_serie VARCHAR(20) NOT NULL UNIQUE,
    modelo       VARCHAR(50) NOT NULL,
    tipo         VARCHAR(20) NOT NULL CHECK (tipo IN ('Alfa Pendular', 'Intercidades', 'Regional', 'Urbano')),
    capacidade   SMALLINT NOT NULL CHECK (capacidade > 0),
    ano_fabrico  SMALLINT NOT NULL,
    estado       VARCHAR(20) NOT NULL
);

CREATE TABLE cargo (
    id_cargo     INTEGER PRIMARY KEY AUTOINCREMENT,
    nome_cargo   VARCHAR(50) NOT NULL UNIQUE,
    salario_base DECIMAL(8,2) NOT NULL CHECK (salario_base > 0)
);

CREATE TABLE funcionario (
    id_funcionario  INTEGER PRIMARY KEY AUTOINCREMENT,
    nif             CHAR(9) NOT NULL UNIQUE CHECK (LENGTH(nif) = 9),
    nome_completo   VARCHAR(100) NOT NULL,
    data_nascimento DATE NOT NULL,
    telefone        VARCHAR(15),
    email           VARCHAR(100) NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    data_admissao   DATE NOT NULL,
    id_cargo        INT NOT NULL,
    id_estacao      INT NOT NULL,
    CHECK (data_admissao > data_nascimento),
    FOREIGN KEY (id_cargo)   REFERENCES cargo(id_cargo),
    FOREIGN KEY (id_estacao) REFERENCES estacao(id_estacao)
);

CREATE TABLE passageiro (
    id_passageiro   INTEGER PRIMARY KEY AUTOINCREMENT,
    nif             CHAR(9) UNIQUE CHECK (LENGTH(nif) = 9),
    nome_completo   VARCHAR(100) NOT NULL,
    data_nascimento DATE,
    email           VARCHAR(100) NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    telefone        VARCHAR(15),
    morada          VARCHAR(120),
    codigo_postal   CHAR(8),
    FOREIGN KEY (codigo_postal) REFERENCES codigo_postal(codigo_postal) ON DELETE SET NULL
);

CREATE TABLE viagem (
    id_viagem          INTEGER PRIMARY KEY AUTOINCREMENT,
    id_comboio         INT NOT NULL,
    id_estacao_origem  INT NOT NULL,
    id_estacao_destino INT NOT NULL,
    data_hora_partida  DATETIME NOT NULL,
    data_hora_chegada  DATETIME NOT NULL,
    estado             VARCHAR(20) NOT NULL CHECK (estado IN ('Agendada', 'Em curso', 'Concluída', 'Atrasada', 'Cancelada')),
    CHECK (id_estacao_origem <> id_estacao_destino),
    CHECK (data_hora_chegada > data_hora_partida),
    FOREIGN KEY (id_comboio)         REFERENCES comboio(id_comboio),
    FOREIGN KEY (id_estacao_origem)  REFERENCES estacao(id_estacao),
    FOREIGN KEY (id_estacao_destino) REFERENCES estacao(id_estacao)
);

CREATE TABLE bilhete (
    id_bilhete    INTEGER PRIMARY KEY AUTOINCREMENT,
    id_viagem     INT NOT NULL,
    id_passageiro INT NOT NULL,
    classe        TINYINT NOT NULL CHECK (classe IN (1, 2)),
    carruagem     TINYINT NOT NULL,
    lugar         SMALLINT NOT NULL,
    preco         DECIMAL(6,2) NOT NULL CHECK (preco >= 0),
    data_compra   DATETIME NOT NULL,
    UNIQUE (id_viagem, carruagem, lugar),
    FOREIGN KEY (id_viagem)     REFERENCES viagem(id_viagem),
    FOREIGN KEY (id_passageiro) REFERENCES passageiro(id_passageiro)
);

CREATE TABLE escala (
    id_viagem      INT NOT NULL,
    id_funcionario INT NOT NULL,
    funcao         VARCHAR(30) NOT NULL CHECK (funcao IN ('Maquinista', 'Revisor', 'Chefe de comboio', 'Assistente de bordo')),
    PRIMARY KEY (id_viagem, id_funcionario),
    FOREIGN KEY (id_viagem)      REFERENCES viagem(id_viagem) ON DELETE CASCADE,
    FOREIGN KEY (id_funcionario) REFERENCES funcionario(id_funcionario)
);
select * from escala ;
select * from bilhete ;