<?php

namespace App\Models;

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

class TikTokTransaction extends Model
{
    use HasFactory;

    protected $table = 'tiktok_transactions';

    protected $fillable = [
        'tiktok_shop_id',
        'uploaded_by',
        'order_id',
        'type',
        'order_date',
        'settled_date',
        'currency',
        'total_revenue',
        'total_fees',
        'net_settlement',
        'commission_fee',
        'transaction_fee',
        'shipping_fee',
        'import_batch_id',
    ];

    protected $casts = [
        'order_date' => 'date',
        'settled_date' => 'date',
        'total_revenue' => 'decimal:2',
        'total_fees' => 'decimal:2',
        'net_settlement' => 'decimal:2',
        'commission_fee' => 'decimal:2',
        'transaction_fee' => 'decimal:2',
        'shipping_fee' => 'decimal:2',
    ];

    /**
     * Get the TikTok Shop this transaction belongs to
     */
    public function tiktokShop()
    {
        return $this->belongsTo(TikTokShop::class, 'tiktok_shop_id');
    }

    /**
     * Get the user who uploaded this transaction (audit trail)
     */
    public function uploader()
    {
        return $this->belongsTo(User::class, 'uploaded_by');
    }

    /**
     * Legacy: Keep user() relationship for backward compatibility
     * @deprecated Use uploader() instead
     */
    public function user()
    {
        return $this->uploader();
    }

    /**
     * Scope: Get only orders (exclude refunds/adjustments)
     */
    public function scopeOrders($query)
    {
        return $query->where('type', 'Order');
    }

    /**
     * Scope: Get by date range
     */
    public function scopeDateRange($query, $startDate, $endDate)
    {
        return $query->whereBetween('order_date', [$startDate, $endDate]);
    }

    /**
     * Get daily aggregated data
     */
    public static function getDailyStats($startDate, $endDate)
    {
        return self::selectRaw('
                order_date as date,
                COUNT(*) as total_orders,
                SUM(CASE WHEN type = "Order" THEN total_revenue ELSE 0 END) as revenue,
                SUM(CASE WHEN type = "Refund" THEN ABS(total_revenue) ELSE 0 END) as refunds,
                SUM(total_fees) as fees,
                SUM(net_settlement) as settlement
            ')
            ->whereBetween('order_date', [$startDate, $endDate])
            ->groupBy('order_date')
            ->orderBy('order_date', 'desc')
            ->get();
    }

    /**
     * Get summary stats for date range
     */
    public static function getSummary($startDate, $endDate)
    {
        return self::selectRaw('
                COUNT(CASE WHEN type = "Order" THEN 1 END) as total_orders,
                COUNT(CASE WHEN type = "Refund" THEN 1 END) as total_refunds,
                SUM(CASE WHEN type = "Order" THEN total_revenue ELSE 0 END) as total_revenue,
                SUM(total_fees) as total_fees,
                SUM(net_settlement) as total_settlement,
                AVG(CASE WHEN type = "Order" THEN total_revenue END) as avg_order_value
            ')
            ->whereBetween('order_date', [$startDate, $endDate])
            ->first();
    }
}
