import { Injectable } from '@nestjs/common';
import { EntityManager, EntityRepository } from '@mikro-orm/postgresql';
import { SmsLog } from '../entities/smsLogs.entity';
import { PaginatedResult } from '../../../common/interfaces/pagination.interface';

@Injectable()
export class SmsLogRepository extends EntityRepository<SmsLog> {
  constructor(readonly em: EntityManager) {
    super(em, SmsLog);
  }

  async getSmsCountByRestaurant(
    page: number = 1,
    limit: number = 10,
  ): Promise<PaginatedResult<{ restaurantId: string; restaurantName: string; smsCount: number }>> {
    const offset = (page - 1) * limit;

    // Get total count of unique restaurants with SMS logs
    const totalResult = await this.em.execute(
      `
      SELECT COUNT(DISTINCT sl.restaurant_id) as total
      FROM sms_logs sl
      WHERE sl.restaurant_id IS NOT NULL
      `,
    );
    const total = parseInt(totalResult[0]?.total || '0', 10);

    // Get paginated results with SMS counts grouped by restaurant
    const results = await this.em.execute(
      `
      SELECT 
        r.id as "restaurantId",
        r.name as "restaurantName",
        COUNT(sl.id)::int as "smsCount"
      FROM sms_logs sl
      INNER JOIN restaurants r ON sl.restaurant_id = r.id
      GROUP BY r.id, r.name
      ORDER BY "smsCount" DESC, r.name ASC
      LIMIT ? OFFSET ?
      `,
      [limit, offset],
    );

    const data = results.map((row: any) => ({
      restaurantId: row.restaurantId,
      restaurantName: row.restaurantName || 'Unknown',
      smsCount: parseInt(row.smsCount || '0', 10),
    }));

    const totalPages = Math.ceil(total / limit);

    return {
      data,
      meta: {
        total,
        page,
        limit,
        totalPages,
      },
    };
  }

  async getSmsCountByRestaurantId(restaurantId: string): Promise<number> {
    const result = await this.em.execute(
      `
      SELECT COUNT(sl.id)::int as "smsCount"
      FROM sms_logs sl
      WHERE sl.restaurant_id = ?
      `,
      [restaurantId],
    );

    return parseInt(result[0]?.smsCount || '0', 10);
  }
}

