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

/**
 * Promotional Campaign Model
 * 
 */
class Promotional_campaign_m extends CI_Model
{
    protected $table = 'promotional_campaigns';
    protected $primary_key = 'id';

    public function __construct()
    {
        parent::__construct();
    }
    /**
     * Insert new campaign
     * @param array $data
     * @return int|false Campaign ID or false on failure
     */
    public function insert_campaign($data)
    {
        if ($this->db->insert($this->table, $data)) {
            return $this->db->insert_id();
        }
        return false;
    }
    /**
     * Get campaign by code
     * 
     * @param string $code
     * @return object|null
     */
    public function get_by_code($code)
    {
        return $this->db
            ->where('code', $code)
            ->where('deleted_at IS NULL')
            ->get($this->table)
            ->row();
    }

    /**
     * Get campaign by ID
     * 
     * @param int $id
     * @return object|null
     */
    public function get_by_id($id)
    {
        return $this->db
            ->where($this->primary_key, $id)
            ->where('deleted_at IS NULL')
            ->get($this->table)
            ->row();
    }

    /**
     * Get all active campaigns
     * 
     * @return array
     */
    public function get_active_campaigns()
    {
        return $this->db
            ->where('is_active', 1)
            ->where('deleted_at IS NULL')
            ->where('start_date <=', date('Y-m-d H:i:s'))
            ->where('end_date >=', date('Y-m-d H:i:s'))
            ->order_by('priority', 'DESC')
            ->get($this->table)
            ->result();
    }

    public function get_campaign_products($campaign_id, $options = [])
    {
        $campaign = $this->get_by_id($campaign_id);

        if (!$campaign) {
            return [];
        }

        // Start building query (pakai product_details & stock)
        $this->db->select("
            p.id_products,
            p.title,
            p.alias,
            p.brand_id,
            p.created_at,
            pd.id as product_detail_id,
            pd.price,
            pd.discounted_price,
            pd.sku,
            COALESCE(s.stock, 0) as stock,
            COALESCE(s.stock_keep, 0) as stock_keep,
            (GREATEST(0, COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0))) as stock_sell,
            c.category as category_name
        ");
        $this->db->from('products p');
        $this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
        $this->db->join('stock s', 'pd.id = s.id_product_detail', 'left');
        $this->db->join('category_product cp', 'p.id_products = cp.id_product', 'left');
        $this->db->join('categories c', 'cp.id_category = c.id_categories', 'left');

        // Only active + not deleted
        $this->db->where('p.product_status', '1');
        $this->db->where('p.deleted_at IS NULL', null, false);

        // Only products with stock to sell
        $this->db->where('(GREATEST(0, COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0))) >', 0);

        // Apply targeting filters
        $this->_apply_targeting_filters($campaign);

        $this->db->group_by('p.id_products');

        // Apply ordering
        $this->_apply_ordering($options['order_by'] ?? 'newest');

        // Apply pagination
        if (isset($options['limit'])) {
            $this->db->limit($options['limit'], $options['offset'] ?? 0);
        }

        return $this->db->get()->result();
    }


    /**
     * Apply targeting filters to query
     * 
     * @param object $campaign
     * @return void
     */
    private function _apply_targeting_filters($campaign)
    {
        // 1. Exclude specific products (highest priority)
        if (!empty($campaign->excluded_products)) {
            $excluded = json_decode($campaign->excluded_products, true);
            if (is_array($excluded) && count($excluded) > 0) {
                $this->db->where_not_in('p.id_products', $excluded);
            }
        }

        // 2. Filter by specific products (whitelist)
        if (!empty($campaign->applicable_products)) {
            $products = json_decode($campaign->applicable_products, true);
            if (is_array($products) && count($products) > 0) {
                $this->db->where_in('p.id_products', $products);
                return; // If specific products defined, ignore categories
            }
        }

        // 3. Filter by categories
        if (!empty($campaign->applicable_categories)) {
            $categories = json_decode($campaign->applicable_categories, true);
            if (is_array($categories) && count($categories) > 0) {
                $this->db->where_in('cp.id_category', $categories);
            }
        }

        // If all targeting fields are NULL/empty, all products are eligible
    }

    /**
     * Apply ordering to query
     * 
     * @param string $order_by
     * @return void
     */
    private function _apply_ordering($order_by)
    {
        switch ($order_by) {
            case 'newest':
                $this->db->order_by('p.created_at', 'DESC');
                break;

            case 'price_low':
                $this->db->order_by('pd.price', 'ASC'); // ganti ke pd
                break;

            case 'price_high':
                $this->db->order_by('pd.price', 'DESC'); // ganti ke pd
                break;

            case 'name':
                $this->db->order_by('p.title', 'ASC'); // sesuaikan field (bukan name)
                break;

            default:
                $this->db->order_by('p.created_at', 'DESC');
                break;
        }
    }


    /**
     * Check if product is eligible for campaign
     * 
     * @param int $campaign_id
     * @param int $product_id
     * @param int $category_id
     * @return bool
     */
    public function is_product_eligible($campaign_id, $product_id, $category_id = null)
    {
        $campaign = $this->get_by_id($campaign_id);

        if (!$campaign) {
            return false;
        }

        // Check excluded products first
        if (!empty($campaign->excluded_products)) {
            $excluded = json_decode($campaign->excluded_products, true);
            if (is_array($excluded) && in_array($product_id, $excluded)) {
                return false;
            }
        }

        // Check applicable products
        if (!empty($campaign->applicable_products)) {
            $products = json_decode($campaign->applicable_products, true);
            return is_array($products) && in_array($product_id, $products);
        }

        // Check applicable categories
        if (!empty($campaign->applicable_categories)) {
            $categories = json_decode($campaign->applicable_categories, true);
            return is_array($categories) && in_array($category_id, $categories);
        }

        // Default: all products eligible
        return true;
    }

    /**
     * Increment campaign usage counter
     * 
     * @param int $campaign_id
     * @return bool
     */
    public function increment_usage($campaign_id)
    {
        $this->db->set('current_usage', 'current_usage + 1', false);
        $this->db->where($this->primary_key, $campaign_id);
        return $this->db->update($this->table);
    }

    /**
     * Get campaign statistics
     * 
     * @param int $campaign_id
     * @return object
     */
    public function get_campaign_stats($campaign_id)
    {
        // Views
        $views = $this->db
            ->where('campaign_id', $campaign_id)
            ->where('action_type', 'view')
            ->count_all_results('promotional_campaign_logs');

        // Product clicks
        $clicks = $this->db
            ->where('campaign_id', $campaign_id)
            ->where('action_type', 'click_product')
            ->count_all_results('promotional_campaign_logs');

        // Add to cart
        $add_to_cart = $this->db
            ->where('campaign_id', $campaign_id)
            ->where('action_type', 'add_to_cart')
            ->count_all_results('promotional_campaign_logs');

        // Conversions (orders)
        $conversions = $this->db
            ->where('campaign_id', $campaign_id)
            ->count_all_results('promotional_campaign_usage');

        // Total revenue with discount
        $revenue_query = $this->db
            ->select_sum('discount_amount')
            ->where('campaign_id', $campaign_id)
            ->get('promotional_campaign_usage')
            ->row();

        return (object) [
            'views' => $views,
            'clicks' => $clicks,
            'add_to_cart' => $add_to_cart,
            'conversions' => $conversions,
            'total_discount_given' => $revenue_query->discount_amount ?? 0,
            'conversion_rate' => $views > 0 ? round(($conversions / $views) * 100, 2) : 0,
        ];
    }

    /**
     * Insert new campaign
     * 
     * @param array $data
     * @return int|bool
     */
    public function insert($data)
    {
        $data['created_at'] = date('Y-m-d H:i:s');

        if ($this->db->insert($this->table, $data)) {
            return $this->db->insert_id();
        }

        return false;
    }

    /**
     * Update campaign
     * 
     * @param int $id
     * @param array $data
     * @return bool
     */
    public function update_campaign($id, $data)
    {
        $data['updated_at'] = date('Y-m-d H:i:s');

        $this->db->where($this->primary_key, $id);
        return $this->db->update($this->table, $data);
    }

    /**
     * Soft delete campaign
     * 
     * @param int $id
     * @return bool
     */
    public function delete_campaign($id)
    {
        $this->db->where($this->primary_key, $id);
        return $this->db->update($this->table, [
            'deleted_at' => date('Y-m-d H:i:s')
        ]);
    }
}