<?php

declare(strict_types=1);

namespace App\Models;

use PDOException;

class ProductVariant extends BaseModel
{
    protected string $table = 'producto_variantes';
    private ?bool $quantityColumnAvailable = null;
    private ?bool $minimumStockColumnAvailable = null;

    public function listGroupedByProduct(int $companyId): array
    {
        $hasQuantity = $this->hasQuantityColumn();
        $hasMinimumStock = $this->hasMinimumStockColumn();
        $quantitySelect = $hasQuantity ? 'cantidad,' : 'NULL AS cantidad,';
        $minimumStockSelect = $hasMinimumStock ? 'stock_minimo,' : 'NULL AS stock_minimo,';

        try {
            $stmt = $this->db()->prepare(
                'SELECT
                    id,
                    empresa_id,
                    producto_id,
                    nombre,
                    sku,
                    ' . $quantitySelect . '
                    ' . $minimumStockSelect . '
                    precio_venta,
                    activo
                 FROM producto_variantes
                 WHERE empresa_id = :empresa_id
                 ORDER BY producto_id ASC, id ASC'
            );
            $stmt->execute([':empresa_id' => $companyId]);
        } catch (PDOException $exception) {
            if ($this->isMissingTable($exception)) {
                return [];
            }

            throw $exception;
        }

        $rows = $stmt->fetchAll() ?: [];
        $grouped = [];

        foreach ($rows as $row) {
            $productId = (int) ($row['producto_id'] ?? 0);
            if ($productId <= 0) {
                continue;
            }

            $grouped[$productId][] = $row;
        }

        return $grouped;
    }

    public function deleteByProduct(int $companyId, int $productId): void
    {
        try {
            $stmt = $this->db()->prepare(
                'DELETE FROM producto_variantes
                 WHERE empresa_id = :empresa_id
                   AND producto_id = :producto_id'
            );
            $stmt->execute([
                ':empresa_id' => $companyId,
                ':producto_id' => $productId,
            ]);
        } catch (PDOException $exception) {
            if ($this->isMissingTable($exception)) {
                return;
            }

            throw $exception;
        }
    }

    public function replaceByProduct(int $companyId, int $productId, array $variants): void
    {
        $this->deleteByProduct($companyId, $productId);

        if ($variants === []) {
            return;
        }

        $hasQuantity = $this->hasQuantityColumn();
        $hasMinimumStock = $this->hasMinimumStockColumn();
        $insertSql = ($hasQuantity && $hasMinimumStock)
            ? 'INSERT INTO producto_variantes (
                    empresa_id, producto_id, nombre, sku, cantidad, stock_minimo, precio_venta, activo, created_at, updated_at
                ) VALUES (
                    :empresa_id, :producto_id, :nombre, :sku, :cantidad, :stock_minimo, :precio_venta, 1, NOW(), NOW()
                )'
            : ($hasQuantity
            ? 'INSERT INTO producto_variantes (
                    empresa_id, producto_id, nombre, sku, cantidad, precio_venta, activo, created_at, updated_at
                ) VALUES (
                    :empresa_id, :producto_id, :nombre, :sku, :cantidad, :precio_venta, 1, NOW(), NOW()
                )'
            : ($hasMinimumStock
            ? 'INSERT INTO producto_variantes (
                    empresa_id, producto_id, nombre, sku, stock_minimo, precio_venta, activo, created_at, updated_at
                ) VALUES (
                    :empresa_id, :producto_id, :nombre, :sku, :stock_minimo, :precio_venta, 1, NOW(), NOW()
                )'
            : 'INSERT INTO producto_variantes (
                    empresa_id, producto_id, nombre, sku, precio_venta, activo, created_at, updated_at
                ) VALUES (
                    :empresa_id, :producto_id, :nombre, :sku, :precio_venta, 1, NOW(), NOW()
                )'));

        try {
            $stmt = $this->db()->prepare($insertSql);
        } catch (PDOException $exception) {
            if ($this->isMissingTable($exception)) {
                return;
            }

            throw $exception;
        }

        foreach ($variants as $variant) {
            $name = trim((string) ($variant['nombre'] ?? ''));
            if ($name === '') {
                continue;
            }

            $price = (float) ($variant['precio_venta'] ?? 0);
            if ($price <= 0) {
                continue;
            }

            $sku = trim((string) ($variant['sku'] ?? ''));
            $quantity = max(0, (int) ($variant['cantidad'] ?? 0));
            $minimumStock = max(0, (int) ($variant['stock_minimo'] ?? 0));

            $params = [
                ':empresa_id' => $companyId,
                ':producto_id' => $productId,
                ':nombre' => mb_substr($name, 0, 120),
                ':sku' => $sku !== '' ? mb_substr($sku, 0, 80) : null,
                ':precio_venta' => $price,
            ];

            if ($hasQuantity) {
                $params[':cantidad'] = $quantity;
            }
            if ($hasMinimumStock) {
                $params[':stock_minimo'] = $minimumStock;
            }

            $stmt->execute($params);
        }
    }

    private function hasQuantityColumn(): bool
    {
        if ($this->quantityColumnAvailable !== null) {
            return $this->quantityColumnAvailable;
        }

        try {
            $stmt = $this->db()->prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME = 'producto_variantes'
                   AND COLUMN_NAME = 'cantidad'"
            );
            $stmt->execute();
            $this->quantityColumnAvailable = (int) $stmt->fetchColumn() > 0;
        } catch (PDOException $exception) {
            if ($this->isMissingTable($exception)) {
                $this->quantityColumnAvailable = false;
            } else {
                throw $exception;
            }
        }

        return $this->quantityColumnAvailable;
    }

    private function hasMinimumStockColumn(): bool
    {
        if ($this->minimumStockColumnAvailable !== null) {
            return $this->minimumStockColumnAvailable;
        }

        try {
            $stmt = $this->db()->prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME = 'producto_variantes'
                   AND COLUMN_NAME = 'stock_minimo'"
            );
            $stmt->execute();
            $this->minimumStockColumnAvailable = (int) $stmt->fetchColumn() > 0;
        } catch (PDOException $exception) {
            if ($this->isMissingTable($exception)) {
                $this->minimumStockColumnAvailable = false;
            } else {
                throw $exception;
            }
        }

        return $this->minimumStockColumnAvailable;
    }

    private function isMissingTable(PDOException $exception): bool
    {
        $sqlState = (string) ($exception->errorInfo[0] ?? '');
        $driverCode = (int) ($exception->errorInfo[1] ?? 0);

        if ($sqlState === '42S02' || $driverCode === 1146) {
            return true;
        }

        $message = strtolower($exception->getMessage());
        return str_contains($message, 'table') && str_contains($message, 'doesn\'t exist');
    }
}
