-- if changing DB name from utilzz to something else - change ALL utilzz references in this file
-- use utilzz;

drop view if exists routines_drop;

create view routines_drop as
select 
    r.ROUTINE_SCHEMA as db,
    r.ROUTINE_TYPE as type,
    r.ROUTINE_NAME as name,
    concat('drop ', r.ROUTINE_TYPE, ' ', r.ROUTINE_SCHEMA, '.', r.ROUTINE_NAME, ';') as stmt
from information_schema.ROUTINES r;


drop view if exists views_drop;

create view views_drop as
select
    v.TABLE_SCHEMA as db,
    v.TABLE_NAME as name,
    concat('drop view ', v.TABLE_SCHEMA, '.', v.TABLE_NAME, ';') as stmt
from information_schema.VIEWS v;


drop function if exists fk_dump;

delimiter $$
create function fk_dump(schema_ varchar(255)) returns varchar(255)
deterministic
begin
    create temporary table if not exists utilzz._fk_dump(
        db varchar(64) not null default '',
        name varchar(64) default null,
        ref_db varchar(64) default null,
        ref_name varchar(64) default null,
        fk_name varchar(64) not null default '',
        create_stmt varchar(1024) default null,
        drop_stmt varchar(1024) default null,
        primary key(db, fk_name)
    );

    delete from utilzz._fk_dump where db = schema_;

    insert utilzz._fk_dump(
        db,
        name,
        ref_db,
        ref_name,
        fk_name,
        create_stmt,
        drop_stmt)
    select 
        kcu.TABLE_SCHEMA db, 
        kcu.TABLE_NAME name,
        kcu.REFERENCED_TABLE_SCHEMA ref_db, 
        kcu.REFERENCED_TABLE_NAME ref_name,
        kcu.CONSTRAINT_NAME fk_name,
        concat(
            'ALTER TABLE ',
            kcu.TABLE_NAME,
            ' ADD CONSTRAINT ',
            kcu.CONSTRAINT_NAME,
            ' FOREIGN KEY (',
            group_concat(kcu.COLUMN_NAME order by kcu.ORDINAL_POSITION separator ', '),
            ') REFERENCES ',
            kcu.REFERENCED_TABLE_NAME,
            ' (',
            group_concat(kcu.REFERENCED_COLUMN_NAME order by kcu.POSITION_IN_UNIQUE_CONSTRAINT separator ', '),
            ') ON UPDATE ',
            rc.UPDATE_RULE,
            ' ON DELETE ',
            rc.DELETE_RULE,
            ';') create_stmt,
        concat(
            'ALTER TABLE ',
            kcu.TABLE_NAME,
            ' DROP FOREIGN KEY ',
            kcu.CONSTRAINT_NAME,
            ';') drop_stmt

    from information_schema.key_column_usage kcu
    join information_schema.referential_constraints rc on 
        rc.CONSTRAINT_SCHEMA = schema_
        and kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    where kcu.REFERENCED_TABLE_NAME is not null
        and kcu.TABLE_SCHEMA = schema_
    group by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.REFERENCED_TABLE_SCHEMA, kcu.REFERENCED_TABLE_NAME, kcu.CONSTRAINT_NAME
    on duplicate key update
        utilzz._fk_dump.create_stmt = values(create_stmt),
        utilzz._fk_dump.drop_stmt = values(drop_stmt);

    drop temporary table if exists utilzz._fk_dump2;
    create temporary table if not exists utilzz._fk_dump2 like utilzz._fk_dump;
    insert utilzz._fk_dump2 select * from utilzz._fk_dump;

    return 'select * from utilzz._fk_dump(2)';
end$$

delimiter ;


drop function if exists fk_dump_reset;

delimiter $$
create function fk_dump_reset(schema_ varchar(255)) returns varchar(255)
deterministic
begin
    drop temporary table if exists _fk_dump;

    return fk_dump(schema_);
end$$

delimiter ;


drop function if exists table_insert;

delimiter $$
create function table_insert(table_schema varchar(255), table_name varchar(255)) returns text
deterministic
begin
    declare max_length int unsigned;
    declare result text;

    -- 6 is number of characters for column name comments
    -- 1 is number of spaces to separate column name comments and default value placeholder
    -- 4 is number of spaces to align indent to
    select ceil((max(char_length(c.COLUMN_NAME)) + 6 + 1) / 4) * 4 into max_length 
    from information_schema.COLUMNS c
    where c.TABLE_SCHEMA = table_schema and c.TABLE_NAME = table_name;

    select 
        concat(
            'insert ',
            c.TABLE_NAME,
            ' (\n',
            group_concat(concat('    ', c.COLUMN_NAME) order by c.ORDINAL_POSITION separator ',\n'),
            ')\nvalues (\n',
            group_concat(
                concat(
                    concat('/* ', c.COLUMN_NAME, ' */'), 
                    repeat(' ', (max_length - char_length(c.COLUMN_NAME) - 6)), -- 6 is number of characters for column name comments, that we need to subtract here
                    if(
                        c.IS_NULLABLE = 'YES', 
                        'NULL', 
                        case when c.DATA_TYPE in ('int', 'bigint', 'double', 'decimal') then coalesce(c.COLUMN_DEFAULT, '\'\'') else quote(coalesce(c.COLUMN_DEFAULT, '')) end))
                order by c.ORDINAL_POSITION separator ',\n'),
            ');'
        )
    into result
    from information_schema.COLUMNS c
    where c.TABLE_SCHEMA = table_schema and c.TABLE_NAME = table_name;

    return result;
end$$

delimiter ;


drop function if exists routine_call;

delimiter $$
create function routine_call(routine_schema varchar(255), routine_name varchar(255)) returns text
deterministic
begin
    declare max_length int unsigned;
    declare result text;

    -- 6 is number of characters for column name comments
    -- 1 is number of spaces to separate column name comments and default value placeholder
    -- 4 is number of spaces to align indent to
    select ceil((max(char_length(p.PARAMETER_NAME)) + 6 + 1) / 4) * 4 into max_length 
    from information_schema.PARAMETERS p
    where p.SPECIFIC_SCHEMA = routine_schema and p.SPECIFIC_NAME = routine_name and p.PARAMETER_NAME is not null;

    select 
        concat(
            if(p.ROUTINE_TYPE = 'FUNCTION', 'select ', 'call '),
            p.SPECIFIC_NAME,
            '(\n',
            group_concat(
                concat(
                    concat('/* ', p.PARAMETER_NAME, ' */'), 
                    repeat(' ', (max_length - char_length(p.PARAMETER_NAME) - 6)), -- 6 is number of characters for column name comments, that we need to subtract here
                    '\'\'')
                order by p.ORDINAL_POSITION separator ',\n'),
            ');'
        )
    into result
    from information_schema.PARAMETERS p
    where p.SPECIFIC_SCHEMA = routine_schema and p.SPECIFIC_NAME = routine_name and p.PARAMETER_NAME is not null
    group by p.SPECIFIC_SCHEMA, p.SPECIFIC_NAME, p.ROUTINE_TYPE;

    return result;
end$$

delimiter ;