#
# Atualização do banco de dados necessária para a feature/GC-214
#

drop database if exists test;
create database test;
use test;
create table result (
  queries varchar(255) default ''
);
drop procedure if exists update_databases;

delimiter //

create procedure update_databases()
begin
  declare database_name char(255);
  declare done_cursor_databases int default 0;
  declare done_update_cursor_databases int default 0;
  declare cursor_databases cursor for select schema_name from information_schema.schemata group by schema_name;
  declare continue handler for not found set done_cursor_databases = 1;
  -- declare continue handler for sqlstate '42S21' SET @x = 1;
  -- declare continue handler for sqlstate '42000' SET @x = 1;

  open cursor_databases;

  filter_cursor: loop
    fetch cursor_databases into database_name;

    if done_cursor_databases then
      leave filter_cursor;
    end if;

    if substr(database_name, 1, 7) = 'g3nesis' and database_name <> 'g3nesis_v2' then
      set @database_name = database_name;

      set @sql_query = concat('DROP TABLE IF EXISTS ', @database_name, '.g3n_email_templates;');
      prepare delete_template from @sql_query;
      execute delete_template;
      deallocate prepare delete_template;

      set @sql_query = concat(
        'CREATE TABLE IF NOT EXISTS ', @database_name, '.g3n_email_templates (',
          '`id` int(11) NOT NULL AUTO_INCREMENT,',
          '`name` varchar(255) NOT NULL,',
          '`subject` varchar(255) NOT NULL,',
          '`html` text NOT NULL,',
          '`email_variable` varchar(15) DEFAULT NULL,',
          '`json` text NOT NULL,',
          '`deleted` int(1) NOT NULL DEFAULT 0,',
          '`created_at` datetime default CURRENT_TIMESTAMP,',
          '`last_update` datetime default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,',
          'PRIMARY KEY (`id`),',
          'UNIQUE KEY(`name`)',
        ') ENGINE=InnoDB;'
      );
      insert into test.result (queries) value (@sql_query);
      prepare create_template from @sql_query;
      execute create_template;
      deallocate prepare create_template;
    end if;
  end loop;

  close cursor_databases;
end;
//

delimiter ;
call test.update_databases;
select * from test.result;
drop procedure if exists update_databases;
drop database test;