<?php

namespace App\Exports;

use App\Models\ConfServerLicense;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithMapping;

class ServerLicenseExport implements FromCollection, WithHeadings, WithColumnFormatting, WithMapping

{  public function collection()
    {
        $select = "
            SELECT c.`SNKEY` as 'SNKEY', c.`COMPANY`, c.`STATUS`, c.`LICENSEDATE`, c.`BACKUP_DATE`,
                   c.`CUR_VERSION`, c.`INSTALL_DATE`, c.`INSTALL_VERSION`, c.`DATABASETYPE`, c.`OWNERTYPE`,
                   c.`NBTEAM`, c.`CUSTOMERSIZE`, c.`CUSTOMERTYPE`, c.`CUSTOMERBRANCH`, c.`CONTACTCUSTOMERNAME`,
                   c.`CONTACT`, c.`CUSTOMERURL`, c.`MESSAGETYPE`, c.`PHP_VERSION_ID`, j.`javaversion` as 'JAVA_VERSION'
            FROM conf_server_license c
            LEFT JOIN conf_server_license_javaversion j ON c.SNKEY = j.SNKEY
        ";

        return collect(DB::select($select));
    }

    public function headings(): array
    {
        return [
            'SNKEY', 'COMPANY', 'STATUS', 'LICENSEDATE', 'BACKUP_DATE', 'CUR_VERSION', 'INSTALL_DATE', 'INSTALL_VERSION',
            'DATABASETYPE', 'OWNERTYPE', 'NBTEAM', 'CUSTOMERSIZE', 'CUSTOMERTYPE', 'CUSTOMERBRANCH', 'CONTACTCUSTOMERNAME',
            'CONTACT', 'CUSTOMERURL', 'MESSAGETYPE', 'PHP_VERSION_ID', 'JAVA VERSION'
        ];
    }

    public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_TEXT,
        ];
    }
    public function map($row): array
    {
        return [
            '`' . $row->SNKEY, // Add a backtick (`) to enforce text format in Excel
            $row->COMPANY,
            $row->STATUS,
            $row->LICENSEDATE,
            $row->BACKUP_DATE,
            $row->CUR_VERSION,
            $row->INSTALL_DATE,
            $row->INSTALL_VERSION,
            $row->DATABASETYPE,
            $row->OWNERTYPE,
            $row->NBTEAM,
            $row->CUSTOMERSIZE,
            $row->CUSTOMERTYPE,
            $row->CUSTOMERBRANCH,
            $row->CONTACTCUSTOMERNAME,
            $row->CONTACT,
            $row->CUSTOMERURL,
            $row->MESSAGETYPE,
            $row->PHP_VERSION_ID,
            $row->JAVA_VERSION,
        ];
    }
 
}