const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')
const moment=require("moment")
const { empty } = require("../Helpers/Common")

const SignAgreement = sequelize.define('documents', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.CHAR, allowNull: false, defaultValue: null },
    document_type: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    content: { type: Sequelize.TEXT, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false, defaultValue: null },
    updated_on: { type: Sequelize.INTEGER, allowNull: false, defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    added_by:{type:Sequelize.INTEGER,allowNull:false,defaultValue:null},
    attachment:{type:Sequelize.STRING(1000),allowNull:true,defaultValue:null},
    agreement_pdf_path:{type:Sequelize.STRING(255),allowNull:true,defaultValue:null}
},{timestamps:false}
)
	
const AgreementImages = sequelize.define('agreement_images', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    agreement_id: { type: Sequelize.INTEGER, allowNull: false },
    image_path: { type: Sequelize.STRING(255), allowNull: false },
}, { timestamps: false });

const storeAgreementImage = async (agreementId, imagePaths) => {
    try {
        // Ensure imagePaths is an array
        if (!Array.isArray(imagePaths)) {
            throw new Error("imagePaths must be an array.");
        }

        // Create an array of image objects to be inserted into the table
        const imagesToInsert = imagePaths.map(imagePath => ({
            agreement_id: agreementId,
            image_path: imagePath,
        }));

        // Use bulkCreate to insert all images at once
        const newImages = await AgreementImages.bulkCreate(imagesToInsert);

        return newImages; // Return the array of newly inserted images
    } catch (error) {
        console.log('Error storing agreement images:', error);
        throw error; // Re-throw the error so it can be handled by the caller
    }
};

const getAgreementImages = async (agreementId) => {
    try {
        // Query to fetch the image paths associated with the provided agreementId
        const images = await AgreementImages.findAll({
            where: {
                agreement_id: agreementId
            }
        });

        // Return the list of image paths
        return images.map(image => image.image_path);
    } catch (error) {
        console.log("Error fetching agreement images:", error);
        throw error; // Re-throw the error to be handled by the caller
    }
};

const deleteOldAgreementImage = async (agreementId) => {
    try {
        await AgreementImages.destroy({
            where: {
                agreement_id: agreementId
            }
        });
        console.log(`Old image paths deleted for agreement ID: ${agreementId}`);
    } catch (error) {
        console.error('Error deleting old image paths:', error);
        throw error;
    }
};

const getAgreementsMissingImages = async (limit = 50) => {
    try {
        const safeLimit = Number(limit) > 0 ? Number(limit) : 50;
        const agreements = await sequelize.query(
            `SELECT d.*
             FROM documents d
             LEFT JOIN agreement_images ai ON d.id = ai.agreement_id
             WHERE d.status != 2
               AND d.document_type = 2
               AND (
                 d.attachment IS NOT NULL
                 OR d.agreement_pdf_path IS NOT NULL
                 OR (d.content IS NOT NULL AND d.content != '')
               )
             GROUP BY d.id
             HAVING COUNT(ai.id) = 0
             ORDER BY d.id ASC
             LIMIT :limit`,
            {
                type: sequelize.QueryTypes.SELECT,
                replacements: { limit: safeLimit },
            }
        );
        return agreements || [];
    } catch (error) {
        console.log('Error fetching agreements missing images:', error);
        return [];
    }
};

const getAllAgreements=async()=>{
    try {
        let result = await sequelize.query(`SELECT d.*, CONCAT(u.first_name, ' ', u.last_name) AS authorized_by
        FROM documents d
        LEFT JOIN users u ON d.added_by = u.id
        WHERE d.status != 2 AND d.document_type = 2
        ORDER BY d.id DESC;       
        `)
        if(!empty(result[0])){
            const data = result[0].map((row)=>row)
            return data
        }else{
            return []
        }
    } catch (error) {
        console.log(error)
    }
}
const getTemplateFieldIdById=async(id)=>{
    try {
        let result = await sequelize.query(`
        SELECT cft.*, CONCAT(u.first_name, ' ', u.last_name) AS authorized_by
        FROM documents cft
        LEFT JOIN users u ON cft.added_by = u.id
        WHERE cft.id = ${id}`)
        if(!empty(result)){
            
            return result[0][0]
        }else{
            return []
        }
    } catch (error) {
        
    }
}
const getSignAgreementsByRequestId = async (request_id) => {
    try {
        // Query to get the document IDs from the requests table
        const result = await sequelize.query(
            `SELECT r.sign_agreement
             FROM requests r
             WHERE r.id = :requestId`,
            {
                type: sequelize.QueryTypes.SELECT,
                replacements: { requestId: request_id },
            }
        );

        if (result && result.length > 0 && result[0].sign_agreement) {
            // Assuming `documents` is a comma-separated list of document IDs
            const documentIds = result[0].sign_agreement.split(',');

            // Fetch sign agreements based on the document IDs, also LEFT JOIN agreement_images to get image paths
            const agreements = await sequelize.query(
                `SELECT d.*, 
                        CONCAT(u.first_name, ' ', u.last_name) AS authorized_by,
                        GROUP_CONCAT(ai.image_path) AS image_paths
                 FROM documents d
                 LEFT JOIN users u ON d.added_by = u.id
                 LEFT JOIN agreement_images ai ON d.id = ai.agreement_id
                 WHERE d.status != 2
                   AND d.id IN (:documentIds)
                 GROUP BY d.id
                 ORDER BY d.id DESC`,
                {
                    type: sequelize.QueryTypes.SELECT,
                    replacements: { documentIds },
                }
            );

            return agreements;
        } else {
            return [];
        }
    } catch (error) {
        console.log("Error fetching sign agreements:", error);
        return [];
    }
};
// public function get_template_by_id($template_id) {
//     $result= $this->db
//                 ->select("cft.*, concat(u.first_name,' ',u.last_name) as authorized_by")
//                 ->join("users u","cft.added_by=u.id","left")
//                 ->where("cft.id", $template_id)
//                 ->get("documents cft");
//     if($result){
//         if($result->num_rows() == 1){
//             $data=$result->row_array();
//             return $data;
//         }else{
//             return FALSE;
//         }
//     }else{
//         return FALSE;
//     }
// }
const addAgreements=async(data)=>{
    try {
        let result = await SignAgreement.create(data)
        if(!empty(result)){
            return result.id
        }
    } catch (error) {
        console.log(error)
    }
}
const updateAgreements=async(id,data)=>{
    try {
        let result = await SignAgreement.update(data,{
            where:{id:id},returning:true
        })
        if(!empty(result)){
           return true
        }else{
            return false
        }
    } catch (error) {
        console.log(error)
    }
}
module.exports={
    getAllAgreements,
    getTemplateFieldIdById,
    addAgreements,
    updateAgreements,
    getSignAgreementsByRequestId,
    storeAgreementImage,
    deleteOldAgreementImage,
    getAgreementImages,
    getAgreementsMissingImages,
}