Changes between Initial Version and Version 1 of DdlScript

Timestamp:

05/04/26 11:30:12 (3 weeks ago)

Author:

231141

Comment:

--

DdlScript

@@ +1 @@
+{{{
+#!sql
+CREATE TABLE UserAccount (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    email VARCHAR(255) NOT NULL UNIQUE,
+    name VARCHAR(100) NOT NULL,
+    surname VARCHAR(100) NOT NULL,
+    phone_number VARCHAR(20) NOT NULL UNIQUE,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP,
+    deleted_at TIMESTAMP,
+
+    CONSTRAINT chk_user_updated_at CHECK (updated_at IS NULL OR updated_at >= created_at),
+    CONSTRAINT chk_user_deleted_at CHECK (deleted_at IS NULL OR deleted_at >= created_at),
+    CONSTRAINT chk_email CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
+    CONSTRAINT chk_phone CHECK (phone_number ~ '^\+?[0-9]{7,15}$'),
+    CONSTRAINT chk_user_created_at CHECK (created_at <= CURRENT_TIMESTAMP)
+);
+
+CREATE TABLE Location (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    city VARCHAR(100) NOT NULL,
+    latitude DECIMAL(9,6) NOT NULL,
+    longitude DECIMAL(9,6) NOT NULL,
+    CONSTRAINT uq_location_cords UNIQUE (latitude, longitude),
+    CONSTRAINT chk_latitude CHECK (latitude BETWEEN -90 AND 90),
+    CONSTRAINT chk_longitude CHECK (longitude BETWEEN -180 AND 180)
+);
+
+CREATE TABLE Category (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    category_name VARCHAR(100) NOT NULL UNIQUE,
+    description VARCHAR(500),
+    parent_category_id INT,
+    CONSTRAINT fk_category_parent
+        FOREIGN KEY (parent_category_id)
+        REFERENCES Category(id)
+        ON DELETE SET NULL
+);
+
+CREATE TABLE NotificationType (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    name VARCHAR(100) NOT NULL,
+    description VARCHAR(500)
+);
+
+CREATE TABLE Badge (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    badge_name VARCHAR(100) NOT NULL,
+    description VARCHAR(500),
+    category_id INT NOT NULL,
+    min_price INT,
+    max_price INT,
+    tier_level INT,
+
+    CONSTRAINT chk_min_price CHECK (min_price >= 0),
+    CONSTRAINT chk_max_price CHECK (max_price >= 0),
+    CONSTRAINT chk_price_range CHECK (min_price <= max_price),
+    CONSTRAINT chk_tier_range CHECK (1<=tier_level and 5>=tier_level),
+
+    CONSTRAINT fk_badge_category
+        FOREIGN KEY (category_id)
+        REFERENCES Category(id)
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE Worker (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    user_id INT NOT NULL,
+    work_mode VARCHAR(20) NOT NULL,
+    service_radius_km INT NOT NULL,
+    location_id INT NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP,
+
+    CONSTRAINT chk_worker_created_at CHECK (created_at<=CURRENT_TIMESTAMP),
+    CONSTRAINT chk_worker_updated_at CHECK (updated_at IS NULL OR updated_at >= created_at),
+    CONSTRAINT chk_radius CHECK (service_radius_km > 0),
+    CONSTRAINT chk_worker_work_mode CHECK (work_mode IN ('HYBRID','ONSITE','REMOTE')),
+    CONSTRAINT fk_worker_user
+        FOREIGN KEY (user_id)
+        REFERENCES UserAccount(id)
+        ON DELETE CASCADE,
+    CONSTRAINT fk_worker_location
+        FOREIGN KEY (location_id)
+        REFERENCES Location(id)
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE Client (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    user_id INT NOT NULL,
+    CONSTRAINT fk_client_user
+        FOREIGN KEY (user_id)
+        REFERENCES UserAccount(id)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE Favourite (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    worker_id INT NOT NULL,
+    client_id INT NOT NULL,
+    CONSTRAINT fk_favourite_worker
+        FOREIGN KEY (worker_id)
+        REFERENCES Worker(id)
+        ON DELETE CASCADE,
+    CONSTRAINT fk_favourite_client
+        FOREIGN KEY (client_id)
+        REFERENCES Client(id)
+        ON DELETE CASCADE,
+    CONSTRAINT unq_worker_client UNIQUE(worker_id,client_id)
+);
+
+CREATE TABLE WorkerCategory (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    worker_id INT NOT NULL,
+    category_id INT NOT NULL,
+    CONSTRAINT unq_worker_category UNIQUE(worker_id, category_id),
+    CONSTRAINT fk_worker_category_worker
+        FOREIGN KEY (worker_id)
+        REFERENCES Worker(id)
+        ON DELETE CASCADE ,
+    CONSTRAINT fk_worker_category_category
+        FOREIGN KEY (category_id)
+        REFERENCES Category(id)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE WorkerBadge (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    badge_id INT NOT NULL,
+    worker_id INT NOT NULL,
+    date_acquired DATE,
+    is_active BOOLEAN,
+    CONSTRAINT unq_worker_badge UNIQUE(badge_id, worker_id),
+    CONSTRAINT fk_worker_badge_badge
+        FOREIGN KEY (badge_id)
+        REFERENCES Badge(id)
+        ON DELETE CASCADE,
+    CONSTRAINT fk_worker_badge_worker
+        FOREIGN KEY (worker_id)
+        REFERENCES Worker(id)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE TaskRequest (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    client_id INT NOT NULL,
+    description VARCHAR(1000) NOT NULL,
+    work_mode VARCHAR(20) NOT NULL,
+    status VARCHAR(20) CHECK (status in('OPEN','CLOSED')),
+    category_id INT NOT NULL,
+    location_id INT NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP,
+    deleted_at TIMESTAMP,
+    CONSTRAINT chk_task_req_work_mode CHECK (work_mode IN ('HYBRID','ONSITE','REMOTE')),
+    CONSTRAINT chk_task_req_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
+    CONSTRAINT chk_task_req_updated_at CHECK
+        (updated_at IS NULL OR (updated_at >= created_at AND updated_at <= CURRENT_TIMESTAMP)),
+    CONSTRAINT chk_task_req_deleted_at CHECK
+        (deleted_at IS NULL OR (deleted_at >= created_at AND deleted_at <= CURRENT_TIMESTAMP)),
+    CONSTRAINT fk_task_request_client
+        FOREIGN KEY (client_id)
+        REFERENCES Client(id)
+        ON DELETE CASCADE,
+    CONSTRAINT fk_task_request_category
+        FOREIGN KEY (category_id)
+        REFERENCES Category(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT fk_task_request_location
+        FOREIGN KEY (location_id)
+        REFERENCES Location(id)
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE Offer (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    worker_id INT NOT NULL,
+    task_request_id INT NOT NULL,
+    price INT NOT NULL,
+    offer_status VARCHAR(20) NOT NULL,
+    initiated_by VARCHAR(10) NOT NULL CHECK (initiated_by IN ('CLIENT','WORKER')),
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP,
+    deleted_at TIMESTAMP,
+    CONSTRAINT chk_price CHECK (price > 0),
+    CONSTRAINT chk_offer_status CHECK (offer_status IN ('PENDING', 'ACCEPTED', 'REJECTED')),
+    CONSTRAINT chk_offer_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
+    CONSTRAINT chk_task_off_updated_at CHECK
+        (updated_at IS NULL OR (updated_at >= created_at AND updated_at <= CURRENT_TIMESTAMP)),
+    CONSTRAINT chk_task_off_deleted_at CHECK
+        (deleted_at IS NULL OR (deleted_at >= created_at AND deleted_at <= CURRENT_TIMESTAMP)),
+    CONSTRAINT fk_offer_worker
+        FOREIGN KEY (worker_id)
+        REFERENCES Worker(id)
+        ON DELETE CASCADE,
+    CONSTRAINT fk_offer_task_request
+        FOREIGN KEY (task_request_id)
+        REFERENCES TaskRequest(id)
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE Task (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    offer_id INT NOT NULL,
+    status VARCHAR(20) NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP,
+    completed_at TIMESTAMP,
+    CONSTRAINT chk_task_status CHECK (status IN ('ACTIVE', 'COMPLETED', 'CANCELLED')),
+    CONSTRAINT chk_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
+    CONSTRAINT chk_completed_at CHECK (completed_at <= CURRENT_TIMESTAMP),
+    CONSTRAINT chk_timeline CHECK (updated_at IS NULL OR updated_at >= created_at),
+    CONSTRAINT fk_task_offer
+        FOREIGN KEY (offer_id)
+        REFERENCES Offer(id)
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE Message (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    text VARCHAR(1000) NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    task_id INT NOT NULL,
+    sender_id INT NOT NULL,
+
+    CONSTRAINT chk_sent_at CHECK (created_at <= CURRENT_TIMESTAMP),
+
+    CONSTRAINT fk_message_task
+        FOREIGN KEY (task_id)
+        REFERENCES Task(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT fk_message_sender
+        FOREIGN KEY (sender_id)
+        REFERENCES UserAccount(id)
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE Review (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    comment TEXT,
+    rating INT NOT NULL,
+    task_id INT NOT NULL,
+    reviewed_id INT NOT NULL,
+    reviewer_id INT NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5),
+    CONSTRAINT chk_created CHECK (created_at <= CURRENT_TIMESTAMP),
+    CONSTRAINT fk_review_task
+        FOREIGN KEY (task_id)
+        REFERENCES Task(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT fk_review_reviewed
+        FOREIGN KEY (reviewed_id)
+        REFERENCES UserAccount(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT fk_review_reviewer
+        FOREIGN KEY (reviewer_id)
+        REFERENCES UserAccount(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT unq UNIQUE(task_id, reviewer_id)
+);
+
+CREATE TABLE Complaint (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    reason VARCHAR(255) NOT NULL,
+    description VARCHAR(1000),
+    status VARCHAR(20) NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP,
+    task_id INT NOT NULL,
+    client_id INT NOT NULL,
+    worker_id INT NOT NULL,
+    CONSTRAINT chk_complaint_status CHECK (status IN ('OPEN', 'RESOLVED', 'DISMISSED')),
+    CONSTRAINT chk_complaint_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
+    CONSTRAINT chk_timeline CHECK (updated_at IS NULL OR updated_at >= created_at),
+    CONSTRAINT fk_complaint_task
+        FOREIGN KEY (task_id)
+        REFERENCES Task(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT fk_complaint_client
+        FOREIGN KEY (client_id)
+        REFERENCES Client(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT fk_complaint_worker
+        FOREIGN KEY (worker_id)
+        REFERENCES Worker(id)
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE ComplaintAttachment (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    file_url VARCHAR(500) NOT NULL,
+    description VARCHAR(500),
+    complaint_id INT NOT NULL,
+    user_id INT,
+    CONSTRAINT fk_complaint_attachment_complaint
+        FOREIGN KEY (complaint_id)
+        REFERENCES Complaint(id)
+        ON DELETE CASCADE,
+    CONSTRAINT fk_complaint_attachment_user
+        FOREIGN KEY (user_id)
+        REFERENCES UserAccount(id)
+        ON DELETE SET NULL
+);
+
+CREATE TABLE Payment (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    amount INT NOT NULL,
+    payment_method VARCHAR(50) NOT NULL,
+    status VARCHAR(20) NOT NULL,
+    task_id INT NOT NULL,
+    client_id INT NOT NULL,
+    worker_id INT NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP,
+
+    CONSTRAINT chk_amount CHECK (amount > 0),
+    CONSTRAINT chk_status CHECK (status IN ('PENDING', 'PAID', 'FAILED')),
+    CONSTRAINT chk_payment_method CHECK (payment_method IN ('CARD','CASH','PAYPAL')),
+    CONSTRAINT chk_p_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
+    CONSTRAINT chk_task_p_updated_at CHECK
+        (updated_at IS NULL OR (updated_at >= created_at AND updated_at <= CURRENT_TIMESTAMP)),
+    CONSTRAINT fk_payment_task
+        FOREIGN KEY (task_id)
+        REFERENCES Task(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT fk_payment_client
+        FOREIGN KEY (client_id)
+        REFERENCES Client(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT fk_payment_worker
+        FOREIGN KEY (worker_id)
+        REFERENCES Worker(id)
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE TaskPayment (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    payment_id INT NOT NULL,
+    task_id INT NOT NULL,
+    CONSTRAINT fk_task_payment_payment
+        FOREIGN KEY (payment_id)
+        REFERENCES Payment(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT fk_task_payment_task
+        FOREIGN KEY (task_id)
+        REFERENCES Task(id)
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE NotificationPreference (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    is_enabled BOOLEAN NOT NULL,
+    channel VARCHAR(10) NOT NULL,
+    user_id INT NOT NULL,
+    notification_type_id INT NOT NULL,
+    CONSTRAINT fk_notification_preference_user
+        FOREIGN KEY (user_id)
+        REFERENCES UserAccount(id)
+        ON DELETE CASCADE,
+    CONSTRAINT fk_notification_preference_type
+        FOREIGN KEY (notification_type_id)
+        REFERENCES NotificationType(id)
+        ON DELETE RESTRICT,
+    CONSTRAINT chk_channel CHECK (channel IN ('EMAIL','SMS','PUSH'))
+);
+
+CREATE TABLE Notification (
+    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    title VARCHAR(255) NOT NULL,
+    body VARCHAR(1000) NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    user_id INT NOT NULL,
+    notification_type_id INT,
+    task_id INT,
+    offer_id INT,
+    payment_id INT,
+    CONSTRAINT chk_notification_time CHECK (created_at <= CURRENT_TIMESTAMP),
+    CONSTRAINT fk_notification_user
+        FOREIGN KEY (user_id)
+        REFERENCES UserAccount(id)
+        ON DELETE CASCADE,
+    CONSTRAINT fk_notification_type
+        FOREIGN KEY (notification_type_id)
+        REFERENCES NotificationType(id)
+        ON DELETE SET NULL,
+    CONSTRAINT fk_notification_task
+        FOREIGN KEY (task_id)
+        REFERENCES Task(id)
+        ON DELETE SET NULL,
+    CONSTRAINT fk_notification_offer
+        FOREIGN KEY (offer_id)
+        REFERENCES Offer(id)
+        ON DELETE SET NULL,
+    CONSTRAINT fk_notification_payment
+        FOREIGN KEY (payment_id)
+        REFERENCES Payment(id)
+        ON DELETE SET NULL
+);
+
+CREATE TABLE cities (
+    city VARCHAR(25),
+    latitude DECIMAL,
+    longitude DECIMAL
+);
+}}}