<?php

/**
 * Migration 0049 — the Instinctor directory (network registry)
 *
 * One table holding every business in the Instinctor network as a curated
 * listing: instinctor.com tenants (source='tenant', owner_id = users.id) and
 * own-domain standalone clients (source='external', owner_id NULL) as the
 * same kind of row. The public /directory pages on instinctor.com render
 * straight from it. The table ships everywhere with the plugin but stays
 * empty and inert on client installs: the routes register under
 * isInstinctorApp only, and nothing else reads it.
 *
 * blurb is AUTHORED text, never scraped from the member site: a category
 * page must read as unique human-written copy, not as duplicated site
 * fragments (the thin-content defense). review_avg / review_count stay NULL
 * unless they hold real review data (no fake ratings, ever). city_slug is
 * computed on save ("Philadelphia" + "PA" -> "philadelphia-pa") and is what
 * category URLs key on.
 *
 * Idempotent: CREATE TABLE IF NOT EXISTS, same pattern as 0036/0037.
 *
 * @since 2.1.2501
 */

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

return [
    'description' => 'Create directory_listings (network directory registry)',

    'up' => [
        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_directory_listings (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            owner_id bigint(20) unsigned NULL DEFAULT NULL,
            source varchar(20) NOT NULL DEFAULT 'external',
            slug varchar(80) NOT NULL,
            business_name varchar(191) NOT NULL,
            trade varchar(40) NOT NULL,
            city varchar(80) NOT NULL DEFAULT '',
            region varchar(10) NOT NULL DEFAULT '',
            country varchar(2) NOT NULL DEFAULT 'US',
            city_slug varchar(100) NOT NULL DEFAULT '',
            blurb text NULL DEFAULT NULL,
            image_rel_path varchar(255) NOT NULL DEFAULT '',
            url varchar(500) NOT NULL DEFAULT '',
            review_avg decimal(2,1) NULL DEFAULT NULL,
            review_count int unsigned NULL DEFAULT NULL,
            status varchar(20) NOT NULL DEFAULT 'draft',
            featured tinyint(1) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY trade_city_status (trade, city_slug, status),
            KEY owner_id (owner_id)
        ) {charset}",
    ],
];
