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

# This is part of the YARRG website.  YARRG is a tool and website
# 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 Affero 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 Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero 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;

my $trace;
if (@ARGV and $ARGV[0] eq '-D') {
	$trace=1;
	shift @ARGV;
}

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

$|=1;

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

parse_info_serverside();

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

$dbh->trace(1) if $trace;


#---------- schema update code ----------

our @need_compact;

sub table ($$) {
    my ($table, $fields) = @_;
    table_maycompact($table,undef,undef,$fields);
}

sub table_maycompact ($$$$) {
    my ($table, $cpact_idfield, $cpact_needupdates, $fields) = @_;

    #----- parse $fields -----

    my @want_fields;
    my @want_field_specs;
    my %want_field_specs;

    foreach my $fspec (split /\n/, $fields) {
	next unless $fspec =~ m/\S/;
	if ($fspec =~ m/^\s*\+/) {
	    push @want_field_specs, "\t".$';
	    next;
	} elsif ($fspec =~ m/^\s*(\w+)(\s+)(\w.*\S)\s*$/) {
	    my ($f,$spaces,$rhs) = ($1,$2,$3);
	    my $spec= "\t".$f.$spaces.$rhs;
	    push @want_fields, $f;
	    push @want_field_specs, $spec;
	    $want_field_specs{$f}= $spec;
	} else {
	    die "$table $fspec ?";
	}
    }

    my $want_field_specs= join ",\n", @want_field_specs;

    #----- ensure table exists -----

    db_doall(<<END);
 CREATE TABLE IF NOT EXISTS $table (
$want_field_specs
	);
END
    my @need_recreate;

    #----- check whether we need to remove autoinc -----

    if ($fields !~ /\bautoinc/i) {
	my $autoinc= $dbh->prepare(<<END);
 SELECT sql FROM sqlite_master
	WHERE type='table' and name=? and tbl_name=?
END
        $autoinc->execute($table,$table);
	my ($sql)= $autoinc->fetchrow_array();
	die unless defined $sql;
	push @need_recreate, 'remove autoinc'
	    if $sql =~ m/\bautoinc/i;
    }

    #----- check whether we need to add fields -----

    my $check= $dbh->prepare("SELECT * FROM $table LIMIT 1");
    $check->execute();
    my %have_fields;
    $have_fields{$_}=1 foreach @{ $check->{NAME_lc} };
    $check->finish();

    my @have_fields;
    my @have_field_specs;

    foreach my $f (@want_fields) {
	if ($have_fields{$f}) {
	    push @have_fields, $f;
	    push @have_field_specs, $want_field_specs{$f};
	} else {
	    push @need_recreate, "field $f";
	}
    }

    #----- Do we need to recreate ? -----
    return unless @need_recreate;
    # yes:

    print "    Recreating $table: ", join('; ',@need_recreate);

    my $have_fields= join ',', @have_fields;
    my $have_field_specs= join ",\n", @have_field_specs;

    db_doall(<<END);
 CREATE TEMPORARY TABLE aside_$table (
$have_field_specs
	);
 INSERT INTO aside_$table SELECT $have_fields FROM $table;

 DROP TABLE $table;
 CREATE TABLE $table (
$want_field_specs
	);

 INSERT INTO $table ($have_fields) SELECT $have_fields FROM aside_$table;

 DROP TABLE aside_$table;
END

    #----- Do we need to compact ids ? -----
    (print("\n"), return) unless
        defined $cpact_idfield
	and grep { m/^remove autoinc/ } @need_recreate;
    # yes:

    print "; will compact.\n";
    unshift @$cpact_needupdates, [ $table ], [ $cpact_idfield ];

    push @need_compact, {
	Table => $table,
	Id => $cpact_idfield,
	Updates => $cpact_needupdates,
	Fields => [ @want_fields ],
	FieldSpecs => $want_field_specs
	};
}


#---------- actual 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
    ;
}

table_maycompact('commods', 'commodid',
		 [ [ qw(buy sell) ], [ qw(commodid) ],
 ], <<END);
	commodid	INTEGER	PRIMARY KEY	NOT NULL
	commodname	TEXT	UNIQUE		NOT NULL
	unitmass	INTEGER
	unitvolume	INTEGER
	ordval		INTEGER
	commodclass	TEXT
	inclass		INTEGER
END

table_maycompact('islands', 'islandid',
		 [ [ qw(buy sell stalls uploads) ], [ qw(islandid) ], 
		   [ qw(dists routes) ], [ qw(aiid biid) ], 
 ], <<END);
	islandid	INTEGER	PRIMARY KEY	NOT NULL
	islandname	TEXT	UNIQUE		NOT NULL
	archipelago	TEXT			NOT NULL
END

table('stalls', <<END);
	stallid		INTEGER	PRIMARY KEY	NOT NULL
	islandid	INTEGER			NOT NULL
	stallname	TEXT			NOT NULL
	+ UNIQUE (islandid, stallname)
END

table('commodclasses', <<END);
	commodclass	TEXT	PRIMARY KEY	NOT NULL
	size		INTEGER
END

db_doall(<<END)
 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)
 );
 CREATE TABLE IF NOT EXISTS routes (
	aiid		INTEGER			NOT NULL,
	biid		INTEGER			NOT NULL,
	dist		INTEGER			NOT NULL,
	PRIMARY KEY (aiid, biid)
 );
 CREATE TABLE IF NOT EXISTS vessels (
	name		TEXT			NOT NULL,
	mass		INTEGER			NOT NULL,
	volume		INTEGER			NOT NULL,
	shot		INTEGER			NOT NULL,
	PRIMARY KEY (name)
 );
END
    ;


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

sub commodsortkey ($) {
    my ($commod) = @_;
    my $ordval= $commods{$commod}{Ordval};
    return sprintf "B %20d", $ordval if defined $ordval;
    return sprintf "A %s", $commod;
}

{
    my $insert= $dbh->prepare(<<'END');
 INSERT OR IGNORE INTO commods
     (unitmass,
      unitvolume,
      commodname)
     VALUES (?,?,?);
END
    my $setsizes= $dbh->prepare(<<'END');
 UPDATE commods
     SET unitmass = ?,
         unitvolume = ?
     WHERE commodname = ?
END
    my $setordval= $dbh->prepare(<<'END');
 UPDATE commods
     SET ordval = ?
     WHERE commodname = ?
END
    my $setclass= $dbh->prepare(<<'END');
 UPDATE commods
     SET commodclass = ?
     WHERE commodname = ?
END
    my $setinclass= $dbh->prepare(<<'END');
 UPDATE commods
     SET inclass = ?
     WHERE commodname = ?
END
    my %incl;
    foreach my $commod (sort {
	        commodsortkey($a) cmp commodsortkey($b)
	    } keys %commods) {
	my $c= $commods{$commod};
        die "no mass for $commod" unless defined $c->{Mass};
        die "no volume for $commod" unless defined $c->{Volume};
	
	my @qa= ($c->{Mass}, $c->{Volume}, $commod);
	$insert->execute(@qa);
	$setsizes->execute(@qa);
	$setordval->execute($c->{Ordval} || 0, $commod);
	my $cl= $c->{Class};
	$setclass->execute($cl, $commod);

	if (defined $c->{Ordval} and defined $cl) {
	    $incl{$cl}++;
	    $setinclass->execute($incl{$cl}, $commod);
	} elsif (defined $cl) {
	    $incl{$cl} += 0;
	}
    }
    db_doall(<<END);
 DELETE FROM commodclasses;
END
    my $addclass= $dbh->prepare(<<'END');
 INSERT INTO commodclasses
     (commodclass, size)
     VALUES (?,?)
END
    foreach my $cl (sort keys %incl) {
	$addclass->execute($cl, $incl{$cl});	
    }

    my $search= $dbh->prepare(<<'END');
 SELECT commodname,commodid FROM commods;
END
    my %check;
    foreach my $bs (qw(buy sell)) {
	$check{$bs}= $dbh->prepare(<<END);
 SELECT islandname,stallname,price,qty
   FROM $bs
   JOIN stalls USING (stallid)
   JOIN islands ON ($bs.islandid = islands.islandid)
   WHERE commodid = ? LIMIT 1
END
    }
    my $delete= $dbh->prepare(<<'END');
 DELETE FROM commods WHERE commodid = ?
END
    $search->execute();
    my $any=0;
    while (my $row= $search->fetchrow_hashref()) {
	next if defined $commods{$row->{'commodname'}};
	print $any++ ? '; ' : "    Dropping old commodities: ",
	      $row->{'commodname'};
	foreach my $bs (qw(buy sell)) {
	    $check{$bs}->execute($row->{'commodid'});
	    my $problem= $check{$bs}->fetchrow_hashref();
	    if ($problem) {
		print "\n";
		die <<END

FATAL ERROR
    Removed commodity
       $row->{'commodid'}
       $row->{'commodname'}
    but
       $bs
       $problem->{'islandname'}
       $problem->{'stallname'}
       $problem->{'qty'} at $problem->{'price'}
END
            }
	}
	$delete->execute($row->{'commodid'});
    }
    print ".\n" if $any;
    db_check_referential_integrity();
}


#---------- vessel types ----------
{
    my $idempotent= $dbh->prepare(<<'END')
 INSERT OR REPLACE INTO vessels (name, shot, mass, volume)
                         VALUES (?,?,?,?)
END
    ;
    foreach my $name (sort keys %vessels) {
	my $v= $vessels{$name};
	my $shotdamage= $shotname2damage{$v->{Shot}};
	die "no shot damage for shot $v->{Shot} for vessel $name"
	    unless defined $shotdamage;
	my @qa= ($name, $shotdamage, map { $v->{$_} } qw(Mass Volume));
	$idempotent->execute(@qa);
    }
}


#---------- compact IDs ----------

sub getminmax ($$$) {
    my ($tab,$minmax,$f) = @_;
    my $sth= $dbh->prepare("SELECT $minmax($f) FROM $tab");
    $sth->execute();
    my ($val)= $sth->fetchrow_array();
    return defined($val) ? $val : '?';
}

foreach my $cp (@need_compact) {
    print "    Compacting $cp->{Table}";
    my $tab= $cp->{Table};
    my $id= $cp->{Id};
    my $tmp_field_specs= $cp->{FieldSpecs};
    my $fields= join ',', @{$cp->{Fields}};
    $tmp_field_specs =~ s/\bprimary key\b/UNIQUE/i or
	die "$tab $tmp_field_specs ?";
    db_doall(<<END);
 CREATE TABLE aside_$tab (
	new_$id		INTEGER PRIMARY KEY NOT NULL,
$tmp_field_specs
 );
 INSERT INTO aside_$tab ($fields)
	SELECT $fields
	FROM $tab;
END
    my $oldmax= getminmax($tab,'max',$id);
    my $offset= $oldmax+1;
    
    printf(" %s %s..%d=>1..%d:",
	   $cp->{Id},
	   getminmax($tab,'min',$id),
	   $oldmax,
	   getminmax("aside_$tab",'max',"new_$id"));
    my @updates= @{ $cp->{Updates} };
    while (@updates) {
	my $utabs= shift @updates;
	my $ufields= shift @updates;
	foreach my $utab (@$utabs) {
	    printf(" %s",$utab);
	    my $fh= '.';
	    foreach my $ufield (@$ufields) {
		printf("%s%s",$fh,$ufield); $fh=',';
		db_doall(<<END);
 UPDATE $utab
    SET $ufield = $offset +
        (SELECT new_$id FROM aside_$tab
          WHERE aside_$tab.$id = $utab.$ufield);
 UPDATE $utab
    SET $ufield = $ufield - $offset;
END
            }
	}
    }
    print "\n";
}

#---------- put it all into effect ----------

db_chkcommit();

{
    local $dbh->{AutoCommit} = 1;
    $dbh->do('VACUUM');
}