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

function ensure_company_profile_columns(PDO $pdo): void {
  $columns = [
    'tax_file_no' => "ALTER TABLE companies ADD COLUMN tax_file_no VARCHAR(120) NULL AFTER currency",
    'commercial_register_no' => "ALTER TABLE companies ADD COLUMN commercial_register_no VARCHAR(120) NULL AFTER tax_file_no",
    'phone1' => "ALTER TABLE companies ADD COLUMN phone1 VARCHAR(60) NULL AFTER commercial_register_no",
    'phone2' => "ALTER TABLE companies ADD COLUMN phone2 VARCHAR(60) NULL AFTER phone1",
    'logo_path' => "ALTER TABLE companies ADD COLUMN logo_path VARCHAR(255) NULL AFTER phone2",
  ];

  foreach ($columns as $name => $sql) {
    $st = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='companies' AND COLUMN_NAME=?");
    $st->execute([$name]);
    if (!(int)$st->fetchColumn()) {
      $pdo->exec($sql);
    }
  }
}



function ensure_user_profile_columns(PDO $pdo): void {
  $columns = [
    'phone' => "ALTER TABLE users ADD COLUMN phone VARCHAR(60) NULL AFTER email",
    'avatar_path' => "ALTER TABLE users ADD COLUMN avatar_path VARCHAR(255) NULL AFTER phone",
  ];

  foreach ($columns as $name => $sql) {
    $st = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME=?");
    $st->execute([$name]);
    if (!(int)$st->fetchColumn()) {
      $pdo->exec($sql);
    }
  }
}

function db_list_tables(PDO $pdo): array {
  $tables = [];
  foreach ($pdo->query('SHOW TABLES') as $row) {
    $tables[] = array_values($row)[0];
  }
  return $tables;
}

function db_export_sql(PDO $pdo): string {
  $tables = db_list_tables($pdo);
  $out = "-- ERP_Lite SQL Backup\n";
  $out .= "-- Generated at: " . date('Y-m-d H:i:s') . "\n";
  $out .= "SET NAMES utf8mb4;\n";
  $out .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

  foreach ($tables as $table) {
    $row = $pdo->query("SHOW CREATE TABLE `{$table}`")->fetch(PDO::FETCH_NUM);
    $create = $row[1] ?? '';
    $out .= "-- ----------------------------\n";
    $out .= "-- Table structure for `{$table}`\n";
    $out .= "-- ----------------------------\n";
    $out .= "DROP TABLE IF EXISTS `{$table}`;\n";
    $out .= $create . ";\n\n";

    $rows = $pdo->query("SELECT * FROM `{$table}`", PDO::FETCH_ASSOC)->fetchAll();
    if ($rows) {
      $out .= "-- Data for `{$table}`\n";
      foreach ($rows as $r) {
        $cols = [];
        $vals = [];
        foreach ($r as $col => $val) {
          $cols[] = "`{$col}`";
          $vals[] = $val === null ? 'NULL' : $pdo->quote((string)$val);
        }
        $out .= "INSERT INTO `{$table}` (" . implode(',', $cols) . ") VALUES (" . implode(',', $vals) . ");\n";
      }
      $out .= "\n";
    }
  }

  $out .= "SET FOREIGN_KEY_CHECKS=1;\n";
  return $out;
}

function db_execute_sql_text(PDO $pdo, string $sql): void {
  $sql = preg_replace('/^\xEF\xBB\xBF/', '', $sql ?? '');
  $pdo->exec("SET FOREIGN_KEY_CHECKS=0");
  try {
    $pdo->exec($sql);
  } finally {
    $pdo->exec("SET FOREIGN_KEY_CHECKS=1");
  }
}

function db_wipe_except_current(PDO $pdo, int $keepCompanyId, int $keepUserId): void {
  $tables = db_list_tables($pdo);
  $protected = ['companies', 'users'];

  $pdo->exec('SET FOREIGN_KEY_CHECKS=0');
  try {
    foreach ($tables as $table) {
      if (in_array($table, $protected, true)) continue;

      $st = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME=? AND COLUMN_NAME='company_id'");
      $st->execute([$table]);
      $hasCompanyId = (int)$st->fetchColumn() > 0;

      if ($hasCompanyId) {
        $pdo->prepare("DELETE FROM `{$table}` WHERE company_id=?")->execute([$keepCompanyId]);
      }
    }

    $pdo->prepare("DELETE FROM users WHERE company_id=? AND id<>?")->execute([$keepCompanyId, $keepUserId]);
    $pdo->prepare("DELETE FROM users WHERE company_id<>?")->execute([$keepCompanyId]);
    $pdo->prepare("DELETE FROM companies WHERE id<>?")->execute([$keepCompanyId]);
  } finally {
    $pdo->exec('SET FOREIGN_KEY_CHECKS=1');
  }
}
