<?php

namespace App\Http\Controllers;

use App\Models\ReportTemplate;
use Illuminate\Http\Request;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;

class ReportTemplateController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index(Request $request): JsonResponse
    {
        try {
            $query = ReportTemplate::with(['creator']);

            // Filter by template type
            if ($request->has('type')) {
                $query->where('template_type', $request->get('type'));
            }

            // Filter by active status
            if ($request->has('active')) {
                $query->where('is_active', $request->get('active'));
            } else {
                $query->active(); // Default to active templates
            }

            // Search by template name
            if ($request->has('search')) {
                $search = $request->get('search');
                $query->where('template_name', 'like', "%{$search}%");
            }

            // Order by
            $orderBy = $request->get('order_by', 'template_name');
            $orderDirection = $request->get('order_direction', 'asc');
            $query->orderBy($orderBy, $orderDirection);

            // Pagination
            $perPage = $request->get('per_page', 15);
            $templates = $query->paginate($perPage);

            // Convert parameters to array for each template
            $templates->getCollection()->transform(function ($template) {
                if ($template->parameters) {
                    $template->parameters = json_decode($template->parameters, true);
                }
                return $template;
            });

            return response()->json([
                'success' => true,
                'data' => $templates->items(),
                'pagination' => [
                    'current_page' => $templates->currentPage(),
                    'per_page' => $templates->perPage(),
                    'total' => $templates->total(),
                    'last_page' => $templates->lastPage(),
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch report templates',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request): JsonResponse
    {
        try {
            $user = $request->user();

            $validated = $request->validate([
                'template_name' => 'required|string|max:255',
                'template_type' => 'required|in:financial,user,booking,commission,performance,system',
                'template_query' => 'required|string',
                'parameters' => 'nullable|array',
                'is_active' => 'sometimes|boolean'
            ]);

            $validated['created_by'] = $user->user_id;

            // Handle default value manually
            if (!isset($validated['is_active'])) {
                $validated['is_active'] = true;
            }

            // Convert parameters to JSON if provided
            if (isset($validated['parameters'])) {
                $validated['parameters'] = json_encode($validated['parameters']);
            }

            $template = ReportTemplate::create($validated);

            // Convert parameters back to array for response
            if ($template->parameters) {
                $template->parameters = json_decode($template->parameters, true);
            }

            return response()->json([
                'success' => true,
                'message' => 'Report template created successfully',
                'data' => $template->load('creator')
            ], 201);
        } catch (\Illuminate\Validation\ValidationException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Validation failed',
                'errors' => $e->errors()
            ], 422);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to create report template',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }

    /**
     * Display the specified resource.
     */
    public function show(string $id): JsonResponse
    {
        try {
            $template = ReportTemplate::with(['creator'])->find($id);

            if (!$template) {
                return response()->json([
                    'success' => false,
                    'message' => 'Report template not found'
                ], 404);
            }

            // Convert parameters to array for response
            if ($template->parameters) {
                $template->parameters = json_decode($template->parameters, true);
            }

            return response()->json([
                'success' => true,
                'data' => $template
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch report template',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, string $id): JsonResponse
    {
        try {
            $template = ReportTemplate::find($id);

            if (!$template) {
                return response()->json([
                    'success' => false,
                    'message' => 'Report template not found'
                ], 404);
            }

            $validated = $request->validate([
                'template_name' => 'sometimes|string|max:255',
                'template_type' => 'sometimes|in:financial,user,booking,commission,performance,system',
                'template_query' => 'sometimes|string',
                'parameters' => 'nullable|array',
                'is_active' => 'sometimes|boolean'
            ]);

            // ✅ Corrected template query - commission_earnings table ke bina
            if ($request->has('template_query') && empty($validated['template_query'])) {
                $validated['template_query'] = "SELECT
    DATE_FORMAT(b.created_at, '%Y-%m') as month,
    COUNT(b.booking_id) as total_bookings,
    COALESCE(SUM(b.total_amount), 0) as total_revenue,
    COALESCE(SUM(b.commission_amount), 0) as total_commissions,
    COALESCE(AVG(b.total_amount), 0) as avg_booking_value
FROM bookings b
WHERE b.created_at BETWEEN ? AND ?
GROUP BY DATE_FORMAT(b.created_at, '%Y-%m')
ORDER BY month DESC";
            }

            // Convert parameters to JSON if provided
            if (isset($validated['parameters'])) {
                $validated['parameters'] = json_encode($validated['parameters']);
            }

            $template->update($validated);

            // Convert parameters back to array for response
            if ($template->parameters) {
                $template->parameters = json_decode($template->parameters, true);
            }

            return response()->json([
                'success' => true,
                'message' => 'Report template updated successfully',
                'data' => $template->load('creator')
            ]);
        } catch (\Illuminate\Validation\ValidationException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Validation failed',
                'errors' => $e->errors()
            ], 422);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to update report template',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(string $id): JsonResponse
    {
        try {
            $template = ReportTemplate::find($id);

            if (!$template) {
                return response()->json([
                    'success' => false,
                    'message' => 'Report template not found'
                ], 404);
            }

            $template->delete();

            return response()->json([
                'success' => true,
                'message' => 'Report template deleted successfully'
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to delete report template',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }

    /**
     * Activate template
     */
    public function activate(string $id): JsonResponse
    {
        try {
            $template = ReportTemplate::find($id);

            if (!$template) {
                return response()->json([
                    'success' => false,
                    'message' => 'Report template not found'
                ], 404);
            }

            $template->activate();

            return response()->json([
                'success' => true,
                'message' => 'Report template activated successfully',
                'data' => $template->load('creator')
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to activate report template',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }

    /**
     * Deactivate template
     */
    public function deactivate(string $id): JsonResponse
    {
        try {
            $template = ReportTemplate::find($id);

            if (!$template) {
                return response()->json([
                    'success' => false,
                    'message' => 'Report template not found'
                ], 404);
            }

            $template->deactivate();

            return response()->json([
                'success' => true,
                'message' => 'Report template deactivated successfully',
                'data' => $template->load('creator')
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to deactivate report template',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }

    public function generateReport(Request $request, string $id): JsonResponse
    {
        try {
            $user = $request->user();
            $template = ReportTemplate::active()->find($id);

            if (!$template) {
                return response()->json([
                    'success' => false,
                    'message' => 'Report template not found or inactive'
                ], 404);
            }

            $parameters = $this->getTemplateParametersSafely($template);
            $validatedParams = [];

            // Auto-populate current month if date parameters are required but not provided
            $hasDateParams = false;
            foreach ($parameters as $param) {
                if (isset($param['name']) && ($param['name'] === 'start_date' || $param['name'] === 'end_date')) {
                    $hasDateParams = true;
                    break;
                }
            }

            if ($hasDateParams) {
                // Set default to current month if dates not provided
                if (!$request->has('start_date')) {
                    $request->merge(['start_date' => now()->startOfMonth()->format('Y-m-d')]);
                }
                if (!$request->has('end_date')) {
                    $request->merge(['end_date' => now()->endOfMonth()->format('Y-m-d')]);
                }
            }

            // Only validate if template has parameters
            if (is_array($parameters) && !empty($parameters)) {
                $rules = [];
                foreach ($parameters as $param) {
                    if (isset($param['name'])) {
                        $rules[$param['name']] = $param['validation'] ?? 'required';
                    }
                }

                // Update validation rules to use after_or_equal
                if (isset($rules['end_date']) && str_contains($rules['end_date'], 'after:start_date')) {
                    $rules['end_date'] = str_replace('after:start_date', 'after_or_equal:start_date', $rules['end_date']);
                }

                if (!empty($rules)) {
                    $validatedParams = $request->validate($rules);
                }
            }

            // Rest of the method remains same...
            $reportData = $this->executeTemplateQuery($template, $validatedParams);

            $report = \App\Models\Report::create([
                'report_name' => $template->template_name . ' - ' . now()->format('Y-m-d'),
                'report_type' => $template->template_type,
                'report_data' => $reportData,
                'date_range_start' => $validatedParams['start_date'] ?? null,
                'date_range_end' => $validatedParams['end_date'] ?? null,
                'generated_by' => $user->user_id
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Report generated successfully using template',
                'data' => $report->load('generator')
            ], 201);
        } catch (\Illuminate\Validation\ValidationException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Validation failed',
                'errors' => $e->errors()
            ], 422);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to generate report using template',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }

    /**
     * Safely get template parameters
     */
    private function getTemplateParametersSafely(ReportTemplate $template): array
    {
        try {
            $parameters = $template->parameters;

            // If parameters is already array, return it
            if (is_array($parameters)) {
                return $parameters;
            }

            // If parameters is JSON string, decode it
            if (is_string($parameters)) {
                $decoded = json_decode($parameters, true);
                return is_array($decoded) ? $decoded : [];
            }

            // If parameters is null or other, return empty array
            return [];
        } catch (\Exception $e) {
            return [];
        }
    }

    public function getPredefinedTemplates(): JsonResponse
    {
        try {
            $templates = [
                [
                    'template_name' => 'Monthly Financial Summary',
                    'template_type' => 'financial',
                    'template_query' => "SELECT
                    DATE_FORMAT(b.created_at, '%Y-%m') as month,
                    COUNT(b.booking_id) as total_bookings,
                    SUM(b.total_amount) as total_revenue,
                    SUM(b.commission_amount) as total_commissions,
                    AVG(b.total_amount) as avg_booking_value
                FROM bookings b
                WHERE b.payment_status = 'paid'
                AND b.created_at BETWEEN ? AND ?
                GROUP BY DATE_FORMAT(b.created_at, '%Y-%m')
                ORDER BY month DESC",
                    'parameters' => [
                        ['name' => 'start_date', 'type' => 'date', 'validation' => 'required|date', 'label' => 'Start Date'],
                        ['name' => 'end_date', 'type' => 'date', 'validation' => 'required|date|after_or_equal:start_date', 'label' => 'End Date']
                    ]
                ],
                // Update other templates similarly...
            ];

            return response()->json([
                'success' => true,
                'data' => $templates,
                'message' => 'Predefined templates retrieved successfully'
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch predefined templates',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }
    /**
     * Create template from predefined (With Model Cast)
     */
    public function createFromPredefined(Request $request): JsonResponse
    {
        try {
            $user = $request->user();

            $validated = $request->validate([
                'template_name' => 'required|string|max:255',
                'template_type' => 'required|in:financial,user,booking,commission,performance,system',
                'template_query' => 'required|string',
                'parameters' => 'sometimes|array',
                'is_active' => 'sometimes|boolean'
            ]);

            $validated['created_by'] = $user->user_id;

            // Handle default value manually
            if (!isset($validated['is_active'])) {
                $validated['is_active'] = true;
            }

            // Ensure parameters is always set, even if empty
            if (!isset($validated['parameters'])) {
                $validated['parameters'] = [];
            }

            // Model cast automatically handle JSON conversion
            $template = ReportTemplate::create($validated);

            return response()->json([
                'success' => true,
                'message' => 'Template created from predefined successfully',
                'data' => $template->load('creator')
            ], 201);
        } catch (\Illuminate\Validation\ValidationException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Validation failed',
                'errors' => $e->errors()
            ], 422);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to create template from predefined',
                'error' => config('app.debug') ? $e->getMessage() : null
            ], 500);
        }
    }

    private function executeTemplateQuery(ReportTemplate $template, array $parameters): array
    {
        try {
            $query = $template->template_query;
            $bindings = array_values($parameters);

            // Execute the query
            $results = DB::select($query, $bindings);

            // Convert stdClass to array
            $data = array_map(function ($item) {
                return (array) $item;
            }, $results);

            // ✅ TEMPORARILY DISABLE CURRENCY FORMATTING FOR USER REPORTS
            // Format currency values (only for financial reports)
            if ($template->template_type === 'financial') {
                $data = $this->formatCurrencyValues($data);
            }

            return [
                'summary' => $this->generateSummary($data, $template->template_type),
                'detailed_data' => $data,
                'total_records' => count($data),
                'generated_using' => $template->template_name,
                'parameters_used' => $parameters,
                'generated_at' => now()->toISOString(),
                'currency' => 'USD',
                'note' => 'Real data from database'
            ];
        } catch (\Exception $e) {
            return [
                'summary' => ['error' => 'Query execution failed', 'message' => $e->getMessage()],
                'detailed_data' => [],
                'total_records' => 0,
                'generated_using' => $template->template_name,
                'parameters_used' => $parameters,
                'generated_at' => now()->toISOString(),
                'currency' => 'USD',
                'note' => 'Query failed: ' . $e->getMessage()
            ];
        }
    }


    /**
     * Generate summary based on data and template type
     */
    /**
     * Generate summary based on actual data
     */
    private function generateSummary(array $data, string $templateType): array
    {
        if (empty($data)) {
            return ['message' => 'No data available for the selected parameters'];
        }

        switch ($templateType) {
            case 'financial':
                $totalRevenue = 0;
                $totalCommissions = 0;
                $totalBookings = 0;

                foreach ($data as $row) {
                    $totalRevenue += floatval(str_replace([',', '$'], '', $row['total_revenue'] ?? $row['revenue'] ?? 0));
                    $totalCommissions += floatval(str_replace([',', '$'], '', $row['total_commissions'] ?? $row['commissions'] ?? 0));
                    $totalBookings += intval($row['total_bookings'] ?? $row['bookings'] ?? 0);
                }

                return [
                    'total_revenue' => '$' . number_format($totalRevenue, 2),
                    'total_commissions' => '$' . number_format($totalCommissions, 2),
                    'total_bookings' => $totalBookings,
                    'avg_booking_value' => $totalBookings > 0 ? '$' . number_format($totalRevenue / $totalBookings, 2) : '$0.00'
                ];

            case 'user':
                $totalUsers = 0;
                $completedProfiles = 0;
                $verifiedUsers = 0;

                foreach ($data as $row) {
                    $totalUsers += intval($row['total_users'] ?? 0);
                    $completedProfiles += intval($row['completed_profiles'] ?? $row['is_profile_completed'] ?? 0);
                    $verifiedUsers += intval($row['verified_users'] ?? $row['is_verified'] ?? 0);
                }

                return [
                    'total_users' => $totalUsers,
                    'completed_profiles' => $completedProfiles,
                    'verified_users' => $verifiedUsers,
                    'completion_rate' => $totalUsers > 0 ? number_format(($completedProfiles / $totalUsers) * 100, 2) . '%' : '0%',
                    'verification_rate' => $totalUsers > 0 ? number_format(($verifiedUsers / $totalUsers) * 100, 2) . '%' : '0%'
                ];

            case 'booking':
                $totalBookings = 0;
                $totalRevenue = 0;
                $activeBookings = 0;

                foreach ($data as $row) {
                    $totalBookings += intval($row['total_bookings'] ?? 0);
                    $totalRevenue += floatval(str_replace([',', '$'], '', $row['total_revenue'] ?? $row['revenue'] ?? 0));
                    if (isset($row['booking_status']) && in_array($row['booking_status'], ['confirmed', 'active'])) {
                        $activeBookings++;
                    }
                }

                return [
                    'total_bookings' => $totalBookings,
                    'total_revenue' => '$' . number_format($totalRevenue, 2),
                    'active_bookings' => $activeBookings,
                    'completion_rate' => $totalBookings > 0 ? number_format((count($data) / $totalBookings) * 100, 2) . '%' : '0%'
                ];

            case 'commission':
                $totalCommissions = 0;
                $totalEarnings = 0;
                $pendingPayouts = 0;

                foreach ($data as $row) {
                    $totalCommissions += floatval(str_replace([',', '$'], '', $row['total_commissions'] ?? $row['commission_amount'] ?? 0));
                    $totalEarnings += floatval(str_replace([',', '$'], '', $row['total_earnings'] ?? $row['earnings'] ?? 0));
                    if (isset($row['earning_status']) && $row['earning_status'] === 'pending') {
                        $pendingPayouts++;
                    }
                }

                return [
                    'total_commissions' => '$' . number_format($totalCommissions, 2),
                    'total_earnings' => '$' . number_format($totalEarnings, 2),
                    'pending_payouts' => $pendingPayouts,
                    'avg_commission_rate' => $totalEarnings > 0 ? number_format(($totalCommissions / $totalEarnings) * 100, 2) . '%' : '0%'
                ];

            default:
                return [
                    'total_records' => count($data),
                    'data_available' => !empty($data),
                    'message' => 'Report summary generated from actual data'
                ];
        }
    }


    /**
     * Format currency values in data (Only for actual currency fields)
     */
    private function formatCurrencyValues(array $data): array
    {
        // Define currency-related fields only
        $currencyFields = ['amount', 'revenue', 'commission', 'rate', 'value', 'earning', 'payout', 'price', 'fee', 'total'];

        // Define non-currency fields (should not be formatted)
        $nonCurrencyFields = ['count', 'users', 'bookings', 'id', 'rating', 'percentage', 'rate', 'years', 'months', 'days'];

        foreach ($data as &$item) {
            foreach ($item as $key => &$value) {
                if (is_numeric($value)) {
                    $shouldFormat = false;
                    $keyLower = strtolower($key);

                    // Check if this is a currency field
                    foreach ($currencyFields as $field) {
                        if (strpos($keyLower, $field) !== false) {
                            $shouldFormat = true;
                            break;
                        }
                    }

                    // Check if this is definitely NOT a currency field
                    foreach ($nonCurrencyFields as $field) {
                        if (strpos($keyLower, $field) !== false) {
                            $shouldFormat = false;
                            break;
                        }
                    }

                    if ($shouldFormat) {
                        $value = '$' . number_format((float)$value, 2);
                    }
                }
            }
        }

        return $data;
    }


    /**
     * Updated sample data with proper formatting
     */
    private function getSampleData(string $templateType, array $parameters): array
    {
        switch ($templateType) {
            case 'financial':
                return [
                    'summary' => [
                        'total_revenue' => '$150,000.00',
                        'total_commissions' => '$22,500.00',
                        'total_bookings' => 450,
                        'avg_booking_value' => '$333.33'
                    ],
                    'detailed_data' => [
                        [
                            'month' => date('Y-m'),
                            'total_bookings' => 150,
                            'total_revenue' => '$50,000.00',
                            'total_commissions' => '$7,500.00',
                            'avg_booking_value' => '$333.33'
                        ]
                    ],
                    'total_records' => 1,
                    'generated_using' => 'Financial Template',
                    'parameters_used' => $parameters,
                    'generated_at' => now()->toISOString(),
                    'currency' => 'USD',
                    'note' => 'Sample data used - actual query execution failed'
                ];

            default:
                return [
                    'summary' => ['message' => 'Report data generated'],
                    'detailed_data' => [],
                    'total_records' => 0,
                    'generated_using' => 'Template',
                    'parameters_used' => $parameters,
                    'generated_at' => now()->toISOString(),
                    'currency' => 'USD',
                    'note' => 'Sample data used - actual query execution failed'
                ];
        }
    }
}
