import { Injectable } from '@nestjs/common';
import * as ADODB from 'node-adodb';

@Injectable()
export class AbsenService {
  
  async getAbsenData(mdbPath: string, lokasi: string, month: number, year: number, page: number, limit: number, offset: number) {
    // node-adodb defaults to 32-bit cscript. If you need 64-bit, pass true as the second argument:
    // ADODB.open('Provider=...', true)
    
    // Using Microsoft.Jet.OLEDB.4.0 for standard 32-bit Access drivers (most common for att2000.mdb)
    // If it's a 64-bit engine, change Provider to Microsoft.ACE.OLEDB.12.0 and pass `true`
    const connectionString = `Provider=Microsoft.Jet.OLEDB.4.0;Data Source=${mdbPath};Persist Security Info=False;`;
    const openFn = (ADODB as any).open || (ADODB as any).default?.open;
    if (!openFn) throw new Error("Could not resolve ADODB.open function");
    const connection = openFn(connectionString);

    const response = {
      status: 'success',
      lokasi,
      periode: `${year}-${String(month).padStart(2, '0')}`,
      pagination: {
        page,
        limit,
        has_more: false
      },
      data: {
        userinfo: [] as any[],
        checkinout: [] as any[]
      }
    };

    // 1. Get USERINFO only on page 1
    if (page === 1) {
      try {
        const users = (await connection.query('SELECT USERID, Badgenumber, Name FROM USERINFO')) as any[];
        response.data.userinfo = users.map((u: any) => ({
          USERID: u.USERID,
          Badgenumber: u.Badgenumber ? String(u.Badgenumber).trim() : '',
          Name: u.Name ? String(u.Name).trim() : ''
        }));
      } catch (err) {
        console.warn("Failed to read USERINFO", err);
      }
    }

    // 2. Get CHECKINOUT
    // Format dates for Access SQL: #YYYY-MM-DD HH:MM:SS#
    const startDate = `${year}-${String(month).padStart(2, '0')}-01 00:00:00`;
    // Get last day of month
    const daysInMonth = new Date(year, month, 0).getDate();
    const endDate = `${year}-${String(month).padStart(2, '0')}-${String(daysInMonth).padStart(2, '0')} 23:59:59`;

    try {
      // For large databases, doing LIMIT/OFFSET in Access SQL is hard.
      // node-adodb doesn't stream. 
      // If we query the whole month, it might be large. But let's fetch the month and slice in JS.
      // Alternatively, we can use a subquery approach, but standard Access SQL for pagination is messy.
      
      const sqlScan = `SELECT USERID, CHECKTIME FROM CHECKINOUT WHERE CHECKTIME >= #${startDate}# AND CHECKTIME <= #${endDate}# ORDER BY CHECKTIME ASC`;
      const allScans = (await connection.query(sqlScan)) as any[];
      
      // Paginate in memory since node-adodb brings it all to node
      const sliced = allScans.slice(offset, offset + limit);
      response.data.checkinout = sliced.map((c: any) => {
        // Access returns date objects or strings. Ensure it's formatted nicely if needed, 
        // or just let JSON.stringify handle it.
        // Node-adodb returns dates in a specific format or ISO string.
        let checktime = c.CHECKTIME;
        
        // node-adodb secara otomatis mengubah waktu lokal MDB menjadi UTC dan mengembalikan string berakhiran Z
        // Contoh: MDB berisi "05:58:04" lokal -> node-adodb mengirim "22:58:04Z"
        // Kita HARUS mem-parsing string ini ke dalam Date object JavaScript, lalu memanggil getHours() 
        // agar NodeJS secara otomatis menambahkan +7 jam kembali sesuai zona waktu server lokal (WIB).
        if (typeof checktime === 'string') {
          checktime = new Date(checktime);
        }
        
        if (checktime instanceof Date && !isNaN(checktime.getTime())) {
          const yyyy = checktime.getFullYear();
          const mm = String(checktime.getMonth() + 1).padStart(2, '0');
          const dd = String(checktime.getDate()).padStart(2, '0');
          const hh = String(checktime.getHours()).padStart(2, '0');
          const min = String(checktime.getMinutes()).padStart(2, '0');
          const ss = String(checktime.getSeconds()).padStart(2, '0');
          checktime = `${yyyy}-${mm}-${dd} ${hh}:${min}:${ss}`;
        }

        return {
          USERID: c.USERID,
          CHECKTIME: checktime
        };
      });

      if (offset + limit < allScans.length) {
        response.pagination.has_more = true;
      }
      
    } catch (err) {
      console.warn("Failed to read CHECKINOUT", err);
      throw err;
    }

    return response;
  }
}