import {
  Controller,
  Post,
  Get,
  Query,
  UploadedFile,
  UseInterceptors,
  ParseIntPipe,
  DefaultValuePipe,
  BadRequestException,
  Res,
  Body,
  Param,
  Patch,
  Delete,
} from '@nestjs/common';
import { FileInterceptor } from '@nestjs/platform-express';
import { diskStorage } from 'multer';
import * as fs from 'fs';
import * as path from 'path';
import type { Response } from 'express';
import { ExcelService } from './excel.service';
import { ExcelReportService } from './excel-report.service';
import type { CertReportQuery } from './excel-report.service';

// ─────────────────────────────────────────────────────────────────────────────
// Shared Multer storage — saves to ./uploads/ with sanitised filename
// ─────────────────────────────────────────────────────────────────────────────
const xlsxStorage = diskStorage({
  destination: (req, file, cb) => {
    const uploadPath = './uploads';
    if (!fs.existsSync(uploadPath)) fs.mkdirSync(uploadPath, { recursive: true });
    cb(null, uploadPath);
  },
  filename: (req, file, cb) => {
    const ext  = path.extname(file.originalname);
    const base = path.basename(file.originalname, ext).replace(/\s+/g, '_');
    cb(null, `${Date.now()}-${base}${ext}`);
  },
});

// ─────────────────────────────────────────────────────────────────────────────
// Shared file-type filter — only xlsx / xls / csv allowed
// ─────────────────────────────────────────────────────────────────────────────
const xlsxFilter = (req: any, file: Express.Multer.File, cb: any) => {
  const allowed = ['.xlsx', '.xls', '.csv'];
  const ext = path.extname(file.originalname).toLowerCase();
  if (!allowed.includes(ext)) {
    return cb(
      new BadRequestException(`Only ${allowed.join(', ')} files are allowed`),
      false,
    );
  }
  cb(null, true);
};

@Controller('excel')
export class ExcelController {
  constructor(
    private readonly excelService:       ExcelService,
    private readonly excelReportService: ExcelReportService,
  ) {}

  // ─────────────────────────────────────────
  // IMPORT EXCEL
  // POST /api/excel/import
  // ─────────────────────────────────────────
  @Post('import')
  @UseInterceptors(
    FileInterceptor('file', { storage: xlsxStorage, fileFilter: xlsxFilter }),
  )
  async importExcel(@UploadedFile() file: Express.Multer.File) {
    if (!file) throw new BadRequestException('No file uploaded');
    return this.excelService.importExcel(file.path);
  }

  // ─────────────────────────────────────────
  // ✅ NEW — MANUAL ADD a legacy certificate
  // POST /api/excel/manual-add
  // (must be BEFORE the @Get(':id') etc routes to avoid conflict)
  // ─────────────────────────────────────────
  @Post('manual-add')
  async addManualLegacy(
    @Body()
    dto: {
      cert_no: string;
      company_name: string;
      standard: string;
      orginally_reg?: string;
      issue_date?: string;
      expire_date?: string;
      status?: string;
    },
  ) {
    return this.excelService.addManualLegacy(dto);
  }

  // ─────────────────────────────────────────
  // GET ALL CERTIFICATES — paginated + sortable
  // GET /api/excel?page=1&limit=20&sort=id&order=DESC
  // ─────────────────────────────────────────
  @Get()
  async getAllCertificates(
    @Query('page',  new DefaultValuePipe(1),     ParseIntPipe) page:  number,
    @Query('limit', new DefaultValuePipe(20),    ParseIntPipe) limit: number,
    @Query('sort',  new DefaultValuePipe('id'))               sort:  string,
    @Query('order', new DefaultValuePipe('DESC'))             order: string,
  ) {
    return this.excelService.getAllCertificates({ page, limit, sort, order });
  }

  // ─────────────────────────────────────────
  // SEARCH — paginated + multi-filter
  // ─────────────────────────────────────────
  @Get('search')
  async searchCertificates(
    @Query('q')         q?:        string,
    @Query('standard')  standard?: string,
    @Query('status')    status?:   string,
    @Query('from_date') from_date?: string,
    @Query('to_date')   to_date?:  string,
    @Query('month')     monthStr?: string,
    @Query('year')      yearStr?:  string,
    @Query('page',  new DefaultValuePipe(1),  ParseIntPipe) page:  number = 1,
    @Query('limit', new DefaultValuePipe(20), ParseIntPipe) limit: number = 20,
  ) {
    const month = monthStr ? parseInt(monthStr, 10) : undefined;
    const year  = yearStr  ? parseInt(yearStr,  10) : undefined;
    return this.excelService.searchCertificates({
      q, standard, status, from_date, to_date, month, year, page, limit,
    });
  }

  // ─────────────────────────────────────────
  // STATS — totals by standard / status
  // GET /api/excel/stats
  // ─────────────────────────────────────────
  @Get('stats')
  async getStats() {
    return this.excelService.getStats();
  }

  // ─────────────────────────────────────────
  // IMPORT CODEBOOK
  // POST /api/excel/import-codebook
  // ─────────────────────────────────────────
  @Post('import-codebook')
  @UseInterceptors(
    FileInterceptor('file', { storage: xlsxStorage, fileFilter: xlsxFilter }),
  )
  async importCodebookExcel(@UploadedFile() file: Express.Multer.File) {
    if (!file) throw new BadRequestException('No file uploaded');
    return this.excelService.importCodebookExcel(file.path);
  }

  // ─────────────────────────────────────────
  // GET ALL CODEBOOKS — paginated + search
  // GET /api/excel/codebooks?page=1&limit=50&search=metal
  // ─────────────────────────────────────────
  @Get('codebooks')
  async getAllCodebooks(
    @Query('page',  new DefaultValuePipe(1),  ParseIntPipe) page:  number,
    @Query('limit', new DefaultValuePipe(50), ParseIntPipe) limit: number,
    @Query('search') search?: string,
  ) {
    return this.excelService.getAllCodebooks({ page, limit, search });
  }

  // ─────────────────────────────────────────
  // REPORT — JSON preview before download
  // ─────────────────────────────────────────
  @Get('report/preview')
  async previewReport(@Query() query: CertReportQuery) {
    return this.excelReportService.getPreview(query);
  }

  // ─────────────────────────────────────────
  // REPORT — Download Excel (.xlsx)
  // ─────────────────────────────────────────
  @Get('report/excel')
  async downloadExcel(
    @Query() query: CertReportQuery,
    @Res() res: Response,
  ) {
    const buffer = await this.excelReportService.generateExcel(query);
    const stamp  = new Date().toISOString().slice(0, 10);
    res.set({
      'Content-Type':
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'Content-Disposition': `attachment; filename="cert-report-${stamp}.xlsx"`,
      'Content-Length':      buffer.length,
    });
    res.end(buffer);
  }

  // ─────────────────────────────────────────
  // REPORT — Download PDF
  // ─────────────────────────────────────────
  @Get('report/pdf')
  async downloadPdf(
    @Query() query: CertReportQuery,
    @Res() res: Response,
  ) {
    const buffer = await this.excelReportService.generatePdf(query);
    const stamp  = new Date().toISOString().slice(0, 10);
    res.set({
      'Content-Type':        'application/pdf',
      'Content-Disposition': `attachment; filename="cert-report-${stamp}.pdf"`,
      'Content-Length':      buffer.length,
    });
    res.end(buffer);
  }

  // ─────────────────────────────────────────
  // ✅ NEW — UPDATE a legacy certificate
  // PATCH /api/excel/:id
  // ─────────────────────────────────────────
  @Patch(':id')
  async updateLegacy(
    @Param('id', ParseIntPipe) id: number,
    @Body()
    dto: Partial<{
      cert_no: string;
      company_name: string;
      standard: string;
      orginally_reg: string;
      issue_date: string;
      expire_date: string;
      status: string;
    }>,
  ) {
    return this.excelService.updateLegacy(id, dto);
  }

  // ─────────────────────────────────────────
  // ✅ NEW — DELETE a legacy certificate
  // DELETE /api/excel/:id
  // ─────────────────────────────────────────
  @Delete(':id')
  async deleteLegacy(@Param('id', ParseIntPipe) id: number) {
    return this.excelService.deleteLegacy(id);
  }

  // ─────────────────────────────────────────
  // ✅ NEW — GET single legacy by ID
  // GET /api/excel/:id
  // (must be LAST — :id pattern matches anything)
  // ─────────────────────────────────────────
  @Get(':id')
  async getLegacyById(@Param('id', ParseIntPipe) id: number) {
    return this.excelService.getLegacyById(id);
  }
}