<?php
class Updatesoft_Model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
        $this->load->dbforge();
        $this->QueueId = 0;
    }

    public function checkPass($data)
    {
        $this->db->select("*");
        $this->db->from("tms_user_master");
        $this->db->where("id", $data['id']);
        $this->db->where("password", md5($data['password']));
        return $this->db->get()->num_rows();
    }

    public function insertUpdateLog($data, $id = 0)
    {
        if ($id > 0) {
            $this->db->where("id", $id);
            $this->db->update("tms_updatesoft_log", $data);
            return $id;
        } else {
            $this->db->insert("tms_updatesoft_log", $data);
            return $this->db->insert_id();
        }
    }


    public function createTable($tableName, $columns)
    {

        try {
            if ($this->QueueId > 0) {
                $tableName = str_replace('##QU##', $this->QueueId, $tableName);
            }


            if (!$this->db->table_exists($tableName)) {
                $columnsAr = explode(',', trim($columns));


                foreach ($columnsAr as $column) {
                    if (strpos($column, "##QU##")) {
                        $column  = str_replace('##QU##', $this->QueueId, $column);
                    }
                    $this->dbforge->add_field(trim($column));
                }
                $attributes = array('ENGINE' => 'InnoDB', 'COLLATE' => 'latin1_swedish_ci', 'CHARACTER SET' => 'latin1');
                $tblCreate = $this->dbforge->create_table($tableName, true, $attributes);
                $dberr = $this->db->error();
                if ($tblCreate == 1) {
                    return array("status" => true, "msg" => "Table($tableName) Created Successfully");
                } else {
                    throw new Exception($dberr['message']);
                }
            } else {
                throw new Exception("Table($tableName) Already Exists");
            }
        } catch (Exception $e) {
            return array("status" => false, "msg" => $e->getMessage());
        }
    }

    public function dropTable($tableName)
    {
        try {
            if ($this->QueueId > 0) {
                $tableName = str_replace('##QU##', $this->QueueId, $tableName);
            }

            if ($this->db->table_exists($tableName)) {
                $tblDrop = $this->dbforge->drop_table($tableName);
                $dberr = $this->db->error();
                if ($tblDrop == 1) {
                    return array("status" => true, "msg" => "Table($tableName) Dropped Successfully");
                } else {
                    throw new Exception($dberr['message']);
                }
            } else {
                throw new Exception("Table($tableName) Not Exists");
            }
        } catch (Exception $e) {
            return array("status" => false, "msg" => $e->getMessage());
        }
    }

    public function renameTable($oldTableName, $newTableName)
    {
        try {
            if ($this->QueueId > 0) {
                $oldTableName = str_replace('##QU##', $this->QueueId, $oldTableName);
                $newTableName = str_replace('##QU##', $this->QueueId, $newTableName);
            }
            if ($this->db->table_exists($oldTableName)) {
                $tblRename = $this->dbforge->rename_table($oldTableName, $newTableName);
                $dberr = $this->db->error();
                if ($tblRename == 1) {
                    return array("status" => true, "msg" => "Table($oldTableName) Renamed  to ($newTableName) Successfully");
                } else {
                    throw new Exception($dberr['message']);
                }
            } else {
                throw new Exception("Table($oldTableName) Not Exists");
            }
        } catch (Exception $e) {
            return array("status" => false, "msg" => $e->getMessage());
        }
    }

    public function addColumn($tableName, $columnName, $dataType, $dataTypeVal, $allowNull, $defaultValue, $afterColumnName)
    {
        try {

            if ($this->QueueId > 0) {
                $tableName = str_replace('##QU##', $this->QueueId, $tableName);
            }

            if ($this->db->table_exists($tableName)) {
                if (!$this->db->field_exists($columnName, $tableName)) {
                    $fields = array(
                        "$columnName" => array(
                            'type' => $dataType,
                            'constraint' => $dataTypeVal,
                            'null' => ($allowNull == 'Y') ? TRUE : FALSE,
                            'default' => ($defaultValue == 'NULL') ? NULL : $defaultValue,
                            'after' => $afterColumnName
                        )
                    );
                    $addCol = $this->dbforge->add_column($tableName, $fields);
                    $dberr = $this->db->error();
                    if ($addCol == 1) {
                        return array("status" => true, "msg" => "Column($columnName) added in Table($tableName)");
                    } else {
                        throw new Exception($dberr['message']);
                    }
                } else {
                    throw new Exception("Column($columnName) Already Exists in Table($tableName)");
                }
            } else {
                throw new Exception("Table($tableName) Not Exists");
            }
        } catch (Exception $e) {
            return array("status" => false, "msg" => $e->getMessage());
        }
    }

    public function dropColumn($tableName, $columnName)
    {
        try {
            if ($this->QueueId > 0) {
                $tableName = str_replace('##QU##', $this->QueueId, $tableName);
            }
            if ($this->db->table_exists($tableName)) {
                if ($this->db->field_exists($columnName, $tableName)) {
                    $dropCol = $this->dbforge->drop_column($tableName, $columnName);
                    $dberr = $this->db->error();
                    if ($dropCol == 1) {
                        return array("status" => true, "msg" => "Column($columnName) dropped in Table($tableName)");
                    } else {
                        throw new Exception($dberr['message']);
                    }
                } else {
                    throw new Exception("Column($columnName) Not Exists in Table($tableName)");
                }
            } else {
                throw new Exception("Table($tableName) Not Exists");
            }
        } catch (Exception $e) {
            return array("status" => false, "msg" => $e->getMessage());
        }
    }


    public function alterTable($tableName, $oldColumnName, $newColumnName, $dataType, $dataTypeVal, $allowNull, $defaultValue, $afterColumnName)
    {
        try {
            if ($this->QueueId > 0) {
                $tableName = str_replace('##QU##', $this->QueueId, $tableName);
            }

            if ($this->db->table_exists($tableName)) {
                if ($this->db->field_exists($oldColumnName, $tableName)) {
                    $fields = array(
                        $oldColumnName => array(
                            'name' => $newColumnName,
                            'type' => $dataType,
                            'constraint' => $dataTypeVal,
                            'null' => ($allowNull == 'Y') ? TRUE : FALSE,
                            'default' => ($defaultValue == 'NULL') ? NULL : $defaultValue,
                            'after' => $afterColumnName
                        )
                    );
                    $alttbl = $this->dbforge->modify_column($tableName, $fields);
                    $dberr = $this->db->error();
                    if ($alttbl == 1) {
                        return array("status" => true, "msg" => "Column($oldColumnName) has been altered to ($newColumnName) in table ($tableName)");
                    } else {
                        throw new Exception($dberr['message']);
                    }
                } else {
                    throw new Exception("Column($oldColumnName) Not Exists in Table($tableName)");
                }
            } else {
                throw new Exception("Table($tableName) Not Exists");
            }
        } catch (Exception $e) {
            return array("status" => false, "msg" => $e->getMessage());
        }
    }


    public function query($query)
    {
        try {

            if ($this->QueueId > 0) {
                $query = str_replace('##QU##', $this->QueueId, $query);
            }

            $qry = $this->db->query($query);
            $dberr = $this->db->error();
            if ($qry == 1) {
                return array("status" => true, "msg" => "Query has been run successfully");
            } else {
                throw new Exception($dberr['message']);
            }
        } catch (Exception $e) {
            return array("status" => false, "msg" => $e->getMessage());
        }
    }


    public function createProcedure($procedureName, $procedureStmt)
    {
        try {
            $checkPro =  $this->db->query("select routine_name as procedure_name from information_schema.routines where routine_type='PROCEDURE' AND routine_name = '" . $procedureName . "'")->row()->procedure_name;

            if (!empty($checkPro)) {
                $newProcedureName = $procedureName . "_" . date("Ymd_His");
                $updatebkp =  $this->db->query("UPDATE `mysql`.`proc` SET NAME = '" . $newProcedureName . "',specific_name = '" . $newProcedureName . "' WHERE `mysql`.`proc`.type ='PROCEDURE' AND name = '" . $procedureName . "'");
                $dbErr = $this->db->error();
                if ($updatebkp != 1) {
                    throw new Exception($dbErr['message']);
                }
            }
            $crtpro = $this->db->query("CREATE PROCEDURE $procedureName " . $procedureStmt);

            $dbErr = $this->db->error();
            if ($crtpro == 1) {
                return array("status" => true, "msg" => "Procedure ($procedureName) has been created successfully");
            } else {
                throw new Exception($dbErr['message']);
            }
        } catch (Exception $e) {
            return array("status" => false, "msg" => $e->getMessage());
        }
    }



    public function createFunction($functionName, $functionStmt)
    {


        try {

            $dbName = $this->db->database;

            $checkFunExists =  $this->db->query("SELECT routine_name as function_name FROM information_schema.routines WHERE routine_schema = '" . $dbName . "' AND routine_type = 'FUNCTION' AND routine_name  ='" . $functionName . "'")->row()->function_name;


            if (!empty($checkFunExists)) {
                $newFunctionName = $functionName . "_" . date("Ymd_His");
                $updatefunBkp = $this->db->query("UPDATE `mysql`.`proc` SET NAME = '" . $newFunctionName . "',specific_name = '" . $newFunctionName . "' WHERE `mysql`.`proc`.type ='function' AND  name = '" . $functionName . "'");
                $dbErr = $this->db->error();
                if ($updatefunBkp != 1) {
                    throw new Exception($dbErr['message']);
                }
            }
            $crtFun = $this->db->query("CREATE FUNCTION $functionName " . $functionStmt);
            $dbErr = $this->db->error();
            if ($crtFun == 1) {
                return array("status" => true, "msg" => "Function ($functionName) has been created successfully");
            } else {
                throw new Exception($dbErr['message']);
            }
        } catch (Exception $e) {
            return array("status" => false, "msg" => $e->getMessage());
        }
    }


    public function getAllQueueData()
    {
        $this->db->select("id as queue_id,queue as queue_name");
        $this->db->from("tms_queue_master");
        return $this->db->get()->result();
    }
}
