<?php

namespace Database\Seeders;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Mtc\Modules\Members\Models\Member;

class OrderTableSeeder extends Seeder
{
    // how many members we stream per batch (orders per batch = this number)
    private int $MEMBER_BATCH = 1000;

    // placeholder limit target (MySQL ~65k; stay under)
    private int $MAX_PLACEHOLDERS = 60000;

    // hard row caps per insert to avoid huge packets
    private int $HARD_CAP_DEFAULT = 1000;

    public function run(): void
    {
        DB::disableQueryLog();

        Schema::disableForeignKeyConstraints();
        DB::table('order_items')->truncate();
        DB::table('order_info')->truncate();
        DB::table('order_address')->truncate();
        DB::table('order')->truncate();
        Schema::enableForeignKeyConstraints();

        $deliveryMethods = DB::table('delivery_methods')
            ->select('id', 'name', 'cost')
            ->get()
            ->map(fn ($d) => (array) $d)
            ->all();

        $items = DB::table('items')
            ->select('id', 'name', 'price', 'price_exvat')
            ->get()
            ->map(fn ($i) => (array) $i)
            ->all();

        $assessmentIds = DB::table('assessments')->pluck('id')->all();

        if (empty($deliveryMethods) || empty($items)) {
            throw new \RuntimeException('Seed prerequisites missing: delivery methods or items are empty.');
        }

        $currentOrderId = 0;

        Model::withoutEvents(function () use ($deliveryMethods, $items, $assessmentIds, &$currentOrderId) {
            Member::query()
                ->orderBy('id')
                ->chunkById($this->MEMBER_BATCH, function ($members) use (
                    $deliveryMethods,
                    $items,
                    $assessmentIds,
                    &$currentOrderId,
                ) {
                    DB::transaction(function () use (
                        $members,
                        $deliveryMethods,
                        $items,
                        $assessmentIds,
                        &$currentOrderId,
                    ) {
                        $membersCollection = $members instanceof \Illuminate\Support\Collection
                            ? $members
                            : collect($members);

                        $membersCollection->loadMissing(['addressBilling', 'addressShipping']);

                        $now = now();
                        $orderRows = [];
                        $pending = [];

                        foreach ($membersCollection as $member) {
                            $delivery = $deliveryMethods[array_rand($deliveryMethods)];

                            $cnt = random_int(1, 3);
                            $picked = [];
                            for ($k = 0; $k < $cnt; $k++) {
                                $picked[] = $items[array_rand($items)];
                            }

                            $sumEx = array_sum(array_column($picked, 'price_exvat'));
                            $sumIn = array_sum(array_column($picked, 'price')) + (float) $delivery['cost'];

                            $orderId = ++$currentOrderId;

                            $orderRows[] = [
                                'id' => $orderId,
                                'paid' => 1,
                                'date' => $now,
                                'delivery_method_id' => $delivery['id'],
                                'delivery_name' => $delivery['name'],
                                'delivery_cost' => $delivery['cost'],
                                'cost_total_exvat' => $sumEx,
                                'cost_total' => $sumIn,
                                'status' => random_int(0, 1),
                                'member' => $member->id,
                                'admin_id' => 1,
                            ];

                            $pending[] = [
                                'order_id' => $orderId,
                                'member' => $member,
                                'items' => $picked,
                            ];
                        }

                        if (empty($orderRows)) {
                            return;
                        }

                        DB::table('order')->insert($orderRows);

                        $addressRows = [];
                        $infoRows = [];
                        $itemRows = [];

                        foreach ($pending as $meta) {
                            $orderId = $meta['order_id'];
                            /** @var \Mtc\Modules\Members\Models\Member $member */
                            $member = $meta['member'];
                            $pickedItems = $meta['items'];

                            $billing = $member->addressBilling;
                            $shipping = $member->addressShipping;

                            $bf = $billing?->firstname ?: fake()->firstName();
                            $bm = $billing?->middle_name ?: '';
                            $bl = $billing?->lastname ?: fake()->lastName();
                            $bp = $billing?->postcode ?: fake()->postcode();
                            $genderField = fake()->randomElement(['male', 'female']);
                            $addressRows[] = [
                                'order_id' => $orderId,
                                'type' => 'billing',
                                'gender' => $genderField,
                                'firstname' => $bf,
                                'middle_name' => $bm,
                                'lastname' => $bl,
                                'address1' => $billing?->address1 ?: fake()->streetAddress(),
                                'address2' => $billing?->address2 ?: '',
                                'city' => $billing?->city ?: fake()->city(),
                                'state' => $billing?->state ?: '',
                                'country' => $billing?->country ?: 'GB',
                                'postcode' => $bp,
                                'timestamp' => $now,
                                'notes' => $billing?->notes ?: '',
                            ];

                            $sf = $shipping?->firstname ?: $bf;
                            $sm = $shipping?->middle_name ?: '';
                            $sl = $shipping?->lastname ?: $bl;
                            $sp = $shipping?->postcode ?: fake()->postcode();
                            $addressRows[] = [
                                'order_id' => $orderId,
                                'type' => 'shipping',
                                'gender' => $genderField,
                                'firstname' => $sf,
                                'middle_name' => $sm,
                                'lastname' => $sl,
                                'address1' => $shipping?->address1 ?: fake()->streetAddress(),
                                'address2' => $shipping?->address2 ?: '',
                                'city' => $shipping?->city ?: fake()->city(),
                                'state' => $shipping?->state ?: '',
                                'country' => $shipping?->country ?: 'GB',
                                'postcode' => $sp,
                                'timestamp' => $now,
                                'notes' => $shipping?->notes ?: '',
                            ];

                            $memberEmail = (string) ($member->getRawOriginal('email') ?? '');
                            $memberPhone = (string) ($member->getRawOriginal('contact_no') ?? '');
                            $memberDob = (string) ($member->getRawOriginal('dob') ?? '');

                            $infoRows[] = [
                                'order_id' => $orderId,
                                'message' => '',
                                'newsletter' => 1,
                                'how_did_you_hear_about_us' => '',
                                'how_did_you_hear_about_us_text' => '',
                                'timestamp' => $now,
                                'phone_prefix' => '+44',
                                'assessment_ids' => json_encode($assessmentIds),
                                'multisite__site_id' => 1,
                                'member_conditions' => json_encode($member->getConditionsArray()),
                                'member_medications' => json_encode($member->getMedicationsArray()),
                                'member_allergies' => json_encode($member->getAllergiesArray()),
                                'email' => $memberEmail,
                                'contact_no' => $memberPhone,
                                'dob' => $memberDob !== '' ? $memberDob : null,
                            ];

                            foreach ($pickedItems as $item) {
                                $itemRows[] = [
                                    'order_id' => $orderId,
                                    'item_id' => $item['id'],
                                    'has_requested_subscription' => 0,
                                    'assessment_id' => 0,
                                    'item_name' => $item['name'],
                                    'item_price' => $item['price'],
                                    'quantity' => 1,
                                    'size' => 'One Size',
                                    'sizeid' => 0,
                                    'PLU' => '',
                                    'timestamp' => $now,
                                    'price_paid' => $item['price'],
                                    'price_paid_exvat' => $item['price_exvat'],
                                ];
                            }
                        }

                        $this->insertChunked('order_address', $addressRows);
                        $this->insertChunked('order_info', $infoRows);
                        $this->insertChunked('order_items', $itemRows);
                    });
                });
        });
    }

    private function insertChunked(string $table, array $rows): void
    {
        if (empty($rows)) {
            return;
        }

        $cols = count(array_keys($rows[0]));
        $byPlaceholders = max(1, intdiv($this->MAX_PLACEHOLDERS, max(1, $cols)));
        $chunkSize = min($byPlaceholders, $this->HARD_CAP_DEFAULT);

        $total = count($rows);
        for ($i = 0; $i < $total; $i += $chunkSize) {
            DB::table($table)->insert(array_slice($rows, $i, $chunkSize));
        }
    }
}
