<?php

/**
 * Created by PhpStorm.
 * User: voznyy
 * Date: 31.08.15
 * Time: 14:17
 */

class Comments
{
    private $dbconnection;
    private $idShift = 0;
    public $baseEntityURL = "";

        public function getForecastsComments() {
            $threadsSql = "SELECT ".$this->idShift." AS entityId, 'forecasts' AS name, '' AS tagId;";

            $postsSql = "SELECT ".$this->idShift." AS entityId,
                        comments.id AS commentId,
                        comments.summary AS text,
                        comments.created_date AS createdAt,
                        comments.author AS author,
                        users.email AS email
                    FROM comments
                    LEFT JOIN users ON comments.user_id = users.id
                    WHERE comments.comment_type_lid = 110
                    AND comments.deleted = FALSE;";

        $threads = $this->dbconnection->query($threadsSql);
        $posts = $this->dbconnection->query($postsSql);
        $this->dbconnection->close();

        $this->buildMXL(array(
            "threads" => $threads,
            "posts" => $posts
        ));
    }

    public function getGamesComments() {
        $threadsSql = "SELECT games.id AS entityId, games.transliteration AS name, tags.id AS tagId
                        FROM comments
                        JOIN games ON comments.game_id = games.id
                        JOIN tags ON tags.game_id = games.id
                        WHERE games.deleted = FALSE
                        AND comments.deleted = FALSE
                        AND tags.game_id IS NOT NULL
                        GROUP BY games.id LIMIT 100;";

        $postsSql = "SELECT
                        games.id AS entityId,
                        comments.id AS commentId,
                        comments.summary AS text,
                        comments.created_date AS createdAt,
                        comments.author AS author,
                        users.email AS email
                    FROM comments
                    JOIN games ON comments.game_id = games.id
                    LEFT JOIN users ON comments.user_id = users.id
                    WHERE games.deleted = FALSE
                    AND comments.game_id IS NOT NULL
                    AND comments.deleted = FALSE LIMIT 100;";

        $threads = $this->dbconnection->query($threadsSql);
        $posts = $this->dbconnection->query($postsSql);
        $this->dbconnection->close();

        $this->buildMXL(array(
            "threads" => $threads,
            "posts" => $posts
        ));
    }

    public function getPlayersComments() {
        $threadsSql = "SELECT players.id AS entityId, CONCAT(players.fname, ' ', players.lname) AS name, tags.id AS tagId
                        FROM comments
                        JOIN players ON players.id = comments.player_id
                        JOIN tags ON players.id = tags.player_id
                        WHERE comments.player_id IS NOT NULL
                        AND players.deleted = FALSE
                        AND comments.deleted = FALSE
                        AND tags.player_id IS NOT NULL
                        GROUP BY players.id";

        $postsSql = "SELECT
                        players.id AS entityId,
                        comments.id AS commentId,
                        comments.summary AS text,
                        comments.created_date AS createdAt,
                        comments.author AS author,
                        users.email AS email
                    FROM comments
                    JOIN players ON players.id = comments.player_id
                    LEFT JOIN users ON comments.user_id = users.id
                    WHERE player_id IS NOT NULL
                    AND players.deleted = FALSE
                    AND comments.deleted = FALSE;";

        $threads = $this->dbconnection->query($threadsSql);
        $posts = $this->dbconnection->query($postsSql);
        $this->dbconnection->close();

        $this->buildMXL(array(
            "threads" => $threads,
            "posts" => $posts
        ));
    }

    public function getTeamsComments() {
        $threadsSql = "SELECT teams.id AS entityId, teams.name AS name, tags.id AS tagId
                        FROM comments
                        JOIN teams ON teams.id = comments.team_id
                        JOIN tags ON teams.id = tags.team_id
                        WHERE comments.team_id IS NOT NULL
                        AND tags.team_id IS NOT NULL
                        AND teams.deleted = FALSE
                        AND comments.deleted = FALSE
                        GROUP BY teams.id";

        $postsSql = "SELECT
                    teams.id AS entityId,
                    comments.id AS commentId,
                    comments.summary AS text,
                    comments.created_date AS createdAt,
                    comments.author AS author,
                    users.email AS email
                    FROM comments
                    JOIN teams ON teams.id = comments.team_id
                    LEFT JOIN users ON comments.user_id = users.id
                    WHERE comments.team_id IS NOT NULL
                    AND teams.deleted = FALSE
                    AND comments.deleted = FALSE
                    ORDER BY createdAt;";

        $threads = $this->dbconnection->query($threadsSql);
        $posts = $this->dbconnection->query($postsSql);
        $this->dbconnection->close();

        $this->buildMXL(array(
            "threads" => $threads,
            "posts" => $posts
        ));
    }

    public function getStadiumsComments() {
        $threadsSql = "SELECT stadiums.id AS entityId, stadiums.name AS name, tags.id AS tagId
                        FROM comments
                        JOIN stadiums ON stadiums.id = comments.stadium_id
                        JOIN tags ON tags.stadium_id = stadiums.id
                        WHERE comments.stadium_id IS NOT NULL
                        AND tags.stadium_id IS NOT NULL
                        AND stadiums.deleted = FALSE
                        AND comments.deleted = FALSE
                        GROUP BY stadiums.id;";

        $postsSql = "SELECT
                    stadiums.id AS entityId,
                    comments.id AS commentId,
                    comments.summary AS text,
                    comments.created_date AS createdAt,
                    comments.author AS author,
                    users.email AS email
                    FROM comments
                    JOIN stadiums ON stadiums.id = comments.stadium_id
                    LEFT JOIN users ON comments.user_id = users.id
                    WHERE comments.stadium_id IS NOT NULL
                    AND stadiums.deleted = FALSE
                    AND comments.deleted = FALSE
                    ORDER BY createdAt;";

        $threads = $this->dbconnection->query($threadsSql);
        $posts = $this->dbconnection->query($postsSql);
        $this->dbconnection->close();

        $this->buildMXL(array(
            "threads" => $threads,
            "posts" => $posts
        ));
    }

    public function getNewsComments() {
        $threadsSql = "SELECT news.id AS entityId, news.name AS name, news.id AS tagId
                FROM news
                JOIN comments ON comments.news_id = news.id
                WHERE news.news_status_lid = 19
                AND news.news_type_lid = 22
                AND news.deleted = FALSE
                AND news.language_id = 1
                AND comments.deleted = FALSE
                GROUP BY news.id;";

        $postsSql = "SELECT
                        news.id AS entityId,
                        comments.id AS commentId,
                        comments.summary AS text,
                        comments.created_date AS createdAt,
                        comments.author AS author,
                        users.email AS email
                    FROM news
                    JOIN comments ON comments.news_id = news.id
                    JOIN users ON comments.user_id = users.id
                    WHERE news.news_status_lid = 19
                    AND news.news_type_lid = 22
                    AND news.deleted = FALSE
                    AND news.language_id = 1
                    AND comments.deleted = FALSE;";

        $threads = $this->dbconnection->query($threadsSql);
        $posts = $this->dbconnection->query($postsSql);
        $this->dbconnection->close();

        $this->buildMXL(array(
            "threads" => $threads,
            "posts" => $posts
        ));
    }

    private function buildMXL($data) {

        $domtree = new DOMDocument('1.0', 'UTF-8');
        $domtree->formatOutput = true;
        $xmlRoot = $domtree->createElement("xml");
        $xmlRoot = $domtree->appendChild($xmlRoot);
        $so = $domtree->createElement("so");
        $xmlRoot->appendChild($so);

        if ($data["posts"]->num_rows > 0 && $data["threads"]->num_rows > 0) {
            while($row = $data["threads"]->fetch_assoc()) {
                $so->appendChild( $this->createThreadNode($domtree, $row["entityId"], $row["name"], $row["tagId"]) );
            }

            while($row = $data["posts"]->fetch_assoc()) {
                $createdAt = strtotime($row["createdAt"]);

                $so->appendChild($this->createPostNode($domtree, array(
                    "id" => $row["entityId"],
                    "text" => $row["text"],
                    "createdAt" => date("Y-m-d\TH:i:s\Z", $createdAt),
                    "email" => $row["email"],
                    "author" => $row["author"],
                )));
            }
        }

        echo $domtree->saveXML();

    }

    /*XML NODES*/
    private function createThreadNode(DOMDocument $dom, $id, $name, $tagId) {
        $post = $dom->createElement("thread", "");
        $postId = $dom->createAttribute("id");
        $postId->value = $id + $this->idShift;
        $post->appendChild($postId);

        $link = $post->appendChild( $dom->createElement("link", $this->baseEntityURL.$tagId) );
        $cdataTitle = $dom->createCDATASection($name);
        $title = $post->appendChild( $dom->createElement("title") );
        $title->appendChild($cdataTitle);
        return $post;
    }

    private function createPostNode(DOMDocument $dom, array $data) {
        $post = $dom->createElement("post");
        $postId = $dom->createAttribute("id");
        $postId->value = $data["id"];
        $post->appendChild($postId);

        $thread = $dom->createElement("thread");
        $threadId = $dom->createAttribute("id");
        $threadId->value = $data["id"] + $this->idShift;
        $thread->appendChild($threadId);

        $post->appendChild($thread);

        $textCdata = $dom->createCDATASection($data["text"]);

        $message = $post->appendChild( $dom->createElement("message") );
        $message->appendChild($textCdata);
        $post->appendChild( $dom->createElement("createdAt", $data["createdAt"]) );


        $post->appendChild( $dom->createElement("useremail", $data["email"]) );

        $usernameCdata = $dom->createCDATASection($data["author"]);
        $username = $post->appendChild( $dom->createElement("username") );
        $username->appendChild($usernameCdata);

        $post->appendChild( $dom->createElement("rating", 0 ));

        return $post;
    }

    public function setIdShift($shift) {
        $this->idShift = $shift;
    }

    private function connectToDatabase() {
        require('Database.php');
        $db = new Database;
        return $db->getConnection();
    }

    function __construct() {
        $this->dbconnection = $this->connectToDatabase();
    }
}
