<?php

require_once BASE_PATH
    . '/config/database.php';


class Post
{
    private PDO $db;


    public function __construct()
    {
        $database =
            new Database();

        $this->db =
            $database->connect();
    }


    // ============================================================
    // OWNER - COUNT POSTS
    // ============================================================

    public function countForOwner(
        string $search = '',
        string $status = ''
    ): int {

        $params = [];

        $where = '1 = 1';


        if ($search !== '') {

            $where .= "
                AND (
                    p.title LIKE :search_title
                    OR p.excerpt LIKE :search_excerpt
                )
            ";

            $searchValue = '%' . $search . '%';

            $params['search_title'] = $searchValue;
            $params['search_excerpt'] = $searchValue;
        }


        if (
            in_array(
                $status,
                [
                    'draft',
                    'published',
                    'hidden'
                ],
                true
            )
        ) {

            $where .= "
                AND p.status = :status
            ";

            $params['status'] =
                $status;
        }


        $stmt =
            $this->db->prepare("
                SELECT COUNT(*)

                FROM posts p

                WHERE {$where}
            ");


        $stmt->execute($params);


        return (int)
            $stmt->fetchColumn();
    }


    // ============================================================
    // OWNER - GET POSTS
    // ============================================================

    public function getForOwner(
        string $search = '',
        string $status = '',
        int $limit = 15,
        int $offset = 0
    ): array {

        $params = [];

        $where = '1 = 1';


        if ($search !== '') {

            $where .= "
                AND (
                    p.title LIKE :search_title
                    OR p.excerpt LIKE :search_excerpt
                )
            ";

            $searchValue = '%' . $search . '%';

            $params['search_title'] = $searchValue;
            $params['search_excerpt'] = $searchValue;
        }


        if (
            in_array(
                $status,
                [
                    'draft',
                    'published',
                    'hidden'
                ],
                true
            )
        ) {

            $where .= "
                AND p.status = :status
            ";

            $params['status'] =
                $status;
        }


        $limit =
            max(
                1,
                $limit
            );


        $offset =
            max(
                0,
                $offset
            );


        $sql = "
            SELECT
                p.*,

                pc.name AS category_name,
                pc.slug AS category_slug,

                u.full_name AS author_name

            FROM posts p

            LEFT JOIN post_categories pc
                ON pc.id = p.category_id

            INNER JOIN users u
                ON u.id = p.author_id

            WHERE {$where}

            ORDER BY p.created_at DESC

            LIMIT {$limit}
            OFFSET {$offset}
        ";


        $stmt =
            $this->db->prepare($sql);


        $stmt->execute($params);


        return $stmt->fetchAll();
    }


    // ============================================================
    // FIND BY ID
    // ============================================================

    public function findById(
        int $id
    ): ?array {

        $stmt =
            $this->db->prepare("
                SELECT
                    p.*,

                    pc.name AS category_name,
                    pc.slug AS category_slug,

                    u.full_name AS author_name

                FROM posts p

                LEFT JOIN post_categories pc
                    ON pc.id = p.category_id

                INNER JOIN users u
                    ON u.id = p.author_id

                WHERE p.id = :id

                LIMIT 1
            ");


        $stmt->execute([
            'id' => $id
        ]);


        $post =
            $stmt->fetch();


        return $post ?: null;
    }


    // ============================================================
    // CHECK SLUG
    // ============================================================

    public function slugExists(
        string $slug,
        ?int $excludeId = null
    ): bool {

        $sql = "
            SELECT COUNT(*)

            FROM posts

            WHERE slug = :slug
        ";


        $params = [
            'slug' => $slug
        ];


        if ($excludeId !== null) {

            $sql .= "
                AND id != :exclude_id
            ";

            $params['exclude_id'] =
                $excludeId;
        }


        $stmt =
            $this->db->prepare($sql);


        $stmt->execute($params);


        return (int)
            $stmt->fetchColumn() > 0;
    }


    // ============================================================
    // CREATE POST
    // ============================================================

    public function create(
        array $data
    ): int {

        $publishedAt =
            $data['status'] === 'published'
                ? date('Y-m-d H:i:s')
                : null;


        $stmt =
            $this->db->prepare("
                INSERT INTO posts (
                    category_id,
                    author_id,
                    title,
                    slug,
                    excerpt,
                    content,
                    featured_image,
                    status,
                    published_at
                )
                VALUES (
                    :category_id,
                    :author_id,
                    :title,
                    :slug,
                    :excerpt,
                    :content,
                    :featured_image,
                    :status,
                    :published_at
                )
            ");


        $stmt->execute([
            'category_id' =>
                !empty(
                    $data['category_id']
                )
                    ? (int)
                        $data['category_id']
                    : null,

            'author_id' =>
                (int)
                $data['author_id'],

            'title' =>
                $data['title'],

            'slug' =>
                $data['slug'],

            'excerpt' =>
                !empty(
                    $data['excerpt']
                )
                    ? $data['excerpt']
                    : null,

            'content' =>
                $data['content'],

            'featured_image' =>
                !empty(
                    $data['featured_image']
                )
                    ? $data['featured_image']
                    : null,

            'status' =>
                $data['status'],

            'published_at' =>
                $publishedAt
        ]);


        return (int)
            $this->db->lastInsertId();
    }


    // ============================================================
    // UPDATE POST
    // ============================================================

    public function updatePost(
        int $id,
        array $data
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE posts

                SET
                    category_id = :category_id,
                    title = :title,
                    slug = :slug,
                    excerpt = :excerpt,
                    content = :content,
                    featured_image = :featured_image,

                    published_at =
                        CASE

                            WHEN
                                :publish_status = 'published'
                                AND status != 'published'

                            THEN NOW()


                            WHEN
                                :publish_status_2 = 'published'
                                AND published_at IS NULL

                            THEN NOW()


                            ELSE published_at

                        END,

                    status = :status

                WHERE id = :id
            ");


        $stmt->execute([
            'category_id' =>
                !empty(
                    $data['category_id']
                )
                    ? (int)
                        $data['category_id']
                    : null,

            'title' =>
                $data['title'],

            'slug' =>
                $data['slug'],

            'excerpt' =>
                !empty(
                    $data['excerpt']
                )
                    ? $data['excerpt']
                    : null,

            'content' =>
                $data['content'],

            'featured_image' =>
                !empty(
                    $data['featured_image']
                )
                    ? $data['featured_image']
                    : null,

            'publish_status' =>
                $data['status'],

            'publish_status_2' =>
                $data['status'],

            'status' =>
                $data['status'],

            'id' =>
                $id
        ]);
    }


    // ============================================================
    // HIDE POST
    // ============================================================

    public function hide(
        int $id
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE posts

                SET status = 'hidden'

                WHERE id = :id
            ");


        $stmt->execute([
            'id' => $id
        ]);
    }

    // ============================================================
    // OWNER - DELETE POST
    // ============================================================

    public function deleteById(
        int $id
    ): void {

        $stmt =
            $this->db->prepare("
                DELETE FROM posts

                WHERE id = :id
            ");

        $stmt->execute([
            'id' => $id
        ]);
    }


    public function updateModerationStatus(
        int $id,
        string $status
    ): void {
        $stmt = $this->db->prepare(
            'UPDATE posts SET status = :status WHERE id = :id'
        );
        $stmt->execute([
            'status' => $status,
            'id' => $id
        ]);
    }


    // ============================================================
    // PUBLIC - COUNT PUBLISHED
    // ============================================================

    public function countPublished(
        string $search = '',
        int $categoryId = 0
    ): int {

        $params = [];


        $where = "
            p.status = 'published'

            AND p.published_at IS NOT NULL

            AND p.published_at <= NOW()
        ";


        if ($search !== '') {

            $where .= "
                AND (
                    p.title LIKE :search_title
                    OR p.excerpt LIKE :search_excerpt
                )
            ";

            $searchValue = '%' . $search . '%';

            $params['search_title'] = $searchValue;
            $params['search_excerpt'] = $searchValue;
        }


        if ($categoryId > 0) {

            $where .= "
                AND p.category_id = :category_id
            ";

            $params['category_id'] =
                $categoryId;
        }


        $stmt =
            $this->db->prepare("
                SELECT COUNT(*)

                FROM posts p

                WHERE {$where}
            ");


        $stmt->execute($params);


        return (int)
            $stmt->fetchColumn();
    }


    // ============================================================
    // PUBLIC - GET PUBLISHED POSTS
    // ============================================================

    public function getPublished(
        string $search = '',
        int $categoryId = 0,
        int $limit = 9,
        int $offset = 0
    ): array {

        $params = [];


        $where = "
            p.status = 'published'

            AND p.published_at IS NOT NULL

            AND p.published_at <= NOW()
        ";


        if ($search !== '') {

            $where .= "
                AND (
                    p.title LIKE :search_title
                    OR p.excerpt LIKE :search_excerpt
                )
            ";

            $searchValue = '%' . $search . '%';

            $params['search_title'] = $searchValue;
            $params['search_excerpt'] = $searchValue;
        }


        if ($categoryId > 0) {

            $where .= "
                AND p.category_id = :category_id
            ";

            $params['category_id'] =
                $categoryId;
        }


        $limit =
            max(
                1,
                $limit
            );


        $offset =
            max(
                0,
                $offset
            );


        $stmt =
            $this->db->prepare("
                SELECT
                    p.*,

                    pc.name AS category_name,
                    pc.slug AS category_slug,

                    u.full_name AS author_name

                FROM posts p

                LEFT JOIN post_categories pc
                    ON pc.id = p.category_id

                INNER JOIN users u
                    ON u.id = p.author_id

                WHERE {$where}

                ORDER BY
                    p.published_at DESC,
                    p.id DESC

                LIMIT {$limit}
                OFFSET {$offset}
            ");


        $stmt->execute($params);


        return $stmt->fetchAll();
    }


    // ============================================================
    // PUBLIC - FIND BY SLUG
    // ============================================================

    public function findPublishedBySlug(
        string $slug
    ): ?array {

        $stmt =
            $this->db->prepare("
                SELECT
                    p.*,

                    pc.name AS category_name,
                    pc.slug AS category_slug,

                    u.full_name AS author_name

                FROM posts p

                LEFT JOIN post_categories pc
                    ON pc.id = p.category_id

                INNER JOIN users u
                    ON u.id = p.author_id

                WHERE p.slug = :slug

                  AND p.status = 'published'

                  AND p.published_at IS NOT NULL

                  AND p.published_at <= NOW()

                LIMIT 1
            ");


        $stmt->execute([
            'slug' => $slug
        ]);


        $post =
            $stmt->fetch();


        return $post ?: null;
    }


    // ============================================================
    // PUBLIC - RELATED POSTS
    // ============================================================

    public function getRelatedPublished(
        ?int $categoryId,
        int $excludeId,
        int $limit = 3
    ): array {

        $params = [
            'exclude_id' =>
                $excludeId
        ];


        $where = "
            p.id != :exclude_id

            AND p.status = 'published'

            AND p.published_at IS NOT NULL

            AND p.published_at <= NOW()
        ";


        if ($categoryId !== null) {

            $where .= "
                AND p.category_id = :category_id
            ";

            $params['category_id'] =
                $categoryId;
        }


        $limit =
            max(
                1,
                $limit
            );


        $stmt =
            $this->db->prepare("
                SELECT
                    p.*,

                    pc.name AS category_name,
                    pc.slug AS category_slug

                FROM posts p

                LEFT JOIN post_categories pc
                    ON pc.id = p.category_id

                WHERE {$where}

                ORDER BY
                    p.published_at DESC,
                    p.id DESC

                LIMIT {$limit}
            ");


        $stmt->execute($params);


        return $stmt->fetchAll();
    }
}
