#!/usr/bin/perl

# Check for duplicate email addresses in both parents fields


use Cwd;
use DBI;

my $cwd = getcwd();


# Read config variables
eval require "../schooldb.conf";
if ( $@ ) {
    print "Error: $@<br>\n";
    die "Error $@\n";
}

# @schooldb = ('training');

print "\nEnter MySQL Root Password: ";
my $mysqlroot = <STDIN>;
chomp $mysqlroot;


my $user = 'root';


foreach my $dbase ( @schooldb ) {

    print qq{\nDB:$dbase\n};
    
    my $dsn = "DBI:mysql:dbname=$dbase";
    my $dbh = DBI->connect($dsn,$user,$mysqlroot);
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    # Change applied to all extra fields
    my $sth1 = $dbh->prepare("update meta set datatype = 'text', formtype = 'textarea',
       viewsize = '1x60' where id = ?");

    my $sth2 = $dbh->prepare("update meta set fieldname = ? where id = ?");

    
    foreach my $idx (1..16) {

	my $fieldid = 'field'.$idx;
	my $newfieldname = qq{Extra Field $idx};
	
	my $sth = $dbh->prepare("select * from meta where fieldid = ?");
	$sth->execute($fieldid);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	my %r = %$ref;

	my $id = $r{id};

	# Update datatype,formtype,viewsize
	$sth1->execute($id);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	
	if ( $r{fieldname} ne $fieldid ) { # different updated metaname from default
	    print qq{Field ID:$fieldid / Updated Name: $r{fieldname}\n};
	    
	} else { # identical fieldid and fieldname; change
	    $sth2->execute($newfieldname, $id);
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	    print "New Name:$newfieldname / $fieldid / $r{fieldname}\n";

	}
	    
    }

}


print "Done\n";