<?php

/**
 * Migration 0058 — customers email unique PER TENANT, not per install
 *
 * The original schema (0001) made instinctor_customers.email globally unique.
 * Customers became owner-scoped rows in 0043, but the index never followed:
 * when tenant B's client uses an email any other tenant already stored, the
 * INSERT dies with a duplicate-key error, insert_id() returns 0, and the
 * booking/order is written with customer_id = 0. A person who books at two
 * Instinctor-hosted businesses can only be a client of the first one
 * (observed on instinctor.com 2026-08-11; 0055 called this fix out as the
 * remaining half of the problem).
 *
 * UNIQUE (owner_id, email) scopes the constraint to the tenant. Two facts
 * about NULLs in MySQL unique indexes do the rest of the work:
 *   - email NULL (phone-only clients, 0055) stays exempt, as before;
 *   - owner_id NULL (vendor / single-tenant scope) makes the whole tuple
 *     exempt, so vendor-scope rows lose DB-level dupe enforcement. Every
 *     writer does an owner-scoped SELECT before inserting, so this only
 *     matters under a write race, where a spare row beats the old failure
 *     mode (a lost customer and a booking pointing at #0).
 *
 * The composite key is added BEFORE the old one is dropped: existing data
 * always satisfies the weaker constraint, and a death between the two
 * statements leaves both keys in place for the guarded re-run to finish.
 *
 * @since 2.1.3084
 */

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

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

return [
    'description' => 'customers: UNIQUE (owner_id, email) replaces global UNIQUE (email)',

    'up' => [],

    'php' => function () {
        $t = \Instinctor\Core\DB::prefix() . 'instinctor_customers';

        $hasIdx = function (string $idx) use ($t): 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",
                $t, $idx
            )) > 0;
        };

        if (!$hasIdx('owner_email')) {
            \Instinctor\Core\DB::query("ALTER TABLE {$t} ADD UNIQUE KEY owner_email (owner_id, email)");
        }
        if ($hasIdx('email')) {
            \Instinctor\Core\DB::query("ALTER TABLE {$t} DROP INDEX email");
        }
    },
];
