#!/usr/bin/perl -w

use strict;
use warnings;
use DBI;

my %config = do '/root/bill_config.pl';

my $db_billing_name = $config{db_billing_name};
my $db_prebilling_name = $config{db_prebilling_name};
my $db_host = $config{db_host};
my $db_user = $config{db_user};
my $db_pass = $config{db_pass};

my $dbb = DBI->connect("DBI:mysql:" . $db_billing_name . ":" . $db_host, $db_user, $db_pass);
my $dbp = DBI->connect("DBI:mysql:" . $db_prebilling_name . ":" . $db_host, $db_user, $db_pass);

my ($class,$prefix,$descr,$rset_price);

my $sthp = $dbp->prepare("SELECT `dest`,`descr`,`price_rset` FROM `iptel_price_op_2016`;");
=pod
$sthp->execute;
$sthp->bind_columns(\$prefix,\$descr,\$rset_price);
my $count = 0;
while ($sthp->fetch) {
    $class = ($prefix =~ /^7/) ? 1 : 2;
    $descr = $dbb->quote($descr);
    my $sthb = $dbb->prepare("SELECT `descr` FROM `tel_cat` WHERE `zone_num` = $prefix AND `cat_id`=7;");
    $sthb->execute;
    my $lb_descr;
    $sthb->bind_columns(\$lb_descr);
    $sthb->fetch;
    $sthb->finish;
    unless (defined($lb_descr)) {
        $count++;
        my $query = "INSERT INTO `tel_cat` (`zone_num`, `cat_id`, `class`, `descr`) VALUES (" . $prefix. ", 7, " . $class . ", " . $descr . ");";
#       $dbb->do($query);
        print $query . "\n";
    }
}
print "Count inserted prefixes: " . $count . "\n";
$sthp->finish;

my $tar_cat = 2;
$sthp = $dbp->prepare("SELECT `price_rset` FROM `iptel_price_op_2016` GROUP BY `price_rset` ORDER BY `price_rset`;");

$sthp->execute;
$sthp->bind_columns(\$rset_price);
while ($sthp->fetch) {
    $descr = $dbb->quote($rset_price . " руб.");
    my $query = "INSERT INTO `categories` (`tar_id`, `cat_idx`, uuid, `oper_id`, `above`, `descr`, `free_seconds`, `round_seconds`, `min_charge_dur`, `enabled`) VALUES (291, " . $tar_cat . ", NULL, 1, " . $rset_price . ", " . $descr . ", 3, 60, 60, 0);";
    $dbb->do($query);
    print $query . "\n";
    $tar_cat++;
}
$sthp->finish;
print "Last inserted categories: " . $tar_cat-- . "\n";
=cut

$sthp = $dbp->prepare("SELECT `dest`, `price` FROM `test_price` ORDER BY `price`;");
$sthp->execute;
$sthp->bind_columns(\$prefix, \$rset_price);
while ($sthp->fetch) {
    my ($cat_idx,$zone_id);
    my $sthb = $dbb->prepare("SELECT `cat_idx` FROM `categories` WHERE `above` = " . $rset_price . " AND `tar_id` = 305;");
    $sthb->execute;
    $sthb->bind_columns(\$cat_idx);
    $sthb->fetch;
    $sthb->finish;

    $sthb = $dbb->prepare("SELECT `zone_id` FROM `tel_cat` WHERE `zone_num` = " . $prefix . " AND `cat_id` = 7;");
    $sthb->execute;
    $sthb->bind_columns(\$zone_id);
    $sthb->fetch;
    $sthb->finish;

    my $query = "INSERT INTO `tel_cat_idx` (`tar_id`, `cat_idx`, `zone_id`, `direction`) VALUES (305, " . $cat_idx . ", " . $zone_id . ", 1);";
    $dbb->do($query);
    print $query . "\n";
}

$sthp->finish;
$dbb->disconnect;
$dbp->disconnect;