--users
--  ↑
--  └── project_users ────→ projects
--         ↑
--         └── project_user_roles ───→ project_roles ───→ projects
--                                           │
--                                           └── project_role_permissions ───→ permissions

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(100) UNIQUE NOT NULL,
    customer_id INT REFERENCES customers(id),
    description TEXT,
    location VARCHAR(255),
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    status VARCHAR(50),
    consultant_name VARCHAR(255),
    designer_name VARCHAR(255),
    budget DECIMAL(18,2),
    progress_percent FLOAT DEFAULT 0, -- งานอาจจะคืบหน้าไป 80% 
    completed_percent FLOAT DEFAULT 0, -- แต่ถ้าตรวจรับแล้วผ่านจริงแค่ 50%
    delayed_days INT DEFAULT 0,
    delayed_percent FLOAT DEFAULT 0,
    delayed_description TEXT,
    created_by INT REFERENCES users(id),
    updated_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now(),
    deleted_at TIMESTAMP
);

CREATE TABLE project_users (
    id SERIAL PRIMARY KEY,
    project_id INT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    
    notes TEXT,
    assigned_date TIMESTAMP DEFAULT now(),

    created_by INT REFERENCES users(id),
    updated_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT now(),

    UNIQUE (project_id, user_id)
);

CREATE TABLE project_roles (
    id SERIAL PRIMARY KEY,
    project_id INT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    description TEXT,

    created_by INT REFERENCES users(id),
    updated_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now(),
    deleted_at TIMESTAMP,

    UNIQUE (project_id, name)
);

CREATE TABLE project_user_roles (
    id SERIAL PRIMARY KEY,
    project_user_id INT NOT NULL REFERENCES project_users(id) ON DELETE CASCADE,
    project_role_id INT NOT NULL REFERENCES project_roles(id) ON DELETE CASCADE,

    created_by INT REFERENCES users(id),
    updated_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT now(),

    UNIQUE (project_user_id, project_role_id)
);

CREATE TABLE project_role_permissions (
    id SERIAL PRIMARY KEY,
    project_role_id INT NOT NULL REFERENCES project_roles(id) ON DELETE CASCADE,
    permission_id INT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,

    created_by INT REFERENCES users(id),
    updated_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT now(),

    UNIQUE (project_role_id, permission_id)
);

CREATE TABLE project_status_logs (
    id SERIAL PRIMARY KEY,
    project_id INT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    status VARCHAR(50),
    description TEXT,
    progress_percent FLOAT,
    created_by INT REFERENCES users(id),
    updated_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT now()
);