<?php

namespace FlowSystems\WebhookActions\Repositories;

defined('ABSPATH') || exit;

class SchemaRepository {
  private string $schemasTable;

  public function __construct() {
    global $wpdb;
    $this->schemasTable = $wpdb->prefix . 'fswa_trigger_schemas';
  }

  /**
   * Find schema by webhook ID and trigger name
   *
   * @param int $webhookId
   * @param string $trigger
   * @return array|null
   */
  public function findByWebhookAndTrigger(int $webhookId, string $trigger): ?array {
    global $wpdb;

    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $schema = $wpdb->get_row(
      $wpdb->prepare(
        "SELECT * FROM {$this->schemasTable} WHERE webhook_id = %d AND trigger_name = %s",
        $webhookId,
        $trigger
      ),
      ARRAY_A
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

    if (!$schema) {
      return null;
    }

    return $this->decodeSchema($schema);
  }

  /**
   * Find the most recently captured example payload for a trigger on ANY webhook
   * (optionally excluding one). A do_action payload shape is the same regardless
   * of which webhook fired, so this lets a freshly created webhook reuse a payload
   * already captured elsewhere instead of requiring a new test submission.
   *
   * @param string $trigger
   * @param int    $excludeWebhookId
   * @return array|null
   */
  public function findLatestExampleByTrigger(string $trigger, int $excludeWebhookId = 0): ?array {
    global $wpdb;

    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $schema = $wpdb->get_row(
      $wpdb->prepare(
        "SELECT * FROM {$this->schemasTable}
          WHERE trigger_name = %s
            AND webhook_id <> %d
            AND example_payload IS NOT NULL
            AND example_payload <> ''
          ORDER BY captured_at DESC, id DESC
          LIMIT 1",
        $trigger,
        $excludeWebhookId
      ),
      ARRAY_A
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

    return $schema ? $this->decodeSchema($schema) : null;
  }

  /**
   * The most recent captured example payload per trigger, across all webhooks.
   * Used to ground the AI agent in the real payload shapes available on this
   * site (payload shape is trigger-global).
   *
   * @return array<string, array> trigger_name => decoded example payload
   */
  public function latestExamplesPerTrigger(int $maxTriggers = 8): array {
    global $wpdb;

    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $rows = $wpdb->get_results(
      "SELECT trigger_name, example_payload FROM {$this->schemasTable}
        WHERE example_payload IS NOT NULL
          AND example_payload <> ''
        ORDER BY captured_at DESC, id DESC",
      ARRAY_A
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

    $examples = [];
    foreach ((array) $rows as $row) {
      $trigger = (string) $row['trigger_name'];
      if ($trigger === '' || isset($examples[$trigger])) {
        continue;
      }
      $decoded = json_decode((string) $row['example_payload'], true);
      if (is_array($decoded)) {
        $examples[$trigger] = $decoded;
        if (count($examples) >= $maxTriggers) {
          break;
        }
      }
    }
    return $examples;
  }

  /**
   * Resolve the example payload the mapping UI / agent should use for a
   * webhook+trigger, honoring the per-schema "reuse shared example" toggle.
   *
   * - This webhook's own captured example always wins when present.
   * - Otherwise, when reuse is enabled (default), borrow the most recent example
   *   captured for the same trigger on any other webhook (the do_action payload
   *   shape is trigger-global).
   * - Otherwise nothing — the caller should prompt for a fresh capture.
   *
   * @param array|null $schema Pre-fetched row to avoid a duplicate query.
   * @return array{example: array|string|null, source: string|null, from_webhook_id: int}
   */
  public function resolveExample(int $webhookId, string $trigger, ?array $schema = null): array {
    $schema = $schema ?? $this->findByWebhookAndTrigger($webhookId, $trigger);

    if (!empty($schema['example_payload'])) {
      return ['example' => $schema['example_payload'], 'source' => 'own', 'from_webhook_id' => $webhookId];
    }

    // Default to reuse when there is no row yet or the toggle is truthy.
    $reuse = $schema === null ? true : (bool) ($schema['use_shared_example'] ?? 1);
    if ($reuse) {
      $borrowed = $this->findLatestExampleByTrigger($trigger, $webhookId);
      if (!empty($borrowed['example_payload'])) {
        return [
          'example'         => $borrowed['example_payload'],
          'source'          => 'shared',
          'from_webhook_id' => (int) ($borrowed['webhook_id'] ?? 0),
        ];
      }
    }

    return ['example' => null, 'source' => null, 'from_webhook_id' => 0];
  }

  /**
   * Get all schemas for a webhook
   *
   * @param int $webhookId
   * @return array
   */
  public function getByWebhook(int $webhookId): array {
    global $wpdb;

    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $schemas = $wpdb->get_results(
      $wpdb->prepare(
        "SELECT * FROM {$this->schemasTable} WHERE webhook_id = %d ORDER BY trigger_name",
        $webhookId
      ),
      ARRAY_A
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

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

    return array_map([$this, 'decodeSchema'], $schemas);
  }

  /**
   * Insert or update a schema
   *
   * @param int $webhookId
   * @param string $trigger
   * @param array $data
   * @return int|false Schema ID or false on failure
   */
  public function upsert(int $webhookId, string $trigger, array $data) {
    global $wpdb;

    $existing = $this->findByWebhookAndTrigger($webhookId, $trigger);

    $insertData = [
      'webhook_id' => $webhookId,
      'trigger_name' => $trigger,
    ];

    if (array_key_exists('example_payload', $data)) {
      $insertData['example_payload'] = is_array($data['example_payload'])
        ? wp_json_encode($data['example_payload'])
        : $data['example_payload'];
    }

    if (array_key_exists('field_mapping', $data)) {
      $insertData['field_mapping'] = is_array($data['field_mapping'])
        ? wp_json_encode($data['field_mapping'])
        : $data['field_mapping'];
    }

    if (array_key_exists('include_user_data', $data)) {
      $insertData['include_user_data'] = (int) $data['include_user_data'];
    }

    if (array_key_exists('use_shared_example', $data)) {
      $insertData['use_shared_example'] = (int) (bool) $data['use_shared_example'];
    }

    if (array_key_exists('captured_at', $data)) {
      $insertData['captured_at'] = $data['captured_at'];
    }

    if (array_key_exists('conditions', $data)) {
      $insertData['conditions'] = $data['conditions'] !== null
        ? (is_array($data['conditions']) ? wp_json_encode($data['conditions']) : $data['conditions'])
        : null;
    }

    if (array_key_exists('conditions_evaluate_on', $data)) {
      $insertData['conditions_evaluate_on'] = in_array($data['conditions_evaluate_on'], ['original', 'transformed'], true)
        ? $data['conditions_evaluate_on']
        : 'original';
    }

    if ($existing) {
      // Update existing record
      unset($insertData['webhook_id'], $insertData['trigger_name']);

      if (empty($insertData)) {
        return (int) $existing['id'];
      }

      // Build format array based on data types
      $formats = [];
      foreach ($insertData as $key => $value) {
        if (in_array($key, ['include_user_data', 'use_shared_example'], true)) {
          $formats[] = '%d';
        } else {
          $formats[] = '%s';
        }
      }

      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
      $result = $wpdb->update(
        $this->schemasTable,
        $insertData,
        ['id' => $existing['id']],
        $formats,
        ['%d']
      );

      return $result !== false ? (int) $existing['id'] : false;
    }

    // Insert new record - build format array based on data types
    $formats = [];
    foreach ($insertData as $key => $value) {
      if (in_array($key, ['webhook_id', 'include_user_data', 'use_shared_example'], true)) {
        $formats[] = '%d';
      } else {
        $formats[] = '%s';
      }
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
    $result = $wpdb->insert($this->schemasTable, $insertData, $formats);

    return $result ? $wpdb->insert_id : false;
  }

  /**
   * Capture example payload for a webhook/trigger combination
   *
   * @param int $webhookId
   * @param string $trigger
   * @param array $payload
   * @return bool
   */
  public function captureExamplePayload(int $webhookId, string $trigger, array $payload): bool {
    $existing = $this->findByWebhookAndTrigger($webhookId, $trigger);

    // Keep taking the freshest example until the webhook is actually configured.
    //
    // Freezing on the very first firing looks cheap but hands the agent the
    // wrong payload on any trigger that fires more than once: opening the
    // new-post screen makes WordPress write an auto-draft, so
    // transition_post_status captures ["auto-draft","new",…] with post_title
    // "Auto Draft" and post_date_gmt "0000-00-00 00:00:00" — and the real
    // publish a minute later was discarded. Everything downstream then inherits
    // it: the mapping is built from a draft, and test_dispatch sends that draft.
    //
    // A stored field mapping means the build is settled, so from then on the
    // example stays put and the write stops happening on every event.
    if ($existing && !empty($existing['example_payload']) && !empty($existing['field_mapping'])) {
      return true;
    }

    $result = $this->upsert($webhookId, $trigger, [
      'example_payload' => $payload,
      'captured_at' => current_time('mysql'),
    ]);

    return $result !== false;
  }

  /**
   * Delete a schema by ID
   *
   * @param int $id
   * @return bool
   */
  public function delete(int $id): bool {
    global $wpdb;

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $result = $wpdb->delete($this->schemasTable, ['id' => $id], ['%d']);

    return $result !== false;
  }

  /**
   * Delete all schemas for a webhook
   *
   * @param int $webhookId
   * @return bool
   */
  public function deleteByWebhook(int $webhookId): bool {
    global $wpdb;

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $result = $wpdb->delete($this->schemasTable, ['webhook_id' => $webhookId], ['%d']);

    return $result !== false;
  }

  /**
   * Delete schema by webhook and trigger
   *
   * @param int $webhookId
   * @param string $trigger
   * @return bool
   */
  public function deleteByWebhookAndTrigger(int $webhookId, string $trigger): bool {
    global $wpdb;

    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $result = $wpdb->query(
      $wpdb->prepare(
        "DELETE FROM {$this->schemasTable} WHERE webhook_id = %d AND trigger_name = %s",
        $webhookId,
        $trigger
      )
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

    return $result !== false;
  }

  /**
   * Clear the captured example payload (for re-capture)
   *
   * @param int $webhookId
   * @param string $trigger
   * @return bool
   */
  public function clearExamplePayload(int $webhookId, string $trigger): bool {
    $existing = $this->findByWebhookAndTrigger($webhookId, $trigger);

    if (!$existing) {
      return true;
    }

    global $wpdb;

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $result = $wpdb->update(
      $this->schemasTable,
      [
        'example_payload' => null,
        'captured_at' => null,
      ],
      ['id' => $existing['id']],
      ['%s', '%s'],
      ['%d']
    );

    return $result !== false;
  }

  /**
   * Decode JSON fields in schema
   *
   * @param array $schema
   * @return array
   */
  private function decodeSchema(array $schema): array {
    if (!empty($schema['example_payload'])) {
      $decoded = json_decode($schema['example_payload'], true);
      if ($decoded !== null || json_last_error() === JSON_ERROR_NONE) {
        $schema['example_payload'] = $decoded;
      }
    }

    if (!empty($schema['field_mapping'])) {
      $decoded = json_decode($schema['field_mapping'], true);
      if ($decoded !== null || json_last_error() === JSON_ERROR_NONE) {
        $schema['field_mapping'] = $decoded;
      }
    }

    if (!empty($schema['conditions'])) {
      $decoded = json_decode($schema['conditions'], true);
      if ($decoded !== null || json_last_error() === JSON_ERROR_NONE) {
        $schema['conditions'] = $decoded;
      }
    } else {
      $schema['conditions'] = null;
    }

    $schema['include_user_data'] = (bool) ($schema['include_user_data'] ?? false);

    if (!in_array($schema['conditions_evaluate_on'] ?? '', ['original', 'transformed'], true)) {
      $schema['conditions_evaluate_on'] = 'original';
    }

    return $schema;
  }
}
