<?php
// Central, idempotent schema migration for the agents module.

require_once __DIR__ . '/db_schema_helpers.php';

if (!function_exists('ua_agent_schema_table_exists')) {
  function ua_agent_schema_table_exists(mysqli $conn, string $table): bool {
    $table = $conn->real_escape_string($table);
    $r = @$conn->query("SHOW TABLES LIKE '{$table}'");
    return (bool)($r && $r->num_rows > 0);
  }
}

if (!function_exists('ua_agent_schema_col_exists')) {
  function ua_agent_schema_col_exists(mysqli $conn, string $table, string $column): bool {
    if (!ua_agent_schema_table_exists($conn, $table)) return false;
    $table = str_replace('`', '', $table);
    $column = $conn->real_escape_string($column);
    $r = @$conn->query("SHOW COLUMNS FROM `{$table}` LIKE '{$column}'");
    return (bool)($r && $r->num_rows > 0);
  }
}

if (!function_exists('ua_agent_schema_index_exists')) {
  function ua_agent_schema_index_exists(mysqli $conn, string $table, string $index): bool {
    if (!ua_agent_schema_table_exists($conn, $table)) return false;
    $table = str_replace('`', '', $table);
    $index = $conn->real_escape_string($index);
    $r = @$conn->query("SHOW INDEX FROM `{$table}` WHERE Key_name='{$index}'");
    return (bool)($r && $r->num_rows > 0);
  }
}

if (!function_exists('ua_agent_schema_add_column')) {
  function ua_agent_schema_add_column(mysqli $conn, string $table, string $column, string $definition): void {
    if (ua_agent_schema_col_exists($conn, $table, $column)) return;
    $table = str_replace('`', '', $table);
    $column = str_replace('`', '', $column);
    @$conn->query("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
  }
}

if (!function_exists('ua_agent_schema_add_index')) {
  function ua_agent_schema_add_index(mysqli $conn, string $table, string $index, string $definition): void {
    if (ua_agent_schema_index_exists($conn, $table, $index)) return;
    $table = str_replace('`', '', $table);
    $index = str_replace('`', '', $index);
    @$conn->query("ALTER TABLE `{$table}` ADD {$definition} `{$index}`");
  }
}

if (!function_exists('ua_agent_schema_is_current')) {
  function ua_agent_schema_is_current(mysqli $conn): bool {
    foreach (['agents','agent_ledger_entries','agent_activity_log','agent_sync_events','agent_sync_nonces'] as $table) {
      if (!ua_agent_schema_table_exists($conn, $table)) return false;
    }
    $required = [
      ['agents','balance_cached'],
      ['agents','linked_instance_id'],
      ['payments','idempotency_key'],
      ['subscribers','created_by_type'],
      ['subscribers','agent_status_protected'],
      ['invoices','paid_by_type'],
    ];
    foreach ($required as [$table, $column]) {
      if (!ua_agent_schema_col_exists($conn, $table, $column)) return false;
    }
    return ua_agent_schema_index_exists($conn, 'agent_ledger_entries', 'uq_agent_ledger_idempotency')
      && ua_agent_schema_index_exists($conn, 'agent_sync_events', 'uq_agent_sync_idempotency');
  }
}

if (!function_exists('ua_agent_schema_migrate')) {
  function ua_agent_schema_migrate(mysqli $conn): array {
    static $done = false;
    if ($done || ua_agent_schema_is_current($conn)) {
      $done = true;
      return ['ok'=>true,'errors'=>[],'changed'=>false];
    }
    $errors = [];
    $oldMode = null;
    if (function_exists('mysqli_report')) {
      $oldMode = MYSQLI_REPORT_OFF;
      @mysqli_report(MYSQLI_REPORT_OFF);
    }
    @$conn->set_charset('utf8mb4');

    $queries = [
      "CREATE TABLE IF NOT EXISTS `agents` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `code` VARCHAR(40) NULL,
        `name` VARCHAR(255) NOT NULL,
        `full_name` VARCHAR(255) NULL,
        `national_id` VARCHAR(80) NULL,
        `phone` VARCHAR(50) NULL,
        `phone_alt` VARCHAR(50) NULL,
        `email` VARCHAR(160) NULL,
        `address` VARCHAR(255) NULL,
        `area` VARCHAR(160) NULL,
        `notes` TEXT NULL,
        `username` VARCHAR(120) NULL,
        `password_hash` VARCHAR(255) NULL,
        `pin_hash` VARCHAR(255) NULL,
        `is_active` TINYINT(1) NOT NULL DEFAULT 1,
        `portal_enabled` TINYINT(1) NOT NULL DEFAULT 1,
        `pos_enabled` TINYINT(1) NOT NULL DEFAULT 1,
        `linked_instance_id` VARCHAR(80) NULL,
        `commission_type` VARCHAR(20) NOT NULL DEFAULT 'none',
        `commission_value` DECIMAL(14,2) NOT NULL DEFAULT 0,
        `credit_limit` DECIMAL(14,2) NULL,
        `balance` DECIMAL(14,2) NOT NULL DEFAULT 0,
        `balance_cached` DECIMAL(14,2) NOT NULL DEFAULT 0,
        `last_activity_at` DATETIME NULL,
        `created_by` INT NULL,
        `updated_by` INT NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME NULL,
        `archived_at` DATETIME NULL,
        UNIQUE KEY `uq_agents_code` (`code`),
        UNIQUE KEY `uq_agents_username` (`username`),
        KEY `idx_agents_active` (`is_active`,`archived_at`),
        KEY `idx_agents_instance` (`linked_instance_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

      "CREATE TABLE IF NOT EXISTS `agent_ledger_entries` (
        `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
        `entry_uuid` CHAR(36) NOT NULL,
        `agent_id` INT NOT NULL,
        `direction` VARCHAR(20) NOT NULL,
        `entry_type` VARCHAR(50) NOT NULL,
        `amount` DECIMAL(14,2) NOT NULL DEFAULT 0,
        `currency` VARCHAR(12) NOT NULL DEFAULT 'SYP',
        `balance_before` DECIMAL(14,2) NOT NULL DEFAULT 0,
        `balance_after` DECIMAL(14,2) NOT NULL DEFAULT 0,
        `subscriber_id` INT NULL,
        `invoice_id` INT NULL,
        `payment_id` INT NULL,
        `sale_id` INT NULL,
        `voucher_id` INT NULL,
        `source_type` VARCHAR(50) NULL,
        `source_id` VARCHAR(120) NULL,
        `source_instance_id` VARCHAR(80) NULL,
        `idempotency_key` VARCHAR(191) NOT NULL,
        `reverses_entry_id` BIGINT NULL,
        `status` VARCHAR(20) NOT NULL DEFAULT 'POSTED',
        `description` VARCHAR(500) NULL,
        `metadata_json` LONGTEXT NULL,
        `occurred_at` DATETIME NOT NULL,
        `posted_at` DATETIME NULL,
        `created_by_type` VARCHAR(30) NOT NULL DEFAULT 'admin',
        `created_by_id` INT NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY `uq_agent_ledger_uuid` (`entry_uuid`),
        UNIQUE KEY `uq_agent_ledger_idempotency` (`idempotency_key`),
        KEY `idx_agent_ledger_agent_time` (`agent_id`,`occurred_at`),
        KEY `idx_agent_ledger_invoice` (`invoice_id`),
        KEY `idx_agent_ledger_payment` (`payment_id`),
        KEY `idx_agent_ledger_subscriber` (`subscriber_id`),
        KEY `idx_agent_ledger_reverse` (`reverses_entry_id`),
        KEY `idx_agent_ledger_status` (`status`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

      "CREATE TABLE IF NOT EXISTS `agent_activity_log` (
        `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
        `event_uuid` CHAR(36) NOT NULL,
        `agent_id` INT NULL,
        `event_type` VARCHAR(60) NOT NULL,
        `direction` VARCHAR(20) NOT NULL DEFAULT 'LOCAL',
        `source_instance_id` VARCHAR(80) NULL,
        `entity_type` VARCHAR(50) NULL,
        `entity_id` VARCHAR(120) NULL,
        `request_id` VARCHAR(120) NULL,
        `status` VARCHAR(20) NOT NULL DEFAULT 'SUCCESS',
        `summary` VARCHAR(500) NULL,
        `payload_json` LONGTEXT NULL,
        `error_message` TEXT NULL,
        `actor_type` VARCHAR(30) NOT NULL DEFAULT 'admin',
        `actor_id` INT NULL,
        `occurred_at` DATETIME NOT NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY `uq_agent_activity_uuid` (`event_uuid`),
        KEY `idx_agent_activity_agent_time` (`agent_id`,`occurred_at`),
        KEY `idx_agent_activity_direction` (`direction`,`status`),
        KEY `idx_agent_activity_entity` (`entity_type`,`entity_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

      "CREATE TABLE IF NOT EXISTS `agent_sync_events` (
        `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
        `event_uuid` CHAR(36) NOT NULL,
        `agent_id` INT NULL,
        `instance_id` VARCHAR(80) NOT NULL,
        `direction` VARCHAR(20) NOT NULL,
        `event_type` VARCHAR(60) NOT NULL,
        `entity_type` VARCHAR(50) NULL,
        `entity_id` VARCHAR(120) NULL,
        `idempotency_key` VARCHAR(191) NOT NULL,
        `payload_json` LONGTEXT NULL,
        `result_json` LONGTEXT NULL,
        `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
        `attempts` INT NOT NULL DEFAULT 0,
        `last_error` TEXT NULL,
        `available_at` DATETIME NOT NULL,
        `processed_at` DATETIME NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME NULL,
        UNIQUE KEY `uq_agent_sync_uuid` (`event_uuid`),
        UNIQUE KEY `uq_agent_sync_idempotency` (`idempotency_key`),
        KEY `idx_agent_sync_queue` (`direction`,`status`,`available_at`),
        KEY `idx_agent_sync_agent` (`agent_id`,`created_at`),
        KEY `idx_agent_sync_instance` (`instance_id`,`created_at`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

      "CREATE TABLE IF NOT EXISTS `agent_sync_nonces` (
        `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
        `instance_id` VARCHAR(80) NOT NULL,
        `nonce` VARCHAR(120) NOT NULL,
        `request_timestamp` BIGINT NOT NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY `uq_agent_sync_nonce` (`instance_id`,`nonce`),
        KEY `idx_agent_sync_nonce_created` (`created_at`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
    ];

    foreach ($queries as $sql) {
      if (!@$conn->query($sql)) $errors[] = $conn->error;
    }

    $agentColumns = [
      'code' => "VARCHAR(40) NULL",
      'full_name' => "VARCHAR(255) NULL",
      'national_id' => "VARCHAR(80) NULL",
      'phone_alt' => "VARCHAR(50) NULL",
      'email' => "VARCHAR(160) NULL",
      'address' => "VARCHAR(255) NULL",
      'area' => "VARCHAR(160) NULL",
      'notes' => "TEXT NULL",
      'portal_enabled' => "TINYINT(1) NOT NULL DEFAULT 1",
      'linked_instance_id' => "VARCHAR(80) NULL",
      'commission_type' => "VARCHAR(20) NOT NULL DEFAULT 'none'",
      'commission_value' => "DECIMAL(14,2) NOT NULL DEFAULT 0",
      'credit_limit' => "DECIMAL(14,2) NULL",
      'balance_cached' => "DECIMAL(14,2) NOT NULL DEFAULT 0",
      'last_activity_at' => "DATETIME NULL",
      'created_by' => "INT NULL",
      'updated_by' => "INT NULL",
      'updated_at' => "DATETIME NULL",
      'archived_at' => "DATETIME NULL",
    ];
    foreach ($agentColumns as $column => $definition) {
      ua_agent_schema_add_column($conn, 'agents', $column, $definition);
    }
    if (!ua_agent_schema_index_exists($conn, 'agents', 'uq_agents_code')) {
      @$conn->query("ALTER TABLE `agents` ADD UNIQUE KEY `uq_agents_code` (`code`)");
    }
    if (!ua_agent_schema_index_exists($conn, 'agents', 'idx_agents_active')) {
      @$conn->query("ALTER TABLE `agents` ADD KEY `idx_agents_active` (`is_active`,`archived_at`)");
    }
    if (!ua_agent_schema_index_exists($conn, 'agents', 'idx_agents_instance')) {
      @$conn->query("ALTER TABLE `agents` ADD KEY `idx_agents_instance` (`linked_instance_id`)");
    }

    if (ua_agent_schema_table_exists($conn, 'payments')) {
      ua_agent_schema_add_column($conn, 'payments', 'agent_id', "INT NULL");
      ua_agent_schema_add_column($conn, 'payments', 'source_instance_id', "VARCHAR(80) NULL");
      ua_agent_schema_add_column($conn, 'payments', 'idempotency_key', "VARCHAR(191) NULL");
      ua_agent_schema_add_column($conn, 'payments', 'reversed_payment_id', "INT NULL");
      ua_agent_schema_add_column($conn, 'payments', 'payment_status', "VARCHAR(20) NOT NULL DEFAULT 'POSTED'");
      if (!ua_agent_schema_index_exists($conn, 'payments', 'idx_payments_agent_id')) {
        @$conn->query("ALTER TABLE `payments` ADD KEY `idx_payments_agent_id` (`agent_id`)");
      }
      if (!ua_agent_schema_index_exists($conn, 'payments', 'uq_payments_idempotency')) {
        @$conn->query("ALTER TABLE `payments` ADD UNIQUE KEY `uq_payments_idempotency` (`idempotency_key`)");
      }
    }

    if (ua_agent_schema_table_exists($conn, 'subscribers')) {
      ua_agent_schema_add_column($conn, 'subscribers', 'created_by_type', "VARCHAR(30) NULL");
      ua_agent_schema_add_column($conn, 'subscribers', 'created_by_id', "INT NULL");
      ua_agent_schema_add_column($conn, 'subscribers', 'source_instance_id', "VARCHAR(80) NULL");
      ua_agent_schema_add_column($conn, 'subscribers', 'agent_status_protected', "TINYINT(1) NOT NULL DEFAULT 0");
      ua_agent_schema_add_column($conn, 'subscribers', 'agent_status_password_hash', "VARCHAR(255) NULL");
      if (!ua_agent_schema_index_exists($conn, 'subscribers', 'idx_subscribers_agent_source')) {
        @$conn->query("ALTER TABLE `subscribers` ADD KEY `idx_subscribers_agent_source` (`created_by_type`,`created_by_id`)");
      }
    }

    if (ua_agent_schema_table_exists($conn, 'invoices')) {
      ua_agent_schema_add_column($conn, 'invoices', 'paid_by_type', "VARCHAR(20) NULL");
      ua_agent_schema_add_column($conn, 'invoices', 'paid_by_id', "INT NULL");
      if (!ua_agent_schema_index_exists($conn, 'invoices', 'idx_invoices_paid_actor')) {
        @$conn->query("ALTER TABLE `invoices` ADD KEY `idx_invoices_paid_actor` (`paid_by_type`,`paid_by_id`)");
      }
    }

    // Preserve legacy balances as the opening cached value until phase 10 reconciliation.
    @$conn->query("UPDATE agents SET full_name=COALESCE(NULLIF(full_name,''),name)");
    @$conn->query("UPDATE agents SET balance_cached=balance WHERE balance_cached=0 AND balance<>0");

    $done = empty($errors);
    return ['ok' => empty($errors), 'errors' => array_values(array_unique(array_filter($errors))), 'changed'=>true];
  }
}
