<?php

namespace App\Models;

class InventoryTransactionModel extends TenantModel
{
    protected $table         = 'inventory_transactions';
    protected $primaryKey    = 'id';
    protected $returnType    = 'object';
    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $updatedField  = '';

    protected $allowedFields = [
        'inventory_item_id',
        'type',
        'quantity',
        'reference',
        'created_by',
    ];

    public function forItem(int $itemId): array
    {
        return $this->select('inventory_transactions.*, users.full_name as created_by_name')
            ->join('users', 'users.id = inventory_transactions.created_by', 'left')
            ->where('inventory_item_id', $itemId)
            ->orderBy('inventory_transactions.created_at', 'desc')
            ->findAll();
    }

    /**
     * Real daily net stock-value movement for the last N days — the
     * "Stock Value Trend" the original mockup wanted, honestly derived
     * from actual ledger entries rather than a reconstructed history
     * that predates this table (see the migration's doc comment). Days
     * before the ledger existed simply show zero movement, which is the
     * truth, not a gap papered over.
     *
     * @return array<string, string> date (Y-m-d) => net value change that day
     */
    public function dailyNetValueTrend(int $days = 30): array
    {
        $from = date('Y-m-d', strtotime('-' . ($days - 1) . ' days'));

        $rows = $this->select('DATE(inventory_transactions.created_at) as tx_date, SUM(inventory_transactions.quantity * inventory_items.unit_cost) as net_value')
            ->join('inventory_items', 'inventory_items.id = inventory_transactions.inventory_item_id')
            ->where('inventory_transactions.created_at >=', $from . ' 00:00:00')
            ->groupBy('tx_date')
            ->findAll();

        $byDate = [];

        foreach ($rows as $row) {
            $byDate[$row->tx_date] = $row->net_value;
        }

        $trend = [];

        for ($i = $days - 1; $i >= 0; $i--) {
            $date          = date('Y-m-d', strtotime("-{$i} days"));
            $trend[$date]  = $byDate[$date] ?? '0.00';
        }

        return $trend;
    }
}
