#!/usr/bin/perl
#
# MAME -> MythGame metadata importer.
#
# (c)Joaquin Ferrero. 2011/11/12
#
# May be freely distributed and used as long as the copyright notice
# is preserved.
#
# gamemetadata table:
#+------------+--------------+------+-----+---------+-------+
#| Field      | Type         | Null | Key | Default | Extra |
#+------------+--------------+------+-----+---------+-------+
#| system     | varchar(128) | NO   | MUL |         |       | $SYSTEM
#| romname    | varchar(128) | NO   | MUL |         |       | from mame <name>
#| gamename   | varchar(128) | NO   | MUL |         |       | from mame <description>
#| genre      | varchar(128) | NO   | MUL |         |       | from catver.ini
#| year       | varchar(10)  | NO   | MUL |         |       | from mame <year>
#| publisher  | varchar(128) | NO   |     |         |       | from mame <manufacturer>
#| favorite   | tinyint(1)   | YES  |     | NULL    |       | "0"
#| rompath    | varchar(255) | NO   |     |         |       | roms directory
#| screenshot | varchar(255) | NO   |     | NULL    |       | snap directory
#| fanart     | varchar(255) | NO   |     | NULL    |       | background directory
#| plot       | text         | NO   |     | NULL    |       | from history.dat
#| boxart     | varchar(255) | NO   |     | NULL    |       | titles directory
#| gametype   | varchar(64)  | NO   |     |         |       | "MAME"
#| diskcount  | tinyint(1)   | NO   |     | 1       |       | 1
#| country    | varchar(128) | NO   |     |         |       | "Desconocido"
#| crc_value  | varchar(64)  | NO   |     |         |       | ?
#| inetref    | text         | YES  |     | NULL    |       | ?
#| display    | tinyint(1)   | NO   |     | 1       |       | 1
#| version    | varchar(64)  | NO   |     |         |       | "CUSTOM"
#+------------+--------------+------+-----+---------+-------+
#

## Modules ##################################################################
use 5.010;
use strict;
use warnings;
use autodie;
use DBI;

## Configuration ############################################################

# Verbosity (0, 1, 2, 3)
my $VERBOSE = 2;

# Remove roms that are marked as clones
my $FILTER_CLONES = 0;

# Remove roms with the string "mature" in genre
my $FILTER_MATURE = 0;

# Remove BIOS roms from list (You probably want this)
my $FILTER_BIOS = 1;

# Remove PinMAME roms
my $FILTER_PINMAME = 1;

# The System name displayed
my $SYSTEM = "Mame";

# The MAME binary
my $MAME = "mame";

# The MAME directory
my $MAMEDIR = "/home/javier/.mame";

# The Category file
# Download the category file from http://www.progettoemma.net/?catlist
my $CATFILE = "Catver.ini";

# The History file
# Download the History file from http://www.arcade-history.com/index.php?page=download
my $HSTFILE = "history.dat";

# The Mame roms directory
my $ROMSDIR = "roms";

# The Mame snap directory
my $SNAPDIR = "snap";

# The Mame titles directory
my $TITLEDIR = "titles";

# The Myth DataBase
my $MYTH_DB_USER = "mythtv";
my $MYTH_DB_PASS = "kIrNeDw7";
my $MYTH_DB_NAME = "mythconverg";

# Init the gamemetadata table completely?
# 0: No. Only update selection of games (cloned, mature...)
# 1: Yes. Init gameplayers and gamemetadata tables with Mame data
# (but favourites are remembered!)
my $INIT_GAMETABLE = 1;

## End of Configuration #####################################################


## Check system #############################################################
-d  $MAMEDIR            or die "ERROR: No Mame dir found: $!\n";
-e "$MAMEDIR/$CATFILE"  or die "ERROR: No Catver.ini file found!: $!\n";
-e "$MAMEDIR/$HSTFILE"  or die "ERROR: No history.dat file found!: $!\n";
-d "$MAMEDIR/$SNAPDIR"  or die "ERROR: No snap dir found!: $!\n";
-d "$MAMEDIR/$TITLEDIR" or die "ERROR: No title dir found!: $!\n";
-d "$MAMEDIR/$ROMSDIR"  or die "ERROR: No roms dir found!: $!\n";
qx($MAME -?)            or die "ERROR: No Mame executable found.\n";
say "System Ok"         if $VERBOSE == 2;


## Vars #####################################################################
my($romname, $cloneof, $gamename, $genre, $year, $publisher,
$favorite, $rompath, $screenshot, $plot, $boxart, $inetref);

my %catver;             # Extra information
my %history;
my %favorites;

my $dbh;                # Access to database
my $sth;                # SQL execute
my $sql;                # SQL sentence


## Init #####################################################################

$|++; # No cache

# Connect to database
$dbh
    = DBI->connect(
        "DBI:mysql:database=$MYTH_DB_NAME", $MYTH_DB_USER, $MYTH_DB_PASS
    )
    or
        die "ERROR: No can't to connect to database: $DBI::errstr\n";
say "Connected to DB"                   if $VERBOSE > 0;


## Program ##################################################################

# Remember favourites games #################################################
if ($INIT_GAMETABLE) {
    say "Reading favourites games"      if $VERBOSE > 0;

    %favorites = map { $_->[0] => 1 } @{ $dbh->selectall_arrayref(
        "SELECT
            romname
        FROM 
            gamemetadata
        WHERE
                system='$SYSTEM'
            AND
                favorite=1
        "
    )};
}

# Read Catver.ini file ######################################################
{
    print "Reading Catver.ini file... " if $VERBOSE > 0;
    open my $catver_fh, q[<:crlf], $CATFILE;
    while (<$catver_fh>) {
        last if /VerAdded/;
        chomp;
        next unless /^(.*?)=(.*)/;
        $catver{$1} = $2;
    }
    close $catver_fh;
    say "Ok"                            if $VERBOSE > 0;
    say scalar(keys %catver),
        " categories readed"            if $VERBOSE > 1;
}

# Read history.dat file #####################################################
{
    print "Reading history.dat file... " if $VERBOSE > 0;
    open my $history_fh, q[<:crlf], $HSTFILE;
    local $/ = '$end';
    while (<$history_fh>) {
        if (/   ^ \$info= (?<info>.+?)            $ .*?
                ^ \$<a [ ] href=" (?<inetref>.*?) " .*?
                ^ \$bio (?<bio>.*?)               ^ \$end
            /smx
        ) {
            my($info,$inetref,$bio) = @+{qw(info inetref bio)};

            my($desc,$para) = $bio =~ /^\s*(.*?)\n+(.*?)$/m;
            $para = $desc if $para =~ /^-.+-$/;

            for (split /,/, $info) {
                $history{$_} = [ $inetref, $para ];
            }
        }
    }
    close $history_fh;
    say "Ok"                            if $VERBOSE > 0;
    say scalar(keys %history),
        " histories readed"             if $VERBOSE > 1;
}

# Init gameplayers information ##############################################
if ($INIT_GAMETABLE) {
    say "Initializing gameplayers table" if $VERBOSE > 0;

    $dbh->do("DELETE FROM gameplayers WHERE gametype='MAME'");
    $dbh->do(
        "INSERT INTO
            gameplayers
         SET
            playername='$SYSTEM',
            workingpath='$MAMEDIR',
            rompath='$MAMEDIR/$ROMSDIR',
            screenshots='$MAMEDIR/$SNAPDIR',
            commandline='$MAME',
            gametype='MAME',
            extensions='',
            spandisks=0
        "
    );
}

# Init games' list ##########################################################
if ($INIT_GAMETABLE) {
    say "Initializing gamemetadata table" if $VERBOSE > 0;

    $dbh->do("DELETE FROM gamemetadata WHERE gametype='MAME'");
}

# Create SQL sentence #######################################################
$sql = $INIT_GAMETABLE ? 'INSERT DELAYED INTO' : 'UPDATE LOW_PRIORITY';

$sql .= "
            gamemetadata
        SET
            system=?,
            romname=?,
            gamename=?,
            genre=?,
            year=?,
            publisher=?,
            favorite=?,
            rompath=?,
            screenshot=?,
            fanart='',
            plot=?,
            boxart=?,
            gametype='MAME',
            diskcount=1,
            country='Desconocido',
            crc_value='',
            inetref=?,
            display=1,
            version='CUSTOM'
        "
        ;

$sql .= $INIT_GAMETABLE ? '' : "WHERE\n\t    gamename=?\n";

say "[$sql]"                            if $VERBOSE > 2;

$sth = $dbh->prepare($sql);

# Process mame game list ####################################################
my $number_of_roms       = 0;
my $number_of_roms_in_db = 0;
my $number_of_roms_clone = 0;
my $number_of_roms_norom = 0;
my $number_of_roms_nocat = 0;
my $number_of_roms_nohst = 0;
{
    say "Processing game list"          if $VERBOSE > 0;
    open my $mame_xml_fh, "$MAME -listxml |";
    local $/ = '</game>';
    while (<$mame_xml_fh>) {
        ($romname)   = /<game name="(.*?)"/;
        next if not $romname;
        ($cloneof)   = /cloneof="(.*?)"/;
        $cloneof   //= '';
        ($gamename)  = /<description>(.*?)</;
        ($year)      = /<year>(.*?)</;
        ($publisher) = /<manufacturer>(.*?)</;

        $number_of_roms++;

        # Check ROM file existence
        $rompath    = "$MAMEDIR/$ROMSDIR/$romname.zip";
        if (!-e $rompath) {
            $number_of_roms_norom++;
            say "ERROR: rom $rompath not found" if $VERBOSE > 1;
            next;
        }

        # Check if ROM is clone
        if ($cloneof) {
            $number_of_roms_clone++;
            next if $FILTER_CLONES;
        }

        # Other optional files
        $screenshot = -e "$MAMEDIR/$SNAPDIR/$romname.png" ? "$MAMEDIR/$SNAPDIR/$romname.png"
                    : -e "$MAMEDIR/$SNAPDIR/$cloneof.png" ? "$MAMEDIR/$SNAPDIR/$cloneof.png"
                    : ''
                    ;
        $boxart     = -e "$MAMEDIR/$TITLEDIR/$romname.png" ? "$MAMEDIR/$TITLEDIR/$romname.png"
                    : -e "$MAMEDIR/$TITLEDIR/$cloneof.png" ? "$MAMEDIR/$TITLEDIR/$cloneof.png"
                    : ''
                    ;

        # Others
        if ($catver{$romname}) {
            $genre = $catver{$romname};

            if (
                ($FILTER_BIOS    and lc($genre) eq 'bios')
             or ($FILTER_MATURE  and $genre =~ /\bmature\b/i)
             or ($FILTER_PINMAME and $genre =~ /PinMAME/)
            ) {
                if (! $INIT_GAMETABLE) {
                    say "Deleting game $romname information from DB"
                                                if $VERBOSE > 1;
                    $dbh->do(
                        "DELETE FROM gamemetadata WHERE system=? AND romname=?", {},
                        $SYSTEM, $romname
                    );
                }

                next;
            }
        }
        else {
            $number_of_roms_nocat++;
            $genre = 'Desconocido';
        }
        
        if ($history{$romname}) {
            ($inetref, $plot) = @{$history{$romname}};
        }
        else {
            $number_of_roms_nohst++;
            ($inetref, $plot) = ('', '');
        }

        $favorite = $favorites{$romname} // 0;

        # Insert record
        say $gamename                       if $VERBOSE > 2;

        my @sql_par = (
            $SYSTEM,
            $romname,$gamename,
            $genre,$year,$publisher,$favorite,
            $rompath,$screenshot,$plot,$boxart,
            $inetref,
        );
        push @sql_par, $gamename            if ! $INIT_GAMETABLE;

        $sth->execute(@sql_par);
        
        $number_of_roms_in_db++;
    }

    close $mame_xml_fh;
}

## Stats ####################################################################
if ($VERBOSE > 0) {
    say "Number of ROMs supported:        $number_of_roms";
    say "Number of ROM Files not found:   $number_of_roms_norom";
    say "Number of ROMs in database:      $number_of_roms_in_db";
    say "Number of ROMs clones:           $number_of_roms_clone";
    say "Number of ROMs without category: $number_of_roms_nocat";
    say "Number of ROMs without history:  $number_of_roms_nohst";
    say "Number of favorites ROMS:        ", scalar keys %favorites;
}


## Final ####################################################################
$sth->finish();
$dbh->disconnect();

__END__