<?php
class Analyticsmodel extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    private function applyFilters($branch_id, $executive_id, $ignore_global = false) {
        // Base Role Filters
        if ($branch_id) {
            $this->db->where('l.branch_id', $branch_id);
        }
        if ($executive_id) {
            $this->db->where('l.executive_id', $executive_id);
        }

        if ($ignore_global) {
            return;
        }
        
        // Global UI Filters
        $filter_branch = $this->input->get('filter_branch_id');
        $filter_product = $this->input->get('filter_product_id');
        $date_from = $this->input->get('date_from');
        $date_to = $this->input->get('date_to');

        if (!empty($filter_branch)) {
            $this->db->where('l.branch_id', $filter_branch);
        }
        if (!empty($filter_product)) {
            $this->db->where('l.product_id', $filter_product);
        }
        if (!empty($date_from)) {
            $this->db->where('l.created_at >=', $date_from . ' 00:00:00');
        }
        if (!empty($date_to)) {
            $this->db->where('l.created_at <=', $date_to . ' 23:59:59');
        }
    }

    private function applyDateRangeFilter($date_range) {
        if (!$date_range || $date_range == 'default') return;

        if ($date_range == 'last_week') {
            $this->db->where('l.created_at >=', date('Y-m-d', strtotime('-7 days')));
        } elseif ($date_range == 'this_month') {
            $this->db->where('l.created_at >=', date('Y-m-01'));
        } elseif ($date_range == '3_months') {
            $this->db->where('l.created_at >=', date('Y-m-d', strtotime('-3 months')));
        } elseif ($date_range == '6_months') {
            $this->db->where('l.created_at >=', date('Y-m-d', strtotime('-6 months')));
        }
    }

    // 20. Executive KPI Cards
    public function getExecutiveKPIs($branch_id = null, $executive_id = null) {
        $kpis = [];

        // Total Leads
        $this->db->select('COUNT(*) as total');
        $this->db->from('tbl_leads l');
        $this->applyFilters($branch_id, $executive_id);
        $kpis['total_leads'] = $this->db->get()->row()->total;

        // New Leads Today
        $this->db->select('COUNT(*) as total');
        $this->db->from('tbl_leads l');
        $this->db->where('DATE(l.created_at)', date('Y-m-d'));
        $this->applyFilters($branch_id, $executive_id);
        $kpis['new_leads_today'] = $this->db->get()->row()->total;

        // Converted Leads
        $this->db->select('COUNT(*) as total');
        $this->db->from('tbl_leads l');
        $this->db->where_in('l.lead_status', ['Disbursed / Account Opened', 'Completed', 'Login Done', 'Sanctioned / Approved']);
        $this->applyFilters($branch_id, $executive_id);
        $kpis['converted_leads'] = $this->db->get()->row()->total;

        // Conversion Rate
        $kpis['conversion_rate'] = ($kpis['total_leads'] > 0) ? round(($kpis['converted_leads'] / $kpis['total_leads']) * 100, 2) : 0;

        // Upcoming Follow-ups (>= Today)
        $this->db->select('COUNT(*) as total');
        $this->db->from('tbl_leads l');
        $this->db->where('l.next_followup_date >=', date('Y-m-d'));
        $this->applyFilters($branch_id, $executive_id);
        $kpis['upcoming_followups'] = $this->db->get()->row()->total;

        // Overdue Follow-ups (< Today)
        $this->db->select('COUNT(*) as total');
        $this->db->from('tbl_leads l');
        $this->db->where('l.next_followup_date <', date('Y-m-d'));
        $this->db->where('l.next_followup_date IS NOT NULL');
        $this->applyFilters($branch_id, $executive_id);
        $kpis['overdue_followups'] = $this->db->get()->row()->total;

        // Best Product
        $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');
        $this->applyFilters($branch_id, $executive_id);
        $this->db->group_by('l.product_id');
        $this->db->order_by('count', 'DESC');
        $this->db->limit(1);
        $best_product = $this->db->get()->row();
        $kpis['best_product'] = $best_product ? $best_product->product_name : 'N/A';

        // Best Source
        $this->db->select('l.customer_lead_source_type, COUNT(l.lead_id) as count');
        $this->db->from('tbl_leads l');
        $this->applyFilters($branch_id, $executive_id);
        $this->db->group_by('l.customer_lead_source_type');
        $this->db->order_by('count', 'DESC');
        $this->db->limit(1);
        $best_source = $this->db->get()->row();
        $kpis['best_source'] = $best_source ? $best_source->customer_lead_source_type : 'N/A';

        // Best Branch
        $this->db->select('b.branch_name, COUNT(l.lead_id) as count');
        $this->db->from('tbl_leads l');
        $this->db->join('tbl_branches b', 'l.branch_id = b.branch_id', 'left');
        $this->applyFilters($branch_id, $executive_id);
        $this->db->group_by('l.branch_id');
        $this->db->order_by('count', 'DESC');
        $this->db->limit(1);
        $best_branch = $this->db->get()->row();
        $kpis['best_branch'] = $best_branch ? $best_branch->branch_name : 'N/A';

        return $kpis;
    }

    // 1. Lead Funnel Analysis
    public function getLeadFunnel($branch_id = null, $executive_id = null, $date_range = 'default') {
        // Funnel Stages based on standard statuses: New Leads -> Contacted -> Interested -> Documents Pending -> Under Process (Application Submitted) -> Approved -> Disbursed/Closed
        $stages = [
            'New Leads' => ['New Lead'],
            'Contacted' => ['Contacted', 'Follow-up'],
            'Interested' => ['Interested'],
            'Documents Pending' => ['Document Pending'],
            'Application Submitted' => ['Login Done', 'Under Process'],
            'Approved' => ['Sanctioned / Approved'],
            'Disbursed/Closed Won' => ['Disbursed / Account Opened', 'Completed']
        ];

        $funnel_data = [];
        foreach ($stages as $label => $statuses) {
            $this->db->select('COUNT(*) as total');
            $this->db->from('tbl_leads l');
            $this->db->where_in('l.lead_status', $statuses);
            $this->applyFilters($branch_id, $executive_id);
            $this->applyDateRangeFilter($date_range);
            $count = $this->db->get()->row()->total;
            // Funnel expects sequential drops, but here we just get actual numbers in each stage bucket
            $funnel_data[] = ['stage' => $label, 'count' => $count];
        }
        return $funnel_data;
    }

    // 2. Product-wise Lead Distribution
    public function getProductDistribution($branch_id = null, $executive_id = null, $date_range = 'default') {
        $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');
        $this->applyFilters($branch_id, $executive_id);
        $this->applyDateRangeFilter($date_range);
        $this->db->group_by('l.product_id');
        $this->db->order_by('count', 'DESC');
        return $this->db->get()->result();
    }

    // 3. Product-wise Conversion Rate
    public function getProductConversionRate($branch_id = null, $executive_id = null, $date_range = 'default') {
        $this->db->select("
            p.product_name, 
            COUNT(l.lead_id) as total_leads,
            SUM(CASE WHEN l.lead_status IN ('Disbursed / Account Opened', 'Completed', 'Sanctioned / Approved') THEN 1 ELSE 0 END) as converted_leads
        ");
        $this->db->from('tbl_leads l');
        $this->db->join('tbl_products p', 'l.product_id = p.product_id', 'left');
        $this->applyFilters($branch_id, $executive_id);
        $this->applyDateRangeFilter($date_range);
        $this->db->group_by('l.product_id');
        $this->db->order_by('total_leads', 'DESC');
        return $this->db->get()->result();
    }

    // 4. Branch-wise Lead Performance
    public function getBranchPerformance($branch_id = null, $executive_id = null, $date_range = 'default') {
        $this->db->select("
            b.branch_name, 
            COUNT(l.lead_id) as total_leads,
            SUM(CASE WHEN l.lead_status IN ('Disbursed / Account Opened', 'Completed', 'Sanctioned / Approved') THEN 1 ELSE 0 END) as converted_leads
        ");
        $this->db->from('tbl_leads l');
        $this->db->join('tbl_branches b', 'l.branch_id = b.branch_id', 'left');
        $this->applyFilters($branch_id, $executive_id, true);
        $this->applyDateRangeFilter($date_range);
        $this->db->group_by('l.branch_id');
        $this->db->order_by('total_leads', 'DESC');
        return $this->db->get()->result();
    }

    // 5. Monthly Lead Trend
    public function getMonthlyLeadTrend($branch_id = null, $executive_id = null, $date_range = 'default') {
        $format = "'%Y-%m'"; // Default month-year
        if ($date_range == 'last_week' || $date_range == 'this_month') {
            $format = "'%Y-%m-%d'"; // Day-wise for short periods
        }

        $this->db->select("
            DATE_FORMAT(l.created_at, $format) as month_year,
            COUNT(l.lead_id) as generated,
            SUM(CASE WHEN l.lead_status IN ('Disbursed / Account Opened', 'Completed', 'Sanctioned / Approved') THEN 1 ELSE 0 END) as converted,
            SUM(CASE WHEN l.lead_status IN ('Lost', 'Rejected', 'Closed', 'Not Interested') THEN 1 ELSE 0 END) as lost
        ");
        $this->db->from('tbl_leads l');
        $this->applyFilters($branch_id, $executive_id, true);
        $this->applyDateRangeFilter($date_range);
        if ($date_range == 'default') {
            $this->db->where('l.created_at >=', date('Y-m-01', strtotime('-6 months')));
        }
        $this->db->group_by('month_year');
        $this->db->order_by('month_year', 'ASC');
        return $this->db->get()->result();
    }

    // 9. Upcoming Follow-up Dashboard
    public function getFollowupDashboard($branch_id = null, $executive_id = null, $date_range = 'default') {
        $today = date('Y-m-d');
        $tomorrow = date('Y-m-d', strtotime('+1 day'));
        $day_3 = date('Y-m-d', strtotime('+3 days'));
        $day_7 = date('Y-m-d', strtotime('+7 days'));

        $this->db->select("
            SUM(CASE WHEN l.next_followup_date < '$today' AND l.next_followup_date IS NOT NULL THEN 1 ELSE 0 END) as overdue,
            SUM(CASE WHEN l.next_followup_date = '$today' THEN 1 ELSE 0 END) as today,
            SUM(CASE WHEN l.next_followup_date = '$tomorrow' THEN 1 ELSE 0 END) as tomorrow,
            SUM(CASE WHEN l.next_followup_date > '$tomorrow' AND l.next_followup_date <= '$day_3' THEN 1 ELSE 0 END) as next_3_days,
            SUM(CASE WHEN l.next_followup_date > '$day_3' AND l.next_followup_date <= '$day_7' THEN 1 ELSE 0 END) as next_7_days
        ");
        $this->db->from('tbl_leads l');
        $this->applyFilters($branch_id, $executive_id);
        $this->applyDateRangeFilter($date_range);
        return $this->db->get()->row();
    }

    // 11. Lead Status Distribution
    public function getLeadStatusDistribution($branch_id = null, $executive_id = null, $date_range = 'default') {
        $this->db->select('l.lead_status, COUNT(l.lead_id) as count');
        $this->db->from('tbl_leads l');
        $this->applyFilters($branch_id, $executive_id);
        $this->applyDateRangeFilter($date_range);
        $this->db->group_by('l.lead_status');
        $this->db->order_by('count', 'DESC');
        return $this->db->get()->result();
    }

}
