RelationalDesign: schema_creation_v3.sql

DROP SCHEMA IF EXISTS project CASCADE;
CREATE SCHEMA project;
SET search_path TO project;

CREATE TABLE admin (
    admin_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    name VARCHAR(128) NOT NULL,
    email VARCHAR(128) NOT NULL UNIQUE,
    password VARCHAR(256) NOT NULL
);

CREATE TABLE architect (
    architect_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    full_name VARCHAR(128) NOT NULL
);

CREATE TABLE building (
    building_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    name VARCHAR(256) NOT NULL,
    address VARCHAR(512) NOT NULL,
    description TEXT,
    admin_id VARCHAR(36) NOT NULL,
    FOREIGN KEY (admin_id) REFERENCES admin(admin_id)
);

CREATE TABLE designs (
    architect_id VARCHAR(36) NOT NULL,
    building_id VARCHAR(36) NOT NULL,
    PRIMARY KEY (architect_id, building_id),
    FOREIGN KEY (architect_id) REFERENCES architect(architect_id),
    FOREIGN KEY (building_id) REFERENCES building(building_id)
);

CREATE TABLE floor (
    floor_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    floor_number INTEGER NOT NULL,
    layout_image VARCHAR(512) NOT NULL,
    building_id VARCHAR(36) NOT NULL,
    admin_id VARCHAR(36) NOT NULL,
    FOREIGN KEY (building_id) REFERENCES building(building_id),
    UNIQUE (building_id, floor_number)
);

CREATE TABLE unit (
    unit_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    unit_number VARCHAR(64) NOT NULL,
    room_number INTEGER NOT NULL,
    floor_area DECIMAL(10,2) NOT NULL,
    status VARCHAR(32) NOT NULL CHECK (status IN ('Available', 'Reserved', 'Sold')) DEFAULT 'Available',
    price DECIMAL(15,2) NOT NULL,
    image VARCHAR(512),
    floorplan VARCHAR(512),
    vector_image VARCHAR(512) NOT NULL,
    floor_id VARCHAR(36) NOT NULL,
    admin_id VARCHAR(36) NOT NULL,
    FOREIGN KEY (floor_id) REFERENCES floor(floor_id),
    UNIQUE (floor_id, unit_number)
);

CREATE TABLE agent (
    agent_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    name VARCHAR(256) NOT NULL,
    email VARCHAR(256) NOT NULL UNIQUE,
    password VARCHAR(256) NOT NULL
);

CREATE TABLE client (
    client_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    name VARCHAR(128) NOT NULL,
    email VARCHAR(256) NOT NULL UNIQUE,
    phone VARCHAR(32) NOT NULL
);

CREATE TABLE timeslot (
    timeslot_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    date DATE NOT NULL,
    time_start TIME NOT NULL,
    time_end TIME NOT NULL,
    status VARCHAR(32) NOT NULL CHECK (status IN ('Available', 'Booked')) DEFAULT 'Available',
    agent_id VARCHAR(36) NOT NULL,
    FOREIGN KEY (agent_id) REFERENCES agent(agent_id),
    UNIQUE (agent_id, date, time_start)
);

CREATE TABLE inquiry (
    inquiry_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    message TEXT NOT NULL,
    status VARCHAR(32) NOT NULL CHECK (status IN ('New', 'Replied', 'Closed')) DEFAULT 'New',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    unit_id VARCHAR(36) NOT NULL,
    client_id VARCHAR(36) NOT NULL,
    agent_id VARCHAR(36) NOT NULL,
    response VARCHAR(500),
    responded_at TIMESTAMP,
    FOREIGN KEY (unit_id) REFERENCES unit(unit_id),
    FOREIGN KEY (client_id) REFERENCES client(client_id),
    FOREIGN KEY (agent_id) REFERENCES agent(agent_id)
);

CREATE TABLE appointment (
    appointment_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    status VARCHAR(32) NOT NULL CHECK (status IN ('Scheduled', 'Completed', 'Cancelled')) DEFAULT 'Scheduled',
    client_id VARCHAR(36) NOT NULL,
    unit_id VARCHAR(36) NOT NULL,
    timeslot_id VARCHAR(36) NOT NULL,
    agent_id VARCHAR(36) NOT NULL,
    FOREIGN KEY (client_id) REFERENCES client(client_id),
    FOREIGN KEY (unit_id) REFERENCES unit(unit_id),
    FOREIGN KEY (timeslot_id) REFERENCES timeslot(timeslot_id),
    UNIQUE (timeslot_id)
);