#!/usr/bin/perl

use Modern::Perl;
use C4::Context;

say 'Add subfields 666z and 666Z';
my $dbh = C4::Context->dbh;
$dbh->do(qq{
        INSERT INTO marc_subfield_structure (tagfield,tagsubfield,liblibrarian,libopac,repeatable,mandatory,kohafield,tab,seealso,authorised_value,authtypecode,value_builder,hidden,isurl,frameworkcode, link,defaultvalue,maxlength)
        VALUES ('666','z','bad','bad',0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,0,9999)
    });
$dbh->do(qq{
        INSERT INTO marc_subfield_structure (tagfield,tagsubfield,liblibrarian,libopac,repeatable,mandatory,kohafield,tab,seealso,authorised_value,authtypecode,value_builder,hidden,isurl,frameworkcode, link,defaultvalue,maxlength)
        VALUES ('666','Z','bad','bad',0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,0,9999)
    });



say 'Try update to DBRev 3.19.00.006 for marc_subfield_structure only';
$dbh->do(q|SET foreign_key_checks = 0|);
my $sth = $dbh->table_info( '','','','TABLE' );
my ( $cat, $schema, $name, $type, $remarks );
while ( ( $cat, $schema, $name, $type, $remarks ) = $sth->fetchrow_array ) {
    if ($name eq 'marc_subfield_structure') {
	my $table_sth = $dbh->prepare(qq|SHOW CREATE TABLE $name|);
	$table_sth->execute;
	my @table = $table_sth->fetchrow_array;
	unless ( $table[1] =~ /COLLATE=utf8mb4_unicode_ci/ ) { #catches utf8mb4 collated tables
	    $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci|);
	}
    }
}
$dbh->do(q|SET foreign_key_checks = 1|);;



say 'Remove subfield 666z and 666Z';
$dbh->do(qq{DELETE FROM marc_subfield_structure WHERE tagfield = '666'
            AND (tagsubfield = 'z' or tagsubfield = 'Z')
            AND frameworkcode = ''}) ;