<?php
namespace App\Model;
use App\Model\BaseModel;
use App\Core\Service\Logger;
use App\Library\Utilities;

class AdminClientModel extends BaseModel
{
    public $columns = [
        [
            "name" => "name","title" => "Name","class" => "sorting text-left pointer","style" => "min-width:150px","data_class" => "text-left",
            "data_column" => "name","data_link_string" => true,"sort_by" => "name", "wrapper" => "div"
        ],
        [
            "name" => "mobile_no","title" => "Phone No.","class" => "sorting text-center pointer","style" => "min-width:125px","data_class" => "text-center",
            "data_column" => "mobile_no","data_link_string" => false,"sort_by" => "mobile_no","wrapper" => "div"
        ],
        [
            "name" => "email_id","title" => "Email Id","class" => "sorting text-center pointer","data_class" => "text-left","data_column" => "email_id",
            "data_link_string" => false,"sort_by" => "email_id","wrapper" => "div"
        ],
        [
            "name" => "plan_name","title" => "Plan","class" => "sorting text-center pointer","style" => "min-width:100px","data_class" => "text-center",
            "data_column" => "plan_name","data_link_string" => false,"sort_by" => "plan_name","wrapper" => "div"
        ],
        [
            "name" => "user_count","title" => "License","class" => "sorting text-center pointer","style" => "min-width:100px","data_class" => "text-center",
            "data_column" => "user_count","data_link_string" => false,"sort_by" => "user_count","wrapper" => "div"
        ],
        [
            "name" => "add_on_agent","title" => "Add on","class" => "sorting text-center pointer","style" => "min-width:100px","data_class" => "text-center",
            "data_column" => "add_on_agent","data_link_string" => false,"sort_by" => "add_on_agent","wrapper" => "div"
        ],
        [
            "name" => "valid_from","title" => "Started At","class" => "sorting text-center pointer","style" => "min-width:125px",
            "data_class" => "text-center","data_column" => "start_date","data_link_string" => false,"sort_by" => "valid_from","wrapper" => "div"
        ],
        [
            "name" => "valid_date","title" => "Valid Up To","class" => "sorting text-center pointer","style" => "min-width:128px","data_class" => "text-center",
            "data_column" => "end_date","data_link_string" => false,"sort_by" => "valid_date","wrapper" => "div"
        ],
        [
            "name" => "days_remaining","title" => "Days Remaining","class" => "sorting text-center pointer","style" => "min-width:180px",
            "data_class" => "text-center","data_column" => "days_remaining","data_link_string" => false,"sort_by" => "days_remaining","wrapper" => "progress"
        ],
        [
            "name" => "status","title" => "Status","class" => "sorting text-center pointer","style" => "min-width:85px","data_class" => "text-center",
            "data_column" => "status","data_link_string" => false,"sort_by" => "status","wrapper" => "status"
        ]
    ];
    public function __construct() {
        parent::__construct();
    }
    private function prepareClientData($input) {
        try {
            $data = [];
            $data['name'] = !empty($input['name'])?$input['name']:'';
            $data['contact_person'] = !empty($input['contact_person'])?$input['contact_person']:'';
            $data['phone_code'] = !empty($input['phone_code'])?$input['phone_code']:'';
            $data['phone_no'] = !empty($input['phone_no'])?$input['phone_no']:'';
            $data['country_id'] = !empty($input['country_id'])?$input['country_id']:'';
            $data['state_id'] = !empty($input['state_id'])?$input['state_id']:'';
            $data['city'] = !empty($input['city'])?$input['city']:'';
            $data['pincode'] = !empty($input['pincode'])?$input['pincode']:'';
            $data['email_id'] = !empty($input['email_id'])?$input['email_id']:'';
            $data['address'] = !empty($input['address'])?$input['address']:'';
            $data['pan'] = !empty($input['pan'])?$input['pan']:'';
            $data['gstin'] = !empty($input['gstin'])?$input['gstin']:'';
            $data['status'] = isset($input['status'])?1:0;
            if(!empty($input['validity'])){
                $data['valid_from'] = !empty($input['validity'][0])?date('Y-m-d', strtotime($input['validity'][0])):NULL;
                $data['valid_date'] = !empty($input['validity'][1])?date('Y-m-d', strtotime($input['validity'][1])):NULL;
            }
            $data['plan_id'] = !empty($input['plan_id'])?$input['plan_id']:0;
            if(!empty($input['id'])) {
                $data['id'] = $input['id'];
            }
            if(!empty($input['password'])) {
                $data['password'] = password_hash($input['password'], PASSWORD_DEFAULT);
            }
            if(!empty($input['access_token'])) {
                $data['access_token'] = trim($input['access_token']);
            }
            return ['status' => true, 'data' => $data];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    private function preapareClientSettingsData($input) {
        try {
            $data = [];
            if(strtolower($input['setting_type']) == 'basic_settings') {
                $data['address'] = !empty($input['address'])?$input['address']:'';
                $data['state_id'] = !empty($input['state_id'])?$input['state_id']:0;
                $data['city'] = !empty($input['city'])?trim($input['city']):'';
                $data['pincode'] = !empty($input['pincode'])?trim($input['pincode']):'';
                $data['email_id'] = !empty($input['email_id'])?trim($input['email_id']):'';
                $data['alt_phone_code'] = !empty($input['alt_phone_code'])?trim($input['alt_phone_code']):'';
                $data['alt_phone_no'] = !empty($input['alt_phone_no'])?trim($input['alt_phone_no']):'';
                $data['website'] = !empty($input['website'])?trim($input['website']):'';
                $data['signatory'] = !empty($input['signatory'])?trim($input['signatory']):'';
                $data['pan'] = !empty($input['pan'])?trim($input['pan']):'';
                $data['gstin'] = !empty($input['gstin'])?trim($input['gstin']):'';

            } else if(strtolower($input['setting_type']) == 'authentication_settings') {
                $data['captcha_rights'] = isset($input['captcha_rights'])?1:0;
                $data['google_2fa_allowed'] = isset($input['google_2fa_allowed'])?1:0;
                $data['allow_update_google_2fa_secret'] = isset($input['allow_update_google_2fa_secret'])?1:0;

            } else if(strtolower($input['setting_type']) == 'order_settings') {
                $data['allow_partial_payment'] = isset($input['allow_partial_payment'])?1:0;
                $data['apply_coupon'] = isset($input['apply_coupon'])?1:0;
                $data['allow_override_lead'] = isset($input['allow_override_lead'])?1:0;
                $data['allow_archive_lead'] = isset($input['allow_archive_lead'])?1:0;
                $data['allow_update_quotation_rate'] = isset($input['allow_update_quotation_rate'])?1:0;
                $data['allow_archive_order'] = isset($input['allow_archive_order'])?1:0;
                $data['order_limit_for_rto_continue'] = isset($input['order_limit_for_rto_continue'])?1:0;
                $data['courier_slip_title'] = !empty($input['courier_slip_title'])?trim($input['courier_slip_title']):'';
                $data['courier_slip_product_name'] = !empty($input['courier_slip_product_name'])?trim($input['courier_slip_product_name']):'';
                $data['courier_product_height'] = !empty($input['courier_product_height'])?trim($input['courier_product_height']):0.00;
                $data['courier_product_weight'] = !empty($input['courier_product_weight'])?trim($input['courier_product_weight']):0.00;
                $data['courier_product_length'] = !empty($input['courier_product_length'])?trim($input['courier_product_length']):0.00;
                $data['courier_product_breath'] = !empty($input['courier_product_breath'])?trim($input['courier_product_breath']):0.00;
                $data['order_limit_for_pending'] = !empty($input['order_limit_for_pending'])?trim($input['order_limit_for_pending']):0;
                $data['order_limit_for_rto'] = !empty($input['order_limit_for_rto'])?trim($input['order_limit_for_rto']):0;
            } else if(strtolower($input['setting_type']) == 'shopify_settings') {
                $data['move_order_to_shopify'] = isset($input['move_order_to_shopify'])?1:0;
                $data['api_url'] = !empty($input['api_url'])?trim($input['api_url']):'';
                $data['shopify_api_key'] = !empty($input['shopify_api_key'])?trim($input['shopify_api_key']):'';
                $data['shopify_api_secret_key'] = !empty($input['shopify_api_secret_key'])?trim($input['shopify_api_secret_key']):'';
                $data['shopify_access_token'] = !empty($input['shopify_access_token'])?trim($input['shopify_access_token']):'';
                $data['webhook_key'] = !empty($input['webhook_key'])?trim($input['webhook_key']):'';
                // $data['api_url'] = !empty($input['api_url'])?trim($input['api_url']):'';
                // $data['webhook_token'] = !empty($input['webhook_token'])?trim($input['webhook_token']):'';
            } else if(strtolower($input['setting_type']) == 'logo') {
                $data['logo_img'] = !empty($input['logo_img'])?trim($input['logo_img']):'';
            } else if(strtolower($input['setting_type']) == 'access_token') {
                $data['access_token'] = !empty($input['access_token'])?trim($input['access_token']):'';
            }
            return ['status' => true, 'data' => $data];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    private function preparePaymentLogData($input) {
        try {
            $data = [];
            if(!empty($input['payment_date']) && !empty($input['amount'])) {
                $data['payment_date'] = date('Y-m-d', strtotime($input['payment_date']));
                $data['amount'] = !empty($input['amount'])?floatval($input['amount']):0.00;
                $data['transaction_no'] = !empty($input['transaction_no'])?$input['transaction_no']:'';
                $data['remark'] = !empty($input['remark'])?$input['remark']:'';
            }
            return ['status' => true, 'data' => $data];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        } 
    }

    public function preparePlanLogData($input, $curplan, $action){
        try {
            $resp = $this->fetchSingleData("SELECT * FROM ".$this->tables['admin_plan_master']." WHERE id=:plan_id", [':plan_id'=>$input['plan_id']]);
            if(!$resp['status']) {
                throw new \Exception($resp['error']);
            } else if(empty($resp['data'])) {
                throw new \Exception("No plan found.");
            } else if(empty($resp['data']['status']) || $resp['data']['status'] != 1) {
                throw new \Exception("Plan is not active.");
            } else {
                $planlog = [];
                $planlog['plan_id'] = $resp['data']['id'];
                $planlog['plan_name'] = $resp['data']['name'];
                $planlog['plan_price'] = !empty($input['plan_price'])?$input['plan_price']:$resp['data']['price'];   
                $planlog['add_on_price'] = !empty($input['add_on_price'])?$input['add_on_price']:$resp['data']['add_on_price'];
                $planlog['plan_price_original'] = $resp['data']['price'];
                $planlog['add_on_price_original'] = $resp['data']['add_on_price'];
                $planlog['is_renewable'] = $resp['data']['is_renewable'];
                $planlog['is_trial'] = $resp['data']['is_trial'];
                $planlog['validity'] = $resp['data']['validity'];
                $planlog['validity_for'] = $resp['data']['validity_for'];
                $planlog['user_count'] = $resp['data']['user_count'];
                $planlog['add_on_agent'] = !empty($input['add_on_agent'])?$input['add_on_agent']:0;
                
                if(!empty($input['plan_validity'])){
                    $planlog['start_date'] = !empty($input['plan_validity'][0])?date('Y-m-d', strtotime($input['plan_validity'][0])):NULL;
                    $planlog['end_date'] = !empty($input['plan_validity'][1])?date('Y-m-d', strtotime($input['plan_validity'][1])):NULL;
                }
                $planlog['action'] = $action;
                if($action == 'RENEW') {
                    $planlog['parent_id'] = !empty($curplan['id'])?$curplan['id']:0;
                }
                if(empty($planlog['start_date']) || empty($planlog['end_date'])) {
                    if($action == 'RENEW') {
                        $planlog['start_date'] = date('Y-m-d', strtotime($curplan['end_date']. " +1 day"));
                        $planlog['end_date'] = date('Y-m-d', strtotime($planlog['start_date']." +".($planlog['validity']-1)." ".$planlog['validity_for']));
                    } else {
                        $planlog['start_date'] = date('Y-m-d');
                        $planlog['end_date'] = date('Y-m-d', strtotime("+".($planlog['validity']-1)." ".$planlog['validity_for']));
                    }  
                }
                return ['status' => true, 'data' => $planlog];   
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }
    
    public function dropClientDB($client_id, $conn) {
        try {
            $sql = file_get_contents($_ENV['CLIENT_DB_DELETE_SCRIPT_PATH']);
            $sqlarr = explode('$EOL$', $sql);
            array_pop($sqlarr);
            foreach ($sqlarr as $statement) {
                $statement = str_replace('##CLIENT_ID##', $client_id, $statement);
                Logger::debug($statement, ['Method' => 'dropClientDB', 'Client ID' => $client_id]);
                $rs = $this->query($statement, [], $conn);
                if(empty($rs['status'])) {
                    throw new \Exception($rs['error']);
                }
            }
            try {
                $parentdir = $_ENV['SERVER_PATH'] . '/' . $_ENV['ATTACHMENT_DIR'];
                $uploadFolder = $_ENV['SERVER_PATH'] . '/' . $_ENV['ATTACHMENT_DIR'] . '/' . $client_id . '/';
                if (is_dir($uploadFolder)) {
                    if(!rmdir($uploadFolder)) {
                        Logger::error('Permission denied. Unable to remove attachment directory.', ['Method' => 'createClientDB', 'Client ID' => $client_id]);
                    } else {
                        Logger::debug($parentdir.' deleted', ['Method' => 'createClientDB', 'Client ID' => $client_id]);
                    }
                }
            } catch(\Exception $e) {
                Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
        }
    }

    public function createClientDB($client_id, $conn)
    {
        try {
            if ($client_id > 0) {
                $sql    = file_get_contents($_ENV['CLIENT_DB_CREATE_SCRIPT_PATH']);
                $sqlarr = explode('$EOL$', $sql);
                array_pop($sqlarr);
                foreach ($sqlarr as $statement) {
                    $statement = str_replace('##CLIENT_ID##', $client_id, $statement);
                    Logger::debug($statement, ['Method' => 'createClientDB', 'Client ID' => $client_id]);
                    $rs = $this->query($statement, [], $conn);
                    if(empty($rs['status'])) {
                        throw new \Exception($rs['error']);
                    }
                }
                try {
                    $parentdir = $_ENV['SERVER_PATH'] . '/' . $_ENV['ATTACHMENT_DIR'];
                    $uploadFolder = $_ENV['SERVER_PATH'] . '/' . $_ENV['ATTACHMENT_DIR'] . '/' . $client_id . '/';
                    if (!is_dir($uploadFolder)) {
                        if(is_writable($parentdir)) {
                            if (!mkdir($uploadFolder, 0755, true)) {
                                Logger::error('Permission denied. Unable to create attachment directory.', ['Method' => 'createClientDB', 'Client ID' => $client_id]);
                            }
                        } else {
                            Logger::error($parentdir.' is not writable. Unable to create attachment directory.', ['Method' => 'createClientDB', 'Client ID' => $client_id]);
                        }
                    }
                } catch(\Exception $e) {
                    Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
                }
                return ['status' => true, 'data' => 'Client\'s database created if not already exists'];
            } else {
                throw new \Exception("Client id is blank when creating client database");
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    public function saveClientData($data) {
        try {
            $client_data    = $this->prepareClientData($data);
            if(!$client_data['status']) {
                throw new \Exception($client_data['error']);
            }
            $plan_log_data  = $this->preparePlanLogData($data, null, 'NEW');
            if(!$plan_log_data['status']) {
                throw new \Exception($plan_log_data['error']);
            }
            $payment_log_data = $this->preparePaymentLogData($data);
            if(!$payment_log_data['status']) {
                throw new \Exception($payment_log_data['error']);
            }
            $conn = $this->conn();   
            $conn->beginTransaction();
            $clrs = $this->insert($this->tables['admin_client_master'], $client_data['data'], $conn);
            if(!$clrs['status']) {
                $conn->rollBack();
                throw new \Exception($clrs['error']);
            }
            $plan_log_data['data']['client_id'] = $clrs['insert_id'];
            $plrs = $this->insert($this->tables['admin_client_plan_log'], $plan_log_data['data'], $conn);
            if(!$plrs['status']) {
                $conn->rollBack();
                throw new \Exception($plrs['error']);
            }
            $clupdate = [
                'plan_id' => $client_data['data']['plan_id'],
                'plan_start_date' => $plan_log_data['data']['start_date'],
                'plan_end_date' => $plan_log_data['data']['end_date'],
                'user_count' => $plan_log_data['data']['user_count'],
                'add_on_agent' => $plan_log_data['data']['add_on_agent'],
                'add_on_price' => $plan_log_data['data']['add_on_price'],
                'plan_price' => $plan_log_data['data']['plan_price']   
            ];
            $clupdrs = $this->update($this->tables['admin_client_master'], $clupdate, ['id' => $clrs['insert_id']], [], $conn);
            if(!$clupdrs['status']) {
                $conn->rollBack();
                throw new \Exception($clupdrs['error']);
            }
            if(!empty($payment_log_data['data'])) {
                $payment_log_data['data']['client_id'] = $clrs['insert_id'];
                $payrs = $this->insert($this->tables['admin_client_payment_log'], $payment_log_data['data'], $conn);
                if(!$payrs['status']) {
                    $conn->rollBack();
                    throw new \Exception($payrs['error']);
                }    
            }
            $dbrs = $this->createClientDB($clrs['insert_id'], $conn);
            if(empty($dbrs['status'])) {
                $conn->rollBack();
                throw new \Exception($dbrs['error']);
            }
            $conn->commit();
            return ['status' => true, 'data' => $clrs['insert_id']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    public function updateClientData($data) {
        try {
            $client_data    = $this->prepareClientData($data);
            if(!$client_data['status']) {
                throw new \Exception($client_data['error']);
            }
            $plan_log_data  = $this->preparePlanLogData($data, null, 'NEW');
            if(!$plan_log_data['status']) {
                throw new \Exception($plan_log_data['error']);
            }
            $payment_log_data = $this->preparePaymentLogData($data);
            if(!$payment_log_data['status']) {
                throw new \Exception($payment_log_data['error']);
            }
            
            $client = $this->fetchSingleData("SELECT * FROM ".$this->tables['admin_client_master']." WHERE id=:id", [':id'=>$data['id']]);
            if(!$client['status']) {
                throw new \Exception($client['error']);
            } else if(empty($client['data'])) {
                throw new \Exception("Client's details not found.");
            }

            $conn = $this->conn();   
            $conn->beginTransaction();
            $clrs = $this->update($this->tables['admin_client_master'], $client_data['data'], ['id'=>$data['id']], [], $conn);
            if(!$clrs['status']) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($clrs['error']);
            }
            if( 
                $plan_log_data['data']['plan_id'] != $client['data']['plan_id'] || 
                $plan_log_data['data']['start_date'] != $client['data']['plan_start_date'] || 
                $plan_log_data['data']['end_date'] != $client['data']['plan_end_date'] || 
                $plan_log_data['data']['user_count'] != $client['data']['user_count'] || 
                $plan_log_data['data']['add_on_agent'] != $client['data']['add_on_agent'] || 
                $plan_log_data['data']['plan_price'] != $client['data']['plan_price'] || 
                $plan_log_data['data']['add_on_price'] != $client['data']['add_on_price']
            ) {
                $plan_log_data['data']['client_id'] = $data['id'];
                $plrs = $this->insert($this->tables['admin_client_plan_log'], $plan_log_data['data'], $conn);
                if(!$plrs['status']) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($plrs['error']);
                }
                $clupdate = [
                    'plan_id' => $client_data['data']['plan_id'],
                    'plan_start_date' => $plan_log_data['data']['start_date'],
                    'plan_end_date' => $plan_log_data['data']['end_date'],
                    'user_count' => $plan_log_data['data']['user_count'],
                    'add_on_agent' => $plan_log_data['data']['add_on_agent'],
                    'add_on_price' => $plan_log_data['data']['add_on_price'],
                    'plan_price' => $plan_log_data['data']['plan_price']   
                ];
                $clupdrs = $this->update($this->tables['admin_client_master'], $clupdate, ['id' => $data['id']], [], $conn);
                if(!$clupdrs['status']) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($clupdrs['error']);
                }
            }

            if(!empty($payment_log_data['data'])) {
                $payment_log_data['data']['client_id'] = $data['id'];
                $payrs = $this->insert($this->tables['admin_client_payment_log'], $payment_log_data['data'], $conn);
                if(!$payrs['status']) {
                    if($conn->inTransaction()) $conn->rollBack();
                    throw new \Exception($payrs['error']);
                }    
            }
            $dbrs = $this->createClientDB($data['id'], $conn);
            if(empty($dbrs['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($dbrs['error']);
            }
            if($conn->inTransaction()) $conn->commit();
            return ['status' => true, 'data' => $data['id']];
        } catch (\Exception $e) {            
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    public function deleteClient($client_id) {
        try {
            $conn = $this->conn();   
            $conn->beginTransaction();
            $clrs = $this->delete($this->tables['admin_client_master'], ['id'=>$client_id], [], $conn);
            if(!$clrs['status']) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($clrs['error']);
            }
            $plrs = $this->delete($this->tables['admin_client_plan_log'], ['client_id'=>$client_id], [], $conn);
            if(!$plrs['status']) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($plrs['error']);
            }
            $paymrs = $this->delete($this->tables['admin_client_payment_log'], ['client_id'=>$client_id], [], $conn);
            if(!$paymrs['status']) {
               if($conn->inTransaction())  $conn->rollBack();
                throw new \Exception($paymrs['error']);
            }
            $users = $this->delete($this->tables['client_users_master'], ['comp_id'=>$client_id], [], $conn);
            if(!$users['status']) {
               if($conn->inTransaction())  $conn->rollBack();
                throw new \Exception($users['error']);
            }
            $dbrs = $this->dropClientDB($client_id, $conn);
            if(empty($dbrs['status'])) {
                if($conn->inTransaction()) $conn->rollBack();
                throw new \Exception($dbrs['error']);
            }
            if($conn->inTransaction()) $conn->commit();
            return ['status' => true];
        } catch (\Exception $e) {            
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    public function getClientData($client_id) {
        try {
            $sql = "SELECT t1.*, t2.name AS plan_name, t3.country_name, t4.state_name, t2.user_count, CONCAT(t2.validity, ' ', t2.validity_for) AS plan_valid_for,
            (CASE t1.status WHEN 0 THEN 'Inactive' WHEN 1 THEN IF(TIMESTAMPDIFF(DAY,CURDATE(),valid_date) <0, 'Expired', 'Active')
            END) AS status_text, IF(TIMESTAMPDIFF(DAY, CURDATE(), t1.valid_date)<=0, 0, TIMESTAMPDIFF(DAY,CURDATE(),t1.valid_date)) AS days_remaining";
            $sql .= " FROM ".$this->tables['admin_client_master']." t1 LEFT JOIN ".$this->tables['admin_plan_master']." t2 ON(t2.id=t1.plan_id) LEFT JOIN ".$this->tables['admin_country_master']." t3 ON(t3.country_id=t1.country_id) LEFT JOIN ".$this->tables['admin_state_master']." t4 ON(t4.state_id=t1.state_id)  WHERE t1.id=:id";
            return $this->fetchSingleData($sql, [':id'=>$client_id]);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }
    public function getClientMaster($search, $page_no, $page_limit, $sort_by="name", $sort_type="ASC")
    {
        try {
            if(isset($search['valid_date'])) {
                $valid_date = $search['valid_date'];
                unset($search['valid_date']);
                if(!empty($valid_date)) {
                    $search['start_date'] = date('Y-m-d', strtotime($valid_date[0]));
                    $search['end_date'] = date('Y-m-d', strtotime($valid_date[1]));
                }
            }
            if(isset($search['days_remaining'])) {
                unset($search['days_remaining']);
            }
            $where = $this->whereString($search, array('*'=>'t1.', 'name'=>''), array('name'=>'LIKE', 'start_date'=>'DGTE', 'end_date'=>'DLTE'), array('name'=>"CONCAT(t1.name, ' ', COALESCE(t1.email_id,''))", 'start_date'=>'valid_date', 'end_date'=>'valid_date'));
            
            if($search['status'] == 'INACTIVE') {
                $search['status'] = 0;
            } else if($search['status'] == 'ACTIVE') {
                $search['status'] = 1;
                $search['valid_date'] = date('Y-m-d');
                $where = $this->whereString($search, array('*'=>'t1.', 'name'=>''), array('name'=>'LIKE', 'valid_date'=>'DGTE'), array('name'=>"CONCAT(t1.name, ' ', COALESCE(t1.email_id,''))"));
            } else if($search['status'] == 'EXPIRED') {
                unset($search['status']);
                $search['valid_date'] = date('Y-m-d');
                $where = $this->whereString($search, array('*'=>'t1.', 'name'=>''), array('name'=>'LIKE', 'valid_date'=>'DLT'), array('name'=>"CONCAT(t1.name, ' ', COALESCE(t1.email_id,''))"));
            }   
            $sort_str = "";
            if(!empty($sort_by) && !empty($sort_type)) {
                $sort_str = " ORDER BY $sort_by $sort_type";
            }
            
            return $this->fetchPaginatedData("SELECT t1.id, t1.name, CONCAT(t1.phone_code, t1.phone_no) AS mobile_no, 
            t1.email_id, t1.country_id, t1.state_id, t1.city, t1.pincode, t1.user_count, t1.add_on_agent,IF(t1.cr_date IS NOT NULL, DATE_FORMAT(t1.cr_date, \"%d-%m-%Y %H:%i:%s\"), \"\") AS cr_date,
            IF(t1.valid_from IS NOT NULL, DATE_FORMAT(t1.valid_from, \"%d-%m-%Y\"), \"\") AS start_date,
            IF(t1.valid_date IS NOT NULL, DATE_FORMAT(t1.valid_date, \"%d-%m-%Y\"), \"\") AS end_date,
            (CASE t1.status WHEN 0 THEN 'Inactive' WHEN 1 THEN IF(TIMESTAMPDIFF(DAY,CURDATE(),valid_date) <0, 'Expired', 'Active')
            END) AS status,
            IF(TIMESTAMPDIFF(DAY,CURDATE(), t1.valid_date)<=0, 0, TIMESTAMPDIFF(DAY,CURDATE(),t1.valid_date)) AS days_remaining,
            IF(TIMESTAMPDIFF(DAY,CURDATE(), t1.valid_date)<=0, 0, FLOOR((TIMESTAMPDIFF(DAY,CURDATE(),t1.valid_date) * 100)/TIMESTAMPDIFF(DAY, t1.plan_start_date, t1.valid_date))) AS days_remaining_per,
            IF(t2.is_trial=0 AND t2.is_renewable=1, \"RENEW\", \"\") AS renew, \"UPDATE\" AS upgrade,
            IF(t2.is_trial=0, \"ADDPAYMENT\", \"\") AS add_payment, t1.plan_id, t2.name AS plan_name FROM ".$this->tables['admin_client_master']." t1 LEFT JOIN ".$this->tables['admin_plan_master']." t2 ON(t2.id=t1.plan_id) WHERE $where $sort_str", $page_no, $page_limit);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }
    
    public function getExportClientList($fld_alias, $id, $file_path) {
        try {
            $hs = [];
            $ds = [];
            foreach($fld_alias as $column => $alias) {
                $hs[] = "'$alias' AS $column";
                if($column == 'name') {
                    $ds[] = " t1.name ";
                } else if($column == 'user_count') {
                    $ds[] = " t1.user_count ";
                } else if($column == 'mobile_no') {
                    $ds[] = " CONCAT(t1.phone_code, t1.phone_no) AS mobile_no ";
                } else if($column == 'plan_name') {
                    $ds[] = " t2.name AS plan_name ";
                } else if($column == 'days_remaining'){
                    $ds[] = " IF(TIMESTAMPDIFF(DAY,CURDATE(), t1.valid_date)<=0, 0, TIMESTAMPDIFF(DAY,CURDATE(),t1.valid_date)) AS days_remaining ";
                } else if($column == 'start_date'){
                    $ds[] = ' IF(t1.valid_from IS NOT NULL, DATE_FORMAT(t1.valid_from, "%d-%m-%Y"), "") AS start_date ';
                } else if($column == 'end_date'){
                    $ds[] = ' IF(t1.valid_date IS NOT NULL, DATE_FORMAT(t1.valid_date, "%d-%m-%Y"), "") AS end_date ';
                } else if($column == 'status') {
                    $ds[] = ' (CASE t1.status WHEN 0 THEN "Inactive" WHEN 1 THEN IF(TIMESTAMPDIFF(DAY,CURDATE(),valid_date) <0, "Expired", "Active") END) AS status ';
                } else {
                    $ds[] = ' IF('.$column.' IS NOT NULL,  '.$column.', "") AS $alias ';
                }
            }
            $hsl = implode(',', $hs);
            $dsl = implode(',', $ds);
            $sqlStr  = " SELECT $hsl ";
            $sqlStr .= " UNION ";
            $sqlStr .= " SELECT $dsl ";
            $sqlStr .= ' INTO OUTFILE "'.$file_path.'"'; 
            $sqlStr .= ' FIELDS TERMINATED BY ";"  ';
            $sqlStr .= ' OPTIONALLY ENCLOSED BY "\'" ';
            $sqlStr .= ' LINES TERMINATED BY "\n" ';
            $sqlStr .= " FROM ".$this->tables['admin_client_master']." t1 ";
            $sqlStr .= " LEFT JOIN ".$this->tables['admin_plan_master']." t2 ON(t2.id=t1.plan_id) ";
            $sqlStr .= " WHERE 1=1 ";
            if(!empty($id)){
                $list_id = implode(",", $id);
                $sqlStr .= " AND t1.id IN ($list_id)";
            }
            
            $resp = $this->fetchAllData($sqlStr);
            if(!$resp['status']) {
                throw new \Exception($resp['error']);
            }
            return ["status" => true, "data" => $resp['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getClientPlanDetail($plan_id, $client_id, $action) {
        try {
            $resp = $this->fetchSingleData("SELECT * FROM ".$this->tables['admin_plan_master']." WHERE id=:id", [':id'=>$plan_id]);
            if(!$resp['status']) {
                throw new \Exception($resp['error']);
            }
            if(empty($resp['data'])) {
                return ["status" => false, "error" => "No plan found."];
            }
            if(empty($resp['data']['status']) || $resp['data']['status'] != 1) {
                return ["status" => false, "error" => "Plan is not active."];
            } else if(empty($client_id)) {
                return ["status" => true, "data" => $resp['data']];
            } else {
                $presp = $this->getChangedPlan($client_id, $plan_id, $resp['data'], $action);
                if(!$presp['status']) {
                    throw new \Exception($presp['error']);
                } else if(empty($presp['data'])) {
                    return ["status" => false, "error" => "No plan found."];
                } else {
                    return ["status" => true, "data" => $presp['data']];     
                }
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getChangedPlan($client_id, $plan_id, $newplan, $action) {
        try {
            $balrs = $this->getClientBalanceAmount($client_id);
            if( $balrs['status'] == false) {
                throw new \Exception($balrs['error']);
            }
            $bal = !empty($balrs['data'])?$balrs['data']:['balance'=>0.00, 'outstanding'=>0.00];
            if(empty($newplan)) {
                $resp = $this->fetchSingleData("SELECT * FROM ".$this->tables['admin_plan_master']." WHERE id=:plan_id", [':plan_id'=>$plan_id]);
                if(!$resp['status']) {
                    throw new \Exception($resp['error']);
                } else if(empty($resp['data'])) {
                    return ["status" => false, "error" => "No plan found."];
                } else if(empty($resp['data']['status']) || $resp['data']['status'] != 1) {
                    return ["status" => false, "error" => "Plan is not active."];
                } else {
                    $newplan = $resp['data'];
                }
            }
            // print_r($new_plan);exit;
            $last_log = $this->fetchSingleData("SELECT * FROM ".$this->tables['admin_client_plan_log']." WHERE client_id=:client_id ORDER BY id DESC LIMIT 1", [':client_id'=>$client_id]);
            if(!$last_log['status']) {
                throw new \Exception($last_log['error']);
            }
            $newplan['action']               = $action;
            $newplan['prev_plan_id']         = 0;
            $newplan['prev_plan_validity']   = '';
            $newplan['prev_plan_validity_for']   = '';
            $newplan['prev_plan_start_date'] = '';
            $newplan['prev_plan_end_date']   = '';
            $newplan['prev_plan_price']      = 0.00;
            $newplan['prev_add_on_price']    = 0.00;
            $newplan['prev_add_on_agent']    = 0;
            $newplan['prev_total_price']     = 0.00;
            $newplan['prev_plan_user_count'] = 0;
            $newplan['prev_balance_amount']     = $bal['balance'];
            $newplan['prev_outstanding_amount'] = $bal['outstanding'];
            $newplan['balance_amount']          = $bal['balance'];
            $newplan['consumed_amount']         = 0.00;
            $newplan['remaining_amount']        = 0.00;
            $newplan['plan_price_revised']   = ($bal['outstanding'] > 0)? ($newplan['price'] + $bal['outstanding']) : $newplan['price'];

            if(!empty($last_log['data'])) {
                $newplan['prev_plan_id']         = $last_log['data']['id'];
                $newplan['prev_plan_validity']   = $last_log['data']['validity'];
                $newplan['prev_plan_validity_for']   = $last_log['data']['validity_for'];
                $newplan['prev_plan_start_date'] = $last_log['data']['start_date'];
                $newplan['prev_plan_end_date']   = $last_log['data']['end_date'];
                $newplan['prev_plan_price']      = $last_log['data']['plan_price'];
                $newplan['prev_add_on_price']    = ($last_log['data']['add_on_price'] > 0)?$last_log['data']['add_on_price']:0;
                $newplan['prev_add_on_agent']    = ($last_log['data']['add_on_agent'] > 0)?$last_log['data']['add_on_agent']:0;
                $newplan['prev_plan_user_count'] = ($last_log['data']['user_count'] > 0)?$last_log['data']['user_count']:0;
                $total_add_on_amount             = $last_log['data']['add_on_price'] * $last_log['data']['add_on_agent'];
                $newplan['prev_total_price']     = $total_add_on_amount + $last_log['data']['plan_price'];

                if(date($last_log['data']['end_date']) > date('Y-m-d')) {
                    $plan_days = Utilities::getDateDiff($last_log['data']['start_date'], $last_log['data']['end_date']);
                    $used_days = Utilities::getDateDiff($last_log['data']['start_date'], date('Y-m-d'));
                    if($used_days > 0) {
                        $price_per_day = \round($newplan['prev_plan_price']/$plan_days); 
                        $price_per_day_add_on = 0;
                        if($total_add_on_amount > 0) {
                            $price_per_day_add_on = \round($total_add_on_amount/$plan_days);
                        }
                        $newplan['consumed_amount']  = \round($used_days * $price_per_day) + \round($used_days * $price_per_day_add_on);
                        $newplan['remaining_amount'] = $newplan['prev_total_price'] - $newplan['consumed_amount'];
                    }
                } else {
                    $newplan['balance_amount'] = $bal['balance'];
                    $newplan['consumed_amount'] = 0.00;
                    $newplan['remaining_amount'] = 0.00;
                }
            }
            if($action == 'UPDATE') {
                if($newplan['plan_price_revised'] > $newplan['remaining_amount']) {
                    $newplan['plan_price_revised'] = $newplan['plan_price_revised'] - $newplan['remaining_amount'];

                    if($newplan['balance_amount'] > 0) {
                        if($newplan['plan_price_revised'] >= $newplan['balance_amount']) {
                            $newplan['plan_price_revised'] = $newplan['plan_price_revised'] - $newplan['balance_amount'];
                            $newplan['balance_amount'] = 0.00;
                        } else {
                            $newplan['balance_amount'] = $newplan['balance_amount'] - $newplan['plan_price_revised'];
                            $newplan['plan_price_revised'] = 0.00;
                        }
                    }
                } else if($newplan['plan_price_revised'] < $newplan['remaining_amount']) {
                    $newplan['balance_amount'] += $newplan['remaining_amount'] - $newplan['plan_price_revised'];
                } else if($newplan['plan_price_revised'] < $newplan['remaining_amount']) {
                    $newplan['plan_price_revised'] = 0.00;
                }
            } else {
                if($newplan['plan_price_revised'] >= $newplan['balance_amount']) {
                    $newplan['plan_price_revised'] = $newplan['plan_price_revised'] - $newplan['balance_amount'];
                    $newplan['balance_amount'] = 0.00;
                } else {
                    $newplan['balance_amount'] = $newplan['balance_amount'] - $newplan['plan_price_revised'];
                    $newplan['plan_price_revised'] = 0.00;
                }
            }
            return ["status" => true, "data" => $newplan];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getClientBalanceAmount($client_id) {
        try {
            $resp = $this->fetchMultiSetData("CALL getClientBalanceAmount(:client_id)", [':client_id' => $client_id], true);
            if(!$resp['status']) {
                throw new \Exception($resp['error']);
            }
            $bal = 0;
            $outsg = 0;
            if(count($resp['data']) > 0) {
                if($resp['data'][0]['amount_diff'] < 0) {
                    $bal = abs($resp['data'][0]['amount_diff']);
                } else {
                    $outsg = $resp['data'][0]['amount_diff'];
                }
                return ["status" => true, "data" => ["amount_diff"=>$resp['data'][0]['amount_diff'], "balance"=>$bal, "outstanding"=>$outsg]];
            } else {
                return ["status" => true, "data" => ["amount_diff"=>0, "balance"=>$bal, "outstanding"=>$outsg]];
            }
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getClientPlanLogs($search, $page_no, $page_limit, $sort_by="id", $sort_type="DESC") {
        try {
            $where = $this->whereString($search, array('*'=>'t1.', 'plan_name'=>''), array('plan_name'=>'LIKE'), array());
            
            $sort_str = "";
            if(!empty($sort_by) && !empty($sort_type)) {
                $sort_str = " ORDER BY $sort_by $sort_type";
            }
            
            return $this->fetchPaginatedData("SELECT t1.id, t1.add_on_agent, t1.action, (CASE t1.is_edited WHEN 0 THEN '' WHEN 1 THEN 'Yes' END) AS isedited, CONCAT(t1.validity, ' ', t1.validity_for, IF(t1.validity>1, '(s)', '')) AS plan_tenure, DATE_FORMAT(t1.start_date, '%d-%m-%Y') AS plan_start, DATE_FORMAT(t1.end_date, '%d-%m-%Y') AS plan_end, IF(t1.is_renewable=1, 'Yes', 'No') AS renewable, IF(t1.is_trial=1, 'Yes', 'No') AS is_trial,
            DATE_FORMAT(t1.cr_date, '%d-%m-%Y %H:%i:%s') AS created_date, t2.name AS plan_name , t2.user_count, t4.name AS client_name, (IFNULL(t1.plan_price, 0) + (IFNULL(t1.add_on_price, 0) * IFNULL(t1.add_on_agent, 0))) AS plan_total_price FROM ".$this->tables['admin_client_plan_log']." t1 LEFT JOIN ".$this->tables['admin_plan_master']." t2 ON(t2.id=t1.plan_id) LEFT JOIN ".$this->tables['admin_client_master']." t4 ON(t4.id=t1.client_id)  WHERE $where $sort_str", $page_no, $page_limit);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getClientPaymentLogs($search, $page_no, $page_limit, $sort_by="id", $sort_type="DESC") {
        try {
            $where = $this->whereString($search, array('*'=>'t1.', 'remark'=>''), array('remark'=>'LIKE'), array());
            
            $sort_str = "";
            if(!empty($sort_by) && !empty($sort_type)) {
                $sort_str = " ORDER BY $sort_by $sort_type";
            }
            
            return $this->fetchPaginatedData("SELECT t1.id, t1.transaction_no, t1.transaction_status, t1.remark,
            IF(t1.amount>0, FORMAT(t1.amount, 2), 0) AS amount, 
            IF(t1.payment_date IS NOT NULL, DATE_FORMAT(t1.payment_date, '%d-%m-%Y'), '') AS payment_date,
            IF(t1.cr_date IS NOT NULL, DATE_FORMAT(t1.cr_date, '%d-%m-%Y %H:%i:%s'), '') AS created_at FROM ".$this->tables['admin_client_payment_log']." t1 WHERE $where $sort_str", $page_no, $page_limit);
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function getClientLastPlanLog($client_id) {
        try {
            $resp = $this->fetchSingleData("SELECT * FROM ".$this->tables['admin_client_plan_log']." WHERE  status=1 AND client_id=:client_id ORDER BY id DESC LIMIT 1", [':client_id'=>$client_id]);
            if(!$resp['status']) {
                throw new \Exception($resp['error']);
            }
            return ["status" => true, "data" => $resp['data']];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function editPlanLog($last_log, $new_log) {
        try {
            $conn = $this->conn();   
            $conn->beginTransaction();
            $rs = $this->insert($this->tables['admin_client_plan_log'], $new_log, $conn);
            if(!$rs['status']) {
                $conn->rollBack();
                throw new \Exception($rs['error']);
            }
            $plrs = $this->update($this->tables['admin_client_plan_log'], ['is_edited'=>1], ['id'=>$last_log['id']], [], $conn);
            if(!$plrs['status']) {
                $conn->rollBack();
                throw new \Exception($plrs['error']);
            }
            $upd = [
                'plan_id'=>$new_log['plan_id'], 
                'add_on_agent'=>$new_log['add_on_agent'], 
                'user_count'=>$new_log['user_count'], 
                'add_on_price'=>$new_log['add_on_price'], 
                'plan_price'=>$new_log['plan_price'],
                'plan_start_date'=>$new_log['start_date'],
                'plan_end_date'=>$new_log['end_date']
            ];
            if($last_log['action'] == 'RENEW') {
                $upd['valid_date'] = $new_log['end_date'];
            } else {
                $upd['valid_from'] = $new_log['start_date'];
                $upd['valid_date'] = $new_log['end_date'];
            }
            $urs = $this->update($this->tables['admin_client_master'], $upd, ['id'=>$new_log['client_id']], [], $conn);
            if(!$urs['status']) {
                $conn->rollBack();
                throw new \Exception($urs['error']);
            }
            $conn->commit();
            return ['status' => true];
        } catch (\Exception $e) {            
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    public function renewUpdateClientPlan($new_log, $client_update, $payment) {
        try {
            $conn = $this->conn();   
            $conn->beginTransaction();
            $rs = $this->insert($this->tables['admin_client_plan_log'], $new_log, $conn);
            if(!$rs['status']) {
                $conn->rollBack();
                throw new \Exception($rs['error']);
            }
            $plrs = $this->update($this->tables['admin_client_master'], $client_update, ['id'=>$new_log['client_id']], [], $conn);
            if(!$plrs['status']) {
                $conn->rollBack();
                throw new \Exception($plrs['error']);
            }
            if(!empty($payment)) {
                $rs = $this->insert($this->tables['admin_client_payment_log'], $payment, $conn);
                if(!$rs['status']) {
                    $conn->rollBack();
                    throw new \Exception($rs['error']);
                }
            }
            $conn->commit();
            return ['status' => true];
        } catch (\Exception $e) {            
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }
    }

    public function editClientSettings($client_id, $data) {
        try {
            $conn = $this->conn();   
            $data = $this->preapareClientSettingsData($data);   
            if(!$data['status']) {
                throw new \Exception($data['error']);
            }
            $data = $data['data'];
            $conn->beginTransaction();
            $rs = $this->update($this->tables['admin_client_master'], $data, ['id'=>$client_id], [], $conn);
            if(!$rs['status']) {
                $conn->rollBack();
                throw new \Exception($rs['error']);
            }
            $conn->commit();
            return ['status' => true];
        } catch (\Exception $e) {            
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ['status' => false, 'error' => $e->getMessage()];
        }   
    }
}