<?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_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_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,
    name VARCHAR(190) NOT NULL,
    category VARCHAR(120) NULL,
    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_name (company_id, name),
    KEY idx_vehicle_types_company (company_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

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

  $defaults = [
    ['name'=>'سيدان','category'=>'سيارات ركوب'],
    ['name'=>'هاتشباك','category'=>'سيارات ركوب'],
    ['name'=>'كوبيه','category'=>'سيارات ركوب'],
    ['name'=>'كشف / كابريو','category'=>'سيارات ركوب'],
    ['name'=>'واجن','category'=>'سيارات ركوب'],
    ['name'=>'SUV رياضية متعددة الاستخدام','category'=>'سيارات ركوب'],
    ['name'=>'كروس أوفر','category'=>'سيارات ركوب'],
    ['name'=>'MPV متعددة الأغراض','category'=>'سيارات ركوب'],
    ['name'=>'ميني فان','category'=>'نقل خفيف'],
    ['name'=>'فان','category'=>'نقل خفيف'],
    ['name'=>'ميكروباص','category'=>'نقل ركاب'],
    ['name'=>'أتوبيس صغير','category'=>'نقل ركاب'],
    ['name'=>'أتوبيس','category'=>'نقل ركاب'],
    ['name'=>'أتوبيس مدرسي','category'=>'نقل ركاب'],
    ['name'=>'شاتل / باص نقل موظفين','category'=>'نقل ركاب'],
    ['name'=>'ليموزين','category'=>'خدمة خاصة'],
    ['name'=>'دفع رباعي 4x4','category'=>'سيارات ركوب'],
    ['name'=>'أوف رود','category'=>'سيارات ركوب'],
    ['name'=>'بيك أب','category'=>'نقل خفيف'],
    ['name'=>'نقل صندوق','category'=>'شاحنات'],
    ['name'=>'نقل ستارة','category'=>'شاحنات'],
    ['name'=>'نقل مبرد / ثلاجة','category'=>'شاحنات'],
    ['name'=>'نقل مسطح','category'=>'شاحنات'],
    ['name'=>'قلاّب','category'=>'شاحنات'],
    ['name'=>'خلاطة خرسانة','category'=>'شاحنات'],
    ['name'=>'ونش / كرين','category'=>'معدات نقل'],
    ['name'=>'سحب وإنقاذ','category'=>'معدات نقل'],
    ['name'=>'تنك / صهريج','category'=>'شاحنات'],
    ['name'=>'رأس جرار','category'=>'شاحنات'],
    ['name'=>'مقطورة','category'=>'شاحنات'],
    ['name'=>'نصف مقطورة','category'=>'شاحنات'],
    ['name'=>'سيارة إسعاف','category'=>'خدمات'],
    ['name'=>'سيارة إطفاء','category'=>'خدمات'],
    ['name'=>'سيارة شرطة / أمن','category'=>'خدمات'],
    ['name'=>'عربة جولف','category'=>'خدمات'],
    ['name'=>'كامبر / منزل متنقل','category'=>'خدمات'],
    ['name'=>'دراجة نارية','category'=>'مركبات خفيفة'],
    ['name'=>'سكوتر','category'=>'مركبات خفيفة'],
    ['name'=>'ATV رباعية','category'=>'مركبات خفيفة'],
    ['name'=>'UTV متعددة التضاريس','category'=>'مركبات خفيفة'],
    ['name'=>'سيارة كهربائية','category'=>'تقنيات'],
    ['name'=>'سيارة هجينة','category'=>'تقنيات'],
    ['name'=>'سيدان فاخرة','category'=>'سيارات ركوب'],
    ['name'=>'سيارة جنائز','category'=>'خدمات'],
  ];

  $chk = $pdo->prepare("SELECT id FROM vehicle_types WHERE company_id=? AND name=? LIMIT 1");
  $ins = $pdo->prepare("INSERT INTO vehicle_types (company_id, name, category, is_system, is_active) VALUES (?,?,?,?,1)");
  foreach ($companyIds as $cid) {
    foreach ($defaults as $d) {
      $chk->execute([(int)$cid, $d['name']]);
      if (!$chk->fetchColumn()) {
        $ins->execute([(int)$cid, $d['name'], $d['category'], 1]);
      }
    }
  }
}

function fleet_ensure_vehicles_schema(PDO $pdo): void {
  if (!fleet_table_exists($pdo, 'vehicles')) {
    $pdo->exec("CREATE TABLE IF NOT EXISTS vehicles (
      id INT AUTO_INCREMENT PRIMARY KEY,
      company_id INT NOT NULL,
      plate_no VARCHAR(80) NOT NULL,
      plate_no_current VARCHAR(80) NULL,
      vehicle_type_id INT NULL,
      ownership_type ENUM('OWNED','EXTERNAL') NOT NULL DEFAULT 'OWNED',
      owner_name VARCHAR(190) NULL,
      brand VARCHAR(120) NULL,
      model VARCHAR(120) NULL,
      model_year VARCHAR(20) NULL,
      color VARCHAR(80) NULL,
      chassis_no VARCHAR(120) NULL,
      engine_no VARCHAR(120) NULL,
      notes TEXT NULL,
      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,
      KEY idx_vehicles_company (company_id),
      KEY idx_vehicles_type (vehicle_type_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
  }

  fleet_add_column_if_missing($pdo, 'vehicles', 'plate_no_current', "VARCHAR(80) NULL AFTER plate_no");
  fleet_add_column_if_missing($pdo, 'vehicles', 'vehicle_type_id', "INT NULL AFTER plate_no_current");
  fleet_add_column_if_missing($pdo, 'vehicles', 'ownership_type', "ENUM('OWNED','EXTERNAL') NOT NULL DEFAULT 'OWNED' AFTER vehicle_type_id");
  fleet_add_column_if_missing($pdo, 'vehicles', 'owner_name', "VARCHAR(190) NULL AFTER ownership_type");
  fleet_add_column_if_missing($pdo, 'vehicles', 'is_active', "TINYINT(1) NOT NULL DEFAULT 1 AFTER notes");
  fleet_add_column_if_missing($pdo, 'vehicles', 'updated_at', "DATETIME NULL DEFAULT NULL AFTER created_at");

  try {
    if (fleet_col_exists($pdo, 'vehicles', 'plate_no') && fleet_col_exists($pdo, 'vehicles', 'plate_no_current')) {
      $pdo->exec("UPDATE vehicles SET plate_no_current = plate_no WHERE (plate_no_current IS NULL OR plate_no_current='') AND plate_no IS NOT NULL AND plate_no<>' '");
    }
  } catch (Throwable $e) {
  }
}

function fleet_vehicle_types(PDO $pdo, int $companyId): array {
  fleet_ensure_vehicle_types_schema($pdo);
  $st = $pdo->prepare("SELECT * FROM vehicle_types WHERE company_id=? AND is_active=1 ORDER BY category IS NULL, category, name");
  $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 name FROM vehicle_types WHERE id=? AND company_id=? LIMIT 1");
  $st->execute([$typeId, $companyId]);
  return (string)($st->fetchColumn() ?: '');
}

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");

  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'bank_txn_type')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN bank_txn_type VARCHAR(20) NULL AFTER payment_method");
  }
  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'bank_name')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN bank_name VARCHAR(120) NULL AFTER payment_method");
  }
  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'bank_account_no')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN bank_account_no VARCHAR(120) NULL AFTER bank_name");
  }
  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'due_date')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN due_date DATE NULL AFTER bank_account_no");
  }
}

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");

  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'bank_txn_type')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN bank_txn_type VARCHAR(20) NULL AFTER payment_method");
  }
  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'bank_name')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN bank_name VARCHAR(120) NULL AFTER payment_method");
  }
  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'bank_account_no')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN bank_account_no VARCHAR(120) NULL AFTER bank_name");
  }
  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'due_date')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN due_date DATE NULL AFTER bank_account_no");
  }
}


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");

  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'bank_txn_type')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN bank_txn_type VARCHAR(20) NULL AFTER payment_method");
  }
  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'bank_name')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN bank_name VARCHAR(120) NULL AFTER payment_method");
  }
  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'bank_account_no')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN bank_account_no VARCHAR(120) NULL AFTER bank_name");
  }
  if (!fleet_col_exists($pdo, 'vehicle_receipts', 'due_date')) {
    $pdo->exec("ALTER TABLE vehicle_receipts ADD COLUMN due_date DATE NULL AFTER bank_account_no");
  }
}

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;
  }
}
