-- DROP TABLE avz_t_file_acl;
-- DROP TABLE avz_t_folder_acl;
-- DROP TABLE avz_t_cabinet_file;
-- DROP TABLE avz_t_cabinet_folder;

-- Table: avz_t_cabinet_folder

-- DROP TABLE avz_t_cabinet_folder;

CREATE TABLE avz_t_cabinet_folder
(
  folder_id serial NOT NULL,
  parent_id integer NOT NULL,
  folder_name character varying(128) NOT NULL,
  note character varying,
  create_user_id integer,
  update_user_id integer,
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_t_cabinet_folder_pkey PRIMARY KEY (folder_id)
)
WITHOUT OIDS;
ALTER TABLE avz_t_cabinet_folder OWNER TO aipo_postgres;

-- Table: avz_t_cabinet_file

-- DROP TABLE avz_t_cabinet_file;

CREATE TABLE avz_t_cabinet_file
(
  file_id serial NOT NULL,
  folder_id bigint NOT NULL,
  file_title character varying(128) NOT NULL,
  file_name character varying(128) NOT NULL,
  file_size bigint,
  file_path character varying NOT NULL,
  note character varying,
  create_user_id integer,
  update_user_id integer,
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_t_cabinet_file_pkey PRIMARY KEY (file_id),
  CONSTRAINT avz_t_cabinet_file_folder_id_fkey FOREIGN KEY (folder_id)
      REFERENCES avz_t_cabinet_folder (folder_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE avz_t_cabinet_file OWNER TO aipo_postgres;



-- Table: avz_t_file_acl

-- DROP TABLE avz_t_file_acl;

CREATE TABLE avz_t_file_acl
(
  acl_id serial NOT NULL,
  file_id integer NOT NULL,
  acl_type character(1) NOT NULL, -- R:参照 U:更新 D:削除
  target_type character(1) NOT NULL, -- 適用区分 U:ユーザー G:グループ E:全員
  target_id character varying(99) NOT NULL, -- ユーザーIDもしくはグループネーム
  grant_flg character(1) NOT NULL,
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_t_file_acl_pkey PRIMARY KEY (acl_id),
  CONSTRAINT avz_t_file_acl_acl_id_fkey FOREIGN KEY (file_id)
      REFERENCES avz_t_cabinet_file (file_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE avz_t_file_acl OWNER TO aipo_postgres;
COMMENT ON COLUMN avz_t_file_acl.acl_type IS 'R:参照 U:更新 D:削除';
COMMENT ON COLUMN avz_t_file_acl.target_type IS '適用区分 U:ユーザー G:グループ E:全員';
COMMENT ON COLUMN avz_t_file_acl.target_id IS 'ユーザーIDもしくはグループネーム';
COMMENT ON COLUMN avz_t_file_acl.grant_flg IS '権限有無 G：権限有り R：権限無し';

-- Table: avz_t_folder_acl

-- DROP TABLE avz_t_folder_acl;

CREATE TABLE avz_t_folder_acl
(
  acl_id serial NOT NULL,
  folder_id integer NOT NULL,
  acl_type character(1) NOT NULL, -- F:ファイル追加 C:フォルダ追加 R:参照 U:更新 D:削除
  target_type character(1) NOT NULL, -- 適用区分 E:全員 U:ユーザー G:グループ
  target_id character varying(99) NOT NULL, -- ユーザーIDもしくはグループネーム
  grant_flg character(1) NOT NULL,
  create_date date,
  update_date timestamp without time zone,
  CONSTRAINT avz_t_folder_acl_pkey PRIMARY KEY (acl_id),
  CONSTRAINT avz_t_folder_acl_acl_id_fkey FOREIGN KEY (folder_id)
      REFERENCES avz_t_cabinet_folder (folder_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE avz_t_folder_acl OWNER TO aipo_postgres;
COMMENT ON COLUMN avz_t_folder_acl.acl_type IS 'F:ファイル追加 C:フォルダ追加 R:参照 U:更新 D:削除';
COMMENT ON COLUMN avz_t_folder_acl.target_type IS '適用区分 U:ユーザー G:グループ E:全員';
COMMENT ON COLUMN avz_t_folder_acl.target_id IS 'ユーザーIDもしくはグループネーム';
COMMENT ON COLUMN avz_t_folder_acl.grant_flg IS '権限有無 G：権限有り R：権限無し';

INSERT INTO avz_t_cabinet_folder VALUES(1,0,'ルートフォルダ','', 1, 1, now(), now());
SELECT setval('avz_t_cabinet_folder_folder_id_seq',1);

INSERT INTO avz_t_folder_acl(
            folder_id, acl_type, target_type, target_id, grant_flg, 
            create_date, update_date)
    VALUES (1, 'F', 'E', 'E', 'G', now(), now());

INSERT INTO avz_t_folder_acl(
            folder_id, acl_type, target_type, target_id, grant_flg, 
            create_date, update_date)
    VALUES (1, 'C', 'E', 'E', 'G', now(), now());

INSERT INTO avz_t_folder_acl(
            folder_id, acl_type, target_type, target_id, grant_flg, 
            create_date, update_date)
    VALUES (1, 'R', 'E', 'E', 'G', now(), now());

INSERT INTO avz_t_folder_acl(
            folder_id, acl_type, target_type, target_id, grant_flg, 
            create_date, update_date)
    VALUES (1, 'U', 'E', 'E', 'G', now(), now());

INSERT INTO avz_t_folder_acl(
            folder_id, acl_type, target_type, target_id, grant_flg, 
            create_date, update_date)
    VALUES (1, 'D', 'E', 'E', 'R', now(), now());
