const Sequelize = require("sequelize")
const User = require("../../../models").users;
const Profile = require("../../../models").profiles;
const Project = require("../../../models").projects;
const ProjectSubmission = require("../../../models").project_submissions;
const Level = require("../../../models").levels;
const StudyMode = require("../../../models").study_modes;
const ProjectType = require("../../../models").project_types;
const ProjectTopic = require("../../../models").project_topics;
const Department = require("../../../models").departments;
const Institute = require("../../../models").institutes;
const Session = require("../../../models").sessions;
const Packages = require("../../../models").packages
const Subcriptions = require("../../../models").subscriptions;
const PaystackDetails = require("../../../models").paystack_details;
const paymentHistory = require("../../../models").payment_history;
const StudentSupervisor = require("../../../models").student_supervisors;
const appendDateFilter = require('../../helpers/appendDateFilter')
const env = process.env.NODE_ENV || 'development';
// const config = require( '../../../config/config.json')[env];
// const sequelize = new Sequelize(config.database, config.username, config.password, config);
const sequelize  = require("../../../config/connection");

exports.instituteReport =  async (req, res) =>{
    try {
        const {startDate, endDate, projectTypeId,} = req.query;
        let results; 
        let instituteTransaction = [];
        let totalTransactionSum = 0;
        let totalTransactionCount = 0;
        const institutes = await Institute.findAll({});
        const projectType = await ProjectType.findAll({})
        let queryString = ""
        if(startDate && endDate){
            queryString += `AND (payment_history.createdAt >= '${endDate}' AND payment_history.createdAt <= '${startDate}')`
        }
        if(projectTypeId)
            queryString += ` AND package_id = ${projectTypeId} `
        for( const institute of institutes){
            let singleCount = 0
            let singleSum = 0;
            var [transactionCount, metadata] = await sequelize.query(`SELECT count(*) FROM payment_history 
            INNER JOIN users  ON payment_history.username = users.username 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute.id}  AND   txn_status = "success" ${queryString}`);
            
            [transactionSum, metadata] = await sequelize.query(`SELECT sum(amount) FROM payment_history 
            INNER JOIN users  ON payment_history.username = users.username 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute.id}  AND txn_status = "success" ${queryString}`);
            

            [results, metadata] = await sequelize.query(`SELECT * FROM payment_history 
            INNER JOIN users  ON payment_history.username = users.username 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id  GROUP BY institutes.code`);
            // var [profile, metadata] = await sequelize.query(`SELECT * FROM profiles 
            // INNER JOIN users  ON profiles.user_id = users.id 
            // INNER JOIN departments ON departments.intitutes_id = institutes.id WHERE institute_id = ${institute.id} AND  `)
            if (isNaN(parseInt(transactionCount[0]['count(*)']))){
                totalTransactionCount += 0 
                singleCount = 0
            }else{
                totalTransactionCount += parseInt(transactionCount[0]['count(*)']);
                singleCount = parseInt(transactionCount[0]['count(*)']);
            } 
            if (isNaN(parseInt(transactionSum[0]['sum(amount)']))){
                totalTransactionSum += 0 
                singleSum = 0;
            }else{
                totalTransactionSum +=  parseInt(transactionSum[0]['sum(amount)']);
                singleSum = parseInt(transactionSum[0]['sum(amount)']);
            } 
            instituteTransaction.push({
                code:institute.code,
                transactionCount:singleCount,
                transactionSum:singleSum
            })
            console.log(totalTransactionSum)
            console.log(totalTransactionCount)
        }
        return res.render("admin/report_institute.ejs", {
            error:false,
            instituteTransaction,
            totalTransactionSum,
            totalTransactionCount,
            projectType,
            user: req.user, 
        })
    } catch (error) {
        console.log(error)
        return res.render("admin/report_institute.ejs", {
            error:error.message,
            instituteTransaction:[],
            totalTransactionSum:0,
            totalTransactionCount:0,
            projectType:[],
            user: req.user, 
    })
    }
    };
