CREATE DATABASE IF NOT EXISTS masters;
use mysql;
INSERT INTO user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv) VALUES('localhost','nobody','','Y','Y','Y','Y','Y','Y','Y','N','N','N','Y','N','N','N');
FLUSH PRIVILEGES;
use masters;

CREATE TABLE IF NOT EXISTS appconf (
  gwap CHARACTER VARYING(10) NOT NULL,
  name CHARACTER VARYING(40) NOT NULL,
  enable CHARACTER VARYING(1) DEFAULT 'f',
  link CHARACTER VARYING(255),
  gazou CHARACTER VARYING(50),
  cngok CHARACTER VARYING(1) DEFAULT 'f',
  sort_order INTEGER,
  name_e CHARACTER VARYING(100),
  menu_category_id INTEGER,
  CONSTRAINT appconf_gwap PRIMARY KEY(gwap)
) type=innodb;
GRANT ALL ON masters.appconf TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS category (
  cat_cd CHARACTER VARYING(10) NOT NULL,
  cat_name CHARACTER VARYING(100) NOT NULL,
  enable CHARACTER VARYING(1) DEFAULT 'f',
  CONSTRAINT category_cat_cd PRIMARY KEY(cat_cd)
) type=innodb;
GRANT ALL ON masters.category TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS company (
  c_name CHARACTER VARYING(200) NOT NULL,
  ck_name CHARACTER VARYING(200),
  c_date DATE,
  capital INTEGER,
  homepage TEXT,
  email CHARACTER VARYING(100),
  boss CHARACTER VARYING(50)
) type=innodb;
GRANT ALL ON masters.company TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS division (
  division CHARACTER VARYING(10) NOT NULL,
  div_name CHARACTER VARYING(50) NOT NULL,
  CONSTRAINT division_division PRIMARY KEY(division)
) type=innodb;
GRANT ALL ON masters.division TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS facilities (
  fac_cd CHARACTER VARYING(10) NOT NULL,
  fac_name CHARACTER VARYING(100) NOT NULL,
  enable CHARACTER VARYING(1) DEFAULT 'f',
  fac_cat_cd character varying(10),
  CONSTRAINT facilities_fac_cd PRIMARY KEY(fac_cd)
) type=innodb;
GRANT ALL ON masters.facilities TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS holiday (
  hdate DATE NOT NULL,
  hname CHARACTER VARYING(100) NOT NULL,
  flgorgin CHARACTER VARYING(1) DEFAULT 'f'
) type=innodb;
GRANT ALL ON masters.holiday TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS mst_touban (
  touban_cd CHARACTER VARYING(10) NOT NULL,
  touban_name CHARACTER VARYING(100) NOT NULL,
  memo CHARACTER VARYING(255),
  CONSTRAINT mst_touban_touban_cd PRIMARY KEY(touban_cd)
) type=innodb;
GRANT ALL ON masters.mst_touban TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS touban (
  touban_cd CHARACTER VARYING(10) NOT NULL,
  touban_date DATE NOT NULL,
  emp_cd TEXT NOT NULL,
  CONSTRAINT PRIMARY KEY(touban_cd,touban_date),
  CONSTRAINT touban_touban_cd FOREIGN KEY(touban_cd) REFERENCES mst_touban(touban_cd)
) type=innodb;
GRANT ALL ON masters.touban TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS mst_type (
  kind CHARACTER VARYING(10) NOT NULL,
  t_name CHARACTER VARYING(20) NOT NULL,
  CONSTRAINT mst_type_type PRIMARY KEY(kind)
) type=innodb;
GRANT ALL ON masters.mst_type TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS mst_wflow (
  petition_cd CHARACTER VARYING(10) NOT NULL,
  petition_name CHARACTER VARYING(200) NOT NULL,
  petition_type CHARACTER VARYING(10) NOT NULL,
  send_end_mail VARCHAR(1) DEFAULT 't' NOT NULL,
  send_sanction_mail VARCHAR(1) DEFAULT 't' NOT NULL,
  send_remand_mail VARCHAR(1) DEFAULT 't' NOT NULL,
  send_withdrawal_mail VARCHAR(1) DEFAULT 't' NOT NULL,
  CONSTRAINT mst_wflow_petition_cd PRIMARY KEY(petition_cd)
) type=innodb;
GRANT ALL ON masters.mst_wflow TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS employee (
  emp_cd CHARACTER VARYING(50) NOT NULL,
  emp_password CHARACTER VARYING(50) NOT NULL,
  last_name_j CHARACTER VARYING(10) NOT NULL,
  first_name_j CHARACTER VARYING(10) NOT NULL,
  last_name_e CHARACTER VARYING(20) NOT NULL,
  first_name_e CHARACTER VARYING(20) NOT NULL,
  last_name_kana CHARACTER VARYING(20) NOT NULL,
  first_name_kana CHARACTER VARYING(20) NOT NULL,
  sex CHARACTER VARYING(1),
  kind CHARACTER VARYING(10),
  division CHARACTER VARYING(10),
  birthday DATE,
  join_date DATE,
  retire_date DATE,
  extension_no CHARACTER VARYING(10),
  home_postal CHARACTER(7),
  home_addr1 CHARACTER VARYING(100),
  home_addr2 CHARACTER VARYING(100),
  home_addr3 CHARACTER VARYING(100),
  home_phone CHARACTER VARYING(20),
  handy_phone CHARACTER VARYING(20),
  email CHARACTER VARYING(50),
  contact CHARACTER VARYING(100),
  sort_no CHARACTER VARYING(10) NOT NULL,
  unify CHARACTER VARYING(1) DEFAULT 'f',
  INDEX kind_ind(kind),
  INDEX division_ind(division),
  CONSTRAINT employee_emp_cd PRIMARY KEY(emp_cd),
  CONSTRAINT employee_division FOREIGN KEY(division) REFERENCES division(division),
  CONSTRAINT employee_kind FOREIGN KEY(kind) REFERENCES mst_type(kind)
) type=innodb;
GRANT ALL ON masters.employee TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS administrators (
  emp_cd CHARACTER VARYING(50) NOT NULL,
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT administrators_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.administrators TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS casus (
  petition_time DATETIME NOT NULL,
  casus TEXT,
  emp_cd CHARACTER VARYING(50) NOT NULL,
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT casus_petition_time PRIMARY KEY(petition_time),
  CONSTRAINT casus_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.casus TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS chief (
  emp_cd CHARACTER VARYING(50) NOT NULL,
  title CHARACTER VARYING(255),
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT chief_emp_cd PRIMARY KEY(emp_cd),
  CONSTRAINT chief_emp_cd_foreign FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.chief TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS chiefemp (
  chief CHARACTER VARYING(50) NOT NULL,
  emp_cd CHARACTER VARYING(50) NOT NULL,
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT chiefemp_chief_emp_cd PRIMARY KEY(chief,emp_cd),
  CONSTRAINT chiefemp_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.chiefemp TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS gwlog (
  record_time DATETIME NOT NULL,
  emp_cd CHARACTER VARYING(50)  NOT NULL,
  gwap CHARACTER VARYING(10),
  access CHARACTER VARYING(15),
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT gwlog_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.gwlog TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS monsche (
  sche_date DATE NOT NULL,
  seq INTEGER NOT NULL,
  tantou CHARACTER VARYING(50),
  schedule TEXT,
  division CHARACTER VARYING(10),
  INDEX division_ind(division),
  CONSTRAINT monsche_division FOREIGN KEY(division) REFERENCES division(division)
) type=innodb;
GRANT ALL ON masters.monsche TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS nextodd (
  petition_time DATETIME NOT NULL,
  emp_cd CHARACTER VARYING(50) NOT NULL,
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT nextodd_petition_time PRIMARY KEY(petition_time),
  CONSTRAINT nextodd_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.nextodd TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS oddmans (
  petition_time DATETIME NOT NULL,
  petition_cd CHARACTER VARYING(10),
  emp_cd CHARACTER VARYING(50) NOT NULL,
  seq INTEGER NOT NULL,
  INDEX petition_cd_ind(petition_cd),
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT oddmans_petition_time_emp_cd PRIMARY KEY(petition_time,emp_cd),
  CONSTRAINT oddmans_petition_cd FOREIGN KEY(petition_cd) REFERENCES mst_wflow(petition_cd),
  CONSTRAINT oddmans_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.oddmans TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS subcategory (
  subcat_cd VARCHAR(10) PRIMARY KEY,
  subcat_name VARCHAR(100) NOT NULL,
  enable CHARACTER VARYING(1),
  cat_cd VARCHAR(10) NOT NULL
) type=innodb;
GRANT ALL ON masters.subcategory TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS record (
  filename TEXT NOT NULL,
  cat_cd VARCHAR(10) NOT NULL,
  subcat_cd VARCHAR(10) NOT NULL,
  remark TEXT,
  emp_cd VARCHAR(50) NOT NULL,
  up_time DATETIME NOT NULL,
  filesize INTEGER,
  INDEX cat_cd_ind(cat_cd),
  INDEX subcat_cd_ind(subcat_cd),
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT record_cat_cd FOREIGN KEY(cat_cd) REFERENCES category(cat_cd),
  CONSTRAINT record_subcat_cd FOREIGN KEY(subcat_cd) REFERENCES subcategory(subcat_cd),
  CONSTRAINT record_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.record TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS reservation (
  rsv_date DATE  NOT NULL,
  rsv_time_b TIME  NOT NULL,
  rsv_time_e TIME  NOT NULL,
  fac_cd CHARACTER VARYING(10) NOT NULL,
  emp_cd CHARACTER VARYING(50) NOT NULL,
  ac_date DATETIME DEFAULT 'now',
  purpose CHARACTER VARYING(50),
  resume TEXT,
  INDEX fac_cd_ind(fac_cd),
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT reservation_fac_cd FOREIGN KEY(fac_cd) REFERENCES facilities(fac_cd),
  CONSTRAINT reservation_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.reservation TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS schedule2 (
  emp_cd CHARACTER VARYING(50) NOT NULL,
  scdl_s_date DATE NOT NULL,
  scdl_e_date DATE DEFAULT NULL,
  scdl_s_time TIME DEFAULT NULL,
  scdl_e_time TIME DEFAULT NULL,
  scdl_plan TEXT NOT NULL,
  scdl_place CHARACTER VARYING(128),
  scdl_remarks TEXT,
  flg_del CHARACTER VARYING(1) DEFAULT 'f',
  flg_pub CHARACTER VARYING(1) DEFAULT 't',
  schedule_cd INTEGER NOT NULL,
  continuous INTEGER,
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT schedule_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.schedule2 TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS transactions (
  emp_cd CHARACTER VARYING(50) NOT NULL,
  trans_id TEXT NOT NULL,
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT transactions_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.transactions TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS whiteboard (
  emp_cd CHARACTER VARYING(50) NOT NULL,
  place CHARACTER VARYING(1) DEFAULT '0',
  ins_time DATETIME,
  destination TEXT,
  back_time TIME,
  write_emp CHARACTER VARYING(50) ,
  INDEX emp_cd_ind(emp_cd),
  INDEX write_emp_cd_ind(write_emp),
  CONSTRAINT whiteboard_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd),
  CONSTRAINT whiteboard_write_emp FOREIGN KEY(write_emp) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.whiteboard TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS workflow (
  petition_time DATETIME NOT NULL,
  emp_cd CHARACTER VARYING(50) NOT NULL,
  petition_cd CHARACTER VARYING(6) NOT NULL,
  oddtime TEXT,
  oddall CHARACTER VARYING(1) DEFAULT 'f',
  time_begin DATETIME,
  time_end DATETIME,
  purpose TEXT,
  detail TEXT,
  etc TEXT,
  ammount INTEGER,
  ngflg CHARACTER VARYING(1) DEFAULT 'f',
  endflg CHARACTER VARYING(1) DEFAULT 'f',
  remark TEXT,
  filename TEXT,
  delflg CHARACTER VARYING(1) DEFAULT 'f',
  INDEX emp_cd_ind(emp_cd),
  INDEX petition_cd_ind(petition_cd),
  CONSTRAINT workflow_petition_time PRIMARY KEY(petition_time),
  CONSTRAINT workflow_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd),
  CONSTRAINT workflow_petition_cd FOREIGN KEY(petition_cd) REFERENCES mst_wflow(petition_cd)
) type=innodb;
GRANT ALL ON masters.workflow TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS telmemo2 (
  regist_time DATETIME NOT NULL,
  emp_cd CHARACTER VARYING(50) NOT NULL,
  purpose INTEGER NOT NULL,
  memo TEXT NOT NULL,
  writer CHARACTER VARYING(50) NOT NULL,
  del CHARACTER VARYING(1),
  INDEX emp_cd_ind(emp_cd),
  INDEX writer_cd_ind(writer),
  CONSTRAINT telmemo2_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd),
  CONSTRAINT telmemo2_writer FOREIGN KEY(writer) REFERENCES employee(emp_cd) 
) type=innodb;
GRANT ALL ON masters.telmemo2 TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS notice (
  sendtime DATETIME NOT NULL,
  emp_cd CHARACTER VARYING(50) NOT NULL,
  title CHARACTER VARYING(50),
  body TEXT,
  confirm CHARACTER VARYING(1),
  INDEX emp_cd_ind(emp_cd),
  CONSTRAINT notice_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd)
) type=innodb;
GRANT ALL ON masters.notice TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS pnotice (
  sendtime DATETIME NOT NULL,
  emp_cd CHARACTER VARYING(50) NOT NULL,
  seentime DATETIME,
  reply CHARACTER VARYING(1),
  remark TEXT,
  INDEX emp_cd_ind(emp_cd),
  PRIMARY KEY(sendtime,emp_cd),
  CONSTRAINT pnotice_emp_cd FOREIGN KEY(emp_cd) REFERENCES employee(emp_cd) 
) type=innodb;
GRANT ALL ON masters.pnotice TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS torihikisaki (
  toricode character varying(10) PRIMARY KEY,
  input_emp_cd CHARACTER VARYING(50),
  syamei CHARACTER VARYING(50),
  shihon INTEGER,
  bank TEXT,
  gyomu TEXT,
  last_name_j CHARACTER VARYING(10),
  first_name_j CHARACTER VARYING(10),
  last_name_e CHARACTER VARYING(20),
  first_name_e CHARACTER VARYING(20),
  email CHARACTER VARYING(50),
  web CHARACTER VARYING(100)
) type=innodb;
GRANT ALL ON masters.torihikisaki TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS tantou (
  seq CHARACTER VARYING(10) NOT NULL,
  toricode CHARACTER VARYING(10),
  last_name_j CHARACTER VARYING(10),
  first_name_j CHARACTER VARYING(10),
  last_name_e CHARACTER VARYING(20),
  first_name_e CHARACTER VARYING(20),
  sex CHARACTER VARYING(1),
  busyo CHARACTER VARYING(100),
  yaku CHARACTER VARYING(100),
  phone CHARACTER VARYING(20),
  fax CHARACTER VARYING(20),
  handy CHARACTER VARYING(20),
  email CHARACTER VARYING(50),
  gyomu CHARACTER VARYING(255),
  memo CHARACTER VARYING(255),
  bashocode character varying(10),
  input_emp_cd character varying(50)
) type=innodb;
GRANT ALL ON masters.tantou TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE tori_place (
  toricode character varying(10) NOT NULL,
  bashocode character varying(10) NOT NULL,
  input_emp_cd CHARACTER VARYING(50),
  bashomei CHARACTER VARYING(50),
  postal CHARACTER VARYING(7),
  address TEXT,
  tel CHARACTER VARYING(20),
  fax CHARACTER VARYING(20)
) TYPE=INNODB;
GRANT ALL ON masters.tori_place TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE record_access (
    up_time DATETIME NOT NULL,
    group_cd INTEGER NOT NULL,
    readable_status CHARACTER VARYING(1) DEFAULT 'F'
) TYPE=INNODB;
GRANT ALL ON masters.record_access TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE group_relation (
    group_cd INTEGER NOT NULL,
    emp_cd CHARACTER VARYING(50) NOT NULL
) TYPE=INNODB;
GRANT ALL ON masters.group_relation TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE group_master (
    group_cd INTEGER NOT NULL,
    group_name CHARACTER VARYING(50) NOT NULL,
    owner_emp_cd CHARACTER VARYING(50) NOT NULL
) TYPE=INNODB;
GRANT ALL ON masters.group_master TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE IF NOT EXISTS serialnumber (
  name CHARACTER VARYING(50) NOT NULL PRIMARY KEY,
  code int4
) type=innodb;
GRANT ALL ON masters.serialnumber TO nobody;
FLUSH PRIVILEGES;


CREATE TABLE forums (
  id int unsigned DEFAULT 0 NOT NULL AUTO_INCREMENT,
  name char(50) DEFAULT '' NOT NULL,
  active smallint DEFAULT 0 NOT NULL,
  description char(255) DEFAULT '' NOT NULL,
  config_suffix char(50) DEFAULT '' NOT NULL,
  folder char(1) DEFAULT '0' NOT NULL,
  parent int unsigned DEFAULT 0 NOT NULL,
  display int unsigned DEFAULT 0 NOT NULL,
  table_name char(50) DEFAULT '' NOT NULL,
  moderation char(1) DEFAULT 'n' NOT NULL,
  email_list char(50) DEFAULT '' NOT NULL,
  email_return char(50) DEFAULT '' NOT NULL,
  email_tag char(50) DEFAULT '' NOT NULL,
  check_dup smallint unsigned DEFAULT 0 NOT NULL,
  multi_level smallint unsigned DEFAULT 0 NOT NULL,
  collapse smallint unsigned DEFAULT 0 NOT NULL,
  flat smallint unsigned DEFAULT 0 NOT NULL,
  lang char(50) DEFAULT '' NOT NULL,
  html char(40) DEFAULT 'N' NOT NULL,
  table_width char(4) DEFAULT '' NOT NULL,
  table_header_color char(7) DEFAULT '' NOT NULL,
  table_header_font_color char(7) DEFAULT '' NOT NULL,
  table_body_color_1 char(7) DEFAULT '' NOT NULL,
  table_body_color_2 char(7) DEFAULT '' NOT NULL,
  table_body_font_color_1 char(7) DEFAULT '' NOT NULL,
  table_body_font_color_2 char(7) DEFAULT '' NOT NULL,
  nav_color char(7) DEFAULT '' NOT NULL,
  nav_font_color char(7) DEFAULT '' NOT NULL,
  allow_uploads char(1) DEFAULT 'N' NOT NULL,
  upload_types char(100) DEFAULT '' NOT NULL,
  upload_size int unsigned DEFAULT '0' NOT NULL,
  max_uploads int unsigned DEFAULT '0' NOT NULL,
  security int unsigned DEFAULT '0' NOT NULL,
  showip smallint unsigned DEFAULT 1 NOT NULL,
  emailnotification smallint unsigned DEFAULT 1 NOT NULL,
  body_color char(7) DEFAULT '' NOT NULL,
  body_link_color char(7) DEFAULT '' NOT NULL,
  body_alink_color char(7) DEFAULT '' NOT NULL,
  body_vlink_color char(7) DEFAULT '' NOT NULL,
  PRIMARY KEY (id),
  KEY (name),
  KEY (active),
  KEY (parent),
  key (security)
) type=innodb;

GRANT ALL ON masters.forums TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE mst_menu_category (
  menu_category_id integer NOT NULL,
  menu_category_name varchar(50),
  menu_category_image varchar(50),
  menu_category_order integer,
  CONSTRAINT appconf_menu_category_id PRIMARY KEY(menu_category_id)
) type=innodb;

GRANT ALL ON masters.mst_menu_category TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE facility_category (
  fac_cat_cd character varying(10) NOT NULL,
  fac_cat_name character varying(100) NOT NULL,
  enable CHARACTER VARYING(1) DEFAULT 't' NOT NULL,
  CONSTRAINT facility_category_fac_cat_cd PRIMARY KEY(fac_cat_cd)
) type=innodb;

GRANT ALL ON masters.facility_category TO nobody;
FLUSH PRIVILEGES;

CREATE TABLE group_access (
	cat_cd	character varying(10) NOT NULL,
	subcat_cd	character varying(10) NOT NULL,
	group_cd	character varying(10) NOT NULL,
	writeable	character varying(1)  DEFAULT 't' NOT NULL
) type=innodb;

GRANT ALL ON masters.group_access TO nobody;
FLUSH PRIVILEGES;

ALTER TABLE group_master ADD CONSTRAINT group_master_group_cd PRIMARY KEY(group_cd);
ALTER TABLE group_master ADD COLUMN public_flg character varying(1) DEFAULT 't' NOT NULL;
ALTER TABLE group_master ALTER COLUMN public_flg SET DEFAULT 'f';

#INSERT INTO APPCONF
INSERT INTO appconf VALUES('mygroup','グループ設定','t','emp_group.php','maintenance_title.gif','f',2,'myGroup',8);
INSERT INTO appconf VALUES('home','HOME','t','index.php','index_title.gif','t',0,'HOME',1);
INSERT INTO appconf VALUES('conf','設定','t','confmenu.php','maintenance_title.gif','t',0,'',8);
INSERT INTO appconf VALUES('p_info','個人情報閲覧','t','phonebook.php','personal_readtitle.gif','f',12,'Colleagues Information',4);
INSERT INTO appconf VALUES('com_event','会社行事・予定','t','monsche.php','com_event_title.gif','f',6,'Schedules of Business',1);
INSERT INTO appconf VALUES('telmemo','伝言メモ','t','telmemo.php','message_title.gif','f',3,'Message Notes',2);
INSERT INTO appconf VALUES('reserve','施設予約','t','reserve.php','institution_title.gif','f',7,'Bookings of Facilities',5);
INSERT INTO appconf VALUES('clnt','取引先・担当者情報','t','client.php','customer_title.gif','f',45,'Information of Clients',4);
INSERT INTO appconf VALUES('empedit','個人情報編集','t','emp.php','maintenance_title.gif','f',13,'Self-information',8);
INSERT INTO appconf VALUES('passwd','パスワード変更','t','passwd.php','pass_title.gif','f',70,'Password Change',6);
INSERT INTO appconf VALUES('workflow','ワークフロー','t','workflow.php','workflow_title.gif','f',60,'Applications',3);
INSERT INTO appconf VALUES('flowpetit','決裁','t','flowpetit.php','settlement_title.gif','f',65,'Decisions',3);
INSERT INTO appconf VALUES('bbs','掲示板','t','bbs/index.php','bbs_title.gif','f',5,'BBS',4);
INSERT INTO appconf VALUES('rota','当番表','t','rota.php','duty_title.gif','f',8,'Turns of Duty',1);
INSERT INTO appconf VALUES('scdl','スケジュール帳','t','schedule2.php','schedule_title.gif','f',2,'Personal Schedule',1);
INSERT INTO appconf VALUES('notice','回覧板','t','notice1.php','circulation_title.gif','f',6,'Circular Board',1);
INSERT INTO appconf VALUES('document','ファイル共有','t','record.php','document_title.gif','f',79,'Library',4);
INSERT INTO appconf VALUES('white','行先ボード','t','whiteboard1.php','destination_title.gif','f',1,'Whiteboard System',2);


#INSERT into CATEGORY
INSERT INTO category VALUES(1,'議事録','t');


#INSERT INTO COMPANY
INSERT INTO company VALUES('WIZ株式会社','WIZ株式会社','1900-01-01',1,'http://localhost/','wiz@wiz','WIZ一郎');

#INSERT into DIVISION
INSERT INTO division VALUES(1,'総務部');

#INSERT into FACILITIES
INSERT INTO facilities VALUES('1','会議室','t', '1');

#INSERT INTO HOLIDAY
INSERT INTO holiday VALUES('2004-01-01','元日','f');
INSERT INTO holiday VALUES('2004-01-12','成人の日','f');
INSERT INTO holiday VALUES('2004-02-11','建国記念の日','f');
INSERT INTO holiday VALUES('2004-03-20','春分の日','f');
INSERT INTO holiday VALUES('2004-04-29','みどりの日','f');
INSERT INTO holiday VALUES('2004-05-03','憲法記念日','f');
INSERT INTO holiday VALUES('2004-05-04','国民の祝日','f');
INSERT INTO holiday VALUES('2004-05-05','こどもの日','f');
INSERT INTO holiday VALUES('2004-07-19','海の日','f');
INSERT INTO holiday VALUES('2004-09-20','敬老の日','f');
INSERT INTO holiday VALUES('2004-09-23','秋分の日','f');
INSERT INTO holiday VALUES('2004-10-11','体育の日','f');
INSERT INTO holiday VALUES('2004-11-03','文化の日','f');
INSERT INTO holiday VALUES('2004-11-23','勤労感謝の日','f');
INSERT INTO holiday VALUES('2004-12-23','天皇誕生日','f');

#INSERT into MST_TOUBAN
INSERT INTO mst_touban VALUES('1','ゴミ当番','');

#INSERT into MST_TYPE
INSERT INTO mst_type VALUES(1,'正社員');

#INSERT into EMPLOYEE
INSERT INTO employee VALUES('admin','uit0tkg6JSA0s','初期','管理者','Initial','Administrator','しょき','かんりしゃ','t','1',1,NULL,NULL,NULL,'1','','','','','','','','',1,'f');

#INSERT INTO ADMINISTRATORS
INSERT INTO administrators VALUES('admin');

#INSERT into SUBCATEGORY
INSERT INTO subcategory VALUES(1,'人事','t','1');

#INSERT INTO WHITEBOARD
INSERT INTO whiteboard VALUES('admin',0,'2001-06-27 09:01:29+09','いつもの場所','','admin');

#INSERT INTO CHIEF
INSERT INTO chief VALUES('admin','');

#INSERT INTO CHIEFEMP

#INSERT INTO serialnumber
INSERT INTO serialnumber VALUES('mst_type',2);
INSERT INTO serialnumber VALUES('division',2);
INSERT INTO serialnumber VALUES('mst_touban',2);
INSERT INTO serialnumber VALUES('facilities',2);
INSERT INTO serialnumber VALUES('subcategory',2);
INSERT INTO serialnumber VALUES('category',2);
INSERT INTO serialnumber VALUES('menu_category',9);

INSERT INTO mst_menu_category VALUES(1,'やることチェック','menu1.gif',1);
INSERT INTO mst_menu_category VALUES(2,'出かける時・戻った時','menu2.gif',2);
INSERT INTO mst_menu_category VALUES(3,'判断を仰ぐ時','menu3.gif',3);
INSERT INTO mst_menu_category VALUES(4,'みんなの情報','menu4.gif',4);
INSERT INTO mst_menu_category VALUES(5,'資源を使おう','menu5.gif',5);
INSERT INTO mst_menu_category VALUES(6,'セキュリティは大切','menu6.gif',6);
INSERT INTO mst_menu_category VALUES(7,'その他','menu7.gif',7);
INSERT INTO mst_menu_category VALUES(8,'設定','menu8.gif',8);

#INSERT INTO facility_category
INSERT INTO facility_category (fac_cat_cd, fac_cat_name, enable) VALUES('1', 'Default', 't');

#UPDATE group_master
UPDATE group_master SET public_flg = 'f';