<?php

/**
 * Migration 0013 — Unique (designer_id, slug) on instinctor_pages
 *
 * Required for the new routing scheme `instinctor.com/:designer_slug/:site_slug`
 * where each designer has their own slug namespace. Two pages can share a
 * slug as long as they belong to different designers. Admin pages
 * (designer_id NULL) also participate: their slugs must be unique within
 * the NULL namespace.
 *
 * The merged-slug-pool model we locked on 2026-04-24: gigs and client
 * sites live in the same table — a gig is just a page that happens to be
 * listed on the marketplace. No type distinction here; the listing is
 * controlled at the Platform `designer_gigs` row level.
 *
 * Pre-flight: any existing slug collisions within a designer get
 * de-duplicated by appending the row id. This is a data-safety step, not
 * a normal edit flow — after this migration, the UNIQUE KEY prevents new
 * collisions at the schema level.
 *
 * @since 2.1.237
 */

if (!defined('ABSPATH')) exit;
return [
    'description' => 'Unique slug per designer on instinctor_pages',

    'up' => [
        // De-dupe any existing colliding slugs: append -{id} to rows
        // whose (designer_id, slug) pair already has a prior occurrence.
        // MariaDB/MySQL-portable: use a self-join instead of a window fn.
        "UPDATE {prefix}instinctor_pages p
            JOIN (
                SELECT a.id
                FROM {prefix}instinctor_pages a
                JOIN {prefix}instinctor_pages b
                    ON COALESCE(a.designer_id, 0) = COALESCE(b.designer_id, 0)
                   AND a.slug = b.slug
                   AND a.id > b.id
            ) dup ON dup.id = p.id
            SET p.slug = CONCAT(p.slug, '-', p.id)",

        // Enforce uniqueness going forward.
        "ALTER TABLE {prefix}instinctor_pages
            ADD UNIQUE KEY designer_slug (designer_id, slug)",
    ],

    'down' => [
        "ALTER TABLE {prefix}instinctor_pages
            DROP KEY designer_slug",
    ],
];
