<?php
require_once __DIR__ . '/../app/db.php';
require_once __DIR__ . '/../app/Services/AccountingService.php';

function fleet_table_exists(PDO $pdo, string $table): bool {
  try {
    $st = $pdo->prepare("SHOW TABLES LIKE ?");
    $st->execute([$table]);
    return (bool)$st->fetchColumn();
  } catch (Throwable $e) {
    return false;
  }
}

function fleet_col_exists(PDO $pdo, string $table, string $col): bool {
  try {
    $st = $pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE ?");
    $st->execute([$col]);
    return (bool)$st->fetchColumn();
  } catch (Throwable $e) {
    return false;
  }
}

function fleet_add_column_if_missing(PDO $pdo, string $table, string $col, string $ddl): void {
  if (!fleet_table_exists($pdo, $table)) return;
  if (!fleet_col_exists($pdo, $table, $col)) {
    $pdo->exec("ALTER TABLE `{$table}` ADD COLUMN `{$col}` {$ddl}");
  }
}

function fleet_ensure_vehicle_receipts_columns(PDO $pdo): void {
  if (!fleet_table_exists($pdo, 'vehicle_receipts')) return;
  fleet_add_column_if_missing($pdo, 'vehicle_receipts', 'bank_txn_type', "VARCHAR(20) NULL AFTER payment_method");
  fleet_add_column_if_missing($pdo, 'vehicle_receipts', 'bank_name', "VARCHAR(120) NULL AFTER payment_method");
  fleet_add_column_if_missing($pdo, 'vehicle_receipts', 'bank_account_no', "VARCHAR(120) NULL AFTER bank_name");
  fleet_add_column_if_missing($pdo, 'vehicle_receipts', 'due_date', "DATE NULL AFTER bank_account_no");
}

function fleet_account_id_by_code(PDO $pdo, int $companyId, string $code): int {
  try {
    $st = $pdo->prepare("SELECT id FROM accounts WHERE company_id=? AND code=? LIMIT 1");
    $st->execute([$companyId, $code]);
    return (int)($st->fetchColumn() ?: 0);
  } catch (Throwable $e) {
    return 0;
  }
}



function fleet_vehicle_type_defaults(): array {
  return [
    [
      "brand" => "Mercedes-Benz",
      "models" => [
        ["name" => "A-Class", "trims" => ["A180", "A200", "A250"]],
        ["name" => "C-Class", "trims" => ["C180", "C200", "C300"]],
        ["name" => "E-Class", "trims" => ["E200", "E300", "E350"]],
        ["name" => "S-Class", "trims" => ["S450", "S500", "S580"]],
        ["name" => "V-Class", "trims" => ["V220", "V250", "V300"]],
        ["name" => "Vito", "trims" => ["Vito"]],
        ["name" => "Sprinter", "trims" => ["Sprinter"]],
        ["name" => "GLC", "trims" => ["GLC200", "GLC300"]],
        ["name" => "GLE", "trims" => ["GLE350", "GLE450"]],
        ["name" => "G-Class", "trims" => ["G500", "G63"]],
      ],
    ],
    [
      "brand" => "BMW",
      "models" => [
        ["name" => "1 Series", "trims" => ["118i", "120i"]],
        ["name" => "3 Series", "trims" => ["318i", "320i", "330i"]],
        ["name" => "5 Series", "trims" => ["520i", "530i", "540i"]],
        ["name" => "7 Series", "trims" => ["730i", "740i"]],
        ["name" => "X1", "trims" => ["X1"]],
        ["name" => "X3", "trims" => ["X3"]],
        ["name" => "X5", "trims" => ["X5"]],
        ["name" => "X7", "trims" => ["X7"]],
      ],
    ],
    [
      "brand" => "Audi",
      "models" => [
        ["name" => "A3", "trims" => ["A3"]],
        ["name" => "A4", "trims" => ["A4 35 TFSI", "A4 40 TFSI"]],
        ["name" => "A6", "trims" => ["A6 40 TFSI", "A6 45 TFSI"]],
        ["name" => "A8", "trims" => ["A8"]],
        ["name" => "Q3", "trims" => ["Q3"]],
        ["name" => "Q5", "trims" => ["Q5"]],
        ["name" => "Q7", "trims" => ["Q7"]],
      ],
    ],
    [
      "brand" => "Toyota",
      "models" => [
        ["name" => "Yaris", "trims" => ["Yaris"]],
        ["name" => "Corolla", "trims" => ["1.6", "2.0", "Hybrid"]],
        ["name" => "Camry", "trims" => ["2.5", "Hybrid"]],
        ["name" => "C-HR", "trims" => ["C-HR"]],
        ["name" => "RAV4", "trims" => ["2.5", "Hybrid"]],
        ["name" => "Fortuner", "trims" => ["Fortuner"]],
        ["name" => "Prado", "trims" => ["Prado"]],
        ["name" => "Land Cruiser", "trims" => ["Land Cruiser"]],
        ["name" => "Hiace", "trims" => ["Hiace 14-Seater", "Hiace 15-Seater"]],
        ["name" => "Coaster", "trims" => ["Coaster 30-Seater", "Coaster 33-Seater"]],
      ],
    ],
    [
      "brand" => "Hyundai",
      "models" => [
        ["name" => "Accent", "trims" => ["RB", "HC"]],
        ["name" => "Elantra", "trims" => ["AD", "CN7"]],
        ["name" => "Sonata", "trims" => ["Sonata"]],
        ["name" => "Tucson", "trims" => ["Tucson"]],
        ["name" => "Santa Fe", "trims" => ["Santa Fe"]],
        ["name" => "Staria", "trims" => ["Staria 9-Seater", "Staria 11-Seater"]],
        ["name" => "H1", "trims" => ["H1 12-Seater"]],
        ["name" => "County", "trims" => ["County 24-Seater"]],
      ],
    ],
    [
      "brand" => "Kia",
      "models" => [
        ["name" => "Pegas", "trims" => ["Pegas"]],
        ["name" => "Cerato", "trims" => ["Cerato"]],
        ["name" => "K5", "trims" => ["K5"]],
        ["name" => "Sportage", "trims" => ["Sportage"]],
        ["name" => "Sorento", "trims" => ["Sorento"]],
        ["name" => "Carnival", "trims" => ["Carnival 7-Seater", "Carnival 9-Seater"]],
      ],
    ],
    [
      "brand" => "Nissan",
      "models" => [
        ["name" => "Sunny", "trims" => ["Sunny"]],
        ["name" => "Sentra", "trims" => ["Sentra"]],
        ["name" => "Altima", "trims" => ["Altima"]],
        ["name" => "X-Trail", "trims" => ["X-Trail"]],
        ["name" => "Patrol", "trims" => ["Patrol"]],
        ["name" => "Urvan", "trims" => ["Urvan 12-Seater", "Urvan 15-Seater"]],
      ],
    ],
    [
      "brand" => "Chevrolet",
      "models" => [
        ["name" => "Optra", "trims" => ["Optra"]],
        ["name" => "Malibu", "trims" => ["Malibu"]],
        ["name" => "Captiva", "trims" => ["Captiva"]],
        ["name" => "Tahoe", "trims" => ["Tahoe"]],
        ["name" => "Suburban", "trims" => ["Suburban"]],
      ],
    ],
    [
      "brand" => "Ford",
      "models" => [
        ["name" => "Focus", "trims" => ["Focus"]],
        ["name" => "Fusion", "trims" => ["Fusion"]],
        ["name" => "Escape", "trims" => ["Escape"]],
        ["name" => "Explorer", "trims" => ["Explorer"]],
        ["name" => "Transit", "trims" => ["Transit"]],
        ["name" => "Tourneo", "trims" => ["Tourneo"]],
      ],
    ],
    [
      "brand" => "Volkswagen",
      "models" => [
        ["name" => "Polo", "trims" => ["Polo"]],
        ["name" => "Jetta", "trims" => ["Jetta"]],
        ["name" => "Passat", "trims" => ["Passat"]],
        ["name" => "Tiguan", "trims" => ["Tiguan"]],
        ["name" => "Touareg", "trims" => ["Touareg"]],
        ["name" => "Caravelle", "trims" => ["Caravelle"]],
        ["name" => "Crafter", "trims" => ["Crafter"]],
      ],
    ],
    [
      "brand" => "Skoda",
      "models" => [
        ["name" => "Octavia", "trims" => ["Octavia"]],
        ["name" => "Superb", "trims" => ["Superb"]],
        ["name" => "Kodiaq", "trims" => ["Kodiaq"]],
      ],
    ],
    [
      "brand" => "Renault",
      "models" => [
        ["name" => "Logan", "trims" => ["Logan"]],
        ["name" => "Megane", "trims" => ["Megane"]],
        ["name" => "Koleos", "trims" => ["Koleos"]],
        ["name" => "Trafic", "trims" => ["Trafic"]],
      ],
    ],
    [
      "brand" => "Peugeot",
      "models" => [
        ["name" => "301", "trims" => ["301"]],
        ["name" => "508", "trims" => ["508"]],
        ["name" => "3008", "trims" => ["3008"]],
        ["name" => "5008", "trims" => ["5008"]],
        ["name" => "Traveller", "trims" => ["Traveller"]],
      ],
    ],
    [
      "brand" => "Citroen",
      "models" => [
        ["name" => "C-Elysee", "trims" => ["C-Elysee"]],
        ["name" => "C4", "trims" => ["C4"]],
        ["name" => "C5 Aircross", "trims" => ["C5 Aircross"]],
        ["name" => "Jumpy", "trims" => ["Jumpy"]],
      ],
    ],
    [
      "brand" => "MG",
      "models" => [
        ["name" => "5", "trims" => ["MG 5"]],
        ["name" => "6", "trims" => ["MG 6"]],
        ["name" => "HS", "trims" => ["HS"]],
        ["name" => "RX5", "trims" => ["RX5"]],
      ],
    ],
    [
      "brand" => "Geely",
      "models" => [
        ["name" => "Emgrand", "trims" => ["Emgrand"]],
        ["name" => "Coolray", "trims" => ["Coolray"]],
        ["name" => "Monjaro", "trims" => ["Monjaro"]],
      ],
    ],
    [
      "brand" => "Chery",
      "models" => [
        ["name" => "Arrizo 5", "trims" => ["Arrizo 5"]],
        ["name" => "Tiggo 4", "trims" => ["Tiggo 4"]],
        ["name" => "Tiggo 7", "trims" => ["Tiggo 7"]],
        ["name" => "Tiggo 8", "trims" => ["Tiggo 8"]],
      ],
    ],
    [
      "brand" => "BYD",
      "models" => [
        ["name" => "F3", "trims" => ["F3"]],
        ["name" => "Qin Plus", "trims" => ["Qin Plus"]],
        ["name" => "Song Plus", "trims" => ["Song Plus"]],
        ["name" => "Tang", "trims" => ["Tang"]],
        ["name" => "Dolphin", "trims" => ["Dolphin"]],
      ],
    ],
    [
      "brand" => "Mitsubishi",
      "models" => [
        ["name" => "Lancer", "trims" => ["Lancer"]],
        ["name" => "Xpander", "trims" => ["Xpander"]],
        ["name" => "Pajero", "trims" => ["Pajero"]],
        ["name" => "Rosa", "trims" => ["Rosa 26-Seater", "Rosa 29-Seater"]],
        ["name" => "Canter", "trims" => ["Canter"]],
      ],
    ],
    [
      "brand" => "Suzuki",
      "models" => [
        ["name" => "Dzire", "trims" => ["Dzire"]],
        ["name" => "Ciaz", "trims" => ["Ciaz"]],
        ["name" => "Ertiga", "trims" => ["Ertiga"]],
        ["name" => "Jimny", "trims" => ["Jimny"]],
      ],
    ],
    [
      "brand" => "Isuzu",
      "models" => [
        ["name" => "D-Max", "trims" => ["D-Max"]],
        ["name" => "N-Series", "trims" => ["NQR", "NPR"]],
      ],
    ],
    [
      "brand" => "Honda",
      "models" => [
        ["name" => "City", "trims" => ["City"]],
        ["name" => "Civic", "trims" => ["Civic"]],
        ["name" => "Accord", "trims" => ["Accord"]],
        ["name" => "CR-V", "trims" => ["CR-V"]],
        ["name" => "Odyssey", "trims" => ["Odyssey"]],
      ],
    ],
    [
      "brand" => "Lexus",
      "models" => [
        ["name" => "ES", "trims" => ["ES250", "ES300h"]],
        ["name" => "LS", "trims" => ["LS500"]],
        ["name" => "RX", "trims" => ["RX350", "RX500h"]],
        ["name" => "LX", "trims" => ["LX600"]],
      ],
    ],
    [
      "brand" => "Volvo",
      "models" => [
        ["name" => "S60", "trims" => ["S60"]],
        ["name" => "S90", "trims" => ["S90"]],
        ["name" => "XC60", "trims" => ["XC60"]],
        ["name" => "XC90", "trims" => ["XC90"]],
      ],
    ],
    [
      "brand" => "Land Rover",
      "models" => [
        ["name" => "Discovery Sport", "trims" => ["Discovery Sport"]],
        ["name" => "Discovery", "trims" => ["Discovery"]],
        ["name" => "Range Rover Sport", "trims" => ["Range Rover Sport"]],
        ["name" => "Range Rover", "trims" => ["Range Rover"]],
      ],
    ],
    [
      "brand" => "Jeep",
      "models" => [
        ["name" => "Compass", "trims" => ["Compass"]],
        ["name" => "Grand Cherokee", "trims" => ["Grand Cherokee"]],
        ["name" => "Wrangler", "trims" => ["Wrangler"]],
      ],
    ],
  ];
}

function fleet_ensure_vehicle_types_schema(PDO $pdo): void {
  $pdo->exec("CREATE TABLE IF NOT EXISTS vehicle_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    parent_id INT NULL,
    node_type ENUM('BRAND','MODEL','TRIM') NOT NULL DEFAULT 'TRIM',
    name VARCHAR(190) NOT NULL,
    display_name VARCHAR(255) NULL,
    category VARCHAR(120) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uniq_vehicle_types_company_parent_name (company_id, parent_id, name),
    KEY idx_vehicle_types_company (company_id),
    KEY idx_vehicle_types_parent (parent_id),
    KEY idx_vehicle_types_node (company_id, node_type, is_active)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  fleet_add_column_if_missing($pdo, 'vehicle_types', 'parent_id', "INT NULL AFTER company_id");
  fleet_add_column_if_missing($pdo, 'vehicle_types', 'node_type', "ENUM('BRAND','MODEL','TRIM') NOT NULL DEFAULT 'TRIM' AFTER parent_id");
  fleet_add_column_if_missing($pdo, 'vehicle_types', 'display_name', "VARCHAR(255) NULL AFTER name");
  fleet_add_column_if_missing($pdo, 'vehicle_types', 'sort_order', "INT NOT NULL DEFAULT 0 AFTER category");

  try {
    $pdo->exec("UPDATE vehicle_types SET node_type='TRIM' WHERE node_type IS NULL OR node_type=''");
  } catch (Throwable $e) {}
  try {
    $pdo->exec("UPDATE vehicle_types SET display_name = name WHERE display_name IS NULL OR display_name=''");
  } catch (Throwable $e) {}

  try {
    $companyIds = $pdo->query("SELECT id FROM companies ORDER BY id")->fetchAll(PDO::FETCH_COLUMN) ?: [];
  } catch (Throwable $e) {
    $companyIds = [];
  }
  foreach ($companyIds as $cid) {
    fleet_seed_vehicle_types_for_company($pdo, (int)$cid);
  }
}

function fleet_find_vehicle_type_by_name(PDO $pdo, int $companyId, ?int $parentId, string $name): ?array {
  if ($parentId) {
    $st = $pdo->prepare("SELECT * FROM vehicle_types WHERE company_id=? AND parent_id=? AND name=? LIMIT 1");
    $st->execute([$companyId, $parentId, $name]);
  } else {
    $st = $pdo->prepare("SELECT * FROM vehicle_types WHERE company_id=? AND parent_id IS NULL AND name=? LIMIT 1");
    $st->execute([$companyId, $name]);
  }
  $row = $st->fetch(PDO::FETCH_ASSOC);
  return $row ?: null;
}

function fleet_vehicle_type_compose_display_name(PDO $pdo, int $companyId, ?int $parentId, string $name): string {
  $parts = [trim($name)];
  $visited = 0;
  while ($parentId && $visited < 10) {
    $visited++;
    $st = $pdo->prepare("SELECT id, parent_id, name FROM vehicle_types WHERE company_id=? AND id=? LIMIT 1");
    $st->execute([$companyId, $parentId]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    if (!$row) break;
    array_unshift($parts, trim((string)$row['name']));
    $parentId = $row['parent_id'] !== null ? (int)$row['parent_id'] : null;
  }
  return trim(implode(' / ', array_filter($parts, fn($v) => $v !== '')));
}

function fleet_vehicle_type_insert(PDO $pdo, int $companyId, ?int $parentId, string $nodeType, string $name, int $sortOrder=0, int $isSystem=0, ?int $createdBy=null): int {
  $nodeType = strtoupper(trim($nodeType));
  if (!in_array($nodeType, ['BRAND','MODEL','TRIM'], true)) $nodeType = 'TRIM';
  $display = fleet_vehicle_type_compose_display_name($pdo, $companyId, $parentId, $name);
  $sql = "INSERT INTO vehicle_types (company_id, parent_id, node_type, name, display_name, category, sort_order, is_system, is_active, created_by) VALUES (?,?,?,?,?,?,?,?,1,?)";
  $pdo->prepare($sql)->execute([$companyId, $parentId, $nodeType, $name, $display, null, $sortOrder, $isSystem, $createdBy]);
  return (int)$pdo->lastInsertId();
}

function fleet_seed_vehicle_types_for_company(PDO $pdo, int $companyId): void {
  $defaults = fleet_vehicle_type_defaults();
  $sortBrand = 10;
  foreach ($defaults as $brand) {
    $brandName = trim((string)($brand['brand'] ?? ''));
    if ($brandName === '') continue;
    $brandRow = fleet_find_vehicle_type_by_name($pdo, $companyId, null, $brandName);
    if (!$brandRow) {
      $brandId = fleet_vehicle_type_insert($pdo, $companyId, null, 'BRAND', $brandName, $sortBrand, 1, null);
    } else {
      $brandId = (int)$brandRow['id'];
      $pdo->prepare("UPDATE vehicle_types SET node_type='BRAND', is_system=1, is_active=1, sort_order=? WHERE id=? AND company_id=?")->execute([$sortBrand, $brandId, $companyId]);
    }
    $sortBrand += 10;
    $sortModel = 10;
    foreach (($brand['models'] ?? []) as $model) {
      $modelName = trim((string)($model['name'] ?? ''));
      if ($modelName === '') continue;
      $modelRow = fleet_find_vehicle_type_by_name($pdo, $companyId, $brandId, $modelName);
      if (!$modelRow) {
        $modelId = fleet_vehicle_type_insert($pdo, $companyId, $brandId, 'MODEL', $modelName, $sortModel, 1, null);
      } else {
        $modelId = (int)$modelRow['id'];
        $pdo->prepare("UPDATE vehicle_types SET node_type='MODEL', display_name=?, is_system=1, is_active=1, sort_order=? WHERE id=? AND company_id=?")
            ->execute([fleet_vehicle_type_compose_display_name($pdo, $companyId, $brandId, $modelName), $sortModel, $modelId, $companyId]);
      }
      $sortModel += 10;
      $sortTrim = 10;
      foreach (($model['trims'] ?? []) as $trimName) {
        $trimName = trim((string)$trimName);
        if ($trimName === '') continue;
        $trimRow = fleet_find_vehicle_type_by_name($pdo, $companyId, $modelId, $trimName);
        if (!$trimRow) {
          fleet_vehicle_type_insert($pdo, $companyId, $modelId, 'TRIM', $trimName, $sortTrim, 1, null);
        } else {
          $pdo->prepare("UPDATE vehicle_types SET node_type='TRIM', display_name=?, is_system=1, is_active=1, sort_order=? WHERE id=? AND company_id=?")
              ->execute([fleet_vehicle_type_compose_display_name($pdo, $companyId, $modelId, $trimName), $sortTrim, (int)$trimRow['id'], $companyId]);
        }
        $sortTrim += 10;
      }
    }
  }
}

function fleet_vehicle_types(PDO $pdo, int $companyId, bool $onlyLeaf=true): array {
  fleet_ensure_vehicle_types_schema($pdo);
  $sql = "SELECT vt.*, (SELECT COUNT(*) FROM vehicle_types c WHERE c.company_id=vt.company_id AND c.parent_id=vt.id AND c.is_active=1) AS children_count FROM vehicle_types vt WHERE vt.company_id=? AND vt.is_active=1";
  if ($onlyLeaf) {
    $sql .= " AND NOT EXISTS (SELECT 1 FROM vehicle_types c WHERE c.company_id=vt.company_id AND c.parent_id=vt.id AND c.is_active=1)";
  }
  $sql .= " ORDER BY COALESCE(vt.sort_order,0), vt.name";
  $st = $pdo->prepare($sql);
  $st->execute([$companyId]);
  return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}

function fleet_vehicle_type_name(PDO $pdo, int $companyId, ?int $typeId): string {
  $typeId = (int)($typeId ?? 0);
  if ($typeId <= 0) return '';
  $st = $pdo->prepare("SELECT COALESCE(display_name, name) FROM vehicle_types WHERE id=? AND company_id=? LIMIT 1");
  $st->execute([$typeId, $companyId]);
  return (string)($st->fetchColumn() ?: '');
}

function fleet_vehicle_type_tree(PDO $pdo, int $companyId): array {
  fleet_ensure_vehicle_types_schema($pdo);
  $st = $pdo->prepare("SELECT vt.*, (SELECT COUNT(*) FROM vehicle_types c WHERE c.company_id=vt.company_id AND c.parent_id=vt.id AND c.is_active=1) AS children_count, (SELECT COUNT(*) FROM vehicles v WHERE v.company_id=vt.company_id AND v.vehicle_type_id=vt.id) AS vehicles_count FROM vehicle_types vt WHERE vt.company_id=? ORDER BY COALESCE(vt.sort_order,0), vt.name");
  $st->execute([$companyId]);
  $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
  $byParent = [];
  foreach ($rows as $row) {
    $pid = $row['parent_id'] !== null ? (int)$row['parent_id'] : 0;
    $byParent[$pid][] = $row;
  }
  return [$rows, $byParent];
}

function fleet_ensure_schema(PDO $pdo): void {
  fleet_ensure_vehicle_types_schema($pdo);
  fleet_ensure_vehicles_schema($pdo);
  $pdo->exec("CREATE TABLE IF NOT EXISTS vehicle_order_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    code VARCHAR(60) NOT NULL,
    name VARCHAR(120) NOT NULL,
    revenue_account_id INT NULL,
    start_no INT NOT NULL DEFAULT 1,
    end_no INT NOT NULL DEFAULT 1000,
    current_no INT NOT NULL DEFAULT 0,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uniq_vehicle_order_types_company_code (company_id, code),
    INDEX idx_vehicle_order_types_company (company_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  fleet_seed_default_order_types($pdo);
}

function fleet_seed_default_order_types(PDO $pdo): void {
  if (!fleet_table_exists($pdo, 'vehicle_order_types')) return;

  try {
    $companyIds = $pdo->query("SELECT id FROM companies ORDER BY id")->fetchAll(PDO::FETCH_COLUMN);
  } catch (Throwable $e) {
    $companyIds = [];
  }

  $defaults = [
    ['code' => 'LIMO',  'name' => 'أمر انتقالات / ليموزين', 'revenue_code' => '4220'],
    ['code' => 'RENT',  'name' => 'أمر تأجير',               'revenue_code' => '4210'],
    ['code' => 'MAINT', 'name' => 'أمر صيانة',               'revenue_code' => null],
    ['code' => 'STOP',  'name' => 'أمر التوقف',              'revenue_code' => null],
  ];

  foreach ($companyIds as $cid) {
    foreach ($defaults as $d) {
      $chk = $pdo->prepare("SELECT id, revenue_account_id FROM vehicle_order_types WHERE company_id=? AND code=? LIMIT 1");
      $chk->execute([(int)$cid, $d['code']]);
      $row = $chk->fetch(PDO::FETCH_ASSOC);
      $accId = $d['revenue_code'] ? fleet_account_id_by_code($pdo, (int)$cid, $d['revenue_code']) : null;

      if ($row) {
        $currentAcc = (int)($row['revenue_account_id'] ?? 0);
        if ($currentAcc <= 0 && $accId) {
          $pdo->prepare("UPDATE vehicle_order_types SET revenue_account_id=?, is_system=1, is_active=1 WHERE id=? AND company_id=?")
              ->execute([$accId, (int)$row['id'], (int)$cid]);
        } else {
          $pdo->prepare("UPDATE vehicle_order_types SET is_system=1, is_active=1 WHERE id=? AND company_id=?")
              ->execute([(int)$row['id'], (int)$cid]);
        }
      } else {
        $pdo->prepare("INSERT INTO vehicle_order_types (company_id, code, name, revenue_account_id, start_no, end_no, current_no, is_system, is_active) VALUES (?,?,?,?,1,1000,0,1,1)")
            ->execute([(int)$cid, $d['code'], $d['name'], $accId]);
      }
    }
  }
}

function fleet_order_types(PDO $pdo, int $companyId): array {
  $st = $pdo->prepare("SELECT t.*, a.code AS acc_code, a.name AS acc_name
    FROM vehicle_order_types t
    LEFT JOIN accounts a ON a.id=t.revenue_account_id
    WHERE t.company_id=?
    ORDER BY t.is_system DESC, t.id ASC");
  $st->execute([$companyId]);
  return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}


function fleet_ensure_limo_schema(PDO $pdo): void {
  $pdo->exec("CREATE TABLE IF NOT EXISTS fleet_locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    name VARCHAR(190) NOT NULL,
    is_pickup TINYINT(1) NOT NULL DEFAULT 1,
    is_dropoff TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uniq_fleet_locations_company_name (company_id, name)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  $pdo->exec("CREATE TABLE IF NOT EXISTS fleet_limo_serials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    serial_no INT NOT NULL,
    trip_date DATE NULL,
    customer_id INT NULL,
    status ENUM('OPEN','DONE','CANCELLED') NOT NULL DEFAULT 'OPEN',
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uniq_fleet_limo_serials_company_serial (company_id, serial_no)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  $pdo->exec("CREATE TABLE IF NOT EXISTS fleet_limo_trips (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    serial_id INT NOT NULL,
    trip_no INT NOT NULL DEFAULT 1,
    display_no VARCHAR(60) NOT NULL,
    vehicle_id INT NOT NULL,
    driver_id INT NULL,
    customer_id INT NULL,
    trip_status ENUM('REQUESTED','DONE','COLLECTED','CANCELLED') NOT NULL DEFAULT 'REQUESTED',
    trip_date DATE NOT NULL,
    pickup_time TIME NULL,
    pickup_location_id INT NULL,
    dropoff_location_id INT NULL,
    km_out DECIMAL(12,2) NOT NULL DEFAULT 0,
    km_in DECIMAL(12,2) NULL,
    used_km DECIMAL(12,2) NOT NULL DEFAULT 0,
    trip_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_type ENUM('CASH','CREDIT') NOT NULL DEFAULT 'CASH',
    expected_collection_date DATE NULL,
    notes TEXT NULL,
    attachment_path VARCHAR(255) NULL,
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  fleet_ensure_vehicle_receipts_columns($pdo);
}

function fleet_limo_default_customer_id(PDO $pdo, int $companyId): ?int {
  if (!fleet_table_exists($pdo, 'customers')) return null;
  try {
    $st = $pdo->prepare("SELECT id FROM customers WHERE company_id=? AND full_name=? LIMIT 1");
    $st->execute([$companyId, 'نزيل الفندق']);
    $id = (int)($st->fetchColumn() ?: 0);
    if ($id > 0) return $id;

    $cols = [];
    foreach ($pdo->query("SHOW COLUMNS FROM customers")->fetchAll(PDO::FETCH_ASSOC) as $c) $cols[] = $c['Field'];

    $fields = ['company_id','full_name'];
    $placeholders = ['?','?'];
    $values = [$companyId, 'نزيل الفندق'];
    if (in_array('notes', $cols, true)) { $fields[]='notes'; $placeholders[]='?'; $values[]='عميل افتراضي لتشغيل الليموزين'; }
    $sql = "INSERT INTO customers (" . implode(',', $fields) . ") VALUES (" . implode(',', $placeholders) . ")";
    $pdo->prepare($sql)->execute($values);
    return (int)$pdo->lastInsertId();
  } catch (Throwable $e) { return null; }
}

function fleet_next_limo_serial_no(PDO $pdo, int $companyId): int {
  $st = $pdo->prepare("SELECT COALESCE(MAX(serial_no),0)+1 FROM fleet_limo_serials WHERE company_id=?");
  $st->execute([$companyId]);
  return max(1, (int)$st->fetchColumn());
}

function fleet_create_limo_serial(PDO $pdo, int $companyId, ?string $tripDate, ?int $customerId, int $userId=0): int {
  $serialNo = fleet_next_limo_serial_no($pdo, $companyId);
  $st = $pdo->prepare("INSERT INTO fleet_limo_serials (company_id, serial_no, trip_date, customer_id, created_by) VALUES (?,?,?,?,?)");
  $st->execute([$companyId, $serialNo, $tripDate ?: null, $customerId ?: null, $userId ?: null]);
  return (int)$pdo->lastInsertId();
}

function fleet_next_limo_trip_no(PDO $pdo, int $companyId, int $serialId): int {
  $st = $pdo->prepare("SELECT COALESCE(MAX(trip_no),0)+1 FROM fleet_limo_trips WHERE company_id=? AND serial_id=?");
  $st->execute([$companyId, $serialId]);
  return max(1, (int)$st->fetchColumn());
}

function fleet_limo_display_no(PDO $pdo, int $companyId, int $serialId, int $tripNo): string {
  $st = $pdo->prepare("SELECT serial_no FROM fleet_limo_serials WHERE id=? AND company_id=? LIMIT 1");
  $st->execute([$serialId, $companyId]);
  $serialNo = (int)($st->fetchColumn() ?: 0);
  return $serialNo . '-' . $tripNo;
}

function fleet_driver_options(PDO $pdo, int $companyId): array {
  return fleet_employee_options($pdo, $companyId);
}


function fleet_find_order_type(PDO $pdo, int $companyId, string $code): ?array {
  $st = $pdo->prepare("SELECT * FROM vehicle_order_types WHERE company_id=? AND code=? AND is_active=1 LIMIT 1");
  $st->execute([$companyId, strtoupper(trim($code))]);
  $row = $st->fetch(PDO::FETCH_ASSOC);
  return $row ?: null;
}

function fleet_next_order_serial(PDO $pdo, int $companyId, string $code): array {
  $manage = !$pdo->inTransaction();
  if ($manage) $pdo->beginTransaction();
  try {
    $st = $pdo->prepare("SELECT * FROM vehicle_order_types WHERE company_id=? AND code=? AND is_active=1 LIMIT 1 FOR UPDATE");
    $st->execute([$companyId, strtoupper(trim($code))]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    if (!$row) throw new RuntimeException('نوع أمر التشغيل غير متاح.');

    $startNo = max(1, (int)($row['start_no'] ?? 1));
    $endNo   = max($startNo, (int)($row['end_no'] ?? 1000));
    $current = (int)($row['current_no'] ?? 0);
    $next = $current > 0 ? ($current + 1) : $startNo;
    if ($next < $startNo) $next = $startNo;
    if ($next > $endNo) throw new RuntimeException('تم الوصول إلى آخر رقم متاح في سريال أمر التشغيل.');

    $pdo->prepare("UPDATE vehicle_order_types SET current_no=?, updated_at=NOW() WHERE id=? AND company_id=?")
        ->execute([$next, (int)$row['id'], $companyId]);

    $row['current_no'] = $next;
    if ($manage) $pdo->commit();
    return ['order_type' => $row, 'serial_no' => $next, 'serial_label' => strtoupper(trim($row['code'])) . '-' . $next];
  } catch (Throwable $e) {
    if ($manage && $pdo->inTransaction()) $pdo->rollBack();
    throw $e;
  }
}

function fleet_ensure_rental_schema(PDO $pdo): void {
  fleet_ensure_schema($pdo);

  $pdo->exec("CREATE TABLE IF NOT EXISTS vehicle_rental_contracts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    order_type_id INT NOT NULL,
    order_serial_no INT NOT NULL,
    contract_serial VARCHAR(60) NOT NULL,
    vehicle_id INT NOT NULL,
    customer_id INT NULL,
    km_out DECIMAL(12,2) NOT NULL DEFAULT 0,
    km_in DECIMAL(12,2) NULL,
    used_km DECIMAL(12,2) NOT NULL DEFAULT 0,
    contract_kind ENUM('COMMERCIAL','SERVICE') NOT NULL DEFAULT 'COMMERCIAL',
    rental_duration ENUM('MONTHLY','DAILY') NOT NULL DEFAULT 'MONTHLY',
    base_rent_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    extra_day_rate DECIMAL(14,2) NOT NULL DEFAULT 0,
    extra_km_rate DECIMAL(14,2) NOT NULL DEFAULT 0,
    contract_date DATE NOT NULL,
    expected_end_date DATE NOT NULL,
    actual_end_date DATE NULL,
    expected_days INT NOT NULL DEFAULT 30,
    actual_days INT NOT NULL DEFAULT 0,
    extra_days INT NOT NULL DEFAULT 0,
    extra_km DECIMAL(12,2) NOT NULL DEFAULT 0,
    additional_driver_1_id INT NULL,
    additional_driver_2_id INT NULL,
    insurance_party ENUM('COMPANY','CLIENT') NOT NULL DEFAULT 'COMPANY',
    insurance_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    insurance_refund DECIMAL(14,2) NOT NULL DEFAULT 0,
    return_condition ENUM('GOOD','DAMAGED') NULL,
    maintenance_charge DECIMAL(14,2) NOT NULL DEFAULT 0,
    gross_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    net_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_type ENUM('CASH','CREDIT') NULL,
    payment_due_date DATE NULL,
    status ENUM('ACTIVE','CLOSED','CANCELLED') NOT NULL DEFAULT 'ACTIVE',
    notes TEXT NULL,
    closed_notes TEXT NULL,
    posted_entry_id INT NULL,
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uniq_vehicle_rental_contracts_serial (company_id, order_type_id, order_serial_no),
    UNIQUE KEY uniq_vehicle_rental_contracts_contract_serial (company_id, contract_serial),
    KEY idx_vehicle_rental_contracts_company (company_id),
    KEY idx_vehicle_rental_contracts_vehicle (vehicle_id),
    KEY idx_vehicle_rental_contracts_customer (customer_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  $pdo->exec("CREATE TABLE IF NOT EXISTS vehicle_rental_maintenance_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    contract_id INT NOT NULL,
    item_name VARCHAR(190) NOT NULL,
    charge_to ENUM('CLIENT','COMPANY') NOT NULL DEFAULT 'CLIENT',
    amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    notes VARCHAR(255) NULL,
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_vehicle_rental_maint_company (company_id),
    KEY idx_vehicle_rental_maint_contract (contract_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  fleet_ensure_vehicle_receipts_columns($pdo);
}


function fleet_find_or_create_account(PDO $pdo, int $companyId, string $code, string $name, string $type, ?string $parentCode = null, bool $isCash = false): int {
  $existing = fleet_account_id_by_code($pdo, $companyId, $code);
  if ($existing) return $existing;

  $parentId = 0;
  if ($parentCode) {
    $parentId = fleet_account_id_by_code($pdo, $companyId, $parentCode);
  }
  if (!$parentId) {
    $roots = [
      'ASSET' => '1000',
      'LIABILITY' => '2000',
      'EQUITY' => '3000',
      'REVENUE' => '4000',
      'EXPENSE' => '5000',
    ];
    $parentId = fleet_account_id_by_code($pdo, $companyId, $roots[$type] ?? '1000');
  }

  $normalSide = in_array($type, ['LIABILITY','EQUITY','REVENUE'], true) ? 'CREDIT' : 'DEBIT';
  $stmt = $pdo->prepare("INSERT INTO accounts (company_id, code, name, type, normal_side, parent_id, is_cash, is_locked, is_active) VALUES (?,?,?,?,?,?,?,?,?)");
  $stmt->execute([$companyId, $code, $name, $type, $normalSide, $parentId ?: null, $isCash ? 1 : 0, 0, 1]);
  return (int)$pdo->lastInsertId();
}

function fleet_default_accounts(PDO $pdo, int $companyId): array {
  $cashId = fleet_account_id_by_code($pdo, $companyId, '1110') ?: fleet_account_id_by_code($pdo, $companyId, '1120');
  if (!$cashId) {
    $cashId = fleet_find_or_create_account($pdo, $companyId, '1110', 'الصندوق', 'ASSET', '1100', true);
  }

  $receivableId = 0;
  foreach (['1200', '1140', '1141'] as $code) {
    $receivableId = fleet_account_id_by_code($pdo, $companyId, $code);
    if ($receivableId) break;
  }
  if (!$receivableId) {
    $receivableId = fleet_find_or_create_account($pdo, $companyId, '1200', 'ذمم مدينة - العملاء', 'ASSET', '1000');
  }

  $advanceId = fleet_account_id_by_code($pdo, $companyId, '2141') ?: fleet_account_id_by_code($pdo, $companyId, '2140');
  if (!$advanceId) {
    $advanceId = fleet_find_or_create_account($pdo, $companyId, '2141', 'دفعات مقدمة من عملاء السيارات', 'LIABILITY', '2100');
  }

  $limoRevenueId = fleet_account_id_by_code($pdo, $companyId, '4220');
  if (!$limoRevenueId) {
    $limoRevenueId = fleet_find_or_create_account($pdo, $companyId, '4220', 'إيرادات انتقالات / ليموزين', 'REVENUE', '4200');
  }

  $rentRevenueId = fleet_account_id_by_code($pdo, $companyId, '4210');
  if (!$rentRevenueId) {
    $rentRevenueId = fleet_find_or_create_account($pdo, $companyId, '4210', 'إيرادات تأجير سيارات', 'REVENUE', '4200');
  }

  return [
    'cash_id' => (int)$cashId,
    'receivable_id' => (int)$receivableId,
    'advance_id' => (int)$advanceId,
    'limo_revenue_id' => (int)$limoRevenueId,
    'rent_revenue_id' => (int)$rentRevenueId,
  ];
}

function fleet_ensure_receipts_schema(PDO $pdo): void {
  $pdo->exec("CREATE TABLE IF NOT EXISTS vehicle_receipts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    receipt_no VARCHAR(60) NOT NULL,
    source_type ENUM('LIMO','RENT','ADVANCE') NOT NULL DEFAULT 'ADVANCE',
    source_id INT NULL,
    serial_ref VARCHAR(60) NULL,
    operation_ref VARCHAR(80) NULL,
    customer_id INT NULL,
    receipt_date DATE NOT NULL,
    cash_account_id INT NOT NULL,
    amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_method ENUM('CASH','BANK') NOT NULL DEFAULT 'CASH',
    bank_txn_type VARCHAR(20) NULL,
    bank_name VARCHAR(120) NULL,
    bank_account_no VARCHAR(120) NULL,
    due_date DATE NULL,
    memo TEXT NULL,
    posted_entry_id INT NULL,
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uniq_vehicle_receipts_company_no (company_id, receipt_no),
    INDEX idx_vehicle_receipts_company_date (company_id, receipt_date),
    INDEX idx_vehicle_receipts_source (company_id, source_type, source_id),
    INDEX idx_vehicle_receipts_customer (company_id, customer_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  fleet_ensure_vehicle_receipts_columns($pdo);
}

function fleet_receipt_next_code(PDO $pdo, int $companyId): string {
  $st = $pdo->prepare("SELECT receipt_no FROM vehicle_receipts WHERE company_id=? ORDER BY id DESC LIMIT 1");
  $st->execute([$companyId]);
  $last = (string)($st->fetchColumn() ?: '');
  if (preg_match('/(\d+)$/', $last, $m)) {
    return 'VR-' . str_pad((string)((int)$m[1] + 1), 5, '0', STR_PAD_LEFT);
  }
  return 'VR-00001';
}

function fleet_receipt_source_meta(PDO $pdo, int $companyId, string $sourceType, ?int $sourceId): array {
  $sourceType = strtoupper(trim($sourceType));
  if (!$sourceId || $sourceType === 'ADVANCE') {
    return ['serial_ref' => null, 'operation_ref' => null, 'customer_id' => null, 'amount' => 0.0];
  }

  if ($sourceType === 'LIMO') {
    $st = $pdo->prepare("SELECT t.id, ls.serial_no, t.display_no, t.customer_id, t.trip_amount
                         FROM fleet_limo_trips t
                         JOIN fleet_limo_serials ls ON ls.id=t.serial_id AND ls.company_id=t.company_id
                         WHERE t.company_id=? AND t.id=? LIMIT 1");
    $st->execute([$companyId, $sourceId]);
    $r = $st->fetch(PDO::FETCH_ASSOC) ?: null;
    if (!$r) throw new RuntimeException('أمر تشغيل الليموزين غير موجود.');
    return [
      'serial_ref' => (string)($r['serial_no'] ?? ''),
      'operation_ref' => (string)($r['display_no'] ?? ''),
      'customer_id' => (int)($r['customer_id'] ?? 0) ?: null,
      'amount' => (float)($r['trip_amount'] ?? 0),
    ];
  }

  if ($sourceType === 'RENT') {
    $st = $pdo->prepare("SELECT id, order_serial_no, contract_serial, customer_id,
                                CASE
                                  WHEN COALESCE(net_amount,0) > 0 THEN net_amount
                                  WHEN COALESCE(gross_amount,0) > 0 THEN gross_amount
                                  ELSE base_rent_amount
                                END AS due_base
                         FROM vehicle_rental_contracts
                         WHERE company_id=? AND id=? LIMIT 1");
    $st->execute([$companyId, $sourceId]);
    $r = $st->fetch(PDO::FETCH_ASSOC) ?: null;
    if (!$r) throw new RuntimeException('عقد التأجير غير موجود.');
    return [
      'serial_ref' => (string)($r['order_serial_no'] ?? ''),
      'operation_ref' => (string)($r['contract_serial'] ?? ''),
      'customer_id' => (int)($r['customer_id'] ?? 0) ?: null,
      'amount' => (float)($r['due_base'] ?? 0),
    ];
  }

  throw new RuntimeException('نوع مصدر التحصيل غير مدعوم.');
}

function fleet_create_receipt_journal(PDO $pdo, int $companyId, int $receiptId, int $userId = 0): int {
  fleet_ensure_receipts_schema($pdo);
  $svc = new AccountingService($pdo);
  $st = $pdo->prepare("SELECT r.*, c.account_id AS customer_account_id, c.full_name AS customer_name
                       FROM vehicle_receipts r
                       LEFT JOIN customers c ON c.id=r.customer_id AND c.company_id=r.company_id
                       WHERE r.id=? AND r.company_id=? LIMIT 1");
  $st->execute([$receiptId, $companyId]);
  $r = $st->fetch(PDO::FETCH_ASSOC) ?: null;
  if (!$r) throw new RuntimeException('سند التحصيل غير موجود.');
  if (!empty($r['posted_entry_id'])) return (int)$r['posted_entry_id'];

  $amount = (float)($r['amount'] ?? 0);
  if ($amount <= 0) throw new RuntimeException('قيمة التحصيل يجب أن تكون أكبر من صفر.');

  $acc = fleet_default_accounts($pdo, $companyId);
  $creditAccount = 0;
  $creditDesc = 'تحصيل سيارات';

  if (($r['source_type'] ?? '') === 'ADVANCE') {
    $creditAccount = (int)$acc['advance_id'];
    $creditDesc = 'دفعة مقدمة من عميل سيارات';
  } else {
    $creditAccount = (int)($r['customer_account_id'] ?? 0) ?: (int)$acc['receivable_id'];
    $creditDesc = 'تسوية ذمة العميل';
  }

  if (!$r['cash_account_id'] || !$creditAccount) {
    throw new RuntimeException('حسابات الربط لتحصيلات السيارات غير مكتملة.');
  }

  $ref = (string)($r['receipt_no'] ?: ('VR-' . $receiptId));
  $label = ($r['source_type'] ?? '') === 'ADVANCE' ? 'تحصيل مقدم تحت الحساب' : 'تحصيل سيارات';
  $memo = trim($label . ' ' . (string)($r['serial_ref'] ?? '') . ' ' . (string)($r['operation_ref'] ?? ''));
  if (!empty($r['memo'])) $memo .= ' - ' . trim((string)$r['memo']);

  $lines = [
    ['account_id' => (int)$r['cash_account_id'], 'debit' => $amount, 'credit' => 0, 'description' => 'تحصيل نقدي/بنكي'],
    ['account_id' => $creditAccount, 'debit' => 0, 'credit' => $amount, 'description' => $creditDesc],
  ];

  $entryId = $svc->createManualJournal($companyId, (string)$r['receipt_date'], $ref, $memo, $lines, $userId, 'MANUAL', $receiptId);
  $pdo->prepare("UPDATE vehicle_receipts SET posted_entry_id=?, updated_at=NOW() WHERE id=? AND company_id=?")->execute([$entryId, $receiptId, $companyId]);
  return $entryId;
}

function fleet_unpost_receipt_journal(PDO $pdo, int $companyId, int $receiptId): void {
  $st = $pdo->prepare("SELECT posted_entry_id FROM vehicle_receipts WHERE id=? AND company_id=? LIMIT 1");
  $st->execute([$receiptId, $companyId]);
  $entryId = (int)($st->fetchColumn() ?: 0);
  if (!$entryId) return;

  $pdo->beginTransaction();
  try {
    $pdo->prepare("DELETE FROM journal_lines WHERE company_id=? AND entry_id=?")->execute([$companyId, $entryId]);
    $pdo->prepare("DELETE FROM journal_entries WHERE company_id=? AND id=?")->execute([$companyId, $entryId]);
    $pdo->prepare("UPDATE vehicle_receipts SET posted_entry_id=NULL, updated_at=NOW() WHERE id=? AND company_id=?")->execute([$receiptId, $companyId]);
    $pdo->commit();
  } catch (Throwable $e) {
    if ($pdo->inTransaction()) $pdo->rollBack();
    throw $e;
  }
}
