<?php

namespace Modules\Posts\Models;

use CodeIgniter\Model;

class PostsModel extends Model
{

  protected $table = 'posts';
    protected $primaryKey = 'id';

    protected $allowedFields = ['title', 'alias', 'photo','file', 'content','excerpt', 'category_id', 'author_id', 'menu_order', 'menu_link', 'page_type', 'status', 'is_block', 'access', 'hits', 'views', 'tags', 'urls', 'metadesc', 'metakey', 'published_date', 'created_by', 'updated_at', 'updated_by'];

    protected $returnType     = 'array';
    protected $useTimestamps  = true;
    protected $useSoftDeletes = true;
    
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';

    public function searchPosts($searchTerm)
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id');
        if (!empty($searchTerm)) {
          $builder->like('p.title', $searchTerm)
                  ->orWhere('p.alias', $searchTerm)
                  ->orWhere('p.photo', $searchTerm)
                  ->orWhere('p.published_date', $searchTerm)
                  ->orWhere('p.excerpt', $searchTerm)
                  ->orWhere('pc.name', $searchTerm)
                  ->orWhere('pmc.name', $searchTerm)
                  ->groupBy('p.id');
        }
        $query = $builder->get();
        return $query->getResultArray();
    }

    //Get all Post Categories
    public function getAll()
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category_name, first_name, last_name, email, ');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->orderBy('id', 'DESC');
		  $result = $builder->get()->getResult();
		  return $result;
    }
    public function getSinglePost($id)
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category_name, first_name, last_name, email, ');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.id', $id);
      $builder->orWhere('p.alias', $id);
		  $result = $builder->get()->getRow();
		  return $result;
    }
    //Published Cat Posts
    public function getPublishedPosts($id)
    {
      $builder = $this->db->table('posts');
      $builder->where('id', $id);
      $builder->where('status', 'Published');
      echo $builder->countAll();
    }

    

    //Get all POSTS
    public function getAllPosts()
    {
      $page = [1];
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category_name, first_name, last_name, email, ');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.deleted_at', null);
      $builder->where('is_block', 'No');
      $builder->whereNotIn('p.category_id', $page);
      $builder->orderBy('id', 'DESC');
		  $result = $builder->get()->getResult();
		  return $result;
    }
    
    //Get all EDITOR's POSTS
    public function getAllEditorPosts()
    {
      $page = [1];
      $editor = [3,4];
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category_name, first_name, last_name, email, ');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left' );
        $builder->join('user_groups as ug', 'ug.id=uag.group_id');
      $builder->where('p.deleted_at', null);
      $builder->where('is_block', 'No');
      $builder->whereIn('uag.group_id', [3, 4]);
      $builder->whereNotIn('p.category_id', $page);
      $builder->orderBy('id', 'DESC');
		  $result = $builder->get()->getResult();
		  return $result;
    }

    //Get all Block Posts
    public function getAllBlockPosts()
    {
      $page = [1];
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category_name, first_name, last_name, email, ');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.deleted_at', null);
      $builder->where('is_block', 'Yes');
      $builder->whereNotIn('p.category_id', $page);
      $builder->orderBy('id', 'DESC');
		  $result = $builder->get()->getResult();
		  return $result;
    }

    public function getAllBlockPages()
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category_name, first_name, last_name, email, ');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.deleted_at', null);
      $builder->where('is_block', 'Yes');
      $builder->where('p.category_id', 1);
      $builder->orderBy('id', 'DESC');
		  $result = $builder->get()->getResult();
		  return $result;
    }
    
    //Get all Block Posts
    public function getAllEditorBlockPosts()
    {
      $page = [1];
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category_name, first_name, last_name, email, ');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left' );
      $builder->join('user_groups as ug', 'ug.id=uag.group_id');
      $builder->where('p.deleted_at', null);
      $builder->where('is_block', 'Yes');
      $builder->whereNotIn('p.category_id', $page);
      $builder->whereIn('uag.group_id', [3, 4]);
      $builder->orderBy('id', 'DESC');
		  $result = $builder->get()->getResult();
		  return $result;
    }

    //This section combines block po
    public function getAllSitePosts()
    {
      $page = [1];
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category_name, first_name, last_name, email, ');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.deleted_at', null);
      $builder->whereNotIn('p.category_id', $page);
      $builder->orderBy('id', 'DESC');
		  $result = $builder->get()->getResult();
		  return $result;
    }

    public function getHomeServices()
    {
      $pages = [2,3,4,5,6,7];
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, first_name, last_name, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id', 'left');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id', 'left');
      $builder->join('usc_users as u', 'u.id=p.author_id', 'left');
      $builder->where('p.deleted_at', null);
      $builder->where('p.category_id', 1);
      $builder->whereIn('p.id', $pages);
      $builder->orderBy('RAND()');
		  $result = $builder->get()->getResult();
		  return $result;
    }


    //Get all PAGES
    public function getAllPages()
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category_name, first_name, last_name, email, ');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.deleted_at', null);
      $builder->where('p.category_id', 1);
      $builder->orderBy('id', 'DESC');
		  $result = $builder->get()->getResult();
		  return $result;
    }

    //Get Latest Posts (Max 5)
    public function getLatestPosts()
    {
      $builder = $this->db->table('posts as p');
      $builder->where('p.deleted_at', null);
      $builder->orderBy('p.id', 'DESC');
      $builder->limit(5);
		  $result = $builder->get()->getResult();
		  return $result;
    }

    //Get Latest Posts (Max 5)
    public function getPopularPosts()
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*');
      $builder->where('p.deleted_at', null);
      $builder->orderBy('p.hits', 'DESC');
      $builder->limit(5);
		  $result = $builder->get()->getResult();
		  return $result;
    }

    //Get latest Posts
    public function getAllPostsForHome()
    {
      $cat = [2,3];
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, first_name, last_name, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id', 'left');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id', 'left');
      $builder->join('usc_users as u', 'u.id=p.author_id', 'left');
      $builder->where('p.deleted_at', null);
      $builder->whereIn('p.category_id', $cat);
      $builder->orderBy('published_date', 'DESC');
      $builder->limit(3);
		  $result = $builder->get()->getResult();
		  return $result;
    }

	//Get Latest Posts in Category News & Blog
	public function getAllPostsForNewsBlog()
    {
      $page = [2,3];
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, first_name, last_name, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id', 'left');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id', 'left');
      $builder->join('usc_users as u', 'u.id=p.author_id', 'left');
      $builder->where('p.deleted_at', null);
      $builder->whereIn('p.category_id', $page);
      $builder->orderBy('published_date', 'DESC');
      $builder->limit(3);
		  $result = $builder->get()->getResult();
		  return $result;
    }
	
    //Frontside of Website Post page
    public function showSinglePost($id)
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, first_name, last_name, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id', 'left');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id', 'left');
      $builder->join('usc_users as u', 'u.id=p.author_id', 'left');
      $builder->where('p.id', $id);
      $builder->orWhere('p.alias', $id);
		  $result = $builder->get()->getRow();
		  return $result;
    }

    //Get all Posts in YIM starting from current
    public function getRelatedPostsForSinglePage($post)
	{
		$page = [2, 3];
		$builder = $this->db->table('posts as p');
		$builder->select('p.*, pc.name as category, pc.alias as category_alias, first_name, last_name, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
		$builder->join('post_category as pc', 'pc.id=p.category_id');
		$builder->join('post_category as pmc', 'pmc.id=pc.master_category_id', 'left');
		$builder->join('usc_users as u', 'u.id=p.author_id');
		$builder->where('p.deleted_at', null);
		$builder->whereIn('p.category_id', $page);
		
		// Group the exclusion conditions properly
		$builder->groupStart()
				->where('p.id !=', $post)
				->where('p.alias !=', $post)
				->groupEnd();
		
		$builder->orderBy('RAND()');
		$builder->limit(5);
		$result = $builder->get()->getResult();
		return $result;
	}

    //Get single Main Category Posts
    public function getSingleMainCategoryPosts($category, $start, $limit)
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, first_name, last_name, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.deleted_at', null);
      $builder->where('pmc.id', $category);
      $builder->orWhere('pmc.alias', $category);
      $builder->orderBy('id', 'DESC');
      $builder->limit($limit, $start);
		  $result = $builder->get()->getResult();
		  return $result;
    }

    //Get single Main Category
    public function getSingleMainCategory($category)
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, first_name, last_name, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.deleted_at', null);
      $builder->where('pmc.id', $category);
      $builder->orWhere('pmc.alias', $category);
		  $result = $builder->get()->getRow();
		  return $result;
    }

    //Get Sub Categories with Posts under Main Category
    public function listSubCategoriesInMainCat($category)
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id');
      $builder->where('pmc.id', $category);
      $builder->orWhere('pmc.alias', $category);
      $builder->groupBy('p.category_id');
		  $result = $builder->get()->getResult();
		  return $result;
    }

    //Get single Sub Category Posts
    public function getSingleSubCategoryPosts($category, $start, $limit)
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, first_name, last_name, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.deleted_at', null);
      $builder->where('pc.id', $category);
      $builder->orWhere('pc.alias', $category);
      $builder->orderBy('id', 'DESC');
      $builder->limit($limit, $start);
		  $result = $builder->get()->getResult();
		  return $result;
    }
    
    //Get single Sub Category
    public function getSingleSubCategory($category)
    {
      $builder = $this->db->table('posts as p');
      $builder->select('p.*, pc.name as category, pc.alias as category_alias, first_name, last_name, pmc.master_category_id, pmc.name as master_category, pmc.alias as master_category_alias');
      $builder->join('post_category as pc', 'pc.id=p.category_id');
      $builder->join('post_category as pmc', 'pmc.id=pc.master_category_id');
      $builder->join('usc_users as u', 'u.id=p.author_id');
      $builder->where('p.deleted_at', null);
      $builder->where('pc.id', $category);
      $builder->orWhere('pc.alias', $category);
		  $result = $builder->get()->getRow();
		  return $result;
    }

}
