CREATE TABLE AGENCY (
    AGENCY_ID       SERIAL          NOT NULL,
    AGENCY_NAME     VARCHAR(100)    NOT NULL    DEFAULT 'Unknown Agency',
    AGENCY_URL      VARCHAR(255),
    AGENCY_TIMEZONE VARCHAR(50)     NOT NULL    DEFAULT 'UTC',
    AGENCY_LANG     VARCHAR(10)                 DEFAULT 'en',
    AGENCY_PHONE    VARCHAR(30),
    AGENCY_EMAIL    VARCHAR(100),
    AGENCY_FARE_URL VARCHAR(255),

    CONSTRAINT PK_AGENCY PRIMARY KEY (AGENCY_ID), 

    CONSTRAINT CHK_AGENCY_EMAIL CHECK (
        AGENCY_EMAIL IS NULL OR
        AGENCY_EMAIL ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'
    ), 

    CONSTRAINT CHK_AGENCY_PHONE CHECK (
        AGENCY_PHONE IS NULL OR
        AGENCY_PHONE ~ '^\+?[0-9\s\(\)\-]{7,20}$'
    ) 
);

CREATE TABLE DISCOUNT (
    DISCOUNT_ID             SERIAL          NOT NULL,
    DISCOUNT_NAME           VARCHAR(100)    NOT NULL    DEFAULT 'General Discount',
    DISCOUNT_PERCENTAGE     DECIMAL(5,2)    NOT NULL    DEFAULT 0.00,
    DESCRIPTION             TEXT,
    ELIGIBILITY_CRITERIA    TEXT,
    VALID_FROM              DATE                        DEFAULT CURRENT_DATE,
    VALID_TO                DATE,

    CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNT_ID), 

    CONSTRAINT CHK_DISCOUNT_PERCENTAGE CHECK (
        DISCOUNT_PERCENTAGE BETWEEN 0 AND 100
    ), 

    CONSTRAINT CHK_DISCOUNT_DATES CHECK (
        VALID_FROM IS NULL OR VALID_TO IS NULL OR VALID_FROM <= VALID_TO
    ) 
);

CREATE TABLE ZONE (
    ZONE_ID     VARCHAR(50)     NOT NULL,
    ZONE_NAME   VARCHAR(100)    NOT NULL    DEFAULT 'Unnamed Zone',
    ZONE_NUMBER INTEGER,
    DESCRIPTION TEXT,

    CONSTRAINT PK_ZONE PRIMARY KEY (ZONE_ID), 

    CONSTRAINT CHK_ZONE_NUMBER CHECK (
        ZONE_NUMBER IS NULL OR ZONE_NUMBER >= 0
    ) 
);

CREATE TABLE VEHICLE_TYPE (
    VEHICLE_TYPE_ID SERIAL          NOT NULL,
    TYPE_NAME       VARCHAR(50)     NOT NULL    DEFAULT 'Unknown Type',
    DESCRIPTION     TEXT,

    CONSTRAINT PK_VEHICLE_TYPE PRIMARY KEY (VEHICLE_TYPE_ID)
);

CREATE TABLE STOPS (
    STOP_ID         VARCHAR(50)     NOT NULL,
    STOP_NAME       VARCHAR(100)    NOT NULL    DEFAULT 'Unnamed Stop',
    LATITUDE        DECIMAL(10,7)   NOT NULL,
    LONGITUDE       DECIMAL(10,7)   NOT NULL,
    ZONE_ID         VARCHAR(50),
    VEHICLE_TYPE_ID INTEGER			NOT NULL,

    CONSTRAINT PK_STOPS PRIMARY KEY (STOP_ID, VEHICLE_TYPE_ID), 

    -- ако се избрише зоната, постојката останува со NULL зона наместо да се брише
    CONSTRAINT FK_STOPS_ZONE FOREIGN KEY (ZONE_ID) REFERENCES ZONE(ZONE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    CONSTRAINT FK_STOPS_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE 
);

CREATE TABLE ROUTE (
    ROUTE_ID            VARCHAR(50)     NOT NULL,
    AGENCY_ID           INTEGER         NOT NULL,
    START_STOP_ID       VARCHAR(50),
	START_STOP_VT 		INTEGER,
    END_STOP_ID         VARCHAR(50),
	END_STOP_VT 		INTEGER,
    ROUTE_NAME          VARCHAR(100)    NOT NULL    DEFAULT 'Unnamed Route',
    DISTANCE_KM         DECIMAL(8,2),
    ESTIMATED_DURATION  INTEGER,
    VEHICLE_TYPE_ID     INTEGER,

    CONSTRAINT PK_ROUTE PRIMARY KEY (ROUTE_ID), 

    -- не може да се избрише агенција додека има активни линии под неа
    CONSTRAINT FK_ROUTE_AGENCY FOREIGN KEY (AGENCY_ID) REFERENCES AGENCY(AGENCY_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- ако се избрише почетната постојка, линијата останува со NULL почеток
    CONSTRAINT FK_ROUTE_STARTSTOP FOREIGN KEY (START_STOP_ID, START_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    -- ако се избрише крајната постојка, линијата останува со NULL крај
    CONSTRAINT FK_ROUTE_ENDSTOP FOREIGN KEY (END_STOP_ID, END_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE,

    -- ако се избрише типот на возило, линијата останува без тип (NULL)
    CONSTRAINT FK_ROUTE_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE 
);

CREATE TABLE ROUTE_STOP (
    ROUTE_STOP_ID       SERIAL          NOT NULL,
    ROUTE_ID            VARCHAR(50)     NOT NULL,
    STOP_ID             VARCHAR(50)     NOT NULL,
	STOP_VT 			INTEGER			NOT NULL,
    STOP_SEQUENCE       INTEGER         NOT NULL,
    DISTANCE_TRAVELED   DECIMAL(8,2),
    TIMEPOINT           INTEGER                     DEFAULT 1,

    CONSTRAINT PK_ROUTE_STOP PRIMARY KEY (ROUTE_STOP_ID),

    -- ако се избрише линијата, сите нејзини постојки во редоследот се бришат автоматски
    CONSTRAINT FK_ROUTESTOP_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
        ON DELETE CASCADE ON UPDATE CASCADE, 

    -- не може да се избрише постојка која е дел од некоја линија
    CONSTRAINT FK_ROUTESTOP_STOP FOREIGN KEY (STOP_ID, STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    CONSTRAINT CHK_ROUTESTOP_SEQ       CHECK (STOP_SEQUENCE >= 0),        
    CONSTRAINT CHK_ROUTESTOP_TIMEPOINT CHECK (TIMEPOINT IN (0, 1)),        
    CONSTRAINT CHK_ROUTESTOP_DIST      CHECK (DISTANCE_TRAVELED IS NULL OR DISTANCE_TRAVELED >= 0) 
);

CREATE TABLE SHAPE (
    SHAPE_ID        VARCHAR(50)     NOT NULL,
    DESCRIPTION     VARCHAR(100),
    VEHICLE_TYPE_ID INTEGER,

    CONSTRAINT PK_SHAPE PRIMARY KEY (SHAPE_ID), 

    -- ако се избрише типот на возило, shape останува без тип (NULL)
    CONSTRAINT FK_SHAPE_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE 
);

CREATE TABLE SHAPE_POINT (
    SHAPE_ID            VARCHAR(50)     NOT NULL,
    SHAPE_PT_SEQUENCE   INTEGER         NOT NULL,
    SHAPE_PT_LAT        DECIMAL(10,7)   NOT NULL,
    SHAPE_PT_LON        DECIMAL(10,7)   NOT NULL,
    SHAPE_DIST_TRAVELED DECIMAL(8,2),
    VEHICLE_TYPE_ID     INTEGER,

    CONSTRAINT PK_SHAPE_POINT PRIMARY KEY (SHAPE_ID, SHAPE_PT_SEQUENCE), 

    -- ако се избрише shape-от, сите негови точки се бришат автоматски
    CONSTRAINT FK_SHAPEPOINT_SHAPE FOREIGN KEY (SHAPE_ID) REFERENCES SHAPE(SHAPE_ID)
        ON DELETE CASCADE ON UPDATE CASCADE, 

     -- ако се избрише типот на возило, точката останува без тип (NULL)    
    CONSTRAINT FK_SHAPEPOINT_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE VEHICLE (
    VEHICLE_ID          VARCHAR(50)     NOT NULL,
    VEHICLE_TYPE_ID     INTEGER         NOT NULL,
    REGISTRATION_NUMBER VARCHAR(30)     NOT NULL,
    MODEL               VARCHAR(100)                DEFAULT 'Unknown Model',
    MANUFACTURER        VARCHAR(100)                DEFAULT 'Unknown Manufacturer',
    CAPACITY            INTEGER,
    YEAR_OF_MANUFACTURE INTEGER,
    STATUS              VARCHAR(20)                 DEFAULT 'ACTIVE',

    CONSTRAINT PK_VEHICLE PRIMARY KEY (VEHICLE_ID), 

    -- не може да се избрише тип на возило додека постојат возила од тој тип
    CONSTRAINT FK_VEHICLE_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    CONSTRAINT CHK_VEHICLE_STATUS CHECK (
        STATUS IS NULL OR STATUS IN ('ACTIVE', 'INACTIVE', 'MAINTENANCE', 'DECOMMISSIONED')
    ),

    CONSTRAINT CHK_VEHICLE_CAPACITY CHECK (CAPACITY IS NULL OR CAPACITY > 0), 

    CONSTRAINT CHK_VEHICLE_REGNO CHECK (
        REGISTRATION_NUMBER ~ '^[A-Z0-9\-\s]{2,20}$'
    )
);

CREATE TABLE EMPLOYEE (
    EMPLOYEE_ID     VARCHAR(50)     NOT NULL,
    FIRST_NAME      VARCHAR(50)     NOT NULL    DEFAULT 'Unknown',
    LAST_NAME       VARCHAR(50)     NOT NULL    DEFAULT 'Unknown',
    PHONE           VARCHAR(30),
    EMAIL           VARCHAR(100),
    HIRE_DATE       DATE                        DEFAULT CURRENT_DATE,
    POSITION        VARCHAR(50),
    STATUS          VARCHAR(20)                 DEFAULT 'ACTIVE',
    PASSWORD_HASH   VARCHAR(255),

    CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID), 

    CONSTRAINT CHK_EMP_EMAIL CHECK (
        EMAIL IS NULL OR
        EMAIL ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'
    ), 

    CONSTRAINT CHK_EMP_PHONE CHECK (
        PHONE IS NULL OR
        PHONE ~ '^\+?[0-9\s\(\)\-]{7,20}$'
    ),

    CONSTRAINT CHK_EMP_STATUS CHECK (
        STATUS IS NULL OR STATUS IN ('ACTIVE', 'INACTIVE', 'ON_LEAVE', 'TERMINATED')
    ),

    CONSTRAINT CHK_EMP_HIREDATE CHECK (
        HIRE_DATE IS NULL OR HIRE_DATE <= CURRENT_DATE
    )
);

CREATE TABLE DISCOUNT_OFFICER (
    OFFICER_ID          VARCHAR(50)     NOT NULL,
    EMPLOYEE_ID         VARCHAR(50)     NOT NULL,
    ASSIGNED_REGION     VARCHAR(100)                DEFAULT 'General',
    CERTIFICATION_CODE  VARCHAR(50),
    AUTHORIZED_FROM     DATE                        DEFAULT CURRENT_DATE,
    AUTHORIZED_TO       DATE,

    CONSTRAINT PK_DISCOUNT_OFFICER PRIMARY KEY (OFFICER_ID), 

    -- не може да се избрише вработен кој е активен службеник за попусти
    CONSTRAINT FK_OFFICER_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

     -- датумот на почеток на овластување мора да биде пред датумот на истекување
    CONSTRAINT CHK_OFFICER_DATES CHECK (
        AUTHORIZED_FROM IS NULL OR AUTHORIZED_TO IS NULL OR AUTHORIZED_FROM <= AUTHORIZED_TO
    )
);

CREATE TABLE DISCOUNT_OFFICER_DISCOUNT (
    ID            SERIAL          NOT NULL,
    OFFICER_ID    VARCHAR(50)     NOT NULL,
    DISCOUNT_ID   INTEGER         NOT NULL,
    ASSIGNED_DATE DATE                        DEFAULT CURRENT_DATE,
    NOTES         TEXT,

    CONSTRAINT PK_OFFICER_DISCOUNT PRIMARY KEY (ID), 

    -- ако се избрише службеникот, неговите доделувања на попусти се бришат автоматски
    CONSTRAINT FK_OD_OFFICER FOREIGN KEY (OFFICER_ID) REFERENCES DISCOUNT_OFFICER(OFFICER_ID)
        ON DELETE CASCADE ON UPDATE CASCADE, 

    -- не може да се избрише попуст кој сеуште е доделен на службеници
    CONSTRAINT FK_OD_DISCOUNT FOREIGN KEY (DISCOUNT_ID) REFERENCES DISCOUNT(DISCOUNT_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE 
);

CREATE TABLE DRIVER (
    DRIVER_ID           VARCHAR(50)     NOT NULL,
    EMPLOYEE_ID         VARCHAR(50)     NOT NULL,
    LICENSE_NUMBER      VARCHAR(50)     NOT NULL,
    LICENSE_CATEGORY    VARCHAR(10)                 DEFAULT 'D',
    LICENSE_EXPIRY_DATE DATE,
    YEARS_EXPERIENCE    INTEGER                     DEFAULT 0,

    CONSTRAINT PK_DRIVER PRIMARY KEY (DRIVER_ID), 

    CONSTRAINT FK_DRIVER_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, -- не може да се избрише вработен кој е регистриран возач

    CONSTRAINT CHK_DRIVER_EXPERIENCE CHECK (
        YEARS_EXPERIENCE IS NULL OR YEARS_EXPERIENCE >= 0
    ), 

    CONSTRAINT CHK_DRIVER_LICENSE CHECK (
        LICENSE_NUMBER ~ '^[A-Z0-9\-]{3,30}$'
    ) 
);

CREATE TABLE CONDUCTOR (
    CONDUCTOR_ID         VARCHAR(50)     NOT NULL,
    EMPLOYEE_ID          VARCHAR(50)     NOT NULL,
    CERTIFICATION_NUMBER VARCHAR(50),
    HIRE_ROLE_DATE       DATE                        DEFAULT CURRENT_DATE,

    CONSTRAINT PK_CONDUCTOR PRIMARY KEY (CONDUCTOR_ID), 

    -- не може да се избрише вработен кој е регистриран кондуктер
    CONSTRAINT FK_CONDUCTOR_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE 
);

CREATE TABLE SHIFT (
    SHIFT_ID    SERIAL          NOT NULL,
    SHIFT_NAME  VARCHAR(50)     NOT NULL    DEFAULT 'Regular Shift',
    START_TIME  TIME            NOT NULL,
    END_TIME    TIME            NOT NULL,
    DESCRIPTION TEXT,

    CONSTRAINT PK_SHIFT PRIMARY KEY (SHIFT_ID) 
);

CREATE TABLE TRIP (
    TRIP_ID                VARCHAR(50)     NOT NULL,
    ROUTE_ID               VARCHAR(50)     NOT NULL,
    VEHICLE_ID             VARCHAR(50)     NOT NULL,
    DRIVER_ID              VARCHAR(50)     NOT NULL,
    SHAPE_ID               VARCHAR(50),
    TRIP_HEADSIGN          VARCHAR(100),
    START_TIME             TIME,
    END_TIME               TIME,
    DIRECTION_ID           INTEGER,
    WHEELCHAIR_ACCESSIBLE  INTEGER,
    TRIP_DATE              DATE                        DEFAULT CURRENT_DATE,
    STATUS                 VARCHAR(20)                 DEFAULT 'SCHEDULED',
    VEHICLE_TYPE_ID        INTEGER,

    CONSTRAINT PK_TRIP PRIMARY KEY (TRIP_ID),

    -- не може да се избрише линија додека има возења на неа
    CONSTRAINT FK_TRIP_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- не може да се избрише возило кое е користено во возење
    CONSTRAINT FK_TRIP_VEHICLE FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE(VEHICLE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- не може да се избрише возач кој е доделен на возење
    CONSTRAINT FK_TRIP_DRIVER FOREIGN KEY (DRIVER_ID) REFERENCES DRIVER(DRIVER_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- ако се избрише shape-от, возењето останува без геометрија (NULL)
    CONSTRAINT FK_TRIP_SHAPE FOREIGN KEY (SHAPE_ID) REFERENCES SHAPE(SHAPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    CONSTRAINT CHK_TRIP_STATUS CHECK (
        STATUS IS NULL OR
        STATUS IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED', 'DELAYED')
    ),

     -- ако се избрише типот на возило, возењето останува без тип (NULL)
    CONSTRAINT FK_TRIP_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE STOP_TIME (
    STOP_TIME_ID        VARCHAR(50)     NOT NULL,
    TRIP_ID             VARCHAR(50)     NOT NULL,
    STOP_ID             VARCHAR(50)     NOT NULL,
	STOP_VT				INTEGER,
    ROUTE_STOP_ID       INTEGER,
    ARRIVAL_TIME        TIME,
    DEPARTURE_TIME      TIME,
    STOP_SEQUENCE       INTEGER,
    SHAPE_DIST_TRAVELED DECIMAL(8,2),
    VEHICLE_TYPE_ID     INTEGER,

    CONSTRAINT PK_STOP_TIME PRIMARY KEY (STOP_TIME_ID), 

    -- ако се избрише возењето, сите времиња по постојки се бришат автоматски
    CONSTRAINT FK_STOPTIME_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
        ON DELETE CASCADE ON UPDATE CASCADE, 
	
    -- не може да се избрише постојка која е дел од активно возење
    CONSTRAINT FK_STOPTIME_STOP FOREIGN KEY (STOP_ID, STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- ако route_stop се избрише, записот останува со NULL (не се губи историјата)
    CONSTRAINT FK_STOPTIME_ROUTESTOP FOREIGN KEY (ROUTE_STOP_ID) REFERENCES ROUTE_STOP(ROUTE_STOP_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    CONSTRAINT CHK_STOPTIME_SEQ CHECK (STOP_SEQUENCE IS NULL OR STOP_SEQUENCE >= 0), 

    -- ако се избрише типот на возило, записот останува без тип (NULL)
    CONSTRAINT FK_STOPTIME_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE 
);

CREATE TABLE TRANSFER (
    TRANSFER_ID         SERIAL          NOT NULL,
    FROM_STOP_ID        VARCHAR(50)     NOT NULL,
	FROM_STOP_VT		INTEGER 		NOT NULL,
    TO_STOP_ID          VARCHAR(50)     NOT NULL,
	TO_STOP_VT 			INTEGER 		NOT NULL,
    FROM_ROUTE_ID       VARCHAR(50),
    TO_ROUTE_ID         VARCHAR(50),
    VEHICLE_TYPE_ID     INTEGER,

    CONSTRAINT PK_TRANSFER PRIMARY KEY (TRANSFER_ID), 

    -- не може да се избрише почетна постојка на трансфер
    CONSTRAINT FK_TRANSFER_FROMSTOP FOREIGN KEY (FROM_STOP_ID, FROM_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- не може да се избрише крајна постојка на трансфер
    CONSTRAINT FK_TRANSFER_TOSTOP FOREIGN KEY (TO_STOP_ID, TO_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

   	-- ако се избрише линијата, трансферот станува генерален (без конкретна линија)
    CONSTRAINT FK_TRANSFER_FROMROUTE FOREIGN KEY (FROM_ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    -- ако се избрише линијата, трансферот станува генерален (без конкретна линија)
    CONSTRAINT FK_TRANSFER_TOROUTE FOREIGN KEY (TO_ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    -- ако се избрише типот на возило, трансферот останува без тип (NULL)
    CONSTRAINT FK_TRANSFER_VEHICLETYPE FOREIGN KEY (VEHICLE_TYPE_ID) REFERENCES VEHICLE_TYPE(VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE 
);

CREATE TABLE DELAY_LOG (
    DELAY_ID        SERIAL          NOT NULL,
    TRIP_ID         VARCHAR(50)     NOT NULL,
    STOP_ID         VARCHAR(50),
	STOP_VT 		INTEGER,
    DELAY_MINUTES   INTEGER,
    DELAY_REASON    TEXT,
    REPORTED_TIME   TIMESTAMP                   DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT PK_DELAY_LOG PRIMARY KEY (DELAY_ID), 

    -- ако се избрише возењето, сите записи за доцнење се бришат автоматски
    CONSTRAINT FK_DELAYLOG_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
        ON DELETE CASCADE ON UPDATE CASCADE, 

    -- ако се избрише постојката, записот за доцнење останува без референца на постојка
    CONSTRAINT FK_DELAYLOG_STOP FOREIGN KEY (STOP_ID, STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    CONSTRAINT CHK_DELAY_MINUTES CHECK (
        DELAY_MINUTES IS NULL OR DELAY_MINUTES >= 0
    ) 
);

CREATE TABLE CAPACITY_LOG (
    CAPACITY_LOG_ID SERIAL          NOT NULL,
    TRIP_ID         VARCHAR(50)     NOT NULL,
    VEHICLE_ID      VARCHAR(50)     NOT NULL,
    STOP_ID         VARCHAR(50),
	STOP_VT			INTEGER,
    PASSENGER_COUNT INTEGER,
    RECORDED_TIME   TIMESTAMP                   DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT PK_CAPACITY_LOG PRIMARY KEY (CAPACITY_LOG_ID), 

    -- ако се избрише возењето, сите логови за капацитет се бришат автоматски
    CONSTRAINT FK_CAPACITYLOG_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
        ON DELETE CASCADE ON UPDATE CASCADE, 

    -- не може да се избрише возило додека постојат логови за него
    CONSTRAINT FK_CAPACITYLOG_VEHICLE FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE(VEHICLE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- ако се избрише постојката, логот останува без референца на постојка
    CONSTRAINT FK_CAPACITYLOG_STOP FOREIGN KEY (STOP_ID, STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    CONSTRAINT CHK_CAPLOG_COUNT CHECK (
        PASSENGER_COUNT IS NULL OR PASSENGER_COUNT >= 0
    )
);

CREATE TABLE VEHICLE_LOG (
    VEHICLE_LOG_ID          SERIAL          NOT NULL,
    VEHICLE_ID              VARCHAR(50)     NOT NULL,
    TRIP_ID                 VARCHAR(50)     NOT NULL,
    LATITUDE                DECIMAL(10,7)   NOT NULL,
    LONGITUDE               DECIMAL(10,7)   NOT NULL,
    SPEED_KMH               DECIMAL(6,2),
    HEADING                 DECIMAL(5,2),
    RECORDED_AT             TIMESTAMP       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    OCCUPANCY_STATUS        VARCHAR(30),
    CONGESTION_LEVEL        VARCHAR(20),
    CURRENT_STOP_ID         VARCHAR(50),
	CURRENT_STOP_VT 		INTEGER,
    NEXT_STOP_ID            VARCHAR(50),
	NEXT_STOP_VT			INTEGER,
    DISTANCE_TO_NEXT_STOP_M DECIMAL(8,2),

    CONSTRAINT PK_VEHICLE_LOG PRIMARY KEY (VEHICLE_LOG_ID), 

     -- не може да се избрише возило додека постојат GPS логови за него
    CONSTRAINT FK_VEHICLELOG_VEHICLE FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE(VEHICLE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    -- ако се избрише возењето, сите GPS логови се бришат автоматски
    CONSTRAINT FK_VEHICLELOG_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
        ON DELETE CASCADE ON UPDATE CASCADE, 

    -- ако се избрише тековната постојка, логот останува со NULL
    CONSTRAINT FK_VEHICLELOG_CURRENTSTOP FOREIGN KEY (CURRENT_STOP_ID,CURRENT_STOP_VT) REFERENCES STOPS(STOP_ID,VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    -- ако се избрише следната постојка, логот останува со NULL
    CONSTRAINT FK_VEHICLELOG_NEXTSTOP FOREIGN KEY (NEXT_STOP_ID,NEXT_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    CONSTRAINT CHK_VLOG_OCCUPANCY CHECK (
        OCCUPANCY_STATUS IS NULL OR OCCUPANCY_STATUS IN (
            'EMPTY', 'MANY_SEATS_AVAILABLE', 'FEW_SEATS_AVAILABLE',
            'STANDING_ROOM_ONLY', 'CRUSHED_STANDING_ONLY',
            'FULL', 'NOT_ACCEPTING_PASSENGERS'
        )
    ),

    CONSTRAINT CHK_VLOG_CONGESTION CHECK (
        CONGESTION_LEVEL IS NULL OR
        CONGESTION_LEVEL IN ('RUNNING_SMOOTHLY', 'STOP_AND_GO', 'CONGESTION', 'SEVERE_CONGESTION')
    )
);

CREATE TABLE FUEL_LOG (
    FUEL_LOG_ID SERIAL          NOT NULL,
    VEHICLE_ID  VARCHAR(50)     NOT NULL,
    FUEL_AMOUNT DECIMAL(8,2),
    FUEL_COST   DECIMAL(10,2),
    FUEL_DATE   DATE                        DEFAULT CURRENT_DATE,
    MILEAGE     DECIMAL(10,2),

    CONSTRAINT PK_FUEL_LOG PRIMARY KEY (FUEL_LOG_ID), 

    -- не може да се избрише возило додека постојат записи за гориво
    CONSTRAINT FK_FUELLOG_VEHICLE FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE(VEHICLE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    CONSTRAINT CHK_FUELLOG_AMOUNT  CHECK (FUEL_AMOUNT IS NULL OR FUEL_AMOUNT > 0),  
    CONSTRAINT CHK_FUELLOG_COST    CHECK (FUEL_COST   IS NULL OR FUEL_COST   > 0),  
    CONSTRAINT CHK_FUELLOG_MILEAGE CHECK (MILEAGE     IS NULL OR MILEAGE    >= 0)   
);

CREATE TABLE DRIVER_SCHEDULE (
    DRIVER_SCHEDULE_ID  SERIAL          NOT NULL,
    DRIVER_ID           VARCHAR(50)     NOT NULL,
    TRIP_ID             VARCHAR(50)     NOT NULL,
    SHIFT_ID            INTEGER         NOT NULL,
    SHIFT_START         TIMESTAMP,
    SHIFT_END           TIMESTAMP,
    SCHEDULE_DATE       DATE                        DEFAULT CURRENT_DATE,

    CONSTRAINT PK_DRIVER_SCHEDULE PRIMARY KEY (DRIVER_SCHEDULE_ID), 

    -- не може да се избрише возач кој има закажани возења
    CONSTRAINT FK_DRIVERSCHED_DRIVER FOREIGN KEY (DRIVER_ID) REFERENCES DRIVER(DRIVER_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- ако се избрише возењето, записот во распоредот се брише автоматски
    CONSTRAINT FK_DRIVERSCHED_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
        ON DELETE CASCADE ON UPDATE CASCADE, 

     -- не може да се избрише смена која е во употреба во распоред
    CONSTRAINT FK_DRIVERSCHED_SHIFT FOREIGN KEY (SHIFT_ID) REFERENCES SHIFT(SHIFT_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT CHK_DRVSCHED_TIMES CHECK (
        SHIFT_START IS NULL OR SHIFT_END IS NULL OR SHIFT_START <= SHIFT_END
    )
);

CREATE TABLE CONDUCTOR_SCHEDULE (
    CONDUCTOR_SCHEDULE_ID   SERIAL          NOT NULL,
    CONDUCTOR_ID            VARCHAR(50)     NOT NULL,
    TRIP_ID                 VARCHAR(50)     NOT NULL,
    SHIFT_ID                INTEGER         NOT NULL,
    SHIFT_START             TIMESTAMP,
    SHIFT_END               TIMESTAMP,
    SCHEDULE_DATE           DATE                        DEFAULT CURRENT_DATE,

    CONSTRAINT PK_CONDUCTOR_SCHEDULE PRIMARY KEY (CONDUCTOR_SCHEDULE_ID), 

    -- не може да се избрише кондуктер кој има закажани возења
    CONSTRAINT FK_CONDSCHED_CONDUCTOR FOREIGN KEY (CONDUCTOR_ID) REFERENCES CONDUCTOR(CONDUCTOR_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    -- ако се избрише возењето, записот во распоредот се брише автоматски
    CONSTRAINT FK_CONDSCHED_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
        ON DELETE CASCADE ON UPDATE CASCADE, 

    -- не може да се избрише смена која е во употреба во распоред
    CONSTRAINT FK_CONDSCHED_SHIFT FOREIGN KEY (SHIFT_ID) REFERENCES SHIFT(SHIFT_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 
        
    CONSTRAINT CHK_CONDSCHED_TIMES CHECK (
        SHIFT_START IS NULL OR SHIFT_END IS NULL OR SHIFT_START <= SHIFT_END
    ) 
);

CREATE TABLE PASSENGER (
    PASSENGER_ID        VARCHAR(50)     NOT NULL,
    FIRST_NAME          VARCHAR(50)     NOT NULL    DEFAULT 'Unknown',
    LAST_NAME           VARCHAR(50)     NOT NULL    DEFAULT 'Unknown',
    EMAIL               VARCHAR(100),
    PHONE               VARCHAR(30),
    DATE_OF_BIRTH       DATE,
    REGISTRATION_DATE   DATE                        DEFAULT CURRENT_DATE,
    PASSENGER_TYPE      VARCHAR(20)                 DEFAULT 'REGULAR',
    PASSWORD_HASH       VARCHAR(255),

    CONSTRAINT PK_PASSENGER PRIMARY KEY (PASSENGER_ID), 

    CONSTRAINT CHK_PASS_EMAIL CHECK (
        EMAIL IS NULL OR
        EMAIL ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'
    ), 

    CONSTRAINT CHK_PASS_PHONE CHECK (
        PHONE IS NULL OR
        PHONE ~ '^\+?[0-9\s\(\)\-]{7,20}$'
    ), 

    CONSTRAINT CHK_PASS_TYPE CHECK (
        PASSENGER_TYPE IS NULL OR
        PASSENGER_TYPE IN ('REGULAR', 'STUDENT', 'SENIOR', 'DISABLED', 'CHILD')
    ), 

    CONSTRAINT CHK_PASS_DOB CHECK (
        DATE_OF_BIRTH IS NULL OR DATE_OF_BIRTH <= CURRENT_DATE
    ),

    CONSTRAINT CHK_PASS_REGDATE CHECK (
        REGISTRATION_DATE IS NULL OR REGISTRATION_DATE <= CURRENT_DATE
    ) 
);

CREATE TABLE TICKET_TYPE (
    TICKET_TYPE_ID      SERIAL          NOT NULL,
    NAME                VARCHAR(50)     NOT NULL    DEFAULT 'Standard Ticket',
    DESCRIPTION         TEXT,
    VALIDITY_DURATION   INTEGER,
    BASE_PRICE          DECIMAL(8,2),

    CONSTRAINT PK_TICKET_TYPE PRIMARY KEY (TICKET_TYPE_ID), 

    CONSTRAINT CHK_TT_VALIDITY CHECK (VALIDITY_DURATION IS NULL OR VALIDITY_DURATION > 0), 
    CONSTRAINT CHK_TT_PRICE    CHECK (BASE_PRICE IS NULL OR BASE_PRICE >= 0)            
);

CREATE TABLE FARE_RULE (
    FARE_RULE_ID    SERIAL          NOT NULL,
    ZONE_ID         VARCHAR(50)     NOT NULL,
    TICKET_TYPE_ID  INTEGER         NOT NULL,
    AGENCY_ID       INTEGER         NOT NULL,
    ROUTE_ID        VARCHAR(50),
    PRICE           DECIMAL(8,2)    NOT NULL    DEFAULT 0.00,
    VALID_FROM      DATE                        DEFAULT CURRENT_DATE,
    VALID_TO        DATE,

    CONSTRAINT PK_FARE_RULE PRIMARY KEY (FARE_RULE_ID),

    -- не може да се избрише зона со активни тарифни правила
    CONSTRAINT FK_FARERULE_ZONE FOREIGN KEY (ZONE_ID) REFERENCES ZONE(ZONE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- не може да се избрише тип на билет со активни тарифни правила
    CONSTRAINT FK_FARERULE_TICKETTYPE FOREIGN KEY (TICKET_TYPE_ID) REFERENCES TICKET_TYPE(TICKET_TYPE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    -- не може да се избрише агенција со активни тарифни правила
    CONSTRAINT FK_FARERULE_AGENCY FOREIGN KEY (AGENCY_ID) REFERENCES AGENCY(AGENCY_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

     -- ако се избрише линијата, правилото станува глобално (важи за сите линии)
    CONSTRAINT FK_FARERULE_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE(ROUTE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT CHK_FARERULE_PRICE CHECK (PRICE >= 0),

    CONSTRAINT CHK_FARERULE_DATES CHECK (
        VALID_FROM IS NULL OR VALID_TO IS NULL OR VALID_FROM <= VALID_TO
    ) 
);

CREATE TABLE TICKET (
    TICKET_ID       VARCHAR(50)     NOT NULL,
    PASSENGER_ID    VARCHAR(50)     NOT NULL,
    TRIP_ID         VARCHAR(50)     NOT NULL,
    TICKET_TYPE_ID  INTEGER         NOT NULL,
    FARE_RULE_ID    INTEGER,
    DISCOUNT_ID     INTEGER,
    START_STOP_ID   VARCHAR(50),
	START_STOP_VT   INTEGER,
    END_STOP_ID     VARCHAR(50),
	END_STOP_VT     INTEGER,
    PURCHASE_DATE   DATE                        DEFAULT CURRENT_DATE,
    PRICE           DECIMAL(8,2),
    STATUS          VARCHAR(20)                 DEFAULT 'VALID',

    CONSTRAINT PK_TICKET PRIMARY KEY (TICKET_ID), 

    -- не може да се избрише патник кој има купени билети
    CONSTRAINT FK_TICKET_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    -- билетот е финансиски запис, возењето не може да се избрише
    CONSTRAINT FK_TICKET_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- не може да се избрише тип на билет додека постојат билети од тој тип
    CONSTRAINT FK_TICKET_TICKETTYPE FOREIGN KEY (TICKET_TYPE_ID) REFERENCES TICKET_TYPE(TICKET_TYPE_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,
 
    -- ако се избрише тарифното правило, билетот ја задржува веќе пресметаната цена
    CONSTRAINT FK_TICKET_FARERULE FOREIGN KEY (FARE_RULE_ID) REFERENCES FARE_RULE(FARE_RULE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE,

    -- ако се избрише попустот, билетот ја задржува веќе пресметаната цена
    CONSTRAINT FK_TICKET_DISCOUNT FOREIGN KEY (DISCOUNT_ID) REFERENCES DISCOUNT(DISCOUNT_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    -- ако се избрише почетната постојка, билетот останува со NULL
    CONSTRAINT FK_TICKET_STARTSTOP FOREIGN KEY (START_STOP_ID, START_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE,

    -- ако се избрише крајната постојка, билетот останува со NULL
    CONSTRAINT FK_TICKET_ENDSTOP FOREIGN KEY (END_STOP_ID, END_STOP_VT) REFERENCES STOPS(STOP_ID, VEHICLE_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    CONSTRAINT CHK_TICKET_STATUS CHECK (
        STATUS IS NULL OR STATUS IN ('VALID', 'USED', 'EXPIRED', 'CANCELLED', 'REFUNDED')
    ), 

    CONSTRAINT CHK_TICKET_PRICE CHECK (PRICE IS NULL OR PRICE >= 0) 
);

CREATE TABLE PAYMENT (
    PAYMENT_ID              VARCHAR(50)     NOT NULL,
    TICKET_ID               VARCHAR(50)     NOT NULL,
    PASSENGER_ID            VARCHAR(50)     NOT NULL,
    AMOUNT                  DECIMAL(10,2)   NOT NULL,
    PAYMENT_METHOD          VARCHAR(20)                 DEFAULT 'CASH',
    PAYMENT_DATE            TIMESTAMP                   DEFAULT CURRENT_TIMESTAMP,
    PAYMENT_STATUS          VARCHAR(20)                 DEFAULT 'PENDING',
    TRANSACTION_REFERENCE   VARCHAR(100),

    CONSTRAINT PK_PAYMENT PRIMARY KEY (PAYMENT_ID), 

    -- не може да се избрише билет кој има поврзано плаќање
    CONSTRAINT FK_PAYMENT_TICKET FOREIGN KEY (TICKET_ID) REFERENCES TICKET(TICKET_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- не може да се избрише патник кој има плаќања
    CONSTRAINT FK_PAYMENT_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT CHK_PAYMENT_AMOUNT CHECK (AMOUNT > 0),

    CONSTRAINT CHK_PAYMENT_METHOD CHECK (
        PAYMENT_METHOD IS NULL OR
        PAYMENT_METHOD IN ('CASH', 'CARD', 'CONTACTLESS', 'MOBILE', 'ONLINE', 'VOUCHER')
    ),

    CONSTRAINT CHK_PAYMENT_STATUS CHECK (
        PAYMENT_STATUS IS NULL OR
        PAYMENT_STATUS IN ('PENDING', 'COMPLETED', 'FAILED', 'REFUNDED', 'CANCELLED')
    ) 
);

CREATE TABLE SUBSCRIPTION_PASS (
    PASS_ID         VARCHAR(50)     NOT NULL,
    PASSENGER_ID    VARCHAR(50)     NOT NULL,
    TICKET_TYPE_ID  INTEGER,
    ZONE_ID         VARCHAR(50),
    PASS_TYPE       VARCHAR(50)                 DEFAULT 'MONTHLY',
    START_DATE      DATE                        DEFAULT CURRENT_DATE,
    END_DATE        DATE,
    PRICE           DECIMAL(8,2),
    STATUS          VARCHAR(20)                 DEFAULT 'ACTIVE',

    CONSTRAINT PK_SUBSCRIPTION_PASS PRIMARY KEY (PASS_ID),

    -- не може да се избрише патник со активна претплатна карта
    CONSTRAINT FK_PASS_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- ако се избрише типот на билет, картата останува непроменета
    CONSTRAINT FK_PASS_TICKETTYPE FOREIGN KEY (TICKET_TYPE_ID) REFERENCES TICKET_TYPE(TICKET_TYPE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 
        
    -- ако се избрише зоната, картата станува неограничена по зони
    CONSTRAINT FK_PASS_ZONE FOREIGN KEY (ZONE_ID) REFERENCES ZONE(ZONE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    CONSTRAINT CHK_PASS_STATUS CHECK (
        STATUS IS NULL OR STATUS IN ('ACTIVE', 'EXPIRED', 'SUSPENDED', 'CANCELLED')
    ),

    CONSTRAINT CHK_PASS_TYPE CHECK (
        PASS_TYPE IS NULL OR
        PASS_TYPE IN ('DAILY', 'WEEKLY', 'MONTHLY', 'ANNUAL', 'STUDENT', 'SENIOR')
    ), 

    CONSTRAINT CHK_PASS_DATES CHECK (
        START_DATE IS NULL OR END_DATE IS NULL OR START_DATE <= END_DATE
    ), 

    CONSTRAINT CHK_PASS_PRICE CHECK (PRICE IS NULL OR PRICE >= 0) 
);

CREATE TABLE NOTIFICATION (
    NOTIFICATION_ID     SERIAL          NOT NULL,
    PASSENGER_ID        VARCHAR(50)     NOT NULL,
    TRIP_ID             VARCHAR(50),
    DELAY_LOG_ID        INTEGER,
    MESSAGE             TEXT,
    NOTIFICATION_TYPE   VARCHAR(50)                 DEFAULT 'GENERAL',
    SENT_TIME           TIMESTAMP                   DEFAULT CURRENT_TIMESTAMP,
    STATUS              VARCHAR(20)                 DEFAULT 'PENDING',

    CONSTRAINT PK_NOTIFICATION PRIMARY KEY (NOTIFICATION_ID),

    -- не може да се избрише патник кој има известувања
    CONSTRAINT FK_NOTIFICATION_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- ако се избрише возењето, известувањето станува општо (без конкретно возење)
    CONSTRAINT FK_NOTIFICATION_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    -- ако се избрише записот за доцнење, известувањето останува без референца
    CONSTRAINT FK_NOTIFICATION_DELAYLOG FOREIGN KEY (DELAY_LOG_ID) REFERENCES DELAY_LOG(DELAY_ID)
        ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT CHK_NOTIF_TYPE CHECK (
        NOTIFICATION_TYPE IS NULL OR
        NOTIFICATION_TYPE IN ('GENERAL', 'DELAY', 'CANCELLATION', 'PROMO', 'REMINDER', 'ALERT')
    ), 

    CONSTRAINT CHK_NOTIF_STATUS CHECK (
        STATUS IS NULL OR STATUS IN ('PENDING', 'SENT', 'FAILED', 'READ')
    )
);

CREATE TABLE PASSENGER_DISCOUNT (
    PASSENGER_DISCOUNT_ID   SERIAL          NOT NULL,
    PASSENGER_ID            VARCHAR(50)     NOT NULL,
    DISCOUNT_ID             INTEGER         NOT NULL,
    OFFICER_ID              VARCHAR(50),
    ASSIGNED_DATE           DATE                        DEFAULT CURRENT_DATE,
    EXPIRY_DATE             DATE,
    STATUS                  VARCHAR(20)                 DEFAULT 'ACTIVE',

    CONSTRAINT PK_PASSENGER_DISCOUNT PRIMARY KEY (PASSENGER_DISCOUNT_ID), 

    -- не може да се избрише патник кој има доделени попусти
    CONSTRAINT FK_PD_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    -- не може да се избрише попуст кој е доделен на патници
    CONSTRAINT FK_PD_DISCOUNT FOREIGN KEY (DISCOUNT_ID) REFERENCES DISCOUNT(DISCOUNT_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 

    -- ако се избрише службеникот, доделувањето останува евидентирано без службеник
    CONSTRAINT FK_PD_OFFICER FOREIGN KEY (OFFICER_ID) REFERENCES DISCOUNT_OFFICER(OFFICER_ID)
        ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT CHK_PD_STATUS CHECK (
        STATUS IS NULL OR STATUS IN ('ACTIVE', 'EXPIRED', 'REVOKED', 'PENDING')
    ), 

    CONSTRAINT CHK_PD_DATES CHECK (
        ASSIGNED_DATE IS NULL OR EXPIRY_DATE IS NULL OR ASSIGNED_DATE <= EXPIRY_DATE
    )
);

CREATE TABLE COMPLAINT (
    COMPLAINT_ID        SERIAL          NOT NULL,
    PASSENGER_ID        VARCHAR(50)     NOT NULL,
    TRIP_ID             VARCHAR(50)     NOT NULL,
    EMPLOYEE_ID         VARCHAR(50),
    COMPLAINT_TEXT      TEXT,
    COMPLAINT_DATE      DATE                        DEFAULT CURRENT_DATE,
    STATUS              VARCHAR(20)                 DEFAULT 'OPEN',
    RESOLUTION_NOTES    TEXT,

    CONSTRAINT PK_COMPLAINT PRIMARY KEY (COMPLAINT_ID),
	
	-- не може да се избрише патник кој има поднесено поплаки
    CONSTRAINT FK_COMPLAINT_PASSENGER FOREIGN KEY (PASSENGER_ID) REFERENCES PASSENGER(PASSENGER_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE, 
	
	-- не може да се избрише возење кое има поплаки (правна евиденција)
    CONSTRAINT FK_COMPLAINT_TRIP FOREIGN KEY (TRIP_ID) REFERENCES TRIP(TRIP_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

	-- ако се избрише вработениот, поплаката останува недоделена (NULL)
    CONSTRAINT FK_COMPLAINT_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
        ON DELETE SET NULL ON UPDATE CASCADE, 

    CONSTRAINT CHK_COMPLAINT_STATUS CHECK (
        STATUS IS NULL OR
        STATUS IN ('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED', 'REJECTED')
    ) 
);