use DBI;

my $dbh =
    DBI->connect('DBI:mysql:opencaca','opencaca', 'geheimesMantra') # Use your own db-configuration here
    or die "Couldn't connect to database: " . DBI->errstr;

# get all valid Signature-Certifcates for User-data
my $sth = $dbh->prepare("SELECT cert_key, dn, email, cn FROM certificate where status='VALID' and role='Signature'")
    or die "Couldn't prepare statement: " . $dbh->errstr;

my @data;

$sth->execute()             # Execute the query
    or die "Couldn't execute statement: " . $sth->errstr;

# Read the matching records, generate the bp-data. Output should be piped to > batch_process_data.txt 
         
while (@data = $sth->fetchrow_array()) {
    my $cert_key = $data[0];
    my $dn = $data[1];
    my $email=$data[2];
    my $cn=$data[3];
   
    $dn=~s/serialNumber=[0-9]*,//g; # remove the serialNumber of the Signature-certificate from the DN 
    @emails=split/,/,$email;        # split the comma-seperated list of emails
    print "USER $cert_key\n";
    print "PROCESS encryption-cert\n";
    print "set_state new_process\n";
    print "ROLE Encryption\n";
    print "SUBJECT $dn\n";
    print "LOA_MODE IGNORE\n";
    # TODO: Read the key-size from Signature-Certificate and use this in bp too.
    $i=0;
    foreach (@emails)
    {$i++;
     print "SUBJECT_ALT_NAME_$i email:$_\n";
 }  print "\n";
} 


$sth->finish;
$dbh->disconnect;