<?php

namespace App\Exports;

use App\Bank\Payment;
use Barryvdh\DomPDF\Facade\Pdf as FacadePdf;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use PDF;

class SubAccountExport implements FromQuery, WithHeadings
{
    /**
     * @return \Illuminate\Support\Collection
     */
    use Exportable;

    protected $subAccounNumber;
    protected $startDate;
    protected $endDate;

    public function __construct($subAccounNumber, $startDate = null, $endDate = null)
    {
        $this->subAccounNumber = $subAccounNumber;
        $this->startDate = $startDate;
        $this->endDate = $endDate;
    }

    public function query()
    {
        // $query = Payment::query()
        //     ->join('imali_sub_accounts', 'imali_sub_accounts.card_number', 'payments.sender_card_number')
        //     ->join('users', 'users.id', 'payments.sender_id')
        //     ->join('stores', 'stores.id', 'payments.store_id')
        //     ->select(
        //         'users.name as user',
        //         'users.last_name as last_name',
        //         'imali_sub_accounts.main_account_number as main_account_number',
        //         'stores.name as store_name',
        //         'imali_sub_accounts.name as name',
        //         'imali_sub_accounts.account_number as account_number',
        //         'imali_sub_accounts.card_number as card_number',
        //         'transaction_id as TransactionID',
        //         DB::raw("DATE_FORMAT(payments.created_at, '%d-%m-%y %H:%i:%s') as Date"),
        //         DB::raw("IFNULL(sender_card_number, 'No associated card') as SubAccountNumber"),
        //         'sender_account_number as CardNumber',
        //         'transaction_name as TransactionType',
        //         'amount as Amount',
        //         DB::raw("CASE 
        //         WHEN transaction_name IN ('Deposito', 'Depósito') THEN new_balance 
        //         ELSE old_balance 
        //      END as OldBalance"),
        //         DB::raw("CASE 
        //         WHEN transaction_name IN ('Deposito', 'Depósito') THEN old_balance 
        //         ELSE new_balance 
        //      END as NewBalance"),
        //         'payments.status as Status'
        //     )
        //     ->where('sender_card_number', $this->subAccounNumber)
        //     ->orWhere('sender_account_number', $this->subAccounNumber);

        $query = Payment::query()
            ->join('imali_sub_accounts', 'imali_sub_accounts.card_number', 'payments.sender_card_number')
            ->join('users', 'users.id', 'payments.sender_id')
            ->join('stores', 'stores.id', 'payments.store_id')
            ->select(
                'users.name as user',
                'users.last_name as last_name',
                'imali_sub_accounts.main_account_number as main_account_number',
                'stores.name as store_name',
                'imali_sub_accounts.name as name',
                'imali_sub_accounts.account_number as account_number',
                'imali_sub_accounts.card_number as card_number',
                'transaction_id as TransactionID',
                DB::raw("DATE_FORMAT(payments.created_at, '%d-%m-%y %H:%i:%s') as Date"),
                DB::raw("IFNULL(sender_card_number, 'No associated card') as SubAccountNumber"),
                'sender_account_number as CardNumber',
                'transaction_name as TransactionType',
                'amount as Amount',
                DB::raw("CASE 
                WHEN transaction_name IN ('Deposito', 'Depósito') THEN imali_sub_accounts.balance - payments.amount
                ELSE old_balance 
             END as OldBalance"),
                DB::raw("CASE 
                WHEN transaction_name IN ('Deposito', 'Depósito') THEN imali_sub_accounts.balance 
                ELSE new_balance 
             END as NewBalance"),
                'payments.status as Status'
            )
            ->where('sender_card_number', $this->subAccounNumber)
            ->orWhere('sender_account_number', $this->subAccounNumber);

        // Intervalo de datas
        if ($this->startDate) {
            $query->where('payments.created_at', '>=', $this->startDate);
        }

        if ($this->endDate) {
            $query->where('payments.created_at', '<=', $this->endDate);
        }

        $query->orderBy('payments.created_at', 'desc');

        // return $query;
        return $query->get(); // Obter resultados da query
    }

    // Gerar ficjeito e deixar numa pasta reports e o Miguel vai acessar o ficheito ppor link
    public function exportPDFOLD()
    {
        $payments = $this->query(); // Obter dados da query

        // return response()->json([
        //     'status' => 'success',
        //     'status' => $payments
        // ]);

        // Gerar o PDF usando o template Blade
        $pdf = FacadePdf::loadView('pdf.subaccount-extract', ['payments' => $payments]);

        // Retornar o PDF para download
        return $pdf->download('subaccount-extract.pdf');
    }

    private function getYesterDayDate()
    {
        // return date('Y-m-d', strtotime(date('Y-m-d')) - 24 * 1200);
        return date('Y-m-d', strtotime(date('Y-m-d')));
    }

    public function exportPDF()
    {
        // return date('Hms');
        $payments = $this->query(); // Obter dados da query
        // return $payments;

        // Gerar o PDF
        $pdf = FacadePdf::loadView('pdf.subaccount-extract', ['payments' => $payments]);

        // Nome do arquivo
        $fileName = 'subaccount-extract-' . $this->subAccounNumber . '-' . $this->getYesterDayDate() . '-' . date('Hms') . '.pdf';

        // Caminho absoluto para pasta public/reports
        $folder = public_path('reports');

        // Criar pasta se não existir
        if (!file_exists($folder)) {
            mkdir($folder, 0755, true);
        }

        // Caminho completo do arquivo
        $filePath = "{$folder}/{$fileName}";

        // Salvar PDF
        $pdf->save($filePath);

        // if (file_exists($filePath)) {
        //     sleep(15);
        //     unlink($filePath);
        // }

        // URL pública (relativa ao domínio)
        $url = url("reports/{$fileName}");

        return response()->json([
            'status' => 'success',
            'message' => 'Extracto de subconta gerado com sucesso.',
            'url' => $url
        ]);
    }

    public function headings(): array
    {
        return [
            'TransactionID',
            'Date',
            'SubAccount Number',
            'Card Number',
            'Transaction Type',
            'Amount',
            'Old Balance',
            'New Balance',
            'Transaction Status'
        ];
    }
}