PostGIS Spatial R-Tree & ODP Fiber Allocation

// PostGIS Spatial Service di NestJS: Cari ODP terdekat & hitung estimasi redaman dBm
@Injectable()
export class CoverageGisService {
  constructor(
    @InjectDataSource() private readonly dataSource: DataSource,
    private readonly redisService: RedisService,
  ) {}

  async checkProspectCoverage(lat: number, lng: number): Promise<CoverageResult> {
    const rawQuery = `
      SELECT
        id, name, code, total_ports, used_ports,
        ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography) AS distance_meters
      FROM odp_nodes
      WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography, 150.0)
        AND (total_ports - used_ports) > 0
      ORDER BY distance_meters ASC
      LIMIT 1;
    `;

    const [nearestOdp] = await this.dataSource.query(rawQuery, [lng, lat]);

    if (!nearestOdp) {
      return { covered: false, reason: "Titik di luar radius 150m ODP aktif" };
    }

    const dist = parseFloat(nearestOdp.distance_meters);
    // Formula Redaman: Base OLT (-15.0 dBm) - (Jarak km * 0.35 dB/km) - (2 Splice * 0.1 dB)
    const lossDbm = Number((-15.0 - (dist / 1000.0) * 0.35 - 0.2).toFixed(2));

    // Lock port ODP di Redis selama 24 jam untuk cegah double-booking
    await this.redisService.set(`lock:odp:${nearestOdp.id}:port`, "reserved", "EX", 86400);

    return {
      covered: true,
      odpCode: nearestOdp.code,
      distanceMeters: Math.round(dist * 10) / 10,
      estimatedLossDbm: lossDbm,
      availablePorts: nearestOdp.total_ports - nearestOdp.used_ports,
    };
  }
}