ddlScript: travel_sage_ddl.sql

CREATE SCHEMA IF NOT EXISTS travel_sage;
SET search_path TO travel_sage;

DROP TABLE IF EXISTS destination_user CASCADE;
DROP TABLE IF EXISTS activity_reservation CASCADE;
DROP TABLE IF EXISTS package_reservation CASCADE;
DROP TABLE IF EXISTS package_activity CASCADE;
DROP TABLE IF EXISTS preference CASCADE;
DROP TABLE IF EXISTS destination_tag CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
DROP TABLE IF EXISTS event CASCADE;
DROP TABLE IF EXISTS activity CASCADE;
DROP TABLE IF EXISTS package CASCADE;
DROP TABLE IF EXISTS meteorological_condition CASCADE;
DROP TABLE IF EXISTS review CASCADE;
DROP TABLE IF EXISTS reservation CASCADE;
DROP TABLE IF EXISTS premium CASCADE;
DROP TABLE IF EXISTS standard CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS destination CASCADE;

CREATE TABLE IF NOT EXISTS destination (
    id_destination SERIAL PRIMARY KEY,
    location_name VARCHAR(255) NOT NULL,
    location_desc TEXT,
    types_of_places TEXT,    
    recommended_season TEXT,   
    average_temp NUMERIC(5,2),
    latitude DECIMAL(9,6),
    longitude DECIMAL(9,6),
    country VARCHAR(100),
    popularity INTEGER DEFAULT 0,
    important_location_name VARCHAR(255), 
    important_location_description TEXT    
);

CREATE TABLE IF NOT EXISTS users (
    id_user SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone_number  VARCHAR(30),
    birth_date DATE,
    is_premium BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS standard (
    id_user INT PRIMARY KEY REFERENCES users(id_user) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS premium (
    id_user INT PRIMARY KEY REFERENCES users(id_user) ON DELETE CASCADE,
    date_created TIMESTAMP NOT NULL DEFAULT now(),
    discount NUMERIC(5,2)
);

CREATE TABLE IF NOT EXISTS reservation (
    id_reservation SERIAL PRIMARY KEY,
    id_user INT NOT NULL REFERENCES users(id_user) ON DELETE CASCADE,
    time_point TIMESTAMP NOT NULL DEFAULT now(),
    premium_discount_applied BOOLEAN NOT NULL DEFAULT FALSE,
    discount_amount NUMERIC(5,2) NOT NULL,
    total_price NUMERIC(12,2) NOT NULL
);

CREATE TABLE IF NOT EXISTS review (
    id_review SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    reservation_id INT UNIQUE REFERENCES reservation(id_reservation) ON DELETE CASCADE,
    id_destination INT NOT NULL REFERENCES destination(id_destination) ON DELETE CASCADE,
    quality SMALLINT NOT NULL CHECK (quality BETWEEN 1 AND 5),
    comment TEXT,
    review_date TIMESTAMP DEFAULT now(),
    vote_count INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS meteorological_condition (
    id_meteo SERIAL PRIMARY KEY,
    id_destination INT NOT NULL REFERENCES destination(id_destination) ON DELETE CASCADE,
    current_temp NUMERIC(5,2) NOT NULL,
    weather_condition VARCHAR(50) NOT NULL,
    warnings VARCHAR(100) NOT NULL,
    humidity NUMERIC(5,2),
    wind NUMERIC(6,2),
    month SMALLINT
);

CREATE TABLE IF NOT EXISTS package (
    id_package SERIAL PRIMARY KEY,
    package_name VARCHAR(255) NOT NULL,
    id_destination INT NOT NULL REFERENCES destination(id_destination) ON DELETE CASCADE,
    price NUMERIC(12,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS activity (
    id_activity SERIAL PRIMARY KEY,
    activity_name VARCHAR(255) NOT NULL,
    id_destination INT REFERENCES destination(id_destination) ON DELETE CASCADE,
    information TEXT,
    amount NUMERIC(10,2),
    category VARCHAR(100) NOT NULL 
);

CREATE TABLE IF NOT EXISTS event (
    id_event SERIAL PRIMARY KEY,
    event_name VARCHAR(255) NOT NULL,
    id_destination INT NOT NULL REFERENCES destination(id_destination) ON DELETE CASCADE,
    start_date DATE,
    end_date DATE,
    details TEXT,
    event_type TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS tag (
    id_tag SERIAL PRIMARY KEY,
    tag_name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS destination_tag (
    id_destination INT REFERENCES destination(id_destination) ON DELETE CASCADE,
    id_tag INT REFERENCES tag(id_tag) ON DELETE CASCADE,
    PRIMARY KEY(id_destination, id_tag)
);

CREATE TABLE IF NOT EXISTS preference (
    id_preference SERIAL PRIMARY KEY,
    id_user INT NOT NULL REFERENCES users(id_user) ON DELETE CASCADE,
    priority INT,
    type_preference TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS package_activity (
    id_package INT REFERENCES package(id_package) ON DELETE CASCADE,
    id_activity INT REFERENCES activity(id_activity) ON DELETE CASCADE,
    PRIMARY KEY(id_package, id_activity)
);

CREATE TABLE IF NOT EXISTS package_reservation (
    id_package INT REFERENCES package(id_package) ON DELETE CASCADE,
    id_reservation INT REFERENCES reservation(id_reservation) ON DELETE CASCADE,
    PRIMARY KEY(id_package, id_reservation)
);

CREATE TABLE IF NOT EXISTS activity_reservation (
    id_activity INT REFERENCES activity(id_activity) ON DELETE CASCADE,
    id_reservation INT REFERENCES reservation(id_reservation) ON DELETE CASCADE,
    PRIMARY KEY(id_activity, id_reservation)
);

CREATE TABLE IF NOT EXISTS destination_user (
    id_destination INT REFERENCES destination(id_destination) ON DELETE CASCADE,
    id_user INT REFERENCES users(id_user) ON DELETE CASCADE,
    rating SMALLINT CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    recommendation_date TIMESTAMP DEFAULT now(),
    PRIMARY KEY(id_destination, id_user)
);