<?php
require_once 'Db.php';

class OMIE extends Db
{
    private string $defaultMarketType = 'omie_pt';

    private function daily24SourceSql(string $suffix = ''): string
    {
        $marketType = ':market_type' . $suffix;
        $marketTypeFallback = ':market_type_fallback' . $suffix;
        $marketTypeExists = ':market_type_exists' . $suffix;

        return "SELECT OMI_FEC, OMI_TIP,
                       OMI_H01, OMI_H02, OMI_H03, OMI_H04, OMI_H05, OMI_H06,
                       OMI_H07, OMI_H08, OMI_H09, OMI_H10, OMI_H11, OMI_H12,
                       OMI_H13, OMI_H14, OMI_H15, OMI_H16, OMI_H17, OMI_H18,
                       OMI_H19, OMI_H20, OMI_H21, OMI_H22, OMI_H23, OMI_H24,
                       OMI_REV
                FROM [bd_lecturas].[dbo].[vw_OMIE_PRE_24]
                WHERE OMI_TIP = {$marketType}
                UNION ALL
                SELECT base.OMI_FEC, base.OMI_TIP,
                       base.OMI_H01, base.OMI_H02, base.OMI_H03, base.OMI_H04, base.OMI_H05, base.OMI_H06,
                       base.OMI_H07, base.OMI_H08, base.OMI_H09, base.OMI_H10, base.OMI_H11, base.OMI_H12,
                       base.OMI_H13, base.OMI_H14, base.OMI_H15, base.OMI_H16, base.OMI_H17, base.OMI_H18,
                       base.OMI_H19, base.OMI_H20, base.OMI_H21, base.OMI_H22, base.OMI_H23, base.OMI_H24,
                       base.OMI_REV
                FROM [bd_lecturas].[dbo].[OMIE_PRE] base
                WHERE base.OMI_TIP = {$marketTypeFallback}
                  AND NOT EXISTS (
                      SELECT 1
                      FROM [bd_lecturas].[dbo].[vw_OMIE_PRE_24] vw
                      WHERE vw.OMI_FEC = base.OMI_FEC
                        AND vw.OMI_TIP = {$marketTypeExists}
                  )";
    }

    private function averageExpression(string $prefix, int $count): string
    {
        $sumParts = [];
        $countParts = [];

        for ($i = 1; $i <= $count; $i++) {
            $column = $prefix . str_pad((string) $i, 2, '0', STR_PAD_LEFT);
            $sumParts[] = "CASE WHEN {$column} IS NOT NULL THEN CAST({$column} AS float) ELSE 0 END";
            $countParts[] = "CASE WHEN {$column} IS NOT NULL THEN 1 ELSE 0 END";
        }

        return '(' . implode(' + ', $sumParts) . ') / NULLIF((' . implode(' + ', $countParts) . '), 0)';
    }

    private function missingCountExpression(string $prefix, int $count): string
    {
        $parts = [];

        for ($i = 1; $i <= $count; $i++) {
            $column = $prefix . str_pad((string) $i, 2, '0', STR_PAD_LEFT);
            $parts[] = "CASE WHEN {$column} IS NULL THEN 1 ELSE 0 END";
        }

        return implode(' + ', $parts);
    }

    private function buildSeriesFromRow(array $row, string $prefix, int $count): array
    {
        $labels = [];
        $values = [];
        $missing = [];

        for ($i = 1; $i <= $count; $i++) {
            $key = $prefix . str_pad((string) $i, 2, '0', STR_PAD_LEFT);
            $rawValue = array_key_exists($key, $row) ? $row[$key] : null;
            $value = $rawValue !== null ? (float) $rawValue : null;

            if ($prefix === 'OMI_H') {
                $labels[] = 'H' . $i;
            } else {
                $hour = intdiv($i - 1, 4) + 1;
                $quarter = (($i - 1) % 4) + 1;
                $labels[] = 'H' . $hour . 'Q' . $quarter;
            }

            $values[] = $value;

            if ($value === null) {
                $missing[] = $labels[count($labels) - 1];
            }
        }

        return [
            'labels' => $labels,
            'values' => $values,
            'missing' => $missing,
        ];
    }

    private function marketType(string $marketType = null): string
    {
        return $marketType ?: $this->defaultMarketType;
    }

    public function getPrecioMedioPorDia($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT OMI_FEC,
                         {$this->averageExpression('OMI_H', 24)} AS precio_medio
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE OMI_FEC BETWEEN CONVERT(DATE, :fecha_inicio, 23) AND CONVERT(DATE, :fecha_fin, 23)
                  ORDER BY OMI_FEC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getPrecioMedioPorMes($mes, $anio): array|false
    {
        $query = "SELECT FORMAT(OMI_FEC, 'yyyy-MM') AS mes,
                         AVG({$this->averageExpression('OMI_H', 24)}) AS precio_medio
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE FORMAT(OMI_FEC, 'yyyy-MM') = :mes_anio
                  GROUP BY FORMAT(OMI_FEC, 'yyyy-MM')";

        $stmt = $this->conn->prepare($query);
        $stmt->execute(['mes_anio' => sprintf('%04d-%02d', $anio, $mes)]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getPrecioMedioAnualPorRango($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT YEAR(OMI_FEC) AS anio,
                         AVG({$this->averageExpression('OMI_H', 24)}) AS precio_medio_anual
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE OMI_FEC BETWEEN :fecha_inicio AND :fecha_fin
                  GROUP BY YEAR(OMI_FEC)
                  ORDER BY anio ASC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);

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

    public function getPrecioMedioSolarMensual($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT FORMAT(Fecha, 'yyyy-MM') AS mes,
                         AVG(pre_medio_dia_solar) AS precio_medio_solar
                  FROM [bd_lecturas].[dbo].[LIST_OMIE_PM_HR_SOLARES]
                  WHERE Fecha BETWEEN :fecha_inicio AND :fecha_fin
                  GROUP BY FORMAT(Fecha, 'yyyy-MM')
                  ORDER BY mes ASC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getPrecioMedioMensualPorRango($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT FORMAT(OMI_FEC, 'yyyy-MM') AS mes,
                         AVG({$this->averageExpression('OMI_H', 24)}) AS precio_medio_mensual
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE OMI_FEC BETWEEN :fecha_inicio AND :fecha_fin
                  GROUP BY FORMAT(OMI_FEC, 'yyyy-MM')
                  ORDER BY mes ASC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);

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

    public function getPrecioMedioSolarMensualPorRango($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT FORMAT(Fecha, 'yyyy-MM') AS mes,
                         AVG(pre_medio_dia_solar) AS precio_medio_solar_mensual
                  FROM [bd_lecturas].[dbo].[LIST_OMIE_PM_HR_SOLARES]
                  WHERE Fecha BETWEEN :fecha_inicio AND :fecha_fin
                  GROUP BY FORMAT(Fecha, 'yyyy-MM')
                  ORDER BY mes ASC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);

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

    public function getPrecioAnualAcumulado($anio): array|false
    {
        $query = "SELECT SUM(pre_medio_dia_solar) AS precio_anual_acumulado
                  FROM [bd_lecturas].[dbo].[LIST_OMIE_PM_HR_SOLARES]
                  WHERE YEAR(Fecha) = :anio";

        $stmt = $this->conn->prepare($query);
        $stmt->execute(['anio' => $anio]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getPrecioMedioMensual($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT FORMAT(OMI_FEC, 'yyyy-MM') AS mes,
                         AVG({$this->averageExpression('OMI_H', 24)}) AS precio_medio
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE OMI_FEC BETWEEN CONVERT(DATE, :fecha_inicio, 23) AND CONVERT(DATE, :fecha_fin, 23)
                  GROUP BY FORMAT(OMI_FEC, 'yyyy-MM')
                  ORDER BY mes";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getPrecioMedioAnual($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT YEAR(OMI_FEC) AS anio,
                         AVG({$this->averageExpression('OMI_H', 24)}) AS precio_medio
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE OMI_FEC BETWEEN CAST(:fecha_inicio AS DATE) AND CAST(:fecha_fin AS DATE)
                  GROUP BY YEAR(OMI_FEC)
                  ORDER BY anio";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => date('Y-m-d', strtotime($fecha_inicio)),
            'fecha_fin' => date('Y-m-d', strtotime($fecha_fin)),
        ]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getDetallePorDia($fecha): array|false
    {
        $query = "SELECT OMI_H01, OMI_H02, OMI_H03, OMI_H04, OMI_H05,
                         OMI_H06, OMI_H07, OMI_H08, OMI_H09, OMI_H10,
                         OMI_H11, OMI_H12, OMI_H13, OMI_H14, OMI_H15,
                         OMI_H16, OMI_H17, OMI_H18, OMI_H19, OMI_H20,
                         OMI_H21, OMI_H22, OMI_H23, OMI_H24
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE OMI_FEC = :fecha";

        $stmt = $this->conn->prepare($query);
        $stmt->execute(['fecha' => $fecha]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getPrecioMedioSolarPorDia($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT [Fecha], [pre_medio_dia_solar]
                  FROM [bd_lecturas].[dbo].[LIST_OMIE_PM_HR_SOLARES]
                  WHERE [Fecha] >= CONVERT(DATETIME, :fecha_inicio, 102)
                    AND [Fecha] <= CONVERT(DATETIME, :fecha_fin, 102)
                  ORDER BY [Fecha]";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getPrecioAcumuladoMensualSolar($anio, $mes): array
    {
        $query = "SELECT SUM(pre_medio_dia_solar) AS precio_acumulado_mensual,
                         COUNT(pre_medio_dia_solar) AS dias_registrados,
                         CASE
                             WHEN COUNT(pre_medio_dia_solar) > 0 THEN SUM(pre_medio_dia_solar) / COUNT(pre_medio_dia_solar)
                             ELSE 0
                         END AS precio_medio_mensual
                  FROM [bd_lecturas].[dbo].[LIST_OMIE_PM_HR_SOLARES]
                  WHERE YEAR(Fecha) = :anio AND MONTH(Fecha) = :mes";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'anio' => $anio,
            'mes' => $mes,
        ]);

        $resultado = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$resultado || $resultado['precio_acumulado_mensual'] === null) {
            return [
                'precio_acumulado_mensual' => '0 EUR/MWh',
                'precio_medio_mensual' => '0 EUR/MWh',
            ];
        }

        return [
            'precio_acumulado_mensual' => number_format((float) $resultado['precio_acumulado_mensual'] * 1000, 2, ',', '.') . ' EUR/MWh',
            'precio_medio_mensual' => number_format((float) $resultado['precio_medio_mensual'] * 1000, 2, ',', '.') . ' EUR/MWh',
        ];
    }

    public function getPrecioMedioSolarMensualRaw(int $anio, int $mes): ?float
    {
        $query = "SELECT AVG(pre_medio_dia_solar) AS precio_medio_mensual
                  FROM [bd_lecturas].[dbo].[LIST_OMIE_PM_HR_SOLARES]
                  WHERE YEAR(Fecha) = :anio
                    AND MONTH(Fecha) = :mes";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'anio' => $anio,
            'mes' => $mes,
        ]);

        $resultado = $stmt->fetch(PDO::FETCH_ASSOC);

        return isset($resultado['precio_medio_mensual']) && $resultado['precio_medio_mensual'] !== null
            ? (float) $resultado['precio_medio_mensual']
            : null;
    }

    public function getPrecioAcumuladoAnualSolar($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT YEAR(Fecha) AS anio,
                         SUM(pre_medio_dia_solar) AS precio_acumulado_anual,
                         COUNT(pre_medio_dia_solar) AS dias_registrados,
                         (SUM(pre_medio_dia_solar) / COUNT(pre_medio_dia_solar)) AS precio_medio_acumulado_anual
                  FROM [bd_lecturas].[dbo].[LIST_OMIE_PM_HR_SOLARES]
                  WHERE Fecha BETWEEN :fecha_inicio AND :fecha_fin
                  GROUP BY YEAR(Fecha)";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);

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

    public function getPrecioMedioSolarAnualRaw(int $anio): ?float
    {
        $query = "SELECT AVG(pre_medio_dia_solar) AS precio_medio_anual
                  FROM [bd_lecturas].[dbo].[LIST_OMIE_PM_HR_SOLARES]
                  WHERE YEAR(Fecha) = :anio";

        $stmt = $this->conn->prepare($query);
        $stmt->execute(['anio' => $anio]);

        $resultado = $stmt->fetch(PDO::FETCH_ASSOC);

        return isset($resultado['precio_medio_anual']) && $resultado['precio_medio_anual'] !== null
            ? (float) $resultado['precio_medio_anual']
            : null;
    }

    public function getDetallePorHoraSolar($fecha): array
    {
        try {
            $query = "SELECT O.Fecha,
                             O.Hora AS Hora,
                             O.Precio AS Precio
                      FROM [bd_lecturas].[dbo].[LIST_OMIE_PRE_HORAS] O
                      INNER JOIN [bd_lecturas].[dbo].[LIST_FOTOVOL_ESTIMACION] F
                        ON O.Mes = F.FOT_MES
                       AND O.[Día] = F.FOT_DIA
                       AND O.Hora = F.FOT_HOR
                      WHERE F.FOT_HOR_SUM = 1
                        AND O.Fecha = CONVERT(DATETIME, :fecha, 102)
                        AND F.FOT_1KW > 0";

            $stmt = $this->conn->prepare($query);
            $stmt->execute(['fecha' => $fecha]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log('Error en getDetallePorHoraSolar: ' . $e->getMessage());
            return [];
        }
    }

    public function getPrecioMedioPorTarifa($fecha_inicio, $fecha_fin): array
    {
        $fecha_inicio = date('Y-m-d', strtotime($fecha_inicio));
        $fecha_fin = date('Y-m-d', strtotime($fecha_fin . ' +1 day'));

        $query = "SELECT TBP_TAR,
                         AVG(TBP_COS) AS precio_medio,
                         MIN(TBP_COS) AS precio_minimo,
                         MAX(TBP_COS) AS precio_maximo
                  FROM [bd_lecturas].[dbo].[BICUBO_COSTE_HORA]
                  WHERE TBP_FEC >= CONVERT(DATETIME, :fecha_inicio, 102)
                    AND TBP_FEC < CONVERT(DATETIME, :fecha_fin, 102)
                  GROUP BY TBP_TAR";

        try {
            $stmt = $this->conn->prepare($query);
            $stmt->execute([
                'fecha_inicio' => $fecha_inicio,
                'fecha_fin' => $fecha_fin,
            ]);

            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            return [];
        }
    }

    public function getUltimaFechaPrecioTarifa(): ?string
    {
        $query = "SELECT CONVERT(date, MAX(TBP_FEC)) AS ultima_fecha
                  FROM [bd_lecturas].[dbo].[BICUBO_COSTE_HORA]";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $resultado = $stmt->fetch(PDO::FETCH_ASSOC);

        return $resultado['ultima_fecha'] ?? null;
    }

    public function getHabitosConsumo($fecha_inicio, $fecha_fin): array
    {
        $query = "SELECT tariff,
                         SUM(H01 + H02 + H03 + H04 + H05 + H06 +
                             H07 + H08 + H09 + H10 + H11 + H12 +
                             H13 + H14 + H15 + H16 + H17 + H18 +
                             H19 + H20 + H21 + H22 + H23 + H24) AS consumo_total
                  FROM [bd_apis].[dbo].[metering]
                  WHERE date BETWEEN :fecha_inicio AND :fecha_fin
                  GROUP BY tariff";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getUltimaFechaConsumoTarifa(): ?string
    {
        $query = "SELECT MAX([date]) AS ultima_fecha
                  FROM [bd_apis].[dbo].[metering]";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $resultado = $stmt->fetch(PDO::FETCH_ASSOC);

        return $resultado['ultima_fecha'] ?? null;
    }

    public function getPreciosPorHorasHoy(): array|false
    {
        $query = "SELECT TOP 1 OMI_FEC, OMI_H01, OMI_H02, OMI_H03, OMI_H04, OMI_H05,
                             OMI_H06, OMI_H07, OMI_H08, OMI_H09, OMI_H10, OMI_H11,
                             OMI_H12, OMI_H13, OMI_H14, OMI_H15, OMI_H16, OMI_H17,
                             OMI_H18, OMI_H19, OMI_H20, OMI_H21, OMI_H22, OMI_H23, OMI_H24
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE OMI_FEC = CONVERT(DATE, GETDATE())
                  UNION ALL
                  SELECT TOP 1 OMI_FEC, OMI_H01, OMI_H02, OMI_H03, OMI_H04, OMI_H05,
                               OMI_H06, OMI_H07, OMI_H08, OMI_H09, OMI_H10, OMI_H11,
                               OMI_H12, OMI_H13, OMI_H14, OMI_H15, OMI_H16, OMI_H17,
                               OMI_H18, OMI_H19, OMI_H20, OMI_H21, OMI_H22, OMI_H23, OMI_H24
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE OMI_FEC < CONVERT(DATE, GETDATE())
                  ORDER BY OMI_FEC DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getUltimoRegistro(): array|false
    {
        $query = "SELECT TOP 1 *
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  ORDER BY OMI_FEC DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getFechasDisponibles(string $marketType = null): array
    {
        $query = "SELECT DISTINCT OMI_FEC
                  FROM (" . $this->daily24SourceSql('_fechas') . ") source
                  ORDER BY OMI_FEC DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'market_type_fechas' => $this->marketType($marketType),
            'market_type_fallback_fechas' => $this->marketType($marketType),
            'market_type_exists_fechas' => $this->marketType($marketType),
        ]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    }

    public function getPreciosPorFecha($fecha): array|false
    {
        $query = "SELECT *
                  FROM [bd_lecturas].[dbo].[OMIE_PRE]
                  WHERE OMI_FEC = :fecha";

        $stmt = $this->conn->prepare($query);
        $stmt->execute(['fecha' => $fecha]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getPreciosVista24PorFecha(string $fecha, string $marketType = null): array|false
    {
        $query = "SELECT TOP 1 *
                  FROM (" . $this->daily24SourceSql('_dia') . ") source
                  WHERE OMI_FEC = :fecha
                  ORDER BY OMI_REV DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha' => $fecha,
            'market_type_dia' => $this->marketType($marketType),
            'market_type_fallback_dia' => $this->marketType($marketType),
            'market_type_exists_dia' => $this->marketType($marketType),
        ]);

        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getPreciosVista96PorFecha(string $fecha, string $marketType = null): array|false
    {
        $query = "SELECT TOP 1 *
                  FROM [bd_lecturas].[dbo].[vw_OMIE_PRE_96]
                  WHERE OMI_FEC = :fecha
                    AND OMI_TIP = :market_type
                  ORDER BY OMI_REV DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'fecha' => $fecha,
            'market_type' => $this->marketType($marketType),
        ]);

        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getSeriesPorFecha(string $fecha, string $resolution = '24', string $marketType = null): array
    {
        $row = $resolution === '96'
            ? $this->getPreciosVista96PorFecha($fecha, $marketType)
            : $this->getPreciosVista24PorFecha($fecha, $marketType);

        if (!$row) {
            return [];
        }

        $series = $resolution === '96'
            ? $this->buildSeriesFromRow($row, 'OMI_Q', 96)
            : $this->buildSeriesFromRow($row, 'OMI_H', 24);

        $numericValues = array_values(array_filter($series['values'], static fn($value) => $value !== null));

        return [
            'fecha' => $row['OMI_FEC'],
            'marketType' => $row['OMI_TIP'],
            'resolution' => $resolution,
            'labels' => $series['labels'],
            'prices' => $series['values'],
            'missingLabels' => $series['missing'],
            'stats' => [
                'average' => count($numericValues) > 0 ? array_sum($numericValues) / count($numericValues) : null,
                'min' => count($numericValues) > 0 ? min($numericValues) : null,
                'max' => count($numericValues) > 0 ? max($numericValues) : null,
                'loadedPoints' => count($numericValues),
                'expectedPoints' => $resolution === '96' ? 96 : 24,
                'missingPoints' => count($series['missing']),
            ],
        ];
    }

    public function getPreciosPorMes($mes, $anio, string $marketType = null): array
    {
        $marketType = $this->marketType($marketType);
        $diasDelMes = cal_days_in_month(CAL_GREGORIAN, (int) $mes, (int) $anio);
        $preciosDiarios = array_fill(1, $diasDelMes, null);
        $diasSinDatos = [];

        $query = "SELECT OMI_FEC,
                         {$this->averageExpression('OMI_H', 24)} AS precio_medio,
                         {$this->missingCountExpression('OMI_H', 24)} AS puntos_faltantes
                  FROM (" . $this->daily24SourceSql('_mes') . ") source
                  WHERE MONTH(OMI_FEC) = :mes
                    AND YEAR(OMI_FEC) = :anio
                  ORDER BY OMI_FEC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'mes' => $mes,
            'anio' => $anio,
            'market_type_mes' => $marketType,
            'market_type_fallback_mes' => $marketType,
            'market_type_exists_mes' => $marketType,
        ]);

        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as $row) {
            $dia = (int) date('j', strtotime($row['OMI_FEC']));
            $preciosDiarios[$dia] = $row['precio_medio'] !== null ? (float) $row['precio_medio'] : null;

            if ((int) $row['puntos_faltantes'] > 0) {
                $diasSinDatos[] = [
                    'fecha' => $row['OMI_FEC'],
                    'missingPoints' => (int) $row['puntos_faltantes'],
                ];
            }
        }

        $diasConDatos = count(array_filter($preciosDiarios, static fn($value) => $value !== null));
        $diasFaltantes = [];

        for ($dia = 1; $dia <= $diasDelMes; $dia++) {
            if ($preciosDiarios[$dia] === null) {
                $diasFaltantes[] = sprintf('%04d-%02d-%02d', $anio, $mes, $dia);
            }
        }

        return [
            'dias' => range(1, $diasDelMes),
            'preciosMedios' => array_values($preciosDiarios),
            'esParcial' => $diasConDatos < $diasDelMes,
            'diasFaltantes' => $diasFaltantes,
            'diasConHuecos' => $diasSinDatos,
        ];
    }

    public function getPreciosPorAnio($anio, string $marketType = null): array
    {
        if (empty($anio) || !is_numeric($anio)) {
            throw new Exception('El ano es invalido o esta vacio');
        }

        $marketType = $this->marketType($marketType);
        $preciosMensuales = array_fill(0, 12, null);

        $query = "SELECT MONTH(OMI_FEC) AS mes,
                         AVG({$this->averageExpression('OMI_H', 24)}) AS precio_medio
                  FROM (" . $this->daily24SourceSql('_anio') . ") source
                  WHERE YEAR(OMI_FEC) = :anio
                  GROUP BY MONTH(OMI_FEC)
                  ORDER BY MONTH(OMI_FEC)";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'anio' => $anio,
            'market_type_anio' => $marketType,
            'market_type_fallback_anio' => $marketType,
            'market_type_exists_anio' => $marketType,
        ]);

        $precios = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($precios as $precio) {
            $mes = (int) $precio['mes'];
            if ($mes >= 1 && $mes <= 12) {
                $preciosMensuales[$mes - 1] = $precio['precio_medio'] !== null ? round((float) $precio['precio_medio'], 5) : null;
            }
        }

        $queryAnual = "SELECT AVG({$this->averageExpression('OMI_H', 24)}) AS precio_medio_anual
                       FROM (" . $this->daily24SourceSql('_anio_total') . ") source
                       WHERE YEAR(OMI_FEC) = :anio";

        $stmtAnual = $this->conn->prepare($queryAnual);
        $stmtAnual->execute([
            'anio' => $anio,
            'market_type_anio_total' => $marketType,
            'market_type_fallback_anio_total' => $marketType,
            'market_type_exists_anio_total' => $marketType,
        ]);

        $mediaAnual = $stmtAnual->fetch(PDO::FETCH_ASSOC)['precio_medio_anual'] ?? null;
        $mesesConDatos = [];

        foreach ($preciosMensuales as $index => $value) {
            if ($value !== null) {
                $mesesConDatos[] = $index + 1;
            }
        }

        return [
            'preciosMensuales' => $preciosMensuales,
            'mediaAnual' => $mediaAnual !== null ? round((float) $mediaAnual, 5) : null,
            'esParcial' => count($mesesConDatos) < 12,
            'mesesConDatos' => $mesesConDatos,
            'mesesFaltantes' => array_values(array_diff(range(1, 12), $mesesConDatos)),
        ];
    }

    public function getDashboardSummary(string $fecha, string $marketType = null): array
    {
        $marketType = $this->marketType($marketType);
        $fechaDate = new DateTime($fecha);
        $inicioMes = $fechaDate->format('Y-m-01');
        $finMes = $fechaDate->format('Y-m-t');
        $inicioAnio = $fechaDate->format('Y-01-01');
        $finAnio = $fechaDate->format('Y-12-31');

        $series24 = $this->getSeriesPorFecha($fecha, '24', $marketType);
        $series96 = $this->getSeriesPorFecha($fecha, '96', $marketType);

        $query = "SELECT
                    (SELECT AVG({$this->averageExpression('OMI_H', 24)})
                     FROM (" . $this->daily24SourceSql('_media_mes') . ") source
                     WHERE OMI_FEC BETWEEN :inicio_mes_media AND :fin_mes_media) AS media_mes,
                    (SELECT COUNT(*)
                     FROM (" . $this->daily24SourceSql('_count_mes') . ") source
                     WHERE OMI_FEC BETWEEN :inicio_mes_count AND :fin_mes_count) AS dias_cargados_mes,
                    (SELECT AVG({$this->averageExpression('OMI_H', 24)})
                     FROM (" . $this->daily24SourceSql('_media_anio') . ") source
                     WHERE OMI_FEC BETWEEN :inicio_anio_media AND :fin_anio_media) AS media_anio,
                    (SELECT COUNT(*)
                     FROM (" . $this->daily24SourceSql('_count_anio') . ") source
                     WHERE OMI_FEC BETWEEN :inicio_anio_count AND :fin_anio_count) AS dias_cargados_anio";

        $stmt = $this->conn->prepare($query);
        $stmt->execute([
            'inicio_mes_media' => $inicioMes,
            'fin_mes_media' => $finMes,
            'market_type_media_mes' => $marketType,
            'market_type_fallback_media_mes' => $marketType,
            'market_type_exists_media_mes' => $marketType,
            'inicio_mes_count' => $inicioMes,
            'fin_mes_count' => $finMes,
            'market_type_count_mes' => $marketType,
            'market_type_fallback_count_mes' => $marketType,
            'market_type_exists_count_mes' => $marketType,
            'inicio_anio_media' => $inicioAnio,
            'fin_anio_media' => $finAnio,
            'market_type_media_anio' => $marketType,
            'market_type_fallback_media_anio' => $marketType,
            'market_type_exists_media_anio' => $marketType,
            'inicio_anio_count' => $inicioAnio,
            'fin_anio_count' => $finAnio,
            'market_type_count_anio' => $marketType,
            'market_type_fallback_count_anio' => $marketType,
            'market_type_exists_count_anio' => $marketType,
        ]);

        $summary = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        $diasMesEsperados = (int) $fechaDate->format('t');
        $diasAnioEsperados = (int) $fechaDate->format('L') === 1 ? 366 : 365;

        return [
            'fecha' => $fecha,
            'marketType' => $marketType,
            'dia' => $series24['stats'] ?? null,
            'cuartoHora' => $series96['stats'] ?? null,
            'mes' => [
                'average' => isset($summary['media_mes']) ? (float) $summary['media_mes'] : null,
                'loadedDays' => isset($summary['dias_cargados_mes']) ? (int) $summary['dias_cargados_mes'] : 0,
                'expectedDays' => $diasMesEsperados,
            ],
            'anio' => [
                'average' => isset($summary['media_anio']) ? (float) $summary['media_anio'] : null,
                'loadedDays' => isset($summary['dias_cargados_anio']) ? (int) $summary['dias_cargados_anio'] : 0,
                'expectedDays' => $diasAnioEsperados,
            ],
        ];
    }

    public function getIntegrityAlerts(string $fecha_inicio, string $fecha_fin, string $marketType = null): array
    {
        $marketType = $this->marketType($marketType);

        $query24 = "SELECT OMI_FEC,
                           {$this->missingCountExpression('OMI_H', 24)} AS missing_points
                    FROM [bd_lecturas].[dbo].[vw_OMIE_PRE_24]
                    WHERE OMI_FEC BETWEEN :fecha_inicio AND :fecha_fin
                      AND OMI_TIP = :market_type
                      AND ({$this->missingCountExpression('OMI_H', 24)}) > 0
                    ORDER BY OMI_FEC DESC";

        $stmt24 = $this->conn->prepare($query24);
        $stmt24->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
            'market_type' => $marketType,
        ]);
        $missing24 = $stmt24->fetchAll(PDO::FETCH_ASSOC);

        $query96 = "SELECT OMI_FEC,
                           {$this->missingCountExpression('OMI_Q', 96)} AS missing_points
                    FROM [bd_lecturas].[dbo].[vw_OMIE_PRE_96]
                    WHERE OMI_FEC BETWEEN :fecha_inicio AND :fecha_fin
                      AND OMI_TIP = :market_type
                      AND ({$this->missingCountExpression('OMI_Q', 96)}) > 0
                    ORDER BY OMI_FEC DESC";

        $stmt96 = $this->conn->prepare($query96);
        $stmt96->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
            'market_type' => $marketType,
        ]);
        $missing96 = $stmt96->fetchAll(PDO::FETCH_ASSOC);

        $queryDuplicates = "SELECT OMI_FEC,
                                   COUNT(*) AS total_rows
                            FROM [bd_lecturas].[dbo].[vw_OMIE_PRE_96]
                            WHERE OMI_FEC BETWEEN :fecha_inicio AND :fecha_fin
                            GROUP BY OMI_FEC
                            HAVING COUNT(*) > 1
                            ORDER BY OMI_FEC DESC";

        $stmtDuplicates = $this->conn->prepare($queryDuplicates);
        $stmtDuplicates->execute([
            'fecha_inicio' => $fecha_inicio,
            'fecha_fin' => $fecha_fin,
        ]);
        $duplicates = $stmtDuplicates->fetchAll(PDO::FETCH_ASSOC);

        return [
            'range' => [
                'start' => $fecha_inicio,
                'end' => $fecha_fin,
            ],
            'missing24' => array_map(static function ($row) {
                return [
                    'fecha' => $row['OMI_FEC'],
                    'missingPoints' => (int) $row['missing_points'],
                ];
            }, $missing24),
            'missing96' => array_map(static function ($row) {
                return [
                    'fecha' => $row['OMI_FEC'],
                    'missingPoints' => (int) $row['missing_points'],
                ];
            }, $missing96),
            'duplicates' => array_map(static function ($row) {
                return [
                    'fecha' => $row['OMI_FEC'],
                    'totalRows' => (int) $row['total_rows'],
                ];
            }, $duplicates),
        ];
    }
}
?>
