<?php

declare(strict_types=1);

namespace App\Model;

use Mosaico\Model\BaseModel;

class BlogModel extends BaseModel
{
    public function getLatestPosts(int $limit): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            id_post,
            title,
            url_name,
            B.firstname AS author,
            image,
            resume,
            content,
            A.create_date,
            publish_date,
            A.is_enabled
            FROM posts A
            LEFT JOIN users B
            ON A.id_user = B.id_user
            WHERE A.is_enabled = 1
            ORDER BY publish_date DESC
            LIMIT " . $limit ."
            ");
        return $query;
    }

    public function getAllPosts(): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            A.id_post,
            title,
            url_name,
            B.firstname AS author,
            image,
            resume,
            content,
            A.create_date,
            publish_date,
            A.is_enabled,
            is_frontpage,
            (
                SELECT 
                group_concat('{\"tag_name\":','\"',D.tag_name,'\"',',\"tag_url\":','\"',D.tag_url,'\"',',\"id_tag\":','\"',D.id_tag,'\"','}' order by D.id_tag) as tags
                FROM post_tags C
                JOIN tags D
                ON C.id_tag = D.id_tag
                WHERE C.id_post = A.id_post
            ) as tag_list
            FROM posts A
            LEFT JOIN users B
            ON A.id_user = B.id_user
            WHERE A.is_enabled = 1
            ORDER BY publish_date DESC
            ");
        return $query;
    }

    public function getTagPosts($id_tag): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            A.id_post,
            title,
            url_name,
            B.firstname AS author,
            image,
            resume,
            content,
            A.create_date,
            publish_date,
            A.is_enabled,
            (
                SELECT 
                group_concat('{\"tag_name\":','\"',D.tag_name,'\"',',\"tag_url\":','\"',D.tag_url,'\"',',\"id_tag\":','\"',D.id_tag,'\"','}' order by D.id_tag) as tags
                FROM post_tags C
                JOIN tags D
                ON C.id_tag = D.id_tag
                WHERE C.id_post = A.id_post
            ) as tag_list
            FROM posts A
            LEFT JOIN users B
            ON A.id_user = B.id_user
            JOIN post_tags E
            ON E.id_post = A.id_post
            AND E.id_tag = " . $id_tag . "
            WHERE A.is_enabled = 1
            ORDER BY publish_date DESC
            ");
        return $query;
    }

    public function getPostsAdmin(): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            id_post,
            title,
            url_name,
            B.firstname AS author,
            B.id_user,
            image,
            resume,
            content,
            A.create_date,
            publish_date,
            A.is_enabled,
            is_frontpage
            FROM posts A
            LEFT JOIN users B
            ON A.id_user = B.id_user
            ");
        return $query;
    }

    public function getPostById($id_post): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            id_post,
            title,
            url_name,
            B.firstname AS author,
            B.id_user, 
            meta_tags,
            meta_description,
            A.create_date,
            publish_date,
            content,
            resume,
            image,
            is_frontpage,
            A.is_enabled
            FROM posts A
            LEFT JOIN users B
            ON A.id_user = B.id_user
            WHERE id_post = " . $id_post . "
        ");
        return $query;
    }

    public function getPost($url_name): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            id_post,
            title,
            url_name,
            B.firstname AS author,
            B.id_user,
            meta_tags,
            meta_description,
            A.create_date,
            publish_date,
            content,
            resume,
            image,
            A.is_enabled
            FROM posts A
            LEFT JOIN users B
            ON A.id_user = B.id_user
            WHERE url_name = '" . $url_name . "'
        ");
        return $query;
    }

    public function getFrontPosts(): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            id_post,
            title,
            url_name,
            B.firstname AS author,
            B.id_user,
            meta_tags,
            meta_description,
            A.create_date,
            publish_date,
            content,
            resume,
            image
            FROM posts A
            LEFT JOIN users B
            ON A.id_user = B.id_user
            WHERE is_frontpage = 1
            LIMIT 3
        ");
        return $query;
    }

    public function insertPost(array $data) : void
    {
        $query = $this->connect()->query("
            INSERT INTO posts SET
            title               = '" . $data['title'] . "',
            url_name            = '" . $data['url_name'] . "',
            meta_description    = '" . $data['meta_description'] . "',
            meta_tags           = '" . $data['meta_tags'] . "',
            publish_date        = '" . $data['publish_date'] . "',
            resume              = '" . $data['resume'] . "',
            content             = '" . $data['content'] . "',
            image               = '" . $data['image'] . "',
            is_frontpage        = " . $data['is_frontpage'] . "
            ");
    }

    public function getLastPostId() : int
    {
        //return $this->connect()->getLastId();
        $query = $this->connect()->query("
            SELECT max(id_post) as id FROM posts
            ");

        return (int) $query->row['id'];
    }

    public function updatePost(array $data) : void
    {
        $query = $this->connect()->query("
            UPDATE posts SET
            title               = '" . $data['title'] . "',
            url_name            = '" . $data['url_name'] . "',
            meta_description    = '" . $data['meta_description'] . "',
            meta_tags           = '" . $data['meta_tags'] . "',
            publish_date        = '" . $data['publish_date'] . "',
            resume              = '" . $data['resume'] . "',
            content             = '" . $data['content'] . "',
            image               = '" . $data['image'] . "',
            is_frontpage        = " . $data['is_frontpage'] . "
            WHERE id_post = " . $data['id_post'] . "
            ");
    }

    public function updateStatus(array $data) : void
    {
        $query = $this->connect()->query("
            UPDATE posts SET
            is_enabled    = " . $data['status'] . "
            WHERE id_post = " . $data['id_post'] . "
            ");
    }

    public function getAuthors(): bool|object
    {
        $query = $this->connect()->query("
            SELECT 
            id_user,
            firstname,
            lastname,
            create_date
            FROM
            users
            WHERE is_enabled = 1
            AND id_role > 1
        ");
        return $query;
    }

    public function getTags()
    {
        $query = $this->connect()->query("
            SELECT 
            id_tag,
            tag_name,
            tag_url
            FROM
            tags
        ");
        return $query->rows;
    }

    public function getTag($tag_url)
    {
        $query = $this->connect()->query("
            SELECT 
            id_tag,
            tag_name,
            tag_url
            FROM
            tags
            WHERE tag_url = '" . $tag_url . "'
        ");
        return $query;
    }

    public function getTagById($id_tag)
    {
        $query = $this->connect()->query("
            SELECT 
            id_tag,
            tag_name,
            tag_url
            FROM
            tags
            WHERE id_tag = " . $id_tag . "
        ");
        return $query;
    }

    public function getPostTagId($id_post)
    {
        $query = $this->connect()->query("
            SELECT 
            id_tag
            FROM
            post_tags
            WHERE id_post = " . $id_post . "
        ");
        return $query->rows;
    }

    public function getPostTags($id_post)
    {
        $query = $this->connect()->query("
            SELECT 
            B.id_tag,
            B.tag_name,
            B.tag_url
            FROM
            post_tags A
            JOIN tags B
            ON A.id_tag = B.id_tag
            WHERE id_post = " . $id_post . "
        ");
        return $query->rows;
    }

    public function deletePostTag($id_post, $id_tag)
    {
        $query = $this->connect()->query("
            DELETE FROM post_tags
            WHERE id_post = " . $id_post . "
            AND id_tag = " . $id_tag . "
        ");
    }

    public function deletePostTags($id_post)
    {
        $query = $this->connect()->query("
            DELETE FROM post_tags
            WHERE id_post = " . $id_post . "
        ");
    }

    public function insertPostTag($id_post, $id_tag)
    {
        $query = $this->connect()->query("
            INSERT INTO post_tags SET
            id_post = " . $id_post . ",
            id_tag = " . $id_tag . "
        ");
        return $query;
    }

    /**
     * [insertTag description]
     * @param  array $data [description]
     * @return void
     */
    public function insertTag($data) : void
    {
        $query = $this->connect()->query("
            INSERT INTO tags SET
            tag_name = '" . $data['tag_name'] . "',
            tag_url = '" . $data['tag_url'] . "'
        ");
    }

    public function updateTag($data) : void
    {
        $query = $this->connect()->query("
            UPDATE tags SET
            tag_name = '" . $data['tag_name'] . "',
            tag_url = '" . $data['tag_url'] . "'
            WHERE id_tag = " . $data['id_tag'] . "
        ");
    }

    public function deleteTag($id_tag) : void
    {
        $query = $this->connect()->query("
            DELETE FROM tags
            WHERE id_tag = " . $id_tag . "
        ");
    }
}