<?php

namespace Drupal\castorcito\Form;

use Drupal\Core\Form\FormBase;
use Drupal\Core\Form\FormStateInterface;
use Drupal\Core\Database\Connection;
use Drupal\Core\Pager\PagerManagerInterface;
use Drupal\Core\Entity\EntityTypeManagerInterface;
use Drupal\castorcito\CastorcitoManager;
use Drupal\Component\Render\FormattableMarkup;
use Symfony\Component\DependencyInjection\ContainerInterface;

class CastorcitoComponentInUsePageForm extends FormBase {

  /**
   * The default limit.
   *
   */
  protected const LIMIT = 50;

  /**
   * The entity type manager.
   *
   * @var \Drupal\Core\Entity\EntityTypeManagerInterface
   */
  protected $entityTypeManager;

  /**
   * Castorcito Manager Service.
   *
   * @var \Drupal\castorcito\CastorcitoManager
   */
  protected $castorcitoManager;

  /**
   * The page manager service.
   *
   * @var \Drupal\Core\Pager\PagerManagerInterface
   */
  protected $pagerManager;

  /**
   * The current database connection.
   *
   * @var Drupal\Core\Database\Connection
   */
  protected $connection;

  /**
   * Constructs a new CastorcitoComponentInUsePageForm.
   * 
   * @param Drupal\Core\Entity\EntityTypeManagerInterface $entity_type_manager
   *   The entity type manager.
   * @param \Drupal\castorcito\CastorcitoManager $castorcito_manager
   *   The castorcito manager.
   * @param \Drupal\Core\Pager\PagerManagerInterface $pager_manager
   *   The pager manager.
   * @param Drupal\Core\Database\Connection $connection
   *   The current database connection.
   */
  public function __construct(
    EntityTypeManagerInterface $entity_type_manager,
    CastorcitoManager $castorcito_manager,
    PagerManagerInterface $pager_manager,
    Connection $connection
  ) {
    $this->entityTypeManager = $entity_type_manager;
    $this->castorcitoManager = $castorcito_manager;
    $this->pagerManager = $pager_manager;
    $this->connection = $connection;
  }

  /**
   * {@inheritdoc}
   */
  public static function create(ContainerInterface $container) {
    return new static(
      $container->get('entity_type.manager'),
      $container->get('castorcito.manager'),
      $container->get('pager.manager'),
      $container->get('database')
    );
  }

  /**
   * {@inheritdoc}
   */
  public function getFormId() {
    return 'castorcito_component_in_use_page_form';
  }

  /**
   * {@inheritdoc}
   */
  public function buildForm(array $form, FormStateInterface $form_state, $entity_type = NULL, $castorcito_component = NULL) {
    $search = $form_state->getValue('search') ?? $this->getRequest()->query->get('search');

    $entity_types = [
      'node',
      'block_content',
      'taxonomy_term',
      'commerce_product',
      'group',
    ];

    if (in_array($entity_type, $entity_types)) {
      $form['search'] = [
        '#type' => 'textfield',
        '#title' => $this->t('Search by label'),
        '#default_value' => $search,
      ];

      $form['submit'] = [
        '#type' => 'submit',
        '#value' => $this->t('Filter'),
      ];
    }
    else {
      $form['search_message'] = [
        '#type' => 'markup',
        '#markup' => $this->t('The search is only available when the list is of the entity types "node", "block_content", "taxonomy_term", "commerce_product", "group".'),
      ];
    }

    $list = $this->listUsedComponentsPage($entity_type, $castorcito_component);
    $rows = [];
    foreach ($list as $entity_id => $row) {
      $rows[] = [
        [
          'data' => $entity_id
        ],
        [
          'data' => new FormattableMarkup('<a href=":link" target="_blank">@label</a>', [
            ':link' => $row['link'],
            '@label' => $row['label'],
          ]),
        ],
      ];
    }

    $form['table'] = [
      '#type' => 'table',
      '#header' => ['Entity ID', 'Label'],
      '#rows' => $rows,
      '#empty' => $this->t('No results found.'),
    ];

    $form['pager'] = [
      '#type' => 'pager',
    ];

    return $form;
  }

  /**
   * {@inheritdoc}
   */
  public function submitForm(array &$form, FormStateInterface $form_state) {
    $search = $form_state->getValue('search');
    $query = $this->getRequest()->query->all();
    $new_query = array_merge($query, ['search' => $search]);

    $route_name = $this->getRouteMatch()->getRouteName();
    $route_parameters = $this->getRouteMatch()->getRawParameters()->all();

    $form_state->setRedirect($route_name, $route_parameters, ['query' => $new_query]);
  }

  /**
   * 
   * @param type $entity_type
   * @param type $component_id
   * @return array
   */
  protected function listUsedComponentsPage($entity_type, $component_id) {
    $json_fields = $this->entityTypeManager->getStorage('field_storage_config')->loadByProperties([
      'module' => 'json_field',
    ]);

    $search = $this->getRequest()->query->get('search');
    $query_parts = [];
    $args = [':like_value' => '%' . $component_id . '%'];
    if ($search) {
      $args[':like_search'] = '%' . $search . '%';
    }
    foreach ($json_fields as $field) {
      if ($field->getTargetEntityTypeId() !== $entity_type) {
        continue;
      }

      $field_table = $entity_type . '__' . $field->getName();
      $entity_data_table = $entity_type . '_field_data';
      $column = $field->getName() . '_value';
      
      $query_entity_types = [
        'node' => "
          SELECT t.entity_id, t.bundle
          FROM {$field_table} t
          INNER JOIN {$entity_data_table} nfd ON t.entity_id = nfd.nid
          WHERE t.{$column} LIKE :like_value AND nfd.title LIKE :like_search
        ",
        'block_content' => "
          SELECT t.entity_id, t.bundle
          FROM {$field_table} t
          INNER JOIN {$entity_data_table} bcfd ON t.entity_id = bcfd.id
          WHERE t.{$column} LIKE :like_value AND bcfd.info LIKE :like_search
        ",
        'taxonomy_term' => "
          SELECT t.entity_id, t.bundle
          FROM {$field_table} t
          INNER JOIN {$entity_data_table} ttfd ON t.entity_id = ttfd.tid
          WHERE t.{$column} LIKE :like_value AND ttfd.name LIKE :like_search
        ",
        'commerce_product' => "
          SELECT t.entity_id, t.bundle
          FROM {$field_table} t
          INNER JOIN {$entity_data_table} cpfd ON t.entity_id = cpfd.product_id
          WHERE t.{$column} LIKE :like_value AND cpfd.title LIKE :like_search
        ",
        'group' => "
          SELECT t.entity_id, t.bundle
          FROM {$field_table} t
          INNER JOIN groups_field_data gfd ON t.entity_id = gfd.id
          WHERE t.{$column} LIKE :like_value AND gfd.label LIKE :like_search
        ",
      ];

      if (isset($query_entity_types[$entity_type]) && $search) {
        $query_parts[] = $query_entity_types[$entity_type];
      }
      else {
        $query_parts[] = "
          SELECT entity_id, bundle
          FROM {$field_table}
          WHERE {$column} LIKE :like_value
        ";
      }
    }

    if (empty($query_parts)) {
      return [];
    }

    $sql = implode(' UNION ', $query_parts);
    $final_sql = "SELECT * FROM ({$sql}) AS subquery";

    $count_sql = "SELECT COUNT(*) FROM ({$sql}) AS subquery";
    $total = $this->connection->query($count_sql, $args)->fetchField();

    $pager = $this->pagerManager->createPager($total, self::LIMIT);
    $current_page = $pager->getCurrentPage();

    $offset = $current_page * self::LIMIT;

    $final_sql .= " ORDER BY entity_id ASC LIMIT " . self::LIMIT . " OFFSET " . $offset;

    $results = $this->connection->query($final_sql, $args)->fetchAll();

    $list = [];
    $entity_ids = array_column($results, 'entity_id');
    $entities = $this->entityTypeManager->getStorage($entity_type)->loadMultiple($entity_ids);
    foreach ($results as $row) {
      $entity = $entities[$row->entity_id] ?? NULL;
      if ($entity) {
        $list[$row->entity_id] = [
          'label' => $entity->label(),
          'link' => $entity->toUrl('edit-form')->toString(),
        ];
      }
    }

    return $list;
  }

  /**
   * Provides a generic title callback for a in use entity.
   * 
   * @param $castorcito_component
   * 
   * @return string|null
   *   The title for the entity in use page, if an entity was found.
   */
  public function inUseTitle($castorcito_component) {
    $component = $this->castorcitoManager->castorcitoComponent($castorcito_component);
    $entity_type_definition = $this->entityTypeManager->getDefinition($this->getRouteMatch()->getParameter('entity_type'));
    return $this->t('%entity_type - In use "%component"', [
      '%component' => $component->label(),
      '%entity_type' => $entity_type_definition->getBundleLabel()
    ]);
  }

}
