<?php

namespace WMS\Controllers;

use WMS\Core\Database;
use WMS\Core\Request;
use WMS\Core\Response;

class ZonePreferencesController
{
    /**
     * GET /api/zone-preferences/summary
     * Devuelve el resumen agrupado por familia (extraída de notes).
     */
    public static function summary(array $params): void
    {
        try {
            $db = Database::getConnection();
            $sql = "SELECT
                        TRIM(SUBSTRING_INDEX(izp.notes, '=', -1)) AS familia,
                        izp.zone_id,
                        z.code AS zone_code,
                        z.name AS zone_name,
                        COUNT(*) AS items_count,
                        SUM(CASE WHEN izp.preferred_level IS NOT NULL THEN 1 ELSE 0 END) AS items_with_level,
                        MIN(izp.preferred_level + 0) AS min_level,
                        MAX(izp.preferred_level + 0) AS max_level_pref,
                        MIN(izp.pallet_weight_kg) AS min_pallet_kg,
                        MAX(izp.pallet_weight_kg) AS max_pallet_kg,
                        AVG(izp.pallet_weight_kg) AS avg_pallet_kg
                    FROM item_zone_preferences izp
                    LEFT JOIN warehouse_zones z ON z.id = izp.zone_id
                    WHERE izp.notes LIKE 'familia=%'
                    GROUP BY familia, izp.zone_id, z.code, z.name
                    ORDER BY items_count DESC";
            $rows = $db->query($sql)->fetchAll();
            Response::success($rows);
        } catch (\Throwable $e) {
            Response::error('Error: ' . $e->getMessage(), 500);
        }
    }

    /**
     * GET /api/zone-preferences?familia=...
     * Lista los items con preferencia, opcionalmente filtrados por familia.
     */
    public static function index(array $params): void
    {
        try {
            $db = Database::getConnection();
            $familia = Request::query('familia');
            $sql = "SELECT izp.id, izp.item_code, i.item_name, i.item_group,
                           izp.zone_id, z.code AS zone_code, z.name AS zone_name,
                           izp.preferred_level, izp.max_level,
                           izp.pallet_weight_kg, izp.source, izp.notes, izp.updated_at
                    FROM item_zone_preferences izp
                    LEFT JOIN items i ON i.id = izp.item_id
                    LEFT JOIN warehouse_zones z ON z.id = izp.zone_id";
            $args = [];
            if ($familia) {
                $sql .= " WHERE izp.notes = ?";
                $args[] = 'familia=' . $familia;
            }
            $sql .= " ORDER BY izp.preferred_level + 0 ASC, izp.item_code ASC";
            $stmt = $db->prepare($sql);
            $stmt->execute($args);
            Response::success($stmt->fetchAll());
        } catch (\Throwable $e) {
            Response::error('Error: ' . $e->getMessage(), 500);
        }
    }

    /**
     * PUT /api/zone-preferences/family/{familia}
     * Body: { zone_id: int }
     * Reasigna toda una familia a una zona distinta.
     */
    public static function updateFamily(array $params): void
    {
        try {
            $body = Request::body();
            $familia = (string) ($params['familia'] ?? '');
            $zoneId = (int) ($body['zone_id'] ?? 0);
            if ($familia === '' || $zoneId <= 0) {
                Response::error('familia y zone_id requeridos', 422);
                return;
            }
            $db = Database::getConnection();
            $stmt = $db->prepare("SELECT id, code FROM warehouse_zones WHERE id = ?");
            $stmt->execute([$zoneId]);
            $zone = $stmt->fetch();
            if (!$zone) { Response::error('Zona no encontrada', 404); return; }

            $stmt = $db->prepare(
                "UPDATE item_zone_preferences SET zone_id = ? WHERE notes = ?"
            );
            $stmt->execute([$zoneId, 'familia=' . $familia]);
            $affected = $stmt->rowCount();

            Response::success([
                'familia'      => $familia,
                'new_zone_id'  => $zoneId,
                'new_zone'     => $zone['code'],
                'updated_rows' => $affected,
            ], "Familia $familia reasignada a {$zone['code']} ($affected items)");
        } catch (\Throwable $e) {
            Response::error('Error: ' . $e->getMessage(), 500);
        }
    }
}