<?php /** @noinspection ALL */

use Billing\Address;
use Billing\Balance;
use Billing\Card;
use Billing\Resource;
use Billing\Subscriber;
use Billing\Bill;
use Billing\Notification;
use Billing\Stat;
use Billing\Ticket;

include_once $_SERVER['DOCUMENT_ROOT'] . "/vendor/autoload.php";

require_once $_SERVER['DOCUMENT_ROOT'] . "/lib/datatypes/GPBMetadata/Types.php";

// MeekroDB configuration
DB::getMDB()->host = 'localhost';
DB::getMDB()->user = '';
DB::getMDB()->password = '';
DB::getMDB()->dbName = '';
DB::getMDB()->encoding = 'utf8';
//DB::getMDB()->debugMode();

class DB_utils
{
    public static $county = array(
        '1' => 'Dolj',
        '2' => 'Bacău',
        '3' => 'Harghita',
        '4' => 'Bistrița-Năsăud',
        '5' => 'Dâmbovița',
        '6' => 'Suceava',
        '7' => 'Botoșani',
        '8' => 'Brașov',
        '9' => 'București',
        '10' => 'Brăila',
        '11' => 'Hunedoara',
        '12' => 'Teleorman',
        '13' => 'Covasna',
        '14' => 'Tulcea',
        '15' => 'Timiș',
        '16' => 'Buzău',
        '17' => 'Prahova',
        '18' => 'Ilfov',
        '19' => 'Neamț',
        '20' => 'Cluj',
        '21' => 'Alba',
        '22' => 'Giurgiu',
        '23' => 'Argeș',
        '24' => 'Călărași',
        '25' => 'Bihor',
        '26' => 'Iași',
        '27' => 'Vâlcea',
        '28' => 'Vrancea',
        '29' => 'Arad',
        '30' => 'Ialomița',
        '31' => 'Caraș-Severin',
        '32' => 'Galați',
        '33' => 'Gorj',
        '34' => 'Constanța',
        '35' => 'Satu Mare',
        '36' => 'Maramureș',
        '37' => 'Mehedinți',
        '38' => 'Sălaj',
        '39' => 'Vaslui',
        '40' => 'Mureș',
        '41' => 'Sibiu',
        '42' => 'Olt'
    );
    public static $subscriberStatus = array(
        'Online' => 1,
        'Pending' => 2,
        'Roamer' => 3,
        'Written off' => 4,
        'Collection' => 5
    );
    public static $resourceStatus = array(
        'Pending' => 1,
        'Active' => 2,
        'Suspended' => 3,
        'Roamer' => 4,
        'Ported in' => 5,
        'Ported out' => 6,
        'Ported free' => 7
    );
    public static $months = array(
        '1' => 'January',
        '2' => 'February',
        '3' => 'March',
        '4' => 'April',
        '5' => 'May',
        '6' => 'June',
        '7' => 'July',
        '8' => 'August',
        '9' => 'September',
        '10' => 'October',
        '11' => 'November',
        '12' => 'December'
    );
    public static $currency = "€";
    public static $vat = 0.19;
    public static $date_format = "%Y-%m-%d %H:%i:%s";

    public static function getUsageType(int $id)
    {
        $row = DB::getMDB()->queryFirstRow("select name from service where id=%i", $id);
        if (is_null($row)) {
            return null;
        } else {
            return $row['name'];
        }
    }

    public static function getStat(int $id)
    {
        $row = DB::getMDB()->queryFirstRow("select * from stats where id=%i", $id);
        $stat = new Stat();
        $values = array();
        $stat->setId($id);

        if (is_null($row)) {
            for ($i = 0; $i < 25; $i++) {
                $values[] = 0;
            }
        } else {
            for ($i = 0; $i < 25; $i++) {
                $values[] = $row['param' . $i];
            }
        }

        $stat->setValues($values);
        return $stat;
    }

    public static function getCard(int $id)
    {
        $row = DB::getMDB()->queryFirstRow("select name, number, expiration, cvv from cards where id=%i", $id);
        if (is_null($row)) {
            return null;
        } else {
            $card = new Card();
            $card->setId($id);
            $card->setName($row['name']);
            $card->setNumber($row['number']);
            $card->setExpiration($row['expiration']);
            $card->setCvv($row['cvv']);

            return $card;
        }
    }

    public static function getBill(int $id)
    {
        $row = DB::getMDB()->queryFirstRow("select subscriber_id, status_id, msisdn, debt, svc1_cost, svc2_cost, svc3_cost, svc4_cost, unix_timestamp(sys_creation_date), billing_id, url, comment from bills where id=%i", $id);
        if (is_null($row)) {
            return null;
        } else {
            $bill = new Bill();
            $bill->setId($id);
            $bill->setSubscriberId($row['subscriber_id']);
            $bill->setStatusId($row['status_id']);
            $bill->setMsisdn($row['msisdn']);
            $bill->setDebt($row['debt']);
            $bill->setSvc1Cost($row['svc1_cost']);
            $bill->setSvc2Cost($row['svc2_cost']);
            $bill->setSvc3Cost($row['svc3_cost']);
            $bill->setSvc4Cost($row['svc4_cost']);
            $bill->setDate($row['unix_timestamp(sys_creation_date)']);
            $bill->setBillingId($row['billing_id']);
            $bill->setUrl($row['url']);
            $bill->setComment($row['comment']);

            return $bill;
        }
    }

    public static function getNotification(int $id)
    {
        $row = DB::getMDB()->queryFirstRow("select status, error, text, url, unix_timestamp(sys_creation_date) from notifications where id=%i", $id);
        if (is_null($row)) {
            return null;
        } else {
            $notification = new Notification();

            $notification->setId($id);
            $notification->setStatus($row['status']);
            $notification->setError($row['error']);
            $notification->setText($row['text']);
            $notification->setUrl($row['url']);
            $notification->setDate($row['unix_timestamp(sys_creation_date)']);

            return $notification;
        }
    }

    public static function getAddress(int $id)
    {
        $row = DB::getMDB()->queryFirstRow("select national_id, name, email, address, county_id, zip, city from address where id=%i", $id);
        if (is_null($row)) {
            return null;
        } else {
            $address = new Address();
            $address->setId($id);
            $address->setName($row['name']);
            $address->setSsn($row['national_id']);
            $address->setEmail($row['email']);
            $address->setAddress($row['address']);
            $address->setCountyId($row['county_id']);
            $address->setZip($row['zip']);
            $address->setCity($row['city']);

            return $address;
        }
    }

    public static function getResource(int $id)
    {
        $row = DB::getMDB()->queryFirstRow("select resource_type_id, resource_status_id, value from resource where id=%i", $id);
        if (is_null($row)) {
            return null;
        } else {
            $resource = new Resource();
            $resource->setId($id);
            $resource->setStatus($row['resource_status_id']);
            $resource->setType($row['resource_type_id']);
            $resource->setValue($row['value']);

            return $resource;
        }
    }

    public static function getBalance(int $id)
    {
        $row = DB::getMDB()->queryFirstRow("select balance, reserved from balance where id=%i", $id);
        if (is_null($row)) {
            return null;
        } else {
            $balance = new Balance();
            $balance->setId($id);
            $balance->setBalance($row['balance']);
            $balance->setReserved($row['reserved']);

            return $balance;
        }
    }

    public static function getSubscriber(int $id)
    {
        $row = DB::getMDB()->queryFirstRow("select msisdn, imsi, handset, bonus, billcycle, status_id, plan_id, balance_id, address_id, card_id, prepaid, postpaid from subscriber where id=%i", $id);
        if (is_null($row)) {
            return null;
        } else {
            $subscriber = new Subscriber();
            $subscriber->setId($id);
            $subscriber->setStatus($row['status_id']);
            $subscriber->setPlanId($row['plan_id']);
            $subscriber->setBillcycle($row['billcycle']);
            $subscriber->setPrepaid($row['prepaid']);
            $subscriber->setPostpaid($row['postpaid']);

            $balance = self::getBalance($row['balance_id']);
            $address = self::getAddress($row['address_id']);
            $card = self::getCard($row['card_id']);
            $msisdn = self::getResource($row['msisdn']);
            $imsi = self::getResource($row['imsi']);
            $handset = self::getResource($row['handset']);
            $bonus = self::getResource($row['bonus']);

            $subscriber->setBalance($balance);
            $subscriber->setCard($card);
            $subscriber->setMsisdn($msisdn);
            $subscriber->setImsi($imsi);
            $subscriber->setAddress($address);
            $subscriber->setHandset($handset);
            $subscriber->setBonus($bonus);

            return $subscriber;
        }
    }

    public static function insertCard(Card $card)
    {
        DB::getMDB()->insert('cards', array(
            'operator_id' => $_SESSION ['operator_id'],
            'name' => $card->getName(),
            'number' => $card->getNumber(),
            'expiration' => $card->getExpiration(),
            'cvv' => $card->getCvv()
        ));
    }

    public static function insertAddress(Address $address)
    {
        DB::getMDB()->insert('address', array(
            'operator_id' => $_SESSION ['operator_id'],
            'national_id' => $address->getSsn(),
            'name' => $address->getName(),
            'email' => $address->getEmail(),
            'address' => $address->getAddress(),
            'county_id' => $address->getCountyId(),
            'city' => $address->getCity(),
            'zip' => $address->getZip()
        ));
    }

    public static function insertSubscriber(Subscriber $sub)
    {
        DB::getMDB()->startTransaction();

        self::insertCard($sub->getCard());
        $sub->getCard()->setId(DB::getMDB()->insertId());

        self::insertAddress($sub->getAddress());
        $sub->getAddress()->setId(DB::getMDB()->insertId());

        if ($sub->getPrepaid() == 1) {
            DB::getMDB()->insert('balance', array(
                'operator_id' => $_SESSION ['operator_id'],
                'balance' => $sub->getBalance()->getBalance(),
                'reserved' => $sub->getBalance()->getReserved()
            ));
            $sub->getBalance()->setId(DB::getMDB()->insertId());
        }

        DB::getMDB()->update('resource', array(
            'resource_status_id' => $sub->getMsisdn()->getStatus()
        ), "id=%i", $sub->getMsisdn()->getId());

        DB::getMDB()->update('resource', array(
            'resource_status_id' => $sub->getImsi()->getStatus()
        ), "id=%i", $sub->getImsi()->getId());

        DB::getMDB()->insert('subscriber', array(
            'operator_id' => $_SESSION ['operator_id'],
            'msisdn' => $sub->getMsisdn()->getId(),
            'imsi' => $sub->getImsi()->getId(),
            'billcycle' => $sub->getBillcycle(),
            'status_id' => $sub->getStatus(),
            'plan_id' => $sub->getPlanId(),
            'balance_id' => $sub->getBalance()->getId(),
            'address_id' => $sub->getAddress()->getId(),
            'prepaid' => $sub->getPrepaid(),
            'postpaid' => $sub->getPostpaid(),
            'card_id' => $sub->getCard()->getId()
        ));

        DB::getMDB()->commit();

        return $sub;
    }

    public static function updateCard(Card $card)
    {
        DB::getMDB()->update('cards', array(
            'operator_id' => $_SESSION ['operator_id'],
            'name' => $card->getName(),
            'number' => $card->getNumber(),
            'expiration' => $card->getExpiration(),
            'cvv' => $card->getCvv()
        ), "id=%i", $card->getId());

    }

    public static function updateNotification(Notification $notification)
    {
        DB::getMDB()->update('notifications', array(
            'operator_id' => $_SESSION ['operator_id'],
            'status' => 1
        ), "id=%i", $notification->getId());
    }

    public static function updateAddress(Address $address)
    {
        DB::getMDB()->update('address', array(
            'operator_id' => $_SESSION ['operator_id'],
            'national_id' => $address->getSsn(),
            'name' => $address->getName(),
            'email' => $address->getEmail(),
            'address' => $address->getAddress(),
            'city' => $address->getCity(),
            'county_id' => $address->getCountyId(),
            'zip' => $address->getZip()
        ), "id=%i", $address->getId());

    }

    public static function updateResource(Resource $res)
    {
        if ($res->getId() > 0) {
            DB::getMDB()->update('resource', array(
                'resource_status_id' => $res->getStatus()
            ), "id=%i", $res->getId());
        }
    }

    public static function updateBalance(Balance $bal)
    {
        DB::getMDB()->update('balance', array(
            'operator_id' => $_SESSION ['operator_id'],
            'balance' => $bal->getBalance(),
            'reserved' => $bal->getReserved()
        ), "id=%i", $bal->getId());
    }

    public static function updateSubscriber(Subscriber $sub)
    {
        DB::getMDB()->startTransaction();

        self::updateCard($sub->getCard());
        self::updateAddress($sub->getAddress());

        if ($sub->getPrepaid() == 1) {
            self::updateBalance($sub->getBalance());
        }

        self::updateResource($sub->getMsisdn());
        self::updateResource($sub->getImsi());
        self::updateResource($sub->getHandset());
        self::updateResource($sub->getBonus());

        DB::getMDB()->update('subscriber', array(
            'operator_id' => $_SESSION ['operator_id'],
            'msisdn' => $sub->getMsisdn()->getId(),
            'imsi' => $sub->getImsi()->getId(),
            'billcycle' => $sub->getBillcycle(),
            'status_id' => $sub->getStatus(),
            'plan_id' => $sub->getPlanId(),
            'balance_id' => $sub->getBalance()->getId(),
            'address_id' => $sub->getAddress()->getId(),
            'prepaid' => $sub->getPrepaid(),
            'postpaid' => $sub->getPostpaid(),
            'handset' => $sub->getHandset()->getId(),
            'bonus' => $sub->getBonus()->getId(),
            'card_id' => $sub->getCard()->getId()
        ), "id=%i", $sub->getId());

        DB::getMDB()->commit();
    }

    public static function genUsage($mtable, $recs, $to, $from, $year, $month)
    {
        DB::getMDB()->startTransaction();

        $postpaid = 1;
        if ($mtable == 'online') {
            $postpaid = 0;
        }

        $subscribers = DB::getMDB()->query('
                     select 
                        a.id id, 
                        a.plan_id plan_id,
                        a.status_id status,
                        b.value msisdn
                    from
                        subscriber a,
                        resource b
                    where 
                        a.postpaid=%d and
                        b.id=a.msisdn and
                        ((a.status_id = 1) or (a.status_id = 3) or (a.status_id = 5)) and
                        ((b.resource_status_id=2) or (b.resource_status_id=4) or (b.resource_status_id=5) or (b.resource_status_id=6)) and
                        a.id>=%d and 
                        a.id<=%d
                    order by a.id', $postpaid, $from, $to);

        foreach ($subscribers as $row) {
            for ($i = 0; $i < $recs; $i++) {
                $a_number = $row['msisdn'];
                $b_number = DB::getMDB()->queryOneField('value', 'SELECT 
                                                    value
                                                FROM
                                                    resource
                                                WHERE
                                                    resource_type_id=1 AND
                                                    resource_status_id=1
                                                ORDER by rand() LIMIT 1');
                $plan = $row['plan_id'];
                $ttime = $year . '-' . $month . '-';
                $ttime = $ttime . rand(1, 28) . ' ' . rand(0, 23) . ':' . rand(0, 59) . ':' . rand(0, 59);

                $munits = rand(3, 45);
                $mservice = rand(1, 4);
                if ($mservice == 4) {
                    $munits = $munits * rand(3, 5);
                }
                else {
                    if($mservice < 3) {
                        $munits = 1;
                    }
                }

                if ($mservice == 3) {
                    $b_number = "Data usage";
                }

                $sql = "insert into `" . $mtable . "`(`operator_id`, `a_number`, `b_number`, `plan_id`, `units`, `tstamp`, `service`) 
                values ('3', '" . $a_number . "', '" . $b_number . "', " . $plan . ", " . $munits . ", str_to_date('" . $ttime . "', '%Y-%m-%d %H:%i:%s'), " . $mservice . ");";
                // echo $sql."<br>";
                DB::getMDB()->query($sql);
            }
        }

        if ($mtable == 'online') {
            DB::getMDB()->insert('activity', array(
                'operator_id' => $_SESSION ['operator_id'],
                'type' => 3
            ));
        }

        DB::getMDB()->commit();
    }

    public static function addTicket($subscriber_id, $ticket_text, $status = 0, $type = 1) {
        DB::getMDB()->insert('tickets', array(
            'operator_id' => $_SESSION ['operator_id'],
            'subscriber_id' => $subscriber_id,
            'text' => $ticket_text,
            'status' => $status,
            'type' => $type
        ));

        DB::getMDB()->commit();
    }

    public static function updateTicketStatus($ticket_id, $status) {
        DB::getMDB()->update('tickets', array(
            'status' => $status
        ), "id=%i", $ticket_id);

        DB::getMDB()->commit();
    }

    public static function getTickets($subscriber_id) {
        $rows = DB::getMDB()->query('SELECT id, subscriber_id, status, operator_id, unix_timestamp(sys_creation_date), type, text from tickets WHERE subscriber_id=%d order by sys_creation_date', $subscriber_id);
        $tickets = array();
        foreach($rows as $row) {
            $ticket = new Ticket();
            $ticket->setId($row['id']);
            $ticket->setSubscriberId($row['subscriber_id']);
            $ticket->setStatus($row['status']);
            $ticket->setOperatorId($row['operator_id']);
            $ticket->setDate($row['unix_timestamp(sys_creation_date)']);
            $ticket->setType($row['type']);
            $ticket->setText($row['text']);

            array_push($tickets, $ticket);
        }

        return $tickets;
    }

    public static function cleanup() {
        if (!Utils::admin()) {
            return;
        }

        DB::getMDB()->startTransaction();
        DB::getMDB()->query('truncate activity'); error_log('ACTIVITY truncated.');
        DB::getMDB()->query('truncate offline'); error_log('OFFLINE truncated.');
        DB::getMDB()->query('truncate online'); error_log('ONLINE truncated.');
        DB::getMDB()->query('truncate bills'); error_log('BILLS truncated.');
        DB::getMDB()->query('truncate notifications'); error_log('NOTIFICATIONS truncated.');
        DB::getMDB()->query('truncate balance_reserve'); error_log('BALANCE_RESERVE truncated.');
        for($i=1;$i<5;$i++) {
            DB::getMDB()->update('stats', array(
                'param0' => '',
                'param1' => '',
                'param2' => '',
                'param3' => '',
                'param4' => '',
                'param5' => '',
                'param6' => '',
                'param7' => '',
                'param8' => '',
                'param9' => '',
                'param10' => '',
                'param11' => '',
                'param12' => '',
                'param13' => '',
                'param14' => '',
                'param15' => '',
                'param16' => '',
                'param17' => '',
                'param18' => '',
                'param19' => '',
                'param20' => '',
                'param21' => '',
                'param22' => '',
                'param23' => '',
                'param24' => ''
            ), "id=%i", $i);
        }

        $rows = DB::getMDB()->query('select id from subscriber');
        foreach($rows as $row) {
            $sub = DB_utils::getSubscriber($row['id']);
            if(($sub->getStatus() == self::$subscriberStatus['Written off']) || ($sub->getStatus() == self::$subscriberStatus['Collection'])) {
                if($sub->getMsisdn()->getStatus() != self::$resourceStatus['Suspended']) {
                    $sub->getMsisdn()->setStatus(self::$resourceStatus['Suspended']);
                    DB_utils::updateResource($sub->getMsisdn());
                    error_log('MSISDN '.$sub->getMsisdn()->getValue(). ' set to SUSPENDED');
                }
            }
            else {
                if($sub->getMsisdn()->getStatus() != self::$resourceStatus['Active']) {
                    $sub->getMsisdn()->setStatus(self::$resourceStatus['Active']);
                    DB_utils::updateResource($sub->getMsisdn());
                    error_log('MSISDN '.$sub->getMsisdn()->getValue(). ' set to ACTIVE');
                }
            }
        }

        DB::getMDB()->commit();
    }
}