DatabaseCreation: ddl.sql

CREATE TYPE bus_status AS ENUM ('Active', 'Inactive', 'Under_maintenance');
CREATE TYPE fuel_type AS ENUM ('Diesel', 'Petrol', 'Electric');
CREATE TYPE employee_status AS ENUM ('Active', 'Inactive');
CREATE TYPE customer_type AS ENUM ('Regular', 'Student', 'Retiree');
CREATE TYPE payment_status AS ENUM ('Pending', 'Completed', 'Failed');

CREATE TABLE Bus
(
    bus_id BIGSERIAL NOT NULL,
    brand  varchar(255),
    PRIMARY KEY (bus_id)
);

CREATE TABLE Bus_instance
(
    chassis_number      varchar(255) NOT NULL,
    bus_id              int8         NOT NULL,
    capacity            int4,
    model               varchar(255),
    registration_number varchar(255) UNIQUE,
    date_of_manufacture date,
    fuel_type           fuel_type,
    status              bus_status,
    PRIMARY KEY (chassis_number),
    CONSTRAINT bus_instance_bus_fk
        FOREIGN KEY (bus_id) REFERENCES Bus (bus_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT check_capacity
        CHECK (capacity BETWEEN 25 AND 80)
);

CREATE TABLE ApplicationUser
(
    user_id       BIGSERIAL NOT NULL,
    name          varchar(255),
    surname       varchar(255),
    email         varchar(255),
    address       varchar(255) UNIQUE,
    date_of_birth date,
    phone_number  varchar(255) UNIQUE,
    id_card       varchar(255) UNIQUE,
    PRIMARY KEY (user_id),
    CONSTRAINT check_email_format
        CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT check_phone_format
        CHECK (phone_number ~ '^(\+389|0)[0-9]{7,9}$'),
    CONSTRAINT check_date_birth
        CHECK (date_of_birth <= CURRENT_DATE - INTERVAL '14 years')
);

CREATE TABLE Admin
(
    user_id           int8 NOT NULL,
    verification_code varchar(255) UNIQUE,
    employment_date   date,
    status            employee_status,
    PRIMARY KEY (user_id),
    CONSTRAINT admin_user_fk
        FOREIGN KEY (user_id) REFERENCES ApplicationUser (user_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
);

CREATE TABLE Driver
(
    user_id           int8 NOT NULL,
    driver_license    varchar(255) UNIQUE,
    employment_date   date,
    status            employee_status,
    verification_code varchar(255) UNIQUE,
    PRIMARY KEY (user_id),
    CONSTRAINT driver_user_fk
        FOREIGN KEY (user_id) REFERENCES ApplicationUser (user_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE

);

CREATE TABLE Conductor
(
    user_id           int8 NOT NULL,
    employment_date   date,
    status            employee_status,
    verification_code varchar(255) UNIQUE,
    PRIMARY KEY (user_id),
    CONSTRAINT conductor_user_fk
        FOREIGN KEY (user_id) REFERENCES ApplicationUser (user_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
);

CREATE TABLE Customer
(
    user_id int8 NOT NULL,
    type    customer_type,
    PRIMARY KEY (user_id),
    CONSTRAINT customer_user_fk
        FOREIGN KEY (user_id) REFERENCES ApplicationUser (user_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
);

CREATE TABLE Maintenance
(
    maintenance_id   BIGSERIAL NOT NULL,
    chassis_number   varchar(255) UNIQUE,
    maintenance_date date,
    description      varchar(255),
    cost             int4,
    maintenance_name varchar(255),
    PRIMARY KEY (maintenance_id),
    CONSTRAINT maintenance_bus_instance_fk
        FOREIGN KEY (chassis_number) REFERENCES Bus_instance (chassis_number)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT check_cost
        CHECK (cost > 0)
);

CREATE TABLE Maintainer
(
    user_id           int8 NOT NULL,
    employment_date   date,
    status            employee_status,
    maintenance_id    int8,
    verification_code varchar(255) UNIQUE,
    PRIMARY KEY (user_id),
    CONSTRAINT maintainer_user_fk
        FOREIGN KEY (user_id) REFERENCES ApplicationUser (user_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT maintainer_maintenance_fk
        FOREIGN KEY (maintenance_id) REFERENCES Maintenance (maintenance_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE
);

CREATE TABLE Station
(
    station_id     SERIAL NOT NULL,
    ordinal_number int4,
    station_name   varchar(255) UNIQUE,
    address        varchar(255),
    PRIMARY KEY (station_id)
);

CREATE TABLE Line
(
    line_id          SERIAL NOT NULL,
    start_station_id int4   NOT NULL,
    end_station_id   int4   NOT NULL,
    line_number      int4,
    is_active        bool,
    PRIMARY KEY (line_id),
    CONSTRAINT line_start_station_fk
        FOREIGN KEY (start_station_id) REFERENCES Station (station_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT line_end_station_fk
        FOREIGN KEY (end_station_id) REFERENCES Station (station_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE
);

CREATE TABLE Schedule
(
    schedule_id    SERIAL NOT NULL,
    line_id        int4   NOT NULL,
    departure_time time,
    arrival_time   time,
    day_of_week    varchar(255),
    PRIMARY KEY (schedule_id),
    CONSTRAINT schedule_line_fk
        FOREIGN KEY (line_id) REFERENCES Line (line_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT check_schedule_time
        CHECK (departure_time < arrival_time),
    CONSTRAINT check_day_of_week
        CHECK (day_of_week IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'))
);

CREATE TABLE Position
(
    station_id     int4 NOT NULL,
    line_id        int4 NOT NULL,
    num_of_station int4 NOT NULL,
    PRIMARY KEY (station_id, line_id, num_of_station),
    CONSTRAINT position_station_fk
        FOREIGN KEY (station_id) REFERENCES Station (station_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT position_line_fk
        FOREIGN KEY (line_id) REFERENCES Line (line_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
);

CREATE TABLE Payment_type
(
    type_id     BIGSERIAL NOT NULL,
    type        varchar(255),
    description varchar(255),
    PRIMARY KEY (type_id)
);

CREATE TABLE Payment
(
    payment_id         BIGSERIAL NOT NULL,
    type_id            int8      NOT NULL,
    status             payment_status,
    payment_date       date,
    transaction_number varchar(255),
    PRIMARY KEY (payment_id),
    CONSTRAINT payment_type_fk
        FOREIGN KEY (type_id) REFERENCES Payment_type (type_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE
);

CREATE TABLE Pass_type
(
    id          SERIAL       NOT NULL,
    type        varchar(255) NOT NULL,
    valid_from  date,
    valid_until date,
    amount      int4,
    PRIMARY KEY (id),
    CONSTRAINT check_pass_type_time
        CHECK (valid_from < valid_until),
    CONSTRAINT check_price
        CHECK (amount > 0)
);

CREATE TABLE Ticket
(
    ticket_id BIGSERIAL NOT NULL,
    user_id   int8      NOT NULL,
    PRIMARY KEY (ticket_id),
    CONSTRAINT ticket_user_fk
        FOREIGN KEY (user_id) REFERENCES Customer (user_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE
);

CREATE TABLE Line_assignment
(
    assignment_id  BIGSERIAL    NOT NULL,
    driver_id      int8         NOT NULL,
    admin_id       int8         NOT NULL,
    chassis_number varchar(255) NOT NULL,
    schedule_id    int4         NOT NULL,
    start_time     timestamp,
    end_time       timestamp,
    PRIMARY KEY (assignment_id),
    CONSTRAINT line_assignment_driver_fk
        FOREIGN KEY (driver_id) REFERENCES Driver (user_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT line_assignment_admin_fk
        FOREIGN KEY (admin_id) REFERENCES Admin (user_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT line_assignment_bus_instance_fk
        FOREIGN KEY (chassis_number) REFERENCES Bus_instance (chassis_number)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT line_assignment_schedule_fk
        FOREIGN KEY (schedule_id) REFERENCES Schedule (schedule_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT check_time
        CHECK (start_time < end_time),
    CONSTRAINT unique_driver_shift
        UNIQUE (driver_id, start_time, end_time)
);

CREATE TABLE Single_ticket
(
    ticket_id     int8 NOT NULL,
    amount        int4,
    assignment_id int8 NOT NULL,
    PRIMARY KEY (ticket_id),
    CONSTRAINT single_ticket_ticket_fk
        FOREIGN KEY (ticket_id) REFERENCES Ticket (ticket_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT single_ticket_assignment_fk
        FOREIGN KEY (assignment_id) REFERENCES Line_assignment (assignment_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT check_single_ticket_price
        CHECK (amount = 50)
);

CREATE TABLE Pass_ticket
(
    ticket_id int8 NOT NULL,
    type_id   int4 NOT NULL,
    PRIMARY KEY (ticket_id),
    CONSTRAINT pass_ticket_ticket_fk
        FOREIGN KEY (ticket_id) REFERENCES Ticket (ticket_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT pass_ticket_pass_type_fk
        FOREIGN KEY (type_id) REFERENCES Pass_type (id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE
);

CREATE TABLE Customer_Payment_Ticket
(
    payment_id int8 NOT NULL,
    ticket_id  int8 NOT NULL,
    user_id    int8 NOT NULL,
    PRIMARY KEY (payment_id, ticket_id, user_id),
    CONSTRAINT cpt_payment_fk
        FOREIGN KEY (payment_id) REFERENCES Payment (payment_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT cpt_ticket_fk
        FOREIGN KEY (ticket_id) REFERENCES Ticket (ticket_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT cpt_customer_fk
        FOREIGN KEY (user_id) REFERENCES Customer (user_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE
);

CREATE TABLE Validation
(
    validation_id   BIGSERIAL NOT NULL,
    conductor_id    int8      NOT NULL,
    ticket_id       int8      NOT NULL,
    assignment_id   int8      NOT NULL,
    validation_time timestamp,
    PRIMARY KEY (validation_id),
    CONSTRAINT conductor_fk
        FOREIGN KEY (conductor_id) REFERENCES Conductor (user_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT ticket_id
        FOREIGN KEY (ticket_id) REFERENCES Ticket (ticket_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
    CONSTRAINT assignment_id
        FOREIGN KEY (assignment_id) REFERENCES Line_assignment (assignment_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
);