<?php

declare(strict_types=1);

namespace App\Models;

class InventoryMovement extends BaseModel
{
    protected string $table = 'movimientos_inventario';
    /** @var array<string,bool> */
    private array $columnAvailabilityCache = [];

    public function latestWithProduct(int $companyId, int $limit = 60): array
    {
        $hasVariantColumn = $this->tableHasColumn('movimientos_inventario', 'variante_id');
        $variantSelect = $hasVariantColumn ? 'm.variante_id, v.nombre AS variante_nombre,' : 'NULL AS variante_id, NULL AS variante_nombre,';
        $variantJoin = $hasVariantColumn
            ? ' LEFT JOIN producto_variantes v ON v.id = m.variante_id AND v.empresa_id = m.empresa_id '
            : '';

        $sql = 'SELECT m.id, m.tipo, m.referencia, m.cantidad, m.costo_unitario, m.created_at,
                       ' . $variantSelect . '
                       m.sucursal_id, s.nombre AS sucursal,
                       p.nombre AS producto, p.stock_actual, p.stock_minimo
                FROM movimientos_inventario m
                INNER JOIN productos p ON p.id = m.producto_id
                LEFT JOIN sucursales s ON s.id = m.sucursal_id'
                . $variantJoin .
                '
                WHERE m.empresa_id = :empresa_id
                ORDER BY m.id DESC
                LIMIT :limit';

        $stmt = $this->db()->prepare($sql);
        $stmt->bindValue(':empresa_id', $companyId, \PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll() ?: [];
    }

    public function transferHistory(int $companyId, int $limit = 40): array
    {
        $limit = max(1, min(100, $limit));
        $hasVariantColumn = $this->tableHasColumn('movimientos_inventario', 'variante_id');
        $variantSelect = $hasVariantColumn ? 'mo.variante_id, v.nombre AS variante_nombre,' : 'NULL AS variante_id, NULL AS variante_nombre,';
        $variantJoin = $hasVariantColumn
            ? ' LEFT JOIN producto_variantes v ON v.id = mo.variante_id AND v.empresa_id = mo.empresa_id '
            : '';
        $variantMatch = $hasVariantColumn
            ? ' AND ((md.variante_id IS NULL AND mo.variante_id IS NULL) OR md.variante_id = mo.variante_id) '
            : '';

        $sql = 'SELECT
                    mo.id,
                    mo.referencia,
                    mo.created_at,
                    mo.cantidad,
                    mo.costo_unitario,
                    ' . $variantSelect . '
                    p.nombre AS producto,
                    so.nombre AS sucursal_origen,
                    sd.nombre AS sucursal_destino
                FROM movimientos_inventario mo
                INNER JOIN movimientos_inventario md
                    ON md.empresa_id = mo.empresa_id
                   AND md.producto_id = mo.producto_id
                   AND md.referencia = mo.referencia
                   AND md.tipo = "entrada"
                   AND md.id <> mo.id'
                   . $variantMatch .
                '
                INNER JOIN productos p ON p.id = mo.producto_id
                LEFT JOIN sucursales so ON so.id = mo.sucursal_id
                LEFT JOIN sucursales sd ON sd.id = md.sucursal_id'
                . $variantJoin .
                '
                WHERE mo.empresa_id = :empresa_id
                  AND mo.tipo = "salida"
                  AND mo.referencia LIKE "TRASLADO#%"
                ORDER BY mo.id DESC
                LIMIT :limit';

        $stmt = $this->db()->prepare($sql);
        $stmt->bindValue(':empresa_id', $companyId, \PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll() ?: [];
    }

    private function tableHasColumn(string $table, string $column): bool
    {
        $cacheKey = $table . '.' . $column;
        if (array_key_exists($cacheKey, $this->columnAvailabilityCache)) {
            return $this->columnAvailabilityCache[$cacheKey];
        }

        $stmt = $this->db()->prepare(
            "SELECT COUNT(*) FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = :table
               AND COLUMN_NAME = :column"
        );
        $stmt->execute([
            ':table' => $table,
            ':column' => $column,
        ]);

        $this->columnAvailabilityCache[$cacheKey] = (int) $stmt->fetchColumn() > 0;
        return $this->columnAvailabilityCache[$cacheKey];
    }
}
