const pool = require('../config/db');
const asyncHandler = require('../middlewares/asyncHandler');
const { getLocationFilterFromRequest } = require('../utils/auditFilterHelper');
const { combineObservedDateAndTime } = require('../utils/auditDateTimeHelper');
const {
  getAllAudits,
  getAuditById,
  createAudit,
  updateAudit,
  deleteAudit,
  updateAuditStatus,
  getFormsByAuditId,
  addObservationsToAudit
} = require('../repositories/auditsRepository');
const { insertAuditLog, getAuditLogs, getAuditLogsJoined } = require('../repositories/auditLogsRepository');
const { getPairsAndGapsByAuditId } = require('../repositories/openingClosingMapRepository');

// Get all sat_audits
exports.getAllAudits = asyncHandler(async (req, res) => {
    const filter = getLocationFilterFromRequest(req);
    const rows = await getAllAudits(filter);
    res.status(200).json({ success: true, data: rows });
});

// Get audit by ID
exports.getAuditById = asyncHandler(async (req, res) => {
    const { id } = req.params;
    const audit = await getAuditById(id);
    
    if (!audit) {
        return res.status(404).json({ success: false, message: 'Audit not found' });
    }
    
    res.status(200).json({ success: true, data: audit });
});

// Get forms added to an audit
exports.getAuditForms = asyncHandler(async (req, res) => {
    const { id } = req.params;
    const audit = await getAuditById(id);
    if (!audit) {
        return res.status(404).json({ success: false, message: 'Audit not found' });
    }
    const forms = await getFormsByAuditId(id);
    res.status(200).json({ success: true, data: forms });
});

// Manually set audit status (e.g. "Set to In Progress" from Audit Status page)
exports.setAuditStatus = asyncHandler(async (req, res) => {
    const { id } = req.params;
    const { audit_status_id } = req.body;
    const statusId = audit_status_id != null ? Number(audit_status_id) : null;
    if (statusId === null || statusId < 1 || statusId > 3) {
        return res.status(400).json({ success: false, message: 'audit_status_id must be 1 (Open), 2 (Closed), or 3 (In Progress).' });
    }
    const audit = await getAuditById(id);
    if (!audit) {
        return res.status(404).json({ success: false, message: 'Audit not found' });
    }
    const updated = await updateAuditStatus(id, statusId);
    if (!updated) {
        return res.status(404).json({ success: false, message: 'Audit not found' });
    }
    res.status(200).json({ success: true, message: 'Audit status updated.', data: { audit_id: id, audit_status_id: statusId } });
});

// Get opening-closing observation pairs for an audit (for every opening observation there should be a closing observation)
exports.getOpeningClosingPairs = asyncHandler(async (req, res) => {
    const { id } = req.params;
    const audit = await getAuditById(id);
    if (!audit) {
        return res.status(404).json({ success: false, message: 'Audit not found' });
    }
    let data;
    try {
        data = await getPairsAndGapsByAuditId(id);
    } catch (e) {
        return res.status(500).json({ success: false, message: 'Failed to load opening-closing data. Ensure sat_opening_closing_map table exists.' });
    }
    res.status(200).json({ success: true, data });
});

// Create audit
exports.createAudit = asyncHandler(async (req, res) => {
    const {
        auditor_id,
        audit_name,
        country,
        location,
        work_area,
        observed_at,
        start_time,
        end_time,
        observations,
    } = req.body;
    const sat_coauditors = req.body.sat_coauditors ?? req.body.coauditors;
    const sat_auditees = req.body.sat_auditees ?? req.body.auditees;
    
    if (!auditor_id || !audit_name || !country || !location) {
        return res.status(400).json({ success: false, message: 'auditor_id, audit_name, country, and location are required' });
    }
    if (work_area === undefined || work_area === null || work_area === '') {
        return res.status(400).json({ success: false, message: 'work_area is required' });
    }
    
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();

        const auditData = { auditor_id, audit_name, country, location, work_area };

        // Pass observed_at if provided (manual date)
        if (observed_at) {
            auditData.observed_at = observed_at;
        }
        // Optional start_time, end_time (not mandatory)
        if (start_time !== undefined && start_time !== null && start_time !== '') {
            auditData.start_time = start_time;
        }
        if (end_time !== undefined && end_time !== null && end_time !== '') {
            auditData.end_time = end_time;
        }
        // TIMESTAMP columns: ensure full date+time (defense in depth if client sends HH:MM:SS only)
        if (auditData.start_time) {
            auditData.start_time = combineObservedDateAndTime(auditData.observed_at, auditData.start_time);
        }
        if (auditData.end_time) {
            auditData.end_time = combineObservedDateAndTime(auditData.observed_at, auditData.end_time);
        }
        const auditId = await createAudit(auditData, sat_coauditors, sat_auditees, observations, connection);
        await insertAuditLog(auditId, auditor_id, 'CREATED', { audit_status_id: 1 }, connection);

        await connection.commit();
        res.status(201).json({ success: true, message: 'Audit created successfully', data: { audit_id: auditId } });
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
});

// Update audit
exports.updateAudit = asyncHandler(async (req, res) => {
    const { id } = req.params;
    const { auditor_id, audit_name, location, audit_status_id } = req.body;
    const sat_coauditors = req.body.sat_coauditors ?? req.body.coauditors;
    const sat_auditees = req.body.sat_auditees ?? req.body.auditees;
    
    const updateData = {};
    if (auditor_id !== undefined) updateData.auditor_id = auditor_id;
    if (audit_name !== undefined) updateData.audit_name = audit_name;
    if (location !== undefined) updateData.location = location;
    if (audit_status_id !== undefined) updateData.audit_status_id = audit_status_id;
    
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();
        
        const updated = await updateAudit(id, updateData, sat_coauditors, sat_auditees, connection);
        
        if (!updated) {
            await connection.rollback();
            return res.status(404).json({ success: false, message: 'Audit not found' });
        }
        if (updateData.audit_status_id === 2) {
            const userId = req.user?.userId;
            if (userId) await insertAuditLog(id, userId, 'CLOSED', { audit_status_id: 2 }, connection);
        }

        await connection.commit();
        res.status(200).json({ success: true, message: 'Audit updated successfully' });
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
});

// Get audit logs (optional query: audit_id, action=CREATED|OBSERVATION_ADDED|CLOSED, limit, offset)
exports.getAuditLogs = asyncHandler(async (req, res) => {
    const { audit_id, action, limit, offset } = req.query;
    const rows = await getAuditLogs({ audit_id, action, limit, offset });
    res.status(200).json({ success: true, data: rows });
});

// Get audit log joined with sat_audits (audit details + log entry per row). Optional: audit_id, action, limit, offset
exports.getAuditLogsJoined = asyncHandler(async (req, res) => {
    const filter = getLocationFilterFromRequest(req);
    const { audit_id, action, limit, offset } = req.query;
    const rows = await getAuditLogsJoined({ audit_id, action, limit, offset, ...filter });
    res.status(200).json({ success: true, data: rows });
});

// Add observations to an existing audit
exports.addObservationsToAudit = asyncHandler(async (req, res) => {
    const { id } = req.params;
    const userId = req.user?.userId;
    
    if (!userId) {
        return res.status(401).json({ success: false, message: 'Access denied. User not authenticated.' });
    }
    
    const { observations, opening_sub_id } = req.body;
    
    if (!observations || !Array.isArray(observations) || observations.length === 0) {
        return res.status(400).json({ success: false, message: 'At least one observation is required' });
    }
    
    // Verify audit exists
    const audit = await getAuditById(id);
    if (!audit) {
        return res.status(404).json({ success: false, message: 'Audit not found' });
    }
    
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();
        
        const submissionIds = await addObservationsToAudit(id, observations, opening_sub_id, connection);
        await insertAuditLog(id, userId, 'OBSERVATION_ADDED', {}, connection);
        
        await connection.commit();
        res.status(201).json({ 
            success: true, 
            message: `${observations.length} observation(s) added successfully`,
            data: { submission_ids: submissionIds }
        });
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
});

// Delete audit
exports.deleteAudit = asyncHandler(async (req, res) => {
    const { id } = req.params;
    const deleted = await deleteAudit(id);
    
    if (!deleted) {
        return res.status(404).json({ success: false, message: 'Audit not found' });
    }
    
    res.status(200).json({ success: true, message: 'Audit deleted successfully' });
});

