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

function account_type_roots(): array {
  return [
    'ASSET' => 1000,
    'LIABILITY' => 2000,
    'EQUITY' => 3000,
    'REVENUE' => 4000,
    'EXPENSE' => 5000,
  ];
}

function account_normal_side(string $type): string {
  return in_array($type, ['ASSET','EXPENSE'], true) ? 'DEBIT' : 'CREDIT';
}

function next_account_code(PDO $pdo, int $companyId, ?int $parentId, string $type, ?int $excludeId = null): string {
  $params = [$companyId];
  $excludeSql = '';
  if ($excludeId) {
    $excludeSql = ' AND id <> ?';
  }

  if ($parentId) {
    $st = $pdo->prepare("SELECT code, type FROM accounts WHERE company_id=? AND id=? LIMIT 1");
    $st->execute([$companyId, $parentId]);
    $parent = $st->fetch(PDO::FETCH_ASSOC);
    if (!$parent) {
      throw new RuntimeException('الحساب الأب غير موجود');
    }
    $type = $parent['type'];
    $sql = "SELECT MAX(CAST(code AS UNSIGNED)) FROM accounts WHERE company_id=? AND parent_id=?" . $excludeSql;
    $params = [$companyId, $parentId];
    if ($excludeId) $params[] = $excludeId;
    $max = (int)$pdo->prepare($sql)->execute($params);
  }

  if ($parentId) {
    $sql = "SELECT MAX(CAST(code AS UNSIGNED)) AS max_code FROM accounts WHERE company_id=? AND parent_id=?" . ($excludeId ? " AND id<>?" : "");
    $params = [$companyId, $parentId];
    if ($excludeId) $params[] = $excludeId;
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $max = (int)$st->fetchColumn();

    $parentCodeSt = $pdo->prepare("SELECT CAST(code AS UNSIGNED) FROM accounts WHERE company_id=? AND id=? LIMIT 1");
    $parentCodeSt->execute([$companyId, $parentId]);
    $parentCode = (int)$parentCodeSt->fetchColumn();
    $candidate = $max > 0 ? $max + 10 : $parentCode + 10;
  } else {
    $roots = account_type_roots();
    $base = $roots[$type] ?? 9000;
    $sql = "SELECT MAX(CAST(code AS UNSIGNED)) AS max_code FROM accounts WHERE company_id=? AND parent_id IS NULL AND type=?" . ($excludeId ? " AND id<>?" : "");
    $params = [$companyId, $type];
    if ($excludeId) $params[] = $excludeId;
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $max = (int)$st->fetchColumn();
    $candidate = $max > 0 ? $max + 1000 : $base;
  }

  while (true) {
    $sql = "SELECT COUNT(*) FROM accounts WHERE company_id=? AND code=?" . ($excludeId ? " AND id<>?" : "");
    $params = [$companyId, (string)$candidate];
    if ($excludeId) $params[] = $excludeId;
    $st = $pdo->prepare($sql);
    $st->execute($params);
    if ((int)$st->fetchColumn() === 0) {
      return (string)$candidate;
    }
    $candidate += $parentId ? 10 : 1000;
  }
}


function account_reference_sources(): array {
  return [
    ['journal_entry_lines', 'account_id', null],
    ['journal_lines',       'account_id', null],
    ['journal_details',     'account_id', null],
    ['transactions',        'account_id', 'company_id'],
    ['receipts',            'account_id', 'company_id'],
    ['payments',            'account_id', 'company_id'],
    ['voucher_lines',       'account_id', null],
    ['vouchers',            'account_id', 'company_id'],
    ['advances',            'account_id', 'company_id'],
    ['advance_lines',       'account_id', null],
    ['expenses',            'account_id', 'company_id'],
    ['revenues',            'account_id', 'company_id'],
    ['invoice_items',       'account_id', null],
    ['invoices',            'account_id', 'company_id'],
    ['ledger_entries',      'account_id', null],
    ['trial_balance',       'account_id', 'company_id'],
    ['account_balances',    'account_id', 'company_id'],
  ];
}

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

function column_exists(PDO $pdo, string $table, string $column): bool {
  $st = $pdo->prepare("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?");
  $st->execute([$table, $column]);
  return (int)$st->fetchColumn() > 0;
}

function account_has_references(PDO $pdo, int $companyId, int $accountId): bool {
  foreach (account_reference_sources() as [$table, $column, $companyColumn]) {
    if (!table_exists($pdo, $table) || !column_exists($pdo, $table, $column)) {
      continue;
    }
    $sql = "SELECT COUNT(*) FROM `{$table}` WHERE `{$column}` = ?";
    $params = [$accountId];
    if ($companyColumn && column_exists($pdo, $table, $companyColumn)) {
      $sql .= " AND `{$companyColumn}` = ?";
      $params[] = $companyId;
    }
    $st = $pdo->prepare($sql);
    $st->execute($params);
    if ((int)$st->fetchColumn() > 0) return true;
  }
  return false;
}
