<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

/**
 * Spare parts inventory. `on_hand` on the parts row is denormalised — the
 * `stock_movements` ledger is the source of truth. `receive` / `issue` /
 * `adjust` write both atomically.
 */
final class Part extends Model
{
    protected string $table = 'parts';
    protected string $primaryKey = 'part_id';

    public const UNITS = ['ea', 'm', 'kg', 'L', 'set', 'roll', 'box'];

    public static function listForTenant(int $tenantId, array $filters = []): array
    {
        $sql = 'SELECT p.*,
                       (p.on_hand < p.min_stock) AS is_low
                  FROM parts p
                 WHERE p.tenant_id = :t AND p.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['category'])) {
            $sql .= ' AND p.category = :c';
            $params['c'] = $filters['category'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (p.name LIKE :q1 OR p.sku LIKE :q2 OR p.supplier LIKE :q3)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
            $params['q3'] = $q;
        }
        if (!empty($filters['low_only'])) {
            $sql .= ' AND p.on_hand < p.min_stock';
        }
        $sql .= ' ORDER BY p.name';
        return DB::all($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $partId): ?array
    {
        return DB::one(
            'SELECT * FROM parts WHERE tenant_id = :t AND part_id = :id AND deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $partId]
        );
    }

    public static function optionsForTenant(int $tenantId): array
    {
        return DB::all(
            'SELECT part_id, sku, name, unit, unit_cost, on_hand
               FROM parts
              WHERE tenant_id = :t AND deleted_at IS NULL AND is_active = 1
              ORDER BY name',
            ['t' => $tenantId]
        );
    }

    public static function create(int $tenantId, array $data): int
    {
        return (int) DB::insert('parts', [
            'tenant_id'   => $tenantId,
            'sku'         => $data['sku'],
            'name'        => $data['name'],
            'description' => $data['description'] ?? null,
            'category'    => $data['category'] ?? null,
            'unit'        => in_array($data['unit'] ?? '', self::UNITS, true) ? $data['unit'] : 'ea',
            'unit_cost'   => (float) ($data['unit_cost']   ?? 0),
            'on_hand'     => (float) ($data['on_hand']     ?? 0),
            'min_stock'   => (float) ($data['min_stock']   ?? 0),
            'max_stock'   => ($data['max_stock']   ?? '') !== '' ? (float) $data['max_stock']   : null,
            'reorder_qty' => ($data['reorder_qty'] ?? '') !== '' ? (float) $data['reorder_qty'] : null,
            'location'    => $data['location'] ?? null,
            'supplier'    => $data['supplier'] ?? null,
            'is_active'   => !empty($data['is_active']) ? 1 : 1,
        ]);
    }

    public static function update(int $tenantId, int $partId, array $data): void
    {
        $allowed = ['sku','name','description','category','unit','unit_cost','min_stock','max_stock','reorder_qty','location','supplier','is_active'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $partId];
        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if (in_array($col, ['unit_cost','min_stock','max_stock','reorder_qty'], true)) {
                $val = ($val === '' || $val === null) ? null : (float) $val;
            }
            if ($col === 'is_active') { $val = !empty($val) ? 1 : 0; }
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        if (!$set) return;
        DB::run(
            'UPDATE parts SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND part_id = :id',
            $params
        );
    }

    public static function softDelete(int $tenantId, int $partId): void
    {
        DB::run(
            'UPDATE parts SET deleted_at = NOW() WHERE tenant_id = :t AND part_id = :id',
            ['t' => $tenantId, 'id' => $partId]
        );
    }

    // ── Stock movements ─────────────────────────────────────────────────

    public static function movementsFor(int $tenantId, int $partId, int $limit = 100): array
    {
        return DB::all(
            'SELECT m.*, u.username, w.title AS wo_title, w.work_order_id
               FROM stock_movements m
          LEFT JOIN users u       ON u.user_id       = m.user_id
          LEFT JOIN work_orders w ON w.work_order_id = m.work_order_id
              WHERE m.tenant_id = :t AND m.part_id = :p
              ORDER BY m.created_at DESC
              LIMIT ' . (int) $limit,
            ['t' => $tenantId, 'p' => $partId]
        );
    }

    /** In (receive stock). */
    public static function receive(int $tenantId, int $partId, float $qty, ?float $unitCost, ?string $reason, ?int $userId): void
    {
        self::move($tenantId, $partId, 'in', $qty, $unitCost, $reason, null, $userId);
    }

    /** Out (issue stock, optionally to a WO — use consumeToWo for WO consumption). */
    public static function issue(int $tenantId, int $partId, float $qty, ?string $reason, ?int $woId, ?int $userId): void
    {
        self::move($tenantId, $partId, 'out', $qty, null, $reason, $woId, $userId);
    }

    /** Adjust (positive or negative delta — for stock-take corrections). */
    public static function adjust(int $tenantId, int $partId, float $deltaQty, ?string $reason, ?int $userId): void
    {
        $dir = $deltaQty >= 0 ? 'in' : 'out';
        self::move($tenantId, $partId, 'adjust', abs($deltaQty), null, $reason, null, $userId, $dir);
    }

    /**
     * Consume a part on a WO. Writes both a stock_movements row (ledger) and a
     * wo_parts_used row (per-WO denorm) in a single transaction.
     */
    public static function consumeToWo(int $tenantId, int $partId, int $woId, float $qty, ?int $userId): void
    {
        $part = self::findInTenant($tenantId, $partId);
        if (!$part) return;
        $unitCost = (float) $part['unit_cost'];

        DB::tx(function () use ($tenantId, $partId, $woId, $qty, $unitCost, $userId) {
            self::writeMove($tenantId, $partId, 'out', $qty, $unitCost, 'WO consumption', $woId, $userId);
            DB::insert('wo_parts_used', [
                'work_order_id' => $woId,
                'part_id'       => $partId,
                'quantity'      => $qty,
                'unit_cost'     => $unitCost,
                'user_id'       => $userId,
            ]);
        });
    }

    /** Public API entry: single-shot ledger write wrapped in its own transaction. */
    private static function move(
        int $tenantId,
        int $partId,
        string $direction,
        float $qty,
        ?float $unitCost,
        ?string $reason,
        ?int $woId,
        ?int $userId,
        ?string $effectiveDir = null
    ): void {
        DB::tx(function () use ($tenantId, $partId, $direction, $qty, $unitCost, $reason, $woId, $userId, $effectiveDir) {
            self::writeMove($tenantId, $partId, $direction, $qty, $unitCost, $reason, $woId, $userId, $effectiveDir);
        });
    }

    /**
     * Inner ledger writer — NO transaction wrapper. Callable from within an
     * existing transaction (see consumeToWo) or wrapped by move() when used
     * standalone. Keeps on_hand and the ledger row atomic to each other.
     */
    private static function writeMove(
        int $tenantId,
        int $partId,
        string $direction,
        float $qty,
        ?float $unitCost,
        ?string $reason,
        ?int $woId,
        ?int $userId,
        ?string $effectiveDir = null
    ): void {
        $eff = $effectiveDir ?? $direction;
        $delta = ($eff === 'in') ? $qty : -$qty;

        DB::insert('stock_movements', [
            'tenant_id'     => $tenantId,
            'part_id'       => $partId,
            'direction'     => $direction,
            'quantity'      => $qty,
            'unit_cost'     => $unitCost,
            'reason'        => $reason,
            'work_order_id' => $woId,
            'user_id'       => $userId,
        ]);
        DB::run(
            'UPDATE parts SET on_hand = on_hand + :d WHERE tenant_id = :t AND part_id = :id',
            ['d' => $delta, 't' => $tenantId, 'id' => $partId]
        );
    }

    // ── WO parts view ───────────────────────────────────────────────────

    public static function partsUsedOnWo(int $woId): array
    {
        return DB::all(
            'SELECT wu.*, p.sku, p.name, p.unit, (wu.quantity * wu.unit_cost) AS line_cost
               FROM wo_parts_used wu
               JOIN parts p ON p.part_id = wu.part_id
              WHERE wu.work_order_id = :w
              ORDER BY wu.created_at DESC',
            ['w' => $woId]
        );
    }

    public static function totalMaterialCostForWo(int $woId): float
    {
        return (float) DB::value(
            'SELECT COALESCE(SUM(quantity * unit_cost), 0) FROM wo_parts_used WHERE work_order_id = :w',
            ['w' => $woId]
        );
    }

    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(is_active = 1) AS active,
                    SUM(on_hand < min_stock) AS low_stock,
                    SUM(on_hand = 0) AS out_of_stock
               FROM parts
              WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['total'=>0,'active'=>0,'low_stock'=>0,'out_of_stock'=>0];
    }
}
