// import { User, Accountant, Invoice, Bill, Expense, Salary, DomainManagement, Hosting, LeadManagement, ClientManagement, Settings } from '../models/index.js';
// import { Op, fn, col } from 'sequelize';

// // ── Get Dashboard Stats ──────────────────────
// export const getDashboardStats = async (req, res) => {
//     try {
//         const [
//             totalUsers,
//             totalrevenue,
//             totalInvoices,
//             totalBills,
//             totalExpenses,
//             totalSalaries,
//             totalInvoiceAmount,
//             totalExpenseAmount,
//             totalSalaryAmount
//         ] = await Promise.all([
//             User.count(),
//             Accountant.count(),
//             Invoice.count(),
//             Bill.count(),
//             Expense.count(),
//             Salary.count(),
//             Invoice.sum('total_amount'),
//             Expense.sum('amount'),
//             Salary.sum('amount')
//         ]);

//         res.status(200).json({
//             success: true,
//             dashboard: {
//                 totalUsers,
//                 totalrevenue: totalInvoiceAmount || 0,
//                 totalInvoices,
//                 totalBills,
//                 totalExpenses,
//                 totalSalaries,
//                 totalInvoiceAmount: totalInvoiceAmount || 0,
//                 totalExpenseAmount: totalExpenseAmount || 0,
//                 totalSalaryAmount: totalSalaryAmount || 0
//             }
//         });
//     } catch (error) {
//         console.log(error);
//         res.status(500).json({ success: false, message: 'Internal server error' });
//     }
// };

// // ── Get Finance Dashboard Data ───────────────
// export const getFinanceDashboard = async (req, res) => {
//     try {
//         // Get monthly data for current and last month
//         const currentMonth = new Date();
//         const lastMonth = new Date(currentMonth.getFullYear(), currentMonth.getMonth() - 1);
        
//         // ── INVOICES ──
//         const currentMonthRevenue = await Invoice.sum('total_amount', {
//             where: {
//                 status: { [Op.in]: ['paid', 'Paid'] },
//                 created_at: {
//                     [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
//                     [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
//                 }
//             }
//         }) || 0;

//         const lastMonthRevenue = await Invoice.sum('total_amount', {
//             where: {
//                 status: { [Op.in]: ['paid', 'Paid'] },
//                 created_at: {
//                     [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1),
//                     [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1)
//                 }
//             }
//         }) || 0;

//         // Pending Invoices
//         const pendingInvoices = await Invoice.findAll({
//             where: { status: { [Op.in]: ['pending', 'Pending'] } },
//             attributes: ['total_amount']
//         });
//         const pendingAmount = pendingInvoices.reduce((sum, inv) => sum + (inv.total_amount || 0), 0);
//         const pendingCount = pendingInvoices.length;

//         // ── EXPENSES ──
//         const currentMonthExpenses = await Expense.sum('amount', {
//             where: {
//                 expense_date: {
//                     [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
//                     [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
//                 }
//             }
//         }) || 0;

//         const lastMonthExpenses = await Expense.sum('amount', {
//             where: {
//                 expense_date: {
//                     [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1),
//                     [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1)
//                 }
//             }
//         }) || 0;

//         // ── SALARY ──
//         const currentMonthSalary = await Salary.sum('amount', {
//             where: {
//                 created_at: {
//                     [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
//                     [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
//                 }
//             }
//         }) || 0;

//         // ── BILLS ──
//         const unpaidBills = await Bill.sum('amount', {
//             where: { status: { [Op.in]: ['pending', 'overdue'] } }
//         }) || 0;

//         // ── DOMAINS ──
//         const expiredDomains = await DomainManagement.count({
//             where: { status: { [Op.in]: ['expired', 'Expired'] } }
//         });

//         const expiringDomains = await DomainManagement.count({
//             where: { 
//                 status: { [Op.in]: ['active', 'Active'] },
//                 expiry_date: {
//                     [Op.between]: [new Date(), new Date(Date.now() + 30 * 24 * 60 * 60 * 1000)]
//                 }
//             }
//         });

//         // ── HOSTING ──
//         const activeHosting = await Hosting.count({
//             where: { status: { [Op.in]: ['active', 'Active'] } }
//         });

//         const expiringHosting = await Hosting.count({
//             where: { status: { [Op.in]: ['expiring', 'Expiring'] } }
//         });

//         // ── LEADS ──
//         const newLeads = await LeadManagement.count({
//             where: { status: 'New' }
//         });

//         const qualifiedLeads = await LeadManagement.count({
//             where: { status: 'Qualified' }
//         });

//         // ── CLIENTS ──
//         const totalClients = await ClientManagement.count();
//         const totalPotentialValue = await ClientManagement.sum('amount') || 0;

//         // ── GENERAL COUNTS ──
//         const totalUsers = await User.count();
//         const totalInvoices = await Invoice.count();
//         const totalExpenses = await Expense.count();
//         const totalSalaries = await Salary.count();
//         const totalBills = await Bill.count();

//         // Calculate trends
//         const revenueTrend = lastMonthRevenue ? (((currentMonthRevenue - lastMonthRevenue) / lastMonthRevenue) * 100).toFixed(1) : 0;
//         const expenseTrend = lastMonthExpenses ? (((currentMonthExpenses - lastMonthExpenses) / lastMonthExpenses) * 100).toFixed(1) : 0;

//         res.status(200).json({
//             success: true,
//             stats: {
//                 // Financial Stats
//                 totalRevenue: parseFloat(currentMonthRevenue),
//                 revenueTrend: parseFloat(revenueTrend),
//                 lastMonthRevenue: parseFloat(lastMonthRevenue),
//                 pendingInvoices: parseFloat(pendingAmount),
//                 pendingCount,
//                 pendingPercentage: totalInvoices > 0 ? (pendingCount / totalInvoices * 100) : 0,
//                 monthlyExpenses: parseFloat(currentMonthExpenses),
//                 expenseTrend: parseFloat(expenseTrend),
//                 lastMonthExpenses: parseFloat(lastMonthExpenses),
//                 salaryPaid: parseFloat(currentMonthSalary),
//                 unpaidBills: parseFloat(unpaidBills),
//                 salaryRelease: "100%",

//                 // Counts
//                 totalUsers,
//                 totalInvoices,
//                 totalExpenses,
//                 totalSalaries,
//                 totalBills,
//                 totalClients,
//                 expiredDomains,
//                 expiringDomains,
//                 activeHosting,
//                 expiringHosting,

  
//                 newLeads,
//                 qualifiedLeads,

//                 totalPotentialValue: parseFloat(totalPotentialValue)
//             }
//         });
//     } catch (error) {
//         console.error('Dashboard error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch dashboard stats',
//             error: error.message
//         });
//     }
// };

// // ── Get Monthly & Weekly Chart Data ─────────
// export const getRevenueExpenseChart = async (req, res) => {
//     try {
//         const { period = 'monthly' } = req.query;
        
//         if (period === 'monthly') {
//             // Get last 7 months data
//             const monthlyData = [];
//             const months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul'];
            
//             for (let i = 0; i < 7; i++) {
//                 const date = new Date();
//                 date.setMonth(date.getMonth() - (6 - i));
//                 const year = date.getFullYear();
//                 const month = date.getMonth();
                
//                 const revenue = await Invoice.sum('total_amount', {
//                     where: {
//                         status: { [Op.in]: ['paid', 'Paid'] },
//                         created_at: {
//                             [Op.gte]: new Date(year, month, 1),
//                             [Op.lt]: new Date(year, month + 1, 1)
//                         }
//                     }
//                 }) || 0;

//                 const expenses = await Expense.sum('amount', {
//                     where: {
//                         expense_date: {
//                             [Op.gte]: new Date(year, month, 1),
//                             [Op.lt]: new Date(year, month + 1, 1)
//                         }
//                     }
//                 }) || 0;

//                 monthlyData.push({
//                     period: months[i],
//                     revenue: parseFloat(revenue) || 0,
//                     expenses: parseFloat(expenses) || 0
//                 });
//             }

//             return res.status(200).json({
//                 success: true,
//                 data: monthlyData
//             });
//         } else {
//             // Weekly data (last 8 weeks)
//             const weeklyData = [];
            
//             for (let i = 0; i < 8; i++) {
//                 const endDate = new Date();
//                 endDate.setDate(endDate.getDate() - (7 - i) * 7);
//                 const startDate = new Date(endDate);
//                 startDate.setDate(startDate.getDate() - 7);

//                 const revenue = await Invoice.sum('total_amount', {
//                     where: {
//                         status: { [Op.in]: ['paid', 'Paid'] },
//                         created_at: {
//                             [Op.gte]: startDate,
//                             [Op.lte]: endDate
//                         }
//                     }
//                 }) || 0;

//                 const expenses = await Expense.sum('amount', {
//                     where: {
//                         expense_date: {
//                             [Op.gte]: startDate,
//                             [Op.lte]: endDate
//                         }
//                     }
//                 }) || 0;

//                 weeklyData.push({
//                     period: `W${i + 1}`,
//                     revenue: parseFloat(revenue) || 0,
//                     expenses: parseFloat(expenses) || 0
//                 });
//             }

//             return res.status(200).json({
//                 success: true,
//                 data: weeklyData
//             });
//         }
//     } catch (error) {
//         console.error('Chart error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch chart data',
//             error: error.message
//         });
//     }
// };

// // ── Get Expense Breakdown by Category ───────
// export const getExpenseBreakdown = async (req, res) => {
//     try {
//         const currentMonth = new Date();
        
//         const expenses = await Expense.findAll({
//             where: {
//                 expense_date: {
//                     [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
//                     [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
//                 }
//             },
//             attributes: ['category', [fn('SUM', col('amount')), 'total']],
//             group: ['category'],
//             raw: true
//         });

//         // Map to frontend format with colors
//         const colorMap = {
//             'infrastructure': '#6366f1',
//             'marketing': '#f59e0b',
//             'travel': '#10b981',
//             'utilities': '#f43f5e',
//             'office': '#8b5cf6',
//             'salary': '#0ea5e9',
//             'other': '#6b7280'
//         };

//         const breakdown = expenses.map(exp => ({
//             name: exp.category ? exp.category.charAt(0).toUpperCase() + exp.category.slice(1) : 'Other',
//             value: parseFloat(exp.total) || 0,
//             color: colorMap[exp.category?.toLowerCase()] || colorMap['other']
//         }));

//         res.status(200).json({
//             success: true,
//             data: breakdown
//         });
//     } catch (error) {
//         console.error('Expense breakdown error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch expense breakdown',
//             error: error.message
//         });
//     }
// };

// // ── Get Recent Invoices ──────────────────────
// export const getRecentInvoices = async (req, res) => {
//     try {
//         const invoices = await Invoice.findAll({
//             limit: 5,
//             order: [['created_at', 'DESC']],
//             attributes: ['id', 'invoice_number', 'total_amount', 'status', 'client_name', 'created_at']
//         });

//         const formatted = invoices.map(inv => ({
//             id: inv.invoice_number || `INV-${inv.id}`,
//             client: inv.client_name || 'Unknown',
//             amount: parseFloat(inv.total_amount) || 0,
//             status: (inv.status || 'pending').toLowerCase(),
//             date: new Date(inv.created_at).toLocaleDateString('en-IN', { month: 'short', day: 'numeric' })
//         }));

//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
//     } catch (error) {
//         console.error('Recent invoices error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch recent invoices',
//             error: error.message
//         });
//     }
// };

// // ── Get Recent Expenses ──────────────────────
// export const getRecentExpenses = async (req, res) => {
//     try {
//         const expenses = await Expense.findAll({
//             limit: 5,
//             order: [['created_at', 'DESC']],
//             attributes: ['id', 'expense_name', 'category', 'amount', 'created_at']
//         });

//         const iconMap = {
//             'infrastructure': '🖥️',
//             'marketing': '📣',
//             'travel': '✈️',
//             'utilities': '⚡',
//             'office': '🏢',
//             'salary': '💳',
//             'other': '📊'
//         };

//         const formatted = expenses.map(exp => ({
//             icon: iconMap[exp.category?.toLowerCase()] || iconMap['other'],
//             name: exp.expense_name || exp.category || 'Expense',
//             date: new Date(exp.created_at).toLocaleDateString('en-IN', { month: 'short', day: 'numeric' }),
//             amount: parseFloat(exp.amount) || 0,
//             type: 'expense'
//         }));

//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
//     } catch (error) {
//         console.error('Recent expenses error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch recent expenses',
//             error: error.message
//         });
//     }
// };

// // ── Get Top Clients ──────────────────────────
// export const getTopClients = async (req, res) => {
//     try {
//         const topClients = await Invoice.findAll({
//             attributes: ['client_name', [fn('SUM', col('total_amount')), 'totalRevenue'], [fn('COUNT', '*'), 'invoiceCount']],
//             group: ['client_name'],
//             order: [[fn('SUM', col('total_amount')), 'DESC']],
//             limit: 4,
//             raw: true,
//             subQuery: false
//         });

//         const formatted = topClients.map((client) => {
//             const initials = client.client_name?.split(' ').map(n => n[0]).join('').toUpperCase() || 'XX';
//             return {
//                 name: client.client_name || 'Unknown',
//                 revenue: parseFloat(client.totalRevenue) || 0,
//                 invoices: parseInt(client.invoiceCount) || 0,
//                 avatar: initials
//             };
//         });

//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
//     } catch (error) {
//         console.error('Top clients error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch top clients',
//             error: error.message
//         });
//     }
// };

// // ── Get Complete Dashboard (All Data) ───────
// export const getCompleteDashboard = async (req, res) => {
//     try {
//         // Fetch all data in parallel
//         const [stats, chart, expenses, invoices, expensesList, clients, domains, hosting, leads] = await Promise.all([
//             // Stats
//             (async () => {
//                 const currentMonth = new Date();
//                 const lastMonth = new Date(currentMonth.getFullYear(), currentMonth.getMonth() - 1);
                
//                 const [currentMonthRevenue, lastMonthRevenue, pendingInvoicesList, currentMonthExpenses, lastMonthExpenses, currentMonthSalary, unpaidBills, expiredDomainCount, expiringDomainCount, activeHostingCount, expiringHostingCount, newLeadCount, qualifiedLeadCount, totalClientsCount, totalPotentialValue] = await Promise.all([
//                     Invoice.sum('amount', { where: { status: { [Op.in]: ['paid', 'Paid'] }, created_at: { [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1) } } }),
//                     Invoice.sum('amount', { where: { status: { [Op.in]: ['paid', 'Paid'] }, created_at: { [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1) } } }),
//                     Invoice.findAll({ where: { status: { [Op.in]: ['pending', 'Pending'] } }, attributes: ['total_amount'] }),
//                     Expense.sum('amount', { where: { expense_date: { [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1) } } }),
//                     Expense.sum('amount', { where: { expense_date: { [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1) } } }),
//                     Salary.sum('amount', { where: { created_at: { [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1) } } }),
//                     Bill.sum('amount', { where: { status: { [Op.in]: ['pending', 'overdue'] } } }),
//                     DomainManagement.count({ where: { status: { [Op.in]: ['expired', 'Expired'] } } }),
//                     DomainManagement.count({ where: { status: { [Op.in]: ['active', 'Active'] }, expiry_date: { [Op.between]: [new Date(), new Date(Date.now() + 30 * 24 * 60 * 60 * 1000)] } } }),
//                     Hosting.count({ where: { status: { [Op.in]: ['active', 'Active'] } } }),
//                     Hosting.count({ where: { status: { [Op.in]: ['expiring', 'Expiring'] } } }),
//                     LeadManagement.count({ where: { status: 'New' } }),
//                     LeadManagement.count({ where: { status: 'Qualified' } }),
//                     ClientManagement.count(),
//                     ClientManagement.sum('amount')
//                 ]);

//                 const pendingAmount = pendingInvoicesList.reduce((sum, inv) => sum + (inv.amount || 0), 0);
//                 const revenueTrend = (lastMonthRevenue || 0) ? (((currentMonthRevenue || 0) - (lastMonthRevenue || 0)) / (lastMonthRevenue || 1) * 100).toFixed(1) : 0;
//                 const expenseTrend = (lastMonthExpenses || 0) ? (((currentMonthExpenses || 0) - (lastMonthExpenses || 0)) / (lastMonthExpenses || 1) * 100).toFixed(1) : 0;
//                 const totalInvoices = await Invoice.count();

//                 return {
//                     totalRevenue: parseFloat(currentMonthRevenue || 0),
//                     revenueTrend: parseFloat(revenueTrend),
//                     lastMonthRevenue: parseFloat(lastMonthRevenue || 0),
//                     pendingInvoices: parseFloat(pendingAmount),
//                     pendingCount: pendingInvoicesList.length,
//                     pendingPercentage: totalInvoices > 0 ? (pendingInvoicesList.length / totalInvoices * 100) : 0,
//                     monthlyExpenses: parseFloat(currentMonthExpenses || 0),
//                     expenseTrend: parseFloat(expenseTrend),
//                     lastMonthExpenses: parseFloat(lastMonthExpenses || 0),
//                     salaryPaid: parseFloat(currentMonthSalary || 0),
//                     unpaidBills: parseFloat(unpaidBills || 0),
//                     expiredDomains: expiredDomainCount,
//                     expiringDomains: expiringDomainCount,
//                     activeHosting: activeHostingCount,
//                     expiringHosting: expiringHostingCount,
//                     newLeads: newLeadCount,
//                     qualifiedLeads: qualifiedLeadCount,
//                     totalClients: totalClientsCount,
//                     totalPotentialValue: parseFloat(totalPotentialValue || 0)
//                 };
//             })(),
//             // Chart Data
//             (async () => {
//                 const monthlyData = [];
//                 const months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul'];
//                 for (let i = 0; i < 7; i++) {
//                     const date = new Date();
//                     date.setMonth(date.getMonth() - (6 - i));
//                     const year = date.getFullYear();
//                     const month = date.getMonth();
//                     const revenue = await Invoice.sum('amount', { where: { status: { [Op.in]: ['paid', 'Paid'] }, created_at: { [Op.gte]: new Date(year, month, 1), [Op.lt]: new Date(year, month + 1, 1) } } }) || 0;
//                     const expenseSum = await Expense.sum('amount', { where: { expense_date: { [Op.gte]: new Date(year, month, 1), [Op.lt]: new Date(year, month + 1, 1) } } }) || 0;
//                     monthlyData.push({ period: months[i], revenue: parseFloat(revenue) || 0, expenses: parseFloat(expenseSum) || 0 });
//                 }
//                 return monthlyData;
//             })(),
//             // Expenses Breakdown
//             (async () => {
//                 const currentMonth = new Date();
//                 const expenseData = await Expense.findAll({
//                     where: { expense_date: { [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1) } },
//                     attributes: ['category', [fn('SUM', col('amount')), 'total']],
//                     group: ['category'],
//                     raw: true
//                 });
//                 const colorMap = { 'infrastructure': '#6366f1', 'marketing': '#f59e0b', 'travel': '#10b981', 'utilities': '#f43f5e', 'office': '#8b5cf6', 'salary': '#0ea5e9', 'other': '#6b7280' };
//                 return expenseData.map(exp => ({ name: exp.category ? exp.category.charAt(0).toUpperCase() + exp.category.slice(1) : 'Other', value: parseFloat(exp.total) || 0, color: colorMap[exp.category?.toLowerCase()] || colorMap['other'] }));
//             })(),
//             // Recent Invoices
//             (async () => {
//                 const invoiceData = await Invoice.findAll({ limit: 5, order: [['created_at', 'DESC']], attributes: ['id', 'invoice_number', 'total_amount', 'status', 'client_name', 'created_at'] });
//                 return invoiceData.map(inv => ({ id: inv.invoice_number || `INV-${inv.id}`, client: inv.client_name || 'Unknown', amount: parseFloat(inv.total_amount) || 0, status: (inv.status || 'pending').toLowerCase(), date: new Date(inv.created_at).toLocaleDateString('en-IN', { month: 'short', day: 'numeric' }) }));
//             })(),
//             // Recent Expenses
//             (async () => {
//                 const expenseData = await Expense.findAll({ limit: 5, order: [['created_at', 'DESC']], attributes: ['id', 'expense_name', 'category', 'amount', 'created_at'] });
//                 const iconMap = { 'infrastructure': '🖥️', 'marketing': '📣', 'travel': '✈️', 'utilities': '⚡', 'office': '🏢', 'salary': '💳', 'other': '📊' };
//                 return expenseData.map(exp => ({ icon: iconMap[exp.category?.toLowerCase()] || iconMap['other'], name: exp.expense_name || exp.category || 'Expense', date: new Date(exp.created_at).toLocaleDateString('en-IN', { month: 'short', day: 'numeric' }), amount: parseFloat(exp.amount) || 0, type: 'expense' }));
//             })(),
//             // Top Clients
//             (async () => {
//                 const clientData = await Invoice.findAll({ attributes: ['client_name', [fn('SUM', col('total_amount')), 'totalRevenue'], [fn('COUNT', '*'), 'invoiceCount']], group: ['client_name'], order: [[fn('SUM', col('total_amount')), 'DESC']], limit: 4, raw: true, subQuery: false });
//                 return clientData.map(client => { const initials = client.client_name?.split(' ').map(n => n[0]).join('').toUpperCase() || 'XX'; return { name: client.client_name || 'Unknown', revenue: parseFloat(client.totalRevenue) || 0, invoices: parseInt(client.invoiceCount) || 0, avatar: initials }; });
//             })(),
//             // Domains
//             (async () => {
//                 const domainData = await DomainManagement.findAll({ limit: 5, order: [['created_at', 'DESC']], attributes: ['id', 'domain_name', 'expiry_date', 'status', 'auto_renew'] });
//                 return domainData.map(d => ({ id: d.id, domain: d.domain_name, expiryDate: new Date(d.expiry_date).toLocaleDateString('en-IN'), status: d.status, autoRenew: d.auto_renew }));
//             })(),
//             // Hosting
//             (async () => {
//                 const hostingData = await Hosting.findAll({ limit: 5, order: [['created_at', 'DESC']], attributes: ['id', 'domain_name', 'renewal_date', 'status', 'cost'] });
//                 return hostingData.map(h => ({ id: h.id, domain: h.domain_name, renewalDate: new Date(h.renewal_date).toLocaleDateString('en-IN'), status: h.status, cost: parseFloat(h.cost) }));
//             })(),
//             // Leads
//             (async () => {
//                 const leadData = await LeadManagement.findAll({ limit: 5, order: [['createdAt', 'DESC']], attributes: ['id', 'name', 'email', 'status', 'source'] });
//                 return leadData.map(l => ({ id: l.id, name: l.name, email: l.email, status: l.status, source: l.source }));
//             })()
//         ]);

//         res.status(200).json({
//             success: true,
//             data: {
//                 stats,
//                 chartData: chart,
//                 expenseBreakdown: expenses,
//                 recentInvoices: invoices,
//                 recentExpenses: expensesList,
//                 topClients: clients,
//                 recentDomains: domains,
//                 recentHosting: hosting,
//                 recentLeads: leads
//             }
//         });
//     } catch (error) {
//         console.error('Complete dashboard error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch complete dashboard',
//             error: error.message
//         });
//     }
// };

// // ── Get Recent Domains ──────────────────────
// export const getRecentDomains = async (req, res) => {
//     try {
//         const domains = await DomainManagement.findAll({
//             limit: 5,
//             order: [['created_at', 'DESC']],
//             attributes: ['id', 'domain_name', 'expiry_date', 'status', 'auto_renew', 'ssl_enabled', 'ssl_expiry_date']
//         });

//         const formatted = domains.map(d => ({
//             id: d.id,
//             domain: d.domain_name,
//             expiryDate: new Date(d.expiry_date).toLocaleDateString('en-IN'),
//             status: d.status || 'active',
//             autoRenew: d.auto_renew,
//             sslEnabled: d.ssl_enabled,
//             sslExpiryDate: d.ssl_expiry_date ? new Date(d.ssl_expiry_date).toLocaleDateString('en-IN') : null
//         }));

//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
//     } catch (error) {
//         console.error('Recent domains error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch recent domains',
//             error: error.message
//         });
//     }
// };

// // ── Get Recent Hosting ──────────────────────
// export const getRecentHosting = async (req, res) => {
//     try {
//         const hosting = await Hosting.findAll({
//             limit: 5,
//             order: [['created_at', 'DESC']],
//             attributes: ['id', 'domain_name', 'client_name', 'renewal_date', 'status', 'cost', 'auto_renew']
//         });

//         const formatted = hosting.map(h => ({
//             id: h.id,
//             domain: h.domain_name,
//             clientName: h.client_name,
//             renewalDate: new Date(h.renewal_date).toLocaleDateString('en-IN'),
//             status: h.status || 'active',
//             cost: parseFloat(h.cost) || 0,
//             autoRenew: h.auto_renew
//         }));

//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
//     } catch (error) {
//         console.error('Recent hosting error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch recent hosting',
//             error: error.message
//         });
//     }
// };

// // ── Get Recent Leads ────────────────────────
// export const getRecentLeads = async (req, res) => {
//     try {
//         const leads = await LeadManagement.findAll({
//             limit: 5,
//             order: [['createdAt', 'DESC']],
//             attributes: ['id', 'name', 'email', 'phone', 'status', 'source', 'date']
//         });

//         const formatted = leads.map(l => ({
//             id: l.id,
//             name: l.name,
//             email: l.email,
//             phone: l.phone,
//             status: l.status || 'New',
//             source: l.source,
//             date: new Date(l.date).toLocaleDateString('en-IN')
//         }));

//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
//     } catch (error) {
//         console.error('Recent leads error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch recent leads',
//             error: error.message
//         });
//     }
// };

// // ── Get Unpaid Bills ─────────────────────── 
// export const getUnpaidBills = async (req, res) => {
//     try {
//         const bills = await Bill.findAll({
//             where: { status: { [Op.in]: ['pending', 'overdue'] } },
//             limit: 5,
//             order: [['due_date', 'ASC']],
//             attributes: ['id', 'bill_number', 'vendor', 'amount', 'status', 'due_date', 'created_at']
//         });

//         const formatted = bills.map(b => ({
//             id: b.id,
//             billNumber: b.bill_number,
//             vendor: b.vendor,
//             amount: parseFloat(b.amount) || 0,
//             status: b.status || 'pending',
//             dueDate: new Date(b.due_date).toLocaleDateString('en-IN'),
//             createdDate: new Date(b.created_at).toLocaleDateString('en-IN')
//         }));

//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
//     } catch (error) {
//         console.error('Unpaid bills error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch unpaid bills',
//             error: error.message
//         });
//     }
// };


// // i want to track the distribution of invoice amounts across different clients to identify which clients contribute the most to our revenue. This will help us focus our efforts on retaining and growing these key accounts. I want to create a new API endpoint that aggregates the total invoice amounts by client and returns this data in a format suitable for visualization on the dashboard.
// //  like revenue d= salary - expence- domain-hosting 
// export const TotalReceiveAmount = async (req, res) => {
//     try{
//         const distribution = await Invoice.findAll({
//             attributes: ['client_name', [fn('SUM', col('total_amount')), 'totalRevenue']],

//             group: ['client_name'], 
//             order: [[fn('SUM', col('total_amount')), 'DESC']],
//             raw: true
//         });

//         const formatted = distribution.map(d => ({
//             client: d.client_name || 'Unknown',
//             revenue: parseFloat(d.totalRevenue) || 0
//         }));
        
//         res.status(200).json({

//             success: true,

//             data: formatted
//         });


//     }
//     catch(error){
//         console.error('Amount distribution error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch amount distribution',
//             error: error.message
//         });
//     }   



// }

// export const TotalExpance = async (req, res) => {
//     try{    
//         const distribution = await Expense.findAll({    
//             attributes: ['category', [fn('SUM', col('amount')), 'totalExpense']],
//             group: ['category'],  
//             order: [[fn('SUM', col('amount')), 'DESC']],
//             raw: true
//         });
        
//         const formatted = distribution.map(d => ({

//             category: d.category || 'Other',

//             expense: parseFloat(d.totalExpense) || 0
//         }));
        
//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
//     }
//     catch(error){
//         console.error('Expense distribution error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch expense distribution',
//             error: error.message
//         });
//     }
// }

// export const TotalSalary = async (req, res) => {
//     try{
//         const distribution = await Salary.findAll({ 
//             attributes: ['employee_name', [fn('SUM', col('amount')), 'totalSalary']],
//             group: ['employee_name'],
//             order: [[fn('SUM', col('amount')), 'DESC']],
//             raw: true
//         });
        
//         const formatted = distribution.map(d => ({
//             employee: d.employee_name || 'Unknown',
//             salary: parseFloat(d.totalSalary) || 0
//         }));
        
//         res.status(200).json({
//             success: true,
//             data: formatted
//         });

//     }
//     catch(error){
//         console.error('Salary distribution error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch salary distribution',
//             error: error.message
//         });
//     }
// }


// export const TotalHostingCost = async (req, res) => {
//     try{
//         const distribution = await Hosting.findAll({
//             attributes: ['domain_name', [fn('SUM', col('cost')), 'totalCost']],
//             group: ['domain_name'],
//             order: [[fn('SUM', col('cost')), 'DESC']],
//             raw: true
//         });
        
//         const formatted = distribution.map(d => ({
//             domain: d.domain_name || 'Unknown',
//             cost: parseFloat(d.totalCost) || 0
//         }));
        
//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
        
//     }
//     catch(error){
//         console.error('Domain hosting cost distribution error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch domain hosting cost distribution',
//             error: error.message
//         });
//     }
// }

// export const TotalDomainCost = async (req, res) => {
//     try{    
//         const distribution = await DomainManagement.findAll({
//             attributes: ['domain_name', [fn('SUM', col('cost')), 'totalCost']],
//             group: ['domain_name'],
//             order: [[fn('SUM', col('cost')), 'DESC']],
//             raw: true
//         });
        
//         const formatted = distribution.map(d => ({
//             domain: d.domain_name || 'Unknown',
//             cost: parseFloat(d.totalCost) || 0
//         }));
        
//         res.status(200).json({
//             success: true,
//             data: formatted
//         });
//     }
//     catch(error){
//         console.error('Domain cost distribution error:', error);
//         res.status(500).json({
//             success: false,
//             message: 'Failed to fetch domain cost distribution',
//             error: error.message
//         });
//     }
// }


// export const TotalProfit = async (req, res) => {    
//     try{
//         const revenueData = await Invoice.sum('total_amount', { where: { status: { [Op.in]: ['paid', 'Paid'] } } }) || 0;
//         const expenseData = await Expense.sum('amount') || 0;
//         const salaryData = await Salary.sum('amount') || 0;
//         const hostingCost = await Hosting.sum('cost') || 0;
//         const domainCost = await DomainManagement.sum('cost') || 0;

//         const profit = parseFloat(revenueData) - (parseFloat(expenseData) + parseFloat(salaryData) + parseFloat(hostingCost) + parseFloat(domainCost));

//         res.status(200).json({
//             success: true,
//             data: {
//                 totalRevenue: parseFloat(revenueData),
//                 totalExpenses: parseFloat(expenseData),
//                 totalSalary: parseFloat(salaryData),
//                 totalHostingCost: parseFloat(hostingCost),
//                 totalDomainCost: parseFloat(domainCost),
//                 totalProfit: profit
//             }
//         });
//     }
//     catch(error){
//         console.error('Profit calculation error:', error);  
//         res.status(500).json({
//             success: false,
//             message: 'Failed to calculate profit',
//             error: error.message
//         });
//     }
// }


import { User, Accountant, Invoice, Bill, Expense, Salary, DomainManagement, Hosting, LeadManagement, ClientManagement, Settings } from '../models/index.js';
import { Op, fn, col } from 'sequelize';

// ── Get Dashboard Stats ──────────────────────
export const getDashboardStats = async (req, res) => {
    try {
        const [
            totalUsers,
            totalrevenue,
            totalInvoices,
            totalBills,
            totalExpenses,
            totalSalaries,
            totalInvoiceAmount,
            totalExpenseAmount,
            totalSalaryAmount
        ] = await Promise.all([
            User.count(),
            Accountant.count(),
            Invoice.count(),
            Bill.count(),
            Expense.count(),
            Salary.count(),
            Invoice.sum('total_amount'),
            Expense.sum('amount'),
            Salary.sum('amount')
        ]);

        res.status(200).json({
            success: true,
            dashboard: {
                totalUsers,
                totalrevenue: totalInvoiceAmount || 0,
                totalInvoices,
                totalBills,
                totalExpenses,
                totalSalaries,
                totalInvoiceAmount: totalInvoiceAmount || 0,
                totalExpenseAmount: totalExpenseAmount || 0,
                totalSalaryAmount: totalSalaryAmount || 0
            }
        });
    } catch (error) {
        console.log(error);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};

// ── Get Finance Dashboard Data ───────────────
export const getFinanceDashboard = async (req, res) => {
    try {
        // Get monthly data for current and last month
        const currentMonth = new Date();
        const lastMonth = new Date(currentMonth.getFullYear(), currentMonth.getMonth() - 1);
        
        // ── INVOICES ──
        const currentMonthRevenue = await Invoice.sum('total_amount', {
            where: {
                status: { [Op.in]: ['paid', 'Paid'] },
                created_at: {
                    [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
                    [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
                }
            }
        }) || 0;

        const lastMonthRevenue = await Invoice.sum('total_amount', {
            where: {
                status: { [Op.in]: ['paid', 'Paid'] },
                created_at: {
                    [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1),
                    [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1)
                }
            }
        }) || 0;

        // Pending Invoices
        const pendingInvoices = await Invoice.findAll({
            where: { status: { [Op.in]: ['pending', 'Pending'] } },
            attributes: ['total_amount']
        });
        const pendingAmount = pendingInvoices.reduce((sum, inv) => sum + (inv.total_amount || 0), 0);
        const pendingCount = pendingInvoices.length;

        // ── EXPENSES ──
        const currentMonthExpenses = await Expense.sum('amount', {
            where: {
                expense_date: {
                    [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
                    [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
                }
            }
        }) || 0;

        const lastMonthExpenses = await Expense.sum('amount', {
            where: {
                expense_date: {
                    [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1),
                    [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1)
                }
            }
        }) || 0;

        // ── SALARY ──
        const currentMonthSalary = await Salary.sum('amount', {
            where: {
                created_at: {
                    [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
                    [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
                }
            }
        }) || 0;

        // ── BILLS ──
        const unpaidBills = await Bill.sum('amount', {
            where: { status: { [Op.in]: ['pending', 'overdue'] } }
        }) || 0;

        // ── DOMAINS ──
        const expiredDomains = await DomainManagement.count({
            where: { status: { [Op.in]: ['expired', 'Expired'] } }
        });

        const expiringDomains = await DomainManagement.count({
            where: { 
                status: { [Op.in]: ['active', 'Active'] },
                expiry_date: {
                    [Op.between]: [new Date(), new Date(Date.now() + 30 * 24 * 60 * 60 * 1000)]
                }
            }
        });

        // ── HOSTING ──
        const activeHosting = await Hosting.count({
            where: { status: { [Op.in]: ['active', 'Active'] } }
        });

        const expiringHosting = await Hosting.count({
            where: { status: { [Op.in]: ['expiring', 'Expiring'] } }
        });

        // ── LEADS ──
        const newLeads = await LeadManagement.count({
            where: { status: 'New' }
        });

        const qualifiedLeads = await LeadManagement.count({
            where: { status: 'Qualified' }
        });

        // ── CLIENTS ──
        const totalClients = await ClientManagement.count();
        const totalPotentialValue = await ClientManagement.sum('amount') || 0;

        // ── GENERAL COUNTS ──
        const totalUsers = await User.count();
        const totalInvoices = await Invoice.count();
        const totalExpenses = await Expense.count();
        const totalSalaries = await Salary.count();
        const totalBills = await Bill.count();

        // Calculate trends
        const revenueTrend = lastMonthRevenue ? (((currentMonthRevenue - lastMonthRevenue) / lastMonthRevenue) * 100).toFixed(1) : 0;
        const expenseTrend = lastMonthExpenses ? (((currentMonthExpenses - lastMonthExpenses) / lastMonthExpenses) * 100).toFixed(1) : 0;

        res.status(200).json({
            success: true,
            stats: {
                // Financial Stats
                totalRevenue: parseFloat(currentMonthRevenue),
                revenueTrend: parseFloat(revenueTrend),
                lastMonthRevenue: parseFloat(lastMonthRevenue),
                pendingInvoices: parseFloat(pendingAmount),
                pendingCount,
                pendingPercentage: totalInvoices > 0 ? (pendingCount / totalInvoices * 100) : 0,
                monthlyExpenses: parseFloat(currentMonthExpenses),
                expenseTrend: parseFloat(expenseTrend),
                lastMonthExpenses: parseFloat(lastMonthExpenses),
                salaryPaid: parseFloat(currentMonthSalary),
                unpaidBills: parseFloat(unpaidBills),
                salaryRelease: "100%",

                // Counts
                totalUsers,
                totalInvoices,
                totalExpenses,
                totalSalaries,
                totalBills,
                totalClients,
                expiredDomains,
                expiringDomains,
                activeHosting,
                expiringHosting,

                newLeads,
                qualifiedLeads,

                totalPotentialValue: parseFloat(totalPotentialValue)
            }
        });
    } catch (error) {
        console.error('Dashboard error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch dashboard stats',
            error: error.message
        });
    }
};

// ── Get Monthly & Weekly Chart Data ─────────
export const getRevenueExpenseChart = async (req, res) => {
    try {
        const { period = 'monthly' } = req.query;
        
        if (period === 'monthly') {
            // Get last 7 months data
            const monthlyData = [];
            const months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul'];
            
            for (let i = 0; i < 7; i++) {
                const date = new Date();
                date.setMonth(date.getMonth() - (6 - i));
                const year = date.getFullYear();
                const month = date.getMonth();
                
                const revenue = await Invoice.sum('total_amount', {
                    where: {
                        status: { [Op.in]: ['paid', 'Paid'] },
                        created_at: {
                            [Op.gte]: new Date(year, month, 1),
                            [Op.lt]: new Date(year, month + 1, 1)
                        }
                    }
                }) || 0;

                const expenses = await Expense.sum('amount', {
                    where: {
                        expense_date: {
                            [Op.gte]: new Date(year, month, 1),
                            [Op.lt]: new Date(year, month + 1, 1)
                        }
                    }
                }) || 0;

                monthlyData.push({
                    period: months[i],
                    revenue: parseFloat(revenue) || 0,
                    expenses: parseFloat(expenses) || 0
                });
            }

            return res.status(200).json({
                success: true,
                data: monthlyData
            });
        } else {
            // Weekly data (last 8 weeks)
            const weeklyData = [];
            
            for (let i = 0; i < 8; i++) {
                const endDate = new Date();
                endDate.setDate(endDate.getDate() - (7 - i) * 7);
                const startDate = new Date(endDate);
                startDate.setDate(startDate.getDate() - 7);

                const revenue = await Invoice.sum('total_amount', {
                    where: {
                        status: { [Op.in]: ['paid', 'Paid'] },
                        created_at: {
                            [Op.gte]: startDate,
                            [Op.lte]: endDate
                        }
                    }
                }) || 0;

                const expenses = await Expense.sum('amount', {
                    where: {
                        expense_date: {
                            [Op.gte]: startDate,
                            [Op.lte]: endDate
                        }
                    }
                }) || 0;

                weeklyData.push({
                    period: `W${i + 1}`,
                    revenue: parseFloat(revenue) || 0,
                    expenses: parseFloat(expenses) || 0
                });
            }

            return res.status(200).json({
                success: true,
                data: weeklyData
            });
        }
    } catch (error) {
        console.error('Chart error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch chart data',
            error: error.message
        });
    }
};

// ── Get Expense Breakdown by Category ───────
export const getExpenseBreakdown = async (req, res) => {
    try {
        const currentMonth = new Date();
        
        const expenses = await Expense.findAll({
            where: {
                expense_date: {
                    [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
                    [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
                }
            },
            attributes: ['category', [fn('SUM', col('amount')), 'total']],
            group: ['category'],
            raw: true
        });

        // Map to frontend format with colors
        const colorMap = {
            'infrastructure': '#6366f1',
            'marketing': '#f59e0b',
            'travel': '#10b981',
            'utilities': '#f43f5e',
            'office': '#8b5cf6',
            'salary': '#0ea5e9',
            'other': '#6b7280'
        };

        const breakdown = expenses.map(exp => ({
            name: exp.category ? exp.category.charAt(0).toUpperCase() + exp.category.slice(1) : 'Other',
            value: parseFloat(exp.total) || 0,
            color: colorMap[exp.category?.toLowerCase()] || colorMap['other']
        }));

        res.status(200).json({
            success: true,
            data: breakdown
        });
    } catch (error) {
        console.error('Expense breakdown error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch expense breakdown',
            error: error.message
        });
    }
};

// ── Get Recent Invoices ──────────────────────
export const getRecentInvoices = async (req, res) => {
    try {
        const invoices = await Invoice.findAll({
            limit: 5,
            order: [['created_at', 'DESC']],
            attributes: ['id', 'invoice_number', 'total_amount', 'status', 'client_name', 'created_at']
        });

        const formatted = invoices.map(inv => ({
            id: inv.invoice_number || `INV-${inv.id}`,
            client: inv.client_name || 'Unknown',
            amount: parseFloat(inv.total_amount) || 0,
            status: (inv.status || 'pending').toLowerCase(),
            date: new Date(inv.created_at).toLocaleDateString('en-IN', { month: 'short', day: 'numeric' })
        }));

        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Recent invoices error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch recent invoices',
            error: error.message
        });
    }
};

// ── Get Recent Expenses ──────────────────────
export const getRecentExpenses = async (req, res) => {
    try {
        const expenses = await Expense.findAll({
            limit: 5,
            order: [['created_at', 'DESC']],
            attributes: ['id', 'expense_name', 'category', 'amount', 'created_at']
        });

        const iconMap = {
            'infrastructure': '🖥️',
            'marketing': '📣',
            'travel': '✈️',
            'utilities': '⚡',
            'office': '🏢',
            'salary': '💳',
            'other': '📊'
        };

        const formatted = expenses.map(exp => ({
            icon: iconMap[exp.category?.toLowerCase()] || iconMap['other'],
            name: exp.expense_name || exp.category || 'Expense',
            date: new Date(exp.created_at).toLocaleDateString('en-IN', { month: 'short', day: 'numeric' }),
            amount: parseFloat(exp.amount) || 0,
            type: 'expense'
        }));

        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Recent expenses error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch recent expenses',
            error: error.message
        });
    }
};

// ── Get Top Clients ──────────────────────────
export const getTopClients = async (req, res) => {
    try {
        const topClients = await Invoice.findAll({
            attributes: ['client_name', [fn('SUM', col('total_amount')), 'totalRevenue'], [fn('COUNT', '*'), 'invoiceCount']],
            group: ['client_name'],
            order: [[fn('SUM', col('total_amount')), 'DESC']],
            limit: 4,
            raw: true,
            subQuery: false
        });

        const formatted = topClients.map((client) => {
            const initials = client.client_name?.split(' ').map(n => n[0]).join('').toUpperCase() || 'XX';
            return {
                name: client.client_name || 'Unknown',
                revenue: parseFloat(client.totalRevenue) || 0,
                invoices: parseInt(client.invoiceCount) || 0,
                avatar: initials
            };
        });

        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Top clients error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch top clients',
            error: error.message
        });
    }
};

// ── Get Complete Dashboard (All Data) ───────
export const getCompleteDashboard = async (req, res) => {
    try {
        // Fetch all data in parallel
        const [stats, chart, expenses, invoices, expensesList, clients, domains, hosting, leads] = await Promise.all([
            // Stats
            (async () => {
                const currentMonth = new Date();
                const lastMonth = new Date(currentMonth.getFullYear(), currentMonth.getMonth() - 1);
                
                const [
                    currentMonthRevenue,
                    lastMonthRevenue,
                    pendingInvoicesList,
                    currentMonthExpenses,
                    lastMonthExpenses,
                    currentMonthSalary,
                    unpaidBills,
                    expiredDomainCount,
                    expiringDomainCount,
                    activeHostingCount,
                    expiringHostingCount,
                    newLeadCount,
                    qualifiedLeadCount,
                    totalClientsCount,
                    totalPotentialValue
                ] = await Promise.all([
                    // FIX 1: was 'amount', corrected to 'total_amount'
                    Invoice.sum('total_amount', {
                        where: {
                            status: { [Op.in]: ['paid', 'Paid'] },
                            created_at: {
                                [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
                                [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
                            }
                        }
                    }),
                    // FIX 2: was 'amount', corrected to 'total_amount'
                    Invoice.sum('total_amount', {
                        where: {
                            status: { [Op.in]: ['paid', 'Paid'] },
                            created_at: {
                                [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1),
                                [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1)
                            }
                        }
                    }),
                    Invoice.findAll({ where: { status: { [Op.in]: ['pending', 'Pending'] } }, attributes: ['total_amount'] }),
                    Expense.sum('amount', { where: { expense_date: { [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1) } } }),
                    Expense.sum('amount', { where: { expense_date: { [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1) } } }),
                    Salary.sum('amount', { where: { created_at: { [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1) } } }),
                    Bill.sum('amount', { where: { status: { [Op.in]: ['pending', 'overdue'] } } }),
                    DomainManagement.count({ where: { status: { [Op.in]: ['expired', 'Expired'] } } }),
                    DomainManagement.count({ where: { status: { [Op.in]: ['active', 'Active'] }, expiry_date: { [Op.between]: [new Date(), new Date(Date.now() + 30 * 24 * 60 * 60 * 1000)] } } }),
                    Hosting.count({ where: { status: { [Op.in]: ['active', 'Active'] } } }),
                    Hosting.count({ where: { status: { [Op.in]: ['expiring', 'Expiring'] } } }),
                    LeadManagement.count({ where: { status: 'New' } }),
                    LeadManagement.count({ where: { status: 'Qualified' } }),
                    ClientManagement.count(),
                    ClientManagement.sum('amount')
                ]);

                // FIX 3: was inv.amount, corrected to inv.total_amount
                const pendingAmount = pendingInvoicesList.reduce((sum, inv) => sum + (inv.total_amount || 0), 0);
                const revenueTrend = (lastMonthRevenue || 0) ? (((currentMonthRevenue || 0) - (lastMonthRevenue || 0)) / (lastMonthRevenue || 1) * 100).toFixed(1) : 0;
                const expenseTrend = (lastMonthExpenses || 0) ? (((currentMonthExpenses || 0) - (lastMonthExpenses || 0)) / (lastMonthExpenses || 1) * 100).toFixed(1) : 0;
                const totalInvoices = await Invoice.count();

                // ── NET REVENUE = Paid Invoices - (Salary + Expenses + Domain + Hosting) ──
                const currentMonthDomainCost = await DomainManagement.sum('cost', {
                    where: {
                        created_at: {
                            [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
                            [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
                        }
                    }
                }) || 0;

                const currentMonthHostingCost = await Hosting.sum('cost', {
                    where: {
                        created_at: {
                            [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1),
                            [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1)
                        }
                    }
                }) || 0;

                // last month domain + hosting for lastMonthRevenue net calculation
                const lastMonthDomainCost = await DomainManagement.sum('cost', {
                    where: {
                        created_at: {
                            [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1),
                            [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1)
                        }
                    }
                }) || 0;

                const lastMonthHostingCost = await Hosting.sum('cost', {
                    where: {
                        created_at: {
                            [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1),
                            [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1)
                        }
                    }
                }) || 0;

                const lastMonthSalary = await Salary.sum('amount', {
                    where: {
                        created_at: {
                            [Op.gte]: new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1),
                            [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1)
                        }
                    }
                }) || 0;

                // Net Revenue = Paid Invoices - Salary - Expenses - Domain - Hosting
                const netRevenue = parseFloat(currentMonthRevenue || 0)
                    - parseFloat(currentMonthSalary || 0)
                    - parseFloat(currentMonthExpenses || 0)
                    - parseFloat(currentMonthDomainCost)
                    - parseFloat(currentMonthHostingCost);

                const lastMonthNetRevenue = parseFloat(lastMonthRevenue || 0)
                    - parseFloat(lastMonthSalary)
                    - parseFloat(lastMonthExpenses || 0)
                    - parseFloat(lastMonthDomainCost)
                    - parseFloat(lastMonthHostingCost);

                return {
                    totalRevenue: netRevenue,
                    revenueTrend: parseFloat(revenueTrend),
                    lastMonthRevenue: lastMonthNetRevenue,
                    pendingInvoices: parseFloat(pendingAmount),
                    pendingCount: pendingInvoicesList.length,
                    pendingPercentage: totalInvoices > 0 ? (pendingInvoicesList.length / totalInvoices * 100) : 0,
                    monthlyExpenses: parseFloat(currentMonthExpenses || 0),
                    expenseTrend: parseFloat(expenseTrend),
                    lastMonthExpenses: parseFloat(lastMonthExpenses || 0),
                    salaryPaid: parseFloat(currentMonthSalary || 0),
                    unpaidBills: parseFloat(unpaidBills || 0),
                    salaryRelease: "100%",
                    expiredDomains: expiredDomainCount,
                    expiringDomains: expiringDomainCount,
                    activeHosting: activeHostingCount,
                    expiringHosting: expiringHostingCount,
                    newLeads: newLeadCount,
                    qualifiedLeads: qualifiedLeadCount,
                    totalClients: totalClientsCount,
                    totalPotentialValue: parseFloat(totalPotentialValue || 0)
                };
            })(),
            // Chart Data
            (async () => {
                const monthlyData = [];
                const months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul'];
                for (let i = 0; i < 7; i++) {
                    const date = new Date();
                    date.setMonth(date.getMonth() - (6 - i));
                    const year = date.getFullYear();
                    const month = date.getMonth();
                    // FIX 4: was 'amount', corrected to 'total_amount'
                    const revenue = await Invoice.sum('total_amount', {
                        where: {
                            status: { [Op.in]: ['paid', 'Paid'] },
                            created_at: {
                                [Op.gte]: new Date(year, month, 1),
                                [Op.lt]: new Date(year, month + 1, 1)
                            }
                        }
                    }) || 0;
                    const expenseSum = await Expense.sum('amount', { where: { expense_date: { [Op.gte]: new Date(year, month, 1), [Op.lt]: new Date(year, month + 1, 1) } } }) || 0;
                    monthlyData.push({ period: months[i], revenue: parseFloat(revenue) || 0, expenses: parseFloat(expenseSum) || 0 });
                }
                return monthlyData;
            })(),
            // Expenses Breakdown
            (async () => {
                const currentMonth = new Date();
                const expenseData = await Expense.findAll({
                    where: { expense_date: { [Op.gte]: new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1), [Op.lt]: new Date(currentMonth.getFullYear(), currentMonth.getMonth() + 1, 1) } },
                    attributes: ['category', [fn('SUM', col('amount')), 'total']],
                    group: ['category'],
                    raw: true
                });
                const colorMap = { 'infrastructure': '#6366f1', 'marketing': '#f59e0b', 'travel': '#10b981', 'utilities': '#f43f5e', 'office': '#8b5cf6', 'salary': '#0ea5e9', 'other': '#6b7280' };
                return expenseData.map(exp => ({ name: exp.category ? exp.category.charAt(0).toUpperCase() + exp.category.slice(1) : 'Other', value: parseFloat(exp.total) || 0, color: colorMap[exp.category?.toLowerCase()] || colorMap['other'] }));
            })(),
            // Recent Invoices
            (async () => {
                const invoiceData = await Invoice.findAll({ limit: 5, order: [['created_at', 'DESC']], attributes: ['id', 'invoice_number', 'total_amount', 'status', 'client_name', 'created_at'] });
                return invoiceData.map(inv => ({ id: inv.invoice_number || `INV-${inv.id}`, client: inv.client_name || 'Unknown', amount: parseFloat(inv.total_amount) || 0, status: (inv.status || 'pending').toLowerCase(), date: new Date(inv.created_at).toLocaleDateString('en-IN', { month: 'short', day: 'numeric' }) }));
            })(),
            // Recent Expenses
            (async () => {
                const expenseData = await Expense.findAll({ limit: 5, order: [['created_at', 'DESC']], attributes: ['id', 'expense_name', 'category', 'amount', 'created_at'] });
                const iconMap = { 'infrastructure': '🖥️', 'marketing': '📣', 'travel': '✈️', 'utilities': '⚡', 'office': '🏢', 'salary': '💳', 'other': '📊' };
                return expenseData.map(exp => ({ icon: iconMap[exp.category?.toLowerCase()] || iconMap['other'], name: exp.expense_name || exp.category || 'Expense', date: new Date(exp.created_at).toLocaleDateString('en-IN', { month: 'short', day: 'numeric' }), amount: parseFloat(exp.amount) || 0, type: 'expense' }));
            })(),
            // Top Clients
            (async () => {
                const clientData = await Invoice.findAll({ attributes: ['client_name', [fn('SUM', col('total_amount')), 'totalRevenue'], [fn('COUNT', '*'), 'invoiceCount']], group: ['client_name'], order: [[fn('SUM', col('total_amount')), 'DESC']], limit: 4, raw: true, subQuery: false });
                return clientData.map(client => { const initials = client.client_name?.split(' ').map(n => n[0]).join('').toUpperCase() || 'XX'; return { name: client.client_name || 'Unknown', revenue: parseFloat(client.totalRevenue) || 0, invoices: parseInt(client.invoiceCount) || 0, avatar: initials }; });
            })(),
            // Domains
            (async () => {
                const domainData = await DomainManagement.findAll({ limit: 5, order: [['created_at', 'DESC']], attributes: ['id', 'domain_name', 'expiry_date', 'status', 'auto_renew'] });
                return domainData.map(d => ({ id: d.id, domain: d.domain_name, expiryDate: new Date(d.expiry_date).toLocaleDateString('en-IN'), status: d.status, autoRenew: d.auto_renew }));
            })(),
            // Hosting
            (async () => {
                const hostingData = await Hosting.findAll({ limit: 5, order: [['created_at', 'DESC']], attributes: ['id', 'domain_name', 'renewal_date', 'status', 'cost'] });
                return hostingData.map(h => ({ id: h.id, domain: h.domain_name, renewalDate: new Date(h.renewal_date).toLocaleDateString('en-IN'), status: h.status, cost: parseFloat(h.cost) }));
            })(),
            // Leads
            (async () => {
                const leadData = await LeadManagement.findAll({ limit: 5, order: [['createdAt', 'DESC']], attributes: ['id', 'name', 'email', 'status', 'source'] });
                return leadData.map(l => ({ id: l.id, name: l.name, email: l.email, status: l.status, source: l.source }));
            })()
        ]);

        res.status(200).json({
            success: true,
            data: {
                stats,
                chartData: chart,
                expenseBreakdown: expenses,
                recentInvoices: invoices,
                recentExpenses: expensesList,
                topClients: clients,
                recentDomains: domains,
                recentHosting: hosting,
                recentLeads: leads
            }
        });
    } catch (error) {
        console.error('Complete dashboard error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch complete dashboard',
            error: error.message
        });
    }
};

// ── Get Recent Domains ──────────────────────
export const getRecentDomains = async (req, res) => {
    try {
        const domains = await DomainManagement.findAll({
            limit: 5,
            order: [['created_at', 'DESC']],
            attributes: ['id', 'domain_name', 'expiry_date', 'status', 'auto_renew', 'ssl_enabled', 'ssl_expiry_date']
        });

        const formatted = domains.map(d => ({
            id: d.id,
            domain: d.domain_name,
            expiryDate: new Date(d.expiry_date).toLocaleDateString('en-IN'),
            status: d.status || 'active',
            autoRenew: d.auto_renew,
            sslEnabled: d.ssl_enabled,
            sslExpiryDate: d.ssl_expiry_date ? new Date(d.ssl_expiry_date).toLocaleDateString('en-IN') : null
        }));

        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Recent domains error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch recent domains',
            error: error.message
        });
    }
};

// ── Get Recent Hosting ──────────────────────
export const getRecentHosting = async (req, res) => {
    try {
        const hosting = await Hosting.findAll({
            limit: 5,
            order: [['created_at', 'DESC']],
            attributes: ['id', 'domain_name', 'client_name', 'renewal_date', 'status', 'cost', 'auto_renew']
        });

        const formatted = hosting.map(h => ({
            id: h.id,
            domain: h.domain_name,
            clientName: h.client_name,
            renewalDate: new Date(h.renewal_date).toLocaleDateString('en-IN'),
            status: h.status || 'active',
            cost: parseFloat(h.cost) || 0,
            autoRenew: h.auto_renew
        }));

        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Recent hosting error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch recent hosting',
            error: error.message
        });
    }
};

// ── Get Recent Leads ────────────────────────
export const getRecentLeads = async (req, res) => {
    try {
        const leads = await LeadManagement.findAll({
            limit: 5,
            order: [['createdAt', 'DESC']],
            attributes: ['id', 'name', 'email', 'phone', 'status', 'source', 'date']
        });

        const formatted = leads.map(l => ({
            id: l.id,
            name: l.name,
            email: l.email,
            phone: l.phone,
            status: l.status || 'New',
            source: l.source,
            date: new Date(l.date).toLocaleDateString('en-IN')
        }));

        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Recent leads error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch recent leads',
            error: error.message
        });
    }
};

// ── Get Unpaid Bills ─────────────────────── 
export const getUnpaidBills = async (req, res) => {
    try {
        const bills = await Bill.findAll({
            where: { status: { [Op.in]: ['pending', 'overdue'] } },
            limit: 5,
            order: [['due_date', 'ASC']],
            attributes: ['id', 'bill_number', 'vendor', 'amount', 'status', 'due_date', 'created_at']
        });

        const formatted = bills.map(b => ({
            id: b.id,
            billNumber: b.bill_number,
            vendor: b.vendor,
            amount: parseFloat(b.amount) || 0,
            status: b.status || 'pending',
            dueDate: new Date(b.due_date).toLocaleDateString('en-IN'),
            createdDate: new Date(b.created_at).toLocaleDateString('en-IN')
        }));

        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Unpaid bills error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch unpaid bills',
            error: error.message
        });
    }
};


// i want to track the distribution of invoice amounts across different clients to identify which clients contribute the most to our revenue. This will help us focus our efforts on retaining and growing these key accounts. I want to create a new API endpoint that aggregates the total invoice amounts by client and returns this data in a format suitable for visualization on the dashboard.
//  like revenue d= salary - expence- domain-hosting 
export const TotalReceiveAmount = async (req, res) => {
    try {
        const distribution = await Invoice.findAll({
            attributes: ['client_name', [fn('SUM', col('total_amount')), 'totalRevenue']],
            group: ['client_name'], 
            order: [[fn('SUM', col('total_amount')), 'DESC']],
            raw: true
        });

        const formatted = distribution.map(d => ({
            client: d.client_name || 'Unknown',
            revenue: parseFloat(d.totalRevenue) || 0
        }));
        
        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Amount distribution error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch amount distribution',
            error: error.message
        });
    }   
};

export const TotalExpance = async (req, res) => {
    try {    
        const distribution = await Expense.findAll({    
            attributes: ['category', [fn('SUM', col('amount')), 'totalExpense']],
            group: ['category'],  
            order: [[fn('SUM', col('amount')), 'DESC']],
            raw: true
        });
        
        const formatted = distribution.map(d => ({
            category: d.category || 'Other',
            expense: parseFloat(d.totalExpense) || 0
        }));
        
        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Expense distribution error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch expense distribution',
            error: error.message
        });
    }
};

export const TotalSalary = async (req, res) => {
    try {
        const distribution = await Salary.findAll({ 
            attributes: ['employee_name', [fn('SUM', col('amount')), 'totalSalary']],
            group: ['employee_name'],
            order: [[fn('SUM', col('amount')), 'DESC']],
            raw: true
        });
        
        const formatted = distribution.map(d => ({
            employee: d.employee_name || 'Unknown',
            salary: parseFloat(d.totalSalary) || 0
        }));
        
        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Salary distribution error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch salary distribution',
            error: error.message
        });
    }
};

export const TotalHostingCost = async (req, res) => {
    try {
        const distribution = await Hosting.findAll({
            attributes: ['domain_name', [fn('SUM', col('cost')), 'totalCost']],
            group: ['domain_name'],
            order: [[fn('SUM', col('cost')), 'DESC']],
            raw: true
        });
        
        const formatted = distribution.map(d => ({
            domain: d.domain_name || 'Unknown',
            cost: parseFloat(d.totalCost) || 0
        }));
        
        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Domain hosting cost distribution error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch domain hosting cost distribution',
            error: error.message
        });
    }
};

export const TotalDomainCost = async (req, res) => {
    try {    
        const distribution = await DomainManagement.findAll({
            attributes: ['domain_name', [fn('SUM', col('cost')), 'totalCost']],
            group: ['domain_name'],
            order: [[fn('SUM', col('cost')), 'DESC']],
            raw: true
        });
        
        const formatted = distribution.map(d => ({
            domain: d.domain_name || 'Unknown',
            cost: parseFloat(d.totalCost) || 0
        }));
        
        res.status(200).json({
            success: true,
            data: formatted
        });
    } catch (error) {
        console.error('Domain cost distribution error:', error);
        res.status(500).json({
            success: false,
            message: 'Failed to fetch domain cost distribution',
            error: error.message
        });
    }
};

export const TotalProfit = async (req, res) => {    
    try {
        const revenueData = await Invoice.sum('total_amount', { where: { status: { [Op.in]: ['paid', 'Paid'] } } }) || 0;
        const expenseData = await Expense.sum('amount') || 0;
        const salaryData = await Salary.sum('amount') || 0;
        const hostingCost = await Hosting.sum('cost') || 0;
        const domainCost = await DomainManagement.sum('cost') || 0;

        const profit = parseFloat(revenueData) - (parseFloat(expenseData) + parseFloat(salaryData) + parseFloat(hostingCost) + parseFloat(domainCost));

        res.status(200).json({
            success: true,
            data: {
                totalRevenue: parseFloat(revenueData),
                totalExpenses: parseFloat(expenseData),
                totalSalary: parseFloat(salaryData),
                totalHostingCost: parseFloat(hostingCost),
                totalDomainCost: parseFloat(domainCost),
                totalProfit: profit
            }
        });
    } catch (error) {
        console.error('Profit calculation error:', error);  
        res.status(500).json({
            success: false,
            message: 'Failed to calculate profit',
            error: error.message
        });
    }
};