$DBversion = "3.11.00.XXX";

if (C4::Context->preference("Version") < TransformToNum($DBversion)) {

   $dbh->do("ALTER TABLE serial ADD published_end_date DATE NULL DEFAULT NULL AFTER publisheddate");

   $dbh->do("

       ALTER TABLE  serial ADD serialseq_x VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq,

       ADD serialseq_y VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_x,

       ADD serialseq_z VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_y

   ");

   $dbh->do("

       ALTER TABLE subscription ADD serialseq_x_label VARCHAR( 100 ) NULL DEFAULT NULL AFTER numberingmethod,

       ADD serialseq_y_label VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_x_label,

       ADD serialseq_z_label VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_y_label

   ");

    my $trim = sub {

        my $string = shift;

        $string =~ s/^\s+//;

        $string =~ s/\s+$//;

        return $string;

    };

   my $dbh = C4::Context->dbh;

   my $sth = $dbh->prepare("SELECT * FROM subscription ORDER BY numberingmethod ASC");

   $sth->execute();

   while ( my $subscription = $sth->fetchrow_hashref() ) {

       ## parse out the labels and save them

       my $nm = $subscription->{'numberingmethod'};

       print "NUMBERING METHOD: '$nm'\n";

       my @labels = split(/\{[XYZ]\}[, ]?/, $nm);

       @labels = map( $trim->( $_ ), @labels );

       my ( $x_label, $y_label, $z_label ) = @labels;

       $dbh->do(

           "UPDATE subscription SET serialseq_x_label = ?, serialseq_y_label = ?, serialseq_z_label = ? WHERE subscriptionid = ?",

           undef,

           ( $x_label, $y_label, $z_label, $subscription->{'subscriptionid'} )

       );

       ## parse of the data and save

       my @splits = split(/\{[XYZ]\}/, $nm);

       foreach my $s ( @splits ) {

           $s = quotemeta( $s );

       }

       my $sth2 = $dbh->prepare("SELECT * FROM serial WHERE subscriptionid = ?");

       $sth2->execute( $subscription->{'subscriptionid'} );

       while( my $serial = $sth2->fetchrow_hashref() ) {

           my $serialseq = $serial->{'serialseq'};

           my ( $x, $y, $z );

           ## We cannot split on multiple values at once,

           ## so let's replace each of those values with __SPLIT__

           if ( @splits ) {

               map( $serialseq =~ s/$_/__SPLIT__/ , @splits );

               ( undef, $x, $y, $z ) = split(/__SPLIT__/, $serialseq);

           } else { ## Nothing to split on means the only thing in serialseq is {X}

               $x = $serialseq;

           }

           $dbh->do(

               "UPDATE serial SET serialseq_x = ?, serialseq_y = ?, serialseq_z = ? WHERE serialid = ?",

               undef,

               ( $x, $y, $z, $serial->{'serialid'} )

           );

       }

   }

   print "Upgrade to $DBversion done ( Split serials enumeration data into separate fields )\n";

   SetVersion ($DBversion);

}