<?php
// app/services/MediaLibraryService.php

declare(strict_types=1);

namespace ScMedia\Services;

use ScMedia\Db\Db;

final class MediaLibraryService {
  private Db $db;
  private SettingsService $settings;
  private MetadataService $metadata;

  public function __construct(Db $db, SettingsService $settings, MetadataService $metadata) {
    $this->db = $db;
    $this->settings = $settings;
    $this->metadata = $metadata;
  }

  public function upsertMediaFile(
    int $profileId,
    string $root,
    string $abs,
    array $stat,
    bool $isMkv,
    ?array $mkvInfo
  ): void {
    $root = rtrim($root, "/");
    $rel = ltrim(str_replace($root, '', $abs), '/');
    if ($rel === '') {
      $rel = basename($abs);
    }

    $name = basename($abs);
    $ext = strtolower(pathinfo($abs, PATHINFO_EXTENSION));
    $size = (int)($stat['size'] ?? 0);
    $mtime = (int)($stat['mtime'] ?? 0);

    $seriesKey = null;
    $kind = 'unknown';
    if (str_contains($rel, '/')) {
      $parts = explode('/', $rel);
      $seriesKey = $parts[0] !== '' ? $parts[0] : null;
      $kind = $seriesKey ? 'series' : 'unknown';
    } else {
      $kind = 'movie';
    }

    [$container, $durationMs] = $this->extractContainerData($mkvInfo);

    $this->db->exec("
      INSERT INTO media_files
        (scan_profile_id, abs_path, rel_path, name, ext, size_bytes, mtime, is_mkv, kind, series_key, container, duration_ms, last_analyzed_at)
      VALUES
        (:pid, :abs, :rel, :name, :ext, :size, :mtime, :is_mkv, :kind, :series, :container, :duration, NOW())
      ON DUPLICATE KEY UPDATE
        scan_profile_id = VALUES(scan_profile_id),
        rel_path = VALUES(rel_path),
        name = VALUES(name),
        ext = VALUES(ext),
        size_bytes = VALUES(size_bytes),
        mtime = VALUES(mtime),
        is_mkv = VALUES(is_mkv),
        kind = VALUES(kind),
        series_key = VALUES(series_key),
        container = VALUES(container),
        duration_ms = VALUES(duration_ms),
        last_analyzed_at = NOW()
    ", [
      ':pid' => $profileId,
      ':abs' => $abs,
      ':rel' => $rel,
      ':name' => $name,
      ':ext' => $ext,
      ':size' => $size,
      ':mtime' => $mtime,
      ':is_mkv' => $isMkv ? 1 : 0,
      ':kind' => $kind,
      ':series' => $seriesKey,
      ':container' => $container !== '' ? $container : null,
      ':duration' => $durationMs,
    ]);
  }

  public function listTab(string $tab): array {
    if ($tab === 'series') {
      $rows = $this->db->fetchAll("
        SELECT series_key, COUNT(*) AS total_files
        FROM media_files
        WHERE is_mkv=1 AND kind='series' AND series_key IS NOT NULL
        GROUP BY series_key
        ORDER BY series_key ASC
        LIMIT 2000
      ");

      $out = [];
      $seriesKeys = array_map(fn($r) => (string)$r['series_key'], $rows);
      $metaMap = $this->metadata->getSubjectMetaMap('series', $seriesKeys);
      foreach ($rows as $r) {
        $key = (string)$r['series_key'];
        $files = $this->getSeriesFiles($key);
        $stats = $this->summarizeFiles($files);
      $meta = $metaMap[$key] ?? null;
      $display = $this->metadata->formatDisplay($meta);
        $out[] = [
          'series_key' => $key,
          'title_display' => $display['title_display'],
          'title_original' => $display['title_original'],
          'year' => $display['year'],
          'meta' => $display['meta'],
          'total_files' => (int)$r['total_files'],
          'needs_attention' => $stats['needs_attention'],
          'issues' => $stats['issues'],
        ];
      }
      return $out;
    }

    if ($tab === 'needs_mkv') {
      $rows = $this->db->fetchAll("
        SELECT *
        FROM media_files
        WHERE is_mkv=0
        ORDER BY updated_at DESC
        LIMIT 2000
      ");
      return $this->decorateFiles($rows, 'movie');
    }

    // movies (default)
    $rows = $this->db->fetchAll("
      SELECT *
      FROM media_files
      WHERE is_mkv=1 AND kind='movie'
      ORDER BY updated_at DESC
      LIMIT 2000
    ");
    return $this->decorateFiles($rows, 'movie');
  }

  public function listTabPaged(string $tab, int $page, int $perPage, string $sort, string $dir): array {
    $page = max(1, $page);
    $perPage = max(1, $perPage);
    $offset = ($page - 1) * $perPage;
    $dir = strtolower($dir) === 'asc' ? 'ASC' : 'DESC';

    if ($tab === 'series') {
      $orderBy = 'series_key ASC';
      if ($sort === 'name') {
        $orderBy = 'series_key ' . $dir;
      }
      $totalRow = $this->db->fetchOne("
        SELECT COUNT(DISTINCT series_key) AS total
        FROM media_files
        WHERE is_mkv=1 AND kind='series' AND series_key IS NOT NULL
      ");
      $total = (int)($totalRow['total'] ?? 0);
      $rows = $this->db->fetchAll("
        SELECT series_key, COUNT(*) AS total_files
        FROM media_files
        WHERE is_mkv=1 AND kind='series' AND series_key IS NOT NULL
        GROUP BY series_key
        ORDER BY {$orderBy}
        LIMIT {$perPage} OFFSET {$offset}
      ");

      $out = [];
      $seriesKeys = array_map(fn($r) => (string)$r['series_key'], $rows);
      $metaMap = $this->metadata->getSubjectMetaMap('series', $seriesKeys);
      foreach ($rows as $r) {
        $key = (string)$r['series_key'];
        $files = $this->getSeriesFiles($key);
        $stats = $this->summarizeFiles($files);
        $meta = $metaMap[$key] ?? null;
        $display = $this->metadata->formatDisplay($meta);
        $out[] = [
          'series_key' => $key,
          'title_display' => $display['title_display'],
          'title_original' => $display['title_original'],
          'year' => $display['year'],
          'meta' => $display['meta'],
          'total_files' => (int)$r['total_files'],
          'needs_attention' => $stats['needs_attention'],
          'issues' => $stats['issues'],
        ];
      }
      return ['items' => $out, 'total' => $total];
    }

    if ($tab === 'needs_mkv') {
      $orderBy = $this->mediaOrderBy($sort, $dir, 'updated_at DESC');
      $totalRow = $this->db->fetchOne("
        SELECT COUNT(*) AS total
        FROM media_files
        WHERE is_mkv=0
      ");
      $total = (int)($totalRow['total'] ?? 0);
      $rows = $this->db->fetchAll("
        SELECT *
        FROM media_files
        WHERE is_mkv=0
        ORDER BY {$orderBy}
        LIMIT {$perPage} OFFSET {$offset}
      ");
      $items = $this->decorateFiles($rows, 'movie');
      return ['items' => $items, 'total' => $total];
    }

    // movies (default)
    $orderBy = $this->mediaOrderBy($sort, $dir, 'updated_at DESC');
    $totalRow = $this->db->fetchOne("
      SELECT COUNT(*) AS total
      FROM media_files
      WHERE is_mkv=1 AND kind='movie'
    ");
    $total = (int)($totalRow['total'] ?? 0);
    $rows = $this->db->fetchAll("
      SELECT *
      FROM media_files
      WHERE is_mkv=1 AND kind='movie'
      ORDER BY {$orderBy}
      LIMIT {$perPage} OFFSET {$offset}
    ");
    $items = $this->decorateFiles($rows, 'movie');
    return ['items' => $items, 'total' => $total];
  }

  private function mediaOrderBy(string $sort, string $dir, string $fallback): string {
    $dir = strtoupper($dir) === 'DESC' ? 'DESC' : 'ASC';
    $map = [
      'name' => 'name',
      'mtime' => 'mtime',
      'updated' => 'updated_at',
      'size' => 'size_bytes',
    ];
    if (isset($map[$sort])) {
      return $map[$sort] . ' ' . $dir;
    }
    return $fallback;
  }

  public function getSeriesFiles(string $seriesKey): array {
    $rows = $this->db->fetchAll("
      SELECT *
      FROM media_files
      WHERE is_mkv=1 AND kind='series' AND series_key=:k
      ORDER BY rel_path ASC
      LIMIT 5000
    ", [':k' => $seriesKey]);
    return $this->decorateFiles($rows, 'series');
  }

  public function dryRun(string $tab): array {
    $rows = $this->listTab($tab);
    $totalFiles = 0;
    $rename = 0;
    $delete = 0;
    $unknown = 0;
    $convert = 0;
    $details = [];

    if ($tab === 'series') {
      foreach ($rows as $series) {
        $files = $this->getSeriesFiles((string)$series['series_key']);
        foreach ($files as $f) {
          $res = $this->planFile($f['abs_path'] ?? '');
          $totalFiles++;
          $rename += $res['rename'];
          $delete += $res['delete'];
          $unknown += $res['unknown_type'];
          $convert += $res['convert'];
          $details[] = $res['detail'];
        }
      }
    } else {
      foreach ($rows as $f) {
        $res = $this->planFile($f['abs_path'] ?? '');
        $totalFiles++;
        $rename += $res['rename'];
        $delete += $res['delete'];
        $unknown += $res['unknown_type'];
        $convert += $res['convert'];
        $details[] = $res['detail'];
      }
    }

    return [
      'files' => $totalFiles,
      'rename' => $rename,
      'delete' => $delete,
      'unknown_type' => $unknown,
      'convert' => $convert,
      'rows' => $details,
    ];
  }

  public function getFileDetail(string $absPath): array {
    $row = $this->db->fetchOne("
      SELECT *
      FROM media_files
      WHERE abs_path=:p
      LIMIT 1
    ", [':p' => $absPath]);

    if (!$row) {
      return ['ok' => false, 'error' => 'File not found'];
    }

    $meta = $this->db->fetchOne("
      SELECT info_json
      FROM media_file_meta
      WHERE abs_path=:p
      LIMIT 1
    ", [':p' => $absPath]);

    $info = null;
    if ($meta && isset($meta['info_json'])) {
      $decoded = json_decode((string)$meta['info_json'], true);
      $info = is_array($decoded) ? $decoded : null;
    }

    $rules = $this->getRules();
    $tracks = $this->parseTracks($info, $rules);
    $issues = $this->evaluateIssues($tracks, $rules);
    if (is_array($row) && is_array($info)) {
      $this->refreshContainerData((int)$row['id'], $info, $row);
      $row = $this->db->fetchOne("
        SELECT *
        FROM media_files
        WHERE abs_path=:p
        LIMIT 1
      ", [':p' => $absPath]) ?: $row;
    }

    $subject = $this->subjectForRow($row);
    $meta = $subject ? $this->metadata->getSubjectMeta($subject['kind'], $subject['key']) : null;
    $display = $this->metadata->formatDisplay($meta);
    $metaPayload = is_array($display['meta'] ?? null) ? $display['meta'] : [];
    if ($subject) {
      $metaPayload['subject_kind'] = $subject['kind'];
      $metaPayload['subject_key'] = $subject['key'];
    }
    $metaPayload['title_display'] = $display['title_display'];
    $metaPayload['title_original'] = $display['title_original'];
    $metaPayload['year'] = $display['year'];

    return [
      'ok' => true,
      'file' => $this->decorateFileRow($row, $issues),
      'tracks' => $tracks,
      'meta' => $metaPayload,
    ];
  }

  public function getRules(): array {
    $all = $this->settings->getAll();
    $rules = $all['media_rules'] ?? [];
    return [
      'name_map' => is_array($rules['name_map'] ?? null) ? $rules['name_map'] : [],
      'delete_rules' => is_array($rules['delete_rules'] ?? null) ? $rules['delete_rules'] : [],
      'language_priority' => is_array($rules['language_priority'] ?? null) ? $rules['language_priority'] : ['ru','en'],
      'audio_type_priority' => is_array($rules['audio_type_priority'] ?? null) ? $rules['audio_type_priority'] : ['dub','voiceover','original','commentary','unknown'],
      'require_audio_type' => !empty($rules['require_audio_type']),
      'series_order_threshold' => (float)($rules['series_order_threshold'] ?? 0.7),
    ];
  }

  private function planFile(string $absPath): array {
    $row = $this->db->fetchOne("SELECT * FROM media_files WHERE abs_path=:p LIMIT 1", [':p' => $absPath]);
    $name = $row ? (string)($row['name'] ?? $absPath) : $absPath;
    $plan = $this->buildEdits($absPath);

    $actions = [];
    if (count($plan['rename']) > 0) $actions[] = 'rename';
    if (count($plan['delete']) > 0) $actions[] = 'delete';
    if ($plan['unknown_type'] > 0) $actions[] = 'unknown_type';
    if ($plan['convert'] > 0) $actions[] = 'convert';

    return [
      'rename' => count($plan['rename']),
      'delete' => count($plan['delete']),
      'unknown_type' => $plan['unknown_type'],
      'convert' => $plan['convert'],
      'detail' => [
        'name' => $name,
        'actions' => $actions,
      ],
    ];
  }

  public function updateFilePath(string $absPath, string $newAbsPath, ?array $mkvInfo = null): void {
    $row = $this->db->fetchOne("SELECT * FROM media_files WHERE abs_path=:p LIMIT 1", [':p' => $absPath]);
    if (!$row) return;

    $rel = (string)($row['rel_path'] ?? '');
    $dir = $rel !== '' ? trim(dirname($rel), '.') : '';
    $newName = basename($newAbsPath);
    $newRel = $dir !== '' ? $dir . '/' . $newName : $newName;
    $stat = @stat($newAbsPath);
    $size = is_array($stat) ? (int)($stat['size'] ?? 0) : (int)($row['size_bytes'] ?? 0);
    $mtime = is_array($stat) ? (int)($stat['mtime'] ?? 0) : (int)($row['mtime'] ?? 0);

    [$container, $durationMs] = $this->extractContainerData($mkvInfo);

    $this->db->exec("
      UPDATE media_files
      SET abs_path=:abs, rel_path=:rel, name=:name, ext=:ext, is_mkv=1,
          size_bytes=:size, mtime=:mtime, container=:container, duration_ms=:duration,
          updated_at=NOW()
      WHERE abs_path=:old
    ", [
      ':abs' => $newAbsPath,
      ':rel' => $newRel,
      ':name' => $newName,
      ':ext' => strtolower(pathinfo($newAbsPath, PATHINFO_EXTENSION)),
      ':size' => $size,
      ':mtime' => $mtime,
      ':container' => $container !== '' ? $container : null,
      ':duration' => $durationMs,
      ':old' => $absPath,
    ]);
  }

  public function buildEdits(string $absPath): array {
    $row = $this->db->fetchOne("SELECT * FROM media_files WHERE abs_path=:p LIMIT 1", [':p' => $absPath]);
    $convert = ($row && empty($row['is_mkv'])) ? 1 : 0;

    $meta = $this->db->fetchOne("SELECT info_json FROM media_file_meta WHERE abs_path=:p LIMIT 1", [':p' => $absPath]);
    $info = null;
    if ($meta && isset($meta['info_json'])) {
      $decoded = json_decode((string)$meta['info_json'], true);
      $info = is_array($decoded) ? $decoded : null;
    }

    $rules = $this->getRules();
    $tracks = $this->parseTracks($info, $rules);
    $rename = [];
    $delete = [];
    $unknownCount = 0;
    $deleteIds = [];

    foreach ($tracks as $t) {
      $id = (int)($t['id'] ?? 0);
      if ($this->matchesDeleteRule($t, $rules['delete_rules'])) {
        if ($id > 0) {
          $deleteIds[$id] = true;
          $delete[] = $id;
        }
      }
      if ($t['name_norm'] !== '' && $t['name_norm'] !== $t['name']) {
        if ($id > 0) {
          $rename[] = ['id' => $id, 'name' => $t['name_norm']];
        }
      }
      if (!empty($rules['require_audio_type']) && $t['type'] === 'audio' && $t['audio_type'] === 'unknown') {
        $unknownCount++;
      }
    }

    // Do not rename deleted tracks
    $rename = array_values(array_filter($rename, fn($r) => !isset($deleteIds[(int)$r['id']])));

    return [
      'rename' => $rename,
      'delete' => $delete,
      'unknown_type' => $unknownCount,
      'convert' => $convert,
    ];
  }

  private function decorateFiles(array $rows, string $kind): array {
    $rules = $this->getRules();
    $keys = [];
    foreach ($rows as $r) {
      if ($kind === 'series') {
        $keys[] = (string)($r['series_key'] ?? '');
      } else {
        $keys[] = (string)($r['abs_path'] ?? '');
      }
    }
    $metaMap = $this->metadata->getSubjectMetaMap($kind === 'series' ? 'series' : 'movie', $keys);
    $out = [];
    foreach ($rows as $r) {
      $meta = null;
      if ((int)($r['is_mkv'] ?? 0) === 1) {
        $metaRow = $this->db->fetchOne("
          SELECT info_json
          FROM media_file_meta
          WHERE abs_path=:p
          LIMIT 1
        ", [':p' => (string)$r['abs_path']]);
        if ($metaRow && isset($metaRow['info_json'])) {
          $decoded = json_decode((string)$metaRow['info_json'], true);
          $meta = is_array($decoded) ? $decoded : null;
        }
      }

      $tracks = $this->parseTracks($meta, $rules);
      $issues = $this->evaluateIssues($tracks, $rules);
      if (is_array($meta)) {
        $this->refreshContainerData((int)$r['id'], $meta, $r);
      }
      $subjectKey = ($kind === 'series')
        ? (string)($r['series_key'] ?? '')
        : (string)($r['abs_path'] ?? '');
      $metaRow = $metaMap[$subjectKey] ?? null;
      $display = $this->metadata->formatDisplay($metaRow);
      $out[] = $this->decorateFileRow($r, $issues, $display);
    }
    return $out;
  }

  private function decorateFileRow(array $r, array $issues, ?array $display = null): array {
    $display = $display ?? $this->metadata->formatDisplay(null);
    return [
      'id' => (int)($r['id'] ?? 0),
      'abs_path' => (string)($r['abs_path'] ?? ''),
      'rel_path' => (string)($r['rel_path'] ?? ''),
      'name' => (string)($r['name'] ?? ''),
      'ext' => (string)($r['ext'] ?? ''),
      'is_mkv' => !empty($r['is_mkv']),
      'kind' => (string)($r['kind'] ?? 'unknown'),
      'series_key' => $r['series_key'] ?? null,
      'container' => $r['container'] ?? null,
      'duration_ms' => $r['duration_ms'] !== null ? (int)$r['duration_ms'] : null,
      'size_bytes' => (int)($r['size_bytes'] ?? 0),
      'needs_attention' => $issues['needs_attention'],
      'issues' => $issues['issues'],
      'title_display' => $display['title_display'],
      'title_original' => $display['title_original'],
      'year' => $display['year'],
      'meta' => $display['meta'],
    ];
  }

  private function subjectForRow(array $row): ?array {
    $kind = (string)($row['kind'] ?? 'unknown');
    if ($kind === 'series') {
      $seriesKey = (string)($row['series_key'] ?? '');
      if ($seriesKey !== '') {
        return ['kind' => 'series', 'key' => $seriesKey];
      }
    }
    $abs = (string)($row['abs_path'] ?? '');
    if ($abs !== '') {
      return ['kind' => 'movie', 'key' => $abs];
    }
    return null;
  }


  private function summarizeFiles(array $files): array {
    $needs = 0;
    $issues = [];
    foreach ($files as $f) {
      if (!empty($f['needs_attention'])) {
        $needs++;
        foreach (($f['issues'] ?? []) as $i) {
          $issues[$i] = true;
        }
      }
    }
    return [
      'needs_attention' => $needs,
      'issues' => array_values(array_keys($issues)),
    ];
  }

  private function parseTracks(?array $info, array $rules): array {
    if (!is_array($info)) return [];
    $tracks = is_array($info['tracks'] ?? null) ? $info['tracks'] : [];
    $out = [];
    foreach ($tracks as $t) {
      $props = is_array($t['properties'] ?? null) ? $t['properties'] : [];
      $type = (string)($t['type'] ?? '');
      $name = (string)($props['track_name'] ?? '');
      $lang = (string)($props['language'] ?? 'und');
      $codec = (string)($props['codec_id'] ?? '');
      $channels = isset($props['audio_channels']) ? (string)$props['audio_channels'] : '';
      $isDefault = !empty($props['default_track']);
      $isForced = !empty($props['forced_track']);

      $audioType = 'unknown';
      if ($type === 'audio') {
        $audioType = $this->detectAudioType($name);
      }

      $nameNorm = $this->applyNameMap($name, $rules['name_map']);

      $out[] = [
        'id' => (int)($t['id'] ?? 0),
        'type' => $type,
        'lang' => $lang,
        'name' => $name,
        'name_norm' => $nameNorm,
        'codec' => $codec,
        'channels' => $channels,
        'default' => $isDefault,
        'forced' => $isForced,
        'audio_type' => $audioType,
      ];
    }
    return $out;
  }

  private function evaluateIssues(array $tracks, array $rules): array {
    $issues = [];
    foreach ($tracks as $t) {
      if ($t['name_norm'] !== '' && $t['name_norm'] !== $t['name']) {
        $issues['rename'] = true;
      }
      if ($this->matchesDeleteRule($t, $rules['delete_rules'])) {
        $issues['delete'] = true;
      }
      if (!empty($rules['require_audio_type']) && $t['type'] === 'audio' && $t['audio_type'] === 'unknown') {
        $issues['unknown_type'] = true;
      }
    }
    return [
      'needs_attention' => count($issues) > 0,
      'issues' => array_values(array_keys($issues)),
    ];
  }

  private function applyNameMap(string $name, array $map): string {
    $trim = trim($name);
    if ($trim === '') return $name;

    foreach ($map as $row) {
      if (!is_array($row)) continue;
      $mode = (string)($row['mode'] ?? 'exact');
      $pattern = (string)($row['pattern'] ?? '');
      $canonical = (string)($row['canonical'] ?? '');
      if ($pattern === '' || $canonical === '') continue;

      if ($mode === 'exact') {
        if (mb_strtolower($trim) === mb_strtolower($pattern)) {
          return $canonical;
        }
      } else if ($mode === 'regex') {
        $ok = @preg_match($pattern, $trim);
        if ($ok === 1) {
          return $canonical;
        }
      }
    }

    return $name;
  }

  private function matchesDeleteRule(array $t, array $rules): bool {
    foreach ($rules as $r) {
      if (!is_array($r)) continue;
      $type = (string)($r['type'] ?? '');
      $lang = (string)($r['lang'] ?? '');
      $audioType = (string)($r['audio_type'] ?? '');
      $nameContains = (string)($r['name_contains'] ?? '');
      $exceptDefault = !empty($r['except_default']);
      $exceptForced = !empty($r['except_forced']);

      if ($type !== '' && $t['type'] !== $type) continue;
      if ($lang !== '' && $t['lang'] !== $lang) continue;
      if ($audioType !== '' && $t['audio_type'] !== $audioType) continue;
      if ($nameContains !== '' && !str_contains(mb_strtolower($t['name']), mb_strtolower($nameContains))) continue;
      if ($exceptDefault && !empty($t['default'])) continue;
      if ($exceptForced && !empty($t['forced'])) continue;

      return true;
    }
    return false;
  }

  private function detectAudioType(string $name): string {
    $n = mb_strtolower($name);
    if ($n === '') return 'unknown';

    $commentary = ['commentary','коммент','комментар','director','режисс'];
    $dub = ['dub','dubbing','дуб','дубляж','полный'];
    $voiceover = ['vo','voiceover','озвуч','многогол','двухгол','одногол'];
    $original = ['original','orig','оригин'];

    foreach ($commentary as $k) {
      if (str_contains($n, $k)) return 'commentary';
    }
    foreach ($dub as $k) {
      if (str_contains($n, $k)) return 'dub';
    }
    foreach ($voiceover as $k) {
      if (str_contains($n, $k)) return 'voiceover';
    }
    foreach ($original as $k) {
      if (str_contains($n, $k)) return 'original';
    }

    return 'unknown';
  }

  private function refreshContainerData(int $id, array $info, array $row): void {
    [$container, $durationMs] = $this->extractContainerData($info);

    $needsUpdate = false;
    if ($container !== '' && ($row['container'] ?? null) !== $container) {
      $needsUpdate = true;
    }
    if ($durationMs !== null && (int)($row['duration_ms'] ?? 0) !== $durationMs) {
      $needsUpdate = true;
    }
    if (!$needsUpdate) return;

    $this->db->exec("
      UPDATE media_files
      SET container=:c, duration_ms=:d, updated_at=NOW()
      WHERE id=:id
    ", [
      ':c' => $container !== '' ? $container : null,
      ':d' => $durationMs,
      ':id' => $id,
    ]);
  }

  private function extractContainerData(?array $info): array {
    if (!is_array($info)) {
      return [null, null];
    }
    $container = '';
    if (isset($info['container']['properties']['container_type'])) {
      $container = (string)$info['container']['properties']['container_type'];
    }
    if ($container === '' && isset($info['container']['type'])) {
      $container = (string)$info['container']['type'];
    }
    if ($container !== '' && preg_match('/^\d+$/', $container) && isset($info['container']['type'])) {
      $container = (string)$info['container']['type'];
    }

    $dur = $info['container']['properties']['duration'] ?? $info['container']['duration'] ?? null;
    $durationMs = null;
    if (is_numeric($dur)) {
      $durVal = (float)$dur;
      // mkvmerge reports duration in ns; guard for seconds in older formats
      if ($durVal > 1e9) {
        $durationMs = (int)round($durVal / 1e6);
      } else {
        $durationMs = (int)round($durVal * 1000);
      }
    }

    return [$container !== '' ? $container : null, $durationMs];
  }
}
