import {
  Injectable,
  NotFoundException,
  ForbiddenException,
  BadRequestException,
  InternalServerErrorException,
} from '@nestjs/common';
import { Kysely, sql } from 'kysely';
import { DB } from '@/database/database.type';
import { Logger } from 'pino-nestjs';
import { ImageService } from '@/utils/image/image.service';
import { containsDisallowedUserContent, MODERATION_REJECTION_MESSAGE } from '@/utils/content-moderation';
import {
  CreateReportDto,
  UpdateReportDto,
  ReportMyPetDto,
  AddCommentDto,
  AddRewardDto,
  MarkReunitedDto,
} from './pets.dto';
import { mapReportRow, mapUnifiedReportRow, applyPublicReportMask } from './pets.helper';

type Trx = Kysely<DB>;

// Combines internal `pet_reports` with externally-scraped listings into one
// browsable feed. Mirrors the Express `unifiedReportsCte`.
const UNIFIED_REPORTS_CTE = `
  WITH unified_reports AS (
    SELECT
      r.id, r.user_id, r.status, r.pet_type, r.pet_name, r.breed, r.size, r.color, r.markings,
      r.photo_uri, r.photo_uris, r.description, r.latitude, r.longitude, r.location_name,
      r.last_seen_date, r.reward, r.reward_pool, r.contact_name, r.contact_phone, r.show_contact_public,
      r.reunion_message, r.reunion_date, r.is_boosted, r.boosted_at, r.boost_expires_at,
      r.created_at, r.updated_at, r.pet_reunited_id, COALESCE(r.is_reunited, false) AS is_reunited,
      r.pet_id, r.deleted_at, r.is_active,
      'internal'::text AS report_source,
      NULL::text AS details_url, NULL::uuid AS website_id, NULL::text AS scraped_animal_id,
      NULL::text AS contact_email, NULL::timestamptz AS scraped_at
    FROM pet_reports r

    UNION ALL

    SELECT
      spr.id, NULL::uuid AS user_id, spr.status,
      COALESCE(NULLIF(spr.pet_type, ''), NULLIF(spr.detected_type, ''), 'other') AS pet_type,
      COALESCE(NULLIF(spr.pet_name, ''), 'Unknown') AS pet_name,
      COALESCE(NULLIF(spr.breed, ''), spr.detected_breed, '') AS breed,
      spr.size,
      COALESCE(NULLIF(spr.detected_color, ''), spr.color, '') AS color,
      spr.markings,
      COALESCE(NULLIF(spr.photo_uri, ''), spr.photo_uris->>0, '') AS photo_uri,
      spr.photo_uris,
      COALESCE(NULLIF(spr.description, ''), spr.raw_text, '') AS description,
      spr.latitude, spr.longitude,
      COALESCE(NULLIF(spr.found_location, ''), spr.location_name, '') AS location_name,
      spr.last_seen_date, spr.reward, spr.reward_pool, spr.contact_name, spr.contact_phone,
      spr.show_contact_public, spr.reunion_message, spr.reunion_date, spr.is_boosted,
      spr.boosted_at, spr.boost_expires_at, spr.scraped_at AS created_at, spr.updated_at,
      spr.pet_reunited_id, COALESCE(spr.is_reunited, false) AS is_reunited,
      NULL::uuid AS pet_id, spr.deleted_at, spr.is_active,
      'scraped'::text AS report_source,
      spr.details_url, spr.website_id, spr.scraped_animal_id, spr.contact_email, spr.scraped_at
    FROM scrapped_pet_report spr
    WHERE spr.status IN ('lost', 'found')
  )
`;

function normalizeQueryList(value: unknown): string[] {
  if (!value) return [];
  const rawValues = Array.isArray(value) ? value : [value];
  const normalized = rawValues
    .flatMap((item) => {
      const raw = String(item).trim();
      if (raw.startsWith('[')) {
        try {
          const parsed = JSON.parse(raw);
          if (Array.isArray(parsed)) return parsed.map(String);
        } catch {
          // Fall back to comma-separated parsing for malformed input.
        }
      }
      return raw.split(',');
    })
    .map((item) => item.trim().toLowerCase())
    .filter(Boolean);
  return Array.from(new Set(normalized));
}

@Injectable()
export class ReportsService {
  constructor(
    private readonly db: Kysely<DB>,
    private readonly imageService: ImageService,
    private readonly logger: Logger,
  ) {}

  /** Returns a violation message if the free-plan limit is exceeded, or null if OK. Never throws. */
  private async checkFreeUserReportLimit(
    trx: Trx,
    userId: string,
    reportStatus: string,
  ): Promise<string | null> {
    const user = await trx
      .selectFrom('users')
      .select('premium_until')
      .where('id', '=', userId)
      .where('deleted_at', 'is', null)
      .executeTakeFirst();

    const isPremium = !!(user?.premium_until && new Date(user.premium_until) > new Date());
    if (isPremium) return null;

    const settings = await trx
      .selectFrom('app_settings')
      .select(['key', 'value'])
      .where('key', 'in', ['free_max_lost_reports', 'free_max_found_reports'])
      .where('deleted_at', 'is', null)
      .execute();

    const settingsMap = new Map<string, string>(settings.map((row) => [String(row.key), String(row.value)]));

    const freeMaxLostReports = Math.max(1, Number(settingsMap.get('free_max_lost_reports') || 1));
    const freeMaxFoundReports = Math.max(1, Number(settingsMap.get('free_max_found_reports') || 1));

    const activeCountResult = await trx
      .selectFrom('pet_reports')
      .select((eb) => eb.fn.countAll().as('count'))
      .where('user_id', '=', userId)
      .where('status', '=', reportStatus)
      .where('is_active', '=', true)
      .where('is_reunited', '=', false)
      .where('deleted_at', 'is', null)
      .executeTakeFirst();

    const activeCount = Number(activeCountResult?.count || 0);
    const maxAllowed = reportStatus === 'found' ? freeMaxFoundReports : freeMaxLostReports;

    if (activeCount >= maxAllowed) {
      return `Free users can only have ${maxAllowed} active ${reportStatus} report(s). Please upgrade to Premium.`;
    }
    return null;
  }

  /** Redeems a `found_report_bonus_tokens` row issued by the ads reward flow, bypassing the free-plan limit. */
  private async consumeFoundBonusToken(
    trx: Trx,
    userId: string,
    bonusToken: string,
  ): Promise<{ id: string }> {
    if (!bonusToken.trim()) {
      throw new BadRequestException('Missing found report bonus token');
    }

    const row = await sql<{ id: string; user_id: string; status: string; expires_at: Date | null }>`
      SELECT id, user_id, status, expires_at
      FROM found_report_bonus_tokens
      WHERE token = ${bonusToken.trim()} AND deleted_at IS NULL
      FOR UPDATE
    `.execute(trx);

    const token = row.rows[0];
    if (!token) {
      throw new NotFoundException('Found report bonus token not found');
    }
    if (String(token.user_id) !== userId) {
      throw new ForbiddenException('Found report bonus token does not belong to this user');
    }
    if (token.status !== 'issued') {
      throw new BadRequestException('Found report bonus token already used');
    }
    if (token.expires_at && new Date(token.expires_at) <= new Date()) {
      await trx
        .updateTable('found_report_bonus_tokens')
        .set({ status: 'expired', updated_at: new Date() })
        .where('id', '=', token.id)
        .execute();
      throw new BadRequestException('Found report bonus token expired');
    }

    await trx
      .updateTable('found_report_bonus_tokens')
      .set({ status: 'consumed', consumed_at: new Date(), updated_at: new Date() })
      .where('id', '=', token.id)
      .execute();

    return { id: String(token.id) };
  }

  async createReport(
    userId: string | null,
    body: CreateReportDto,
    files?: { photos?: Express.Multer.File[] },
  ) {
    try {
      const reportStatus = body.status || 'lost';
      const submittedFlagId = String(body.flag_id || '').trim();

      if (
        containsDisallowedUserContent([
          body.pet_name,
          body.breed,
          body.color,
          body.markings,
          body.description,
          body.location_name,
          body.reward,
          body.contact_name,
        ])
      ) {
        throw new BadRequestException(MODERATION_REJECTION_MESSAGE);
      }

      // Photo uploads happen outside the transaction (external CDN call).
      let photo_uris = body.existingPhotoUris || [];
      if (files?.photos) {
        const uploadedPhotos = await this.imageService.uploadMulterFiles(files.photos, 'photos');
        photo_uris = [...photo_uris, ...uploadedPhotos];
      }
      const photo_uri = photo_uris[0] || '';

      const result = await this.db.transaction().execute(async (trx) => {
        let consumedBonusTokenId: string | null = null;

        // 1. Free plan per-type limits from app_settings, with a found-bonus-token escape hatch.
        if (userId) {
          const limitViolation = await this.checkFreeUserReportLimit(trx, userId, reportStatus);
          if (limitViolation) {
            const bonusTokenValue = String(body.found_bonus_token || body.foundBonusToken || '').trim();
            if (reportStatus === 'found' && bonusTokenValue) {
              const consumed = await this.consumeFoundBonusToken(trx, userId, bonusTokenValue);
              consumedBonusTokenId = consumed.id;
            } else {
              throw new ForbiddenException(limitViolation);
            }
          }
        }

        // 2. Check if this specific pet already has an active lost report
        if (body.pet_id && reportStatus === 'lost') {
          const existingPetReport = await trx
            .selectFrom('pet_reports')
            .select('id')
            .where('pet_id', '=', body.pet_id)
            .where('is_reunited', '=', false)
            .where('is_active', '=', true)
            .where('status', '=', 'lost')
            .where('deleted_at', 'is', null)
            .executeTakeFirst();

          if (existingPetReport) {
            throw new BadRequestException('This pet already has an active lost report.');
          }
        }

        let finalPetId = body.pet_id;

        // 3. If no pet_id provided and it's a lost report by a logged-in user, auto-create a profile first
        if (!finalPetId && reportStatus === 'lost' && userId) {
          try {
            const profileResult = await trx
              .insertInto('pet_profiles')
              .values({
                user_id: userId,
                pet_type: body.pet_type,
                pet_name: body.pet_name || 'Unknown',
                breed: body.breed || '',
                size: body.size || 'medium',
                color: body.color || '',
                markings: body.markings || '',
                photo_uris: JSON.stringify(photo_uris),
                biometric_photo_uris: JSON.stringify([]),
                microchip_number: '',
                medical_notes: body.description || '',
                suburb: body.location_name || '',
                owner_name: body.contact_name || '',
                owner_phone: body.contact_phone || '',
                is_active: true,
                created_at: new Date(),
                updated_at: new Date(),
              })
              .returning('id')
              .executeTakeFirstOrThrow();

            finalPetId = profileResult.id;
          } catch (profileErr) {
            this.logger.error('[createReport] Auto-profile creation failed:', profileErr);
          }
        }

        // 4. Create report
        const report = await trx
          .insertInto('pet_reports')
          .values({
            user_id: userId || '',
            status: reportStatus,
            pet_type: body.pet_type,
            pet_name: body.pet_name,
            breed: body.breed || '',
            size: body.size || 'medium',
            color: body.color || '',
            markings: body.markings || '',
            photo_uri,
            photo_uris: JSON.stringify(photo_uris),
            description: body.description || '',
            latitude: body.latitude || 0,
            longitude: body.longitude || 0,
            location_name: body.location_name || '',
            last_seen_date: body.last_seen_date || '',
            reward: body.reward || '',
            contact_name: body.contact_name || '',
            contact_phone: body.contact_phone || '',
            show_contact_public: body.showContactPublic !== false,
            pet_id: finalPetId || null,
            is_active: true,
            is_reunited: false,
            is_boosted: false,
            reward_pool: 0,
            created_at: new Date(),
            updated_at: new Date(),
          })
          .returningAll()
          .executeTakeFirstOrThrow();

        // 5. Link a previously-flagged content-moderation record to this report
        if (submittedFlagId) {
          await trx
            .updateTable('pet_reports')
            .set({ is_flagged: true, flag_id: submittedFlagId, updated_at: new Date() })
            .where('id', '=', report.id)
            .execute();

          await sql`
            UPDATE flagged_reports
            SET target_type = 'report', source_table = 'pet_reports', target_id = ${report.id}, updated_at = NOW()
            WHERE id = ${submittedFlagId}
              AND deleted_at IS NULL
              AND (user_id = ${userId} OR user_id IS NULL)
          `.execute(trx);

          (report as any).is_flagged = true;
          (report as any).flag_id = submittedFlagId;
        }

        // 6. Update pet profile link if any
        if (finalPetId) {
          await trx
            .updateTable('pet_profiles')
            .set({ lost_id: report.id })
            .where('id', '=', finalPetId)
            .where('deleted_at', 'is', null)
            .execute();
        }

        // 7. Mark the redeemed bonus token as consumed by this report
        if (consumedBonusTokenId) {
          await trx
            .updateTable('found_report_bonus_tokens')
            .set({ consumed_report_id: report.id, updated_at: new Date() })
            .where('id', '=', consumedBonusTokenId)
            .execute();
        }

        return report;
      });

      return {
        status: true,
        statusCode: 201,
        data: mapReportRow(result, true, false),
      };
    } catch (error) {
      if (
        error instanceof ForbiddenException ||
        error instanceof BadRequestException ||
        error instanceof NotFoundException
      ) {
        throw error;
      }
      this.logger.error('Failed to create report', error);
      throw new InternalServerErrorException('Failed to create pet report');
    }
  }

  async reportMyPet(userId: string, body: ReportMyPetDto, files?: { photos?: Express.Multer.File[] }) {
    try {
      // 1. Verify ownership and get pet info
      const pet = await this.db
        .selectFrom('pet_profiles')
        .selectAll()
        .where('id', '=', body.pet_id)
        .where('user_id', '=', userId)
        .where('deleted_at', 'is', null)
        .executeTakeFirst();

      if (!pet) {
        throw new NotFoundException('Verified pet profile not found');
      }

      // 2. Check active lost report
      const activeReport = await this.db
        .selectFrom('pet_reports')
        .select('id')
        .where('pet_id', '=', body.pet_id)
        .where('is_reunited', '=', false)
        .where('is_active', '=', true)
        .where('status', '=', 'lost')
        .where('deleted_at', 'is', null)
        .executeTakeFirst();

      if (activeReport) {
        throw new BadRequestException('This pet already has an active lost report.');
      }

      if (containsDisallowedUserContent([body.location_name, body.reward, body.contact_name])) {
        throw new BadRequestException(MODERATION_REJECTION_MESSAGE);
      }

      // 3. Free plan limits check
      const limitViolation = await this.checkFreeUserReportLimit(this.db, userId, 'lost');
      if (limitViolation) {
        throw new ForbiddenException(limitViolation);
      }

      // 4. Process photos
      let photo_uris = body.existingPhotoUris || [];
      if (files?.photos) {
        const uploadedPhotos = await this.imageService.uploadMulterFiles(files.photos, 'photos');
        photo_uris = [...photo_uris, ...uploadedPhotos];
      }
      if (photo_uris.length === 0) {
        photo_uris = typeof pet.photo_uris === 'string' ? JSON.parse(pet.photo_uris) : pet.photo_uris || [];
      }

      // 5. Create report
      const result = await this.db
        .insertInto('pet_reports')
        .values({
          user_id: userId,
          status: 'lost',
          pet_type: pet.pet_type,
          pet_name: pet.pet_name,
          breed: pet.breed || '',
          size: pet.size || 'medium',
          color: pet.color || '',
          markings: pet.markings || '',
          photo_uri: photo_uris[0] || '',
          photo_uris: JSON.stringify(photo_uris),
          description: pet.medical_notes || '',
          latitude: body.latitude || 0,
          longitude: body.longitude || 0,
          location_name: body.location_name || '',
          last_seen_date: body.last_seen_date || '',
          reward: body.reward || '',
          contact_name: body.contact_name || '',
          contact_phone: body.contact_phone || '',
          show_contact_public: body.showContactPublic !== false,
          pet_id: body.pet_id,
          is_active: true,
          is_reunited: false,
          is_boosted: false,
          reward_pool: 0,
          created_at: new Date(),
          updated_at: new Date(),
        })
        .returningAll()
        .executeTakeFirstOrThrow();

      // 6. Link a previously-flagged content-moderation record to this report
      const submittedFlagId = String(body.flag_id || '').trim();
      if (submittedFlagId) {
        await this.db
          .updateTable('pet_reports')
          .set({ is_flagged: true, flag_id: submittedFlagId, updated_at: new Date() })
          .where('id', '=', result.id)
          .execute();

        await sql`
          UPDATE flagged_reports
          SET target_type = 'report', source_table = 'pet_reports', target_id = ${result.id}, updated_at = NOW()
          WHERE id = ${submittedFlagId}
            AND deleted_at IS NULL
            AND (user_id = ${userId} OR user_id IS NULL)
        `.execute(this.db);

        (result as any).is_flagged = true;
        (result as any).flag_id = submittedFlagId;
      }

      // 7. Update pet profile link
      await this.db
        .updateTable('pet_profiles')
        .set({ lost_id: result.id })
        .where('id', '=', body.pet_id)
        .where('deleted_at', 'is', null)
        .execute();

      return {
        status: true,
        statusCode: 201,
        data: mapReportRow(result, true, false),
      };
    } catch (error) {
      if (
        error instanceof NotFoundException ||
        error instanceof BadRequestException ||
        error instanceof ForbiddenException
      ) {
        throw error;
      }
      this.logger.error('Failed to report my pet', error);
      throw new InternalServerErrorException('Failed to generate report for pet');
    }
  }

  async updateReport(
    id: string,
    userId: string,
    body: UpdateReportDto,
    files?: { photos?: Express.Multer.File[] },
  ) {
    try {
      const existing = await this.db
        .selectFrom('pet_reports')
        .select('user_id')
        .where('id', '=', id)
        .where('deleted_at', 'is', null)
        .executeTakeFirst();

      if (!existing) {
        throw new NotFoundException('Report not found');
      }

      if (existing.user_id !== userId) {
        throw new ForbiddenException('Not authorized');
      }

      if (
        containsDisallowedUserContent([
          body.pet_name,
          body.breed,
          body.color,
          body.markings,
          body.description,
          body.location_name,
          body.reward,
          body.contact_name,
          body.reunion_message,
        ])
      ) {
        throw new BadRequestException(MODERATION_REJECTION_MESSAGE);
      }

      let photo_uris = body.existingPhotoUris || [];
      let filesUploadedOrPassed = false;

      if (body.existingPhotoUris !== undefined) {
        filesUploadedOrPassed = true;
      }

      if (files?.photos) {
        const uploadedPhotos = await this.imageService.uploadMulterFiles(files.photos, 'photos');
        photo_uris = [...photo_uris, ...uploadedPhotos];
        filesUploadedOrPassed = true;
      }

      const updateData: any = {};

      const updatableFields = [
        'status',
        'pet_name',
        'breed',
        'size',
        'color',
        'markings',
        'description',
        'latitude',
        'longitude',
        'location_name',
        'last_seen_date',
        'reward',
        'contact_name',
        'contact_phone',
        'reunion_message',
      ];

      for (const field of updatableFields) {
        if ((body as any)[field] !== undefined) {
          updateData[field] = (body as any)[field];
        }
      }

      if (body.showContactPublic !== undefined) {
        updateData.show_contact_public = body.showContactPublic;
      }
      if (body.is_reunited !== undefined) {
        updateData.is_reunited = body.is_reunited;
      }
      if (body.is_active !== undefined) {
        updateData.is_active = body.is_active;
      }

      if (filesUploadedOrPassed) {
        updateData.photo_uri = photo_uris[0] || '';
        updateData.photo_uris = JSON.stringify(photo_uris);
      }

      if (Object.keys(updateData).length === 0) {
        throw new BadRequestException('No fields to update');
      }

      updateData.updated_at = new Date();

      const result = await this.db
        .updateTable('pet_reports')
        .set(updateData)
        .where('id', '=', id)
        .where('deleted_at', 'is', null)
        .returningAll()
        .executeTakeFirstOrThrow();

      return {
        status: true,
        statusCode: 200,
        data: mapReportRow(result, true, false),
      };
    } catch (error) {
      if (
        error instanceof NotFoundException ||
        error instanceof ForbiddenException ||
        error instanceof BadRequestException
      ) {
        throw error;
      }
      this.logger.error('Failed to update report', error);
      throw new InternalServerErrorException('Failed to update pet report');
    }
  }

  async deleteReport(id: string, userId: string) {
    try {
      const existing = await this.db
        .selectFrom('pet_reports')
        .select('user_id')
        .where('id', '=', id)
        .where('deleted_at', 'is', null)
        .executeTakeFirst();

      if (!existing) {
        throw new NotFoundException('Report not found');
      }

      if (existing.user_id !== userId) {
        throw new ForbiddenException('Not authorized');
      }

      await this.db
        .updateTable('pet_reports')
        .set({ deleted_at: new Date(), updated_at: new Date() })
        .where('id', '=', id)
        .where('deleted_at', 'is', null)
        .execute();

      return {
        status: true,
        statusCode: 200,
        message: 'Report deleted',
      };
    } catch (error) {
      if (error instanceof NotFoundException || error instanceof ForbiddenException) {
        throw error;
      }
      this.logger.error('Failed to delete report', error);
      throw new InternalServerErrorException('Failed to delete pet report');
    }
  }

  async toggleLike(id: string, userId: string) {
    try {
      const existing = await this.db
        .selectFrom('report_likes')
        .select('id')
        .where('report_id', '=', id)
        .where('user_id', '=', userId)
        .where('deleted_at', 'is', null)
        .executeTakeFirst();

      let liked_by_me = false;

      if (existing) {
        await this.db
          .updateTable('report_likes')
          .set({ deleted_at: new Date() })
          .where('report_id', '=', id)
          .where('user_id', '=', userId)
          .where('deleted_at', 'is', null)
          .execute();
        liked_by_me = false;
      } else {
        const previousLike = await this.db
          .selectFrom('report_likes')
          .select('id')
          .where('report_id', '=', id)
          .where('user_id', '=', userId)
          .executeTakeFirst();

        if (previousLike) {
          await this.db
            .updateTable('report_likes')
            .set({ deleted_at: null, created_at: new Date() })
            .where('id', '=', previousLike.id)
            .execute();
        } else {
          await this.db
            .insertInto('report_likes')
            .values({
              report_id: id,
              user_id: userId,
              created_at: new Date(),
            })
            .execute();
        }
        liked_by_me = true;
      }

      const countResult = await this.db
        .selectFrom('report_likes')
        .select((eb) => eb.fn.countAll().as('count'))
        .where('report_id', '=', id)
        .where('deleted_at', 'is', null)
        .executeTakeFirst();

      return {
        status: true,
        statusCode: 200,
        data: {
          likes: Number(countResult?.count || 0),
          liked_by_me,
        },
      };
    } catch (error) {
      this.logger.error('Failed to toggle like', error);
      throw new InternalServerErrorException('Failed to toggle like on report');
    }
  }

  async addComment(id: string, userId: string | null, body: AddCommentDto) {
    try {
      if (containsDisallowedUserContent([body.author, body.text])) {
        throw new BadRequestException(MODERATION_REJECTION_MESSAGE);
      }

      const authorName = body.author || 'Anonymous';

      const result = await this.db
        .insertInto('comments')
        .values({
          report_id: id,
          user_id: userId,
          author: authorName,
          text: body.text,
          created_at: new Date(),
        })
        .returningAll()
        .executeTakeFirstOrThrow();

      const descText = `${authorName} commented: "${body.text.slice(0, 50)}${
        body.text.length > 50 ? '...' : ''
      }"`;

      await this.db
        .insertInto('timeline_events')
        .values({
          report_id: id,
          type: 'comment',
          description: descText,
          created_at: new Date(),
        })
        .execute();

      return {
        status: true,
        statusCode: 201,
        data: result,
      };
    } catch (error) {
      if (error instanceof BadRequestException) throw error;
      this.logger.error('Failed to add comment', error);
      throw new InternalServerErrorException('Failed to submit comment');
    }
  }

  async addReward(id: string, body: AddRewardDto) {
    try {
      const result = await this.db
        .updateTable('pet_reports')
        .set((eb) => ({
          reward_pool: eb('reward_pool', '+', body.amount),
          updated_at: new Date(),
        }))
        .where('id', '=', id)
        .where('deleted_at', 'is', null)
        .returningAll()
        .executeTakeFirst();

      if (!result) {
        throw new NotFoundException('Report not found');
      }

      await this.db
        .insertInto('timeline_events')
        .values({
          report_id: id,
          type: 'reward',
          description: `A reward of $${body.amount} was added to the pool.`,
          created_at: new Date(),
        })
        .execute();

      return {
        status: true,
        statusCode: 200,
        data: result,
      };
    } catch (error) {
      if (error instanceof NotFoundException) throw error;
      this.logger.error('Failed to add reward', error);
      throw new InternalServerErrorException('Failed to add reward');
    }
  }

  async boostReport(id: string) {
    try {
      const boostDurationDays = 3;
      const expiresAt = new Date();
      expiresAt.setDate(expiresAt.getDate() + boostDurationDays);

      const result = await this.db
        .updateTable('pet_reports')
        .set({
          is_boosted: true,
          boosted_at: new Date(),
          boost_expires_at: expiresAt,
          updated_at: new Date(),
        })
        .where('id', '=', id)
        .where('deleted_at', 'is', null)
        .returningAll()
        .executeTakeFirst();

      if (!result) {
        throw new NotFoundException('Report not found');
      }

      await this.db
        .insertInto('timeline_events')
        .values({
          report_id: id,
          type: 'boost',
          description: `The report was boosted for ${boostDurationDays} days.`,
          created_at: new Date(),
        })
        .execute();

      return {
        status: true,
        statusCode: 200,
        data: result,
      };
    } catch (error) {
      if (error instanceof NotFoundException) throw error;
      this.logger.error('Failed to boost report', error);
      throw new InternalServerErrorException('Failed to boost report');
    }
  }

  async markReunited(id: string, userId: string, body: MarkReunitedDto) {
    try {
      if (containsDisallowedUserContent([body.message])) {
        throw new BadRequestException(MODERATION_REJECTION_MESSAGE);
      }

      const existing = await this.db
        .selectFrom('pet_reports')
        .select('user_id')
        .where('id', '=', id)
        .where('deleted_at', 'is', null)
        .executeTakeFirst();

      if (!existing) {
        throw new NotFoundException('Report not found');
      }

      if (existing.user_id !== userId) {
        throw new ForbiddenException('Not authorized');
      }

      const result = await this.db
        .updateTable('pet_reports')
        .set({
          is_reunited: true,
          is_active: false,
          reunion_message: body.message || '',
          reunion_date: new Date(),
          updated_at: new Date(),
        })
        .where('id', '=', id)
        .where('deleted_at', 'is', null)
        .returningAll()
        .executeTakeFirstOrThrow();

      if (result.pet_id) {
        await this.db
          .updateTable('pet_profiles')
          .set({ lost_id: null })
          .where('id', '=', result.pet_id)
          .where('deleted_at', 'is', null)
          .execute();
      }

      await this.db
        .insertInto('timeline_events')
        .values({
          report_id: id,
          type: 'reunited',
          description: `Pet was marked as reunited!${body.message ? ': ' + body.message : ''}`,
          created_at: new Date(),
        })
        .execute();

      return {
        status: true,
        statusCode: 200,
        data: result,
      };
    } catch (error) {
      if (
        error instanceof NotFoundException ||
        error instanceof ForbiddenException ||
        error instanceof BadRequestException
      ) {
        throw error;
      }
      this.logger.error('Failed to mark reunited', error);
      throw new InternalServerErrorException('Failed to mark reunited');
    }
  }

  async getAllReports(userId: string | null, query: any) {
    try {
      const isPaginated = query.page !== undefined;
      const limit = Math.min(parseInt(query.limit ?? (isPaginated ? '20' : '100')), 500);
      const page = parseInt(query.page || '0');
      const offset = isPaginated ? page * limit : parseInt(query.offset || '0');

      const statusFilter = query.status || null;
      const suburbFilter = query.suburb || null;
      const petTypeFilters = normalizeQueryList(query.pet_type).slice(0, 20);
      const breedFilters = normalizeQueryList(query.breed).slice(0, 50);
      const colorFilters = normalizeQueryList(query.color).slice(0, 50);
      const mineFilter = query.mine === 'true';
      const savedFilter = query.saved === 'true';

      const lat = parseFloat(query.lat || 'NaN');
      const lng = parseFloat(query.lng || 'NaN');
      const radiusKm = parseFloat(query.radius || 'NaN');

      const hasGeo = !isNaN(lat) && !isNaN(lng) && !isNaN(radiusKm) && radiusKm > 0;

      // Build the dynamic WHERE clause as a list of `sql` fragments, matching Express's
      // parameterized-string-builder approach 1:1 (Kysely auto-binds each ${} value).
      const conditions = [sql`r.deleted_at IS NULL`];

      if (statusFilter === 'reunited') {
        conditions.push(sql`r.is_reunited = true`);
      } else if (statusFilter && statusFilter !== 'all' && ['lost', 'found'].includes(statusFilter)) {
        conditions.push(sql`r.status = ${statusFilter}`);
        conditions.push(sql`r.is_reunited = false`);
      } else if (!mineFilter && !savedFilter) {
        conditions.push(sql`r.is_reunited = false`);
      }

      if (!mineFilter) {
        conditions.push(sql`r.is_active = true`);
      }

      if (suburbFilter) {
        conditions.push(sql`LOWER(r.location_name) LIKE ${'%' + String(suburbFilter).toLowerCase() + '%'}`);
      }

      if (petTypeFilters.length > 0) {
        conditions.push(sql`LOWER(r.pet_type) = ANY(${petTypeFilters}::text[])`);
      }

      if (breedFilters.length > 0) {
        conditions.push(sql`LOWER(r.breed) = ANY(${breedFilters}::text[])`);
      }

      if (colorFilters.length > 0) {
        conditions.push(sql`EXISTS (
          SELECT 1 FROM unnest(${colorFilters}::text[]) selected_color
          WHERE LOWER(r.color) LIKE '%' || selected_color || '%'
        )`);
      }

      if (mineFilter && userId) {
        conditions.push(sql`r.user_id = ${userId}`);
      }

      if (savedFilter && userId) {
        conditions.push(
          sql`EXISTS (SELECT 1 FROM saved_pets sp WHERE sp.pet_id = r.id AND sp.user_id = ${userId} AND sp.deleted_at IS NULL)`,
        );
      }

      if (userId) {
        conditions.push(
          sql`NOT EXISTS (
            SELECT 1 FROM blocked_users bu
            WHERE bu.blocker_id = ${userId} AND bu.blocked_id = r.user_id AND bu.deleted_at IS NULL
          )`,
        );
      }

      let distanceExpr = sql`NULL::float`;
      if (hasGeo) {
        const delta = radiusKm / 111.0;
        conditions.push(sql`r.latitude BETWEEN ${lat - delta} AND ${lat + delta}`);
        conditions.push(sql`r.longitude BETWEEN ${lng - delta} AND ${lng + delta}`);
        distanceExpr = sql`(6371 * acos(GREATEST(-1.0, LEAST(1.0, cos(radians(${lat})) * cos(radians(latitude)) * cos(radians(longitude) - radians(${lng})) + sin(radians(${lat})) * sin(radians(latitude))))))`;
      }

      const whereClause = sql`WHERE ${sql.join(conditions, sql` AND `)}`;
      const outerWhere = hasGeo ? sql`WHERE distance < ${radiusKm}` : sql``;
      const orderClause = hasGeo
        ? sql`ORDER BY distance ASC`
        : sql`ORDER BY CASE WHEN is_boosted = true AND boost_expires_at > NOW() THEN 0 ELSE 1 END, created_at DESC`;

      const mainQuery = sql<any>`
        ${sql.raw(UNIFIED_REPORTS_CTE)}
        SELECT * FROM (
          SELECT r.*,
            COALESCE((SELECT COUNT(*) FROM report_likes WHERE report_id = r.id AND deleted_at IS NULL), 0)::int AS likes,
            ${distanceExpr} AS distance
          FROM unified_reports r
          ${whereClause}
        ) sub
        ${outerWhere}
        ${orderClause}
        LIMIT ${limit} OFFSET ${offset}
      `;

      const result = await mainQuery.execute(this.db);

      const reports = result.rows
        .map((row: any) => {
          const is_owner = userId ? row.user_id === userId : false;
          const mapped = mapUnifiedReportRow(row, is_owner, false);
          if (hasGeo && row.distance !== null) {
            mapped.distanceKm = parseFloat(parseFloat(row.distance).toFixed(1));
          }
          return mapped;
        })
        .map((report: any) => (userId ? report : applyPublicReportMask(report)));

      if (userId) {
        const likedResult = await this.db
          .selectFrom('report_likes')
          .select('report_id')
          .where('user_id', '=', userId)
          .where('deleted_at', 'is', null)
          .execute();

        const savedResult = await this.db
          .selectFrom('saved_pets')
          .select('pet_id')
          .where('user_id', '=', userId)
          .where('deleted_at', 'is', null)
          .execute();

        const likedIds = new Set(likedResult.map((r) => r.report_id));
        const savedIds = new Set(savedResult.map((r) => r.pet_id));

        reports.forEach((r: any) => {
          r.liked_by_me = likedIds.has(r.id);
          r.savedByMe = savedIds.has(r.id);
        });
      }

      if (isPaginated) {
        const countQuery = sql<{ count: string }>`
          ${sql.raw(UNIFIED_REPORTS_CTE)}
          SELECT (
            SELECT COUNT(*)
            FROM (
              SELECT r.id, ${distanceExpr} AS distance
              FROM unified_reports r
              ${whereClause}
            ) s
            ${outerWhere}
          ) as count
        `;
        const countResult = await countQuery.execute(this.db);
        const total = parseInt(countResult.rows[0]?.count || '0');

        return {
          status: true,
          statusCode: 200,
          data: {
            reports,
            total,
            page,
            hasMore: (page + 1) * limit < total,
          },
        };
      }

      return {
        status: true,
        statusCode: 200,
        data: reports,
      };
    } catch (error) {
      this.logger.error('Failed to get reports', error);
      throw new InternalServerErrorException('Failed to retrieve reports');
    }
  }

  async getReunitedReports(limitQuery?: string, offsetQuery?: string) {
    try {
      const limit = parseInt(limitQuery || '10');
      const offset = parseInt(offsetQuery || '0');

      const dataQuery = this.db
        .selectFrom('pet_reports')
        .select([
          'pet_name',
          'breed',
          'location_name',
          'photo_uri',
          'photo_uris',
          'reunion_date',
          'pet_reunited_id',
          'status',
          'id',
        ])
        .where('is_reunited', '=', true)
        .where('deleted_at', 'is', null)
        .where((eb) => eb.not(eb('pet_name', '=', 'Unknown').and('pet_reunited_id', 'is not', null)))
        .orderBy('created_at', 'desc')
        .limit(limit)
        .offset(offset);

      const countQuery = this.db
        .selectFrom('pet_reports')
        .select((eb) => eb.fn.countAll().as('total_count'))
        .where('is_reunited', '=', true)
        .where('deleted_at', 'is', null)
        .where((eb) => eb.not(eb('pet_name', '=', 'Unknown').and('pet_reunited_id', 'is not', null)));

      const [dataResult, countResult] = await Promise.all([
        dataQuery.execute(),
        countQuery.executeTakeFirst(),
      ]);

      const total = Number(countResult?.total_count || 0);
      const page = Math.floor(offset / limit);

      const reports = dataResult.map((r: any) => ({
        id: r.id,
        pet_name: r.pet_name,
        breed: r.breed,
        location_name: r.location_name,
        photo_uri: r.photo_uri,
        photo_uris: typeof r.photo_uris === 'string' ? JSON.parse(r.photo_uris) : r.photo_uris || [],
        reunion_date: r.reunion_date instanceof Date ? r.reunion_date.toISOString() : r.reunion_date,
        pet_reunited_id: r.pet_reunited_id,
        status: r.status,
      }));

      return {
        status: true,
        statusCode: 200,
        data: {
          reports,
          total,
          page,
          hasMore: offset + limit < total,
        },
      };
    } catch (error) {
      this.logger.error('Failed to get reunited reports', error);
      throw new InternalServerErrorException('Failed to retrieve reunited reports');
    }
  }

  async getReportById(id: string, userId: string | null) {
    try {
      const blockedFilter = userId
        ? sql`AND NOT EXISTS (
            SELECT 1 FROM blocked_users bu
            WHERE bu.blocker_id = ${userId} AND bu.blocked_id = r.user_id AND bu.deleted_at IS NULL
          )`
        : sql``;

      const result = await sql<any>`
        ${sql.raw(UNIFIED_REPORTS_CTE)}
        SELECT r.*,
          COALESCE((SELECT COUNT(*) FROM report_likes WHERE report_id = r.id AND deleted_at IS NULL), 0)::int AS likes
        FROM unified_reports r
        WHERE r.id = ${id}
          AND r.deleted_at IS NULL
          ${blockedFilter}
      `.execute(this.db);

      const row = result.rows[0];
      if (!row) {
        throw new NotFoundException('Report not found');
      }

      const isExternal = row.report_source === 'scraped';
      const is_owner = userId ? row.user_id === userId : false;
      if (!is_owner && row.is_active === false) {
        throw new NotFoundException('Report not found');
      }

      let liked_by_me = false;
      if (userId && !isExternal) {
        const likeCheck = await this.db
          .selectFrom('report_likes')
          .select('id')
          .where('report_id', '=', id)
          .where('user_id', '=', userId)
          .where('deleted_at', 'is', null)
          .executeTakeFirst();
        liked_by_me = !!likeCheck;
      }

      const commentsPromise = isExternal
        ? Promise.resolve([])
        : userId
          ? sql<any>`
              SELECT c.* FROM comments c
              WHERE c.report_id = ${id}
                AND c.deleted_at IS NULL
                AND NOT EXISTS (
                  SELECT 1 FROM blocked_users bu
                  WHERE bu.blocker_id = ${userId} AND bu.blocked_id = c.user_id AND bu.deleted_at IS NULL
                )
              ORDER BY c.created_at ASC
            `
              .execute(this.db)
              .then((r) => r.rows)
          : this.db
              .selectFrom('comments')
              .selectAll()
              .where('report_id', '=', id)
              .where('deleted_at', 'is', null)
              .orderBy('created_at', 'asc')
              .execute();

      const timelinePromise = isExternal
        ? Promise.resolve([])
        : this.db
            .selectFrom('timeline_events')
            .selectAll()
            .where('report_id', '=', id)
            .where('deleted_at', 'is', null)
            .orderBy('created_at', 'asc')
            .execute();

      const savedPromise = userId
        ? this.db
            .selectFrom('saved_pets')
            .select('pet_id')
            .where('pet_id', '=', id)
            .where('user_id', '=', userId)
            .where('deleted_at', 'is', null)
            .executeTakeFirst()
        : Promise.resolve(null);

      const [comments, timeline, savedCheck] = await Promise.all([
        commentsPromise,
        timelinePromise,
        savedPromise,
      ]);

      const report = mapUnifiedReportRow(row, is_owner, liked_by_me);
      report.savedByMe = !!savedCheck;
      report.comments = (comments as any[]).map((c) => ({
        id: c.id,
        user_id: c.user_id,
        author: c.author,
        text: c.text,
        created_at: c.created_at instanceof Date ? c.created_at.toISOString() : c.created_at,
      }));
      report.timeline = (timeline as any[]).map((t) => ({
        id: t.id,
        type: t.type,
        description: t.description,
        created_at: t.created_at instanceof Date ? t.created_at.toISOString() : t.created_at,
      }));

      const responseReport = userId ? report : applyPublicReportMask(report);

      return {
        status: true,
        statusCode: 200,
        data: responseReport,
      };
    } catch (error) {
      if (error instanceof NotFoundException) throw error;
      this.logger.error('Failed to get report by id', error);
      throw new InternalServerErrorException('Failed to retrieve pet report details');
    }
  }

  async getReportStats() {
    try {
      const result = await sql<{ lost: string; found: string; reunited: string }>`
        ${sql.raw(UNIFIED_REPORTS_CTE)}
        SELECT
          COUNT(*) FILTER (WHERE status = 'lost' AND is_reunited = false AND is_active = true) AS lost,
          COUNT(*) FILTER (WHERE status = 'found' AND is_reunited = false AND is_active = true) AS found,
          COUNT(*) FILTER (WHERE is_reunited = true) AS reunited
        FROM unified_reports
        WHERE deleted_at IS NULL
      `.execute(this.db);

      const row = result.rows[0];
      return {
        status: true,
        statusCode: 200,
        data: {
          lost: parseInt(row?.lost || '0', 10) || 0,
          found: parseInt(row?.found || '0', 10) || 0,
          reunited: parseInt(row?.reunited || '0', 10) || 0,
        },
      };
    } catch (error) {
      this.logger.error('Failed to get report stats', error);
      throw new InternalServerErrorException('Failed to retrieve report statistics');
    }
  }

  /** Distinct filter values (pet_type / breed / color) across active reports, internal + scraped. */
  async getReportFilterOptions() {
    const activeReportsWhere = 'deleted_at IS NULL AND is_active = true AND is_reunited = false';

    try {
      const [petTypes, breeds, colors] = await Promise.all([
        sql<{ value: string }>`
          ${sql.raw(UNIFIED_REPORTS_CTE)}
          SELECT LOWER(BTRIM(pet_type)) AS value
          FROM unified_reports
          WHERE ${sql.raw(activeReportsWhere)} AND NULLIF(BTRIM(pet_type), '') IS NOT NULL
          GROUP BY LOWER(BTRIM(pet_type))
          ORDER BY value
        `.execute(this.db),
        sql<{ value: string }>`
          ${sql.raw(UNIFIED_REPORTS_CTE)}
          SELECT INITCAP(normalized_value) AS value
          FROM (
            SELECT LOWER(REGEXP_REPLACE(BTRIM(breed), '\\s+', ' ', 'g')) AS normalized_value
            FROM unified_reports
            WHERE ${sql.raw(activeReportsWhere)} AND NULLIF(BTRIM(breed), '') IS NOT NULL
          ) breeds
          GROUP BY normalized_value
          ORDER BY normalized_value
        `.execute(this.db),
        sql<{ value: string }>`
          ${sql.raw(UNIFIED_REPORTS_CTE)}
          SELECT INITCAP(normalized_value) AS value
          FROM (
            SELECT LOWER(REGEXP_REPLACE(BTRIM(color_part), '\\s+', ' ', 'g')) AS normalized_value
            FROM unified_reports
            CROSS JOIN LATERAL REGEXP_SPLIT_TO_TABLE(COALESCE(color, ''), ',') AS split_color(color_part)
            WHERE ${sql.raw(activeReportsWhere)} AND NULLIF(BTRIM(color_part), '') IS NOT NULL
          ) colors
          GROUP BY normalized_value
          ORDER BY normalized_value
        `.execute(this.db),
      ]);

      return {
        status: true,
        statusCode: 200,
        data: {
          pet_type: petTypes.rows.map((r) => r.value),
          breed: breeds.rows.map((r) => r.value),
          color: colors.rows.map((r) => r.value),
        },
      };
    } catch (error) {
      this.logger.error('Failed to fetch report filter options', error);
      throw new InternalServerErrorException('Failed to fetch report filter options');
    }
  }

  async savePet(id: string, userId: string) {
    try {
      const reportExists = await sql<{ id: string }>`
        ${sql.raw(UNIFIED_REPORTS_CTE)}
        SELECT id FROM unified_reports
        WHERE id = ${id} AND deleted_at IS NULL AND is_active = true
        LIMIT 1
      `.execute(this.db);

      if (reportExists.rows.length === 0) {
        throw new NotFoundException('Report not found');
      }

      const existing = await this.db
        .selectFrom('saved_pets')
        .select(['id', 'deleted_at'])
        .where('user_id', '=', userId)
        .where('pet_id', '=', id)
        .executeTakeFirst();

      if (existing) {
        if (existing.deleted_at) {
          await this.db
            .updateTable('saved_pets')
            .set({ deleted_at: null, created_at: new Date() })
            .where('id', '=', existing.id)
            .execute();
        }
      } else {
        await this.db
          .insertInto('saved_pets')
          .values({
            user_id: userId,
            pet_id: id,
            created_at: new Date(),
          })
          .execute();
      }

      return {
        status: true,
        statusCode: 200,
        message: 'Pet saved successfully',
      };
    } catch (error) {
      if (error instanceof NotFoundException) throw error;
      this.logger.error('Failed to save pet', error);
      throw new InternalServerErrorException('Failed to save pet report');
    }
  }

  async unsavePet(id: string, userId: string) {
    try {
      await this.db
        .updateTable('saved_pets')
        .set({ deleted_at: new Date() })
        .where('user_id', '=', userId)
        .where('pet_id', '=', id)
        .where('deleted_at', 'is', null)
        .execute();

      return {
        status: true,
        statusCode: 200,
        message: 'Pet unsaved successfully',
      };
    } catch (error) {
      this.logger.error('Failed to unsave pet', error);
      throw new InternalServerErrorException('Failed to unsave pet report');
    }
  }
}