<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Products_m extends MY_Model {

    protected $table = 'products';
    protected $primary_key = 'productId';

    public function __construct() {
        parent::__construct();
    }

    /**
     * Get active products with category and discount info
     */
    public function getActiveProducts($limit = null, $order_by = 'productDate', $order = 'DESC') {
        $this->db->select('products.*, categories.category_name, discounts.discount_percentage, discounts.discount_name');
        $this->db->from('products');
        $this->db->join('categories', 'categories.categoryUnique = products.categoryUnique', 'left');
        $this->db->join('discounts', 'discounts.discountUnique = products.discountUnique', 'left');
        $this->db->where('products.deleted', 0);
        $this->db->where('products.product_status', 1);
        $this->db->order_by($order_by, $order);
        
        if ($limit) {
            $this->db->limit($limit);
        }
        
        return $this->db->get()->result();
    }

    /**
     * Get featured/bestseller products for homepage
     */
    public function getFeaturedProducts($limit = 8) {
        return $this->getActiveProducts($limit);
    }

    /**
     * Get new arrivals (latest products)
     */
    public function getNewArrivals($limit = 8) {
        return $this->getActiveProducts($limit, 'productDate', 'DESC');
    }

    /**
     * Get products by category
     */
    public function getByCategory($categoryUnique, $limit = null) {
        $this->db->select('products.*, categories.category_name, discounts.discount_percentage, discounts.discount_name');
        $this->db->from('products');
        $this->db->join('categories', 'categories.categoryUnique = products.categoryUnique', 'left');
        $this->db->join('discounts', 'discounts.discountUnique = products.discountUnique', 'left');
        $this->db->where('products.categoryUnique', $categoryUnique);
        $this->db->where('products.deleted', 0);
        $this->db->where('products.product_status', 1);
        $this->db->order_by('products.productDate', 'DESC');
        
        if ($limit) {
            $this->db->limit($limit);
        }
        
        return $this->db->get()->result();
    }

    /**
     * Get product by slug and unique
     */
    public function getBySlug($slug, $productUnique) {
        $this->db->select('products.*, categories.category_name, discounts.discount_percentage, discounts.discount_name');
        $this->db->from('products');
        $this->db->join('categories', 'categories.categoryUnique = products.categoryUnique', 'left');
        $this->db->join('discounts', 'discounts.discountUnique = products.discountUnique', 'left');
        $this->db->where('products.slug', $slug);
        $this->db->where('products.productUnique', $productUnique);
        $this->db->where('products.deleted', 0);
        
        return $this->db->get()->row();
    }

    /**
     * Get product by unique
     */
    public function getByUnique($productUnique) {
        $this->db->select('products.*, categories.category_name, discounts.discount_percentage, discounts.discount_name');
        $this->db->from('products');
        $this->db->join('categories', 'categories.categoryUnique = products.categoryUnique', 'left');
        $this->db->join('discounts', 'discounts.discountUnique = products.discountUnique', 'left');
        $this->db->where('products.productUnique', $productUnique);
        $this->db->where('products.deleted', 0);
        
        return $this->db->get()->row();
    }

    /**
     * Get product photos
     */
    public function getProductPhotos($productUnique) {
        return $this->db->where('productUnique', $productUnique)->get('product_photos')->result();
    }

    /**
     * Get products on sale (with discount)
     */
    public function getOnSaleProducts($limit = 8) {
        $this->db->select('products.*, categories.category_name, discounts.discount_percentage, discounts.discount_name');
        $this->db->from('products');
        $this->db->join('categories', 'categories.categoryUnique = products.categoryUnique', 'left');
        $this->db->join('discounts', 'discounts.discountUnique = products.discountUnique', 'left');
        $this->db->where('products.deleted', 0);
        $this->db->where('products.product_status', 1);
        $this->db->where('products.discountUnique IS NOT NULL');
        $this->db->order_by('products.productDate', 'DESC');
        
        if ($limit) {
            $this->db->limit($limit);
        }
        
        return $this->db->get()->result();
    }

    /**
     * Search products
     */
    public function searchProducts($keyword, $limit = null) {
        $this->db->select('products.*, categories.category_name, discounts.discount_percentage, discounts.discount_name');
        $this->db->from('products');
        $this->db->join('categories', 'categories.categoryUnique = products.categoryUnique', 'left');
        $this->db->join('discounts', 'discounts.discountUnique = products.discountUnique', 'left');
        $this->db->where('products.deleted', 0);
        $this->db->where('products.product_status', 1);
        $this->db->group_start();
        $this->db->like('products.name', $keyword);
        $this->db->or_like('products.description', $keyword);
        $this->db->or_like('products.short_description', $keyword);
        $this->db->group_end();
        $this->db->order_by('products.productDate', 'DESC');
        
        if ($limit) {
            $this->db->limit($limit);
        }
        
        return $this->db->get()->result();
    }

    /**
     * Get product reviews
     */
    public function getProductReviews($productUnique) {
        $this->db->select('reviews.*, users.firstname, users.lastname');
        $this->db->from('reviews');
        $this->db->join('users', 'users.userUnique = reviews.userUnique', 'left');
        $this->db->where('reviews.productUnique', $productUnique);
        $this->db->order_by('reviews.reviewDateAdded', 'DESC');
        
        return $this->db->get()->result();
    }

    /**
     * Get average rating for product
     */
    public function getAverageRating($productUnique) {
        $this->db->select_avg('review_rating', 'average_rating');
        $this->db->select('COUNT(*) as total_reviews', false);
        $this->db->where('productUnique', $productUnique);
        return $this->db->get('reviews')->row();
    }
}

