import {
  Injectable,
  NotFoundException,
  BadRequestException,
  ForbiddenException,
  UnauthorizedException,
  GoneException,
  Logger,
} from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import * as bcrypt from 'bcrypt';
import * as crypto from 'crypto';
import * as fs from 'fs';
import * as path from 'path';

import { Document, DocumentStatus } from '../entities/document.entity';
import { DocumentRoleAssignment } from '../entities/document-role-assignment.entity';
import { DocumentOtp } from '../entities/document-otp.entity';
import {
  DocumentAccessLog,
  DocumentAction,
} from '../entities/document-access-log.entity';

import { UploadDocumentDto } from '../dto/upload-document.dto';
import { UpdateDocumentDto } from '../dto/update-document.dto';
import { ListDocumentsQueryDto } from '../dto/list-documents.dto';
import { UnlockDocumentDto } from '../dto/unlock-document.dto';
import { ListAccessLogQueryDto } from '../dto/list-access-log.dto';

import { MailsService } from '../../mails/mails.service';
import { buildDocumentOtpEmail } from '../templates/document-otp-email-template';
import { buildDocumentSharedEmail } from '../templates/document-shared-email-template';

// ─── Constants matching the rest of your codebase ──────────────────────
const SUPER_ADMIN_ROLE_ID = 1;
const OTP_TTL_MINUTES = 10;
const OTP_MAX_ATTEMPTS = 5;
const ACCESS_TOKEN_TTL_MINUTES = 15;

// ─── In-memory access token store (short-lived) ────────────────────────
// After a successful unlock we hand out an opaque token the frontend
// includes in the ?token=… query of /view and /download. Living in-memory
// is fine because tokens are 15-min single-purpose; if you run multiple
// Node instances behind a load balancer, back this with Redis instead.
interface AccessTokenPayload {
  document_id: number;
  user_id: number;
  expires_at: number; // ms epoch
  downloaded: boolean;
}
const ACCESS_TOKENS = new Map<string, AccessTokenPayload>();

// ─── Log context (pulled from the controller) ──────────────────────────
export interface RequestContext {
  ip?: string | null;
  user_agent?: string | null;
}

@Injectable()
export class DocumentsService {
  private readonly logger = new Logger('DocumentsService');

  constructor(
    @InjectDataSource('scheme_dbs')
    private readonly dataSource: DataSource,
    private readonly mailsService: MailsService,
  ) {}

  // ═══════════════════════════════════════════════════════════════════
  //   ROLE & PERMISSION HELPERS
  // ═══════════════════════════════════════════════════════════════════

  /** Returns [{role_id, name}] for the given user, using the user_roles + roles tables. */
  private async getUserRoles(
    userId: number,
  ): Promise<{ role_id: number; name: string }[]> {
    const rows: { role_id: number; name: string }[] = await this.dataSource
      .query(
        `SELECT ur.role_id, r.name
         FROM user_roles ur
         INNER JOIN roles r ON r.id = ur.role_id
         WHERE ur.user_id = ?`,
        [userId],
      );
    return rows ?? [];
  }

  private async isAdmin(userId: number): Promise<boolean> {
    const roles = await this.getUserRoles(userId);
    return roles.some((r) => r.role_id === SUPER_ADMIN_ROLE_ID);
  }

  /**
   * All users belonging to ANY of the given roles, deduped by user id.
   * Used both to populate the "who gets notified" checklist in the
   * upload modal, and to resolve the final email recipient list on upload.
   */
  private async getUsersForRoles(
    roleIds: number[],
  ): Promise<{ id: number; name: string; email: string; role_id: number }[]> {
    if (!roleIds.length) return [];
    const rows: {
      id: number;
      firstName: string | null;
      lastName: string | null;
      email: string;
      role_id: number;
    }[] = await this.dataSource.query(
      `SELECT DISTINCT u.id, u.firstName, u.lastName, u.email, ur.role_id
       FROM user_roles ur
       INNER JOIN users u ON u.id = ur.user_id
       WHERE ur.role_id IN (?)`,
      [roleIds],
    );
    return rows.map((r) => ({
      id: r.id,
      name: `${r.firstName ?? ''} ${r.lastName ?? ''}`.trim() || r.email,
      email: r.email,
      role_id: r.role_id,
    }));
  }

  /** Public, admin-gated entry point for GET /documents/roles/users. */
  async getUsersForRolesPublic(
    roleIds: number[],
    userId: number,
  ): Promise<{ id: number; name: string; email: string; role_id: number }[]> {
    if (!(await this.isAdmin(userId))) {
      throw new ForbiddenException('Only admin can view role membership');
    }
    if (roleIds.length === 0) return [];
    return this.getUsersForRoles(roleIds);
  }

  /**
   * Fire-and-forget notification emails after an upload. Never throws —
   * a failed email must not roll back or fail the upload itself; each
   * failure is logged individually so one bad address doesn't block the rest.
   */
  private async sendNewDocumentNotifications(
    doc: Document,
    roleIds: number[],
    notifyUserIds: number[] | undefined,
    uploaderId: number,
  ): Promise<void> {
    const roleMembers = await this.getUsersForRoles(roleIds);
    if (roleMembers.length === 0) return;

    // De-duped candidate list (a user can be in more than one assigned role).
    const byId = new Map(roleMembers.map((u) => [u.id, u]));

    // If the admin picked a specific subset, honour it — but only within
    // the actual role membership (never notify someone outside the
    // assigned roles, even if the client sent a stray id).
    const recipients = notifyUserIds
      ? notifyUserIds.map((id) => byId.get(id)).filter((u): u is NonNullable<typeof u> => !!u)
      : Array.from(byId.values());

    if (recipients.length === 0) return;

    const uploader = await this.getUserSnapshot(uploaderId).catch(() => ({
      name: 'Admin',
      email: '',
      role_name: null,
    }));

    const portalUrl = `${process.env.FRONTEND_URL || ''}/modules/documents`;

    await Promise.all(
      recipients.map(async (user) => {
        try {
          const { subject, html } = buildDocumentSharedEmail({
            recipient_name: user.name,
            document_title: doc.title,
            document_category: doc.category,
            uploader_name: uploader.name,
            require_otp: !!doc.require_otp,
            has_password: !!doc.password_hash,
            portal_url: portalUrl,
          });
         await this.mailsService.sendCustom(user.email, subject, html);
        } catch (err: any) {
          this.logger.error(
            `Failed to send new-document email to user=${user.id} (${user.email}): ${err.message}`,
          );
        }
      }),
    );
  }

  private async getUserSnapshot(
    userId: number,
  ): Promise<{ name: string; email: string; role_name: string | null }> {
    const row = await this.dataSource
      .query(
        `SELECT u.firstName, u.lastName, u.email
         FROM users u WHERE u.id = ? LIMIT 1`,
        [userId],
      )
      .then((rows: any[]) => rows?.[0]);

    if (!row) {
      throw new NotFoundException(`User ${userId} not found`);
    }

    const roles = await this.getUserRoles(userId);
    return {
      name: `${row.firstName ?? ''} ${row.lastName ?? ''}`.trim() || row.email,
      email: row.email,
      role_name: roles[0]?.name ?? null,
    };
  }

  // ═══════════════════════════════════════════════════════════════════
  //   ADMIN — UPLOAD / EDIT / DELETE
  // ═══════════════════════════════════════════════════════════════════

  async upload(
    dto: UploadDocumentDto,
    file: Express.Multer.File,
    userId: number,
  ): Promise<Document> {
    if (!(await this.isAdmin(userId))) {
      throw new ForbiddenException('Only admin can upload documents');
    }
    if (!file) {
      throw new BadRequestException('A file is required');
    }

    // Resolve role names once so we can denormalise into role_assignments.
    const roleRows: { id: number; name: string }[] = await this.dataSource
      .query(`SELECT id, name FROM roles WHERE id IN (?)`, [dto.role_ids]);

    if (roleRows.length !== dto.role_ids.length) {
      // Clean up the uploaded file — no point keeping an orphan on disk.
      this.safeUnlink(file.path);
      throw new BadRequestException('One or more role_ids are invalid');
    }

    const passwordHash = dto.password
      ? await bcrypt.hash(dto.password, 10)
      : null;

    // Transaction so document + role_assignments land together, or not at all.
    return this.dataSource.transaction(async (manager) => {
      const doc = manager.create(Document, {
        title: dto.title,
        category: dto.category,
        description: dto.description ?? null,
        file_path: file.path,
        file_name: file.originalname,
        file_size: file.size ?? null,
        mime_type: file.mimetype ?? null,
        password_hash: passwordHash,
        require_otp: dto.require_otp === 'false' ? 0 : 1,
        allow_download: dto.allow_download === 'false' ? 0 : 1,
        expiry_date: dto.expiry_date ?? null,
        status: DocumentStatus.ACTIVE,
        uploaded_by: userId,
      });
      const saved = await manager.save(Document, doc);

      const assignments = roleRows.map((r) =>
        manager.create(DocumentRoleAssignment, {
          document_id: saved.id,
          role_id: r.id,
          role_name: r.name,
          assigned_by: userId,
        }),
      );
      await manager.save(DocumentRoleAssignment, assignments);

      this.logger.log(
        `Document ${saved.id} uploaded by user=${userId} → roles=[${dto.role_ids.join(',')}]`,
      );

      // Reload with assignments for the response.
      return manager.findOneOrFail(Document, {
        where: { id: saved.id },
        relations: ['role_assignments'],
      });
    }).then((saved) => {
      // Outside the transaction, and NOT awaited — a slow/failed email
      // provider should never delay the upload response to the admin.
      this.sendNewDocumentNotifications(
        saved,
        dto.role_ids,
        dto.notify_user_ids,
        userId,
      ).catch((err) =>
        this.logger.error(`Notification dispatch failed for doc ${saved.id}: ${err.message}`),
      );
      return saved;
    });
  }

  async update(
    id: number,
    dto: UpdateDocumentDto,
    userId: number,
  ): Promise<Document> {
    if (!(await this.isAdmin(userId))) {
      throw new ForbiddenException('Only admin can edit documents');
    }

    return this.dataSource.transaction(async (manager) => {
      const doc = await manager.findOne(Document, { where: { id } });
      if (!doc) throw new NotFoundException(`Document ${id} not found`);

      if (dto.title !== undefined) doc.title = dto.title;
      if (dto.category !== undefined) doc.category = dto.category;
      if (dto.description !== undefined) doc.description = dto.description;
      if (dto.require_otp !== undefined) doc.require_otp = dto.require_otp ? 1 : 0;
      if (dto.allow_download !== undefined)
        doc.allow_download = dto.allow_download ? 1 : 0;
      if (dto.expiry_date !== undefined) doc.expiry_date = dto.expiry_date;
      if (dto.status !== undefined) doc.status = dto.status;

      // Password: empty string = clear it, non-empty = re-hash.
      if (dto.password !== undefined) {
        doc.password_hash = dto.password
          ? await bcrypt.hash(dto.password, 10)
          : null;
      }

      await manager.save(Document, doc);

      // Replace-all role assignment when role_ids provided.
      if (dto.role_ids) {
        const roleRows: { id: number; name: string }[] = await manager
          .query(`SELECT id, name FROM roles WHERE id IN (?)`, [dto.role_ids]);
        if (roleRows.length !== dto.role_ids.length) {
          throw new BadRequestException('One or more role_ids are invalid');
        }

        await manager.delete(DocumentRoleAssignment, { document_id: id });
        await manager.save(
          DocumentRoleAssignment,
          roleRows.map((r) =>
            manager.create(DocumentRoleAssignment, {
              document_id: id,
              role_id: r.id,
              role_name: r.name,
              assigned_by: userId,
            }),
          ),
        );
      }

      return manager.findOneOrFail(Document, {
        where: { id },
        relations: ['role_assignments'],
      });
    });
  }

  async remove(id: number, userId: number): Promise<{ ok: true }> {
    if (!(await this.isAdmin(userId))) {
      throw new ForbiddenException('Only admin can delete documents');
    }

    const doc = await this.dataSource.getRepository(Document).findOne({ where: { id } });
    if (!doc) throw new NotFoundException(`Document ${id} not found`);

    // Soft-archive (audit log + FKs must survive).
    doc.status = DocumentStatus.ARCHIVED;
    await this.dataSource.getRepository(Document).save(doc);

    this.logger.log(`Document ${id} archived by user=${userId}`);
    return { ok: true };
  }

  // ═══════════════════════════════════════════════════════════════════
  //   LISTING — admin sees all, staff sees only theirs
  // ═══════════════════════════════════════════════════════════════════

  async findAll(q: ListDocumentsQueryDto, userId: number) {
    const admin = await this.isAdmin(userId);
    const userRoles = await this.getUserRoles(userId);
    const myRoleIds = userRoles.map((r) => r.role_id);

    const page = q.page ?? 1;
    const limit = q.limit ?? 20;
    const offset = (page - 1) * limit;

    const qb = this.dataSource
      .getRepository(Document)
      .createQueryBuilder('d')
      .leftJoinAndSelect('d.role_assignments', 'ra')
      .orderBy('d.created_at', 'DESC');

    // Non-admin: only rows this user's roles are assigned to.
    if (!admin) {
      if (myRoleIds.length === 0) {
        // User has no roles → no docs.
        return { data: [], meta: { total: 0, page, limit, totalPages: 0 } };
      }
      qb.andWhere(
        // subquery so pagination stays correct despite the join above
        `d.id IN (
          SELECT dra.document_id FROM document_role_assignments dra
          WHERE dra.role_id IN (:...myRoleIds)
        )`,
        { myRoleIds },
      );
      // Staff shouldn't see archived / expired.
      qb.andWhere('d.status = :st', { st: DocumentStatus.ACTIVE });
      qb.andWhere('(d.expiry_date IS NULL OR d.expiry_date >= CURDATE())');
    } else {
      if (q.status) qb.andWhere('d.status = :st', { st: q.status });
    }

    if (q.category) qb.andWhere('d.category = :c', { c: q.category });
    if (q.role_id) {
      qb.andWhere(
        `d.id IN (SELECT dra2.document_id FROM document_role_assignments dra2 WHERE dra2.role_id = :rid)`,
        { rid: q.role_id },
      );
    }
    if (q.search) {
      qb.andWhere('(d.title LIKE :s OR d.description LIKE :s)', {
        s: `%${q.search}%`,
      });
    }

    const [data, total] = await qb.skip(offset).take(limit).getManyAndCount();

    // Attach open counts + failed counts per doc for the admin table.
    // Keep this cheap: one grouped query for the visible page.
    if (data.length && admin) {
      const ids = data.map((d) => d.id);
      const counts: {
        document_id: number;
        opens: string;
        failed: string;
      }[] = await this.dataSource.query(
        `SELECT document_id,
                SUM(action IN ('viewed','downloaded')) AS opens,
                SUM(action IN ('otp_failed','password_failed')) AS failed
         FROM document_access_log
         WHERE document_id IN (?)
         GROUP BY document_id`,
        [ids],
      );
      const byId = new Map(counts.map((c) => [Number(c.document_id), c]));
      for (const d of data) {
        const c = byId.get(Number(d.id));
        (d as any).open_count = Number(c?.opens ?? 0);
        (d as any).failed_count = Number(c?.failed ?? 0);
      }
    }

    return {
      data,
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    };
  }

  async findOne(id: number, userId: number): Promise<Document> {
    const doc = await this.dataSource.getRepository(Document).findOne({
      where: { id },
      relations: ['role_assignments'],
    });
    if (!doc) throw new NotFoundException(`Document ${id} not found`);

    if (!(await this.canUserAccessDoc(userId, doc))) {
      throw new ForbiddenException('You do not have access to this document');
    }
    return doc;
  }

  // ═══════════════════════════════════════════════════════════════════
  //   OTP FLOW
  // ═══════════════════════════════════════════════════════════════════

  async requestOtp(
    documentId: number,
    userId: number,
    ctx: RequestContext,
  ): Promise<{ ok: true; expires_in_minutes: number }> {
    const doc = await this.dataSource
      .getRepository(Document)
      .findOne({ where: { id: documentId } });
    if (!doc) throw new NotFoundException(`Document ${documentId} not found`);

    this.assertNotExpired(doc);

    if (!(await this.canUserAccessDoc(userId, doc))) {
      await this.log(documentId, userId, DocumentAction.DENIED, ctx);
      throw new ForbiddenException('You do not have access to this document');
    }

    if (!doc.require_otp) {
      // OTP wasn't required — still let the caller proceed straight to unlock.
      return { ok: true, expires_in_minutes: 0 };
    }

    const user = await this.getUserSnapshot(userId);

    // 6-digit OTP, cryptographically random.
    const otp = String(crypto.randomInt(0, 1_000_000)).padStart(6, '0');
    const otp_hash = await bcrypt.hash(otp, 10);
    const expires_at = new Date(Date.now() + OTP_TTL_MINUTES * 60 * 1000);

    // Invalidate any prior live OTPs for this user+doc and insert a fresh row.
    await this.dataSource.transaction(async (manager) => {
      await manager.update(
        DocumentOtp,
        { document_id: documentId, user_id: userId, consumed: 0 },
        { consumed: 1 },
      );
      await manager.save(DocumentOtp, {
        document_id: documentId,
        user_id: userId,
        otp_hash,
        expires_at,
        consumed: 0,
        attempts: 0,
      });
    });

    // Send the email — do not await inside the transaction.
    const { subject, html } = buildDocumentOtpEmail({
      recipient_name: user.name,
      document_title: doc.title,
      document_category: doc.category,
      otp,
      expires_in_minutes: OTP_TTL_MINUTES,
      requester_ip: ctx.ip ?? null,
    });

    try {
     await this.mailsService.sendCustom(user.email, subject, html);
    } catch (err: any) {
      this.logger.error(
        `Failed to send OTP email for doc=${documentId} user=${userId}: ${err.message}`,
      );
      // Don't leak SMTP internals to the client.
      throw new BadRequestException(
        'Could not send the OTP email. Please try again in a moment.',
      );
    }

    await this.log(documentId, userId, DocumentAction.OTP_SENT, ctx);

    return { ok: true, expires_in_minutes: OTP_TTL_MINUTES };
  }

  /**
   * Verifies password + OTP (as required by the document), then hands out
   * a short-lived access token the frontend uses on /view and /download.
   */
  async unlock(
    documentId: number,
    dto: UnlockDocumentDto,
    userId: number,
    ctx: RequestContext,
  ): Promise<{
    token: string;
    view_url: string;
    download_url: string | null;
    expires_in_minutes: number;
  }> {
    const doc = await this.dataSource
      .getRepository(Document)
      .findOne({ where: { id: documentId } });
    if (!doc) throw new NotFoundException(`Document ${documentId} not found`);

    this.assertNotExpired(doc);

    if (!(await this.canUserAccessDoc(userId, doc))) {
      await this.log(documentId, userId, DocumentAction.DENIED, ctx);
      throw new ForbiddenException('You do not have access to this document');
    }

    // ─── Password check ──────────────────────────────────────────────
    if (doc.password_hash) {
      if (!dto.password) {
        throw new UnauthorizedException('Password is required');
      }
      const ok = await bcrypt.compare(dto.password, doc.password_hash);
      if (!ok) {
        await this.log(
          documentId,
          userId,
          DocumentAction.PASSWORD_FAILED,
          ctx,
        );
        throw new UnauthorizedException('Incorrect password');
      }
    }

    // ─── OTP check ──────────────────────────────────────────────────
    if (doc.require_otp) {
      if (!dto.otp) {
        throw new UnauthorizedException('OTP is required');
      }

      const otpRow = await this.dataSource.getRepository(DocumentOtp).findOne({
        where: { document_id: documentId, user_id: userId, consumed: 0 },
        order: { created_at: 'DESC' },
      });

      if (!otpRow) {
        await this.log(documentId, userId, DocumentAction.OTP_FAILED, ctx);
        throw new UnauthorizedException(
          'No active OTP — please request a new code',
        );
      }
      if (otpRow.expires_at.getTime() < Date.now()) {
        await this.log(documentId, userId, DocumentAction.OTP_FAILED, ctx);
        throw new UnauthorizedException('OTP has expired — request a new code');
      }
      if (otpRow.attempts >= OTP_MAX_ATTEMPTS) {
        // Lock this OTP so brute force doesn't get anywhere.
        otpRow.consumed = 1;
        await this.dataSource.getRepository(DocumentOtp).save(otpRow);
        await this.log(documentId, userId, DocumentAction.OTP_FAILED, ctx);
        throw new UnauthorizedException(
          'Too many failed attempts — request a new code',
        );
      }

      const ok = await bcrypt.compare(dto.otp, otpRow.otp_hash);
      if (!ok) {
        otpRow.attempts += 1;
        await this.dataSource.getRepository(DocumentOtp).save(otpRow);
        await this.log(documentId, userId, DocumentAction.OTP_FAILED, ctx);
        throw new UnauthorizedException('Incorrect OTP');
      }

      // Consume the OTP so it can't be reused.
      otpRow.consumed = 1;
      await this.dataSource.getRepository(DocumentOtp).save(otpRow);
    }

    // ─── Issue an access token ──────────────────────────────────────
    const token = crypto.randomBytes(24).toString('hex');
    ACCESS_TOKENS.set(token, {
      document_id: documentId,
      user_id: userId,
      expires_at: Date.now() + ACCESS_TOKEN_TTL_MINUTES * 60 * 1000,
      downloaded: false,
    });

    return {
      token,
      view_url: `/documents/${documentId}/view?token=${token}`,
      download_url: doc.allow_download
        ? `/documents/${documentId}/download?token=${token}`
        : null,
      expires_in_minutes: ACCESS_TOKEN_TTL_MINUTES,
    };
  }

  // ═══════════════════════════════════════════════════════════════════
  //   VIEW / DOWNLOAD — token-gated file streaming
  // ═══════════════════════════════════════════════════════════════════

  /**
   * Returns the resolved absolute file path + document row for the
   * controller to stream. Verifies the access token and writes the
   * audit-log entry.
   */
  async openForServe(
    documentId: number,
    token: string,
    mode: 'view' | 'download',
    userId: number,
    ctx: RequestContext,
  ): Promise<{ absPath: string; document: Document }> {
    const payload = ACCESS_TOKENS.get(token);
    if (!payload) {
      await this.log(documentId, userId, DocumentAction.DENIED, ctx);
      throw new UnauthorizedException('Invalid or expired access token');
    }
    if (payload.expires_at < Date.now()) {
      ACCESS_TOKENS.delete(token);
      await this.log(documentId, userId, DocumentAction.DENIED, ctx);
      throw new UnauthorizedException('Access token has expired');
    }
    if (
      payload.document_id !== documentId ||
      payload.user_id !== userId
    ) {
      await this.log(documentId, userId, DocumentAction.DENIED, ctx);
      throw new ForbiddenException('Token does not match this request');
    }

    const doc = await this.dataSource
      .getRepository(Document)
      .findOne({ where: { id: documentId } });
    if (!doc) throw new NotFoundException(`Document ${documentId} not found`);

    this.assertNotExpired(doc);

    if (mode === 'download') {
      if (!doc.allow_download) {
        await this.log(documentId, userId, DocumentAction.DENIED, ctx);
        throw new ForbiddenException('This document is view-only');
      }
      payload.downloaded = true;
    }

    // Resolve the file on disk.
    const absPath = path.isAbsolute(doc.file_path)
      ? doc.file_path
      : path.join(process.cwd(), doc.file_path);

    if (!fs.existsSync(absPath)) {
      this.logger.error(
        `Document ${doc.id} file missing on disk: ${absPath}`,
      );
      throw new NotFoundException('File is missing on disk');
    }

    await this.log(
      documentId,
      userId,
      mode === 'download' ? DocumentAction.DOWNLOADED : DocumentAction.VIEWED,
      ctx,
    );

    return { absPath, document: doc };
  }

  // ═══════════════════════════════════════════════════════════════════
  //   AUDIT TRAIL
  // ═══════════════════════════════════════════════════════════════════

  async getAccessLog(
    documentId: number,
    q: ListAccessLogQueryDto,
    userId: number,
  ) {
    if (!(await this.isAdmin(userId))) {
      throw new ForbiddenException('Only admin can view the audit trail');
    }

    const doc = await this.dataSource
      .getRepository(Document)
      .findOne({ where: { id: documentId } });
    if (!doc) throw new NotFoundException(`Document ${documentId} not found`);

    const page = q.page ?? 1;
    const limit = q.limit ?? 50;
    const offset = (page - 1) * limit;

    const qb = this.dataSource
      .getRepository(DocumentAccessLog)
      .createQueryBuilder('l')
      .where('l.document_id = :id', { id: documentId })
      .orderBy('l.created_at', 'DESC');

    if (q.user_id) qb.andWhere('l.user_id = :u', { u: q.user_id });
    if (q.action) qb.andWhere('l.action = :a', { a: q.action });
    if (q.date_from) qb.andWhere('l.created_at >= :df', { df: q.date_from });
    if (q.date_to) qb.andWhere('l.created_at < DATE_ADD(:dt, INTERVAL 1 DAY)', { dt: q.date_to });

    const [data, total] = await qb.skip(offset).take(limit).getManyAndCount();

    return {
      data,
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    };
  }

  async exportAccessLogCsv(
    documentId: number,
    userId: number,
  ): Promise<string> {
    if (!(await this.isAdmin(userId))) {
      throw new ForbiddenException('Only admin can export the audit trail');
    }

    const rows: DocumentAccessLog[] = await this.dataSource
      .getRepository(DocumentAccessLog)
      .find({
        where: { document_id: documentId },
        order: { created_at: 'DESC' },
      });

    const header = [
      'timestamp',
      'user_id',
      'user_name',
      'role',
      'action',
      'ip',
      'user_agent',
    ].join(',');

    const escape = (v: any) => {
      if (v === null || v === undefined) return '';
      const s = String(v).replace(/"/g, '""');
      return /[",\n]/.test(s) ? `"${s}"` : s;
    };

    const body = rows
      .map((r) =>
        [
          r.created_at.toISOString(),
          r.user_id,
          r.user_name,
          r.role_name,
          r.action,
          r.ip_address,
          r.user_agent,
        ]
          .map(escape)
          .join(','),
      )
      .join('\n');

    return header + '\n' + body + '\n';
  }

  async getAnalytics(userId: number) {
    if (!(await this.isAdmin(userId))) {
      throw new ForbiddenException('Only admin can view analytics');
    }

    const [{ total_docs, active_docs }] = await this.dataSource.query(
      `SELECT
         COUNT(*)                                     AS total_docs,
         SUM(status = 'active')                       AS active_docs
       FROM documents`,
    );

    const [{ total_opens, failed_attempts }] = await this.dataSource.query(
      `SELECT
         SUM(action IN ('viewed','downloaded'))                    AS total_opens,
         SUM(action IN ('otp_failed','password_failed','denied'))  AS failed_attempts
       FROM document_access_log`,
    );

    return {
      total_docs: Number(total_docs ?? 0),
      active_docs: Number(active_docs ?? 0),
      total_opens: Number(total_opens ?? 0),
      failed_attempts: Number(failed_attempts ?? 0),
    };
  }

  // ═══════════════════════════════════════════════════════════════════
  //   INTERNAL HELPERS
  // ═══════════════════════════════════════════════════════════════════

  private async canUserAccessDoc(
    userId: number,
    doc: Document,
  ): Promise<boolean> {
    if (await this.isAdmin(userId)) return true;
    // Uploader keeps access to their own doc even if not admin.
    if (doc.uploaded_by === userId) return true;

    const roles = await this.getUserRoles(userId);
    if (roles.length === 0) return false;

    const roleIds = roles.map((r) => r.role_id);
    const rows: { c: number }[] = await this.dataSource.query(
      `SELECT COUNT(*) AS c
       FROM document_role_assignments
       WHERE document_id = ? AND role_id IN (?)`,
      [doc.id, roleIds],
    );
    return Number(rows[0]?.c ?? 0) > 0;
  }

  private assertNotExpired(doc: Document): void {
    if (doc.status === DocumentStatus.ARCHIVED) {
      throw new GoneException('This document has been archived');
    }
    if (doc.expiry_date) {
      const today = new Date().toISOString().slice(0, 10);
      if (doc.expiry_date < today) {
        throw new GoneException('This document has expired');
      }
    }
  }

  private async log(
    documentId: number,
    userId: number,
    action: DocumentAction,
    ctx: RequestContext,
  ): Promise<void> {
    try {
      // Best-effort snapshot — don't let a bad user lookup break the log entry.
      let user_name: string | null = null;
      let role_name: string | null = null;
      try {
        const snap = await this.getUserSnapshot(userId);
        user_name = snap.name;
        role_name = snap.role_name;
      } catch {
        /* ignored — user might have been deleted */
      }

      await this.dataSource.getRepository(DocumentAccessLog).save({
        document_id: documentId,
        user_id: userId,
        user_name,
        role_name,
        action,
        ip_address: ctx.ip ?? null,
        user_agent: (ctx.user_agent ?? '').slice(0, 300) || null,
      });
    } catch (err: any) {
      // Never let logging failure break the user's action.
      this.logger.error(
        `Failed to write access log for doc=${documentId} user=${userId} action=${action}: ${err.message}`,
      );
    }
  }

  private safeUnlink(p: string): void {
    try {
      if (p && fs.existsSync(p)) fs.unlinkSync(p);
    } catch {
      /* ignore */
    }
  }
}