<?php

class Dashboard
{
    private $conn;
    private $projectTable = "projects";
    private $clientTable = "clients";
    private $urlsTable = "surveyurls";

    public $pid;
    public $cid;

    public function __construct($db)
    {
        $this->conn = $db;
    }

    public function recentProjects()
    {
        $query = "SELECT
            p.pid,
            p.name,
            COUNT(s.survey_url) as 'total',
            SUM(case when s.status = 'open' then 1 else 0 end) AS open,
            SUM(case when s.status = 'started' then 1 else 0 end) AS started,
            SUM(case when s.status = 'complete' then 1 else 0 end) AS complete,
            SUM(case when s.status = 'screenout' then 1 else 0 end) AS screenout,
            SUM(case when s.status = 'quotafull' then 1 else 0 end) AS quotafull
        FROM " . $this->projectTable . " AS p
            LEFT JOIN " . $this->urlsTable . " AS s ON p.pid = s.pid WHERE p.status = 'open' GROUP BY p.pid ORDER BY p.updated_at DESC LIMIT 5";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }
}