<?php
/**
 * @author       Aicha Vack
 * @package      Joomla.Site
 * @subpackage   com_visforms
 * @link         https://www.vi-solutions.de
 * @license      GNU General Public License version 2 or later; see license.txt
 * @copyright    2022 vi-solutions
 */
// no direct access
defined('_JEXEC') or die('Restricted access');

use Joomla\CMS\Factory;
use Joomla\CMS\Log\Log;
use Joomla\CMS\Language\Text;
use Joomla\CMS\Filter\InputFilter;
use Joomla\Registry\Registry;
use Visolutions\Component\Visforms\Administrator\Model\VisdataModel;
use Visolutions\Component\Visforms\Administrator\Model\Helper\CreateFormData;
use Visolutions\Component\Visforms\Administrator\Model\Helper\CreateFieldData;
use Visolutions\Component\Visforms\Administrator\Service\HTML\Visformsselect;
use Joomla\CMS\Filesystem\Path;

class visFormsBfImportHelper {

    private $lowerCaseDbTableList;
    private $usedBfFormsDbTables = array ('#__facileforms_forms', '#__facileforms_elements', '#__facileforms_records', '#__facileforms_subrecords', '#__facileforms_config');
    public $dbo;
    private $loggerName = 'Visforms';
    // bfforms configuration settings form database as label, value pairs
    private $bfFormsConfig = array();
    // default email address set in bfforms; if not given default email address from joomla config; if not given ''
    private $defaultEmailAddress;
    private $bfForms;
    // currently processed bfform
    private $bfForm;
    // list of form names already stored in Visforms database (name must be unique)
    private $forbiddenFormNames = array();
    // f
    private $bfFields;
    // BF Form configuration from json string in 'template_code'
    private $bfFormRoot;
    private $bfFieldImportFromDatabase;
    // bfform id => id of newly created Visforms form
    protected $formIdMapper = array();
    // Field ID Map of the currently imported form; key = Visforms field id, value = BF db field id
    protected $fieldIDs = array();

    public function __construct() {
        $this->initializeLogger();
        // create connection to a database with bfforms; development only
        $this->testDbo = $this->getTestDbo();
        $this->dbo = Factory::getDbo();
        $this->setLowerCaseTableList();
    }

    // Testing: Allow to use different database connection to installation with BF for testing
    public function getTestDbo() {
        $conf = Factory::getConfig();
        $host = $conf->get('host');
        $user = $conf->get('user');
        $password = $conf->get('password');
        $database = $conf->get('db'); // 'breezing';
        $prefix = $conf->get('dbprefix');
        $driver = $conf->get('dbtype');
        $options = array('driver' => $driver, 'host' => $host, 'user' => $user, 'password' => $password, 'database' => $database, 'prefix' => $prefix);
        return JDatabaseDriver::getInstance($options);
    }

    private function getPayLoad() {
        $app = Factory::getApplication();
        if (!$this->getBfFormDefinitionFromDatabase()) {
            $app->enqueueMessage(Text::_('COM_VISFORMS_BREEZINGFORMS_IMPORT_DEFINITION_FROM_DB_FAILED'), 'error');
            return false;
        }
        $this->setForbiddenFormNames();
        $this->setBfformsConfig();
        $this->setDefaultEmailAddress();
        return true;
    }

    public function importBfForms () {
        $this->addLogEntry('*** starting com_visforms breezingforms import '. Factory::getDate().' ***', Log::INFO);
        $app = Factory::getApplication();
        if (!$this->getPayLoad()) {
            $msg = Text::_('COM_VISFORMS_BREEZINGFORMS_IMPORT_FAILED');
            $this->addLogEntry($msg);
            $app->enqueueMessage($msg, 'ERROR');
            return false;
        }
        if (empty($this->bfForms) || !is_array($this->bfForms)) {
            // proper message already set
            return false;
        }
        foreach ($this->bfForms as $bfForm) {
            // import form definition
            $this->bfForm = $bfForm;
            if (!$this->getBfFieldDefinition()) {
                $this->bfFields = array();
                unset($this->bfFieldImportFromDatabase);
            }
            $form = new CreateFormData();
            $this->importForm($form);
            // import fields
            if (!$this->importFields()) {
                $form->postSaveObjectHook();
                $this->bfFields = array();
                unset($this->bfFieldImportFromDatabase);
                // no data import
                continue;
            };
            $form->postSaveObjectHook();
            // import data
            if ($this->bfForm->dblog) {
                $this->importData();
            }
            $this->bfFields = array();
            unset($this->bfFieldImportFromDatabase);
        }
        $msg = Text::_('COM_VISFORMS_BREEZINGFORMS_IMPORTED');
        $this->addLogEntry($msg, Log::INFO);
        $app->enqueueMessage($msg, 'success');

        return true;
    }

    private function importForm($form) {
        $bfForm = $this->bfForm;
        // create the form
        $form->createObject();
        // mandatory
        $form->setParameter('name', $this->ensureUniqueFormName($bfForm->name));
        $form->setParameter('title', $bfForm->title);
        // missing
        $form->setParameter('access', '1');
        // optional
        $form->setParameter('published', (($bfForm->published == 1) ? 1 : 0));
        $form->setParameter('description', InputFilter::getInstance()->clean($bfForm->description, 'html'));
        $form->setParameter('saveresult', (($bfForm->dblog) ? 1 : 0));
        // result mail
        $form->setParameter('emailresult', (($bfForm->emailntf == 0) ? 0 : 1));
        $form->setParameter('subject', $bfForm->custom_mail_subject);
        $form->setParameter('emailfrom', ((!empty($bfForm->alt_mailfrom)) ? $bfForm->alt_mailfrom : $this->defaultEmailAddress));
        $form->setParameter('emailfromname', ((!empty($bfForm->alt_mailfromname)) ? $bfForm->alt_mailfromname : Factory::getConfig()->get('fromname', '')));
        $form->setParameter('emailto', ((!empty($bfForm->emailadr)) ? $bfForm->emailadr : Factory::getConfig()->get('mailfrom', '')));
        $form->setGroupParameter('emailresultsettings', 'emailresultincfield', (($bfForm->emaillog == 0) ? 0 : 1));
        $form->setGroupParameter('emailresultsettings', 'emailresulthideemptyfields', (($bfForm->emaillog == 2) ? 1 : 0));
        if ($bfForm->email_type && !empty($bfForm->email_custom_template)) {
            $emailText = InputFilter::getInstance()->clean($bfForm->email_custom_template, 'html');
            $form->setParameter('emailresulttext', $emailText);
        }
        // user mail
        $form->setParameter('emailreceipt', (($bfForm->mb_emailntf == 0) ? 0 : 1));
        $form->setParameter('emailreceiptsubject', $bfForm->mb_custom_mail_subject);
        $form->setParameter('emailreceiptfrom', ((!empty($bfForm->mb_alt_mailfrom)) ? $bfForm->mb_alt_mailfrom : $this->defaultEmailAddress));
        $form->setParameter('emailreceiptfromname', ((!empty($bfForm->mb_alt_mailfromname)) ? $bfForm->mb_alt_mailfromname : Factory::getConfig()->get('fromname', '')));
        $form->setGroupParameter('emailreceiptsettings', 'emailreceiptincfield', (($bfForm->mb_emaillog == 0) ? 0 : 1));
        $form->setGroupParameter('emailreceiptsettings', 'emailreceipthideemptyfields', (($bfForm->mb_emaillog == 2) ? 1 : 0));
        if ($bfForm->mb_email_type && !empty($bfForm->mb_email_custom_template)) {
            $emailText = InputFilter::getInstance()->clean($bfForm->mb_email_custom_template, 'html');
            $form->setParameter('emailreceipttext', $emailText);
        }
        if ($this->bfFieldImportFromDatabase === false) {
            // Check in field list, if we have to add a captcha to the form
            foreach ($this->bfFields as $index => $params) {
                $params = (object) $params;
                if ($params->bfType == 'bfCaptcha' || $params->bfType == 'bfReCaptcha' && $params->invisibleCaptcha == false) {
                    $captcha = ($params->bfType == 'bfReCaptcha') ? 2 : 1;
                    $captchaLabel = (!empty($params->label)) ? $params->label : 'Captcha';
                    $captchaCustomInfo = (!empty($params->hint)) ? InputFilter::getInstance()->clean($params->hint) : '';
                    $form->setParameter('captcha', $captcha);
                    $form->setGroupParameter('captchaoptions', 'captchalabel', $captchaLabel);
                    $form->setGroupParameter('captchaoptions', 'captchacustominfo', $captchaCustomInfo);
                    $form->setGroupParameter('captchaoptions', 'showcaptchalabel', ($params->hideLabel) ? 1 : 0);
                    // only incule one captcha, whichever comes first
                    break;
                }
            }
        }
        if ($this->bfFieldImportFromDatabase === true) {
            foreach ($this->bfFields as $index => $params) {
                $params = (object) $params;
                if ($params->type == 'Captcha' || $params->type == 'ReCaptcha' && $params->invisibleCaptcha == false) {
                    $captcha =  ($params->type == 'ReCaptcha') ? 2 : 1;
                    $captchaLabel = (!empty($params->title)) ? $params->title : 'Captcha';
                    $form->setParameter('captcha', $captcha);
                    $form->setGroupParameter('captchaoptions', 'captchalabel', $captchaLabel);
                    break;
                }
            }
        }

        $form->saveObject();
        $newId = $form->getId();
        if (!empty($newId)) {
            $this->formIdMapper[$bfForm->id] = $newId;
            $this->addLogEntry('Form definition of BF form with id ' . $bfForm->id . ' imported', Log::INFO);
        }
        else {
            $this->addLogEntry('Unable to save form definition of BF form with id ' . $bfForm->id, Log::INFO);
        }
    }

    private function importFields() {
        if ($this->bfFieldImportFromDatabase === true) {
            $this->importFieldsFromDBDefinition();
        }
        if ($this->bfFieldImportFromDatabase === false) {
            $this->importFieldsFromTemplateCode();
        }
        return true;
    }

    private function importFieldsFromTemplateCode() {
        // bf field type => Visforms fieldtype
        $fieldTypesMap = array('bfTextarea' => 'textarea', 'bfCalendar' => 'date', 'bfTextfield' => 'text', 'bfFile' => 'file', 'bfSelect' => 'select', 'bfCheckboxGroup' => 'multicheckbox', 'bfRadioGroup' => 'radio', 'bfNumberInput' => 'number', 'bfCheckbox' => 'checkbox');
        $field = new CreateFieldData($this->formIdMapper[$this->bfForm->id]);
        // create all fields
        foreach ($this->bfFields as $index => $params) {
            $params = (object) $params;
            if (!array_key_exists($params->bfType, $fieldTypesMap)) {
                // bfform element type not a real field type
                continue;
            }
            // clean input
            $params->bfName = InputFilter::getInstance()->clean($params->bfName);
            $params->label = InputFilter::getInstance()->clean($params->label);
            $params->type = $fieldTypesMap[$params->bfType];
            $field->setType($params->type);
            $field->createObject();
            // mandatory
            $field->setParameter('name', $params->bfName);
            $field->setParameter('label', $params->label);
            $field->setParameter('typefield', $params->type);
            // Do not set published state to 0; Visforms does not store data of disabled fields!
            // $field->setParameter('published', empty($params->off) ? 1 : 0);
            // add one mandatory option
            if ('multicheckbox' == $params->type  || 'radio' == $params->type || 'select' == $params->type) {
                // 0;Title 1;value1\n0;Title 2;value2\n0;Title 3;value3 (group or list) Default; label; value
                // split at linebreak in order to get a list of the options (0;Title 1;value1)
                if ('multicheckbox' == $params->type  || 'radio' == $params->type) {
                    $bfOptions = $this->breakBfSRCOptionStringInCleanOptions(InputFilter::getInstance()->clean($params->group)); //explode(PHP_EOL, $params->group);
                }
                else {
                    $bfOptions = $this->breakBfSRCOptionStringInCleanOptions(InputFilter::getInstance()->clean($params->list)); //explode(PHP_EOL, $params->list);
                }
                // '{"1":{"listitemid":"0","listitemvalue":"value","listitemlabel":"label"}}';
                // '{"1":{"listitemid":"0","listitemvalue":"value1","listitemlabel":"label1"},"2":{"listitemid":"1","listitemvalue":"value2","listitemlabel":"label2"}}';
                // field name triggered specific semantics
                // the last id is also the options count
                $lastId = count($bfOptions);
                // build multiple options json string
                $options = $this->createVfListFieldOptionsString($bfOptions, $lastId);
                // set option jason string
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_list_hidden', $options);
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_lastId', $lastId);
                // custom select field params
                if ('select' == $params->type) {
                    if (!empty($params->multiple)) {
                        $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_attribute_multiple', '1');
                    }
                }
            }
            // set mandatory checked value
            if ('checkbox' == $params->type) {
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_attribute_value', $params->value);
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_attribute_checked', (($params->checked) ? '1': '0'));
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_unchecked_value', '0');
            }
            // custom date field params
            if ('date' == $params->type) {
                $formats = array('%d.%m.%Y' => 'd.m.Y;%d.%m.%Y', '%m/%d/%Y' => 'm/d/Y;%m/%d/%Y', '%Y-%m-%d' => 'Y-m-d;%Y-%m-%d');
                if (array_key_exists($params->format, $formats)) {
                    $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_format', $formats[$params->format]);
                }
            }
            // custom upload field params
            if ('file' == $params->type) {
                if (!empty($params->allowedFileExtensions)) {
                    $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_allowedextensions', InputFilter::getInstance()->clean($params->allowedFileExtensions));
                }
            }

            // optional
            if ('submit' !== $params->type && 'reset' !== $params->type) {
                if (!empty($params->required)) {
                    $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_attribute_required', '1');
                }
                if (!empty($params->hideLabel)) {
                    // show_label 1 = hide label in Visforms
                    $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_show_label', '1');
                }
                if (!empty($params->hint)) {
                    $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_custominfo', InputFilter::getInstance()->clean($params->hint));
                }
                if (!empty($params->hideInMailback)) {
                    $field->setParameter('includeinreceiptmail', '0');
                }
            }
            $field->saveObject();
            $newId = $field->getId();
            if (!empty($newId)) {
                // fields have an incomplete representation in BF Database; we need field id in order to import stored data
                $this->fieldIDs[$newId] = $params->dbId;
                $this->addLogEntry('Element with id' . $params->dbId. ' for form with id '. $this->bfForm->id . ' imported', Log::INFO);
            }
            else {
                $this->addLogEntry('Unable to import element with id' . $params->dbId. ' for form with id '. $this->bfForm->id);
            }
        }
        if ($this->bfFormRoot[0]['submitInclude'] == true) {
            $submitLabel = InputFilter::getInstance()->clean($this->bfFormRoot[0]['submitLabel']);
            $this->createButtonField($field,'submit', (!empty(($submitLabel)) ? $submitLabel : 'submit'));
        }
        if ($this->bfFormRoot[0]['cancelInclude'] == true) {
            $cancelLabel = InputFilter::getInstance()->clean($this->bfFormRoot[0]['cancelLabel']);
            $this->createButtonField($field,'reset', (!empty(($cancelLabel)) ? $cancelLabel : 'reset'));
        }
    }

    private function importFieldsFromDBDefinition() {
        // bf field type => Visforms fieldtype
        $fieldTypesMap = array('Textarea' => 'textarea', 'Text' => 'text', 'File Upload' => 'file', 'Select List' => 'select', 'Checkbox' => 'checkbox', 'Radio Button' => 'radio', 'Hidden Input' => 'hidden', 'Regular Button' => 'submit');
        $field = new CreateFieldData($this->formIdMapper[$this->bfForm->id]);
        // create all fields
        foreach ($this->bfFields as $index => $params) {
            $params = (object) $params;
            if (!array_key_exists($params->type, $fieldTypesMap)) {
                // bfform element type not a real field type
                continue;
            }
            // clean input
            $params->name = InputFilter::getInstance()->clean($params->name);
            $params->title = InputFilter::getInstance()->clean($params->title);
            $params->data1 = InputFilter::getInstance()->clean($params->data1);
            $params->data2 = InputFilter::getInstance()->clean($params->data2);

            if (empty($params->name) || empty($params->title)) {
                // unusable record
                continue;
            }
            // use Visforms Field Types
            $params->type = $fieldTypesMap[$params->type];
            // find out, if checkox bftype stands for checkbox or for checkbox group before initializing the field
            // each option is stored as an element in BF Datatable
            if ('radio' == $params->type || 'checkbox' == $params->type) {
                $optionRecords = $this->getBFFieldOptionGroupElementsFromDatabase($params->name);
                if (empty($optionRecords)) {
                    continue;
                }
                // multiple options = checkboxgroup
                if ('checkbox' == $params->type && count($optionRecords) > 1) {
                    $params->type = 'multicheckbox';
                }
            }
            $field->setType($params->type);
            $field->createObject();
            // mandatory
            $field->setParameter('name', $params->name);
            $field->setParameter('label', $params->title);
            $field->setParameter('typefield', $params->type);
            // Do not set published state to 0; Visforms does not store data of disabled fields!
            // $field->setParameter('published', !empty($params->published) ? 1 : 0);

            if ('radio' == $params->type || 'multicheckbox' == $params->type) {
                $cleanedBfOptions = array();
                foreach ($optionRecords as $optionRecord) {
                    // clean input
                    $params->title = InputFilter::getInstance()->clean($params->title);
                    $params->data1 = InputFilter::getInstance()->clean($params->data1);
                    $params->data2 = InputFilter::getInstance()->clean($params->data2);
                    if (empty($optionRecord->title)) {
                        continue;
                    }
                    $option = array();
                    // Default checked?
                    $option[] = (empty($optionRecord->flag1)) ? 0 : 1;
                    // option label
                    $option[] = (!empty($optionRecord->data2)) ? $optionRecord->data2 : $optionRecord->title;
                    // option value
                    $option[] = (!empty($optionRecord->data1)) ? $optionRecord->data1 : $optionRecord->title;
                    $cleanedBfOptions[] = implode(';', $option);
                }
                $lastId = count($cleanedBfOptions);
                if ($lastId === 0) {
                    continue;
                }
                $options = $this->createVfListFieldOptionsString($cleanedBfOptions, $lastId);
                // set option jason string
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_list_hidden', $options);
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_lastId', $lastId);
            }
            // add options
            if ('select' == $params->type) {
                $cleanedBfOptions = $this->breakBfSRCOptionStringInCleanOptions($params->data2);
                $lastId = count($cleanedBfOptions);
                if ($lastId === 0) {
                    continue;
                }
                $options = $this->createVfListFieldOptionsString($cleanedBfOptions, $lastId);
                // set option jason string
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_list_hidden', $options);
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_lastId', $lastId);
            }

            if ('checkbox' == $params->type) {
                // single checkbox
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_attribute_value', (!empty($params->data1) ? $params->data1 : $params->title));
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_attribute_checked', (!empty($params->flag1) ? '1': '0'));
                $field->setGroupParameter('defaultvalue', 'f_' . $params->type . '_unchecked_value', '0');
            }
            // optional
            if ('submit' !== $params->type && 'reset' !== $params->type) {
                if (empty($params->mailback)) {
                    $field->setParameter('includeinreceiptmail', '0');
                }
            }
            $field->saveObject();
            $newId = $field->getId();
            if (!empty($newId)) {
                // fields have an incomplete representation in BF Database; we need field id in order to import stored data
                $this->fieldIDs[$newId] = $params->id;
                $this->addLogEntry('Element with id' . $params->id . ' for form with id '. $this->bfForm->id . ' imported', Log::INFO);
            }
            else {
                $this->addLogEntry('Unable to import element with id' . $params->id . ' for form with id '. $this->bfForm->id);
            }
        }
    }

    private function importData() {
        if (empty($this->fieldIDs)) {
            // no fields created, nothing to do
            return true;
        }
        $this->addLogEntry('Try to import submissions for form with id ' . $this->bfForm->id, Log::INFO);
        $db = $this->testDbo;
        $query = $db->getQuery(true);
        $query->select($db->qn('id'))
            ->select($db->qn('submitted', 'created'))
            ->select($db->qn('form'))
            ->select($db->qn('ip'))
            ->select($db->qn('user_id', 'created_by'))
            ->from($db->qn('#__facileforms_records'))
            ->where($db->qn('form')  . ' = ' . $this->bfForm->id);
        try {
            $db->setQuery($query);
            // data should be handled as array
            $records = $db->loadAssocList();

        }
        catch (RuntimeException $e) {
            $this->addLogEntry('Unable to laod submission records from database: ' . $e->getMessage());
            return false;
        }
        if (empty($records)) {
            $this->addLogEntry('No submission records found', Log::INFO);
            return true;
        }
        $this->addLogEntry(count($records) . ' submission records loaded', Log::INFO);
        Factory::getApplication()->input->set('fid', $this->formIdMapper[$this->bfForm->id]);
        $dataModel = new VisdataModel();
        $fields = $dataModel->getDatafields();
        foreach ($records as $record) {
            $data = array();
            $data['published'] = 0;
            $data['created'] = $record['created'];
            $data['created_by'] = $record['created_by'];
            $data['ip'] = $record['ip'];
            foreach ($fields as $index => $field) {
                // no data to import
                if (in_array($field->typefield, array('fieldsep', 'pagebreak', 'image', 'submit', 'reset'))) {
                    continue;
                }
                $name = 'F' . $field->id;
                $bfElementId = $this->fieldIDs[$field->id];
                $query = $db->getQuery(true);
                $query->select($db->qn('value'))
                    ->from($db->qn('#__facileforms_subrecords'))
                    ->where($db->qn('record') . ' = ' . $record['id'])
                    ->where($db->qn('element') . ' = ' . $bfElementId);
                try {
                    $db->setQuery($query);
                    $subrecords = $db->loadColumn();
                }
                catch (RuntimeException $e) {
                    $this->addLogEntry('Unable to load subrecords for submission record ' . $record['id'] . ' and element ' . $bfElementId . ' ' . $e->getMessage());
                    continue;
                }
                if (empty($subrecords)) {
                    $this->addLogEntry('No subrecords for submission record ' . $record['id'] . ' and element ' . $bfElementId . ' found', Log::INFO);
                    continue;
                }
                $this->addLogEntry('Subrecords for submission record ' . $record['id'] . ' and element ' . $bfElementId . ' loaded', Log::INFO);
                switch ($field->typefield) {
                    case 'file':
                        $fullPath = $subrecords[0];
                        $test = JPATH_ROOT . DIRECTORY_SEPARATOR;
                        // testing: bf and visforms are installed on different Joomla installations; fix installation root directory name
                        // $test = str_replace('build-j402', 'breezing', $test);
                        // remove root and leading directory separator in stored submission
                        $relPath = str_replace(Path::clean($test), '', Path::clean($fullPath));
                        // get file name
                        $fileName = basename($relPath);
                        // remove trailing directory separator and file name
                        $folder = str_replace(DIRECTORY_SEPARATOR . $fileName, '', $relPath);
                        $file = new \stdClass();
                        $file->folder = $folder;
                        $file->file = $fileName;
                        $registry = new Registry($file);
                        $data[$name] = $registry->toString();
                        break;
                    case 'multicheckbox':
                    case 'select':
                        $data[$name] = implode(Visformsselect::$msdbseparator, $subrecords);
                        break;
                    default:
                        $data[$name] = $subrecords[0];
                        break;
                }
            }
            $dataModel->setState($dataModel->getName() . '.id', 0);
            if (!$dataModel->save($data)) {
                $this->addLogEntry('Could not store submission for record '. $record['id'], Log::INFO);
            }
            $this->addLogEntry('Submission for record '. $record['id'] . ' imported', Log::INFO);

        }
    }

    // payload

    private function getBfFormDefinitionFromDatabase () {
        $db = $this->testDbo;
        // Forms from database
        // email fields are doubled. prefix mb_ for user mail configuration (=mailback)
        $bfFormDbFields = array(
            'id',
            'template_code', // base64 hash if Quickform, null or html if from other form generator
            'ordering', 'published', 'name', 'title', 'description',
            'email_type', 'mb_email_type', // 0 = Standardtext, 1 = Custom Text
            'alt_mailfrom', 'alt_fromname', 'mb_alt_mailfrom', 'mb_alt_fromname', 'custom_mail_subject', 'mb_custom_mail_subject', 'emailadr', // custom mail to for result mail
            'emailntf', 'mb_emailntf', // 0 = no email, 1 = email to default address (from configuration), 2 = email to custom address
            'emaillog',  'mb_emaillog', // 0 = do not sent data with mail, 1 = not empty values, 2 = all data
            'email_custom_template', 'mb_email_custom_template', // Custom Email Text. Can contain HTML and Doctype elements
            'dblog', // 0 = no, 1 = not empty values, 2 = all
        );
        // get all form fields by name
        $query = $db->getQuery(true);
        $query->select($db->qn($bfFormDbFields))
            ->from($db->qn('#__facileforms_forms'))
            // Uncomment to test with named forms (list of form id's)
            // ->where($db->qn('id') . ' in (5, 10)')
            ->order($db->qn('ordering') . ' ASC');
        try {
            $db->setQuery($query);
            $this->bfForms = $db->loadObjectList();
            if (!empty($this->bfForms)) {
                $this->addLogEntry(count($this->bfForms) . ' BF Form records loaded', Log::INFO);
            }
            else {
                $msg = Text::_('COM_VISFORMS_BREEZINGFORMS_IMPORT_ZERO_FORMS_FOUND');
                $this->addLogEntry($msg, Log::INFO);
                Factory::getApplication()->enqueueMessage($msg, 'INFO');
            }
        }
        catch (RuntimeException $e) {
            $this->addLogEntry('Unable to load BF Form records from database: ' . $e->getMessage());
            Factory::getApplication()->enqueueMessage(Text::_('COM_VISFORMS_BREEZINGFORMS_IMPORT_DEFINITION_FROM_DB_FAILED'), 'ERROR');
            return false;
        }
        return true;
    }

    // init helper

    // Used to decide, whether Import Info is displayed in Visforms Dashboard
    public function checkBfFormsTablesExist() {
        // check all bf tables we use are available in the database
        if (empty($this->lowerCaseDbTableList || !is_array($this->lowerCaseDbTableList))) {
            return false;
        }
        foreach ($this->usedBfFormsDbTables as $requiredTable) {
            if (!in_array($requiredTable, $this->lowerCaseDbTableList)) {
                return false;
            }
        }
        return true;
    }

    private function setLowerCaseTableList() {
        $tables = $this->testDbo->getTableList();
        if (!empty($tables)) {
            // $this->lowerCaseDbTableList =  array_map('strtolower', $tables);
            $this->lowerCaseDbTableList = array_map(function ($table) {
                return str_replace(strtolower($this->testDbo->getPrefix()), "#__", $table);
            }, $tables);
        }
        else {
            $this->lowerCaseDbTableList = false;
        }
    }

    // form import helper

    private function ensureUniqueFormName($name) {
        while (in_array(strtolower($name), $this->forbiddenFormNames)) {
            $name = $name . '1';
        }
        return $name;
    }

    // field definition import helper

    private function getBfFieldDefinition() {
        $bfForm = $this->bfForm;
        if (!empty($bfForm->template_code)) {
            $templateCode = base64_decode($bfForm->template_code);
            if ($templateCode !== false) {
                // get field defintion from extraxted hash
                try {
                    $formDefinition = json_decode($templateCode, true, 512, JSON_THROW_ON_ERROR);
                    if ($formDefinition) {
                        $this->bfFieldImportFromDatabase = false;
                        // Information about fields is stored in template_code
                        $this->buildDefinitionFromTemplateCode($formDefinition);
                        // revers field order
                        $this->bfFields = array_reverse($this->bfFields);
                        // Information about Buttons is stored in template_code
                        $this->buildDefinitionFromTemplateCode($formDefinition, 'root');
                        if (empty($this->bfFields)) {
                            $this->addLogEntry('No field defintion found for form with id '. $this->bfForm->id, Log::INFO);
                        }
                        else {
                            $this->addLogEntry(count($this->bfFields) . ' field records for BF form with id ' . $this->bfForm->id . ' loaded', Log::INFO);
                        }
                        return true;
                    }
                }
                catch (JsonException $e) {
                    // check if template_code contains non base64 characters (html)
                    // if so, assume it is an easy modus form
                    if (InputFilter::getInstance()->clean($bfForm->template_code, 'base64') != $bfForm->template_code) {
                        $this->bfFieldImportFromDatabase = true;
                        $this->getBfFieldDefinitionFromDatabase();
                        return true;
                    }
                    $this->addLogEntry('Unable to detect form modus. Cannot import fields and data', Log::INFO);
                    return false;
                }
            }
        }
        // load elements table
        $this->bfFieldImportFromDatabase = true;
        $this->getBfFieldDefinitionFromDatabase();
        return true;
    }

    private function getBfFieldDefinitionFromDatabase() {
        $db = $this->testDbo;
        $query = $db->getQuery(true);
        $query->select($db->qn(array('id', 'form', 'published', 'name', 'title', 'type', 'data1', 'data2', 'mailback')))
            ->from($db->qn('#__facileforms_elements'))
            ->where($db->qn('form')  . ' = ' . $this->bfForm->id)
            ->where ($db->qn('type') . " in ('Text', 'Textarea', 'Hidden Input', 'Select List', 'Radio Button', 'Checkbox', 'File Upload', 'Regular Button', 'Captcha', 'ReCaptcha')")
            // for radios and checkbox group each option is stored in a separate record set.
            // Group is identified by identical names; only get the first record and relaod all group elements later
            ->group($db->qn('name'))
            ->order($db->qn('ordering') . ' ASC');
        try {
            $db->setQuery($query);
            $this->bfFields = $db->loadObjectList();
            if (!empty($this->bfFields)) {
                $this->addLogEntry(count($this->bfFields) . ' field records for BF form with id ' . $this->bfForm->id . ' loaded', Log::INFO);
            }
            else {
                $this->addLogEntry('No field records for BF form with id ' . $this->bfForm->id . ' found', Log::INFO);
            }
        }
        catch (RuntimeException $e) {
            $this->addLogEntry('Unable to load field records for BF form with id' . $this->bfForm->id . ' from database' . $e->getMessage());
            return false;
        }
    }

    private function buildDefinitionFromTemplateCode($part, $type = 'element') {
        if (is_array($part)) {
            while ($subPart = array_pop($part)) {
                if (isset($subPart['type']) && $subPart['type'] == $type) {
                    if ($type == 'root') {
                        $this->bfFormRoot[] = $subPart;
                    }
                    else {
                        $this->bfFields[] = $subPart;
                    }
                    $this->buildDefinitionFromTemplateCode($subPart, $type);
                }
                else {
                    $this->buildDefinitionFromTemplateCode($subPart, $type);
                }
            }
        }
    }

    private function getBFFieldOptionGroupElementsFromDatabase($name) {
        $db = $this->testDbo;
        $query = $db->getQuery(true);
        $query->select($db->qn(array('id', 'form', 'published', 'name', 'title', 'type', 'data1', 'data2', 'flag1')))
            ->from($db->qn('#__facileforms_elements'))
            ->where($db->qn('form')  . ' = ' . $this->bfForm->id)
            ->where($db->qn('name')  . ' = ' . $db->q($name))
            ->where ($db->qn('type') . " in ('Radio Button', 'Checkbox')");
        try {
            $db->setQuery($query);
            return $db->loadObjectList();
        }
        catch (RuntimeException $e) {
            $this->addLogEntry($e->getMessage());
            return false;
        }
    }

    // Visfield builder helper

    private function createButtonField($field, $buttonType, $label) {
        if (!in_array($buttonType, array('submit', 'reset'))) {
            return false;
        }
        $field->setType($buttonType);
        $field->createObject();
        // mandatory
        $field->setParameter('name', $buttonType);
        $field->setParameter('label', $label);
        $field->setParameter('typefield', $buttonType);
        $field->saveObject();
        return true;
    }

    private function createVfListFieldOptionsString ($bfOptions, $lastId) {
        $hasDefaultSet = false;
        // build multiple options json string
        $options = '{';
        for ($i = 0, $j = 1; $i < $lastId; $i++, $j++) {
            $bfOption = explode(';',$bfOptions[$i]);
            // leading comma after the very first one
            if (0 < $i) {
                $options .= ',';
            }
            if ($bfOption[0] == '1' && !$hasDefaultSet) {
                $checked = ",\"listitemischecked\":\"1\"";
                $hasDefaultSet = true;
            }
            else{
                $checked = "";
            }
            $options .= "\"$j\":{\"listitemid\":\"$i\",\"listitemvalue\":\"$bfOption[2]\",\"listitemlabel\":\"$bfOption[1]\"$checked}";
        }
        $options .= '}';
        return $options;
    }

    private function breakBfSRCOptionStringInCleanOptions($bfOptionString) {
        // $bfOptionString 0;Title 1;value1\n0;Title 2;value2\n0;Title 3;value3 (group or list) Default; label; value or
        // $bfOptionString 0;Title 1;value1\r\n0;Title 2;value2\r\n0;Title 3;value3
        $cleanedOptions = array();
        if (empty($bfOptionString)) {
            return $cleanedOptions;
        }
        $bfOptionString = InputFilter::getInstance()->clean($bfOptionString);
        if (empty($bfOptionString)) {
            return $cleanedOptions;
        }
        // split at linebreak in order to get a list of the options (0;Title 1;value1)
        $bfOptions = preg_split('/\r\n|\r|\n/', $bfOptionString);
        if (empty($bfOptions)) {
            return $cleanedOptions;
        }
        // check validity = explode at ';' ; check array length and values; implode
        foreach ($bfOptions as $bfOption) {
            if (empty($bfOption)) {
                continue;
            }
            $bfOption = explode(';', $bfOption);
            // valid option has 3 parts. First part can be 0 or 1; Second and third part m
            if (count($bfOption) === 3) {
                $bfOption[0] = (string) ((int)$bfOption[0] );
                // option part can only consist of ''; that will not be a valid visforms option label or value
                $bfOption[1] = preg_replace('/\'|\"|/', '', $bfOption[1]);
                $bfOption[2] = preg_replace('/\'|\"|/', '', $bfOption[2]);
                $test1 = !empty($bfOption[1]);
                $test2 = $bfOption[1] !== '\'\'';
                $test3 = !empty($bfOption[2]);
                $test4 = $bfOption[2] !== '\'\'';
                if (($bfOption[0] == "0" ||  $bfOption[0] === "1") && !empty($bfOption[1])  && !empty($bfOption[2])) {
                    $cleanedOptions[] = implode(';', $bfOption);
                }
            }
        }
        return $cleanedOptions;
    }

    // get a list of all form names, already use in Visforms.
    private function setForbiddenFormNames() {
        $db = $this->dbo;
        $query = $db->getQuery(true);
        $query->select($db->qn('name'))
            ->from($db->qn('#__visforms'));
        try {
            $db->setQuery($query);
            $this->forbiddenFormNames = $db->loadColumn();
            $this->addLogEntry('List of form names, already used in Visforms, loaded', Log::INFO);
        }
        catch (RuntimeException $e) {
            $this->addLogEntry($e->getMessage());
            return false;
        }
        return true;
    }

    private function setBfformsConfig() {
        $db = $this->testDbo;
        $query = $db->getQuery(true);
        $query->select('*')
            ->from($db->qn('#__facileforms_config'));
        try {
            $db->setQuery($query);
            $this->bfFormsConfig = $db->loadAssocList('id', 'value');
            $this->addLogEntry('BF Config loaded', Log::INFO);
        }
        catch (RuntimeException $e) {
            $this->addLogEntry('Unable to load BF config from database: '. $e->getMessage());
            return false;
        }
        return true;
    }

    private function setDefaultEmailAddress() {
        $this->defaultEmailAddress = (!empty($this->bfFormsConfig['emailadr'])) ? $this->bfFormsConfig['emailadr'] : Factory::getConfig()->get('mailfrom', '');
    }

    // logging

    private function initializeLogger() {
        $options['format']              = "{CODE}\t{MESSAGE}";
        $options['text_entry_format']   = "{PRIORITY}\t{MESSAGE}";
        $options['text_file']           = 'visforms_bfformsimport.php';
        try {
            Log::addLogger($options, Log::ALL, array($this->loggerName, 'jerror'));
        }
        catch (RuntimeException $e) {}
    }

    private function addLogEntry($message, $code = Log::ERROR) {
        try {
            Log::add($message, $code, $this->loggerName);
            if (strtolower($code) == 'error' || strtolower($code) == 'success') {
                // Pass Message to UI
                Factory::getApplication()->enqueueMessage($message, $code);
            }
        }
        catch (RuntimeException $exception) {}
    }
}