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

class Category extends Public_Controller
{
  public function __construct()
  {
    parent::__construct();
    $this->load->library('GoogleClient');
    $this->load->library('VisitorTracking');
    $this->load->model('Top_banner_m');
    $this->load->model('Review_m');
    $this->load->model('customer_m');
    $this->load->model('Footer_m');
    $this->load->model('Category_m');
    $this->load->model('Statistic_m');

    $loginUrl = $this->googleclient->getLoginUrl();

    $this->data_footer['googleUrl'] = $loginUrl;

    if ($this->session->userdata('site_lang') == 'english') {
      $this->lang->load('mainpage', 'english');
    } else {
      $this->lang->load('mainpage', 'indonesian');
    }
  }

  public function get($categoryAlias = null, $childAlias = null)
  {
    $this->load->helper('product');
    $this->visitortracking->trackVisitor();

    if ($this->session->userdata('customer')) {
      $id_customer = (int) $this->session->userdata('customer')['customer_id'];
      $this->data['customer'] = $this->customer_m->get_customer($id_customer);
    }

    if (
      $categoryAlias !== strtolower($categoryAlias) ||
      ($childAlias && $childAlias !== strtolower($childAlias))
    ) {

      $newUrl = "category/" . strtolower($categoryAlias);
      if ($childAlias) {
        $newUrl .= "/" . strtolower($childAlias);
      }
      redirect($newUrl);
    }

    $meta_description = ($this->session->userdata('site_lang') == 'english')
      ? "Explore a world full of pleasure. Discover products to make you more confident and satisfied, only at Laci Asmara!"
      : "Jelajahi dunia penuh kenikmatan. Temukan produk yang bikin kamu makin pede dan puas, hanya di Laci Asmara!";

    if ($categoryAlias === 'all') {
      // Fetch all active categories
      $categories = $this->db->select('*')
        ->from('categories')
        ->where('status', 1)
        ->order_by('priority', 'ASC')
        ->get()
        ->result();

      // Update header data with categories
      $this->data_header['categories'] = $categories;
      $this->data_header['browser_title'] .= ' - All Categories';
      $this->data_header['meta_description'] = $meta_description;

      // Load views for all categories page
      $this->load->view("themes/$this->theme_no/header_new", $this->data_header);
      $this->load->view('all-categories');
      $this->load->view("themes/$this->theme_no/footer_new", $this->data_footer);
    } else {
      // Check if the category is a main category (level 1) or a subcategory (level 2)
      $category = $this->getCategoryByAlias($categoryAlias, $childAlias);

      if (!$category) {
        // If the category is not found, display the page not found view
        $this->load->view("pagenotfound");
      } else {
        $query = $this->get_optimized_product_query($category['id_categories']);
        $this->db->cache_on();
        $products = $this->db->query($query)->result_array();
        $this->db->cache_off();

        $campaign = $this->campaignmanager->get_campaign_from_session();
        if ($campaign) {
          $this->load->model('Promotional_campaign_m');

          // Filter hanya produk yang eligible
          foreach ($products as $key => &$product) {
            $product_category = $this->db->select('id_category')
              ->from('category_product')
              ->where('id_product', $product['id_products'])
              ->get()
              ->row();

            if ($product_category) {
              $is_eligible = $this->Promotional_campaign_m->is_product_eligible(
                $campaign->id,
                $product['id_products'],
                $product_category->id_category
              );

              if ($is_eligible) {
                $product['is_campaign_eligible'] = true;
              }
            }
          }

          // Apply discount ke semua yang eligible sekaligus
          $products = $this->campaignmanager->apply_discount_to_products_array(
            $products,
            $campaign
          );
        }


        // Format data produk
        $formatted_products = $this->prepare_category_products($products);

        $this->data_header['browser_title'] .= ' - Category ' . $category['category'];
        $this->data_header['meta_description'] = $meta_description;

        $this->data_header['products'] = $formatted_products;

        // Load product list for the selected category
        $this->load->view("themes/3/header_new", $this->data_header);
        $this->load->view("themes/3/product_list", ['category' => $category]);
        $this->load->view("themes/3/footer_new", $this->data_footer);
      }
    }
  }

  private function prepare_category_products($products)
  {
    $customer = $this->session->userdata('customer');
    $is_reseller = false;
    $reseller_price_map = [];

    // Get reseller information (existing code)
    if (!empty($customer['customer_id'])) {
      $customer_id = $customer['customer_id'];
      $customer_data = $this->db->where('id_customers', $customer_id)->get('customers')->row_array();
      if (!empty($customer_data['reseller_id'])) {
        $reseller = $this->db->where('id_resellers', $customer_data['reseller_id'])->get('resellers')->row_array();
        if ($reseller) {
          $is_reseller = true;
          $reseller_prices = $this->db->where('reseller_id', $reseller['id_resellers'])->get('resellers_price')->result_array();
          foreach ($reseller_prices as $price) {
            $reseller_price_map[$price['product_detail_id']] = $price['price'];
          }
        }
      }
    }

    $formatted_products = [];

    foreach ($products as $product) {
      // Price calculations
      $is_discounted = $product['discounted_price'] > 0;
      $default_price = $product['price'];
      $discounted_price = $product['discounted_price'];
      $current_price = $is_discounted ? $discounted_price : $default_price;
      $savings_amount = ($discounted_price > 0 && $discounted_price < $default_price)
        ? ($default_price - $discounted_price)
        : 0;
      $msrp_price = null;

      // Handle reseller pricing
      if ($is_reseller && isset($reseller_price_map[$product['id_detail']])) {
        $msrp_price = $current_price;
        $current_price = $reseller_price_map[$product['id_detail']];
      }

      // Get reviews
      $review_data = $this->Review_m->get_product_reviews($product['id_products']);
      $average_rating = isset($review_data['average_rating']) ? round($review_data['average_rating'], 1) : 0;
      $total_reviews = isset($review_data['total_reviews']) ? $review_data['total_reviews'] : 0;

      // Parse variants
      $variants = !empty($product['variants']) ? explode('; ', $product['variants']) : [];

      $badges = $this->parse_badges_data($product['badges_data'] ?? '');
      $badges_names = $product['badges_names'] ?? '';

      // Stock calculations - all done in SQL now, just ensure non-negative values
      $current_variant_stock_sell = max(0, intval($product['current_variant_stock_sell'] ?? 0));
      $total_stock_sell = max(0, intval($product['total_stock_sell'] ?? 0));

      $formatted_products[] = [
        'id' => $product['id_products'],
        'title' => $product['title'],
        'id_detail' => $product['id_detail'],
        'alias' => $product['alias'],
        'sku' => $product['sku'],
        'current_price' => $current_price,
        'original_price' => $is_discounted ? $default_price : null,
        'msrp_price' => $msrp_price,
        'image' => $product['image'],
        'image_secondary' => $product['image_secondary'],
        'stock' => $product['total_stock'],
        'stock_sell' => $current_variant_stock_sell,
        'total_stock_sell' => $total_stock_sell,
        'average_rating' => $average_rating,
        'total_reviews' => $total_reviews,
        'variants' => $variants,
        'is_wishlisted' => $this->_check_wishlist_status($product['id_products'], $customer['customer_id'] ?? null),
        'created_at' => $product['created_at'],
        'brand_priority' => $product['brand_priority'],
        'savings_amount' => $savings_amount,
        'is_discounted' => $is_discounted,
        'discount_percentage' => $product['discount_percentage'],

        // Stock flags - now calculated in SQL
        'is_completely_sold_out' => (bool) $product['is_completely_sold_out'],
        'current_variant_out_of_stock' => (bool) $product['current_variant_out_of_stock'],
        'has_other_variants_available' => (bool) $product['has_other_variants_available'],

        // Additional stock information
        'total_variants' => (int) $product['total_variants'],
        'available_variants' => (int) $product['available_variants'],

        // Badges
        'badges' => $badges,
        'badges_names' => $badges_names, // Simple comma-separated names for quick display
        'badges_count' => (int) ($product['badges_count'] ?? 0),
        'has_badges' => !empty($badges),

        // New arrivals flag for badges
        'new_arrivals' => ($product['new_arrival'] === 'yes'),
        'best_seller' => ($product['best_seller'] === 'yes'),
        'popular_product' => ($product['popular_product'] === 'yes'),
      ];
    }

    return $formatted_products;
  }

  private function parse_badges_data($badges_data)
  {
    $badges = [];

    if (empty($badges_data)) {
      return $badges;
    }

    $badges_raw = explode('|', $badges_data);

    foreach ($badges_raw as $badge_raw) {
      if (empty($badge_raw)) continue;

      $badge_parts = explode(':', $badge_raw);

      if (count($badge_parts) >= 9) {
        $badges[] = [
          'id' => (int) $badge_parts[0],
          'name' => $badge_parts[1],
          'slug' => $badge_parts[2],
          'description' => $badge_parts[3],
          'background_color' => !empty($badge_parts[4]) ? $badge_parts[4] : '#FF6B6B',
          'text_color' => !empty($badge_parts[5]) ? $badge_parts[5] : '#FFFFFF',
          'icon' => $badge_parts[6],
          'position' => $badge_parts[7],
          'priority' => (int) $badge_parts[8],

          // Additional computed properties
          'css_class' => 'badge-' . $badge_parts[2],
          'has_icon' => !empty($badge_parts[6]),
          'style' => sprintf(
            'background-color: %s; color: %s;',
            !empty($badge_parts[4]) ? $badge_parts[4] : '#FF6B6B',
            !empty($badge_parts[5]) ? $badge_parts[5] : '#FFFFFF'
          )
        ];
      }
    }

    // Already sorted by priority in SQL, but double check
    usort($badges, function ($a, $b) {
      return $a['priority'] - $b['priority'];
    });

    return $badges;
  }

  private function get_optimized_product_query($id_category)
  {
    return "
      SELECT 
				p.id_products,
				p.title,
				p.alias,
				p.brand_id,
				p.created_at,
				p.best_seller,
				p.new_arrival,
				p.popular_product,
				p.priority,

				COALESCE(primary_variant.id, first_variant.id) AS id_detail,
				COALESCE(primary_variant.price, first_variant.price) AS price,
				COALESCE(primary_variant.discounted_price, first_variant.discounted_price) AS discounted_price,
				COALESCE(primary_variant.sku, first_variant.sku) AS sku,

				COALESCE(
					main_image.image,
					COALESCE(primary_variant_image.image, first_variant_image.image),
					p.image
				) AS image,
				COALESCE(
					main_image_secondary.image,
					COALESCE(primary_variant_image_secondary.image, first_variant_image_secondary.image),
					p.image1
				) AS image_secondary,

				stock_summary.total_stock,
				stock_summary.total_stock_keep,
				stock_summary.total_stock_sell,
				stock_summary.available_variants,
				stock_summary.total_variants,

				COALESCE(
					GREATEST(0, COALESCE(primary_variant_stock.stock, 0) - COALESCE(primary_variant_stock.stock_keep, 0)),
					GREATEST(0, COALESCE(first_variant_stock.stock, 0) - COALESCE(first_variant_stock.stock_keep, 0)),
					0
				) AS current_variant_stock_sell,

				CASE 
					WHEN stock_summary.total_stock_sell <= 0 THEN 1 
					ELSE 0 
				END AS is_completely_sold_out,

				CASE 
					WHEN COALESCE(
						GREATEST(0, COALESCE(primary_variant_stock.stock, 0) - COALESCE(primary_variant_stock.stock_keep, 0)),
						GREATEST(0, COALESCE(first_variant_stock.stock, 0) - COALESCE(first_variant_stock.stock_keep, 0)),
						0
					) <= 0 THEN 1 
					ELSE 0 
				END AS current_variant_out_of_stock,

				CASE 
					WHEN stock_summary.available_variants > 1 THEN 1 
					ELSE 0 
				END AS has_other_variants_available,

				COALESCE(primary_variant_attrs.variants, first_variant_attrs.variants, 'No variants available') AS variants,

				COALESCE(total_sales.total_sales, 0) AS total_sales,
				COALESCE(total_reviews.total_reviews, 0) AS total_reviews,
				COALESCE(reseller_price.price, 0) AS reseller_price,
				COALESCE(reseller_price.min_quantity, 0) AS reseller_min_quantity,
				COALESCE(b.priority, 0) AS brand_priority,

				-- Badges
				COALESCE(product_badges_data.badges_names, '') AS badges_names,
				COALESCE(product_badges_data.badges_data, '') AS badges_data,
				COALESCE(product_badges_data.badges_count, 0) AS badges_count,

				CASE
					WHEN COALESCE(primary_variant.discounted_price, first_variant.discounted_price) > 0 
						AND COALESCE(primary_variant.discounted_price, first_variant.discounted_price) < COALESCE(primary_variant.price, first_variant.price)
					THEN (COALESCE(primary_variant.price, first_variant.price) - COALESCE(primary_variant.discounted_price, first_variant.discounted_price))
					ELSE 0
				END AS savings_amount,

				CASE
					WHEN COALESCE(primary_variant.discounted_price, first_variant.discounted_price) > 0 
						AND COALESCE(primary_variant.discounted_price, first_variant.discounted_price) < COALESCE(primary_variant.price, first_variant.price)
					THEN ROUND(((COALESCE(primary_variant.price, first_variant.price) - COALESCE(primary_variant.discounted_price, first_variant.discounted_price)) / COALESCE(primary_variant.price, first_variant.price)) * 100, 0)
					ELSE 0
				END AS discount_percentage,

				CASE 
					WHEN stock_summary.total_stock_sell <= 0 THEN 1 
					ELSE 0 
				END AS sort_order

			FROM products p

			-- Stock Summary
			LEFT JOIN (
				SELECT 
					pd.product_id,
					COUNT(DISTINCT pd.id) AS total_variants,
					SUM(COALESCE(s.stock, 0)) AS total_stock,
					SUM(COALESCE(s.stock_keep, 0)) AS total_stock_keep,
					SUM(GREATEST(0, COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0))) AS total_stock_sell,
					SUM(CASE WHEN GREATEST(0, COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) > 0 THEN 1 ELSE 0 END) AS available_variants
				FROM product_details pd
				LEFT JOIN stock s ON pd.id = s.id_product_detail
				GROUP BY pd.product_id
			) stock_summary ON p.id_products = stock_summary.product_id

			-- Primary Variant
			LEFT JOIN (
				SELECT pd.*
				FROM product_details pd
				INNER JOIN (
					SELECT product_id, MIN(id) as min_id
					FROM product_details
					GROUP BY product_id
				) min_pd ON pd.product_id = min_pd.product_id AND pd.id = min_pd.min_id
			) primary_variant ON p.id_products = primary_variant.product_id

			-- First Variant (with max stock sell)
			LEFT JOIN (
				SELECT pd.*
				FROM product_details pd
				LEFT JOIN stock s ON pd.id = s.id_product_detail
				INNER JOIN (
					SELECT pd.product_id, MAX(GREATEST(0, COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0))) AS max_stock
					FROM product_details pd
					LEFT JOIN stock s ON pd.id = s.id_product_detail
					GROUP BY pd.product_id
				) max_stock_pd ON pd.product_id = max_stock_pd.product_id
				LEFT JOIN stock s2 ON pd.id = s2.id_product_detail
				WHERE GREATEST(0, COALESCE(s2.stock, 0) - COALESCE(s2.stock_keep, 0)) = max_stock_pd.max_stock
			) first_variant ON p.id_products = first_variant.product_id

			-- Stock
			LEFT JOIN stock primary_variant_stock ON primary_variant.id = primary_variant_stock.id_product_detail
			LEFT JOIN stock first_variant_stock ON first_variant.id = first_variant_stock.id_product_detail

			-- Images
			LEFT JOIN product_images main_image ON p.id_products = main_image.product_id 
				AND main_image.product_details_id = 0 AND main_image.priority = 1 AND main_image.status = '1'
			LEFT JOIN product_images main_image_secondary ON p.id_products = main_image_secondary.product_id 
				AND main_image_secondary.product_details_id = 0 AND main_image_secondary.priority = 2 AND main_image_secondary.status = '1'

			LEFT JOIN product_images primary_variant_image ON primary_variant.id = primary_variant_image.product_details_id 
				AND primary_variant_image.priority = 1 AND primary_variant_image.status = '1'
			LEFT JOIN product_images primary_variant_image_secondary ON primary_variant.id = primary_variant_image_secondary.product_details_id 
				AND primary_variant_image_secondary.priority = 2 AND primary_variant_image_secondary.status = '1'

			LEFT JOIN product_images first_variant_image ON first_variant.id = first_variant_image.product_details_id 
				AND first_variant_image.priority = 1 AND first_variant_image.status = '1'
			LEFT JOIN product_images first_variant_image_secondary ON first_variant.id = first_variant_image_secondary.product_details_id 
				AND first_variant_image_secondary.priority = 2 AND first_variant_image_secondary.status = '1'

			-- Variant Attributes
			LEFT JOIN (
				SELECT 
					pc.product_details_id,
					GROUP_CONCAT(DISTINCT CONCAT_WS(': ', pa.product_attribute, pad.attribute_detail) SEPARATOR '; ') AS variants
				FROM product_combination pc
				JOIN product_attributes pa ON pc.attribute_id = pa.id
				JOIN product_attributes_detail pad ON pc.attribute_detail_id = pad.id
				GROUP BY pc.product_details_id
			) primary_variant_attrs ON primary_variant.id = primary_variant_attrs.product_details_id

			LEFT JOIN (
				SELECT 
					pc.product_details_id,
					GROUP_CONCAT(DISTINCT CONCAT_WS(': ', pa.product_attribute, pad.attribute_detail) SEPARATOR '; ') AS variants
				FROM product_combination pc
				JOIN product_attributes pa ON pc.attribute_id = pa.id
				JOIN product_attributes_detail pad ON pc.attribute_detail_id = pad.id
				GROUP BY pc.product_details_id
			) first_variant_attrs ON first_variant.id = first_variant_attrs.product_details_id

			-- Badges
			LEFT JOIN (
				SELECT 
					pb.product_id,
					COUNT(pb.badge_id) AS badges_count,
					GROUP_CONCAT(b.name ORDER BY pb.priority ASC SEPARATOR ', ') AS badges_names,
					GROUP_CONCAT(
						CONCAT(
							b.id, ':', 
							b.name, ':', 
							b.slug, ':', 
							IFNULL(b.description, ''), ':', 
							IFNULL(b.background_color, '#FF6B6B'), ':', 
							IFNULL(b.text_color, '#FFFFFF'), ':', 
							IFNULL(b.icon, ''), ':', 
							pb.position, ':', 
							pb.priority
						)
						ORDER BY pb.priority ASC
						SEPARATOR '|'
					) AS badges_data
				FROM product_badges pb
				INNER JOIN badges b ON pb.badge_id = b.id
				WHERE pb.is_active = 1 
					AND b.is_active = 1
					AND (pb.start_date IS NULL OR pb.start_date <= NOW())
					AND (pb.end_date IS NULL OR pb.end_date >= NOW())
				GROUP BY pb.product_id
			) product_badges_data ON p.id_products = product_badges_data.product_id

			-- Additional Joins
			LEFT JOIN (
				SELECT item_id, SUM(quantity) AS total_sales 
				FROM orders_detail 
				WHERE warehouse_id = 1 
				GROUP BY item_id
			) total_sales ON COALESCE(primary_variant.id, first_variant.id) = total_sales.item_id

			LEFT JOIN (
				SELECT product_id, COUNT(id) AS total_reviews 
				FROM product_review 
				GROUP BY product_id
			) total_reviews ON p.id_products = total_reviews.product_id

			LEFT JOIN resellers_price reseller_price ON COALESCE(primary_variant.id, first_variant.id) = reseller_price.product_detail_id

			LEFT JOIN brands b ON p.brand_id = b.id_brands

			INNER JOIN category_product cp ON p.id_products = cp.id_product

      WHERE p.product_status = '1'
          AND p.deleted_at IS NULL
          AND cp.id_category = " . (int)$id_category . "
      GROUP BY
      p.id_products
          ORDER BY 
              sort_order ASC,
              p.created_at DESC,
              p.best_seller DESC,
              p.new_arrival DESC,
              p.popular_product DESC,
              p.priority DESC,
              COALESCE(b.priority, 0) DESC
    ";
  }

  private function _check_wishlist_status($product_id, $customer_id)
  {
    if (!$customer_id) return false;

    $exists = $this->db->where([
      'customer_id' => $customer_id,
      'product_id' => $product_id
    ])->get('wishlists')->num_rows();

    return $exists > 0;
  }
  /**
   * Get category by alias and handle child alias logic
   */
  private function getCategoryByAlias($categoryAlias, $childAlias = null)
  {
    if ($childAlias) {
      // Pertama cari parent category
      $parentQuery = $this->db->where('alias', $categoryAlias)
        ->or_where('alias_en', $categoryAlias)
        ->get('categories');
      $parentCategory = $parentQuery->row_array();

      if (!$parentCategory) {
        return null;
      }

      // Kemudian cari child yang parent_id nya sesuai
      $childQuery = $this->db->where('parent', $parentCategory['id_categories'])
        ->where('(alias = ' . $this->db->escape($childAlias) .
          ' OR alias_en = ' . $this->db->escape($childAlias) . ')')
        ->get('categories');
      return $childQuery->row_array();
    } else {
      // Tambah pengecekan apakah ini sebenarnya child category
      $category = $this->db->where('alias', $categoryAlias)
        ->or_where('alias_en', $categoryAlias)
        ->get('categories')
        ->row_array();

      if ($category && $category['parent']) {
        // Ini sebenarnya child category, redirect ke URL yang benar
        $parentCategory = $this->db->get_where(
          'categories',
          ['id_categories' => $category['parent']]
        )->row_array();

        if ($parentCategory) {
          redirect("category/{$parentCategory['alias']}/{$category['alias']}");
        }
      }

      return $category;
    }
  }

  public function index()
  {
    $this->visitortracking->trackVisitor();

    $categories = $this->db->select('*')
      ->from('categories')
      ->where('status', 1)
      ->order_by('priority', 'ASC')
      ->get()
      ->result();

    // Prepare header data
    $$this->data_header['browser_title'] .= ' - All Category';

    // Update header data with categories
    $this->data_header['categories'] = $categories;

    // Load views for all categories page
    $this->load->view("themes/$this->theme_no/header_new", $this->data_header);
    $this->load->view('all-categories');
    $this->load->view("themes/$this->theme_no/footer_new", $this->data_footer);
  }
}