
/* Drop Tables */

DROP TABLE IF EXISTS DEPARTMENT_PERSON;
DROP TABLE IF EXISTS DEPARTMENT;
DROP TABLE IF EXISTS ACCOUNT_PART;
DROP TABLE IF EXISTS PARTNER_PARTVERSION;
DROP TABLE IF EXISTS PART_VERSION;
DROP TABLE IF EXISTS PART;
DROP TABLE IF EXISTS PARTNER;
DROP TABLE IF EXISTS DEALING_SCHEDULE;
DROP TABLE IF EXISTS HOLIDAY;
DROP TABLE IF EXISTS PERSON;
DROP TABLE IF EXISTS JOURNAL_DETAILS;
DROP TABLE IF EXISTS JOURNAL;
DROP TABLE IF EXISTS DEALING;
DROP TABLE IF EXISTS COLUMN_SETTING;
DROP TABLE IF EXISTS ACCOUNT_STRUCTURE;
DROP TABLE IF EXISTS BUDGET;
DROP TABLE IF EXISTS ACCOUNT;
DROP TABLE IF EXISTS COLUMN_MASTER;




/* Create Tables */

CREATE TABLE DEPARTMENT
(
	IDENTIFICATION SERIAL NOT NULL,
	DEPARTMENT_NUMBER VARCHAR,
	NAME VARCHAR NOT NULL,
	PERSON VARCHAR,
	POSTAL_CODE CHAR(8),
	ADDRESS VARCHAR,
	PHONE_NUMBER VARCHAR,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE DEPARTMENT_PERSON
(
	IDENTIFICATION SERIAL NOT NULL,
	LEFT_IDENTIFICATION INT NOT NULL,
	RIGHT_IDENTIFICATION INT NOT NULL,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE PART
(
	IDENTIFICATION SERIAL NOT NULL,
	PART_NUMBER VARCHAR,
	NAME VARCHAR NOT NULL,
	TRADE_NAME VARCHAR,
	LIFECYCLE_STATE VARCHAR,
	UNIT VARCHAR,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE PART_VERSION
(
	SUPER_IDENTIFICATION INT NOT NULL,
	IDENTIFICATION SERIAL NOT NULL,
	LIFECYCLE_STATE VARCHAR,
	PRICE INT,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE ACCOUNT_PART
(
	IDENTIFICATION SERIAL NOT NULL,
	LEFT_IDENTIFICATION INT NOT NULL,
	RIGHT_IDENTIFICATION INT NOT NULL,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE PARTNER
(
	IDENTIFICATION SERIAL NOT NULL,
	PARTNER_NUMBER VARCHAR NOT NULL,
	NAME VARCHAR NOT NULL,
	LIFECYCLE_STATE VARCHAR,
	CATEGORY VARCHAR,
	NAME_KANA VARCHAR,
	POSTAL_CODE CHAR(8),
	ADDRESS VARCHAR,
	PHONE_NUMBER VARCHAR,
	DEPARTMENT_NUMBER VARCHAR,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE DEALING_SCHEDULE
(
	IDENTIFICATION SERIAL NOT NULL,
	APPLICATION_START_DATE DATE NOT NULL,
	APPLICATION_FINISH_DATE DATE NOT NULL,
	CREDIT_ACCOUNT_NUMBER VARCHAR NOT NULL,
	DEBIT_ACCOUNT_NUMBER VARCHAR NOT NULL,
	PART_NUMBER VARCHAR NOT NULL,
	PRICE INT,
	QUANTITY DOUBLE PRECISION,
	AMOUNT INT NOT NULL,
	PARTNER_NUMBER VARCHAR NOT NULL,
	DUE_DATE INT NOT NULL,
	HOLIDAY_CORRESPONDENCE INT NOT NULL,
	NEXT_DUE_DATE DATE NOT NULL,
	PAYMENT_MONTH INT,
	LIFECYCLE_STATE VARCHAR,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE HOLIDAY
(
	IDENTIFICATION SERIAL NOT NULL,
	NAME VARCHAR,
	TARGET_DATE VARCHAR,
	APPLICATION_START_DATE DATE,
	APPLICATION_FINISH_DATE DATE,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE PERSON
(
	IDENTIFICATION SERIAL NOT NULL,
	PERSON_NUMBER VARCHAR NOT NULL,
	PASSWORD VARCHAR,
	NAME VARCHAR NOT NULL,
	POSTAL_CODE CHAR(8),
	ADDRESS VARCHAR,
	PHONE_NUMBER VARCHAR,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE DEALING
(
	IDENTIFICATION SERIAL NOT NULL,
	DEALING_DATE DATE NOT NULL,
	DEALING_TIME TIME,
	PARTNER_NUMBER VARCHAR,
	PERSON VARCHAR,
	LIFECYCLE_STATE VARCHAR,
	TEMPLATE_FLAG BOOLEAN DEFAULT 'false' NOT NULL,
	DEPARTMENT_NUMBER VARCHAR,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE COLUMN_SETTING
(
	IDENTIFICATION SERIAL NOT NULL,
	SUPER_IDENTIFICATION INT NOT NULL,
	USER_NUMBER VARCHAR,
	DISPLAY BOOLEAN,
	STRUCT_ORDER INT,
	WITDH INT,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE ACCOUNT_STRUCTURE
(
	IDENTIFICATION SERIAL NOT NULL,
	UPPER_IDENTIFICATION INT NOT NULL,
	LOWER_IDENTIFICATION INT NOT NULL,
	STRUCT_ORDER INT NOT NULL,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE BUDGET
(
	IDENTIFICATION SERIAL NOT NULL,
	BUDGET_NUMBER VARCHAR NOT NULL,
	DEPARTMENT_NUMBER VARCHAR,
	START_DATE DATE NOT NULL,
	FINISH_DATE DATE NOT NULL,
	AMOUNT INT NOT NULL,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE PARTNER_PARTVERSION
(
	IDENTIFICATION SERIAL NOT NULL,
	LEFT_IDENTIFICATION INT NOT NULL,
	RIGHT_IDENTIFICATION INT NOT NULL,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE JOURNAL_DETAILS
(
	IDENTIFICATION SERIAL NOT NULL,
	SUPER_IDENTIFICATION INT NOT NULL,
	LIFECYCLE_STATE VARCHAR NOT NULL,
	BUDGET_NUMBER VARCHAR NOT NULL,
	AMOUNT INT NOT NULL,
	DEBIT_CREDIT_TYPE VARCHAR NOT NULL,
	STRUCT_ORDER INT NOT NULL,
	TEMPLATE_FLAG BOOLEAN DEFAULT 'false' NOT NULL,
	DEPARTMENT_NUMBER VARCHAR,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE ACCOUNT
(
	IDENTIFICATION SERIAL NOT NULL,
	ACCOUNT_NUMBER VARCHAR NOT NULL,
	NAME VARCHAR NOT NULL,
	AMOUNT INT DEFAULT 0 NOT NULL,
	CONSUMPTION_TAX_RATE INT DEFAULT 8.0 NOT NULL,
	ACCOUNT_TYPE VARCHAR NOT NULL,
	DEBIT_CREDIT_TYPE VARCHAR NOT NULL,
	BP_TYPE VARCHAR NOT NULL,
	TOP_ACCOUNT BOOLEAN DEFAULT 'false' NOT NULL,
	HAS_ACCOUNT BOOLEAN DEFAULT 'false' NOT NULL,
	DEPARTMENT_NUMBER VARCHAR,
	DISPLAY BOOLEAN DEFAULT 'true' NOT NULL,
	SYSTEM_FLAG BOOLEAN DEFAULT 'false' NOT NULL,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE JOURNAL
(
	IDENTIFICATION SERIAL NOT NULL,
	SUPER_IDENTIFICATION INT NOT NULL,
	DEALING_DATE DATE NOT NULL,
	LIFECYCLE_STATE VARCHAR,
	PRICE INT,
	QUANTITY DOUBLE PRECISION,
	AMOUNT INT NOT NULL,
	DISCOUNT INT,
	PART_NUMBER VARCHAR,
	STRUCT_ORDER INT NOT NULL,
	TEMPLATE_FLAG BOOLEAN NOT NULL,
	DEPARTMENT_NUMBER VARCHAR,
	CREATE_DATE TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;


CREATE TABLE COLUMN_MASTER
(
	IDENTIFICATION SERIAL NOT NULL,
	TABLE_VIEW_NAME VARCHAR NOT NULL,
	TABLE_ID VARCHAR NOT NULL,
	COLUMN_VIEW_NAME VARCHAR NOT NULL,
	COLUMN_ID VARCHAR NOT NULL,
	COLUMN_TYPE VARCHAR NOT NULL,
	COLUMN_LENGTH INT,
	CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CREATE_PERSON VARCHAR,
	MODIFY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	MODIFY_PERSON VARCHAR,
	PRIMARY KEY (IDENTIFICATION)
) WITHOUT OIDS;



/* Create Foreign Keys */

ALTER TABLE DEPARTMENT_PERSON
	ADD FOREIGN KEY (LEFT_IDENTIFICATION)
	REFERENCES DEPARTMENT (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE ACCOUNT_PART
	ADD FOREIGN KEY (RIGHT_IDENTIFICATION)
	REFERENCES PART (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE PART_VERSION
	ADD FOREIGN KEY (SUPER_IDENTIFICATION)
	REFERENCES PART (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE PARTNER_PARTVERSION
	ADD FOREIGN KEY (RIGHT_IDENTIFICATION)
	REFERENCES PART_VERSION (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE PARTNER_PARTVERSION
	ADD FOREIGN KEY (LEFT_IDENTIFICATION)
	REFERENCES PARTNER (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE DEPARTMENT_PERSON
	ADD FOREIGN KEY (RIGHT_IDENTIFICATION)
	REFERENCES PERSON (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE JOURNAL
	ADD CONSTRAINT A FOREIGN KEY (SUPER_IDENTIFICATION)
	REFERENCES DEALING (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE ACCOUNT_STRUCTURE
	ADD FOREIGN KEY (LOWER_IDENTIFICATION)
	REFERENCES ACCOUNT (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE ACCOUNT_PART
	ADD FOREIGN KEY (LEFT_IDENTIFICATION)
	REFERENCES ACCOUNT (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE ACCOUNT_STRUCTURE
	ADD FOREIGN KEY (UPPER_IDENTIFICATION)
	REFERENCES ACCOUNT (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE JOURNAL_DETAILS
	ADD FOREIGN KEY (SUPER_IDENTIFICATION)
	REFERENCES JOURNAL (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE COLUMN_SETTING
	ADD FOREIGN KEY (SUPER_IDENTIFICATION)
	REFERENCES COLUMN_MASTER (IDENTIFICATION)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;



