<?php
defined('BASEPATH') OR exit('No direct script access allowed');
use PhpSpreadsheet\IOFactory;
class Setting extends CI_Controller {

	/**
	 * Index Page for this controller.
	 *
	 * Maps to the following URL
	 * 		http://example.com/index.php/welcome
	 *	- or -
	 * 		http://example.com/index.php/welcome/index
	 *	- or -
	 * Since this controller is set as the default controller in
	 * config/routes.php, it's displayed at http://example.com/
	 *
	 * So any other public methods not prefixed with an underscore will
	 * map to /index.php/welcome/<method_name>
	 * @see https://codeigniter.com/userguide3/general/urls.html
	 */
	public function index()
	{
		redirect('setting/smtpSetting');
	}
	public function smtpSetting()
    {   
	
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		
		$this->session->set_userdata('menu','smtp');
		$sql="select * from smtp_details where id='1' order by id asc";    
		$query = $this->db->query($sql);
		$status_data = $query->result_array();
		
		$this->load->view('templates/layout');
		$this->load->view('setting/all_item',array('item_data'=>$status_data));
		
	}
	public function emailTemplates()
    {   
	
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		
		$this->session->set_userdata('menu','email');
		$sql="select * from email_templates order by id asc";    
		$query = $this->db->query($sql);
		$status_data = $query->result_array();
		
		$this->load->view('templates/layout');
		$this->load->view('setting/templates',array('item_data'=>$status_data));
		
		
	}
	public function editTemplate($id) {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		
		$this->session->set_userdata('menu','email');
		$sql="select * from email_templates where id='$id' ";    
		$query = $this->db->query($sql);
		$status_data = $query->result_array();
		
		$this->load->view('templates/layout');
		$this->load->view('setting/edit_template',array('item_data'=>$status_data));
	}
	public function updateTemplate() {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		
		$this->session->set_userdata('menu','email');
		$content = $_POST['content'];
		$id = $_POST['id'];
		$sql="update email_templates set template_description='$content' where id='$id' ";    
		$query = $this->db->query($sql);
		redirect('setting/emailTemplates');
		
	}
	
	public function saveEmailSetting() {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		$this->session->set_userdata('menu','smtp');

		$smtp_host = $_POST['smtp_host'];
		$smtp_port = $_POST['smtp_port'];
		$smtp_user = $_POST['smtp_user'];
		$smtp_pass = $_POST['smtp_pass'];
		$smtp_crypto = $_POST['smtp_crypto'];
		
		$sql="update smtp_details set smtp_host ='$smtp_host', smtp_port='$smtp_port', smtp_user='$smtp_user', smtp_pass='$smtp_pass', smtp_crypto='$smtp_crypto' where id='1'";    
		$query = $this->db->query($sql);
		$date = date('Y-m-d h:i:s');
		
		$sql1="insert into log_table (description,action, created_time, user_id) values('SMTP Details Modified','SMTP Details','".$date."','".$login_id."')";
		$query1 = $this->db->query($sql1);
		$this->session->set_flashdata('smtp_msg', 'SMTP Details Updated Successfully');

		redirect('setting/smtpSetting');
	}
	public function editItem($id) {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		$sql1="select * from item_master where id='$id'";
		$query1 = $this->db->query($sql1);
		
		$business_data = $query1->result_array();
		if(empty($business_data)) {
			redirect('setting/allItems');
		}
		$business_data = $business_data[0];
		
		$this->session->set_userdata('menu','setting');
		
		$sql="select * from item_master where id='$id'";    
		$query = $this->db->query($sql);
		$status_data = $query->result_array();
		$status_data = $status_data[0];
		$this->load->view('templates/layout');
		$this->load->view('setting/edit_item',array('item_data'=>$status_data));
	
	}
	public function updateItem() {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		$this->session->set_userdata('menu','setting');
		$id = $_POST['id'];
		$item_name = $_POST['item_name'];
		$sql="update item_master set item_name='$item_name' where id='$id'";    
		$query = $this->db->query($sql);
		$date = date('Y-m-d h:i:s');
		
		$sql1="insert into log_table (description,action, created_time, user_id) values('Item Updated','Item Modified','".$date."','".$login_id."')";
		$query1 = $this->db->query($sql1);
		redirect('setting/allItems');
	}
	public function changeItemStatus($id) {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		$this->session->set_userdata('menu','setting');
		$sql="select * from item_master where id='$id'";    
		$query = $this->db->query($sql);
		$business_data = $query->result_array();
		if(empty($business_data)) {
			redirect('setting/allItems');
		}
		$business_data  = $business_data[0];
		if($business_data['status']=='1') {
			$sql="update item_master set status='0' where id='$id'"; 
		} else {
			$sql="update item_master set status='1' where id='$id'"; 
		}
		$query = $this->db->query($sql);
		$date = date('Y-m-d h:i:s');
		$sql1="insert into log_table (description,action, created_time, user_id) values('Item Status Changed','Item Status Updated','".$date."','".$login_id."')";
		$query1 = $this->db->query($sql1);
		redirect('setting/allItems');
	}
	public function deleteItem($id) {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		$this->session->set_userdata('menu','setting');
		$sql="select * from item_master where id='$id'";    
		$query = $this->db->query($sql);
		$business_data = $query->result_array();
		if(empty($business_data)) {
			redirect('setting/allItems');
		}
		$business_data  = $business_data[0];
		
		$sql="delete from item_master where id='$id'"; 
		
		$query = $this->db->query($sql);
		$date = date('Y-m-d h:i:s');
		$sql1="insert into log_table (description,action, created_time, user_id) values('Delete Item','Item Deleted','".$date."','".$login_id."')";
		$query1 = $this->db->query($sql1);
		redirect('setting/allItems');
	}
	
	public function logs() {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		$this->session->set_userdata('menu','setting');
		$sql="select l.*,u.first_name,u.last_name from log_table l left join users u on u.id::text=l.user_id order by l.id desc";    
		$query = $this->db->query($sql);
		$status_data = $query->result_array();
		$this->load->view('templates/layout');
		$this->load->view('setting/logs',array('log_data'=>$status_data));
		$this->load->view('templates/footer');
	}
	
	public function settings() {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}

		$request_data = $this->db->query("SELECT u.*,m.* FROM picogeneral_nodes u left join assign_module m on m.setting_id = u.id order by u.id asc")->result_array
		();
		
		$fields = $this->db->list_fields('picogeneral_nodes');
		foreach ($fields as $key=>$field) {
			$var[$key] = $field; // key = value
		}		
		
		$this->load->view('templates/layout');
		$this->load->view('setting/all_settings',array('request_data'=>$request_data,'fields'=>$fields));

	}
	public function accessControl() {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		
		$data = $this->db->query("select * from sensor_variable order by id asc")->result_array();
		$this->load->view('templates/layout');
		$this->load->view('setting/access_control',array('data'=>$data));
		
	}
	public function graphColor() {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		$request_data = $this->db->query("select * from graphs_color where id='1'")->result_array();
		$this->load->view('templates/layout');
		$this->load->view('setting/graph_color',array('request_data'=>$request_data));

	}
	public function saveColor() {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		$color_one = $_POST['color_one'];
		$color_two = $_POST['color_two'];
		$request_data = $this->db->query("update graphs_color set color_one='$color_one', color_two='$color_two' where id='1'");
		$this->session->set_flashdata('color_save','Color Successfully Saved');
		redirect('setting/graphColor');
	}
	public function saveData() {
		$login_id = $this->session->userdata('id');
		if($login_id=='') {
			redirect('login');
		}
		$id = $_POST['id'];
		$status = $_POST['status'];
		$d_name = $_POST['d_name'];
		if($d_name=='visible_user') {
			$request_data = $this->db->query("update assign_module set visible_user='$status' where setting_id='$id'");
		}
		if($d_name=='modify_user') {
			$request_data = $this->db->query("update assign_module set modify_user='$status' where setting_id='$id'");
		}
		if($d_name=='visible_admin') {
			$request_data = $this->db->query("update assign_module set visible_admin='$status' where setting_id='$id'");
		}
		if($d_name=='modify_admin') {
			$request_data = $this->db->query("update assign_module set modify_admin='$status' where setting_id='$id'");
		}
		echo true;

	}
	
	public function saveAccessData() {
		
		$id = $_POST['id'];
		$status = $_POST['status'];
		$d_name = $_POST['d_name'];
		if($d_name=='visible_map') {
			$request_data = $this->db->query("update sensor_variable set maps='$status' where id='$id'");
		}
		if($d_name=='visible_chart') {
			$request_data = $this->db->query("update sensor_variable set graph='$status' where id='$id'");
		}
		echo true;
	}
	public function list() {
		$this->load->model('Setting_model');
        $data['holidays'] = $this->Setting_model->get_all();
		$this->load->view('templates/layout');
        $this->load->view('setting/list_holidays', $data);
    }
	public function upload() {
		$this->load->view('templates/layout');
        $this->load->view('setting/upload_holidays');
	}

    public function import_excel() {
        if (!empty($_FILES['holiday_file']['name'])) {

            // Load PhpSpreadsheet manually from third_party folder
           $psPath = APPPATH . 'third_party\PhpSpreadsheet\IOFactory';
           
			/*if (!file_exists($psPath)) {
                $this->session->set_flashdata('error', 'PhpSpreadsheet not found. Please place PhpSpreadsheet (with vendor/autoload.php) into application/third_party/PhpSpreadsheet/');
                redirect('setting/list');
                return;
            }*/
            require_once $psPath;

            try {
                $spreadsheet = PhpSpreadsheet\IOFactory::load($_FILES['holiday_file']['tmp_name']);
            } catch (Exception $e) {
                $this->session->set_flashdata('error', 'Error loading file: ' . $e->getMessage());
                redirect('setting/list');
                return;
            }
			
            $sheetData = $spreadsheet->getActiveSheet()->toArray();

            $inserted = 0;
            foreach ($sheetData as $key => $row) {
                if ($key === 0) continue; // Skip header

                // skip empty rows
                if (empty($row[0]) || empty($row[1])) continue;

                // Normalize date
                $date = '';
                // If Excel stores as serial/datetime, PhpSpreadsheet may return a numeric value
                if (is_numeric($row[0])) {
                    $date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($row[0]);
                    $date = date('Y-m-d', $date);
                } else {
                    $date = date('Y-m-d', strtotime($row[0]));
                }
				echo '<pre>'; print_r($row);exit;
                $data = [
                    'holiday_date' => $date,
                    'holiday_name' => $row[1]
                ];
                $this->Setting_model->insertHoliday($data);
                $inserted++;
            }

            $this->session->set_flashdata('success', "Imported successfully. Rows inserted: {$inserted}");
        } else {
            $this->session->set_flashdata('error', 'No file selected.');
        }

        redirect('setting/list');
    }

}
