const express = require('express');
const auth = require('../middleware/auth');
const checkRole = require('../middleware/checkRole');
const UserModel = require('../models/User');
const db = require('../config/database');
const EmailService = require('../services/EmailService');

const router = express.Router();

router.use(auth);

const canWrite = checkRole('DISTRIBUIDOR', 'ADMIN');

const hasAssignmentsTable = () => {
    const row = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='TelemarketingAsignaciones'").get();
    return Boolean(row);
};

const ensureAssignmentsTable = () => {
    if (hasAssignmentsTable()) return;
    db.exec(`
        CREATE TABLE IF NOT EXISTS TelemarketingAsignaciones (
            TelemarketingUserID INTEGER NOT NULL,
            SellerUserID INTEGER NOT NULL,
            CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (TelemarketingUserID, SellerUserID),
            FOREIGN KEY (TelemarketingUserID) REFERENCES Usuarios(UsuarioID) ON DELETE CASCADE,
            FOREIGN KEY (SellerUserID) REFERENCES Usuarios(UsuarioID) ON DELETE CASCADE
        );
        CREATE INDEX IF NOT EXISTS idx_tm_assignments_telemarketing ON TelemarketingAsignaciones(TelemarketingUserID);
        CREATE INDEX IF NOT EXISTS idx_tm_assignments_seller ON TelemarketingAsignaciones(SellerUserID);
    `);
};

router.get('/', (req, res) => {
    try {
        const { q, role, active } = req.query;
        const users = UserModel.getAll({ q, role, active });
        res.json(users);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

router.get('/:id', (req, res) => {
    try {
        const user = UserModel.getById(req.params.id);
        if (!user) return res.status(404).json({ error: 'Usuario no encontrado' });
        res.json(user);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

router.get('/:id/assignments', canWrite, (req, res) => {
    try {
        const user = UserModel.getById(req.params.id);
        if (!user) return res.status(404).json({ error: 'Usuario no encontrado' });
        if (user.role !== 'TELEMARKETING') {
            return res.json([]);
        }
        ensureAssignmentsTable();

        const assignments = db.prepare(`
            SELECT ta.SellerUserID as id, u.Nombre as nombre, u.Rol as rol
            FROM TelemarketingAsignaciones ta
            JOIN Usuarios u ON u.UsuarioID = ta.SellerUserID
            WHERE ta.TelemarketingUserID = ?
            ORDER BY u.Nombre ASC
        `).all(user.id);

        res.json(assignments || []);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

router.put('/:id/assignments', canWrite, (req, res) => {
    try {
        const user = UserModel.getById(req.params.id);
        if (!user) return res.status(404).json({ error: 'Usuario no encontrado' });
        if (user.role !== 'TELEMARKETING') {
            return res.status(400).json({ error: 'Solo se pueden asignar vendedores a telemarketing' });
        }
        ensureAssignmentsTable();

        const sellerIds = Array.isArray(req.body?.sellerIds) ? req.body.sellerIds : [];
        const normalized = sellerIds
            .map((value) => Number(value))
            .filter((value) => Number.isInteger(value) && value > 0);

        const tx = db.transaction(() => {
            db.prepare('DELETE FROM TelemarketingAsignaciones WHERE TelemarketingUserID = ?')
                .run(user.id);

            if (normalized.length > 0) {
                const insert = db.prepare(`
                    INSERT OR IGNORE INTO TelemarketingAsignaciones (TelemarketingUserID, SellerUserID)
                    VALUES (?, ?)
                `);
                normalized.forEach((sellerId) => {
                    insert.run(user.id, sellerId);
                });
            }
        });

        tx();

        const updated = db.prepare(`
            SELECT ta.SellerUserID as id, u.Nombre as nombre, u.Rol as rol
            FROM TelemarketingAsignaciones ta
            JOIN Usuarios u ON u.UsuarioID = ta.SellerUserID
            WHERE ta.TelemarketingUserID = ?
            ORDER BY u.Nombre ASC
        `).all(user.id);

        res.json(updated || []);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

router.post('/', canWrite, async (req, res) => {
    try {
        const result = await UserModel.create(req.body);
        if (result?.error) {
            return res.status(result.status || 400).json({ error: result.error });
        }

        if (result?.email) {
            await EmailService.sendUserWelcome({
                to: result.email,
                name: result.full_name || result.Nombre,
                code: result.seller_code || result.Codigo,
                password: result.temp_password,
                role: result.role || result.Rol
            });
        }
        res.status(201).json(result);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

router.post('/send-verifications', canWrite, async (req, res) => {
    try {
        const includeInactive = Boolean(req.body?.includeInactive);
        const rows = db.prepare(`
            SELECT Email, Nombre, Codigo
            FROM Usuarios
            WHERE Email IS NOT NULL AND Email != ''
            ${includeInactive ? '' : 'AND Activo = 1'}
        `).all();

        let sent = 0;
        for (const row of rows) {
            await EmailService.sendUserVerification({
                to: row.Email,
                name: row.Nombre,
                code: row.Codigo
            });
            sent += 1;
        }

        res.json({ sent });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

router.put('/:id', canWrite, (req, res) => {
    try {
        const updated = UserModel.update(req.params.id, req.body);
        if (!updated) return res.status(404).json({ error: 'Usuario no encontrado' });
        res.json(updated);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

router.patch('/:id/active', canWrite, (req, res) => {
    try {
        const updated = UserModel.toggleActive(req.params.id, req.body?.is_active);
        if (!updated) return res.status(404).json({ error: 'Usuario no encontrado' });
        res.json(updated);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

module.exports = router;
