import { Injectable, Logger } from "@nestjs/common";
import { EntityManager } from "typeorm";
import ResponseDto from "../../dto/response.dto";

@Injectable()
export default class InsightProvider {

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

    constructor(private readonly entityManager: EntityManager){}
    
    /**
     * Daily overview
     * @param day 
     * @returns 
     */
    async overview(day: string, withTraining: number): Promise<ResponseDto>{

        const response: ResponseDto = new ResponseDto();

        try{

            const idsDocuments = 
                await this.entityManager.query(
                    `SELECT id FROM document d WHERE d.document_area IS NOT NULL AND d.document_date = ?`,
                    [ day ]
                );
            
            const ids: number[] = [];
            idsDocuments.forEach((i: any) => {
                ids.push(i.id);
            });
            let idsString = ids.join(",");

            if(idsString.length == 0){ // Continue query with irreal number
                idsString = "-1";
            }

            const collectedMoney = (
                await this.entityManager.query(
                    `
                        SELECT 
                            IFNULL(SUM(amount), 0) amount,
                            IFNULL(SUM(amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(amount_tax), 0) amountTax,
                            IFNULL(SUM(amount_discount), 0) amountDiscount,
                            IFNULL(SUM(amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(amount_tax_discount), 0) amountTaxDiscount
                        FROM document 
                        WHERE document_area IN ('sale', 'invoice'${withTraining == 1 ? ", 'training'" : ""}) 
                        AND document_date = ?
                    `,
                    [ day ]
                )
            )[0];

            const paidOutMoney = (
                await this.entityManager.query(
                    `
                        SELECT 
                            IFNULL(SUM(amount), 0) amount,
                            IFNULL(SUM(amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(amount_tax), 0) amountTax,
                            IFNULL(SUM(amount_discount), 0) amountDiscount,
                            IFNULL(SUM(amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(amount_tax_discount), 0) amountTaxDiscount
                        FROM document 
                        WHERE document_area IN ('cancel','refund', 'credit_note') 
                        AND document_date = ?
                    `,
                    [ day ]
                )
            )[0];

            const amountForDocumentArea =
                await this.entityManager.query(
                    `
                        SELECT
                            document_area documentArea,
                            COUNT(*) documentCount,
                            IFNULL(SUM(amount), 0) amount,
                            IFNULL(SUM(amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(amount_tax), 0) amountTax,
                            IFNULL(SUM(amount_discount), 0) amountDiscount,
                            IFNULL(SUM(amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(amount_tax_discount), 0) amountTaxDiscount
                        FROM document
                        WHERE document_area IS NOT NULL
                        AND document_date = ?
                        GROUP BY document_area
                    `,
                    [ day ]
                );
            
            const amountDiscounts = (
                await this.entityManager.query(
                    `SELECT IFNULL(SUM(discount), 0) discount FROM document_discount WHERE id_document IN (${idsString})`
                )
            )[0];

            const amountOperators = 
                await this.entityManager.query(
                    `
                        SELECT 
                            dop.id_operator idOperator,
                            dop.title,
                            d.document_area documentArea,
                            IFNULL(SUM(d.amount), 0) amount,
                            IFNULL(SUM(d.amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(d.amount_tax), 0) amountTax,
                            IFNULL(SUM(d.amount_discount), 0) amountDiscount,
                            IFNULL(SUM(d.amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(d.amount_tax_discount), 0) amountTaxDiscount
                        FROM document_operator dop 
                        INNER JOIN document d ON d.id = dop.id_document
                        WHERE dop.id_document IN (${idsString})
                        GROUP BY d.document_area
                    `
                );

            const amountUsers = 
                await this.entityManager.query(
                    `
                        SELECT 
                            dop.id_operator idOperator,
                            dop.title,
                            d.document_area documentArea,
                            IFNULL(SUM(d.amount), 0) amount,
                            IFNULL(SUM(d.amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(d.amount_tax), 0) amountTax,
                            IFNULL(SUM(d.amount_discount), 0) amountDiscount,
                            IFNULL(SUM(d.amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(d.amount_tax_discount), 0) amountTaxDiscount
                        FROM document_operator dop 
                        INNER JOIN document d ON d.id = dop.id_document
                        WHERE dop.id_document IN (${idsString})
                        GROUP BY d.document_area, dop.id_operator
                    `
                );

            const amountDevices = 
                await this.entityManager.query(
                    `
                        SELECT 
                            dd.id_device idDevice,
                            dd.title,
                            d.document_area documentArea,
                            IFNULL(SUM(d.amount), 0) amount,
                            IFNULL(SUM(d.amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(d.amount_tax), 0) amountTax,
                            IFNULL(SUM(d.amount_discount), 0) amountDiscount,
                            IFNULL(SUM(d.amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(d.amount_tax_discount), 0) amountTaxDiscount
                        FROM document_device dd 
                        INNER JOIN document d ON d.id = dd.id_document
                        WHERE dd.id_document IN (${idsString})
                        GROUP BY d.document_area, dd.id_device
                    `
                );

            const amountStores = 
                await this.entityManager.query(
                    `
                        SELECT 
                            ds.id_store idStore,
                            ds.title,
                            d.document_area documentArea,
                            IFNULL(SUM(d.amount), 0) amount,
                            IFNULL(SUM(d.amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(d.amount_tax), 0) amountTax,
                            IFNULL(SUM(d.amount_discount), 0) amountDiscount,
                            IFNULL(SUM(d.amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(d.amount_tax_discount), 0) amountTaxDiscount
                        FROM document_store ds 
                        INNER JOIN document d ON d.id = ds.id_document
                        WHERE ds.id_document IN (${idsString})
                        GROUP BY d.document_area, ds.id_store
                    `
                );
            
            const amountStorages = 
                await this.entityManager.query(
                    `
                        SELECT 
                            ds.id_storage idStorage,
                            ds.title,
                            d.document_area documentArea,
                            IFNULL(SUM(d.amount), 0) amount,
                            IFNULL(SUM(d.amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(d.amount_tax), 0) amountTax,
                            IFNULL(SUM(d.amount_discount), 0) amountDiscount,
                            IFNULL(SUM(d.amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(d.amount_tax_discount), 0) amountTaxDiscount
                        FROM document_storage ds 
                        INNER JOIN document d ON d.id = ds.id_document
                        WHERE ds.id_document IN (${idsString})
                        GROUP BY d.document_area, ds.id_storage
                    `
                );

            const amountCustomers = 
                await this.entityManager.query(
                    `
                        SELECT 
                            dc.id_customer idCustomer,
                            dc.business_name businessName,
                            dc.firstname,
                            dc.lastname,
                            d.document_area documentArea,
                            IFNULL(SUM(d.amount), 0) amount,
                            IFNULL(SUM(d.amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(d.amount_tax), 0) amountTax,
                            IFNULL(SUM(d.amount_discount), 0) amountDiscount,
                            IFNULL(SUM(d.amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(d.amount_tax_discount), 0) amountTaxDiscount
                        FROM document_customer dc 
                        INNER JOIN document d ON d.id = dc.id_document
                        WHERE dc.id_document IN (${idsString})
                        GROUP BY d.document_area, dc.id_customer
                    `
                );
            
            const amountSuppliers = 
                await this.entityManager.query(
                    `
                        SELECT 
                            ds.id_supplier idSupplier,
                            ds.business_name businessName,
                            ds.firstname,
                            ds.lastname,
                            d.document_area documentArea,
                            IFNULL(SUM(d.amount), 0) amount,
                            IFNULL(SUM(d.amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(d.amount_tax), 0) amountTax,
                            IFNULL(SUM(d.amount_discount), 0) amountDiscount,
                            IFNULL(SUM(d.amount_taxable_discount), 0) amountTaxableDiscount,
                            IFNULL(SUM(d.amount_tax_discount), 0) amountTaxDiscount
                        FROM document_supplier ds 
                        INNER JOIN document d ON d.id = ds.id_document
                        WHERE ds.id_document IN (${idsString})
                        GROUP BY d.document_area, ds.id_supplier
                    `
                );
            
            const articlesSales = 
                await this.entityManager.query(
                    `
                        SELECT 
                            id_article idArticle,
                            title titleArticle,
                            IFNULL(SUM(quantity), 0) quantity,
                            IFNULL(SUM(amount), 0) amount,
                            IFNULL(SUM(amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(amount_tax), 0) amountTax,
                            IFNULL(SUM(amount_discount), 0) amountDiscount,
                            IFNULL(SUM(amount_taxable), 0) amountTaxableDiscount,
                            IFNULL(SUM(amount_tax_discount), 0) amountTaxDiscount,
                            IFNULL(
                                (
                                    SELECT 
                                        JSON_ARRAYAGG(
                                            JSON_OBJECT(
                                                'id', c.id,
                                                'title', c.title
                                            )
                                        ) 
                                    FROM article_category ac
                                    INNER JOIN category c ON c.id = ac.id_category
                                    WHERE ac.id_article = idArticle
                                ),
                                '[]'
                            ) AS categories
                        FROM document_row WHERE id_document IN (
                            SELECT id FROM document WHERE document_area IN ('sale', 'invoice'${withTraining == 1 ? ", 'training'" : ""})
                            AND document_date = ?
                        ) GROUP BY id_article
                        ORDER BY amount DESC
                    `,
                    [ day ]
                );

            articlesSales.forEach((a: any) => {
                a.categories = JSON.parse(a.categories ?? '[]');
            });

            const articlesCancelled = 
                await this.entityManager.query(
                    `
                        SELECT 
                            id_article idArticle,
                            title titleArticle,
                            IFNULL(SUM(quantity), 0) quantity,
                            IFNULL(SUM(amount), 0) amount,
                            IFNULL(SUM(amount_taxable), 0) amountTaxable,
                            IFNULL(SUM(amount_tax), 0) amountTax,
                            IFNULL(SUM(amount_discount), 0) amountDiscount,
                            IFNULL(SUM(amount_taxable), 0) amountTaxableDiscount,
                            IFNULL(SUM(amount_tax_discount), 0) amountTaxDiscount,
                            IFNULL(
                                (
                                    SELECT 
                                        JSON_ARRAYAGG(
                                            JSON_OBJECT(
                                                'id', c.id,
                                                'title', c.title
                                            )
                                        ) 
                                    FROM article_category ac
                                    INNER JOIN category c ON c.id = ac.id_category
                                    WHERE ac.id_article = idArticle
                                ),
                                '[]'
                            ) AS categories
                        FROM document_row WHERE id_document IN (
                            SELECT id FROM document WHERE document_area IN ('cancel','refund', 'credit_note')
                            AND document_date = ?
                        ) GROUP BY id_article
                        ORDER BY amount DESC
                    `,
                    [ day ]
                );
            
            const resultPayments = 
                await this.entityManager.query(
                    `
                        SELECT 
                            dp.id_payment idPayment,
                            dp.title title,
                            d.document_area documentArea,
                            SUM(dp.amount) amount
                        FROM document_payment dp 
                        INNER JOIN document d ON d.id = dp.id_document
                        WHERE dp.id_document IN (${idsString})
                        GROUP BY d.document_area, dp.id_payment
                    `
                );

            const last10Documents = 
                await this.entityManager.query(
                    `
                        SELECT 
                            d.id, 
                            d.document_area documentArea, 
                            d.document_date documentDate, 
                            d.document_time documentTime,
                            d.document_progressive documentProgressive,
                            d.document_number documentNumber,
                            d.receipt_number receiptNumber,
                            d.receipt_close receiptClose,
                            d.amount 
                        FROM document d 
                        WHERE d.document_date = ?
                        ORDER BY id DESC LIMIT 10
                    `,
                    [ day ]
                );

            response.success = true;
            response.data    = 
                {
                    "collectedMoney"        : collectedMoney,
                    "paidOutMoney"          : paidOutMoney,
                    "amountForDocumentArea" : amountForDocumentArea,
                    "amountDiscounts"       : amountDiscounts,
                    "amountOperators"       : amountOperators,
                    "amountUsers"           : amountUsers,
                    "amountDevices"         : amountDevices,
                    "amountStores"          : amountStores,
                    "amountStorages"        : amountStorages,
                    "amountCustomers"       : amountCustomers,
                    "amountSuppliers"       : amountSuppliers,
                    "articlesSales"         : articlesSales,
                    "articlesCancelled"     : articlesCancelled,
                    "resultPayments"        : resultPayments,
                    "last10Documents"       : last10Documents
                };

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

        return response;
    }

}