const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')
const moment=require("moment")

const requestUpdates = sequelize.define('request_update', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    request_id: { type: Sequelize.INTEGER, allowNull: false },
    partner_id: { type: Sequelize.INTEGER, allowNull: false },
    partner_name: { type: Sequelize.STRING, allowNull: true },
    activity: { type: Sequelize.TEXT, allowNull: false, defaultValue: false },
    added_by: { type: Sequelize.INTEGER, allowNull: false, },
    timestamp: { type: Sequelize.INTEGER, allowNull: false, },
    status: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
}, { timestamps: false })

const addRequestUpdates = async (data) => {
    try {
        let results = await requestUpdates.create(data)
        if (results) {
            return true;
        }
        else {
            return false;
        }
    } catch (error) {
        console.log(error, 'Error in adding partner ')
    }
}
const fetchAllUpdates = async (start_position, request_id, per_page = NULL, partner_id = false, user_id, user_type) => {
    const where = user_type == 3
        ? `and (p.id is NULL or p.id = ${partner_id}) and (u.user_type!=3 or ru.added_by=${user_id})`
        : "";
    const mapUpdates = (rows = []) => rows.map((row) => {
        row.raw_timestamp = row.timestamp;
        const start_date = moment(row.timestamp * 1000);
        row.timestamp = getAgoString(start_date);
        return row;
    });
    const sortMergedUpdates = (updates = []) => updates.sort((a, b) => {
        const aKey = Number(a?.raw_timestamp) || Number(a?.id) || 0;
        const bKey = Number(b?.raw_timestamp) || Number(b?.id) || 0;
        return bKey - aKey;
    });
    const fetchRequestUpdatesById = async (targetRequestId) => {
        const query = await sequelize.query(` select ru.*,concat(coalesce(u.first_name,u.company_name),' ',u.last_name) as name, u.company_name,u.profile_pic,(case when p.onboard_complete=0 then 'N' else 'Y' end) as active`
            + ` from request_updates ru `
            + ` left join users u on ru.added_by=u.id`
            + ` left join partners p on p.id=ru.partner_id`
            + ` where ru.request_id=${targetRequestId} and ru.is_jobcost = 0  ${where} order by ru.id desc `
        );
        return mapUpdates(query?.[0] || []);
    };

    let designUpdates = [];
    let estimateUpdates = [];
    try {
        const [designMeta, data] = await Promise.all([
            sequelize.query(
                `SELECT design_order_id, isFromDesignOrder, isFromEstimate FROM requests WHERE id=${request_id} LIMIT 1`,
                { type: sequelize.QueryTypes.SELECT }
            ),
            fetchRequestUpdatesById(request_id),
        ]);

        const meta = designMeta?.[0];
        const extraUpdatePromises = [];

        if (meta?.isFromDesignOrder == 1 && meta?.design_order_id) {
            extraUpdatePromises.push(
                fetchAllDesignOrderUpdates(
                    start_position,
                    meta.design_order_id,
                    per_page,
                    partner_id,
                    user_id,
                    user_type
                )
            );
        } else {
            extraUpdatePromises.push(Promise.resolve([]));
        }

        if (meta?.isFromEstimate) {
            extraUpdatePromises.push(fetchRequestUpdatesById(meta.isFromEstimate));
        } else {
            extraUpdatePromises.push(Promise.resolve([]));
        }

        [designUpdates, estimateUpdates] = await Promise.all(extraUpdatePromises);

        return sortMergedUpdates([...designUpdates, ...estimateUpdates, ...data]);
    } catch (err) {
        console.log('Error fetching design order updates meta:', err);
        const data = await fetchRequestUpdatesById(request_id);
        return sortMergedUpdates([...designUpdates, ...estimateUpdates, ...data]);
    }
}
// const fetchAllUpdates = async (start_position, request_id, per_page = NULL, partner_id = false, user_id, user_type) => {
//     let where = ""
//     if (user_type == 3) {
//         where = `and (p.id is NULL or p.id = ${partner_id}) and (u.user_type!=3 or ru.added_by=${user_id})`
//     }
//     // Check if the request originated from a design order or previous estimate to merge those updates
//     let designUpdates = [];
//     let estimateUpdates = [];
//     try {
//         const designMeta = await sequelize.query(
//             `SELECT design_order_id, isFromDesignOrder, isFromEstimate FROM requests WHERE id=${request_id} LIMIT 1`,
//             { type: sequelize.QueryTypes.SELECT }
//         );
//         if (designMeta?.[0]?.isFromDesignOrder == 1 && designMeta?.[0]?.design_order_id) {
//             designUpdates = await fetchAllDesignOrderUpdates(
//                 start_position,
//                 designMeta[0].design_order_id,
//                 per_page,
//                 partner_id,
//                 user_id,
//                 user_type
//             );
//         }
//         // If this request was converted from another estimate, merge that estimate's updates too
//         if (designMeta?.[0]?.isFromEstimate) {
//             const sourceEstimateId = designMeta[0].isFromEstimate;
//             let estimateWhere = ""
//             if (user_type == 3) {
//                 estimateWhere = `and (p.id is NULL or p.id = ${partner_id}) and (u.user_type!=3 or ru.added_by=${user_id})`
//             }
//             const estQuery = await sequelize.query(` select ru.*,concat(coalesce(u.first_name,u.company_name),' ',u.last_name) as name, u.company_name,u.profile_pic,(case when p.onboard_complete=0 then 'N' else 'Y' end) as active`
//                 + ` from request_updates ru `
//                 + ` left join users u on ru.added_by=u.id`
//                 + ` left join partners p on p.id=ru.partner_id`
//                 + ` where ru.request_id=${sourceEstimateId} and ru.is_jobcost = 0  ${estimateWhere} order by ru.id desc `
//             );
//             if (estQuery[0] && estQuery[0].length > 0) {
//                 estimateUpdates = estQuery[0].map((row) => {
//                     row.raw_timestamp = row.timestamp;
//                     const start_date = moment(row.timestamp * 1000);
//                     row.timestamp = getAgoString(start_date);
//                     return row;
//                 });
//             }
//         }
//     } catch (err) {
//         console.log('Error fetching design order updates meta:', err);
//     }
//     let query = await sequelize.query(` select ru.*,concat(coalesce(u.first_name,u.company_name),' ',u.last_name) as name, u.company_name,u.profile_pic,(case when p.onboard_complete=0 then 'N' else 'Y' end) as active`
//         + ` from request_updates ru `
//         + ` left join users u on ru.added_by=u.id`
//         + ` left join partners p on p.id=ru.partner_id`
//         + ` where ru.request_id=${request_id} and ru.is_jobcost = 0  ${where} order by ru.id desc `

//     )
   
//     let data = [];
//     if (query[0] && query[0].length > 0) {

//         data = query[0].map((row) => {
//             row.raw_timestamp = row.timestamp;
//             const start_date = moment(row.timestamp * 1000); 
//             row.timestamp = getAgoString(start_date);
//             return row
//         });

//         // Merge and sort so newest appears first across all sources (numeric timestamp fallback to id)
//         const merged = [...designUpdates, ...estimateUpdates, ...data].sort((a, b) => {
//             const aKey = Number(a?.raw_timestamp) || Number(a?.id) || 0;
//             const bKey = Number(b?.raw_timestamp) || Number(b?.id) || 0;
//             return bKey - aKey;
//         });
//         return merged;
//     }
//     else {
//         return [...designUpdates, ...estimateUpdates, ...data].sort((a, b) => {
//             const aKey = Number(a?.raw_timestamp) || Number(a?.id) || 0;
//             const bKey = Number(b?.raw_timestamp) || Number(b?.id) || 0;
//             return bKey - aKey;
//         });
//     }
// }
const fetchAllJobcostUpdates = async (request_id) => {
    let where = ""

    let query = await sequelize.query(` select ru.*,concat(coalesce(u.first_name,u.company_name),' ',u.last_name) as name, u.company_name,u.profile_pic,(case when p.onboard_complete=0 then 'N' else 'Y' end) as active`
        + ` from request_updates ru `
        + ` left join users u on ru.added_by=u.id`
        + ` left join partners p on p.id=ru.partner_id`
        + ` left join projects pj on p.id=ru.partner_id`
        + ` where ru.request_id=${request_id} and ru.is_jobcost != 0  ${where} order by ru.id desc `

    )
   
    let data = [];
    if (query[0] && query[0].length > 0) {

        data = query[0].map((row) => {
            const start_date = moment(row.timestamp * 1000); 
            row.timestamp = getAgoString(start_date);
            return row
        });

        return data;
    }
    else {
        return data;
    }
}

const fetchAllDesignOrderUpdates = async (start_position, design_order_id, per_page = NULL, partner_id = false, user_id, user_type) => {
    let where = ""
    if (user_type == 3) {
        where = `and (p.id is NULL or p.id = ${partner_id}) and (u.user_type!=3 or du.added_by=${user_id})`
    }
    let query = await sequelize.query(` select du.*,concat(coalesce(u.first_name,u.company_name),' ',u.last_name) as name, u.company_name,u.profile_pic,(case when p.onboard_complete=0 then 'N' else 'Y' end) as active`
        + ` from design_order_updates du `
        + ` left join users u on du.added_by=u.id`
        + ` left join partners p on p.id=du.partner_id`
        + ` where du.design_order_id=${design_order_id} ${where} order by du.id desc`
    )
   
    let data = [];
    if (query[0] && query[0].length > 0) {

        data = query[0].map((row) => {
            row.raw_timestamp = row.timestamp; // keep raw timestamp for ordering
            const start_date = moment(row.timestamp * 1000); 
            row.timestamp = getAgoString(start_date);
            return row
        });

        return data;
    }
    else {
        return data;
    }
}

const splitEstimateUpdates = sequelize.define('split_estimate_updates', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    split_estimate_id: { type: Sequelize.BIGINT, allowNull: false },
    partner_id: { type: Sequelize.INTEGER, allowNull: false },
    partner_name: { type: Sequelize.STRING, allowNull: true },
    activity: { type: Sequelize.TEXT, allowNull: false, defaultValue: false },
    added_by: { type: Sequelize.INTEGER, allowNull: false, },
    timestamp: { type: Sequelize.INTEGER, allowNull: false, },
    status: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
}, { timestamps: false })

const addSplitEstimateUpdates = async (data) => {
    try {
        let results = await splitEstimateUpdates.create(data)
        if (results) {
            return true;
        }
        else {
            return false;
        }
    } catch (error) {
        console.log(error, 'Error in adding split estimate update ')
    }
}

const fetchAllSplitEstimateUpdates = async (start_position, split_estimate_id, per_page = NULL, partner_id = false, user_id, user_type) => {
    let where = ""
    if (user_type == 3) {
        where = `and (p.id is NULL or p.id = ${partner_id}) and (u.user_type!=3 or seu.added_by=${user_id})`
    }
    // If this split estimate originated from a design order, collect those updates too
    let designUpdates = [];
    try {
        const designMeta = await sequelize.query(
            `SELECT isFromDesignOrder FROM split_estimates WHERE id=${split_estimate_id} LIMIT 1`,
            { type: sequelize.QueryTypes.SELECT }
        );
        if (designMeta?.[0]?.isFromDesignOrder) {
            designUpdates = await fetchAllDesignOrderUpdates(
                start_position,
                designMeta[0].isFromDesignOrder, // contains design order id
                per_page,
                partner_id,
                user_id,
                user_type
            );
        }
    } catch (err) {
        console.log('Error fetching split estimate design order updates meta:', err);
    }
    let query = await sequelize.query(` select seu.*,concat(coalesce(u.first_name,u.company_name),' ',u.last_name) as name, u.company_name,u.profile_pic,(case when p.onboard_complete=0 then 'N' else 'Y' end) as active`
        + ` from split_estimate_updates seu `
        + ` left join users u on seu.added_by=u.id`
        + ` left join partners p on p.id=seu.partner_id`
        + ` where seu.split_estimate_id=${split_estimate_id} ${where} order by seu.id desc `
    )
   
    let data = [];
    if (query[0] && query[0].length > 0) {

        data = query[0].map((row) => {
            row.raw_timestamp = row.timestamp; // keep raw timestamp for ordering
            const start_date = moment(row.timestamp * 1000); 
            row.timestamp = getAgoString(start_date);
            return row
        });

        return [...designUpdates, ...data].sort((a, b) => {
            const aKey = Number(a?.raw_timestamp) || Number(a?.id) || 0;
            const bKey = Number(b?.raw_timestamp) || Number(b?.id) || 0;
            return bKey - aKey; // newest first
        });
    }
    else {
        return [...designUpdates, ...data].sort((a, b) => {
            const aKey = a?.raw_timestamp || a?.timestamp || a?.id || 0;
            const bKey = b?.raw_timestamp || b?.timestamp || b?.id || 0;
            return bKey - aKey; // newest first
        });
    }
}

function getAgoString(start_date) {
    const now = moment();
    const duration = moment.duration(now.diff(start_date));


    if (duration.asDays() > 1) {
        return start_date.format('MMMM D, YYYY');
    } else {
        return duration.humanize() + ' ago';
    }
}
module.exports = {
    requestUpdates,
    addRequestUpdates,
    fetchAllUpdates,
    fetchAllDesignOrderUpdates,
    fetchAllJobcostUpdates,
    splitEstimateUpdates,
    addSplitEstimateUpdates,
    fetchAllSplitEstimateUpdates
}