<?php
/**
 * Database schema for the public WordPress.org Free package.
 *
 * Owns only the email-verification table required by Free authentication.
 * It does not create, inspect, migrate or delete tables belonging to separately
 * distributed packages. Existing external-package tables remain untouched after
 * an in-place downgrade and can be reused if that package is installed again.
 */

if (!defined('ABSPATH')) {
  exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Intentional CUSTAUSU-owned schema inspection.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema checks must reflect current database state.

if (!defined('CUSTAUSU_DB_VERSION')) {
  define('CUSTAUSU_DB_VERSION', '4');
}

// CAS legacy DB-version compatibility alias.

if (!function_exists('custausu_db_version')) {
  function custausu_db_version(): string {
    return (string)CUSTAUSU_DB_VERSION;
  }
}

if (!function_exists('custausu_db_option_version_key')) {
  function custausu_db_option_version_key(): string {
    return 'custausu_db_version';
  }
}

if (!function_exists('custausu_db_option_migrations_key')) {
  function custausu_db_option_migrations_key(): string {
    return 'custausu_db_migrations';
  }
}

if (!function_exists('custausu_db_option_installed_package_plan_key')) {
  function custausu_db_option_installed_package_plan_key(): string {
    return 'custausu_db_installed_package_plan';
  }
}

if (!function_exists('custausu_db_sanitize_package_plan')) {
  function custausu_db_sanitize_package_plan($plan): string {
    unset($plan);
    return 'freemium';
  }
}

if (!function_exists('custausu_db_current_package_plan')) {
  function custausu_db_current_package_plan(): string {
    return 'freemium';
  }
}

if (!function_exists('custausu_db_table')) {
  function custausu_db_table(string $name): string {
    global $wpdb;
    return strtolower(trim($name)) === 'email_verifications'
      ? $wpdb->prefix . 'cas_email_verifications'
      : '';
  }
}

if (!function_exists('custausu_db_schema_keys_all')) {
  function custausu_db_schema_keys_all(): array {
    return ['email_verifications'];
  }
}

if (!function_exists('custausu_db_schema_keys_for_package')) {
  function custausu_db_schema_keys_for_package(?string $plan = null): array {
    unset($plan);
    return ['email_verifications'];
  }
}

if (!function_exists('custausu_db_tables')) {
  function custausu_db_tables(): array {
    return ['email_verifications' => custausu_db_table('email_verifications')];
  }
}

if (!function_exists('custausu_db_expected_tables')) {
  function custausu_db_expected_tables(?string $plan = null): array {
    unset($plan);
    return custausu_db_tables();
  }
}

if (!function_exists('custausu_db_table_exists')) {
  function custausu_db_table_exists(string $table): bool {
    global $wpdb;
    $table = trim($table);
    if ($table === '') {
      return false;
    }
    $found = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
    return is_string($found) && $found === $table;
  }
}

if (!function_exists('custausu_db_expected_tables_missing')) {
  function custausu_db_expected_tables_missing(?string $plan = null): array {
    $missing = [];
    foreach (custausu_db_expected_tables($plan) as $key => $table) {
      if (!custausu_db_table_exists($table)) {
        $missing[$key] = $table;
      }
    }
    return $missing;
  }
}

if (!function_exists('custausu_db_schema_sql')) {
  function custausu_db_schema_sql(?array $only_keys = null): array {
    global $wpdb;
    $table = custausu_db_table('email_verifications');
    $charset_collate = $wpdb->get_charset_collate();
    $sql = [
      'email_verifications' => "CREATE TABLE {$table} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        user_id bigint(20) unsigned NOT NULL,
        email varchar(191) NOT NULL,
        purpose varchar(50) NOT NULL,
        token_hash char(64) NOT NULL,
        status varchar(20) NOT NULL,
        sent_count int(10) unsigned NOT NULL DEFAULT 1,
        created_at datetime NOT NULL,
        expires_at datetime DEFAULT NULL,
        used_at datetime DEFAULT NULL,
        last_sent_at datetime DEFAULT NULL,
        meta_json longtext DEFAULT NULL,
        PRIMARY KEY  (id),
        UNIQUE KEY token_hash (token_hash),
        KEY user_id (user_id),
        KEY email (email),
        KEY purpose (purpose),
        KEY status (status),
        KEY expires_at (expires_at),
        KEY user_purpose_status (user_id, purpose, status)
      ) {$charset_collate};",
    ];

    if ($only_keys === null) {
      return $sql;
    }

    return array_intersect_key($sql, array_fill_keys($only_keys, true));
  }
}
