<?php

namespace PloverCRM\Modules\Integrations\Services;

use PloverCRM\Modules\Integrations\Models\MetaCapiConnection;

/**
 * Meta CAPI Connection Service
 * 
 * Manages Meta Conversion API connections storage and retrieval.
 */
class MetaCapiConnectionService
{
  const TABLE_NAME = 'plover_crm_meta_capi_connections';

  /**
   * Get all connections
   * 
   * @param bool $activeOnly Only return active connections
   * @return MetaCapiConnection[]
   */
  public static function getAllConnections(bool $activeOnly = true): array
  {
    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

    $where = $activeOnly ? "WHERE status = 'active'" : '';
    $results = $wpdb->get_results("SELECT * FROM $table $where ORDER BY name ASC");

    if (!$results) {
      return [];
    }

    return array_map(fn($row) => new MetaCapiConnection((array) $row), $results);
  }

  /**
   * Get a connection by ID
   */
  public static function getConnection(int $id): ?MetaCapiConnection
  {
    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

    $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE id = %d", $id));

    if (!$row) {
      return null;
    }

    return new MetaCapiConnection((array) $row);
  }

  /**
   * Check if any connection exists for a dataset
   */
  public static function datasetExists(string $datasetId): bool
  {
    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

    return (bool) $wpdb->get_var($wpdb->prepare(
      "SELECT COUNT(*) FROM $table WHERE dataset_id = %s",
      $datasetId
    ));
  }

  /**
   * Add a new connection
   */
  public static function addConnection(array $data): ?int
  {
    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

    // Check if dataset already connected
    if (self::datasetExists($data['dataset_id'] ?? '')) {
      return null;
    }

    $result = $wpdb->insert($table, [
      'name' => sanitize_text_field($data['name'] ?? ''),
      'dataset_id' => sanitize_text_field($data['dataset_id'] ?? ''),
      'access_token' => sanitize_text_field($data['access_token'] ?? ''),
      'status' => 'active',
      'connected_at' => time(),
      'updated_at' => time(),
    ], ['%s', '%s', '%s', '%s', '%d', '%d']);

    if (!$result) {
      return null;
    }

    return $wpdb->insert_id;
  }

  /**
   * Update a connection
   */
  public static function updateConnection(int $id, array $data): bool
  {
    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

    $update = [];
    $format = [];

    $stringFields = ['name', 'access_token', 'status'];

    foreach ($stringFields as $field) {
      if (isset($data[$field])) {
        $update[$field] = sanitize_text_field($data[$field]);
        $format[] = '%s';
      }
    }

    if (empty($update)) {
      return false;
    }

    $update['updated_at'] = time();
    $format[] = '%d';

    $result = $wpdb->update($table, $update, ['id' => $id], $format, ['%d']);

    return $result !== false;
  }

  /**
   * Delete a connection
   */
  public static function deleteConnection(int $id): bool
  {
    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;
    return (bool) $wpdb->delete($table, ['id' => $id], ['%d']);
  }

  /**
   * Check if any valid connection exists
   */
  public static function hasAnyConnection(): bool
  {
    $connections = self::getAllConnections(true);
    foreach ($connections as $connection) {
      if ($connection->isValid()) {
        return true;
      }
    }
    return false;
  }

  /**
   * Test connection credentials
   */
  public static function testConnection(string $datasetId, string $accessToken): array
  {
    $apiUrl = "https://graph.facebook.com/v24.0/{$datasetId}/events";

    // Send a test event
    $testPayload = [
      'data' => [
        [
          'event_name' => 'TestEvent',
          'event_time' => time(),
          'action_source' => 'system_generated',
          'user_data' => [
            'external_id' => 'test_' . uniqid()
          ]
        ]
      ]
    ];

    $args = [
      'method' => 'POST',
      'headers' => [
        'Content-Type' => 'application/json',
      ],
      'body' => json_encode($testPayload),
      'timeout' => 30,
    ];

    $apiUrl = add_query_arg(['access_token' => $accessToken], $apiUrl);
    $response = wp_remote_post($apiUrl, $args);

    if (is_wp_error($response)) {
      return [
        'success' => false,
        'message' => $response->get_error_message()
      ];
    }

    $responseCode = wp_remote_retrieve_response_code($response);
    $responseBody = wp_remote_retrieve_body($response);
    $data = json_decode($responseBody, true);

    if ($responseCode === 200 && !isset($data['error'])) {
      return [
        'success' => true,
        'message' => 'Connection successful'
      ];
    }

    return [
      'success' => false,
      'message' => $data['error']['message'] ?? "HTTP Error: {$responseCode}"
    ];
  }

  /**
   * Ensure the connections table exists
   */
  public static function ensureTableExists(): void
  {
    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

    if ($wpdb->get_var("SHOW TABLES LIKE '$table'") === $table) {
      return;
    }

    self::createTable();
  }

  /**
   * Create the connections table
   */
  public static function createTable(): void
  {
    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table (
      id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      name varchar(255) NOT NULL,
      dataset_id varchar(100) NOT NULL,
      access_token text NOT NULL,
      status varchar(20) NOT NULL DEFAULT 'active',
      connected_at bigint(20) unsigned NOT NULL DEFAULT 0,
      updated_at bigint(20) unsigned NOT NULL DEFAULT 0,
      PRIMARY KEY (id),
      UNIQUE KEY dataset_id (dataset_id),
      KEY status (status)
    ) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
  }
}
