DROP SCHEMA car_dealership CASCADE;
CREATE SCHEMA car_dealership;


-- ══════════════════════════════════════════════════
-- LEVEL 0 — DOMAINS
-- ══════════════════════════════════════════════════
SET search_path TO car_dealership;

CREATE DOMAIN email_type AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN phone_type AS VARCHAR(20)
    CHECK (VALUE ~ '^\+?[0-9]{7,15}$');

CREATE DOMAIN year_limits as INT
    CHECK (VALUE BETWEEN 1995 AND EXTRACT(YEAR FROM CURRENT_DATE));

CREATE DOMAIN price_range AS NUMERIC(10, 2)
    CHECK (VALUE >= 0);

-- ══════════════════════════════════════════════════
-- LEVEL 1 — NO DEPENDENCIES
-- ══════════════════════════════════════════════════
CREATE TABLE Brand
(
    id    SERIAL PRIMARY KEY,
    brand VARCHAR(255) NOT NULL
);

CREATE TABLE EngineType
(
    id   SERIAL PRIMARY KEY,
    type VARCHAR(255) NOT NULL DEFAULT 'Petrol'
        CHECK (type IN ('Petrol', 'Diesel', 'Electric', 'Hybrid'))
);

CREATE TABLE Engine
(
    engineNumber VARCHAR(20) PRIMARY KEY,
    horsepower   INT NOT NULL CHECK (horsepower BETWEEN 50 AND 730),
    engineTypeId INT NOT NULL,

    CONSTRAINT fk_engine_engineType FOREIGN KEY (engineTypeId)
        REFERENCES EngineType (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE VehicleType
(
    id   SERIAL PRIMARY KEY,
    type VARCHAR(255) NOT NULL
);

CREATE TABLE Status
(
    id     SERIAL PRIMARY KEY,
    status VARCHAR(255) NOT NULL CHECK (status IN
                                        ('In Stock', 'Ordered', 'In Production', 'Reserved', 'Sold',
                                         'In Transit'))
);

CREATE TABLE Factory
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE EquipmentType
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE Customer
(
    id         SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name  VARCHAR(255) NOT NULL,
    email      email_type   NOT NULL UNIQUE,
    phone      phone_type   NOT NULL UNIQUE,
    created_at DATE         NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE Employee
(
    id         SERIAL PRIMARY KEY,
    position   VARCHAR(255)              NOT NULL CHECK (position IN ('Salesperson', 'Manager', 'Finance', 'Admin')),
    first_name VARCHAR(255)              NOT NULL,
    last_name  VARCHAR(255)              NOT NULL,
    email      car_dealership.email_type NOT NULL UNIQUE,
    phone      car_dealership.phone_type NOT NULL UNIQUE,
    hire_date  DATE                      NOT NULL DEFAULT CURRENT_DATE,
    manager_id INT                                DEFAULT NULL,

    CONSTRAINT fk_employee_manager FOREIGN KEY (manager_id)
        REFERENCES Employee (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- ══════════════════════════════════════════════════
-- LEVEL 2
-- ══════════════════════════════════════════════════

CREATE TABLE Model
(
    id       SERIAL PRIMARY KEY,
    model    VARCHAR(255) NOT NULL,
    brand_id INT          NOT NULL,
    year     year_limits  NOT NULL,

    CONSTRAINT fk_model_brand FOREIGN KEY (brand_id)
        REFERENCES Brand (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE EquipmentPackage
(
    id                SERIAL PRIMARY KEY,
    name              VARCHAR(255) NOT NULL UNIQUE,
    price             price_range  NOT NULL,
    description       VARCHAR(255) NOT NULL,
    is_default        BOOLEAN      NOT NULL DEFAULT FALSE,
    equipment_type_id INT          NOT NULL,

    CONSTRAINT fk_equipmentPackage_equipmentType FOREIGN KEY (equipment_type_id)
        REFERENCES EquipmentType (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- ══════════════════════════════════════════════════
-- LEVEL 3
-- ══════════════════════════════════════════════════

CREATE TABLE Address
(
    id               SERIAL PRIMARY KEY,
    country          VARCHAR(255) NOT NULL,
    city             VARCHAR(255) NOT NULL,
    postal_code      INT          NOT NULL,
    street           VARCHAR(255) NOT NULL,
    building_number  INT          NOT NULL,
    entry_number     INT,
    apartment_number INT,
    customer_id      INT,
    factory_id       INT,

    CONSTRAINT fk_address_customer FOREIGN KEY (customer_id)
        REFERENCES Customer (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT fk_address_factory FOREIGN KEY (factory_id)
        REFERENCES Factory (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT chk_address_owner
        CHECK ((customer_id IS NOT NULL AND factory_id IS NULL) or (customer_id IS NULL AND factory_id IS NOT NULL))
);

CREATE TABLE Vehicle
(
    vin             VARCHAR(17) PRIMARY KEY,
    model_id        INT         NOT NULL,
    status_id       INT         NOT NULL,
    vehicle_type_id INT         NOT NULL,
    color           VARCHAR(255) DEFAULT 'Silver',
    price           price_range NOT NULL,
    production_year year_limits NOT NULL,
    engine_number   VARCHAR(20) NOT NULL UNIQUE,

    CONSTRAINT fk_vehicle_model FOREIGN KEY (model_id)
        REFERENCES Model (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_vehicle_status FOREIGN KEY (status_id)
        REFERENCES Status (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_vehicle_vehicleType FOREIGN KEY (vehicle_type_id)
        REFERENCES VehicleType (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_vehicle_engine FOREIGN KEY (engine_number)
        REFERENCES ENGINE (engineNumber)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

CREATE TABLE PackageElement
(
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    price      price_range  NOT NULL,
    package_id INT          NOT NULL,

    CONSTRAINT fk_packageElement_equipmentPackage FOREIGN KEY (package_id)
        REFERENCES EquipmentPackage (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- ══════════════════════════════════════════════════
-- LEVEL 4
-- ══════════════════════════════════════════════════

CREATE TABLE Production
(
    id         SERIAL PRIMARY KEY,
    factory_id INT          NOT NULL,
    vin        VARCHAR(17)  NOT NULL UNIQUE,
    status     VARCHAR(255) NOT NULL DEFAULT 'Scheduled' CHECK (status IN
                                                                ('Scheduled', 'In Progress', 'Quality Check',
                                                                 'Completed')),

    CONSTRAINT fk_production_factory FOREIGN KEY (factory_id)
        REFERENCES Factory (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_production_vehicle FOREIGN KEY (vin)
        REFERENCES Vehicle (vin)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

CREATE TABLE Configuration
(
    id          SERIAL PRIMARY KEY,
    vin         VARCHAR(17)  NOT NULL,
    description VARCHAR(255) NOT NULL,
    total_price price_range  NOT NULL,
    created_at  DATE         NOT NULL DEFAULT CURRENT_DATE,
    customer_id INT                   DEFAULT NULL,

    CONSTRAINT fk_configuration_vehicle FOREIGN KEY (vin)
        REFERENCES Vehicle (vin)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    CONSTRAINT fk_configuration_customer FOREIGN KEY (customer_id)
        REFERENCES Customer (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE TestDrive
(
    id          SERIAL PRIMARY KEY,
    date        DATE        NOT NULL DEFAULT CURRENT_DATE,
    time_start  TIMESTAMP   NOT NULL,
    time_end    TIMESTAMP   NOT NULL,
    customer_id INT         NOT NULL,
    vin         VARCHAR(17) NOT NULL,
    result      VARCHAR(255) CHECK (result IN ('Interested', 'Not Interested', 'Follow-Up')),

    CONSTRAINT fk_testDrive_customer FOREIGN KEY (customer_id)
        REFERENCES Customer (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_testDrive_vehicle FOREIGN KEY (vin)
        REFERENCES Vehicle (vin)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    CONSTRAINT chk_testDrive_times CHECK (time_end > time_start)
);



-- ══════════════════════════════════════════════════
-- LEVEL 5 — DEPEND ON LEVEL 4
-- ══════════════════════════════════════════════════

CREATE TABLE ConfigurationPackage
(
    id               SERIAL PRIMARY KEY,
    configuration_id INT NOT NULL,
    package_id       INT NOT NULL,

    CONSTRAINT fk_confPack_conf FOREIGN KEY (configuration_id)
        REFERENCES Configuration (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_confPack_pack FOREIGN KEY (package_id)
        REFERENCES EquipmentPackage (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT unique_confPack UNIQUE (configuration_id, package_id)
);

CREATE TABLE "Order"
(
    id               SERIAL PRIMARY KEY,
    customer_id      INT          NOT NULL,
    configuration_id INT          NOT NULL,
    employee_id      INT          NOT NULL,
    date             DATE         NOT NULL DEFAULT CURRENT_DATE,
    status           VARCHAR(255) NOT NULL DEFAULT 'Pending' CHECK
        (status IN ('Pending', 'Confirmed', 'Canceled', 'Completed')),

    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
        REFERENCES Customer (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_order_configuration FOREIGN KEY (configuration_id)
        REFERENCES Configuration (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_order_employee FOREIGN KEY (employee_id)
        REFERENCES Employee (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- ══════════════════════════════════════════════════
-- LEVEL 6 — DEPEND ON LEVEL 5
-- ══════════════════════════════════════════════════

CREATE TABLE ConfigurationElement
(
    id                       SERIAL PRIMARY KEY,
    package_element_id       INT NOT NULL,
    configuration_package_id INT NOT NULL,

    CONSTRAINT fk_confEl_packEl FOREIGN KEY (package_element_id)
        REFERENCES PackageElement (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_confEl_confPack FOREIGN KEY (configuration_package_id)
        REFERENCES ConfigurationPackage (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT unique_confEl UNIQUE (package_element_id, configuration_package_id)
);

CREATE TABLE Contract
(
    id          SERIAL PRIMARY KEY,
    employee_id INT          NOT NULL,
    notes       VARCHAR(255),
    date        DATE         NOT NULL DEFAULT CURRENT_DATE,
    type        VARCHAR(255) NOT NULL CHECK (type IN ('Standard', 'Finance', 'Fleet')),
    order_id    INT          NOT NULL UNIQUE,
    customer_id INT          NOT NULL,
    vin         VARCHAR(17)  NOT NULL UNIQUE,

    CONSTRAINT fk_contract_employee FOREIGN KEY (employee_id)
        REFERENCES Employee (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_contract_order FOREIGN KEY (order_id)
        REFERENCES "Order" (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_contract_customer FOREIGN KEY (customer_id)
        REFERENCES Customer (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_contract_vehicle FOREIGN KEY (vin)
        REFERENCES Vehicle (vin)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

-- ══════════════════════════════════════════════════
-- LEVEL 7 — DEPEND ON LEVEL 6
-- ══════════════════════════════════════════════════

CREATE TABLE Sale
(
    id          SERIAL PRIMARY KEY,
    date        DATE NOT NULL DEFAULT CURRENT_DATE,
    employee_id INT  NOT NULL,
    contract_id INT  NOT NULL UNIQUE,
    customer_id INT  NOT NULL,

    CONSTRAINT fk_sale_employee FOREIGN KEY (employee_id)
        REFERENCES Employee (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_sale_contract FOREIGN KEY (contract_id)
        REFERENCES Contract (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_sale_customer FOREIGN KEY (customer_id)
        REFERENCES Customer (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- ══════════════════════════════════════════════════
-- LEVEL 8 — DEPEND ON LEVEL 7
-- ══════════════════════════════════════════════════

CREATE TABLE Payment
(
    id      SERIAL PRIMARY KEY,
    type    VARCHAR(255) NOT NULL CHECK (type IN ('Cash', 'Installment', 'Leasing')),
    amount  price_range  NOT NULL,
    sale_id INT          NOT NULL UNIQUE,

    CONSTRAINT fk_payment_sale FOREIGN KEY (sale_id)
        REFERENCES Sale (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE

);

-- ══════════════════════════════════════════════════
-- LEVEL 9 — DEPEND ON LEVEL 8
-- ══════════════════════════════════════════════════

CREATE TABLE Discount
(
    id          SERIAL PRIMARY KEY,
    percentage  SMALLINT NOT NULL CHECK (percentage BETWEEN 1 AND 100),
    payment_id  INT      NOT NULL,
    employee_id INT      NOT NULL,

    CONSTRAINT fk_discount_payment FOREIGN KEY (payment_id)
        REFERENCES Payment (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_discount_employee FOREIGN KEY (employee_id)
        REFERENCES Employee (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);