<?php

/**
 * Migration 0015 — Google OAuth columns on instinctor_users
 *
 * Adds two columns so SocialLoginService can authenticate via Google
 * Sign-In on standalone mode (where wp_users / wp_usermeta don't exist):
 *
 *   google_id   The Google account's stable subject id (the `sub` /
 *               `id` field returned by /oauth2/v2/userinfo). Used to
 *               look up returning users without depending on email,
 *               which can change. UNIQUE so two accounts can't share
 *               the same Google identity. NULL is allowed and unique
 *               key permits multiple NULLs (MySQL behaviour), so
 *               password-only users are unaffected.
 *   avatar_url  The Google profile picture URL. Optional; populated
 *               on first sign-in for display in the customer area.
 *
 * Idempotent: each column/index is guarded by an INFORMATION_SCHEMA
 * existence check in the PHP callback. A later in-place edit of
 * 0004_standalone_tables.php had baked these columns into the users
 * CREATE TABLE, so on fresh installs the original bare ALTER here
 * failed with "Duplicate column name 'google_id'" on every boot and
 * blocked all migrations after 0015 (14k+ log entries on
 * infinite2delivery before this was caught). Never edit an applied
 * migration's schema in place; add a new guarded migration instead.
 *
 * The unique key is only added when this migration creates google_id
 * itself (all rows NULL → unique is safe). When the column pre-exists
 * via the 0004 shape (NOT NULL DEFAULT '' + plain KEY google_id),
 * password-only rows share '' and a unique index would fail — the
 * plain key is left as is. SocialLoginService only ever looks up
 * non-empty ids, so both shapes behave identically.
 *
 * @since 2.1.325
 */

if (!defined('ABSPATH')) exit;
return [
    'description' => 'Google OAuth columns on instinctor_users',

    // Schema work happens in the PHP callback so we can guard each
    // ALTER on its own existence check. Pure SQL `IF NOT EXISTS` is
    // not portable between MySQL and MariaDB for ALTER TABLE.
    'up' => [],

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

        $columnExists = function (string $column) use ($table): 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",
                    $table,
                    $column
                )
            ) > 0;
        };

        $hadGoogleId = $columnExists('google_id');

        if (!$hadGoogleId) {
            \Instinctor\Core\DB::query(
                "ALTER TABLE {$table}
                 ADD COLUMN google_id varchar(64) NULL DEFAULT NULL AFTER email"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('google_id column add failed: ' . \Instinctor\Core\DB::last_error())
                );
            }
        }

        if (!$columnExists('avatar_url')) {
            \Instinctor\Core\DB::query(
                "ALTER TABLE {$table}
                 ADD COLUMN avatar_url varchar(512) NULL DEFAULT NULL AFTER google_id"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('avatar_url column add failed: ' . \Instinctor\Core\DB::last_error())
                );
            }
        }

        // Unique key only on the column this migration just created
        // (all NULL → safe). Pre-existing 0004-shape columns keep their
        // plain KEY google_id; see header comment.
        if (!$hadGoogleId) {
            $idxExists = (int) \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_google_id'",
                    $table
                )
            ) > 0;
            if (!$idxExists) {
                \Instinctor\Core\DB::query(
                    "ALTER TABLE {$table} ADD UNIQUE KEY idx_google_id (google_id)"
                );
                if (\Instinctor\Core\DB::last_error() !== '') {
                    throw new \RuntimeException(
                        esc_html('idx_google_id index add failed: ' . \Instinctor\Core\DB::last_error())
                    );
                }
            }
        }
    },

    'down' => [
        "ALTER TABLE {prefix}instinctor_users DROP INDEX IF EXISTS idx_google_id",
        "ALTER TABLE {prefix}instinctor_users DROP COLUMN IF EXISTS avatar_url",
        "ALTER TABLE {prefix}instinctor_users DROP COLUMN IF EXISTS google_id",
    ],
];
