Project Management Logic

-- Phase status and gate review readiness
SELECT pp.phase_name, pp.phase_number, pp.completion_pct, pp.gate_review_status,
    CASE
        WHEN pp.completion_pct = 100 AND pp.gate_review_status = 'APPROVED'
        THEN 'Ready for next phase'
        WHEN pp.completion_pct = 100 THEN 'Awaiting gate review'
        ELSE 'In progress'
    END AS phase_readiness
FROM proj.project_phases pp
WHERE pp.project_id = @project_id ORDER BY pp.phase_number;

-- Overdue milestone detection (scheduled job)
UPDATE proj.project_milestones SET status = 'OVERDUE'
WHERE status = 'UPCOMING' AND planned_date < NOW() AND actual_date IS NULL;

-- Notify project managers of overdue milestones
SELECT p.name, pm.milestone_name, pm.milestone_type, pm.planned_date,
    NOW() - pm.planned_date AS days_overdue, u.email AS manager_email
FROM proj.project_milestones pm
JOIN proj.projects p ON p.id = pm.project_id
JOIN users u ON u.id = p.manager_id
WHERE pm.status = 'OVERDUE';

-- Risk score = Probability x Impact (range: 1 to 25)
risk_score = probability * impact
-- 1-4 = Low (green)  |  5-9 = Medium (yellow)  |  10-16 = High (orange)  |  17-25 = Critical (red)

-- Active high-priority risks requiring review
SELECT r.risk_title, r.risk_category, r.probability, r.impact, r.risk_score,
    r.response_strategy, u.full_name AS risk_owner, r.review_date
FROM proj.project_risks r
JOIN users u ON u.id = r.risk_owner
WHERE r.project_id = @project_id AND r.status = 'OPEN' AND r.risk_score >= 10
ORDER BY r.risk_score DESC;

-- Budget utilization check (BR-PRJ-003: Alert at 80%)
SELECT p.code, p.name, p.budget_amount, b.committed_amount, b.spent_amount,
    ROUND((b.spent_amount / NULLIF(p.budget_amount, 0)) * 100, 2) AS utilization_pct,
    CASE
        WHEN b.spent_amount >= p.budget_amount * 0.80 THEN 'ALERT: 80% threshold breached'
        WHEN b.spent_amount >= p.budget_amount * 0.60 THEN 'WARNING: Approaching threshold'
        ELSE 'Normal'
    END AS budget_status
FROM proj.projects p JOIN planning.budgets b ON b.project_id = p.id
WHERE p.status = 'ACTIVE' AND p.is_deleted = false;

-- Trace project back to originating CRM lead
SELECT p.code, p.name, p.status, l.lead_code, l.customer_name,
    l.deal_value AS original_deal_value, p.budget_amount AS project_budget
FROM proj.projects p JOIN crm.leads l ON l.id = p.crm_lead_id
WHERE p.crm_lead_id IS NOT NULL;

-- Detect over-allocated users (total allocation > 100% across active projects)
SELECT u.id, u.full_name,
    SUM(tm.allocation_pct) AS total_allocation,
    COUNT(tm.project_id) AS project_count,
    CASE WHEN SUM(tm.allocation_pct) > 100 THEN 'OVER-ALLOCATED' ELSE 'OK' END AS status
FROM proj.project_team_members tm
JOIN users u ON u.id = tm.user_id
JOIN proj.projects p ON p.id = tm.project_id
WHERE tm.is_active = true AND p.status IN ('PLANNING', 'ACTIVE')
GROUP BY u.id, u.full_name HAVING SUM(tm.allocation_pct) > 100
ORDER BY total_allocation DESC;

-- Example checklist_items JSONB content
{
  "items": [
    { "key": "deliverables_accepted", "label": "All deliverables accepted by customer", "checked": true  },
    { "key": "invoices_settled",      "label": "All invoices settled",                   "checked": true  },
    { "key": "contracts_closed",     "label": "Vendor contracts formally closed",        "checked": false },
    { "key": "docs_archived",        "label": "Project documents archived",              "checked": true  },
    { "key": "team_released",        "label": "Team members released from allocation",   "checked": false },
    { "key": "lessons_documented",   "label": "Lessons learned documented",              "checked": true  }
  ]
}

-- Pending deliverables check before closure approval (BR-PRJ-004)
SELECT pm.milestone_name, pm.milestone_type, pm.status
FROM proj.project_milestones pm
WHERE pm.project_id = @project_id AND pm.status IN ('UPCOMING', 'OVERDUE');
-- If rows returned AND closure_type != 'CANCELLED', block closure

-- BR-PRJ-001: Trigger to prevent project code modification
CREATE OR REPLACE FUNCTION proj.prevent_code_update()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.code IS DISTINCT FROM NEW.code THEN
        RAISE EXCEPTION 'Project code is immutable and cannot be modified';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_project_code_update
BEFORE UPDATE ON proj.projects
FOR EACH ROW EXECUTE FUNCTION proj.prevent_code_update();

-- BR-PRJ-004: Closure validation function
CREATE OR REPLACE FUNCTION proj.validate_closure(
    p_project_id INT, p_closure_type VARCHAR
) RETURNS BOOLEAN AS $$
DECLARE v_pending_count INT;
BEGIN
    IF p_closure_type = 'CANCELLED' THEN RETURN true; END IF;
    SELECT COUNT(*) INTO v_pending_count FROM proj.project_milestones
    WHERE project_id = p_project_id AND status IN ('UPCOMING', 'OVERDUE');
    RETURN v_pending_count = 0;
END;
$$ LANGUAGE plpgsql;