DELIMITER //

ALTER TABLE QX_CHART ADD CHART_POSITION INT
//

create table QX_PORTAL_CONFIG (
  NAME varchar(255) not null,
  CSS_FILE varchar(1024) null,
  SHOW_FOCUS_MENU bit default 1,
  SHOW_SUBFOCUS_MENU bit default 1,
  constraint PK_QX_PORTAL_CONFIG primary key(NAME)
)
//

create table QX_PORTAL_TOOLBAR (
  PORTAL_CONFIG_NAME varchar(255) not null,
  ORDER_ID int not null,
  CONTROL_NAME varchar(255) not null,
  constraint PK_QX_PORTAL_TOOLBAR primary key(PORTAL_CONFIG_NAME, ORDER_ID),
  constraint FK_QX_PORTAL_TOOLBAR_PORTAL_CONFIG foreign key (PORTAL_CONFIG_NAME) references QX_PORTAL_CONFIG(NAME)
)
//

create table QX_FORM_FILTER (
  FORM_ID              VARCHAR(250) NOT NULL,
  FIELD_ID             VARCHAR(250) NOT NULL,
  BY_FORM_ID           VARCHAR(250) NOT NULL,
  BY_FIELD_ID          VARCHAR(250) NOT NULL,
  constraint PK_QX_FORM_FILTER primary key (FORM_ID, FIELD_ID, BY_FORM_ID, BY_FIELD_ID)
)
//

-- QX_FILTER_ACCOUNT --

CREATE TABLE TMP_FILTER_ACCOUNT(
	ACCOUNT_ID int NULL,
	EMAIL_FILTER int NULL
)
//

ALTER TABLE QX_FILTER_ACCOUNT DROP FOREIGN KEY FK_QX_FILTER_ACCOUNT_QX_ACCOUNT
//

ALTER TABLE QX_FILTER_ACCOUNT DROP FOREIGN KEY FK_QX_FILTER_ACCOUNT_QX_EMAIL_FILTER
//

ALTER TABLE QX_FILTER_ACCOUNT DROP PRIMARY KEY
//

INSERT INTO TMP_FILTER_ACCOUNT
select * from QX_FILTER_ACCOUNT
//

TRUNCATE TABLE QX_FILTER_ACCOUNT
//

ALTER TABLE QX_FILTER_ACCOUNT ADD PKEY int NULL
//

ALTER TABLE QX_FILTER_ACCOUNT MODIFY PKEY int NOT NULL
//

ALTER TABLE QX_FILTER_ACCOUNT
ADD CONSTRAINT PK_QX_FILTER_ACCOUNT PRIMARY KEY (PKEY)
//

ALTER TABLE QX_FILTER_ACCOUNT ADD CONSTRAINT FK_QX_FILTER_ACCOUNT_QX_ACCOUNT 
FOREIGN KEY (ACCOUNT_ID) REFERENCES QX_ACCOUNT(PKEY)
//

ALTER TABLE QX_FILTER_ACCOUNT ADD CONSTRAINT FK_QX_FILTER_ACCOUNT_QX_EMAIL_FILTER 
FOREIGN KEY (EMAIL_FILTER) REFERENCES QX_EMAIL_FILTER(PKEY)
//

CREATE PROCEDURE TMP()
BEGIN
  DECLARE done int DEFAULT 0;
  DECLARE accnt int;
  DECLARE filter int;
  DECLARE id int;
  DECLARE cur CURSOR FOR SELECT ACCOUNT_ID, EMAIL_FILTER FROM TMP_FILTER_ACCOUNT;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur;
  FETCH cur INTO accnt, filter;
  WHILE done = 0 DO
    CALL QX_NEXT_KEY('qx_filter_account', 1, @id);
    INSERT INTO QX_FILTER_ACCOUNT (PKEY, ACCOUNT_ID, EMAIL_FILTER) VALUES (@id, accnt, filter);
    FETCH cur INTO accnt, filter;
  END WHILE;
  CLOSE cur;
END
//

CALL TMP()
//

DROP PROCEDURE TMP
//

DROP TABLE TMP_FILTER_ACCOUNT
//


-- QX_EMAIL_ROUTING --

CREATE TABLE TMP_EMAIL_ROUTING(
	ACCOUNT_ID int NULL,
	ROUTING_RULE int NULL
)
//

ALTER TABLE QX_EMAIL_ROUTING DROP FOREIGN KEY FK_QX_EMAIL_ROUTING_QX_ACCOUNT
//

ALTER TABLE QX_EMAIL_ROUTING DROP FOREIGN KEY FK_QX_EMAIL_ROUTING_QX_EMAIL_ROUTING_RULES
//

ALTER TABLE QX_EMAIL_ROUTING DROP PRIMARY KEY
//

INSERT INTO TMP_EMAIL_ROUTING select * from QX_EMAIL_ROUTING
//

TRUNCATE TABLE QX_EMAIL_ROUTING
//

ALTER TABLE QX_EMAIL_ROUTING ADD PKEY int NULL
//

ALTER TABLE QX_EMAIL_ROUTING MODIFY PKEY int NOT NULL
//

ALTER TABLE QX_EMAIL_ROUTING ADD CONSTRAINT PK_QX_EMAIL_ROUTING PRIMARY KEY (PKEY)
//

ALTER TABLE QX_EMAIL_ROUTING ADD CONSTRAINT FK_QX_EMAIL_ROUTING_QX_ACCOUNT 
foreign key (ACCOUNT_ID) references QX_ACCOUNT(PKEY)
//

ALTER TABLE QX_EMAIL_ROUTING ADD CONSTRAINT FK_QX_EMAIL_ROUTING_QX_EMAIL_ROUTING_RULES 
foreign key (ROUTING_RULE) references QX_EMAIL_ROUTING_RULES(PKEY)
//

CREATE PROCEDURE TMP()
BEGIN
  DECLARE done int DEFAULT 0;
  DECLARE accnt int;
  DECLARE rule int;
  DECLARE id int;
  DECLARE cur CURSOR FOR SELECT ACCOUNT_ID, ROUTING_RULE FROM TMP_EMAIL_ROUTING;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur;
  FETCH cur INTO accnt, rule;
  WHILE done = 0 DO
    CALL QX_NEXT_KEY('qx_email_routing', 1, @id);
    INSERT INTO QX_EMAIL_ROUTING (PKEY, ACCOUNT_ID, ROUTING_RULE) VALUES (@id, accnt, rule);
    FETCH cur INTO accnt, rule;
  END WHILE;
  CLOSE cur;
END
//

CALL TMP()
//

DROP PROCEDURE TMP
//

DROP TABLE TMP_EMAIL_ROUTING
//


-- QX_HIS_TBLFIELD --

CREATE TABLE TMP_HIS_TBLFIELD(
	HIS_TABLE_ID int NULL,
	HIS_FIELD_ID int NULL
)
//

ALTER TABLE QX_HIS_TBLFIELD DROP FOREIGN KEY FK_QX_HIS_TBLFIELD_TABLE
//

ALTER TABLE QX_HIS_TBLFIELD DROP FOREIGN KEY FK_QX_HIS_TBLFIELD_FIELD
//

ALTER TABLE QX_HIS_TBLFIELD DROP PRIMARY KEY
//

INSERT INTO TMP_HIS_TBLFIELD select * from QX_HIS_TBLFIELD
//

TRUNCATE TABLE QX_HIS_TBLFIELD
//

ALTER TABLE QX_HIS_TBLFIELD ADD PKEY int NULL
//

ALTER TABLE QX_HIS_TBLFIELD MODIFY PKEY int NOT NULL
//

ALTER TABLE QX_HIS_TBLFIELD ADD CONSTRAINT PK_QX_HIS_TBLFIELD PRIMARY KEY (PKEY)
//

ALTER TABLE QX_HIS_TBLFIELD ADD CONSTRAINT FK_QX_HIS_TBLFIELD_TABLE	
foreign key (HIS_TABLE_ID) references QX_HIS_TABLE (HIS_TABLE_ID)
//

ALTER TABLE QX_HIS_TBLFIELD ADD CONSTRAINT FK_QX_HIS_TBLFIELD_FIELD
foreign key (HIS_FIELD_ID, HIS_TABLE_ID) references QX_HIS_FIELD (HIS_FIELD_ID, HIS_TABLE_ID)
//

CREATE PROCEDURE TMP()
BEGIN
  DECLARE done int DEFAULT 0;
  DECLARE tbl int;
  DECLARE fld int;
  DECLARE id int;
  DECLARE cur CURSOR FOR SELECT HIS_TABLE_ID, HIS_FIELD_ID FROM TMP_HIS_TBLFIELD;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur;
  FETCH cur INTO tbl, fld;
  WHILE done = 0 DO
    CALL QX_NEXT_KEY('qx_his_tblfield', 1, @id);
    INSERT INTO QX_HIS_TBLFIELD (PKEY, HIS_TABLE_ID, HIS_FIELD_ID) VALUES(@id, tbl, fld);
    FETCH cur INTO tbl, fld;
  END WHILE;
  CLOSE cur;
END
//

CALL TMP()
//

DROP PROCEDURE TMP
//

DROP TABLE TMP_HIS_TBLFIELD
//

ALTER TABLE QX_FORM_SETTINGS ADD IS_VISIBLE BIT DEFAULT 1
//

ALTER TABLE QX_INBOX ADD READ_FLAG BIT DEFAULT 0
//

UPDATE QX_INBOX SET READ_FLAG=1
//

CALL QX_NEXT_KEY('QX_SYS_PROP', 1, @id)
//
INSERT INTO QX_SYS_PROP (PKEY, PROP_ID, PROP_VAL, PROP_TEXT, PROP_DATA, NEED_RESTART, DESCRIPTION)  
                 VALUES (@id, 'CHECK_MAIL_TIMEOUT', '0', NULL, NULL, 0, 'Email checker timeout')
//

ALTER TABLE QX_LINKEDDATASET DROP PRIMARY KEY
//

ALTER TABLE QX_LINKEDDATASET ADD CONSTRAINT PK_QX_LINKEDDATASET PRIMARY KEY (DATASET_ID, FORM_ID)
//

UPDATE QX_SYS_PROP SET PROP_VAL = '076' WHERE PROP_ID = 'DB_CORE_VERSION'
//