<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

class CreateNexusPhase1Schema extends Migration
{
    /**
     * NEXUS Engine — Phase 1: Database Layer
     *
     * Creates:
     *   - nexus_sessions
     *   - nexus_config
     *   - nexus_user_metrics
     *   - nexus_audit_log
     *   - nexus_economy_snapshots
     *
     * Alters:
     *   - sells      (+ margin_percent, recess_percent, excess_percent, fill_price, nexus_session_id, rejection_reason)
     *   - bids       (+ nexus_session_id, wallet_source)
     *   - users      (+ ictp_base)
     *   - plans      (+ nexus_daily_min/max, nexus_monthly_min/max, nexus_success_rate_min/max)
     *
     * All additive. No drops, no renames, no type changes.
     * nexus_status seeded as 'inactive' — engine does not activate until Phase 9.
     */

    public function up()
    {
        // ─────────────────────────────────────────
        // 1. nexus_sessions
        // Tracks each of the 3 daily 1-hour bidding sessions
        // ─────────────────────────────────────────
        Schema::create('nexus_sessions', function (Blueprint $table) {
            $table->id();
            $table->unsignedTinyInteger('session_number')->comment('1, 2, or 3 — daily session slot');
            $table->date('session_date');
            $table->timestamp('scheduled_start')->nullable();
            $table->timestamp('actual_start')->nullable();
            $table->timestamp('actual_end')->nullable();
            $table->enum('status', ['pending', 'active', 'processing', 'completed', 'cancelled'])->default('pending');
            $table->decimal('total_bid_cost', 20, 2)->default(0)->comment('Sum of all bid amounts in this session');
            $table->decimal('total_sell_value', 20, 2)->default(0)->comment('Sum of all approved sell amounts');
            $table->decimal('win_rate_target', 5, 2)->nullable()->comment('Target win rate % set by NEXUS for this session');
            $table->decimal('win_rate_actual', 5, 2)->nullable()->comment('Actual win rate % achieved');
            $table->unsignedInteger('total_bids')->default(0);
            $table->unsignedInteger('total_sells_evaluated')->default(0);
            $table->unsignedInteger('total_sells_approved')->default(0);
            $table->unsignedInteger('total_sells_rejected')->default(0);
            $table->json('criteria_order')->nullable()->comment('Rotation order of the 9 evaluation criteria for this session');
            $table->text('session_notes')->nullable();
            $table->timestamps();

            $table->index(['session_date', 'session_number']);
            $table->index('status');
        });

        // ─────────────────────────────────────────
        // 2. nexus_config
        // All NEXUS engine parameters — admin-configurable
        // Seeded with defaults; nexus_status = inactive
        // ─────────────────────────────────────────
        Schema::create('nexus_config', function (Blueprint $table) {
            $table->id();
            $table->string('key', 100)->unique();
            $table->text('value')->nullable();
            $table->string('type', 20)->default('string')->comment('string, integer, decimal, boolean, json');
            $table->string('group', 50)->default('general')->comment('general, session, economy, loss, winrate, bot');
            $table->text('description')->nullable();
            $table->boolean('is_editable')->default(true);
            $table->timestamps();
        });

        // ─────────────────────────────────────────
        // 3. nexus_user_metrics
        // Per-user running totals tracked by NEXUS
        // One row per user, updated each session
        // ─────────────────────────────────────────
        Schema::create('nexus_user_metrics', function (Blueprint $table) {
            $table->id();
            $table->unsignedInteger('user_id')->unique();
            $table->decimal('monthly_profit', 20, 2)->default(0)->comment('Running profit total for current calendar month');
            $table->decimal('monthly_profit_cap', 20, 2)->nullable()->comment('Max profit allowed this month — derived from plan');
            $table->unsignedInteger('total_sells_approved')->default(0);
            $table->unsignedInteger('total_sells_rejected')->default(0);
            $table->unsignedInteger('first_sell_granted')->default(0)->comment('1 if first-timer guarantee has been used');
            $table->decimal('total_earned', 20, 2)->default(0)->comment('Lifetime total approved sell value');
            $table->decimal('ictp_base', 20, 2)->nullable()->comment('ICTP base value — parked, not activated');
            $table->timestamp('metrics_reset_at')->nullable()->comment('When monthly_profit was last reset');
            $table->timestamps();

            $table->index('user_id');
        });

        // ─────────────────────────────────────────
        // 4. nexus_audit_log
        // Immutable record of every NEXUS engine action
        // Never deleted, never updated — append only
        // ─────────────────────────────────────────
        Schema::create('nexus_audit_log', function (Blueprint $table) {
            $table->id();
            $table->string('event', 100)->comment('e.g. session.started, sell.approved, sell.rejected, config.changed');
            $table->string('actor_type', 50)->nullable()->comment('system, admin, scheduler');
            $table->unsignedInteger('actor_id')->nullable()->comment('admin id if actor is admin');
            $table->unsignedInteger('subject_user_id')->nullable()->comment('user affected, if any');
            $table->unsignedBigInteger('nexus_session_id')->nullable();
            $table->unsignedInteger('sell_id')->nullable();
            $table->unsignedInteger('bid_id')->nullable();
            $table->json('context')->nullable()->comment('Full JSON snapshot of relevant data at time of event');
            $table->text('notes')->nullable();
            $table->timestamp('created_at')->useCurrent();

            $table->index('event');
            $table->index('nexus_session_id');
            $table->index('subject_user_id');
            $table->index('created_at');
        });

        // ─────────────────────────────────────────
        // 5. nexus_economy_snapshots
        // Point-in-time snapshot of market economy
        // Taken at start and end of each session
        // ─────────────────────────────────────────
        Schema::create('nexus_economy_snapshots', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('nexus_session_id')->nullable();
            $table->string('snapshot_type', 20)->default('session_start')->comment('session_start, session_end, manual');
            $table->decimal('total_pending_sell_value', 20, 2)->default(0);
            $table->decimal('total_active_investment', 20, 2)->default(0);
            $table->decimal('platform_liquidity', 20, 2)->default(0);
            $table->decimal('recess_threshold', 20, 2)->nullable()->comment('Asking price below which session is in recess');
            $table->decimal('excess_threshold', 20, 2)->nullable()->comment('Asking price above which session is in excess');
            $table->json('market_breakdown')->nullable()->comment('Per-market sell value breakdown');
            $table->timestamp('captured_at')->useCurrent();

            $table->index('nexus_session_id');
            $table->index('captured_at');
        });

        // ─────────────────────────────────────────
        // ALTER: sells
        // NEXUS evaluation result columns
        // ─────────────────────────────────────────
        Schema::table('sells', function (Blueprint $table) {
            $table->decimal('margin_percent', 8, 4)->nullable()->after('amount')->comment('Margin % at time of evaluation');
            $table->decimal('recess_percent', 8, 4)->nullable()->after('margin_percent')->comment('Recess % at time of evaluation');
            $table->decimal('excess_percent', 8, 4)->nullable()->after('recess_percent')->comment('Excess % at time of evaluation');
            $table->decimal('fill_price', 20, 2)->nullable()->after('excess_percent')->comment('Final price NEXUS fills the sell at');
            $table->unsignedBigInteger('nexus_session_id')->nullable()->after('fill_price')->comment('Session this sell was evaluated in');
            $table->string('rejection_reason', 100)->nullable()->after('nexus_session_id')->comment('Step code that caused rejection e.g. step_3_loss_req');

            $table->index('nexus_session_id');
        });

        // ─────────────────────────────────────────
        // ALTER: bids
        // NEXUS session linkage and wallet source
        // ─────────────────────────────────────────
        Schema::table('bids', function (Blueprint $table) {
            $table->unsignedBigInteger('nexus_session_id')->nullable()->after('type')->comment('Session this bid was placed in');
            $table->string('wallet_source', 50)->nullable()->after('nexus_session_id')->comment('Which wallet funded this bid e.g. balance, store_bonus');

            $table->index('nexus_session_id');
        });

        // ─────────────────────────────────────────
        // ALTER: users
        // ICTP base — parked, schema only, not activated
        // ─────────────────────────────────────────
        Schema::table('users', function (Blueprint $table) {
            $table->decimal('ictp_base', 20, 2)->nullable()->after('updated_at')->comment('ICTP base value — parked Phase 9, do not use');
        });

        // ─────────────────────────────────────────
        // ALTER: plans
        // NEXUS per-plan session and monthly limits
        // ─────────────────────────────────────────
        Schema::table('plans', function (Blueprint $table) {
            $table->decimal('nexus_daily_min', 20, 2)->nullable()->after('max_roi')->comment('Min daily sell value NEXUS allows for this plan');
            $table->decimal('nexus_daily_max', 20, 2)->nullable()->after('nexus_daily_min')->comment('Max daily sell value NEXUS allows for this plan');
            $table->decimal('nexus_monthly_min', 20, 2)->nullable()->after('nexus_daily_max')->comment('Min monthly profit allowed for this plan');
            $table->decimal('nexus_monthly_max', 20, 2)->nullable()->after('nexus_monthly_min')->comment('Max monthly profit cap for this plan');
            $table->decimal('nexus_success_rate_min', 5, 2)->nullable()->after('nexus_monthly_max')->comment('Min win rate % for this plan');
            $table->decimal('nexus_success_rate_max', 5, 2)->nullable()->after('nexus_success_rate_min')->comment('Max win rate % for this plan');
        });

        // ─────────────────────────────────────────
        // SEED: nexus_config defaults
        // nexus_status = inactive — engine does not run until Phase 9
        // ─────────────────────────────────────────
        $now = now();
        DB::table('nexus_config')->insert([

            // Core engine switch — MUST stay inactive until Phase 9
            ['key' => 'nexus_status',               'value' => 'inactive',  'type' => 'string',  'group' => 'general',  'description' => 'Master engine switch. Set to active only at go-live. Never change during build.', 'is_editable' => false, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'nexus_version',              'value' => '1.0',       'type' => 'string',  'group' => 'general',  'description' => 'NEXUS Engine version', 'is_editable' => false, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'nexus_go_live_date',         'value' => '2026-03-27','type' => 'string',  'group' => 'general',  'description' => 'Target go-live date', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],

            // Session schedule — UTC times
            ['key' => 'session_1_start_utc',        'value' => '01:00',     'type' => 'string',  'group' => 'session',  'description' => 'Session 1 start time UTC', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'session_2_start_utc',        'value' => '09:00',     'type' => 'string',  'group' => 'session',  'description' => 'Session 2 start time UTC', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'session_3_start_utc',        'value' => '17:00',     'type' => 'string',  'group' => 'session',  'description' => 'Session 3 start time UTC', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'session_duration_minutes',   'value' => '60',        'type' => 'integer', 'group' => 'session',  'description' => 'How long each session runs in minutes', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],

            // Win rate thresholds per session (based on sell value vs bid cost ratio)
            ['key' => 'win_rate_session_1',         'value' => '80',        'type' => 'decimal', 'group' => 'winrate',  'description' => 'Target win rate % for session 1 (ratio <= 0.5)', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'win_rate_session_2',         'value' => '50',        'type' => 'decimal', 'group' => 'winrate',  'description' => 'Target win rate % for session 2 (ratio 0.5–1.0)', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'win_rate_session_3',         'value' => '30',        'type' => 'decimal', 'group' => 'winrate',  'description' => 'Target win rate % for session 3 (ratio > 1.0)', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],

            // Loss requirement baseline range (admin sets range, NEXUS picks value per session)
            ['key' => 'loss_requirement_min',       'value' => '5',         'type' => 'decimal', 'group' => 'loss',     'description' => 'Minimum loss requirement % baseline', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'loss_requirement_max',       'value' => '20',        'type' => 'decimal', 'group' => 'loss',     'description' => 'Maximum loss requirement % baseline', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],

            // Economy guardian thresholds
            ['key' => 'recess_threshold_percent',   'value' => '10',        'type' => 'decimal', 'group' => 'economy',  'description' => 'Asking price % below product price triggers recess', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'excess_threshold_percent',   'value' => '30',        'type' => 'decimal', 'group' => 'economy',  'description' => 'Asking price % above product price triggers excess', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'buyback_zone_percent',       'value' => '5',         'type' => 'decimal', 'group' => 'economy',  'description' => 'Step 1: % below initial price that qualifies for buyback zone', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],

            // Bot governance
            ['key' => 'bot_session_alignment',      'value' => 'true',      'type' => 'boolean', 'group' => 'bot',      'description' => 'Whether AI bots respect NEXUS session windows', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
            ['key' => 'bot_fund_range_enforcement', 'value' => 'true',      'type' => 'boolean', 'group' => 'bot',      'description' => 'Whether AI bot fund ranges are enforced by NEXUS', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],

            // Criteria rotation — default order (rotates every session)
            ['key' => 'criteria_rotation_enabled',  'value' => 'true',      'type' => 'boolean', 'group' => 'session',  'description' => 'Whether evaluation criteria order rotates each session', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],

            // First-timer guarantee
            ['key' => 'first_timer_guarantee',      'value' => 'true',      'type' => 'boolean', 'group' => 'general',  'description' => 'Step 2: Guarantee first sell approval for new sellers', 'is_editable' => true, 'created_at' => $now, 'updated_at' => $now],
        ]);
    }

    public function down()
    {
        // Remove nexus_config seed
        DB::table('nexus_config')->truncate();

        // Reverse table alterations
        Schema::table('plans', function (Blueprint $table) {
            $table->dropColumn([
                'nexus_daily_min', 'nexus_daily_max',
                'nexus_monthly_min', 'nexus_monthly_max',
                'nexus_success_rate_min', 'nexus_success_rate_max',
            ]);
        });

        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('ictp_base');
        });

        Schema::table('bids', function (Blueprint $table) {
            $table->dropIndex(['nexus_session_id']);
            $table->dropColumn(['nexus_session_id', 'wallet_source']);
        });

        Schema::table('sells', function (Blueprint $table) {
            $table->dropIndex(['nexus_session_id']);
            $table->dropColumn([
                'margin_percent', 'recess_percent', 'excess_percent',
                'fill_price', 'nexus_session_id', 'rejection_reason',
            ]);
        });

        // Drop new tables in reverse order
        Schema::dropIfExists('nexus_economy_snapshots');
        Schema::dropIfExists('nexus_audit_log');
        Schema::dropIfExists('nexus_user_metrics');
        Schema::dropIfExists('nexus_config');
        Schema::dropIfExists('nexus_sessions');
    }
}
