#! /usr/bin/perl
use strict;
use warnings;
use Date::Manip;
require "../html/ccbs.pm";

$SIG{__DIE__} = sub {
	print STDERR shift;
	exit;
};

#
# Not really a parser, but lets us export entire tournaments from one CCBS
# database to another in a simple way without having to worry about the
# numerical details.
#

my $tno = shift;
die "Missing tournament number (give on command line)." if (!defined($tno));

print "begin;\n";

my $dbh = ccbs::db_connect();

# The tournament itself
my $tournament = $dbh->selectrow_hashref('SELECT * FROM tournaments NATURAL JOIN countries NATURAL JOIN seasons NATURAL JOIN machines NATURAL JOIN scoringsystems WHERE tournament=?', undef, $tno);

print "INSERT INTO tournaments \n";
print "  (season, tournamentname, country, location, \"date\", machine, scoringsystem) VALUES (\n";
print "    (SELECT season FROM seasons WHERE seasonname='$tournament->{'seasonname'}' ),\n";
print "    '$tournament->{'tournamentname'}',\n";
print "    (SELECT country FROM countries WHERE countryname='$tournament->{'countryname'}'),\n";
print "    '$tournament->{'location'}',\n";
print "    '$tournament->{'date'}',\n";
print "    (SELECT machine FROM machines WHERE machinename='$tournament->{'machinename'}'),\n";
print "    (SELECT scoringsystem FROM scoringsystems WHERE scoringsystemname='$tournament->{'scoringsystemname'}')\n";
print ");\n";

# useful
my $tsql = "(SELECT tournament FROM tournaments WHERE tournamentname='$tournament->{'tournamentname'}' AND season=( SELECT season FROM seasons WHERE seasonname='$tournament->{'seasonname'}' ))";

# Rankings, and players (FIXME: do clubs as well)
my $rankings = ccbs::db_fetch_all($dbh, 'SELECT * FROM tournamentrankings NATURAL JOIN players NATURAL JOIN countries WHERE tournament=?', $tno);
for my $rank (@$rankings) {
	printf "INSERT INTO players SELECT nextval('players_player_seq') AS player, '%s' AS nick, ( SELECT country FROM countries WHERE countryname='%s' ) AS country WHERE '%s' NOT IN ( SELECT nick FROM players );\n", $rank->{'nick'}, $rank->{'countryname'}, $rank->{'nick'};

	my $p = $rank->{'points'};
	$p = 'NULL' if (!defined($p));

	print "INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (\n";
	print "   $tsql,";
	print "   $rank->{'ranking'},\n";
	print "   (SELECT player FROM players WHERE nick='$rank->{'nick'}'),\n";
	print "   $p);\n";
}

# Skip tournamentparticipation, it is not needed except during the tournament.

# Rounds
my $rounds = ccbs::db_fetch_all($dbh, 'SELECT * FROM rounds WHERE tournament=?', $tno);
for my $round (@$rounds) {
	print "INSERT INTO rounds ( tournament, round, randomsongs, chosensongs, numqualifying ) VALUES (\n";
	print "   $tsql,\n";
	print "   $round->{'round'},\n";
	print "   $round->{'randomsongs'},\n";
	print "   $round->{'chosensongs'},\n";
	print "   $round->{'numqualifying'});\n";
}

# Groups
my $groups = ccbs::db_fetch_all($dbh, 'SELECT * FROM groups WHERE tournament=?', $tno);
for my $group (@$groups) {
	print "INSERT INTO groups ( tournament, round, parallel ) VALUES (\n";
	print "   $tsql,\n";
	print "   $group->{'round'},\n";
	print "   $group->{'parallel'});\n";
}

# Round random songs
my $rrs = ccbs::db_fetch_all($dbh, 'SELECT * FROM roundrandomsongs NATURAL JOIN songs WHERE tournament=?', $tno);
for my $rr (@$rrs) {
	(my $title = $rr->{'title'}) =~ s/'/\\'/g;

	print "INSERT INTO roundrandomsongs ( tournament, round, parallel, song ) VALUES (\n";
	print "   $tsql,\n";
	print "   $rr->{'round'},\n";
	print "   $rr->{'parallel'},\n";
	print "   ( SELECT song FROM songs WHERE lower(title)=lower('$title') ));\n";
}

# Round participation
my $rps = ccbs::db_fetch_all($dbh, 'SELECT * FROM roundparticipation NATURAL JOIN players WHERE tournament=?', $tno);
for my $rp (@$rps) {
	print "INSERT INTO roundparticipation ( tournament, round, parallel, player, position ) VALUES (\n";
	print "   $tsql,\n";
	print "   $rp->{'round'},\n";
	print "   $rp->{'parallel'},\n";
	print "   ( SELECT player FROM players WHERE nick='$rp->{'nick'}' ),\n";
	print "   $rp->{'position'});\n";
}

# And finally, scores
my $scores = ccbs::db_fetch_all($dbh, 'SELECT * FROM scores NATURAL JOIN songs NATURAL JOIN players WHERE tournament=?', $tno);
for my $score (@$scores) {
	my $chosen = $score->{'chosen'} ? 't' : 'f';
	(my $title = $score->{'title'}) =~ s/'/\\'/g;

	print "INSERT INTO scores ( tournament, round, parallel, player, songnumber, song, playmode, difficulty, chosen, score ) VALUES (\n";
	print "   $tsql,\n";
	print "   $score->{'round'},\n";
	print "   $score->{'parallel'},\n";
	print "   ( SELECT player FROM players WHERE nick='$score->{'nick'}' ),\n";
	print "   $score->{'songnumber'},\n";
	print "   ( SELECT song FROM songs WHERE lower(title)=lower('$title') ),\n";
	print "   '$score->{'playmode'}',\n";
	print "   '$score->{'difficulty'}',\n";
	print "   '$chosen',\n";
	print "   $score->{'score'});\n";
}

print "commit;\n";

$dbh->disconnect;

