// import.service.ts
import { Injectable } from '@nestjs/common';
import * as XLSX from 'xlsx';
import * as path from 'path';
import { DataSource } from 'typeorm';

@Injectable()
export class ImportService {
  constructor(private readonly dataSource: DataSource) {}

  async importFromLocal() {
    const filePath = path.join(process.cwd(), './uploads/demo.xlsx');
    const workbook = XLSX.readFile(filePath);

    const sheetName = 'Assign To Employee';
    const rows = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);

    // console.log(rows); // DEBUG

    const qr = this.dataSource.createQueryRunner();
    await qr.connect();
    await qr.startTransaction();

    try {
      const vendorRepo = qr.manager.getRepository('vendors');
      const userRepo = qr.manager.getRepository('users');
      const deviceRepo = qr.manager.getRepository('devices');
      const assignRepo = qr.manager.getRepository('device_assignments');

      const vendorMap = new Map();
      const userMap = new Map();
      const deviceMap = new Map();

      for (const row of rows as any[]) {
        /** ---------- VENDOR ---------- */
        let vendorId = vendorMap.get(row['Vendor']);

        if (!vendorId && row['Vendor']) {
          const vendor = await vendorRepo.save({
            name: row['Vendor'],
            contact_person: row['Vendor'],
            notes: row['Location'],
          });
          vendorId = vendor.id;
          vendorMap.set(row['Vendor'], vendorId);
        }

        /** ---------- USER ---------- */
        let userId = userMap.get(row['Emp Id']);

        if (!userId) {
          const user = await userRepo.save({
            employee_code: row['Emp Id'],
            first_name: row['Name'],
            email: row['Email'] || `${row['Emp Id']}@demo.com`,
            status: row['Employement Status'],
          });
          userId = user.id;
          userMap.set(row['Emp Id'], userId);
        }

        /** ---------- DEVICE ---------- */
        let deviceId = deviceMap.get(row['Asset Code']);

        if (!deviceId) {
          const device = await deviceRepo.save({
            asset_tag: row['Asset Code'],
            serial_number: row['Serial No '],
            // device_type: row['Device Type']?.toLowerCase(),
            // brand: row['Brand'],
            // specifications: { file: row['Allotment Form/DC'] },
            model: row['OS'],
            vendor_id: vendorId,
          });
          deviceId = device.id;
          deviceMap.set(row['Asset Code'], deviceId);
        }

        /** ---------- ASSIGNMENT ---------- */
        await assignRepo.save({
          device_id: deviceId,
          employee_id: userId,
          assigned_date:
            this.formatDate(row['Assigned Date']) ??
            this.formatDate(new Date()),
          remarks: row['Remarks'],
          status: 'active',
        });
      }

      await qr.commitTransaction();
      return { message: 'Import successful ✅' };
    } catch (error) {
      await qr.rollbackTransaction();
      console.error(error);
      throw error;
    } finally {
      await qr.release();
    }
  }

  private formatDate(value: any): string | null {
    if (!value) return null;

    // ✅ Case 1: Already Date object
    if (value instanceof Date) {
      return value.toISOString().split('T')[0];
    }

    // ✅ Case 2: Excel number (serial date)
    if (typeof value === 'number') {
      const parsed = XLSX.SSF.parse_date_code(value);
      if (!parsed) return null;

      const d = new Date(parsed.y, parsed.m - 1, parsed.d);
      return d.toISOString().split('T')[0];
    }

    // ✅ Case 3: String (24-Mar-2026)
    if (typeof value === 'string') {
      // already YYYY-MM-DD
      if (/^\d{4}-\d{2}-\d{2}$/.test(value)) return value;

      const [day, mon, year] = value.split('-');

      const months: any = {
        Jan: '01',
        Feb: '02',
        Mar: '03',
        Apr: '04',
        May: '05',
        Jun: '06',
        Jul: '07',
        Aug: '08',
        Sep: '09',
        Oct: '10',
        Nov: '11',
        Dec: '12',
      };

      if (!months[mon]) return null;

      return `${year}-${months[mon]}-${day.padStart(2, '0')}`;
    }

    return null;
  }
}
