<?php

namespace Database\Seeders\Demo;

use App\Model\Field;
use App\Model\Subscriber;
use Illuminate\Support\Facades\DB;

/**
 * Stage 08 — bulk-insert subscribers from sample-contacts.csv.
 *
 * Reads real contact data from `sample-contacts.csv` (128k rows) and
 * distributes `subscribers_total` randomly across the customer's lists.
 *
 * CSV columns used: first_name, last_name, company_name, address, phone1, email, web.
 * DATE_OF_BIRTH is generated randomly (age 18-55).
 *
 * Uses raw DB::table()->insert() in chunks of 1000 for performance.
 */
class Stage08_SubscribersSeeder extends AbstractDemoSeeder
{
    protected int $fakerOffset = 8;

    /** Parsed CSV rows (loaded once, reused across lists). */
    protected array $csvRows = [];

    public function run(): void
    {
        $f = $this->faker();

        DB::connection()->disableQueryLog();

        $this->loadCsv();
        $this->log('loaded ' . count($this->csvRows) . ' contacts from sample-contacts.csv');

        $totalLists = 0;
        $totalSubs  = 0;

        foreach (DemoConfig::customers() as $cfg) {
            $lists = Stage07_MailListsSeeder::$created[$cfg['key']] ?? [];
            if (empty($lists)) continue;

            $total = $cfg['subscribers_total'] ?? 1000;
            $distribution = $this->randomDistribution(count($lists), $total, $f);

            foreach ($lists as $i => $list) {
                $count = $distribution[$i];
                $created = $this->seedListSubscribers($list, $count);
                $totalSubs  += $created;
                $totalLists += 1;
                $this->log(sprintf("  list #%d (%s) → %d subscribers", $list->id, $list->name, $created));
            }
        }

        $this->log("seeded {$totalSubs} subscribers across {$totalLists} lists");
    }

    /**
     * Load and parse sample-contacts.csv once into memory.
     * Only keeps rows that have all required fields populated.
     */
    protected function loadCsv(): void
    {
        $path = base_path('sample-contacts.csv');
        if (!file_exists($path)) {
            throw new \RuntimeException("sample-contacts.csv not found at {$path}");
        }

        $handle = fopen($path, 'r');
        $headers = fgetcsv($handle);
        // Map header names to indices
        $idx = array_flip($headers);

        while (($row = fgetcsv($handle)) !== false) {
            if (count($row) < count($headers)) continue;

            $email = trim($row[$idx['email']] ?? '');
            $first = trim($row[$idx['first_name']] ?? '');
            $last  = trim($row[$idx['last_name']] ?? '');
            if (!$email || !$first || !$last) continue;

            $this->csvRows[] = [
                'email'      => $email,
                'first_name' => $first,
                'last_name'  => $last,
                'company'    => trim($row[$idx['company_name']] ?? ''),
                'address'    => trim($row[$idx['address']] ?? ''),
                'phone'      => trim($row[$idx['phone1']] ?? ''),
                'web'        => trim($row[$idx['web']] ?? ''),
            ];
        }
        fclose($handle);
    }

    /**
     * Distribute $total randomly across $n buckets.
     * Each bucket gets at least 100 subscribers.
     */
    protected function randomDistribution(int $n, int $total, $f): array
    {
        $min = 100;
        $remaining = $total - ($min * $n);
        if ($remaining < 0) $remaining = 0;

        $weights = [];
        for ($i = 0; $i < $n; $i++) {
            $weights[] = $f->numberBetween(1, 100);
        }
        $weightSum = array_sum($weights);

        $counts = [];
        $assigned = 0;
        for ($i = 0; $i < $n; $i++) {
            $share = (int) round($remaining * $weights[$i] / $weightSum);
            $counts[$i] = $min + $share;
            $assigned += $counts[$i];
        }
        $counts[$n - 1] += ($total - $assigned);

        return $counts;
    }

    /**
     * Bulk-insert $count subscribers for one list using CSV data.
     *
     * Hot loop uses mt_rand / pre-generated random buffers / timestamp arithmetic
     * instead of Faker + Carbon clones, since this runs millions of iterations.
     */
    protected function seedListSubscribers($list, int $count): int
    {
        $csvCount = count($this->csvRows);

        // Map tag → custom_NNN column.
        $fields = Field::where('mail_list_id', $list->id)->get();
        $colByTag = [];
        foreach ($fields as $field) {
            $colByTag[$field->tag] = $field->custom_field_name;
        }
        $emailCol = $colByTag['EMAIL'] ?? null;

        // Pre-resolve tag→csv-key once per list (replaces switch in hot loop).
        // '__dob__' = synthetic key for date-of-birth (computed per row).
        $csvKeyByCol = [];
        $tagToCsvKey = [
            'FIRST_NAME'    => 'first_name',
            'LAST_NAME'     => 'last_name',
            'COMPANY'       => 'company',
            'ADDRESS'       => 'address',
            'PHONE'         => 'phone',
            'WEB'           => 'web',
            'DATE_OF_BIRTH' => '__dob__',
        ];
        foreach ($colByTag as $tag => $col) {
            if ($tag === 'EMAIL') continue;
            if (isset($tagToCsvKey[$tag])) {
                $csvKeyByCol[$col] = $tagToCsvKey[$tag];
            }
        }

        $statuses = [
            Subscriber::STATUS_SUBSCRIBED, Subscriber::STATUS_SUBSCRIBED,
            Subscriber::STATUS_SUBSCRIBED, Subscriber::STATUS_SUBSCRIBED, Subscriber::STATUS_SUBSCRIBED,
            Subscriber::STATUS_UNSUBSCRIBED,
            Subscriber::STATUS_UNCONFIRMED,
            Subscriber::STATUS_BLACKLISTED,
        ];
        $verifications = [
            null, null, null,
            Subscriber::VERIFICATION_STATUS_DELIVERABLE,
            Subscriber::VERIFICATION_STATUS_DELIVERABLE,
            Subscriber::VERIFICATION_STATUS_RISKY,
            Subscriber::VERIFICATION_STATUS_UNDELIVERABLE,
            Subscriber::VERIFICATION_STATUS_UNKNOWN,
        ];
        $sources = [
            Subscriber::SUBSCRIPTION_TYPE_ADDED,
            Subscriber::SUBSCRIPTION_TYPE_IMPORTED,
            Subscriber::SUBSCRIPTION_TYPE_SINGLE_OPTIN,
            Subscriber::SUBSCRIPTION_TYPE_DOUBLE_OPTIN,
        ];
        $froms   = ['signup', 'import', 'api', 'admin'];
        $tagPool = ['vip', 'newsletter', 'promo', 'engaged', 'cold', 'paid', 'lead', 'customer', 'partner', 'beta'];

        $statusMax = count($statuses) - 1;
        $verifMax  = count($verifications) - 1;
        $sourceMax = count($sources) - 1;
        $fromMax   = count($froms) - 1;
        $csvMax    = $csvCount - 1;

        $nowTs = now()->getTimestamp();
        // Subscriber create dates span "last 30 days" but never older than
        // their parent list (Stage07 backdates lists up to 30 days). Floor
        // = max(now-30d, list.created_at).
        $listCreatedTs = $list->created_at ? $list->created_at->getTimestamp() : $nowTs;
        $windowSeconds = max(0, $nowTs - max($nowTs - 30 * 86400, $listCreatedTs));
        $batchSize = 2000;
        $inserted = 0;
        $seen = [];

        $totalBatches = (int) ceil($count / $batchSize);

        for ($batch = 0; $batch < $totalBatches; $batch++) {
            $remaining = min($batchSize, $count - ($batch * $batchSize));

            // One random_bytes() call instead of $remaining calls (uid is 13 hex chars).
            $uidBuf = bin2hex(random_bytes($remaining * 7));

            $rows = [];
            for ($i = 0; $i < $remaining; $i++) {
                $csvRow = $this->csvRows[mt_rand(0, $csvMax)];

                $email = $csvRow['email'];
                if (isset($seen[$email])) {
                    $email = preg_replace('/@/', '+l' . $list->id . 'b' . $batch . 'i' . $i . '@', $email, 1);
                }
                $seen[$email] = true;

                // Timestamp arithmetic on epoch — no Carbon clones.
                $createdAt = date('Y-m-d H:i:s', $nowTs - ($windowSeconds > 0 ? mt_rand(0, $windowSeconds) : 0));
                // Random DOB: age 18-55, day-resolution.
                $dob = date('Y-m-d', $nowTs - mt_rand(18 * 365, 55 * 365 + 364) * 86400);

                // Build tags JSON without Faker (0-3 unique picks from $tagPool).
                $tc = mt_rand(0, 3);
                if ($tc === 0) {
                    $tagsJson = '[]';
                } else {
                    $picked = (array) array_rand($tagPool, $tc);
                    $tags = [];
                    foreach ($picked as $k) {
                        $tags[] = $tagPool[$k];
                    }
                    $tagsJson = json_encode($tags);
                }

                $row = [
                    'uid'                  => substr($uidBuf, $i * 14, 13),
                    'mail_list_id'         => $list->id,
                    'email'                => $email,
                    'status'               => $statuses[mt_rand(0, $statusMax)],
                    'subscription_type'    => $sources[mt_rand(0, $sourceMax)],
                    'verification_status'  => $verifications[mt_rand(0, $verifMax)],
                    'tags'                 => $tagsJson,
                    'from'                 => $froms[mt_rand(0, $fromMax)],
                    'ip'                   => mt_rand(1, 254) . '.' . mt_rand(0, 254) . '.' . mt_rand(0, 254) . '.' . mt_rand(1, 254),
                    'created_at'           => $createdAt,
                    'updated_at'           => $createdAt,
                ];

                if ($emailCol) {
                    $row[$emailCol] = $email;
                }

                foreach ($csvKeyByCol as $col => $csvKey) {
                    $row[$col] = $csvKey === '__dob__' ? $dob : $csvRow[$csvKey];
                }
                $rows[] = $row;
            }

            DB::table('subscribers')->insert($rows);
            $inserted += count($rows);
        }
        return $inserted;
    }
}