RelationlDesign: kreiranje_na_tabeli_verz2.sql

DROP TABLE IF EXISTS MessageD;
DROP TABLE IF EXISTS DocumentD;
DROP TABLE IF EXISTS Inspection;
DROP TABLE IF EXISTS MaintenanceLog;
DROP TABLE IF EXISTS ServiceRequest;
DROP TABLE IF EXISTS Payment;
DROP TABLE IF EXISTS Lease;
DROP TABLE IF EXISTS Listing;
DROP TABLE IF EXISTS UnitImage;
DROP TABLE IF EXISTS PropertyImage;
DROP TABLE IF EXISTS Unit;
DROP TABLE IF EXISTS Property;
DROP TABLE IF EXISTS LandlordProfile;
DROP TABLE IF EXISTS TenantProfile;
DROP TABLE IF EXISTS UserD;
DROP TABLE IF EXISTS Address;
DROP TABLE IF EXISTS PropertyType;
DROP TABLE IF EXISTS ServiceCategory;
DROP TABLE IF EXISTS PaymentMethod;

drop schema if exists domify cascade;
        
create schema domify;

SET search_path TO domify;

CREATE TABLE Address (
    id BIGINT PRIMARY KEY,
    street VARCHAR(255) NOT NULL,
    number VARCHAR(50) NOT NULL,
    municipality VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL
);

CREATE TABLE PropertyType (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE ServiceCategory (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE PaymentMethod (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE UserD (
    id BIGINT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    rating DECIMAL(3,2) NOT NULL DEFAULT 0.00,
    bio TEXT,
    address_id BIGINT NOT NULL,
    FOREIGN KEY (address_id) REFERENCES Address(id) ON DELETE RESTRICT
);

CREATE TABLE TenantProfile (
    id BIGINT PRIMARY KEY,
    FOREIGN KEY (id) REFERENCES UserD(id) ON DELETE CASCADE
);

CREATE TABLE LandlordProfile (
    id BIGINT PRIMARY KEY,
    managed_properties_count INT NOT NULL DEFAULT 0,
    is_agent BOOL NOT NULL,
    FOREIGN KEY (id) REFERENCES UserD(id) ON DELETE CASCADE
);


CREATE TABLE Property (
    id BIGINT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    owner_id BIGINT NOT NULL,
    property_type_id BIGINT NOT NULL,
    address_id BIGINT NOT NULL,
    FOREIGN KEY (owner_id) REFERENCES UserD(id) ON DELETE RESTRICT,
    FOREIGN KEY (property_type_id) REFERENCES PropertyType(id) ON DELETE RESTRICT,
    FOREIGN KEY (address_id) REFERENCES Address(id) ON DELETE RESTRICT
);

CREATE TABLE Unit (
    id BIGINT PRIMARY KEY,
    unit_number VARCHAR(50) NOT NULL,
    floor INT NOT NULL,
    bedrooms INT NOT NULL,
    bathrooms INT NOT NULL,
    area_sq_m DECIMAL(8,2) NOT NULL,
    rent_amount DECIMAL(10,2),
    property_id BIGINT NOT NULL,
    FOREIGN KEY (property_id) REFERENCES Property(id) ON DELETE CASCADE
);

CREATE TABLE PropertyImage (
    id BIGINT PRIMARY KEY,
    image VARCHAR(500) NOT NULL,
    property_id BIGINT NOT NULL,
    FOREIGN KEY (property_id) REFERENCES Property(id) ON DELETE CASCADE
);

CREATE TABLE Listing (
    id BIGINT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    available_from DATE NOT NULL,
    available_to DATE NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'available',
    description TEXT,
    unit_id BIGINT NOT NULL,
    FOREIGN KEY (unit_id) REFERENCES Unit(id) ON DELETE CASCADE
);

CREATE TABLE Lease (
    id BIGINT PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    rent_amount DECIMAL(10,2) NOT NULL,
    deposit_amount DECIMAL(10,2) NOT NULL,
    listing_id BIGINT NOT NULL,
    tenant_id BIGINT NOT NULL,
    landlord_id BIGINT NOT NULL,
    FOREIGN KEY (listing_id) REFERENCES Listing(id) ON DELETE RESTRICT,
    FOREIGN KEY (tenant_id) REFERENCES TenantProfile(id) ON DELETE RESTRICT,
    FOREIGN KEY (landlord_id) REFERENCES LandlordProfile(id) ON DELETE RESTRICT
);

CREATE TABLE Payment (
    id BIGINT PRIMARY KEY,
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) NOT NULL,
    payment_date DATE NOT NULL,
    lease_id BIGINT NOT NULL,
    payment_method_id BIGINT NOT NULL,
    FOREIGN KEY (lease_id) REFERENCES Lease(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_method_id) REFERENCES PaymentMethod(id) ON DELETE RESTRICT
);

CREATE TABLE ServiceRequest (
    id BIGINT PRIMARY KEY,
    description TEXT NOT NULL,
    request_date DATE NOT NULL,
    status VARCHAR(50) NOT NULL,
    lease_id BIGINT NOT NULL,
    service_category_id BIGINT NOT NULL,
    FOREIGN KEY (lease_id) REFERENCES Lease(id) ON DELETE CASCADE,
    FOREIGN KEY (service_category_id) REFERENCES ServiceCategory(id) ON DELETE RESTRICT
);

CREATE TABLE MaintenanceLog (
    id BIGINT PRIMARY KEY,
    description TEXT NOT NULL,
    maintenance_date DATE NOT NULL,
    service_request_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    FOREIGN KEY (service_request_id) REFERENCES ServiceRequest(id) ON DELETE CASCADE,
 FOREIGN KEY (user_id) REFERENCES UserD(id) ON DELETE CASCADE
);

CREATE TABLE Inspection (
    id BIGINT PRIMARY KEY,
    inspection_date DATE NOT NULL,
    notes TEXT NOT NULL,
    lease_id BIGINT NOT NULL,
        landlord_id BIGINT NOT NULL,
    FOREIGN KEY (lease_id) REFERENCES Lease(id) ON DELETE CASCADE,
    FOREIGN KEY (landlord_id) REFERENCES LandlordProfile(id) ON DELETE RESTRICT
);

CREATE TABLE DocumentD (
    id BIGINT PRIMARY KEY,
    file_type VARCHAR(50) NOT NULL,
    file_url VARCHAR(500) NOT NULL,
    uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id BIGINT,
    lease_id BIGINT,
    FOREIGN KEY (user_id) REFERENCES UserD(id) ON DELETE CASCADE,
    FOREIGN KEY (lease_id) REFERENCES Lease(id) ON DELETE CASCADE,
    CHECK (user_id IS NOT NULL OR lease_id IS NOT NULL)
);

CREATE TABLE MessageD (
    id BIGINT PRIMARY KEY,
    content TEXT NOT NULL,
    sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    from_user_id BIGINT NOT NULL,
    to_user_id BIGINT NOT NULL,
    lease_id BIGINT NOT NULL,
    FOREIGN KEY (from_user_id) REFERENCES UserD(id) ON DELETE CASCADE,
    FOREIGN KEY (to_user_id) REFERENCES UserD(id) ON DELETE CASCADE,
    FOREIGN KEY (lease_id) REFERENCES Lease(id) ON DELETE CASCADE
);

CREATE TABLE UnitImage (
    id BIGINT PRIMARY KEY,
    image VARCHAR(500) NOT NULL,
    unit_id BIGINT NOT NULL,
    FOREIGN KEY (unit_id) REFERENCES Unit(id) ON DELETE CASCADE
);

CREATE TABLE Interested (
    listing_id BIGINT NOT NULL,
    tenant_profile_id BIGINT NOT NULL,
    PRIMARY KEY (listing_id, tenant_profile_id),
    FOREIGN KEY (listing_id) REFERENCES Listing(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_profile_id) REFERENCES TenantProfile(id) ON DELETE CASCADE
);