<?php

namespace pictpostpersonal\model;

use pictpostpersonal\SQLiteCRUD;
use pictpostpersonal\SQL;
use pictpostpersonal\PPPDateTime;

/**
 * メッセージを扱うクラス。
 */
class Message
{
    private $sqlcrud;
    private $db;

    public function __construct($db)
    {
        $this->db = $db;
        $this->sqlcrud = new SQLiteCRUD($db);
    }

    public static function initialize(SQLiteCRUD $sql_crud)
    {
        $sql_crud->table('messages')->createTable(
            [
                SQL::Id('id'),
                SQL::Integer('article_id'),
                SQL::Integer('parent_id', ''),
                SQL::Text('name', '', 'NOT NULL'),
                SQL::Text('email'),
                SQL::Text('message'),
                SQL::Text('from_ip'),
                SQL::DateTime('created_at', ''),
                SQL::Integer('is_approved', 0),
                SQL::Integer('from_admin', 0),
                SQL::Integer('read', 0),
            ],
            [
                'article_id',
            ]
        );
    }

    /**
     * 新しいメッセージを保存します
     *
     * @param $article_id
     * @param $name
     * @param $email
     * @param $message
     * @param null $parent_id
     * @param bool $from_admin
     * 
     * @return int
     * 
     */
    public function create($article_id, $name, $email, $message, $parent_id = null, $from_admin = false): int
    {
        $approved = false;
        if ($from_admin) {
            $approved = true;
        }
        date_default_timezone_set('Asia/Tokyo');
        $date = PPPDateTime::Date('Y-m-d H:i:s');
        $data = [
            'article_id' => strip_tags($article_id),
            'parent_id' => strip_tags($parent_id ?? ''),
            'name' => htmlspecialchars($name),
            'email' => htmlspecialchars($email),
            'message' => htmlspecialchars($message),
            'from_admin' => $from_admin,
            'created_at' => $date,
            'is_approved' => $approved,
            'from_ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknonw'
        ];
        $id = $this->sqlcrud->table('messages')->create($data);

        //アクティビティを記録
        if (!$from_admin) {
            $activity = new Activity($this->db);
            $activity->create(Activity::ACTIVITY_ACTION_MESSAGE, $article_id);
        }

        return $id;
    }

    public function delete($message_id)
    {
        $this->sqlcrud->table('messages')->where('id=:id', ['id' => $message_id])->delete();
    }

    public function deletes($message_ids)
    {
        $message_ids = array_filter($message_ids, function ($id) {
            return is_numeric($id) && intval($id) > 0;
        });
        if (empty($message_ids)) {
            return;
        }
        $in = implode(',', $message_ids);
        $this->sqlcrud->table('messages')->where("id IN ($in)")->delete();
    }

    public function approve($message_id)
    {
        $this->sqlcrud->table('messages')->where('id=:id', ['id' => $message_id])->update(['is_approved' => 1]);
    }

    public function disapprove($message_id)
    {
        $this->sqlcrud->table('messages')->where('id=:id', ['id' => $message_id])->update(['is_approved' => 0]);
    }

    public function read($message_id)
    {
        $this->sqlcrud->table('messages')->where('id=:id', ['id' => $message_id])->update(['read' => 1]);
    }

    public function disread($message_id)
    {
        $this->sqlcrud->table('messages')->where('id=:id', ['id' => $message_id])->update(['read' => 0]);
    }

    public function search(string $query, array $conditions, string $sort, $page_par_count = -1, $page = -1)
    {
        $offset = $page_par_count * ($page - 1);
        return $this->sqlcrud->table('messages')->orderBy($sort)->where($query, $conditions)->fetch($page_par_count, $offset);
    }

    public function getCount($query, $conditions)
    {
        $count = $this->sqlcrud->table('messages')->where($query, $conditions)->count();
        return $count;
    }

    public function searchWithTotalCount(string $query, array $conditions, string $sort, $page_par_count = -1, $page = -1)
    {
        return [
            self::Search($query, $conditions,  $sort,  $page_par_count, $page),
            self::getCount($query, $conditions)
        ];
    }

    public function getUnreadMessages($limit, $page, $sort, $query = '', $values = [])
    {
        $and = '';
        if ($query != '') {
            $and = ' and ';
        }
        $query = $query . $and . 'read = false';
        $offset = $limit * ($page - 1);
        return $this->sqlcrud->table('messages')->orderBy($sort)->where($query, $values)->fetch($limit, $offset);
    }
    public function getUnreadCommentCount($query = '', $values = [])
    {
        $and = '';
        if ($query != '') {
            $and = ' and ';
        }
        $query = $query . $and . 'read = false';
        return $this->sqlcrud->table('messages')->where($query, $values)->count();
    }
    public function getUnreadMessagesWithTotalCount($limit, $page, $sort, $query = '', $values = [])
    {
        $and = '';
        if ($query != '') {
            $and = ' and ';
        }
        $query = $query . $and . 'read = false';
        return [
            $this->getUnreadMessages($limit, $page, $query, $values),
            $this->sqlcrud->table('messages')->where($query, $values)->orderBy($sort)->fetch()
        ];
    }


    /**
     * 指定された記事IDに基づくコメント一覧を取得する
     *
     * @param int $articleId    記事ID。システムコメントの場合 -1。
     * @param bool $show_unapproved 承認されていないコメントを表示するか。デフォルトfalse。
     * @param int   $count  取得件数。デフォルト全件。
     * @param int   $page   取得するページ。
     * @return array    コメントツリーを格納した配列
     *
     */

    public function getMessagesByArticleId($article_id, bool $show_unapproved = false): array
    {
        return $this->fetchMessages($article_id, $show_unapproved);
    }
    private function fetchMessages($article_id, bool $show_unapproved = false, $parent_id = -1)
    {
        $query = '';
        if ($show_unapproved) {
            $query =  'article_id = :article_id AND  parent_id = :parent_id';
        } else {
            $query =  'article_id = :article_id AND is_approved = 1 AND  parent_id = :parent_id';
        }
        $result = [];
        $messages = $this->sqlcrud->table('messages')->orderBy('created_at ASC')->where($query, ['article_id' => $article_id, 'parent_id' => $parent_id])->fetch();
        foreach ($messages as $message) {
            $message['replies'] = $this->fetchMessages($article_id, $show_unapproved, $message['id']);
            $result[] = $message;
        }
        return $result;
    }
}
