CREATE TABLE api_keys (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 key_val TEXT NOT NULL UNIQUE,
 provider TEXT NOT NULL DEFAULT 'youtube',
 quota_used INTEGER NOT NULL DEFAULT 0,
 quota_limit INTEGER NOT NULL DEFAULT 10000,
 quota_date TEXT DEFAULT (DATE('now')), -- Track daily reset
 is_active INTEGER NOT NULL DEFAULT 1,
 error_count INTEGER NOT NULL DEFAULT 0,
 last_error TEXT,
 last_used TEXT
);

CREATE TABLE quota_log (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 key_id INTEGER NOT NULL REFERENCES api_keys(id),
 operation TEXT NOT NULL,
 region TEXT,
 units INTEGER NOT NULL DEFAULT 1,
 logged_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_quota_log_date ON quota_log(logged_at);
CREATE INDEX idx_quota_log_region ON quota_log(region, logged_at);

<?php

class ApiKeyManager
{
 private PDO $db;
 private ?array $current = null;

 public function __construct(PDO $db)
 {
 $this->db = $db;
 $this->resetExpiredQuotas();
 }

 private function resetExpiredQuotas(): void
 {
 // YouTube quota resets at midnight Pacific Time
 // We use the date stored on the row to detect day rollover
 $this->db->exec(<<<SQL
 UPDATE api_keys
 SET quota_used = 0,
 error_count = 0,
 is_active = 1,
 quota_date = DATE('now')
 WHERE quota_date < DATE('now')
 AND provider = 'youtube'
 SQL);
 }

 public function getBestKey(int $unitsNeeded = 1): ?array
 {
 if ($this->current !== null &&
 ($this->current['quota_limit'] - $this->current['quota_used']) >= $unitsNeeded) {
 return $this->current;
 }

 $stmt = $this->db->prepare(<<<SQL
 SELECT id, key_val, quota_used, quota_limit,
 (quota_limit - quota_used) AS remaining
 FROM api_keys
 WHERE provider = 'youtube'
 AND is_active = 1
 AND (quota_limit - quota_used) >= ?
 ORDER BY remaining DESC
 LIMIT 1
 SQL);
 $stmt->execute([$unitsNeeded]);

 $this->current = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
 return $this->current;
 }

 public function consume(int $keyId, string $operation, ?string $region, int $units): void
 {
 $this->db->prepare(<<<SQL
 UPDATE api_keys
 SET quota_used = quota_used + ?,
 last_used = datetime('now')
 WHERE id = ?
 SQL)->execute([$units, $keyId]);

 $this->db->prepare(<<<SQL
 INSERT INTO quota_log (key_id, operation, region, units)
 VALUES (?, ?, ?, ?)
 SQL)->execute([$keyId, $operation, $region, $units]);

 $this->current = null; // Invalidate cached key
 }

 public function markError(int $keyId, string $error): void
 {
 $this->db->prepare(<<<SQL
 UPDATE api_keys
 SET error_count = error_count + 1,
 last_error = ?,
 is_active = CASE WHEN error_count + 1 >= 3 THEN 0 ELSE is_active END
 WHERE id = ?
 SQL)->execute([$error, $keyId]);

 $this->current = null;
 }

 public function getUsageSummary(): array
 {
 return $this->db->query(<<<SQL
 SELECT
 k.key_val,
 k.quota_used,
 k.quota_limit,
 ROUND(100.0 * k.quota_used / k.quota_limit, 1) AS pct_used,
 k.is_active,
 (
 SELECT SUM(l.units)
 FROM quota_log l
 WHERE l.key_id = k.id
 AND DATE(l.logged_at) = DATE('now')
 AND l.operation = 'search.list'
 ) AS search_units_today
 FROM api_keys k
 WHERE k.provider = 'youtube'
 ORDER BY k.quota_used DESC
 SQL)->fetchAll(PDO::FETCH_ASSOC);
 }
}

<?php

class YouTubeClient
{
 private ApiKeyManager $keys;
 private const MAX_RETRIES = 3;

 public function fetchTrending(string $region, int $maxResults = 50): array
 {
 return $this->callApi('videos.list', [
 'part' => 'snippet,statistics',
 'chart' => 'mostPopular',
 'regionCode' => $region,
 'maxResults' => $maxResults,
 ], cost: 1, region: $region);
 }

 public function search(string $query, string $region): array
 {
 return $this->callApi('search', [
 'part' => 'snippet',
 'q' => $query,
 'regionCode' => $region,
 'type' => 'video',
 'maxResults' => 20,
 ], cost: 100, region: $region);
 }

 private function callApi(
 string $endpoint,
 array $params,
 int $cost,
 string $region
 ): array {
 $key = $this->keys->getBestKey($cost);
 if ($key === null) {
 // No quota — return graceful empty instead of crashing
 error_log("[TVS] All API keys exhausted. Returning cached/empty for {$region}");
 return [];
 }

 $params['key'] = $key['key_val'];
 $url = "https://www.googleapis.com/youtube/v3/{$endpoint}?" . http_build_query($params);

 $ch = curl_init($url);
 curl_setopt_array($ch, [
 CURLOPT_RETURNTRANSFER => true,
 CURLOPT_TIMEOUT => 15,
 CURLOPT_HTTPHEADER => ['Accept: application/json'],
 ]);
 $body = curl_exec($ch);
 $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
 curl_close($ch);

 if ($code === 403) {
 $err = json_decode($body, true);
 $reason = $err['error']['errors'][0]['reason'] ?? 'unknown';

 if ($reason === 'quotaExceeded') {
 // Force-exhaust this key so it rotates away
 $this->keys->consume($key['id'], $endpoint, $region,
 $key['quota_limit'] - $key['quota_used']);
 // Retry with next available key
 return $this->callApi($endpoint, $params, $cost, $region);
 }

 $this->keys->markError($key['id'], "403:{$reason}");
 return [];
 }

 if ($code !== 200) {
 $this->keys->markError($key['id'], "HTTP:{$code}");
 return [];
 }

 $this->keys->consume($key['id'], $endpoint, $region, $cost);
 return json_decode($body, true)['items'] ?? [];
 }
}

<?php

const REGION_SEARCH_BUDGET = [
 'AE' => 3000, // UAE — high Middle East traffic
 'GB' => 2000, // UK — strong European traffic
 'US' => 2000,
 'CH' => 800,
 'DK' => 600,
 'BE' => 600,
 'CZ' => 400,
 'FI' => 400,
];

function canSearch(string $region, PDO $db): bool
{
 $budget = REGION_SEARCH_BUDGET[$region] ?? 200;

 $used = $db->prepare(<<<SQL
 SELECT COALESCE(SUM(units), 0)
 FROM quota_log
 WHERE region = ?
 AND operation = 'search'
 AND DATE(logged_at) = DATE('now')
 SQL);
 $used->execute([$region]);
 $usedToday = (int)$used->fetchColumn();

 return ($usedToday + 100) <= $budget;
}

<?php
// GET /ibt — Admin panel quota section

$summary = $keyManager->getUsageSummary();
foreach ($summary as $key) {
 printf(
 "Key: ...%s | Used: %d/%d (%s%%) | Active: %s | Search today: %d units\n",
 substr($key['key_val'], -8),
 $key['quota_used'],
 $key['quota_limit'],
 $key['pct_used'],
 $key['is_active'] ? 'yes' : 'NO',
 $key['search_units_today'] ?? 0
 );
}

; api_keys.conf — deployed to all sites by ops.sh
youtube_key=AIzaSyABC123...
youtube_key=AIzaSyDEF456...
youtube_key=AIzaSyGHI789...

<?php

public function autoImportApiKeys(): void
{
 $conf = @parse_ini_file(__DIR__ . '/../api_keys.conf', false, INI_SCANNER_RAW);
 if (!$conf) return;

 $stmt = $this->pdo->prepare(<<<SQL
 INSERT OR IGNORE INTO api_keys (key_val, provider)
 VALUES (?, 'youtube')
 SQL);

 foreach ((array)($conf['youtube_key'] ?? []) as $key) {
 $stmt->execute([trim($key)]);
 }
}