<?php

namespace Modules\Connector\Http\Controllers\Api\V2;

use App\Business;
use App\Services\Api\BusinessContextService;
use App\Transaction;
use App\User;
use App\Utils\ModuleUtil;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Modules\Accounting\Entities\AccountingAccount;
use Modules\Accounting\Entities\AccountingAccountType;
use Modules\Accounting\Utils\AccountingUtil;
use Modules\AssetManagement\Entities\Asset;
use Modules\AssetManagement\Entities\AssetTransaction;
use Modules\Project\Entities\Project;
use Modules\Repair\Utils\RepairUtil;
use Modules\Superadmin\Entities\Subscription;

class ModuleExtendedController extends BaseController
{
    public function __construct(
        protected BusinessContextService $contextService,
        protected ModuleUtil $moduleUtil,
        protected RepairUtil $repairUtil,
        protected AccountingUtil $accountingUtil
    ) {
    }

    public function manufacturingDashboard(Request $request)
    {
        $this->ensureModule('Manufacturing');
        $business_id = $this->contextService->getBusinessId($request);

        $recipes = 0;
        if (Schema::hasTable('mfg_recipes')) {
            $recipes = DB::table('mfg_recipes')->where('business_id', $business_id)->count();
        }

        $production = Transaction::where('business_id', $business_id)
            ->where('sub_type', 'production_purchase')
            ->count();

        $recent_production = Transaction::where('business_id', $business_id)
            ->where('sub_type', 'production_purchase')
            ->orderByDesc('transaction_date')
            ->limit(10)
            ->get(['id', 'ref_no', 'transaction_date', 'final_total', 'status']);

        return $this->success([
            'recipes_count' => $recipes,
            'production_count' => $production,
            'recent_production' => $recent_production,
        ]);
    }

    public function repairDashboard(Request $request)
    {
        $this->ensureModule('Repair');
        $business_id = $this->contextService->getBusinessId($request);

        $by_status = $this->repairUtil->getRepairByStatus($business_id);
        $by_staff = $this->repairUtil->getRepairByServiceStaff($business_id);

        $trending_brands = [];
        if (Schema::hasTable('repair_job_sheets')) {
            $trending_brands = DB::table('repair_job_sheets')
                ->leftJoin('brands', 'repair_job_sheets.brand_id', '=', 'brands.id')
                ->where('repair_job_sheets.business_id', $business_id)
                ->whereNotNull('repair_job_sheets.brand_id')
                ->select('brands.name as brand', DB::raw('COUNT(repair_job_sheets.id) as total'))
                ->groupBy('brands.id', 'brands.name')
                ->orderByDesc('total')
                ->limit(5)
                ->get();
        }

        $repair_jobs = Transaction::where('business_id', $business_id)
            ->where('sub_type', 'repair')
            ->orderByDesc('transaction_date')
            ->limit(10)
            ->get(['id', 'invoice_no', 'transaction_date', 'final_total', 'status']);

        return $this->success([
            'by_status' => $by_status,
            'by_service_staff' => $by_staff,
            'trending_brands' => $trending_brands,
            'recent_jobs' => $repair_jobs,
        ]);
    }

    public function projectDashboard(Request $request)
    {
        $this->ensureModule('Project');
        $business_id = $this->contextService->getBusinessId($request);

        $statuses = Project::statusDropdown();
        $by_status = [];
        if (Schema::hasTable('pjt_projects')) {
            $counts = DB::table('pjt_projects')
                ->where('business_id', $business_id)
                ->select('status', DB::raw('COUNT(*) as total'))
                ->groupBy('status')
                ->pluck('total', 'status');

            foreach ($statuses as $key => $label) {
                $by_status[] = [
                    'status' => $key,
                    'label' => $label,
                    'count' => (int) ($counts[$key] ?? 0),
                ];
            }
        }

        $tasks_count = Schema::hasTable('pjt_project_tasks')
            ? DB::table('pjt_project_tasks')->where('business_id', $business_id)->count()
            : 0;

        $recent_projects = Schema::hasTable('pjt_projects')
            ? DB::table('pjt_projects')
                ->where('business_id', $business_id)
                ->orderByDesc('id')
                ->limit(10)
                ->get(['id', 'name', 'status', 'start_date', 'end_date'])
            : collect();

        return $this->success([
            'by_status' => $by_status,
            'projects_count' => array_sum(array_column($by_status, 'count')),
            'tasks_count' => $tasks_count,
            'recent_projects' => $recent_projects,
        ]);
    }

    public function assetsDashboard(Request $request)
    {
        $this->ensureModule('AssetManagement');
        $business_id = $this->contextService->getBusinessId($request);
        $user = Auth::user();
        $is_admin = $this->moduleUtil->is_admin($user, $business_id);

        $my_allocated = 0;
        $my_by_category = [];

        if (Schema::hasTable('asset_transactions')) {
            $allocated = AssetTransaction::where('receiver', $user->id)
                ->select(
                    DB::raw('SUM(quantity) as total_quantity_allocated'),
                    DB::raw('(SELECT SUM(quantity) FROM asset_transactions as AT WHERE AT.parent_id = asset_transactions.id AND AT.transaction_type="revoke") as total_revoked_quantity')
                )->first();

            $my_allocated = ($allocated->total_quantity_allocated ?? 0) - ($allocated->total_revoked_quantity ?? 0);

            $my_by_category = AssetTransaction::where('asset_transactions.receiver', $user->id)
                ->leftJoin('assets as a', 'a.id', '=', 'asset_transactions.asset_id')
                ->leftJoin('categories as cat', 'a.category_id', '=', 'cat.id')
                ->select(
                    DB::raw("SUM(COALESCE(asset_transactions.quantity, 0) - COALESCE((SELECT SUM(quantity) FROM asset_transactions as AT WHERE AT.parent_id = asset_transactions.id AND AT.transaction_type='revoke'),0)) as total"),
                    'cat.name as category'
                )
                ->groupBy('cat.id', 'cat.name')
                ->get();
        }

        $data = [
            'is_admin' => $is_admin,
            'my_allocated' => $my_allocated,
            'my_by_category' => $my_by_category,
        ];

        if ($is_admin && Schema::hasTable('assets')) {
            $data['total_assets'] = Asset::where('business_id', $business_id)
                ->sum('quantity');

            $data['by_category'] = Asset::where('assets.business_id', $business_id)
                ->leftJoin('categories as cat', 'assets.category_id', '=', 'cat.id')
                ->select(DB::raw('SUM(quantity) as total'), 'cat.name as category')
                ->groupBy('cat.id', 'cat.name')
                ->get();

            $current_date_add30 = Carbon::now()->addDays(30)->toDateString();
            $data['expiring_assets'] = Asset::where('assets.business_id', $business_id)
                ->leftJoin('asset_warranties as aw', 'aw.asset_id', '=', 'assets.id')
                ->groupBy('assets.id', 'assets.name', 'assets.asset_code')
                ->havingRaw("MAX(aw.end_date) <= ?", [$current_date_add30])
                ->select('assets.name', 'assets.asset_code', DB::raw('MAX(aw.end_date) as max_end_date'), 'assets.id')
                ->limit(20)
                ->get();
        }

        return $this->success($data);
    }

    public function inventoryDashboard(Request $request)
    {
        $this->ensureModule('InventoryManagement');
        $business_id = $this->contextService->getBusinessId($request);

        if (! Schema::hasTable('inventory')) {
            return $this->success(['counts' => [], 'by_status' => [], 'total' => 0]);
        }

        $base = DB::table('inventory')
            ->join('business_locations', 'inventory.branch_id', '=', 'business_locations.id')
            ->where('business_locations.business_id', $business_id);

        $counts = (clone $base)
            ->select('inventory.*', 'business_locations.name as branch_name')
            ->orderByDesc('inventory.id')
            ->limit(20)
            ->get();

        $by_status = (clone $base)
            ->select('inventory.status', DB::raw('COUNT(*) as total'))
            ->groupBy('inventory.status')
            ->get();

        return $this->success([
            'counts' => $counts,
            'by_status' => $by_status,
            'total' => (clone $base)->count(),
        ]);
    }

    public function accountingDashboard(Request $request)
    {
        $this->ensureModule('Accounting');
        $business_id = $this->contextService->getBusinessId($request);

        $start_date = $request->input('start_date', session()->get('financial_year.start', Carbon::now()->startOfYear()->format('Y-m-d')));
        $end_date = $request->input('end_date', session()->get('financial_year.end', Carbon::now()->endOfYear()->format('Y-m-d')));
        $balance_formula = $this->accountingUtil->balanceFormula();

        $account_types = AccountingAccountType::accounting_primary_type();
        $overview = [];

        if (Schema::hasTable('accounting_accounts')) {
            $coa_overview = AccountingAccount::leftjoin('accounting_accounts_transactions as AAT',
                'AAT.accounting_account_id', '=', 'accounting_accounts.id')
                ->where('business_id', $business_id)
                ->whereDate('AAT.operation_date', '>=', $start_date)
                ->whereDate('AAT.operation_date', '<=', $end_date)
                ->select(
                    DB::raw($balance_formula),
                    'accounting_accounts.account_primary_type'
                )
                ->groupBy('accounting_accounts.account_primary_type')
                ->get();

            foreach ($account_types as $k => $v) {
                $value = 0;
                foreach ($coa_overview as $row) {
                    if ($row->account_primary_type == $k && ! empty($row->balance)) {
                        $value = (float) $row->balance;
                    }
                }
                $overview[] = [
                    'type' => $k,
                    'label' => $v['label'],
                    'balance' => $value,
                ];
            }
        }

        $accounts_count = Schema::hasTable('accounting_accounts')
            ? DB::table('accounting_accounts')->where('business_id', $business_id)->count()
            : 0;

        return $this->success([
            'start_date' => $start_date,
            'end_date' => $end_date,
            'overview' => $overview,
            'accounts_count' => $accounts_count,
        ]);
    }

    public function superadminDashboard(Request $request)
    {
        if (! Auth::user()->can('superadmin')) {
            abort(403, 'Unauthorized action.');
        }

        $not_subscribed = Business::leftjoin('subscriptions AS s', 'business.id', '=', 's.business_id')
            ->whereNull('s.id')
            ->count();

        $start = Carbon::today()->subYear();
        $end = Carbon::today();
        $subscriptions = Subscription::whereRaw('DATE(created_at) BETWEEN ? AND ?', [$start, $end])
            ->select('package_price', 'created_at')
            ->orderBy('created_at')
            ->get();

        $monthly = [];
        foreach ($subscriptions as $value) {
            $month_year = format_shamsi_chart_label($value->created_at, 'M-Y', 'jMMMM-jYYYY');
            if (! isset($monthly[$month_year])) {
                $monthly[$month_year] = 0;
            }
            $monthly[$month_year] += (float) $value->package_price;
        }

        return $this->success([
            'not_subscribed' => $not_subscribed,
            'businesses_count' => Business::count(),
            'packages_count' => Schema::hasTable('packages') ? DB::table('packages')->count() : 0,
            'subscriptions_count' => Subscription::count(),
            'monthly_subscriptions' => $monthly,
        ]);
    }

    public function superadminStats(Request $request)
    {
        if (! Auth::user()->can('superadmin')) {
            abort(403, 'Unauthorized action.');
        }

        $start_date = $request->input('start_date', Carbon::today()->startOfMonth()->format('Y-m-d'));
        $end_date = $request->input('end_date', Carbon::today()->format('Y-m-d'));

        $subscription = Subscription::whereRaw('DATE(created_at) BETWEEN ? AND ?', [$start_date, $end_date])
            ->where('status', 'approved')
            ->select(DB::raw('SUM(package_price) as total'))
            ->first()
            ->total ?? 0;

        $registrations = Business::whereRaw('DATE(created_at) BETWEEN ? AND ?', [$start_date, $end_date])
            ->count();

        return $this->success([
            'new_subscriptions' => (float) $subscription,
            'new_registrations' => $registrations,
        ]);
    }

    public function spreadsheetDashboard(Request $request)
    {
        $this->ensureModule('Spreadsheet');
        $business_id = $this->contextService->getBusinessId($request);

        if (! Schema::hasTable('sheet_spreadsheets')) {
            return $this->success(['folders' => [], 'spreadsheets' => []]);
        }

        $folders = Schema::hasTable('categories')
            ? DB::table('categories')
                ->where('business_id', $business_id)
                ->where('category_type', 'spreadsheet')
                ->get(['id', 'name'])
            : collect();

        $spreadsheets = DB::table('sheet_spreadsheets')
            ->where('business_id', $business_id)
            ->orderByDesc('id')
            ->get(['id', 'name', 'folder_id', 'created_at', 'updated_at']);

        return $this->success([
            'folders' => $folders,
            'spreadsheets' => $spreadsheets,
        ]);
    }

    protected function ensureModule(string $module): void
    {
        if (! $this->moduleUtil->isModuleInstalled($module)) {
            abort(403, 'Unauthorized action.');
        }
    }
}