<?php

// app/Models/TotalReport.php
namespace App\Models;

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

class TotalReport extends Model
{
    use HasFactory;

    // Fetch and merge data from sell_transactions, direct_sales, and expenses
    public static function getMergedReport()
    {
        // Use raw queries to ensure proper SQLite compatibility
        $sellTransactions = collect(DB::select("
            SELECT DATE(date_time) as date, SUM(sell_price) as total_sales, SUM(profit) as total_profit 
            FROM sell_transactions 
            WHERE DATE(date_time) >= date('now', '-30 days') 
            GROUP BY DATE(date_time) 
            ORDER BY date ASC
        "))->keyBy('date');

        $directSales = collect(DB::select("
            SELECT DATE(direct_date_time) as date, SUM(direct_sell_price) as total_direct_sales, SUM(direct_profit) as total_direct_profit 
            FROM direct_sales 
            WHERE DATE(direct_date_time) >= date('now', '-30 days') 
            GROUP BY DATE(direct_date_time) 
            ORDER BY date ASC
        "))->keyBy('date');

        $expenses = collect(DB::select("
            SELECT DATE(expense_date) as date, SUM(amount) as total_expenses 
            FROM expenses 
            WHERE DATE(expense_date) >= date('now', '-30 days') 
            GROUP BY DATE(expense_date) 
            ORDER BY date ASC
        "))->keyBy('date');

        // Merge all unique dates from all datasets
        $allDates = $sellTransactions->keys()
            ->merge($directSales->keys())
            ->merge($expenses->keys())
            ->unique()
            ->sort();

        // Merge the data
        $mergedData = collect();

        foreach ($allDates as $date) {
            $totalSales = $sellTransactions[$date]->total_sales ?? 0;
            $totalProfit = $sellTransactions[$date]->total_profit ?? 0;
            $totalExpenses = $expenses[$date]->total_expenses ?? 0;
            $mainProfit = ($totalSales + $totalProfit) - $totalExpenses;

            $mergedData->push([
                'date' => $date,
                'total_sales' => $totalSales,
                'total_profit' => $totalProfit,
                'total_direct_sales' => $directSales[$date]->total_direct_sales ?? 0,
                'total_direct_profit' => $directSales[$date]->total_direct_profit ?? 0,
                'total_expenses' => $totalExpenses,
                'main_profit' => $mainProfit,
            ]);
        }

        return $mergedData->values();
    }
}
