CREATE TABLE IF NOT EXISTS Payment (
    PAYMENT_ID      INTEGER NOT NULL UNIQUE,
    P_DATE          DATE NOT NULL,
    P_TYPE          VARCHAR(25) NOT NULL,
    AMOUNT          DECIMAL(7,2) NOT NULL,
    HRS_WORKED      DECIMAL(2,1) NOT NULL,
    DESCRIPTION     VARCHAR(255) NOT NULL,
    PRIMARY KEY (PAYMENT_ID)
);

CREATE TABLE IF NOT EXISTS Employee (
    EMPLOYEE_ID     INTEGER NOT NULL UNIQUE,
    PAYMENT_ID      INTEGER NOT NULL,
    FIRSTNAME       VARCHAR(25) NOT NULL,
    LASTNAME        VARCHAR(25) NOT NULL,
    ADDRESS         VARCHAR(100) NOT NULL,
    PHONE           INTEGER NOT NULL,
    SALARY          DECIMAL (7,2) NOT NULL,
    PRIMARY KEY (EMPLOYEE_ID),
    CONSTRAINT fk_Employee_Payment FOREIGN KEY (PAYMENT_ID) 
                                REFERENCES Payment(PAYMENT_ID)
                                ON DELETE CASCADE
                                ON UPDATE CASCADE
);


CREATE TABLE IF NOT EXISTS Tool (
    TOOL_ID         INTEGER NOT NULL UNIQUE,
    REPAIR_ID       INTEGER NOT NULL UNIQUE,
    BRAND           VARCHAR(50) NOT NULL,
    T_TYPE          VARCHAR(50) NOT NULL,
    DESCRIPTION     VARCHAR(255) NOT NULL,
    PRIMARY KEY (TOOL_ID),
    CONSTRAINT fk_Tool_Repair FOREIGN KEY (REPAIR_ID) 
                            REFERENCES Tool(REPAIR_ID)
                            ON DELETE CASCADE
                            ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS Repair (
    REPAIR_ID       INTEGER NOT NULL UNIQUE,
    SUPPLIER        VARCHAR(100) NOT NULL,
    R_DATE          DATE NOT NULL,
    COST            DECIMAL(7,2) NOT NULL,
    DESCRIPTION     VARCHAR(255) NOT NULL,
    PRIMARY KEY (REPAIR_ID)
);

CREATE TABLE IF NOT EXISTS Car (
    CAR_ID          INTEGER NOT NULL UNIQUE,
    TOOL_ID         INTEGER NOT NULL UNIQUE,
    YEAR            INTEGER NOT NULL,
    MAKE            VARCHAR(25) NOT NULL,
    MODEL           VARCHAR(25) NOT NULL,
    START_DATE      DATE NOT NULL,
    END_DATE        DATE NOT NULL,
    PRIMARY KEY (CAR_ID),
    CONSTRAINT fk_Car_Tool FOREIGN KEY (TOOL_ID) 
                            REFERENCES Tool(TOOL_ID)
                            ON DELETE CASCADE
                            ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS Route (
    ROUTE_ID        INTEGER NOT NULL UNIQUE,
    CAR_ID          INTEGER NOT NULL UNIQUE,
    START_DATE      DATE NOT NULL,
    END_DATE        DATE NOT NULL,
    PRIMARY KEY (ROUTE_ID),
    CONSTRAINT fk_Route_Car FOREIGN KEY (CAR_ID) 
                            REFERENCES Car(CAR_ID)
                            ON DELETE CASCADE
                            ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS Assign_To (
    CAR_ID          INTEGER NOT NULL UNIQUE,
    EMPLOYEE_ID     INTEGER NOT NULL UNIQUE,
    START_DATE      DATE NOT NULL,
    END_DATE        DATE NOT NULL,
    CONSTRAINT fk_Assign_Car FOREIGN KEY (CAR_ID) 
                            REFERENCES Car(CAR_ID)
                            ON DELETE CASCADE
                            ON UPDATE CASCADE,
    CONSTRAINT fk_Assign_Employee FOREIGN KEY (EMPLOYEE_ID) 
                            REFERENCES Employee(EMPLOYEE_ID)
                            ON DELETE CASCADE
                            ON UPDATE CASCADE
);


CREATE TABLE IF NOT EXISTS Additional_Service (
    SERVICE_ID      INTEGER NOT NULL UNIQUE,
    PRICE           DECIMAL(7,2) NOT NULL,
    S_DATE          DATE NOT NULL,
    DESCRIPTION     VARCHAR(255) NOT NULL,
    PRIMARY KEY (SERVICE_ID)
);

CREATE TABLE IF NOT EXISTS Client (
    CLIENT_ID       INTEGER NOT NULL UNIQUE,
    FIRSTNAME       VARCHAR(25) NOT NULL,
    LASTNAME        VARCHAR(25) NOT NULL,
    PHONE           INTEGER NOT NULL,
    PRIMARY KEY (CLIENT_ID)
);

CREATE TABLE IF NOT EXISTS Income (
    INCOME_ID       INTEGER NOT NULL UNIQUE,
    I_DATE          DATE NOT NULL,
    I_TYPE          VARCHAR(30) NOT NULL,
    AMOUNT          DECIMAL(7,2) NOT NULL,
    DESCRIPTION     VARCHAR(255) NOT NULL,
    PRIMARY KEY (INCOME_ID)
);

CREATE TABLE IF NOT EXISTS Contract (
    CONTRACT_ID     INTEGER NOT NULL UNIQUE,
    INCOME_ID       INTEGER NOT NULL UNIQUE,
    FEE             DECIMAL(7,2) NOT NULL,
    DESCRIPTION     VARCHAR(250) NOT NULL,
    START_DATE      DATE NOT NULL,
    END_DATE        DATE NOT NULL,
    PRIMARY KEY (CONTRACT_ID),
    CONSTRAINT fk_Contract_Income FOREIGN KEY (INCOME_ID) 
                            REFERENCES Income(INCOME_ID)
                            ON DELETE CASCADE
                            ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS House (
    HOUSE_ID        INTEGER NOT NULL UNIQUE,
    SERVICE_ID      INTEGER NOT NULL,
    CLIENT_ID       INTEGER NOT NULL,
    CONTRACT_ID     INTEGER NOT NULL,
    H_NUMBER        VARCHAR(10) NOT NULL,
    STREET_NAME     VARCHAR(30) NOT NULL,
    CITY            VARCHAR(30) NOT NULL,
    ZIP_CODE        VARCHAR(10) NOT NULL,
    PRIMARY KEY (HOUSE_ID),
    CONSTRAINT fk_House_Service FOREIGN KEY (SERVICE_ID) 
                                REFERENCES Additional_Service(SERVICE_ID)
                                ON DELETE CASCADE
                                ON UPDATE CASCADE,
    CONSTRAINT fk_House_Client FOREIGN KEY (CLIENT_ID) 
                                REFERENCES Client(CLIENT_ID)
                                ON DELETE CASCADE
                                ON UPDATE CASCADE,
    CONSTRAINT fk_House_Contract FOREIGN KEY (CONTRACT_ID) 
                                REFERENCES Contract(CONTRACT_ID)
                                ON DELETE CASCADE
                                ON UPDATE CASCADE
);
