#!/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;

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

my $dbfn= "OCEAN-$oceanname.db";

our $dbh;

sub dbdoall ($) {
    foreach my $cmd (split /\;/, $_[0]) {
	$dbh->do("$cmd;") if $cmd =~ m/\S/;
    }
}

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

parse_masters_ocean($oceanname);
our $ocean= $oceans{$oceanname};

$dbh= DBI->connect("dbi:SQLite:$dbfn",'','',
		   { AutoCommit=>0,
		     RaiseError=>1, ShowErrorStatement=>1,
		     unicode=>1 })
    or die "$dbfn $DBI::errstr ?";

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

foreach my $bs (qw(buy sell)) {
    dbdoall(<<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
    ;
}

dbdoall(<<END)
 CREATE TABLE IF NOT EXISTS commodities (
	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,
	age		INTEGER			NOT NULL,
	clientspec	TEXT			NOT NULL,
	serverspec	TEXT			NOT NULL
 );
 CREATE TABLE IF NOT EXISTS distances (
	aiid		INTEGER			NOT NULL,
	biid		INTEGER			NOT NULL,
	distance	INTEGER			NOT NULL,
	PRIMARY KEY (aiid, biid)
 );
END
    ;

$dbh->commit;

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

{
    my $sth= $dbh->prepare(<<'END')
 INSERT OR IGNORE INTO commodities (commodname) values (?);
END
    ;
    foreach my $commod (sort keys %commods) {
	$sth->execute($commod);
    }
    $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";
}

#use Data::Dumper;
#print Dumper(\%routes);

__DATA__

  /* 'distances' will hold the final results. */
  create table distances (
  a varchar(40) not null references islands(name),
  b varchar(40) not null references islands(name),
  distance int not null,
  primary key (a,b) );

  /* Create a rule such that insertion into the distances table is only 
  possible if a shorter route or a new route is being recorded; all other 
  inserts are ignored. */

  create or replace rule update_only_if_shorter as
  on insert to distances
  where ((new.a,new.b) in (select a,b from distances))
  do instead
  update distances set distance=(case when new.distance<=distance then 
  new.distance else distance end)
  where a=new.a and b=new.b;

  /* Start by copying manually entered routes into distances */
  insert into distances (select a,b,distance from routes);
  /* Also all the reverse routes */
  insert into distances (select b,a,distance from routes);
  /* Also all the null routes */
  insert into distances (select distinct a,a,0 from routes);
  insert into distances (select distinct b,b,0 from routes);

  /* Now extend the distances table by computing new routes */
  insert into distances (select source.a,dest.b,
  min(source.distance+dest.distance)
  from distances source,distances dest
  where source.b=dest.a group by source.a,dest.b);

  /* Repeat that query until no new routes are added (i.e. select count(*) 
  from distances; returns the same value after the query as it does before) */