<?php

/**
 * Migration 0043 — owner_id on the business tables (multi-tenancy Phase 2)
 *
 * Adds a nullable owner_id to instinctor_customers, instinctor_services and
 * instinctor_bookings so a row can belong to a specific tenant (owner). NULL
 * means the vendor / single-tenant scope: on a single-tenant WordPress install
 * every row stays NULL, and Tenant::scopeSql() resolves to "owner_id IS NULL",
 * so scoped queries return exactly what an unscoped query returned before. The
 * column is therefore inert on WP and only comes alive on instinctor.com where
 * real tenants exist.
 *
 * owner_id references instinctor_users.id (role 'owner'), matching pages.owner_id.
 *
 * Guarded via the 'php' callback: every ADD is checked against information_schema
 * first, so it is safe on a fresh install, a re-run, or a partially-applied
 * state (MySQL has no ADD COLUMN IF NOT EXISTS; MariaDB does — this works on both).
 *
 * @since 2.1.2227
 */

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 customers, services, bookings (tenant scoping)',

    'up' => [],

    'php' => function () {
        $tables = ['instinctor_customers', 'instinctor_services', 'instinctor_bookings'];

        $hasCol = function (string $table, string $col): bool {
            return (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",
                $table, $col
            )) > 0;
        };
        $hasIdx = function (string $table, string $idx): bool {
            return (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",
                $table, $idx
            )) > 0;
        };

        foreach ($tables as $short) {
            $t = \Instinctor\Core\DB::prefix() . $short;
            if (!$hasCol($t, 'owner_id')) {
                \Instinctor\Core\DB::query("ALTER TABLE {$t} ADD COLUMN owner_id INT UNSIGNED NULL DEFAULT NULL");
            }
            if (!$hasIdx($t, 'owner_id')) {
                \Instinctor\Core\DB::query("ALTER TABLE {$t} ADD KEY owner_id (owner_id)");
            }
        }
    },
];
