#!/usr/bin/perl

use DBI;
use Getopt::Std;

$mydir = `cd \$(dirname $0) 2>/dev/null; pwd`; chomp($mydir);
unshift @INC,("$mydir/../setup");
if( -f "$mydir/../setup/db_config.local") { require "db_config.local"; }
else { require "db_config"; }

###################################################
# Connect to the database
$dbh=DBI->connect("dbi:mysql:dbname=$dbname:host=$dbhost:port=$dbport",$dbuser,$dbpass,{
    AutoCommit=>0,
    PrintError=>0}) or print "Connect Failure:".$DBI::errstr."\n" and exit 2;
###################################################

# Remove eq_aaronic table
$sth = $dbh->prepare("DROP TABLE IF EXISTS `eq_aaronic`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_activity table to tc_activity
$sth = $dbh->prepare("RENAME TABLE `eq_activity` TO `tc_activity`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_appointment table to tc_appointment
$sth = $dbh->prepare("RENAME TABLE `eq_appointment` TO `tc_appointment`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change elder field in tc_appointment table to indiv
$sth = $dbh->prepare("ALTER TABLE `tc_appointment` CHANGE `elder` `indiv` INT(16)");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_assignment table to tc_assignment
$sth = $dbh->prepare("RENAME TABLE `eq_assignment` TO `tc_assignment`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_attendance table to tc_attendance
$sth = $dbh->prepare("RENAME TABLE `eq_attendance` TO `tc_attendance`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change elder field in tc_attendance table to indiv
$sth = $dbh->prepare("ALTER TABLE `tc_attendance` CHANGE `elder` `indiv` INT(16)");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_calling table to tc_calling
$sth = $dbh->prepare("RENAME TABLE `eq_calling` TO `tc_calling`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Remove eq_child table
$sth = $dbh->prepare("DROP TABLE IF EXISTS `eq_child`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_companionship table to tc_companionship
$sth = $dbh->prepare("RENAME TABLE `eq_companionship` TO `tc_companionship`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Remove aaronic field from tc_companionship
$sth = $dbh->prepare("ALTER TABLE `tc_companionship` DROP `aaronic`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change elder field in tc_companionship table to indiv
$sth = $dbh->prepare("ALTER TABLE `tc_companionship` CHANGE `elder` `indiv` INT(16)");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_district table to tc_district
$sth = $dbh->prepare("RENAME TABLE `eq_district` TO `tc_district`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_elder table to tc_indiv
$sth = $dbh->prepare("RENAME TABLE `eq_elder` TO `tc_indiv`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change elder field in tc_indiv table to indiv
$sth = $dbh->prepare("ALTER TABLE `tc_indiv` CHANGE `elder` `indiv` INT(16) NOT NULL AUTO_INCREMENT");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Add a new 'address' column to the tc_indiv table
$sth = $dbh->prepare("ALTER TABLE `tc_indiv` ADD `address` VARCHAR( 255 ) NULL DEFAULT NULL AFTER `name`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Add a new 'family' column to the tc_indiv table
$sth = $dbh->prepare("ALTER TABLE `tc_indiv` ADD `family` INT(16) UNSIGNED NULL DEFAULT NULL AFTER `email`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Add a new 'hh_position' column to the tc_indiv table
$sth = $dbh->prepare("ALTER TABLE `tc_indiv` ADD `hh_position` ENUM( 'Head of Household', 'Spouse', 'Other' ) NOT NULL DEFAULT 'Other' AFTER `family`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Add a new 'steward' column to the tc_indiv table
$sth = $dbh->prepare("ALTER TABLE `tc_indiv` ADD `steward` ENUM( 'High Priest', 'Elder', '' ) NULL DEFAULT NULL AFTER `priesthood`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename int_pri column to hti_pri in the tc_indiv table
$sth = $dbh->prepare("ALTER TABLE `tc_indiv` CHANGE `int_pri` `hti_pri` INT( 10 ) UNSIGNED NULL DEFAULT '1'");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename int_notes column to hti_notes in the tc_indiv table
$sth = $dbh->prepare("ALTER TABLE `tc_indiv` CHANGE `int_notes` `hti_notes` VARCHAR( 128 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_family table to tc_family
$sth = $dbh->prepare("RENAME TABLE `eq_family` TO `tc_family`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change elder_id field in tc_family table to indiv_id
$sth = $dbh->prepare("ALTER TABLE `tc_family` CHANGE `elder_id` `indiv_id` INT(16)");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Remove eq_parent table
$sth = $dbh->prepare("DROP TABLE IF EXISTS `eq_parent`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_participation table to tc_participation
$sth = $dbh->prepare("RENAME TABLE `eq_participation` TO `tc_participation`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change elder field in tc_participation table to indiv
$sth = $dbh->prepare("ALTER TABLE `tc_participation` CHANGE `elder` `indiv` INT(16)");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_ppi table to tc_interview
$sth = $dbh->prepare("RENAME TABLE `eq_ppi` TO `tc_interview`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change elder field in tc_interview table to indiv
$sth = $dbh->prepare("ALTER TABLE `tc_interview` CHANGE `elder` `indiv` INT(16)");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Remove aaronic field from tc_interview
$sth = $dbh->prepare("ALTER TABLE `tc_interview` DROP `aaronic`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change ppi field in tc_interview table to interview
$sth = $dbh->prepare("ALTER TABLE `tc_interview` CHANGE `ppi` `interview` INT(16) NOT NULL AUTO_INCREMENT");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Add a new 'interview_type' column to the tc_interview table
$sth = $dbh->prepare("ALTER TABLE `tc_interview` ADD `interview_type` enum('hti','ppi') NOT NULL DEFAULT 'hti' AFTER `eqpresppi`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# set all interviews to be ppi's if eqpresppi is 1
$sth = $dbh->prepare("UPDATE tc_interview SET interview_type='ppi' WHERE eqpresppi='1'");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# remove 'eqpresppi' from tc_interview
$sth = $dbh->prepare("ALTER TABLE `tc_interview` DROP `eqpresppi`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_presidency table to tc_presidency
$sth = $dbh->prepare("RENAME TABLE `eq_presidency` TO `tc_presidency`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change elder field in tc_presidency table to indiv
$sth = $dbh->prepare("ALTER TABLE `tc_presidency` CHANGE `elder` `indiv` INT(16)");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Remove eqpres field from tc_presidency
$sth = $dbh->prepare("ALTER TABLE `tc_presidency` DROP `eqpres`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_visit table to tc_visit
$sth = $dbh->prepare("RENAME TABLE `eq_visit` TO `tc_visit`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Add a new 'companion1' column to the tc_visit table
$sth = $dbh->prepare("ALTER TABLE `tc_visit` ADD `companion1` INT( 16 ) NULL AFTER `companionship`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Add a new 'companion2' column to the tc_visit table
$sth = $dbh->prepare("ALTER TABLE `tc_visit` ADD `companion2` INT( 16 ) NULL AFTER `companion1`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Add a new 'visit_type' column to the tc_visit table
$sth = $dbh->prepare("ALTER TABLE `tc_visit` ADD `visit_type` enum('presidency','hometeaching') NOT NULL DEFAULT 'hometeaching' AFTER `visited`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# set all visits currently marked as companionship=0 with "type" "presidency" and all other visits should set to "hometeaching".
$sth = $dbh->prepare("update tc_visit set visit_type='presidency' where companionship='0'");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Rename eq_willingness table to tc_willingness
$sth = $dbh->prepare("RENAME TABLE `eq_willingness` TO `tc_willingness`");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

# Change elder field in tc_willingness table to indiv
$sth = $dbh->prepare("ALTER TABLE `tc_willingness` CHANGE `elder` `indiv` INT(16)");
$sth->execute or die "-E- DB error: $DBI::errstr\n";

print "-> Done!\n";

###################################################
# Disconnect from the database
$dbh->disconnect();
###################################################