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

class Db_update_model extends CI_Model {
    var $forge = null;
    var $schema = '';
    var $table = '';
    var $defaultData = '';
    var $fields = array();
    function __construct() {
        parent::__construct();
        $this->checkSchema();
    }

    function checkSchema(){
        if ($this->schema){
            $query = 'CREATE SCHEMA IF NOT EXISTS "'.$this->schema.'";';
            return $this->db->query($query);
        }
    }

    function build(){
        if (!$this->_table_exists())
        {
                echo 'create table : <b>'.$this->schema.'.'.$this->table.'</b><br>';
                $this->forge->add_field($this->fields);
                $this->forge->create_table($this->schema.'.'.$this->table,true);
        }
        echo 'check table : <b>'.$this->schema.'.'.$this->table.'</b><br>';
        foreach($this->fields as $field=>$value){
                echo $field.' -> ';
                if(!$this->_field_exists($field)){
                        $column = array($field => $value);
                        if ($this->forge->add_column($this->schema.'.'.$this->table, $column)){
                                echo 'altered ';
                        }
                }
                if (substr($value['type'],0,6)<>'serial'){
                        unset($value['auto_increment']); // untuk alter, tidak bisa pake SERIAL
                        unset($value['default']); // bugs pada CI3, error generate 
                        $column = array($field => $value);
                        $this->forge->modify_column($this->schema.'.'.$this->table, $column);
                }
                echo 'OK, ';
                //
                
        }
        if ($this->defaultData){
            //cek, apakah data masih kosong
            $query = 'SELECT count(*) as "countRow" 
                        FROM "'.$this->schema.'"."'.$this->table.'"';
            $data = $this->db->query($query)->row_array();
            if ($data['countRow'] == 0){
                $this->db->query($this->defaultData);
                // echo $this->db->last_query();
            }
        }              
        $this->_clean_fields();
        echo "<br><br>";
    }

    private function _table_exists(){
        $query = 'SELECT * FROM "information_schema"."tables" WHERE "table_schema" = \''.$this->schema.'\' AND "table_name" = \''.$this->table.'\' ';
        $data = $this->db->query($query);
        return $data->num_rows();
    }

    private function _field_exists($fieldName){
        $query = 'SELECT * FROM "information_schema"."columns" WHERE "table_schema" = \''.$this->schema.'\' AND "table_name" = \''.$this->table.'\'AND "column_name" = \''.$fieldName.'\' ';
        $data = $this->db->query($query);
        return $data->num_rows();
    }

    private function _clean_fields(){
        $key = '';
        $defaultNow = '';
        foreach($this->fields as $field=>$value){
                if (isset($value['key'])){
                        if ($value['key']){
                                $key .= '"'.$field.'",';      
                        }
                }
                if (isset($value['default'])){
                        if ($value['default']=='now()'){
                                $query = 'ALTER TABLE "'.$this->schema.'"."'.$this->table.'" ALTER COLUMN "'.$field.'" SET DEFAULT now() ';
                                $this->db->query($query);
                        }
                }
                
        }
        //
        $key = substr($key,0,strlen($key)-1);
        if ($key){
                $query = 'ALTER TABLE "'.$this->schema.'"."'.$this->table.'" DROP CONSTRAINT "'.$this->table.'_pkey"';
                $this->db->query($query);
                $query = 'ALTER TABLE "'.$this->schema.'"."'.$this->table.'" ADD PRIMARY KEY ('.$key.')';
                $this->db->query($query);
        }

    }
    
}