<?php

/**
 * Migration 0050 — directory contact messages (member comms, phase 1)
 *
 * "Contact this business" on /directory/b/:slug stores every message here
 * and relays it to the member's PRIVATE notify contacts: new notify_email /
 * notify_phone columns on directory_listings, curated on the platform
 * screen, never rendered publicly. The member's real email and phone stay
 * off the page; contact flows through the platform. Delivery flags record
 * what the relay managed, so undelivered messages can be re-sent later.
 *
 * Ships everywhere with the plugin but stays empty and inert on client
 * installs, same contract as 0049: the routes register under
 * isInstinctorApp only.
 *
 * @since 2.1.2501
 */

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

return [
    'description' => 'Directory messages table + private notify contacts on listings',

    'up' => [
        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_directory_messages (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            listing_id bigint(20) unsigned NOT NULL,
            sender_name varchar(120) NOT NULL,
            sender_business varchar(191) NOT NULL DEFAULT '',
            sender_email varchar(191) NOT NULL,
            message text NOT NULL,
            ip varchar(45) NOT NULL DEFAULT '',
            email_sent tinyint(1) NOT NULL DEFAULT 0,
            sms_sent tinyint(1) NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'new',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY listing_id (listing_id),
            KEY ip_created (ip, created_at)
        ) {charset}",
    ],

    // ALTERs need the information_schema guard (same pattern as 0043):
    // MySQL has no ADD COLUMN IF NOT EXISTS we can rely on.
    'php' => function () {
        $t = \Instinctor\Core\DB::prefix() . 'instinctor_directory_listings';
        $hasCol = function (string $col) use ($t): 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',
                $t, $col
            )) > 0;
        };
        if (!$hasCol('notify_email')) {
            \Instinctor\Core\DB::query("ALTER TABLE {$t} ADD COLUMN notify_email varchar(191) NOT NULL DEFAULT ''");
        }
        if (!$hasCol('notify_phone')) {
            \Instinctor\Core\DB::query("ALTER TABLE {$t} ADD COLUMN notify_phone varchar(40) NOT NULL DEFAULT ''");
        }
    },
];
