<?php

namespace PloverCRM\Modules\Integrations\Services;

use PloverCRM\Modules\Integrations\Models\GoogleAdsConnection;

/**
 * Google Ads Connection Service.
 *
 * Stores the selected Google Ads customer accounts for the current OAuth connection.
 */
class GoogleAdsConnectionService
{
  const TABLE_NAME = 'plover_crm_google_connections';

  public static function isOauthConnected(): bool
  {
    $config = get_option('plover_crm_google_config', []);

    return !empty($config['refresh_token']);
  }

  public static function getAllConnections(bool $activeOnly = true): array
  {
    self::purgeConnectionsIfOauthMissing();

    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

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

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

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

  public static function getConnection(int $id): ?GoogleAdsConnection
  {
    self::purgeConnectionsIfOauthMissing();

    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 GoogleAdsConnection((array) $row);
  }

  public static function connectionExists(string $customerId): bool
  {
    self::purgeConnectionsIfOauthMissing();

    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

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

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

    // Check if account already connected
    if (self::connectionExists($data['customer_id'] ?? '')) {
      // Optionally update if exists, or return null. 
      // For now, let's treat it as "if exists, update token" which is safer for re-auth.
      // But to follow strict Add logic:
      return null;
    }

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

    if (!$result) {
      return null;
    }

    return $wpdb->insert_id;
  }

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

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

    $stringFields = ['customer_name', '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;
  }

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

  public static function deleteAllConnections(): int
  {
    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

    self::ensureTableExists();

    $deleted = $wpdb->query("DELETE FROM $table");

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

  public static function purgeConnectionsIfOauthMissing(): void
  {
    if (self::isOauthConnected()) {
      return;
    }

    global $wpdb;
    $table = $wpdb->prefix . self::TABLE_NAME;

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

    $wpdb->query("DELETE FROM $table");
  }

  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();
  }

  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,
      customer_id varchar(100) NOT NULL,
      customer_name varchar(255) NOT NULL,
      refresh_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 customer_id (customer_id),
      KEY status (status)
    ) $charset_collate;";

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