<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class ProfitGraphmodel extends Model
{
    use HasFactory;

    public static function getLast30DaysData()
    {
        $tenantId = auth()->user()->tenant_id ?? null;
        
        $sellTransactions = DB::table('sell_transactions')
            ->selectRaw('DATE(date_time) as date, SUM(sell_price) as total_sales, SUM(profit) as total_profit')
            ->where('date_time', '>=', now()->subDays(30))
            ->where('tenant_id', $tenantId)
            ->groupBy('date')
            ->orderBy('date', 'ASC')
            ->get()
            ->keyBy('date');

        $directSales = DB::table('direct_sales')
            ->selectRaw('DATE(direct_date_time) as date, SUM(direct_sell_price) as total_direct_sales, SUM(direct_profit) as total_direct_profit')
            ->where('direct_date_time', '>=', now()->subDays(30))
            ->where('tenant_id', $tenantId)
            ->groupBy('date')
            ->orderBy('date', 'ASC')
            ->get()
            ->keyBy('date');

        $salesDue = DB::table('sales')
            ->selectRaw('DATE(created_at) as date, SUM(due_amount) as total_due_amount')
            ->where('created_at', '>=', now()->subDays(30))
            ->where('tenant_id', $tenantId)
            ->groupBy('date')
            ->orderBy('date', 'ASC')
            ->get()
            ->keyBy('date');

        $allDates = $sellTransactions->keys()->merge($directSales->keys())->merge($salesDue->keys())->unique()->sort();

        $mergedData = collect();

        foreach ($allDates as $date) {
            $mergedData->push([
                'date' => $date,
                'total_sales' => $sellTransactions[$date]->total_sales ?? 0,
                'total_profit' => $sellTransactions[$date]->total_profit ?? 0,
                'total_direct_sales' => $directSales[$date]->total_direct_sales ?? 0,
                'total_direct_profit' => $directSales[$date]->total_direct_profit ?? 0,
                'total_due_amount' => $salesDue[$date]->total_due_amount ?? 0,
            ]);
        }

        return $mergedData->values();
    }
}
