<?php

namespace App\Models;

use CodeIgniter\Model;

class ScriptModel extends Model
{
    protected $DBGroup = 'default';

    public function get_fresh_lead()
    {
        return $this->db->table('leads')
            ->orderBy('id', 'desc')
            ->get()
            ->getResult();
    }

    public function get_total_leads()
    {
        $request = service('request');
        $uri     = service('uri');
        $session = session();

        $start = $uri->getSegment(3);
        $START = $start ? $start : '0';

        $lstage     = $request->getVar('lstage');
        $lsource    = $request->getVar('lsource');
        $cdate_from = $request->getVar('cdate_from');
        $cdate_to   = $request->getVar('cdate_to');
        $page       = $request->getVar('page');

        $user_type = $session->get('user_type');
        $user_id   = $session->get('user_id');

        $where = "status='1' AND delete_status='0'";

        if ($user_type == '0') {
            $center_id = get_sub_center_by_id($user_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND leads.user_id IN($center_id)";
            }
        }

        if ($user_type == '2') {
            $parent_id = $session->get('parent_id');
            $center_id = get_team_lead_center($parent_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND leads.user_id IN($center_id)";
            }
        }

        if ($lsource != '') {
            $where .= " AND lead_source='$lsource'";
        }

        if ($cdate_from != '') {
            $created_date = date('Y-m-d', strtotime($cdate_from));
            if ($cdate_to == $cdate_from) {
                $where .= " AND DATE(leads.created_at) = '$created_date'";
            } else {
                $where .= " AND leads.created_at >= '$created_date'";
            }
        } else {
            $where .= " AND MONTH(leads.assigned_date) >= MONTH(CURRENT_DATE())";
        }

        if ($cdate_from != '' && $cdate_to != '') {
            if ($cdate_to > $cdate_from) {
                $cdate_to = date('Y-m-d', strtotime($cdate_to));
                $where .= " AND leads.created_at <= '$cdate_to'";
            }
        }

        $where .= " ORDER BY id desc";

        $query = "SELECT * FROM leads WHERE $where";
        return $this->db->query($query)->getResult();
    }

    public function lead_activity_by_id($lead_id)
    {
        return $this->db->table('lead_activity')
            ->select('count(id) as total,activity_type')
            ->where('lead_id', $lead_id)
            ->where('activity_type <>', '')
            ->groupBy('activity_type')
            ->get()
            ->getResultArray();
    }

    public function lead_opportunity($lead_id)
    {
        return $this->db->table('lead_activity')
            ->select('count(id) as total,opportunity')
            ->where('lead_id', $lead_id)
            ->where('opportunity <>', '')
            ->groupBy('opportunity,created_at')
            ->orderBy('created_at', 'desc')
            ->get()
            ->getRowArray();
    }

    public function get_sales_leads()
    {
        $request = service('request');
        $session = session();

        $cdate_from = $request->getVar('cdate_from');
        $cdate_to   = $request->getVar('cdate_to');

        $user_type = $session->get('user_type');
        $user_id   = $session->get('user_id');

        $where = "status='1'";

        if ($user_type == '0') {
            $center_id = get_sub_center_by_id($user_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND sales.user_id IN($center_id)";
            }
        }

        if ($user_type == '2') {
            $parent_id = $session->get('parent_id');
            $center_id = get_team_lead_center($parent_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND sales.user_id IN($center_id)";
            }
        }

        if ($cdate_from != '') {
            $created_date = date('Y-m-d', strtotime($cdate_from));
            if ($cdate_to == $cdate_from) {
                $where .= " AND DATE(sales.sales_date) = date('$created_date')";
            } else {
                $where .= " AND sales.sales_date >= date('$created_date')";
            }
        } else {
            $where .= " AND MONTH(sales.sales_date) >= MONTH(CURRENT_DATE())";
        }

        if ($cdate_from != '' && $cdate_to != '') {
            if ($cdate_to > $cdate_from) {
                $cdate_to = date('Y-m-d', strtotime($cdate_to));
                $where .= " AND sales.sales_date <= date('$cdate_to')";
            }
        }

        $where .= " AND sales_status IN('Admission Done','Registration Done')";

        $query = "SELECT * FROM sales WHERE $where";
        return $this->db->query($query)->getResult();
    }

    public function get_lead_summary()
    {
        $request = service('request');
        $session = session();

        $cdate_from = $request->getVar('cdate_from');
        $cdate_to   = $request->getVar('cdate_to');

        $user_type = $session->get('user_type');
        $user_id   = $session->get('user_id');

        $where = "status='1' AND delete_status='0'";

        if ($user_type == '0') {
            $center_id = get_sub_center_by_id($user_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND leads.user_id IN($center_id)";
            }
        }

        if ($user_type == '2') {
            $parent_id = $session->get('parent_id');
            $center_id = get_team_lead_center($parent_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND leads.user_id IN($center_id)";
            }
        }

        if ($cdate_from != '') {
            $created_date = date('Y-m-d', strtotime($cdate_from));
            if ($cdate_to == $cdate_from) {
                $where .= " AND DATE(leads.assigned_date) = '$created_date'";
            } else {
                $where .= " AND leads.assigned_date >= '$created_date'";
            }
        } else {
            $where .= " AND MONTH(leads.assigned_date) >= MONTH(CURRENT_DATE())";
        }

        if ($cdate_from != '' && $cdate_to != '') {
            if ($cdate_to > $cdate_from) {
                $cdate_to = date('Y-m-d', strtotime($cdate_to));
                $where .= " AND leads.assigned_date <= '$cdate_to'";
            }
        }

        $query = "SELECT lead_source, count(id) as total FROM leads WHERE $where GROUP BY lead_source";
        return $this->db->query($query)->getResult();
    }

    public function get_source_leads()
    {
        $request = service('request');
        $session = session();

        $cdate_from = $request->getVar('cdate_from');
        $cdate_to   = $request->getVar('cdate_to');

        $user_type = $session->get('user_type');
        $user_id   = $session->get('user_id');

        $where = "status='1' AND delete_status='0'";

        if ($user_type == '0') {
            $center_id = get_sub_center_by_id($user_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND leads.user_id IN($center_id)";
            }
        }

        if ($user_type == '2') {
            $parent_id = $session->get('parent_id');
            $center_id = get_team_lead_center($parent_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND leads.user_id IN($center_id)";
            }
        }

        if ($cdate_from != '') {
            $created_date = date('Y-m-d', strtotime($cdate_from));
            if ($cdate_to == $cdate_from) {
                $where .= " AND DATE(leads.assigned_date) = '$created_date'";
            } else {
                $where .= " AND leads.assigned_date >= '$created_date'";
            }
        } else {
            $where .= " AND MONTH(leads.assigned_date) >= MONTH(CURRENT_DATE())";
        }

        if ($cdate_from != '' && $cdate_to != '') {
            if ($cdate_to > $cdate_from) {
                $cdate_to = date('Y-m-d', strtotime($cdate_to));
                $where .= " AND leads.assigned_date <= '$cdate_to'";
            }
        }

        $query = "SELECT user_id,(select activity_type from lead_activity WHERE lead_id=leads.id ORDER BY id DESC LIMIT 1) as activity_type, lead_source, count(id) as total FROM leads WHERE $where GROUP BY user_id,lead_source,activity_type";
        return $this->db->query($query)->getResult();
    }

    public function get_conversion_leads()
    {
        $request = service('request');
        $session = session();

        $cdate_from = $request->getVar('cdate_from');
        $cdate_to   = $request->getVar('cdate_to');

        $user_type = $session->get('user_type');
        $user_id   = $session->get('user_id');

        $where = "status='1' AND delete_status='0'";

        if ($user_type == '0') {
            $center_id = get_sub_center_by_id($user_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND leads.user_id IN($center_id)";
            }
        }

        if ($user_type == '2') {
            $parent_id = $session->get('parent_id');
            $center_id = get_team_lead_center($parent_id, 'string');
            if (empty($center_id) === false) {
                $where .= " AND leads.user_id IN($center_id)";
            }
        }

        if ($cdate_from != '') {
            $created_date = date('Y-m-d', strtotime($cdate_from));
            if ($cdate_to == $cdate_from) {
                $where .= " AND DATE(leads.assigned_date) = '$created_date'";
            } else {
                $where .= " AND leads.assigned_date >= '$created_date'";
            }
        } else {
            $where .= " AND MONTH(leads.assigned_date) >= MONTH(CURRENT_DATE())";
        }

        if ($cdate_from != '' && $cdate_to != '') {
            if ($cdate_to > $cdate_from) {
                $cdate_to = date('Y-m-d', strtotime($cdate_to));
                $where .= " AND leads.assigned_date <= '$cdate_to'";
            }
        }

        $query = "SELECT user_id,lead_source, count(id) as total,(select sales_status from sales WHERE lead_id=leads.id AND (sales_status='Registration Done' OR sales_status='Admission Done') ORDER BY id DESC LIMIT 1) as sales_status FROM leads WHERE $where GROUP BY user_id,lead_source,sales_status";
        return $this->db->query($query)->getResult();
    }

    public function add_leads($data)
    {
        $row = [
            'user_id'        => $data['user_id'],
            'name'           => ucwords(strtolower(trim($data['name']))),
            'phone'          => trim($data['phone']),
            'email'          => trim($data['email']),
            'enquiry_for'    => trim($data['course']),
            'course'         => trim($data['course']),
            'specialization' => trim($data['specialization']),
            'university'     => trim($data['university']),
            'state'          => trim($data['state']),
            'city'           => trim($data['city']),
            'lead_source'    => trim($data['lead_source']),
            'lead_origin'    => trim($data['lead_origin']),
            'created_by'     => ucwords(strtolower(trim($data['created_by']))),
            'created_at'     => trim($data['created_at']),
            'updated_at'     => trim($data['updated_at']),
        ];

        $this->db->table('leads')->insert($row);
        return $this->db->insertID();
    }

    public function add_activity($data)
    {
        $activity_type = $this->get_disposition_by_name(trim($data['activity_type']));

        $row = [
            'lead_id'       => $data['lead_id'],
            'user_id'       => $data['user_id'],
            'activity_type' => $activity_type['id'],
            'created_by'    => trim($data['created_by']),
            'created_at'    => trim($data['created_at']),
        ];

        $this->db->table('lead_activity')->insert($row);
        return $this->db->insertID();
    }

    public function get_disposition_by_name($name)
    {
        return $this->db->table('disposition')
            ->where('name', $name)
            ->get()
            ->getRowArray();
    }

    public function get_lead_by_email($email)
    {
        return $this->db->table('leads')
            ->where('email', $email)
            ->get()
            ->getRowArray();
    }

    public function get_last_activity($id)
    {
        return $this->db->table('lead_activity')
            ->where('lead_id', $id)
            ->orderBy('id', 'desc')
            ->limit(1)
            ->get()
            ->getRowArray();
    }
}