-- BASE DE DADOS CFN
-- Caminhos de Ferro Nacionais

PRAGMA foreign_keys = ON;

-- 1. TABELA ESTACAO

CREATE TABLE Estacao (
    ID_Estacao INTEGER PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL,
    Localidade VARCHAR(100) NOT NULL,
    Morada VARCHAR(150) NOT NULL,
    Codigo_Postal VARCHAR(8) NOT NULL
);

-- 2. TABELA FUNCIONARIO

CREATE TABLE Funcionario (
    ID_Funcionario INTEGER PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL,
    NIF CHAR(9) NOT NULL UNIQUE,
    Telefone VARCHAR(15) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Cargo VARCHAR(50) NOT NULL
);

-- 3. TABELA COMBOIO

CREATE TABLE Comboio (
    ID_Comboio INTEGER PRIMARY KEY,
    Codigo VARCHAR(20) NOT NULL UNIQUE,
    Tipo VARCHAR(50) NOT NULL,
    Capacidade INTEGER NOT NULL CHECK (Capacidade > 0),
    Estado VARCHAR(20) NOT NULL
        CHECK (Estado IN ('Ativo', 'Manutencao', 'Inativo'))
);

-- 4. TABELA PASSAGEIRO

CREATE TABLE Passageiro (
    ID_Passageiro INTEGER PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL,
    NIF CHAR(9) NOT NULL UNIQUE,
    Telefone VARCHAR(15) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

-- 5. TABELA VIAGEM

CREATE TABLE Viagem (
    ID_Viagem INTEGER PRIMARY KEY,
    ID_Estacao INTEGER NOT NULL,
    ID_Comboio INTEGER NOT NULL,
    ID_Funcionario INTEGER NOT NULL,
    Data DATE NOT NULL,
    Hora_Partida TIME NOT NULL,
    Hora_Chegada TIME NOT NULL,
    Estado VARCHAR(20) NOT NULL
        CHECK (Estado IN (
            'Agendada',
            'Em curso',
            'Concluida',
            'Cancelada'
        )),

    FOREIGN KEY (ID_Estacao)
        REFERENCES Estacao(ID_Estacao),

    FOREIGN KEY (ID_Comboio)
        REFERENCES Comboio(ID_Comboio),

    FOREIGN KEY (ID_Funcionario)
        REFERENCES Funcionario(ID_Funcionario)
);

-- 6. TABELA BILHETE

CREATE TABLE Bilhete (
    ID_Bilhete INTEGER PRIMARY KEY,
    ID_Passageiro INTEGER NOT NULL,
    ID_Viagem INTEGER NOT NULL,
    Data_Compra DATE NOT NULL,
    Lugar INTEGER NOT NULL CHECK (Lugar > 0),
    Preco DECIMAL(8,2) NOT NULL CHECK (Preco >= 0),
    Classe VARCHAR(20) NOT NULL
        CHECK (Classe IN ('Economica', 'Primeira')),

    FOREIGN KEY (ID_Passageiro)
        REFERENCES Passageiro(ID_Passageiro),

    FOREIGN KEY (ID_Viagem)
        REFERENCES Viagem(ID_Viagem),

    UNIQUE (ID_Viagem, Lugar)
);