<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Carbon\Carbon;

class FinanceRecord extends Model
{
    use HasFactory;

    protected $fillable = [
        'user_id',
        'record_date',
        'type',
        'category',
        'amount',
        'note',
        'source',
        'ref_id',
        'is_recurring',
        'recurring_type'
    ];

    protected $casts = [
        'record_date' => 'date',
        'amount' => 'decimal:2',
        'is_recurring' => 'boolean',
    ];

    /**
     * Relationships
     */
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function deliveryRecord(): BelongsTo
    {
        return $this->belongsTo(DeliveryRecord::class, 'ref_id');
    }

    public function categoryInfo()
    {
        return $this->belongsTo(FinanceCategory::class, 'category', 'slug');
    }

    /**
     * Scopes
     */
    public function scopeIncome($query)
    {
        return $query->where('type', 'income');
    }

    public function scopeExpense($query)
    {
        return $query->where('type', 'expense');
    }

    public function scopeForUser($query, $userId)
    {
        return $query->where('user_id', $userId);
    }

    public function scopeForMonth($query, $month)
    {
        return $query->whereRaw("DATE_FORMAT(record_date, '%Y-%m') = ?", [$month]);
    }

    public function scopeForYear($query, $year)
    {
        return $query->whereYear('record_date', $year);
    }

    public function scopeBetweenDates($query, $startDate, $endDate)
    {
        return $query->whereBetween('record_date', [$startDate, $endDate]);
    }

    public function scopeByCategory($query, $category)
    {
        return $query->where('category', $category);
    }

    public function scopeManual($query)
    {
        return $query->where('source', 'manual');
    }

    public function scopeFromDelivery($query)
    {
        return $query->where('source', 'delivery');
    }

    /**
     * Attributes
     */
    public function getFormattedDateAttribute()
    {
        return $this->record_date->format('d/m/Y');
    }

    public function getFormattedAmountAttribute()
    {
        return number_format($this->amount, 2);
    }

    public function getIsIncomeAttribute()
    {
        return $this->type === 'income';
    }

    public function getIsExpenseAttribute()
    {
        return $this->type === 'expense';
    }

    /**
     * Static Methods for Analytics
     */
    public static function syncDeliveryCommissions($userId, $month = null)
    {
        $query = DeliveryRecord::where('user_id', $userId);
        
        if ($month) {
            $query->whereRaw("DATE_FORMAT(delivery_date, '%Y-%m') = ?", [$month]);
        }

        $deliveryRecords = $query->get();

        foreach ($deliveryRecords as $delivery) {
            // Check if already synced
            $exists = self::where('source', 'delivery')
                        ->where('ref_id', $delivery->id)
                        ->exists();

            if (!$exists) {
                self::create([
                    'user_id' => $userId,
                    'record_date' => $delivery->delivery_date,
                    'type' => 'income',
                    'category' => 'delivery-commission',
                    'amount' => $delivery->commission_amount,
                    'note' => "ค่าคอมมิชชั่นจัดส่ง {$delivery->parcel_count} ชิ้น",
                    'source' => 'delivery',
                    'ref_id' => $delivery->id,
                ]);
            }
        }
    }

    public static function getMonthlyStats($userId, $month)
    {
        $records = self::forUser($userId)->forMonth($month)->get();

        $totalIncome = $records->where('type', 'income')->sum('amount');
        $totalExpense = $records->where('type', 'expense')->sum('amount');
        $netAmount = $totalIncome - $totalExpense;

        // Category breakdown
        $expenseByCategory = $records->where('type', 'expense')
            ->groupBy('category')
            ->map(function ($items, $category) {
                return [
                    'category' => $category,
                    'amount' => $items->sum('amount'),
                    'count' => $items->count(),
                    'percentage' => 0, // Will calculate later
                ];
            });

        // Calculate percentages
        if ($totalExpense > 0) {
            $expenseByCategory = $expenseByCategory->map(function ($item) use ($totalExpense) {
                $item['percentage'] = round(($item['amount'] / $totalExpense) * 100, 1);
                return $item;
            });
        }

        return [
            'total_income' => $totalIncome,
            'total_expense' => $totalExpense,
            'net_amount' => $netAmount,
            'is_positive' => $netAmount >= 0,
            'expense_by_category' => $expenseByCategory->sortByDesc('amount')->values(),
            'income_sources' => $records->where('type', 'income')
                ->groupBy('source')
                ->map(function ($items) {
                    return $items->sum('amount');
                }),
            'daily_summary' => self::getDailySummary($userId, $month),
        ];
    }

    public static function getDailySummary($userId, $month)
    {
        $startDate = Carbon::parse($month . '-01');
        $endDate = $startDate->copy()->endOfMonth();

        $dailyData = [];
        $currentDate = $startDate->copy();

        while ($currentDate <= $endDate) {
            $dayRecords = self::forUser($userId)
                ->whereDate('record_date', $currentDate)
                ->get();

            $dailyData[] = [
                'date' => $currentDate->format('Y-m-d'),
                'day' => $currentDate->day,
                'income' => $dayRecords->where('type', 'income')->sum('amount'),
                'expense' => $dayRecords->where('type', 'expense')->sum('amount'),
                'net' => $dayRecords->where('type', 'income')->sum('amount') - 
                        $dayRecords->where('type', 'expense')->sum('amount'),
            ];

            $currentDate->addDay();
        }

        return $dailyData;
    }

    public static function getYearlyTrend($userId, $year)
    {
        $monthlyData = [];

        for ($month = 1; $month <= 12; $month++) {
            $monthStr = sprintf('%d-%02d', $year, $month);
            $records = self::forUser($userId)->forMonth($monthStr)->get();

            $monthlyData[] = [
                'month' => $monthStr,
                'month_name' => Carbon::parse($monthStr)->locale('th')->monthName,
                'income' => $records->where('type', 'income')->sum('amount'),
                'expense' => $records->where('type', 'expense')->sum('amount'),
                'net' => $records->where('type', 'income')->sum('amount') - 
                        $records->where('type', 'expense')->sum('amount'),
            ];
        }

        return $monthlyData;
    }

    public static function getExpenseAnalysis($userId, $month)
    {
        $currentMonthExpenses = self::forUser($userId)
            ->expense()
            ->forMonth($month)
            ->get()
            ->groupBy('category')
            ->map(function ($items, $category) {
                return $items->sum('amount');
            });

        // Compare with last month
        $lastMonth = Carbon::parse($month)->subMonth()->format('Y-m');
        $lastMonthExpenses = self::forUser($userId)
            ->expense()
            ->forMonth($lastMonth)
            ->get()
            ->groupBy('category')
            ->map(function ($items, $category) {
                return $items->sum('amount');
            });

        $analysis = [];
        foreach ($currentMonthExpenses as $category => $amount) {
            $lastAmount = $lastMonthExpenses->get($category, 0);
            $change = $lastAmount > 0 ? (($amount - $lastAmount) / $lastAmount) * 100 : 0;

            $analysis[] = [
                'category' => $category,
                'current_amount' => $amount,
                'last_amount' => $lastAmount,
                'change_percentage' => round($change, 1),
                'is_increased' => $amount > $lastAmount,
            ];
        }

        return collect($analysis)->sortByDesc('change_percentage')->values();
    }

    public static function getRecommendations($userId, $month)
    {
        $stats = self::getMonthlyStats($userId, $month);
        $analysis = self::getExpenseAnalysis($userId, $month);
        $recommendations = [];

        // Check if spending is negative
        if (!$stats['is_positive']) {
            $deficit = abs($stats['net_amount']);
            $recommendations[] = [
                'type' => 'critical',
                'icon' => 'fa-exclamation-triangle',
                'message' => "รายจ่ายเกินรายรับ " . number_format($deficit, 2) . " บาท",
                'suggestion' => "ควรลดค่าใช้จ่ายอย่างเร่งด่วน หรือหารายได้เพิ่ม",
            ];
        }

        // Find categories with highest increase
        $topIncreased = collect($analysis)
            ->filter(function ($item) {
                return $item['is_increased'] && $item['change_percentage'] > 20;
            })
            ->take(3);

        foreach ($topIncreased as $item) {
            $category = FinanceCategory::where('slug', $item['category'])->first();
            if ($category) {
                $recommendations[] = [
                    'type' => 'warning',
                    'icon' => 'fa-chart-line',
                    'message' => "ค่า{$category->name}เพิ่มขึ้น {$item['change_percentage']}%",
                    'suggestion' => "พิจารณาลดค่าใช้จ่ายหมวดนี้ลง เพิ่มขึ้น " . 
                                   number_format($item['current_amount'] - $item['last_amount'], 2) . " บาท",
                ];
            }
        }

        // Find top spending categories
        $topSpending = $stats['expense_by_category']->take(3);
        foreach ($topSpending as $item) {
            if ($item['percentage'] > 30) {
                $category = FinanceCategory::where('slug', $item['category'])->first();
                if ($category) {
                    $recommendations[] = [
                        'type' => 'info',
                        'icon' => 'fa-info-circle',
                        'message' => "{$category->name}คิดเป็น {$item['percentage']}% ของค่าใช้จ่ายทั้งหมด",
                        'suggestion' => "หมวดนี้ใช้จ่ายค่อนข้างสูง ควรวางแผนการใช้จ่ายให้รอบคอบ",
                    ];
                }
            }
        }

        return $recommendations;
    }
}