const sequelize = require('../config/dbconnection');
const Sequelize = require('sequelize')
const InserLog=  sequelize.define('timelogs', {
    timeId: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    userId:{ type: Sequelize.INTEGER, allowNull: false, },
    weekNo:{ type: Sequelize.INTEGER, allowNull: false, },
    clockYear:{ type: Sequelize.INTEGER, allowNull: true, },
    entryDate:{ type: Sequelize.DATE, allowNull: true, },
    startTime:{ type: Sequelize.INTEGER, allowNull: true, },
    endTime:{ type: Sequelize.INTEGER, allowNull: true, },
    entryType:{ type: Sequelize.INTEGER, allowNull: true, },
    lastUpdated:{ type: Sequelize.INTEGER, allowNull: true, },
    status:{ type: Sequelize.INTEGER, allowNull: false,defaultValue:1, },
}, { timestamps: false ,   })

const userLogin = async (username) => {
    try {
        // const [results, metadata] = await sequelize.query(
        const results = await sequelize.query(
            `
        SELECT u.id as user_id, u.password, u.username, u.first_name, u.last_name, u.company_name, u.user_type, u.email,u.added_by_client,u.parent_id,u.parent_user_type, 
        u.profile_pic, u.company_logo, u.worker_sid, u.department_id, ur.user_permissions,u.facebook_url,u.youtube_url,u.google_url,
        u.linkedIn_url,u.twitter_url,u.job_title,u.alert_status,ur.isClientUserRole AS isClient, ur.name as user_role, p.isVendor,p.name,p.id AS client_id,p2.id AS partner_client_id,p2.name AS parent_client_name,st.id AS team_id, d.id AS driver_id, d.hourly_rate
        FROM users u
        LEFT JOIN user_roles ur ON ur.id = u.user_type
        LEFT JOIN partners p ON p.user_id = u.id
        LEFT JOIN partners p2 ON p2.user_id = u.parent_id
        LEFT JOIN driver d ON d.user_id = u.id
        LEFT JOIN staff_members st ON st.user_id = u.id
        WHERE u.username = :username OR u.email = :username OR st.phone = :username AND u.status = 1
        `,
            {
                replacements: { username },
                type: sequelize.QueryTypes.SELECT,
            }
        );
        if (results) {
            return results;
        }
        else {
            return [];
        }
    } catch (error) {
        console.error(error);
        return [];
    }
}
const userLoginId = async (id) => {
    try {
        const [results, metadata] = await sequelize.query(
            `
        SELECT u.id as user_id, u.password, u.username, u.first_name, u.last_name, u.company_name, u.user_type, u.email,u.added_by_client, u.parent_id,u.parent_user_type,
        u.profile_pic, u.company_logo, u.worker_sid, u.department_id, ur.user_permissions,u.facebook_url,u.youtube_url,u.google_url,
        u.linkedIn_url,u.twitter_url,u.job_title,u.alert_status,ur.isClientUserRole AS isClient, ur.name as user_role, p.isVendor,p.name,p.id AS client_id,p2.id AS partner_client_id,p2.name AS parent_client_name,sm.id AS team_id, dr.id AS driver_id, dr.hourly_rate
        FROM users u
        LEFT JOIN user_roles ur ON ur.id = u.user_type
        LEFT JOIN partners p ON p.user_id = u.id
        LEFT JOIN partners p2 ON p2.user_id = u.parent_id
        LEFT JOIN staff_members sm ON sm.user_id = u.id
        LEFT JOIN driver dr ON dr.user_id = u.id
        WHERE u.id = :id AND (u.status = 1 OR u.status = 2)
        `,
            {
                replacements: { id },
                type: sequelize.QueryTypes.SELECT,
            }
        );
        if (results) {
            return results;
        }
        else {
            return false;
        }
    } catch (error) {
        console.error(error);
        return({ error: 'Server error' });
    }
}

const isPrivileged = async (permissions) => {
    try {
        const result = await sequelize.query(`SELECT up.id AS privilege_ids, up.name AS privileges FROM user_privileges up WHERE up.id IN (${permissions})`)
        // console.log("isPrivileged data - ",result);
        if (result) {
            return result[0];
        } else {
            return false;
        }
    }
    catch (error) {
        console.log("isPrivileged error : ", error);
        return false;
    }
}

/**
 * Get user_permissions string for a role (default role id 1 = Super Admin).
 * Used when granting full admin permissions to users with "Administrator Access".
 */
const getPermissionsForRoleId = async (roleId = 1) => {
    try {
        const [rows] = await sequelize.query(
            'SELECT user_permissions FROM user_roles WHERE id = :roleId AND status = 1',
            { replacements: { roleId }, type: sequelize.QueryTypes.SELECT }
        );
        return rows && rows.length > 0 ? (rows[0].user_permissions || null) : rows ? rows?.user_permissions : null;
    } catch (error) {
        console.error('getPermissionsForRoleId error:', error);
        return null;
    }
}
const addLog = async (data) => {
    let result = await InserLog.create(data);
    return result;
}
const updateLog = async (data,id) => {
    let result = await InserLog.update(data, { where: { timeId: id } });
    return result;
}
const getLogId = async (id,week,year) => {
    let query = await sequelize.query(`SELECT * FROM timelogs WHERE userId = ${id} AND weekNo = ${week} AND clockYear = ${year} AND endTime = '0000-00-00 00:00:00' limit 1`);
   
    return query[0][0];

}
const getAllActiveTeams=async()=>{
    let query = await sequelize.query(`SELECT * FROM users WHERE status =1 and active='y' and user_type!=3 and user_type!=1`);
    let data=[]
    if(query[0].length>0){
        data=query[0]
    }
    return data;
}
const checkActiveUser=async(id)=>{
    let query = await sequelize.query(`SELECT * FROM users WHERE id = ${id} and status =1 and active='y'`);
    return query[0][0];
    
}
const getAllStaffTime=async(WhereCondition="")=>{
    let query=await sequelize.query(`SELECT u.id as userid,concat(u.first_name,'',u.last_name) as name ,ur.name as designation FROM users u `
    +` left join user_roles ur ON u.user_type=ur.id `
    +`where u.user_type <> 3 and u.user_type <> 1`)
    let data=[]
    let results=[]
    if(query[0].length>0){
      data=query[0]
    }
    if(data.length>0){
        for (const row of data) {
            row.totalHours = await getAllHours(row.userid,WhereCondition);

            results.push(row);
          }
          return results;
    }
}
const getAllHours=async(id,WhereCondition)=>{
    let query=await sequelize.query(`SELECT TIMEDIFF(endTime, startTime) AS diff FROM timelogs WHERE userId = ${id} AND status != 2 AND endTime != '00:00:00' ${WhereCondition}`)
    let results=query[0]
    const totalHours = sumHours(results.map(row => row.diff));
    console.log("#######Hour",totalHours);
    return totalHours
}
function sumHours(times) {
    let seconds = 0;
    times.forEach(time => {
      const [hour, minute, second] = time.split(':');
      seconds += parseInt(hour) * 3600;
      seconds += parseInt(minute) * 60;
      seconds += parseInt(second);
    });
  
    const hours = Math.floor(seconds / 3600);
    seconds -= hours * 3600;
    const minutes = Math.floor(seconds / 60);
    seconds -= minutes * 60;
    return `${hours.toString().padStart(2, '0')}:${minutes.toString().padStart(2, '0')}`;
}

const checkOldPassword = async (userId, oldPassword, newPassword) => {
try {
    const query = await sequelize.query(
    `SELECT u.id as user_id, u.admin_password, u.username, u.first_name, u.last_name, u.password,
    u.user_type, u.email, u.profile_pic, u.company_logo
    FROM users u
    WHERE u.id = :userId`,
    {
        replacements: { userId },
        type: sequelize.QueryTypes.SELECT
    }
    );
    if (query && query.length > 0) {
    console.log("User found:", query[0]);
    return query[0]
    }
} catch (error) {
    console.error("Error occurred during database query:", error);
}
};

const getAdminData = async (userId) => {
    try {
        const [results, metadata] = await sequelize.query(
            `
        SELECT u.id as user_id, u.username, u.first_name, u.last_name, u.company_name, u.user_type, u.email, 
        u.profile_pic, u.company_logo, u.personal_timezone, u.date_format, u.time_format,
        sm.id AS employee_id
        FROM users u
        LEFT JOIN staff_members sm ON sm.user_id = u.id
        WHERE u.id = :userId AND u.status = 1
        `,
            {
                replacements: { userId },
                type: sequelize.QueryTypes.SELECT,
            }
        );
        if (results) {
            return results;
        }
        else {
            return false;
        }
    } catch (error) {
        console.error(err);
        reply.code(500).send({ error: 'Server error' });
    }
}


// const updateUserFCMToken = async (username, fcmToken) => {
//     try {
//         console.log("username",username)
//         console.log("fcmToken",fcmToken)
//         const [updatedRowsCount] = await sequelize.query(
//             
//             UPDATE users SET fcmToken = ${fcmToken} 
//             WHERE username = "${username}"       
//             // ,
//             // {
//             //     replacements: { fcmToken, username },
//             //     type: sequelize.QueryTypes.UPDATE,
//             // }
//         );

//         if (updatedRowsCount > 0) {
//             return { success: true, message: 'FCM token updated successfully' };
//         } else {
//             return { success: false, message: 'User not found or no updates were made' };
//         }
//     } catch (error) {
//         console.error(error);
//         return { error: 'Server error' };
//     }
// };


const updateUserFCMToken = async (username, fcmToken) => {
    try {
        console.log("username", username);
        console.log("fcmToken", fcmToken);
        const [updatedRowsCount] = await sequelize.query(
            
           ` UPDATE users
            SET fcmToken = :fcmToken
            WHERE username = :username`
            ,
            {
                replacements: { fcmToken, username },
                type: sequelize.QueryTypes.UPDATE,
            }
        );

        if (updatedRowsCount > 0) {
            return { success: true, message: 'FCM token updated successfully' };
        } else {
            return { success: false, message: 'User not found or no updates were made' };
        }
    } catch (error) {
        console.error(error);
        return { error: 'Server error' };
    }
};

const getSidebarPermissions = async (userId) => {
    try {
        // Query to fetch sidebar permissions
        const query = `
            SELECT sp.*
            FROM users u
            LEFT JOIN sidebar_permissions sp ON FIND_IN_SET(sp.id, REPLACE(u.sidebar_order, ' ', ''))
            WHERE u.id = ${userId} AND sp.status = 1
            ORDER BY FIELD(sp.id, REPLACE(u.sidebar_order, ' ', ''))
        `;

        const rows = await sequelize.query(query);

        // Query to fetch the sidebar order
        const orderData = await sequelize.query(`SELECT u.sidebar_order FROM users u WHERE u.id = ${userId}`);

        if (rows[0]?.length > 0) {
            // Format the permissions data
            const formattedData = rows[0].map((row) => ({
                id: row.id,
                name: row.name,
                title: row.title,
                permissions: row.permissions.split(','),
            }));

            // Extract the sidebar_order as an array of IDs
            const sidebarOrder = orderData[0]?.[0]?.sidebar_order
                ? orderData[0][0].sidebar_order.split(',').map(Number)
                : [];

            return { formattedData, sidebarOrder };
        } else {
            return false;
        }
    } catch (error) {
        console.error(error);
        return false;
    }
};

const updateUserSidebarOrder = async (userId, newOrder) => {
    try {
        const [updatedRowsCount] = await sequelize.query(
           ` UPDATE users
            SET sidebar_order = :newOrder
            WHERE id = :userId`
            ,
            {
                replacements: { newOrder, userId },
                type: sequelize.QueryTypes.UPDATE,
            }
        );

        if (updatedRowsCount > 0) {
            return { success: true, message: 'Sidebar order updated successfully' };
        } else {
            return { success: false, message: 'User not found or no updates were made' };
        }
    } catch (error) {
        console.error(error);
        return { error: 'Server error' };
    }
};

const userLoginByPin = async (pin) => {
    try {
        if (!pin || pin.trim() === '') {
            console.log("userLoginByPin - Empty PIN provided");
            return false;
        }

        const trimmedPin = pin.trim();
        console.log("userLoginByPin - Searching for PIN:", trimmedPin);

        const [results, metadata] = await sequelize.query(
            `
        SELECT u.id as user_id, u.password, u.username, u.first_name, u.last_name, u.company_name, u.user_type, u.email,u.added_by_client, u.parent_id,u.parent_user_type,
        u.profile_pic, u.company_logo, u.worker_sid, u.department_id, ur.user_permissions,u.facebook_url,u.youtube_url,u.google_url,
        u.linkedIn_url,u.twitter_url,u.job_title,u.alert_status,ur.isClientUserRole AS isClient, ur.name as user_role, p.isVendor,p.name,p.id AS client_id,p2.id AS partner_client_id,p2.name AS parent_client_name,sm.id AS team_id, dr.id AS driver_id, dr.hourly_rate
        FROM users u
        LEFT JOIN user_roles ur ON ur.id = u.user_type
        LEFT JOIN partners p ON p.user_id = u.id
        LEFT JOIN partners p2 ON p2.user_id = u.parent_id
        LEFT JOIN staff_members sm ON sm.user_id = u.id
        LEFT JOIN driver dr ON dr.user_id = u.id
        WHERE u.user_pin_unique = :pin AND (u.status = 1 OR u.status = 2)
        `,
            {
                replacements: { pin: trimmedPin },
                type: sequelize.QueryTypes.SELECT,
            }
        );
        
        console.log("userLoginByPin - Results:", results);
        console.log("userLoginByPin - Results type:", typeof results);
        console.log("userLoginByPin - Results is array:", Array.isArray(results));
        
        // Handle both array and non-array results
        let resultArray = [];
        if (Array.isArray(results)) {
            resultArray = results;
        } else if (results) {
            // If results is not an array but exists, wrap it
            resultArray = [results];
        }
        
        console.log("userLoginByPin - Results length:", resultArray.length);
        
        if (resultArray.length > 0) {
            console.log("userLoginByPin - User found:", resultArray[0].user_id);
            return resultArray[0];
        } else {
            console.log("userLoginByPin - No user found with PIN:", trimmedPin);
            return false;
        }
    } catch (error) {
        console.error("userLoginByPin - Error:", error);
        console.error("userLoginByPin - Error details:", error.message);
        return false;
    }
}
  
module.exports = {
    userLogin, isPrivileged,addLog,getLogId,updateLog,getAllActiveTeams,checkActiveUser,getAllStaffTime,checkOldPassword, getAdminData,
    userLoginId,updateUserFCMToken, getSidebarPermissions, updateUserSidebarOrder, userLoginByPin,
    getPermissionsForRoleId,
};
