#!/usr/bin/perl -w
#
# usage: ./db-idempotent-populate <Oceanname>
#  creates or updates OCEAN-Oceanname.db
#  from master-master.txt

# This is part of ypp-sc-tools, a set of third-party tools for assisting
# players of Yohoho Puzzle Pirates.
#
# Copyright (C) 2009 Ian Jackson <ijackson@chiark.greenend.org.uk>
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# Yohoho and Puzzle Pirates are probably trademarks of Three Rings and
# are used without permission.  This program is not endorsed or
# sponsored by Three Rings.

use strict (qw(vars));

use DBI;

use Commods;
use CommodsDatabase;

@ARGV==1 or die;
my ($oceanname) = @ARGV;

#---------- setup ----------

parse_info_serverside();
parse_info_serverside_ocean($oceanname);
our $ocean= $oceans{$oceanname};

db_setocean($oceanname);
db_writer();
db_connect();

#---------- schema ----------

foreach my $bs (qw(buy sell)) {
    db_doall(<<END)
 CREATE TABLE IF NOT EXISTS $bs (
	commodid	INTEGER			NOT NULL,
	islandid	INTEGER			NOT NULL,
	stallid		INTEGER			NOT NULL,
	price		INTEGER			NOT NULL,
	qty		INTEGER			NOT NULL,
	PRIMARY KEY (commodid, islandid, stallid)
 );
 CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
 CREATE INDEX IF NOT EXISTS ${bs}_by_price  ON $bs (commodid, price, islandid);
END
    ;
}

db_doall(<<END)
 CREATE TABLE IF NOT EXISTS commods (
	commodid	INTEGER	PRIMARY KEY AUTOINCREMENT NOT NULL,
	commodname	TEXT	UNIQUE		NOT NULL,
	unitmass	INTEGER,
	unitvolume	INTEGER
 );
 CREATE TABLE IF NOT EXISTS islands (
	islandid	INTEGER	PRIMARY KEY AUTOINCREMENT NOT NULL,
	islandname	TEXT	UNIQUE		NOT NULL,
	archipelago	TEXT			NOT NULL
 );
 CREATE TABLE IF NOT EXISTS stalls (
	stallid		INTEGER	PRIMARY KEY AUTOINCREMENT NOT NULL,
	islandid	INTEGER			NOT NULL,
	stallname	TEXT			NOT NULL,
	UNIQUE (islandid, stallname)
 );
 CREATE TABLE IF NOT EXISTS uploads (
	islandid	INTEGER PRIMARY KEY	NOT NULL,
	timestamp	INTEGER			NOT NULL,
	message		TEXT			NOT NULL,
	clientspec	TEXT			NOT NULL,
	serverspec	TEXT			NOT NULL
 );
 CREATE TABLE IF NOT EXISTS dists (
	aiid		INTEGER			NOT NULL,
	biid		INTEGER			NOT NULL,
	dist		INTEGER			NOT NULL,
	PRIMARY KEY (aiid, biid)
 );
END
    ;

$dbh->commit;

#---------- commodity list ----------

{
    my $insert= $dbh->prepare(<<'END')
 INSERT OR IGNORE INTO commods
     (unitmass,
      unitvolume,
      commodname)
     VALUES (?,?,?);
END
    ;
    my $update= $dbh->prepare(<<'END')
 UPDATE commods
     SET unitmass = ?,
         unitvolume = ?
     WHERE commodname = ?
END
    ;
    foreach my $commod (sort keys %commods) {
	my $c= $commods{$commod};
        die "no mass for $commod" unless defined $c->{Mass};
        die "no colume for $commod" unless defined $c->{Volume};
	my @qa= ($c->{Mass}, $c->{Volume}, $commod);
	$insert->execute(@qa);
	$update->execute(@qa);
    }
    $dbh->commit;
}

#---------- island list ----------

{
    my $sth= $dbh->prepare(<<'END')
 INSERT OR IGNORE INTO islands (islandname, archipelago) VALUES (?, ?);
END
    ;
    foreach my $archname (sort keys %$ocean) {
	my $arch= $ocean->{$archname};
	foreach my $islandname (sort keys %$arch) {
	    $sth->execute($islandname, $archname);
	}
    }
    $dbh->commit;
}

#---------- routes ----------

{
    foreach my $islandname (sort keys %{ $route_mysteries{$oceanname} }) {
	warn "$route_mysteries{$oceanname}{$islandname} routes".
	    " for unknown island $islandname\n";
    }

    my $allroutes= $routes{$oceanname};

    my @propqueue= ();

    sub distance_set_propagate ($$$$) {
	my ($lev, $start, $upto, $start2upto) = @_;
	$allroutes->{$start}{$upto}= $start2upto;
	push @propqueue, [ $lev, $start, $upto ];
    }

    sub distance_propagate_now {
	my ($lev, $start, $upto) = @_;
	my $startref= $allroutes->{$start};
	my $start2upto= $startref->{$upto};
	my $uptoref=  $allroutes->{$upto};

	for my $next (keys %$uptoref) {
	    next if $next eq $upto;
	    my $unext= $uptoref->{$next};
	    next unless defined $unext;
	    distance_update("${lev}p", $start, $next, $start2upto + $unext);
	}
    }

    sub distance_update ($$$$) {
	my ($lev, $x, $y, $newdist) = @_;
	distance_update_one("${lev}x",$x,$y,$newdist);
	distance_update_one("${lev}y",$y,$x,$newdist);
    }

    sub distance_update_one ($$$$) {
	my ($lev, $x, $y, $newdist) = @_;
	my $xref= $allroutes->{$x};
	my $currently= $xref->{$y};
	return if defined($currently) and $currently <= $newdist;
	distance_set_propagate("${lev}o",$x,$y,$newdist);
    }

    foreach my $xn (keys %$allroutes) {
	my $routes= $allroutes->{$xn};
	distance_set_propagate('0', $xn, $xn, 0);
	foreach my $yn (keys %$routes) {
	    distance_set_propagate('0', $yn, $yn, 0);
	    distance_set_propagate('X', $xn, $yn, $routes->{$yn});
	    distance_set_propagate('Y', $yn, $xn, $routes->{$yn});
	}
    }
    my $ref;
    while ($ref= shift @propqueue) {
	distance_propagate_now(@$ref);
    }

    db_doall(<<END)
 DELETE FROM dists;
END
    ;
    my $sth= $dbh->prepare(<<'END')
 INSERT INTO dists VALUES
	((SELECT islandid FROM islands WHERE islandname == ?),
	 (SELECT islandid FROM islands WHERE islandname == ?),
	 ?);
END
    ;
    foreach my $xn (keys %$allroutes) {
	my $routes= $allroutes->{$xn};
	foreach my $yn (keys %$routes) {
	    $sth->execute($xn, $yn, $routes->{$yn});
	}
    }
    $dbh->commit();

    # select ia.islandname, ib.islandname,dists.dist from dists, islands as ia on dists.aiid = ia.islandid, islands as ib on dists.biid = ib.islandid order by ia.islandname, ib.islandname;
}

__DATA__