<?php

class ADBRimoFcp extends TTCrudBaseModel
{
    public int $id;
    public int $netzgebiet_id;
    public ?string $name;
    public string $rimo_id;
    public ?string $label;
    public ?string $building_type;
    public ?string $rimo_ex_state;
    public ?string $rimo_op_state;
    public ?float $gps_lat;
    public ?float $gps_long;
    public int $create;
    public int $edit;

    public static function getRimoFcpStatistics(array $fcpIds = []): array {
        $fronkDbName = FRONKDB_DBNAME ?? 'thetool';
        $addressDbName = ADDRESSDB_DBNAME ?? 'addressdb';

        $fcps = self::getAll($fcpIds ? ['id' => $fcpIds] : [], null, 0, ['key' => 'name', 'order' => 'ASC']);
        if (!$fcps) return [];

        $fcpResultMap = [];
        foreach ($fcps as $fcp) {
            $fcpResultMap[$fcp->id] = [
                'fcp_id' => $fcp->id,
                'fcp_name' => $fcp->name,
                'fcp_rimo_id' => $fcp->rimo_id,
                'total_hausnummer_count' => 0,
                'total_wohneinheit_count' => 0,
                'total_active_preorders' => 0,
                'counts_by_rimo_type' => new stdClass(),
            ];
        }

        $idList = implode(',', array_keys($fcpResultMap));
        if (empty($idList)) return array_values($fcpResultMap);

        $sql = "
        SELECT
            hn.fcp_id,
            COALESCE(hn.rimo_type, 'UNKNOWN') AS rimo_type,
            COUNT(DISTINCT hn.id) AS hausnummer_count,
            COUNT(DISTINCT we.id) AS wohneinheit_count,
            COUNT(DISTINCT CASE WHEN ps.code < 899 THEN p.id END) AS preorder_count
        FROM `{$addressDbName}`.`Hausnummer` AS hn
        LEFT JOIN `{$addressDbName}`.`Wohneinheit` AS we ON hn.id = we.hausnummer_id
        LEFT JOIN `{$fronkDbName}`.`Preorder` AS p ON p.adb_hausnummer_id = hn.id
        LEFT JOIN `{$fronkDbName}`.`Preorderstatus` AS ps ON p.status_id = ps.id
        WHERE hn.fcp_id IN ({$idList})
        GROUP BY hn.fcp_id, rimo_type
    ";

        $result = self::getDB()->query($sql);
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $fcpId = $row['fcp_id'];
                $rimoType = $row['rimo_type'];

                $hCount = (int)$row['hausnummer_count'];
                $wCount = (int)$row['wohneinheit_count'];
                $pCount = (int)$row['preorder_count'];

                $fcpResultMap[$fcpId]['counts_by_rimo_type']->{$rimoType} = (object)[
                    'hausnummer_count' => $hCount,
                    'wohneinheit_count' => $wCount,
                    'preorder_count' => $pCount,
                ];

                $fcpResultMap[$fcpId]['total_hausnummer_count'] += $hCount;
                $fcpResultMap[$fcpId]['total_wohneinheit_count'] += $wCount;
                $fcpResultMap[$fcpId]['total_active_preorders'] += $pCount;
            }
        }

        return array_values($fcpResultMap);
    }
}