<?php

namespace WMS\Controllers;

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

class ZonePreferencesController
{
    /**
     * GET /api/zone-preferences/summary
     * Agrupa por items.u_subgrupo (familia maestra de SAP).
     */
    public static function summary(array $params): void
    {
        try {
            $db = Database::getConnection();
            $sql = "SELECT
                        COALESCE(NULLIF(i.u_subgrupo,''), '(sin u_subgrupo)') 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 items i ON i.id = izp.item_id
                    LEFT JOIN warehouse_zones z ON z.id = izp.zone_id
                    GROUP BY familia, izp.zone_id, z.code, z.name
                    ORDER BY items_count DESC, familia ASC";
            $rows = $db->query($sql)->fetchAll();
            Response::success($rows);
        } catch (\Throwable $e) {
            Response::error('Error: ' . $e->getMessage(), 500);
        }
    }

    /**
     * GET /api/zone-preferences?familia=...
     * Lista items, opcionalmente filtrados por items.u_subgrupo.
     */
    public static function index(array $params): void
    {
        try {
            $db = Database::getConnection();
            $familia = Request::query('familia');
            $zoneId  = Request::query('zone_id');
            $sql = "SELECT izp.id, izp.item_code, i.item_name, i.item_group, i.u_subgrupo,
                           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";
            $where = [];
            $args = [];
            if ($familia !== null && $familia !== '') {
                if ($familia === '(sin u_subgrupo)') {
                    $where[] = "(i.u_subgrupo IS NULL OR i.u_subgrupo = '')";
                } else {
                    $where[] = "i.u_subgrupo = ?";
                    $args[] = $familia;
                }
            }
            if ($zoneId !== null && $zoneId !== '') {
                $where[] = "izp.zone_id = ?";
                $args[] = (int) $zoneId;
            }
            if ($where) {
                $sql .= " WHERE " . implode(' AND ', $where);
            }
            $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);
        }
    }

    /**
     * GET /api/zone-preferences/{id}
     */
    public static function show(array $params): void
    {
        try {
            $id = (int) ($params['id'] ?? 0);
            $db = Database::getConnection();
            $stmt = $db->prepare(
                "SELECT izp.*, i.item_name, i.item_group, i.u_subgrupo, z.code AS zone_code, z.name AS zone_name
                 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
                 WHERE izp.id = ?"
            );
            $stmt->execute([$id]);
            $row = $stmt->fetch();
            if (!$row) { Response::error('Preferencia no encontrada', 404); return; }
            Response::success($row);
        } catch (\Throwable $e) {
            Response::error('Error: ' . $e->getMessage(), 500);
        }
    }

    /**
     * POST /api/zone-preferences
     * Body: { item_code, zone_id, preferred_level?, max_level?, pallet_weight_kg?, notes? }
     * NOTA: la familia se deriva automáticamente de items.u_subgrupo.
     */
    public static function create(array $params): void
    {
        try {
            $body = Request::body();
            $itemCode = trim((string) ($body['item_code'] ?? ''));
            $zoneId = (int) ($body['zone_id'] ?? 0);
            if ($itemCode === '' || $zoneId <= 0) {
                Response::error('item_code y zone_id son requeridos', 422);
                return;
            }

            $db = Database::getConnection();

            $stmt = $db->prepare("SELECT id, item_name, u_subgrupo FROM items WHERE item_code = ?");
            $stmt->execute([$itemCode]);
            $item = $stmt->fetch();
            if (!$item) { Response::error("Item '{$itemCode}' no encontrado", 404); return; }

            $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; }

            // notes opcional: preservar familia legacy si se envía explícitamente
            $notes = $body['notes'] ?? null;
            if (!$notes && !empty($body['familia'])) {
                $notes = 'familia=' . $body['familia'];
            }

            $stmt = $db->prepare(
                "INSERT INTO item_zone_preferences
                    (item_id, item_code, zone_id, preferred_level, max_level, pallet_weight_kg, source, notes)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                 ON DUPLICATE KEY UPDATE
                    zone_id          = VALUES(zone_id),
                    preferred_level  = VALUES(preferred_level),
                    max_level        = VALUES(max_level),
                    pallet_weight_kg = VALUES(pallet_weight_kg),
                    source           = VALUES(source),
                    notes            = VALUES(notes)"
            );
            $stmt->execute([
                $item['id'],
                $itemCode,
                $zoneId,
                self::nullable($body['preferred_level'] ?? null),
                self::nullable($body['max_level'] ?? null),
                self::nullable($body['pallet_weight_kg'] ?? null),
                $body['source'] ?? 'MANUAL',
                $notes,
            ]);
            $id = (int) $db->lastInsertId();
            if (!$id) {
                $stmt = $db->prepare("SELECT id FROM item_zone_preferences WHERE item_id = ?");
                $stmt->execute([$item['id']]);
                $id = (int) ($stmt->fetchColumn() ?: 0);
            }
            Response::success([
                'id'         => $id,
                'item_code'  => $itemCode,
                'item_name'  => $item['item_name'],
                'u_subgrupo' => $item['u_subgrupo'],
                'zone_code'  => $zone['code'],
            ], 'Preferencia guardada');
        } catch (\Throwable $e) {
            Response::error('Error: ' . $e->getMessage(), 500);
        }
    }

    /**
     * PUT /api/zone-preferences/{id}
     */
    public static function update(array $params): void
    {
        try {
            $id = (int) ($params['id'] ?? 0);
            $body = Request::body();
            $db = Database::getConnection();

            $stmt = $db->prepare("SELECT * FROM item_zone_preferences WHERE id = ?");
            $stmt->execute([$id]);
            $cur = $stmt->fetch();
            if (!$cur) { Response::error('Preferencia no encontrada', 404); return; }

            $allowed = ['zone_id', 'preferred_level', 'max_level', 'pallet_weight_kg', 'notes'];
            $fields = [];
            $values = [];
            foreach ($allowed as $k) {
                if (!array_key_exists($k, $body)) continue;
                $v = $body[$k];
                if ($v === '') $v = null;
                if ($k === 'zone_id' && $v !== null) {
                    $stmt = $db->prepare("SELECT id FROM warehouse_zones WHERE id = ?");
                    $stmt->execute([(int)$v]);
                    if (!$stmt->fetch()) { Response::error('Zona no encontrada', 404); return; }
                    $v = (int) $v;
                }
                $fields[] = "$k = ?";
                $values[] = $v;
            }
            if (empty($fields)) {
                Response::error('Nada para actualizar', 422);
                return;
            }
            $values[] = $id;
            $stmt = $db->prepare("UPDATE item_zone_preferences SET " . implode(', ', $fields) . " WHERE id = ?");
            $stmt->execute($values);
            Response::success(['id' => $id, 'updated' => $stmt->rowCount()], 'Preferencia actualizada');
        } catch (\Throwable $e) {
            Response::error('Error: ' . $e->getMessage(), 500);
        }
    }

    /**
     * DELETE /api/zone-preferences/{id}
     */
    public static function delete(array $params): void
    {
        try {
            $id = (int) ($params['id'] ?? 0);
            $db = Database::getConnection();
            $stmt = $db->prepare("DELETE FROM item_zone_preferences WHERE id = ?");
            $stmt->execute([$id]);
            $n = $stmt->rowCount();
            if ($n === 0) { Response::error('Preferencia no encontrada', 404); return; }
            Response::success(['deleted' => $n], 'Preferencia eliminada');
        } catch (\Throwable $e) {
            Response::error('Error: ' . $e->getMessage(), 500);
        }
    }

    /**
     * PUT /api/zone-preferences/family/{familia}
     * Reasigna la zona destino para TODAS las preferencias de items
     * con items.u_subgrupo = familia.
     */
    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; }

            if ($familia === '(sin u_subgrupo)') {
                $stmt = $db->prepare(
                    "UPDATE item_zone_preferences izp
                     JOIN items i ON i.id = izp.item_id
                     SET izp.zone_id = ?
                     WHERE i.u_subgrupo IS NULL OR i.u_subgrupo = ''"
                );
                $stmt->execute([$zoneId]);
            } else {
                $stmt = $db->prepare(
                    "UPDATE item_zone_preferences izp
                     JOIN items i ON i.id = izp.item_id
                     SET izp.zone_id = ?
                     WHERE i.u_subgrupo = ?"
                );
                $stmt->execute([$zoneId, $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);
        }
    }

    private static function nullable($v) {
        if ($v === null || $v === '') return null;
        return $v;
    }
}