<?php
    
    class Model_konsumen extends CI_model
    {
        public function __construct()
        {
            // Set table name
            $this->table = 'tb_users';
            $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
        }
        
        public function getPengguna($params = [])
        {
            $dbprefix = $this->db->dbprefix('tb_users');
            
            $this->db->select('*');
            $this->db->from($dbprefix);
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('nama_lengkap', $params['search']['keywords']);
                }
            }
            if (!empty($params['search']['sortBy'])) {
                $this->db->order_by('`tb_users`.`id_user`', $params['search']['sortBy']);
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
                } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('tb_users.id_user', $params['id']);
                    }
                    $query = $this->db->get();
                    $result = $query->row_array();
                    } else {
                    $this->db->order_by('tb_users.id_user', 'DESC');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                        } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
        
        public function get_detail($where)
        {
            $qry = "SELECT 
			`produk`.`title` AS `produk`,
			`invoice_detail`.`id_rincianinvoice`,
			`invoice_detail`.`id_invoice`,
			`invoice_detail`.`keterangan`,
			`invoice_detail`.`detail`,
			`invoice_detail`.`jumlah`,
			`invoice_detail`.`ukuran`,
			`invoice_detail`.`satuan`,
			`invoice_detail`.`status_hitung`,
			`invoice`.`id_transaksi`,
			`invoice`.`tgl_ambil`,
			`bahan`.`title` AS `bahan`,
			`tb_users`.`nama_lengkap` AS `fo`
			FROM
			`produk`
			INNER JOIN `invoice_detail` ON (`produk`.`id` = `invoice_detail`.`id_produk`)
			INNER JOIN `invoice` ON (`invoice_detail`.`id_invoice` = `invoice`.`id_invoice`)
			INNER JOIN `bahan` ON (`invoice_detail`.`id_bahan` = `bahan`.`id`)
			INNER JOIN `tb_users` ON (`invoice`.`id_user` = `tb_users`.`id_user`)
			WHERE
			$where AND `invoice_detail`.`jenis_cetakan`!='9'";
            $query = $this->db->query($qry);
            
            return $query->result();
        }
        
        public function grafik_konsumen($tahun)
        {
            $this->db->select('YEAR(tgl_bayar) as tahun, MONTH(tgl_bayar) as bulan');
            $this->db->select_sum('jml_bayar');
            $this->db->from('bayar_invoice_detail');
            $this->db->where('YEAR(tgl_bayar)', $tahun);
            $this->db->group_by(['MONTH(tgl_bayar)', 'YEAR(tgl_bayar)']);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
                } else {
                return false;
            }
        }
        
        public function getRows($params = [])
        {
            $this->db->select('
            `i`.`tgl_trx`,
			`i`.`id_transaksi`,
			`i`.`id_konsumen`,
			`i`.`id_dropshiper`,
			`i`.`potongan_harga`,
			`i`.`cashback`,
			`i`.`pajak`,
			`i`.`status`,
			`i`.`oto`,
			`i`.`lunas`,
			`i`.`cetak`,
			`k`.`id`,
			`k`.`nama`,
			`k`.`no_hp`,
			`k`.`tampil`,
			`k`.`perusahaan`,
            i.id_invoice,
            k.nama AS konsumen_nama,
            u.nama_lengkap AS user_nama,
            SUM(id.jumlah * id.harga) AS total_order,
            i.potongan_harga AS total_potongan_harga,
            (SELECT IFNULL(SUM(bd.jml_bayar), 0) FROM bayar_invoice_detail bd 
            WHERE bd.id_invoice = i.id_invoice) AS total_bayar,
            (SUM(id.jumlah * id.harga) - 
            (SELECT IFNULL(SUM(bd.jml_bayar), 0) FROM bayar_invoice_detail bd 
            WHERE bd.id_invoice = i.id_invoice)) AS sisa_bayar
            ');
            
            // From invoice table and joining with konsumen and tb_users
            $this->db->from('invoice i');
            $this->db->join('konsumen k', 'k.id = i.id_konsumen', 'left');
            $this->db->join('tb_users u', 'u.id_user = i.id_marketing', 'left'); // Adjust this join condition as needed
            $this->db->join('invoice_detail id', 'id.id_invoice = i.id_invoice', 'left');
            
            // Group by invoice ID
            $this->db->group_by('i.id_invoice');
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('`i`.`id_invoice`', $params['search']['keywords']);
                    $this->db->or_like('`k`.`nama`', $params['search']['keywords']);
                    $this->db->or_like('`k`.`no_hp`', $params['search']['keywords']);
                    $this->db->or_like('`u`.`nama_lengkap`', $params['search']['keywords']);
                    $this->db->or_like('`i`.`id_transaksi`', $params['search']['keywords']);
                    $this->db->or_like('`i`.`total_bayar`', $params['search']['keywords']);
                }
                if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
                    $this->db->where('i.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
                }
                if (!empty($params['search']['trx'])) {
                    if ($params['search']['trx'] == 1) {
                        $this->db->where('`i`.`status`', 'simpan');
                        $this->db->like('`i`.`lunas`', $params['search']['trx']);
                        } elseif ($params['search']['trx'] == 2) {
                        $this->db->like('`i`.`lunas`', 0);
                        } else {
                        $this->db->like('`i`.`status`', $params['search']['trx']);
                    }
                }
            }
            if (!empty($params['search']['sortBy'])) {
                if ($params['search']['sortBy'] == 'min_order') {
                    $this->db->order_by('`i`.`total_bayar`', 'ASC');
                    } elseif ($params['search']['sortBy'] == 'max_order') {
                    $this->db->order_by('`i`.`total_bayar`', 'DESC');
                    } else {
                    $this->db->order_by('`i`.`id_invoice`', $params['search']['sortBy']);
                }
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
                } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('i.id_invoice', $params['id']);
                    }
                    $query = $this->db->get();
                    $result = $query->row_array();
                    } else {
                    $this->db->order_by('i.id_invoice', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                        } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
         
        public function get_daily_buyers_data($month, $year) {
            // Prepare the first and last day of the month
            $first_day = "$year-$month-01";
            $last_day = date("Y-m-t", strtotime($first_day)); // Get the last day of the month
            
            $query = $this->db->query("
            SELECT 
            DATE(i.tgl_trx) AS transaction_date,
            COUNT(DISTINCT CASE WHEN k.tgl_daftar = i.tgl_trx THEN k.id END) AS new_buyers,
            COUNT(DISTINCT CASE WHEN k.tgl_daftar < i.tgl_trx THEN k.id END) AS old_buyers
            FROM 
            invoice i
            JOIN 
            konsumen k ON i.id_konsumen = k.id
            WHERE 
            i.tgl_trx BETWEEN '$first_day' AND '$last_day'
            GROUP BY 
            transaction_date
            ORDER BY 
            transaction_date
            ");
            
            return $query->result_array();
        }
        
        public function get_monthly_buyers_data($year) {
            $query = $this->db->query("
            SELECT 
            MONTH(i.tgl_trx) AS month,
            COUNT(DISTINCT CASE WHEN k.tgl_daftar = i.tgl_trx THEN k.id END) AS new_buyers,
            COUNT(DISTINCT CASE WHEN k.tgl_daftar < i.tgl_trx THEN k.id END) AS old_buyers
            FROM 
            invoice i
            JOIN 
            konsumen k ON i.id_konsumen = k.id
            WHERE 
            YEAR(i.tgl_trx) = '$year'
            GROUP BY 
            month
            ORDER BY 
            month
            ");
            
            return $query->result_array();
        }
    }
