import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, Between } from 'typeorm';
import { LeaveRequest, LeaveStatus } from '../leaves/entities/leave-request.entity';
import { LeaveBalance } from '../leaves/entities/leave-balance.entity';
import { User } from '../users/entities/user.entity';
import { ReportFiltersDto } from './dto/report-filters.dto';
import * as ExcelJS from 'exceljs';
import PDFDocument from 'pdfkit';
import dayjs from 'dayjs';
import 'dayjs/locale/fr';

dayjs.locale('fr');

@Injectable()
export class ReportsService {
  constructor(
    @InjectRepository(LeaveRequest)
    private readonly leaveRequestRepo: Repository<LeaveRequest>,

    @InjectRepository(LeaveBalance)
    private readonly leaveBalanceRepo: Repository<LeaveBalance>,

    @InjectRepository(User)
    private readonly userRepo: Repository<User>,
  ) {}

  // ─── Export Excel — Congés ───────────────────────────────────────────────────
  async exportLeavesExcel(filters: ReportFiltersDto): Promise<ExcelJS.Buffer> {
    const requests = await this.getLeaveRequests(filters);
    const workbook = new ExcelJS.Workbook();
    const sheet    = workbook.addWorksheet('Congés');

    // En-têtes
    sheet.columns = [
      { header: 'Matricule',   key: 'matricule',   width: 12 },
      { header: 'Nom',         key: 'last_name',   width: 18 },
      { header: 'Prénom',      key: 'first_name',  width: 18 },
      { header: 'Équipe',      key: 'team',        width: 20 },
      { header: 'Type',        key: 'leave_type',  width: 20 },
      { header: 'Début',       key: 'start_date',  width: 14 },
      { header: 'Fin',         key: 'end_date',    width: 14 },
      { header: 'Jours',       key: 'days_count',  width: 8  },
      { header: 'Statut',      key: 'status',      width: 16 },
      { header: 'Motif',       key: 'reason',      width: 30 },
      { header: 'Soumis le',   key: 'created_at',  width: 16 },
    ];

    // Style en-têtes
    sheet.getRow(1).eachCell(cell => {
      cell.style = {
        fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF1E88E5' } },
        font: { bold: true, color: { argb: 'FFFFFFFF' }, size: 11 },
        alignment: { horizontal: 'center' },
      };
    });
    sheet.getRow(1).height = 25;

    // Données
    const statusLabels: Record<string, string> = {
      pending_n1: 'En attente N1',
      pending_n2: 'En attente N2',
      pending_n3: 'En attente N3',
      approved:   'Approuvée',
      rejected:   'Rejetée',
      cancelled:  'Annulée',
    };

    requests.forEach((r, idx) => {
      const row = sheet.addRow({
        matricule:  r.user.matricule,
        last_name:  r.user.last_name,
        first_name: r.user.first_name,
        team:       r.user.team?.name ?? '—',
        leave_type: r.leave_type.label,
        start_date: dayjs(r.start_date).format('DD/MM/YYYY'),
        end_date:   dayjs(r.end_date).format('DD/MM/YYYY'),
        days_count: r.days_count,
        status:     statusLabels[r.status] ?? r.status,
        reason:     r.reason ?? '—',
        created_at: dayjs(r.created_at).format('DD/MM/YYYY'),
      });

      // Couleur alternée
      if (idx % 2 === 0) {
        row.eachCell(cell => {
          cell.style = {
            ...cell.style,
            fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFF8FAFC' } },
          };
        });
      }

      // Couleur statut
      const statusCell  = row.getCell('status');
      const statusColor = r.status === 'approved'  ? 'FF22C55E' :
                          r.status === 'rejected'  ? 'FFE53935' :
                          r.status === 'cancelled' ? 'FF94A3B8' : 'FFFB8C00';
      statusCell.style = {
        ...statusCell.style,
        font: { color: { argb: statusColor }, bold: true },
      };
    });

    // Totaux
    sheet.addRow([]);
    const totalRow = sheet.addRow({
      matricule:  `Total : ${requests.length} demande(s)`,
      days_count: requests.reduce((sum, r) => sum + +r.days_count, 0),
    });
    totalRow.getCell('matricule').style = { font: { bold: true } };
    totalRow.getCell('days_count').style = { font: { bold: true } };

    return workbook.xlsx.writeBuffer();
  }

  // ─── Export Excel — Soldes ───────────────────────────────────────────────────
  async exportBalancesExcel(filters: ReportFiltersDto): Promise<ExcelJS.Buffer> {
    const year     = filters.start_date
      ? dayjs(filters.start_date).year()
      : dayjs().year();

    const query = this.leaveBalanceRepo
      .createQueryBuilder('b')
      .leftJoinAndSelect('b.user', 'user')
      .leftJoinAndSelect('user.team', 'team')
      .leftJoinAndSelect('b.leave_type', 'leave_type')
      .where('b.year = :year', { year })
      .andWhere('user.is_active = true');

    if (filters.team_id) {
      query.andWhere('team.id = :teamId', { teamId: filters.team_id });
    }
    if (filters.user_id) {
      query.andWhere('user.id = :userId', { userId: filters.user_id });
    }

    const balances = await query
      .orderBy('team.name', 'ASC')
      .addOrderBy('user.last_name', 'ASC')
      .getMany();

    const workbook = new ExcelJS.Workbook();
    const sheet    = workbook.addWorksheet(`Soldes ${year}`);

    sheet.columns = [
      { header: 'Matricule',  key: 'matricule',  width: 12 },
      { header: 'Nom',        key: 'last_name',  width: 18 },
      { header: 'Prénom',     key: 'first_name', width: 18 },
      { header: 'Équipe',     key: 'team',       width: 20 },
      { header: 'Type congé', key: 'type',       width: 20 },
      { header: 'Acquis',     key: 'acquired',   width: 10 },
      { header: 'Pris',       key: 'taken',      width: 10 },
      { header: 'Ajusté',     key: 'adjusted',   width: 10 },
      { header: 'Restant',    key: 'remaining',  width: 10 },
    ];

    sheet.getRow(1).eachCell(cell => {
      cell.style = {
        fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF1E88E5' } },
        font: { bold: true, color: { argb: 'FFFFFFFF' } },
        alignment: { horizontal: 'center' },
      };
    });
    sheet.getRow(1).height = 25;

    balances.forEach((b, idx) => {
      const remaining = +b.acquired - +b.taken + +b.adjusted;
      const row = sheet.addRow({
        matricule:  b.user.matricule,
        last_name:  b.user.last_name,
        first_name: b.user.first_name,
        team:       b.user.team?.name ?? '—',
        type:       b.leave_type.label,
        acquired:   +b.acquired,
        taken:      +b.taken,
        adjusted:   +b.adjusted,
        remaining,
      });

      if (idx % 2 === 0) {
        row.eachCell(cell => {
          cell.style = {
            ...cell.style,
            fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFF8FAFC' } },
          };
        });
      }

      // Alerte solde critique
      if (remaining <= 2 && remaining >= 0) {
        row.getCell('remaining').style = {
          font: { color: { argb: 'FFEF4444' }, bold: true },
        };
      }
    });

    return workbook.xlsx.writeBuffer();
  }

  // ─── Export CSV — Congés ─────────────────────────────────────────────────────
  async exportLeavesCSV(filters: ReportFiltersDto): Promise<string> {
    const requests = await this.getLeaveRequests(filters);

    const headers = [
      'Matricule', 'Nom', 'Prénom', 'Équipe', 'Type',
      'Début', 'Fin', 'Jours', 'Statut', 'Motif', 'Soumis le'
    ];

    const rows = requests.map(r => [
      r.user.matricule,
      r.user.last_name,
      r.user.first_name,
      r.user.team?.name ?? '',
      r.leave_type.label,
      dayjs(r.start_date).format('DD/MM/YYYY'),
      dayjs(r.end_date).format('DD/MM/YYYY'),
      r.days_count,
      r.status,
      r.reason?.replace(/,/g, ';') ?? '',
      dayjs(r.created_at).format('DD/MM/YYYY'),
    ]);

    const csvContent = [headers, ...rows]
      .map(row => row.join(','))
      .join('\n');

    // BOM UTF-8 pour Excel
    return '\uFEFF' + csvContent;
  }

  // ─── Export PDF — Résumé RH ──────────────────────────────────────────────────
  async exportSummaryPDF(filters: ReportFiltersDto): Promise<Buffer> {
    const requests  = await this.getLeaveRequests(filters);
    const users     = await this.userRepo.find({
      where: { is_active: true },
      relations: ['team'],
    });

    const year = filters.start_date
      ? dayjs(filters.start_date).year()
      : dayjs().year();

    return new Promise((resolve, reject) => {
      const doc    = new PDFDocument({ margin: 40, size: 'A4' });
      const chunks: Buffer[] = [];

      doc.on('data',  chunk => chunks.push(chunk));
      doc.on('end',   ()    => resolve(Buffer.concat(chunks)));
      doc.on('error', err   => reject(err));

      // ── Titre ────────────────────────────────────────────────────────────────
      doc.fontSize(20).fillColor('#1E3A5F')
        .text('Résumé RH — G Fly', { align: 'center' });

      doc.fontSize(11).fillColor('#64748B')
        .text(
          `Rapport généré le ${dayjs().format('DD/MM/YYYY à HH:mm')}`,
          { align: 'center' }
        );

      if (filters.start_date || filters.end_date) {
        doc.fontSize(10).fillColor('#64748B')
          .text(
            `Période : ${filters.start_date ? dayjs(filters.start_date).format('DD/MM/YYYY') : '—'} → ${filters.end_date ? dayjs(filters.end_date).format('DD/MM/YYYY') : '—'}`,
            { align: 'center' }
          );
      }

      doc.moveDown(1.5);

      // ── KPIs ─────────────────────────────────────────────────────────────────
      doc.fontSize(14).fillColor('#1E3A5F').text('Vue d\'ensemble', { underline: true });
      doc.moveDown(0.5);

      const approved  = requests.filter(r => r.status === LeaveStatus.APPROVED).length;
      const rejected  = requests.filter(r => r.status === LeaveStatus.REJECTED).length;
      const pending   = requests.filter(r => [
        LeaveStatus.PENDING_N1,
        LeaveStatus.PENDING_N2,
        LeaveStatus.PENDING_N3,
      ].includes(r.status)).length;
      const totalDays = requests
        .filter(r => r.status === LeaveStatus.APPROVED)
        .reduce((sum, r) => sum + +r.days_count, 0);

      const kpis = [
        { label: 'Total collaborateurs actifs', value: users.length },
        { label: 'Demandes de congés',          value: requests.length },
        { label: 'Approuvées',                  value: approved },
        { label: 'Rejetées',                    value: rejected },
        { label: 'En attente',                  value: pending },
        { label: 'Total jours approuvés',        value: `${totalDays}j` },
      ];

      kpis.forEach(kpi => {
        doc.fontSize(11).fillColor('#374151')
          .text(`${kpi.label} : `, { continued: true })
          .fillColor('#1E88E5').text(String(kpi.value));
      });

      doc.moveDown(1.5);

      // ── Par type de congé ─────────────────────────────────────────────────────
      doc.fontSize(14).fillColor('#1E3A5F').text('Par type de congé', { underline: true });
      doc.moveDown(0.5);

      const byType = requests.reduce<Record<string, number>>((acc, r) => {
        const label = r.leave_type.label;
        acc[label]  = (acc[label] ?? 0) + 1;
        return acc;
      }, {});

      Object.entries(byType).forEach(([type, count]) => {
        doc.fontSize(10).fillColor('#374151')
          .text(`• ${type} : ${count} demande(s)`);
      });

      doc.moveDown(1.5);

      // ── Par équipe ────────────────────────────────────────────────────────────
      doc.fontSize(14).fillColor('#1E3A5F').text('Par équipe', { underline: true });
      doc.moveDown(0.5);

      const byTeam = requests.reduce<Record<string, number>>((acc, r) => {
        const team = r.user.team?.name ?? 'Sans équipe';
        acc[team]  = (acc[team] ?? 0) + 1;
        return acc;
      }, {});

      Object.entries(byTeam).forEach(([team, count]) => {
        doc.fontSize(10).fillColor('#374151')
          .text(`• ${team} : ${count} demande(s)`);
      });

      doc.moveDown(1.5);

      // ── Liste des demandes approuvées ─────────────────────────────────────────
      if (approved > 0) {
        doc.fontSize(14).fillColor('#1E3A5F')
          .text('Demandes approuvées', { underline: true });
        doc.moveDown(0.5);

        requests
          .filter(r => r.status === LeaveStatus.APPROVED)
          .slice(0, 20) // Max 20 pour éviter un PDF trop long
          .forEach(r => {
            doc.fontSize(9).fillColor('#374151').text(
              `${r.user.last_name} ${r.user.first_name} — ${r.leave_type.label} — ${dayjs(r.start_date).format('DD/MM')} → ${dayjs(r.end_date).format('DD/MM/YYYY')} (${r.days_count}j)`
            );
          });

        if (approved > 20) {
          doc.fontSize(9).fillColor('#94A3B8')
            .text(`... et ${approved - 20} autre(s) demande(s)`);
        }
      }

      // ── Pied de page ──────────────────────────────────────────────────────────
      doc.fontSize(8).fillColor('#94A3B8')
        .text(
          'G Fly — Document confidentiel — Usage interne uniquement',
          40,
          doc.page.height - 30,
          { align: 'center' }
        );

      doc.end();
    });
  }

  // ─── Helper — récupérer les demandes avec filtres ────────────────────────────
  private async getLeaveRequests(filters: ReportFiltersDto): Promise<LeaveRequest[]> {
    const query = this.leaveRequestRepo
      .createQueryBuilder('lr')
      .leftJoinAndSelect('lr.user', 'user')
      .leftJoinAndSelect('user.team', 'team')
      .leftJoinAndSelect('lr.leave_type', 'leave_type');

    if (filters.start_date && filters.end_date) {
      query.andWhere('lr.start_date >= :start', { start: filters.start_date })
           .andWhere('lr.end_date   <= :end',   { end:   filters.end_date   });
    }

    if (filters.team_id) {
      query.andWhere('team.id = :teamId', { teamId: filters.team_id });
    }

    if (filters.user_id) {
      query.andWhere('user.id = :userId', { userId: filters.user_id });
    }

    return query.orderBy('lr.created_at', 'DESC').getMany();
  }
}