<?php
/**
* Since 2013 Ovidiu Cimpean.
*
* Ovidiu Cimpean - Newsletter Pro © All rights reserved.
*
* DISCLAIMER
*
* Do not edit, modify or copy this file.
* If you wish to customize it, contact us at addons4prestashop@gmail.com.
*
* @author    Ovidiu Cimpean <addons4prestashop@gmail.com>
* @copyright Since 2013 Ovidiu Cimpean
* @license   Do not edit, modify or copy this file
*
* @version   Release: 4
*/

class NewsletterProXMLToSQL
{
    public $db_tables;

    public $xml;

    public $sql_array;

    public $sql_tables;

    const SQL_SIZE = 32768; /* 1024*32 bytes */

    public function __construct(SimpleXMLElement $xml)
    {
        $this->module = NewsletterPro::getInstance();
        $this->sql_array = [];
        $this->sql_tables = [];
        $this->xml = $xml;
        $this->createSql();
    }

    public function createSql()
    {
        $this->setDbTables();
        $this->buildInsertStatement();
    }

    private function setDbTables()
    {
        $this->db_tables = [];

        foreach (explode(',', $this->xml->header->tables) as $table_name) {
            if ($this->tableExists($table_name)) {
                $this->db_tables[] = $table_name;
            }
        }
    }

    private function buildInsertStatement()
    {
        $tables = $this->xml->tables->table;
        foreach ($tables as $table) {
            $this->emptyBuffer();

            $table_attributes = $table->attributes();
            $table_name = (string) $table_attributes['name'];
            $this->current_table_name = $table_name;

            if (in_array($table_name, $this->db_tables)) {
                $xml_records = $this->getXMLRecords($table->records->record);

                if (!empty($xml_records)) {
                    $db_fields = array_keys($this->getTableFields($table_name));

                    reset($xml_records);
                    // get the fields for the record
                    $xml_fields = array_keys($xml_records[key($xml_records)]);
                    $fields = array_intersect($db_fields, $xml_fields);
                    $header_fields = $this->getXMLHeaderFields($table->fields->field);

                    $sql_insert = 'INSERT IGNORE INTO `'.$table_name.'` '."\n\t".'(`'.implode('`,`', $fields).'`) '."\n\tVALUES\n";
                    $this->appendSQL($sql_insert);

                    $sql_temp = '';
                    foreach ($xml_records as $record) {
                        $sql_temp .= "\t(";
                        foreach ($record as $fields_name => $value) {
                            if (isset($header_fields[$fields_name])) {
                                $current_field_header = $header_fields[$fields_name];

                                if ((bool) $current_field_header['allow_null'] && ('NULL' == Tools::strtoupper($value))) {
                                    $sql_temp .= 'NULL';
                                } elseif ((bool) $current_field_header['is_hex']) {
                                    $sql_temp .= (!empty($value) ? $value : '\'\'');
                                } else {
                                    $sql_temp .= '\''.str_replace('\"', '"', addcslashes($value, "\x00\n\r\\'\"\x1a")).'\'';
                                }

                                $end_trim = ', ';
                                $sql_temp .= $end_trim;
                            }
                        }
                        $sql_temp = rtrim($sql_temp, $end_trim);
                        $sql_temp .= "),\n";

                        if (NewsletterProTools::strSize($sql_temp) > self::SQL_SIZE) {
                            $this->appendSQL(rtrim($sql_temp, "\n,"));
                            $this->appendSQL(";\n\n");
                            $this->addSQLByTable($table_name, $this->getSQLEmpty());
                            $sql_temp = '';
                            $this->appendSQL($sql_insert);
                        }
                    }

                    if (!empty($sql_temp)) {
                        $this->appendSQL(rtrim($sql_temp, "\n,"));
                        $this->appendSQL(";\n\n");

                        // add sql by table
                        $this->addSQLByTable($table_name, $this->getSQLEmpty());
                    }
                }
            }
        }
    }

    private function appendSQL($value)
    {
        $this->sql_array[] = $value;
    }

    private function addSQLByTable($table_name, $sql)
    {
        $this->sql_tables[$table_name][] = $sql;
    }

    public function getSQLEmpty()
    {
        $sql = $this->getSQLBuffer();
        $this->sql_array = [];

        return $sql;
    }

    public function emptyBuffer()
    {
        $this->sql_array = [];
    }

    public function getSQLBuffer()
    {
        return implode('', $this->sql_array);
    }

    public function getSQL($table_name = null)
    {
        if (isset($table_name)) {
            return $this->sql_tables[$table_name];
        }

        return $this->sql_tables;
    }

    private function getXMLHeaderFields($xml_fields, $compare_with_db_fields = true)
    {
        $fields = [];

        foreach ($xml_fields as $field) {
            $field_attributes = $field->attributes();

            $f_array = [];

            foreach ($field_attributes as $key => $value) {
                $f_array[$key] = (string) $value;
            }

            $fields[(string) $field_attributes['name']] = $f_array;
        }

        if ($compare_with_db_fields) {
            if (!isset($this->current_table_name)) {
                throw new Exception(sprintf($this->module->l('This function cannot be used because the variable "%s" is not defined.'), '$this->current_table_name'));
            }

            $xml_header_fields = $fields;
            $db_fields = array_keys($this->getTableFields($this->current_table_name));
            $header_fields_availalbe = array_intersect($db_fields, array_keys($xml_header_fields));
            $header_fields = array_intersect_key($xml_header_fields, array_fill_keys($header_fields_availalbe, null));

            return $header_fields;
        }

        return $fields;
    }

    private function getXMLRecords($xml_records)
    {
        $records = [];
        if ($xml_records) {
            foreach ($xml_records as $record) {
                $item = [];
                foreach ($record as $fields_name => $value) {
                    $item[$fields_name] = (string) $value;
                }

                $records[] = $item;
            }
        }

        return $records;
    }

    public function tableExists($table_name)
    {
        return count(Db::getInstance()->executeS('SHOW TABLES LIKE "'.pSQL($table_name).'"'));
    }

    public function getTableFields($table_name)
    {
        $fields = [];
        $result = Db::getInstance()->executeS('SHOW FIELDS FROM `'.pSQL($table_name).'`');
        foreach ($result as $value) {
            $fields[$value['Field']] = $value['Default'];
        }

        return $fields;
    }
}