#!/usr/bin/perl
# BEGIN BPS TAGGED BLOCK {{{
#
# COPYRIGHT:
#
# This software is Copyright (c) 1996-2026 Best Practical Solutions, LLC
#                                          <sales@bestpractical.com>
#
# (Except where explicitly superseded by other copyright notices)
#
#
# LICENSE:
#
# This work is made available to you under the terms of Version 2 of
# the GNU General Public License. A copy of that license should have
# been provided with this software, but in any event can be snarfed
# from www.gnu.org.
#
# This work 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, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301 or visit their web page on the internet at
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
#
#
# CONTRIBUTION SUBMISSION POLICY:
#
# (The following paragraph is not intended to limit the rights granted
# to you to modify and distribute this software under the terms of
# the GNU General Public License and is only of importance to you if
# you choose to contribute your changes and enhancements to the
# community by submitting them to Best Practical Solutions, LLC.)
#
# By intentionally submitting any modifications, corrections or
# derivatives to this work, or any other work intended for use with
# Request Tracker, to Best Practical Solutions, LLC, you confirm that
# you are the copyright holder for those contributions and you grant
# Best Practical Solutions,  LLC a nonexclusive, worldwide, irrevocable,
# royalty-free, perpetual, license to use, copy, create derivative
# works based on those contributions, and sublicense and distribute
# those contributions and any derivatives thereof.
#
# END BPS TAGGED BLOCK }}}
use 5.26.3;
use strict;
use warnings;

use lib "/usr/local/libdata/perl5/site_perl";
use lib "/usr/local/libdata/perl5/site_perl";

use Term::ReadKey;
use Getopt::Long;

$| = 1; # unbuffer all output.

my %args;
GetOptions(
    \%args,
    'dba=s', 'dba-password=s', 'prompt-for-dba-password',
);

no warnings 'once';
use RT::Interface::CLI qw(Init);
Init();

my $db_type = RT->Config->Get('DatabaseType') || '';
my $db_host = RT->Config->Get('DatabaseHost') || '';
my $db_port = RT->Config->Get('DatabasePort') || '';
my $db_name = RT->Config->Get('DatabaseName') || '';
my $db_user = RT->Config->Get('DatabaseUser') || '';
my $db_pass = RT->Config->Get('DatabasePassword') || '';

my $dba_user = $args{'dba'} || $ENV{'RT_DBA_USER'} || RT->Config->Get('DatabaseAdmin') || '';
my $dba_pass = $args{'dba-password'} || $ENV{'RT_DBA_PASSWORD'};

if ( !$args{force} && ( !defined $dba_pass || $args{'prompt-for-dba-password'} ) ) {
    $dba_pass = get_dba_password();
    chomp $dba_pass if defined($dba_pass);
}

my $dbh = get_admin_dbh();

unless ( $dbh ){
    print "Unable to connect to database.\n\n";
    exit;
}

my @sequence_tables = qw(
    ATTACHMENTS
    QUEUES
    LINKS
    PRINCIPALS
    GROUPS
    SCRIPCONDITIONS
    TRANSACTIONS
    SCRIPS
    OBJECTSCRIPS
    ACL
    GROUPMEMBERS
    CACHEDGROUPMEMBERS
    USERS
    TICKETS
    SCRIPACTIONS
    TEMPLATES
    OBJECTCUSTOMFIELDS
    OBJECTCUSTOMFIELDVALUES
    CUSTOMFIELDS
    CUSTOMFIELDVALUES
    ATTRIBUTES
    CLASSES
    ARTICLES
    TOPICS
    OBJECTTOPICS
    OBJECTCLASSES
    ASSETS
    CATALOGS
    CUSTOMROLES
    OBJECTCUSTOMROLES
    CONFIGURATIONS
    AUTHTOKENS
    SHORTENERS
    OBJECTCONTENTS
    SAVEDSEARCHES
    DASHBOARDS
    DASHBOARDSUBSCRIPTIONS
    );


my $found_tables = {};
foreach my $name ( $RT::Handle->_TableNames ) {
    my ($table) = grep lc($name) eq lc($_), @sequence_tables;
    next unless $table;
    warn "Found table $table";
    $found_tables->{$table}++;
}

print "Working with:\n"
    ."Type:\t$db_type\nHost:\t$db_host\nPort:\t$db_port\nName:\t$db_name\n"
    ."User:\t$db_user\nDBA:\t$dba_user" . ($args{'skip-create'} ? ' (No DBA)' : '') . "\n\n";

foreach my $table ( keys %$found_tables ){

    my $pg_seq = '_id_seq';

    # Two of the Pg sequences don't follow the standard _id_seq ending
    if ( $table eq 'OBJECTCUSTOMFIELDS' or $table eq 'OBJECTCUSTOMFIELDVALUES' ){
        $pg_seq = '_id_s';
    }

    fix_id_sequence($table, {
        Pg     => lc($table) . $pg_seq,
        Oracle => $table . '_seq',
    });
}

print "Sequence updates complete.\n\n";


sub fix_id_sequence {
    my ($table, $sequence_per_db) = @_;
    my $sequence = $sequence_per_db->{$db_type} or return;
    my ($max, $query);

    if ($db_type eq 'Oracle') {
        my $user = RT->Config->Get('DatabaseUser');
        $query = "SELECT MAX(ID) FROM $user.$table";
    }
    else {
        $query = "SELECT MAX(ID) FROM $table";
    }

    my $sth = $dbh->prepare($query) or die $dbh->errstr;
    $sth->execute();
    ($max) = $sth->fetchrow_array();

    my $next_id = ($max || 0) + 1;

    # Nothing to do if there are no rows
    return if $next_id == 1;

    warn "Resetting $sequence to $next_id for $table";

    my @sql;
    if ($db_type eq 'Pg') {
        @sql = "ALTER SEQUENCE $sequence RESTART WITH $next_id;";
    }
    elsif ($db_type eq 'Oracle') {
        my $user = RT->Config->Get('DatabaseUser');
        @sql = (
            "ALTER SEQUENCE $user.$sequence INCREMENT BY " . ($next_id - 1) . "",
            "SELECT $user.$sequence.nextval FROM dual",
            "ALTER SEQUENCE $user.$sequence INCREMENT BY 1",
        );
    }

    foreach my $statement ( @sql ) {
        my $rv = $dbh->do($statement);

        if ( not defined $rv ) {
            warn "Error: " . $dbh->errstr;
        }
    }
    return;
}

sub get_dba_password {
    return "" if $db_type eq 'SQLite';
    print "In order to create or update your RT database,"
        . " this script needs to connect to your "
        . " $db_type instance on $db_host (port '$db_port') as $dba_user\n";
    print "Please specify that user's database password below. If the user has no database\n";
    print "password, just press return.\n\n";
    print "Password: ";
    ReadMode('noecho');
    my $password = ReadLine(0);
    ReadMode('normal');
    print "\n";
    return ($password);
}

sub get_admin_dbh {
    return _get_dbh( RT::Handle->DSN, $dba_user, $dba_pass );
}

sub _get_dbh {
    my ($dsn, $user, $pass) = @_;
    my $dbh = DBI->connect(
        $dsn, $user, $pass,
        { RaiseError => 0, PrintError => 0 },
    );
    unless ( $dbh ) {
        my $msg = "Failed to connect to $dsn as user '$user': ". $DBI::errstr;
        if ( $args{'debug'} ) {
            require Carp; Carp::confess( $msg );
        } else {
            print STDERR $msg; exit -1;
        }
    }
    return $dbh;
}

