---------------------------------------------------------------------------------------------
-- アカウント管理ポートレット
---------------------------------------------------------------------------------------------

-- 部署テーブルへ親部署を追加
ALTER TABLE eip_m_post ADD COLUMN parent_post_id integer DEFAULT 0 NOT NULL;

-- 部署テーブルへ部署コードを追加
ALTER TABLE eip_m_post ADD COLUMN post_code character varying(20);

-- 役職テーブルへ役職コードを追加
ALTER TABLE eip_m_position ADD COLUMN position_code character varying(20);

-- ユーザーグループロールテーブルへ役職IDを追加
ALTER TABLE TURBINE_USER_GROUP_ROLE ADD COLUMN position_id integer;

-- ユーザー情報テーブルへスケジュール移行済みフラグを追加
ALTER TABLE TURBINE_USER ADD COLUMN schedule_migrate character(1);

-- ユーザー情報テーブルへアドレス移行済みフラグを追加
ALTER TABLE TURBINE_USER ADD COLUMN address_migrate character(1);

-- ユーザー情報テーブルへ職種を追加
ALTER TABLE TURBINE_USER ADD COLUMN category character(99);

-- ユーザー情報テーブルへ表示名を追加
ALTER TABLE TURBINE_USER ADD COLUMN display_name character(99);

-- ユーザー情報テーブルの移行フラグ列にデフォルト値を設定
ALTER TABLE turbine_user
   ALTER COLUMN schedule_migrate SET DEFAULT '0';

ALTER TABLE turbine_user
   ALTER COLUMN address_migrate SET DEFAULT '0';

-------------------------------
-- Table: avz_m_position_position　役職順番制御テーブル

-- DROP TABLE avz_m_position_position; 

CREATE TABLE avz_m_position_position
(
  id integer NOT NULL,
  position_id integer,
  "position" integer,
  CONSTRAINT avz_m_position_position_pkey PRIMARY KEY (id),
  CONSTRAINT avz_m_position_position_id_fkey FOREIGN KEY (position_id)
      REFERENCES eip_m_position (position_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE avz_m_position_position OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_m_position_position
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_m_position_position OWNER TO aipo_postgres;

-------------------------------
-- Table: avz_m_post_position　部署順番制御テーブル

-- DROP TABLE avz_m_post_position;

CREATE TABLE avz_m_post_position
(
  id integer NOT NULL,
  post_id integer,
  "position" integer,
  CONSTRAINT avz_m_post_position_pkey PRIMARY KEY (id),
  CONSTRAINT avz_m_post_position_id_fkey FOREIGN KEY (post_id)
      REFERENCES eip_m_post (post_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE avz_m_post_position OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_m_post_position
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_m_post_position OWNER TO aipo_postgres;

------------------------------------------------------------
-- Table: avz_m_role 役割テーブル

-- DROP TABLE avz_m_role;

CREATE TABLE avz_m_role
(
  role_id integer NOT NULL,
  role_name character varying(64) NOT NULL,
  role_code character varying(10),
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_m_role_pkey PRIMARY KEY (role_id)
)
WITH (OIDS=FALSE);
ALTER TABLE avz_m_role OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_m_role
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_m_role OWNER TO aipo_postgres;

------------------------------------------------------------
-- Table: eip_m_user_position ユーザー役割情報

-- DROP TABLE eip_m_user_position;

CREATE TABLE avz_m_user_role
(
  id integer NOT NULL,
  user_id integer,
  role_id integer,
  CONSTRAINT avz_m_user_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES avz_m_role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT avz_m_user_role_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE avz_m_user_role OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_m_user_role
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_m_user_role OWNER TO aipo_postgres;

------------------------------------------------------------
-- Table: avz_m_groupsend グループ送信情報

-- DROP TABLE avz_m_groupsend;

CREATE TABLE avz_m_groupsend
(
  groupsend_id integer NOT NULL,
  groupsend_name character varying(64) NOT NULL,
  groupsend_code character varying(10),
  category character(1),
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_m_groupsend_pkey PRIMARY KEY (groupsend_id)
)
WITH (OIDS=FALSE);
ALTER TABLE avz_m_groupsend OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_m_groupsend
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_m_groupsend OWNER TO aipo_postgres;

------------------------------------------------------------
-- Table: avz_m_user_groupsend ユーザーグループ送信情報

-- DROP TABLE avz_m_user_groupsend;

CREATE TABLE avz_m_user_groupsend
(
  id integer NOT NULL,
  user_id integer,
  groupsend_id integer,
  CONSTRAINT avz_m_user_groupsend_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE avz_m_user_groupsend OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_m_user_groupsend
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_m_user_groupsend OWNER TO aipo_postgres;

-------------------------------
-- Table: avz_m_groupsend_position　グループ送信順番制御テーブル

-- DROP TABLE avz_m_groupsend_position;

CREATE TABLE avz_m_groupsend_position
(
  id integer NOT NULL,
  groupsend_id integer,
  "position" integer,
  CONSTRAINT avz_m_groupsend_position_pkey PRIMARY KEY (id),
  CONSTRAINT avz_m_groupsend_position_id_fkey FOREIGN KEY (groupsend_id)
      REFERENCES avz_m_groupsend (groupsend_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE avz_m_groupsend_position OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_m_groupsend_position
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_m_groupsend_position OWNER TO aipo_postgres;

---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
--アドレス帳ポートレット
---------------------------------------------------------------------------------------------

-- 個人アドレス帳テーブルへFAX番号を追加
ALTER TABLE eip_m_addressbook ADD COLUMN fax_number character varying(99);

-- 個人アドレス帳テーブルの電話番号、携帯番号の桁数を変更
ALTER TABLE eip_m_addressbook ALTER telephone TYPE character varying(99);
ALTER TABLE eip_m_addressbook ALTER cellular_phone TYPE character varying(99);

---------------------------------------------------------------------------------------------
--カレンダーポートレット
---------------------------------------------------------------------------------------------

-- スケジュールマップテーブルへ必須：R／任意：S(selfish)フラグ を追加
ALTER TABLE eip_t_schedule_map ADD COLUMN required character(1);

-- スケジュールマップテーブルへ重要フラグ 重要：T、通常：F を追加
ALTER TABLE eip_t_schedule_map ADD COLUMN priority character(1);

-- スケジュールマップテーブルへダミー未回答フラグ ダミー未回答：T、通常：F を追加
ALTER TABLE eip_t_schedule_map ADD COLUMN dummy_non_response character(1);

-- スケジュールテーブルへ繰り返し回数を追加
ALTER TABLE eip_t_schedule ADD COLUMN repeat_num integer;

-- スケジュールテーブルの「繰り返しパターン」の桁数を10⇒16に変更
ALTER TABLE eip_t_schedule ALTER REPEAT_PATTERN TYPE VARCHAR (16);

-- スケジュール制御
-- Table: avz_t_schedule_acl
-- DROP TABLE avz_t_schedule_acl;

CREATE TABLE avz_t_schedule_acl
(
  acl_id integer NOT NULL,
  user_id integer NOT NULL,
  acl_type character(1) NOT NULL,
  target_type character(1) NOT NULL,
  target_id integer NOT NULL,
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_t_schedule_acl_pkey PRIMARY KEY (acl_id)
)
WITHOUT OIDS;
ALTER TABLE avz_t_schedule_acl OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_t_schedule_acl
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_t_schedule_acl OWNER TO aipo_postgres;

---------------------------------------------------------------------------------------------
--Webメールポートレット
---------------------------------------------------------------------------------------------
-- メールアカウントテーブルへ、「現在メール容量」を追加
ALTER TABLE EIP_M_MAIL_ACCOUNT ADD COLUMN CURRENT_MAIL_VOLUME integer DEFAULT 0;

-- メールアカウントテーブルへ、「送信トレイフォルダID」を追加
ALTER TABLE EIP_M_MAIL_ACCOUNT ADD COLUMN DEFAULT_SEND_FOLDER_ID integer;

-- メールアカウントテーブルへ、「アカウント利用ユーザー種別」を追加
ALTER TABLE EIP_M_MAIL_ACCOUNT ADD ACCOUNT_USAGE varchar(1) default '0';

-- メールフォルダテーブルへ、「親フォルダＩＤ」を追加
ALTER TABLE eip_t_mail_folder ADD COLUMN parent_folder_id integer DEFAULT 0;

-- メールフォルダテーブルへ、「フォルダ容量」を追加
ALTER TABLE eip_t_mail_folder ADD COLUMN folder_volume bigint DEFAULT 0;

-- メールフォルダテーブルへ、「フォルダタイプ」を追加
ALTER TABLE eip_t_mail_folder ADD COLUMN folder_type character(1);

-- メールフォルダテーブルへ、「フォルダ種別」を追加
ALTER TABLE eip_t_mail_folder ADD COLUMN folder_kind character(1);

-- Webメール受信テーブルへ検索用列追加の変更
ALTER TABLE eip_t_mail ADD COLUMN text_for_search text;
ALTER TABLE eip_t_mail ADD COLUMN subject_for_search text;
ALTER TABLE eip_t_mail ADD COLUMN address_for_search text;

-- Webメール受信テーブルへ返信転送区分列追加の変更
ALTER TABLE EIP_T_MAIL ADD COLUMN REPLY_FORWARD character(1);

-- Webメール受信テーブルへ開封確認送信済フラグ列追加の変更
ALTER TABLE EIP_T_MAIL ADD COLUMN NOTIFICATION_FLG character(1);

-- Webメール受信テーブルの作成日のデータ型をDATE⇒TIMESTAMPに変更
ALTER TABLE EIP_T_MAIL ALTER CREATE_DATE TYPE TIMESTAMP;

-- Table: avz_t_mail_send

-- DROP TABLE avz_t_mail_send;

CREATE TABLE avz_t_mail_send
(
  mail_id integer NOT NULL,
  user_id integer NOT NULL,
  account_id integer NOT NULL,
  folder_id integer NOT NULL,
  "type" character(1),
  read_flg character(1),
  subject text,
  person text,
  event_date timestamp without time zone,
  file_volume integer,
  has_files character(1),
  file_path text,
  mail bytea,
  create_date timestamp without time zone,
  update_date timestamp without time zone,
  text_for_search text,
  subject_for_search text,
  address_for_search text,
  reply_forward character(1),
  CONSTRAINT avz_t_mail_send_pkey PRIMARY KEY (mail_id)
)
WITH (OIDS=FALSE);
ALTER TABLE avz_t_mail_send OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_t_mail_send
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_t_mail_send OWNER TO aipo_postgres;

-- Table: avz_t_mail_send_recv_acl 代理送受信制御テーブル
-- DROP TABLE avz_t_mail_send_recv_acl;

CREATE TABLE avz_t_mail_send_recv_acl
(
  acl_id integer NOT NULL,
  account_id integer NOT NULL,
  acl_type character(1) NOT NULL,
  target_type character varying(99) NOT NULL,
  target_id integer NOT NULL,
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_t_mail_send_recv_acl_pkey PRIMARY KEY (acl_id)
)
WITHOUT OIDS;
ALTER TABLE avz_t_mail_send_recv_acl OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_t_mail_send_recv_acl
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_t_mail_send_recv_acl OWNER TO aipo_postgres;


-- Table: avz_t_mail_batch Webメール一括処理
-- DROP TABLE avz_t_mail_batch;

CREATE TABLE avz_t_mail_batch
(
  id integer NOT NULL,
  user_id integer NOT NULL,
  account_id integer NOT NULL,
  type character(1) NOT NULL,
  status character(1) NOT NULL,
  from_date date,
  to_date date,
  folder_id integer NOT NULL,
  folder_type character(1) NOT NULL,
  file_name character varying(64),
  to_folder_id integer,
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_t_mail_batch_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE avz_t_mail_batch OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_t_mail_batch
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_t_mail_batch OWNER TO aipo_postgres;


-- Table: avz_t_schedule_file スケジュール添付ファイル

-- DROP TABLE avz_t_schedule_file;

CREATE TABLE avz_t_schedule_file
(
  file_id integer NOT NULL,
  owner_id integer,
  schedule_id integer,
  file_name character varying(128) NOT NULL,
  file_path text NOT NULL,
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_t_schedule_file_pkey PRIMARY KEY (file_id),
  CONSTRAINT avz_t_schedule_file_schedule_id_fkey FOREIGN KEY (schedule_id)
      REFERENCES eip_t_schedule (schedule_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (OIDS=FALSE);
ALTER TABLE avz_t_schedule_file OWNER TO aipo_postgres;

CREATE SEQUENCE pk_avz_t_schedule_file
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE pk_avz_t_schedule_file OWNER TO aipo_postgres;

-- スケジュールテーブルへ仮予定フラグを追加
ALTER TABLE eip_t_schedule ADD COLUMN temporary_flag character varying(1);

-- スケジュールマップへ個別色を追加
ALTER TABLE eip_t_schedule_map ADD COLUMN individual_color character varying(6);
