#!/usr/bin/perl

##### WHAT #
# What names return per search
##########

use strict;
#use warnings;
use DBI;

        my $searcher = "owner.manager, owner.owner, owner.manown";

my $dbh = DBI->connect('DBI:mysql:db;host=localhost',
                        'user', 'pass')
                or die "Database connection: $!";

open( FILE, "< $ARGV[0]" );

my %uword = ();
my %seen = ();
my $count = 0;
my @data;
my $key;
my $i = 0;

while ( <FILE> ) {

        my $line = $_;

        chomp ($line);
        my @word = split / /, $line;


        $count = 0;
        while ( $word[ $count ] ) {
                $word[ $count ] =~ tr/^[\-a-zA-Z]//;
                $word[ $count ] =~ s/\'/\\\'/g;
                $count++;
        }

# deduplicate each word, but if it is a duplicate,
# i still want to know what lines the word was on.
        foreach my $string ( @word ) {
                if ( $uword{ $string }[ 0 ] == 1 ) {
                        push @{ $uword{ $string } }, $line;
                        next;
                }
                $uword{ $string }[ 0 ] = 1;
                push @{ $uword{ $string } }, $line;
        }
}

# for every unique word, do a search...
for my $key ( keys %uword ) {

        my ( $imo, $owner, $manown, $manager );

        my $select =    qq/SELECT $searcher /;
        my $from =              qq/FROM owner, spd /;
        my $where =             qq/WHERE MATCH( $searcher ) AGAINST('+$key' IN BOOLEAN MODE) /;
        my $join =              qq/AND owner.number = spd.number/;

        my $query = $select . $from . $where . $join;

        print "SQL: $query\n";
        my $sth = $dbh->prepare( $query );
        $sth->execute;

        $sth->bind_columns( \$manager, \$owner, \$manown );

# since i don't know or care what field my matches came
# from, take the results and put them into an array.
        while ( $sth->fetch ) {

                if ( defined( $owner ) ) {
                        $data[$count] = $owner;
                        $count++;
                }
                if ( defined( $manown ) ) {
                        $data[$count] = $manown;
                        $count++;
                }
                if ( defined( $manager ) ) {
                        $data[$count] = $manager;
                        $count++
                }
        }
# same general deduplication algorithm as before.
# @data holds full names of data.

        foreach my $string ( @data ) {   # dedupe data and sanity check.
                next if !defined ($string);   # should never be true.
                next if $seen{ $string }[ 0 ] == 1; # check %seen hash / array for dupe
                $seen{ $string }[ 0 ] = 1;    # define hash of array and assign check to it.
                $seen{ $string }[ 1 ] = $key; # add word from line to hash for reference.
        }
}

$dbh->disconnect;

# this is here because i was up real late thinking
# "wtf does what here"....

# $key                             - (below) is the deduped sql string
# $seen { $key }[ 1 ]              - is the word searched in sql
# $uword { '$seen{ $key }[ 1 ]' }  - should be the line(s) of test the search string came from

for my $key ( keys %seen ) {

        print "$seen{ $key }[ 1 ], $key,";
        print join(',', @{ $uword{ $seen{ $key }[ 1 ] }[ 1 .. $#{ @uword{ $seen{ $key } } } ] } );
        print "\n";
}