<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Throwable;

/**
 * Role-activity counters for the authenticated user's own dashboard.
 *
 * Returns a small set of headline statistics for each business role the user
 * actively holds (franchise / pandit / vendor — determined from their ACTIVE
 * packages). Every counter is computed defensively (table/column guarded) and
 * degrades to 0, so a missing table never breaks the dashboard.
 */
class MyStatsController extends Controller
{
    public function stats(Request $request): JsonResponse
    {
        $uid = (int) $request->user()->id;
        $types = $this->activeTypes($uid);

        $out = [];
        if (in_array('franchise', $types, true)) {
            $out['franchise'] = $this->franchise($uid);
        }
        if (in_array('pandit', $types, true)) {
            $out['pandit'] = $this->pandit($uid);
        }
        if (in_array('vendor', $types, true)) {
            $out['vendor'] = $this->vendor($uid);
        }

        return $this->ok(['stats' => $out]);
    }

    /** Which business roles the user actively holds (from active packages). */
    private function activeTypes(int $uid): array
    {
        $types = [];
        try {
            if (!Schema::hasTable('user_packages')) {
                return [];
            }
            $rows = DB::table('user_packages')->where('user_id', $uid)->where('status', 'active')
                ->pluck('category_code')->all();
            foreach ($rows as $code) {
                $hay = strtolower((string) $code);
                if (preg_match('/franc|partner|fpr|prabandh/', $hay)) {
                    $types[] = 'franchise';
                } elseif (preg_match('/pandit|achary|acharya|pnd|priest|guru/', $hay)) {
                    $types[] = 'pandit';
                } elseif (preg_match('/vendor|nidhi|supplier|seller|shop|store/', $hay)) {
                    $types[] = 'vendor';
                }
            }
        } catch (Throwable) {
        }
        return array_values(array_unique($types));
    }

    /** Safe COUNT(*) for a table filtered by one column = value. */
    private function countBy(string $table, string $col, $val): int
    {
        try {
            if (!Schema::hasTable($table) || !Schema::hasColumn($table, $col) || $val === null || $val === '') {
                return 0;
            }
            $q = DB::table($table)->where($col, $val);
            if (Schema::hasColumn($table, 'deleted_at')) {
                $q->whereNull('deleted_at');
            }
            return (int) $q->count();
        } catch (Throwable) {
            return 0;
        }
    }

    private function stat(string $label, $value, bool $money = false): array
    {
        return ['label' => $label, 'value' => $value, 'money' => $money];
    }

    /**
     * Active users that belong to a franchise, across BOTH hierarchy paths:
     *   (A) DIRECT — a user registered straight under the franchise
     *       (franchise_id = franchise, or the inherited franchise_code), with no
     *       pandit above them, and not an expert (pandit/vendor) themselves.
     *   (B) VIA PANDIT — a user under a pandit who is under this franchise
     *       (franchise → pandit → user), matched by the user's pandit_id.
     *
     * Both count as the franchise's active users. Path B is matched by pandit_id
     * (not only franchise_code) so a yajman still counts even if the franchise
     * code did not propagate down the chain. Only active, registered users are
     * counted (is_active = 1); invited-but-unregistered prospects are excluded.
     * Returns ['direct'=>int, 'via_pandit'=>int, 'total'=>int].
     */
    private function franchiseMembers(int $uid, ?string $code): array
    {
        $out = ['direct' => 0, 'via_pandit' => 0, 'total' => 0];
        try {
            if (!Schema::hasTable('users')) {
                return $out;
            }
            $hasFrId    = Schema::hasColumn('users', 'franchise_id');
            $hasFrCode  = Schema::hasColumn('users', 'franchise_code');
            $hasPndId   = Schema::hasColumn('users', 'pandit_id');
            $hasPndCode = Schema::hasColumn('users', 'pandit_code');
            $hasRole    = Schema::hasColumn('users', 'role');
            $hasActive  = Schema::hasColumn('users', 'is_active');

            // Restrict every count to active, registered members.
            $active = function ($q) use ($hasActive) {
                if ($hasActive) {
                    $q->where('is_active', 1);
                }
                return $q;
            };
            // Scope: rows that belong to this franchise (id or inherited code).
            $belongs = function ($q) use ($uid, $code, $hasFrId, $hasFrCode) {
                $q->where(function ($w) use ($uid, $code, $hasFrId, $hasFrCode) {
                    if ($hasFrId) {
                        $w->orWhere('franchise_id', $uid);
                    }
                    if ($code && $hasFrCode) {
                        $w->orWhere('franchise_code', $code);
                    }
                });
                return $q;
            };
            if (!($hasFrId || $hasFrCode)) {
                return $out;
            }

            // ── Who has "changed category" (must NOT be counted as a plain user) ──
            // Categories live in user_packages (an upgrade ADDS a package; it does
            // NOT flip the users.role column), so package category is the primary
            // signal. Also treat as expert: an expert role, and anyone who OWNS
            // their own code (pandit_id = id → owns PN, franchise_id = id → the
            // franchise itself). NOTE: a yajman inherits their pandit's pandit_code,
            // so pandit_code-not-null is NOT an expert signal — use pandit_id = id.
            $pkgPandit = []; $expertIds = [];
            try {
                if (Schema::hasTable('user_packages') && Schema::hasColumn('user_packages', 'category_code')) {
                    foreach (DB::table('user_packages')->where('status', 'active')->get(['user_id', 'category_code']) as $r) {
                        $hay = strtolower((string) $r->category_code);
                        $isPandit = (bool) preg_match('/pandit|achary|acharya|pnd|priest|guru/', $hay);
                        $isFranc  = (bool) preg_match('/franc|partner|fpr|prabandh/', $hay);
                        $isVendor = (bool) preg_match('/vendor|nidhi|supplier|seller|shop|store/', $hay);
                        if ($isPandit) {
                            $pkgPandit[] = (int) $r->user_id;
                        }
                        if ($isPandit || $isFranc || $isVendor) {
                            $expertIds[] = (int) $r->user_id;
                        }
                    }
                }
            } catch (Throwable) {
            }
            // Add role-based experts and self-owned-code holders.
            try {
                $eq = DB::table('users')->where(function ($w) use ($hasRole, $hasPndId, $hasFrId) {
                    if ($hasRole) {
                        $w->orWhereIn('role', ['pandit', 'vendor', 'partner', 'franchise']);
                    }
                    if ($hasPndId) {
                        $w->orWhereColumn('pandit_id', 'id');
                    }
                    if ($hasFrId) {
                        $w->orWhereColumn('franchise_id', 'id');
                    }
                });
                $expertIds = array_merge($expertIds, $eq->pluck('id')->map(fn ($x) => (int) $x)->all());
            } catch (Throwable) {
            }
            $expertIds = array_values(array_unique($expertIds));
            $pkgPandit = array_values(array_unique($pkgPandit));

            // Pandit ids that sit under this franchise (the middle of path B).
            // A pandit OWNS their code (pandit_id = id), holds a pandit package,
            // or carries the pandit role — never inferred from inherited pandit_code.
            $panditIds = [];
            if ($hasPndId) {
                $pq = DB::table('users')->where('id', '<>', $uid);
                $belongs($pq);
                $pq->where(function ($w) use ($hasRole, $pkgPandit) {
                    $w->orWhereColumn('pandit_id', 'id');
                    if ($hasRole) {
                        $w->orWhere('role', 'pandit');
                    }
                    if ($pkgPandit) {
                        $w->orWhereIn('id', $pkgPandit);
                    }
                });
                $panditIds = $pq->pluck('id')->map(fn ($x) => (int) $x)->all();
            }

            // (A) Direct yajmans under the franchise: belong to the franchise, no
            // pandit above them, and have NOT changed category (not in expertIds).
            $aq = DB::table('users')->where('id', '<>', $uid);
            $belongs($aq);
            $active($aq);
            if ($hasPndId) {
                $aq->whereNull('pandit_id');
            }
            if ($expertIds) {
                $aq->whereNotIn('id', $expertIds);
            }
            $directIds = $aq->pluck('id')->map(fn ($x) => (int) $x)->all();

            // (B) Yajmans under this franchise's pandits — end users only. Drop the
            // pandit rows themselves and anyone who has changed category.
            $viaIds = [];
            if ($panditIds && $hasPndId) {
                $bq = DB::table('users')->whereIn('pandit_id', $panditIds)->where('id', '<>', $uid);
                $active($bq);
                $bq->whereNotIn('id', $panditIds);
                if ($expertIds) {
                    $bq->whereNotIn('id', $expertIds);
                }
                $viaIds = $bq->pluck('id')->map(fn ($x) => (int) $x)->all();
            }

            $all = array_values(array_unique(array_merge($directIds, $viaIds)));
            $out['direct'] = count($directIds);
            $out['via_pandit'] = count($viaIds);
            $out['total'] = count($all);
        } catch (Throwable) {
        }
        return $out;
    }

    private function pandit(int $uid): array
    {
        $pid = null;
        try {
            $pid = DB::table('pandits')->where('user_id', $uid)->value('id');
        } catch (Throwable) {
        }
        return [
            $this->stat('Bookings', $this->countBy('pandit_assignments', 'pandit_id', $pid)),
            $this->stat('Consultations', $this->countBy('consultations', 'pandit_id', $pid)),
            $this->stat('Services', $this->countBy('pandit_services', 'pandit_id', $pid)),
        ];
    }

    /**
     * GET /api/my/pandit — live counters for the Pandit Activity page. Yajmans are
     * counted by the hierarchy code (users.pandit_id = this pandit); earnings from
     * commission_entries where this pandit is the beneficiary.
     */
    public function panditDashboard(Request $request): JsonResponse
    {
        $uid = (int) $request->user()->id;

        // Yajmans under this pandit (hierarchy code).
        $yajmans = 0;
        try {
            if (Schema::hasTable('users') && Schema::hasColumn('users', 'pandit_id')) {
                $yajmans = (int) DB::table('users')->where('pandit_id', $uid)->where('id', '<>', $uid)->count();
            }
        } catch (Throwable) {
        }

        // Pandit profile id → service/consultation counts.
        $pid = null;
        try {
            $pid = DB::table('pandits')->where('user_id', $uid)->value('id');
        } catch (Throwable) {
        }
        $bookings = $this->countBy('pandit_assignments', 'pandit_id', $pid);
        $consultations = $this->countBy('consultations', 'pandit_id', $pid);
        $services = $this->countBy('pandit_services', 'pandit_id', $pid);

        // Earnings (commission as beneficiary), total + this month.
        $earnings = 0.0; $earningsMonth = 0.0;
        try {
            if (Schema::hasTable('commission_entries') && Schema::hasColumn('commission_entries', 'beneficiary_id')) {
                $amtCol = Schema::hasColumn('commission_entries', 'commission_amount') ? 'commission_amount'
                    : (Schema::hasColumn('commission_entries', 'amount') ? 'amount' : null);
                if ($amtCol) {
                    $q = DB::table('commission_entries')->where('beneficiary_id', $uid);
                    $earnings = (float) (clone $q)->sum($amtCol);
                    if (Schema::hasColumn('commission_entries', 'created_at')) {
                        $earningsMonth = (float) (clone $q)->where('created_at', '>=', now()->startOfMonth())->sum($amtCol);
                    }
                }
            }
        } catch (Throwable) {
        }

        return $this->ok([
            'yajmans'        => $yajmans,
            'consultations'  => $bookings + $consultations,
            'services'       => $services,
            'earnings'       => round($earnings),
            'earnings_month' => round($earningsMonth),
        ]);
    }

    private function vendor(int $uid): array
    {
        $code = null;
        try {
            $code = DB::table('vendors')->where('user_id', $uid)->value('vendor_code')
                ?: DB::table('vendors')->where('user_id', $uid)->value('code');
        } catch (Throwable) {
        }
        $products = $this->countBy('products', 'vendor_code', $code);
        // Orders: try the likely vendor link columns on the order tables.
        $orders = 0;
        foreach ([['shop_orders', 'vendor_code'], ['service_orders', 'vendor_code'], ['shop_order_items', 'vendor_code']] as [$t, $c]) {
            $orders += $this->countBy($t, $c, $code);
        }
        return [
            $this->stat('Products', $products),
            $this->stat('Orders', $orders),
        ];
    }

    /**
     * GET /api/my/franchise — richer dashboard payload for the Franchise Activity
     * page: KPI counters, a 6-month signup trend, revenue snapshot, and a task
     * checklist. All figures are computed defensively against the franchise's own
     * code (users carrying it downline) and the user's commission entries.
     */
    public function franchiseDashboard(Request $request): JsonResponse
    {
        $uid = (int) $request->user()->id;
        $code = null;
        try {
            $code = DB::table('users')->where('id', $uid)->value('franchise_code');
        } catch (Throwable) {
        }

        $usersTbl = Schema::hasTable('users');
        $hasFr = $usersTbl && Schema::hasColumn('users', 'franchise_code');
        $hasCreated = $usersTbl && Schema::hasColumn('users', 'created_at');
        $hasRole = $usersTbl && Schema::hasColumn('users', 'role');
        $monthStart = now()->startOfMonth();

        $team = 0; $experts = 0; $newThisMonth = 0;
        try {
            if ($code && $hasFr) {
                $base = DB::table('users')->where('franchise_code', $code)->where('id', '<>', $uid);
                $team = (int) (clone $base)->count();
                // Experts = pandits/vendors under the franchise. Count by role AND by
                // the expertise tag, so an invited pandit still shows even before they
                // finish the upgrade to the pandit role.
                $experts = (int) (clone $base)->where(function ($w) {
                    if (Schema::hasColumn('users', 'role')) {
                        $w->orWhereIn('role', ['pandit', 'vendor', 'partner']);
                    }
                    if (Schema::hasColumn('users', 'expertise')) {
                        $w->orWhereNotNull('expertise');
                    }
                    if (Schema::hasColumn('users', 'pandit_code')) {
                        $w->orWhereNotNull('pandit_code');
                    }
                })->count();
                if ($hasCreated) {
                    $newThisMonth = (int) (clone $base)->where('created_at', '>=', $monthStart)->count();
                }
            }
        } catch (Throwable) {
        }

        // Active users under this franchise, counted across BOTH paths:
        // (A) direct under the franchise and (B) under this franchise's pandits.
        $memberBreak = $this->franchiseMembers($uid, $code);
        $activeUsers = (int) $memberBreak['total'];
        $directUsers = (int) $memberBreak['direct'];
        $panditChainUsers = (int) $memberBreak['via_pandit'];

        // 6-month signup trend (users onboarded under this franchise).
        $trend = [];
        for ($i = 5; $i >= 0; $i--) {
            $m = now()->startOfMonth()->subMonths($i);
            $cnt = 0;
            try {
                if ($code && $hasFr && $hasCreated) {
                    $cnt = (int) DB::table('users')->where('franchise_code', $code)
                        ->whereBetween('created_at', [$m->copy()->startOfMonth(), $m->copy()->endOfMonth()])
                        ->count();
                }
            } catch (Throwable) {
            }
            $trend[] = ['label' => $m->format('M'), 'value' => $cnt];
        }

        // Commissions + earnings (total and this month).
        $commissions = 0; $earnings = 0.0; $earningsMonth = 0.0;
        try {
            if (Schema::hasTable('commission_entries') && Schema::hasColumn('commission_entries', 'beneficiary_id')) {
                $q = DB::table('commission_entries')->where('beneficiary_id', $uid);
                $commissions = (int) (clone $q)->count();
                $amtCol = Schema::hasColumn('commission_entries', 'commission_amount') ? 'commission_amount'
                    : (Schema::hasColumn('commission_entries', 'amount') ? 'amount' : null);
                if ($amtCol) {
                    $earnings = (float) (clone $q)->sum($amtCol);
                    if (Schema::hasColumn('commission_entries', 'created_at')) {
                        $earningsMonth = (float) (clone $q)->where('created_at', '>=', $monthStart)->sum($amtCol);
                    }
                }
            }
        } catch (Throwable) {
        }

        // Derived figures for the charts.
        $signupsTotal = array_sum(array_map(fn ($t) => (int) $t['value'], $trend));
        $lastM = (int) ($trend[count($trend) - 1]['value'] ?? 0);
        $prevM = (int) ($trend[count($trend) - 2]['value'] ?? 0);
        $referralGrowth = $prevM > 0 ? (int) round((($lastM - $prevM) / $prevM) * 100) : ($lastM > 0 ? 100 : 0);
        $members = $activeUsers; // team_mix "Users" slice = active users (both paths)
        $earlier = max(0, round($earnings) - round($earningsMonth));

        // Assigned geographic territory (exclusive, mandatory for a franchise).
        $territory = null;
        try {
            if (Schema::hasTable('franchise_territories')) {
                $t = DB::table('franchise_territories')->where('franchise_user_id', $uid)->where('is_active', 1)->first();
                // Self-heal: a franchise activated outside the upgrade review (e.g. via
                // package activation) may have its chosen territory only on the
                // application. Materialise it into franchise_territories so it is
                // stored and shown.
                if (!$t && Schema::hasTable('category_upgrade_requests') && Schema::hasColumn('category_upgrade_requests', 'territory_code')) {
                    $req = DB::table('category_upgrade_requests')
                        ->where('user_id', $uid)->whereNotNull('territory_code')->where('territory_code', '<>', '')
                        ->orderByRaw("(status = 'approved') desc")->orderByDesc('id')->first();
                    if ($req) {
                        $key = strtolower((string) $req->territory_code);
                        $free = !DB::table('franchise_territories')->where('territory_key', $key)
                            ->where('is_active', 1)->where('franchise_user_id', '<>', $uid)->exists();
                        if ($free) {
                            try {
                                DB::table('franchise_territories')->insert([
                                    'territory_key'     => $key,
                                    'territory_name'    => (string) ($req->territory_name ?: $req->territory_code),
                                    'franchise_code'    => $code,
                                    'franchise_user_id' => $uid,
                                    'assigned_by'       => $uid,
                                    'assigned_at'       => now(),
                                    'is_active'         => 1,
                                    'created_at'        => now(),
                                    'updated_at'        => now(),
                                ]);
                                $t = DB::table('franchise_territories')->where('franchise_user_id', $uid)->where('is_active', 1)->first();
                            } catch (Throwable) {
                            }
                        }
                    }
                }
                $territory = $t ? (array) $t : null;
            }
        } catch (Throwable) {
        }

        // Live counts by hierarchy code (for the dashboard KPI overlay). Prefer
        // franchise_id; fall back to franchise_code.
        $pandits = 0; $vendors = 0; $yajmans = 0;
        try {
            if (Schema::hasTable('users') && Schema::hasColumn('users', 'franchise_id')) {
                $under = DB::table('users')->where('franchise_id', $uid)->where('id', '<>', $uid);
                if (Schema::hasColumn('users', 'pandit_id')) {
                    $yajmans = (int) (clone $under)->whereNotNull('pandit_id')->count();
                }
                if (Schema::hasColumn('users', 'role')) {
                    $pandits = (int) (clone $under)->where('role', 'pandit')->count();
                    $vendors = (int) (clone $under)->where('role', 'vendor')->count();
                }
            }
        } catch (Throwable) {
        }

        return $this->ok([
            'franchise_code' => $code,
            'territory' => $territory,
            // Live counts (real, by hierarchy code) for the KPI overlay.
            'live' => [
                'team'                => $team,
                'experts'             => $experts,
                'pandits'             => $pandits,
                'vendors'             => $vendors,
                'yajmans'             => $yajmans,
                // Active users under the franchise, both paths.
                'active_users'        => $activeUsers,
                'direct_users'        => $directUsers,
                'pandit_chain_users'  => $panditChainUsers,
                'new_this_month'      => $newThisMonth,
                'earnings'            => round($earnings),
                'earnings_month'      => round($earningsMonth),
            ],
            // Headline KPI cards.
            'kpis' => [
                ['label' => 'New Experts Onboarded', 'value' => $experts, 'note' => $newThisMonth . ' new this month'],
                ['label' => 'Active Users', 'value' => $activeUsers, 'note' => $directUsers . ' direct + ' . $panditChainUsers . ' via pandit'],
                ['label' => 'Monthly Revenue', 'value' => round($earningsMonth), 'money' => true, 'note' => 'Commission this month'],
                ['label' => 'Commission Entries', 'value' => $commissions, 'note' => 'Total payouts recorded'],
            ],
            // Line/bar chart: 6-month member acquisition.
            'trend' => $trend,
            'signups_total' => $signupsTotal,
            'referral_growth' => $referralGrowth,
            // Pie: team composition.
            'team_mix' => [
                ['label' => 'Experts', 'value' => $experts],
                ['label' => 'Direct Users', 'value' => $directUsers],
                ['label' => 'Users via Pandit', 'value' => $panditChainUsers],
            ],
            // Secondary bars: activity snapshot.
            'activity_bars' => [
                ['label' => 'Signups', 'value' => $signupsTotal],
                ['label' => 'Commissions', 'value' => $commissions],
                ['label' => 'Experts', 'value' => $experts],
            ],
            // Revenue breakdown.
            'revenue' => [
                'total' => round($earnings),
                'this_month' => round($earningsMonth),
            ],
            'revenue_breakdown' => [
                ['label' => 'This month', 'value' => round($earningsMonth)],
                ['label' => 'Earlier', 'value' => $earlier],
            ],
            'tasks' => [
                'Verify new expert profiles awaiting approval',
                'Submit this month\'s performance report to HQ',
                'Run a user engagement / referral drive',
                'Schedule a local astrology event or webinar',
            ],
        ]);
    }

    private function franchise(int $uid): array
    {
        // The partner's own franchise code; team = users who carry it downline.
        $code = null;
        try {
            $code = DB::table('users')->where('id', $uid)->value('franchise_code');
        } catch (Throwable) {
        }
        $team = 0;
        try {
            if ($code && Schema::hasTable('users') && Schema::hasColumn('users', 'franchise_code')) {
                $team = (int) DB::table('users')->where('franchise_code', $code)->where('id', '<>', $uid)->count();
            }
        } catch (Throwable) {
        }

        $commissions = 0;
        $earnings = 0.0;
        try {
            if (Schema::hasTable('commission_entries') && Schema::hasColumn('commission_entries', 'beneficiary_id')) {
                $q = DB::table('commission_entries')->where('beneficiary_id', $uid);
                $commissions = (int) (clone $q)->count();
                $amtCol = Schema::hasColumn('commission_entries', 'commission_amount') ? 'commission_amount'
                    : (Schema::hasColumn('commission_entries', 'amount') ? 'amount' : null);
                if ($amtCol) {
                    $earnings = (float) (clone $q)->sum($amtCol);
                }
            }
        } catch (Throwable) {
        }

        return [
            $this->stat('Team', $team),
            $this->stat('Commissions', $commissions),
            $this->stat('Earnings', round($earnings), true),
        ];
    }
}