<?php
/**
 * Chat Feedback Admin Meta model.
 *
 * Stores admin-only workflow metadata for feedback/csat records.
 *
 * @package KimaAI\Models
 */

declare(strict_types=1);

namespace KimaAI\Models;

defined('ABSPATH') || exit;

class ChatFeedbackAdminModel {
  use HasSchema;

  public const TABLE = 'kimaai_chat_feedback_admin';
  public const PRIMARY_KEY = 'id';

  public const COLUMNS = [
    'id' => 'BIGINT(20) NOT NULL AUTO_INCREMENT',
    'kind' => 'VARCHAR(16) NOT NULL', // Allowed values: message, csat
    'ref_id' => 'BIGINT(20) NOT NULL', // id from feedback/csat table
    'addressed' => 'TINYINT(1) NOT NULL DEFAULT 0',
    'admin_note' => 'LONGTEXT NULL',
    'addressed_by' => 'BIGINT(20) NULL',
    'addressed_at' => 'TIMESTAMP NULL',
    'updated_at' => 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
    'created_at' => 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP',
  ];

  public const INDEXES = [
    'UNIQUE KEY uniq_kind_ref (kind, ref_id)',
    'KEY addressed_idx (addressed)',
    'KEY updated_at_idx (updated_at)',
  ];

  /**
   * The fully-qualified table name.
   *
   * @var string
   */
  private string $table_name;

  /**
   * ChatFeedbackAdminModel constructor.
   */
  public function __construct() {
    global $wpdb;
    $this->table_name = esc_sql($wpdb->prefix . self::TABLE);
  }

  /**
   * Upsert admin meta for a given record.
   */
  public function upsert(string $kind, int $ref_id, array $data): bool {
    global $wpdb;

    $kind = sanitize_text_field($kind);
    if (!in_array($kind, ['message', 'csat'], true) || $ref_id <= 0) {
      return false;
    }

    $addressed = isset($data['addressed']) ? (int) (bool) $data['addressed'] : 0;
    $admin_note = isset($data['admin_note']) ? sanitize_textarea_field((string) $data['admin_note']) : null;

    $addressed_by = get_current_user_id() ? (int) get_current_user_id() : null;
    $addressed_at = null;

    // If addressed is true, set timestamp; if toggled back to false, clear it.
    if ($addressed === 1) {
      $addressed_at = current_time('mysql');
    }

    // Use REPLACE to upsert on UNIQUE(kind, ref_id).
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
    $result = $wpdb->replace(
      $this->table_name,
      [
        'kind' => $kind,
        'ref_id' => $ref_id,
        'addressed' => $addressed,
        'admin_note' => $admin_note,
        'addressed_by' => $addressed === 1 ? $addressed_by : null,
        'addressed_at' => $addressed === 1 ? $addressed_at : null,
        'updated_at' => current_time('mysql'),
        'created_at' => current_time('mysql'),
      ],
      ['%s', '%d', '%d', '%s', '%d', '%s', '%s', '%s']
    );

    return (bool) $result;
  }

  /**
   * Returns admin meta for a single feedback or CSAT record.
   *
   * @param string $kind Record kind: 'message' or 'csat'.
   * @param int $ref_id The referenced record ID.
   * @return array
   */
  public function get(string $kind, int $ref_id): array {
    global $wpdb;

    $kind = sanitize_text_field($kind);
    if (!in_array($kind, ['message', 'csat'], true) || $ref_id <= 0) {
      return [];
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
    $row = $wpdb->get_row(
      $wpdb->prepare(
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        "SELECT * FROM {$this->table_name} WHERE kind = %s AND ref_id = %d LIMIT 1",
        $kind,
        $ref_id
      ),
      ARRAY_A
    );

    return is_array($row) ? $row : [];
  }

  /**
   * Batch get meta keyed by "kind:ref_id".
   */
  public function getMany(string $kind, array $ref_ids): array {
    global $wpdb;

    $kind = sanitize_text_field($kind);
    if (!in_array($kind, ['message', 'csat'], true)) {
      return [];
    }

    $ref_ids = array_values(array_filter(array_map('intval', $ref_ids), static fn($id) => $id > 0));
    if (empty($ref_ids)) {
      return [];
    }

    $placeholders = implode(',', array_fill(0, count($ref_ids), '%d'));
    // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
    $sql = $wpdb->prepare(
      // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
      "SELECT * FROM {$this->table_name} WHERE kind = %s AND ref_id IN ($placeholders)",
      array_merge([$kind], $ref_ids)
    );

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared
    $rows = $wpdb->get_results($sql, ARRAY_A);

    $out = [];
    foreach ($rows as $row) {
      $key = $kind . ':' . (int) ($row['ref_id'] ?? 0);
      $out[$key] = $row;
    }
    return $out;
  }

  /**
   * Deletes admin meta rows for a record kind and list of referenced IDs.
   *
   * @param string $kind Record kind: 'message' or 'csat'.
   * @param array $ref_ids Referenced record IDs.
   * @return int Number of deleted rows.
   */
  public function deleteByKindAndRefIds(string $kind, array $ref_ids): int {
    global $wpdb;

    $kind = sanitize_text_field($kind);
    if (!in_array($kind, ['message', 'csat'], true)) {
      return 0;
    }

    $ref_ids = array_values(array_filter(array_map('intval', $ref_ids), static fn($ref_id) => $ref_id > 0));
    if ($ref_ids === []) {
      return 0;
    }

    $placeholders = implode(', ', array_fill(0, count($ref_ids), '%d'));

    // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
    $prepared_delete = $wpdb->prepare(
      // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
      "DELETE FROM {$this->table_name} WHERE kind = %s AND ref_id IN ($placeholders)",
      array_merge([$kind], $ref_ids)
    );

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared
    $result = $wpdb->query($prepared_delete);

    return is_numeric($result) ? (int) $result : 0;
  }
}
