<?php
/**
 * Installer
 * @package ci-hotel
 * @author      Teruki Hosokawa <hosotk@gmail.com>
 * @licence     New BSD Licence
 */
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,'
             . '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_policy TEXT,'
             . 'topics TEXT,'
             . 'cin_hour TIME,'
             . 'cin_hour2 TIME,'
             . 'cout_hour TIME,'
             . 'lat DOUBLE,'
             . 'lng DOUBLE,'
             . 'res_mode TINYINT DEFAULT 1,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'status INT NOT NULL DEFAULT 1,'
             . 'PRIMARY KEY(hotelid))';
        $q[] = 'CREATE TABLE plans ('
             . 'hotelid INT NOT NULL,'
             . 'planid INT NOT NULL,'
             . 'plan_name VARCHAR(255) NOT NULL,'
             . 'roomtypeid INT NOT NULL,'
             . 'status INT NOT NULL,'
             . 'summary TEXT,'
             . 'description TEXT,'
             . 'image TEXT,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'start_date DATE,'
             . 'end_date DATE,'
             . 'meal TINYINT NOT NULL DEFAULT 0,'
             . 'max_rooms INT,'
             . 'max_days INT,'
             . 'guest_min INT NOT NULL,'
             . 'guest_max INT NOT NULL,'
             . 'default_num INT NOT NULL,'
             . 'PRIMARY KEY(hotelid, planid))';
        $q[] = 'CREATE TABLE guesttypes ('
             . 'hotelid INT NOT NULL,'
             . 'guesttypeid INT NOT NULL,'
             . 'guesttype_name VARCHAR(255) NOT NULL,'
             . 'summary TEXT,'
             . 'description TEXT,'
             . 'roomtype_rate INT NOT NULL DEFAULT 100,'
             . 'plan_rate INT NOT NULL DEFAULT 100,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'status TINYINT NOT NULL DEFAULT 1,'
             . 'rate TINYINT DEFAULT 100,'
             . 'ordering INT DEFAULT 0,'
             . 'PRIMARY KEY(hotelid, guesttypeid))';
        $q[] = 'CREATE TABLE roomtypes ('
             . 'hotelid INT NOT NULL,'
             . 'roomtypeid INT NOT NULL,'
             . 'roomtype_name VARCHAR(255),'
             . 'status INT NOT NULL,'
             . 'summary TEXT,'
             . 'description TEXT,'
             . 'image TEXT,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'start_date DATE,'
             . 'end_date DATE,'
             . 'guest_min INT NOT NULL,'
             . 'guest_max INT NOT NULL,'
             . 'ordering INT,'
             . 'default_num INT NOT NULL,'
             . 'PRIMARY KEY(hotelid, roomtypeid))';
        $q[] = 'CREATE TABLE daytypes ('
             . 'hotelid INT NOT NULL,'
             . 'daytypeid INT NOT NULL,'
             . 'daytype_name VARCHAR(255),'
             . 'summary TEXT,'
             . 'color VARCHAR(64),'
             . 'ordering INT DEFAULT 0,'
             . 'status INT,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'rate INT DEFAULT 100,'
             . 'PRIMARY KEY(hotelid, daytypeid))';
        $q[] = 'CREATE TABLE options ('
             . 'hotelid INT NOT NULL,'
             . 'optionid INT NOT NULL,'
             . 'option_name VARCHAR(255),'
             . 'start_date DATE,'
             . 'end_date DATE,'
             . 'price INT NOT NULL,'
             . 'unitname VARCHAR(32),'
             . 'default_num INT NOT NULL,'
             . 'max_num INT NOT NULL,'
             . 'status TINYINT,'
             . 'summary TEXT,'
             . 'description TEXT,'
             . 'image TEXT,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'PRIMARY KEY(optionid))';
        $q[] = 'CREATE TABLE reservations ('
             . 'hotelid INT NOT NULL,'
             . 'resid INT NOT NULL,'
             . 'rescode VARCHAR(255),'
             . 'memberid INT NOT NULL,'
             . 'planid INT NOT NULL,'
             . 'plan_name VARCHAR(255),'
             . 'roomtypeid INT NOT NULL,'
             . 'roomtype_name VARCHAR(255),'
             . 'room_num INT NOT NULL,'
             . 'plan_num INT NOT NULL,'
             . 'guest_num INT,'
             . 'guest_num2 INT,'
             . 'cin DATE NOT NULL,'
             . 'cin_hour TIME,'
             . 'cout DATE NOT NULL,'
             . 'stay_days INT NOT NULL,'
             . 'created DATETIME,'
             . 'modified DATETIME,'
             . 'total_price INT,'
             . 'status INT,'
             . 'memo VARCHAR(255),'
             . 'PRIMARY KEY(hotelid, resid))';
        // 予約詳細（宿泊者の客タイプ）
        $q[] = 'CREATE TABLE reservation_detail ('
             . 'hotelid INT NOT NULL,'
             . 'resid INT NOT NULL,'
             . 'day DATE NOT NULL,'
             . 'room_no INT NOT NULL,'
             . 'guesttypeid INT NOT NULL,'
             . 'guesttype_name VARCHAR(255),'
             . 'num INT NOT NULL,'
             . 'price INT NOT NULL,'
             . 'subtotal INT NOT NULL,'
             . 'daytypeid INT NOT NULL,'
             . 'daytype_name VARCHAR(255),'
             . 'created DATETIME,'
             . 'modified DATETIME)';
        // 予約者情報
        $q[] = 'CREATE TABLE reservation_member ('
             . 'hotelid INT NOT NULL,'
             . 'resid INT NOT NULL,'
             . '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 reservation_options ('
             . 'hotelid INT NOT NULL,'
             . 'resid INT NOT NULL,'
             . 'day DATE NOT NULL,'
             . 'optionid INT NOT NULL,'
             . 'option_name VARCHAR(255),'
             . 'price INT NOT NULL,'
             . 'subtotal INT NOT NULL,'
             . 'unitname VARCHAR(32),'
             . 'num INT NOT NULL)';
        
        // 変換テーブル
        $q[] = 'CREATE TABLE day_to_daytype ('
             . 'hotelid INT NOT NULL,'
             . 'day DATE,'
             . 'daytypeid INT NOT NULL,'
             . 'created DATETIME,'
             . 'PRIMARY KEY(hotelid, day))';
        $q[] = 'CREATE TABLE plan_prices ('
             . 'hotelid INT NOT NULL,'
             . 'planid INT NOT NULL,'
             . 'daytypeid INT NOT NULL,'
             . 'guesttypeid INT NOT NULL,'
             . 'price VARCHAR(255),'
             . 'created DATETIME,'
             . 'modified DATETIME)';
        $q[] = 'CREATE TABLE plan_to_guesttypes ('
             . 'hotelid INT NOT NULL,'
             . 'planid INT NOT NULL,'
             . 'guesttypeid INT NOT NULL,'
             . 'created DATETIME)';
        $q[] = 'CREATE TABLE plan_to_options ('
             . 'hotelid INT NOT NULL,'
             . 'planid INT NOT NULL,'
             . 'optionid INT NOT NULL,'
             . 'created DATETIME)';

        // 空き管理テーブル
        $q[] = 'CREATE TABLE vacant_rooms ('
             . 'hotelid INT NOT NULL,'
             . 'roomtypeid INT NOT NULL,'
             . 'day DATE NOT NULL,'
             . 'vr_num INT NOT NULL,'
             . 'vr_inuse INT NOT NULL)';
        $q[] = 'CREATE TABLE vacant_plans ('
             . 'hotelid INT NOT NULL,'
             . 'planid INT NOT NULL,'
             . 'day DATE NOT NULL,'
             . 'vp_num INT NOT NULL,'
             . 'vp_inuse INT NOT NULL)';
        $q[] = 'CREATE TABLE vacant_options ('
             . 'hotelid INT NOT NULL,'
             . 'optionid INT NOT NULL,'
             . 'day DATE NOT NULL,'
             . 'vo_num INT NOT NULL,'
             . 'vo_inuse INT NOT NULL)';
        
        $q[] = 'CREATE TABLE members ('
             . 'memberid INT NOT NULL AUTO_INCREMENT,'
             . '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(255),'
             . 'last_login DATETIME,'
             . 'hotelid INT,'
             . '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 TINYINT,'
             . '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 TINYINT,'
             . 'PRIMARY KEY(superid))';
        
        $q[] = 'CREATE TABLE hotel_seq ('
             . 'hotelid INT NOT NULL,'
             . 'seq_planid INT NOT NULL DEFAULT 0,'
             . 'seq_roomtypeid INT NOT NULL DEFAULT 0,'
             . 'seq_optionid INT NOT NULL DEFAULT 0,'
             . 'seq_resid INT NOT NULL DEFAULT 0,'
             . 'seq_daytypeid INT NOT NULL DEFAULT 0,'
             . 'seq_guesttypeid INT NOT NULL DEFAULT 0)';
        $q[] = 'CREATE TABLE global_seq ('
             . 'seq_hotelid INT NOT NULL DEFAULT 0,'
             . 'seq_memberid INT NOT NULL DEFAULT 0,'
             . 'seq_adminid INT NOT NULL DEFAULT 0,'
             . 'seq_superid INT NOT NULL DEFAULT 0)';

             
        // data
        // 1.2.4 >>>>
        $q[] = "INSERT INTO system (name, value) VALUES('ssl', 'false')";
        // 1.2.4 <<<<
        
        $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 global_seq(seq_hotelid, seq_adminid, seq_superid) VALUES (1, 1, 1)";
        
        foreach ($q as $v)
        {
            $this->db->query($v);
        }
        
        // サンプルホテル作成
        $q = array();
        $q[] = "INSERT INTO hotel_seq (hotelid) VALUES(1)";
        
        $q[] = "INSERT INTO hotels ("
             . "hotelid, hotel_name, res_prev_days, res_term) VALUES("
             . "1, 'test-hotel', 0, 6)";
        
        $q[] = "INSERT INTO daytypes ("
             . "hotelid, daytypeid, daytype_name, color, ordering, status) VALUES("
             . "1, 1, 'A', 'red', 0, 1)";
        
        $q[] = "INSERT INTO guesttypes ("
             . "hotelid, guesttypeid, guesttype_name, status) VALUES("
             . "1, 1, '大人', 1)";
        
        $q[] = "UPDATE global_seq SET seq_hotelid = 1";
        $q[] = "UPDATE hotel_seq SET seq_daytypeid = 1, seq_guesttypeid = 1 WHERE hotelid = 1";
        
        foreach ($q as $v)
        {
            $this->db->query($v);
        }
        
        $this->load->view('install/create_schema');
    }
    
}

/* End of file install.php */
