LogicalAndPhysicalDesign: schema_creation.sql

BEGIN;

DROP TABLE IF EXISTS booking_services CASCADE;
DROP TABLE IF EXISTS sitter_services CASCADE;
DROP TABLE IF EXISTS booking_pets CASCADE;
DROP TABLE IF EXISTS admin_management CASCADE;
DROP TABLE IF EXISTS payments CASCADE;
DROP TABLE IF EXISTS reviews CASCADE;
DROP TABLE IF EXISTS bookings CASCADE;
DROP TABLE IF EXISTS services CASCADE;
DROP TABLE IF EXISTS pets CASCADE;
DROP TABLE IF EXISTS pet_types CASCADE;
DROP TABLE IF EXISTS pet_sitters CASCADE;
DROP TABLE IF EXISTS pet_owners CASCADE;
DROP TABLE IF EXISTS admins CASCADE;
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
    user_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    username VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(128) NOT NULL,
    last_name VARCHAR(128) NOT NULL,
    password VARCHAR(256) NOT NULL,
    email VARCHAR(254) UNIQUE NOT NULL
);

CREATE TABLE admins (
    user_id VARCHAR(36) PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE pet_owners (
    user_id VARCHAR(36) PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE pet_sitters (
    user_id VARCHAR(36) PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE pet_types (
    pettype_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    species VARCHAR(128) UNIQUE NOT NULL,
    average_lifespan INTEGER,
    needs_outdoor_walk BOOLEAN NOT NULL
);

CREATE TABLE pets (
    pet_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    name VARCHAR(128) NOT NULL,
    photo TEXT,
    age INTEGER NOT NULL CHECK (age >= 0),
    special_needs TEXT,
    description TEXT,
    owner_id VARCHAR(36) NOT NULL REFERENCES pet_owners(user_id) ON DELETE CASCADE,
    pettype_id VARCHAR(36) NOT NULL REFERENCES pet_types(pettype_id) ON DELETE RESTRICT
);

CREATE TABLE services (
    service_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    type VARCHAR(128) NOT NULL,
    description TEXT
);

CREATE TABLE bookings (
    booking_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    status VARCHAR(32) NOT NULL CHECK (status IN ('Pending', 'Confirmed', 'Completed', 'Rejected')) DEFAULT 'Pending',
    date_from DATE NOT NULL,
    date_to DATE NOT NULL CHECK (date_to >= date_from),
    address VARCHAR(512) NOT NULL,
    owner_id VARCHAR(36) NOT NULL REFERENCES pet_owners(user_id) ON DELETE RESTRICT,
    sitter_id VARCHAR(36) NOT NULL REFERENCES pet_sitters(user_id) ON DELETE RESTRICT
);

CREATE TABLE reviews (
    review_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    booking_id VARCHAR(36) UNIQUE NOT NULL REFERENCES bookings(booking_id) ON DELETE CASCADE
);

CREATE TABLE payments (
    payment_id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    amount INTEGER NOT NULL CHECK (amount > 0),
    payment_type VARCHAR(32) NOT NULL,
    booking_id VARCHAR(36) UNIQUE NOT NULL REFERENCES bookings(booking_id) ON DELETE CASCADE
);

CREATE TABLE admin_management (
    admin_id VARCHAR(36) REFERENCES admins(user_id) ON DELETE CASCADE,
    user_id VARCHAR(36) REFERENCES users(user_id) ON DELETE CASCADE,
    PRIMARY KEY (admin_id, user_id)
);

CREATE TABLE booking_pets (
    booking_id VARCHAR(36) REFERENCES bookings(booking_id) ON DELETE CASCADE,
    pet_id VARCHAR(36) REFERENCES pets(pet_id) ON DELETE CASCADE,
    PRIMARY KEY (booking_id, pet_id)
);

CREATE TABLE sitter_services (
    sitter_id VARCHAR(36) REFERENCES pet_sitters(user_id) ON DELETE CASCADE,
    service_id VARCHAR(36) REFERENCES services(service_id) ON DELETE CASCADE,
    PRIMARY KEY (sitter_id, service_id)
);

CREATE TABLE booking_services (
    booking_id VARCHAR(36) REFERENCES bookings(booking_id) ON DELETE CASCADE,
    service_id VARCHAR(36) REFERENCES services(service_id) ON DELETE CASCADE,
    PRIMARY KEY (booking_id, service_id)
);

COMMIT;