|| '''[[WikiStart| Home ]]''' || '''[[DatabaseCreation| Database Creation ]]''' ||
{{{
#!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
);
}}}