import { Injectable, Logger } from "@nestjs/common";
import { EntityManager } from "typeorm";
import { DocumentCreateDto, DocumentFilterDto, DocumentMovementListDto, DocumentPaymentDto, DocumentRowDto } from "../../dto/document.dto";
import ResponseDto from "../../dto/response.dto";
import DocumentEntity from "../../entity/document.entity";
import InvoiceCounterEntity from "../../entity/invoice_counter.entity";
import CreditNoteCounterEntity from "../../entity/credit_note.entity";
import DocumentRowEntity from "../../entity/document_row.entity";
import ArticleEntity from "../../entity/article.entity";
import MovementEntity from "../../entity/movement.entity";
import StorageEntity from "../../entity/storage.entity";
import StoreEntity from "../../entity/store.entity";
import DocumentCustomerEntity from "../../entity/document_customer.entity";
import CustomerEntity from "../../entity/customer.entity";
import SupplierEntity from "../../entity/supplier.entity";
import DocumentSupplierEntity from "../../entity/document_supplier.entity";
import DocumentStoreEntity from "../../entity/document_store.entity";
import DocumentStorageEntity from "../../entity/document_storage.entity";
import DeviceEntity from "../../entity/device.entity";
import DocumentDeviceEntity from "../../entity/document_device.entity";
import OperatorEntity from "../../entity/operator.entity";
import DocumentOperatorEntity from "../../entity/document_operator.entity";
import DocumentPaymentEntity from "../../entity/document_payment.entity";
import PaymentEntity from "../../entity/payment.entity";
import DocumentDiscountEntity from "../../entity/document_discount.entity";
import UserEntity from "../../entity/user.entity";
import DocumentUserEntity from "../../entity/document_user.entity";
import PriceEntity from "../../entity/price.entity";
import DocumentProgressiveEntity from "../../entity/document_progressive.entity";
import HelperProvider from "../helper/helper.provider";
import { throwError } from "rxjs";
import { threadId } from "node:worker_threads";
import InvoiceProvider from "../invoice/invoice.provider";

@Injectable()
export default class DocumentProvider {

    private logger: Logger = new Logger(DocumentProvider.name);

    constructor(private readonly entityManager: EntityManager, private helperProvider: HelperProvider, private readonly invoiceProvider: InvoiceProvider){}

    /**
     * Create new document
     * @param payload 
     */
    async create(payload: DocumentCreateDto): Promise<ResponseDto>{

        const response: ResponseDto = new ResponseDto();

        try{

            // Save debug document
            await this.entityManager.query(
                `INSERT INTO document_log_debug (payload) VALUES (?)`,
                [ 
                    JSON.stringify(payload)
                ]
            );

            let documentNumber: any;
            let idProgressive: any;
            let lastProgressive: any;
            let invoiceCounter: InvoiceCounterEntity | null       = null;
            let creditNoteCounter: CreditNoteCounterEntity | null = null;

            if(!payload.rows || (payload.rows.length == 0)){
                throw new Error("empty_rows");
            }

            let idDocument: any;
            await this.entityManager.transaction(async (txn) => {


                if(!payload.id){

                    // ________ If is invoice or credit note ________
                    switch(payload.documentArea){
                        case "invoice": {
                            if(!payload.idInvoiceCounter){
                                throw new Error("no_invoice_counter");
                            }
                            invoiceCounter = 
                                await txn.findOneOrFail(
                                    InvoiceCounterEntity,
                                    {
                                        where: {
                                            active: 1
                                        },
                                        lock: {
                                            mode: "pessimistic_write"
                                        }                                    
                                    }
                                );
                            documentNumber = `${(invoiceCounter.prefix ?? "").trim()}${invoiceCounter.currentNumber}${(invoiceCounter.suffix ?? "").trim()}`;
                        } break;
                        case "credit_note": {
                            if(!payload.idCreditNoteCounter){
                                throw new Error("no_credit_note_counter");
                            }
                            creditNoteCounter = 
                                await txn.findOneOrFail(
                                    CreditNoteCounterEntity,
                                    {
                                        where: {
                                            active: 1
                                        },
                                        lock: {
                                            mode: "pessimistic_write"
                                        }                                    
                                    }
                                );
                            documentNumber = `${(creditNoteCounter.prefix ?? "").trim()}${creditNoteCounter.currentNumber}${(creditNoteCounter.suffix ?? "").trim()}`;
                        } break;
                    }

                    // ________ Get progressive ________

                    const resultProgressive = 
                        await txn.query(
                            `SELECT id, progressive FROM document_progressive WHERE document_area = ? AND id_store = ? FOR UPDATE`,
                            [ payload.documentArea, payload.idStore ]
                        );
                    
                    if(resultProgressive.length == 0){

                        const documentProgressive: DocumentProgressiveEntity    = new DocumentProgressiveEntity();
                        documentProgressive.documentArea                        = payload.documentArea;
                        documentProgressive.progressive                         = 1;
                        documentProgressive.store                               = new StoreEntity(payload.idStore);

                        await txn.save(documentProgressive);

                        idProgressive = documentProgressive.id;
                        lastProgressive = 1;
                    }else{
                        idProgressive   = resultProgressive[0]['id'];
                        lastProgressive = resultProgressive[0]['progressive'];
                    }

                }else{

                    // Get data to update

                    const oldDocument = 
                        await txn.findOneOrFail(
                            DocumentEntity,
                            {
                                where: {
                                    id: payload.id
                                },
                                select: {
                                    documentNumber: true,
                                    documentProgressive: true
                                }
                            }
                        );
                    
                    // Reset old fields!
                    lastProgressive = oldDocument.documentProgressive;
                    documentNumber  = oldDocument.documentNumber;

                    // Clean related data
                    if(payload.id){
                        await txn.query(`DELETE FROM document_row WHERE id_document = ?`,           [payload.id]);
                        await txn.query(`DELETE FROM movement WHERE id_document = ?`,               [payload.id]);
                        await txn.query(`DELETE FROM document_customer WHERE id_document = ?`,      [payload.id]);
                        await txn.query(`DELETE FROM document_device WHERE id_document = ?`,        [payload.id]);
                        await txn.query(`DELETE FROM document_discount WHERE id_document = ?`,      [payload.id]);
                        await txn.query(`DELETE FROM document_operator WHERE id_document = ?`,      [payload.id]);
                        await txn.query(`DELETE FROM document_payment WHERE id_document = ?`,       [payload.id]);
                        await txn.query(`DELETE FROM document_storage WHERE id_document = ?`,       [payload.id]);
                        await txn.query(`DELETE FROM document_store WHERE id_document = ?`,         [payload.id]);
                        await txn.query(`DELETE FROM document_supplier WHERE id_document = ?`,      [payload.id]);
                        await txn.query(`DELETE FROM document_user WHERE id_document = ?`,          [payload.id]);
                    }

                }

                // ________ Check document to cancel ________

                if(payload.idDocumentToCancel){
                    const documentToCancel = 
                        await txn.findOneOrFail(
                            DocumentEntity,
                            {
                                where: {
                                    id: payload.idDocumentToCancel
                                }
                            }
                        );
                    if(documentToCancel.cancelled == 1){
                        throw new Error("document_already_cancelled");
                    }
                    await txn.query(`UPDATE document SET cancelled = 1 WHERE id = ?`, [payload.idDocumentToCancel]);
                }

                // Try to create document

                const document: DocumentEntity  = new DocumentEntity();
                document.id                     = payload.id;
                document.documentArea           = payload.documentArea;
                document.documentDate           = payload.documentDate;
                document.documentTime           = payload.documentTime;
                document.documentProgressive    = lastProgressive;
                document.documentNumber         = documentNumber;
                document.receiptNumber          = payload.receiptNumber;
                document.receiptClose           = payload.receiptClose;
                document.amount                 = payload.amount;
                document.amountTaxable          = payload.amountTaxable;
                document.amountTax              = payload.amountTax;
                document.amountDiscount         = payload.amountDiscount;
                document.amountTaxableDiscount  = payload.amountTaxableDiscount;
                document.amountTaxDiscount      = payload.amountTaxDiscount;
                document.note                   = payload.note;
                
                if(payload.idPrice){
                    const exists = 
                        await txn.exists(
                            PriceEntity,
                            {
                                where: {
                                    id: payload.idPrice
                                }
                            }
                        );
                    if(!exists){
                        throw new Error("invalid_price");
                    }
                    document.price = new PriceEntity(payload.idPrice);
                }

                if(payload.idRefDocument){
                    if(payload.id && payload.id == payload.idRefDocument){
                        throw new Error("recursive_ref_document");
                    }
                    const exists = 
                        txn.exists(
                            DocumentEntity,
                            {
                                where: {
                                    id: payload.idRefDocument
                                }
                            }
                        );
                    if(!exists){
                        throw new Error("invalid_ref_document");
                    }
                    document.refDocument = new DocumentEntity(payload.idRefDocument);
                }

                await txn.save(document);

                // ________ Save document rows ________

                const rows: DocumentRowEntity[]                             = [];
                const movements: MovementEntity[]                           = [];
                const lastValueCosts: {idArticle: number, lastValueCost: number, lastValueCostTaxable: number}[] = [];
                const lastValueSales: {idArticle: number, lastValueSale: number, lastValueSaleTaxable: number}[] = [];

                for await (const row of payload.rows!){

                    const documentRow: DocumentRowEntity    = new DocumentRowEntity();
                    documentRow.amount                      = row.amount;
                    documentRow.amountDiscount              = row.amountDiscount;
                    documentRow.amountTax                   = row.amountTax;
                    documentRow.amountTaxDiscount           = row.amountTaxDiscount;
                    documentRow.amountTaxable               = row.amountTaxable;
                    documentRow.amountTaxableDiscount       = row.amountTaxableDiscount;
                    documentRow.article                     = new ArticleEntity(row.idArticle);
                    documentRow.code                        = row.code;
                    documentRow.price                       = row.price;
                    documentRow.priceDiscount               = row.priceDiscount;
                    documentRow.quantity                    = row.quantity;
                    documentRow.rate                        = row.rate;
                    documentRow.tax                         = row.tax;
                    documentRow.taxDiscount                 = row.taxDiscount;
                    documentRow.taxable                     = row.taxable;
                    documentRow.taxableDiscount             = row.taxableDiscount;
                    documentRow.title                       = row.title;
                    documentRow.barcode                     = row.barcode;
                    documentRow.nature                      = row.nature;
                    documentRow.enforceability              = row.enforceability;
                    documentRow.document                    = new DocumentEntity(document.id);

                    rows.push(documentRow);

                    // Generate movement
                    if(payload.documentArea != "neutral"){

                        const movement: MovementEntity  = new MovementEntity();
                        movement.document               = new DocumentEntity(document.id);
                        movement.movementDate           = payload.documentDate;
                        movement.storage                = new StorageEntity(payload.idStorage!);
                        movement.store                  = new StoreEntity(payload.idStore);
                        movement.article                = new ArticleEntity(row.idArticle);
                        movement.charge                 = 0;
                        movement.discharge              = 0;

                        if(payload.documentArea == "inventory"){ 

                            // Get stock and set movement quantity
                            const stock         = await this.helperProvider.getStock(row.idArticle!, payload.idStore, payload.idStorage);
                            const movementStock = row.quantity! + (stock * -1);

                            if(movementStock > 0){
                                movement.charge = movementStock;
                            }else{
                                movement.discharge = (movementStock * -1); // Ever positive!
                            }

                        }else{

                            // Basic behavior discharge, but if document_type is in ['charge', 'cancel', 'refund', 'transfer_in'] charge!
                            movement.discharge = row.quantity;
                            if(['charge', 'cancel', 'refund', 'transfer_in', 'credit_note'].includes(payload.documentArea!)){
                                movement.discharge = 0;
                                movement.charge    = row.quantity;
                            }
                        }

                        // If is charge, increase lastValueCost
                        // If is discharge, simulation, receipt or invoice increaseLastValueSale
                        if(["charge"].includes(document.documentArea!)){
                            lastValueCosts.push(
                                {"idArticle": row.idArticle!, "lastValueCost": row.price!, "lastValueCostTaxable": row.taxable!}
                            );
                        }
                        if(['invoice', 'discharge', 'sale'].includes(document.documentArea!)){
                            lastValueSales.push(
                                {"idArticle": row.idArticle!, "lastValueSale": row.price!, "lastValueSaleTaxable": row.taxable!}
                            );
                        }

                        movements.push(movement);
                    }

                }

                await txn.save(rows);

                if(movements.length > 0){
                    await txn.save(movements);  
                }

                if(lastValueCosts.length > 0){
                    for await (const lastValueCost of lastValueCosts){
                        await txn.query(
                            `UPDATE article SET last_value_cost = ?, last_value_cost_taxable = ? WHERE id = ?`, 
                            [
                                lastValueCost.lastValueCost,
                                lastValueCost.lastValueCostTaxable,
                                lastValueCost.idArticle
                            ]
                        );
                    }
                }
                if(lastValueSales.length > 0){
                    for await (const lastValueSale of lastValueSales){
                        await txn.query(
                            `UPDATE article SET last_value_sale = ?, last_value_sale_taxable = ? WHERE id = ?`, 
                            [
                                lastValueSale.lastValueSale,
                                lastValueSale.lastValueSaleTaxable,
                                lastValueSale.idArticle
                            ]
                        );
                    }
                }


                //  ________ Save payments ________

                const payments: DocumentPaymentEntity[] = [];

                for await (const payment of (payload.payments ?? [])){

                    const paymentEntity: PaymentEntity = 
                        await txn.findOneOrFail(
                            PaymentEntity,
                            {
                                where: {
                                    id: payment.id
                                }
                            }
                        );

                    const documentPayment: DocumentPaymentEntity    = new DocumentPaymentEntity();
                    documentPayment.amount                          = payment.amount;
                    documentPayment.code                            = paymentEntity.code;
                    documentPayment.document                        = new DocumentEntity(document.id);
                    documentPayment.payment                         = new PaymentEntity(paymentEntity.id);
                    documentPayment.paymentType                     = paymentEntity.paymentType;
                    documentPayment.title                           = paymentEntity.title;
                    
                    payments.push(documentPayment);
                }

                await txn.save(payments);

                //  ________ Save discounts ________

                for await (const discount of (payload.discounts ?? [])){

                    const documentDiscount: DocumentDiscountEntity  = new DocumentDiscountEntity();
                    documentDiscount.discount                       = discount;
                    documentDiscount.document                       = new DocumentEntity(document.id);

                    await txn.save(documentDiscount);
                }

                // ________ Customer and supplier ________

                if(payload.idCustomer){

                    const customer = 
                        await txn.findOneOrFail(
                            CustomerEntity,
                            {
                                where: {
                                    id: payload.idCustomer
                                }
                            }
                        );

                    const documentCustomer: DocumentCustomerEntity  = new DocumentCustomerEntity();
                    documentCustomer.address                        = customer.address;
                    documentCustomer.businessName                   = customer.businessName;
                    documentCustomer.city                           = customer.city;
                    documentCustomer.customer                       = new CustomerEntity(payload.idCustomer);
                    documentCustomer.document                       = new DocumentEntity(document.id);
                    documentCustomer.email                          = customer.email;
                    documentCustomer.firstname                      = customer.firstname;
                    documentCustomer.fiscalCode                     = customer.fiscalCode;
                    documentCustomer.lastname                       = customer.lastname;
                    documentCustomer.pec                            = customer.pec;
                    documentCustomer.phoneNumber                    = customer.phoneNumber;
                    documentCustomer.province                       = customer.province;
                    documentCustomer.sdiCode                        = customer.sdiCode;
                    documentCustomer.vatNumber                      = customer.vatNumber;
                    documentCustomer.zipCode                        = customer.zipCode;

                    await txn.save(documentCustomer);
                }

                if(payload.idSupplier){

                    const supplier = 
                        await txn.findOneOrFail(
                            SupplierEntity,
                            {
                                where: {
                                    id: payload.idSupplier
                                }
                            }
                        );

                    const documentSupplier: DocumentSupplierEntity  = new DocumentSupplierEntity();
                    documentSupplier.address                        = supplier.address;
                    documentSupplier.businessName                   = supplier.businessName;
                    documentSupplier.city                           = supplier.city;
                    documentSupplier.supplier                       = new SupplierEntity(supplier.id);
                    documentSupplier.document                       = new DocumentEntity(document.id);
                    documentSupplier.email                          = supplier.email;
                    documentSupplier.firstname                      = supplier.firstname;
                    documentSupplier.fiscalCode                     = supplier.fiscalCode;
                    documentSupplier.lastname                       = supplier.lastname;
                    documentSupplier.pec                            = supplier.pec;
                    documentSupplier.phoneNumber                    = supplier.phoneNumber;
                    documentSupplier.province                       = supplier.province;
                    documentSupplier.sdiCode                        = supplier.sdiCode;
                    documentSupplier.vatNumber                      = supplier.vatNumber;
                    documentSupplier.zipCode                        = supplier.zipCode;

                    await txn.save(documentSupplier);
                }

                // ________ Store ________

                const store = 
                    await txn.findOneOrFail(
                        StoreEntity,
                        {
                            where: {
                                id: payload.idStore
                            }
                        }
                    );

                const documentStore: DocumentStoreEntity    = new DocumentStoreEntity();
                documentStore.document                      = new DocumentEntity(document.id);
                documentStore.store                         = new StoreEntity(payload.idStore);
                documentStore.title                         = store.title;
                documentStore.code                          = store.code;

                await txn.save(documentStore);

                // ________ Storage ________

                const storage = 
                    await txn.findOneOrFail(
                        StorageEntity,
                        {
                            where: {
                                id: payload.idStorage
                            }
                        }
                    );

                const documentStorage: DocumentStorageEntity    = new DocumentStorageEntity();
                documentStorage.code                            = storage.code;
                documentStorage.document                        = new DocumentEntity(document.id);
                documentStorage.storage                         = new StorageEntity(payload.idStorage);
                documentStorage.title                           = storage.title;

                await txn.save(documentStorage);

                // ________ Device ________

                if(payload.idDevice){

                    const device = 
                        await txn.findOneOrFail(
                            DeviceEntity,
                            {
                                where: {
                                    id: payload.idDevice
                                }
                            }
                        );

                    const documentDevice: DocumentDeviceEntity  = new DocumentDeviceEntity();
                    documentDevice.code                         = device.code;
                    documentDevice.device                       = new DeviceEntity(payload.idDevice);
                    documentDevice.document                     = new DocumentEntity(document.id);
                    documentDevice.title                        = device.title;

                    await txn.save(documentDevice);
                }

                // ________ Operator ________

                if(payload.idOperator){

                    const operator = 
                        await txn.findOneOrFail(
                            OperatorEntity,
                            {
                                where: {
                                    id: payload.idOperator
                                }
                            }
                        );

                    const documentOperator: DocumentOperatorEntity  = new DocumentOperatorEntity();
                    documentOperator.code                           = operator.code;
                    documentOperator.document                       = new DocumentEntity(document.id);
                    documentOperator.operator                       = new OperatorEntity(operator.id);
                    documentOperator.title                          = operator.title;

                    await txn.save(documentOperator);
                }

                // ________ Discount ________

                if(payload.idUser){

                    const user: UserEntity = 
                        await txn.findOneOrFail(
                            UserEntity,
                            {
                                where: {
                                    id: payload.idUser
                                }
                            }
                        );

                    const documentUser: DocumentUserEntity  = new DocumentUserEntity();
                    documentUser.code                       = user.code;
                    documentUser.document                   = new DocumentEntity(document.id);
                    documentUser.title                      = user.title;
                    documentUser.user                       = new UserEntity(user.id);

                    await txn.save(documentUser);
                }   

                // ________ Update progressive ________

                if(idProgressive){
                    await txn.query(`UPDATE document_progressive SET progressive = (progressive + 1) WHERE id = ?`, [idProgressive]);
                }

                // ________ If is invoice or credit note update document number ________

                if(invoiceCounter){
                    await txn.query(`UPDATE invoice_counter SET current_number = (current_number + 1) WHERE id = ?`, [invoiceCounter.id]);
                }
                if(creditNoteCounter){
                    await txn.query(`UPDATE credit_note_counter SET current_number = (current_number + 1) WHERE id = ?`, [creditNoteCounter.id]);
                }

                // ________ If is invoice or credit note generate XML ________
                if(['invoice', 'credit_note'].includes(payload.documentArea!)){
                    const xml: string | null = await this.invoiceProvider.createXML(document.id!, txn);
                    if(xml){
                        await txn.query(`UPDATE document SET invoice_xml = ? WHERE id = ?`, [xml, document.id!]);
                    }
                }

                idDocument = document.id;
            });

            response.success = true;
            response.data    = {"id": idDocument};

        }catch(e: any){
            this.logger.error(e.message);
            response.error = e.message;
        }

        return response;
    }

    /**
     * Get document by id
     * @param id 
     */
    async getDocumentById(id: number): Promise<ResponseDto>{

        const response: ResponseDto = new ResponseDto();

        try{

            const document = 
                await this.entityManager.findOneOrFail(
                    DocumentEntity,
                    {
                        where: {
                            id: id
                        },
                        relations: {
                            refDocument: true,
                            price: true
                        }
                    }
                );

            // ________ Related document data ________

            const documentCustomer = 
                await this.entityManager.findOne(
                    DocumentCustomerEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        },
                        relations: {
                            customer: true
                        }
                    }
                );
            
            const documentDevice = 
                await this.entityManager.findOne(
                    DocumentDeviceEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        },
                        relations: {
                            device: true
                        }
                    }
                );
            
            const documentDiscounts = 
                await this.entityManager.find(
                    DocumentDiscountEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        }
                    }
                );

            const documentOperator = 
                await this.entityManager.findOne(
                    DocumentOperatorEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        },
                        relations: {
                            operator: true
                        }
                    }
                );

            const documentPayments = 
                await this.entityManager.find(
                    DocumentPaymentEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        },
                        relations: {
                            payment: true
                        }
                    }
                );

            const documentRows = 
                await this.entityManager.find(
                    DocumentRowEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        },
                        relations: {
                            article: true
                        }
                    }
                );
            
            const documentStorage = 
                await this.entityManager.findOne(
                    DocumentStorageEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        },
                        relations: {
                            storage: true
                        }
                    }
                );

            const documentStore = 
                await this.entityManager.findOne(
                    DocumentStoreEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        },
                        relations: {
                            store: true
                        }
                    }
                );

            const documentSupplier = 
                await this.entityManager.findOne(
                    DocumentSupplierEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        },
                        relations: {
                            supplier: true
                        }
                    }
                );

            const documentUser = 
                await this.entityManager.findOne(
                    DocumentUserEntity,
                    {
                        where: {
                            document: {
                                id: id
                            }
                        },
                        relations: {
                            user: true
                        }
                    }
                );

            // ________ Populate DTO ________

            const documentDto: DocumentCreateDto                = new DocumentCreateDto();
            documentDto.amount                                  = document.amount;
            documentDto.amountDiscount                          = document.amountDiscount;
            documentDto.amountTax                               = document.amountTax;
            documentDto.amountTaxDiscount                       = document.amountTaxDiscount;
            documentDto.amountTaxable                           = document.amountTaxable;
            documentDto.amountTaxableDiscount                   = document.amountTaxableDiscount;
            documentDto.documentArea                            = document.documentArea as any;
            documentDto.documentDate                            = document.documentDate;
            documentDto.documentTime                            = document.documentTime;
            documentDto.id                                      = id;
            documentDto.idCustomer                              = documentCustomer && documentCustomer.customer ? documentCustomer.customer!.id : undefined;
            documentDto.idDevice                                = documentDevice && documentDevice.device ? documentDevice.device.id : undefined;
            documentDto.idOperator                              = documentOperator && documentOperator.operator ? documentOperator.operator.id : undefined;
            documentDto.idPrice                                 = document.price ? document.price.id : undefined;
            documentDto.idRefDocument                           = document.refDocument ? document.refDocument.id : undefined;
            documentDto.idStorage                               = documentStorage && documentStorage.storage ? documentStorage.storage.id : undefined;
            documentDto.idStore                                 = documentStore && documentStore.store ? documentStore.store.id : undefined;
            documentDto.idSupplier                              = documentSupplier && documentSupplier.supplier ? documentSupplier.supplier.id : undefined;
            documentDto.idUser                                  = documentUser && documentUser.user ? documentUser.user.id : undefined;
            documentDto.note                                    = document.note;
            documentDto.receiptClose                            = document.receiptClose;
            documentDto.receiptNumber                           = document.receiptNumber;

            documentDto.rows = [];
            documentRows.forEach((dr) => {

                const documentRowDto: DocumentRowDto    = new DocumentRowDto();
                documentRowDto.amount                   = dr.amount;
                documentRowDto.amountDiscount           = dr.amountDiscount;
                documentRowDto.amountTax                = dr.amountTax;
                documentRowDto.amountTaxDiscount        = dr.amountTaxDiscount;
                documentRowDto.amountTaxable            = dr.amountTaxable;
                documentRowDto.amountTaxableDiscount    = dr.amountTaxableDiscount;
                documentRowDto.barcode                  = dr.barcode;
                documentRowDto.code                     = dr.code;
                documentRowDto.idArticle                = dr.article ? dr.article.id : undefined;
                documentRowDto.price                    = dr.price;
                documentRowDto.priceDiscount            = dr.priceDiscount;
                documentRowDto.quantity                 = dr.quantity;
                documentRowDto.rate                     = dr.rate;
                documentRowDto.nature                   = dr.nature;
                documentRowDto.enforceability           = dr.enforceability;
                documentRowDto.tax                      = dr.tax;
                documentRowDto.taxDiscount              = dr.taxDiscount;
                documentRowDto.taxable                  = dr.taxable;
                documentRowDto.taxableDiscount          = dr.taxableDiscount;
                documentRowDto.title                    = dr.title;

                documentDto.rows!.push(documentRowDto);
            });

            documentDto.payments = [];
            documentPayments.forEach((dp) => {

                const documentPaymentDto: DocumentPaymentDto    = new DocumentPaymentDto();
                documentPaymentDto.amount                       = dp.amount;
                documentPaymentDto.id                           = dp.payment!.id;

                documentDto.payments!.push(documentPaymentDto);
            });


            documentDto.discounts = [];
            documentDiscounts.forEach((d) => {
                documentDto.discounts!.push(d.discount ?? 0);
            });

            response.success = true;
            response.data    = documentDto;

        }catch(e: any){
            this.logger.error(e.message);
            response.error = e.message;
        }

        return response;
    }

    /**
     * Filter by document number
     * @param filterFor 
     * @param filterValue 
     */
    async getDocumentsByNumber(filterFor: 'receiptNumber' | 'documentNumber', filterValue: string): Promise<ResponseDto>{

        const response: ResponseDto = new ResponseDto();

        try{

            let data = [];
            switch(filterFor){
                case "receiptNumber": {
                    
                    const documentNumber = filterValue.split("-")[0];
                    const documentClose  = filterValue.split("-")[1];

                    if(documentNumber.length != 4 || documentClose.length != 4){
                        throw new Error("receipt_number_not_valid");
                    }

                    data = await this.entityManager.query(
                        `
                            SELECT 
                                d.id, 
                                d.document_area documentArea, 
                                d.document_date documentDate, 
                                d.document_time documentTime, 
                                d.amount,
                                IF(
                                    dc.id IS NULL,
                                    NULL,
                                    JSON_OBJECT(
                                        'id', dc.id,
                                        'businessName', dc.business_name,
                                        'firstname', dc.firstname,
                                        'lastname', dc.lastname
                                    )
                                ) customer,
                                IF(
                                    ds.id IS NULL,
                                    NULL,
                                    JSON_OBJECT(
                                        'id', ds.id,
                                        'businessName', ds.business_name,
                                        'firstname', ds.firstname,
                                        'lastname', ds.lastname
                                    )
                                ) supplier
                            FROM 
                                document d
                            LEFT JOIN document_customer dc ON dc.id_document = d.id
                            LEFT JOIN document_supplier ds ON ds.id_document = d.id
                            WHERE d.receipt_number = ? AND d.receipt_close = ?
                        `,
                        [ documentNumber, documentClose ]
                    );
                    
                } break;
                case "documentNumber": {
                    data = await this.entityManager.query(
                        `
                            SELECT 
                                d.id, 
                                d.document_area documentArea, 
                                d.document_date documentDate, 
                                d.document_time documentTime, 
                                d.amount,
                                IF(
                                    dc.id IS NULL,
                                    NULL,
                                    JSON_OBJECT(
                                        'id', dc.id,
                                        'businessName', dc.business_name,
                                        'firstname', dc.firstname,
                                        'lastname', dc.lastname
                                    )
                                ) customer,
                                IF(
                                    ds.id IS NULL,
                                    NULL,
                                    JSON_OBJECT(
                                        'id', ds.id,
                                        'businessName', ds.business_name,
                                        'firstname', ds.firstname,
                                        'lastname', ds.lastname
                                    )
                                ) supplier
                            FROM 
                                document d
                            LEFT JOIN document_customer dc ON dc.id_document = d.id
                            LEFT JOIN document_supplier ds ON ds.id_document = d.id
                            WHERE d.document_number = ?
                        `,
                        [ filterValue ]
                    );
                } break;
            }

            data.forEach((d: any) => {
                d.customer = d.customer ? JSON.parse(d.customer) : null;
                d.supplier = d.supplier ? JSON.parse(d.supplier) : null;
            });

            response.success = true;
            response.data    = data;

        }catch(e: any){
            this.logger.error(e.message);
            response.error = e.message;
        }
        
        return response;
    }

    /**
     * Get base data for document
     * @returns 
     */
    async getBaseDataForDocument(){

        const response: ResponseDto = new ResponseDto();

        try{

            const stores                = await this.entityManager.query(`SELECT id, code, title FROM store`);
            const storages              = await this.entityManager.query(`SELECT id, code, title, id_store idStore FROM storage`);
            const payments              = await this.entityManager.query(`SELECT id, code, title FROM payment`);
            const prices                = await this.entityManager.query(`SELECT id, code, title FROM price`);
            const operators             = await this.entityManager.query(`SELECT id, code, title FROM operator`);
            const devices               = await this.entityManager.query(`SELECT id, code, title FROM device`);
            const invoiceCounters       = await this.entityManager.query(`SELECT id, code, title, id_store idStore FROM invoice_counter WHERE active = 1`);
            const creditNoteCounters    = await this.entityManager.query(`SELECT id, code, title, id_store idStore FROM credit_note_counter WHERE active = 1`);
            const rates                 = await this.entityManager.query(`SELECT id, code, value, nature, enforceability FROM rate`);
            const customers             = await this.entityManager.query(`SELECT id, business_name businessName, firstname, lastname, vat_number vatNumber, fiscal_code fiscalCode, id_price_default idPriceDefault, id_payment_default idPaymentDefault FROM customer`);
            const supplier              = await this.entityManager.query(`SELECT id, business_name businessName, firstname, lastname, vat_number vatNumber, fiscal_code fiscalCode, id_price_default idPriceDefault, id_payment_default idPaymentDefault FROM supplier`);

            // Get articles
            const articles = 
                await this.entityManager.query(
                    `
                        SELECT 
                            a.id, 
                            a.code, 
                            a.title,
                            a.id_rate idRate,
                            a.sku,
                            IFNULL(a.discount, 0) discount,
                            (
                                SELECT 
                                    IFNULL(
                                        JSON_ARRAYAGG(
                                            JSON_OBJECT(
                                                'idPrice', id_price,
                                                'price', price,
                                                'taxable', taxable,
                                                'tax', tax,
                                                'idRate', id_rate
                                            )
                                        ),
                                        '[]'
                                    )
                                FROM article_price
                                WHERE id_article = a.id
                            ) prices,
                            (
                                SELECT 
                                    IFNULL(
                                        JSON_ARRAYAGG(
                                            JSON_OBJECT(
                                                'barcode', barcode,
                                                'ratio', ratio
                                            )
                                        ),
                                        '[]'
                                    )
                                FROM article_barcode
                                WHERE id_article = a.id
                            ) barcodes
                        FROM article a
                    `
                );
            articles.forEach((a: any) => {
                a.prices    = JSON.parse(a.prices ?? '[]');
                a.barcodes  = JSON.parse(a.barcodes ?? '[]');
            });

            response.success = true;
            response.data    = 
                {
                    "stores"            : stores,
                    "storages"          : storages,
                    "payments"          : payments,
                    "prices"            : prices,
                    "operators"         : operators,
                    "devices"           : devices,
                    "rates"             : rates,
                    "invoiceCounters"   : invoiceCounters,
                    "creditNoteCounters": creditNoteCounters,
                    "customers"         : customers,
                    "supplier"          : supplier,
                    "articles"          : articles
                };

        }catch(e: any){
            this.logger.error(e.message);
        }

        return response;
    }

    async list(payload: DocumentFilterDto, idNext?: number): Promise<ResponseDto>{

        const response: ResponseDto = new ResponseDto();

        try{

            if((payload.documentDateStart && !payload.documentDateEnd) || (!payload.documentDateStart && payload.documentDateEnd)){
                throw new Error("invalid_dates");
            }
            if(payload.documentDateStart && payload.documentDateEnd){
                if(new Date(payload.documentDateStart).getTime() > new Date(payload.documentDateEnd).getTime()){
                    throw new Error("invalid_dates");
                }
            }

            const wheres: string[] = [];
            const values: any[]    = [];

            if(payload.documentArea){
                wheres.push("d.document_area = ?");
                values.push(payload.documentArea);
            }
            if(payload.documentDateStart && payload.documentDateEnd){
                wheres.push(`d.document_date BETWEEN ? AND ?`);
                values.push(payload.documentDateStart);
                values.push(payload.documentDateEnd);
            }
            if(payload.idCustomer){
                wheres.push(`dc.id_customer = ?`);
                values.push(payload.idCustomer);
            }
            if(payload.idSupplier){
                wheres.push(`ds.id_supplier = ?`);
                values.push(payload.idSupplier);
            }

            if(idNext){
                if(isNaN(idNext)){
                    throw new Error("invalid_id_next");
                }
                wheres.push(`id < ?`)
            }

            const whereString = wheres.length > 0 ? ` WHERE ${wheres.join(" AND ")} ` : "";

            const documents = 
                await this.entityManager.query(
                    `
                        SELECT 
                            d.id,
                            d.document_area documentArea,
                            d.document_date documentDate,
                            d.document_time documentTime,
                            d.amount,
                            d.amount_taxable amountTaxable,
                            d.amount_tax amountTax,
                            d.amount_discount amountDiscount,
                            d.amount_taxable_discount amountTaxableDiscount,
                            d.amount_tax_discount amountTaxDiscount,
                            d.document_progressive documentProgressive,
                            d.document_number documentNumber,
                            d.receipt_number receiptNumber,
                            d.receipt_close receiptClose,
                            IFNULL(d.cancelled, 0) cancelled,
                            IF(
                                dc.id IS NULL,
                                NULL,
                                JSON_OBJECT(
                                    'id', dc.id,
                                    'businessName', dc.business_name,
                                    'firstname', dc.firstname,
                                    'lastname', dc.lastname,
                                    'vatNumber', dc.vat_number
                                )
                            ) AS customer,
                            IF(
                                ds.id IS NULL,
                                NULL,
                                JSON_OBJECT(
                                    'id', ds.id,
                                    'businessName', ds.business_name,
                                    'firstname', ds.firstname,
                                    'lastname', ds.lastname,
                                    'vatNumber', ds.vat_number
                                ) 
                            ) AS supplier,
                            IF(
                                dst.id IS NULL,
                                NULL,
                                JSON_OBJECT(
                                    'code', dst.code,
                                    'title', dst.title
                                )
                            ) AS store
                        FROM document d
                        LEFT JOIN document_customer dc ON dc.id_document = d.id
                        LEFT JOIN document_supplier ds ON ds.id_document = d.id
                        LEFT JOIN document_store dst ON dst.id_document = d.id
                        ${whereString}
                        ORDER BY id DESC
                        LIMIT ${process.env.LIMIT_SQL}
                    `,
                    values
                );

            // Clean docs
            documents.forEach((d: any) => {
                d.customer  = d.customer ? JSON.parse(d.customer) : null;
                d.supplier  = d.supplier ? JSON.parse(d.supplier) : null;
                d.store     = d.store ? JSON.parse(d.store) : null;
            });

            response.success = true;
            response.data    = documents;
                
        }catch(e: any){
            this.logger.error(e.message);
        }

        return response;
    }

    /**
     * List movements
     * @param payload 
     */
    async listMovements(payload: DocumentMovementListDto, idNext?: number): Promise<ResponseDto>{

        const response: ResponseDto = new ResponseDto();

        try{

            if((payload.movementDateStart && !payload.movementDateEnd) || (!payload.movementDateStart && payload.movementDateEnd)){
                throw new Error("invalid_dates");
            }
            if(payload.movementDateStart && payload.movementDateEnd){
                if(new Date(payload.movementDateStart).getTime() > new Date(payload.movementDateEnd).getTime()){
                    throw new Error("invalid_dates");
                }
            }

            const wheres: string[] = [];
            const values: any[]    = [];

            if(payload.idDocument){
                wheres.push(`m.id_document = ?`);
                values.push(payload.idDocument);
            }
            if(payload.idArticle){
                wheres.push(`m.id_article = ?`);
                values.push(payload.idArticle);
            }
            if(payload.idStore){
                wheres.push(`m.id_store = ?`);
                values.push(payload.idStore);
            }
            if(payload.idStorage){
                wheres.push(`m.id_storage = ?`);
                values.push(payload.idStorage);
            }
            if(payload.movementDateStart && payload.movementDateEnd){
                wheres.push(`m.movement_date BETWEEN ? AND ?`);
                values.push(payload.movementDateStart);
                values.push(payload.movementDateEnd);
            }

            if(idNext){
                if(isNaN(idNext)){
                    throw new Error("invalid_id_next");
                }
                wheres.push(`m.id < ?`)
                values.push(idNext);
            }

            const whereString = wheres.length > 0 ? ` WHERE ${wheres.join(" AND ")} ` : "";

            const resultMovements = 
                await this.entityManager.query(
                    `
                        SELECT 
                            m.id, 
                            m.movement_date, 
                            m.charge, 
                            m.discharge,
                            m.id_document idDocument,
                            IF(
                                a.id IS NULL,
                                NULL,
                                JSON_OBJECT(
                                    'id', a.id,
                                    'code', a.code,
                                    'title', a.title
                                )
                            ) article,
                            IF(
                                s.id IS NULL,
                                NULL,
                                JSON_OBJECT(
                                    'id', s.id,
                                    'code', s.code,
                                    'title', s.title
                                )
                            ) store,
                            IF(
                                ss.id IS NULL,
                                NULL,
                                JSON_OBJECT(
                                    'id', ss.id,
                                    'code', ss.code,
                                    'title', ss.title
                                )
                            ) storage
                        FROM movement m
                        LEFT JOIN article a ON a.id = m.id_article
                        LEFT JOIN store s ON s.id = m.id_store
                        LEFT JOIN storage ss ON ss.id = m.id_storage 
                        ${whereString}
                        ORDER BY m.id DESC
                        LIMIT ${process.env.LIMIT_SQL}
                    `,
                    values
                );

            resultMovements.forEach((rm: any) => {
                rm.article  = rm.article ? JSON.parse(rm.article) : null;
                rm.store    = rm.store ? JSON.parse(rm.store) : null;
                rm.storage  = rm.storage ? JSON.parse(rm.storage) : null;
            });

            response.success = true;
            response.data    = resultMovements;

        }catch(e: any){
            this.logger.error(e.message);
        }

        return response;
    }

    /**
     * Trash document
     * @param idDocument 
     */
    async trashDocument(idDocument: number): Promise<ResponseDto>{

        const response: ResponseDto = new ResponseDto();

        try{

            const exists = 
                await this.entityManager.exists(
                    DocumentEntity,
                    {
                        where: {
                            id: idDocument
                        }
                    }
                );

            if(!exists){
                throw new Error("document_not_found");
            }

            await this.entityManager.query(`DELETE FROM document WHERE id = ?`, [idDocument]);

            response.success = true;

        }catch(e: any){
            this.logger.error(e.message);
        }

        return response;
    }

    /**
     * Get ipotetical progressive number
     * @param documentArea 
     * @param idStore 
     */
    async getIpoteticalProgressiveNumber(documentArea: string, idStore: number): Promise<ResponseDto>{
        
        const response: ResponseDto = new ResponseDto();

        try{

            const documentProgressive = 
                await this.entityManager.findOne(
                    DocumentProgressiveEntity,
                    {
                        where: {
                            documentArea: documentArea,
                            store: {
                                id: idStore
                            }
                        }
                    }
                );

            response.success = true;
            let progressive  = 1;

            if(documentProgressive){
                progressive = documentProgressive.progressive ?? 1;
            }

            response.data = { "progressive": progressive };

        }catch(e: any){
            this.logger.error(e.message);
        }

        return response;
    }

    /**
     * Get ipotetical document number
     * @param documentArea 
     * @param idCounter 
     * @returns 
     */
    async getIpoteticalDocumentNumber(documentArea: string, idCounter: number): Promise<ResponseDto>{
        
        const response: ResponseDto = new ResponseDto();

        try{

            if(!['invoice', 'credit_note'].includes(documentArea)){
                throw new Error("invalid_document_area");
            }

            let newNumber: string | null = null;

            switch(documentArea){
                case "invoice": {
                    const counter = 
                        await this.entityManager.findOneOrFail(
                            InvoiceCounterEntity,
                            {
                                where: {
                                    id: idCounter
                                }
                            }
                        );
                    newNumber = `${(counter.prefix ?? "").trim()}${counter.currentNumber}${(counter.suffix ?? "").trim()}`;
                } break;
                case "credit_note": {
                    const counter = 
                        await this.entityManager.findOneOrFail(
                            CreditNoteCounterEntity,
                            {
                                where: {
                                    id: idCounter
                                }
                            }
                        );
                    newNumber = `${(counter.prefix ?? "").trim()}${counter.currentNumber}${(counter.suffix ?? "").trim()}`;
                } break;
            }

            if(!newNumber){
                throw new Error("invalid_counter_number");
            }

            response.success = true;
            response.data    = { "documentNumber": newNumber };

        }catch(e: any){
            this.logger.error(e.message);
        }

        return response;
    }

    /**
     * Get invoice or credit note xml
     * @param idDocument 
     * @returns 
     */
    async getInvoiceOrCreditNoteXML(idDocument: number): Promise<ResponseDto>{

        const response: ResponseDto = new ResponseDto();    

        try{

            const document = 
                await this.entityManager.findOneOrFail(
                    DocumentEntity,
                    {
                        where: {
                            id: idDocument
                        }
                    }
                );

            if(document.documentArea != "invoice" && document.documentArea != "credit_note"){
                throw new Error("invalid_document_area");
            }

            response.success = true;
            response.data    = {"xml": document.invoiceXml}

        }catch(e: any){
            this.logger.error(e.message);
        }

        return response;
    }

}