create_db.sql

CREATE DATABASE IF NOT EXISTS real_estate;

USE real_estate;

-- ROLE_OF_USER
CREATE TABLE role_of_user (
    role_id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE
);

-- USER
CREATE TABLE user (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) UNIQUE,
    password_hash VARCHAR(500) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    birth_date DATE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20),
    role_id INT,
    avatar_url VARCHAR(1000),
    rating DECIMAL(3,2),

    CONSTRAINT chk_user_status
        CHECK (status IN ('ACTIVE', 'BLOCKED', 'DELETED')),

    CONSTRAINT chk_user_rating
        CHECK (rating BETWEEN 0 AND 5),

    CONSTRAINT fk_user_role
        FOREIGN KEY (role_id)
        REFERENCES role_of_user(role_id)
);


-- SUBSCRIPTION
CREATE TABLE subscription (
    subscription_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    search_query TEXT NOT NULL,

    CONSTRAINT fk_subscription_user
        FOREIGN KEY (user_id)
        REFERENCES user(user_id)
);

-- COUNTRY
CREATE TABLE country (
    country_id INT AUTO_INCREMENT PRIMARY KEY,
    country_name VARCHAR(50) NOT NULL UNIQUE
);

-- CITY
CREATE TABLE city (
    city_id INT AUTO_INCREMENT PRIMARY KEY,
    city_name VARCHAR(50) NOT NULL UNIQUE
);

-- DISTRICT
CREATE TABLE district (
    district_id INT AUTO_INCREMENT PRIMARY KEY,
    district_name VARCHAR(300) NOT NULL UNIQUE
);

-- METRO
CREATE TABLE metro (
    metro_id INT AUTO_INCREMENT PRIMARY KEY,
    station VARCHAR(50) NOT NULL UNIQUE
);

-- TYPE_REAL_ESTATE
CREATE TABLE type_real_estate (
    property_type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL UNIQUE
);

-- DEVELOPER
CREATE TABLE developer (
    developer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    inn VARCHAR(12) NOT NULL UNIQUE,
    website VARCHAR(500)
);

-- RESIDENTIAL_COMPLEX
CREATE TABLE residential_complex (
    residential_complex_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    developer_id INT,
    description TEXT,

    CONSTRAINT fk_residential_complex_developer
        FOREIGN KEY (developer_id)
        REFERENCES developer(developer_id)
);

-- OBJECT_REAL_ESTATE
CREATE TABLE object_real_estate (
    property_id INT AUTO_INCREMENT PRIMARY KEY,
    property_type_id INT NOT NULL,
    owner_id INT NOT NULL,
    residential_complex_id INT,
    cadastral_number VARCHAR(50) UNIQUE,
    area_total DECIMAL(10,2) NOT NULL,
    area_living DECIMAL(10,2),
    area_kitchen DECIMAL(10,2),
    ceiling_height DECIMAL(4,2),
    built_year INT,
    floor INT,
    floors_total INT,
    rooms_count INT,
    description TEXT,

    CONSTRAINT chk_area_total
        CHECK (area_total > 0),

    CONSTRAINT fk_object_type
        FOREIGN KEY (property_type_id)
        REFERENCES type_real_estate(property_type_id),

    CONSTRAINT fk_object_owner
        FOREIGN KEY (owner_id)
        REFERENCES user(user_id),

    CONSTRAINT fk_object_residential_complex
        FOREIGN KEY (residential_complex_id)
        REFERENCES residential_complex(residential_complex_id)
);

-- ADDRESS
CREATE TABLE address (
    address_id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL UNIQUE,
    country_id INT NOT NULL,
    city_id INT NOT NULL,
    district_id INT,
    street VARCHAR(255) NOT NULL,
    house_number VARCHAR(20) NOT NULL,
    apartment VARCHAR(20),
    latitude DECIMAL(10,7),
    longitude DECIMAL(10,7),

    CONSTRAINT fk_address_property
        FOREIGN KEY (property_id)
        REFERENCES object_real_estate(property_id),

    CONSTRAINT fk_address_country
        FOREIGN KEY (country_id)
        REFERENCES country(country_id),

    CONSTRAINT fk_address_city
        FOREIGN KEY (city_id)
        REFERENCES city(city_id),

    CONSTRAINT fk_address_district
        FOREIGN KEY (district_id)
        REFERENCES district(district_id)
);

-- INFRASTRUCTURE
CREATE TABLE infrastructure (
    infrastructure_id INT AUTO_INCREMENT PRIMARY KEY,
    infrastructure_name VARCHAR(100) NOT NULL UNIQUE
);

-- AMENITY
CREATE TABLE amenity (
    amenity_id INT AUTO_INCREMENT PRIMARY KEY,
    amenity_name VARCHAR(100) NOT NULL UNIQUE
);

-- DEAL_TYPE
CREATE TABLE deal_type (
    deal_type_id INT AUTO_INCREMENT PRIMARY KEY,
    deal_name VARCHAR(50) NOT NULL UNIQUE
);

-- TARIFF
CREATE TABLE tariff (
    tariff_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    duration_days INT NOT NULL,

    CONSTRAINT chk_tariff_price
        CHECK (price >= 0),

    CONSTRAINT chk_tariff_duration
        CHECK (duration_days > 0)
);

-- LISTING
CREATE TABLE listing (
    listing_id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL,
    author_id INT NOT NULL,
    deal_type_id INT NOT NULL,
    tariff_id INT,
    price DECIMAL(15,2) NOT NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    status VARCHAR(20),
    publication_date TIMESTAMP NOT NULL,
    expiration_tariff_date TIMESTAMP NULL,
    expiration_date TIMESTAMP NOT NULL,
    views_count INT DEFAULT 0,

    CONSTRAINT chk_listing_price
        CHECK (price > 0),

    CONSTRAINT chk_listing_status
        CHECK (status IN ('DRAFT', 'ACTIVE', 'ARCHIVED', 'BLOCKED')),

    CONSTRAINT fk_listing_property
        FOREIGN KEY (property_id)
        REFERENCES object_real_estate(property_id),

    CONSTRAINT fk_listing_author
        FOREIGN KEY (author_id)
        REFERENCES user(user_id),

    CONSTRAINT fk_listing_deal_type
        FOREIGN KEY (deal_type_id)
        REFERENCES deal_type(deal_type_id),

    CONSTRAINT fk_listing_tariff
        FOREIGN KEY (tariff_id)
        REFERENCES tariff(tariff_id)
);

-- FAVORITE
CREATE TABLE favorite (
    favorite_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    listing_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_favorite_user
        FOREIGN KEY (user_id)
        REFERENCES user(user_id),

    CONSTRAINT fk_favorite_listing
        FOREIGN KEY (listing_id)
        REFERENCES listing(listing_id)
);

-- VIEW LISTING
CREATE TABLE view_listing (
    view_id INT AUTO_INCREMENT PRIMARY KEY,
    listing_id INT NOT NULL,
    user_id INT,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_view_listing
        FOREIGN KEY (listing_id)
        REFERENCES listing(listing_id),

    CONSTRAINT fk_view_user
        FOREIGN KEY (user_id)
        REFERENCES user(user_id)
);

-- REVIEW
CREATE TABLE review (
    review_id INT AUTO_INCREMENT PRIMARY KEY,
    author_user_id INT NOT NULL,
    target_user_id INT NOT NULL,
    rating INT NOT NULL,
    review_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_review_rating
        CHECK (rating BETWEEN 1 AND 5),

    CONSTRAINT fk_review_author
        FOREIGN KEY (author_user_id)
        REFERENCES user(user_id),

    CONSTRAINT fk_review_target
        FOREIGN KEY (target_user_id)
        REFERENCES user(user_id)
);

-- PHOTO
CREATE TABLE photo (
    photo_id INT AUTO_INCREMENT PRIMARY KEY,
    listing_id INT NOT NULL,
    file_url VARCHAR(1000) NOT NULL,
    sort_order INT DEFAULT 0,
    is_main BOOLEAN DEFAULT FALSE,

    CONSTRAINT fk_photo_listing
        FOREIGN KEY (listing_id)
        REFERENCES listing(listing_id)
);

-- PRICE_HISTORY
CREATE TABLE price_history (
    price_history_id INT AUTO_INCREMENT PRIMARY KEY,
    listing_id INT NOT NULL,
    old_price DECIMAL(15,2) NOT NULL,
    new_price DECIMAL(15,2) NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_price_history_listing
        FOREIGN KEY (listing_id)
        REFERENCES listing(listing_id)
);

-- PAYMENT
CREATE TABLE payment (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    listing_id INT NOT NULL,
    tariff_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    payment_status VARCHAR(20),

    CONSTRAINT chk_payment_amount
        CHECK (amount >= 0),

    CONSTRAINT chk_payment_status
        CHECK (payment_status IN ('PENDING', 'PAID', 'FAILED')),

    CONSTRAINT fk_payment_user
        FOREIGN KEY (user_id)
        REFERENCES user(user_id),

    CONSTRAINT fk_payment_listing
        FOREIGN KEY (listing_id)
        REFERENCES listing(listing_id),

    CONSTRAINT fk_payment_tariff
        FOREIGN KEY (tariff_id)
        REFERENCES tariff(tariff_id)
);

-- CHAT
CREATE TABLE chat (
    chat_id INT AUTO_INCREMENT PRIMARY KEY,
    listing_id INT NOT NULL,
    user1_id INT NOT NULL,
    user2_id INT NOT NULL,

    CONSTRAINT fk_chat_listing
        FOREIGN KEY (listing_id)
        REFERENCES listing(listing_id),

    CONSTRAINT fk_chat_user1
        FOREIGN KEY (user1_id)
        REFERENCES user(user_id),

    CONSTRAINT fk_chat_user2
        FOREIGN KEY (user2_id)
        REFERENCES user(user_id)
);

-- MESSAGE
CREATE TABLE message (
    message_id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id INT NOT NULL,
    sender_id INT NOT NULL,
    message_text TEXT NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_read BOOLEAN DEFAULT FALSE,

    CONSTRAINT fk_message_chat
        FOREIGN KEY (chat_id)
        REFERENCES chat(chat_id),

    CONSTRAINT fk_message_sender
        FOREIGN KEY (sender_id)
        REFERENCES user(user_id)
);

-- M2M_ADDRESS_METRO
CREATE TABLE m2m_address_metro (
    address_id INT NOT NULL,
    metro_id INT NOT NULL,
    distance_minutes INT,

    PRIMARY KEY (address_id, metro_id),

    CONSTRAINT chk_distance_minutes
        CHECK (distance_minutes >= 0),

    CONSTRAINT fk_m2m_address
        FOREIGN KEY (address_id)
        REFERENCES address(address_id),

    CONSTRAINT fk_m2m_metro
        FOREIGN KEY (metro_id)
        REFERENCES metro(metro_id)
);

-- M2M_OBJECT_AMENITY
CREATE TABLE m2m_object_amenity (
    property_id INT NOT NULL,
    amenity_id INT NOT NULL,

    PRIMARY KEY (property_id, amenity_id),

    CONSTRAINT fk_m2m_object_amenity_property
        FOREIGN KEY (property_id)
        REFERENCES object_real_estate(property_id),

    CONSTRAINT fk_m2m_object_amenity_amenity
        FOREIGN KEY (amenity_id)
        REFERENCES amenity(amenity_id)
);

-- M2M_OBJECT_INFRASTRUCTURE
CREATE TABLE m2m_object_infrastructure (
    property_id INT NOT NULL,
    infrastructure_id INT NOT NULL,
    distance_meters INT,

    PRIMARY KEY (property_id, infrastructure_id),

    CONSTRAINT chk_distance_meters
        CHECK (distance_meters >= 0),

    CONSTRAINT fk_m2m_object_infrastructure_property
        FOREIGN KEY (property_id)
        REFERENCES object_real_estate(property_id),

    CONSTRAINT fk_m2m_object_infrastructure_infrastructure
        FOREIGN KEY (infrastructure_id)
        REFERENCES infrastructure(infrastructure_id)
);