#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $db = DBI->connect( "dbi:SQLite:dbname=SrvrGrpData","","" );
$db->do( "DROP TABLE IF EXISTS srvr_grp" );
$db->do( "DROP TABLE IF EXISTS srvr_usr" );
$db->do( "DROP TABLE IF EXISTS grp_usr" );
$db->do( "CREATE TABLE srvr_grp (srvrid varchar(40), grpname varchar(40), grpnum int)" );
$db->do( "CREATE TABLE srvr_usr (srvrid varchar(40), usrname varchar(40), usrnum int, defgrp int)" );
$db->do( "CREATE TABLE grp_usr  (srvrid varchar(40), grpid int, usrname varchar(40))" );

my $ins_grp = $db->prepare( "insert into srvr_grp (srvrid,grpname,grpnum) values (?,?,?)" );
my $ins_gu = $db->prepare( "insert into grp_usr (srvrid,grpid,usrname) values (?,?,?)" );

my $grp_path = "/path/to/group_files";
my $usr_path = "/path/to/passwd_files";

chdir $grp_path or die "chdir $grp_path: $!\n";
opendir( D, "." ) or die "opendir $grp_path: $!\n";
while ( my $f = readdir( D )) {
    open( F, "<", $f ) or do { warn " open failed on $f: $!\n"; next };
    my ( $srvr ) = ( $f =~ /^(\w+)/ );
    while (<F>) {
        next if ( /^#/ );
        chomp;
        my ( $gname, $skip, $gnum, $gmembers ) = split m{:};
        $ins_grp->execute( $srvr, $gname, $gnum );
        for my $u ( split m{,}, $gmembers ) {
            $ins_gu->execute( $srvr, $gnum, $u );
        }
    }
    close F;
}
closedir D;
$ins_grp->finish;
$ins_gu->finish;

my $ins_usr = $db->prepare( "insert into srvr_usr (srvrid,usrname,usrnum,defgrp) values (?,?,?,?)" );
chdir $usr_path or die "chdir $usr_path: $!\n";
opendir( D, "." ) or die "opendir $usr_path: $!\n";
while ( my $f = readdir( D )) {
    open( F, "<", $f ) or do { warn " open failed on $f: $!\n"; next };
    my ( $srvr ) = ( $f =~ /^(\w+)/ );
    while (<F>) {
        next if ( /^#/ );
        chomp;
        my ( $uname, $unum, $dgrp ) = split m{:};
        $ins_usr->execute( $srvr, $uname, $unum, $dgrp );
    }
    close F;
}
closedir D;
$ins_usr->finish;

for my $table ( qw/srvr_grp srvr_usr grp_usr/ ) {
    print "\n== selecting all from $table: ==\n";
    my $qsth = $db->prepare( "select * from $table" );
    $qsth->execute;
    my $rows = $qsth->fetchall_arrayref;
    for my $row ( @$rows ) {
        print join( "\t", @$row ), "\n";
    }
}