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

class Statistic_m extends MY_Model
{

    protected $_table_name = 'link_tracks';
    protected $_primary_key = 'id';
    protected $_order_by = 'id';

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

    public function get_today_traffic()
    {
        $this->db->from('link_tracks');
        $this->db->where('DATE(click_date)', 'CURDATE()', false); // Gunakan CURDATE() langsung tanpa escaping
        return $this->db->count_all_results();
    }

    public function get_today_unique_traffic()
    {
        $this->db->select('COUNT(DISTINCT ip_address) AS total_unique_traffic', false);
        $this->db->from('link_tracks');
        $this->db->where('DATE(click_date)', 'CURDATE()', false);

        $query = $this->db->get();
        return $query->row()->total_unique_traffic ?? 0;
    }


    public function get_today_revenue()
    {
        $this->db->select('SUM(grand_total_amount) AS total_revenue', false);
        $this->db->from('orders');
        $this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left'); // Join dengan tabel customers

        $this->db->where('DATE(order_date)', 'CURDATE()', false); // Hanya pesanan hari ini
        $this->db->where('payment_status', 5); // Hanya yang berstatus sukses
        $this->db->where('orders.customer_id !=', 2615);
        $this->db->where('customers.reseller_id IS NULL'); // Hanya pelanggan non-reseller

        $query = $this->db->get();
        return $query->row()->total_revenue ?? 0;
    }


    public function get_today_new_customer()
    {
        $this->db->select('COUNT(id_customers) AS total_new_customer', false);
        $this->db->from('customers');
        $this->db->where('DATE(join_date)', 'CURDATE()', false);

        $query = $this->db->get();
        return $query->row()->total_new_customer ?? 0;
    }

    public function get_today_buyers_count()
    {
        $this->db->from('orders');
        $this->db->where('DATE(order_date)', 'CURDATE()', false);
        $this->db->where('payment_status', 3);
        $this->db->where('customer_id !=', 2615);

        return $this->db->count_all_results();
    }

    public function get_best_selling_product()
    {
        $this->db->select('p.id_products, p.title, COUNT(od.product_id) as total_sold');
        $this->db->from('orders_detail od');
        $this->db->join('products p', 'od.product_id = p.id_products', 'left');
        $this->db->join('orders o', 'od.orders_id = o.id_orders', 'left');
        $this->db->join('customers c', 'o.customer_id = c.id_customers', 'left'); // Join dengan customers

        $this->db->where('o.payment_status', 5); // Hanya order yang sukses
        $this->db->where('c.reseller_id IS NULL'); // Hanya pelanggan non-retailer
        $this->db->where('o.customer_id !=', 2615); // Kecualikan customer dengan ID 2615

        $this->db->group_by('od.product_id');
        $this->db->order_by('total_sold', 'DESC');
        $this->db->limit(1); // Ambil hanya produk dengan penjualan terbanyak

        $query = $this->db->get();
        return $query->row();
    }

    // Chart
    // Mendapatkan pendapatan harian
    public function get_daily_revenue($date)
    {
        $this->db->select('SUM(grand_total_amount) AS total_revenue', false);
        $this->db->from('orders');
        $this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
        $this->db->where('DATE(order_date)', $date);
        $this->db->where('payment_status', 5); // Hanya yang berstatus sukses
        $this->db->where('orders.customer_id !=', 2615);
        $this->db->where('customers.reseller_id IS NULL'); // Hanya pelanggan non-reseller

        $query = $this->db->get();
        return $query->row()->total_revenue ?? 0;
    }

    // Mendapatkan total pendapatan seminggu terakhir
    public function get_current_week_revenue()
    {
        $this->db->select('SUM(grand_total_amount) AS total_revenue', false);
        $this->db->from('orders');
        $this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
        $this->db->where('DATE(order_date) >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)', NULL, false);
        $this->db->where('DATE(order_date) <= CURDATE()', NULL, false);
        $this->db->where('payment_status', 5);
        $this->db->where('orders.customer_id !=', 2615);
        $this->db->where('customers.reseller_id IS NULL');

        $query = $this->db->get();
        return $query->row()->total_revenue ?? 0;
    }

    // Mendapatkan total pendapatan seminggu yang sama tahun lalu
    public function get_last_year_week_revenue()
    {
        $this->db->select('SUM(grand_total_amount) AS total_revenue', false);
        $this->db->from('orders');
        $this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
        $this->db->where('DATE(order_date) >= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), INTERVAL 6 DAY)', NULL, false);
        $this->db->where('DATE(order_date) <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)', NULL, false);
        $this->db->where('payment_status', 5);
        $this->db->where('orders.customer_id !=', 2615);
        $this->db->where('customers.reseller_id IS NULL');

        $query = $this->db->get();
        return $query->row()->total_revenue ?? 0;
    }

    // Mendapatkan total expenses marketing untuk pengiriman produk
    public function get_marketing_expenses_by_product_sent()
    {
        $this->db->select('SUM(grand_total_amount) AS total_marketing_expenses', false);
        $this->db->from('orders');
        $this->db->where('payment_status', '5');
        $this->db->where('distribution_department', 'marketing');
        $this->db->where('distribution_type', 'marketing_send_product');
        $query = $this->db->get();
        return $query->row()->total_marketing_expenses ?? 0;
    }

    // Statistic For Admin
    public function get_visits_by_date_range($start_date, $end_date)
    {
        $date_diff = strtotime($end_date) - strtotime($start_date);
        $prev_start_date = date('Y-m-d', strtotime($start_date . ' -1 year'));
        $prev_end_date = date('Y-m-d', strtotime($end_date . ' -1 year'));

        // Ambil data kunjungan total dari link_tracks
        $this->db->select('DATE(click_date) as visit_date, COUNT(*) as total_visits');
        $this->db->from('link_tracks');
        $this->db->where('DATE(click_date) >=', $start_date);
        $this->db->where('DATE(click_date) <=', $end_date);
        $this->db->group_by('DATE(click_date)');
        $this->db->order_by('DATE(click_date)', 'ASC');
        $query = $this->db->get();
        $results = $query->result_array();

        // Ambil data kunjungan periode sebelumnya
        $this->db->select('DATE(click_date) as visit_date, COUNT(*) as total_visits');
        $this->db->from('link_tracks');
        $this->db->where('DATE(click_date) >=', $prev_start_date);
        $this->db->where('DATE(click_date) <=', $prev_end_date);
        $this->db->group_by('DATE(click_date)');
        $this->db->order_by('DATE(click_date)', 'ASC');
        $prev_query = $this->db->get();
        $prev_results = $prev_query->result_array();

        // Ambil total kunjungan unik
        $this->db->select('COUNT(DISTINCT ip_address) as unique_visits');
        $this->db->from('link_tracks');
        $this->db->where('DATE(click_date) >=', $start_date);
        $this->db->where('DATE(click_date) <=', $end_date);
        $unique_query = $this->db->get();
        $unique_visits = $unique_query->row()->unique_visits;

        // Ambil total kunjungan unik periode sebelumnya
        $this->db->select('COUNT(DISTINCT ip_address) as unique_visits');
        $this->db->from('link_tracks');
        $this->db->where('DATE(click_date) >=', $prev_start_date);
        $this->db->where('DATE(click_date) <=', $prev_end_date);
        $prev_unique_query = $this->db->get();
        $prev_unique_visits = $prev_unique_query->row()->unique_visits;

        // Siapkan data untuk chart
        $labels = [];
        $visits = [];
        $prev_visits = [];
        $total_visits = 0;
        $prev_total_visits = 0;

        // Generate tanggal dalam rentang
        $current = strtotime($start_date);
        $end = strtotime($end_date);
        while ($current <= $end) {
            $date = date('Y-m-d', $current);
            $labels[] = date('d M', $current);

            // Cari data kunjungan untuk tanggal ini
            $visits_for_date = 0;
            foreach ($results as $result) {
                if ($result['visit_date'] == $date) {
                    $visits_for_date = (int)$result['total_visits'];
                    $total_visits += $visits_for_date;
                    break;
                }
            }
            $visits[] = $visits_for_date;

            // Cari data kunjungan periode sebelumnya
            $prev_date = date('Y-m-d', strtotime($date . ' -1 year'));
            $prev_visits_for_date = 0;
            foreach ($prev_results as $result) {
                if ($result['visit_date'] == $prev_date) {
                    $prev_visits_for_date = (int)$result['total_visits'];
                    $prev_total_visits += $prev_visits_for_date;
                    break;
                }
            }
            $prev_visits[] = $prev_visits_for_date;

            $current = strtotime('+1 day', $current);
        }

        return [
            'labels' => $labels,
            'visits' => $visits,
            'prev_visits' => $prev_visits,
            'total_visits' => $total_visits,
            'prev_total_visits' => $prev_total_visits,
            'unique_visits' => $unique_visits,
            'prev_unique_visits' => $prev_unique_visits
        ];
    }

    public function get_top_traffic_sources($start_date, $end_date)
    {
        // Query untuk mengambil sumber traffic eksternal
        $this->db->select('
            CASE
                WHEN utm_source = "google" AND utm_medium = "organic" THEN "Google Organic Search"
                WHEN utm_source = "google" AND utm_medium IN ("cpc", "paid") THEN "Google Ads"
                WHEN utm_source = "facebook" THEN "Facebook"
                WHEN utm_source = "instagram" THEN "Instagram" 
                WHEN utm_source = "youtube" THEN "YouTube"
                WHEN utm_source = "tiktok" THEN "TikTok"
                WHEN utm_source = "twitter" THEN "Twitter"
                WHEN utm_source = "whatsapp" THEN "WhatsApp"
                WHEN utm_source = "telegram" THEN "Telegram"
                WHEN utm_source = "mailchimp" THEN "Email Campaign"
                WHEN utm_source = "newsletter" THEN "Newsletter"
                WHEN utm_medium = "referral" THEN "Referral Link (Affiliate)"
                WHEN utm_source = "organic_share" AND utm_campaign = "product_share" THEN "Product Share"
                
                WHEN (utm_source IS NULL OR utm_source = "" OR utm_source = "direct") AND referer IS NOT NULL AND referer != "" THEN
                    CASE
                        WHEN referer LIKE "%google.%" OR referer LIKE "%bing.%" OR referer LIKE "%yahoo.%" THEN "Search Engine"
                        WHEN referer LIKE "%facebook.%" OR referer LIKE "%fb.%" OR referer LIKE "%m.facebook.%" THEN "Facebook"
                        WHEN referer LIKE "%instagram.%" THEN "Instagram"
                        WHEN referer LIKE "%youtube.%" OR referer LIKE "%youtu.be%" THEN "YouTube"
                        WHEN referer LIKE "%tiktok.%" THEN "TikTok"
                        WHEN referer LIKE "%twitter.%" OR referer LIKE "%t.co%" THEN "Twitter"
                        WHEN referer LIKE "%whatsapp.%" OR referer LIKE "%wa.me%" THEN "WhatsApp"
                        WHEN referer LIKE "%telegram.%" OR referer LIKE "%t.me%" THEN "Telegram"
                        WHEN referer LIKE "%shopee.%" THEN "Shopee"
                        WHEN referer LIKE "%tokopedia.%" THEN "Tokopedia"
                        WHEN referer LIKE "%bukalapak.%" THEN "Bukalapak"
                        WHEN referer LIKE "%lazada.%" THEN "Lazada"
                        WHEN referer LIKE "%blibli.%" THEN "Blibli"
                        ELSE "Other External Sites"
                    END
                
                WHEN (utm_source IS NULL OR utm_source = "" OR utm_source = "direct") 
                    AND (referer IS NULL OR referer = "") THEN "Direct Traffic"
                
                ELSE COALESCE(utm_source, "Unknown External")
            END AS source,
            
            referer,
            utm_source,
            utm_medium,
            utm_campaign,
            
            COUNT(*) as total_visits,
            COUNT(DISTINCT ip_address) as unique_visits,
            COUNT(DISTINCT session_id) as unique_sessions
        ');

        $this->db->from('link_tracks');
        $this->db->where('DATE(click_date) >=', $start_date);
        $this->db->where('DATE(click_date) <=', $end_date);

        // HANYA ambil traffic yang BENAR-BENAR external
        $this->db->group_start();
        // Case 1: Ada UTM source yang bukan internal
        $this->db->group_start();
        $this->db->where('utm_source IS NOT NULL');
        $this->db->where('utm_source != ""');
        $this->db->where_not_in('utm_source', [
            'laciasmara.com',
            'www.laciasmara.com',
            'top_banner',
            'categorypage',
            'bottomnavbar',
            'topbanner',
            'slidingbanner',
            'topnavigationbar',
            'website_search',
            'organic',
            'direct'
        ]);
        $this->db->not_like('utm_source', 'recommendation_', 'after');
        $this->db->not_like('utm_source', 'banner_', 'after');
        $this->db->not_like('utm_source', 'internal_', 'after');
        $this->db->group_end();

        $this->db->or_group_start();
        // Case 2: Tidak ada UTM tapi ada referer external
        $this->db->group_start();
        $this->db->where('(utm_source IS NULL OR utm_source = "" OR utm_source = "direct")');
        $this->db->group_end();
        $this->db->where('referer IS NOT NULL');
        $this->db->where('referer != ""');
        $this->db->not_like('referer', 'laciasmara.com');
        $this->db->not_like('referer', 'localhost');
        $this->db->group_end();

        $this->db->or_group_start();
        // Case 3: Direct traffic (no referer, no utm)
        $this->db->where('(utm_source IS NULL OR utm_source = "" OR utm_source = "direct")');
        $this->db->where('(referer IS NULL OR referer = "")');
        $this->db->group_end();
        $this->db->group_end();

        // Exclude bot traffic
        $this->db->where('is_robot', 0);

        $this->db->group_by('source');
        $this->db->order_by('total_visits', 'DESC');
        $this->db->limit(10);

        $query = $this->db->get();
        $sources = $query->result_array();

        // Hitung total kunjungan untuk persentase
        $total_visits = array_sum(array_column($sources, 'total_visits'));

        // Handle jika tidak ada data
        if ($total_visits == 0) {
            return [];
        }

        // Tambahkan persentase dan metrics tambahan
        $sources_with_percentage = array_map(function ($source) use ($total_visits) {
            $source['percentage'] = round(($source['total_visits'] / $total_visits) * 100, 2);

            // Engagement rate
            $source['engagement_rate'] = $source['total_visits'] > 0
                ? round(($source['unique_visits'] / $source['total_visits']) * 100, 2)
                : 0;

            // Session quality (visits per session)
            $source['visits_per_session'] = $source['unique_sessions'] > 0
                ? round($source['total_visits'] / $source['unique_sessions'], 2)
                : 0;

            // Format numbers untuk display
            $source['total_visits_formatted'] = number_format($source['total_visits']);
            $source['unique_visits_formatted'] = number_format($source['unique_visits']);

            // Generate clean display_url
            $display_url = $source['source'];

            // Jika ada UTM source, prioritaskan itu untuk display
            if (!empty($source['utm_source']) && !in_array($source['utm_source'], ['direct', '', 'none'])) {
                $display_url = $source['utm_source'];

                // Tambahkan medium jika ada dan relevan
                if (!empty($source['utm_medium']) && !in_array($source['utm_medium'], ['referral', 'none'])) {
                    $display_url .= ' (' . $source['utm_medium'] . ')';
                }

                // Tambahkan campaign jika ada dan pendek
                if (!empty($source['utm_campaign']) && strlen($source['utm_campaign']) < 20 && $source['utm_campaign'] !== 'none') {
                    $display_url .= ' - ' . $source['utm_campaign'];
                }
            }
            // Jika tidak ada UTM, gunakan domain dari referer
            elseif (!empty($source['referer'])) {
                $parsed_url = parse_url($source['referer']);
                if (isset($parsed_url['host'])) {
                    $display_url = $parsed_url['host'];
                    // Hapus www.
                    $display_url = preg_replace('/^www\./', '', $display_url);
                }
            }

            // Truncate jika terlalu panjang
            if (strlen($display_url) > 70) {
                $display_url = substr($display_url, 0, 47) . '...';
            }

            $source['display_url'] = $display_url;

            // Traffic source category untuk frontend filtering
            $lower_source = strtolower($source['source']);
            if (strpos($lower_source, 'google') !== false || strpos($lower_source, 'search') !== false) {
                $source['category'] = 'search';
            } elseif (in_array(strtolower($source['source']), ['facebook', 'instagram', 'youtube', 'tiktok', 'twitter', 'whatsapp', 'telegram'])) {
                $source['category'] = 'social';
            } elseif (strpos($lower_source, 'mailchimp') !== false || strpos($lower_source, 'newsletter') !== false) {
                $source['category'] = 'email';
            } elseif (strpos($lower_source, 'affiliate') !== false || strpos($lower_source, 'referral') !== false) {
                $source['category'] = 'referral';
            } elseif (strpos($lower_source, 'direct') !== false) {
                $source['category'] = 'direct';
            } elseif (in_array(strtolower($source['source']), ['shopee', 'tokopedia', 'bukalapak', 'lazada', 'blibli'])) {
                $source['category'] = 'marketplace';
            } else {
                $source['category'] = 'other';
            }

            // Cleanup - hapus field yang tidak diperlukan untuk response
            unset($source['referer'], $source['utm_source'], $source['utm_medium'], $source['utm_campaign']);

            return $source;
        }, $sources);

        return $sources_with_percentage;
    }

    public function get_internal_traffic_sources($start_date, $end_date)
    {
        // Query untuk mengambil halaman yang paling sering dibuka
        $this->db->select('
            CASE
                WHEN link_url LIKE "%/product/%" THEN "Product Pages"
                WHEN link_url LIKE "%/category/%" OR link_url LIKE "%/kategori/%" THEN "Category Pages"
                WHEN link_url LIKE "%/cart%" OR link_url LIKE "%/keranjang%" THEN "Shopping Cart"
                WHEN link_url LIKE "%/checkout%" THEN "Checkout Pages"
                WHEN link_url LIKE "%/search%" OR link_url LIKE "%/cari/%" THEN "Search Results"
                WHEN link_url LIKE "%/account/%" OR link_url LIKE "%/akun/%" THEN "Account Pages"
                WHEN link_url = "/" OR link_url LIKE "%/home%" OR link_url LIKE "%laciasmara.com%" THEN "Homepage"
                ELSE 
                    CASE
                        WHEN CHAR_LENGTH(link_url) > 50 THEN CONCAT(LEFT(link_url, 47), "...")
                        ELSE link_url
                    END
            END AS source,
            
            link_url,
            
            COUNT(*) as total_internal_visits,
            COUNT(DISTINCT ip_address) as unique_internal_visits,
            COUNT(DISTINCT session_id) as unique_sessions
        ');

        $this->db->from('link_tracks');
        $this->db->where('DATE(click_date) >=', $start_date);
        $this->db->where('DATE(click_date) <=', $end_date);

        $this->db->not_like('link_url', '/track_impression');

        // Filter untuk internal traffic saja (dari domain sendiri atau tanpa referer)
        $this->db->group_start();
        $this->db->like('referer', 'laciasmara.com');
        $this->db->or_where('referer IS NULL');
        $this->db->or_where('referer = ""');
        $this->db->or_like('referer', 'localhost'); // untuk development
        $this->db->group_end();

        // Exclude bot traffic
        $this->db->where('is_robot', 0);

        $this->db->group_by('link_url');
        $this->db->order_by('total_internal_visits', 'DESC');
        $this->db->limit(10);

        $query = $this->db->get();
        $page_visits = $query->result_array();

        // Hitung total kunjungan untuk persentase
        $total_visits = array_sum(array_column($page_visits, 'total_internal_visits'));

        // Handle jika tidak ada data
        if ($total_visits == 0) {
            return [];
        }

        // Tambahkan persentase dan informasi tambahan
        $page_visits_with_percentage = array_map(function ($page) use ($total_visits) {
            $page['percentage'] = round(($page['total_internal_visits'] / $total_visits) * 100, 2);

            // Tambahkan conversion rate (unique visitors dari total visits)
            $page['engagement_rate'] = $page['total_internal_visits'] > 0
                ? round(($page['unique_internal_visits'] / $page['total_internal_visits']) * 100, 2)
                : 0;

            // Format angka untuk tampilan yang lebih friendly
            $page['total_visits_formatted'] = number_format($page['total_internal_visits']);
            $page['unique_visits_formatted'] = number_format($page['unique_internal_visits']);

            // Generate clean display_url - format yang user-friendly dengan handling URL kompleks
            $clean_url = $page['link_url'];

            // Hapus domain jika ada
            $clean_url = preg_replace('/^https?:\/\/[^\/]+/', '', $clean_url);

            // Hapus parameter query yang tidak perlu
            $clean_url = preg_replace('/\?.*$/', '', $clean_url);

            // Bersihkan leading dan trailing slash
            $clean_url = trim($clean_url, '/');

            // Jika kosong, set sebagai homepage
            if (empty($clean_url)) {
                $display_url = 'Homepage';
            } else {
                // Parse URL untuk membuat format yang user-friendly
                $url_parts = explode('/', $clean_url);

                if (count($url_parts) >= 2) {
                    // Deteksi pola URL dan format sesuai
                    if ($url_parts[0] === 'product' || (isset($url_parts[1]) && $url_parts[1] === 'product')) {
                        // Format: Product - [nama produk] atau Product - [category] - [nama produk]
                        $product_name = end($url_parts);
                        $product_name = str_replace(['-', '_'], ' ', $product_name);
                        $product_name = ucwords($product_name);

                        // Jika ada lebih dari 2 segment setelah 'product', tampilkan hierarchy
                        $product_index = array_search('product', $url_parts);
                        if ($product_index !== false && count($url_parts) > $product_index + 2) {
                            $category_part = $url_parts[$product_index + 1];
                            $category_part = str_replace(['-', '_'], ' ', $category_part);
                            $category_part = ucwords($category_part);
                            $display_url = 'Product - ' . $category_part . ' - ' . $product_name;
                        } else {
                            $display_url = 'Product - ' . $product_name;
                        }
                    } elseif (
                        $url_parts[0] === 'category' || $url_parts[0] === 'kategori' ||
                        (isset($url_parts[1]) && ($url_parts[1] === 'category' || $url_parts[1] === 'kategori')) ||
                        (isset($url_parts[2]) && ($url_parts[2] === 'category' || $url_parts[2] === 'kategori'))
                    ) {
                        // Format: Category - [parent] - [child] untuk URL kompleks
                        $category_index = -1;
                        foreach (['category', 'kategori'] as $cat_word) {
                            $index = array_search($cat_word, $url_parts);
                            if ($index !== false) {
                                $category_index = $index;
                                break;
                            }
                        }

                        if ($category_index !== false) {
                            $categories_after = array_slice($url_parts, $category_index + 1);
                            if (count($categories_after) > 1) {
                                // Multi-level category: Category - Parent - Child
                                $formatted_cats = array_map(function ($cat) {
                                    $cat = str_replace(['-', '_'], ' ', $cat);
                                    return ucwords($cat);
                                }, $categories_after);
                                $display_url = 'Category - ' . implode(' - ', $formatted_cats);
                            } else {
                                // Single level category: Category - Name
                                $category_name = end($categories_after);
                                $category_name = str_replace(['-', '_'], ' ', $category_name);
                                $category_name = ucwords($category_name);
                                $display_url = 'Category - ' . $category_name;
                            }
                        } else {
                            // Fallback
                            $category_name = end($url_parts);
                            $category_name = str_replace(['-', '_'], ' ', $category_name);
                            $category_name = ucwords($category_name);
                            $display_url = 'Category - ' . $category_name;
                        }
                    } elseif (in_array('cart', $url_parts) || in_array('keranjang', $url_parts)) {
                        $display_url = 'Shopping Cart';
                    } elseif (in_array('checkout', $url_parts)) {
                        $display_url = 'Checkout';
                    } elseif (in_array('search', $url_parts) || in_array('cari', $url_parts)) {
                        $display_url = 'Search Results';
                    } elseif (in_array('account', $url_parts) || in_array('akun', $url_parts)) {
                        $display_url = 'Account Pages';
                    } else {
                        // Untuk URL lainnya, tampilkan path yang lebih descriptive
                        if (count($url_parts) > 2) {
                            // Ambil 2 bagian terakhir untuk context
                            $last_two = array_slice($url_parts, -2);
                            $formatted_parts = array_map(function ($part) {
                                $part = str_replace(['-', '_'], ' ', $part);
                                return ucwords($part);
                            }, $last_two);
                            $display_url = implode(' - ', $formatted_parts);
                        } else {
                            // Single atau double segment
                            $formatted_parts = array_map(function ($part) {
                                $part = str_replace(['-', '_'], ' ', $part);
                                return ucwords($part);
                            }, $url_parts);
                            $display_url = implode(' - ', $formatted_parts);
                        }
                    }
                } else {
                    // Single segment URL
                    $single_part = $url_parts[0];
                    $single_part = str_replace(['-', '_'], ' ', $single_part);
                    $single_part = ucwords($single_part);
                    $display_url = $single_part;
                }
            }

            // Truncate jika terlalu panjang
            if (strlen($display_url) > 70) {
                $display_url = substr($display_url, 0, 57) . '...';
            }

            $page['display_url'] = $display_url;

            // Cleanup - hapus field yang tidak diperlukan untuk response
            unset($page['link_url']);

            return $page;
        }, $page_visits);

        return $page_visits_with_percentage;
    }

    /* LOGIKA RATA-RATA WAKTU DAN BOUNCE RATE
    * =====================================
    * 
    * RATA-RATA WAKTU KUNJUNGAN:
    * - Menghitung durasi antara first page dan last page dalam satu session
    * - Menggunakan session_id untuk mengelompokkan aktivitas user
    * - Formula: (waktu_terakhir - waktu_pertama) per session
    * 
    * BOUNCE RATE:
    * - Persentase session yang hanya memiliki 1 page view
    * - Formula: (session dengan 1 page view / total session) × 100%
    */

    /**
     * Menghitung rata-rata waktu kunjungan per session
     * @param string $start_date
     * @param string $end_date
     * @return array
     */
    public function get_average_session_time($start_date, $end_date)
    {
        $query = "
            SELECT 
                AVG(session_duration) as avg_duration,
                COUNT(*) as total_sessions
            FROM (
                SELECT 
                    session_id,
                    ip_address,
                    TIMESTAMPDIFF(SECOND, MIN(click_date), MAX(click_date)) as session_duration,
                    COUNT(*) as page_views
                FROM link_tracks 
                WHERE DATE(click_date) >= ? 
                    AND DATE(click_date) <= ?
                    AND session_id IS NOT NULL
                    AND session_id != ''
                    AND is_robot = 0
                GROUP BY session_id, ip_address
                HAVING page_views > 1 
                    AND session_duration > 0 
                    AND session_duration < 3600
            ) as sessions
        ";

        $result = $this->db->query($query, [$start_date, $end_date]);
        $row = $result->row_array();

        $average_seconds = $row ? round($row['avg_duration']) : 0;
        $total_sessions = $row ? $row['total_sessions'] : 0;

        return [
            'average_time_seconds' => $average_seconds,
            'average_time_formatted' => $this->format_duration($average_seconds),
            'total_sessions_with_time' => $total_sessions
        ];
    }

    public function get_bounce_rate($start_date, $end_date)
    {
        $query = "
        SELECT 
            COUNT(*) as total_sessions,
            SUM(CASE WHEN page_views = 1 THEN 1 ELSE 0 END) as bounced_sessions
        FROM (
            SELECT 
                session_id,
                ip_address,
                COUNT(*) as page_views
            FROM link_tracks 
            WHERE DATE(click_date) >= ? 
                AND DATE(click_date) <= ?
                AND session_id IS NOT NULL
                AND session_id != ''
                AND is_robot = 0
            GROUP BY session_id, ip_address
        ) as sessions
    ";

        $result = $this->db->query($query, [$start_date, $end_date]);
        $row = $result->row_array();

        if (!$row || $row['total_sessions'] == 0) {
            return [
                'bounce_rate' => 0,
                'bounced_sessions' => 0,
                'total_sessions' => 0
            ];
        }

        $bounce_rate = round(($row['bounced_sessions'] / $row['total_sessions']) * 100, 1);

        return [
            'bounce_rate' => $bounce_rate,
            'bounced_sessions' => $row['bounced_sessions'],
            'total_sessions' => $row['total_sessions']
        ];
    }

    /**
     * Format durasi dalam detik ke format MM:SS
     * @param int $seconds
     * @return string
     */
    private function format_duration($seconds)
    {
        $minutes = floor($seconds / 60);
        $seconds = $seconds % 60;
        return sprintf('%d:%02d', $minutes, $seconds);
    }

    public function get_engagement_metrics($start_date, $end_date)
    {
        $avg_time = $this->get_average_session_time($start_date, $end_date);
        $bounce_rate = $this->get_bounce_rate($start_date, $end_date);

        return [
            'average_time_seconds' => $avg_time['average_time_seconds'],
            'average_time_formatted' => $avg_time['average_time_formatted'],
            'bounce_rate' => $bounce_rate['bounce_rate'],
            'bounced_sessions' => $bounce_rate['bounced_sessions'],
        ];
    }

    /**
     * Get sales data with filters and sorting
     *
     * @param string $start_date Start date in Y-m-d format
     * @param string $end_date End date in Y-m-d format
     * @param array $payment_filters Payment method filters
     * @param array $shipping_filters Shipping method filters
     * @param array $insurance_filters Insurance filters
     * @param string $search Search term for customer name, email or order id
     * @param string $sort Sort direction
     * @return array
     */
    public function get_sales_data($start_date, $end_date, $payment_filters = [], $shipping_filters = [], $insurance_filters = [], $order_type_filters = [], $search = '', $sort = '')
    {
        // Basic selection
        $this->db->select('
            orders.id_orders,
            orders.tokopedia_invoice,
            orders.grand_total_amount,
            orders.shipping_fee,
            orders.order_date,
            orders.customer_id,
            orders.phone,
            orders.payment_type,
            orders.insurance_status,
            customers.name AS customer_name,
            customers.email AS customer_email,
            DATE(orders.order_date) as order_date
        ');
        $this->db->from('orders');
        $this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');

        // Date range filter
        $this->db->where_in('orders.payment_status', [5, 8]);
        $this->db->where('DATE(orders.order_date) >=', $start_date);
        $this->db->where('DATE(orders.order_date) <=', $end_date);

        $this->apply_order_type_filters($order_type_filters);

        // Process payment type filters
        $this->apply_payment_filters($payment_filters);

        // Process shipping method filters
        $this->apply_shipping_filters($shipping_filters);

        // Process insurance filters
        $this->apply_insurance_filters($insurance_filters);

        // Process search
        if (!empty($search)) {
            $this->db->group_start();
            $this->db->like('customers.name', $search);
            $this->db->or_like('customers.email', $search);
            $this->db->or_like('orders.id_orders', $search);
            $this->db->group_end();
        }

        // Apply sorting
        $this->apply_sorting($sort);

        // Get results
        $sales = $this->db->get()->result_array();

        return [
            'sales' => $sales
        ];
    }

    /**
     * Get comparison chart data between current period and previous year
     *
     * @param string $start_date Current period start date
     * @param string $end_date Current period end date
     * @param string $previous_start_date Previous period start date
     * @param string $previous_end_date Previous period end date
     * @param array $payment_filters Payment method filters
     * @param array $shipping_filters Shipping method filters
     * @param array $insurance_filters Insurance filters
     * @param array $order_type_filters Order type filters
     * @return array
     */
    public function get_comparison_chart_data(
        $start_date,
        $end_date,
        $previous_start_date,
        $previous_end_date,
        $payment_filters = [],
        $shipping_filters = [],
        $insurance_filters = [],
        $order_type_filters = []
    ) {
        // Calculate date difference to determine chart interval
        $date_diff = strtotime($end_date) - strtotime($start_date);
        $days_diff = round($date_diff / (60 * 60 * 24));

        $labels = [];
        $current_data = [];
        $previous_data = [];

        // For short ranges (<=31 days), show daily data
        if ($days_diff <= 31) {
            // Generate daily data
            $current_day = new DateTime($start_date);
            $end_day = new DateTime($end_date);
            $end_day->modify('+1 day'); // Include the end date

            while ($current_day < $end_day) {
                $current_date = $current_day->format('Y-m-d');
                $labels[] = $current_day->format('d M'); // Format: 01 Jan

                // Get current year daily revenue
                $daily_revenue = $this->get_filtered_daily_revenue(
                    $current_date,
                    $payment_filters,
                    $shipping_filters,
                    $insurance_filters,
                    $order_type_filters
                );
                $current_data[] = $daily_revenue;

                // Get previous year daily revenue for the same day
                $previous_date = date('Y-m-d', strtotime('-1 year', strtotime($current_date)));
                $previous_daily_revenue = $this->get_filtered_daily_revenue(
                    $previous_date,
                    $payment_filters,
                    $shipping_filters,
                    $insurance_filters,
                    $order_type_filters
                );
                $previous_data[] = $previous_daily_revenue;

                $current_day->modify('+1 day');
            }
        }
        // For medium ranges (<=90 days), show weekly data
        else if ($days_diff <= 90) {
            // Generate weekly data
            $current_day = new DateTime($start_date);
            $end_day = new DateTime($end_date);

            // Group by weeks
            $week_num = 1;
            while ($current_day <= $end_day) {
                $week_start = clone $current_day;
                $week_end = clone $current_day;
                $week_end->modify('+6 days');

                if ($week_end > $end_day) {
                    $week_end = clone $end_day;
                }

                $labels[] = 'Week ' . $week_num;

                // Get current year weekly revenue
                $weekly_revenue = $this->get_filtered_range_revenue(
                    $week_start->format('Y-m-d'),
                    $week_end->format('Y-m-d'),
                    $payment_filters,
                    $shipping_filters,
                    $insurance_filters,
                    $order_type_filters
                );
                $current_data[] = $weekly_revenue;

                // Get previous year weekly revenue
                $previous_week_start = date('Y-m-d', strtotime('-1 year', $week_start->getTimestamp()));
                $previous_week_end = date('Y-m-d', strtotime('-1 year', $week_end->getTimestamp()));
                $previous_weekly_revenue = $this->get_filtered_range_revenue(
                    $previous_week_start,
                    $previous_week_end,
                    $payment_filters,
                    $shipping_filters,
                    $insurance_filters,
                    $order_type_filters
                );
                $previous_data[] = $previous_weekly_revenue;

                $current_day->modify('+7 days');
                $week_num++;
            }
        }
        // For long ranges (>90 days), show monthly data
        else {
            // Generate monthly data
            $current_month = new DateTime($start_date);
            $current_month->modify('first day of this month');
            $end_month = new DateTime($end_date);
            $end_month->modify('last day of this month');

            while ($current_month <= $end_month) {
                $month_start = $current_month->format('Y-m-01');
                $month_end = $current_month->format('Y-m-t');

                $labels[] = $current_month->format('M Y'); // Format: Jan 2025

                // Get current year monthly revenue
                $monthly_revenue = $this->get_filtered_range_revenue(
                    $month_start,
                    $month_end,
                    $payment_filters,
                    $shipping_filters,
                    $insurance_filters,
                    $order_type_filters
                );
                $current_data[] = $monthly_revenue;

                // Get previous year monthly revenue
                $previous_month_start = date('Y-m-01', strtotime('-1 year', strtotime($month_start)));
                $previous_month_end = date('Y-m-t', strtotime('-1 year', strtotime($month_end)));
                $previous_monthly_revenue = $this->get_filtered_range_revenue(
                    $previous_month_start,
                    $previous_month_end,
                    $payment_filters,
                    $shipping_filters,
                    $insurance_filters,
                    $order_type_filters
                );
                $previous_data[] = $previous_monthly_revenue;

                $current_month->modify('+1 month');
            }
        }

        return [
            'labels' => $labels,
            'current_data' => $current_data,
            'previous_data' => $previous_data
        ];
    }

    /**
     * Get daily revenue with all filters applied
     *
     * @param string $date Date in Y-m-d format
     * @param array $payment_filters Payment method filters
     * @param array $shipping_filters Shipping method filters 
     * @param array $insurance_filters Insurance filters
     * @param array $order_type_filters Order type filters
     * @return float
     */
    public function get_filtered_daily_revenue($date, $payment_filters = [], $shipping_filters = [], $insurance_filters = [], $order_type_filters = [])
    {
        $this->db->select('SUM(grand_total_amount) AS total_revenue', false);
        $this->db->from('orders');
        $this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
        $this->db->where('DATE(order_date)', $date);
        $this->db->where_in('payment_status', [5, 8]);

        // Apply all filters
        $this->apply_payment_filters($payment_filters);
        $this->apply_shipping_filters($shipping_filters);
        $this->apply_insurance_filters($insurance_filters);
        $this->apply_order_type_filters($order_type_filters);

        $query = $this->db->get();
        return $query->row()->total_revenue ?? 0;
    }

    /**
     * Get revenue for a date range with all filters applied
     *
     * @param string $start_date Start date in Y-m-d format
     * @param string $end_date End date in Y-m-d format
     * @param array $payment_filters Payment method filters
     * @param array $shipping_filters Shipping method filters
     * @param array $insurance_filters Insurance filters
     * @param array $order_type_filters Order type filters
     * @return float
     */
    public function get_filtered_range_revenue($start_date, $end_date, $payment_filters = [], $shipping_filters = [], $insurance_filters = [], $order_type_filters = [])
    {
        $this->db->select('SUM(grand_total_amount) AS total_revenue', false);
        $this->db->from('orders');
        $this->db->join('customers', 'orders.customer_id = customers.id_customers', 'left');
        $this->db->where('DATE(order_date) >=', $start_date);
        $this->db->where('DATE(order_date) <=', $end_date);
        $this->db->where_in('payment_status', [5, 8]);
        $this->db->where('orders.customer_id !=', 2615);

        // Apply all filters
        $this->apply_payment_filters($payment_filters);
        $this->apply_shipping_filters($shipping_filters);
        $this->apply_insurance_filters($insurance_filters);
        $this->apply_order_type_filters($order_type_filters);

        $query = $this->db->get();
        return $query->row()->total_revenue ?? 0;
    }

    /**
     * Apply order type filters to query
     * 
     * @param array $order_type_filters Order type filters
     */
    private function apply_order_type_filters($order_type_filters)
    {
        // Only apply if any filter is active and not All Orders
        $active_filters = array_filter($order_type_filters, function ($value) {
            return $value == 'true';
        });

        // If "All Orders" is selected or no filters are active, return all orders
        if ((isset($order_type_filters['isAllOrder']) && $order_type_filters['isAllOrder'] == 'true') || empty($active_filters)) {
            return;
        }

        // Apply specific filters
        $this->db->group_start();

        // Website orders: not retailer and not tokopedia
        if (isset($order_type_filters['isWebsiteOrder']) && $order_type_filters['isWebsiteOrder'] == 'true') {

            $this->db->group_start(); // mulai grup kondisi untuk invoice kosong/null
            $this->db->where('orders.tokopedia_invoice', '');
            $this->db->or_where('orders.tokopedia_invoice IS NULL');
            $this->db->group_end();

            // exclude reseller
            $this->db->group_start(); // mulai grup kondisi untuk non-reseller
            $this->db->where('customers.reseller_id', NULL);
            $this->db->or_where('customers.reseller_id', '');
            $this->db->group_end();
        }

        // Retailer orders: has reseller_id in customers table
        if (isset($order_type_filters['isRetailerOrder']) && $order_type_filters['isRetailerOrder'] == 'true') {
            $this->db->group_start();
            $this->db->where('customers.reseller_id IS NOT NULL');
            $this->db->where('customers.reseller_id !=', '');
            $this->db->group_end();
        }

        // Tokopedia orders: has tokopedia_invoice
        if (isset($order_type_filters['isTokopediaOrder']) && $order_type_filters['isTokopediaOrder'] == 'true') {
            $this->db->group_start();
            $this->db->where('orders.tokopedia_invoice IS NOT NULL');
            $this->db->where('orders.tokopedia_invoice !=', '');
            $this->db->group_end();
        }

        $this->db->group_end();
    }
    /**
     * Apply payment filters to query
     * 
     * @param array $payment_filters Payment filters
     */
    private function apply_payment_filters($payment_filters)
    {
        // Only apply if any filter is active
        $active_filters = array_filter($payment_filters, function ($value) {
            return $value == 'true';
        });

        if (!empty($active_filters)) {
            $this->db->group_start();

            if (isset($payment_filters['isBCATransfer']) && $payment_filters['isBCATransfer'] == 'true') {
                $this->db->or_where('payment_type', 'bank transfer BCA');
            }

            if (isset($payment_filters['isMandiriTransfer']) && $payment_filters['isMandiriTransfer'] == 'true') {
                $this->db->or_where('payment_type', 'bank transfer MANDIRI');
            }

            if (isset($payment_filters['isPaypal']) && $payment_filters['isPaypal'] == 'true') {
                $this->db->or_where('payment_type', 'Paypal');
            }

            if (isset($payment_filters['isDoku']) && $payment_filters['isDoku'] == 'true') {
                $this->db->or_where('payment_type', 'DOKU');
            }

            $this->db->group_end();
        }
    }

    /**
     * Apply shipping filters to query
     * 
     * @param array $shipping_filters Shipping filters
     */
    private function apply_shipping_filters($shipping_filters)
    {
        // Only apply if any filter is active
        $active_filters = array_filter($shipping_filters, function ($value) {
            return $value == 'true';
        });

        if (!empty($active_filters)) {
            $shipping_ids = [];

            if (isset($shipping_filters['isRegularShipping']) && $shipping_filters['isRegularShipping'] == 'true') {
                $shipping_ids[] = 3; // Regular shipping
            }
            if (isset($shipping_filters['isTwoHourShipping']) && $shipping_filters['isTwoHourShipping'] == 'true') {
                $shipping_ids[] = 2; // Two hour shipping
            }
            if (isset($shipping_filters['isOneDayShipping']) && $shipping_filters['isOneDayShipping'] == 'true') {
                $shipping_ids[] = 1; // One day shipping
            }
            if (isset($shipping_filters['isNextDayShipping']) && $shipping_filters['isNextDayShipping'] == 'true') {
                $shipping_ids[] = 4; // Next day shipping
            }

            if (!empty($shipping_ids)) {
                // Join with orders_detail table
                $this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id', 'inner');
                // Filter by shipping method IDs
                $this->db->where_in('orders_detail.chosen_shipping_id', $shipping_ids);
                // Group by order ID to avoid duplicates
                $this->db->group_by('orders.id_orders');
            }
        }
    }

    /**
     * Apply insurance filters to query
     * 
     * @param array $insurance_filters Insurance filters
     */
    private function apply_insurance_filters($insurance_filters)
    {
        if (!is_array($insurance_filters)) {
            return; // atau $insurance_filters = [];
        }
        // Only apply if any filter is active
        $active_filters = array_filter($insurance_filters, function ($value) {
            return $value == 'true';
        });

        if (!empty($active_filters)) {
            $this->db->group_start();

            if (isset($insurance_filters['usingInsurance']) && $insurance_filters['usingInsurance'] == 'true') {
                $this->db->or_where('insurance_status', 'Yes');
            }

            if (isset($insurance_filters['notUsingInsurance']) && $insurance_filters['notUsingInsurance'] == 'true') {
                $this->db->or_where('insurance_status', 'No');
            }

            $this->db->group_end();
        }
    }

    /**
     * Apply sorting to query
     * 
     * @param string $sort Sort parameter
     */
    private function apply_sorting($sort)
    {
        switch ($sort) {
            case 'paling_baru':
                $this->db->order_by('orders.order_date', 'DESC');
                break;
            case 'paling_lama':
                $this->db->order_by('orders.order_date', 'ASC');
                break;
            default:
                // Default sorting - newest first
                $this->db->order_by('orders.order_date', 'DESC');
                break;
        }
    }

    // Trending searches
    public function get_trending_searches($days = 7, $limit = 10)
    {
        $this->db->select('search_query, COUNT(*) AS total_searches');
        $this->db->from('link_tracks');
        $this->db->where("search_query IS NOT NULL");
        $this->db->where("search_query !=", '');
        $this->db->where("click_date >=", "DATE_SUB(NOW(), INTERVAL {$days} DAY)", false);
        $this->db->group_by('search_query');
        $this->db->order_by('total_searches', 'DESC');
        $this->db->limit($limit);

        $query = $this->db->get();
        $results = $query->result_array();
        return array_column($results, 'search_query');
    }
}