use strict;
use warnings;

use Text::CSV qw(csv);
use Data::Dump qw(pp);
use DBI;


my $aoa = csv (in => \*DATA);    # as array of array
#pp $aoa;

my $dbfile  = "testing.sqlite";

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError => 1})
    or die "Couldn't connect to database: " . DBI->errstr;

$dbh->do("DROP TABLE IF EXISTS mytable");    

$dbh->do ("CREATE TABLE mytable (Server text, Path text, Permissions text, 
                        logon text, inheritedFrom text)");
                        
my $insert = $dbh->prepare ("INSERT INTO mytable 
                               (Server, Path, Permissions,logon, inheritedFrom)
                               VALUES (?,?,?,?,?)");
                               
my $get_uniquePathPer = $dbh->prepare ("SELECT Path, Permissions FROM mytable
                                        GROUP BY Path, Permissions");
                                   
                                   
my $get_logons = $dbh->prepare("SELECT logon FROM mytable 
                                WHERE Path=? and Permissions =?");                                   

# import CSV data into mytable
#

$dbh->begin_work;
shift @$aoa;  #throw away header
foreach my $csvRef (@$aoa)
{
   $insert->execute((@$csvRef)[0..4]);
}
$dbh->commit;

# Get unique combinations of Path and Permissions
#

$get_uniquePathPer->execute();
my $aoaPathPer = $get_uniquePathPer->fetchall_arrayref;

# For each unique combination get all logons
#

foreach my $combo_ref (@$aoaPathPer)
{ 
    print "$combo_ref->[0]\n";
    print "$combo_ref->[1]\n";
    
    $get_logons->execute($combo_ref->[0], $combo_ref->[1]);
    my $aoa_logons = $get_logons->fetchall_arrayref;

    my @logons = map{@$_}@$aoa_logons;
    print join (",", @logons),"\n\n";
}

=Outputs
/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf
FMRWX
@FOO NOW Onsite Support,Administrators,Creator Owner,FP NOW BMG FSE NTFS Admins,ClusterSvcDIR,SYSTEM,MiJim

/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf
MRWX
@FP DIR BMG

/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf
RX
&CDAdmin,@FOO DSMS Admins,FOO BMG FS Support,DPeterso,FP BMG IMG Read Access

/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/My-B8245.pdf
FMRWX
@FOO NOW Onsite Support,Administrators,Creator Owner

=cut
                                   
                               
__DATA__
File Server,Access Path,Current Permissions,Logon Name,Inherited From Folders,Flags,User/Group,Classification Results,Classification Results by Category (Including Nested),Total Hit Count
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,FMRWX,@FOO NOW Onsite Support,\Common,This folder only,Pathway12.My.Corp.com\@FOO NOW Onsite Support,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,FMRWX,Administrators,\Common,This folder only,10.15.106.71\Administrators,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,FMRWX,Creator Owner,\Common,This folder only,Abstract\Creator Owner,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,FMRWX,FP NOW BMG FSE NTFS Admins,\Common,This folder only,Pathway12.My.Corp.com\FP NOW BMG FSE NTFS Admins,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,FMRWX,ClusterSvcDIR,\Common,This folder only,Pathway12.My.Corp.com\ClusterSvcDIR,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,FMRWX,SYSTEM,\Common,This folder only,Abstract\SYSTEM,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,FMRWX,MiJim,<not inherited>,This folder only,"Pathway12.My.Corp.com\Michaels, Jim@My",IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,MRWX,@FP DIR BMG,\Common,This folder only,Pathway12.My.Corp.com\@FP DIR BMG,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,RX,&CDAdmin,\Common,This folder only,Pathway12.My.Corp.com\&CDAdmin,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,RX,@FOO DSMS Admins,\Common,This folder only,Pathway12.My.Corp.com\@FOO DSMS Admins,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,RX,FOO BMG FS Support,\Common,This folder only,Pathway12.My.Corp.com\FOO BMG FS Support,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,RX,DPeterso,\Common,This folder only,"Pathway12.My.Corp.com\Peterson, Dan@My",IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/Axidome Quote My Corp 2020211 KnowBe4 2-yr Renewal FINAL.pdf,RX,FP BMG IMG Read Access,\Common,This folder only,Pathway12.My.Corp.com\FP BMG IMG Read Access,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/My-B8245.pdf,FMRWX,@FOO NOW Onsite Support,\Common,This folder only,Pathway12.My.Corp.com\@FOO NOW Onsite Support,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/My-B8245.pdf,FMRWX,Administrators,\Common,This folder only,10.15.106.71\Administrators,IRS Data (1/1),PII (1),1
10.15.106.71,/Common/Awareness and Training/KnowBe4/2020- KnowBe4 Subscription Renewal Docs/My-B8245.pdf,FMRWX,Creator Owner,\Common,This folder only,Abstract\Creator Owner,IRS Data (1/1),PII (1),1