#
# ITHau.Faktura (http://www.freewarefaktura.de)
#
# Dieses SQL-Script enthält die Updates für eine MySQL-Datenbank
#
# Hinweis:
# Beachten Sie bitte die Datums-Angaben zu den Update-Statements.
# Kommentieren Sie Statements aus oder entfernen Sie diese, wenn
# Sie diese Updates bereits ausgeführt haben.
#


### 2006-09-17 ###
ALTER TABLE TBL_CONTACT_PERSONAL_DATA ADD COLUMN RECTORY VarChar(255);

### 2006-11-29 ###
ALTER TABLE TBL_PRODUCT ADD COLUMN WEIGHT Double;
ALTER TABLE TBL_PRODUCT ADD COLUMN WEIGHT_FORMULA VarChar(255);

### 2007-03-05 ###
ALTER TABLE TBL_CONTACT_PERSONAL_DATA ADD COLUMN LASTSEENBY VarChar(255);
ALTER TABLE TBL_CONTACT_PERSONAL_DATA ADD COLUMN FOUNDBY VarChar(255);

### 2007-03-30 ###
ALTER TABLE TBL_CONTACT ADD COLUMN ISACTIVE BOOL DEFAULT 1;
UPDATE TBL_CONTACT SET ISACTIVE=1;

### 2007-07-18 ###
INSERT INTO REF_APPLOG_CATEGORY_NAME(ID, CATEGORYNAME) VALUES (8, 'E-Mail versendet');

### 2007-07-20 ###
ALTER TABLE TBL_DECEASE_ORDER ADD COLUMN MORTICIAN VarChar(255);

### 2007-08-01 ###
CREATE TABLE TBL_ORDER_ANTICIPATED_PAYMENT(
        ID INTEGER NOT NULL AUTO_INCREMENT,
        FK_TBL_ORDER INTEGER NOT NULL,
        CAPTION VarChar(255),
        PRICE Double,
        SORT INTEGER,
        INDEX (`ID`),
        PRIMARY KEY (`ID`),
        KEY `idxFkTblOrder` (`FK_TBL_ORDER`),
            FOREIGN KEY (`FK_TBL_ORDER` )
            REFERENCES `TBL_ORDER`(`ID`)
            ON UPDATE CASCADE ON DELETE CASCADE
    ) ENGINE=INNODB;

### 2007-09-28 ###
ALTER TABLE TBL_TEXT ADD COLUMN SORT INTEGER;
ALTER TABLE TBL_TEXT_CATEGORY ADD COLUMN SORT INTEGER;

### 2007-10-26 ###
ALTER TABLE TBL_ORDER ADD COLUMN MATCHCODE VarChar(100);

### 2007-12-03 ###
ALTER TABLE TBL_CONTACT ADD COLUMN NOTES Text;

### 2008-01-21 ###
CREATE TABLE TBL_ORDER_PAY_COMMISSION(
        ID INTEGER NOT NULL AUTO_INCREMENT,
        FK_TBL_ORDER INTEGER NOT NULL,
        FK_TBL_CONTACT INTEGER DEFAULT NULL,
        COMMISSION_RATE Double,
        COMMENT VarChar(255),
        PRIMARY KEY (`ID`),
        KEY `idxFkTblOrder` (`FK_TBL_ORDER`),
            FOREIGN KEY (`FK_TBL_ORDER` )
            REFERENCES `TBL_ORDER`(`ID`)
            ON UPDATE CASCADE ON DELETE CASCADE,
        KEY `idxFkTblContact` (FK_TBL_CONTACT),
            FOREIGN KEY (FK_TBL_CONTACT )
            REFERENCES TBL_CONTACT(`ID`)
            ON UPDATE CASCADE ON DELETE NO ACTION
    ) ENGINE=INNODB;

INSERT INTO TBL_ORDER_PAY_COMMISSION(FK_TBL_ORDER, FK_TBL_CONTACT, COMMISSION_RATE)
    SELECT TBL_ORDER.ID, TBL_ORDER.FK_TBL_CONTACT_COMMISION, TBL_ORDER.COMMISSION_RATE FROM TBL_ORDER
    WHERE TBL_ORDER.FK_TBL_CONTACT_COMMISION IS NOT NULL;

### 2008-04-19 ###
CREATE TABLE APPOWNER(
  FIELD_NAME Text(255) NOT NULL,
  FIELD_VALUE Text(255)
);

### 2008-06-10 ###
ALTER TABLE TBL_ORDER_PAY_COMMISSION ADD FOREIGN KEY (FK_TBL_CONTACT)
            REFERENCES TBL_CONTACT(`ID`)
            ON UPDATE CASCADE ON DELETE CASCADE;

### 2008-06-20 ###
CREATE TABLE OBJECT_LOCK(
        LOCK_ID Char(255),
        LOCK_USER_ID Char(255),
        PRIMARY KEY(LOCK_ID, LOCK_USER_ID)
    ) ENGINE=INNODB;

### 2008-07-22 ###
ALTER TABLE TBL_CONTACT ADD COLUMN DELIVERY_NOTES Text;
ALTER TABLE TBL_CONTACT_PERSONAL_DATA ADD COLUMN RELIGION_CAN_BE_PUBLISHED BOOL DEFAULT 0;

### 2008-07-25 ###
CREATE TABLE TBL_PRODUCT_GRADUATED_PRICE(
        ID Int NOT NULL AUTO_INCREMENT,
        FK_TBL_PRODUCT INTEGER NOT NULL,
        QUANTITY Double,
        PRICE Double,
        COMMENT VarChar(255),
        PRIMARY KEY (`ID`),
        KEY `idxFkTblProduct` (`FK_TBL_PRODUCT`),
            FOREIGN KEY (`FK_TBL_PRODUCT` )
            REFERENCES `TBL_PRODUCT`(`ID`)
            ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;


### 2009-01-09 ###
ALTER TABLE TBL_PRODUCT ADD COLUMN ALLOW_ADDITION TINYINT DEFAULT 0;
ALTER TABLE TBL_ORDER_ITEM ADD COLUMN ALLOW_ADDITION TINYINT DEFAULT 0;

### 2009-03-09 ###

ALTER TABLE TBL_ORDER ADD COLUMN FK_TBL_CONTACT_PRODUCTION INTEGER DEFAULT NULL;
ALTER TABLE TBL_ORDER ADD FOREIGN KEY (FK_TBL_CONTACT_PRODUCTION)
            REFERENCES TBL_CONTACT(`ID`)
            ON UPDATE CASCADE ON DELETE NO ACTION;

ALTER TABLE TBL_ORDER ADD COLUMN PRODUCTION_STATE INTEGER DEFAULT 0;

CREATE TABLE TBL_ORDER_PRODUCTION_SUPPLIER(
        ID Int NOT NULL AUTO_INCREMENT,
        FK_TBL_ORDER INTEGER NOT NULL,
        FK_TBL_CONTACT INTEGER DEFAULT NULL,
        COMMENT Text,
        `SORT` INTEGER DEFAULT 0,
        PRIMARY KEY (`ID`),
        KEY `idxFkTblOrder` (`FK_TBL_ORDER`),
            FOREIGN KEY (`FK_TBL_ORDER` )
            REFERENCES `TBL_ORDER`(`ID`)
            ON UPDATE CASCADE ON DELETE CASCADE,
        KEY `idxFkTblContact` (FK_TBL_CONTACT),
            FOREIGN KEY (FK_TBL_CONTACT )
            REFERENCES TBL_CONTACT(`ID`)
            ON UPDATE CASCADE ON DELETE NO ACTION
) ENGINE=INNODB;

ALTER TABLE TBL_ORDER ADD COLUMN CALCULATION_STATE INTEGER DEFAULT 0;

CREATE TABLE TBL_ORDER_CALCULATION(
        ID Int NOT NULL AUTO_INCREMENT,
        FK_TBL_ORDER INTEGER NOT NULL,
        FK_TBL_CONTACT INTEGER DEFAULT NULL,
        `CATEGORY` VarChar(255) DEFAULT NULL,
        QUANTITY Double DEFAULT 0,
        PRICE Double DEFAULT 0,
        COMMENT VarChar(255),
        `SORT` INTEGER DEFAULT 0,
        PRIMARY KEY (`ID`),
        KEY `idxFkTblOrder` (`FK_TBL_ORDER`),
            FOREIGN KEY (`FK_TBL_ORDER` )
            REFERENCES `TBL_ORDER`(`ID`)
            ON UPDATE CASCADE ON DELETE CASCADE,
        KEY `idxFkTblContact` (FK_TBL_CONTACT),
            FOREIGN KEY (FK_TBL_CONTACT )
            REFERENCES TBL_CONTACT(`ID`)
            ON UPDATE CASCADE ON DELETE NO ACTION
) ENGINE=INNODB;

### 2009-03-10 ###

ALTER TABLE TBL_ORDER_PRODUCTION_SUPPLIER ADD COLUMN SORT INTEGER;
ALTER TABLE TBL_ORDER_CALCULATION ADD COLUMN SORT INTEGER;

### 2009-03-16 ###

ALTER TABLE REF_ORDER_TYPE ADD COLUMN ENABLE_PRODUCTION_INFOS BOOL DEFAULT 0;
ALTER TABLE REF_ORDER_TYPE ADD COLUMN ENABLE_CALCULATION_INFOS BOOL DEFAULT 0;

### 2009-04-20 ###
ALTER TABLE TBL_ORDER ADD COLUMN PARENT_ID INTEGER DEFAULT NULL;
ALTER TABLE TBL_ORDER ADD FOREIGN KEY (PARENT_ID)
            REFERENCES TBL_ORDER(`ID`)
            ON UPDATE CASCADE ON DELETE SET NULL;

### 2009-05-31 ###
ALTER TABLE TBL_ORDER ADD COLUMN NOTES Text;

### 2009-09-09 ###
ALTER TABLE TBL_CONTACT MODIFY COLUMN FIRSTNAME VarChar(255);
ALTER TABLE TBL_CONTACT MODIFY COLUMN LASTNAME VarChar(255);
ALTER TABLE TBL_CONTACT MODIFY COLUMN MAIDENNAME VarChar(255);
ALTER TABLE TBL_CONTACT MODIFY COLUMN ADDRESS VarChar(255);
ALTER TABLE TBL_CONTACT MODIFY COLUMN DELIVER_COMPANY VarChar(255);
ALTER TABLE TBL_CONTACT MODIFY COLUMN DELIVER_FIRSTNAME VarChar(255);
ALTER TABLE TBL_CONTACT MODIFY COLUMN DELIVER_LASTNAME VarChar(255);
ALTER TABLE TBL_CONTACT MODIFY COLUMN DELIVER_ADDRESS VarChar(255);

### 2009-10-26 ###
ALTER TABLE TBL_CONTACT MODIFY COLUMN MATCHCODE VarChar(255);

### 2009-10-28 ###
CREATE TABLE TBL_PRODUCT_SUPPLIER_GRADUATED_PRICE(
        ID Int NOT NULL AUTO_INCREMENT,
        FK_TBL_PRODUCT_SUPPLIER INTEGER NOT NULL,
        QUANTITY Double,
        PRICE Double,
        COMMENT VarChar(255),
        PRIMARY KEY (`ID`),
        KEY `idxFkTblProductSupplier` (`FK_TBL_PRODUCT_SUPPLIER`),
            FOREIGN KEY (`FK_TBL_PRODUCT_SUPPLIER` )
            REFERENCES `TBL_PRODUCT_SUPPLIER`(`ID`)
            ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

### 2010-01-31 ###
CREATE TABLE `TBL_AUTOMATED_SETTLEMENT` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_CONTACT` INTEGER DEFAULT NULL,
  `DUE_DATE` DATETIME,
  `DUE_INTERVAL` INTEGER,
  `INTERVAL_TYPE`  TINYINT DEFAULT 0,
  `DATE_LAST_INVOICED` DATETIME,
  `DAY_OF_DELIVERY` DATETIME,
  `PRODUCT_ID` VARCHAR(50),
  `PRODUCT_TEXT` TEXT,
  `PRODUCT_PRICE` DOUBLE NULL DEFAULT 0,
  `TAXVALUE` DOUBLE NULL DEFAULT 0,
  `QUANTITY` DOUBLE NULL DEFAULT 0,
  `QUANTITY_UNIT` VARCHAR(50),
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  KEY `idxFkContactId` (`FK_TBL_CONTACT`),
  FOREIGN KEY (`FK_TBL_CONTACT` )
    REFERENCES `TBL_CONTACT`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

### 2010-02-09 ###
CREATE TABLE `TBL_DECEASE_ORDER_DOCS` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_DECEASE_ORDER` INTEGER DEFAULT NULL,
  `DOC_NAME` VARCHAR(100),
  `DOC_DESCRIPTION` TEXT,
  `QUANTITY` INTEGER,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  KEY `idxFkOrderId` (`FK_TBL_DECEASE_ORDER`),
  FOREIGN KEY (`FK_TBL_DECEASE_ORDER` )
    REFERENCES `TBL_DECEASE_ORDER`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

### 2010-02-19 ###
CREATE TABLE `TBL_AUTO_ADD_PRODUCT` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_PRODUCT` INTEGER DEFAULT NULL,
  `FK_TBL_AUTO_ADD_PRODUCT` INTEGER DEFAULT NULL,
  `COMMENT` TEXT,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  KEY `idxFkProductId` (`FK_TBL_PRODUCT`),
  FOREIGN KEY (`FK_TBL_PRODUCT` )
    REFERENCES `TBL_PRODUCT`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE,
  KEY `idxFkAutoAddProductId` (`FK_TBL_AUTO_ADD_PRODUCT`),
  FOREIGN KEY (`FK_TBL_AUTO_ADD_PRODUCT` )
    REFERENCES `TBL_PRODUCT`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
);

### 2010-02-19 ###
ALTER TABLE TBL_ORDER_ITEM ADD COLUMN ITEM_TYPE TINYINT DEFAULT 0;

### 2010-04-19 ###
ALTER TABLE TBL_ORDER_ITEM ADD COLUMN ACCOUNT_NUMBER VarChar(50) DEFAULT NULL;
ALTER TABLE TBL_PRODUCT ADD COLUMN ACCOUNT_NUMBER VarChar(50) DEFAULT NULL;

### 2010-05-15 ###
CREATE TABLE `TBL_CONTACT_ORDER_ADDITION` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_CONTACT` INTEGER DEFAULT NULL,
  `CAPTION` VARCHAR(150),
  `ADDITIONVALUE` Double,
  `ADDITIONTYPE` TINYINT DEFAULT 0,
  `SORT` Integer,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  KEY `idxConOrdAddFkContactId` (`FK_TBL_CONTACT`),
  FOREIGN KEY (`FK_TBL_CONTACT` )
    REFERENCES `TBL_CONTACT`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
);

ALTER TABLE TBL_CONTACT ADD COLUMN CONTACT_ORDER_ADDITION_ADD_MODE TINYINT;


### 2010-05-31 ###
ALTER TABLE TBL_CONTACT_PERSONAL_DATA ADD COLUMN PASTOR VARCHAR(255);
ALTER TABLE TBL_CONTACT_PERSONAL_DATA ADD COLUMN DEATHREGISTEROFFICE VARCHAR(255);

### 2010-06-07 ###
ALTER TABLE TBL_CONTACT ADD COLUMN TAX_ID VARCHAR(50);


### 2010-07-02 ###

CREATE TABLE `REF_ORDER_TEXT`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_REF_ORDER_TYPE` Integer default NULL,
  `REF_ORDER_TEXT_POSITION` Integer default NULL,
  `ORDERTEXT` Text,
  `SORT` Integer,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  KEY `fkRefOrdTextFK_REF_ORDER_TYPE` (`FK_REF_ORDER_TYPE`),
  FOREIGN KEY (`FK_REF_ORDER_TYPE` )
    REFERENCES `REF_ORDER_TYPE`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE,
  KEY `fkRefOrdTextREF_ORDER_TEXT_POSITION` (`REF_ORDER_TEXT_POSITION`),
  FOREIGN KEY (`REF_ORDER_TEXT_POSITION` )
    REFERENCES `REF_ORDER_TEXT_POSITION`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE `TBL_PRODUCT_ORDER_TEXT`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_PRODUCT` Integer default NULL,
  `FK_REF_ORDER_TYPE` Integer,
  `REF_ORDER_TEXT_POSITION` Integer default NULL,
  `ORDERTEXT` Text,
  `SORT` Integer,
  INDEX (`ID`),
  PRIMARY KEY (`ID`),
  KEY `idxFkProOrdTextFkProductId` (`FK_TBL_PRODUCT`),
  FOREIGN KEY (`FK_TBL_PRODUCT` )
    REFERENCES `REF_ORDER_TEXT_POSITION`(`ID`)
    ON UPDATE CASCADE
) ENGINE=INNODB;

### 2010-07-27 ###
CREATE TABLE `TBL_RESUBMISSION`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT, 
  `DUE_DATE` DateTime default NULL,
  `DUE_INTERVAL` Integer,
  `INTERVAL_TYPE` TINYINT,
  `DATE_LAST_REPRESENTED` DateTime default NULL,
  `COMMENT` Text,
  `TARGET_TYPE` Text(255),
  `TARGET_ID` Integer,
  INDEX (`ID`),
  PRIMARY KEY (`ID`)
) ENGINE=INNODB;

### 2010-07-28 ###
ALTER TABLE TBL_ORDER ADD COLUMN STATE VARCHAR(100);
CREATE INDEX idxTBL_ORDER_STATE ON TBL_ORDER(STATE(25));

### 2010-08-01 ###
CREATE TABLE `TBL_TIMETRACKING`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_CONTACT` INTEGER DEFAULT NULL,
  `START_TIME` DateTime,
  `END_TIME` DateTime,
  `USER_NAME` VarChar(255) default NULL,
  `COMMENT` Text,
  `BILLED` BOOL DEFAULT 0,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  KEY `idxTimeTrackingFkContactId` (`FK_TBL_CONTACT`),
  FOREIGN KEY (`FK_TBL_CONTACT` )
    REFERENCES `TBL_CONTACT`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE `TBL_TIMETRACKING_ITEM`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_TIMETRACKING` INTEGER DEFAULT NULL,
  `START_TIME` DateTime,
  `END_TIME` DateTime,
  `PRICE` Double,
  `COMMENT` Text,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  KEY `idxTimeTrackingItemFkTimeTracking` (`FK_TBL_TIMETRACKING`),
  FOREIGN KEY (`FK_TBL_TIMETRACKING` )
    REFERENCES `TBL_TIMETRACKING`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
);


### 2010-08-13 ###
ALTER TABLE TBL_TIMETRACKING_ITEM ADD COLUMN LAST_PAUSE_START_TIME DateTime;
ALTER TABLE TBL_TIMETRACKING_ITEM ADD COLUMN LAST_PAUSE_END_TIME DateTime;
ALTER TABLE TBL_TIMETRACKING_ITEM ADD COLUMN PAUSE_MINUTES INTEGER;

### 2010-09-01 ###
ALTER TABLE TBL_PRODUCT_ORDER_TEXT DROP FOREIGN KEY `TBL_PRODUCT_ORDER_TEXT_ibfk_1`;
ALTER TABLE TBL_PRODUCT_ORDER_TEXT ADD FOREIGN KEY (FK_TBL_PRODUCT)
    REFERENCES TBL_PRODUCT(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE REF_ORDER_TEXT ENGINE = InnoDB;
ALTER TABLE REF_ORDER_TEXT ADD FOREIGN KEY (FK_REF_ORDER_TYPE)
    REFERENCES REF_ORDER_TYPE(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE;  
ALTER TABLE REF_ORDER_TEXT ADD FOREIGN KEY (REF_ORDER_TEXT_POSITION)
    REFERENCES REF_ORDER_TEXT_POSITION(`ID`)
    ON UPDATE CASCADE;


### 2010-09-05 ###

CREATE TABLE `TBL_FACILITY_GROUP`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `USER_ID` VarChar(50),
  `FACILITY_GROUP_NAME` VarChar(255) default NULL,
  `LOCATION` VarChar(255) default NULL,
  `FK_TBL_CONTACT` INTEGER default NULL,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_CONTACT`), 
  KEY `idxFacilityFkContact` (`FK_TBL_CONTACT`),
  FOREIGN KEY (`FK_TBL_CONTACT` )
    REFERENCES `TBL_CONTACT`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE `TBL_FACILITY`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FACILITY_NAME` VarChar(255) default NULL,
  `DETAIL_INFO` VarChar(255),
  `LOCATION` VarChar(255) default NULL,
  `FK_TBL_FACILITY_GROUP` INTEGER DEFAULT NULL,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_FACILITY_GROUP`), 
  KEY `idxFacilityFkGroup` (`FK_TBL_FACILITY_GROUP`),
  FOREIGN KEY (`FK_TBL_FACILITY_GROUP` )
    REFERENCES `TBL_FACILITY_GROUP`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE `TBL_FACILITY_PROPERTY_SECTION`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_FACILITY` INTEGER DEFAULT NULL,
  `SORT` INTEGER default 0,
  `SECTION_NAME` VarChar(255),
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_FACILITY`), 
  KEY `idxFacilityPropertySectionFkFacility` (`FK_TBL_FACILITY`),
  FOREIGN KEY (`FK_TBL_FACILITY` )
    REFERENCES `TBL_FACILITY`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE `TBL_FACILITY_PROPERTY`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_FACILITY_PROPERTY_SECTION` INTEGER default NULL,
  `SORT` INTEGER default 0,
  `PROPERTY_NAME` VarChar(255),
  `PROPERTY_TYPE` TINYINT,
  `PROPERTY_VALUE` Text,
  `PROPERTY_UNIT` VarChar(25),
  `DEFAULT_VALUE` Text,
  `REFERENCE_NUMBER` VarChar(255),
  `GATHERING_INTERVAL` INTEGER,
  `INTERVAL_TYPE` TINYINT,
  `GATHERING_START_DATE` DateTime,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_FACILITY_PROPERTY_SECTION`), 
  KEY `idxFacilityPropertyFkSection` (`FK_TBL_FACILITY_PROPERTY_SECTION`),
  FOREIGN KEY (`FK_TBL_FACILITY_PROPERTY_SECTION` )
    REFERENCES `TBL_FACILITY_PROPERTY_SECTION`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE `TBL_FACILITY_PROPERTY_GATHERED`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_FACILITY_PROPERTY` INTEGER default NULL,
  `GATHERING_TIME` DateTime,
  `PROPERTY_VALUE` Text,
  `COMMENT` VarChar(255),
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_FACILITY_PROPERTY`), 
  KEY `idxFacilityPropertyGatheredFkProperty` (`FK_TBL_FACILITY_PROPERTY`),
  FOREIGN KEY (`FK_TBL_FACILITY_PROPERTY` )
    REFERENCES `TBL_FACILITY_PROPERTY`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;


INSERT INTO OPT_NUMBERS(NUMBERNAME, CURRENTVALUE) VALUES('BusinessObjects.FacilityGroup', 1000);


### 2010-11-24 ###

ALTER TABLE TBL_CONTACT ADD COLUMN SALUTATION VarChar(255);


### 2010-12-07 ###

ALTER TABLE TBL_FACILITY_PROPERTY ADD COLUMN `PROPERTY_SHORT_NAME` VarChar(255);


### 2010-12-16 ###

CREATE TABLE `TBL_GLOBAL_DATA`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `DATA_NAME` VarChar(255),
  INDEX (`ID`), 
  PRIMARY KEY (`ID`)
) ENGINE=INNODB;

CREATE TABLE `TBL_GLOBAL_DATA_DATA`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `FK_TBL_GLOBAL_DATA` INTEGER default NULL,
  `COL_NAME` VarChar(255),
  `ROW_NAME` VarChar(255),
  `DATA` VarChar(255),
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_GLOBAL_DATA`), 
  KEY `idxGlobalDataDataFkGlobalData` (`FK_TBL_GLOBAL_DATA`),
  FOREIGN KEY (`FK_TBL_GLOBAL_DATA` )
    REFERENCES `TBL_GLOBAL_DATA`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

### 2010-12-23 ###

ALTER TABLE TBL_FACILITY_GROUP ADD COLUMN LASTUPDATE DateTime;
ALTER TABLE TBL_FACILITY ADD COLUMN LASTUPDATE DateTime;

CREATE TABLE `TBL_FACILITY_PROPERTY_DEFAULTS`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `PROPERTY_NAME` VarChar(255),
  `PROPERTY_SHORT_NAME` VarChar(255),
  `PROPERTY_TYPE` TINYINT,
  `PROPERTY_UNIT` VarChar(255),
  `REFERENCE_NUMBER` VarChar(255),
  `DEFAULT_VALUE` Text,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`)
) ENGINE=INNODB;


### 2010-12-27 ###

ALTER TABLE TBL_FACILITY_PROPERTY ADD COLUMN PROPERTIES Text;


### 2011-01-02 ###

ALTER TABLE TBL_FACILITY_PROPERTY ADD COLUMN COMMENT Text;

### 2011-01-04 ###

ALTER TABLE TBL_FACILITY_PROPERTY_SECTION ADD COLUMN VIEWMODE VarChar(255);

### 2011-01-05 ###

ALTER TABLE TBL_FACILITY_PROPERTY ADD COLUMN MUST_GATHER_UNBROKEN BOOL DEFAULT 0;
ALTER TABLE TBL_FACILITY_PROPERTY_GATHERED ADD COLUMN PROPERTIES Text;


### 2011-01-21 ###

ALTER TABLE TBL_PRODUCT ADD COLUMN ORDER_PROCESSING_NOTE Text DEFAULT NULL;


### 2011-02-11 ###

ALTER TABLE TBL_PRODUCT_SUPPLIER ADD COLUMN PRODUCTNAME VarChar(255) DEFAULT NULL;



### 2011-02-18 ###

ALTER TABLE TBL_DECEASE_ORDER ADD COLUMN FK_TBL_CONTACT_STONECUTTER INTEGER;
ALTER TABLE TBL_DECEASE_ORDER ADD FOREIGN KEY (FK_TBL_CONTACT_STONECUTTER)
    REFERENCES TBL_CONTACT(`ID`)
    ON UPDATE CASCADE ON DELETE NO ACTION;
            
ALTER TABLE TBL_DECEASE_ORDER ADD COLUMN GRAVE_ALLOCATION VarChar(255);


### 2011-02-22 ###

CREATE TABLE `TBL_CONTACT_COMMUNICATION`(
    `ID` INTEGER NOT NULL AUTO_INCREMENT,
    `FK_TBL_CONTACT` INTEGER default NULL,
    `COMMUNICATION_DATE` DateTime,
    `COMMUNICATION_TEXT` Text,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_CONTACT`), 
  KEY `idxConCommFkTblContact` (`FK_TBL_CONTACT`),
  FOREIGN KEY (`FK_TBL_CONTACT` )
    REFERENCES `TBL_CONTACT`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;


### 2011-02-24 ###

CREATE TABLE `TBL_PRODUCT_STOCK_CHANGE`(
    `ID` INTEGER NOT NULL AUTO_INCREMENT,
    `FK_TBL_PRODUCT_STOCK` INTEGER default NULL,
    `FK_TBL_ORDER` INTEGER default NULL,
    `FK_TBL_ORDER_ITEM` INTEGER default NULL,
    `CHANGE_AMOUNT` double,
    `DESCRIPTION` Text,
    `CHANGED_AT` DateTime,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_PRODUCT_STOCK`), 
  KEY `idxStockChangeFkTblStock` (`FK_TBL_PRODUCT_STOCK`),
  FOREIGN KEY (`FK_TBL_PRODUCT_STOCK` )
    REFERENCES `TBL_PRODUCT_STOCK`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;


### 2011-03-07 ###

ALTER TABLE TBL_CONTACT_COMMUNICATION ADD COLUMN COMMUNICATION_TYPE VarChar(255);


### 2011-03-26 ###

CREATE TABLE `TBL_FACILITY_PROPERTY_TEMPLATE`(
    `ID` INTEGER NOT NULL AUTO_INCREMENT,
    `TEMPLATE_NAME` VarChar(255) default NULL,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`)
) ENGINE=INNODB;

CREATE TABLE `TBL_FACILITY_PROPERTY_TEMPLATE_VAR`(
    `ID` INTEGER NOT NULL AUTO_INCREMENT,
    `FK_TBL_FACILITY_PROPERTY_TEMPLATE` INTEGER default NULL,
    `SORT` INTEGER default 0,
    `VAR_NAME` VarChar(255),
    `VAR_TYPE` TinyInt,
    `VAR_VALUE` Text,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_FACILITY_PROPERTY_TEMPLATE`), 
  KEY `fkFacilityTplVar` (`FK_TBL_FACILITY_PROPERTY_TEMPLATE`),
  FOREIGN KEY (`FK_TBL_FACILITY_PROPERTY_TEMPLATE` )
    REFERENCES `TBL_FACILITY_PROPERTY_TEMPLATE`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE `TBL_FACILITY_PROPERTY_TEMPLATE_PROPERTY`(
    `ID` INTEGER NOT NULL AUTO_INCREMENT,
    `FK_TBL_FACILITY_PROPERTY_TEMPLATE` INTEGER default NULL,
    `SORT` INTEGER default 0,
    `PROPERTY_NAME` VarChar(255),
    `PROPERTY_SHORT_NAME` VarChar(255),
    `PROPERTY_TYPE` TinyInt,
    `PROPERTY_UNIT` VarChar(25),
    `REFERENCE_NUMBER` VarChar(255),
    `DEFAULT_VALUE` Text,
    `GATHERING_INTERVAL` Integer,
    `INTERVAL_TYPE` TinyInt,
    `GATHERING_START_DATE` DateTime,
    `COMMENT` Text,
    `PROPERTIES` Text,
    `MUST_GATHER_UNBROKEN` BOOL,
  INDEX (`ID`), 
  PRIMARY KEY (`ID`),
  INDEX(`FK_TBL_FACILITY_PROPERTY_TEMPLATE`), 
  KEY `fkFacilityTplProperty` (`FK_TBL_FACILITY_PROPERTY_TEMPLATE`),
  FOREIGN KEY (`FK_TBL_FACILITY_PROPERTY_TEMPLATE` )
    REFERENCES `TBL_FACILITY_PROPERTY_TEMPLATE`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;


### 2011-04-06 ###
ALTER TABLE TBL_PRODUCT ADD COLUMN ISLOCKED BOOL DEFAULT 0;
UPDATE TBL_PRODUCT SET ISLOCKED=0;

ALTER TABLE TBL_PRODUCT ADD COLUMN LOCK_MESSAGE VarChar(255) DEFAULT NULL;


### 2011-07-28 ###
CREATE TABLE TBL_PRODUCT_UPDATE_STOCK(
  ID INTEGER NOT NULL AUTO_INCREMENT,
  FK_TBL_PRODUCT INTEGER NOT NULL,
  FK_TBL_UPDATE_PRODUCT INTEGER DEFAULT NULL,
  COMMENT VarChar(255),
  PRIMARY KEY (`ID`),
  KEY `idxFkTblProdStockUpd` (`FK_TBL_PRODUCT`),
    FOREIGN KEY (`FK_TBL_PRODUCT` )
    REFERENCES `TBL_PRODUCT`(`ID`)
    ON UPDATE CASCADE ON DELETE CASCADE,
  KEY `idxFkTblProdUpdStockUpd` (FK_TBL_UPDATE_PRODUCT),
    FOREIGN KEY (FK_TBL_UPDATE_PRODUCT )
    REFERENCES TBL_PRODUCT(`ID`)
    ON UPDATE CASCADE ON DELETE NO ACTION
) ENGINE=INNODB;

INSERT INTO 
    TBL_PRODUCT_UPDATE_STOCK(FK_TBL_PRODUCT, FK_TBL_UPDATE_PRODUCT)
    SELECT TBL_PRODUCT.ID, TBL_PRODUCT.FK_TBL_PRODUCT_UPDATESTOCKHERE
    FROM TBL_PRODUCT WHERE TBL_PRODUCT.FK_TBL_PRODUCT_UPDATESTOCKHERE IS NOT NULL;
	
	
### 2011-08-23 ###
CREATE TABLE `TBL_ATTACHMENT`(
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `ATTACHMENT_TYPE` VarChar(50),
  `FILEPATH` VarChar(255),
  `COMMENT` Text,
  `TARGET_TYPE` VarChar(255),
  `TARGET_ID` Integer,
  INDEX (`ID`),
  PRIMARY KEY (`ID`)
) ENGINE=INNODB;