presence count

* task: add trust proxy header

* add logging

libs/common/src/sql/procedures/fact_space_occupancy_count/procedure_insert_all_fact_space_occupancy_count.sql

@@ -1,3 +1,4 @@
+-- 1. Load all presence-related logs
 WITH device_logs AS (
     SELECT 
         device.uuid AS device_id,
@@ -15,7 +16,7 @@ WITH device_logs AS (
       AND "device-status-log".code = 'presence_state'
 ),
 
--- 1. All 'none' → presence or motion
+-- 2. Standard transitions: 'none' → 'motion' or 'presence'
 presence_transitions AS (
     SELECT 
         space_id,
@@ -23,10 +24,31 @@ presence_transitions AS (
         event_time::date AS event_date,
         value
     FROM device_logs
-    WHERE (value = 'motion' OR value = 'presence') AND prev_value = 'none'
+    WHERE value IN ('motion', 'presence') AND prev_value = 'none'
 ),
 
--- 2. Cluster events per space_id within 30s
+-- 3. Fallback: days with 'motion' or 'presence' but no 'none'
+fallback_daily_presence AS (
+    SELECT 
+        space_id,
+        event_time::date AS event_date,
+        MIN(event_time) AS event_time,
+        'presence'::text AS value
+    FROM device_logs
+    WHERE value IN ('motion', 'presence', 'none')
+    GROUP BY space_id, event_time::date
+    HAVING BOOL_OR(value = 'motion') OR BOOL_OR(value = 'presence')
+       AND NOT BOOL_OR(value = 'none')
+),
+
+-- 4. Merge both detection sources
+all_presence_events AS (
+    SELECT * FROM presence_transitions
+    UNION ALL
+    SELECT space_id, event_time, event_date, value FROM fallback_daily_presence
+),
+
+-- 5. Cluster events per space_id within 30 seconds
 clustered_events AS (
     SELECT 
         space_id,
@@ -40,11 +62,11 @@ clustered_events AS (
                 WHEN event_time - LAG(event_time) OVER (PARTITION BY space_id ORDER BY event_time) > INTERVAL '30 seconds'
                 THEN 1 ELSE 0
             END AS new_cluster_flag
-        FROM presence_transitions
+        FROM all_presence_events
     ) marked
 ),
 
--- 3. Determine dominant type (motion vs presence) per cluster
+-- 6. Determine dominant type (motion vs presence) per cluster
 cluster_type AS (
     SELECT 
         space_id,
@@ -60,7 +82,7 @@ cluster_type AS (
     GROUP BY space_id, event_date, cluster_id
 ),
 
--- 4. Count clusters by dominant type
+-- 7. Count clusters by dominant type
 summary AS (
     SELECT 
         space_id,
@@ -70,15 +92,16 @@ summary AS (
         COUNT(*) AS count_total_presence_detected
     FROM cluster_type
     GROUP BY space_id, event_date
+),
+
+-- 8. Prepare final result
+final_table AS (
+    SELECT *
+    FROM summary
+    ORDER BY space_id, event_date
 )
 
--- 5. Output
-, final_table as (
-SELECT *
-FROM summary
-ORDER BY space_id, event_date)
-
-
+-- 9. Insert or upsert into the destination table
 INSERT INTO public."presence-sensor-daily-space-detection" (
 	space_uuid,
 	event_date,
@@ -97,4 +120,4 @@ ON CONFLICT (space_uuid, event_date) DO UPDATE
 SET
     count_motion_detected = EXCLUDED.count_motion_detected,
     count_presence_detected = EXCLUDED.count_presence_detected,
-    count_total_presence_detected = EXCLUDED.count_total_presence_detected;
+    count_total_presence_detected = EXCLUDED.count_total_presence_detected;

libs/common/src/sql/procedures/fact_space_occupancy_count/procedure_update_fact_space_occupancy.sql

@@ -4,6 +4,7 @@ WITH params AS (
       $2::uuid AS space_id
 ),
 
+-- 1. Load logs
 device_logs AS (
     SELECT 
         device.uuid AS device_id,
@@ -21,7 +22,7 @@ device_logs AS (
       AND "device-status-log".code = 'presence_state'
 ),
 
--- 1. All 'none' → presence or motion
+-- 2. Transitions from 'none' → motion/presence
 presence_transitions AS (
     SELECT 
         space_id,
@@ -29,10 +30,30 @@ presence_transitions AS (
         event_time::date AS event_date,
         value
     FROM device_logs
-    WHERE (value = 'motion' OR value = 'presence') AND prev_value = 'none'
+    WHERE value IN ('motion', 'presence') AND prev_value = 'none'
 ),
 
--- 2. Cluster events per space_id within 30s
+-- 3. Fallback: days with motion/presence but no 'none'
+fallback_daily_presence AS (
+    SELECT 
+        space_id,
+        event_time::date AS event_date,
+        MIN(event_time) AS event_time,
+        'presence'::text AS value
+    FROM device_logs
+    GROUP BY space_id, event_time::date
+    HAVING BOOL_OR(value = 'motion') OR BOOL_OR(value = 'presence')
+       AND NOT BOOL_OR(value = 'none')
+),
+
+-- 4. Combine standard and fallback detections
+all_presence_events AS (
+    SELECT * FROM presence_transitions
+    UNION ALL
+    SELECT * FROM fallback_daily_presence
+),
+
+-- 5. Cluster detections (within 30s)
 clustered_events AS (
     SELECT 
         space_id,
@@ -46,11 +67,11 @@ clustered_events AS (
                 WHEN event_time - LAG(event_time) OVER (PARTITION BY space_id ORDER BY event_time) > INTERVAL '30 seconds'
                 THEN 1 ELSE 0
             END AS new_cluster_flag
-        FROM presence_transitions
+        FROM all_presence_events
     ) marked
 ),
 
--- 3. Determine dominant type (motion vs presence) per cluster
+-- 6. Dominant type per cluster
 cluster_type AS (
     SELECT 
         space_id,
@@ -66,7 +87,7 @@ cluster_type AS (
     GROUP BY space_id, event_date, cluster_id
 ),
 
--- 4. Count clusters by dominant type
+-- 7. Count presence by type
 summary AS (
     SELECT 
         space_id,
@@ -76,22 +97,22 @@ summary AS (
         COUNT(*) AS count_total_presence_detected
     FROM cluster_type
     GROUP BY space_id, event_date
+),
+
+-- 8. Filter by params and return final table
+final_table AS (
+    SELECT 
+        summary.space_id,
+        summary.event_date,
+        count_motion_detected,
+        count_presence_detected,
+        count_total_presence_detected
+    FROM summary
+    JOIN params p ON summary.space_id = p.space_id
+    WHERE p.event_date IS NULL OR summary.event_date = p.event_date
 )
 
--- 5. Output
-, final_table as (
-SELECT summary.space_id,
-       summary.event_date,
-       count_motion_detected,
-       count_presence_detected,
-       count_total_presence_detected
-FROM summary
-JOIN params P ON true
-where summary.space_id = P.space_id
-and (P.event_date IS NULL or summary.event_date::date = P.event_date)
-ORDER BY space_id, event_date)
-
-
+-- 9. Insert or upsert into the table
 INSERT INTO public."presence-sensor-daily-space-detection" (
 	space_uuid,
 	event_date,

libs/common/src/sql/queries/fact_hourly_space_presence_detected/fact_hourly_space_presence_detected.sql

@@ -30,6 +30,19 @@ presence_detection AS (
     FROM device_logs
 ),
 
+fallback_daily_presence AS (
+    SELECT 
+        space_id,
+        event_time::date AS event_date,
+        0 AS event_hour,
+        COUNT(*) > 0 AS has_presence,
+        BOOL_OR(value = 'none') AS has_none
+    FROM device_logs
+    WHERE value IN ('motion', 'presence', 'none')
+    GROUP BY space_id, event_time::date
+    HAVING COUNT(*) > 0 AND NOT BOOL_OR(value = 'none')
+),
+
 space_level_presence_events AS (
     SELECT DISTINCT 
         pd.space_id,
@@ -38,6 +51,15 @@ space_level_presence_events AS (
         pd.event_time
     FROM presence_detection pd
     WHERE presence_started = 1
+
+    UNION
+
+    SELECT 
+        fdp.space_id,
+        fdp.event_date,
+        fdp.event_hour,
+        NULL::timestamp AS event_time
+    FROM fallback_daily_presence fdp
 ),
 
 space_level_presence_summary AS (
@@ -77,3 +99,4 @@ LEFT JOIN space_level_presence_summary pds
 ORDER BY space_id, event_date, event_hour;
 
 
+

libs/common/src/util/email.service.ts

@@ -1,7 +1,7 @@
 import { HttpException, HttpStatus, Injectable } from '@nestjs/common';
 import { ConfigService } from '@nestjs/config';
-import * as nodemailer from 'nodemailer';
 import axios from 'axios';
+import * as nodemailer from 'nodemailer';
 import {
   SEND_EMAIL_API_URL_DEV,
   SEND_EMAIL_API_URL_PROD,
@@ -83,12 +83,17 @@ export class EmailService {
       );
     }
   }
-  async sendEmailWithTemplate(
-    email: string,
-    name: string,
-    isEnable: boolean,
-    isDelete: boolean,
-  ): Promise<void> {
+  async sendEmailWithTemplate({
+    email,
+    name,
+    isEnable,
+    isDelete,
+  }: {
+    email: string;
+    name: string;
+    isEnable: boolean;
+    isDelete: boolean;
+  }): Promise<void> {
     const isProduction = process.env.NODE_ENV === 'production';
     const API_TOKEN = this.configService.get<string>(
       'email-config.MAILTRAP_API_TOKEN',

src/invite-user/services/invite-user.service.ts

@@ -1,36 +1,42 @@
-import {
-  Injectable,
-  HttpException,
-  HttpStatus,
-  BadRequestException,
-} from '@nestjs/common';
-import { AddUserInvitationDto } from '../dtos';
-import { BaseResponseDto } from '@app/common/dto/base.response.dto';
+import { RoleType } from '@app/common/constants/role.type.enum';
 import { UserStatusEnum } from '@app/common/constants/user-status.enum';
+import { BaseResponseDto } from '@app/common/dto/base.response.dto';
 import { SuccessResponseDto } from '@app/common/dto/success.response.dto';
 import { generateRandomString } from '@app/common/helper/randomString';
-import { EntityManager, In, IsNull, Not, QueryRunner } from 'typeorm';
-import { DataSource } from 'typeorm';
-import { UserEntity } from '@app/common/modules/user/entities';
-import { RoleType } from '@app/common/constants/role.type.enum';
+import { InviteUserEntity } from '@app/common/modules/Invite-user/entities';
 import {
   InviteUserRepository,
   InviteUserSpaceRepository,
 } from '@app/common/modules/Invite-user/repositiories';
-import { CheckEmailDto } from '../dtos/check-email.dto';
+import { RoleTypeRepository } from '@app/common/modules/role-type/repositories';
+import { SpaceRepository } from '@app/common/modules/space';
+import { SpaceEntity } from '@app/common/modules/space/entities/space.entity';
+import { UserEntity } from '@app/common/modules/user/entities';
 import { UserRepository } from '@app/common/modules/user/repositories';
 import { EmailService } from '@app/common/util/email.service';
-import { SpaceRepository } from '@app/common/modules/space';
-import { ActivateCodeDto } from '../dtos/active-code.dto';
-import { UserSpaceService } from 'src/users/services';
+import {
+  BadRequestException,
+  HttpException,
+  HttpStatus,
+  Injectable,
+} from '@nestjs/common';
 import { SpaceUserService } from 'src/space/services';
+import { UserSpaceService } from 'src/users/services';
+import {
+  DataSource,
+  EntityManager,
+  In,
+  IsNull,
+  Not,
+  QueryRunner,
+} from 'typeorm';
+import { AddUserInvitationDto } from '../dtos';
+import { ActivateCodeDto } from '../dtos/active-code.dto';
+import { CheckEmailDto } from '../dtos/check-email.dto';
 import {
   DisableUserInvitationDto,
   UpdateUserInvitationDto,
 } from '../dtos/update.invite-user.dto';
-import { RoleTypeRepository } from '@app/common/modules/role-type/repositories';
-import { InviteUserEntity } from '@app/common/modules/Invite-user/entities';
-import { SpaceEntity } from '@app/common/modules/space/entities/space.entity';
 
 @Injectable()
 export class InviteUserService {
@@ -658,12 +664,12 @@ export class InviteUserService {
           HttpStatus.BAD_REQUEST,
         );
       }
-      await this.emailService.sendEmailWithTemplate(
-        userData.email,
-        userData.firstName,
-        disable,
-        false,
-      );
+      await this.emailService.sendEmailWithTemplate({
+        email: userData.email,
+        name: userData.firstName,
+        isEnable: !disable,
+        isDelete: false,
+      });
       await queryRunner.commitTransaction();
 
       return new SuccessResponseDto({
@@ -797,12 +803,12 @@ export class InviteUserService {
           { isActive: false },
         );
       }
-      await this.emailService.sendEmailWithTemplate(
-        userData.email,
-        userData.firstName,
-        false,
-        true,
-      );
+      await this.emailService.sendEmailWithTemplate({
+        email: userData.email,
+        name: userData.firstName,
+        isEnable: false,
+        isDelete: true,
+      });
       await queryRunner.commitTransaction();
 
       return new SuccessResponseDto({

src/main.ts

@@ -1,15 +1,14 @@
+import { RequestContextMiddleware } from '@app/common/middleware/request-context.middleware';
+import { SeederService } from '@app/common/seed/services/seeder.service';
+import { Logger, ValidationPipe } from '@nestjs/common';
 import { NestFactory } from '@nestjs/core';
-import { AppModule } from './app.module';
+import { json, urlencoded } from 'body-parser';
 import rateLimit from 'express-rate-limit';
 import helmet from 'helmet';
-import { setupSwaggerAuthentication } from '../libs/common/src/util/user-auth.swagger.utils';
-import { ValidationPipe } from '@nestjs/common';
-import { json, urlencoded } from 'body-parser';
-import { SeederService } from '@app/common/seed/services/seeder.service';
-import { HttpExceptionFilter } from './common/filters/http-exception/http-exception.filter';
-import { Logger } from '@nestjs/common';
 import { WINSTON_MODULE_NEST_PROVIDER } from 'nest-winston';
-import { RequestContextMiddleware } from '@app/common/middleware/request-context.middleware';
+import { setupSwaggerAuthentication } from '../libs/common/src/util/user-auth.swagger.utils';
+import { AppModule } from './app.module';
+import { HttpExceptionFilter } from './common/filters/http-exception/http-exception.filter';
 
 async function bootstrap() {
   const app = await NestFactory.create(AppModule);
@@ -27,9 +26,18 @@ async function bootstrap() {
     rateLimit({
       windowMs: 5 * 60 * 1000,
       max: 500,
+      standardHeaders: true,
+      legacyHeaders: false,
     }),
   );
 
+  app.use((req, res, next) => {
+    console.log('Real IP:', req.ip);
+    next();
+  });
+
+  app.getHttpAdapter().getInstance().set('trust proxy', 1);
+
   app.use(
     helmet({
       contentSecurityPolicy: false,