<?php
/**
 * @author Jaap Jansma <jaap.jansma@civicoop.org>
 * @license AGPL-3.0
 */

namespace Civi\DataProcessor\Storage;

use Civi\DataProcessor\DataFlow\AbstractDataFlow;
use Civi\DataProcessor\DataFlow\SqlDataFlow;
use Civi\DataProcessor\DataFlow\SqlTableDataFlow;
use Civi\DataProcessor\DataSpecification\DataSpecification;
use Civi\DataProcessor\DataSpecification\FieldExistsException;
use Civi\DataProcessor\DataSpecification\FieldSpecification;
use Civi\DataProcessor\Utils\Sql;
use CRM_Core_DAO;
use CRM_Utils_Type;
use Exception;

class DatabaseTable implements StorageInterface {

  const MAX_INSERT_SIZE = 1000; // Insert 1000 records per time.

  /**
   * @var \Civi\DataProcessor\DataFlow\AbstractDataFlow
   */
  protected $sourceDataFlow;

  /**
   * @var \Civi\DataProcessor\DataFlow\AbstractDataFlow
   */
  protected $dataFlow = null;

  /**
   * @var string
   */
  protected $table;

  public function __construct($table) {
    $this->table = $table;
  }

  public function dropTable() {
    CRM_Core_DAO::executeQuery("DROP TABLE  IF EXISTS `$this->table`");
  }

  /**
   * Sets the source data flow
   *
   * @param \Civi\DataProcessor\DataFlow\AbstractDataFlow $sourceDataFlow
   * @return \Civi\DataProcessor\Storage\StorageInterface
   */
  public function setSourceDataFlow(AbstractDataFlow $sourceDataFlow): StorageInterface  {
    $this->sourceDataFlow = $sourceDataFlow;
    return $this;
  }

  /**
   * Initialize the storage
   *
   * @return void
   */
  public function initialize(): void {
    if ($this->isInitialized()) {
      return;
    }

    if ($this->timeToRefreshStorage()) {
      try {
        $this->refreshStorage();
      } catch (Exception $e) {
        return;
      }
    }

    $this->dataFlow = new SqlTableDataFlow($this->table, $this->table);
    foreach($this->sourceDataFlow->getDataSpecification()->getFields() as $field) {
      try {
        $newField = new FieldSpecification($field->alias, $field->type, $field->title, $field->options, $field->alias);
        $this->dataFlow->getDataSpecification()->addFieldSpecification($newField->name, $newField);
      } catch (FieldExistsException $e) {
      }
    }
    $this->dataFlow->setOutputFieldHandlers($this->sourceDataFlow->getOutputFieldHandlers());
  }

  /**
   * Chekcs whether the storage is initialized.
   *
   * @return bool
   */
  public function isInitialized(): bool {
    if ($this->dataFlow !== null) {
      return true;
    }
    return false;
  }

  /**
   * @return \Civi\DataProcessor\DataFlow\AbstractDataFlow
   */
  public function getDataFlow(): AbstractDataFlow {
    if (!$this->isInitialized()) {
      $this->initialize();
    }
    return $this->dataFlow;
  }

  /**
   * Checks whether the storage needs to be refreshed.
   *
   * @return bool
   */
  protected function timeToRefreshStorage(): bool {
    $tableExists = CRM_Core_DAO::checkTableExists($this->table);
    if (!$tableExists) {
      return true;
    }
    return false;
  }

  /**
   * @return void
   * @throws \Exception
   */
  protected function refreshStorage() {
    $tableExists = CRM_Core_DAO::checkTableExists($this->table);
    if ($tableExists) {
      CRM_Core_DAO::executeQuery("DROP TABLE `$this->table`");
    }
    if ($this->sourceDataFlow instanceof SqlDataFlow) {
      // Do a CREATE TABLE SELECT ... FROM ...
      $primaryKey = 'primaryKey_'.time();
      $this->sourceDataFlow->resetInitializeState();
      $selectStatement = $this->sourceDataFlow->getSelectQueryStatement();
      $whereStatement = $this->sourceDataFlow->getWhereStatement();
      $groupByStatement = $this->sourceDataFlow->getGroupByStatement();
      $orderByStatement = $this->sourceDataFlow->getOrderByStatement();
      $createTableStatement = "
        CREATE TABLE `$this->table` (`$primaryKey` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`$primaryKey`))
        AS $selectStatement
        $whereStatement
        $groupByStatement
        $orderByStatement";
      CRM_Core_DAO::executeQuery($createTableStatement);
    } else {
      $dataSpecification = $this->sourceDataFlow->getDataSpecification();
      $this->createTable($dataSpecification);
      $this->insertRecords($this->sourceDataFlow->allRecords(), $dataSpecification);
    }
  }

  /**
   * Create a database table based on the given data specification
   *
   * @param \Civi\DataProcessor\DataSpecification\DataSpecification $dataSpecification
   * @throws \Exception
   */
  private function createTable(DataSpecification $dataSpecification) {
    $addColumnSqls = array();
    foreach($dataSpecification->getFields() as $field) {
      $type = Sql::convertTypeToSqlColumnType($field->type);
      $addColumnSqls[] = "`$field->alias` $type";
    }
    $addColumnSql = implode(", ", $addColumnSqls);
    $createTableStatement = "CREATE TABLE `$this->table` ($addColumnSql) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE utf8_general_ci";
    CRM_Core_DAO::executeQuery($createTableStatement);
  }

  /**
   * Insert the records into the table
   *
   * @param $allRecords
   * @param \Civi\DataProcessor\DataSpecification\DataSpecification $dataSpecification
   *
   * @throws \CRM_Core_Exception
   */
  private function insertRecords($allRecords, DataSpecification $dataSpecification) {
    // Collect the fields
    $fieldSqls = array();
    foreach($dataSpecification->getFields() as $field) {
      $fieldSqls[] = "`$field->name`";
    }
    $fieldSql = implode(",", $fieldSqls);
    $baseInsertStatement = "INSERT INTO `$this->table` ($fieldSql)";

    $values = array();
    foreach($allRecords as $record) {
      $row = array();
      foreach($record as $k => $v) {
        $field = $dataSpecification->getFieldSpecificationByName($k);
        $row[] = "'". CRM_Utils_Type::escape($v, $field->type)."'";
      }
      $values[] = "(". implode(", ", $row). ")";

      // Insert the rows when we exceed the MAX_INSERT_SIZE
      if (count($values) >= self::MAX_INSERT_SIZE) {
        $strValues = implode(", ", $values);
        $insertStatement = "$baseInsertStatement VALUES $strValues";
        CRM_Core_DAO::executeQuery($insertStatement);
        $values = array();
      }
    }

    // Insert the remaining rows.
    if (count($values)) {
      $strValues = implode(", ", $values);
      $insertStatement = "$baseInsertStatement VALUES $strValues";
      CRM_Core_DAO::executeQuery($insertStatement);
    }
  }
}
