const express = require('express');
const router = express.Router();
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const { requireAdmin } = require('../middleware/auth');
const { db,
    getAllClients, getClientById, addClient, updateClient, deleteClient,
    getAllProducts, getProductById, addProduct, updateProduct, deleteProduct,
    getAllOrders, getOrderById, updateOrderStatus, deleteOrder,
    getAllAdmins, addAdmin, removeAdmin, isAdminInDb,
    createMessage
} = require('../database');
const {
    sendQuotationEmail,
    sendDispatchEmail,
    sendDeliveredEmail
} = require('../services/email');

// Ensure upload directory exists
const uploadDir = path.join(__dirname, '..', 'public', 'uploads', 'products');
if (!fs.existsSync(uploadDir)) {
    fs.mkdirSync(uploadDir, { recursive: true });
}

// Multer configuration for file uploads
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
        cb(null, uploadDir);
    },
    filename: (req, file, cb) => {
        const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
        const ext = path.extname(file.originalname);
        const name = path.basename(file.originalname, ext);
        cb(null, `${timestamp}_${name}${ext}`);
    }
});

const upload = multer({
    storage,
    limits: { fileSize: 5 * 1024 * 1024 }, // 5MB
    fileFilter: (req, file, cb) => {
        const allowed = /\.(png|jpg|jpeg|gif)$/i;
        if (allowed.test(path.extname(file.originalname))) {
            cb(null, true);
        } else {
            cb(new Error('Only image files are allowed'));
        }
    }
});

// Product categories
const ALL_CATEGORIES = [
    "Winches", "Cable Drum Trailers", "Rollers", "Cable Drum Lifting Jacks", "Cable Locators", "Reeling Machine",
    "Cable Pulling Grips & Swivel Link", "Duct Rods", "Hydraulic Cutting and Crimping Tools",
    "Warning Tapes", "Manhole", "Ropes", "Duct", "Electrical", "Solar", "Pipes", "Optical Fibre Cables",
    "Optical Fiber Connectors", "Optical Fiber Adapters", "Optical Fiber Consumable",
    "Optical Fiber Instruments", "Optical Distribution Frames", "Optical Fiber Patch Cord",
    "Optical Fibre Tools", "Cabinets", "Cable Joint Products", "Cable & Wires", "Connectors",
    "Distribution Boxes", "Ducts Accessories", "Manhole Accessories", "Marking & Protection", "Earthing Hardware",
    "Miscellaneous", "Poles & Accessories", "Tapes", "Terminal Blocks", "Test & Measurement",
    "Telecom Tools", "Other Products"
];

// Helper to parse product
function parseProduct(product) {
    let options = {};
    if (product.product_options) {
        try {
            options = JSON.parse(product.product_options);
            // Always clean options on read to stop "parasitic" growth
            options = cleanOptions(options);
        } catch (e) {
            options = {};
        }
    }
    return {
        id: product.id,
        name: product.product_name,
        category: product.category,
        options: options,
        rate: product.product_rate,
        stock: product.stock_status || 'out_of_stock',
        image: product.image_filename
    };
}

// Admin Dashboard
// Helper to clean options (Deprecated here, moved to database.js but keeping for read safety)
function cleanOptions(optionsObj) {
    if (!optionsObj || typeof optionsObj !== 'object') return {};
    const cleaned = {};
    for (const [key, value] of Object.entries(optionsObj)) {
        if (key.includes('{') || key.includes('}') || key.includes('[') || key.includes(']') || key.includes(':')) {
            continue;
        }
        cleaned[key] = value;
    }
    return cleaned;
}

router.get('/admin', requireAdmin, (req, res) => {
    const oneWeekAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString();

    // Get dashboard statistics
    const ordersThisWeek = db.prepare("SELECT COUNT(*) as count FROM orders WHERE datetime(created_at) >= ?").get(oneWeekAgo).count;
    const unplacedOrders = db.prepare("SELECT COUNT(*) as count FROM orders WHERE status IN ('inquiry received', 'quote sent')").get().count;
    const deliveredCount = db.prepare("SELECT COUNT(*) as count FROM orders WHERE status = 'delivered'").get().count;
    const inStockProducts = db.prepare("SELECT COUNT(*) as count FROM products WHERE stock_status = 'in_stock'").get().count;
    const totalClients = db.prepare("SELECT COUNT(*) as count FROM users").get().count;
    const dispatchedCount = db.prepare("SELECT COUNT(*) as count FROM orders WHERE status = 'dispatched'").get().count;
    const pendingOrders = db.prepare("SELECT COUNT(*) as count FROM orders WHERE status IN ('dispatched', 'order placed')").get().count;

    const mostInquiredRow = db.prepare(`
        SELECT product_name, COUNT(*) as inquiries
        FROM orders
        WHERE status IN ('inquiry received', 'received')
        GROUP BY product_name
        ORDER BY inquiries DESC LIMIT 1
    `).get();
    const mostInquiredItem = mostInquiredRow ? mostInquiredRow.product_name : 'No Inquiries Yet';

    const mostActiveRow = db.prepare(`
        SELECT user_email, COUNT(*) as order_count
        FROM orders
        GROUP BY user_email
        ORDER BY order_count DESC LIMIT 1
    `).get();
    const mostActiveClient = mostActiveRow ? mostActiveRow.user_email : 'No Orders Yet';

    const paymentStatusCount = db.prepare(`
        SELECT COUNT(*) as count FROM orders
        WHERE payment_status = 'unpaid' AND status IN ('order placed', 'dispatched', 'delivered')
    `).get().count;

    res.render('admin', {
        section: 'dashboard',
        orders_this_week: ordersThisWeek,
        unplaced_orders: unplacedOrders,
        delivered_count: deliveredCount,
        in_stock_products: inStockProducts,
        total_clients: totalClients,
        dispatched_count: dispatchedCount,
        pending_orders: pendingOrders,
        most_inquired_item: mostInquiredItem,
        most_active_client: mostActiveClient,
        payment_status_count: paymentStatusCount
    });
});
// Manage Clients
router.get('/admin/manage-clients', requireAdmin, (req, res) => {
    const clients = getAllClients();
    let editClient = null;

    if (req.query.edit) {
        editClient = getClientById(req.query.edit);
        if (!editClient) {
            req.flash('Client not found');
            return res.redirect('/admin/manage-clients');
        }
    }

    res.render('admin', {
        section: 'manage-clients',
        clients,
        edit_client: editClient
    });
});

router.post('/admin/manage-clients', requireAdmin, (req, res) => {
    const { client_id, email, client_name, phone, address, company } = req.body;

    if (!email || !client_name || !phone || !company) {
        req.flash('Please fill in all required fields');
        return res.redirect('/admin/manage-clients');
    }

    if (!email.includes('@')) {
        req.flash('Please enter a valid email address');
        return res.redirect('/admin/manage-clients');
    }

    if (client_id) {
        // Update existing client
        if (updateClient(client_id, email, client_name, phone, address || '', company)) {
            req.flash(`Client ${client_name} updated successfully!`);
        } else {
            req.flash('Email already exists for another client. Please use a different email.');
        }
    } else {
        // Add new client
        if (addClient(email, client_name, phone, address || '', company)) {
            req.flash(`Client ${client_name} added successfully!`);
        } else {
            req.flash('Email already exists. Please use a different email.');
        }
    }

    res.redirect('/admin/manage-clients');
});

router.get('/admin/edit-client/:client_id', requireAdmin, (req, res) => {
    res.redirect(`/admin/manage-clients?edit=${req.params.client_id}`);
});

router.post('/admin/delete-client/:client_id', requireAdmin, (req, res) => {
    deleteClient(req.params.client_id);
    req.flash('Client deleted successfully!');
    res.redirect('/admin/manage-clients');
});

// Manage Products
router.get('/admin/manage-products', requireAdmin, (req, res) => {
    const products = getAllProducts().map(parseProduct);

    // Group by category
    const productsByCategory = {};
    ALL_CATEGORIES.forEach(cat => {
        productsByCategory[cat] = [];
    });

    products.forEach(product => {
        let matched = false;
        for (const cat of ALL_CATEGORIES) {
            if (product.category.toLowerCase().trim() === cat.toLowerCase().trim()) {
                productsByCategory[cat].push(product);
                matched = true;
                break;
            }
        }
        if (!matched) {
            productsByCategory['Other Products'].push(product);
        }
    });

    res.render('admin', {
        section: 'manage-products',
        all_categories: ALL_CATEGORIES,
        products_by_category: productsByCategory
    });
});

router.post('/admin/add-product', requireAdmin, upload.single('product_image'), (req, res) => {
    try {
        const { product_name, category, product_rate, stock_status } = req.body;

        // Build options object
        let options = {};

        // 1. Primary source: JSON backup (the only source now)
        if (req.body.product_options_json) {
            try {
                options = JSON.parse(req.body.product_options_json);
            } catch (e) {
                console.error('Error parsing backup JSON:', e);
            }
        }

        const optionsJson = Object.keys(options).length > 0 ? JSON.stringify(options) : null;
        console.log('ULTIMATE CLEANING LOG (Add):', optionsJson);
        const imageFilename = req.file ? req.file.filename : null;

        addProduct(product_name, category, optionsJson, product_rate || null, stock_status, imageFilename);

        req.flash(`Product '${product_name}' added successfully to ${category} category!`);
    } catch (err) {
        console.error('Error adding product:', err);
        req.flash(`Error adding product: ${err.message}`);
    }

    res.redirect('/admin/manage-products');
});

router.get('/admin/edit-product/:product_id', requireAdmin, (req, res) => {
    const product = getProductById(req.params.product_id);

    if (!product) {
        req.flash('Product not found');
        return res.redirect('/admin/manage-products');
    }

    let editOptions = {};
    if (product.product_options) {
        try {
            editOptions = JSON.parse(product.product_options);
            // Clean the data on read to fix existing corruption
            editOptions = cleanOptions(editOptions);
        } catch (e) {
            editOptions = {};
        }
    }

    res.render('admin', {
        section: 'edit-product',
        edit_product: product,
        edit_options: editOptions
    });
});

router.post('/admin/edit-product/:product_id', requireAdmin, upload.single('product_image'), (req, res) => {
    try {
        const productId = req.params.product_id;
        const { product_name, category, product_rate, stock_status } = req.body;

        // Build options object
        let options = {};

        // 1. Primary source: JSON backup (the only source now)
        if (req.body.product_options_json) {
            try {
                options = JSON.parse(req.body.product_options_json);
            } catch (e) {
                console.error('Error parsing backup JSON:', e);
            }
        }

        const optionsJson = Object.keys(options).length > 0 ? JSON.stringify(options) : null;
        console.log('ULTIMATE CLEANING LOG (Edit):', optionsJson);

        // Handle image
        if (req.file) {
            // Delete old image
            const oldProduct = getProductById(productId);
            if (oldProduct && oldProduct.image_filename) {
                const oldPath = path.join(uploadDir, oldProduct.image_filename);
                if (fs.existsSync(oldPath)) {
                    fs.unlinkSync(oldPath);
                }
            }
            updateProduct(productId, product_name, category, optionsJson, product_rate, stock_status, req.file.filename);
        } else {
            updateProduct(productId, product_name, category, optionsJson, product_rate, stock_status);
        }

        req.flash(`Product '${product_name}' updated successfully!`);
    } catch (err) {
        console.error('Error updating product:', err);
        req.flash(`Error updating product: ${err.message}`);
    }

    res.redirect('/admin/manage-products');
});

router.post('/admin/delete-product/:product_id', requireAdmin, (req, res) => {
    try {
        const product = getProductById(req.params.product_id);
        if (product) {
            // Delete image file
            if (product.image_filename) {
                const filePath = path.join(uploadDir, product.image_filename);
                if (fs.existsSync(filePath)) {
                    fs.unlinkSync(filePath);
                }
            }
            deleteProduct(req.params.product_id);
            req.flash(`Product '${product.product_name}' deleted successfully!`);
        } else {
            req.flash('Product not found');
        }
    } catch (err) {
        req.flash(`Error deleting product: ${err.message}`);
    }

    res.redirect('/admin/manage-products');
});

// Client Orders
router.get('/admin/client-orders', requireAdmin, (req, res) => {
    const orders = getAllOrders();
    res.render('admin', {
        section: 'client-orders',
        orders
    });
});

router.post('/admin/delete-order/:order_id', requireAdmin, (req, res) => {
    deleteOrder(req.params.order_id);
    req.flash('Order deleted successfully!', 'success');
    res.redirect('/admin/client-orders');
});

// Send Quotation
router.post('/admin/send-quotation', requireAdmin, upload.single('attachment'), async (req, res) => {
    const { id: orderId, user_email: clientEmail, message } = req.body;

    if (!clientEmail || !orderId) {
        req.flash('Missing recipient information.', 'danger');
        return res.redirect('/admin/client-orders');
    }

    const order = getOrderById(orderId);
    const orderName = order ? order.product_name : 'Unknown Product';
    const orderQuantity = order ? order.quantity : 'N/A';

    let attachmentName = null;
    let attachmentData = null;

    if (req.file) {
        attachmentName = req.file.filename;
        const fileContent = fs.readFileSync(req.file.path);
        attachmentData = {
            content: fileContent.toString('base64'),
            filename: req.file.originalname,
            type: req.file.mimetype
        };
    }

    try {
        await sendQuotationEmail(clientEmail, orderId, orderName, orderQuantity, message, attachmentData);

        // Save message to database
        createMessage(
            orderId,
            clientEmail,
            `Quotation for Order #${orderId}`,
            message,
            attachmentName,
            orderName,
            orderQuantity
        );

        // Update order status
        updateOrderStatus(orderId, 'quote sent');

        req.flash(`Quotation sent successfully to ${clientEmail}`, 'success');
    } catch (err) {
        console.error('SendGrid quotation error:', err);
        req.flash('Failed to send quotation email.', 'danger');
    }

    res.redirect('/admin/client-orders');
});

// Dispatch Order
router.post('/admin/dispatch-order/:order_id', requireAdmin, async (req, res) => {
    const orderId = req.params.order_id;
    const order = getOrderById(orderId);

    if (!order) {
        req.flash('Order not found', 'danger');
        return res.redirect('/admin/client-orders');
    }

    try {
        await sendDispatchEmail(order.user_email, orderId, order.product_name, order.quantity);

        // Save message
        createMessage(
            orderId,
            order.user_email,
            `Order Dispatched – Order #${orderId}`,
            `Your order #${orderId} has been dispatched.`,
            null,
            order.product_name,
            order.quantity
        );

        updateOrderStatus(orderId, 'dispatched');

        req.flash(`Dispatch notification sent to ${order.user_email} and order marked as dispatched`, 'success');
    } catch (err) {
        console.error('SendGrid dispatch error:', err);
        req.flash('Failed to send dispatch notification.', 'danger');
    }

    res.redirect('/admin/client-orders');
});

// Mark Delivered
router.post('/admin/mark-delivered/:order_id', requireAdmin, async (req, res) => {
    const orderId = req.params.order_id;
    const order = getOrderById(orderId);

    if (!order) {
        req.flash('Order not found', 'danger');
        return res.redirect('/admin/client-orders');
    }

    try {
        await sendDeliveredEmail(order.user_email, orderId, order.product_name, order.quantity);

        // Save message
        createMessage(
            orderId,
            order.user_email,
            `Order Delivered – Order #${orderId}`,
            `Your order #${orderId} has been delivered successfully.`,
            null,
            order.product_name,
            order.quantity
        );

        updateOrderStatus(orderId, 'delivered');

        req.flash(`Delivery confirmation sent to ${order.user_email} and order marked as delivered`, 'success');
    } catch (err) {
        console.error('SendGrid delivery error:', err);
        req.flash('Failed to send delivery confirmation.', 'danger');
    }

    res.redirect('/admin/client-orders');
});

// Admin List
router.get('/admin_list', requireAdmin, (req, res) => {
    const admins = getAllAdmins();
    res.render('admin_list', {
        admins,
        main_admin_email: process.env.ADMIN_EMAIL
    });
});

router.post('/admin/add-admin', requireAdmin, (req, res) => {
    const adminEmail = (req.body.admin_email || '').trim().toLowerCase();

    if (!adminEmail || !adminEmail.includes('@')) {
        req.flash('Please enter a valid email address');
        return res.redirect('/admin_list');
    }

    if (isAdminInDb(adminEmail)) {
        req.flash('User is already an admin');
        return res.redirect('/admin_list');
    }

    if (addAdmin(adminEmail)) {
        req.flash(`Admin ${adminEmail} added successfully!`);
    } else {
        req.flash('Error adding admin. Please try again.');
    }

    res.redirect('/admin_list');
});

router.post('/admin/remove-admin/:admin_id', requireAdmin, (req, res) => {
    const result = removeAdmin(req.params.admin_id);
    req.flash(result.message);
    res.redirect('/admin_list');
});

module.exports = router;
