DROP TABLE avz_t_intramail_map;
DROP TABLE avz_t_intramail_file;
DROP TABLE avz_t_intramail;


-- Table: avz_t_intramail

-- DROP TABLE avz_t_intramail;

CREATE TABLE avz_t_intramail
(
  intramail_id serial NOT NULL,
  owner_id integer NOT NULL,
  subject character varying(128),
  body character varying,
  send_flg character(1) NOT NULL,
  mail_volume integer NOT NULL,
  create_date date NOT NULL,
  update_date timestamp without time zone NOT NULL,
  disabled character(1) NOT NULL,
  CONSTRAINT avz_t_intramail_pkey PRIMARY KEY (intramail_id)
)
WITHOUT OIDS;
ALTER TABLE avz_t_intramail OWNER TO aipo_postgres;

COMMENT ON COLUMN avz_t_intramail.disabled IS '削除フラグ：F＝有効、Ｔ＝無効';

-- Table: avz_t_intramail_map

-- DROP TABLE avz_t_intramail_map;

CREATE TABLE avz_t_intramail_map
(
  id serial NOT NULL,
  user_id integer NOT NULL,
  intramail_id integer NOT NULL,
  transmission_cls character(1) NOT NULL,
  read_flg character(1) NOT NULL,
  view_flg character(1) NOT NULL,
  create_date date,
  update_date timestamp without time zone,
  disabled character(1) NOT NULL,
  CONSTRAINT avz_t_intramail_map_pkey PRIMARY KEY (id),
  CONSTRAINT avz_t_intramail_map_intramail_id_fkey FOREIGN KEY (intramail_id)
      REFERENCES avz_t_intramail (intramail_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE avz_t_intramail_map OWNER TO aipo_postgres;

COMMENT ON COLUMN avz_t_intramail_map.disabled IS '有効無効フラグ：F＝有効、Ｔ＝無効';

-- Table: avz_t_intramail_file

-- DROP TABLE avz_t_intramail_file;

CREATE TABLE avz_t_intramail_file
(
  file_id serial NOT NULL,
  owner_id integer NOT NULL,
  intramail_id integer NOT NULL,
  file_name character varying(128) NOT NULL,
  file_path character varying NOT NULL,
  file_thumbnail bytea,
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_t_intramail_file_pkey PRIMARY KEY (file_id),
  CONSTRAINT avz_t_intramail_file_intramail_id_fkey FOREIGN KEY (intramail_id)
      REFERENCES avz_t_intramail (intramail_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE avz_t_intramail_file OWNER TO aipo_postgres;


-- Index: avz_t_intramail_idx1

-- DROP INDEX avz_t_intramail_idx1;

CREATE INDEX avz_t_intramail_idx1
  ON avz_t_intramail
  USING btree
  (owner_id, send_flg, disabled);

-- Index: avz_t_intramail_map_idx1

-- DROP INDEX avz_t_intramail_map_idx1;

CREATE INDEX avz_t_intramail_map_idx1
  ON avz_t_intramail_map
  USING btree
  (intramail_id);

-- Index: avz_t_intramail_map_idx2

-- DROP INDEX avz_t_intramail_map_idx2;

CREATE INDEX avz_t_intramail_map_idx2
  ON avz_t_intramail_map
  USING btree
  (intramail_id, transmission_cls);

-- Index: avz_t_intramail_map_idx3

-- DROP INDEX avz_t_intramail_map_idx3;

CREATE INDEX avz_t_intramail_map_idx3
  ON avz_t_intramail_map
  USING btree
  (user_id, transmission_cls, view_flg, disabled);

-- Index: avz_t_intramail_file_idx1

-- DROP INDEX avz_t_intramail_file_idx1;

CREATE INDEX avz_t_intramail_file_idx1
  ON avz_t_intramail_file
  USING btree
  (intramail_id);