<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Eloquent\Model;
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),
                                '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));
        }
    }
}
