#!/usr/bin/perl
#
# This script should hopefully work out at what state the database is and, if
# the commit argument is provided, run the right schema files to bring it up to
# date. Let us know if it doesn't work; as with any upgrade script, do take a
# backup of your database before running.
#
# update-schema [--commit]

use strict;
use warnings;

# Horrible boilerplate to set up appropriate library paths.
use FindBin;
use lib "$FindBin::Bin/../commonlib/perllib";

use mySociety::Config;
use mySociety::DBHandle qw(dbh);
use mySociety::MaPit;

mySociety::Config::set_file("$FindBin::Bin/../conf/general");
mySociety::DBHandle::configure(
    Name => mySociety::Config::get('FMS_DB_NAME'),
    User => mySociety::Config::get('FMS_DB_USER'),
    Password => mySociety::Config::get('FMS_DB_PASS'),
    Host => mySociety::Config::get('FMS_DB_HOST', undef),
    Port => mySociety::Config::get('FMS_DB_PORT', undef)
);

my $commit = 0;
$commit = 1 if @ARGV && $ARGV[0] eq '--commit';

my $nothing = 1;
my $current_version = get_db_version();
print "Current database version = $current_version\n";
print "= Dry run =\n" unless $commit;

for my $path (glob("$FindBin::Bin/../db/schema_*")) {
    my ($name) = $path =~ /schema_(.*)\.sql$/;
    next if $name le $current_version;
    next if $name =~ /$current_version-/; # For number only match
    print "* $name\n";
    $nothing = 0;
    next unless $commit;

    open(FP, $path) or die $!;
    my @statements;
    my $s = '';
    my $in_function = 0;
    while(<FP>) {
        next if /^--/; # Ignore comments
        $s .= $_;
        # Functions may have semicolons within them
        $in_function = 1 if /create function/i;
        $in_function = 0 if /language 'plpgsql'/i;
        if ($s =~ /;/ && !$in_function) {
            push @statements, $s;
            $s = '';
        }
    }
    close FP;

    foreach my $st (@statements) {
        dbh()->do($st);
    }
}

if ( $commit && $current_version lt '0028' ) {
    print "Bodies created, fetching names from mapit\n";
    my $area_ids = dbh()->selectcol_arrayref('SELECT area_id FROM body_areas');
    if ( @$area_ids ) {
        my $areas = mySociety::MaPit::call('areas', $area_ids);
        foreach (values %$areas) {
            dbh()->do('UPDATE body SET name=? WHERE id=?', {}, $_->{name}, $_->{id});
        }
        dbh()->do('COMMIT');
    }
}

print "Nothing to do\n" if $nothing;

# ---

# By querying the database schema, we can see where we're currently at
# (assuming schema change files are never half-applied, which should be the case)
sub get_db_version {
    return '0028' if table_exists('body');
    return '0027' if column_exists('problem', 'subcategory');
    return '0026' if column_exists('open311conf', 'send_extended_statuses');
    return '0025' if column_like('alert_type', "ref='new_problems'", 'item_where', 'duplicate');
    return '0024' if column_exists('contacts', 'non_public');
    return '0023' if column_exists('open311conf', 'can_be_devolved');
    return '0022' if column_exists('problem', 'interest_count');
    return '0021' if column_exists('problem', 'external_source');
    return '0020' if column_exists('open311conf', 'suppress_alerts');
    return '0019' if column_exists('users', 'title');
    return '0018' if column_exists('open311conf', 'comment_user_id');
    return '0017' if column_exists('open311conf', 'send_comments');
    return '0016' if column_exists('comment', 'send_fail_count');
    return '0015-add_send_method_used_column_to_problem' if column_exists('problem', 'send_method_used');
    return '0015-add_extra_to_comment' if column_exists('comment', 'extra');
    return '0014' if column_exists('problem', 'send_fail_count');
    return '0013-add_send_method_column_to_open311conf' if column_exists('open311conf', 'send_method');
    return '0013-add_external_id_to_comment' if column_exists('comment', 'external_id');
    return '0012' if column_exists('problem', 'geocode');
    return '0011' if column_exists('contacts', 'extra');
    return '0010' if table_exists('open311conf');
    return '0009-update_alerts_problem_state_queries' if column_like('alert_type', "ref='new_problems'", 'item_where', 'investigating');
    return '0009-add_extra_to_problem' if column_exists('problem', 'extra');
    return '0008' if 0;
    return '0007' if column_exists('comment', 'problem_state');
    return '0006' if 0;
    return '0005-add_council_user_flag' if column_exists('users', 'from_council');
    return '0005-add_abuse_flags_to_users_and_reports' if column_exists('problem', 'flagged');
    return '0000';
}

# Returns true if a table exists
sub table_exists {
    my $table = shift;
    return dbh()->selectrow_array('select count(*) from pg_tables where tablename = ?', {}, $table);
}

# Returns true if a column of table exists
sub column_exists {
    my ( $table, $column ) = @_;
    return dbh()->selectrow_array('select count(*) from pg_class, pg_attribute WHERE pg_class.relname=? AND pg_attribute.attname=? AND pg_class.oid=pg_attribute.attrelid AND pg_attribute.attnum > 0', {}, $table, $column);
}

# Returns true if a column of a row in a table contains some text
sub column_like {
    my ( $table, $where, $column, $contents ) = @_;
    return dbh()->selectrow_array("select count(*) from $table WHERE $where AND $column LIKE ?", {}, "%$contents%");
}