use strict;
use warnings;
use DBI;
use FindBin ();
use lib ('C:\Programme\xampp\htdocs\drow');

my $DB_TABLE = "drow_dictionary";
my $LANG0 = "Drow";
my $LANG1 = "Common";
my $NOTES = "Notes";

package My::Config;
use strict;

use lib ('C:/Programme/xampp/apache/bin/');
my $DATABASE = "drow";
my $DB_TABLE = "drow_dictionary";
my $DB_USERNAME = "root";
my $DB_PASSWORD = "";

loadDictionary( "words.txt" );


sub loadDictionary {
  my( $DICTFILE ) = $FindBin::RealBin . $_[0];
  my @lines;

   # Read in the dictionary into memory
  print "Reading file '$DICTFILE'...";
  open( DICTFILE,'<', $DICTFILE ) || die( "Couldn't open file: $!" );;
  my( @filelines ) = <DICTFILE>;
  close( DICTFILE );
  foreach my $line (@filelines) {
    push( @lines, my $line ) if( my $line =~ m/\w/ );
  }
  @filelines = ();

  print (0+@lines)." lines read.\n";
  print "Sorting...";
  @lines = sort( @lines );
  print "Done.\n";

  # Setup Database Connection
my $dbh = DBI->connect("DBI:mysql:$My::Config::DATABASE:localhost", $My::Config::DB_USERNAME, $My::Config::DB_PASSWORD ) ||
 die( "Could not establish database connection: ".$DBI::errstr );
 $dbh->do( "delete from $My::Config::DB_TABLE" );;

  # Cycle through lines, adding them into database
  my( $query ) ="insert into $DB_TABLE ($LANG0, $LANG1, $NOTES) values (?,?,?) ";
  my $sth = $dbh->prepare($query);

  foreach my $line (@lines ) {
    my( $notes ) = "";
    # Simple word0=word1 line
    my $line =~ s/\`/\'/g; # Change back-quotes to normal quotes
    my $line =~ s/\_/\ /g; # Change underscores to spaces
    my $line =~ s/\+/\ /g; # Change plusses to spaces
    if( my $line =~ m/^([\w\-\'\s\,\+]*)\=(.*)/ ) {
      my $word1 = $1;
      my $word2 = $2;

      # Get rid of email address of contributed words
      if( my $word2 =~ m/\=/ ) {
        my $word2 = $`;
      }

      # Extract notes if any
      if( my $word2 =~ m/\((.*)\)/) {
        $notes = $1;
        $notes =~ s/\'/\\\'/g;
        my $word2 = $`;
      }

    # Split if it has commas
    my( @words1, @words2 );
    if( my $word1 =~ m/\,/ ) {
      @words1 = split( ',', my $word1 );
    } else {
      @words1 = (my $word1);
    }
    if( my $word2 =~ m/\,/ ) {
      @words2 = split( ',', my $word2 );
    } else {
      @words2 = (my $word2);
    }

    # Do all combinations if has commas
    foreach my $word1 (@words1) {
      foreach my $word2 (@words2) {
        my $word1 = &trim( my $word1 );
        my $word2 = &trim( my $word2 );
        if( $notes eq "" ) {
          $sth->execute(my $word1,my $word2,undef);
        } 
        else {
          $sth->execute(my $word1,my $word2,$notes);
        }

      }
    }
  } else {
    warn( "Can't parse line ==my $line==\n" );
  }
}

}


# Trims whitespace off start and end of string
sub trim{
my( @asz, $sz ) = @_;

foreach $sz (@asz){
  $sz =~ s/^\s*(.*)$/$1/;
  ($sz = $`) if ($sz =~ m/\s*$/);
}
return wantarray() ? @asz : $asz[0];
}