<?php

/**
 * Migration 0047 — owner_id on estimates, jobs and service_staff (multi-tenancy)
 *
 * The same hole migration 0046 closed for staff, in the three tables the Installer
 * vertical is about to fill. None of them had an owner column and EstimateService does
 * no scoping at all (its only Tenant call is a MediaUsage tally), so on instinctor.com
 * two trades would have seen each other's quotes, jobs and crew assignments. An estimate
 * carries a price and a customer's address: this is the worst of the three to get wrong.
 *
 * Caught while wiring quote -> invoice into the Installer template, and again the timing
 * is lucky: all three tables are EMPTY (0 rows), so the column costs nothing today. After
 * the first trade signs up it means guessing which quote belonged to whom.
 *
 * NULL = vendor / single-tenant, so every standalone client is untouched (scopeSql emits
 * "owner_id IS NULL"). Matches pages/bookings (0043), chat (0045) and staff (0046).
 *
 * Guarded via the 'php' callback: each ADD is checked against information_schema first,
 * so this is safe on a fresh install, a re-run, or a partially-applied state.
 *
 * @since 2.1.2286
 */

if (!defined('ABSPATH')) exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- schema migration on the plugin's own tables

return [
    'description' => 'Add nullable owner_id to estimates, jobs, service_staff (tenant scoping)',

    'up' => [],

    'php' => function () {
        foreach (['instinctor_estimates', 'instinctor_jobs', 'instinctor_service_staff'] as $name) {
            $t = \Instinctor\Core\DB::prefix() . $name;

            $exists = (int) \Instinctor\Core\DB::get_var(\Instinctor\Core\DB::prepare(
                "SELECT COUNT(*) FROM information_schema.TABLES
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
                $t
            )) > 0;
            if (!$exists) continue;   // table not created on this install yet

            $hasCol = (int) \Instinctor\Core\DB::get_var(\Instinctor\Core\DB::prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                $t, 'owner_id'
            )) > 0;
            if (!$hasCol) {
                \Instinctor\Core\DB::query("ALTER TABLE {$t} ADD COLUMN owner_id INT UNSIGNED NULL DEFAULT NULL");
            }

            $hasIdx = (int) \Instinctor\Core\DB::get_var(\Instinctor\Core\DB::prepare(
                "SELECT COUNT(*) FROM information_schema.STATISTICS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s",
                $t, 'owner_id'
            )) > 0;
            if (!$hasIdx) {
                \Instinctor\Core\DB::query("ALTER TABLE {$t} ADD KEY owner_id (owner_id)");
            }
        }
    },
];
