const {yearMonthDay} = require("../helpers/dateformat");
const  {successLogger,errorLogger} = require("../helpers/applogger");

class StoreTotal{
    
    constructor(con){
        // Variável de connexão a base de dados
        this.con=con;
        this.createView();
    }

    async createView(){
        // Query para criar a View com os totais de todas as lojas
        const query =`
            CREATE OR REPLACE VIEW stores_totals AS
            SELECT  
                SUM(day_close_stores.amount) - SUM(day_close_stores.fee) as amount,
                stores.name,
                day_close_stores.nib,
                day_close_stores.description,
                day_close_stores.date_reference,
                DATE_FORMAT(day_close_stores.created_at, "%Y-%m-%d") AS date_close
      FROM day_close_stores
            INNER JOIN stores ON stores.id = day_close_stores.store_id
            GROUP BY day_close_stores.store_id,date_close
            HAVING amount > 0 and date_close = \'${yearMonthDay(new Date())}\'`;
           // HAVING amount > 0 AND date_close = '2024-06-30'`;
        // Criando a View
        try {
            await this.con.query(query);
            successLogger("View criada com successo.");
        } catch (error) {
            console.log(error)
            errorLogger("Erro ao tentar criar a view.")
        }

    }

    // Seleciona todos os totais da view stores_totals
     async getStoreTotals() {

        const query =`
            SELECT 
                REPLACE(amount, '.', ',') as amount, 
                name, 
                nib, 
                date_reference, 
                description 
            FROM 
                stores_totals
        `;
       

        try {

            const result = await new Promise((resolve,reject)=>{
                this.con.query(query,(error,result)=>{
                    if(error){
                        reject(error);
                    }   
    
                    resolve(result);
                })
            });

            successLogger('Consulta dos totais das lojas efectuada com sucesso.');

            this.con.end();
            
            return result;

        } catch (error) {

            errorLogger(error);

        }
    }
}

module.exports=StoreTotal;