
insert into keys ( label ) values ( 'futureStocksEnabled');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'futureStocksEnabled'), '0','0', 0);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'futureStocksEnabled') ;


alter table documente add column comanda_type int default 1;

alter table fisa_magazie add column comanda_type int default 1;

insert into keys ( label ) values ( 'xComboWaitForEnter');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'xComboWaitForEnter'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'xComboWaitForEnter') ;

 drop table blocked_stocks;

alter table stocks add column eta int;


update configs set value = '2016002' where id_key = ( select min(id) from keys where label = 'currentVersion' );



ALTER TABLE files ALTER COLUMN filename TYPE text;

ALTER TABLE files ALTER COLUMN title TYPE text;

alter table facturi_emise_items alter column produs_factura type text;

update configs set value = '2016003' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'futureStocksEnabledFacturiEmise');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'futureStocksEnabledFacturiEmise'), '0','0', 0);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'futureStocksEnabledFacturiEmise') ;



update configs set value = '2016004' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'printEticheteAtNir');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'printEticheteAtNir'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'printEticheteAtNir') ;

insert into keys ( label ) values ( 'frontofficeTransferConsumMagazieDepozit');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'frontofficeTransferConsumMagazieDepozit'), 'Depozit','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
    and id_key = (select min(id) from keys where keys.label = 'frontofficeTransferConsumMagazieDepozit') ;

update configs set value = '2016005' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table stock_transfer add column nr_bon int not null default 1;

INSERT INTO registre_nomenclator (nume_registru,uniquekey) VALUES ('Bon Consum Transfer intern','bon_consum_transfer_intern');

insert into registre_numere (id_registru,numar,dateadd) values (
    (select id from registre_nomenclator where uniquekey = 'bon_consum_transfer_intern'),
    (select coalesce(max(nr_bon_consum),0) from bonuri_consum) , current_timestamp);



update configs set value = '2016006' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table products alter column label type text;

alter table products alter column short_label type text;

alter table products alter column sectie_label type text;

insert into keys ( label ) values ( 'showReducereValoricaButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showReducereValoricaButton'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'showReducereValoricaButton') ;

alter table orders add column reducere_valorica integer default -1 ;

alter table orders add column cod_fiscal text default null ;


insert into keys(label) values ('promo_x_plus_unu') ;
insert into messages(id_key, id_lang, label) values ((select min(id) from keys where label='promo_x_plus_unu'), 1, 'X+1 GRATIS') ;

update configs set value = '2016007' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016008' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'facturareSeriiChitante');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareSeriiChitante'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'facturareSeriiChitante') ;


update configs set value = '2016009' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016010' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016011' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016012' where id_key = ( select min(id) from keys where label = 'currentVersion' );


update configs set value = '2016013' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016014' where id_key = ( select min(id) from keys where label = 'currentVersion' );



update configs set value = '2016015' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016016' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016017' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'showTransferIntreMagaziiButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showTransferIntreMagaziiButton'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'showTransferIntreMagaziiButton') ;

insert into keys ( label ) values ( 'showOptiuniButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showOptiuniButton'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'showOptiuniButton') ;

insert into keys ( label ) values ( 'showMeseButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showMeseButton'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'showMeseButton') ;

insert into keys ( label ) values ( 'showLogoutButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showLogoutButton'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'showLogoutButton') ;

insert into keys ( label ) values ( 'showNotaButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showNotaButton'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'showNotaButton') ;

update configs set value = '2016018' where id_key = ( select min(id) from keys where label = 'currentVersion' );



create table valute ( id serial unique, denumire text, principala int not null, paritate float);

insert into valute (denumire, principala, paritate) values ('RON', 1, 1);

alter table subcategories add column id_valuta int constraint FK_subcategories__valuta references valute(id);

alter table stocks add column id_valuta int constraint FK_stocks__valuta references valute(id) default null;

update subcategories set id_valuta = (select id from valute where denumire = 'RON');




update configs set value = '2016019' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'addTvaLaOfertaSauFactura');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'addTvaLaOfertaSauFactura'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'addTvaLaOfertaSauFactura') ;

update configs set value = '2016020' where id_key = ( select min(id) from keys where label = 'currentVersion' );
alter table sales add column ignore_promo int default 0;



update configs set value = '2016021' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016022' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'facturareProdusGeneric');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareProdusGeneric'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'facturareProdusGeneric') ;

update configs set value = '2016023' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016024' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016025' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016025' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016025' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016025' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016026' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016027' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016028' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016029' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016030' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016031' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'numbersUseThousandsSeparator');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'numbersUseThousandsSeparator'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'numbersUseThousandsSeparator') ;


update configs set value = '2016032' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016033' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016034' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016035' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016036' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016037' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016038' where id_key = ( select min(id) from keys where label = 'currentVersion' );


create index stocks__status on stocks(status);

create index stocks__label on stocks(label);

alter table facturi_emise_items alter column produs_factura type text;


insert into keys ( label ) values ( 'ordersReportingLimit');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'ordersReportingLimit'), '48000','48000', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'ordersReportingLimit') ;

update configs set value = '2016039' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016040' where id_key = ( select min(id) from keys where label = 'currentVersion' );

update configs set value = '2016041' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'returModifyMode');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'returModifyMode'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'returModifyMode') ;

insert into keys ( label ) values ( 'selector2Level1MinHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level1MinHeight'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level1MinHeight') ;

insert into keys ( label ) values ( 'selector2Level2MinHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level2MinHeight'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level2MinHeight') ;

insert into keys ( label ) values ( 'selector2Level3MinHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level3MinHeight'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level3MinHeight') ;


update configs set value = '2016042' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016043' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016044' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016045' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016046' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016047' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016048' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016048' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016048' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016049' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016050' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'facturareRoundDecimals');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareRoundDecimals'), '2','2', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'facturareRoundDecimals') ;

insert into keys ( label ) values ( 'forceShowAllDecimals');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'forceShowAllDecimals'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'forceShowAllDecimals') ;

update configs set value = '2016051' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016052' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016053' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016054' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016055' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016056' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016057' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016058' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table bon_entries alter column "text" type text;

update configs set value = '2016059' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016060' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'raportInchiderePreambul');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'raportInchiderePreambul'), '', '', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'raportInchiderePreambul') ;

update configs set value = '2016061' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016062' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016063' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'qtyCheckingsEnabled');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'qtyCheckingsEnabled'), '0', '0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'qtyCheckingsEnabled') ;


update configs set value = '2016064' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'preorderProductLineHtml');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'preorderProductLineHtml'), '', '', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'preorderProductLineHtml') ;

 insert into keys ( label ) values ( 'orderProductLineHtml');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'orderProductLineHtml'), '', '', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'orderProductLineHtml') ;


insert into keys ( label ) values ( 'orderQtyHtml');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'orderQtyHtml'), '', '', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'orderQtyHtml') ;

  insert into keys ( label ) values ( 'preorderQtyHtml');
   insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'preorderQtyHtml'), '', '', 1);
   insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
    and id_key = (select min(id) from keys where keys.label = 'preorderQtyHtml') ;

  insert into keys ( label ) values ( 'orderValueHtml');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'orderValueHtml'), '', '', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'orderValueHtml') ;


  insert into keys ( label ) values ( 'preorderValueHtml');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'preorderValueHtml'), '', '', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'preorderValueHtml') ;

update configs set value = '2016065' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016066' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016067' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'maxDiscountValuePerDay');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'maxDiscountValuePerDay'), '', '', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'maxDiscountValuePerDay') ;

insert into keys ( label ) values ( 'maxDiscountOrdersNoPerDay');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'maxDiscountOrdersNoPerDay'), '', '', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'maxDiscountOrdersNoPerDay') ;


insert into keys ( label ) values ( 'bonFiscalIgnoreProduseCuPretZero');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'bonFiscalIgnoreProduseCuPretZero'), '1', '', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'bonFiscalIgnoreProduseCuPretZero') ;

update configs set value = '2016068' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'quickUserSwitchEnabled');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'quickUserSwitchEnabled'), '0', '0', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'quickUserSwitchEnabled') ;

update configs set value = '2016069' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016070' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016071' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016072' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'touch6DontShowClientDialog');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'touch6DontShowClientDialog'), '1', '0', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'touch6DontShowClientDialog') ;


update configs set value = '2016073' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016074' where id_key = ( select min(id) from keys where label = 'currentVersion' );

drop table if exists cereri_aprovizionare;

create table cereri_aprovizionare (
    id serial unique,
    id_stock int not null constraint FK_CERERI_APROVIZIONARE__STOCKS references stocks(id),
    id_operator int not null constraint FK_CERERI_APROVIZIONARE__USERS_OPERATOR references users(id),
    id_supervizor int constraint FK_CERERI_APROVIZIONARE__USERS_SUPERVIZOR references users(id),
    id_magazie int not null constraint FK_CERERI_APROVIZIONARE__MAGAZII references magazii(id),
    id_furnizor int constraint FK_CERERI_APROVIZIONARE__CLIENTI references clienti(id),
    status int,
    cantitate_ceruta double precision not null,
    pret_negociat double precision,
    nr_zile_comanda double precision,
    cantitate_recomandata_minim double precision,
    cantitate_recomandata_maxim double precision,
    cantitate_iesita_referinta double precision,
    cantitate_intrata_medie_per_comanda_referinta double precision,
    cantitate_existenta_magazie double precision not null,
    cantitate_existenta_totala double precision not null,
    cantitate_deja_comandata double precision,
    referinta_start timestamp not null,
    referinta_stop timestamp not null,
    data_cerere timestamp not null);

update configs set value = '2016075' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016076' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016077' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016078' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016079' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'group_manager_aprovizionare');
insert into groups ( id_key ) values ((select min(id) from keys where keys.label = 'group_manager_aprovizionare'));
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'group_manager_aprovizionare'), 'Manager aprovizionare');

create table preturi_furnizori (
    id serial unique,
    id_stock int not null constraint FK_PRETURI_FURNIZORI__STOCKS references stocks(id),
    id_furnizor int not null constraint FK_PRETURI_FURNIZORI__CLIENTI references clienti(id),
    pret_negociat double precision not null);

update configs set value = '2016080' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016081' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016082' where id_key = ( select min(id) from keys where label = 'currentVersion' );


drop table if exists cereri_aprovizionare;

create table cereri_aprovizionare (
    id serial unique,
    id_stock int not null constraint FK_CERERI_APROVIZIONARE__STOCKS references stocks(id),
    id_operator int not null constraint FK_CERERI_APROVIZIONARE__USERS_OPERATOR references users(id),
    id_supervizor int constraint FK_CERERI_APROVIZIONARE__USERS_SUPERVIZOR references users(id),
    id_magazie int constraint FK_CERERI_APROVIZIONARE__MAGAZII references magazii(id),
    id_furnizor int constraint FK_CERERI_APROVIZIONARE__CLIENTI references clienti(id),
    status int,
    cantitate_ceruta double precision not null,
    pret_negociat double precision,
    nr_zile_comanda double precision,
    cantitate_recomandata_minim double precision,
    cantitate_recomandata_maxim double precision,
    cantitate_iesita_referinta double precision,
    cantitate_intrata_medie_per_comanda_referinta double precision,
    cantitate_existenta_magazie double precision not null,
    cantitate_existenta_total double precision not null,
    cantitate_deja_comandata double precision,
    referinta_start timestamp not null,
    referinta_stop timestamp not null,
    data_cerere timestamp not null);

update configs set value = '2016083' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016084' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016085' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table stocks alter column id_tip_articol set default 1;

update stocks set id_tip_articol = 1 where id_tip_articol is null;

update configs set value = '2016086' where id_key = ( select min(id) from keys where label = 'currentVersion' );

update configs set value = '2016087' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016088' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016089' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'frontOfficeRoundDecimals');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'frontOfficeRoundDecimals'), '2','2', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'frontOfficeRoundDecimals') ;

update configs set value = '2016090' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016091' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016092' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016092' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016092' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016092' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016093' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016094' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table transfer_items add column img_src text;

create table sales_pictures (
id serial unique,
id_sale int not null constraint FK_SALES__ID_SALE references sales(id) on delete cascade,
picture_name text not null
);

create table tags (
id serial unique,
label text not null
);

create table stocks_tags(
id serial unique,
id_stock int not null constraint FK_STOCKS_TAGS__STOCKS references stocks(id),
id_tag int not null constraint FK_STOCKS_TAGS__TAGS references tags(id)
);



update configs set value = '2016095' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016096' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table regcasa alter column anexa type text;


insert into keys ( label ) values ( 'hotelSaleFacturataBeforeIncasata');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hotelSaleFacturataBeforeIncasata'), '0', '0', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'hotelSaleFacturataBeforeIncasata') ;

  update configs set value = '2016097' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016098' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016099' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'contaExportMonetareTichet');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportMonetareTichet'), 'Tichet', 'Tichet', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'contaExportMonetareTichet') ;

  insert into keys ( label ) values ( 'contaExportMonetareCard');
   insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportMonetareCard'), 'Card', 'Card', 1);
   insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
    and id_key = (select min(id) from keys where keys.label = 'contaExportMonetareCard') ;


    insert into keys ( label ) values ( 'contaExportMonetareCec');
     insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportMonetareCec'), 'Cec', 'Cec', 1);
     insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
      and id_key = (select min(id) from keys where keys.label = 'contaExportMonetareCec') ;

update configs set value = '2016100' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table facturi_emise_items add column produs_line_1 text default '';

update configs set value = '2016101' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016102' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016103' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016104' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'facturareCodProdusDiscount');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareCodProdusDiscount'), '', '', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'facturareCodProdusDiscount') ;


insert into keys ( label ) values ( 'eticheteA4UnitateMasura');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'eticheteA4UnitateMasura'), '100gr', '100gr', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'eticheteA4UnitateMasura') ;

update configs set value = '2016105' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016106' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016107' where id_key = ( select min(id) from keys where label = 'currentVersion' );



insert into keys ( label ) values ( 'nirRoundDecimals');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'nirRoundDecimals'), '2', '2', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'nirRoundDecimals') ;

update configs set value = '2016108' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016109' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016110' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016111' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016112' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table stock_transfer add column id_user int default null;

update configs set value = '2016113' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'contaExportUseCoduriGenerice');
     insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportUseCoduriGenerice'), '1', '1', 1);
     insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
      and id_key = (select min(id) from keys where keys.label = 'contaExportUseCoduriGenerice') ;

insert into keys ( label ) values ( 'contaExportUseCoduriExterne');
     insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportUseCoduriExterne'), '1', '1', 1);
     insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
      and id_key = (select min(id) from keys where keys.label = 'contaExportUseCoduriExterne') ;


insert into keys ( label ) values ( 'productieSemifabricateQuantityMultiplier');
     insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'productieSemifabricateQuantityMultiplier'), '', '', 1);
     insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
      and id_key = (select min(id) from keys where keys.label = 'productieSemifabricateQuantityMultiplier') ;


update configs set value = '2016114' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016115' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016116' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016117' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'touch2Selector2RedrawAtTableOpen');
     insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'touch2Selector2RedrawAtTableOpen'), '0', '0', 1);
     insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
      and id_key = (select min(id) from keys where keys.label = 'touch2Selector2RedrawAtTableOpen') ;

update configs set value = '2016118' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016119' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016120' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016121' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016122' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016123' where id_key = ( select min(id) from keys where label = 'currentVersion' );

create table ev_def (
    id serial unique,
    label text not null,
    tip int not null,
    activ boolean not null default true,
    tip_cont int not null constraint FK_ev_def__tip_cont_client references tip_cont_client(id),
    puncte_def int not null,
    delta_t_inainte int not null default 0,
    delta_t_dupa int not null default 0,
    notify_sms_period int not null,
    notify_email_period int not null,
    cod_sync text);


create table ev_instance(
    id serial unique,
    id_locatie int not null constraint FK_ev_locatii_mapping__locations references locations(id),
    id_ev_def int not null constraint FK_ev_instance__ev_def references ev_def (id),
    data_start timestamp not null,
    data_stop timestamp not null,
    cod_sync text);


create table ev_clienti (
    id serial unique,
    id_ev_def int not null constraint FK_ev_clienti__ev_def references ev_def (id),
    id_client int not null constraint FK_ev_clienti__clienti references clienti (id),
    id_locatie int not null constraint FK_ev_clienti__locations references locations(id),
    puncte_client int not null ,
    data_puncte timestamp not null,
    nr_card text not null,
    tip_card int not null constraint FK_ev_clienti__tip_cont_client references tip_cont_client(id),
    cod_sync text);

alter table tip_cont_client add column cod_sync text ;

update tip_cont_client set cod_sync = 'SRV_' || id;



insert into keys ( label ) values ( 'productieSemifabricateMagazieProductie');
     insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'productieSemifabricateMagazieProductie'), '', '', 1);
     insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
      and id_key = (select min(id) from keys where keys.label = 'productieSemifabricateMagazieProductie') ;


insert into keys ( label ) values ( 'productieSemifabricateMagazieBonConsum');
     insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'productieSemifabricateMagazieBonConsum'), '', '', 1);
     insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
      and id_key = (select min(id) from keys where keys.label = 'productieSemifabricateMagazieBonConsum') ;

update configs set value = '2016124' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016125' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016126' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016127' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016128' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016129' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016130' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016131' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016132' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016133' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016134' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016135' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016136' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table mapping_printer_nota drop constraint fk_mapping_prin_nota__printers;

alter table mapping_printer_nota add constraint fk_mapping_printer_nota__printers foreign key (id_printer) references printers (id) on delete cascade;


alter table mapping_printers_items drop constraint fk_mapping_prin_items_printers;

alter table mapping_printers_items add constraint fk_mapping_printer_items__printers foreign key (id_printer) references printers (id) on delete cascade;


alter table printer_categories drop constraint fk_printer_categories_printers;

alter table printer_categories add constraint fk_printer_categories__printers foreign key (id_printer) references printers (id) on delete cascade;





update configs set value = '2016137' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016138' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016139' where id_key = ( select min(id) from keys where label = 'currentVersion' );


update configs set value = '2016140' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'deleteFacturaFileAfterSave');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'deleteFacturaFileAfterSave'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'deleteFacturaFileAfterSave') ;


update configs set value = '2016141' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016142' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'touch2ShowQuickTableItemsPerLine');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'touch2ShowQuickTableItemsPerLine'), '4','4', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'touch2ShowQuickTableItemsPerLine') ;

update configs set value = '2016143' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016144' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016145' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016146' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016147' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016148' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016149' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016150' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table clienti add column cod_sync text;
alter table persoane_fizice add column cod_sync text;

alter table persoane_juridice add column cod_sync text;


insert into keys ( label ) values ( 'codUnicDatabase');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'codUnicDatabase'), 'changeme_if_use_database_sync','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'codUnicDatabase') ;


update configs set value = '2016151' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table clienti rename column cod_sync to cod_sync_client;

alter table persoane_Fizice rename column cod_sync to cod_sync_pf;

alter table cont_client add column cod_sync text ;

alter table promotii_clienti add column cod_sync text ;

update configs set value = '2016152' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016153' where id_key = ( select min(id) from keys where label = 'currentVersion' );



create table clients_locations (
    id serial unique,
    id_client int not null constraint FK_clients_locations__clienti references clienti (id),
    id_location int  constraint FK_clients_locations__locations references locations(id),
    cod_sync text );

create index clients_locations__cod_sync on clients_locations(cod_sync);


insert into keys ( label ) values ( 'clientFilterClassname');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'clientFilterClassname'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'clientFilterClassname') ;

update configs set value = '2016154' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016155' where id_key = ( select min(id) from keys where label = 'currentVersion' );



insert into keys ( label ) values ( 'contClientLocationOnDetalii');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contClientLocationOnDetalii'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'contClientLocationOnDetalii') ;

alter table persoane_fizice add column data_numelui date default current_timestamp;

update configs set value = '2016156' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016157' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table persoane_fizice add column observatii text;

update configs set value = '2016158' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016159' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016160' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016161' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table buletine add column cod_sync text;

alter table pasapoarte add column cod_sync text;

update configs set value = '2016162' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016163' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016164' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016165' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016166' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016167' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table persoane_juridice add column must_sync boolean not null default false;

alter table persoane_fizice add column must_sync boolean not null default false;

alter table clienti add column must_sync boolean not null default false;

alter table buletine add column must_sync boolean not null default false;

alter table pasapoarte add column must_sync boolean not null default false;


insert into keys ( label ) values ( 'group_manager_clienti');
insert into groups ( id_key ) values ((select min(id) from keys where keys.label = 'group_manager_clienti'));
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'group_manager_clienti'), 'Manager clienti');


insert into keys ( label ) values ('gui_fidelity_operator');
insert into messages ( id_lang, id_key, label ) values ( 1, (select max(id) from keys), 'Fidelity Operator');
insert into gui_panels ( id_key, id_parent, classname ) values ( (select max(id) from keys), (select id from gui_panels where id_parent = -1), 'ro.atreides.restaurant.modules.fidelitybo.FidelityOperatorPanel');


insert into keys ( label ) values ( 'group_manager_evenimente');
insert into groups ( id_key ) values ((select min(id) from keys where keys.label = 'group_manager_evenimente'));
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'group_manager_evenimente'), 'Manager evenimente');

alter table ev_instance add column deleted boolean default false;


insert into keys ( label ) values ( 'passwordAtStergereClient');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'passwordAtStergereClient'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'passwordAtStergereClient') ;

alter table triggered_data alter remote set data type text;

alter table persoane_fizice drop column external_id;

update configs set value = '2016168' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016169' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016170' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016171' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016172' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016173' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016174' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016175' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016176' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016177' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016178' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016179' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016180' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016181' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016182' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016183' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016184' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016185' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016186' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016187' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016188' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016189' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016190' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016191' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016192' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016193' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016194' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016195' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016196' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016197' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ('backoffice_manager_panel');
insert into messages ( id_lang, id_key, label ) values ( 1, (select max(id) from keys), 'Backoffice Manager');
insert into gui_panels ( id_key, id_parent, classname ) values ( (select max(id) from keys), (select id from gui_panels where id_parent = -1), 'ro.atreides.restaurant.modules.fidelitybo.BackofficeManagerPanel');


create table sms_queue (
id serial unique,
mesaj text,
destinatar text,
status int,
eroare text
);

update configs set value = '2016198' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016199' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table ev_def add column status int default 1;
update configs set value = '2016200' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016201' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016202' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'clientRequirementOnAsociereCard');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'clientRequirementOnAsociereCard'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'clientRequirementOnAsociereCard') ;

update configs set value = '2016203' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016204' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016205' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'addTranzactieClientAtCloseOrder');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'addTranzactieClientAtCloseOrder'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'addTranzactieClientAtCloseOrder') ;

update configs set value = '2016206' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016207' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016208' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016209' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'jsonLicensing');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'jsonLicensing'), '0','0', 0);


insert into keys ( label ) values ( 'clientName');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'clientName'), '','', 1);

insert into keys ( label ) values ( 'partnerName');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'partnerName'), '','', 1);

insert into keys ( label ) values ( 'bdId');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'bdId'), '','', 1);


update configs set value = '2016210' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016211' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016212' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016213' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016214' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016215' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016216' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016217' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016218' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016219' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016220' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016221' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016222' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'bonConsumNrDocManual');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'bonConsumNrDocManual'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'bonConsumNrDocManual') ;

 ALTER TABLE bonuri_consum ADD column observatii varchar(50);

insert into keys ( label ) values ( 'forceAllMenuOptions');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'forceAllMenuOptions'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'forceAllMenuOptions') ;

insert into keys ( label ) values ( 'selectMultipleMagazii');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selectMultipleMagazii'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selectMultipleMagazii') ;

insert into keys ( label ) values ( 'winmentorMagaziiExcluse');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'winmentorMagaziiExcluse'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'winmentorMagaziiExcluse') ;

insert into keys ( label ) values ( 'dataSyncIgnoreRemoteRecipeItems');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'dataSyncIgnoreRemoteRecipeItems'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'dataSyncIgnoreRemoteRecipeItems') ;

update configs set value = '2016223' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016224' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016225' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016226' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016227' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016228' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016229' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016230' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016231' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'anafGsonServerConf');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'anafGsonServerConf'), 'arhiva.deversoft.ro=9091','arhiva.deversoft.ro=9091', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'anafGsonServerConf') ;

update configs set value = '2016232' where id_key = ( select min(id) from keys where label = 'currentVersion' );



update configs set value = '2016233' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016234' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016235' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016236' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016237' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016238' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016239' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016240' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'touchProductSelectionDelayMillis');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'touchProductSelectionDelayMillis'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'touchProductSelectionDelayMillis') ;

update configs set value = '2016241' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016242' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016243' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016244' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016245' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016246' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table bonfiscal_numbers drop constraint fk_bonfiscal_number__orders;

alter table bonfiscal_numbers add constraint fk_bonfiscal_number__orders foreign key (id_order) references orders (id) on delete cascade;

update configs set value = '2016247' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016248' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016249' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016250' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016251' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table promotii_clienti add column dateadded timestamp default current_timestamp;

update promotii_clienti set dateadded = (select last_updated from clienti where clienti.id = promotii_clienti.id_client);

update configs set value = '2016252' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016253' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016254' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016255' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016256' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016257' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016258' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016259' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016260' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016261' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016262' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016263' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016264' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016265' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016266' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016267' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016268' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016269' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016270' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016270' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016271' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016272' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016273' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016274' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016275' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016276' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016277' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016278' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016279' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016279' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016280' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016281' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016282' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016283' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016284' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016285' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016286' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016287' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table conta_export_mapping add column cota_tva numeric ;
update configs set value = '2016288' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table facturi add column data_scadenta timestamp ;
update facturi set data_Scadenta = dateadd;
alter table facturi alter column data_scadenta set not null;

update configs set value = '2016289' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016290' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016291' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016292' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table bonfiscal_numbers drop constraint if exists fk_bonfiscal_number__orders;

update configs set value = '2016293' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016294' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016295' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016296' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table corrections add column id_sectie int constraint FK_corrections__categories references categories(id) default null;


insert into keys ( label ) values ( 'fidelitatePuncteLaNota');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'fidelitatePuncteLaNota'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'fidelitatePuncteLaNota') ;

alter table orders add column puncte_consumate double precision default -1 ;


update configs set value = '2016297' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016298' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'showImportJsonOrdersButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showImportJsonOrdersButton'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'showImportJsonOrdersButton') ;

update configs set value = '2016352' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016353' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016354' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016355' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016356' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'nirShowCodProdus');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'nirShowCodProdus'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'nirShowCodProdus') ;


update configs set value = '2016357' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016358' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016359' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016360' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table tables add column id_produs_automat int constraint FK_tables__products references products(id) default null;

alter table tables add column id_promotie_automata int constraint FK_tables__promotions references promotions(id) default null;

alter table locations add column allow_autoclose_laplata_orders boolean default false;

update configs set value = '2016361' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016362' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016363' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016364' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016365' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'zPrintSplitProduseByCategorie');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'zPrintSplitProduseByCategorie'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'zPrintSplitProduseByCategorie') ;

update configs set value = '2016366' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016367' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016368' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016369' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016370' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'contaNirTotaluriPerTipProdus');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaNirTotaluriPerTipProdus'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'contaNirTotaluriPerTipProdus') ;

update configs set value = '2016371' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'punctlucru');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'punctlucru'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'punctlucru') ;

update configs set value = '2016372' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016373' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016374' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016375' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'touch2ShowInchideComenziButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'touch2ShowInchideComenziButton'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'touch2ShowInchideComenziButton') ;


update configs set value = '2016376' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016377' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016378' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016379' where id_key = ( select min(id) from keys where label = 'currentVersion' );


create table tombola_tichete (
	id serial unique,
	nr_tichete int not null,
	id_client int not null constraint FK_tombola_tichete__clienti references clienti(id),
	id_location int not null constraint FK_tombola_tichete__locatii references locations(id),
	data_emiterii timestamp not null default current_timestamp,
	card text not null,
	data_expirarii timestamp ,
	cod_sync text
	);

update configs set value = '2016380' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016381' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016382' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'tombolaCurentaName');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'tombolaCurentaName'), 'Tombola Merkur','Tombola Merkur', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'tombolaCurentaName') ;

insert into keys ( label ) values ( 'tombolaCurentaExpireDate');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'tombolaCurentaExpireDate'), '01-01-2018','01-01-2018', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'tombolaCurentaExpireDate') ;

update configs set value = '2016383' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016384' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016385' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016386' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016387' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016388' where id_key = ( select min(id) from keys where label = 'currentVersion' );


create index cont_client__detalii on cont_client(detalii);


insert into keys ( label ) values ( 'fidelityAllLocationsList');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'fidelityAllLocationsList'), '1 mai,Prosper,Constanta 1,Constanta 3,Craiova,Ploiesti,Buzau,Sibiu,Railway station','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'fidelityAllLocationsList') ;

update configs set value = '2016389' where id_key = ( select min(id) from keys where label = 'currentVersion' );


update configs set value = '2016390' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016391' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table triggered_data add column text_filter text not null default '';

update configs set value = '2016392' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'retetarShowFontCombo');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'retetarShowFontCombo'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'retetarShowFontCombo') ;

update configs set value = '2016393' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016394' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016394' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016395' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016396' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016397' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016398' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016399' where id_key = ( select min(id) from keys where label = 'currentVersion' );

update configs set value = '2016400' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016401' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016402' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016403' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016404' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'adminPanelsUseRights');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'adminPanelsUseRights'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'adminPanelsUseRights') ;

update keys set label = 'permissions_manage' where label = 'permissions_remove';

update messages set label = 'Manage' where id_key in ( select id from keys where label = 'permissions_manage');


update configs set value = '2016405' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016406' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016407' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016408' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016409' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016410' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016411' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016412' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'etichetePretKgDivisor');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'etichetePretKgDivisor'), '10','10', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'etichetePretKgDivisor') ;

update configs set value = '2016413' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016414' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016415' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'lotBarcodeStripChecksum');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'lotBarcodeStripChecksum'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'lotBarcodeStripChecksum') ;


insert into keys ( label ) values ( 'barcodePrinterManagesMultipleCopies');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'barcodePrinterManagesMultipleCopies'), '1','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'barcodePrinterManagesMultipleCopies') ;


alter table products add column cantar_order int;

update configs set value = '2016416' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016417' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'barcodeInternalDefaultPrefix');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'barcodeInternalDefaultPrefix'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'barcodeInternalDefaultPrefix') ;

update configs set value = '2016418' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016419' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016420' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016421' where id_key = ( select min(id) from keys where label = 'currentVersion' );

update configs set value = '2016422' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016423' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016424' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'barcodePictureMaxWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'barcodePictureMaxWidth'), '226','226', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'barcodePictureMaxWidth') ;


insert into keys ( label ) values ( 'barcodePictureMaxHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'barcodePictureMaxHeight'), '50','50', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'barcodePictureMaxHeight') ;

update configs set value = '2016425' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table sales add column id_parentsale int constraint FK_sales__sales_parentsale references sales(id);

alter table productie add column id_parent int;

update configs set value = '2016426' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016427' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016428' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016429' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016430' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016431' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016432' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'cantarDistinctChars');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'cantarDistinctChars'), '27','27', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'cantarDistinctChars') ;

update configs set value = '2016433' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016434' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016435' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'meniuriMultipleSelectionAllowed');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'meniuriMultipleSelectionAllowed'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'meniuriMultipleSelectionAllowed') ;

insert into keys ( label ) values ( 'livrariShowSectiiInSelector');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'livrariShowSectiiInSelector'), '1','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'livrariShowSectiiInSelector') ;

insert into keys ( label ) values ( 'livrariMarcajAfterMeniuAdd');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'livrariMarcajAfterMeniuAdd'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'livrariMarcajAfterMeniuAdd') ;

insert into keys ( label ) values ( 'touchMarcajAfterMeniuAdd');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'touchMarcajAfterMeniuAdd'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'touchMarcajAfterMeniuAdd') ;


update configs set value = '2016436' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016437' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016438' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016439' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016440' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'tipPlataFixedValues');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'tipPlataFixedValues'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'tipPlataFixedValues') ;

update configs set value = '2016441' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016442' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016443' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016444' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016445' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016446' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016447' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'incarcaCasaMaxValue');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'incarcaCasaMaxValue'), '1000','1000', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'incarcaCasaMaxValue') ;

update configs set value = '2016448' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016449' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'clientPersFiziceAllowSameNameAndBirthDay');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'clientPersFiziceAllowSameNameAndBirthDay'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'clientPersFiziceAllowSameNameAndBirthDay') ;

update configs set value = '2016450' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016451' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016452' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016453' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016454' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016455' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016456' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016457' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016458' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016459' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016460' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016461' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016462' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016463' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016464' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'terminalInventariereCatalogFormat');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'terminalInventariereCatalogFormat'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'terminalInventariereCatalogFormat') ;

insert into keys ( label ) values ( 'terminalInventariereCatalogCodInterPrefix');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'terminalInventariereCatalogCodInterPrefix'), 'CI','CI', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'terminalInventariereCatalogCodInterPrefix') ;

insert into keys ( label ) values ( 'terminalInventariereCatalogMateriiPrimePrefix');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'terminalInventariereCatalogMateriiPrimePrefix'), 'MP','MP', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'terminalInventariereCatalogMateriiPrimePrefix') ;

insert into keys ( label ) values ( 'terminalInventariereScanFormat');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'terminalInventariereScanFormat'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'terminalInventariereScanFormat') ;

insert into keys ( label ) values ( 'terminalInventariereScanItemSeparator');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'terminalInventariereScanItemSeparator'), '' , '', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'terminalInventariereScanItemSeparator') ;

update configs set value = '2016465' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016466' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016467' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016468' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016469' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016470' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016471' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016472' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016472' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016473' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016474' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016475' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016476' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016477' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016478' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'confirmareNrPersoaneAtNota');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'confirmareNrPersoaneAtNota'), '0','0', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'confirmareNrPersoaneAtNota') ;

insert into keys ( label ) values ( 'requestCardAtNota');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'requestCardAtNota'), '0','0', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'requestCardAtNota') ;

update configs set value = '2016479' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016480' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016481' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016482' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016483' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016484' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016485' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'clienti_manager');
insert into groups ( id_key ) values ((select min(id) from keys where keys.label = 'clienti_manager'));
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'clienti_manager'), 'Manager clienti management online');

alter table persoane_fizice add column discount int not null default 1;


insert into keys ( label ) values ( 'addClientIfNotFound');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'addClientIfNotFound'), '1','0', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'addClientIfNotFound') ;

update configs set value = '2016486' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table persoane_fizice add column password text not null default '';

update configs set value = '2016487' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table persoane_fizice alter adresa set default '';

update configs set value = '2016488' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table products_Barcodes alter column barcode set data type text;

update configs set value = '2016489' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016490' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016491' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016492' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table fisa_magazie drop constraint if exists fk_fisa_magazie__sales ;

alter table fisa_magazie rename column id_sale to id_document_item;

alter table fisa_magazie add column id_document int ;

update configs set value = '2016493' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table cont_client add column id_order int ;

alter table cont_client add CONSTRAINT FK_CONT_CLIENT__ORDERS FOREIGN KEY (id_order) references orders(id) on delete cascade;

update configs set value = '2016493' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016493' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016493' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016493' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016494' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016495' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016496' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016497' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016498' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016499' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016500' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016501' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016502' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016503' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016504' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2016505' where id_key = ( select min(id) from keys where label = 'currentVersion' );

update configs set value = '2016507' where id_key = ( select min(id) from keys where label = 'currentVersion' );

update configs set value = '2018006' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'selector2Level1MaxHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level1MaxHeight'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level1MaxHeight') ;

insert into keys ( label ) values ( 'selector2Level2MaxHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level2MaxHeight'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level2MaxHeight') ;

insert into keys ( label ) values ( 'selector2Level3MaxHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level3MaxHeight'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level3MaxHeight') ;



insert into keys ( label ) values ( 'selector2Level1MaxWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level1MaxWidth'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level1MaxWidth') ;

insert into keys ( label ) values ( 'selector2Level2MaxWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level2MaxWidth'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level2MaxWidth') ;

insert into keys ( label ) values ( 'selector2Level3MaxWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level3MaxWidth'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level3MaxWidth') ;

insert into keys ( label ) values ( 'selector2Level1PrefHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level1PrefHeight'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level1PrefHeight') ;

insert into keys ( label ) values ( 'selector2Level2PrefHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level2PrefHeight'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level2PrefHeight') ;

insert into keys ( label ) values ( 'selector2Level3PrefHeight');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level3PrefHeight'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level3PrefHeight') ;



insert into keys ( label ) values ( 'selector2Level1PrefWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level1PrefWidth'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level1PrefWidth') ;

insert into keys ( label ) values ( 'selector2Level2PrefWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level2PrefWidth'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level2PrefWidth') ;

insert into keys ( label ) values ( 'selector2Level3PrefWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level3PrefWidth'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level3PrefWidth') ;

insert into keys ( label ) values ( 'selector2Level1MinWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level1MinWidth'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level1MinWidth') ;

insert into keys ( label ) values ( 'selector2Level2MinWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level2MinWidth'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level2MinWidth') ;

insert into keys ( label ) values ( 'selector2Level3MinWidth');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level3MinWidth'), '-1','-1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level3MinWidth') ;


insert into keys ( label ) values ( 'selector2Level1FontStyle');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level1FontStyle'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level1FontStyle') ;

insert into keys ( label ) values ( 'selector2Level2FontStyle');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level2FontStyle'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level2FontStyle') ;

insert into keys ( label ) values ( 'selector2Level3FontStyle');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level3FontStyle'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level3FontStyle') ;


insert into keys ( label ) values ( 'selector2Level1FontName');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level1FontName'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level1FontName') ;

insert into keys ( label ) values ( 'selector2Level2FontName');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level2FontName'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level2FontName') ;

insert into keys ( label ) values ( 'selector2Level3FontName');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'selector2Level3FontName'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'selector2Level3FontName') ;update configs set value = '2016515' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'showComandaAprovizionareButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showComandaAprovizionareButton'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'showComandaAprovizionareButton') ;

insert into keys ( label ) values ('gui_comenzi_aprovizionare');
insert into messages ( id_lang, id_key, label ) values ( 1, (select max(id) from keys), 'Comenzi Aprovizionare');
insert into gui_panels ( id_key, id_parent, classname ) values ( (select max(id) from keys), (select id from gui_panels where id_parent = -1), 'ro.atreides.restaurant.gui.reports.CentralizareComenziReport');

insert into keys ( label ) values ( 'gdprKeyAlreadyGenerated');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'gdprKeyAlreadyGenerated'), '0','0', 0);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'gdprKeyAlreadyGenerated') ;



alter table persoane_fizice disable trigger all;

alter table buletine disable trigger all;

alter table pasapoarte disable trigger all;

alter table clienti disable trigger all;

alter table file_versions disable trigger all;



ALTER TABLE persoane_fizice ADD COLUMN CRYPT TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN nume SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN prenume SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN adresa SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN oras SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN telefon_fix SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN fax SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN telefon_mobil SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN email SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN pagina_web SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN locul_nasterii SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN cetatenie SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ALTER COLUMN nationalitate SET DATA TYPE TEXT;

ALTER TABLE persoane_fizice ADD COLUMN data_nasterii_text TEXT;

ALTER TABLE persoane_fizice ADD COLUMN data_numelui_text TEXT;

UPDATE persoane_fizice SET data_nasterii_text = data_nasterii;

UPDATE persoane_fizice SET data_numelui_text = data_numelui;

ALTER table persoane_fizice alter column data_nasterii drop not null;

ALTER table persoane_fizice alter column data_numelui drop not null;



CREATE OR REPLACE FUNCTION no_crypt_value(coloana TEXT, crypt TEXT, pass TEXT) RETURNS TEXT AS $$
DECLARE
	value TEXT;
BEGIN
	IF crypt = 'C' THEN
		value := convert_from(decrypt(CAST(coloana as bytea), CAST(pass as bytea),'aes'), 'UTF-8');
	ELSE
		value := coloana;
	END IF;
	return value;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION crypt_persoanefizice(keyEncryption TEXT) RETURNS void AS $$
DECLARE
	rec record;
	byteKeyEnc bytea := CAST(keyEncryption as bytea);
 	cur_persoane CURSOR FOR SELECT * FROM persoane_fizice WHERE crypt IS NULL;
BEGIN
	OPEN cur_persoane;

	LOOP

		FETCH cur_persoane INTO rec;

		EXIT WHEN NOT FOUND;

		UPDATE persoane_fizice SET nume = encrypt(CAST(replace(nume,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        prenume = encrypt(CAST(replace(prenume,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        adresa = encrypt(CAST(replace(adresa,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        oras = encrypt(CAST(replace(oras,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        telefon_fix = encrypt(CAST(replace(telefon_fix,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        fax = encrypt(CAST(replace(fax,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        telefon_mobil = encrypt(CAST(replace(telefon_mobil,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        email = encrypt(CAST(replace(email,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        pagina_web = encrypt(CAST(replace(pagina_web,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        locul_nasterii = encrypt(CAST(replace(locul_nasterii,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        cetatenie = encrypt(CAST(replace(cetatenie,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        nationalitate = encrypt(CAST(replace(nationalitate,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        data_nasterii_text = encrypt(CAST(  data_nasterii_text as bytea) ,byteKeyEnc,'aes'),
        data_numelui_text = encrypt(CAST(data_numelui_text as bytea) ,byteKeyEnc,'aes'),
        crypt = 'C' WHERE CURRENT OF cur_persoane;

	END LOOP;
	CLOSE cur_persoane;

END;
$$ LANGUAGE plpgsql;




ALTER TABLE buletine ALTER COLUMN cnp SET DATA TYPE TEXT;
ALTER TABLE buletine ALTER COLUMN seria SET DATA TYPE TEXT;
ALTER TABLE buletine ALTER COLUMN numarul SET DATA TYPE TEXT;
ALTER TABLE buletine ALTER COLUMN sectia SET DATA TYPE TEXT;

ALTER TABLE buletine ADD COLUMN CRYPT TEXT;

ALTER TABLE buletine ADD COLUMN eliberat_la_text TEXT;

ALTER TABLE buletine ADD COLUMN expira_la_text TEXT;

UPDATE buletine SET eliberat_la_text = eliberat_la;

UPDATE buletine SET expira_la_text = expira_la;



ALTER TABLE pasapoarte ALTER COLUMN seria SET DATA TYPE TEXT;
ALTER TABLE pasapoarte ALTER COLUMN tip_act SET DATA TYPE TEXT;


ALTER TABLE pasapoarte ADD COLUMN eliberat_la_text TEXT;

ALTER TABLE pasapoarte ADD COLUMN expira_la_text TEXT;

ALTER TABLE pasapoarte ADD COLUMN CRYPT TEXT;

UPDATE pasapoarte SET eliberat_la_text = eliberat_la;

UPDATE pasapoarte SET expira_la_text = expira_la;


CREATE OR REPLACE FUNCTION crypt_buletine(keyEncryption TEXT) RETURNS void AS $$
DECLARE
	rec record;
	byteKeyEnc bytea := CAST(keyEncryption as bytea);
 	cur_buletine CURSOR FOR SELECT * FROM buletine WHERE crypt IS NULL;
BEGIN
	OPEN cur_buletine;

	LOOP

		FETCH cur_buletine INTO rec;

		EXIT WHEN NOT FOUND;

		UPDATE buletine SET cnp = encrypt(CAST(replace(cnp,'\', '\\') as bytea), byteKeyEnc, 'aes'),
        seria = encrypt(CAST(replace(seria,'\', '\\') as bytea), byteKeyEnc, 'aes'),
        numarul = encrypt(CAST(replace(numarul,'\', '\\') as bytea), byteKeyEnc, 'aes'),
        sectia = encrypt(CAST(replace(sectia,'\', '\\') as bytea), byteKeyEnc, 'aes'),
        eliberat_la_text = encrypt(CAST(eliberat_la_text as bytea), byteKeyEnc, 'aes'),
        expira_la_text = encrypt(CAST(expira_la_text as bytea), byteKeyEnc, 'aes'),
        crypt = 'C' WHERE CURRENT OF cur_buletine;

	END LOOP;
	CLOSE cur_buletine;

END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION crypt_pasapoarte(keyEncryption TEXT) RETURNS void AS $$
DECLARE
	rec record;
	byteKeyEnc bytea := CAST(keyEncryption as bytea);
 	cur_pasapoarte CURSOR FOR SELECT * FROM pasapoarte WHERE crypt IS NULL;
BEGIN
	OPEN cur_pasapoarte;

	LOOP

		FETCH cur_pasapoarte INTO rec;

		EXIT WHEN NOT FOUND;

		UPDATE pasapoarte SET seria = encrypt(CAST(replace(seria,'\', '\\') as bytea), byteKeyEnc, 'aes'),
        tip_act = encrypt(CAST(replace(tip_act,'\', '\\') as bytea), byteKeyEnc, 'aes'),
        eliberat_la_text = encrypt(CAST(eliberat_la_text as bytea), byteKeyEnc, 'aes'),
        expira_la_text = encrypt(CAST(expira_la_text as bytea), byteKeyEnc, 'aes'),
        crypt = 'C' WHERE CURRENT OF cur_pasapoarte;

	END LOOP;
	CLOSE cur_pasapoarte;

END;
$$ LANGUAGE plpgsql;



alter table persoane_fizice enable trigger all;

alter table buletine enable trigger all;

alter table pasapoarte enable trigger all;

alter table clienti enable trigger all;

alter table file_versions enable trigger all;


ALTER TABLE file_versions ADD COLUMN crypt TEXT;

CREATE OR REPLACE FUNCTION encrypt_blob_file_versions(keyEncryption TEXT) RETURNS boolean AS $$
DECLARE
	rec record;
	byteKeyEnc bytea := CAST(keyEncryption as bytea);
	cur_file_v cursor FOR SELECT * FROM file_versions WHERE crypt is null limit 100;
	contor integer;
BEGIN
	OPEN cur_file_v;
	LOOP

		FETCH cur_file_v INTO rec;
		EXIT WHEN NOT FOUND;

		UPDATE file_versions SET bytes = encrypt(rec.bytes, byteKeyEnc, 'aes'), crypt = 'C' WHERE CURRENT OF cur_file_v;
	END LOOP;
	CLOSE cur_file_v;

	SELECT count(*) into contor FROM file_versions WHERE crypt is null;

	IF contor <> 0 THEN
		RETURN TRUE;
	ELSE
		RETURN FALSE;
	END IF;
END;
$$ LANGUAGE plpgsql;




ALTER TABLE persoane_juridice ADD crypt TEXT;

ALTER TABLE persoane_juridice ALTER COLUMN nume SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN cui SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN j SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN adresa SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN oras SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN banca SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN iban SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN punct_lucru SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN telefon_fix SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN fax SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN telefon_mobil SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN email SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN pagina_web SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN persoana_contact SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN observatii SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN analitic_furnizor SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN analitic_client SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN alias SET DATA TYPE TEXT;
ALTER TABLE persoane_juridice ALTER COLUMN grad_risc SET DATA TYPE TEXT;

CREATE OR REPLACE FUNCTION crypt_persoane_juridice(keyEncryption TEXT) RETURNS void AS $$
DECLARE
	rec record;
	byteKeyEnc bytea := CAST(keyEncryption as bytea);
 	cur_persoane CURSOR FOR SELECT * FROM persoane_juridice WHERE crypt IS NULL;
BEGIN
	OPEN cur_persoane;

	LOOP

		FETCH cur_persoane INTO rec;

		EXIT WHEN NOT FOUND;

		UPDATE persoane_juridice SET nume = encrypt(CAST(replace(rec.nume,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        cui = encrypt(CAST(replace(rec.cui,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        j = encrypt(CAST(replace(rec.j,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        adresa = encrypt(CAST(replace(rec.adresa,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        oras = encrypt(CAST(replace(rec.oras,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        banca = encrypt(CAST(replace(rec.banca,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        iban = encrypt(CAST(replace(rec.iban,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        punct_lucru = encrypt(CAST(replace(rec.punct_lucru,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        telefon_fix = encrypt(CAST(replace(rec.telefon_fix,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        fax = encrypt(CAST(replace(rec.fax,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        telefon_mobil = encrypt(CAST(replace(rec.telefon_mobil,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        email = encrypt(CAST(replace(rec.email,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        pagina_web = encrypt(CAST(replace(rec.pagina_web,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        persoana_contact = encrypt(CAST(replace(rec.persoana_contact,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        observatii = encrypt(CAST(replace(rec.observatii,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        analitic_furnizor = encrypt(CAST(replace(rec.analitic_furnizor,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        analitic_client = encrypt(CAST(replace(rec.analitic_client,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        grad_risc = encrypt(CAST(replace(rec.grad_risc,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        alias = encrypt(CAST(replace(rec.alias,'\', '\\') as bytea) ,byteKeyEnc,'aes'),
        crypt = 'C' WHERE CURRENT OF cur_persoane;

	END LOOP;
	CLOSE cur_persoane;

END;
$$ LANGUAGE plpgsql;



create index persoane_fizice__crypt on persoane_fizice(crypt);
create index persoane_juridice__crypt on persoane_juridice(crypt);
create index buletine__crypt on buletine(crypt);
create index pasapoarte__crypt on pasapoarte(crypt);
create index file_versions__crypt on file_versions(crypt);


create table notifications(id serial, imp_level integer, description text, data_add date default current_timestamp, status text,
 user_saw boolean default false, handle_type text, user_can_dismiss boolean default false, notification_type text);

alter table configs alter column default_value set DATA TYPE text;
alter table configs alter column value set DATA TYPE text;

insert into keys ( label ) values ( 'clientPersFizicaRights');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'clientPersFizicaRights'), 'nume#*,prenume#*,data_nasterii_text#*,pagina_web,locul_nasterii#*,data_numelui_text,cetatenie,nationalitate,telefon_fix,fax,telefon_mobil,email,observatii,adresa,oras,table=persoane_fizice,cnp,seria,numarul,table=buletine,seria,tip_act,table=pasapoarte,picture,table=files','nume#*,prenume#*,data_nasterii_text#*,pagina_web,locul_nasterii#*,data_numelui_text,cetatenie,nationalitate,telefon_fix,fax,telefon_mobil,email,observatii,adresa,oras,table=persoane_fizice,cnp,seria,numarul,table=buletine,seria,tip_act,table=pasapoarte,picture,table=files', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'clientPersFizicaRights') ;

insert into keys ( label ) values ('gdprKeyHash');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'gdprKeyHash'), '','', 0);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'gdprKeyHash') ;


alter table rest_incasari alter column comment set data type text;



update configs set value = '2018008' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018009' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018010' where id_key = ( select min(id) from keys where label = 'currentVersion' );

create index persoane_fizice__Status on persoane_fizice(status);

create index clients_groups__idgroup on clients_groups(id_group);

create index clients_groups__idclient on clients_groups(id_client);

alter table notifications add column generator text not null default '';

update configs set value = '2018011' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018012' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018013' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table promotions_elements add column min_qty float not null default 0;

update configs set value = '2018014' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018015' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ('facturareActions');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareActions'), '!check,!pdf,!signedpdf,!email,!sms','!check,!pdf,!signedpdf,!email,!sms', 0);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'facturareActions') ;

insert into keys ( label ) values ('facturareEmailCC');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareEmailCC'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'facturareEmailCC') ;


insert into keys ( label ) values ('facturareEmailSbj');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareEmailSbj'), 'Factura {0} {1} - NumeFirma','Factura {0} {1} - NumeFirma', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'facturareEmailSbj') ;

insert into keys ( label ) values ('facturareEmailBody');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareEmailBody'), 'Buna ziua,<br/>Aveti atasata factura {0} {1}.<br/>{2}Va multumim!<br/>-----<br/>Echipa NumeFirma!','Buna ziua,<br/>Aveti atasata factura {0} {1}.<br/>{2}{3}Va multumim!<br/>-----<br/>Echipa NumeFirma!', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'facturareEmailBody') ;


insert into keys ( label ) values ('facturareEmailSoldLine');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareEmailSoldLine'), '<br/><strong>ATENTIE !! Aveti si un sold anterior de {0}<strong/><br/><br>','<br/><strong>ATENTIE !! Aveti si un sold anterior de {0}<strong/><br/><br>', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'facturareEmailSoldLine') ;

insert into keys ( label ) values ('facturareSmsBody');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareSmsBody'), 'Buna ziua, va informam ca s-a emis factura {0} {1}.{2}Va multumim! - Echipa NumeFirma!','Buna ziua, va informam ca s-a emis factura {0} {1}.{2}Va multumim! - Echipa NumeFirma!', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'facturareSmsBody') ;


insert into keys ( label ) values ('facturareSmsSoldLine');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareSmsSoldLine'), 'ATENTIE !! Aveti si un sold anterior de {0} RON.','ATENTIE !! Aveti si un sold anterior de {0} RON.', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'facturareSmsSoldLine') ;

update configs set value = '2018016' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018017' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018018' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018019' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018020' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ('soldActions');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'soldActions'), '!check,!pdf,!signedpdf,!email,!sms','!check,!pdf,!signedpdf,!email,!sms', 0);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'soldActions') ;

insert into keys ( label ) values ('soldEmailCC');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'soldEmailCC'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'soldEmailCC') ;


insert into keys ( label ) values ('soldEmailSbj');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'soldEmailSbj'), 'Extras de cont inventariere conturi pentru {0} la data {1} - NumeFirma','Extras de cont inventariere conturi pentru {0} la data {1} - NumeFirma', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'soldEmailSbj') ;

insert into keys ( label ) values ('soldEmailBody');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'soldEmailBody'), 'Buna ziua,<br/>Aveti atasat extrasul de cont pentru inventarierea soldurilor pentru societatea {0} , la data de {1} . Sold total {2} . <br/>Va multumim!<br/>-----<br/>Echipa NumeFirma!','Buna ziua,<br/>Aveti atasat extrasul de cont pentru inventarierea soldurilor pentru societatea {0} , la data de {1} . Sold total {2} . <br/>Va multumim!<br/>-----<br/>Echipa NumeFirma!', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'soldEmailBody') ;


insert into keys ( label ) values ('soldSmsBody');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'soldSmsBody'), 'Buna ziua, va informam ca s-a trimis pe email extrasul de cont pentru inventarierea conturilor pentru societatea {0} , la data de {1} . Sold total {2} . Va multumim! - Echipa NumeFirma!','Buna ziua, va informam ca s-a trimis pe email extrasul de cont pentru inventarierea conturilor pentru societatea {0} , la data de {1} . Sold total {2} . Va multumim! - Echipa NumeFirma!', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'soldSmsBody') ;

update configs set value = '2018023' where id_key = ( select min(id) from keys where label = 'currentVersion' );

update configs set value = '2018024' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018025' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018026' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018027' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018028' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018029' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table factura_entries drop constraint fk_factura_entries__loturi;

alter table factura_entries add constraint fk_factura_entries__loturi FOREIGN KEY (id_lot) references loturi(id) on delete cascade;

update configs set value = '2018030' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ('notaPrintZeroPriceFilter');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'notaPrintZeroPriceFilter'),
'NONZERO_ORIGINAL_PRICE','NONZERO_ORIGINAL_PRICE', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'notaPrintZeroPriceFilter') ;

update configs set value = '2018031' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into product_attribute ( label, export_field ) values ( 'Winmentor Cont Serviciu', 'winmentor_cont_serviciu');
insert into mapping_product_attribute (id_product, id_attribute, value) select id, (select id from product_attribute where export_field = 'winmentor_cont_serviciu'), '' from products;

update configs set value = '2018033' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ('winmentorExportFacturiTipCodFurnizor');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'winmentorExportFacturiTipCodFurnizor'),
'id','id', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'winmentorExportFacturiTipCodFurnizor') ;

update configs set value = '2018034' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018035' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018036' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018037' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018038' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018039' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table promotions add column apply_mode text ;

update promotions set apply_mode = 'AUTOMAT' where public_promotion = 'true';
update promotions set apply_mode = 'MANUAL' where public_promotion = 'false';

alter table promotions drop column public_promotion ;

insert into keys ( label ) values ('showSupraPromoButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showSupraPromoButton'),
'0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'showSupraPromoButton') ;


alter table orders add column id_suprapromotie int not null default -1;

alter table sales add column id_suprapromotie int not null default -1;update configs set value = '2018040' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018041' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018042' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018043' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018044' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018045' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018046' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018047' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'societateaNoastraTvaLaIncasare');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraTvaLaIncasare'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'societateaNoastraTvaLaIncasare') ;

update configs set value = '2018048' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018049' where id_key = ( select min(id) from keys where label = 'currentVersion' );

create table hr_functii ( id serial unique,
    denumire text not null,
    cod_cor text);

create table hr_contracte_munca ( id serial unique,
    id_angajat int not null constraint FK_hr_contracte_munca__pers_fizice__angajat references persoane_fizice(id),
    id_companie int not null constraint FK_hr_contracte_munca__pers_juridice references persoane_juridice(id),
    functie int not null constraint FK_hr_contracte_munca__hr_functii references hr_functii(id),
    ore double precision not null,
    contract_tip int not null,
    contract_status int not null,
    tip_program int not null,
    contract_start DATE not null,
    contract_end DATE,
    salariu_brut double precision not null,
    locul_muncii text,
    id_titular int constraint FK_hr_contracte_munca__pers_fizice__titular references persoane_fizice(id),
    contract_numar text);

insert into keys ( label ) values ('gui_hr');
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'gui_hr'), 'Resurse umane');
insert into gui_panels ( id_key, id_parent, classname ) values ( (select min(id) from keys where keys.label = 'gui_hr'), (select id from gui_panels where id_parent = -1), 'ro.atreides.hrSSM.gui.HrTabbedPanel');



insert into keys ( label ) values ('gui_ssm');
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'gui_ssm'), 'SSM');
insert into gui_panels ( id_key, id_parent, classname ) values ( (select min(id) from keys where keys.label = 'gui_ssm'), (select id from gui_panels where id_parent = -1), 'ro.atreides.hrSSM.gui.SSMTabbedPanel');


insert into keys ( label ) values ( 'hrSSMzileSarbatoriLegale');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrSSMzileSarbatoriLegale'),
'01.01,02.01,24.01,17.04,19.04,20.04,01.05,01.06,07.05,08.05,15.08,30.11,01.12,25.12,26.12',
'01.01,02.01,24.01,17.04,19.04,20.04,01.05,01.06,07.05,08.05,15.08,30.11,01.12,25.12,26.12', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'hrSSMzileSarbatoriLegale') ;


insert into keys ( label ) values ( 'group_bo_clear_reminder');
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'group_bo_clear_reminder'), 'Reminder Dismiss');
insert into groups(id_key) values ( (select min(id) from keys where keys.label = 'group_bo_clear_reminder') );


create table hr_history (
 id serial unique,
 id_cm int not null constraint FK_hr_history__hr_contracte_munca references hr_contracte_munca(id) ,
 id_document_generat int not null constraint FK_hr_history__file references files(id),
 dateadded timestamp not null default current_timestamp,
 old_contract text ,
 new_contract text ,
 fillin_texts text ,
 operatie text not null ,
 added_by text not null
 );



alter table hr_contracte_munca add column orar text not null default '';

alter table hr_contracte_munca add column cerere_modificare text not null default '';

alter table hr_contracte_munca add column articol_modificare text not null default '';

alter table hr_contracte_munca add column modificare_start timestamp;

alter table hr_contracte_munca add column modificare_stop timestamp;


alter table hr_history add column crypt text ;



create table reminders (
 id serial unique,
 added_by int not null constraint FK_reminders__users__createdby references users(id) not null ,
 dismissed_by int constraint FK_reminders__users__dismissedby references users(id) ,
 added_at timestamp not null default current_timestamp,
 dismissed_at timestamp,
 trigger_at timestamp not null,
 message text not null,
 status_code int not null
 );


insert into keys ( label ) values ('gui_reminders');
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'gui_reminders'), 'Reminders');
insert into gui_panels ( id_key, id_parent, classname ) values ( (select min(id) from keys where keys.label = 'gui_reminders'), (select id from gui_panels where id_parent = -1), 'ro.atreides.utils.reminders.gui.RemindersReport');


insert into keys ( label ) values ( 'hrSSMzileSarbatoriLegaleFormat');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrSSMzileSarbatoriLegaleFormat'), 'dd.MM', 'dd.MM', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'hrSSMzileSarbatoriLegaleFormat') ;

create table contracte (
 id serial unique,
 contract_type int not null,
 id_furnizor int constraint FK_contracte__clienti_furnizor references clienti(id) ,
 id_client int not null constraint FK_contracte__clienti_client references clienti(id) ,
 id_product int constraint FK_contracte__products references products(id) ,
 quantity double precision not null,
 price_per_unit double precision not null,
 starts_at timestamp not null default current_timestamp,
 expires_at timestamp not null,
 invoice_day_rule text not null,
 renew_days int not null,
 template text not null,
 tip_numar int not null,
 numar_contract text not null);

create table contracte_facturare(
 id serial unique,
 id_contract int constraint FK_contracte_facturare__contracte references contracte(id),
 tip_invoice int not null,
 invoicedate timestamp ,
 invoice_period_start timestamp not null,
 invoice_period_end timestamp not null,
 id_factura int constraint FK_contracte_facturare__facturi_emise references facturi_emise(id) );


insert into keys ( label ) values ('gui_contracte_comerciale');
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'gui_contracte_comerciale'), 'Contracte comerciale');
insert into gui_panels ( id_key, id_parent, classname ) values ( (select min(id) from keys where keys.label = 'gui_contracte_comerciale'), (select id from gui_panels where id_parent = -1), 'ro.atreides.contracte.gui.ContracteReport');

create index hr_history__operatie on hr_history(operatie);


insert into keys ( label ) values ('group_clienti_HR');
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'group_clienti_HR'), 'Clienti HR');
insert into pers_juridice_groups(id_key, status) values ( (select min(id) from keys where keys.label = 'group_clienti_HR') , 1 );

insert into keys ( label ) values ('group_clienti_SSM');
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'group_clienti_SSM'), 'Clienti SSM');
insert into pers_juridice_groups(id_key, status) values ( (select min(id) from keys where keys.label = 'group_clienti_SSM') , 1 );

update configs set value = '2018050' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018051' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018052' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018053' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018054' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018055' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018056' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018057' where id_key = ( select min(id) from keys where label = 'currentVersion' );

create table ssm_tematica_periodica(
 id serial unique,
 month text not null,
 id_client int not null constraint FK_contracte__clienti_client references clienti(id) ,
 instruction_code text not null
);

update configs set value = '2018058' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018059' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018060' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table contracte add column id_valuta int not null default 1;


update configs set value = '2018062' where id_key = ( select min(id) from keys where label = 'currentVersion' );

create extension unaccent;

update configs set value = '2018063' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018064' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018065' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018066' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'hrSSMArticolSuspedareDefault');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrSSMArticolSuspedareDefault'), '', '', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'hrSSMArticolSuspedareDefault') ;


alter table hr_history alter column id_document_generat drop not null ;


insert into keys ( label ) values ( 'hrSSMNotificareSuspendareTextFormat');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrSSMNotificareSuspendareTextFormat'), 'Suspendarea expira: {0} / {1} / c.m. {2} articol {3}', 'Suspendarea expira: {0} / {1} / c.m. {2} articol {3}', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'hrSSMNotificareSuspendareTextFormat') ;

 insert into keys ( label ) values ( 'hrSSMNotificareCustomTextFormat');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrSSMNotificareCustomTextFormat'), '{0}: {1} / {2} / c.m. {3} ', '{0}: {1} / {2} / c.m. {3} ', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'hrSSMNotificareCustomTextFormat') ;

  update configs set value = '2018068' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018069' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018070' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018073' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table registre_numere add column detalii text not null default '';

alter table persoane_juridice drop column idreprezentantlegal;

alter table persoane_juridice add column reprezentant_legal text;

update configs set value = '2018074' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018075' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018076' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'hrSSMTestingMode');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrSSMTestingMode'), '0', '0', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'hrSSMTestingMode') ;

update configs set value = '2018078' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018079' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018080' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018081' where id_key = ( select min(id) from keys where label = 'currentVersion' );

create table hr_pontaj_punch (
id serial unique,
punch_in timestamp not null,
punch_out timestamp not null ,
id_cm int not null constraint FK_HR_PONTAJ__HR_CM references hr_contracte_munca(id));


insert into keys ( label ) values ( 'hrSSMAngajareConsumesRegNo');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrSSMAngajareConsumesRegNo'), '0', '0', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'hrSSMAngajareConsumesRegNo') ;

update configs set value = '2018082' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018083' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'hrSSMRegNoConsumedFor');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrSSMRegNoConsumedFor'), 'act_aditional,incetare,suspendare,!angajare,documentGenerat,incetare_suspendare,!corectie', 'act_aditional,incetare,suspendare,!angajare,documentGenerat,incetare_suspendare,!corectie', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'hrSSMRegNoConsumedFor') ;

update configs set value = '2018084' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018085' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018086' where id_key = ( select min(id) from keys where label = 'currentVersion' );


update hr_contracte_munca set orar = '480/960/60/1,2,3,4,5/09:00/NO/13:00';

update configs set value = '2018089' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018090' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018091' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018092' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018093' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018094' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table hr_history add column use_base64 boolean default false;

update configs set value = '2018095' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table hr_pontaj_punch add column type_ int not null default 1;


create table ssm_catalog_riscuri(
 id serial unique,
 capitol text not null,
 categorie text not null,
 factor_risc text not null
);

create table ssm_personalizare_riscuri(
 id serial unique,
 id_client int not null constraint FK_contracte__clienti_client references clienti(id) ,
 id_risc int not null constraint FK_ssm_personalizare_riscuri__ssm_catalog_riscuri references ssm_catalog_riscuri(id) ,
 active boolean,
 probabilitate float,
 nivel float
);


create table ssm_masuri_preventie(
 id serial unique,
 id_risc_personalizat int not null constraint FK_ssm_masuri_preventie__ssm_personalizare_riscuri references ssm_personalizare_riscuri(id) ,
 tip_masura text not null,
 actiuni_necesare text not null,
 termen_realizare text not null,
 persoana_responsabila text not null
);

update configs set value = '2018096' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018097' where id_key = ( select min(id) from keys where label = 'currentVersion' );


create table hr_contracte_munca_modulations (
	id serial unique,
	id_cm int not null constraint hr_contracte_munca_modulations__hr_contracte_munca references hr_contracte_munca(id),
	modulatie text not null,
	details text,
	start_modulation timestamp not null,
	end_modulation timestamp not null,
	dateadded timestamp not null default current_timestamp );

update configs set value = '2018098' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table hr_contracte_munca add column incepere_munca date ;

update hr_contracte_munca set incepere_munca = contract_Start;

alter table hr_contracte_munca alter column incepere_munca set not null;

update configs set value = '2018099' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'hrRaportLunarFormatAngajat');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrRaportLunarFormatAngajat'), '#{nume}/#{prenume}/#{ciSerie}/#{cnp}/#{ciNumar}/#{adresa} #{oras}/ #{ciEliberatDe} #{ciEliberatLa}', '#{nume}/#{prenume}/#{ciSerie}/#{cnp}/#{ciNumar}/#{adresa} #{oras}/ #{ciEliberatDe} #{ciEliberatLa}', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'hrRaportLunarFormatAngajat') ;

update configs set value = '2018100' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018101' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018102' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table hr_pontaj_punch add column id_parent int ;

alter table hr_pontaj_punch add constraint hr_pontaj_punch_parent foreign key (id_parent) references hr_pontaj_punch(id) on delete cascade ;

update configs set value = '2018103' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018104' where id_key = ( select min(id) from keys where label = 'currentVersion' );

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

create table generic_attributes_definition(
id text unique not null PRIMARY KEY DEFAULT ('' || uuid_generate_v4())
 , name text unique);

insert into generic_attributes_definition(name) values('price');
insert into generic_attributes_definition(name) values('availability');

alter table products add column sync_uuid text not null unique default ('' || uuid_generate_v4()) ;

-- requires POSTGRESQL 10 !!
-- foreach table used with generic attributes, these below must be executed for the new table

--drop table if exists generic_attributes_values_products cascade;

create table generic_attributes_values_products ( id text unique not null PRIMARY KEY DEFAULT ('' || uuid_generate_v4()),
id_attribute text not null,
criteria jsonb ,
value text,
referred_id text not null,
dateadded timestamp not null default CURRENT_TIMESTAMP);

create index ix_gavp__dateadded on generic_attributes_values_products(dateadded);

alter table generic_attributes_values_products add constraint
 fk_gavp__generic_attributes_names foreign key(id_attribute) references generic_attributes_definition(id) on delete cascade;

alter table generic_attributes_values_products add constraint
 fk_gavp__products foreign key(referred_id) references products(sync_uuid) on delete cascade;

CREATE UNIQUE INDEX ix_gavp__unique on generic_attributes_values_products ( id_attribute, referred_id, criteria,dateadded ) ;
-- end here

insert into keys ( label ) values ( 'societateaNoastraPlatitorTva');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraPlatitorTva'), '1', '1', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'societateaNoastraPlatitorTva') ;

create table modificari_pret_new ( id serial unique, referred_price_change_uuid text not null);

alter table modificari_pret_new add constraint
 fk_modificari_pret_new__generic_attributes_values_products foreign key(referred_price_change_uuid) references generic_attributes_values_products(id) on delete cascade;

--drop view if exists price_modifications_view;

create view price_modifications_view as
	select products.id as id_product, products.label as nume_produs_ , gavp_.id as id_pricechange_uuid, gavp_.value as pret_,
	pmn_.id as id_price_modification_ , coalesce( produse_finite.id_stock , -1 ) as id_stock_  , gavp_.dateadded as data_, gavp_.criteria as criteria_
	from generic_attributes_values_products gavp_ inner join generic_attributes_definition gad_ on gavp_.id_attribute = gad_.id
	inner join modificari_pret_new pmn_ on gavp_.id = pmn_.referred_price_change_uuid
	inner join products on products.sync_uuid = gavp_.referred_id
	left outer join produse_finite on products.id = produse_finite.id_product
	where gad_.name = 'price';

-- migrat toate preturile de pe unde sunt ( stocks / produse_Reteta ) , in structuri de genul , cu pastrarea modificarilor de pret ....
-- migram intai toate modificarile de pret de pe stocuri

alter table modificari_pret rename to modificari_pret_old;

alter table stocks rename column sell_price to sell_price_to_migrate;

alter table stocks add column last_updated timestamp ;

update stocks set last_updated = (select max( dateadd) from modificari_pret_old mpo where mpo.id_stock = stocks.id);

update stocks set last_updated = current_timestamp where last_updated is null;


insert into generic_attributes_values_products ( id_attribute, value, referred_id, dateadded, criteria ) 
select
( select id from generic_attributes_definition where name ='price') ,
coalesce(mp.pret_vz, s.sell_price_to_migrate) || '' as pret_vz,
products.sync_uuid,
coalesce(mp.dateadd, s.last_updated) as dateadded_ ,
'{"priceTag":"DEFAULT"}'  
from products inner join produse_finite pf on pf.id_product = products.id inner join stocks s on pf.id_stock = s.id left outer join modificari_pret_old mp on mp.id_stock = s.id
order by products.sync_uuid, dateadded_ ;

-- apoi inseram toate preturile curente, de pe produsele cu reteta / servicii

alter table produse_reteta rename column sell_price to sell_price_to_migrate;

alter table produse_reteta add column last_updated timestamp;

update produse_reteta set last_updated = current_timestamp;


insert into generic_attributes_values_products ( id_attribute, value, referred_id, dateadded, criteria ) 
select
( select id from generic_attributes_definition where name ='price') ,
pr.sell_price_to_migrate || '', p.sync_uuid, pr.last_updated, '{"priceTag":"DEFAULT"}' from produse_Reteta pr inner join products p on pr.id_product = p.id;


-- inseram modificari de pret pt toate preturile existente 

insert into modificari_pret_new (referred_price_change_uuid) select gavp_.id from generic_attributes_values_products gavp_ inner join generic_attributes_definition gad_ on gavp_.id_attribute = gad_.id where gad_.name = 'price' ;

-- drop view if exists current_price_for_products;

create view current_price_for_products as
select id_product, nume_produs_, pret_, id_stock_, price_date_, criteria_ from (
	select products.id as id_product, products.label as nume_produs_ , gavp_.value as pret_, gavp_.dateadded as price_date_, gavp_.criteria as criteria_,
	coalesce( produse_finite.id_stock , -1 ) as id_stock_ , rank() over (partition by id_product, criteria order by dateadded desc) as pos 
	from generic_attributes_values_products gavp_ inner join generic_attributes_definition gad_ on gavp_.id_attribute = gad_.id
	inner join modificari_pret_new pmn_ on gavp_.id = pmn_.referred_price_change_uuid
	inner join products on products.sync_uuid = gavp_.referred_id
	left outer join produse_finite on products.id = produse_finite.id_product
	where gad_.name = 'price' and gavp_.dateadded < current_timestamp  
	) as ss where pos <=1 ;


alter table stocks alter column sell_price_to_migrate drop not null;

alter table produse_reteta alter column sell_price_to_migrate drop not null;


delete from documente where id_modif_pret > 0;

alter table documente drop constraint fk_documente_modif_pret;

alter table documente add constraint fk_documente_modif_pret_nou foreign key (id_modif_pret) references modificari_pret_new (id) ;



insert into documente (time_document, nume, id_modif_pret, status, fiscal) select gavp.dateadded, 'Modificare pret ' || mpn.id , mpn.id, 1, 1 from modificari_pret_new mpn inner join generic_attributes_values_products gavp on mpn.referred_price_change_uuid = gavp.id;



create view default_price_for_products as 
select id_product, nume_produs_, pret_, id_stock_, price_date_ from current_price_for_products where criteria_ = '{"priceTag":"DEFAULT"}' ;


update configs set value = '2018105' where id_key = ( select min(id) from keys where label = 'currentVersion' );






insert into keys ( label ) values ( 'societateaNoastraPlatitorTva');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraPlatitorTva'), '1', '1', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'societateaNoastraPlatitorTva') ;


update configs set value = '2018105' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table stocks alter column sell_price_to_migrate drop not null;

alter table produse_reteta alter column sell_price_to_migrate drop not null;


create table price_tags ( id text unique not null PRIMARY KEY DEFAULT ('' || uuid_generate_v4()), name text not null unique);

CREATE UNIQUE INDEX ix_price_tags__unique on price_tags(name);

alter table locations add column price_tag text ;

alter table locations add constraint
 fk_locations__price_tags foreign key(price_tag) references price_tags(name) ;

alter table magazii add column price_tag text ;

alter table magazii add constraint
 fk_magazii__price_tags foreign key(price_tag) references price_tags(name) ;


insert into keys ( label ) values ( 'touch6TransferAvailableTypes');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'touch6TransferAvailableTypes'),
'TRANSFER_INTERN_CU_AVIZ_SI_NIR,TRANSFER_INTERN_FARA_PRINT,TRANSFER_INTERN_PRINT_PRET_ACZ,TRANSFER_INTERN_PRINT_PRET_VZ,TRANSFER_INTERN_PRINT_PRET_ACZ_VZ,AVIZ_INTERN_PRET_ACZ,AVIZ_INTERN_PRET_VZ,AVIZ_EXTERN_PRET_VZ',
'TRANSFER_INTERN_CU_AVIZ_SI_NIR,TRANSFER_INTERN_FARA_PRINT,TRANSFER_INTERN_PRINT_PRET_ACZ,TRANSFER_INTERN_PRINT_PRET_VZ,TRANSFER_INTERN_PRINT_PRET_ACZ_VZ,AVIZ_INTERN_PRET_ACZ,AVIZ_INTERN_PRET_VZ,AVIZ_EXTERN_PRET_VZ', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'touch6TransferAvailableTypes') ;


insert into keys ( label ) values ( 'facturareExportXlsFileDirectory');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'facturareExportXlsFileDirectory'), 'export_facturi','export_facturi', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'facturareExportXlsFileDirectory') ;



insert into keys ( label ) values ( 'transferDetaliiLotFormat');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'transferDetaliiLotFormat'), '"LOT NR #{lotnr}, Expira la #{lotexpira}, Codbare #{lotbarcode}"',
'"LOT NR #{lotnr}, Expira la #{lotexpira}, Codbare #{lotbarcode}"', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'transferDetaliiLotFormat') ;

insert into keys ( label ) values ( 'saleByLotOnly');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'saleByLotOnly'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'saleByLotOnly') ;

update configs set value = '2018107' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018108' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018109' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018110' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018111' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table fisa_magazie add column uuid text not null default ('' || uuid_generate_v4()) ;

drop view fise_active;
create view fise_active as
    select
    fisa_magazie.id as id_fisa,
    fisa_magazie.time_document as add_time,
    fisa_magazie.type_document as type_document, 
    fisa_magazie.uuid as uuid,
    id_stock,
    id_magazie,
    id_lot,
    fisa_magazie.buy_price as buy_price,
    fisa_magazie.cod_bare as cod_bare,
    fisa_magazie.denumire_la_furnizor as denumire_la_furnizor,
     intrata
        - coalesce(( select sum(quantity) from stock_consum where id_fisa_sursa = fisa_magazie.id ) , 0)
      as disponibil,
      magazii.label as nume_magazie
     from fisa_magazie inner join magazii on fisa_magazie.id_magazie = magazii.id
    where intrata > 0;

alter table sales add column uuid_fisa_activa text ;

alter table factura_entries add column uuid text not null default ('' || uuid_generate_v4()) ;

update configs set value = '2018112' where id_key = ( select min(id) from keys where label = 'currentVersion' );


create index fisa_magazie__uuid on fisa_magazie(uuid);

create index factura_entries__uuid on fisa_magazie(uuid);



drop view if exists price_modifications_view;

create view price_modifications_view as
        select products.id as id_product, products.label as nume_produs_ , gavp_.id as id_pricechange_uuid, gavp_.value as pret_,
        pmn_.id as id_price_modification_ , coalesce( produse_finite.id_stock , -1 ) as id_stock_  , gavp_.dateadded as data_, gavp_.criteria as criteria_, gavp_.criteria->>'priceTag' as priceTag
        from generic_attributes_values_products gavp_ inner join generic_attributes_definition gad_ on gavp_.id_attribute = gad_.id
        inner join modificari_pret_new pmn_ on gavp_.id = pmn_.referred_price_change_uuid
        inner join products on products.sync_uuid = gavp_.referred_id
        left outer join produse_finite on products.id = produse_finite.id_product
        where gad_.name = 'price';

update configs set value = '2018113' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table hr_contracte_munca add column orar_nou text;

update configs set value = '2018114' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018115' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018116' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table fisa_magazie alter column uuid drop not null;


insert into keys ( label ) values ( 'productieInTags');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'productieInTags'), 'productieIN','productieIN', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'productieInTags') ;

insert into keys ( label ) values ( 'productieOutTags');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'productieOutTags'), 'productieOUT','productieOUT', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'productieOutTags') ;

insert into keys ( label ) values ( 'productieByLotOnly');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'productieByLotOnly'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'productieByLotOnly') ;

update configs set value = '2018118' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018120' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018121' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table transfer_items add column fisa_sursa_uuid text;

update configs set value = '2018122' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018123' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018124' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018125' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018126' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'bonFiscalPrintCodFiscalAsComment');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'bonFiscalPrintCodFiscalAsComment'), '0','0', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
    and id_key = (select min(id) from keys where keys.label = 'bonFiscalPrintCodFiscalAsComment') ;


update configs set value = '2018127' where id_key = ( select min(id) from keys where label = 'currentVersion' );

-- vezi ce stocuri vor fi afectate ( nu se va descarca stocul la ele ! )
--
-- select * from stocks where id in ( select id_Stock from produse_finite where status = 2 and id_product in ( select id_product from sales ) );

delete from produse_finite where status = 2;

alter table produse_finite drop column status;

update configs set value = '2018128' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'alegeLotFormat');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'alegeLotFormat'),
'LOT/PN #{nr} = Expira #{expira} #{furnizor}',
'LOT/PN #{nr} = Expira #{expira} #{furnizor}', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'alegeLotFormat') ;

insert into keys ( label ) values ('showIncasatButton');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'showIncasatButton'),
'1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'showIncasatButton') ;


update configs set value = '2018129' where id_key = ( select min(id) from keys where label = 'currentVersion' );


drop view fise_active;

alter table fisa_magazie drop column uuid;

alter table factura_entries drop column uuid;

alter table transfer_items drop column fisa_sursa_uuid;

alter table sales drop column uuid_fisa_activa;

create view fise_active as
    select
    fisa_magazie.id as id_fisa,
    fisa_magazie.time_document as add_time,
    fisa_magazie.type_document as type_document,
    id_stock,
    id_magazie,
    id_lot,
    fisa_magazie.buy_price as buy_price,
    fisa_magazie.cod_bare as cod_bare,
    fisa_magazie.denumire_la_furnizor as denumire_la_furnizor,
     intrata
        - coalesce(( select sum(quantity) from stock_consum where id_fisa_sursa = fisa_magazie.id ) , 0)
      as disponibil,
      magazii.label as nume_magazie
     from fisa_magazie inner join magazii on fisa_magazie.id_magazie = magazii.id
    where intrata > 0;


drop view if exists default_price_for_products ;

create view default_price_for_products as
select id_product, nume_produs_, pret_, id_stock_, price_date_ from current_price_for_products where criteria_ = '{"priceTag":"DEFAULT"}' ;




drop table if exists ssm_tematica_periodica;

create table ssm_tematica_periodica(
 id serial unique,
 month text not null,
 id_client int not null constraint FK_contracte__clienti_client references clienti(id) ,
 instruction_code text not null
);


drop table if exists ssm_masuri_preventie;

CREATE TABLE ssm_masuri_preventie (
    id serial unique,
    id_client integer NOT NULL CONSTRAINT fk_ssm_masuri_preventie__clienti  REFERENCES clienti(id),
    id_risc integer NOT NULL CONSTRAINT fk_ssm_masuri_preventie__ssm_catalog_riscuri REFERENCES ssm_catalog_riscuri(id),
    tip_masura text NOT NULL,
    actiuni_necesare text NOT NULL,
    termen_realizare text NOT NULL,
    persoana_responsabila text NOT NULL
);


update configs set value = '2018130' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018131' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018132' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018133' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table loturi add column parametri jsonb ;

create table productie_process_instance (
    id serial unique ,
    template_name text not null,
    dateadded timestamp not null default current_timestamp,
    datefinished timestamp,
    details text,
    process_kpi jsonb,
    state integer not null,
    id_produs_reteta integer CONSTRAINT fk_productie_process_instance__produse_Reteta REFERENCES produse_Reteta(id),
    id_magazie integer NOT NULL CONSTRAINT fk_productie_process_items__magazii REFERENCES magazii(id)
    );

create table productie_process_input_items (
    id serial unique ,
    id_productie_instance integer NOT NULL CONSTRAINT fk_productie_process_items__productie_process_instance REFERENCES productie_process_instance(id),
    id_lot_input integer not null,
    id_stock integer not null,
    qty float not null,
    dateadded timestamp not null default current_timestamp
    );

create table productie_process_output_items (
    id serial unique ,
    id_productie_instance integer NOT NULL CONSTRAINT fk_productie_process_items__productie_process_instance REFERENCES productie_process_instance(id),
    id_lot_output integer,
    temp_item_kpi jsonb,
    id_stock integer not null,
    qty float not null,
    dateadded timestamp not null default current_timestamp,
    processed boolean default false
    );

alter table productie_process_instance add column productie_number integer ;

update configs set value = '2018134' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018135' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018136' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018137' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018138' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018139' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018140' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table ssm_tematica_periodica add column grupa text ;

update configs set value = '2018141' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018142' where id_key = ( select min(id) from keys where label = 'currentVersion' );


update configs set value = '2018143' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018144' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018145' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018146' where id_key = ( select min(id) from keys where label = 'currentVersion' );



insert into keys ( label ) values ( 'contaExportUiPathIesiriFallbackMagazieCod');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportUiPathIesiriFallbackMagazieCod'), '4','4', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'contaExportUiPathIesiriFallbackMagazieCod') ;


insert into keys ( label ) values ( 'contaExportUiPathGrupeazaIesiri');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportUiPathGrupeazaIesiri'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'contaExportUiPathGrupeazaIesiri') ;


insert into keys ( label ) values ( 'contaExportUiPathLineTerminator');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportUiPathLineTerminator'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'contaExportUiPathLineTerminator') ;


alter table tip_articol add column cont_debit text not null default '';


insert into keys ( label ) values ( 'contaExportUiPathExportMonetarFilenameGrupareKey');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportUiPathExportMonetarFilenameGrupareKey'),
  '#{tipplata}=#{nrdoc} #{locatie} #{tipplata} #{data}','#{tipplata}=#{nrdoc} #{locatie} #{tipplata} #{data}', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'contaExportUiPathExportMonetarFilenameGrupareKey') ;


create table conta_exported_clients ( id serial unique, id_client int not null constraint FK_CONTA_EXPORTED_CLIENTS__CLIENTI references clienti(id), exportdate timestamp not null default current_timestamp);

alter table conta_exported_clients add column fields_at_export text not null default '';

create index conta_exported_clients__exportdate on conta_exported_clients(exportdate);

update tip_articol set cont_debit = '707' where cont = '371';
update tip_articol set cont_debit = '708' where cont = '381';
update tip_articol set cont_debit = '704' where cont = '704';

update configs set value = '2018147' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018148' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'societateaNoastraPlatitorTva');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraPlatitorTva'), '1', '1', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'societateaNoastraPlatitorTva') ;

insert into keys ( label ) values ( 'hrArticoleSomajTehnic');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'hrArticoleSomajTehnic'), '52(1) LIT C),52(1) lit c),52 (1) lit c)', '52(1) LIT C),52(1) lit c),52 (1) lit c)', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'hrArticoleSomajTehnic') ;

update configs set value = '2018149' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into product_attribute ( label, export_field ) values ( 'ZileExpirare', 'zile_expirare');
insert into mapping_product_attribute (id_product, id_attribute, value) select id, (select id from product_attribute where export_field = 'zile_expirare'), '-1' from products;

update configs set value = '2018150' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018151' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'cantarDibalLineFormat');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'cantarDibalLineFormat'),
    '#{position}.#{productcode}.#{productname}.#{price}.#{zileexpirare}.#{ingrediente}',
    '#{position}.#{productcode}.#{productname}.#{price}.#{zileexpirare}.#{ingrediente}',
     1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'cantarDibalLineFormat') ;

update configs set value = '2018152' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018153' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018154' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ('agedDebtorsSmsSoldLine');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'agedDebtorsSmsSoldLine'),
'Buna ziua , Va informam ca in evidentele noastre figurati cu o datorie de {0} RON. Va rugam achitati in cel mai scurt timp posibil ! Va multumim ! Echipa DeverSoft !',
'Buna ziua , Va informam ca in evidentele noastre figurati cu o datorie de {0} RON. Va rugam achitati in cel mai scurt timp posibil ! Va multumim ! Echipa DeverSoft !', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'agedDebtorsSmsSoldLine') ;

update configs set value = '2018155' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018156' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018157' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018158' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table droid_sms drop column retrycount;

update configs set value = '2018159' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'raportNoteFirstColumnFormat');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'raportNoteFirstColumnFormat'), '#{orderdetails},#{user},#{total}','#{orderdetails},#{user},#{total}', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'raportNoteFirstColumnFormat') ;


insert into keys ( label ) values ( 'raportMonetarFirstColumnFormat');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'raportMonetarFirstColumnFormat'), '#{orderid},#{user},#{total}','#{orderid},#{user},#{total}', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'raportMonetarFirstColumnFormat') ;

update configs set value = '2018160' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018161' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table tip_regcasa add column iban text not null default '';

alter table tip_regcasa add column bank_internal_account_number text not null default '';

-- if needed, with backup : alter table persoane_juridice alter column iban set data type text;

alter table regcasa add column transaction_ref_unique_per_account text not null default '';


insert into keys ( label ) values ( 'extrasContFacturiClientiBeanFormat');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'extrasContFacturiClientiBeanFormat'),
    '<html><font color="blue">#{partener}</font> #{type} <font color="red">#{valoare} RON</font><br>#{detalii} IBAN #{iban}</html>',
    '<html><font color="blue">#{partener}</font> #{type} <font color="red">#{valoare} RON</font><br>#{detalii} IBAN #{iban}</html>', 1);

insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'extrasContFacturiClientiBeanFormat') ;

create index regcasa__transaction_ref_unique_per_account on regcasa(transaction_ref_unique_per_account);


alter table facturi_emise add column cod_facturare text not null default '';

update facturi_emise set cod_facturare = serie_factura || numar;

CREATE INDEX ix__facturi_emise__cod_facturare on facturi_emise ( cod_facturare ) ;

update configs set value = '2018163' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ('group_payment_gateway');
insert into messages ( id_lang, id_key, label ) values ( 1, (select min(id) from keys where keys.label = 'group_payment_gateway'), 'Gateway plati');
insert into pers_juridice_groups(id_key, status) values ( (select min(id) from keys where keys.label = 'group_payment_gateway') , 1 );

update configs set value = '2018164' where id_key = ( select min(id) from keys where label = 'currentVersion' );




update tip_articol set cont_debit = '707' where cont = '371';
update tip_articol set cont_debit = '708' where cont = '381';

insert into tip_articol ( cod, nume, cont, cont_debit) values ( 3, 'Servicii vandute', '', '704');
insert into tip_articol ( cod, nume, cont, cont_debit) values ( 4, 'Produse finite', '', '7015');

update configs set value = '2018165' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018166' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018167' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018168' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table produse_reteta add column id_tip_articol int constraint FK_produse_reteta__tip_articol references tip_articol(id) not null default 1;
update produse_reteta set id_tip_articol = ( select id from tip_articol where nume = 'Produse finite' ) ;


update configs set value = '2018169' where id_key = ( select min(id) from keys where label = 'currentVersion' );



CREATE TABLE note_contabile
(
   id serial unique,
   data timestamp not null,
   suma float8 not null,
   cont_creditor text,
   cont_debitor text,
   comentarii text,
   document_id int not null,
   document_item_id int,
   regdoc_id int
);

alter table note_contabile add constraint  FK_note_contabile__documente Foreign Key (regdoc_id) references documente(id) on delete cascade;


alter table tip_articol alter column cont set data type text;

alter table tip_articol alter column cont_debit set data type text;

alter table documente add column id_docregcasa int constraint FK_documente__regcasa references regcasa(id) ;


alter table contracte add column contract_status int not null default 2;

update configs set value = '2018171' where id_key = ( select min(id) from keys where label = 'currentVersion' );

update configs set value = '2018172' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018173' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018174' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018175' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018176' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'contaExportUiPathGrupeazaIntrari');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportUiPathGrupeazaIntrari'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'contaExportUiPathGrupeazaIntrari') ;

update configs set value = '2018177' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018178' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018179' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018180' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018181' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'contaExportUiPathDenumireCasaMonetar');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'contaExportUiPathDenumireCasaMonetar'), 'CASA #{location}','CASA #{location}', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'contaExportUiPathDenumireCasaMonetar') ;

update configs set value = '2018182' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018183' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018184' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018185' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018186' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018187' where id_key = ( select min(id) from keys where label = 'currentVersion' );


insert into keys ( label ) values ( 'societateaNoastraPlatitorTVA');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraPlatitorTVA'), '1','1', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'societateaNoastraPlatitorTVA') ;


insert into keys ( label ) values ( 'societateaNoastraCodTara');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraCodTara'), 'RO','RO', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'societateaNoastraCodTara') ;

insert into keys ( label ) values ( 'societateaNoastraCodJudet');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraCodJudet'), 'RO-B','RO-B', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'societateaNoastraCodJudet') ;


insert into keys ( label ) values ( 'societateaNoastraOras');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraOras'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'societateaNoastraOras') ;

insert into keys ( label ) values ( 'societateaNoastraStrada');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraStrada'), '','', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'societateaNoastraStrada') ;


create table judete(id serial unique, id_tara int not null references tari(id) , nume text not null, code text ) ;

insert into judete(id_tara,nume, code) values (1, 'Bucuresti','RO-B');
insert into judete(id_tara,nume, code) values (1, 'Alba','RO-AB');
insert into judete(id_tara,nume, code) values (1, 'Arad','RO-AR');
insert into judete(id_tara,nume, code) values (1, 'Arges','RO-AG');
insert into judete(id_tara,nume, code) values (1, 'Bacau','RO-BC');
insert into judete(id_tara,nume, code) values (1, 'Bihor','RO-BH');
insert into judete(id_tara,nume, code) values (1, 'Bistrita-Nasaud','RO-BN');
insert into judete(id_tara,nume, code) values (1, 'Botosani','RO-BT');
insert into judete(id_tara,nume, code) values (1, 'Braila','RO-BR');
insert into judete(id_tara,nume, code) values (1, 'Brasov','RO-BV');
insert into judete(id_tara,nume, code) values (1, 'Buzau','RO-BZ');
insert into judete(id_tara,nume, code) values (1, 'Caras-Severin','RO-CS');
insert into judete(id_tara,nume, code) values (1, 'Calarasi','RO-CL');
insert into judete(id_tara,nume, code) values (1, 'Cluj','RO-CJ');
insert into judete(id_tara,nume, code) values (1, 'Constanta','RO-CT');
insert into judete(id_tara,nume, code) values (1, 'Covasna','RO-CV');
insert into judete(id_tara,nume, code) values (1, 'Dambovita','RO-DB');
insert into judete(id_tara,nume, code) values (1, 'Dolj','RO-DJ');
insert into judete(id_tara,nume, code) values (1, 'Galati','RO-GL');
insert into judete(id_tara,nume, code) values (1, 'Giurgiu','RO-GR');
insert into judete(id_tara,nume, code) values (1, 'Gorj','RO-GJ');
insert into judete(id_tara,nume, code) values (1, 'Harghita','RO-HR');
insert into judete(id_tara,nume, code) values (1, 'Hunedoara','RO-HD');
insert into judete(id_tara,nume, code) values (1, 'Ialomita','RO-IL');
insert into judete(id_tara,nume, code) values (1, 'Iasi','RO-IS');
insert into judete(id_tara,nume, code) values (1, 'Ilfov','RO-IF');
insert into judete(id_tara,nume, code) values (1, 'Maramures','RO-MM');
insert into judete(id_tara,nume, code) values (1, 'Mehedinti','RO-MH');
insert into judete(id_tara,nume, code) values (1, 'Mures','RO-MS');
insert into judete(id_tara,nume, code) values (1, 'Neamt','RO-NT');
insert into judete(id_tara,nume, code) values (1, 'Olt','RO-OT');
insert into judete(id_tara,nume, code) values (1, 'Prahova','RO-PH');
insert into judete(id_tara,nume, code) values (1, 'Salaj','RO-SJ');
insert into judete(id_tara,nume, code) values (1, 'Satu Mare','RO-SM');
insert into judete(id_tara,nume, code) values (1, 'Sibiu','RO-SB');
insert into judete(id_tara,nume, code) values (1, 'Suceava','RO-SV');
insert into judete(id_tara,nume, code) values (1, 'Teleorman','RO-TR');
insert into judete(id_tara,nume, code) values (1, 'Timis','RO-TM');
insert into judete(id_tara,nume, code) values (1, 'Tulcea','RO-TL');
insert into judete(id_tara,nume, code) values (1, 'Valcea','RO-VL');
insert into judete(id_tara,nume, code) values (1, 'Vaslui','RO-VS');
insert into judete(id_tara,nume, code) values (1, 'Vrancea','RO-VN');

alter table persoane_juridice add column id_judet int not null defaULT 1;

alter table persoane_fizice add column id_judet int not null defaULT 1;

alter table orders alter COLUMN external_id type text;

alter table facturi_emise add column detalii_plata text not null default '';

update configs set value = '2018191' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018192' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018193' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table clienti add column use_new_address_system boolean not null default false;

create table clienti_adrese (
 id serial unique,
 id_client int not null constraint FK_clienti_adrese__clienti references clienti(id) ,
 purpose text not null,
 label text not null,
 body text not null);

create unique index clienti_adrese__unique_index on clienti_adrese ( purpose, label, id_client ) ;

ALTER TABLE clienti_adrese ADD CONSTRAINT unique_purpose_label_client  UNIQUE USING INDEX clienti_adrese__unique_index;

insert into keys ( label ) values ( 'structuredDeliveryAddressToStringFormat');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'structuredDeliveryAddressToStringFormat'),
 '#{STREETTYPE} #{STREETNAME}, nr #{HOUSENO}, #{BUILDINGTYPE} #{BUILDINGNO} , sc #{ENTRANCE} , etaj #{FLOOR}, ap. #{APARTMENTNO}, interfon #{ACCESSCODE} , Oras #{CITY}, instructiuni: #{INSTRUCTIONS}',
  '#{STREETTYPE} #{STREETNAME}, nr #{HOUSENO}, #{BUILDINGTYPE} #{BUILDINGNO} , sc #{ENTRANCE} , etaj #{FLOOR}, ap. #{APARTMENTNO}, interfon #{ACCESSCODE} , Oras #{CITY}, instructiuni: #{INSTRUCTIONS}', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
 and id_key = (select min(id) from keys where keys.label = 'structuredDeliveryAddressToStringFormat') ;


alter table clienti_adrese add column last_updated timestamp not null default current_timestamp;

alter table clienti_adrese add column crypt text;


insert into keys ( label ) values ( 'clientSaveWarnAtUnstructuredAddresses');
 insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'clientSaveWarnAtUnstructuredAddresses'), '0','0', 1);
 insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
  and id_key = (select min(id) from keys where keys.label = 'clientSaveWarnAtUnstructuredAddresses') ;


update configs set value = '2018194' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018195' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018197' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into keys ( label ) values ( 'societateaNoastraIbanEfactura');
insert into configs ( id_key , value, default_value, visibility ) values ((select min(id) from keys where keys.label = 'societateaNoastraIbanEfactura'), '','IBAN1=BANCA1,IBAN2=BANCA2', 1);
insert into mapping_config_groups (id_config, id_group) select min(configs.id), min(config_groups.id) from config_groups, configs where config_groups.name = 'all'
and id_key = (select min(id) from keys where keys.label = 'societateaNoastraIbanEfactura') ;

alter table facturi_emise add column efactura_reference text not null default '';
create index facturi_emise__efactura_reference on facturi_emise(efactura_reference);

alter table facturi_emise add column efactura_status int not null default 0;
create index facturi_emise__efactura_status on facturi_emise(efactura_status);

update configs set value = '2018202' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018203' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018204' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018205' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018206' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018207' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018208' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table contracte add column invoice_line2_format text not null default 'Cf. ctr. #{contract} (#{details} #{note}) : #{period}';
update configs set value = '2018209' where id_key = ( select min(id) from keys where label = 'currentVersion' );


alter table contracte add column billable_items json ;

update contracte set billable_items = cast('[{"productId":"' || id_product || '", "valutaId":"' || id_valuta || '" , "price":"' || price_per_unit || '", "note":""}]' as json);

alter table contracte add column first_invoice timestamp;

update contracte set first_invoice = TO_TIMESTAMP(invoice_day_rule || '.2022', 'DD.MM.YYYY');

-- alter table contracte drop column invoice_day_rule;


alter table contracte alter column quantity drop not null;

alter table contracte alter column price_per_unit drop not null;

alter table contracte alter column id_product drop not null;

alter table contracte alter column invoice_day_rule drop not null;

update configs set value = '2018210' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table contracte add column contract_details text not null default '';

update configs set value = '2018211' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018212' where id_key = ( select min(id) from keys where label = 'currentVersion' );

alter table contracte add column nr_puncte_lucru integer not null default 1;

update configs set value = '2018213' where id_key = ( select min(id) from keys where label = 'currentVersion' );

create table external_codes(id serial unique,
    resource_type text not null,
    local_resource int not null,
    external_source text not null,
    code text not null ) ;


create index external_codes__resource_type on external_codes(resource_type);
create index external_codes__local_resource on external_codes(local_resource);
create index external_codes__external_source on external_codes(external_source);
create index external_codes__code on external_codes(code);

ALTER TABLE external_codes ADD CONSTRAINT external_codes__upsert UNIQUE (resource_type, local_resource, external_source);

alter table categories add column last_updated timestamp not null default current_timestamp;

alter table subcategories add column last_updated timestamp not null default current_timestamp;

update configs set value = '2018214' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018215' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018216' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018217' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018218' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018219' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018220' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018221' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018222' where id_key = ( select min(id) from keys where label = 'currentVersion' );

insert into product_attribute ( label, export_field ) values ( 'CodCentruProfit', 'cod_centru_profit');
insert into mapping_product_attribute (id_product, id_attribute, value) select id, (select id from product_attribute where export_field = 'cod_centru_profit'), '' from products;

update configs set value = '2018223' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018224' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018225' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '' where id_key = ( select min(id) from keys where label = 'currentVersion' );
update configs set value = '2018227' where id_key = ( select min(id) from keys where label = 'currentVersion' );
