<?php

namespace app\services\leads;

use app\services\AbstractKanban;

class LeadsKanban extends AbstractKanban
{
    protected $customFilters = [];
    public function setCustomFilters($filters)
    {
        if ($filters) {
            $this->customFilters = $filters;
            $this->ci->session->set_userdata('kanban_filters', $filters);
        } elseif ($this->ci->session->has_userdata('kanban_filters')) {
            $this->customFilters = $this->ci->session->userdata('kanban_filters');
        }
    }
    protected function table(): string
    {
        return 'leads';
    }

    public function defaultSortDirection()
    {
        return get_option('default_leads_kanban_sort_type');
    }

    public function defaultSortColumn()
    {
        return get_option('default_leads_kanban_sort');
    }

    public function limit()
    {
        return get_option('leads_kanban_limit');
    }

    protected function applySearchQuery($q): self
    {
        if (!startsWith($q, '#')) {
            $q = $this->ci->db->escape_like_str($this->q);
            $this->ci->db->where('(' . db_prefix() . 'leads.name LIKE "%' . $q . '%" ESCAPE \'!\' OR ' . db_prefix() . 'leads_sources.name LIKE "%' . $q . '%" ESCAPE \'!\' OR ' . db_prefix() . 'leads.email LIKE "%' . $q . '%" ESCAPE \'!\' OR ' . db_prefix() . 'leads.phonenumber LIKE "%' . $q . '%" ESCAPE \'!\' OR ' . db_prefix() . 'leads.company LIKE "%' . $q . '%" ESCAPE \'!\' OR CONCAT(' . db_prefix() . 'staff.firstname, \' \', ' . db_prefix() . 'staff.lastname) LIKE "%' . $q . '%" ESCAPE \'!\')');
        } else {
            $this->ci->db->where(db_prefix() . 'leads.id IN
                (SELECT rel_id FROM ' . db_prefix() . 'taggables WHERE tag_id IN
                (SELECT id FROM ' . db_prefix() . 'tags WHERE name="' . $this->ci->db->escape_str(strafter($q, '#')) . '")
                AND ' . db_prefix() . 'taggables.rel_type=\'lead\' GROUP BY rel_id HAVING COUNT(tag_id) = 1)
                ');
        }

        return $this;
    }

    protected function initiateQuery(): self
    {
        // $this->ci->db->select(db_prefix() . 'leads.title, ' . db_prefix() . 'leads.website, ' . db_prefix() . 'leads.lead_value, ' . db_prefix() . 'leads.address, ' . db_prefix() . 'leads.city, ' . db_prefix() . 'leads.state, ' . db_prefix() . 'leads.country, ' . db_prefix() . 'leads.zip, ' . db_prefix() . 'leads.name as lead_name, '  . db_prefix() . 'leads_sources.name as source_name,' . db_prefix() . 'leads.id as id,' . db_prefix() . 'leads.assigned,' . db_prefix() . 'leads.email,' . db_prefix() . 'leads.phonenumber,' . db_prefix() . 'leads.company,' . db_prefix() . 'leads.dateadded,' . db_prefix() . 'leads.status,' . db_prefix() . 'leads.ins_in,' . db_prefix() . 'leads.id_verification_completed_at,' . db_prefix() . 'leads.idv_pass,' . db_prefix() . 'leads.file_opened_completed_at,' . db_prefix() . 'leads.active_case,' . db_prefix() . 'leads.lastcontact,(SELECT COUNT(*) FROM ' . db_prefix() . 'clients WHERE leadid=' . db_prefix() . 'leads.id) as is_lead_client, (SELECT COUNT(id) FROM ' . db_prefix() . 'files WHERE rel_id=' . db_prefix() . 'leads.id AND rel_type="lead") as total_files, (SELECT COUNT(id) FROM ' . db_prefix() . 'notes WHERE rel_id=' . db_prefix() . 'leads.id AND rel_type="lead") as total_notes,(SELECT GROUP_CONCAT(name SEPARATOR ",") FROM ' . db_prefix() . 'taggables JOIN ' . db_prefix() . 'tags ON ' . db_prefix() . 'taggables.tag_id = ' . db_prefix() . 'tags.id WHERE rel_id = ' . db_prefix() . 'leads.id and rel_type="lead" ORDER by tag_order ASC) as tags');
        // $this->ci->db->from('leads');
        // $this->ci->db->join(db_prefix() . 'leads_sources', db_prefix() . 'leads_sources.id=' . db_prefix() . 'leads.source');
        // $this->ci->db->join(db_prefix() . 'staff', db_prefix() . 'staff.staffid=' . db_prefix() . 'leads.assigned', 'left');
        $this->ci->db->select([
            db_prefix() . 'leads.id',
            db_prefix() . 'leads.name as lead_name',
            db_prefix() . 'leads.company',
            db_prefix() . 'leads.email',
            db_prefix() . 'leads.address',
            db_prefix() . 'leads.city',
            db_prefix() . 'leads.state',
            db_prefix() . 'leads.country',
            db_prefix() . 'leads.zip',
            db_prefix() . 'leads.phonenumber',
            db_prefix() . 'leads.status',
            db_prefix() . 'leads.assigned',
            db_prefix() . 'leads.dateadded',
            db_prefix() . 'leads.lastcontact',
            db_prefix() . 'leads.active_case',
            db_prefix() . 'leads_sources.name as source_name',
        ]);

        $this->ci->db->from(db_prefix() . 'leads');

        // Lightweight joins
        $this->ci->db->join(
            db_prefix() . 'leads_sources',
            db_prefix() . 'leads_sources.id=' . db_prefix() . 'leads.source',
            'left'
        );
        $this->ci->db->join(
            db_prefix() . 'staff',
            db_prefix() . 'staff.staffid=' . db_prefix() . 'leads.assigned',
            'left'
        );

               // Apply custom filters
        if (!empty($this->customFilters['id'])) {
            $this->ci->db->where(db_prefix() . 'leads.id', $this->customFilters['id']);
        }
        if (!empty($this->customFilters['kanban_offer_in_out'])) {
            foreach ($this->customFilters['kanban_offer_in_out'] as $option) {
                if ($option == 'ids_not_completed') {
                    $this->ci->db->where(db_prefix() . 'leads.file_opened_completed_at IS NULL' );
                    $this->ci->db->where(db_prefix() . 'leads.status = 1' );
                }
        
                if ($option == 'ids_completed') {
                    $this->ci->db->where(db_prefix() . 'leads.file_opened_completed_at IS NOT NULL' );
                    $this->ci->db->where(db_prefix() . 'leads.status != 1' );
                }
        
                if ($option == 'idv_in') {
                    // $this->ci->db->where(db_prefix() . 'leads.idv_pass = 1');
                    $this->ci->db->where_in(db_prefix() . 'leads.status', [2,3,4,5,6,7,9]);
                    $this->ci->db->where(db_prefix() . 'leads.id_verification_completed_at IS NOT NULL');
                }
                if ($option == 'idv_not_in') {
                    $this->ci->db->where(db_prefix() . 'leads.idv_pass = 0');
                    $this->ci->db->where(db_prefix() . 'leads.id_verification_completed_at IS NULL');
                    $this->ci->db->where_in(db_prefix() . 'leads.status', [1, 8]);
                }
                if ($option == 'ins_in') {
                    $this->ci->db->where(db_prefix() . 'leads.ins_in = 1');
                }
            }
        }
        // if (!empty($this->customFilters['name'])) {
        //     $this->ci->db->like(db_prefix() . 'leads.name', $this->customFilters['name']);
        // }
        if (!empty($this->customFilters['name'])) {
            $name = $this->customFilters['name'];

            // Join leads_borrow to enable borrower searching
            $this->ci->db->join(db_prefix() . 'leads_borrow', db_prefix() . 'leads_borrow.lead_id = ' . db_prefix() . 'leads.id', 'left');

            $this->ci->db->group_start(); // Start bracket
            $this->ci->db->like(db_prefix() . 'leads.name', $name);
            $this->ci->db->or_like(db_prefix() . 'leads_borrow.borrow_firstname', $name);
            $this->ci->db->or_like(db_prefix() . 'leads_borrow.borrow_lastname', $name);
            $this->ci->db->or_like(db_prefix() . 'leads_borrow.borrow_middlename', $name);
            $this->ci->db->group_end(); // End bracket
        }


        if (!empty($this->customFilters['email'])) {
        
            $this->ci->db->like(db_prefix() . 'leads.email', $this->customFilters['email']);
        }

        if (!empty($this->customFilters['phone'])) {
            $this->ci->db->like(db_prefix() . 'leads.phonenumber', $this->customFilters['phone']);
        }

        if (!empty($this->customFilters['address'])) {
            $this->ci->db->like(db_prefix() . 'leads.address', $this->customFilters['address']);
        }

        if (!empty($this->customFilters['state'])) {
            $this->ci->db->like(db_prefix() . 'leads.state', $this->customFilters['state']);
        }

        if (!empty($this->customFilters['group_ids'])) {
            $this->ci->db->where_in(db_prefix() . 'leads.assign_group', $this->customFilters['group_ids']);
        }
        if (!empty($this->customFilters['brokerage_filter'])) {
            $this->ci->db->where_in(db_prefix() . 'leads.brokerage_id', $this->customFilters['brokerage_filter']);
        }
        if (!empty($this->customFilters['brokers_filter'])) {
            $this->ci->db->where_in(db_prefix() . 'leads.addedfrom', $this->customFilters['brokers_filter']);
        }        
        if (!empty($this->customFilters['status'])) {
            $this->ci->db->where_in(db_prefix() . 'leads.status', $this->customFilters['status']);
        }
        if (!empty($this->customFilters['kanban_active_record'])) {
            $activeStatus = $this->customFilters['kanban_active_record'];
        
            if (in_array('active', $activeStatus)) {
                $this->ci->db->where(db_prefix() . 'leads.active_case', 'Active');
            } elseif (in_array('closed', $activeStatus)) {
                $this->ci->db->where(db_prefix() . 'leads.active_case', 'Closed');
            }
        }
             
        
        // $this->ci->db->where('status', $this->status);

        // if (staff_cant('view', 'leads')) {
        //     $this->ci->db->where('(assigned = ' . get_staff_user_id() . ' OR addedfrom=' . get_staff_user_id() . ' OR is_public=1)');
        // }
        // $this->ci->load->library('session');
    
        // $this->ci->db->select('role');
        // $this->ci->db->from('tblstaff');
        // $this->ci->db->where('staffid', $this->ci->session->userdata('staff_user_id'));
        // $role = $this->ci->db->get()->row()->role;
        
        // $this->ci->db->select(db_prefix() . 'leads.title, ' . db_prefix() . 'leads.website, ' . db_prefix() . 'leads.lead_value, ' . db_prefix() . 'leads.address, ' . db_prefix() . 'leads.city, ' . db_prefix() . 'leads.state, ' . db_prefix() . 'leads.country, ' . db_prefix() . 'leads.zip, ' . db_prefix() . 'leads.name as lead_name,' . db_prefix() . 'leads_sources.name as source_name,' . db_prefix() . 'leads.id as id,' . db_prefix() . 'leads.assigned,' . db_prefix() . 'leads.email,' . db_prefix() . 'leads.phonenumber,' . db_prefix() . 'leads.company,' . db_prefix() . 'leads.dateadded,' . db_prefix() . 'leads.status,' . db_prefix() . 'leads.lastcontact,(SELECT COUNT(*) FROM ' . db_prefix() . 'clients WHERE leadid=' . db_prefix() . 'leads.id) as is_lead_client, (SELECT COUNT(id) FROM ' . db_prefix() . 'files WHERE rel_id=' . db_prefix() . 'leads.id AND rel_type="lead") as total_files, (SELECT COUNT(id) FROM ' . db_prefix() . 'notes WHERE rel_id=' . db_prefix() . 'leads.id AND rel_type="lead") as total_notes,(SELECT GROUP_CONCAT(name SEPARATOR ",") FROM ' . db_prefix() . 'taggables JOIN ' . db_prefix() . 'tags ON ' . db_prefix() . 'taggables.tag_id = ' . db_prefix() . 'tags.id WHERE rel_id = ' . db_prefix() . 'leads.id and rel_type="lead" ORDER by tag_order ASC) as tags');
        // $this->ci->db->from('leads');
        // $this->ci->db->join(db_prefix() . 'leads_sources', db_prefix() . 'leads_sources.id=' . db_prefix() . 'leads.source');
        // $this->ci->db->join(db_prefix() . 'staff', db_prefix() . 'staff.staffid=' . db_prefix() . 'leads.assigned', 'left');
    
        // // Check if $this->status is 1, then get records with status 1 and 7
        if ($this->status == 1) {
            $this->ci->db->where_in(db_prefix() . 'leads.status', [1,8]);
        }else if ($this->status == 2) {
            $this->ci->db->where_in(db_prefix() . 'leads.status', [2,7]);
        } else {
            // Otherwise, filter by the exact status
            $this->ci->db->where(db_prefix() . 'leads.status', $this->status);
        }
    
        // if ($role == 2) { // For brokers
        //     $this->ci->db->where_in('addedfrom', $this->ci->session->userdata('staff_user_id'));
        // }

        return $this;
    }
}
