<?php

/**
 * Migration 0061 — registered domains (NameSilo reseller ledger)
 *
 * One row per domain a client buys through the AI Domain Finder. It is the
 * fulfillment ledger AND the idempotency lock. The lock is the UNIQUE KEY on
 * purchase_ref (a per-payment token): DomainService::fulfill() claims a
 * registration exactly once regardless of which buy path drove it, so the same
 * paid purchase arriving twice (Stripe webhook retry, /checkout/confirm racing,
 * an admin re-marking paid, or the platform re-calling the fulfill webhook) can
 * never double-spend at NameSilo. purchase_ref is:
 *   - store path    : "order:{orderId}:{itemId}"
 *   - platform path : "stripe:{sessionId}:{domain}"
 * order_id is NULL on the platform path, so the legacy UNIQUE(order_id, domain)
 * cannot lock it (MySQL treats NULLs as distinct); it is kept only as a
 * secondary guard for the store path.
 *
 * status lifecycle: pending -> registered | failed | unavailable.
 *   - pending      : row claimed, registration in flight (or a crashed attempt)
 *   - registered   : NameSilo confirmed (provider_ref holds its reference)
 *   - unavailable  : re-check at payment time found it taken (never charged for
 *                    a domain we could register -> refund/alt is a business step)
 *   - failed       : NameSilo returned an error (needs manual follow-up)
 *
 * owner_id = tenant (customers.owner_id / resolved owner). NULL for vendor.
 * customer_id = buyer (orders.customer_id). wholesale/retail record the margin.
 *
 * Ships everywhere but stays empty and inert until instinctor_namesilo.api_key
 * is configured (DomainService::isEnabled gates every write).
 *
 * Idempotent: CREATE TABLE IF NOT EXISTS.
 *
 * @since 2.1.3094
 */

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

return [
    'description' => 'Create instinctor_domains (NameSilo reseller fulfillment ledger)',

    'up' => [
        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_domains (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            owner_id bigint(20) unsigned NULL DEFAULT NULL,
            customer_id bigint(20) unsigned NULL DEFAULT NULL,
            order_id bigint(20) unsigned NULL DEFAULT NULL,
            order_item_id bigint(20) unsigned NULL DEFAULT NULL,
            page_id bigint(20) unsigned NULL DEFAULT NULL,
            domain varchar(255) NOT NULL,
            years tinyint unsigned NOT NULL DEFAULT 1,
            wholesale decimal(10,2) NULL DEFAULT NULL,
            retail decimal(10,2) NULL DEFAULT NULL,
            provider varchar(20) NOT NULL DEFAULT 'namesilo',
            provider_ref varchar(191) NULL DEFAULT NULL,
            purchase_ref varchar(191) NULL DEFAULT NULL,
            source varchar(20) NOT NULL DEFAULT 'order',
            status varchar(20) NOT NULL DEFAULT 'pending',
            detail text NULL DEFAULT NULL,
            registered_at datetime NULL DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY purchase_ref (purchase_ref),
            UNIQUE KEY order_domain (order_id, domain),
            KEY owner_id (owner_id),
            KEY customer_id (customer_id),
            KEY status (status)
        ) {charset}",
    ],

    'down' => [
        "DROP TABLE IF EXISTS {prefix}instinctor_domains",
    ],
];
