<?php

namespace Drupal\dkan_common\Storage;

use Drupal\Core\Database\Connection;
use Drupal\Core\Database\DatabaseExceptionWrapper;
use Drupal\Core\Database\SchemaObjectExistsException;
use Symfony\Component\EventDispatcher\EventDispatcherInterface;
use Drupal\dkan_common\Events\Event;

/**
 * Base class for database storage methods.
 */
abstract class AbstractDatabaseTable implements DatabaseTableInterface {

  /**
   * The event name we send when we create a table.
   */
  const EVENT_TABLE_CREATE = 'dkan_common_table_create';

  /**
   * A schema. Should be a drupal schema array.
   *
   * @var array
   */
  protected $schema;

  /**
   * Drupal DB connection object.
   *
   * @var \Drupal\Core\Database\Connection
   */
  protected $connection;

  /**
   * Event dispatcher service.
   *
   * @var \Symfony\Component\EventDispatcher\EventDispatcherInterface
   */
  protected EventDispatcherInterface $eventDispatcher;

  /**
   * Prepare data.
   *
   * Transform the string data given into what should be use by the insert
   * query.
   */
  abstract protected function prepareData(string $data, ?string $id = NULL): array;

  /**
   * Get the primary key used in the table.
   *
   * @return string
   *   The name of the primary key.
   */
  public function primaryKey() {
    return 'id';
  }

  /**
   * Constructor.
   *
   * @param \Drupal\Core\Database\Connection $connection
   *   Drupal database connection object.
   * @param \Symfony\Component\EventDispatcher\EventDispatcherInterface $eventDispatcher
   *   Event dispatcher service.
   */
  public function __construct(
    Connection $connection,
    EventDispatcherInterface $eventDispatcher,
  ) {
    $this->connection = $connection;
    $this->eventDispatcher = $eventDispatcher;

    if ($this->tableExist($this->getTableName())) {
      $this->setSchemaFromTable();
    }
  }

  /**
   * {@inheritdoc}
   */
  public function retrieve(string $id) {
    $this->setTable();

    $select = $this->connection->select($this->getTableName(), 't')
      ->fields('t', array_keys($this->getSchema()['fields']))
      ->condition($this->primaryKey(), $id);

    $statement = $select->execute();

    // The docs do not mention it, but fetch can return false.
    $return = (isset($statement)) ? $statement->fetch() : NULL;

    return ($return === FALSE) ? NULL : $return;
  }

  /**
   * {@inheritdoc}
   */
  public function retrieveAll(): array {
    $this->setTable();
    $tableName = $this->getTableName();

    $result = $this->connection->select($tableName, 't')
      ->fields('t', [$this->primaryKey()])
      ->execute()
      ->fetchAll();

    if ($result === FALSE) {
      return [];
    }

    return array_map(function ($item) {
      return $item->{$this->primaryKey()};
    }, $result);
  }

  /**
   * Store data.
   */
  public function store($data, ?string $id = NULL): string {
    $this->setTable();

    $existing = (isset($id)) ? $this->retrieve($id) : NULL;

    $data = $this->prepareData($data, $id);

    $returned_id = NULL;

    if ($existing === NULL) {
      $fields = $this->getNonSerialFields();

      if (count($fields) != count($data)) {
        throw new \Exception(
          'The number of fields and data given do not match: fields - '
            . json_encode($fields) . ' data - ' . json_encode($data)
        );
      }

      $q = $this->connection->insert($this->getTableName());
      $q->fields($fields);
      $q->values($data);
      $returned_id = $q->execute();
    }
    else {
      $q = $this->connection->update($this->getTableName());
      $q->fields($data)
        ->condition($this->primaryKey(), $id)
        ->execute();
    }

    return ($returned_id) ? "$returned_id" : "{$id}";
  }

  /**
   * Prepare to store possibly multiple values.
   *
   * @param array $data
   *   Array of values to be inserted into the database.
   *
   * @return string|null
   *   Last record id inserted into the database.
   */
  public function storeMultiple(array $data) {
    $this->setTable();

    $fields = $this->getNonSerialFields();

    $q = $this->connection->insert($this->getTableName());
    $q->fields($fields);
    foreach ($data as $datum) {
      $datum = $this->prepareData($datum);
      if (count($fields) != count($datum)) {
        throw new \Exception('The number of fields and data given do not match: fields - ' .
          json_encode($fields) . ' data - ' . json_encode($datum));
      }
      $q->values($datum);
    }
    return $q->execute();
  }

  /**
   * Gather fields from the schema which are not serial.
   *
   * Use this information to skip specifying values for those fields when they
   * are updated or inserted.
   *
   * @return array
   *   An array of field names which are not serial.
   */
  protected function getNonSerialFields() {
    $fields = [];
    foreach ($this->schema['fields'] as $field => $info) {
      if ($info['type'] != 'serial') {
        $fields[] = $field;
      }
    }
    return $fields;
  }

  /**
   * {@inheritDoc}
   *
   * Remove the table.
   */
  public function remove(string $id) {
    $tableName = $this->getTableName();
    return $this->connection->delete($tableName)
      ->condition($this->primaryKey(), $id)
      ->execute();
  }

  /**
   * Count rows in table.
   */
  public function count(): int {
    $this->setTable();
    $query = $this->connection->select($this->getTableName());
    return $query->countQuery()->execute()->fetchField();
  }

  /**
   * Run a query on the database table.
   *
   * @param \Drupal\dkan_common\Storage\Query $query
   *   Query object.
   * @param string $alias
   *   (Optional) alias for primary table.
   * @param bool $fetch
   *   Fetch the rows if true, just return the result statement if not.
   *
   * @return array|\Drupal\Core\Database\StatementInterface
   *   Array of results if $fetch is true, otherwise result of
   *   Select::execute() (prepared Statement object or null).
   */
  public function query(Query $query, string $alias = 't', $fetch = TRUE) {
    $this->setTable();
    $query->collection = $this->getTableName();
    $selectFactory = new SelectFactory($this->connection, $alias);
    $db_query = $selectFactory->create($query);

    try {
      $result = $db_query->execute();
    }
    catch (DatabaseExceptionWrapper $e) {
      throw new \Exception($this->sanitizedErrorMessage($e->getMessage()));
    }

    return $fetch ? $result->fetchAll() : $result;
  }

  /**
   * Create a minimal error message that does not leak database information.
   */
  protected function sanitizedErrorMessage(string $unsanitizedMessage) {
    // Insert portions of exception messages you want caught here.
    $messages = [
      // Portion of the message => User friendly message.
      'Column not found' => 'Column not found',
      'Mixing of GROUP columns' => 'You may not mix simple properties and aggregation expressions in a single query. If one of your properties includes an expression with a sum, count, avg, min or max operator, remove other properties from your query and try again',
      'Can\'t find FULLTEXT index matching the column list' => 'You have attempted a fulltext match against a column that is not indexed for fulltext searching',
    ];
    foreach ($messages as $portion => $message) {
      if (str_contains($unsanitizedMessage, $portion)) {
        return $message . '.';
      }
    }
    return 'Database internal error.';
  }

  /**
   * Create the table in the db if it does not yet exist.
   *
   * @throws \Exception
   *   Throws an exception if the schema was not already set.
   */
  public function setTable() {
    if ($this->tableExist($table_name = $this->getTableName())) {
      return;
    }
    if (!$this->schema) {
      throw new \Exception('Could not instantiate the table due to a lack of schema.');
    }
    try {
      $this->tableCreate($table_name, $this->schema);
    }
    catch (SchemaObjectExistsException) {
      // Table already exists, which is totally OK. Other throwables find
      // their way out to the caller.
    }
  }

  /**
   * Destroy.
   *
   * Drop the database table.
   */
  public function destruct() {
    if ($this->tableExist($this->getTableName())) {
      $this->connection->schema()->dropTable($this->getTableName());
    }
  }

  /**
   * Check for existence of a table name.
   */
  protected function tableExist($table_name): bool {
    return $this->connection->schema()->tableExists($table_name);
  }

  /**
   * Create a table given a name and schema.
   *
   * @throws \Throwable
   */
  protected function tableCreate($table_name, $schema) {
    // Opportunity to further alter the schema before table creation.
    $event = new Event($schema);
    $this->eventDispatcher->dispatch($event, self::EVENT_TABLE_CREATE);
    $this->setSchema($event->getData());

    $this->connection->schema()->createTable($table_name, $this->getSchema());
  }

  /**
   * Set the schema using the existing database table.
   */
  protected function setSchemaFromTable() {
    $fields_info = $this->connection->query('DESCRIBE {' . $this->getTableName() . '}')->fetchAll();
    if (empty($fields_info)) {
      return;
    }

    foreach ($fields_info as $info) {
      $fields[] = $info->Field;
    }
    $schema = $this->getTableSchema($fields);
    if (method_exists($this->connection->schema(), 'getComment')) {
      foreach ($schema['fields'] as $fieldName => $info) {
        $newInfo = $info;
        $newInfo['description'] = $this->connection->schema()->getComment($this->getTableName(), $fieldName);
        $schema['fields'][$fieldName] = $newInfo;
      }
    }
    $this->setSchema($schema);
  }

  /**
   * Get table schema.
   */
  private function getTableSchema($fields) {
    $schema = [];
    $header = $fields;
    foreach ($header as $field) {
      $schema['fields'][$field] = [
        'type' => 'text',
      ];
    }
    return $schema;
  }

  /**
   * Clean up and set the schema for SQL storage.
   */
  private function cleanSchema(): void {
    $cleanSchema = $this->schema;
    $cleanSchema['fields'] = [];
    foreach ($this->schema['fields'] as $field => $info) {
      $new = preg_replace('/[^A-Za-z0-9_ ]/', '', (string) $field);
      $new = trim((string) $new);
      $new = strtolower($new);
      $new = str_replace(' ', '_', $new);

      $mysqlMaxColLength = 64;
      if (strlen($new) > $mysqlMaxColLength) {
        $strings = str_split($new, $mysqlMaxColLength - 5);
        $token = $this->generateToken($field);
        $new = $strings[0] . "_{$token}";
      }

      if ($field != $new) {
        $info['description'] = $field;
      }

      $cleanSchema['fields'][$new] = $info;
    }

    $this->schema = $cleanSchema;
  }

  /**
   * Define a schema for the table.
   *
   * @param array $schema
   *   A schema. Should be a drupal schema array.
   */
  public function setSchema(array $schema): void {
    $this->schema = $schema;
    $this->cleanSchema();
  }

  /**
   * Get the schema for this table.
   *
   * @return array
   *   A Drupal Schema API array, or an empty array if none found.
   */
  public function getSchema(): array {
    return $this->schema ?? [];
  }

  /**
   * Generate a short 4-character token for a field, to help truncate.
   *
   * @param string $field
   *   A field name from the schema.
   *
   * @return string|false
   *   The four-character token, or false if failed.
   */
  public function generateToken(string $field) {
    $md5 = md5($field);
    return substr($md5, 0, 4);
  }

  /**
   * {@inheritdoc}
   */
  public function tableIsEmpty(): bool {
    if (!$this->tableExist($this->getTableName())) {
      return TRUE;
    }
    $query = $this->connection->select($this->getTableName())
      ->range(0, 1);
    $query->addExpression('1');
    return !(bool) $query->execute()->fetchField();
  }

}
