-- Add project request/approval workflow fields

-- Create enum for project request status
CREATE TYPE project_request_status AS ENUM ('PENDING', 'APPROVED', 'REJECTED');

-- Add columns to projects table for request workflow
ALTER TABLE projects
ADD COLUMN IF NOT EXISTS requested_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS approved_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS request_status project_request_status,
ADD COLUMN IF NOT EXISTS requested_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS approved_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS rejection_reason TEXT;

-- Create index for request status for faster querying of pending requests
CREATE INDEX idx_projects_request_status ON projects(request_status) WHERE request_status IS NOT NULL;
CREATE INDEX idx_projects_requested_by ON projects(requested_by) WHERE requested_by IS NOT NULL;

-- Comment explaining the workflow:
-- When a CLIENT creates a project:
--   - request_status = 'PENDING'
--   - requested_by = client user_id
--   - requested_at = NOW()
--   - status = 'PLANNING'
--
-- When an ADMIN/STAFF creates a project:
--   - request_status = NULL (not a request, directly approved)
--   - approved_by = creator user_id
--   - approved_at = NOW()
--   - status = 'PLANNING'
--
-- When ADMIN/STAFF approves a client request:
--   - request_status = 'APPROVED'
--   - approved_by = approver user_id
--   - approved_at = NOW()
--
-- When ADMIN/STAFF rejects a client request:
--   - request_status = 'REJECTED'
--   - approved_by = approver user_id (who rejected)
--   - approved_at = NOW()
--   - rejection_reason = text explaining why