<?php
require_once(VENDOR_PATH . DS . "autoload.php");

use PhpOffice\PhpSpreadsheet\IOFactory;

class Project
{
    private $conn;
    private $table = "projects";
    private $tableClient = "clients";
    private $tableUrls = "surveyurls";
    private $tableVendorDetails = "vendordetails";

    public $pid;
    public $name;
    public $cid;
    public $status;
    public $baseUrl;
    public $noOfUrls;
    public $complete = "";
    public $quotafull = "";
    public $screenout = "";
    public $attributes;
    public $liveAttributes;
    public $file;
    public $textInput;
    public $openAttributes = 0;
    public $urlCount;
    public $tags = "";

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

    private function getDynamicPlaceholders($data, $baseName)
    {
        $columnNames = "";
        $columnPlaceholders = "";
        $columnValues = array();

        if ($data && !($baseName == "param" && $this->openAttributes == 1)) {
            $attributesArray = explode(",", $data);

            foreach ($attributesArray as $key => $val) {
                $columnNames = $columnNames . "," . $baseName . $key + 1;
                $columnPlaceholders = $columnPlaceholders . ", :" . $baseName . $key + 1;
                array_push($columnValues, $val);
            }
        }

        return array(
            "columnNames" => $columnNames,
            "columnPlaceholders" => $columnPlaceholders,
            "columnValues" => $columnValues
        );
    }

    private function insertUrl($url)
    {
        $guid = uniqid();

        $vendorParams = $this->getDynamicPlaceholders($this->attributes, "param");

        $liveParams = $this->getDynamicPlaceholders($this->liveAttributes, "liveparam");

        $query = "INSERT IGNORE INTO $this->tableUrls
            (pid, cid, survey_url, our_id, tags" . $vendorParams["columnNames"] . $liveParams["columnNames"] . ") VALUES 
            (:pid, :cid, :survey_url, :our_id, :tags" . $vendorParams["columnPlaceholders"] . $liveParams["columnPlaceholders"] . ")";

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

        $stmt->bindValue(":pid", $this->pid);
        $stmt->bindValue(":cid", $this->cid);
        $stmt->bindValue(":tags", $this->tags);
        $stmt->bindValue(":survey_url", $url);
        $stmt->bindValue(":our_id", $guid);

        if (count($vendorParams["columnValues"]) > 0) {
            foreach ($vendorParams["columnValues"] as $key => $val) {
                $paramGuid = uniqid();
                $param = $val . "=" . $paramGuid;
                $stmt->bindValue(":param" . $key + 1, $param);
            }
        }

        if (count($liveParams["columnValues"]) > 0) {
            foreach ($liveParams["columnValues"] as $liveParamKey => $liveParamName) {
                $parts = parse_url($url);
                $queryString = isset($parts['query']) ? $parts['query'] : "";
                parse_str($queryString, $urlQueryParams);
                $paramName = ":liveparam" . $liveParamKey + 1;
                if (!empty($urlQueryParams[$liveParamName])) {
                    $paramValue = $liveParamName . "=" . $urlQueryParams[$liveParamName];
                    $stmt->bindValue($paramName, $paramValue);
                } else {
                    $stmt->bindValue($paramName, NULL);
                }
            }
        }

        if ($stmt->execute()) {
            return true;
        }

        return false;
    }

    private function uploadUrls()
    {
        try {
            $spreadsheet = IOFactory::load($this->file);
            $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        } catch (Exception $e) {
            die("Error loading Excel file: " . $e->getMessage());
        }

        foreach ($sheetData as $row) {
            $url = implode(":", $row);
            if (!empty($url)) {
                $result = $this->insertUrl($url);
            }
        }

        return $result;
    }

    private function generateUrls()
    {

        $url = $this->baseUrl;

        $insertedCount = 0;

        for ($x = 0; $x < $this->urlCount; $x++) {
            $guid = uniqid();

            $updatedUrl = replaceQueryParam($url, $this->liveAttributes, $guid);

            $result = $this->insertUrl($updatedUrl);

            if ($result) {
                $insertedCount++;
            }
        }

        return $insertedCount;
    }

    private function uploadTextInput()
    {
        $itemArray = preg_split('/[\r\n]+/', $this->textInput, -1, PREG_SPLIT_NO_EMPTY);

        foreach ($itemArray as $url) {

            if (!empty($url)) {
                $result = $this->insertUrl($url);
            }
        }

        return $result;
    }

    private function sanitizeInputs()
    {
        $this->name = sanitize($this->name);
    }

    public function list()
    {
        $statusQuery = !empty($this->status) ? " WHERE " . $this->table . ".status = '" . $this->status . "'" : "";
        $query = "SELECT " . $this->table . ".* FROM " . $this->table . $statusQuery . " ORDER BY updated_at DESC";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    public function listWithCount()
    {
        $query = "SELECT
            p.pid,
            p.name,
            p.liveAttributes,
            p.updated_at,
            p.status,
            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->table . " AS p
            LEFT JOIN " . $this->tableUrls . " AS s ON p.pid = s.pid WHERE p.status = '" . $this->status . "' ORDER BY p.updated_at DESC";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    public function read()
    {
        $query = "SELECT * FROM " . $this->table . " WHERE pid='" . $this->pid . "'";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    public function projectVendorCombinedData()
    {
        $query = "SELECT
            v.attributes,
            v.isopen,
            p.liveAttributes,
            p.status
        FROM " . $this->tableVendorDetails . " AS v
            LEFT JOIN " . $this->table . " AS p ON p.pid = v.pid 
        WHERE v.pid='" . $this->pid . "' AND v.cid='" . $this->cid . "'";

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

    public function create()
    {
        $this->sanitizeInputs();

        $query = "INSERT INTO $this->table
            (pid, name, baseUrl, liveAttributes)
            VALUES
            (:pid, :name, :baseUrl, :liveAttributes)";
        $stmt = $this->conn->prepare($query);

        $this->pid = uniqid();
        $stmt->bindParam(":pid", $this->pid);
        $stmt->bindParam(":name", $this->name);
        $stmt->bindParam(":baseUrl", $this->baseUrl);
        $stmt->bindParam(":liveAttributes", $this->liveAttributes);

        if ($stmt->execute()) {

            if ($this->file) {
                $uploadStatus = $this->uploadUrls();

                if ($uploadStatus) {
                    return 200;
                }
            }
            return 200;
        }

        return 500;
    }

    public function edit()
    {

        $this->sanitizeInputs();

        $query = "UPDATE $this->table
            SET
                name=:name,
                baseUrl=:baseUrl,
                status=:status,
                liveAttributes=:liveAttributes
            WHERE pid='" . $this->pid . "'";

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

        $stmt->bindParam(":name", $this->name);
        $stmt->bindParam(":baseUrl", $this->baseUrl);
        $stmt->bindParam(":liveAttributes", $this->liveAttributes);
        $stmt->bindParam(":status", $this->status);

        if ($stmt->execute()) {
            return 204;
        }

        return 500;
    }

    public function updateStatus()
    {

        $this->sanitizeInputs();

        $query = "UPDATE " . $this->table . " SET status=:status WHERE pid='" . $this->pid . "'";

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

        $stmt->bindParam(":status", $this->status);

        if ($stmt->execute()) {
            return 204;
        }

        return 500;
    }

    public function upload()
    {
        $uploadStatus = "";
        if ($this->file) {
            $uploadStatus = $this->uploadUrls();
        } else if ($this->textInput) {
            $uploadStatus = $this->uploadTextInput();
        } else if ($this->urlCount) {
            $uploadStatus = $this->generateUrls();
        }

        if (!empty($uploadStatus)) {
            return 227;
        }

        return 500;
    }

    public function getSurveyRows()
    {
        $vendorQuery = !empty($this->cid) ? " AND cid='" . $this->cid . "'" : "";

        $statusQuery = !empty($this->status) ? " AND status='" . $this->status . "'" : "";

        $query = "SELECT
            our_id,
            survey_url,
            param1,
            param2,
            param3,
            status,
            tags
            FROM " . $this->tableUrls . "
            WHERE pid='" . $this->pid . "'" . $statusQuery . $vendorQuery . " ORDER BY cid";

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

    public function detail()
    {

        $tempObject = new stdClass();

        $tempObject->data = null;

        $query = "SELECT
            p.pid,
            p.name,
            p.liveAttributes,
            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->table . " AS p
            LEFT JOIN " . $this->tableUrls . " AS s ON p.pid = s.pid WHERE p.pid = '" . $this->pid . "'";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();

        return $stmt;
    }

    public function vendorStatistic()
    {

        $tempObject = new stdClass();

        $tempObject->data = null;

        $query = "SELECT
            v.isopen,
            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->tableVendorDetails . " AS v
            LEFT JOIN " . $this->tableUrls . " AS s ON v.pid = s.pid AND v.cid = s.cid
        WHERE v.pid = '" . $this->pid . "' AND v.cid = '" . $this->cid . "'";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();

        return $stmt;
    }

    public function addVendor()
    {
        $this->sanitizeInputs();

        $query = "INSERT INTO $this->tableVendorDetails
            (pid, cid, screenout, quotafull, complete, attributes, isopen)
            VALUES
            (:pid, :cid, :screenout, :quotafull, :complete, :attributes, :isopen)";
        $stmt = $this->conn->prepare($query);

        $stmt->bindParam(":pid", $this->pid);
        $stmt->bindParam(":cid", $this->cid);
        $stmt->bindParam(":screenout", $this->screenout);
        $stmt->bindParam(":quotafull", $this->quotafull);
        $stmt->bindParam(":complete", $this->complete);
        $stmt->bindParam(":isopen", $this->openAttributes);
        $stmt->bindParam(":attributes", $this->attributes);

        if ($stmt->execute()) {
            return 200;
        }

        return 500;
    }

    public function removeVendor()
    {

        $query = "DELETE FROM " . $this->tableVendorDetails . " WHERE pid='" . $this->pid . "' AND cid='" . $this->cid . "'";

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

        if ($stmt->execute()) {

            $queryToClearUrls = "DELETE FROM " . $this->tableUrls . " WHERE pid='" . $this->pid . "' AND cid='" . $this->cid . "'";

            $stmtForUrls = $this->conn->prepare($queryToClearUrls);

            if ($stmtForUrls->execute()) {
                return 228;
            }

            return 228;
        }

        return 500;
    }

    public function updateVendor()
    {
        $this->sanitizeInputs();

        $query = "UPDATE $this->tableVendorDetails SET
                attributes=:attributes,
                screenout=:screenout,
                quotafull=:quotafull,
                complete=:complete,
                isopen=:isopen
            WHERE pid='" . $this->pid . "' AND cid='" . $this->cid . "'";

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

        $stmt->bindParam(":screenout", $this->screenout);
        $stmt->bindParam(":quotafull", $this->quotafull);
        $stmt->bindParam(":complete", $this->complete);
        $stmt->bindParam(":isopen", $this->openAttributes);
        $stmt->bindParam(":attributes", $this->attributes);

        if ($stmt->execute()) {
            return 204;
        }

        return 500;
    }

    public function vendorDetails()
    {

        $tempObject = new stdClass();

        $tempObject->data = null;

        $query = "SELECT
            *,
            p.name as projectName,
            p.liveAttributes as liveAttributes,
            p.baseUrl as baseUrl,
            v.name as vendorName
        FROM " . $this->tableVendorDetails . " AS vpd
            LEFT JOIN " . $this->table . " AS p ON vpd.pid = p.pid
            LEFT JOIN " . $this->tableClient . " AS v ON v.cid = '" . $this->cid . "'
        WHERE vpd.pid = '" . $this->pid . "' AND vpd.cid = '" . $this->cid . "'";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();

        return $stmt;
    }
}