<?php

namespace Application\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
use MigrationsBundle\Migrations\MigrationsBase;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20180221174411 extends MigrationsBase
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {

        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');


    if (!$schema->hasTable('service_port') == true) {
        $this->addSql('CREATE TABLE service_port (id INT AUTO_INCREMENT NOT NULL, olt_id BIGINT DEFAULT NULL, onu_id INT DEFAULT NULL, number INT DEFAULT 0 NOT NULL, gemport INT DEFAULT 0 NOT NULL, vlan INT DEFAULT 0 NOT NULL, INDEX IDX_8DD2E92069FFAD89 (olt_id), INDEX IDX_8DD2E920AB83998C (onu_id), UNIQUE INDEX sp_unique (olt_id, number), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('ALTER TABLE service_port ADD CONSTRAINT FK_8DD2E92069FFAD89 FOREIGN KEY (olt_id) REFERENCES olt (id)');
        $this->addSql('ALTER TABLE service_port ADD CONSTRAINT FK_8DD2E920AB83998C FOREIGN KEY (onu_id) REFERENCES onu (id)');
        $this->addSql('ALTER TABLE olt CHANGE execute_snmp execute_snmp BOOLEAN DEFAULT TRUE');
        $this->addSql('ALTER TABLE olt_audit CHANGE execute_snmp execute_snmp BOOLEAN DEFAULT TRUE');
    }

    $result = $this->connection->query("select o.id id, o.olt_id, nap_id, pon_serial_number, position, slot, link, ot.name FROM onu o JOIN nap n ON (n.id = o.nap_id) JOIN olt t ON (t.id = o.olt_id) JOIN oltmodel ot ON(t.model_id = ot.id) WHERE  o.deleted_at IS NULL AND ot.name LIKE 'Huawei MA5608T';");

    $i = 0;
    $vlangemport_config = array('voip' => array('gemport' => 0, 'vlan' => 13), 'data' => array('gemport' => 1, 'vlan' => 10));
    while($rtr = $result->fetch()){
        $i++;
        $pon_serial_number = $rtr['pon_serial_number'];
        $slot = $rtr['slot'];
        $link = $rtr['link'];
        $position = $rtr['position'];
        $olt_id = $rtr['olt_id'];
        $onu_id = $rtr['id'];
        $service_port = array(
            //{% set service_port_data =  "1" ~  slot  ~  link  ~  ("%02d" | format(onu)) %}
            "data" => "1" .  $slot  . $link  . sprintf ("%02d",  $position),

            //{% set service_port_voip =  "2" ~  slot  ~  link  ~  ("%02d" | format(onu)) %}
            "voip" => "2" .  $slot  . $link  . sprintf ("%02d",  $position),
        );
        foreach($service_port as $type => $number){
            $gemport = $vlangemport_config[$type]['gemport'];
            $vlan = $vlangemport_config[$type]['vlan'];
            $sql = "REPLACE INTO service_port (olt_id, onu_id, number, gemport, vlan) VALUES ($olt_id, $onu_id, $number, $gemport, $vlan);";
            $this->addSql($sql);
        }
    }

    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
    }
}