<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Services\Upgrade\UpgradeService;
use App\Support\Audit;
use Carbon\Carbon;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Throwable;

/**
 * User ↔ Category ↔ Package mapping.
 *
 * A package is designed for one user category (packages.for_category). A user
 * who holds that category can be assigned a matching package on their profile,
 * with a start date; the expiry is computed from the package period
 * (period_value + period_unit). A user may hold several categories and each
 * category may carry its own package, so a user can have multiple active
 * packages at once.
 */
class UserPackageController extends Controller
{
    public function __construct(private readonly UpgradeService $upgrade)
    {
    }

    private function ensure(): void
    {
        try {
            if (!Schema::hasTable('user_packages')) {
                DB::statement("CREATE TABLE IF NOT EXISTS `user_packages` (
                    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    `user_id` INT UNSIGNED NOT NULL,
                    `category_code` VARCHAR(40) NOT NULL,
                    `package_id` INT UNSIGNED NOT NULL,
                    `package_code` VARCHAR(60) DEFAULT NULL,
                    `start_date` DATE DEFAULT NULL,
                    `expiry_date` DATE DEFAULT NULL,
                    `status` VARCHAR(20) NOT NULL DEFAULT 'applied',
                    `payment_status` VARCHAR(20) NOT NULL DEFAULT 'pending',
                    `payment_ref` VARCHAR(120) DEFAULT NULL,
                    `paid_amount` DECIMAL(12,2) DEFAULT NULL,
                    `paid_at` DATETIME DEFAULT NULL,
                    `approved_by` INT UNSIGNED DEFAULT NULL,
                    `approved_at` DATETIME DEFAULT NULL,
                    `note` VARCHAR(500) DEFAULT NULL,
                    `created_by` INT UNSIGNED DEFAULT NULL,
                    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` DATETIME DEFAULT NULL,
                    KEY `idx_user` (`user_id`),
                    KEY `idx_cat` (`category_code`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                return;
            }
            // additive self-heal for tables created before the approval lifecycle
            $adds = [
                'payment_status' => "VARCHAR(20) NOT NULL DEFAULT 'pending'",
                'payment_ref'    => "VARCHAR(120) DEFAULT NULL",
                'paid_amount'    => "DECIMAL(12,2) DEFAULT NULL",
                'paid_at'        => "DATETIME DEFAULT NULL",
                'payment_method' => "VARCHAR(20) DEFAULT NULL",
                'approved_by'    => "INT UNSIGNED DEFAULT NULL",
                'approved_at'    => "DATETIME DEFAULT NULL",
                'note'           => "VARCHAR(500) DEFAULT NULL",
            ];
            foreach ($adds as $col => $def) {
                if (!Schema::hasColumn('user_packages', $col)) {
                    DB::statement("ALTER TABLE `user_packages` ADD COLUMN `$col` $def");
                }
            }
        } catch (Throwable) {
        }
    }

    /**
     * Enforce the invariant: a package may be ACTIVE only when its fee is paid
     * AND it has been approved. Reverts any inconsistent "active-but-unpaid"
     * (or active-but-unapproved) rows back to "applied". Cheap, idempotent.
     */
    private function normalize(): void
    {
        try {
            if (Schema::hasColumn('user_packages', 'payment_status') && Schema::hasColumn('user_packages', 'approved_at')) {
                DB::table('user_packages')
                    ->where('status', 'active')
                    ->where(fn ($q) => $q->where('payment_status', '<>', 'paid')->orWhereNull('approved_at'))
                    ->update(['status' => 'applied', 'updated_at' => now()]);
            }
        } catch (Throwable) {
        }
    }

    /**
     * A package becomes ACTIVE only once its fee is paid AND an admin approves.
     * Until then it stays "applied". Returns the resulting status.
     */
    private function maybeActivate(int $mapId): string
    {
        try {
            $r = DB::table('user_packages')->where('id', $mapId)->first();
            if (!$r || $r->status !== 'applied') {
                return $r->status ?? 'applied';
            }
            if (($r->payment_status ?? 'pending') === 'paid' && !empty($r->approved_at)) {
                DB::table('user_packages')->where('id', $mapId)->update(['status' => 'active', 'updated_at' => now()]);
                Audit::log('user_packages', $mapId, 'active', null, ['user' => $r->user_id]);
                // One active package per category + bonus cascade (points 6-7).
                try { $this->upgrade->onPackageActivated($mapId); } catch (Throwable) {}
                return 'active';
            }
            return 'applied';
        } catch (Throwable) {
            return 'applied';
        }
    }

    /** Compute expiry from a package's period. */
    private function computeExpiry(string $start, ?int $value, ?string $unit): ?string
    {
        if (!$value || $value <= 0) {
            return null; // no period → open-ended
        }
        try {
            $d = Carbon::parse($start);
            $d = match ($unit) {
                'years'  => $d->addYears($value),
                'months' => $d->addMonths($value),
                default  => $d->addDays($value),
            };
            return $d->toDateString();
        } catch (Throwable) {
            return null;
        }
    }

    /** Active packages designed for a given category. */
    private function packagesForCategory(string $category): array
    {
        try {
            $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')) {
                // match the category, or packages with no category set (generic)
                $q->where(fn ($w) => $w->where('for_category', $category)->orWhereNull('for_category')->orWhere('for_category', ''));
            }
            $cols = ['id', 'code', 'name'];
            foreach (['price', 'price_usd', 'period_value', 'period_unit', 'max_services', 'max_leads', 'ai_hours', 'comm_plan', 'for_category'] as $c) {
                if (Schema::hasColumn('packages', $c)) {
                    $cols[] = $c;
                }
            }
            \App\Services\Pricing\PriceBook::ensureSchema();
            $cur = \App\Services\Pricing\CurrencyResolver::resolve(request());
            return $q->orderBy('name')->get($cols)->map(fn ($r) => \App\Services\Pricing\PriceBook::decorate(
                (array) $r, 'package', $r->id ?? null, $r->price ?? 0, $cur
            ))->all();
        } catch (Throwable) {
            return [];
        }
    }

    /**
     * GET /api/my/packages — the AUTHENTICATED user's own categories and their
     * package status (front-side). Returns each held category with its current
     * package (active/applied), validity window, and a renewal flag when an
     * active package expires within a month.
     */
    public function mine(Request $request): JsonResponse
    {
        $this->ensure();
        $this->normalize();
        $uid = (int) $request->user()->id;
        $codes = $this->upgrade->memberships($uid);

        $names = [];
        try {
            $names = DB::table('user_categories')->whereIn('code', $codes ?: ['__none__'])->pluck('name', 'code');
        } catch (Throwable) {
        }

        // this user's packages (active + applied), newest per category first
        $packages = [];
        try {
            $rows = DB::table('user_packages as up')
                ->leftJoin('packages as p', 'p.id', '=', 'up.package_id')
                ->where('up.user_id', $uid)
                ->whereIn('up.status', ['active', 'applied'])
                ->orderByDesc('up.id')
                ->get([
                    'up.id', 'up.category_code', 'up.package_id', 'up.package_code',
                    'up.start_date', 'up.expiry_date', 'up.status', 'up.payment_status',
                    'up.payment_method', 'p.name as package_name', 'p.price as package_price',
                ]);
            $today = Carbon::now()->startOfDay();
            foreach ($rows as $r) {
                $daysLeft = null;
                $expiringSoon = false;
                $expired = false;
                if (!empty($r->expiry_date)) {
                    try {
                        $exp = Carbon::parse($r->expiry_date)->startOfDay();
                        $daysLeft = (int) $today->diffInDays($exp, false);
                        if ($r->status === 'active') {
                            $expired = $daysLeft < 0;
                            $expiringSoon = $daysLeft >= 0 && $daysLeft <= 30;
                        }
                    } catch (Throwable) {
                    }
                }
                $packages[] = [
                    'id'             => $r->id,
                    'category_code'  => $r->category_code,
                    'category_name'  => $names[$r->category_code] ?? ucfirst((string) $r->category_code),
                    'package_name'   => $r->package_name ?? $r->package_code,
                    'package_price'  => $r->package_price,
                    'status'         => $r->status,
                    'payment_status' => $r->payment_status,
                    'payment_method' => $r->payment_method,
                    'needs_payment'  => ($r->payment_status ?? 'pending') !== 'paid',
                    'start_date'     => $r->start_date,
                    'expiry_date'    => $r->expiry_date,
                    'days_left'      => $daysLeft,
                    'expiring_soon'  => $expiringSoon,
                    'expired'        => $expired,
                ];
            }
        } catch (Throwable) {
        }

        // categories held (even those without a package yet)
        $categories = [];
        foreach ($codes as $c) {
            $pkg = null;
            foreach ($packages as $p) {
                if ($p['category_code'] === $c) {
                    $pkg = $p; // newest first → primary
                    break;
                }
            }
            $categories[] = [
                'code'    => $c,
                'name'    => $names[$c] ?? ucfirst($c),
                'package' => $pkg,
            ];
        }

        $renewals = array_values(array_filter($packages, fn ($p) => $p['expiring_soon'] || $p['expired']));
        // packages awaiting the user's fee payment (reminder on the dashboard)
        $unpaid = array_values(array_filter($packages, fn ($p) => $p['needs_payment']));

        return $this->ok([
            'categories' => $categories,
            'packages'   => $packages,
            'renewals'   => $renewals,
            'unpaid'     => $unpaid,
        ]);
    }

    /**
     * POST /api/my/packages/{mapId}/pay — the USER starts an online payment for
     * their package fee (auto gateway / UPI). Returns the gateway checkout
     * descriptor; settlement auto-confirms the fee via PaymentService.
     */
    public function payInit(int $mapId, Request $request)
    {
        $this->ensure();
        $uid = (int) $request->user()->id;
        $row = DB::table('user_packages as up')
            ->leftJoin('packages as p', 'p.id', '=', 'up.package_id')
            ->where('up.id', $mapId)->where('up.user_id', $uid)
            ->first(['up.id', 'up.package_id', 'up.payment_status', 'up.status', 'p.name as package_name', 'p.price as price']);
        if (!$row) {
            return $this->fail('Package not found.', 404);
        }
        if (($row->payment_status ?? '') === 'paid') {
            return $this->fail('This package fee is already paid.', 422);
        }
        // Territory currency + resolved fee for this user (INR India / USD international).
        \App\Services\Pricing\PriceBook::ensureSchema();
        $currency = \App\Services\Pricing\CurrencyResolver::resolve($request);
        $priced = \App\Services\Pricing\PriceBook::resolve('package', $row->package_id ?? null, $row->price ?? 0, $currency);
        $amount = (float) $priced['amount'];
        if ($amount <= 0) {
            return $this->fail('This package has no fee to pay.', 422);
        }

        try {
            $pay = app(\App\Services\Payment\PaymentService::class)->create(
                'user_package',
                $mapId,
                $amount,
                [
                    'user_id'    => $uid,
                    'payer_name' => $request->user()->name ?? null,
                    'label'      => 'Package: ' . ($row->package_name ?? ''),
                    'currency'   => $priced['currency'],
                ],
            );
            // remember we're paying by gateway; keep the pay ref for tracking
            DB::table('user_packages')->where('id', $mapId)->update([
                'payment_method' => 'gateway',
                'payment_ref'    => $pay['ref'] ?? null,
                'updated_at'     => now(),
            ]);
        } catch (Throwable) {
            return $this->fail('Could not start the payment.', 500);
        }

        return $this->ok(['checkout' => $pay]);
    }

    /** GET /api/admin/user-packages/{userId} — categories, available packages, current mappings. */
    public function show(int $userId, Request $request): JsonResponse
    {
        $this->ensure();
        $this->normalize();

        $codes = $this->upgrade->memberships($userId);
        // names for the categories
        $names = [];
        try {
            $names = DB::table('user_categories')->whereIn('code', $codes ?: ['__none__'])->pluck('name', 'code');
        } catch (Throwable) {
        }

        $categories = [];
        foreach ($codes as $c) {
            $categories[] = [
                'code'     => $c,
                'name'     => $names[$c] ?? ucfirst($c),
                'packages' => $this->packagesForCategory($c),
            ];
        }

        $mappings = [];
        try {
            $mappings = DB::table('user_packages as up')
                ->leftJoin('packages as p', 'p.id', '=', 'up.package_id')
                ->where('up.user_id', $userId)
                ->orderByDesc('up.id')
                ->get([
                    'up.id', 'up.category_code', 'up.package_id', 'up.package_code',
                    'up.start_date', 'up.expiry_date', 'up.status', 'p.name as package_name',
                    'up.payment_status', 'up.payment_ref', 'up.paid_amount', 'up.paid_at',
                    'up.approved_at', 'up.note', 'p.price as package_price',
                ])
                ->map(fn ($r) => (array) $r)->all();
        } catch (Throwable) {
        }

        return $this->ok(['categories' => $categories, 'mappings' => $mappings]);
    }

    /** POST /api/admin/user-packages/{userId} — assign a package. */
    public function assign(int $userId, Request $request): JsonResponse
    {
        $this->ensure();
        $data = $request->validate([
            'category_code' => ['required', 'string', 'max:40'],
            'package_id'    => ['required', 'integer'],
            'start_date'    => ['sometimes', 'nullable', 'date'],
        ]);

        // user must hold the category
        if (!in_array($data['category_code'], $this->upgrade->memberships($userId), true)) {
            return $this->fail('User does not hold this category.', 422);
        }

        $pkg = DB::table('packages')->where('id', (int) $data['package_id'])->first();
        if (!$pkg) {
            return $this->fail('Package not found.', 404);
        }
        // package must be designed for this category (or generic)
        $forCat = $pkg->for_category ?? null;
        if (!empty($forCat) && $forCat !== $data['category_code']) {
            return $this->fail('This package is not designed for the selected category.', 422);
        }

        $start = $data['start_date'] ?? Carbon::now()->toDateString();
        $expiry = $this->computeExpiry(
            $start,
            isset($pkg->period_value) ? (int) $pkg->period_value : null,
            $pkg->period_unit ?? 'days',
        );

        try {
            $id = DB::table('user_packages')->insertGetId([
                'user_id'        => $userId,
                'category_code'  => $data['category_code'],
                'package_id'     => (int) $pkg->id,
                'package_code'   => $pkg->code ?? null,
                'start_date'     => $start,
                'expiry_date'    => $expiry,
                'status'         => 'applied',  // awaits payment + admin approval
                'payment_status' => 'pending',
                'created_by'     => (int) $request->user()->id,
                'created_at'     => now(),
            ]);
            Audit::log('user_packages', $id, 'assign', null, [
                'user' => $userId, 'category' => $data['category_code'], 'package' => $pkg->code ?? $pkg->id,
            ]);
        } catch (Throwable) {
            return $this->fail('Could not assign the package.', 500);
        }

        return $this->ok(['id' => $id, 'start_date' => $start, 'expiry_date' => $expiry], 201);
    }

    /** POST /api/admin/user-packages/{userId}/cancel/{mapId} */
    public function cancel(int $userId, int $mapId, Request $request): JsonResponse
    {
        $this->ensure();
        try {
            $ok = DB::table('user_packages')->where('id', $mapId)->where('user_id', $userId)
                ->update(['status' => 'cancelled', 'updated_at' => now()]);
            if ($ok) {
                Audit::log('user_packages', $mapId, 'cancel', null, ['user' => $userId]);
            }
            return $ok ? $this->ok(['cancelled' => true]) : $this->fail('Mapping not found.', 404);
        } catch (Throwable) {
            return $this->fail('Could not cancel the package.', 500);
        }
    }

    /**
     * POST /api/admin/user-packages/{userId}/pay/{mapId}
     * Record the package-fee payment. body: { payment_ref?, paid_amount? }.
     * Activates the package if it is also already approved.
     */
    public function confirmPayment(int $userId, int $mapId, Request $request): JsonResponse
    {
        $this->ensure();
        $data = $request->validate([
            'payment_ref' => ['sometimes', 'nullable', 'string', 'max:120'],
            'paid_amount' => ['sometimes', 'nullable', 'numeric'],
        ]);
        $row = DB::table('user_packages')->where('id', $mapId)->where('user_id', $userId)->first();
        if (!$row) {
            return $this->fail('Mapping not found.', 404);
        }
        try {
            DB::table('user_packages')->where('id', $mapId)->update([
                'payment_status' => 'paid',
                'payment_ref'    => $data['payment_ref'] ?? null,
                'paid_amount'    => $data['paid_amount'] ?? ($row->paid_amount ?? null),
                'paid_at'        => now(),
                'payment_method' => 'manual',
                'updated_at'     => now(),
            ]);
            Audit::log('user_packages', $mapId, 'payment_confirmed', null, ['user' => $userId, 'ref' => $data['payment_ref'] ?? null]);
        } catch (Throwable) {
            return $this->fail('Could not record the payment.', 500);
        }
        $status = $this->maybeActivate($mapId);
        return $this->ok(['payment_status' => 'paid', 'status' => $status]);
    }

    /**
     * POST /api/admin/user-packages/{userId}/approve/{mapId}
     * Admin approval. Activates the package if the fee is also already paid.
     */
    public function approve(int $userId, int $mapId, Request $request): JsonResponse
    {
        $this->ensure();
        $row = DB::table('user_packages')->where('id', $mapId)->where('user_id', $userId)->first();
        if (!$row) {
            return $this->fail('Mapping not found.', 404);
        }
        try {
            DB::table('user_packages')->where('id', $mapId)->update([
                'approved_by' => (int) $request->user()->id,
                'approved_at' => now(),
                'updated_at'  => now(),
            ]);
            Audit::log('user_packages', $mapId, 'approved', null, ['user' => $userId]);
        } catch (Throwable) {
            return $this->fail('Could not approve the package.', 500);
        }
        $status = $this->maybeActivate($mapId);
        return $this->ok(['approved' => true, 'status' => $status]);
    }

    /** POST /api/admin/user-packages/{userId}/reject/{mapId} — body: { note? } */
    public function reject(int $userId, int $mapId, Request $request): JsonResponse
    {
        $this->ensure();
        $data = $request->validate(['note' => ['sometimes', 'nullable', 'string', 'max:500']]);
        try {
            $ok = DB::table('user_packages')->where('id', $mapId)->where('user_id', $userId)
                ->update(['status' => 'rejected', 'note' => $data['note'] ?? null, 'updated_at' => now()]);
            if ($ok) {
                Audit::log('user_packages', $mapId, 'rejected', null, ['user' => $userId]);
            }
            return $ok ? $this->ok(['rejected' => true]) : $this->fail('Mapping not found.', 404);
        } catch (Throwable) {
            return $this->fail('Could not reject the package.', 500);
        }
    }

    /**
     * GET /api/admin/user-packages?status=applied — global approval queue across
     * all users (applicant + package + payment + approval state).
     */
    public function queue(Request $request): JsonResponse
    {
        $this->ensure();
        $this->normalize();
        $status = (string) $request->query('status', 'applied');
        try {
            $q = DB::table('user_packages as up')
                ->leftJoin('packages as p', 'p.id', '=', 'up.package_id')
                ->leftJoin('users as u', 'u.id', '=', 'up.user_id');
            if ($status && $status !== 'all') {
                $q->where('up.status', $status);
            }
            $rows = $q->orderByDesc('up.id')->limit(300)->get([
                'up.id', 'up.user_id', 'up.category_code', 'up.package_id', 'up.package_code',
                'up.start_date', 'up.expiry_date', 'up.status', 'up.payment_status', 'up.payment_ref',
                'up.paid_amount', 'up.approved_at', 'up.note',
                'p.name as package_name', 'p.price as package_price',
                'u.name as user_name', 'u.email as user_email',
            ])->map(fn ($r) => (array) $r)->all();
            return $this->ok(['requests' => $rows]);
        } catch (Throwable) {
            return $this->ok(['requests' => []]);
        }
    }
}