CREATE TABLE Role
(
    role_id     SERIAL NOT NULL PRIMARY KEY,
    role_name   text   NOT NULL UNIQUE,
    description text
);

CREATE TABLE Permission
(
    permission_id SERIAL NOT NULL PRIMARY KEY,
    action_name   text   NOT NULL UNIQUE
);

CREATE TABLE Industry
(
    industry_id   SERIAL NOT NULL PRIMARY KEY,
    industry_name text   NOT NULL UNIQUE
);

CREATE TABLE Project_Status
(
    status_id   SERIAL NOT NULL PRIMARY KEY,
    status_name text   NOT NULL UNIQUE
);

CREATE TABLE Technology
(
    technology_id   SERIAL NOT NULL PRIMARY KEY,
    technology_name text   NOT NULL UNIQUE
);

CREATE TABLE Rating_Dimension
(
    dimension_id   SERIAL NOT NULL PRIMARY KEY,
    dimension_name text   NOT NULL UNIQUE,
    description    text   NOT NULL
);

CREATE TABLE Subscription_Tier
(
    tier_id               SERIAL NOT NULL PRIMARY KEY,
    tier_name             text   NOT NULL UNIQUE,
    list_price            numeric(10, 2),
    allows_custom_pricing bool   NOT NULL DEFAULT false,

    CONSTRAINT chk_tier_pricing_exclusivity
        CHECK (
            (allows_custom_pricing = true AND list_price IS NULL) OR
                (allows_custom_pricing = false AND list_price IS NOT NULL)
            ),

    CONSTRAINT chk_tier_list_price_positive
        CHECK (list_price IS NULL OR list_price > 0)
);

CREATE TABLE "User"
(
    user_id       SERIAL    NOT NULL PRIMARY KEY,
    type          text      NOT NULL CHECK (type IN ('client', 'vendor', 'management')),
    first_name    text      NOT NULL,
    last_name     text      NOT NULL,
    email         text      NOT NULL UNIQUE,
    password_hash text      NOT NULL,
    is_active     bool      NOT NULL DEFAULT false,
    last_login_at timestamp,
    created_at    timestamp NOT NULL DEFAULT NOW(),
    updated_at    timestamp NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_user_email_format
        CHECK (email ~ '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$'),

    CONSTRAINT chk_user_names_nonblank
        CHECK (btrim(first_name) <> '' AND btrim(last_name) <> ''),

    CONSTRAINT chk_user_password_nonblank
        CHECK (btrim(password_hash) <> ''),

    CONSTRAINT chk_user_timestamps
        CHECK (updated_at >= created_at
               AND (last_login_at IS NULL OR last_login_at >= created_at))
);

CREATE TABLE Vendor
(
    vendor_id   SERIAL NOT NULL PRIMARY KEY,
    agency_name text   NOT NULL,
    website     text   NOT NULL
);

CREATE TABLE Client
(
    client_id     SERIAL NOT NULL PRIMARY KEY,
    industry_id   int4   NOT NULL,
    company_name  text   NOT NULL,
    contact_email text   NOT NULL,

    CONSTRAINT fk_client_industry
        FOREIGN KEY (industry_id) REFERENCES Industry (industry_id)
            ON DELETE RESTRICT
);

CREATE TABLE Client_User
(
    user_id   int4 NOT NULL PRIMARY KEY,
    client_id int4 NOT NULL,

    CONSTRAINT fk_clientuser_user
        FOREIGN KEY (user_id) REFERENCES "User" (user_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_clientuser_client
        FOREIGN KEY (client_id) REFERENCES Client (client_id)
            ON DELETE RESTRICT
);

CREATE TABLE Vendor_User
(
    user_id   int4 NOT NULL PRIMARY KEY,
    vendor_id int4 NOT NULL,

    CONSTRAINT fk_vendoruser_user
        FOREIGN KEY (user_id) REFERENCES "User" (user_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_vendoruser_vendor
        FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
            ON DELETE RESTRICT
);

CREATE TABLE Management_User
(
    user_id int4 NOT NULL PRIMARY KEY,
    role_id int4 NOT NULL,

    CONSTRAINT fk_mgmtuser_user
        FOREIGN KEY (user_id) REFERENCES "User" (user_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_mgmtuser_role
        FOREIGN KEY (role_id) REFERENCES Role (role_id)
            ON DELETE RESTRICT
);

CREATE TABLE Role_Permission
(
    role_id       int4 NOT NULL,
    permission_id int4 NOT NULL,
    PRIMARY KEY (role_id, permission_id),

    CONSTRAINT fk_roleperm_role
        FOREIGN KEY (role_id) REFERENCES Role (role_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_roleperm_permission
        FOREIGN KEY (permission_id) REFERENCES Permission (permission_id)
            ON DELETE RESTRICT
);

CREATE TABLE Vendor_Subscription
(
    contract_id      SERIAL    NOT NULL PRIMARY KEY,
    vendor_id        int4      NOT NULL,
    tier_id          int4      NOT NULL,
    negotiated_price numeric(10, 2),
    start_date       date      NOT NULL DEFAULT CURRENT_DATE,
    end_date         date,
    is_active        bool      NOT NULL DEFAULT true,
    created_at       timestamp NOT NULL DEFAULT NOW(),
    updated_at       timestamp NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_vendorsub_dates
        CHECK (end_date IS NULL OR end_date > start_date),

    CONSTRAINT fk_vendorsub_vendor
        FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_vendorsub_tier
        FOREIGN KEY (tier_id) REFERENCES Subscription_Tier (tier_id)
            ON DELETE RESTRICT,

    CONSTRAINT chk_vendorsub_price_positive
        CHECK (negotiated_price IS NULL OR negotiated_price > 0)
);

CREATE TABLE Client_Vendor_Contract
(
    contract_id     SERIAL    NOT NULL PRIMARY KEY,
    client_id       int4      NOT NULL,
    vendor_id       int4      NOT NULL,
    contract_number text UNIQUE,
    contract_title  text      NOT NULL,
    start_date      date      NOT NULL DEFAULT CURRENT_DATE,
    end_date        date,
    total_value     numeric(10, 2),
    currency_code   text,
    terms_summary   text,
    is_active       bool      NOT NULL DEFAULT true,
    created_at      timestamp NOT NULL DEFAULT NOW(),
    updated_at      timestamp NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_cvc_dates
        CHECK (end_date IS NULL OR end_date > start_date),

    CONSTRAINT fk_cvc_client
        FOREIGN KEY (client_id) REFERENCES Client (client_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_cvc_vendor
        FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
            ON DELETE RESTRICT,

    CONSTRAINT chk_cvc_total_value_positive
        CHECK (total_value IS NULL OR total_value > 0),

    CONSTRAINT chk_cvc_currency_code
        CHECK (currency_code IS NULL OR currency_code ~ '^[A-Z]{3}$'),

    CONSTRAINT chk_cvc_title_nonblank
        CHECK (btrim(contract_title) <> '')
);

CREATE TABLE Project
(
    project_id   SERIAL         NOT NULL PRIMARY KEY,
    contract_id  int4           NOT NULL,
    status_id    int4           NOT NULL,
    project_name text           NOT NULL,
    start_date   date           NOT NULL DEFAULT CURRENT_DATE,
    end_date     date,
    budget       numeric(10, 2) NOT NULL,
    created_at   timestamp      NOT NULL DEFAULT NOW(),
    updated_at   timestamp      NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_project_dates
        CHECK (end_date IS NULL OR end_date > start_date),

    CONSTRAINT fk_project_contract
        FOREIGN KEY (contract_id) REFERENCES Client_Vendor_Contract (contract_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_project_status
        FOREIGN KEY (status_id) REFERENCES Project_Status (status_id)
            ON DELETE RESTRICT,

    CONSTRAINT chk_project_budget_positive
        CHECK (budget > 0),

    CONSTRAINT chk_project_name_nonblank
        CHECK (btrim(project_name) <> ''),

    CONSTRAINT uq_project_contract_name
        UNIQUE (contract_id, project_name)
);

CREATE TABLE Project_Technology
(
    project_id    int4 NOT NULL,
    technology_id int4 NOT NULL,
    PRIMARY KEY (project_id, technology_id),

    CONSTRAINT fk_projtec_project
        FOREIGN KEY (project_id) REFERENCES Project (project_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_projtec_technology
        FOREIGN KEY (technology_id) REFERENCES Technology (technology_id)
            ON DELETE RESTRICT
);

CREATE TABLE Project_Budget_Audit
(
    audit_id   SERIAL         NOT NULL PRIMARY KEY,
    project_id int4           NOT NULL,
    old_budget numeric(10, 2) NOT NULL,
    new_budget numeric(10, 2) NOT NULL,
    created_at timestamp      NOT NULL DEFAULT NOW(),
    updated_at timestamp      NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_budgetaudit_project
        FOREIGN KEY (project_id) REFERENCES Project (project_id)
            ON DELETE RESTRICT,

    CONSTRAINT chk_budgetaudit_positive
        CHECK (old_budget > 0 AND new_budget > 0)
);

CREATE TABLE Project_Status_History
(
    project_status_history_id SERIAL    NOT NULL PRIMARY KEY,
    project_id                int4      NOT NULL,
    vendor_user_id            int4,
    management_user_id        int4,
    status_id                 int4      NOT NULL,
    changed_at                timestamp NOT NULL DEFAULT NOW(),
    comment                   text,

    CONSTRAINT chk_statushistory_single_actor
        CHECK ((vendor_user_id IS NULL) <> (management_user_id IS NULL)),

    CONSTRAINT fk_statushistory_project
        FOREIGN KEY (project_id) REFERENCES Project (project_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_statushistory_vendoruser
        FOREIGN KEY (vendor_user_id) REFERENCES Vendor_User (user_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_statushistory_mgmtuser
        FOREIGN KEY (management_user_id) REFERENCES Management_User (user_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_statushistory_status
        FOREIGN KEY (status_id) REFERENCES Project_Status (status_id)
            ON DELETE RESTRICT
);

CREATE TABLE Review
(
    review_id      SERIAL    NOT NULL PRIMARY KEY,
    project_id     int4      NOT NULL UNIQUE,
    client_user_id int4      NOT NULL,
    review_date    date      NOT NULL DEFAULT CURRENT_DATE,
    summary_text   text      NOT NULL,
    is_published   bool      NOT NULL DEFAULT false,
    created_at     timestamp NOT NULL DEFAULT NOW(),
    updated_at     timestamp NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_review_project
        FOREIGN KEY (project_id) REFERENCES Project (project_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_review_clientuser
        FOREIGN KEY (client_user_id) REFERENCES Client_User (user_id)
            ON DELETE RESTRICT,

    CONSTRAINT chk_review_summary_nonblank
        CHECK (btrim(summary_text) <> '')
);

CREATE TABLE Review_Score
(
    review_id    int4 NOT NULL,
    dimension_id int4 NOT NULL,
    score_value  int4 NOT NULL,
    PRIMARY KEY (review_id, dimension_id),

    CONSTRAINT fk_reviewscore_review
        FOREIGN KEY (review_id) REFERENCES Review (review_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_reviewscore_dimension
        FOREIGN KEY (dimension_id) REFERENCES Rating_Dimension (dimension_id)
            ON DELETE RESTRICT,

    CONSTRAINT chk_reviewscore_range
        CHECK (score_value BETWEEN 1 AND 5)
);

CREATE TABLE Dispute_Ticket
(
    ticket_id                   SERIAL    NOT NULL PRIMARY KEY,
    assigned_management_user_id int4               DEFAULT NULL,
    review_id                   int4      NOT NULL,
    vendor_user_id              int4      NOT NULL,
    reason                      text      NOT NULL,
    is_resolved                 bool      NOT NULL DEFAULT false,
    filed_at                    date      NOT NULL DEFAULT CURRENT_DATE,
    created_at                  timestamp NOT NULL DEFAULT NOW(),
    updated_at                  timestamp NOT NULL DEFAULT NOW(),
    resolved_at                 timestamp,
    resolution_note             text,

    CONSTRAINT fk_ticket_mgmtuser
        FOREIGN KEY (assigned_management_user_id) REFERENCES Management_User (user_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_ticket_review
        FOREIGN KEY (review_id) REFERENCES Review (review_id)
            ON DELETE RESTRICT,

    CONSTRAINT fk_ticket_vendoruser
        FOREIGN KEY (vendor_user_id) REFERENCES Vendor_User (user_id)
            ON DELETE RESTRICT,

    CONSTRAINT chk_ticket_resolution
        CHECK (
            (is_resolved = false AND resolved_at IS NULL) OR
            (is_resolved = true  AND resolved_at IS NOT NULL
                                 AND assigned_management_user_id IS NOT NULL
                                 AND resolution_note IS NOT NULL)
            ),

    CONSTRAINT chk_ticket_resolved_after_filed
        CHECK (resolved_at IS NULL OR resolved_at::date >= filed_at),

    CONSTRAINT chk_ticket_reason_nonblank
        CHECK (btrim(reason) <> ''),

    CONSTRAINT chk_ticket_note_nonblank
        CHECK (resolution_note IS NULL OR btrim(resolution_note) <> '')
);


-- Uniqueness applies only to active subscriptions and unresolved disputes.
CREATE UNIQUE INDEX uq_vendorsub_one_active
    ON Vendor_Subscription (vendor_id)
    WHERE is_active;

CREATE UNIQUE INDEX uq_dispute_open_per_vendor_review
    ON Dispute_Ticket (review_id, vendor_user_id)
    WHERE is_resolved = false;


COMMENT ON COLUMN Vendor_Subscription.contract_id IS
    'Surrogate key of a platform-vendor subscription period. Unrelated to Client_Vendor_Contract.contract_id, which Project.contract_id references.';

COMMENT ON COLUMN Client_Vendor_Contract.total_value IS
    'Framework value agreed in the contract document. Project budgets are planned independently and are not capped by it.';

COMMENT ON CONSTRAINT chk_statushistory_single_actor ON Project_Status_History IS
    'Exactly one actor per status change: either a vendor user or a management user.';