<?php

/**
 * Migration 0004 — Standalone core tables
 *
 * Creates instinctor_config, instinctor_users, and instinctor_sessions.
 * These tables are the foundation of standalone mode (INSTINCTOR_ENV=standalone).
 * On WordPress installs these tables are created but unused — WP options and
 * WP users handle the equivalent data. No conflict.
 *
 * @since 3.18.50
 */

if (!defined('ABSPATH')) exit;
return [
    'description' => 'Standalone core tables: config, users, sessions',
    'up' => [
        // Key-value configuration store (replaces WP options on standalone)
        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_config (
            id          int unsigned NOT NULL AUTO_INCREMENT,
            config_key  varchar(191) NOT NULL,
            config_val  longtext     NOT NULL,
            updated_at  datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY config_key (config_key)
        ) {charset}",

        // Admin / customer users (standalone — replaces WP users).
        // google_id / avatar_url deliberately NOT here — migration 0015 adds
        // them. They were once retrofitted into this CREATE, which made the
        // bare ALTER in 0015 fail with "Duplicate column" on every fresh
        // install and wedge the migration runner. Never edit an applied
        // migration's schema in place.
        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_users (
            id              int unsigned NOT NULL AUTO_INCREMENT,
            email           varchar(191) NOT NULL,
            password_hash   varchar(255) NOT NULL DEFAULT '',
            display_name    varchar(100) NOT NULL DEFAULT '',
            role            varchar(50)  NOT NULL DEFAULT 'customer',
            phone           varchar(30)  NOT NULL DEFAULT '',
            meta            longtext     NOT NULL,
            email_verified  tinyint(1)   NOT NULL DEFAULT 0,
            active          tinyint(1)   NOT NULL DEFAULT 1,
            created_at      datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at      datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY email (email),
            KEY role (role)
        ) {charset}",

        // Auth + CSRF sessions
        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_sessions (
            id          int unsigned  NOT NULL AUTO_INCREMENT,
            user_id     int unsigned  NOT NULL DEFAULT 0,
            token_hash  varchar(64)   NOT NULL,
            type        varchar(20)   NOT NULL DEFAULT 'auth',
            ip          varchar(45)   NOT NULL DEFAULT '',
            user_agent  varchar(255)  NOT NULL DEFAULT '',
            expires_at  datetime      NOT NULL,
            created_at  datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY token_hash (token_hash),
            KEY user_id (user_id),
            KEY type_expires (type, expires_at)
        ) {charset}",
    ],
    'down' => [
        "DROP TABLE IF EXISTS {prefix}instinctor_sessions",
        "DROP TABLE IF EXISTS {prefix}instinctor_users",
        "DROP TABLE IF EXISTS {prefix}instinctor_config",
    ],
];
