<?php
require __DIR__ . '/../app/bootstrap.php';
auth_require();
require __DIR__ . '/../app/pages/_common.php';
require_perm('manage_permissions'); // admin/supervisor only

function col_exists(string $table, string $col): bool {
  $row = db_one(
    "SELECT COUNT(*) c FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?",
    [$table, $col]
  );
  return intval($row['c'] ?? 0) > 0;
}

function table_exists(string $table): bool {
  $row = db_one(
    "SELECT COUNT(*) c FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME=?",
    [$table]
  );
  return intval($row['c'] ?? 0) > 0;
}

function index_exists(string $table, string $indexName): bool {
  $row = db_one(
    "SELECT COUNT(*) c FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME=? AND INDEX_NAME=?",
    [$table, $indexName]
  );
  return intval($row['c'] ?? 0) > 0;
}

function ensure_contractors_schema(): void {
  if (!table_exists('contractors')) {
    db_exec("CREATE TABLE IF NOT EXISTS contractors (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      code VARCHAR(50) NOT NULL,
      name VARCHAR(200) NOT NULL,
      phone VARCHAR(50) NULL,
      email VARCHAR(120) NULL,
      address VARCHAR(255) NULL,
      license_no VARCHAR(120) NULL,
      notes TEXT NULL,
      is_active TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME NOT NULL,
      updated_at DATETIME NULL,
      UNIQUE KEY uq_contractors_code (code),
      INDEX idx_contractors_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    return;
  }

  // add missing columns (for older v0.3.2 tables)
  if (!col_exists('contractors','code')) {
    db_exec("ALTER TABLE contractors ADD COLUMN code VARCHAR(50) NULL AFTER id");
  }
  if (!col_exists('contractors','address')) {
    db_exec("ALTER TABLE contractors ADD COLUMN address VARCHAR(255) NULL AFTER email");
  }
  if (!col_exists('contractors','license_no')) {
    db_exec("ALTER TABLE contractors ADD COLUMN license_no VARCHAR(120) NULL AFTER address");
  }
  if (!col_exists('contractors','notes')) {
    db_exec("ALTER TABLE contractors ADD COLUMN notes TEXT NULL");
  }
  if (!col_exists('contractors','is_active')) {
    db_exec("ALTER TABLE contractors ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1");
  }
  if (!col_exists('contractors','created_at')) {
    db_exec("ALTER TABLE contractors ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP");
  }
  if (!col_exists('contractors','updated_at')) {
    db_exec("ALTER TABLE contractors ADD COLUMN updated_at DATETIME NULL");
  }

  // backfill contractor codes if empty
  if (col_exists('contractors','code')) {
    db_exec("UPDATE contractors SET code = CONCAT('CTR', LPAD(id, 4, '0'))
             WHERE code IS NULL OR TRIM(code)=''");
    // make NOT NULL
    db_exec("ALTER TABLE contractors MODIFY code VARCHAR(50) NOT NULL");
    // unique index
    if (!index_exists('contractors','uq_contractors_code')) {
      db_exec("ALTER TABLE contractors ADD UNIQUE KEY uq_contractors_code (code)");
    }
  }
}

function ensure_contracts_schema(): void {
  if (!table_exists('contracts')) {
    db_exec("CREATE TABLE IF NOT EXISTS contracts (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      contractor_id INT UNSIGNED NOT NULL,
      contract_no VARCHAR(80) NOT NULL,
      title VARCHAR(200) NOT NULL,
      start_date DATE NULL,
      end_date DATE NULL,
      status VARCHAR(30) NOT NULL DEFAULT 'active',
      terms TEXT NULL,
      created_at DATETIME NOT NULL,
      updated_at DATETIME NULL,
      UNIQUE KEY uq_contracts_no (contract_no),
      INDEX idx_contracts_contractor (contractor_id),
      CONSTRAINT fk_contracts_contractor FOREIGN KEY (contractor_id) REFERENCES contractors(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    return;
  }

  if (!col_exists('contracts','contractor_id')) {
    db_exec("ALTER TABLE contracts ADD COLUMN contractor_id INT UNSIGNED NOT NULL AFTER id");
  }
  if (!col_exists('contracts','contract_no')) {
    db_exec("ALTER TABLE contracts ADD COLUMN contract_no VARCHAR(80) NULL AFTER contractor_id");
  }
  if (!col_exists('contracts','title')) {
    db_exec("ALTER TABLE contracts ADD COLUMN title VARCHAR(200) NULL AFTER contract_no");
  }
  if (!col_exists('contracts','start_date')) {
    db_exec("ALTER TABLE contracts ADD COLUMN start_date DATE NULL");
  }
  if (!col_exists('contracts','end_date')) {
    db_exec("ALTER TABLE contracts ADD COLUMN end_date DATE NULL");
  }
  if (!col_exists('contracts','status')) {
    db_exec("ALTER TABLE contracts ADD COLUMN status VARCHAR(30) NOT NULL DEFAULT 'active'");
  }
  if (!col_exists('contracts','terms')) {
    db_exec("ALTER TABLE contracts ADD COLUMN terms TEXT NULL");
  }
  if (!col_exists('contracts','created_at')) {
    db_exec("ALTER TABLE contracts ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP");
  }
  if (!col_exists('contracts','updated_at')) {
    db_exec("ALTER TABLE contracts ADD COLUMN updated_at DATETIME NULL");
  }

  // backfill missing contract_no/title
  if (col_exists('contracts','contract_no')) {
    db_exec("UPDATE contracts SET contract_no = CONCAT('CN', LPAD(id, 5, '0'))
             WHERE contract_no IS NULL OR TRIM(contract_no)=''");
    // make NOT NULL
    db_exec("ALTER TABLE contracts MODIFY contract_no VARCHAR(80) NOT NULL");
  }
  if (col_exists('contracts','title')) {
    db_exec("UPDATE contracts SET title = IFNULL(title, '') WHERE title IS NULL");
    db_exec("ALTER TABLE contracts MODIFY title VARCHAR(200) NOT NULL");
  }

  if (!index_exists('contracts','uq_contracts_no')) {
    db_exec("ALTER TABLE contracts ADD UNIQUE KEY uq_contracts_no (contract_no)");
  }
  if (!index_exists('contracts','idx_contracts_contractor')) {
    db_exec("ALTER TABLE contracts ADD INDEX idx_contracts_contractor (contractor_id)");
  }

  // FK (try-add; if already exists ignore by checking information_schema)
  $fk = db_one("SELECT COUNT(*) c FROM information_schema.REFERENTIAL_CONSTRAINTS
                WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME='contracts' AND CONSTRAINT_NAME='fk_contracts_contractor'");
  if (intval($fk['c'] ?? 0) === 0) {
    // ensure columns type matches
    db_exec("ALTER TABLE contracts
             ADD CONSTRAINT fk_contracts_contractor FOREIGN KEY (contractor_id) REFERENCES contractors(id) ON DELETE CASCADE");
  }
}

function ensure_contract_services_schema(): void {
  if (!table_exists('contract_services')) {
    db_exec("CREATE TABLE IF NOT EXISTS contract_services (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      contract_id INT UNSIGNED NOT NULL,
      service_name VARCHAR(200) NOT NULL,
      unit VARCHAR(50) NULL,
      unit_price DECIMAL(12,2) NULL,
      notes TEXT NULL,
      is_active TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME NOT NULL,
      updated_at DATETIME NULL,
      INDEX idx_cs_contract (contract_id),
      INDEX idx_cs_active (is_active),
      CONSTRAINT fk_cs_contract FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
  }

  // Add missing columns for legacy installs
  if (!col_exists('contract_services','is_active')) {
    db_exec("ALTER TABLE contract_services ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1");
  }
  if (!col_exists('contract_services','updated_at')) {
    db_exec("ALTER TABLE contract_services ADD COLUMN updated_at DATETIME NULL");
  }
  if (!index_exists('contract_services','idx_cs_active')) {
    db_exec("CREATE INDEX idx_cs_active ON contract_services (is_active)");
  }
}

function ensure_maintenance_schema(): void {
  if (!table_exists('maintenance_requests')) return;

  
// Add missing columns safely (no AFTER anchors to avoid legacy schema differences)
$addCols = [
  'tag' => "ALTER TABLE maintenance_requests ADD COLUMN tag VARCHAR(120) NULL",
  'category' => "ALTER TABLE maintenance_requests ADD COLUMN category VARCHAR(120) NULL",
    'completed_at' => "ALTER TABLE maintenance_requests ADD COLUMN completed_at DATETIME NULL",
  'contractor_id' => "ALTER TABLE maintenance_requests ADD COLUMN contractor_id INT UNSIGNED NULL",
  'contract_id' => "ALTER TABLE maintenance_requests ADD COLUMN contract_id INT UNSIGNED NULL",
  'contractor_assigned_at' => "ALTER TABLE maintenance_requests ADD COLUMN contractor_assigned_at DATETIME NULL",
  'contractor_assigned_by' => "ALTER TABLE maintenance_requests ADD COLUMN contractor_assigned_by INT UNSIGNED NULL",
];
foreach ($addCols as $col => $sql) {
  if (!col_exists('maintenance_requests',$col)) db_exec($sql);
}
// add FKs if missing
  $fk1 = db_one("SELECT COUNT(*) c FROM information_schema.REFERENTIAL_CONSTRAINTS
                 WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME='maintenance_requests' AND CONSTRAINT_NAME='fk_mr_contractor'");
  if (intval($fk1['c'] ?? 0)===0) {
    db_exec("ALTER TABLE maintenance_requests
             ADD CONSTRAINT fk_mr_contractor FOREIGN KEY (contractor_id) REFERENCES contractors(id) ON DELETE SET NULL");
  }

  $fk2 = db_one("SELECT COUNT(*) c FROM information_schema.REFERENTIAL_CONSTRAINTS
                 WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME='maintenance_requests' AND CONSTRAINT_NAME='fk_mr_contract'");
  if (intval($fk2['c'] ?? 0)===0) {
    db_exec("ALTER TABLE maintenance_requests
             ADD CONSTRAINT fk_mr_contract FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE SET NULL");
  }

  $fk3 = db_one("SELECT COUNT(*) c FROM information_schema.REFERENTIAL_CONSTRAINTS
                 WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME='maintenance_requests' AND CONSTRAINT_NAME='fk_mr_contractor_by'");
  if (intval($fk3['c'] ?? 0)===0) {
    db_exec("ALTER TABLE maintenance_requests
             ADD CONSTRAINT fk_mr_contractor_by FOREIGN KEY (contractor_assigned_by) REFERENCES users(id) ON DELETE SET NULL");
  }
}

$title = 'ترقية v0.3.2';
$view = __DIR__ . '/../app/pages/_upgrade_view.php';


function ensure_geo_schema() {
  // Farms: إضافة إحداثيات (Lat/Lng) لتجهيز صفحة الخريطة
  if (table_exists('farms')) {
    if (!col_exists('farms','lat')) {
      db_exec("ALTER TABLE farms ADD COLUMN lat DECIMAL(10,7) NULL AFTER area_m2");
    }
    if (!col_exists('farms','lng')) {
      db_exec("ALTER TABLE farms ADD COLUMN lng DECIMAL(10,7) NULL AFTER lat");
    }
  }

  // Assets: بعض النسخ القديمة قد لا تحتوي الأعمدة
  if (table_exists('assets')) {
    if (!col_exists('assets','lat')) {
      db_exec("ALTER TABLE assets ADD COLUMN lat DECIMAL(10,7) NULL");
    }
    if (!col_exists('assets','lng')) {
      db_exec("ALTER TABLE assets ADD COLUMN lng DECIMAL(10,7) NULL");
    }
  }
}


if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  csrf_check();

  ensure_contractors_schema();
  ensure_contracts_schema();
  ensure_contract_services_schema();
  ensure_maintenance_schema();
    ensure_geo_schema();

  flash_set('ok','تمت ترقية قاعدة البيانات إلى v0.3.2 بنجاح.');
  redirect(url('/index.php?page=dashboard'));
}

require __DIR__ . '/../app/views/layout.php';
