<?php

/**
 * Migration 0030 — `scheduled_for` column on orders
 *
 * Lets a customer place an order for a future fulfillment time (pickup or
 * delivery), from later today to weeks ahead. Used by the restaurant /
 * pizza-builder flow where catering, parties, and holiday orders are
 * routinely placed well in advance.
 *
 * Schema:
 *   - scheduled_for DATETIME NULL — the requested fulfillment time. NULL
 *     means "as soon as possible" (the default). Indexed so the admin can
 *     list upcoming scheduled orders in chronological order.
 *
 * Optional feature, gated behind the `instinctor_scheduled_orders_enabled`
 * store flag at checkout; the column is harmless (always NULL) on stores
 * that never enable it.
 *
 * Idempotent via INFORMATION_SCHEMA guard, same pattern as 0028.
 */

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

return [
    'description' => 'Add `scheduled_for` column + index to inst_instinctor_orders',

    'up' => [],

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

        // Column.
        $colExists = \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  = 'scheduled_for'",
                $table
            )
        );
        if (!$colExists) {
            \Instinctor\Core\DB::query(
                "ALTER TABLE {$table}
                 ADD COLUMN scheduled_for DATETIME NULL DEFAULT NULL
                 AFTER customer_note"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('scheduled_for column add failed: '
                    . \Instinctor\Core\DB::last_error())
                );
            }
        }

        // Index — drives the admin "upcoming scheduled orders" list.
        $idxExists = \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   = 'idx_scheduled_for'",
                $table
            )
        );
        if (!$idxExists) {
            \Instinctor\Core\DB::query(
                "ALTER TABLE {$table} ADD INDEX idx_scheduled_for (scheduled_for)"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('idx_scheduled_for index add failed: '
                    . \Instinctor\Core\DB::last_error())
                );
            }
        }
    },

    'down' => [
        "ALTER TABLE {prefix}instinctor_orders DROP INDEX IF EXISTS idx_scheduled_for",
        "ALTER TABLE {prefix}instinctor_orders DROP COLUMN IF EXISTS scheduled_for",
    ],
];
