begin
DECLARE nau MEDIUMINT UNSIGNED;
declare bar DATETIME;
declare usr MEDIUMINT UNSIGNED;
declare ws BIGINT UNSIGNED;
declare cut MEDIUMINT UNSIGNED;
SELECT NEXTVAL(AU_SEQ), user_id, ses_id, now() INTO nau, usr, ws, bar FROM st_curr_user;
SELECT tab_id INTO cut FROM tab WHERE tab_name = 'ENUM';
INSERT INTO au (db_time, au_nr, user_id, tab_id, operation_type, ses_id) VALUES (bar,nau,usr,cut,'I',ws);
INSERT INTO gttn (d1,id1,id2,c1,c2,c0) VALUES (bar,nau,cut,'ENUM','I','totoro');

set @@optimizer_switch='derived_merge=OFF';
INSERT INTO ae (ae_hash, text)
WITH c AS
(
SELECT
conv(substring(md5(new.ENUM_CODE),1,4),16,10) mod 9999 ae_hash
,new.ENUM_CODE text
FROM dual
WHERE
NOT new.ENUM_CODE IS NULL
)
SELECT ae_hash,text
FROM c WHERE NOT EXISTS (SELECT 0 FROM ae WHERE ae_hash=c.ae_hash AND text=c.text);
set session optimizer_switch=default;
INSERT INTO au_cdata (db_time,au_nr,col_id
,new_ae_id)
SELECT bar,nau,col_id
,c.ae_id
FROM col a
left join ae c ON (conv(substring(md5(new.ENUM_CODE),1,4),16,10) mod 9999=c.ae_hash AND new.ENUM_CODE=c.text)
WHERE col_name='ENUM_CODE';
INSERT INTO au_idata (db_time,au_nr,col_id,new_val) SELECT bar,nau,col_id,new.ENUM_ID FROM col WHERE col_name = 'ENUM_ID';
set @@optimizer_switch='derived_merge=OFF';
INSERT INTO ae (ae_hash, text)
WITH c AS
(
SELECT
conv(substring(md5(new.ENUM_NAME),1,4),16,10) mod 9999 ae_hash
,new.ENUM_NAME text
FROM dual
WHERE
NOT new.ENUM_NAME IS NULL
)
SELECT ae_hash,text
FROM c WHERE NOT EXISTS (SELECT 0 FROM ae WHERE ae_hash=c.ae_hash AND text=c.text);
set session optimizer_switch=default;
INSERT INTO au_cdata (db_time,au_nr,col_id
,new_ae_id)
SELECT bar,nau,col_id
,c.ae_id
FROM col a
left join ae c ON (conv(substring(md5(new.ENUM_NAME),1,4),16,10) mod 9999=c.ae_hash AND new.ENUM_NAME=c.text)
WHERE col_name='ENUM_NAME';
INSERT INTO au_idata (db_time,au_nr,col_id,new_val) SELECT bar,nau,col_id,new.ENUM_TYPE_ID FROM col WHERE col_name = 'ENUM_TYPE_ID';
-- STATEMENT part
-- papildyti/pakeisti/ismesti cold_data
-- part 1 not SYTA
drop temporary table if exists trg_u;
create temporary table trg_u as
-- col_id, col_val, nr, desc_col_id, desc_ae_id
SELECT i.col_id, i.new_val col_val
,d.nr,d.desc_col_id,c.new_ae_id desc_ae_id
FROM (SELECT d1 db_time, id1 au_nr, id2 tab_id, c2 operation_type FROM gttn WHERE c1='ENUM' AND c0='totoro' AND c2 IN ('I','U')) a
join au_idata i ON (a.db_time=i.db_time AND a.au_nr=i.au_nr)
join tab_col b ON (i.col_id=b.col_id AND b.col_bp=1)
join cold d ON (i.col_id=d.col_id)
join au_cdata c ON (a.db_time=c.db_time AND a.au_nr=c.au_nr AND d.desc_col_id=c.col_id)
WHERE (a.operation_type='I' OR not c.old_ae_id<=>c.new_ae_id);
DELETE from cold_data WHERE (col_id,col_val,nr) IN (SELECT col_id, col_val, nr FROM trg_u WHERE desc_ae_id IS null);

UPDATE cold_data y, trg_u u SET
 y.desc_col_id = u.desc_col_id
,y.desc_ae_id  = u.desc_ae_id
WHERE u.col_id=y.col_id and u.col_val=y.col_val and u.nr=y.nr
AND NOT
(y.desc_col_id = u.desc_col_id
AND y.desc_ae_id  = u.desc_ae_id);

insert into cold_data (col_id, col_val, nr, desc_col_id, desc_ae_id)
select col_id, col_val, nr, desc_col_id, desc_ae_id from trg_u u
where not exists (select 0 from cold_data where col_id=u.col_id and col_val=u.col_val and nr=u.nr);

drop temporary table if exists trg_u;
-- papildyti/pakeisti/ismesti colc_data
drop temporary table if exists trg_u;
create temporary table trg_u as
WITH l AS
(
SELECT
a.*
,i.col_id
,i.new_val
,Max(if(c.col_id=i.col_id,i.col_id,null)) over (PARTITION BY a.db_time, a.au_nr) bcol_id
,Max(if(c.col_id=i.col_id,i.new_val,null)) over (PARTITION BY a.db_time, a.au_nr) bcol_val
 FROM
(SELECT d1 db_time, id1 au_nr, id2 tab_id, c2 operation_type FROM gttn WHERE c1='ENUM' AND c0='totoro' AND c2 IN ('I','U')) a
join au_idata i ON (a.db_time=i.db_time AND a.au_nr=i.au_nr)
left join tab_col c ON (a.tab_id=c.tab_id AND i.col_id=c.col_id AND c.col_bp=1)
WHERE (a.operation_type='I' OR not i.old_val<=>i.new_val)
)
SELECT b.col_id, l.bcol_val col_val
,b.nr, b.cont_col_id, l.new_val cont_col_val
 FROM
l join colc b ON (l.bcol_id=b.col_id AND l.col_id=b.cont_col_id)
WHERE NOT bcol_val IS NULL;
DELETE from colc_data WHERE (col_id,col_val,nr) IN
(SELECT col_id,col_val,nr FROM trg_u WHERE cont_col_val IS null);

UPDATE colc_data y, trg_u u SET
 y.cont_col_id = u.cont_col_id
,y.cont_col_val  = u.cont_col_val
WHERE u.col_id=y.col_id and u.col_val=y.col_val and u.nr=y.nr
and not u.cont_col_val is null
AND NOT
(y.cont_col_id = u.cont_col_id
AND y.cont_col_val  = u.cont_col_val);

insert into colc_data (col_id, col_val, nr, cont_col_id, cont_col_val)
select col_id, col_val, nr, cont_col_id, cont_col_val from trg_u u
where not u.cont_col_val is null and not exists (select 0 from colc_data where col_id=u.col_id and col_val=u.col_val and nr=u.nr);

drop temporary table if exists trg_u;
DELETE from gttn WHERE c1='ENUM' AND c0='totoro';


END