<?php
declare(strict_types=1);

function ysp_data_dir(): string {
  $root = realpath(YSP_CODE_ROOT) ?: YSP_CODE_ROOT;
  $dir = $root . '/.data';
  if (!is_dir($dir)) @mkdir($dir, 0775, true);
  if (is_dir($dir)) @chmod($dir, 0777);
  return $dir;
}

function ysp_db_path(): string {
  return ysp_data_dir() . '/manager.sqlite';
}

function ysp_db_available(): bool {
  return class_exists('PDO') && in_array('sqlite', PDO::getAvailableDrivers(), true);
}

function ysp_db(): ?PDO {
  static $pdo = null;
  static $tried = false;
  if ($pdo instanceof PDO) return $pdo;
  if ($tried || !ysp_db_available()) return null;
  $tried = true;
  try {
    $pdo = new PDO('sqlite:' . ysp_db_path(), null, null, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    $pdo->exec('PRAGMA journal_mode = WAL');
    $pdo->exec('PRAGMA busy_timeout = 5000');
    $pdo->exec('PRAGMA foreign_keys = ON');
    ysp_db_init($pdo);
    @chmod(ysp_db_path(), 0666);
    return $pdo;
  } catch (Throwable $e) {
    error_log('YSP SQLite unavailable: ' . $e->getMessage());
    $pdo = null;
    return null;
  }
}

function ysp_db_init(PDO $db): void {
  $db->exec(
    'CREATE TABLE IF NOT EXISTS users (
      name TEXT PRIMARY KEY,
      password_hash TEXT NOT NULL,
      account_json TEXT NOT NULL,
      created_at INTEGER NOT NULL DEFAULT 0,
      last_seen INTEGER NOT NULL DEFAULT 0,
      updated_at INTEGER NOT NULL DEFAULT 0
    )'
  );
  $db->exec(
    'CREATE TABLE IF NOT EXISTS manager_save_meta (
      file TEXT PRIMARY KEY,
      source_size INTEGER NOT NULL DEFAULT 0,
      source_mtime INTEGER NOT NULL DEFAULT 0,
      cache_version INTEGER NOT NULL DEFAULT 0,
      meta_json TEXT NOT NULL,
      saved_at INTEGER NOT NULL DEFAULT 0,
      saved_by TEXT NOT NULL DEFAULT "",
      mode TEXT NOT NULL DEFAULT "",
      slot_key TEXT NOT NULL DEFAULT "",
      manager_game_id TEXT NOT NULL DEFAULT "",
      season_id TEXT NOT NULL DEFAULT "",
      season_year INTEGER NOT NULL DEFAULT 0,
      division_name TEXT NOT NULL DEFAULT "",
      save_group_key TEXT NOT NULL DEFAULT ""
    )'
  );
  $db->exec('CREATE TABLE IF NOT EXISTS manager_save_users (file TEXT NOT NULL, user TEXT NOT NULL, PRIMARY KEY(file, user))');
  $db->exec('CREATE TABLE IF NOT EXISTS manager_save_seasons (season_id TEXT NOT NULL, user TEXT NOT NULL, file TEXT NOT NULL, PRIMARY KEY(season_id, user, file))');
  $db->exec('CREATE INDEX IF NOT EXISTS idx_manager_save_users_user ON manager_save_users(user)');
  $db->exec('CREATE INDEX IF NOT EXISTS idx_manager_save_seasons_user ON manager_save_seasons(user)');
  $db->exec('CREATE INDEX IF NOT EXISTS idx_manager_save_meta_saved ON manager_save_meta(saved_at DESC)');
}

function ysp_db_sync_users_into_state(array &$state): void {
  $db = ysp_db();
  if (!$db) return;
  if (!isset($state['users']) || !is_array($state['users'])) $state['users'] = [];
  foreach ($db->query('SELECT name, account_json FROM users') ?: [] as $row) {
    $name = clean_name((string)($row['name'] ?? ''));
    if ($name === '') continue;
    $account = json_decode((string)($row['account_json'] ?? ''), true);
    if (is_array($account)) $state['users'][$name] = $account;
  }
}

function ysp_db_sync_users_from_state(array $state): void {
  $db = ysp_db();
  if (!$db || !is_array($state['users'] ?? null)) return;
  $stmt = $db->prepare(
    'INSERT INTO users (name, password_hash, account_json, created_at, last_seen, updated_at)
     VALUES (:name, :password_hash, :account_json, :created_at, :last_seen, :updated_at)
     ON CONFLICT(name) DO UPDATE SET
       password_hash = excluded.password_hash,
       account_json = excluded.account_json,
       created_at = excluded.created_at,
       last_seen = excluded.last_seen,
       updated_at = excluded.updated_at'
  );
  $now = time();
  foreach ($state['users'] as $name => $account) {
    $name = clean_name((string)$name);
    if ($name === '' || !is_array($account) || empty($account['password'])) continue;
    $json = json_encode($account, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
    if (!is_string($json)) continue;
    $stmt->execute([
      ':name' => $name,
      ':password_hash' => (string)$account['password'],
      ':account_json' => $json,
      ':created_at' => (int)($account['created'] ?? $now),
      ':last_seen' => (int)($account['lastSeen'] ?? $now),
      ':updated_at' => $now,
    ]);
  }
}

function ysp_db_delete_user(string $user): void {
  $db = ysp_db();
  $user = clean_name($user);
  if (!$db || $user === '') return;
  $stmt = $db->prepare('DELETE FROM users WHERE name = :name');
  $stmt->execute([':name' => $user]);
}

function ysp_db_upsert_manager_save_meta(string $file, array $meta, int $sourceSize, int $sourceMtime): void {
  $db = ysp_db();
  $file = basename($file);
  if (!$db || $file === '' || !str_ends_with($file, '.json')) return;
  $meta['id'] = $file;
  $meta['file'] = $file;
  $metaJson = json_encode($meta, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
  if (!is_string($metaJson)) return;
  $users = array_values(array_filter(array_map('clean_name', array_map('strval', $meta['users'] ?? []))));
  $seasonId = (string)($meta['seasonId'] ?? '');
  $db->beginTransaction();
  try {
    $stmt = $db->prepare(
      'INSERT INTO manager_save_meta
       (file, source_size, source_mtime, cache_version, meta_json, saved_at, saved_by, mode, slot_key, manager_game_id, season_id, season_year, division_name, save_group_key)
       VALUES (:file, :source_size, :source_mtime, :cache_version, :meta_json, :saved_at, :saved_by, :mode, :slot_key, :manager_game_id, :season_id, :season_year, :division_name, :save_group_key)
       ON CONFLICT(file) DO UPDATE SET
         source_size = excluded.source_size,
         source_mtime = excluded.source_mtime,
         cache_version = excluded.cache_version,
         meta_json = excluded.meta_json,
         saved_at = excluded.saved_at,
         saved_by = excluded.saved_by,
         mode = excluded.mode,
         slot_key = excluded.slot_key,
         manager_game_id = excluded.manager_game_id,
         season_id = excluded.season_id,
         season_year = excluded.season_year,
         division_name = excluded.division_name,
         save_group_key = excluded.save_group_key'
    );
    $stmt->execute([
      ':file' => $file,
      ':source_size' => $sourceSize,
      ':source_mtime' => $sourceMtime,
      ':cache_version' => MANAGER_SAVE_META_CACHE_VERSION,
      ':meta_json' => $metaJson,
      ':saved_at' => (int)($meta['savedAt'] ?? 0),
      ':saved_by' => clean_name((string)($meta['savedBy'] ?? '')),
      ':mode' => clean_name((string)($meta['mode'] ?? '')),
      ':slot_key' => (string)($meta['slotKey'] ?? ''),
      ':manager_game_id' => (string)($meta['managerGameId'] ?? ''),
      ':season_id' => $seasonId,
      ':season_year' => (int)($meta['seasonYear'] ?? 0),
      ':division_name' => (string)($meta['divisionName'] ?? ''),
      ':save_group_key' => (string)($meta['saveGroupKey'] ?? ''),
    ]);
    $db->prepare('DELETE FROM manager_save_users WHERE file = :file')->execute([':file' => $file]);
    $db->prepare('DELETE FROM manager_save_seasons WHERE file = :file')->execute([':file' => $file]);
    $userStmt = $db->prepare('INSERT OR IGNORE INTO manager_save_users (file, user) VALUES (:file, :user)');
    $seasonStmt = $db->prepare('INSERT OR IGNORE INTO manager_save_seasons (season_id, user, file) VALUES (:season_id, :user, :file)');
    foreach ($users as $user) {
      $userStmt->execute([':file' => $file, ':user' => $user]);
      if ($seasonId !== '') $seasonStmt->execute([':season_id' => $seasonId, ':user' => $user, ':file' => $file]);
    }
    $db->commit();
  } catch (Throwable $e) {
    if ($db->inTransaction()) $db->rollBack();
    error_log('YSP SQLite save meta upsert failed: ' . $e->getMessage());
  }
}

function ysp_db_delete_manager_save_meta(string $file): void {
  $db = ysp_db();
  $file = basename($file);
  if (!$db || $file === '') return;
  $db->prepare('DELETE FROM manager_save_users WHERE file = :file')->execute([':file' => $file]);
  $db->prepare('DELETE FROM manager_save_seasons WHERE file = :file')->execute([':file' => $file]);
  $db->prepare('DELETE FROM manager_save_meta WHERE file = :file')->execute([':file' => $file]);
}

function ysp_db_clear_manager_save_meta(): void {
  $db = ysp_db();
  if (!$db) return;
  $db->exec('DELETE FROM manager_save_users');
  $db->exec('DELETE FROM manager_save_seasons');
  $db->exec('DELETE FROM manager_save_meta');
}

function ysp_db_manager_save_index(): ?array {
  $db = ysp_db();
  if (!$db) return null;
  $users = [];
  foreach ($db->query('SELECT DISTINCT user FROM manager_save_users') ?: [] as $row) {
    $user = clean_name((string)($row['user'] ?? ''));
    if ($user !== '') $users[$user] = true;
  }
  $seasons = [];
  $seasonAny = [];
  foreach ($db->query('SELECT DISTINCT season_id, user FROM manager_save_seasons') ?: [] as $row) {
    $seasonId = (string)($row['season_id'] ?? '');
    $user = clean_name((string)($row['user'] ?? ''));
    if ($seasonId === '') continue;
    $seasonAny[$seasonId] = true;
    if ($user !== '') $seasons[$seasonId][$user] = true;
  }
  return [
    'cacheVersion' => MANAGER_SAVE_META_CACHE_VERSION,
    'builtAt' => time(),
    'users' => $users,
    'seasons' => $seasons,
    'seasonAny' => $seasonAny,
  ];
}

function ysp_db_manager_save_metas_for_user(string $user): ?array {
  $db = ysp_db();
  $user = clean_name($user);
  if (!$db || $user === '') return null;
  $stmt = $db->prepare(
    'SELECT m.meta_json
     FROM manager_save_meta m
     INNER JOIN manager_save_users u ON u.file = m.file
     WHERE u.user = :user
     ORDER BY m.saved_at DESC, m.file DESC'
  );
  $stmt->execute([':user' => $user]);
  $out = [];
  foreach ($stmt as $row) {
    $meta = json_decode((string)($row['meta_json'] ?? ''), true);
    if (is_array($meta)) $out[] = $meta;
  }
  return $out;
}
