<?php 

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

class Leadmodel extends CI_Model  {
    function __construct() {
        parent::__construct();
    }
    // Generate unique lead number
    public function generateLeadNumber() {
        $prefix = 'LEAD';
        $year = date('Y');
        $month = date('m');
        // Get last lead number for current month
        $this->db->select('lead_number');
        $this->db->from('tbl_leads');
        $this->db->like('lead_number', $prefix . $year . $month, 'after');
        $this->db->order_by('lead_id', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $last_number = $query->row()->lead_number;
            $last_sequence = intval(substr($last_number, -4));
            $new_sequence = $last_sequence + 1;
        } else {
            $new_sequence = 1;
        }
        return $prefix . $year . $month . str_pad($new_sequence, 4, '0', STR_PAD_LEFT);
    }
    // Get all Roles(No Admin)
    public function getRoles() {
        $sql = "SELECT * FROM tbl_admin WHERE role != 'Admin' AND status = 'Active'";
        $query = $this->db->query($sql);
        
        return $query->result();
    }
    // Chech Duplicate Mobile
    public function checkDuplicateLead($mobile) {
        $sql = "SELECT lead_id, customer_mobile FROM tbl_leads WHERE customer_mobile = '".$mobile."'";
        // echo $sql;EXIT;
        $query = $this->db->query($sql);
        return $query->row_array();
    }
    // Create new lead
    public function createLead($lead_data, $product_data) {
        $this->db->trans_start();
        
        // Generate lead number
        $lead_data['lead_number'] = $this->generateLeadNumber();
        
        // Insert lead
        $this->db->insert('tbl_leads', $lead_data);
        $lead_id = $this->db->insert_id();
        
        // Insert product details
        if ($lead_id && !empty($product_data)) {
            $product_data['lead_id'] = $lead_id;
            $this->db->insert('tbl_lead_products', $product_data);
        }
        
        $this->db->trans_complete();
        
        if ($this->db->trans_status() === FALSE) {
            return false;
        }
        return $lead_id;
    }
    // Get all leads with filters
    public function getLeads($filters = array()) {
        $this->db->select('*');
        $this->db->from('vw_leads_list');
        // Apply filters
        if (!empty($filters['branch_id'])) {
            $this->db->where('branch_id', $filters['branch_id']);
        }
        if (!empty($filters['executive_id'])) {
            $this->db->where('executive_id', $filters['executive_id']);
        }
        if (!empty($filters['lead_status'])) {
            $this->db->where('lead_status', $filters['lead_status']);
        }
        if (!empty($filters['product_id'])) {
            $this->db->where('product_id', $filters['product_id']);
        }
        if (!empty($filters['search'])) {
            $this->db->group_start();
            $this->db->like('customer_name', $filters['search']);
            $this->db->or_like('customer_mobile', $filters['search']);
            $this->db->or_like('customer_email', $filters['search']);
            $this->db->or_like('lead_number', $filters['search']);
            $this->db->group_end();
        }
        if (!empty($filters['from_date'])) {
            $this->db->where('DATE(created_at) >=', $filters['from_date']);
        }
        if (!empty($filters['to_date'])) {
            $this->db->where('DATE(created_at) <=', $filters['to_date']);
        }
        $this->db->order_by('lead_id', 'DESC');
        $query = $this->db->get();
        return $query->result();
    }
    // Get lead by ID with all details
    public function getLeadById($lead_id) {
        /*$this->db->select('l.*, b.branch_name, b.branch_code, a.name as executive_name, p.product_name, p.product_code, lp.*');
        $this->db->from('tbl_leads l');
        $this->db->join('tbl_branches b', 'l.branch_id = b.branch_id', 'left');
        $this->db->join('tbl_admin a', 'l.executive_id = a.admin_id', 'left');
        $this->db->join('tbl_products p', 'l.product_id = p.product_id', 'left');
        $this->db->join('tbl_lead_products lp', 'l.lead_id = lp.lead_id', 'left');
        $this->db->where('l.lead_id', $lead_id);
        $query = $this->db->get();
        return $query->row();*/

        $this->db->select('l.*, b.branch_name, b.branch_code, a.name AS executive_name, p.product_name, p.product_code, lp.*, lf.updated_status, lf.lead_priority, lf.followup_notes');
        $this->db->from('tbl_leads l');
        $this->db->join('tbl_branches b', 'l.branch_id = b.branch_id', 'left');
        $this->db->join('tbl_admin a', 'l.executive_id = a.admin_id', 'left');
        $this->db->join('tbl_products p', 'l.product_id = p.product_id', 'left');
        $this->db->join('tbl_lead_products lp', 'l.lead_id = lp.lead_id', 'left');

        // Get only the latest follow-up record for this lead
        $this->db->join(
            '(SELECT f1.*
              FROM tbl_lead_followups f1
              INNER JOIN (
                  SELECT lead_id, MAX(followup_id) AS max_followup_id
                  FROM tbl_lead_followups
                  GROUP BY lead_id
              ) f2 ON f1.followup_id = f2.max_followup_id
            ) lf',
            'l.lead_id = lf.lead_id',
            'left'
        );

        $this->db->where('l.lead_id', $lead_id);

        $query = $this->db->get();
        return $query->row();
    }
    // Update lead
    public function updateLead($lead_id, $lead_data, $product_data = null) {
        $this->db->trans_start();
        // Update lead
        $this->db->where('lead_id', $lead_id);
        $this->db->update('tbl_leads', $lead_data);
        // Update product details if provided
        if ($product_data !== null) {
            $this->db->where('lead_id', $lead_id);
            $existing = $this->db->get('tbl_lead_products');
            if ($existing->num_rows() > 0) {
                $this->db->where('lead_id', $lead_id);
                $this->db->update('tbl_lead_products', $product_data);
            } else {
                $product_data['lead_id'] = $lead_id;
                $this->db->insert('tbl_lead_products', $product_data);
            }
        }
        $this->db->trans_complete();
        return $this->db->trans_status();
    }
    // Add follow-up
    public function addFollowup($followup_data) {
        $this->db->trans_start();
        
        // Insert follow-up
        $this->db->insert('tbl_lead_followups', $followup_data);
        
        // Update lead status and next follow-up date
        $update_data = array(
            'lead_status' => $followup_data['updated_status'],
            'lead_priority' => $followup_data['lead_priority']
        );
        // echo '<pre>'; print_r($update_data); echo '</pre>'; EXIT;
        if (!empty($followup_data['next_followup_date'])) {
            $update_data['next_followup_date'] = $followup_data['next_followup_date'];
        }
        $this->db->where('lead_id', $followup_data['lead_id']);
        $this->db->update('tbl_leads', $update_data);
        $this->db->trans_complete();
        return $this->db->trans_status();
    }
    // Get follow-ups for a lead
    public function getLeadFollowups($lead_id) {
        $this->db->select('f.*, a.name as followup_by_name');
        $this->db->from('tbl_lead_followups f');
        $this->db->join('tbl_admin a', 'f.followup_by = a.admin_id', 'left');
        $this->db->where('f.lead_id', $lead_id);
        $this->db->order_by('f.followup_date', 'DESC');
        $query = $this->db->get();
        return $query->result();
    }
    // Get lead statistics
    public function getLeadStatistics($branch_id = null, $executive_id = null) {
        $this->db->select('lead_status, COUNT(*) as count');
        $this->db->from('tbl_leads');
        if ($branch_id) {
            $this->db->where('branch_id', $branch_id);
        }
        if ($executive_id) {
            $this->db->where('executive_id', $executive_id);
        }
        $this->db->group_by('lead_status');
        $query = $this->db->get();
        $stats = array(
            'New Lead' => 0,
            'Rejected' => 0,
            'Closed' => 0,
            'Completed' => 0,
            'Total' => 0
        );
        foreach ($query->result() as $row) {
            $stats[$row->lead_status] = $row->count;
            $stats['Total'] += $row->count;
        }
        return $stats;
    }
    // Get product-wise statistics
    public function getProductStatistics($branch_id = null, $executive_id = null) {
        $this->db->select('p.product_name, COUNT(l.lead_id) as count');
        $this->db->from('tbl_leads l');
        $this->db->join('tbl_products p', 'l.product_id = p.product_id', 'left');
        if ($branch_id) {
            $this->db->where('l.branch_id', $branch_id);
        }
        if ($executive_id) {
            $this->db->where('l.executive_id', $executive_id);
        }
        $this->db->group_by('l.product_id');
        $query = $this->db->get();
        return $query->result();
    }
    // Get branch-wise statistics
    public function getBranchStatistics($branch_id = null, $executive_id = null) {
        $sql = "SELECT b.branch_id, b.branch_name, COUNT(l.lead_id) AS count FROM tbl_branches b
            LEFT JOIN tbl_leads l ON b.branch_id = l.branch_id
            WHERE b.status = 'Active'";

            // Manager Branch Filter
            if (!empty($branch_id)) {
                $sql .= " AND l.branch_id = '".$branch_id."'";
            }
            // Executive Filter
            if (!empty($executive_id)) {
                $sql .= " AND l.executive_id = '".$executive_id."'";
            }
            $sql .= " GROUP BY b.branch_id ORDER BY count DESC";

            // echo $sql; EXIT;
            $query = $this->db->query($sql);
            return $query->result();
    }
    // Get Branch-wise MIS
    /*public function getBranchMISStatistics($branch_id = null, $executive_id = null) {
        $sql = "SELECT b.branch_id, b.branch_name, COUNT(l.lead_id) AS total_leads,
            COUNT(CASE 
                WHEN l.lead_status = 'New Lead' 
                THEN 1 
            END) AS new_leads,

            COUNT(CASE 
                WHEN l.lead_status = 'Closed' 
                THEN 1 
            END) AS closed_leads,

            COUNT(CASE 
                WHEN l.lead_status = 'Completed' 
                THEN 1 
            END) AS completed_leads,

            COUNT(CASE 
                WHEN l.lead_status = 'Rejected' 
                THEN 1 
            END) AS rejected_leads FROM tbl_branches b
        LEFT JOIN tbl_leads l ON b.branch_id = l.branch_id
        WHERE b.status = 'Active'";
        // Manager Branch Filter
        if (!empty($branch_id)) {
            $sql .= " AND l.branch_id = '".$branch_id."'";
        }
        // Executive Filter
        if (!empty($executive_id)) {
            $sql .= " AND l.executive_id = '".$executive_id."'";
        }
        $sql .= " GROUP BY b.branch_id ORDER BY total_leads DESC";

        $query = $this->db->query($sql);
        return $query->result();
    }*/
    public function getBranchProductMISStatistics($branch_id = null, $executive_id = null) {
        $sql = "SELECT 
                    b.branch_id,
                    b.branch_name,

                    p.product_id,
                    p.product_name,

                    COUNT(l.lead_id) AS total_leads,

                    COUNT(CASE 
                        WHEN l.lead_status = 'New Lead'
                        THEN 1
                    END) AS new_leads,

                    COUNT(CASE 
                        WHEN l.lead_status = 'Closed'
                        THEN 1
                    END) AS closed_leads,

                    COUNT(CASE 
                        WHEN l.lead_status = 'Completed'
                        THEN 1
                    END) AS completed_leads,

                    COUNT(CASE 
                        WHEN l.lead_status = 'Rejected'
                        THEN 1
                    END) AS rejected_leads

                FROM tbl_leads l

                LEFT JOIN tbl_branches b
                    ON l.branch_id = b.branch_id

                LEFT JOIN tbl_products p
                    ON l.product_id = p.product_id

                WHERE b.status = 'Active'
                AND p.status = 'Active'";

        // Manager Filter
        if (!empty($branch_id)) {
            $sql .= " AND l.branch_id = '".$branch_id."'";
        }

        // Executive Filter
        if (!empty($executive_id)) {
            $sql .= " AND l.executive_id = '".$executive_id."'";
        }

        $sql .= " GROUP BY l.branch_id, l.product_id
                  ORDER BY b.branch_name ASC, p.product_name ASC";

        $query = $this->db->query($sql);

        return $query->result();
    }
    // Get upcoming follow-ups
    public function getUpcomingFollowups($branch_id = null, $executive_id = null, $days = 7) {
        $this->db->select('*');
        $this->db->from('vw_leads_list');
        $this->db->where('lead_status', 'Follow-up');
        $this->db->where('next_followup_date <=', date('Y-m-d', strtotime("+$days days")));
        $this->db->where('next_followup_date >=', date('Y-m-d'));
        if ($branch_id) {
            $this->db->where('branch_id', $branch_id);
        }
        if ($executive_id) {
            $this->db->where('executive_id', $executive_id);
        }
        $this->db->order_by('next_followup_date', 'ASC');
        $query = $this->db->get();
        return $query->result();
    }
    // Delete lead
    public function deleteLead($lead_id) {
        $this->db->where('lead_id', $lead_id);
        return $this->db->delete('tbl_leads');
    }
    // Get all products category
    public function getProductsCategory() {
        $this->db->select('*');
        $this->db->from('tbl_product_category');
        $this->db->where('status', 'Active');
        $this->db->order_by('category_name', 'ASC');
        $query = $this->db->get();
        return $query->result();
    }
    public function getProductsByCategory($category_id) {
        $this->db->select('product_id, product_name');
        $this->db->from('tbl_products');
        $this->db->where('product_category_id', $category_id);
        $this->db->where('status', 'Active');
        $this->db->order_by('product_name', 'ASC');
        $query = $this->db->get();
        return $query->result();
    }
    // Get all products
    public function getProducts() {
        $this->db->select('*');
        $this->db->from('tbl_products');
        $this->db->where('status', 'Active');
        $this->db->order_by('product_name', 'ASC');
        $query = $this->db->get();
        return $query->result();
    }
    // Get all branches
    public function getBranches() {
        $this->db->select('*');
        $this->db->from('tbl_branches');
        $this->db->where('status', 'Active');
        $this->db->order_by('branch_name', 'ASC');
        $query = $this->db->get();
        return $query->result();
    }
    // Get executives by branch
    public function getExecutivesByBranch($branch_id) {
        $this->db->select('admin_id, name, email, role');
        $this->db->from('tbl_admin');
        $this->db->where('branch_id', $branch_id);
        $this->db->where('status', 'Active');
        $this->db->order_by('name', 'ASC');
        $query = $this->db->get();
        return $query->result();
    }
}
