<?php
/**
 * Installer
 * @package ci-hotel
 *
 */
class Install extends Controller {

    public function __construct()
    {
        parent::Controller();
    }
    
    public function index()
    {
        $this->load->helper(array('form', 'url'));
        
        if ($this->is_installed() == false)
        {
            $this->load->view('install/index');
            return;
        }
        $this->load->view('install/installed');
    }
    
    /**
     * データベースがインストールされているかを返す
     */
    private function is_installed()
    {
        if ($this->db->table_exists('system') == true)
        {
            return true;
        }
        return false;
    }
    
    public function create_schema()
    {
        $this->load->helper(array('form', 'url'));
        
        if ($this->is_installed() == true)
        {
            $this->load->view('install/installed');
            return;
        }
        
        $q = array();
        $q[] = 'CREATE TABLE system ('
             . 'name VARCHAR(255) NOT NULL UNIQUE,'
             . 'value VARCHAR(255))';
        $q[] = 'CREATE TABLE hotels ('
             . 'hotelid INT NOT NULL AUTO_INCREMENT,'
             . 'hotelcode VARCHAR(128),'
             . 'hotel_name VARCHAR(255) NOT NULL,'
             . 'postcode VARCHAR(10),'
             . 'prefecture VARCHAR(255),'
             . 'city VARCHAR(255),'
             . 'street VARCHAR(255),'
             . 'phone VARCHAR(16),'
             . 'email VARCHAR(255),'
             . 'url VARCHAR(255),'
             . 'res_prev_days INT NOT NULL DEFAULT 0,'
             . 'res_term INT NOT NULL DEFAULT 6,'
             . 'res1_mail TEXT,'
             . 'res2_mail TEXT,'
             . 'res_cancel_mail TEXT,'
             . 'cancel_porycy TEXT,'
             . 'topics TEXT,'
             . 'cin_hour TIME,'
             . 'cin_hour2 TIME,'
             . 'cout_hour TIME,'
             . 'lat DOUBLE,'
             . 'lng DOUBLE,'
             . 'tentative_reservation TINYINT(1) DEFAULT 1,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'status INT NOT NULL DEFAULT 1,'
             . 'PRIMARY KEY(hotelid))';
        $q[] = 'CREATE TABLE plans ('
             . 'planid INT NOT NULL AUTO_INCREMENT,'
             . 'plan_name VARCHAR(255) NOT NULL,'
             . 'hotelid INT DEFAULT 1,'
             . 'roomtypeid INT NOT NULL,'
             . 'status INT NOT NULL,'
             . 'summary TEXT,'
             . 'description TEXT,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'start_date DATE,'
             . 'end_date DATE,'
             . 'breakfast TINYINT(1) NOT NULL DEFAULT 0,'
             . 'dinner TINYINT(1) NOT NULL DEFAULT 0,'
             . 'max_rooms INT,'
             . 'guest_min INT NOT NULL,'
             . 'guest_max INT NOT NULL,'
             . 'default_num INT NOT NULL,'
             . 'basic_count  TINYINT(1) NOT NULL DEFAULT 1,'
             . 'child1_count TINYINT(1) DEFAULT 1,'
             . 'child2_count TINYINT(1) DEFAULT 0,'
             . 'child3_count TINYINT(1) DEFAULT 0,'
             . 'child4_count TINYINT(1) DEFAULT 0,'
             . 'PRIMARY KEY(planid))';
        $q[] = 'CREATE TABLE roomtypes ('
             . 'roomtypeid INT NOT NULL AUTO_INCREMENT,'
             . 'roomtype_name VARCHAR(255),'
             . 'hotelid INT DEFAULT 1,'
             . 'status INT NOT NULL,'
             . 'summary TEXT,'
             . 'description TEXT,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'start_date DATE,'
             . 'end_date DATE,'
             . 'guest_min INT NOT NULL,'
             . 'guest_max INT NOT NULL,'
             . 'default_num INT NOT NULL,'
             . 'PRIMARY KEY(roomtypeid))';
        $q[] = 'CREATE TABLE daytype ('
             . 'day DATE NOT NULL,'
             . 'hotelid INT DEFAULT 1,'
             . 'value VARCHAR(1))';
        $q[] = 'CREATE TABLE planprice ('
             . 'planid INT NOT NULL,'
             . 'a_basic_price VARCHAR(255),'
             . 'b_basic_price VARCHAR(255),'
             . 'c_basic_price VARCHAR(255),'
             . 'd_basic_price VARCHAR(255),'
             . 'a_child1_price VARCHAR(255),'
             . 'b_child1_price VARCHAR(255),'
             . 'c_child1_price VARCHAR(255),'
             . 'd_child1_price VARCHAR(255),'
             . 'a_child2_price VARCHAR(255),'
             . 'b_child2_price VARCHAR(255),'
             . 'c_child2_price VARCHAR(255),'
             . 'd_child2_price VARCHAR(255),'
             . 'a_child3_price VARCHAR(255),'
             . 'b_child3_price VARCHAR(255),'
             . 'c_child3_price VARCHAR(255),'
             . 'd_child3_price VARCHAR(255),'
             . 'a_child4_price VARCHAR(255),'
             . 'b_child4_price VARCHAR(255),'
             . 'c_child4_price VARCHAR(255),'
             . 'd_child4_price VARCHAR(255),'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'status INT)';
        $q[] = 'CREATE TABLE reservation ('
             . 'resid INT NOT NULL AUTO_INCREMENT,'
             . 'rescode VARCHAR(32),'
             . 'memberid INT NOT NULL,'
             . 'hotelid INT NOT NULL,'
             . 'planid INT NOT NULL,'
             . 'roomtypeid INT NOT NULL,'
             . 'room_num INT NOT NULL,'
             . 'plan_num INT NOT NULL,'
             . 'cin DATE NOT NULL,'
             . 'cinhour TIME,'
             . 'cout DATE NOT NULL,'
             . 'created DATETIME,'
             . 'total_price INT,'
             . 'status INT,'
             . 'memo VARCHAR(255),'
             . 'PRIMARY KEY(resid))';
        // 予約部屋構成
        $q[] = 'CREATE TABLE resrooms ('
             . 'resid INT NOT NULL,'
             . 'day DATE NOT NULL,'
             . 'roomtypeid INT NOT NULL,'
             . 'room_no INT NOT NULL,'
             . 'basic_num INT NOT NULL,'
             . 'child1_num INT NOT NULL,'
             . 'child2_num INT NOT NULL,'
             . 'child3_num INT NOT NULL,'
             . 'child4_num INT NOT NULL,'
             . 'basic_price INT NOT NULL,'
             . 'child1_price INT NOT NULL,'
             . 'child2_price INT NOT NULL,'
             . 'child3_price INT NOT NULL,'
             . 'child4_price INT NOT NULL,'
             . 'subtotal INT NOT NULL)';
        // 予約者情報
        $q[] = 'CREATE TABLE resmember ('
             . 'resid INT NOT NULL,'
             . 'memberid INT NOT NULL,'       // 0:未登録ユーザ,それ以外:登録ユーザ
             . 'name VARCHAR(255),'
             . 'name_kana VARCHAR(255),'
             . 'birth DATE,'
             . 'gender TINYINT(1),'
             . 'postcode VARCHAR(10),'
             . 'prefecture VARCHAR(255),'
             . 'city VARCHAR(255),'
             . 'street VARCHAR(255),'
             . 'email VARCHAR(255),'
             . 'phone VARCHAR(16),'
             . 'email2 VARCHAR(255),'
             . 'phone2 VARCHAR(16),'
             . 'company VARCHAR(255),'
             . 'division VARCHAR(255),'
             . 'fax VARCHAR(16))';
             
        $q[] = 'CREATE TABLE members ('
             . 'memberid INT NOT NULL AUTO_INCREMENT,'
             . 'hotelid INT,'
             . 'password VARCHAR(255),'
             . 'name VARCHAR(255),'
             . 'name_kana VARCHAR(255),'
             . 'birth DATE,'
             . 'gender TINYINT(1),'
             . 'postcode VARCHAR(10),'
             . 'prefecture VARCHAR(255),'
             . 'city VARCHAR(255),'
             . 'street VARCHAR(255),'
             . 'email VARCHAR(255) NOT NULL,'
             . 'phone VARCHAR(16),'
             . 'email2 VARCHAR(255),'
             . 'phone2 VARCHAR(16),'
             . 'company VARCHAR(255),'
             . 'division VARCHAR(255),'
             . 'fax VARCHAR(16),'
             . 'status INT,'
             . 'actkey VARCHAR(128),'
             . 'last_login DATETIME,'
             . 'PRIMARY KEY(memberid))';
        $q[] = 'CREATE TABLE admins ('
             . 'adminid INT NOT NULL AUTO_INCREMENT,'
             . 'hotelid INT,'
             . 'username VARCHAR(255) NOT NULL UNIQUE,'
             . 'password VARCHAR(255),'
             . 'email VARCHAR(255) NOT NULL,'
             . 'last_login DATETIME,'
             . 'status INT,'
             . 'PRIMARY KEY(adminid))';
        $q[] = 'CREATE TABLE supers ('
             . 'superid INT NOT NULL AUTO_INCREMENT,'
             . 'username VARCHAR(255) NOT NULL UNIQUE,'
             . 'password VARCHAR(255),'
             . 'email VARCHAR(255) NOT NULL,'
             . 'last_login DATETIME,'
             . 'status INT,'
             . 'PRIMARY KEY(superid))';
        
        $q[] = 'CREATE TABLE vacantrooms ('
             . 'roomtypeid INT NOT NULL,'
             . 'day DATE NOT NULL,'
             . 'vr_inuse INT NOT NULL,'
             . 'vr_num INT NOT NULL)';
        $q[] = 'CREATE TABLE vacantplans ('
             . 'planid INT NOT NULL,'
             . 'day DATE NOT NULL,'
             . 'vp_inuse INT NOT NULL,'
             . 'vp_num INT NOT NULL)';
        
        // 宿泊オプション
        $q[] = 'CREATE TABLE options ('
             . 'optionid INT NOT NULL AUTO_INCREMENT,'
             . 'option_name VARCHAR(255),'
             . 'hotelid INT,'
             . 'start_date DATE,'
             . 'end_date DATE,'
             . 'price INT NOT NULL,'
             . 'default_num INT NOT NULL,'
             . 'summary TEXT,'
             . 'created DATETIME,'
             . 'modified DATETIME)';
        $q[] = 'CREATE TABLE plan_options ('
             . 'planid INT NOT NULL,'
             . 'optionid INT NOT NULL,'
             . 'hotelid INT NOT NULL)';
        
        // data
        $q[] = "INSERT INTO supers ("
             . "superid, username, email, password, status) VALUES("
             . "1, 'super', 'super', '" . sha1("super") . "', 1)";
        $q[] = "INSERT INTO admins ("
             . "adminid, username, hotelid, email, password, status) VALUES("
             . "1, 'admin', 1, 'admin', '" . sha1("admin") . "', 1)";
        
        $q[] = "INSERT INTO hotels ("
             . "hotelid, hotel_name, res_prev_days, res_term) VALUES("
             . "1, 'test-hotel', 0, 6)";
        foreach ($q as $v)
        {
            $this->db->query($v);
        }
        $this->load->view('install/create_schema');
    }
    
}

/* End of file install.php */
