-- ===========================================================================
-- Ecore - Project Management System
-- PHASE P2 : Logical & Physical Design - Schema Creation (DDL)
-- Target   : PostgreSQL, schema "project"
-- Source   : server/prisma/schema.prisma (current, 17 entities + 1 enum)
--
-- Type mapping (Prisma -> PostgreSQL):
--   Int       -> INTEGER (PK: IDENTITY)    Float    -> DOUBLE PRECISION
--   String    -> TEXT                      Boolean  -> BOOLEAN
--   DateTime  -> TIMESTAMP(3)              Json     -> JSONB
--   String[]  -> TEXT[]                    enum     -> project.file_review_status
--
-- Re-runnable: the schema is dropped and recreated at the top.
-- ===========================================================================

DROP SCHEMA IF EXISTS project CASCADE;
CREATE SCHEMA project;


-- ---------------------------------------------------------------------------
-- Enumerated type: review workflow status for task files
-- ---------------------------------------------------------------------------
CREATE TYPE project.file_review_status AS ENUM (
    'PENDING',
    'APPROVED',
    'NEEDS_REVISION',
    'REJECTED'
);

-- ---------------------------------------------------------------------------
-- users : every person in the system (admins, workers, clients) by role.
--         Self-references itself for referrals and company-team membership.
-- ---------------------------------------------------------------------------
CREATE TABLE project.users (
    id                INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email             TEXT         NOT NULL UNIQUE,
    password          TEXT         NOT NULL,
    role              TEXT         NOT NULL,
    name              TEXT         NOT NULL,
    created_at        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    company           TEXT,
    logo              TEXT,
    color_hex         TEXT,
    address           TEXT,
    postal_address    TEXT,
    phone_number      TEXT,
    extra_emails      TEXT,
    brand_pattern     TEXT,
    short_info        TEXT,
    nickname          TEXT,
    avatar_emoji      TEXT,
    skills            TEXT[]       NOT NULL DEFAULT ARRAY[]::TEXT[],
    profile_status    TEXT         DEFAULT 'INCOMPLETE',
    invite_token      TEXT         UNIQUE,
    invite_expires    TIMESTAMP(3),
    referred_by_id    INTEGER,
    company_owner_id  INTEGER,
    CONSTRAINT fk_users_referred_by
        FOREIGN KEY (referred_by_id)   REFERENCES project.users (id) ON DELETE SET NULL,
    CONSTRAINT fk_users_company_owner
        FOREIGN KEY (company_owner_id) REFERENCES project.users (id) ON DELETE SET NULL
);

-- ---------------------------------------------------------------------------
-- projects : a deliverable engagement carried out for a client.
-- ---------------------------------------------------------------------------
CREATE TABLE project.projects (
    id            INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name          TEXT         NOT NULL,
    description   TEXT,
    client_id     INTEGER,
    status        TEXT         NOT NULL DEFAULT 'ACTIVE',
    service_type  TEXT         NOT NULL DEFAULT 'OTHER',
    metadata      JSONB,
    start_date    TIMESTAMP(3),
    end_date      TIMESTAMP(3),
    created_at    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_projects_client
        FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE SET NULL
);
CREATE INDEX idx_projects_client_id ON project.projects (client_id);
CREATE INDEX idx_projects_status    ON project.projects (status);

-- ---------------------------------------------------------------------------
-- domains : a web domain owned by a client (hosting + renewal lifecycle).
-- ---------------------------------------------------------------------------
CREATE TABLE project.domains (
    id                         INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    client_id                  INTEGER      NOT NULL,
    domain_name                TEXT         NOT NULL,
    is_primary                 BOOLEAN      NOT NULL DEFAULT FALSE,
    is_active                  BOOLEAN      NOT NULL DEFAULT TRUE,
    notes                      TEXT,
    activation_date            TIMESTAMP(3),
    expiration_date            TIMESTAMP(3),
    lifespan_years             INTEGER,
    hosting_provider           TEXT,
    hosting_plan               TEXT,
    hosting_expiry             TIMESTAMP(3),
    status                     TEXT         NOT NULL DEFAULT 'PENDING',
    activation_email_sent_at   TIMESTAMP(3),
    renewal_reminder_sent_at   TIMESTAMP(3),
    created_at                 TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                 TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_domains_client
        FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE CASCADE
);
CREATE INDEX idx_domains_client_id       ON project.domains (client_id);
CREATE INDEX idx_domains_expiration_date ON project.domains (expiration_date);

-- ---------------------------------------------------------------------------
-- tasks : a unit of work for a client, optionally grouped under a project.
-- ---------------------------------------------------------------------------
CREATE TABLE project.tasks (
    id           INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title        TEXT         NOT NULL,
    description  TEXT,
    status       TEXT         NOT NULL DEFAULT 'PENDING',
    due_date     TIMESTAMP(3),
    created_at   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    client_id    INTEGER      NOT NULL,
    project_id   INTEGER,
    CONSTRAINT fk_tasks_client
        FOREIGN KEY (client_id)  REFERENCES project.users (id)    ON DELETE RESTRICT,
    CONSTRAINT fk_tasks_project
        FOREIGN KEY (project_id) REFERENCES project.projects (id) ON DELETE SET NULL
);
CREATE INDEX idx_tasks_project_id ON project.tasks (project_id);

-- ---------------------------------------------------------------------------
-- milestones : an ordered sub-step of a task (completion/deployment tracking).
-- ---------------------------------------------------------------------------
CREATE TABLE project.milestones (
    id                INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    task_id           INTEGER      NOT NULL,
    title             TEXT         NOT NULL,
    description       TEXT,
    image_url         TEXT,
    image_urls        TEXT[]       NOT NULL DEFAULT ARRAY[]::TEXT[],
    pushed_to_github  BOOLEAN      NOT NULL DEFAULT FALSE,
    deployed          BOOLEAN      NOT NULL DEFAULT FALSE,
    is_done           BOOLEAN      NOT NULL DEFAULT FALSE,
    done_at           TIMESTAMP(3),
    done_by           INTEGER,
    created_by_id     INTEGER      NOT NULL,
    "order"           INTEGER      NOT NULL DEFAULT 0,
    created_at        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_milestones_task
        FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE
);
CREATE INDEX idx_milestones_task_id ON project.milestones (task_id);

-- ---------------------------------------------------------------------------
-- task_workers : associative (M:N) table assigning workers (users) to tasks.
-- ---------------------------------------------------------------------------
CREATE TABLE project.task_workers (
    id       INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    task_id  INTEGER NOT NULL,
    user_id  INTEGER NOT NULL,
    CONSTRAINT uq_task_workers_task_user UNIQUE (task_id, user_id),
    CONSTRAINT fk_task_workers_task
        FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE,
    CONSTRAINT fk_task_workers_user
        FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE CASCADE
);
CREATE INDEX idx_task_workers_task_id ON project.task_workers (task_id);
CREATE INDEX idx_task_workers_user_id ON project.task_workers (user_id);

-- ---------------------------------------------------------------------------
-- task_files : a file/deliverable on a task, with review workflow + versioning.
-- ---------------------------------------------------------------------------
CREATE TABLE project.task_files (
    id                 INTEGER                    GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    task_id            INTEGER                    NOT NULL,
    file_name          TEXT                       NOT NULL,
    file_url           TEXT                       NOT NULL,
    file_type          TEXT                       NOT NULL,
    caption            TEXT,
    section            TEXT,
    is_completed       BOOLEAN                    NOT NULL DEFAULT FALSE,
    completed_at       TIMESTAMP(3),
    completed_by       INTEGER,
    uploaded_at        TIMESTAMP(3)               NOT NULL DEFAULT CURRENT_TIMESTAMP,
    uploaded_by        INTEGER                    NOT NULL,
    version            INTEGER                    NOT NULL DEFAULT 1,
    review_status      project.file_review_status NOT NULL DEFAULT 'PENDING',
    review_comment     TEXT,
    visible_to_client  BOOLEAN                    NOT NULL DEFAULT TRUE,
    CONSTRAINT fk_task_files_task
        FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------------
-- file_comments : a comment on a task file (internal / client-visible).
-- ---------------------------------------------------------------------------
CREATE TABLE project.file_comments (
    id                 INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    file_id            INTEGER      NOT NULL,
    user_id            INTEGER      NOT NULL,
    content            TEXT         NOT NULL,
    visible_to_client  BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_file_comments_file
        FOREIGN KEY (file_id) REFERENCES project.task_files (id) ON DELETE CASCADE,
    CONSTRAINT fk_file_comments_user
        FOREIGN KEY (user_id) REFERENCES project.users (id)      ON DELETE RESTRICT
);

-- ---------------------------------------------------------------------------
-- task_comments : a comment on a task (internal / client-visible).
-- ---------------------------------------------------------------------------
CREATE TABLE project.task_comments (
    id                 INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    task_id            INTEGER      NOT NULL,
    user_id            INTEGER      NOT NULL,
    content            TEXT         NOT NULL,
    visible_to_client  BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_task_comments_task
        FOREIGN KEY (task_id) REFERENCES project.tasks (id) ON DELETE CASCADE,
    CONSTRAINT fk_task_comments_user
        FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE RESTRICT
);

-- ---------------------------------------------------------------------------
-- invoices : a bill issued to a client (header for line items + payments).
-- ---------------------------------------------------------------------------
CREATE TABLE project.invoices (
    id             INTEGER          GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    invoice_number TEXT             NOT NULL UNIQUE,
    amount         DOUBLE PRECISION NOT NULL,
    due_date       TIMESTAMP(3)     NOT NULL,
    status         TEXT             NOT NULL DEFAULT 'PENDING',
    description    TEXT,
    paid_at        TIMESTAMP(3),
    created_at     TIMESTAMP(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    file_url       TEXT,
    payment_link   TEXT,
    client_id      INTEGER          NOT NULL,
    issue_date     TIMESTAMP(3),
    payment_terms  TEXT,
    notes          TEXT,
    subtotal       DOUBLE PRECISION,
    tax_rate       DOUBLE PRECISION,
    tax_amount     DOUBLE PRECISION,
    CONSTRAINT fk_invoices_client
        FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE RESTRICT
);

-- ---------------------------------------------------------------------------
-- payments : a payment recorded against an invoice (supports partials).
-- ---------------------------------------------------------------------------
CREATE TABLE project.payments (
    id          INTEGER          GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    invoice_id  INTEGER          NOT NULL,
    amount      DOUBLE PRECISION NOT NULL,
    paid_at     TIMESTAMP(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    note        TEXT,
    created_at  TIMESTAMP(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_payments_invoice
        FOREIGN KEY (invoice_id) REFERENCES project.invoices (id) ON DELETE CASCADE
);
CREATE INDEX idx_payments_invoice_id ON project.payments (invoice_id);

-- ---------------------------------------------------------------------------
-- invoice_line_items : a single billed line on an invoice.
-- ---------------------------------------------------------------------------
CREATE TABLE project.invoice_line_items (
    id          INTEGER          GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    invoice_id  INTEGER          NOT NULL,
    name        TEXT             NOT NULL,
    description TEXT,
    quantity    DOUBLE PRECISION NOT NULL,
    unit_price  DOUBLE PRECISION NOT NULL,
    sort_order  INTEGER          NOT NULL DEFAULT 0,
    CONSTRAINT fk_invoice_line_items_invoice
        FOREIGN KEY (invoice_id) REFERENCES project.invoices (id) ON DELETE CASCADE
);
CREATE INDEX idx_invoice_line_items_invoice_id ON project.invoice_line_items (invoice_id);

-- ---------------------------------------------------------------------------
-- timesheet_projects : a project bucket for time tracking (distinct from
--                      delivery projects); optionally linked to a client.
-- ---------------------------------------------------------------------------
CREATE TABLE project.timesheet_projects (
    id            INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    project_name  TEXT         NOT NULL,
    client_id     INTEGER,
    description   TEXT,
    is_paid       BOOLEAN      NOT NULL DEFAULT FALSE,
    paid_at       TIMESTAMP(3),
    created_at    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_timesheet_projects_client
        FOREIGN KEY (client_id) REFERENCES project.users (id) ON DELETE SET NULL
);
CREATE INDEX idx_timesheet_projects_client_id ON project.timesheet_projects (client_id);
CREATE INDEX idx_timesheet_projects_is_paid   ON project.timesheet_projects (is_paid);

-- ---------------------------------------------------------------------------
-- timesheet_entries : a logged block of hours under a timesheet project.
-- ---------------------------------------------------------------------------
CREATE TABLE project.timesheet_entries (
    id            INTEGER          GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    project_id    INTEGER          NOT NULL,
    date          TIMESTAMP(3)     NOT NULL,
    hours_worked  DOUBLE PRECISION NOT NULL,
    hourly_rate   DOUBLE PRECISION NOT NULL,
    total_pay     DOUBLE PRECISION NOT NULL,
    notes         TEXT,
    created_at    TIMESTAMP(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_timesheet_entries_project
        FOREIGN KEY (project_id) REFERENCES project.timesheet_projects (id) ON DELETE CASCADE
);
CREATE INDEX idx_timesheet_entries_project_id ON project.timesheet_entries (project_id);
CREATE INDEX idx_timesheet_entries_date       ON project.timesheet_entries (date);

-- ---------------------------------------------------------------------------
-- notifications : an in-app notification for a user.
-- ---------------------------------------------------------------------------
CREATE TABLE project.notifications (
    id           INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id      INTEGER      NOT NULL,
    type         TEXT         NOT NULL,
    title        TEXT         NOT NULL,
    message      TEXT         NOT NULL,
    read         BOOLEAN      NOT NULL DEFAULT FALSE,
    link         TEXT,
    source_role  TEXT,
    task_id      INTEGER,
    thread_type  TEXT,
    created_at   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_notifications_user
        FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE CASCADE
);
CREATE INDEX idx_notifications_user_id            ON project.notifications (user_id);
CREATE INDEX idx_notifications_read               ON project.notifications (read);
CREATE INDEX idx_notifications_user_created       ON project.notifications (user_id, created_at);
CREATE INDEX idx_notifications_user_type_source   ON project.notifications (user_id, type, source_role);
CREATE INDEX idx_notifications_user_task_thread   ON project.notifications (user_id, task_id, thread_type);

-- ---------------------------------------------------------------------------
-- invites : a pending invitation to onboard a new user (token stored hashed).
-- ---------------------------------------------------------------------------
CREATE TABLE project.invites (
    id                INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email             TEXT         NOT NULL,
    role              TEXT         NOT NULL,
    token_hash        TEXT         NOT NULL UNIQUE,
    name              TEXT         NOT NULL,
    company           TEXT,
    company_owner_id  INTEGER,
    domain_name       TEXT,
    domain_expiry     TEXT,
    hosting_plan      TEXT,
    hosting_expiry    TEXT,
    invited_by_id     INTEGER      NOT NULL,
    used              BOOLEAN      NOT NULL DEFAULT FALSE,
    cancelled         BOOLEAN      NOT NULL DEFAULT FALSE,
    used_at           TIMESTAMP(3),
    created_at        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at        TIMESTAMP(3) NOT NULL,
    CONSTRAINT fk_invites_invited_by
        FOREIGN KEY (invited_by_id) REFERENCES project.users (id) ON DELETE RESTRICT
);
CREATE INDEX idx_invites_token_hash ON project.invites (token_hash);
CREATE INDEX idx_invites_email      ON project.invites (email);
CREATE INDEX idx_invites_expires_at ON project.invites (expires_at);

-- ---------------------------------------------------------------------------
-- notification_preferences : per-user, per-event-type channel toggles.
-- ---------------------------------------------------------------------------
CREATE TABLE project.notification_preferences (
    id              INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id         INTEGER NOT NULL,
    event_type      TEXT    NOT NULL,
    in_app_enabled  BOOLEAN NOT NULL DEFAULT TRUE,
    email_enabled   BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT uq_notif_pref_user_event UNIQUE (user_id, event_type),
    CONSTRAINT fk_notif_pref_user
        FOREIGN KEY (user_id) REFERENCES project.users (id) ON DELETE CASCADE
);
CREATE INDEX idx_notif_pref_user_id ON project.notification_preferences (user_id);

-- ===========================================================================
-- End of schema_creation.sql  (17 tables, 1 enum, 22 foreign keys)
-- ===========================================================================
