<?php

namespace App\Models;

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

class DashboardShowTrade extends Model
{
    use HasFactory;

    protected $primaryKey = 'vessel_full';
    public $incrementing = false;
    protected $keyType = 'string';
    public $timestamps = false;
    protected $table = 'V_DASHBOARD_TRADE';

    /**
     * Get all trades that match 'TRADE_SERVICE' condition
     */
    public static function getTrades()
    {
        return self::where('TRADE_SERVICE', 'like', '%trade%');
    }

    /**
     * Get aggregated trade statistics by status
     */
    public static function getTradeStatsByStatus()
    {
        return self::select([
            'commodity_name',
            DB::raw('SUM(current_stock) as total_current_stock'),
            DB::raw('SUM(CASE WHEN status_filter LIKE \'%transit%\' THEN current_stock ELSE 0 END) as total_transit_stock'),
            DB::raw('SUM(CASE WHEN status_filter NOT LIKE \'%transit%\' THEN current_stock ELSE 0 END) as total_non_transit_stock'),
            DB::raw('SUM(free_stock_for_sale) as total_free_stock'),
            DB::raw('AVG(invoice_cif) as average_invoice_cif'),
            DB::raw('AVG(FCA_SELFCOST_PGT) as average_selfcost')
        ])
            ->join('V_STORAGE_ONVESSEL', 'V_DASHBOARD_TRADE.vessel_id', '=', 'V_STORAGE_ONVESSEL.vessel_id')
            ->where('status', 'in stock')
            ->groupBy('commodity_name');
    }

    /**
     * Get future trade statistics, ensuring current_stock is > 0
     */
    public static function getFutureTradeStats()
    {
        return self::select([
            'commodity_name',
            DB::raw('SUM(current_stock) as total_current_stock'),
            DB::raw('SUM(free_stock_for_sale) as total_free_stock'),
            DB::raw('AVG(invoice_cif) as average_invoice_cif'),
            DB::raw('AVG(SELFCOST) as average_selfcost')
        ])
            ->where('status', 'future')
            ->where(DB::raw('ROUND(current_stock, 0)'), '>', 0)
            ->groupBy('commodity_name');
    }
}