<?php

namespace App\Services\Upgrade;

use App\Services\Kyc\KycService;
use App\Support\Audit;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Throwable;

/**
 * Self-service account-category upgrade.
 *
 * A registered user (base role, e.g. "client") can apply to be added to another
 * user category (pandit / vendor / franchise …). Each application:
 *   - is for ONE category at a time (only one pending request per user),
 *   - must attach the category's MANDATORY KYC documents (optional ones asked
 *     but not required — driven by doc_types.mandatory_for / optional_for),
 *   - is approved by an admin, which grants the membership.
 * After approval the user can apply for the next category (repeatable).
 *
 * Multi-category memberships live in `user_category_members`; the base
 * `users.role` is never changed here.
 */
class UpgradeService
{
    /** In-progress request statuses (block a second application). */
    private const OPEN = ['kyc_pending', 'approval_pending'];

    public function __construct(private readonly KycService $kyc)
    {
    }

    /** Idempotently create/upgrade the supporting tables + doc tag columns. */
    public function ensure(): void
    {
        try {
            if (!Schema::hasTable('user_category_members')) {
                DB::statement("CREATE TABLE IF NOT EXISTS `user_category_members` (
                    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    `user_id` INT UNSIGNED NOT NULL,
                    `category_code` VARCHAR(40) NOT NULL,
                    `status` VARCHAR(20) NOT NULL DEFAULT 'active',
                    `approved_by` INT UNSIGNED DEFAULT NULL,
                    `approved_at` DATETIME DEFAULT NULL,
                    `valid_till` DATE DEFAULT NULL,
                    `package_id` INT UNSIGNED DEFAULT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE KEY `uniq_user_cat` (`user_id`, `category_code`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            } else {
                // Additive: package-driven category validity.
                foreach (['valid_till' => 'DATE DEFAULT NULL', 'package_id' => 'INT UNSIGNED DEFAULT NULL'] as $col => $ddl) {
                    if (!Schema::hasColumn('user_category_members', $col)) {
                        try {
                            DB::statement("ALTER TABLE `user_category_members` ADD COLUMN `$col` $ddl");
                        } catch (Throwable) {
                        }
                    }
                }
            }
            if (!Schema::hasTable('category_upgrade_requests')) {
                DB::statement("CREATE TABLE IF NOT EXISTS `category_upgrade_requests` (
                    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    `user_id` INT UNSIGNED NOT NULL,
                    `category_code` VARCHAR(40) NOT NULL,
                    `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
                    `note` VARCHAR(500) DEFAULT NULL,
                    `reviewed_by` INT UNSIGNED DEFAULT NULL,
                    `reviewed_at` DATETIME DEFAULT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    KEY `idx_user_status` (`user_id`, `status`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            }
            // tag KYC docs with the applied category + request
            foreach (['category' => "VARCHAR(40) DEFAULT NULL", 'request_id' => "INT UNSIGNED DEFAULT NULL"] as $col => $def) {
                if (Schema::hasTable('user_documents') && !Schema::hasColumn('user_documents', $col)) {
                    DB::statement("ALTER TABLE `user_documents` ADD COLUMN `$col` $def");
                }
            }
            // Franchise applicants choose a geographic territory at apply time; it
            // is reserved with the request and assigned on approval.
            foreach (['territory_code' => "VARCHAR(160) DEFAULT NULL", 'territory_name' => "VARCHAR(160) DEFAULT NULL"] as $col => $def) {
                if (Schema::hasTable('category_upgrade_requests') && !Schema::hasColumn('category_upgrade_requests', $col)) {
                    DB::statement("ALTER TABLE `category_upgrade_requests` ADD COLUMN `$col` $def");
                }
            }
            // Base categories (source of truth for role): USR (default), PND, client.
            if (Schema::hasTable('user_categories')) {
                $hasApplies = Schema::hasColumn('user_categories', 'applies_role');
                $hasDefault = Schema::hasColumn('user_categories', 'is_default');
                $hasActive  = Schema::hasColumn('user_categories', 'is_active');
                $hasCreated = Schema::hasColumn('user_categories', 'created_at');
                foreach ([
                    ['USR', 'User / Client', 'yajman', 1],
                    ['PND', 'Pandit', 'pandit', 0],
                    ['client', 'Client', 'yajman', 0],
                ] as [$code, $name, $applies, $def]) {
                    if (DB::table('user_categories')->where('code', $code)->exists()) {
                        continue;
                    }
                    $row = ['code' => $code, 'name' => $name];
                    if ($hasApplies) {
                        $row['applies_role'] = $applies;
                    }
                    if ($hasDefault) {
                        $row['is_default'] = $def;
                    }
                    if ($hasActive) {
                        $row['is_active'] = 1;
                    }
                    if ($hasCreated) {
                        $row['created_at'] = now();
                    }
                    DB::table('user_categories')->insert($row);
                }
            }
        } catch (Throwable) {
            // best-effort; callers degrade gracefully
        }
    }

    /** Active category codes the user already holds. */
    public function memberships(int $userId): array
    {
        $this->ensure();
        try {
            return DB::table('user_category_members')
                ->where('user_id', $userId)->where('status', 'active')
                ->pluck('category_code')->map(fn ($c) => (string) $c)->all();
        } catch (Throwable) {
            return [];
        }
    }

    /**
     * Reconcile category memberships from ACTIVE packages: whenever a user holds
     * an active package for a category, they effectively hold that category, so
     * grant (or reactivate) the matching membership. This keeps the two records
     * consistent even when a package was activated/approved through a path other
     * than the upgrade review (e.g. the package/payment admin screen) — which
     * otherwise leaves the category membership ungranted and the user's role/menu
     * not reflecting the upgrade. Idempotent; safe to call on every read.
     */
    public function syncMembershipsFromActivePackages(int $userId): void
    {
        $this->ensure();
        try {
            if (!Schema::hasTable('user_packages')) {
                return;
            }
            $rows = DB::table('user_packages')
                ->where('user_id', $userId)->where('status', 'active')
                ->get(['category_code', 'package_id', 'expiry_date']);
            if ($rows->isEmpty()) {
                return;
            }
            $hasValidity = Schema::hasColumn('user_category_members', 'valid_till');
            $changed = false;
            foreach ($rows as $r) {
                $code = (string) ($r->category_code ?? '');
                if ($code === '') {
                    continue;
                }
                $exists = DB::table('user_category_members')
                    ->where('user_id', $userId)->where('category_code', $code)->first();
                $set = ['status' => 'active'];
                if ($hasValidity) {
                    $set['valid_till'] = $r->expiry_date ?? null;
                    $set['package_id'] = (int) ($r->package_id ?? 0) ?: null;
                }
                if ($exists) {
                    if ($exists->status !== 'active') {
                        DB::table('user_category_members')->where('id', $exists->id)->update($set);
                        $changed = true;
                    }
                } else {
                    DB::table('user_category_members')->insert(array_merge($set, [
                        'user_id'       => $userId,
                        'category_code' => $code,
                        'approved_at'   => now(),
                        'created_at'    => now(),
                    ]));
                    $changed = true;
                }
            }
            if ($changed) {
                $this->syncUserRole($userId);
                $this->reconcileLegacy($userId);
            }
        } catch (Throwable) {
            // best-effort; never break the read
        }
    }

    /**
     * Lightweight roles payload for the top-bar switcher:
     * base role + active category memberships (code + display name).
     */
    public function myRoles($user): array
    {
        $uid = (int) $user->id;
        $this->syncMembershipsFromActivePackages($uid);
        $codes = $this->memberships($uid);
        $cats = [];
        if ($codes) {
            try {
                $names = DB::table('user_categories')->whereIn('code', $codes)
                    ->pluck('name', 'code');
                foreach ($codes as $c) {
                    $cats[] = ['code' => $c, 'name' => $names[$c] ?? ucfirst($c)];
                }
            } catch (Throwable) {
                foreach ($codes as $c) {
                    $cats[] = ['code' => $c, 'name' => ucfirst($c)];
                }
            }
        }
        return [
            'base_role'  => (string) ($user->role ?? 'client'),
            'categories' => $cats,
        ];
    }

    /** The user's current in-progress request (kyc_pending or approval_pending), or null. */
    public function pending(int $userId): ?array
    {
        $this->ensure();
        try {
            $r = DB::table('category_upgrade_requests')
                ->where('user_id', $userId)->whereIn('status', self::OPEN)
                ->orderByDesc('id')->first();
            if (!$r) {
                return null;
            }
            $catName = null;
            try {
                $catName = DB::table('user_categories')->where('code', $r->category_code)->value('name');
            } catch (Throwable) {
            }
            return [
                'id'             => $r->id,
                'category_code'  => $r->category_code,
                'category_name'  => $catName ?: $r->category_code,
                'status'         => $r->status,
                'stage'          => $r->status === 'approval_pending' ? 'Awaiting category approval' : 'Awaiting KYC verification',
                'created_at'     => $r->created_at,
                'territory_code' => $r->territory_code ?? null,
                'territory_name' => $r->territory_name ?? null,
                'documents'      => $this->docsForRequest($r->id),
            ];
        } catch (Throwable) {
            return null;
        }
    }

    /**
     * Withdraw the user's own pending upgrade request so they can edit and
     * re-apply with different selections. Cancels the request, removes any
     * unpaid "applied" package mapping created for it, and frees the reserved
     * territory. Returns the withdrawn category code, or null if none pending.
     */
    public function withdraw($user): ?string
    {
        $this->ensure();
        $uid = (int) $user->id;
        try {
            $r = DB::table('category_upgrade_requests')
                ->where('user_id', $uid)->whereIn('status', self::OPEN)
                ->orderByDesc('id')->first();
            if (!$r) {
                return null;
            }
            DB::table('category_upgrade_requests')->where('id', $r->id)->update([
                'status'      => 'withdrawn',
                'reviewed_at' => now(),
            ]);
            // Drop an unpaid package mapping created for this application (keep paid ones).
            try {
                if (Schema::hasTable('user_packages')) {
                    $q = DB::table('user_packages')->where('user_id', $uid)->where('category_code', $r->category_code)->where('status', 'applied');
                    if (Schema::hasColumn('user_packages', 'payment_status')) {
                        $q->where('payment_status', '<>', 'paid');
                    }
                    $q->delete();
                }
            } catch (Throwable) {
            }
            Audit::log('upgrade', $r->id, 'withdrawn', null, ['user' => $uid, 'category' => $r->category_code]);
            return (string) $r->category_code;
        } catch (Throwable) {
            return null;
        }
    }

    /** Full self-service state for the profile page. */
    public function state($user): array
    {
        $uid = (int) $user->id;
        $this->syncMembershipsFromActivePackages($uid);
        $baseRole = (string) ($user->role ?? 'client');
        // The default "client / user" category is the baseline every account has:
        // it is always a current role and never offered as an upgrade to apply for.
        $have = array_merge($this->memberships($uid), [$baseRole, 'client', 'user', 'yajman']);
        $have = array_values(array_unique(array_filter($have)));

        $categories = [];
        try {
            $categories = DB::table('user_categories')
                ->when(Schema::hasColumn('user_categories', 'is_active'), fn ($q) => $q->where('is_active', 1))
                ->when(Schema::hasColumn('user_categories', 'deleted_at'), fn ($q) => $q->whereNull('deleted_at'))
                ->orderBy('name')->get()->all();
        } catch (Throwable) {
            $categories = [];
        }

        $pending = $this->pending($uid);
        $region = $this->regionFor($user);
        $options = [];
        foreach ($categories as $c) {
            $code = (string) ($c->code ?? '');
            if ($code === '' || in_array($code, $have, true)) {
                continue; // already held or base role
            }
            $split = $this->kyc->docsForCategory($code, $region);
            $options[] = [
                'code'      => $code,
                'name'      => $c->name ?? $code,
                'mandatory' => $split['mandatory'],
                'optional'  => $split['optional'],
                'packages'  => $this->packagesForCategory($code),
            ];
        }

        // human-friendly membership labels
        $memberNames = [];
        foreach ($this->memberships($uid) as $mc) {
            $nm = null;
            foreach ($categories as $c) {
                if ((string) ($c->code ?? '') === $mc) {
                    $nm = $c->name ?? $mc;
                    break;
                }
            }
            $memberNames[] = ['code' => $mc, 'name' => $nm ?? $mc];
        }

        return [
            'base_role'   => $baseRole,
            'memberships' => $memberNames,
            'pending'     => $pending,
            'options'     => $options,
            'can_apply'   => $pending === null && count($options) > 0,
        ];
    }

    /** Active packages designed for a category (for the apply form's picker). */
    public function packagesForCategory(string $category): array
    {
        try {
            if (!Schema::hasTable('packages')) {
                return [];
            }
            $q = DB::table('packages');
            if (Schema::hasColumn('packages', 'is_active')) {
                $q->where('is_active', 1);
            }
            if (Schema::hasColumn('packages', 'deleted_at')) {
                $q->whereNull('deleted_at');
            }
            if (Schema::hasColumn('packages', 'for_category')) {
                $q->where(fn ($w) => $w->where('for_category', $category)->orWhereNull('for_category')->orWhere('for_category', ''));
            }
            $cols = ['id', 'code', 'name'];
            foreach ([
                'description', 'price', 'price_usd', 'period_value', 'period_unit',
                'ai_hours', 'max_services', 'max_leads', 'comm_plan', 'platform_charge_pct',
            ] as $c) {
                if (Schema::hasColumn('packages', $c)) {
                    $cols[] = $c;
                }
            }
            // Order by price so the tier ladder (Free → Supreme) reads naturally.
            $orderCol = Schema::hasColumn('packages', 'price') ? 'price' : 'name';

            // Decorate each tier with the applicant's TERRITORY currency (INR India /
            // USD international, hard-locked by living city for signed-in users), so the
            // apply-form picker shows the same price the checkout will actually charge.
            \App\Services\Pricing\PriceBook::ensureSchema();
            $cur = \App\Services\Pricing\CurrencyResolver::resolve(request());
            return $q->orderBy($orderCol)->get($cols)->map(fn ($r) => \App\Services\Pricing\PriceBook::decorate(
                (array) $r, 'package', $r->id ?? null, $r->price ?? 0, $cur
            ))->all();
        } catch (Throwable) {
            return [];
        }
    }

    /** Create an "applied" (unpaid) package mapping tied to an upgrade application. */
    private function assignPackageForApplication(int $uid, string $category, int $packageId): void
    {
        try {
            if (!Schema::hasTable('user_packages')) {
                return;
            }
            $pkg = DB::table('packages')->where('id', $packageId)->first();
            if (!$pkg) {
                return;
            }
            // package must target this category (or be generic)
            $forCat = $pkg->for_category ?? null;
            if (!empty($forCat) && $forCat !== $category) {
                return;
            }
            $start = now()->toDateString();
            $expiry = null;
            $val = isset($pkg->period_value) ? (int) $pkg->period_value : 0;
            if ($val > 0) {
                $d = \Carbon\Carbon::parse($start);
                $unit = $pkg->period_unit ?? 'days';
                $expiry = ($unit === 'years' ? $d->addYears($val) : ($unit === 'months' ? $d->addMonths($val) : $d->addDays($val)))->toDateString();
            }
            // Free (₹0) packages require no payment — mark them paid so no payment
            // step is needed and approval can activate them directly.
            $isFree = !isset($pkg->price) || (float) $pkg->price <= 0;
            DB::table('user_packages')->insert([
                'user_id'        => $uid,
                'category_code'  => $category,
                'package_id'     => (int) $pkg->id,
                'package_code'   => $pkg->code ?? null,
                'start_date'     => $start,
                'expiry_date'    => $expiry,
                'status'         => 'applied',
                'payment_status' => $isFree ? 'paid' : 'pending',
                'payment_method' => $isFree ? 'free' : null,
                'created_by'     => $uid,
                'created_at'     => now(),
            ]);
        } catch (Throwable) {
        }
    }

    /**
     * Apply to upgrade to $category, attaching uploaded files keyed by doc code.
     * $files: [ docCode => ['file' => UploadedFile, 'number' => string|null] ]
     * Optionally selects a package ($packageId) for the new role — created as an
     * "applied" (unpaid) mapping the user can then pay for.
     * Enforces: one pending request at a time, all mandatory docs present.
     */
    /**
     * The applicant's territory for KYC purposes: 'IN' (India / INR) or 'INTL'
     * (international / USD). Uses the same currency signal as pricing so the two
     * always agree.
     */
    private function regionFor($user): string
    {
        try {
            $cur = \App\Services\Pricing\CurrencyResolver::lockedCurrencyFor($user);
            if ($cur === null) {
                $cur = strtoupper((string) ($user->currency ?? '')) ?: 'INR';
            }
            return $cur === 'USD' ? 'INTL' : 'IN';
        } catch (\Throwable) {
            return 'IN';
        }
    }

    /** regionFor() by user id (loads the row); defaults to 'IN' when unknown. */
    private function regionForUserId(int $uid): string
    {
        try {
            $u = DB::table('users')->where('id', $uid)->first();
            return $u ? $this->regionFor($u) : 'IN';
        } catch (\Throwable) {
            return 'IN';
        }
    }

    public function apply($user, string $category, array $files, ?int $packageId = null, ?string $territoryCode = null, ?string $territoryName = null): array
    {
        $this->ensure();
        $uid = (int) $user->id;
        $role = (string) ($user->role ?? 'client');
        $category = trim($category);

        if ($this->pending($uid) !== null) {
            throw new \RuntimeException('You already have an upgrade request under review. Please wait for it to be reviewed.');
        }
        if (in_array($category, array_merge($this->memberships($uid), [$role, 'client']), true)) {
            throw new \RuntimeException('You already hold this category.');
        }
        // category must be a valid active user category
        $catRow = DB::table('user_categories')
            ->where('code', $category)
            ->when(Schema::hasColumn('user_categories', 'is_active'), fn ($q) => $q->where('is_active', 1))
            ->first();
        if (!$catRow) {
            throw new \RuntimeException('Unknown user category.');
        }

        // Franchise applications must include a geographic territory, and it must
        // not already be taken by another franchise or reserved by another pending
        // application (exclusive).
        $territoryCode = trim((string) $territoryCode);
        $territoryName = trim((string) $territoryName);
        $isFranchise = (bool) preg_match('/franc|partner|fpr|prabandh/i', $category . ' ' . (string) ($catRow->name ?? ''));
        if ($isFranchise) {
            if ($territoryCode === '' || $territoryName === '') {
                throw new \RuntimeException('Please select a franchise territory to apply.');
            }
            // A franchise partner is assigned at STATE level only — one state, one
            // franchise. Reject a non-state node (country / district / city) if the
            // geographic master recognises the code.
            try {
                if (Schema::hasTable('geographic_nodes')) {
                    $lvl = DB::table('geographic_nodes')->where('code', $territoryCode)->value('level');
                    if ($lvl !== null && strtoupper((string) $lvl) !== 'STATE') {
                        throw new \RuntimeException('Please select a state as your franchise territory.');
                    }
                }
            } catch (\RuntimeException $e) {
                throw $e;
            } catch (Throwable) {
            }
            $key = strtolower($territoryCode);
            $takenActive = Schema::hasTable('franchise_territories') && DB::table('franchise_territories')
                ->where('territory_key', $key)->where('is_active', 1)->where('franchise_user_id', '<>', $uid)->exists();
            $takenPending = DB::table('category_upgrade_requests')
                ->whereIn('status', self::OPEN)->where('territory_code', $territoryCode)->where('user_id', '<>', $uid)->exists();
            if ($takenActive || $takenPending) {
                throw new \RuntimeException('This territory is already assigned to (or requested by) another franchise. Please choose a different one.');
            }
        } else {
            $territoryCode = '';
            $territoryName = '';
        }

        $applicantRegion = $this->regionFor($user);
        $split = $this->kyc->docsForCategory($category, $applicantRegion);
        $mandatoryCodes = array_map(fn ($d) => (string) $d['code'], $split['mandatory']);

        // every mandatory doc must have an uploaded file
        foreach ($mandatoryCodes as $code) {
            if (empty($files[$code]['file']) || !($files[$code]['file'] instanceof UploadedFile)) {
                throw new \RuntimeException("Mandatory document missing: {$code}");
            }
        }
        if (empty($files)) {
            throw new \RuntimeException('No documents were attached.');
        }

        // Point 4: a role upgrade always carries at least the category's FREE
        // package by default when the applicant did not pick one.
        if (!$packageId) {
            $packageId = $this->freePackageIdFor($category);
        }

        return DB::transaction(function () use ($uid, $role, $category, $files, $packageId, $territoryCode, $territoryName) {
            $reqData = [
                'user_id'       => $uid,
                'category_code' => $category,
                'status'        => 'kyc_pending', // first stage: documents await verification
                'created_at'    => now(),
            ];
            if ($territoryCode !== '' && Schema::hasColumn('category_upgrade_requests', 'territory_code')) {
                $reqData['territory_code'] = $territoryCode;
                $reqData['territory_name'] = $territoryName;
            }
            $requestId = DB::table('category_upgrade_requests')->insertGetId($reqData);

            $saved = 0;
            foreach ($files as $code => $entry) {
                $file = $entry['file'] ?? null;
                if (!($file instanceof UploadedFile)) {
                    continue;
                }
                $doc = $this->kyc->upload($uid, $category, (string) $code, (string) ($entry['number'] ?? ''), $file);
                // tag the freshly created doc with the applied category + request
                if (!empty($doc['id'])) {
                    DB::table('user_documents')->where('id', $doc['id'])->update([
                        'category'   => $category,
                        'request_id' => $requestId,
                    ]);
                    $saved++;
                }
            }

            // Optional: the applicant chose a package for the new role — create it
            // as an "applied" (unpaid) mapping they can pay for right away.
            $packageMapId = null;
            if ($packageId) {
                $this->assignPackageForApplication($uid, $category, (int) $packageId);
                $packageMapId = (int) DB::table('user_packages')
                    ->where('user_id', $uid)->where('category_code', $category)->where('package_id', (int) $packageId)
                    ->orderByDesc('id')->value('id');
            }

            Audit::log('upgrade', $requestId, 'apply', null, ['user' => $uid, 'category' => $category, 'docs' => $saved, 'package' => $packageId]);

            return [
                'request_id'     => $requestId,
                'category'       => $category,
                'documents'      => $saved,
                'status'         => 'kyc_pending',
                'package_map_id' => $packageMapId,
            ];
        });
    }

    /**
     * Re-evaluate a request's KYC stage after a document is reviewed.
     * When ALL mandatory documents for the request's category are verified, the
     * request advances from `kyc_pending` → `approval_pending` (so it surfaces
     * in the category's approval queue). If a mandatory document is rejected,
     * the request is rejected so the applicant can re-apply.
     * Returns the (possibly unchanged) new status.
     */
    public function syncKycStage(int $requestId): ?string
    {
        $this->ensure();
        try {
            $req = DB::table('category_upgrade_requests')->where('id', $requestId)->first();
            if (!$req || $req->status !== 'kyc_pending') {
                return $req->status ?? null; // only advances out of the KYC stage
            }
            $mandatory = array_map(
                fn ($d) => (string) $d['code'],
                $this->kyc->docsForCategory((string) $req->category_code, $this->regionForUserId((int) $req->user_id))['mandatory'],
            );
            if (empty($mandatory)) {
                // nothing mandatory → straight to approval queue
                DB::table('category_upgrade_requests')->where('id', $requestId)
                    ->update(['status' => 'approval_pending']);
                return 'approval_pending';
            }

            $docs = DB::table('user_documents')->where('request_id', $requestId)
                ->whereIn('doc_type', $mandatory)->get(['doc_type', 'status']);
            $byType = [];
            foreach ($docs as $d) {
                $byType[$d->doc_type] = $d->status;
            }
            // any mandatory doc rejected → reject the whole application
            foreach ($mandatory as $code) {
                if (($byType[$code] ?? null) === 'rejected') {
                    DB::table('category_upgrade_requests')->where('id', $requestId)
                        ->update(['status' => 'rejected', 'note' => 'A required document was rejected during KYC verification.', 'reviewed_at' => now()]);
                    Audit::log('upgrade', $requestId, 'kyc_rejected', null, ['category' => $req->category_code]);
                    return 'rejected';
                }
            }
            // all mandatory verified → advance to category approval
            $allVerified = true;
            foreach ($mandatory as $code) {
                if (($byType[$code] ?? null) !== 'verified') {
                    $allVerified = false;
                    break;
                }
            }
            if ($allVerified) {
                DB::table('category_upgrade_requests')->where('id', $requestId)
                    ->update(['status' => 'approval_pending']);
                Audit::log('upgrade', $requestId, 'kyc_verified', null, ['category' => $req->category_code]);
                return 'approval_pending';
            }
            return 'kyc_pending';
        } catch (Throwable) {
            return null;
        }
    }

    /**
     * Admin: directly set a user's active category memberships (User Master).
     * Adds new codes, deactivates removed ones. Codes validated against
     * user_categories. Returns the active codes after the sync.
     */
    public function setMemberships(int $userId, array $codes, int $actorId): array
    {
        $this->ensure();
        // keep only valid, active category codes
        $valid = [];
        try {
            $valid = DB::table('user_categories')
                ->when(Schema::hasColumn('user_categories', 'is_active'), fn ($q) => $q->where('is_active', 1))
                ->pluck('code')->map(fn ($c) => (string) $c)->all();
        } catch (Throwable) {
            $valid = [];
        }
        $want = array_values(array_unique(array_filter(array_map('strval', $codes), fn ($c) => in_array($c, $valid, true))));

        try {
            $current = DB::table('user_category_members')->where('user_id', $userId)->get(['id', 'category_code', 'status']);
            $have = [];
            foreach ($current as $row) {
                $have[$row->category_code] = $row;
            }
            // add / reactivate
            foreach ($want as $code) {
                if (isset($have[$code])) {
                    if ($have[$code]->status !== 'active') {
                        DB::table('user_category_members')->where('id', $have[$code]->id)
                            ->update(['status' => 'active', 'approved_by' => $actorId, 'approved_at' => now()]);
                    }
                } else {
                    DB::table('user_category_members')->insert([
                        'user_id' => $userId, 'category_code' => $code, 'status' => 'active',
                        'approved_by' => $actorId, 'approved_at' => now(), 'created_at' => now(),
                    ]);
                }
            }
            // deactivate removed
            foreach ($have as $code => $row) {
                if (!in_array($code, $want, true) && $row->status === 'active') {
                    DB::table('user_category_members')->where('id', $row->id)->update(['status' => 'inactive']);
                }
            }
            Audit::log('users', $userId, 'set_categories', null, ['categories' => $want]);
            // Category is the source of truth for role — sync users.role from the
            // assigned categories' applies_role mapping (admins are protected).
            $this->syncUserRole($userId);
            // Auto-manage the legacy type rows (pandit/vendor/yajman) from the new
            // category set (Phase 4).
            $this->reconcileLegacy($userId);
        } catch (Throwable) {
            // ignore; best-effort
        }
        return $want;
    }

    /**
     * Map a category's `applies_role` to a base `users.role`. Only NON-privileged
     * roles are auto-derivable — a category can never grant admin (super_admin /
     * trust_manager) to prevent privilege escalation via category assignment.
     */
    private const APPLIES_ROLE_MAP = [
        'pandit'  => 'pandit',
        'vendor'  => 'vendor',
        'partner' => 'partner',
        'yajman'  => 'client',
        'client'  => 'client',
        // 'staff' / 'any' deliberately omitted — no auto-role change.
    ];

    private const ROLE_PRIORITY = ['pandit' => 4, 'partner' => 3, 'vendor' => 2, 'client' => 1];

    /**
     * The base role a set of category codes maps to (highest-privilege wins), or
     * null when none of them carry a derivable role.
     */
    public function roleForCategories(array $codes): ?string
    {
        if (!$codes) {
            return null;
        }
        try {
            $applies = DB::table('user_categories')
                ->whereIn('code', $codes)
                ->when(Schema::hasColumn('user_categories', 'applies_role'), fn ($q) => $q, fn ($q) => $q)
                ->pluck('applies_role')->map(fn ($r) => (string) $r)->all();
        } catch (Throwable) {
            return null;
        }
        $best = null;
        $bestP = 0;
        foreach ($applies as $ar) {
            $role = self::APPLIES_ROLE_MAP[$ar] ?? null;
            if ($role === null) {
                continue;
            }
            $p = self::ROLE_PRIORITY[$role] ?? 0;
            if ($p > $bestP) {
                $bestP = $p;
                $best = $role;
            }
        }
        return $best;
    }

    /**
     * Sync a user's base role from their ACTIVE category memberships. Admins
     * (super_admin / trust_manager) are never changed here; AI users are left
     * as-is (their role is managed by the AI master).
     */
    public function syncUserRole(int $userId): void
    {
        try {
            $u = DB::table('users')->where('id', $userId)->first(['id', 'role', 'is_ai']);
            if (!$u) {
                return;
            }
            $cur = (string) ($u->role ?? '');
            if (in_array($cur, ['super_admin', 'trust_manager'], true)) {
                return; // protect admins
            }
            $role = $this->roleForCategories($this->activeCategoryCodes($userId));
            if ($role !== null && $role !== $cur) {
                DB::table('users')->where('id', $userId)->update(['role' => $role]);
            }
        } catch (Throwable) {
        }
    }

    /**
     * A user's ACTIVE, NON-EXPIRED category codes. A membership is effective when
     * status='active' and (valid_till is null OR valid_till >= today). USR and
     * admin-granted categories have a null valid_till, so they never expire.
     */
    public function activeCategoryCodes(int $userId): array
    {
        try {
            $q = DB::table('user_category_members')->where('user_id', $userId)->where('status', 'active');
            if (Schema::hasColumn('user_category_members', 'valid_till')) {
                $today = now()->toDateString();
                $q->where(fn ($w) => $w->whereNull('valid_till')->orWhere('valid_till', '>=', $today));
            }
            return $q->pluck('category_code')->map(fn ($c) => (string) $c)->all();
        } catch (Throwable) {
            return [];
        }
    }

    /**
     * Expire packaged categories whose validity has passed: deactivate the
     * membership, guarantee the user keeps USR, and re-derive their role (which
     * drops them back to User / Client when no packaged category remains).
     * Returns the number of memberships expired.
     */
    public function expireMemberships(): int
    {
        $this->ensure();
        if (!Schema::hasColumn('user_category_members', 'valid_till')) {
            return 0;
        }
        $n = 0;
        try {
            $today = now()->toDateString();
            $due = DB::table('user_category_members')
                ->where('status', 'active')
                ->whereNotNull('valid_till')->where('valid_till', '<', $today)
                ->get(['id', 'user_id', 'category_code']);
            $users = [];
            foreach ($due as $row) {
                DB::table('user_category_members')->where('id', $row->id)->update(['status' => 'inactive']);
                $users[(int) $row->user_id] = true;
                $n++;
                Audit::log('users', (int) $row->user_id, 'category_expired', null, ['category' => $row->category_code]);
            }
            foreach (array_keys($users) as $uid) {
                // Guarantee the USR (User / Client) fallback membership.
                try {
                    $hasUsr = DB::table('user_category_members')->where('user_id', $uid)
                        ->where('category_code', 'USR')->where('status', 'active')->exists();
                    if (!$hasUsr) {
                        $existing = DB::table('user_category_members')->where('user_id', $uid)->where('category_code', 'USR')->first();
                        if ($existing) {
                            DB::table('user_category_members')->where('id', $existing->id)->update(['status' => 'active']);
                        } else {
                            DB::table('user_category_members')->insert([
                                'user_id' => $uid, 'category_code' => 'USR', 'status' => 'active', 'created_at' => now(),
                            ]);
                        }
                    }
                } catch (Throwable) {
                }
                $this->syncUserRole($uid);
                // Retire the legacy type rows the expired category justified (Phase 4).
                $this->reconcileLegacy($uid);
            }
        } catch (Throwable) {
        }
        return $n;
    }

    /** Auto-manage a user's legacy type rows (pandit/vendor/yajman) — Phase 4. */
    private function reconcileLegacy(int $userId): void
    {
        try {
            app(\App\Services\Profile\ProfileProvisioningService::class)->reconcile($userId);
        } catch (Throwable) {
        }
    }

    /**
     * Opportunistic expiry — runs the expiry sweep at most once per day without
     * needing a cron. A server cron can still call the `vpandit:expire-categories`
     * command for punctual expiry.
     */
    public function maybeExpire(): void
    {
        try {
            if (!Schema::hasColumn('user_category_members', 'valid_till')) {
                return;
            }
            $today = now()->toDateString();
            $last = (string) \App\Models\Setting::get('category_expiry_last_run', '');
            if ($last === $today) {
                return;
            }
            \App\Models\Setting::put('category_expiry_last_run', $today, 'system');
            $this->expireMemberships();
        } catch (Throwable) {
        }
    }

    /**
     * Give an AI backing user the PND (Pandit) category membership. The is_ai
     * flag on the user marks it as AI; role is left as the AI master set it.
     */
    public function ensureAiPanditMembership(int $userId): void
    {
        try {
            $this->ensure();
            if (Schema::hasTable('user_categories') && !DB::table('user_categories')->where('code', 'PND')->exists()) {
                $row = ['code' => 'PND', 'name' => 'Pandit'];
                if (Schema::hasColumn('user_categories', 'applies_role')) {
                    $row['applies_role'] = 'pandit';
                }
                if (Schema::hasColumn('user_categories', 'is_active')) {
                    $row['is_active'] = 1;
                }
                if (Schema::hasColumn('user_categories', 'created_at')) {
                    $row['created_at'] = now();
                }
                DB::table('user_categories')->insert($row);
            }
            $has = DB::table('user_category_members')->where('user_id', $userId)->where('category_code', 'PND')->exists();
            if (!$has) {
                DB::table('user_category_members')->insert([
                    'user_id' => $userId, 'category_code' => 'PND', 'status' => 'active', 'created_at' => now(),
                ]);
            }
        } catch (Throwable) {
        }
    }

    /** Active category codes for many users at once (User Master list). */
    public function membershipsForUsers(array $userIds): array
    {
        $this->ensure();
        $this->maybeExpire(); // opportunistic daily expiry sweep (no cron needed)
        try {
            app(\App\Services\Profile\ProfileProvisioningService::class)->maybeReconcile();
        } catch (Throwable) {
        }
        $out = [];
        if (empty($userIds)) {
            return $out;
        }
        try {
            $rows = DB::table('user_category_members')
                ->whereIn('user_id', $userIds)->where('status', 'active')
                ->get(['user_id', 'category_code']);
            foreach ($rows as $r) {
                $out[(int) $r->user_id][] = (string) $r->category_code;
            }
        } catch (Throwable) {
        }
        return $out;
    }

    /** Documents attached to a request (for user + admin views). */
    public function docsForRequest(int $requestId): array
    {
        try {
            if (!Schema::hasColumn('user_documents', 'request_id')) {
                return [];
            }
            return DB::table('user_documents')->where('request_id', $requestId)
                ->orderBy('id')->get()->map(fn ($d) => [
                    'id'       => $d->id,
                    'doc_type' => $d->doc_type,
                    'file_url' => $d->file_url,
                    'status'   => $d->status,
                ])->all();
        } catch (Throwable) {
            return [];
        }
    }

    /** Admin: upgrade requests with applicant + documents (filter by status/category). */
    public function queue(?string $status = 'pending', ?string $category = null): array
    {
        $this->ensure();
        try {
            $q = DB::table('category_upgrade_requests');
            if ($status && $status !== 'all') {
                $q->where('status', $status);
            }
            if ($category !== null && $category !== '') {
                $q->where('category_code', $category);
            }
            return $q->orderByDesc('id')->limit(200)->get()->map(function ($r) {
                $u = DB::table('users')->where('id', $r->user_id)->first(['name', 'email', 'phone']);
                $cat = DB::table('user_categories')->where('code', $r->category_code)->first(['name']);
                // The package chosen for this application (newest for the category).
                $package = null;
                try {
                    if (Schema::hasTable('user_packages')) {
                        $up = DB::table('user_packages as up')
                            ->leftJoin('packages as p', 'p.id', '=', 'up.package_id')
                            ->where('up.user_id', $r->user_id)->where('up.category_code', $r->category_code)
                            ->orderByDesc('up.id')
                            ->first(['up.status', 'up.payment_status', 'p.name as package_name', 'p.price as package_price']);
                        if ($up) {
                            $package = [
                                'name'           => $up->package_name,
                                'price'          => $up->package_price,
                                'is_free'        => $up->package_price === null || (float) $up->package_price <= 0,
                                'status'         => $up->status,
                                'payment_status' => $up->payment_status,
                            ];
                        }
                    }
                } catch (Throwable) {
                }
                return [
                    'id'             => $r->id,
                    'user_id'        => $r->user_id,
                    'user_name'      => $u->name ?? null,
                    'user_email'     => $u->email ?? null,
                    'user_phone'     => $u->phone ?? null,
                    'category_code'  => $r->category_code,
                    'category_name'  => $cat->name ?? $r->category_code,
                    'territory_code' => $r->territory_code ?? null,
                    'territory_name' => $r->territory_name ?? null,
                    'package'        => $package,
                    'status'         => $r->status,
                    'note'           => $r->note,
                    'created_at'     => $r->created_at,
                    'documents'      => $this->docsForRequest($r->id),
                ];
            })->all();
        } catch (Throwable) {
            return [];
        }
    }

    /**
     * Admin: approve → grant membership (+ mark docs verified); reject → note.
     */
    public function review(int $id, string $decision, string $note, int $reviewerId): bool
    {
        $this->ensure();
        $decision = in_array($decision, ['approved', 'rejected'], true) ? $decision : 'rejected';
        $req = DB::table('category_upgrade_requests')->where('id', $id)->first();
        // Final category approval only applies once KYC is verified. A reject can
        // also be issued while still in the KYC stage.
        if (!$req || !in_array($req->status, self::OPEN, true)) {
            return false;
        }
        if ($decision === 'approved' && $req->status !== 'approval_pending') {
            return false; // cannot approve before KYC verification completes
        }

        return DB::transaction(function () use ($req, $id, $decision, $note, $reviewerId) {
            DB::table('category_upgrade_requests')->where('id', $id)->update([
                'status'      => $decision,
                'note'        => $note !== '' ? $note : null,
                'reviewed_by' => $reviewerId,
                'reviewed_at' => now(),
            ]);

            $docStatus = $decision === 'approved' ? 'verified' : 'rejected';
            if (Schema::hasColumn('user_documents', 'request_id')) {
                DB::table('user_documents')->where('request_id', $id)->update([
                    'status'      => $docStatus,
                    'reviewed_by' => $reviewerId,
                    'reviewed_at' => now(),
                ]);
            }

            if ($decision === 'approved') {
                // Package-driven validity: the category is valid until the linked
                // package ends. Pull the latest package's expiry for this category.
                $validTill = null;
                $packageId = null;
                try {
                    if (Schema::hasTable('user_packages')) {
                        $pkg = DB::table('user_packages')
                            ->where('user_id', $req->user_id)->where('category_code', $req->category_code)
                            ->orderByDesc('id')->first();
                        if ($pkg) {
                            $validTill = $pkg->expiry_date ?? null;
                            $packageId = (int) ($pkg->package_id ?? 0) ?: null;
                            // Mark the package approved/active now that it's granted.
                            $upd = ['approved_by' => $reviewerId, 'approved_at' => now()];
                            if (Schema::hasColumn('user_packages', 'status')) {
                                $upd['status'] = 'active';
                            }
                            // Free (₹0) packages need no payment — force them paid so
                            // they activate and are never reverted to "applied".
                            $price = null;
                            try {
                                $price = DB::table('packages')->where('id', $pkg->package_id)->value('price');
                            } catch (Throwable) {
                            }
                            if (($price === null || (float) $price <= 0) && Schema::hasColumn('user_packages', 'payment_status')) {
                                $upd['payment_status'] = 'paid';
                                if (Schema::hasColumn('user_packages', 'payment_method')) {
                                    $upd['payment_method'] = 'free';
                                }
                            }
                            DB::table('user_packages')->where('id', $pkg->id)->update($upd);
                            // One active package per category + bonus cascade (points 6-7).
                            $this->onPackageActivated((int) $pkg->id);
                        }
                    }
                } catch (Throwable) {
                }

                $hasValidity = Schema::hasColumn('user_category_members', 'valid_till');
                // grant the membership (idempotent on the unique key)
                $exists = DB::table('user_category_members')
                    ->where('user_id', $req->user_id)->where('category_code', $req->category_code)->first();
                $set = ['status' => 'active', 'approved_by' => $reviewerId, 'approved_at' => now()];
                if ($hasValidity) {
                    $set['valid_till'] = $validTill;
                    $set['package_id'] = $packageId;
                }
                if ($exists) {
                    DB::table('user_category_members')->where('id', $exists->id)->update($set);
                } else {
                    DB::table('user_category_members')->insert(array_merge($set, [
                        'user_id'       => $req->user_id,
                        'category_code' => $req->category_code,
                        'created_at'    => now(),
                    ]));
                }
                // Assign the requested geographic territory (exclusive) if one was
                // chosen at apply time and it is still free.
                $terrCode = (string) ($req->territory_code ?? '');
                $terrName = (string) ($req->territory_name ?? '');
                if ($terrCode !== '') {
                    try {
                        if (!Schema::hasTable('franchise_territories')) {
                            DB::statement("CREATE TABLE IF NOT EXISTS `franchise_territories` (
                                `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                                `territory_key` VARCHAR(160) NOT NULL,
                                `territory_name` VARCHAR(160) NOT NULL,
                                `state` VARCHAR(80) DEFAULT NULL,
                                `franchise_user_id` INT UNSIGNED NOT NULL,
                                `franchise_code` VARCHAR(40) DEFAULT NULL,
                                `assigned_by` INT UNSIGNED DEFAULT NULL,
                                `assigned_at` DATETIME DEFAULT NULL,
                                `is_active` TINYINT(1) NOT NULL DEFAULT 1,
                                `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                                `updated_at` DATETIME DEFAULT NULL,
                                UNIQUE KEY `uniq_territory` (`territory_key`),
                                UNIQUE KEY `uniq_franchise` (`franchise_user_id`)
                            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                        }
                        $key = strtolower($terrCode);
                        $free = !DB::table('franchise_territories')->where('territory_key', $key)
                            ->where('is_active', 1)->where('franchise_user_id', '<>', $req->user_id)->exists();
                        if ($free) {
                            $fcode = DB::table('users')->where('id', $req->user_id)->value('franchise_code');
                            $trow = [
                                'territory_key' => $key, 'territory_name' => $terrName ?: $terrCode,
                                'franchise_code' => $fcode ?: null, 'assigned_by' => $reviewerId,
                                'assigned_at' => now(), 'is_active' => 1, 'updated_at' => now(),
                            ];
                            $ex = DB::table('franchise_territories')->where('franchise_user_id', $req->user_id)->first();
                            if ($ex) {
                                DB::table('franchise_territories')->where('id', $ex->id)->update($trow);
                            } else {
                                DB::table('franchise_territories')->insert($trow + ['franchise_user_id' => $req->user_id, 'created_at' => now()]);
                            }
                        }
                    } catch (Throwable) {
                    }
                }
                // Reflect the new (possibly higher-privilege) category in the role.
                $this->syncUserRole((int) $req->user_id);
                // Provision the legacy type row this category grants (Phase 4).
                $this->reconcileLegacy((int) $req->user_id);
            }

            Audit::log('upgrade', $id, $decision, null, ['category' => $req->category_code, 'user' => $req->user_id]);
            return true;
        });
    }
    /* ============ Package lifecycle: free default, change-package, bonus cascade (points 1-7) ============ */

    /** Tier rank from a package code suffix (AARAMBH<BASIC<PREMIUM<SUPREME); 0 if unknown. */
    private function tierFromCode(?string $code): int
    {
        $c = strtoupper((string) $code);
        foreach (['AARAMBH' => 1, 'BASIC' => 2, 'PREMIUM' => 3, 'SUPREME' => 4] as $suf => $rank) {
            if (str_contains($c, $suf)) {
                return $rank;
            }
        }
        return 0;
    }

    /** Active packages for a category keyed by tier rank (1..4); falls back to price order. */
    private function packagesByTier(string $category): array
    {
        $out = [];
        try {
            if (!Schema::hasTable('packages')) {
                return $out;
            }
            $q = DB::table('packages');
            if (Schema::hasColumn('packages', 'is_active')) {
                $q->where('is_active', 1);
            }
            if (Schema::hasColumn('packages', 'deleted_at')) {
                $q->whereNull('deleted_at');
            }
            if (Schema::hasColumn('packages', 'for_category')) {
                $q->where('for_category', $category);
            }
            $rows = $q->get(['id', 'code', 'name', 'price', 'period_value', 'period_unit']);
            $byTier = [];
            foreach ($rows as $r) {
                $t = $this->tierFromCode($r->code);
                if ($t > 0) {
                    $byTier[$t] = $r;
                }
            }
            if (!$byTier && $rows->count()) {
                $sorted = $rows->sortBy(fn ($r) => (float) ($r->price ?? 0))->values();
                foreach ($sorted as $i => $r) {
                    $byTier[$i + 1] = $r;
                }
            }
            $out = $byTier;
        } catch (Throwable) {
        }
        return $out;
    }

    /** The free (lowest-tier / zero-price) package id for a category, or null. */
    public function freePackageIdFor(string $category): ?int
    {
        try {
            if (!Schema::hasTable('packages')) {
                return null;
            }
            $q = DB::table('packages');
            if (Schema::hasColumn('packages', 'is_active')) {
                $q->where('is_active', 1);
            }
            if (Schema::hasColumn('packages', 'deleted_at')) {
                $q->whereNull('deleted_at');
            }
            if (Schema::hasColumn('packages', 'for_category')) {
                $q->where('for_category', $category);
            }
            $free = (clone $q)->where(fn ($w) => $w->whereNull('price')->orWhere('price', '<=', 0))->orderBy('price')->first(['id']);
            if ($free) {
                return (int) $free->id;
            }
            $low = $q->orderBy('price')->first(['id']);
            return $low ? (int) $low->id : null;
        } catch (Throwable) {
            return null;
        }
    }

    /** Hierarchy rank (sort_order) of a category; higher = more privileged. */
    private function categoryRank(string $category): int
    {
        try {
            if (Schema::hasTable('user_categories') && Schema::hasColumn('user_categories', 'sort_order')) {
                $v = DB::table('user_categories')->where('code', $category)->value('sort_order');
                if ($v !== null) {
                    return (int) $v;
                }
            }
        } catch (Throwable) {
        }
        return 0;
    }

    /** The user's current ACTIVE package row for a category, or null. */
    private function activePackageRow(int $uid, string $category)
    {
        try {
            return DB::table('user_packages')->where('user_id', $uid)->where('category_code', $category)
                ->where('status', 'active')->orderByDesc('id')->first();
        } catch (Throwable) {
            return null;
        }
    }

    /**
     * Assign a package to a user as ACTIVE immediately (free signup package or a
     * bonus grant). Retires any previously-active package for the same category
     * (one active package per category). Idempotent on (user, category, package).
     */
    public function assignActivePackage(int $uid, string $category, int $packageId, bool $free = false, ?string $expiry = null, string $method = 'free'): void
    {
        try {
            if (!Schema::hasTable('user_packages')) {
                return;
            }
            $pkg = DB::table('packages')->where('id', $packageId)->first();
            if (!$pkg) {
                return;
            }
            $already = DB::table('user_packages')->where('user_id', $uid)->where('category_code', $category)
                ->where('package_id', $packageId)->where('status', 'active')->exists();
            if ($already) {
                return;
            }
            DB::table('user_packages')->where('user_id', $uid)->where('category_code', $category)
                ->where('status', 'active')->update(['status' => 'replaced', 'updated_at' => now()]);
            $start = now()->toDateString();
            if ($expiry === null) {
                $val = isset($pkg->period_value) ? (int) $pkg->period_value : 0;
                if ($val > 0) {
                    $d = \Carbon\Carbon::parse($start);
                    $unit = $pkg->period_unit ?? 'days';
                    $expiry = ($unit === 'years' ? $d->addYears($val) : ($unit === 'months' ? $d->addMonths($val) : $d->addDays($val)))->toDateString();
                }
            }
            DB::table('user_packages')->insert([
                'user_id'        => $uid,
                'category_code'  => $category,
                'package_id'     => (int) $pkg->id,
                'package_code'   => $pkg->code ?? null,
                'start_date'     => $start,
                'expiry_date'    => $expiry,
                'status'         => 'active',
                'payment_status' => 'paid',
                'payment_method' => $method,
                'approved_at'    => now(),
                'paid_at'        => now(),
                'created_by'     => $uid,
                'created_at'     => now(),
                'updated_at'     => now(),
            ]);
            $this->syncMembershipsFromActivePackages($uid);
        } catch (Throwable) {
        }
    }

    /** Ensure a user holds the free package for a category (signup / role default). */
    public function assignFreePackage(int $uid, string $category): void
    {
        try {
            if (Schema::hasTable('user_packages')) {
                $has = DB::table('user_packages')->where('user_id', $uid)->where('category_code', $category)
                    ->whereIn('status', ['active', 'applied'])->exists();
                if ($has) {
                    return;
                }
            }
            $pid = $this->freePackageIdFor($category);
            if ($pid) {
                $this->assignActivePackage($uid, $category, $pid, true, null, 'free');
            }
        } catch (Throwable) {
        }
    }

    /**
     * Post-activation hook: when a user_package becomes ACTIVE, enforce one active
     * package per category and cascade the same tier as a free bonus to every LOWER
     * category the user already holds (points 6-7). Idempotent; safe to call often.
     */
    public function onPackageActivated(int $mapId): void
    {
        try {
            $row = DB::table('user_packages')->where('id', $mapId)->first();
            if (!$row || ($row->status ?? '') !== 'active') {
                return;
            }
            $uid = (int) $row->user_id;
            $cat = (string) $row->category_code;
            DB::table('user_packages')->where('user_id', $uid)->where('category_code', $cat)
                ->where('status', 'active')->where('id', '<>', $mapId)
                ->update(['status' => 'replaced', 'updated_at' => now()]);
            $this->syncMembershipsFromActivePackages($uid);
            $this->cascadeBonus($uid, $cat, (int) $row->package_id, $row->expiry_date ?? null);
        } catch (Throwable) {
        }
    }

    /**
     * Bonus cascade (points 6-7): activating a PAID tier-T package in a higher
     * category grants the SAME tier, free, in every LOWER category the user already
     * holds - upgrade-only, never downgrade, never adding a category they lack.
     */
    public function cascadeBonus(int $uid, string $triggerCategory, int $triggerPackageId, ?string $expiry): void
    {
        try {
            $pkg = DB::table('packages')->where('id', $triggerPackageId)->first();
            if (!$pkg) {
                return;
            }
            if (!isset($pkg->price) || (float) $pkg->price <= 0) {
                return; // a free package grants no bonus
            }
            $tier = $this->tierFromCode($pkg->code);
            if ($tier <= 0) {
                foreach ($this->packagesByTier($triggerCategory) as $t => $p) {
                    if ((int) $p->id === $triggerPackageId) {
                        $tier = $t;
                        break;
                    }
                }
            }
            if ($tier <= 0) {
                return;
            }
            $triggerRank = $this->categoryRank($triggerCategory);
            if ($triggerRank <= 0) {
                return;
            }
            foreach ($this->activeCategoryCodes($uid) as $cat) {
                if ($cat === $triggerCategory) {
                    continue;
                }
                $rank = $this->categoryRank($cat);
                if ($rank <= 0 || $rank >= $triggerRank) {
                    continue; // only strictly-lower categories receive the bonus
                }
                $byTier = $this->packagesByTier($cat);
                $target = $byTier[$tier] ?? null;
                if (!$target) {
                    continue;
                }
                $curRow = $this->activePackageRow($uid, $cat);
                if ($curRow) {
                    $curTier = $this->tierFromCode($curRow->package_code);
                    if ($curTier <= 0) {
                        foreach ($byTier as $t => $p) {
                            if ((int) $p->id === (int) $curRow->package_id) {
                                $curTier = $t;
                                break;
                            }
                        }
                    }
                    if ($curTier >= $tier) {
                        continue; // already same/higher - never downgrade
                    }
                }
                $this->assignActivePackage($uid, $cat, (int) $target->id, true, $expiry, 'bonus');
                Audit::log('user_packages', (int) $target->id, 'bonus_cascade', null, ['user' => $uid, 'category' => $cat, 'tier' => $tier, 'from' => $triggerCategory]);
            }
        } catch (Throwable) {
        }
    }

}