#!/usr/bin/perl -CSD
# Purpose: template toolkit helper script
################################################################################

use strict;
use warnings;
use English;
use YAML::XS qw(LoadFile);
use Hash::Merge qw(merge);
use DBI;
use Capture::Tiny qw(capture);
use Config::Tiny;
use DateTime::TimeZone;
use DateTime::TimeZone::Catalog;
use List::Util qw(any none);

sub sync_extra_sockets;
sub db_connect;
sub read_ngcp_roles;

my $config = {};
foreach my $file (@ARGV) {
  $config = merge($config, LoadFile($file));
}

my $dbcredentials = "/etc/mysql/sipwise_extra.cnf";
my $ngcp_roles_file = "/etc/default/ngcp-roles";

my $ngcp_roles = read_ngcp_roles();
my $ngcp_type = $ngcp_roles->{NGCP_TYPE};
my $ngcp_is_proxy = $ngcp_roles->{NGCP_IS_PROXY};

my @db_conn_types = qw(central pair local);
my $db_conn = {};
my $db_info = {};

my $dbname = $config->{ossbss}->{provisioning}->{database}->{name};

unless(defined $dbname) {
  print "Error: Could not determine provisioning db name\n";
  exit 1;
}

foreach my $db_conn_type (@db_conn_types) {
    next if $db_conn_type eq 'local' &&
            ($ngcp_type ne 'carrier' || $ngcp_is_proxy ne "yes");

    my $dbhost = $config->{database}->{$db_conn_type}->{dbhost};
    my $dbport = $config->{database}->{$db_conn_type}->{dbport};

    unless ($dbhost) {
      print "Error: Could not determine '$db_conn_type' db hostname\n";
      exit 1;
    }
    unless ($dbport) {
      print "Error: Could not determine '$db_conn_type' db port\n";
      exit 1;
    }

    $db_conn->{$db_conn_type} = db_connect($dbhost, $dbport);
    $db_info->{$db_conn_type} = { host => $dbhost, port => $dbport };
}

exit 1 unless(sync_lb_and_extra_sockets(
  $config->{kamailio}->{lb}->{extra_sockets},
  1, 1, 1));

exit 1 unless(sync_rtp_interfaces(
  { map {my $tmp = $_; $tmp =~ s/:.*//; ($_ => $_, $tmp => $tmp)} @{$config->{rtp_interfaces}} },
  1, 1, 1));

exit 1 unless(sync_smsc_peers(
  { map { ($_->{id} => $_->{id}) } @{$config->{sms}->{smsc}} },
  0, 1, 0));

exit 1 unless(sync_general_timezone($config->{general}->{timezone}));

exit 1 unless(sync_db_timezones());

exit 1 unless(sync_timezone_version());

map { $_->disconnect; } values %${db_conn};

exit 0;

sub read_ngcp_roles {
    my $root = Config::Tiny->read($ngcp_roles_file, 'utf8');

    if (my $err = $Config::Tiny::errstr) {
        print "Error when reading $ngcp_roles_file: $err\n";
        exit 1;
    }

    unless ($root->{_}) {
        print "Unexpected result from reading $ngcp_roles_file\n";
        exit 1;
    }

    map { $root->{_}{$_} =~ s/(^['"]|['"]$)//g } keys %{$root->{_}};

    return $root->{_};
}

sub db_connect {
    my ($dbhost, $dbport) = @_;

    my $dbh = DBI->connect("DBI:mysql:database=${dbname};host=${dbhost};port=${dbport};"
                           . "mysql_read_default_file=${dbcredentials}",
                           "", "", { PrintError => 0 });
    unless ($dbh) {
        die "Error: Could not connect to database '$dbname' at '$dbhost:$dbport' using '${dbcredentials}': $DBI::errstr\n";
    }

    return $dbh;
}

sub lb_and_extra_sockets_sync {
    my $extra_sockets_config = shift;
    my $usr_pref = shift;
    my $dom_pref = shift;
    my $peer_pref = shift;
    my $pref_name = shift;

    my $hosts = $config->{hosts}
        // die 'Error: cannot fetch hosts from $config->{hosts}';

    my $lb_config = $config->{kamailio}{lb}
        // die 'Error: cannot fetch kamailio lb config from $config->{kamailio}{lb}';

    my %lb_pairs = ();
    my %ports = (
        udp => $lb_config->{port},
        tcp => $lb_config->{port},
        tls => $lb_config->{tls}{port},
    );

    for my $host (keys %{$hosts}) {
        my $host_ref = $hosts->{$host};
        my $ifaces = $host_ref->{interfaces};

        next unless $host_ref->{status} eq 'online';
        next unless any { $_ eq 'lb' } @{$host_ref->{role}};

        foreach my $iface (@{$ifaces}) {
            my $iface_ref = $host_ref->{$iface};
            next if none { $_ eq 'sip_ext' } @{$iface_ref->{type}};

            my $shared_ips = $iface_ref->{shared_ip} // next;
            my $shared_ips_cnt = scalar @{$shared_ips};
            my $pair = substr $host, 0, -1;

            foreach my $ip (@{$shared_ips}) {
                for my $proto (qw(udp tcp tls)) {
                    my $port = $ports{$proto};
                    $shared_ips_cnt > 1
                        ? $lb_pairs{"$pair:$iface:$ip:$proto"} = "$proto:$ip:$port"
                        : $lb_pairs{"$pair:$iface:$proto"} = "$proto:$ip:$port";
                }
            }
        }
    }

    generic_enum_sync(
        { %lb_pairs,  ## no critic (ProhibitCommaSeparatedStatements)
          $extra_sockets_config && ref $extra_sockets_config eq 'HASH'
            ? %{$extra_sockets_config}
            : (),
        },
        $usr_pref, $dom_pref, $peer_pref,
        $pref_name
    );

    return 1;
}

sub generic_enum_sync {
    my $config_hash = shift;
    my $usr_pref = shift;
    my $dom_pref = shift;
    my $peer_pref = shift;
    my $pref_name = shift;

    my $dbh = $db_conn->{central};

    my $sth = $dbh->prepare("select id from voip_preferences where attribute=?");
    $sth->execute($pref_name);
    my $res = $sth->fetchrow_hashref();
    unless (defined $res) {
        print "Error: Could not find preference '$pref_name' in db\n";
        return;
    }
    my $pref_id = $res->{id};
    $sth->finish;

    my $enum_insert_sth = $dbh->prepare("insert into voip_preferences_enum (preference_id, label, value, usr_pref, dom_pref, peer_pref) values(?, ?, ?, ?, ?, ?)");
    my $enum_update_sth = $dbh->prepare("update voip_preferences_enum set value=? where id=?");
    my $enum_delete_sth = $dbh->prepare("delete from voip_preferences_enum where id=?");
    $sth = $dbh->prepare("select id, label, value from voip_preferences_enum where preference_id=?");
    $sth->execute($pref_id);

    my $db_hash = $sth->fetchall_hashref('label');
    $sth->finish;

    foreach my $row (sort keys %{$db_hash}) {
        $row = $db_hash->{$row};
        next if $row->{label} eq 'default';
        if (!exists $config_hash->{$row->{label}}) {
            print "$pref_name $row->{label} does not exist anymore in config, delete from db\n";
            $enum_delete_sth->execute($row->{id});
        } elsif ($config_hash->{$row->{label}} ne $row->{value}) {
            print "update $pref_name $row->{label}=$row->{value} to $row->{label}=$config_hash->{$row->{label}} in db\n";
            $enum_update_sth->execute($config_hash->{$row->{label}}, $row->{id});
            delete $config_hash->{$row->{label}};
        } else {
            print "$pref_name $row->{label}=$row->{value} is sync between config and db\n";
            delete $config_hash->{$row->{label}};
        }
    }
    foreach my $label (sort keys %{$config_hash}) {
        print "insert new $pref_name $label=$config_hash->{$label} from config into db\n";
        $enum_insert_sth->execute($pref_id, $label, $config_hash->{$label},
                                  $usr_pref ? 1 : 0, $dom_pref ? 1 : 0,
                                  $peer_pref ? 1: 0);
    }

    $enum_insert_sth->finish;
    $enum_update_sth->finish;
    $enum_delete_sth->finish;

    return 1;
}

## lb nodes and kamailio.lb.extra_sockets handling: ##############################
sub sync_lb_and_extra_sockets {
    return lb_and_extra_sockets_sync(@_, 'outbound_socket');
}

## rtp_* interfaces handling: ##############################
sub sync_rtp_interfaces {
    return generic_enum_sync(@_, 'rtp_interface');
}
#
## smsc_* interfaces handling: ##############################
sub sync_smsc_peers {
    return generic_enum_sync(@_, 'smsc_peer');
}

## general.timezone handling: ##############################
sub sync_general_timezone {
    if ($ngcp_type eq 'carrier' && $ngcp_is_proxy eq 'yes') {
        return do_sync_general_timezone(@_, 'pair') &&
               do_sync_general_timezone(@_, 'local');
    }
    return do_sync_general_timezone(@_, 'pair');
}

## /usr/share/zoneinfo into MariaDB ##############################
sub sync_db_timezones {
    if ($ngcp_type eq 'carrier' && $ngcp_is_proxy eq 'yes') {
        return do_sync_db_timezones(@_, 'pair') &&
               do_sync_db_timezones(@_, 'local');
    }
    return do_sync_db_timezones(@_, 'pair');
}

## check ngcp.timezone version and upgrade the database if the version is changed ##############################
sub sync_timezone_version {
    if ($ngcp_type eq 'carrier' && $ngcp_is_proxy eq 'yes') {
        return do_sync_timezone_version(@_, 'pair') &&
               do_sync_timezone_version(@_, 'local');
    }
    return do_sync_timezone_version(@_, 'pair');
}

sub do_sync_general_timezone {
    my $tz = shift;
    my $db_conn_type = shift;
    my $dbh = $db_conn->{$db_conn_type};
    my $ok = 1;
    my ($sql_log_bin) = $dbh->selectrow_array('SELECT @@sql_log_bin');

    eval {
        die "Error: general.timezone value is not set\n" unless $tz;

        $dbh->do("SET sql_log_bin=0");

        my ($current_tz) = $dbh->selectrow_array(<<SQL);
SELECT name FROM ngcp.timezone
SQL
        if ($DBI::err) {
            die "Error: Could not select from ngcp.timezone: $DBI::errstr\n";
        }

        return $ok if $current_tz && $current_tz eq $tz;

        $dbh->do(<<SQL, undef, $tz, $tz);
INSERT INTO ngcp.timezone SET name = ?
    ON DUPLICATE KEY UPDATE name = ?, modified_at = CURRENT_TIMESTAMP
SQL
        if ($DBI::err) {
            die "Error: Could not insert into ngcp.timezone: $DBI::errstr\n";
        }
    };
    if ($@) {
        print $@;
        $ok = 0;
    }
    $dbh->do("SET sql_log_bin=$sql_log_bin");

    return $ok;
}

sub do_sync_db_timezones {
    my $db_conn_type = shift;
    my $dbh = $db_conn->{$db_conn_type};
    my ($dbhost, $dbport) = @{$db_info->{$db_conn_type}}{qw(host port)};

    my ($tzinfo_version, undef, undef) = capture {
        system('dpkg-query -f "\${Version}" -W tzdata');
    };
    unless ($tzinfo_version) {
        print "Error: Could not retrieve tzdata package version\n";
        return;
    }
    chomp $tzinfo_version;

    my $sql = '';
    eval {
        $dbh->begin_work() or die "Cannot start tx: $DBI::errstr\n";
        my ($cur_tzinfo_version) = $dbh->selectrow_array(<<SQL)
SELECT version
  FROM ngcp.tzinfo_version
SQL
            or die "Cannot select from ngcp.tzinfo_version: $DBI::errstr\n";

        if ($cur_tzinfo_version eq $tzinfo_version) {
            return 1;
        }

        print "sync db timezones on host=$dbhost port=$dbport old=$cur_tzinfo_version new=$tzinfo_version\n";

        my ($out, $err, $rc) = capture {
            system("/usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo");
        };

        if ($rc) {
            print "Error: $err\n";
            return;
        }

        open(my $sql_stream, "<", \$out)
            or die "Cannot open sql stream: $ERRNO\n";
        binmode($sql_stream);
        $dbh->do('USE mysql')
            or die "Cannot use mysql database: $DBI::errstr\n";
        my $batch;
        my $delim = ';';
        while ($sql = <$sql_stream>) {
            if ($sql =~ m#^\s*\\d\s+(\S+)#) {
                $delim = $1;
            } else {
                if ($sql =~ /^(.*)\Q$delim\E\s*$/) {
                    if ($1) {
                        $batch .= $1;
                    }
                    $dbh->do($batch) or die "Cannot insert timezone data: $DBI::errstr\n";
                    $batch = undef;
                } else {
                    $batch .= $sql;
                }
            }
        }
        close $sql_stream;
        $dbh->do(<<SQL, undef, $tzinfo_version, $tzinfo_version)
INSERT INTO ngcp.tzinfo_version SET version = ?
    ON DUPLICATE KEY UPDATE version = ?, modified_at = CURRENT_TIMESTAMP
SQL
            or die "Cannot insert into ngcp.tzinfo_version: $DBI::errstr\n";
    };
    my $err = $EVAL_ERROR;
    if ($err) {
        print $err;
        $dbh->rollback();
        return;
    }
    $dbh->commit();

    return 1;
}

sub do_sync_timezone_version {
    my $db_conn_type = shift;
    my $dbh = $db_conn->{$db_conn_type};

    my $olson_tz_version = DateTime::TimeZone::Catalog::OlsonVersion();

    unless ($olson_tz_version) {
        print "Error: Could not fetch timezone catalog version\n";
        return;
    }

    my $sql = '';
    eval {
        $dbh->begin_work() or die "Cannot start tx: $DBI::errstr\n";
        my ($cur_tz_version) = $dbh->selectrow_array(<<SQL)
SELECT COALESCE(version,'')
  FROM ngcp.timezone
SQL
            or die "Cannot select from ngcp.timezone: $DBI::errstr\n";

        if ($cur_tz_version eq $olson_tz_version) {
            return 1;
        }

        my %links = DateTime::TimeZone->links;

        my $ch_upd_timezone = $dbh->prepare(<<SQL)
UPDATE billing.contacts
   SET timezone = ?
 WHERE timezone = ?
SQL
            or die "Cannot prepare sql: $DBI::errstr\n";

        foreach my $old (keys %links) {
            my $new = $links{$old};
            $ch_upd_timezone->execute($new, $old)
                or die "Cannot execute: $DBI::errstr\n";
        }

        $ch_upd_timezone->finish;

        $dbh->do(<<SQL, undef, $olson_tz_version)
UPDATE ngcp.timezone SET version = ?
SQL
            or die "Cannot update ngcp.timezone: $DBI::errstr\n";
    };
    my $err = $EVAL_ERROR;
    if ($err) {
        print $err;
        $dbh->rollback();
        return;
    }
    $dbh->commit();

    return 1;
}

## END OF FILE #################################################################
