use strict;
use warnings;

use FindBin qw($Bin);
use Config::Auto;
use Data::Dumper;
use DBI;

#===============================================================================
# run create_core_data.sh locally against recent db clone and rebase production
# create config/settings/.<centre> dir with idle_timeout.yml & local_addr.yml
# create config/settings/<centre>.txt & static/<centre>.html
# new init.d/lims_fastcgi_<centre>; add <centre> to init.d/restart-lims
# configure fastcgi socket in apache2/default & Alias in apache2/default-ssl
# ensure 'view' definer user exists in users table eg raj@localhost vs raj@%
#===============================================================================

my $DB = $ARGV[0] or die 'ERROR: name of db to re-create is required';

my $src  = "$Bin/../setup/core_data.gz"; # data source
my $sql  = "$Bin/../setup/new_centre.sql"; # minimum data set (user = raj; etc)
my $dsn  = 'dbi:mysql:hilis4';
my $zcat = '/bin/zcat';

use vars qw($SQL_FOR_TABLE $FOREIGN_KEYS);
require "$Bin/../setup/lims.sql"; # provides $SQL_FOR_TABLE & $FOREIGN_KEYS

use lib "$Bin/../lib"; # warn $Bin; exit; # for Config::Auto to find L::L::Utils
my $cfg = Config::Auto::parse("$Bin/../config/lims_config.pl", format => 'perl');
# warn Dumper $cfg; # exit;

my $admin_user = $cfg->{settings}{admin_db_user}; # warn $admin_user;
my $admin_pwd  = $cfg->{settings}{admin_db_pwd};  # warn $admin_pwd;

my $dbh = DBI->connect(
	$dsn, $admin_user, $admin_pwd, # views need SUPER priviledges
	{ RaiseError => 1, PrintError => 0 }
);

$dbh->do($_) for (
    qq!DROP DATABASE IF EXISTS `$DB`!,
    qq!CREATE DATABASE `$DB`!,
    qq!USE `$DB`!,
);

# tables (not views):
print "tables: ...............................\n";
foreach ( sort keys %$SQL_FOR_TABLE ) { # skip views - need all tables to exist:
    next if $_ =~ /_view\Z/;  print $_, "\n";
    $dbh->do( qq!DROP TABLE IF EXISTS $_! );
    $dbh->do( $SQL_FOR_TABLE->{$_} );
}
# views:
print "views: ...............................\n";
foreach ( sort keys %$SQL_FOR_TABLE ) {  # do views last:
    next unless $_ =~ /_view\Z/; print $_, "\n";
    $dbh->do( qq!DROP VIEW IF EXISTS $_! );
    $dbh->do( $SQL_FOR_TABLE->{$_} );
}
# FK's:
print "foreign keys: ...............................\n";
foreach my $fk( sort keys %$FOREIGN_KEYS ) {  print $fk, "\n";
    $dbh->do($FOREIGN_KEYS->{$fk});
}

$dbh->disconnect;

# data:
print "data: ...............................\n";
system "$zcat $src | mysql $DB -u $admin_user -p$admin_pwd";
system "mysql -u $admin_user -p$admin_pwd $DB < $sql";
