<?php if(! defined('BASEPATH')) exit ('no direct access allowed');
    
    class Backupdata extends CI_controller{
		
        /**
			* __construct
			*
			* @return void
		*/
        public function __construct()
		{
			parent::__construct();
			cek_session_login();
            $this->load->helper('directory');
            $this->load->helper("file");
            $this->load->helper('date');
            $this->load->helper('download');
            // $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
            $this->title = info()['title']; 
            $this->demo = info()['demo']; 
			$this->iduser = $this->session->idu; 
            $this->level = $this->session->level; 
			$this->perPage = 10;
			$this->load->library('Database_backup_service');
		}
		
        /**
			* database
			*
			* @return void
		*/
        public function database()
		{
			cek_menu_akses();
			$data['title'] = 'Backup db | '.$this->title;
			$data['autobackup'] = $this->database_backup_service->settings();
			$data['boleh_atur_autobackup'] = in_array(strtolower((string)$this->level), array('admin','owner','superadmin','super_admin'), true);
			
			$this->template->load('main/themes','backup/view_index',$data);
		}    
		
		function ajaxDatabase(){
			
			cek_crud_akses(8);
			
			$page = $this->input->post('page'); 
			if(!$page){ 
				$offset = 0; 
				}else{ 
				$offset = $page; 
			} 
			
			$limits = $this->input->post('limits'); 
			if(!empty($limits)){ 
				$limit = $limits; 
				}else{ 
				$limit = $this->perPage; 
			} 
			// Set conditions for search and filter 
			$keywords = $this->input->post('keywords'); 
			$sortBy = $this->input->post('sortBy'); 
			
			if(!empty($keywords)){ 
				$conditions['search']['keywords'] = $keywords; 
			} 
			if(!empty($sortBy)){ 
				$conditions['search']['sortBy'] = $sortBy; 
			} 
			if(!empty($limits)){ 
				$conditions['search']['limits'] = $limits; 
			}
			
			
			// Get record count 
			$conditions['returnType'] = 'count'; 
			$totalRec = $this->model_data->getBackup($conditions); 
			
			// Pagination configuration 
			$config['target']      = '#filter_backup'; 
			$config['base_url']    = base_url('backup/ajaxDatabase'); 
			$config['total_rows']  = $totalRec; 
			$config['per_page']    = $limit; 
			$config['link_func']   = 'FilterBackup'; 
			
			// Initialize pagination library 
			$this->ajax_pagination->initialize($config); 
			
			// Get records 
			$conditions['start'] = $offset; 
			$conditions['limit'] = $limit;
			
			unset($conditions['returnType']); 
			$data['demo'] = $this->demo;
			$data['posts'] = $this->model_data->getBackup($conditions); 
			
			// Load the data list view 
			$this->load->view('backup/ajax', $data, false); 
		}
        /**
			* list_data
			*
			* @return void
		*/
        public function list_data()
		{
            
            $fetch_data = directory_map('./backup_db/', FALSE, TRUE); 
            $path = FCPATH.'backup_db/';
            $data = array();  
            $no=1;
            foreach($fetch_data as $row)  
            {  
                $filesize = filesize($path.$row);
                $dname = explode(".", $row);
                $ext = end($dname);
                $exp = explode('_',$row);
				if ($ext == 'sql') {
					$resto = '';
					$archive = '<button type="button" class="btn btn-primary btn-sm restoredb" data-toggle="tooltip" title="Restore DB" data-file="' . $row . '"><i class="fa fa-retweet"></i> Restore</button>';
					} else {
					$resto = "";
					$archive = '<button type="button" class="btn btn-info btn-sm unzipdb" data-toggle="tooltip" title="Unzip DB" data-file="' . $row . '"><i class="fa fa-archive "></i> Unzip</button>';
				}
				if ($exp[1] == date('Y-m-d')) {
					$btn = '<button type="button" class="btn btn-success btn-sm downloaddb text-right" data-toggle="tooltip" title="Download DB" data-file="' . $row . '"><i class="fa fa-download"></i></button> ' . $archive;
					} else {
					$btn = '<button type="button" class="btn btn-info btn-sm downloaddb" data-toggle="tooltip" title="Download DB" data-file="' . $row . '"><i class="fa fa-download"></i></button> ' . $archive;
				}
				$hapus = '<button type="button" class="btn btn-danger btn-sm text-white text-right flat"  data-file="'.$row.'" data-toggle="modal" data-target="#confirm-delete"><i class="fa fa-remove"></i> Hapus</button>';
				$file = '<a class="downloaddb" data-file="' . $row . '" href="javascript:void(0)">' . $row . '</a>';
                $jam = $exp[2];
                $expj = explode('.',$jam);
                $jam = jam_replace($expj[0]);
                
                $tgl = $exp[1].' '.$jam;
                $tgl = dtimes($tgl,true,false);
                $sub_array = array();
                $sub_array[] = $file;   
                $sub_array[] = $tgl;  
                $sub_array[] = format_size($filesize) ;
                $sub_array[] = '<div class="btn-group btn-group-sm text-right" role="group">'.$btn.$hapus.'</div>';  
                $data[] = $sub_array;  
			}  
            $output = array(   
            "data" => $data  
            );  
            echo json_encode($output);  
			
		}        
        /**
			* download_db
			*
			* @return void
		*/
        public  function download_db(){
            cek_nput_post('GET');
			database_demo_admin('Unduh');
			
			$this->load->helper('download');
			$file = $this->input->post('file');
			$remoteURL = './backup_db/' . $file;
			force_download($remoteURL, null);
		}        
        /**
			* hapusdb
			*
			* @return void
		*/
        public  function hapusdb(){
            cek_nput_post('GET');
            database_demo('Hapus');
            $file = decrypt_url($this->input->post('file'));
			$cek = $this->model_app->edit('backup_data', ['id'=>$file]);
			if($cek->num_rows() > 0)
			{
				$nama_file = $cek->row()->nama_file;
				$path = FCPATH."backup_db/".$nama_file;
				if (file_exists($path)){
					unlink($path);
					$data = array('status'=>200,'file'=>$file,'msg'=>'Berhasil dihapus');
					$hapus = $this->model_app->hapus('backup_data', ['id'=>$file]);
					} else {
					$data = array('status'=>400,'file'=>'','msg'=>'File tidak ditemukan');
					$hapus = $this->model_app->hapus('backup_data', ['id'=>$file]);
				}
				}else{
				$data = array('status'=>400,'file'=>'','msg'=>'File tidak ditemukan');
			}
            echo json_encode($data);  
		}
		
        public  function backupdb(){
            database_demo('Backup');
			$result = $this->database_backup_service->create_backup($this->iduser, false);
			$this->output->set_content_type('application/json')->set_output(json_encode($result));
			return;
			
			ini_set('memory_limit','512M');
			ini_set('max_execution_time',0);
            $this->load->library('zip');
            $this->load->helper('file');

            $conditions['where'] = array();
			$counter = $this->model_app->counter('backup_data',$conditions);
			$tanggal_backup = date("Y-m-d_H-i-s");
			$nama_file = 'backup-on_'. $tanggal_backup .'-'.$counter;
			$nama_file_sql = 'backup-on_'. $tanggal_backup .'-'.$counter.'.sql';
			$nama_file_zip = 'backup-on_'. $tanggal_backup .'-'.$counter.'.zip';

            $tables = $this->db->list_tables();
            if (empty($tables)) {
                echo json_encode(array('status'=>400,'msg'=>'Backup gagal: tabel database tidak ditemukan'));
                return;
            }

            // nyiapin aturan untuk file backup
            $aturan = array(    
                'tables'      => $tables,
                'ignore'      => array(),
                'format'      => 'txt',
                'add_drop'    => TRUE,
                'add_insert'  => TRUE,
                'newline'     => "\n",
                'foreign_key_checks' => FALSE
            );
			
			$manifest = array();
			$tablesLocked = $this->lock_backup_tables($tables);
			if (!$tablesLocked) {
				echo json_encode(array('status'=>400, 'msg'=>'Backup gagal: database tidak dapat dikunci untuk membuat salinan yang konsisten'));
				return;
			}
			try {
				$backup_sql = $this->create_complete_backup_sql($aturan, $manifest);
			} finally {
				if ($tablesLocked) $this->db->query('UNLOCK TABLES');
			}
            if (empty($backup_sql)) {
                echo json_encode(array('status'=>400,'msg'=>'Backup gagal: file SQL tidak berhasil dibuat'));
                return;
            }

			$validationErrors = $this->validate_backup_sql($backup_sql, $tables, $manifest);
			if (!empty($validationErrors)) {
                echo json_encode(array(
                    'status'=>400,
					'msg'=>'Backup gagal divalidasi: '.implode('; ', array_slice($validationErrors, 0, 5))
                ));
                return;
            }

            $backup_sql = $this->backup_sql_header(count($tables)).$backup_sql;
            $this->zip->clear_data();
            $this->zip->add_data($nama_file_sql, $backup_sql);
            $backup = $this->zip->get_zip();

            $folder = FCPATH.'backup_db/';
            if (!is_dir($folder)) {
				mkdir($folder, 0777, TRUE);
			}
            $simpan = $folder.$nama_file_zip;

            if (write_file($simpan, $backup) === FALSE || !is_readable($simpan) || filesize($simpan) === 0) {
                if (file_exists($simpan)) {
                    unlink($simpan);
                }
                echo json_encode(array('status'=>400,'msg'=>'Data gagal dibackup'));
                return;
            }

			$zipValidation = $this->validate_backup_zip($simpan, $nama_file_sql, $backup_sql);
			if ($zipValidation !== true) {
				@unlink($simpan);
				echo json_encode(array('status'=>400, 'msg'=>'Backup ZIP gagal divalidasi: '.$zipValidation));
				return;
			}
			
			$params = [
                'id_user' =>$this->iduser,
                'title' =>$nama_file,
                'nama_file' =>$nama_file_zip,
                'type_file' =>'zip',
                'create_date' =>today()
			];
            $insert = $this->model_app->input('backup_data',$params);
			if($insert['status']=='ok')
			{
				$data = array(
                    'status'=>200,
					'msg'=>'Data berhasil dibackup dan diverifikasi: '.count($tables).' tabel, seluruh kolom, dan seluruh data tersimpan lengkap'
                );
			}
			else
			{
				if (file_exists($simpan)) {
                    unlink($simpan);
                }
                $data = array('status'=>400,'msg'=>'Backup gagal dicatat ke database');
			}
            echo json_encode($data);  
            // $this->load->helper('download');
            // force_download($nama_database, $backup);
		}

		public function simpan_autobackup()
		{
			if (!in_array(strtolower((string)$this->level), array('admin','owner','superadmin','super_admin'), true)) {
				$this->output->set_status_header(403)->set_content_type('application/json')->set_output(json_encode(array('status'=>false,'msg'=>'Hanya Owner/Admin/Superadmin yang dapat mengatur auto backup.')));
				return;
			}
			$aktif = (int)$this->input->post('aktif') === 1;
			$frekuensi = strtolower(trim((string)$this->input->post('frekuensi')));
			$jam = trim((string)$this->input->post('jam'));
			$settings = $this->database_backup_service->save_settings($aktif, $frekuensi, $jam);
			$this->output->set_content_type('application/json')->set_output(json_encode(array(
				'status'=>true,
				'msg'=>$aktif ? 'Auto backup berhasil diaktifkan.' : 'Auto backup berhasil dinonaktifkan.',
				'data'=>$settings
			)));
		}

        private function backup_sql_header($total_tables)
        {
            $database = $this->db->database;
            return '# Full database backup for: '.$database."\n"
                .'# Created at: '.date('Y-m-d H:i:s')."\n"
                .'# Total tables: '.$total_tables."\n\n"
                .'CREATE DATABASE IF NOT EXISTS '.$this->quote_backup_identifier($database).";\n"
                .'USE '.$this->quote_backup_identifier($database).";\n\n";
        }

		/**
		 * Membuat dump tanpa DB Utility bawaan CI.
		 * Driver CI memecah nama database yang mengandung titik menjadi tiga
		 * identifier (contoh: `app`.`poskasirpercetakan`.`tabel`) sehingga
		 * SHOW CREATE TABLE gagal. Di sini nama database dan tabel dikutip
		 * secara terpisah agar tetap valid di MariaDB/MySQL.
		 */
		private function create_complete_backup_sql(array $params, array &$manifest = array())
		{
			$tables = isset($params['tables']) ? (array)$params['tables'] : array();
			$ignore = isset($params['ignore']) ? (array)$params['ignore'] : array();
			$newline = isset($params['newline']) ? $params['newline'] : "\n";
			$addDrop = !empty($params['add_drop']);
			$addInsert = !empty($params['add_insert']);
			$foreignKeyChecks = isset($params['foreign_key_checks']) ? (bool)$params['foreign_key_checks'] : true;
			$output = '';

			if (!$foreignKeyChecks) $output .= 'SET foreign_key_checks = 0;'.$newline;
			$dbIdentifier = $this->quote_backup_identifier($this->db->database);

			foreach ($tables as $table) {
				if (in_array($table, $ignore, true)) continue;

				$tableIdentifier = $this->quote_backup_identifier($table);
				$qualifiedTable = $dbIdentifier.'.'.$tableIdentifier;
				$schemaQuery = $this->db->query('SHOW CREATE TABLE '.$qualifiedTable);
				if ($schemaQuery === false || $schemaQuery->num_rows() === 0) continue;

				$output .= '#'.$newline.'# TABLE STRUCTURE FOR: '.$table.$newline.'#'.$newline.$newline;
				if ($addDrop) $output .= 'DROP TABLE IF EXISTS '.$tableIdentifier.';'.$newline.$newline;

				$schema = $schemaQuery->row_array();
				$createSql = end($schema);
				$output .= $createSql.';'.$newline.$newline;

				$columnQuery = $this->db->query('SHOW COLUMNS FROM '.$qualifiedTable);
				$columns = array();
				if ($columnQuery !== false) {
					foreach ($columnQuery->result_array() as $column) $columns[] = $column['Field'];
				}
				$manifest[$table] = array('columns'=>$columns, 'rows'=>0);
				if (!$addInsert) continue;

				$dataQuery = $this->db->query('SELECT * FROM '.$qualifiedTable);
				if ($dataQuery === false) continue;
				$manifest[$table]['rows'] = $dataQuery->num_rows();
				if ($dataQuery->num_rows() === 0) continue;

				$fieldNames = array();
				$fieldIsInteger = array();
				while ($field = $dataQuery->result_id->fetch_field()) {
					$fieldNames[] = $this->quote_backup_identifier($field->name);
					$fieldIsInteger[] = in_array($field->type, array(
						MYSQLI_TYPE_TINY, MYSQLI_TYPE_SHORT, MYSQLI_TYPE_INT24,
						MYSQLI_TYPE_LONG, MYSQLI_TYPE_LONGLONG
					), true);
				}
				$fieldSql = implode(', ', $fieldNames);

				foreach ($dataQuery->result_array() as $row) {
					$values = array();
					$i = 0;
					foreach ($row as $value) {
						if ($value === null) $values[] = 'NULL';
						elseif (!empty($fieldIsInteger[$i]) && is_numeric($value)) $values[] = (string)$value;
						else $values[] = $this->db->escape($value);
						$i++;
					}
					$output .= 'INSERT INTO '.$tableIdentifier.' ('.$fieldSql.') VALUES ('.implode(', ', $values).');'.$newline;
				}
				$output .= $newline.$newline;
			}

			if (!$foreignKeyChecks) $output .= 'SET foreign_key_checks = 1;'.$newline;
			return $output;
		}

		private function quote_backup_identifier($identifier)
		{
			return '`'.str_replace('`', '``', (string)$identifier).'`';
		}

		private function lock_backup_tables(array $tables)
		{
			if (empty($tables)) return false;
			$dbIdentifier = $this->quote_backup_identifier($this->db->database);
			$locks = array();
			foreach ($tables as $table) {
				$locks[] = $dbIdentifier.'.'.$this->quote_backup_identifier($table).' READ';
			}
			return $this->db->query('LOCK TABLES '.implode(', ', $locks)) !== false;
		}

		private function validate_backup_sql($backupSql, array $tables, array $manifest)
		{
			$errors = array();
			foreach ($tables as $table) {
				$marker = '# TABLE STRUCTURE FOR: '.$table;
				$start = strpos($backupSql, $marker);
				if ($start === false || !isset($manifest[$table])) {
					$errors[] = $table.' tidak memiliki struktur';
					continue;
				}

				$next = strpos($backupSql, '# TABLE STRUCTURE FOR: ', $start + strlen($marker));
				$block = $next === false ? substr($backupSql, $start) : substr($backupSql, $start, $next - $start);
				foreach ($manifest[$table]['columns'] as $column) {
					if (strpos($block, $this->quote_backup_identifier($column)) === false) {
						$errors[] = $table.'.'.$column.' tidak tercantum';
					}
				}

				$pattern = '/^INSERT INTO '.preg_quote($this->quote_backup_identifier($table), '/').' \(/m';
				$actualRows = preg_match_all($pattern, $block, $matches);
				if ((int)$actualRows !== (int)$manifest[$table]['rows']) {
					$errors[] = $table.' data '.$actualRows.'/'.$manifest[$table]['rows'];
				}
			}
			return $errors;
		}

		private function validate_backup_zip($zipPath, $sqlName, $expectedSql)
		{
			if (!class_exists('ZipArchive')) return 'ekstensi ZipArchive tidak tersedia';
			$zip = new ZipArchive();
			if ($zip->open($zipPath) !== true) return 'arsip tidak dapat dibuka';
			if ($zip->numFiles !== 1 || $zip->locateName($sqlName) === false) {
				$zip->close();
				return 'file SQL tidak ditemukan atau isi arsip tidak sesuai';
			}
			$storedSql = $zip->getFromName($sqlName);
			$zip->close();
			if ($storedSql === false) return 'file SQL tidak dapat dibaca';
			if (!hash_equals(hash('sha256', $expectedSql), hash('sha256', $storedSql))) return 'checksum SQL tidak sesuai';
			return true;
		}

        private function missing_backup_tables($backup_sql, $tables)
        {
            $missing = array();
            foreach ($tables as $table) {
                if (strpos($backup_sql, '# TABLE STRUCTURE FOR: '.$table) === FALSE) {
                    $missing[] = $table;
                }
            }

            return $missing;
        }
        function unzipdb(){
            cek_nput_post('GET');
			
			
			$file = decrypt_url($this->input->post('file'));
			$cek = $this->model_app->edit('backup_data', ['id'=>$file]);
			if($cek->num_rows() > 0)
			{
				$title_file = $cek->row()->title;
				$type_file = $cek->row()->type_file;
				$nama_file = $cek->row()->nama_file;
				$nama_unzip = $cek->row()->title.'.sql';
				$nama_update = str_replace("backup","extract",$title_file);
				$nama_update_file = str_replace("backup","extract",$nama_unzip);
				## Extract the zip file ---- start
				$zip = new ZipArchive;
				$res = $zip->open("backup_db/".$nama_file);
				if ($res === TRUE) {
					
					// Unzip path
					$extractpath = "backup_db/";
					
					// Extract file
					$zip->extractTo($extractpath);
					$zip->close();
					$data = array('status'=>200,'msg'=>'File berhasil di Extract.');
					rename(FCPATH."backup_db/".$nama_unzip, FCPATH."/backup_db/".$nama_update_file);
					$params = [
					'id_user' =>$this->iduser,
					'title' =>$nama_update,
					'nama_file' =>$nama_update_file,
					'type_file' =>'sql'
					];
					$insert = $this->model_app->input('backup_data',$params);
					} else {
					$data = array('status'=>400,'msg'=>'File gagal di extract.');
					
				}
			}
			echo json_encode($data);  
			## ---- end
		}
        function restoredb()
        {
            cek_nput_post('GET');  
			
            database_demo('Restore');
			ini_set('memory_limit','256M');
			ini_set('max_execution_time',0);
			$file = decrypt_url($this->input->post('file'));
            
			$path = FCPATH.'backup_db/'.$file;
            $isi_file = file_get_contents($path);
			$string_query = rtrim( $isi_file, "\n;");
			$array_query = explode(";", $string_query);
			$_data = $this->model_app->views('backup_data')->result();
			 
			foreach($array_query as $query)
			{
				$this->db->query("SET FOREIGN_KEY_CHECKS=0;");
                $res = $this->db->query($query);
				$this->db->query("SET FOREIGN_KEY_CHECKS=1;");
			}
			
			if ($res==true) {
				$data = array('status'=>200,'msg'=>'Database berhasil di Restore.');
				$this->update_data($_data);
				}else{
				$data = array('status'=>400,'msg'=>'Database gagal di Restore.');
			}
			echo json_encode($data);  
		}
		
        private function update_data($params){
		 
			foreach($params AS $rows)
			{
				$data[] = [
				'id_user' =>$rows->id_user,
				'title' =>$rows->title,
				'nama_file' =>$rows->nama_file,
				'type_file' =>$rows->type_file,
				'create_date' =>$rows->create_date
				];
			}
			$this->db->insert_batch('backup_data', $data);
            
		}
		
        function reset_ulang()
        {
            cek_nput_post('GET');
			
            database_demo_admin('Reset');
			
            $this->db->trans_off();    
            $this->db->trans_begin();
			
			//kosongkan jenis_cetakan
            $this->db->truncate('jenis_cetakan');
			$reset_kategori = $this->dummy->reset_kategori();
			$this->db->insert_batch('jenis_cetakan', $reset_kategori);
			
			//kosongkan bahan
            $this->db->truncate('bahan');
			$bahan = $this->dummy->reset_bahan();
			$this->db->insert_batch('bahan', $bahan);
			
			//kosongkan satuan
            $this->db->truncate('satuan');
			$satuan = $this->dummy->reset_satuan();
			$this->db->insert_batch('satuan', $satuan);
			
			//kosongkan produk
            $this->db->truncate('produk');
			$produk = $this->dummy->reset_produk();
			$this->db->insert_batch('produk', $produk);
			
			//kosongkan supplier
            $this->db->truncate('supplier');
			$supplier = $this->dummy->reset_supplier();
			$this->db->insert_batch('supplier', $supplier);
			
			//kosongkan jenis_pengeluaran
            $this->db->truncate('jenis_pengeluaran');
			$jenis_pengeluaran = $this->dummy->reset_pengeluaran();
			$this->db->insert_batch('jenis_pengeluaran', $jenis_pengeluaran);
			
			//kosongkan konsumen
            $this->db->truncate('konsumen');
			$konsumen = $this->dummy->reset_konsumen();
            $this->db->insert_batch('konsumen', $konsumen);
			
			
			//kosongkan rekening_bank
            $this->db->truncate('rekening_bank');
			$rekening_bank = $this->dummy->reset_rekening();
            $this->db->insert_batch('rekening_bank', $rekening_bank);
			
            $this->db->truncate('harga');               //kosongkan harga
            $this->db->truncate('ukuran');              //kosongkan ukuran
            $this->db->truncate('invoice');             //kosongkan invoice
            $this->db->truncate('invoice_detail');      //kosongkan invoice_detail
            $this->db->truncate('bayar_invoice_detail');//kosongkan bayar_invoice_detail
            $this->db->truncate('pengeluaran');         //kosongkan pengeluaran
            $this->db->truncate('pengeluaran_detail');  //kosongkan pengeluaran_detail
            $this->db->truncate('kas_masuk');           //kosongkan kas_masuk
            $this->db->truncate('surat_jalan');         //kosongkan surat_jalan
            $this->db->truncate('user_agent');          //kosongkan user_agent
			
            $this->session->unset_userdata('cart');
            $this->session->unset_userdata('cartp');
            $this->session->unset_userdata('cartbeli');
            $this->db->update('tb_users',['last_invoice'=>0,'last_idp'=>0],['aktif'=>'Y']);
            $this->db->trans_complete();
            if ($this->db->trans_status() === FALSE)
			{
				$this->db->trans_rollback();
                $data = array('status'=>200,'msg'=>'Database gagal di reset');
			}
			else
			{
				$this->db->trans_commit();
                $data = array('status'=>200,'msg'=>'Database berhasil di reset');
			}
			
            echo json_encode($data);
		}
	}                                                    																				
