<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Database_backup_service
{
    private $CI;

    public function __construct()
    {
        $this->CI =& get_instance();
        $this->ensure_settings_schema();
    }

    private function ensure_settings_schema()
    {
        if (!$this->CI->db->table_exists('info')) return;
        $this->CI->load->dbforge();
		$existing = $this->CI->db->list_fields('info');
        $fields = array();
        if (!in_array('autobackup_aktif',$existing,true)) $fields['autobackup_aktif'] = array('type'=>'TINYINT','constraint'=>1,'null'=>false,'default'=>0);
        if (!in_array('autobackup_frekuensi',$existing,true)) $fields['autobackup_frekuensi'] = array('type'=>'VARCHAR','constraint'=>10,'null'=>false,'default'=>'harian');
        if (!in_array('autobackup_jam',$existing,true)) $fields['autobackup_jam'] = array('type'=>'TIME','null'=>false,'default'=>'02:00:00');
        if (!in_array('autobackup_terakhir',$existing,true)) $fields['autobackup_terakhir'] = array('type'=>'DATETIME','null'=>true);
        if (!in_array('autobackup_berikutnya',$existing,true)) $fields['autobackup_berikutnya'] = array('type'=>'DATETIME','null'=>true);
        if (!in_array('autobackup_status',$existing,true)) $fields['autobackup_status'] = array('type'=>'VARCHAR','constraint'=>20,'null'=>false,'default'=>'belum_dijalankan');
        if (!in_array('autobackup_pesan',$existing,true)) $fields['autobackup_pesan'] = array('type'=>'VARCHAR','constraint'=>255,'null'=>true);
        if (!empty($fields)) $this->CI->dbforge->add_column('info', $fields);
    }

    public function settings()
    {
        return $this->CI->db->select('autobackup_aktif,autobackup_frekuensi,autobackup_jam,autobackup_terakhir,autobackup_berikutnya,autobackup_status,autobackup_pesan')->limit(1)->get('info')->row_array();
    }

    public function save_settings($active, $frequency, $time = '02:00')
    {
        if (!in_array($frequency, array('harian','mingguan','bulanan'), true)) $frequency = 'harian';
		$time = $this->normalize_time($time);
        $next = $active ? $this->initial_next_time($frequency, $time) : null;
        $this->CI->db->update('info', array(
            'autobackup_aktif'=>$active ? 1 : 0,
            'autobackup_frekuensi'=>$frequency,
			'autobackup_jam'=>$time.':00',
            'autobackup_berikutnya'=>$next,
            'autobackup_status'=>$active ? 'terjadwal' : 'nonaktif',
            'autobackup_pesan'=>$active ? 'Auto backup menunggu jadwal berikutnya' : 'Auto backup dinonaktifkan'
        ));
        return $this->settings();
    }

    public function run_if_due()
    {
        $row = $this->settings();
        if (!$row || (int)$row['autobackup_aktif'] !== 1 || empty($row['autobackup_berikutnya']) || strtotime($row['autobackup_berikutnya']) > time()) return false;

        $next = $this->advance_until_future($row['autobackup_frekuensi'], $row['autobackup_berikutnya'], $row['autobackup_jam']);
        $this->CI->db->where('autobackup_aktif', 1)->where('autobackup_berikutnya <=', date('Y-m-d H:i:s'))
            ->update('info', array('autobackup_berikutnya'=>$next,'autobackup_status'=>'berjalan','autobackup_pesan'=>'Auto backup sedang diproses'));
        if ($this->CI->db->affected_rows() !== 1) return false;

        $userId = isset($this->CI->session) ? (int)$this->CI->session->userdata('idu') : 0;
        $result = $this->create_backup($userId, true);
        $this->CI->db->update('info', array(
            'autobackup_terakhir'=>date('Y-m-d H:i:s'),
            'autobackup_status'=>$result['status'] === 200 ? 'berhasil' : 'gagal',
            'autobackup_pesan'=>$result['msg']
        ));
        return $result;
    }

    public function create_backup($userId, $automatic = false)
    {
        ini_set('memory_limit','512M'); ini_set('max_execution_time',0);
        $this->CI->load->library('zip'); $this->CI->load->helper('file');
        $tables = $this->CI->db->list_tables();
        if (!$tables) return array('status'=>400,'msg'=>'Tabel database tidak ditemukan');

        $counter = $this->CI->db->count_all('backup_data');
        $stamp = date('Y-m-d_H-i-s');
        $prefix = $automatic ? 'autobackup-on_' : 'backup-on_';
        $title = $prefix.$stamp.'-'.$counter; $sqlName=$title.'.sql'; $zipName=$title.'.zip';
        $manifest=array(); $locked=$this->lock_tables($tables);
        if (!$locked) return array('status'=>400,'msg'=>'Database tidak dapat dikunci untuk backup konsisten');
        try { $sql=$this->build_sql($tables,$manifest); } finally { $this->CI->db->query('UNLOCK TABLES'); }
        $errors=$this->validate_sql($sql,$tables,$manifest);
        if ($errors) return array('status'=>400,'msg'=>'Backup tidak lengkap: '.implode('; ',array_slice($errors,0,5)));
        $sql=$this->header(count($tables)).$sql;

        $this->CI->zip->clear_data(); $this->CI->zip->add_data($sqlName,$sql); $binary=$this->CI->zip->get_zip();
        $folder=FCPATH.'backup_db/'; if(!is_dir($folder) && !mkdir($folder,0777,true)) return array('status'=>400,'msg'=>'Folder backup tidak dapat dibuat');
        $path=$folder.$zipName;
        if(write_file($path,$binary)===false || !is_readable($path) || filesize($path)===0) return array('status'=>400,'msg'=>'File backup gagal disimpan');
        $zipCheck=$this->validate_zip($path,$sqlName,$sql); if($zipCheck!==true){@unlink($path);return array('status'=>400,'msg'=>'Backup ZIP tidak valid: '.$zipCheck);}

        $insert=$this->CI->model_app->input('backup_data',array('id_user'=>(int)$userId,'title'=>$title,'nama_file'=>$zipName,'type_file'=>'zip','create_date'=>date('Y-m-d H:i:s')));
        if($insert['status']!=='ok'){@unlink($path);return array('status'=>400,'msg'=>'Backup gagal dicatat');}
        return array('status'=>200,'msg'=>($automatic?'Auto backup':'Backup').' berhasil diverifikasi: '.count($tables).' tabel, seluruh kolom dan data lengkap');
    }

    private function next_time($frequency,$from)
    {
		$timestamp = strtotime($from);
		if ($frequency === 'mingguan') return date('Y-m-d H:i:s', strtotime('+7 days', $timestamp));
		if ($frequency !== 'bulanan') return date('Y-m-d H:i:s', strtotime('+1 day', $timestamp));
		$year=(int)date('Y',$timestamp); $month=(int)date('n',$timestamp)+1;
		if($month>12){$month=1;$year++;}
		$day=min((int)date('j',$timestamp),(int)date('t',mktime(0,0,0,$month,1,$year)));
		return sprintf('%04d-%02d-%02d %s',$year,$month,$day,date('H:i:s',$timestamp));
    }

	private function normalize_time($time)
	{
		$time=trim((string)$time);
		if(!preg_match('/^(?:[01]\d|2[0-3]):[0-5]\d$/',$time)) return '02:00';
		return $time;
	}

	private function initial_next_time($frequency,$time)
	{
		$candidate=date('Y-m-d').' '.$time.':00';
		if(strtotime($candidate)>time()) return $candidate;
		return $this->next_time($frequency,$candidate);
	}

	private function advance_until_future($frequency,$scheduled,$time)
	{
		$time=$this->normalize_time(substr((string)$time,0,5));
		$base=date('Y-m-d',strtotime($scheduled)).' '.$time.':00';
		do{$base=$this->next_time($frequency,$base);}while(strtotime($base)<=time());
		return $base;
	}
    private function q($name){return '`'.str_replace('`','``',(string)$name).'`';}
    private function lock_tables($tables){$db=$this->q($this->CI->db->database);$locks=array();foreach($tables as $t)$locks[]=$db.'.'.$this->q($t).' READ';return $this->CI->db->query('LOCK TABLES '.implode(', ',$locks))!==false;}
    private function header($count){$db=$this->q($this->CI->db->database);return '# Full database backup for: '.$this->CI->db->database."\n# Created at: ".date('Y-m-d H:i:s')."\n# Total tables: $count\n\nCREATE DATABASE IF NOT EXISTS $db;\nUSE $db;\n\n";}
    private function build_sql($tables,&$manifest)
    {
        $out="SET foreign_key_checks = 0;\n";$db=$this->q($this->CI->db->database);
        foreach($tables as $t){$qt=$this->q($t);$fq=$db.'.'.$qt;$schema=$this->CI->db->query('SHOW CREATE TABLE '.$fq)->row_array();$create=end($schema);$out.="#\n# TABLE STRUCTURE FOR: $t\n#\n\nDROP TABLE IF EXISTS $qt;\n\n$create;\n\n";
            $cols=array();foreach($this->CI->db->query('SHOW COLUMNS FROM '.$fq)->result_array() as $c)$cols[]=$c['Field'];$data=$this->CI->db->query('SELECT * FROM '.$fq);$manifest[$t]=array('columns'=>$cols,'rows'=>$data->num_rows());
            if($data->num_rows()){ $fields=array();$ints=array();while($f=$data->result_id->fetch_field()){$fields[]=$this->q($f->name);$ints[]=in_array($f->type,array(MYSQLI_TYPE_TINY,MYSQLI_TYPE_SHORT,MYSQLI_TYPE_INT24,MYSQLI_TYPE_LONG,MYSQLI_TYPE_LONGLONG),true);} $fieldSql=implode(', ',$fields);
                foreach($data->result_array() as $row){$vals=array();$i=0;foreach($row as $v){$vals[]=$v===null?'NULL':((!empty($ints[$i])&&is_numeric($v))?(string)$v:$this->CI->db->escape($v));$i++;}$out.='INSERT INTO '.$qt.' ('.$fieldSql.') VALUES ('.implode(', ',$vals).');' . "\n";}}
            $out.="\n\n";
        } return $out."SET foreign_key_checks = 1;\n";
    }
    private function validate_sql($sql,$tables,$manifest){$e=array();foreach($tables as $t){$m='# TABLE STRUCTURE FOR: '.$t;$s=strpos($sql,$m);if($s===false||!isset($manifest[$t])){$e[]="$t tanpa struktur";continue;}$n=strpos($sql,'# TABLE STRUCTURE FOR: ',$s+strlen($m));$b=$n===false?substr($sql,$s):substr($sql,$s,$n-$s);foreach($manifest[$t]['columns'] as $c)if(strpos($b,$this->q($c))===false)$e[]="$t.$c hilang";$actual=preg_match_all('/^INSERT INTO '.preg_quote($this->q($t),'/').' \(/m',$b,$x);if($actual!=$manifest[$t]['rows'])$e[]="$t data $actual/{$manifest[$t]['rows']}";}return $e;}
    private function validate_zip($path,$name,$sql){if(!class_exists('ZipArchive'))return'ZipArchive tidak tersedia';$z=new ZipArchive();if($z->open($path)!==true)return'arsip tidak dapat dibuka';$stored=$z->numFiles===1?$z->getFromName($name):false;$z->close();if($stored===false)return'file SQL tidak sesuai';return hash_equals(hash('sha256',$sql),hash('sha256',$stored))?true:'checksum tidak sesuai';}
}
