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


class Link extends Admin_Controller
{
    function __construct()
    {

        parent::__construct();
        $this->load->model('configuration_m');
        $this->load->model('brand_m');
        $this->load->model('category_m');
        $this->load->model('product_m');
        $this->load->helper('form');
    }

    function index()
    {

        $data['userdata'] = $this->session->userdata();

        $data['title'] = 'Link Generator | Laciasmara';
        $data['links'] = $this->getUTMLinksWithStatistics();

        $this->load->view('admin_new/layouts/header', $data);
        $this->load->view('admin_new/links/index');
        $this->load->view('admin_new/layouts/footer');
    }

    function add()
    {

        $data['userdata'] = $this->session->userdata();
        $data['brands'] = $this->brand_m->get_all_brands();
        $data['categories'] = $this->category_m->all_categories();
        $data['products'] = $this->product_m->all_products();
        $data['title'] = 'Add UTM Link | Laciasmara';

        $this->load->view('admin_new/layouts/header', $data);
        $this->load->view('admin_new/links/add_link');
        $this->load->view('admin_new/layouts/footer');
    }

    /**
     * Create new UTM tracking link
     * 
     * @return void
     */
    public function create_link()
    {
        // Check CSRF token
        $this->form_validation->set_rules('link_type', 'Jenis Link', 'required');
        $this->form_validation->set_rules('utm_source', 'Sumber Trafik', 'required');
        $this->form_validation->set_rules('utm_medium', 'Jenis Trafik', 'required');
        $this->form_validation->set_rules('utm_campaign', 'Nama Promosi', 'required');

        if ($this->form_validation->run() == FALSE) {
            $this->session->set_flashdata('message_type', 'error');
            $this->session->set_flashdata('message', 'Mohon lengkapi semua field yang wajib diisi!');
            redirect('admin/marketing/link/add');
            return;
        }

        // Get form data
        $link_type = $this->input->post('link_type');
        $item_id = null;

        if ($link_type == 'product') {
            $item_id = $this->input->post('product_id');
        } else if ($link_type == 'brand') {
            $item_id = $this->input->post('brand_id');
        } else if ($link_type == 'category') {
            $item_id = $this->input->post('category_id');
        } else {
            $this->session->set_flashdata('message_type', 'error');
            $this->session->set_flashdata('message', 'Jenis link tidak valid!');
            redirect('admin/marketing/tracking-link/add');
            return;
        }

        // Build UTM parameters
        $utm_params = [
            'utm_source' => $this->input->post('utm_source'),
            'utm_medium' => $this->input->post('utm_medium'),
            'utm_campaign' => $this->input->post('utm_campaign')
        ];

        // Add optional UTM parameters if they exist
        if ($this->input->post('utm_content')) {
            $utm_params['utm_content'] = $this->input->post('utm_content');
        }

        if ($this->input->post('utm_term')) {
            $utm_params['utm_term'] = $this->input->post('utm_term');
        }

        $url = $this->input->post('urlPreview');

        // Data to be inserted
        $data = [
            'url' => $url,
            'item_id' => $item_id,
            'item_type' => $link_type,
            'utm_source' => $this->input->post('utm_source'),
            'utm_medium' => $this->input->post('utm_medium'),
            'utm_campaign' => $this->input->post('utm_campaign'),
            'utm_content' => $this->input->post('utm_content'),
            'utm_term' => $this->input->post('utm_term'),
            'created_at' => date('Y-m-d H:i:s')
        ];

        // Insert data to database
        $this->db->insert('utm_links', $data);

        // Set flash message and redirect
        $this->session->set_flashdata('message_type', 'success');
        $this->session->set_flashdata('message', 'Link berhasil ditambahkan!');
        redirect('admin/marketing/tracking-link');
    }

    // Di controller
    public function get_utm_links()
    {
        $links = $this->getUTMLinksWithStatistics();
        $this->output
            ->set_content_type('application/json')
            ->set_output(json_encode($links));
    }

    private function getUTMLinksWithStatistics()
    {
        // Get filter parameters - tetap sama seperti sebelumnya
        $sort = $this->input->get('sort', true);
        $dateFilter = $this->input->get('date_filter', true);
        $startDate = $this->input->get('start_date', true);
        $endDate = $this->input->get('end_date', true);
        $page = (int) ($this->input->get('page', true) ?? 1);
        $limit = (int) ($this->input->get('limit', true) ?? 10);
        $offset = ($page - 1) * $limit;

        $isInstagram = filter_var($this->input->get('isInstagram', true), FILTER_VALIDATE_BOOLEAN);
        $isFacebook = filter_var($this->input->get('isFacebook', true), FILTER_VALIDATE_BOOLEAN);
        $isYoutube = filter_var($this->input->get('isYoutube', true), FILTER_VALIDATE_BOOLEAN);
        $isLinkedIn = filter_var($this->input->get('isLinkedIn', true), FILTER_VALIDATE_BOOLEAN);
        $isTiktok = filter_var($this->input->get('isTiktok', true), FILTER_VALIDATE_BOOLEAN);
        $isDuniaAsmara = filter_var($this->input->get('isDuniaAsmara', true), FILTER_VALIDATE_BOOLEAN);
        $isGoogleAds = filter_var($this->input->get('isGoogleAds', true), FILTER_VALIDATE_BOOLEAN);
        $isMeta = filter_var($this->input->get('isMeta', true), FILTER_VALIDATE_BOOLEAN);

        $isProduct = filter_var($this->input->get('isProduct', true), FILTER_VALIDATE_BOOLEAN);
        $isBrand = filter_var($this->input->get('isBrand', true), FILTER_VALIDATE_BOOLEAN);
        $isCategory = filter_var($this->input->get('isCategory', true), FILTER_VALIDATE_BOOLEAN);

        $searchTerm = $this->input->get('search', true);

        // OPTIMASI 1: Gunakan pendekatan dua langkah
        // Langkah 1: Ambil dulu ID UTM links berdasarkan filter

        $this->db->select('utm_links.id');
        $this->db->from('utm_links');

        // Join dengan items untuk pencarian item_name
        $this->db->join('(
            SELECT 
                id_products AS item_id, 
                title AS item_name, 
                "product" AS type 
            FROM products
            UNION ALL
            SELECT 
                id_brands AS item_id, 
                brand AS item_name, 
                "brand" AS type 
            FROM brands
            UNION ALL
            SELECT 
                id_categories AS item_id, 
                category AS item_name, 
                "category" AS type 
            FROM categories
        ) AS items', 'items.item_id = utm_links.item_id AND items.type = utm_links.item_type', 'left');


        // Apply platform filters (sama seperti sebelumnya)
        $platformFilters = [];
        if ($isInstagram) $platformFilters[] = 'instagram';
        if ($isFacebook) $platformFilters[] = 'facebook';
        if ($isYoutube) $platformFilters[] = 'youtube';
        if ($isLinkedIn) $platformFilters[] = 'linkedin';
        if ($isTiktok) $platformFilters[] = 'tiktok';
        if ($isDuniaAsmara) $platformFilters[] = 'duniaasmara';
        if ($isGoogleAds) $platformFilters[] = 'google';
        if ($isMeta) $platformFilters[] = 'meta';

        if (!empty($platformFilters)) {
            $this->db->group_start();
            foreach ($platformFilters as $index => $platform) {
                if ($index === 0) {
                    $this->db->like('utm_links.utm_source', $platform);
                } else {
                    $this->db->or_like('utm_links.utm_source', $platform);
                }
            }
            $this->db->group_end();
        }

        // Apply item type filters (sama seperti sebelumnya)
        $itemTypeFilters = [];
        if ($isProduct) $itemTypeFilters[] = 'product';
        if ($isBrand) $itemTypeFilters[] = 'brand';
        if ($isCategory) $itemTypeFilters[] = 'category';

        if (!empty($itemTypeFilters)) {
            $this->db->group_start();
            foreach ($itemTypeFilters as $index => $itemType) {
                if ($index === 0) {
                    $this->db->where('utm_links.item_type', $itemType);
                } else {
                    $this->db->or_where('utm_links.item_type', $itemType);
                }
            }
            $this->db->group_end();
        }

        // Apply search filter (sama seperti sebelumnya)
        if (!empty($searchTerm)) {
            $this->db->group_start();
            $this->db->like('utm_links.url', $searchTerm);
            $this->db->or_like('utm_links.utm_source', $searchTerm);
            $this->db->or_like('utm_links.utm_medium', $searchTerm);
            $this->db->or_like('utm_links.utm_campaign', $searchTerm);
            $this->db->or_like('utm_links.utm_content', $searchTerm);
            $this->db->or_like('utm_links.utm_term', $searchTerm);
            $this->db->or_like('items.item_name', $searchTerm);
            $this->db->group_end();
        }

        // Apply date filter (sama seperti sebelumnya)
        // if (!empty($dateFilter)) {
        //     switch ($dateFilter) {
        //         case 'today':
        //             $this->db->where('DATE(utm_links.created_at) = CURDATE()');
        //             break;
        //         case 'yesterday':
        //             $this->db->where('DATE(utm_links.created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)');
        //             break;
        //         case 'last_7_days':
        //             $this->db->where('utm_links.created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)');
        //             break;
        //         case 'last_30_days':
        //             $this->db->where('utm_links.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)');
        //             break;
        //         case 'this_month':
        //             $this->db->where('MONTH(utm_links.created_at) = MONTH(CURDATE())');
        //             $this->db->where('YEAR(utm_links.created_at) = YEAR(CURDATE())');
        //             break;
        //         case 'custom':
        //             if (!empty($startDate) && !empty($endDate)) {
        //                 $this->db->where('DATE(utm_links.created_at) >=', date('Y-m-d', strtotime($startDate)));
        //                 $this->db->where('DATE(utm_links.created_at) <=', date('Y-m-d', strtotime($endDate)));
        //             }
        //             break;
        //     }
        // }

        // OPTIMASI 2: Terapkan sorting dan pagination pada subquery ini
        if ($sort) {
            switch ($sort) {
                case 'total_click_banyak':
                case 'total_click_dikit':
                case 'unique_click_banyak':
                case 'unique_click_dikit':
                case 'order_banyak':
                case 'order_dikit':
                    // Untuk sorting yang membutuhkan statistik, kita tetap gunakan default sort dulu
                    $this->db->order_by('utm_links.id', 'DESC');
                    break;
                default:
                    // Default sort by newest
                    $this->db->order_by('utm_links.id', 'DESC');
            }
        } else {
            $this->db->order_by('utm_links.id', 'DESC');
        }

        // Terapkan pagination
        $this->db->limit($limit, $offset);

        // Eksekusi query untuk mendapatkan ID yang akan digunakan
        $id_query = $this->db->get();
        $utm_link_ids = array_column($id_query->result_array(), 'id');

        // Jika tidak ada hasil, return array kosong
        if (empty($utm_link_ids)) {
            return [];
        }

        // OPTIMASI 3: Query kedua untuk mengambil data utama dengan ID yang sudah difilter
        $this->db->select('utm_links.*, 
            items.item_name');
        $this->db->from('utm_links');

        // Join dengan items untuk mendapatkan item_name
        $this->db->join('(
            SELECT 
                id_products AS item_id, 
                title AS item_name, 
                "product" AS type 
            FROM products
            UNION ALL
            SELECT 
                id_brands AS item_id, 
                brand AS item_name, 
                "brand" AS type 
            FROM brands
            UNION ALL
            SELECT 
                id_categories AS item_id, 
                category AS item_name, 
                "category" AS type 
            FROM categories
        ) AS items', 'items.item_id = utm_links.item_id AND items.type = utm_links.item_type', 'left');

        // Filter berdasarkan ID yang sudah diambil
        $this->db->where_in('utm_links.id', $utm_link_ids);

        // Pertahankan urutan yang sama seperti subquery
        $order_ids = implode(',', $utm_link_ids);
        if (!empty($order_ids)) {
            $this->db->order_by("FIELD(utm_links.id, $order_ids)");
        }

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

        // OPTIMASI 4: Buat query terpisah untuk statistik clicks untuk setiap link
        foreach ($utm_links as &$link) {
            // Query click statistics untuk link ini saja dengan batasan waktu
            $url_base = $this->db->escape(substr($link['url'], 0, strpos($link['url'], '?') ?: strlen($link['url'])));

            // OPTIMASI 5: Batasi rentang waktu data link_tracks
            $timeLimit = '';
            // Gunakan date filter yang sama dengan utm_links jika ada
            if (!empty($dateFilter)) {
                switch ($dateFilter) {
                    case 'today':
                        $timeLimit = " AND DATE(created_at) = CURDATE()";
                        break;
                    case 'yesterday':
                        $timeLimit = " AND DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
                        break;
                    case 'last_7_days':
                        $timeLimit = " AND created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)";
                        break;
                    case 'last_30_days':
                        $timeLimit = " AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
                        break;
                    case 'this_month':
                        $timeLimit = " AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE())";
                        break;
                    case 'custom':
                        if (!empty($startDate) && !empty($endDate)) {
                            $startDateFormatted = date('Y-m-d', strtotime($startDate));
                            $endDateFormatted = date('Y-m-d', strtotime($endDate));
                            $timeLimit = " AND DATE(created_at) >= '$startDateFormatted' AND DATE(created_at) <= '$endDateFormatted'";
                        } else {
                            // Default: last 30 days if no date specified
                            $timeLimit = " AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
                        }
                        break;
                    default:
                        // Default: last 30 days
                        $timeLimit = " AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
                }
            } else {
                // Default: last 30 days
                $timeLimit = " AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
            }

            // OPTIMASI 6: Gunakan query langsung untuk statistik klik
            // OPTIMASI 6: Gunakan query langsung untuk statistik klik dengan semua parameter UTM
            $utm_source = $this->db->escape($link['utm_source'] ?: '');
            $utm_medium = $this->db->escape($link['utm_medium'] ?: '');
            $utm_campaign = $this->db->escape($link['utm_campaign'] ?: '');
            $utm_content = $this->db->escape($link['utm_content'] ?: '');
            $utm_term = $this->db->escape($link['utm_term'] ?: '');

            $clicks_query = $this->db->query(
                "SELECT 
                    COUNT(DISTINCT id) as total_clicks,
                    COUNT(DISTINCT ip_address) as unique_clicks
                FROM link_tracks
                WHERE link_url = SUBSTRING_INDEX({$url_base}, '?', 1)
                AND (
                    (utm_source = $utm_source) OR 
                    ($utm_source = '' AND (utm_source IS NULL OR utm_source = 'none')) OR 
                    ($utm_source = 'none' AND (utm_source IS NULL OR utm_source = ''))
                )
                AND (
                    (utm_medium = $utm_medium) OR 
                    ($utm_medium = '' AND (utm_medium IS NULL OR utm_medium = 'none')) OR 
                    ($utm_medium = 'none' AND (utm_medium IS NULL OR utm_medium = ''))
                )
                AND (
                    (utm_campaign = $utm_campaign) OR 
                    ($utm_campaign = '' AND (utm_campaign IS NULL OR utm_campaign = 'none')) OR 
                    ($utm_campaign = 'none' AND (utm_campaign IS NULL OR utm_campaign = ''))
                )
                AND (
                    (utm_content = $utm_content) OR 
                    ($utm_content = '' AND (utm_content IS NULL OR utm_content = 'none')) OR 
                    ($utm_content = 'none' AND (utm_content IS NULL OR utm_content = ''))
                )
                AND (
                    (utm_term = $utm_term) OR 
                    ($utm_term = '' AND (utm_term IS NULL OR utm_term = 'none')) OR 
                    ($utm_term = 'none' AND (utm_term IS NULL OR utm_term = ''))
                )
                $timeLimit"
            );
            $clicks_result = $clicks_query->row_array();

            $link['total_clicks'] = (int)$clicks_result['total_clicks'];
            $link['unique_clicks'] = (int)$clicks_result['unique_clicks'];

            // OPTIMASI 7: Gunakan query terpisah untuk orders
            $orders_query = $this->db->query(
                "SELECT 
                    COUNT(DISTINCT id_orders) as successful_orders,
                    COALESCE(SUM(grand_total_amount), 0) as total_revenue
                 FROM orders
                 WHERE source = " . $this->db->escape($link['utm_source']) . "
                 AND medium = " . $this->db->escape($link['utm_medium']) . "
                 AND campaign = " . $this->db->escape($link['utm_campaign']) . "
                 AND payment_status = 5
                 AND DATE(created_at) $timeLimit"
            );
            $orders_result = $orders_query->row_array();

            $link['successful_orders'] = (int)$orders_result['successful_orders'];
            $link['total_revenue'] = $orders_result['total_revenue'];

            // Calculate conversion rate
            $link['conversion_rate'] = $link['unique_clicks'] > 0
                ? round(($link['successful_orders'] / $link['unique_clicks']) * 100, 2)
                : 0;

            // Format numbers for better readability
            $link['total_revenue'] = number_format($link['total_revenue'], 2);
        }

        // OPTIMASI 8: Terapkan post-sorting jika diperlukan
        if ($sort) {
            usort($utm_links, function ($a, $b) use ($sort) {
                switch ($sort) {
                    case 'total_click_banyak':
                        return $b['total_clicks'] - $a['total_clicks'];
                    case 'total_click_dikit':
                        return $a['total_clicks'] - $b['total_clicks'];
                    case 'unique_click_banyak':
                        return $b['unique_clicks'] - $a['unique_clicks'];
                    case 'unique_click_dikit':
                        return $a['unique_clicks'] - $b['unique_clicks'];
                    case 'order_banyak':
                        return $b['successful_orders'] - $a['successful_orders'];
                    case 'order_dikit':
                        return $a['successful_orders'] - $b['successful_orders'];
                    default:
                        return 0; // sudah diurutkan di query
                }
            });
        }

        return $utm_links;
    }


    public function generate()
    {
        $this->data['subview'] = 'admin/link/index';
        $this->load->view('admin/templates/header', $this->data_header);
        $this->load->view('admin/_layout_main', $this->data);
        $this->load->view('admin/templates/footer');
    }
    public function save_link()
    {
        $data = [
            'url' => $this->input->post('url'),
            'item_id' => $this->input->post('item_id'),
            'item_type' => $this->input->post('item_type'),
            'utm_source' => $this->input->post('utm_source'),
            'utm_medium' => $this->input->post('utm_medium'),
            'utm_campaign' => $this->input->post('utm_campaign')
        ];

        $result = $this->insert($data);

        if ($result) {
            $response = [
                'success' => true,
                'message' => 'Link berhasil disimpan',
                'id' => $result
            ];
        } else {
            $response = [
                'success' => false,
                'message' => 'Gagal menyimpan link'
            ];
        }

        $this->output->set_content_type('application/json')
            ->set_output(json_encode($response));
    }

    private function insert($data)
    {
        $this->db->insert('utm_links', $data);
        return $this->db->insert_id();
    }

    public function get_recent_links()
    {
        $this->db->order_by('created_at', 'DESC');
        $results = $this->db->get('utm_links')->result();

        echo json_encode($results);
    }
}