const bcrypt = require('bcryptjs');
const db = require('../config/db');
const { logAuditFromReq } = require('../middlewares/audit');

function toYmd(date) {
  const y = date.getFullYear();
  const m = String(date.getMonth() + 1).padStart(2, '0');
  const d = String(date.getDate()).padStart(2, '0');
  return `${y}-${m}-${d}`;
}

function parseDateTime(raw) {
  if (raw == null || raw === '') return null;
  const parsed = new Date(raw);
  return Number.isNaN(parsed.getTime()) ? null : parsed;
}

function formatDateTime(raw) {
  const parsed = parseDateTime(raw);
  return parsed ? parsed.toISOString() : null;
}

function parseHistory(raw) {
  if (!raw) return [];
  if (Array.isArray(raw)) return raw;
  if (typeof raw === 'string') {
    try {
      const parsed = JSON.parse(raw);
      return Array.isArray(parsed) ? parsed : [];
    } catch (_) {
      return [];
    }
  }
  return [];
}

function snapshotAttendance(row) {
  return {
    id: row.id,
    user_id: row.user_id,
    attendance_date: row.attendance_date ? toYmd(new Date(row.attendance_date)) : null,
    clock_in_at: formatDateTime(row.clock_in_at),
    clock_out_at: formatDateTime(row.clock_out_at),
    worked_minutes: row.worked_minutes == null ? null : Number(row.worked_minutes),
    clock_in_method: row.clock_in_method ?? null,
    clock_out_method: row.clock_out_method ?? null,
    status: row.status ?? null,
    notes: row.notes ?? null,
    edited_at: formatDateTime(row.edited_at),
    edited_by: row.edited_by ?? null,
    deleted_at: formatDateTime(row.deleted_at),
    deleted_by: row.deleted_by ?? null,
    is_deleted: row.is_deleted ? 1 : 0,
  };
}

function buildChanges(beforeSnapshot, afterSnapshot) {
  const keys = [
    'attendance_date',
    'clock_in_at',
    'clock_out_at',
    'worked_minutes',
    'status',
    'notes',
  ];

  return keys.reduce((changes, key) => {
    if (beforeSnapshot[key] !== afterSnapshot[key]) {
      changes[key] = {
        from: beforeSnapshot[key],
        to: afterSnapshot[key],
      };
    }
    return changes;
  }, {});
}

async function loadAttendanceRow(id, { includeDeleted = false } = {}) {
  const [rows] = await db.query(
    `SELECT al.*, u.username, u.full_name, u.role
     FROM attendance_logs al
     JOIN users u ON u.id = al.user_id
     WHERE al.id = ? ${includeDeleted ? '' : 'AND COALESCE(al.is_deleted, 0) = 0'}`,
    [id]
  );
  return rows[0] || null;
}

function currentWorkedMinutes(clockInAt, clockOutAt, fallback) {
  if (!clockInAt || !clockOutAt) return fallback ?? null;
  const start = parseDateTime(clockInAt);
  const end = parseDateTime(clockOutAt);
  if (!start || !end) return fallback ?? null;
  return Math.max(0, Math.round((end.getTime() - start.getTime()) / 60000));
}

exports.clockIn = async (req, res, next) => {
  try {
    const userId = req.user.id;
    const { pin } = req.body;

    if (!pin || String(pin).trim().length === 0) {
      return res.status(400).json({ error: 'pin is required' });
    }

    const [users] = await db.query(
      'SELECT id, pin_hash, is_active FROM users WHERE id = ?',
      [userId]
    );
    if (users.length === 0 || users[0].is_active !== 1) {
      return res.status(404).json({ error: 'User not found or inactive' });
    }

    const user = users[0];
    if (!user.pin_hash) {
      return res.status(400).json({ error: 'PIN not set for this user' });
    }

    const validPin = await bcrypt.compare(String(pin), user.pin_hash);
    if (!validPin) {
      return res.status(401).json({ error: 'Invalid PIN' });
    }

    const [openRows] = await db.query(
      `SELECT id
       FROM attendance_logs
       WHERE user_id = ? AND status = 'open' AND COALESCE(is_deleted, 0) = 0
       ORDER BY clock_in_at DESC
       LIMIT 1`,
      [userId]
    );

    if (openRows.length > 0) {
      return res.status(409).json({ error: 'User is already clocked in' });
    }

    const today = toYmd(new Date());

    const [result] = await db.query(
      `INSERT INTO attendance_logs
       (user_id, attendance_date, clock_in_at, clock_in_method, clock_in_ip, status, notes)
       VALUES (?, ?, NOW(), 'pin', ?, 'open', NULL)`,
      [userId, today, req.ip || req.connection?.remoteAddress || null]
    );

    const row = await loadAttendanceRow(result.insertId);

    await logAuditFromReq(req, 'clock_in', 'attendance_log', result.insertId, {
      method: 'pin',
    });

    res.status(201).json(row);
  } catch (err) {
    next(err);
  }
};

exports.clockOut = async (req, res, next) => {
  try {
    const userId = req.user.id;
    const { pin } = req.body;

    if (!pin || String(pin).trim().length === 0) {
      return res.status(400).json({ error: 'pin is required' });
    }

    const [users] = await db.query(
      'SELECT id, pin_hash, is_active FROM users WHERE id = ?',
      [userId]
    );
    if (users.length === 0 || users[0].is_active !== 1) {
      return res.status(404).json({ error: 'User not found or inactive' });
    }

    const user = users[0];
    if (!user.pin_hash) {
      return res.status(400).json({ error: 'PIN not set for this user' });
    }

    const validPin = await bcrypt.compare(String(pin), user.pin_hash);
    if (!validPin) {
      return res.status(401).json({ error: 'Invalid PIN' });
    }

    const [openRows] = await db.query(
      `SELECT id
       FROM attendance_logs
       WHERE user_id = ? AND status = 'open' AND COALESCE(is_deleted, 0) = 0
       ORDER BY clock_in_at DESC
       LIMIT 1`,
      [userId]
    );

    if (openRows.length === 0) {
      return res.status(409).json({ error: 'No open clock-in found for this user' });
    }

    const openId = openRows[0].id;

    await db.query(
      `UPDATE attendance_logs
       SET clock_out_at = NOW(),
           clock_out_method = 'pin',
           clock_out_ip = ?,
           status = 'closed',
           worked_minutes = TIMESTAMPDIFF(MINUTE, clock_in_at, NOW())
       WHERE id = ?`,
      [req.ip || req.connection?.remoteAddress || null, openId]
    );

    const row = await loadAttendanceRow(openId);

    await logAuditFromReq(req, 'clock_out', 'attendance_log', openId, {
      method: 'pin',
    });

    res.json(row);
  } catch (err) {
    next(err);
  }
};

exports.manualClockIn = async (req, res, next) => {
  try {
    const userId = req.user.id;

    const [users] = await db.query(
      'SELECT id, is_active FROM users WHERE id = ?',
      [userId]
    );
    if (users.length === 0 || users[0].is_active !== 1) {
      return res.status(404).json({ error: 'User not found or inactive' });
    }

    const [openRows] = await db.query(
      `SELECT id
       FROM attendance_logs
       WHERE user_id = ? AND status = 'open' AND COALESCE(is_deleted, 0) = 0
       ORDER BY clock_in_at DESC
       LIMIT 1`,
      [userId]
    );

    if (openRows.length > 0) {
      return res.status(409).json({ error: 'User is already clocked in' });
    }

    const today = toYmd(new Date());

    const [result] = await db.query(
      `INSERT INTO attendance_logs
       (user_id, attendance_date, clock_in_at, clock_in_method, clock_in_ip, status, notes)
       VALUES (?, ?, NOW(), 'manual', ?, 'open', ?)` ,
      [
        userId,
        today,
        req.ip || req.connection?.remoteAddress || null,
        'Manual clock-in by admin',
      ]
    );

    const row = await loadAttendanceRow(result.insertId);

    await logAuditFromReq(req, 'manual_clock_in', 'attendance_log', result.insertId, {
      method: 'manual',
    });

    res.status(201).json(row);
  } catch (err) {
    next(err);
  }
};

exports.manualClockOut = async (req, res, next) => {
  try {
    const userId = req.user.id;

    const [users] = await db.query(
      'SELECT id, is_active FROM users WHERE id = ?',
      [userId]
    );
    if (users.length === 0 || users[0].is_active !== 1) {
      return res.status(404).json({ error: 'User not found or inactive' });
    }

    const [openRows] = await db.query(
      `SELECT id
       FROM attendance_logs
       WHERE user_id = ? AND status = 'open' AND COALESCE(is_deleted, 0) = 0
       ORDER BY clock_in_at DESC
       LIMIT 1`,
      [userId]
    );

    if (openRows.length === 0) {
      return res.status(409).json({ error: 'No open clock-in found for this user' });
    }

    const openId = openRows[0].id;

    await db.query(
      `UPDATE attendance_logs
       SET clock_out_at = NOW(),
           clock_out_method = 'manual',
           clock_out_ip = ?,
           status = 'closed',
           worked_minutes = TIMESTAMPDIFF(MINUTE, clock_in_at, NOW()),
           notes = COALESCE(notes, 'Manual clock-out by admin')
       WHERE id = ?`,
      [req.ip || req.connection?.remoteAddress || null, openId]
    );

    const row = await loadAttendanceRow(openId);

    await logAuditFromReq(req, 'manual_clock_out', 'attendance_log', openId, {
      method: 'manual',
    });

    res.json(row);
  } catch (err) {
    next(err);
  }
};

exports.getMyAttendance = async (req, res, next) => {
  try {
    const userId = req.user.id;
    const { from_date, to_date } = req.query;

    let sql = `SELECT al.*, u.username, u.full_name, u.role
               FROM attendance_logs al
               JOIN users u ON u.id = al.user_id
               WHERE al.user_id = ? AND COALESCE(al.is_deleted, 0) = 0`;
    const params = [userId];

    if (from_date) {
      sql += ' AND al.attendance_date >= ?';
      params.push(from_date);
    }
    if (to_date) {
      sql += ' AND al.attendance_date <= ?';
      params.push(to_date);
    }

    sql += ' ORDER BY al.clock_in_at DESC, al.id DESC';

    const [rows] = await db.query(sql, params);
    res.json(rows.map((row) => ({ ...row, edit_history: parseHistory(row.edit_history) })));
  } catch (err) {
    next(err);
  }
};

exports.getAttendanceReport = async (req, res, next) => {
  try {
    const { from_date, to_date, user_id } = req.query;

    const conditions = ['COALESCE(al.is_deleted, 0) = 0'];
    const params = [];

    if (user_id) {
      conditions.push('al.user_id = ?');
      params.push(parseInt(user_id, 10));
    }
    if (from_date) {
      conditions.push('al.attendance_date >= ?');
      params.push(from_date);
    }
    if (to_date) {
      conditions.push('al.attendance_date <= ?');
      params.push(to_date);
    }

    const where = `WHERE ${conditions.join(' AND ')}`;

    const [dailyRows] = await db.query(
      `SELECT
         MIN(al.id) AS id,
         al.user_id,
         u.full_name,
         u.username,
         u.role,
         al.attendance_date AS date,
         COUNT(*) AS punches,
         MIN(al.clock_in_at) AS first_in,
         MAX(COALESCE(al.clock_out_at, NOW())) AS last_out,
         SUM(COALESCE(al.worked_minutes, TIMESTAMPDIFF(MINUTE, al.clock_in_at, COALESCE(al.clock_out_at, NOW())))) AS total_mins,
         MAX(al.edited_at) AS edited_at
       FROM attendance_logs al
       JOIN users u ON u.id = al.user_id
       ${where}
       GROUP BY al.user_id, u.full_name, u.username, u.role, al.attendance_date
       ORDER BY al.attendance_date DESC, u.full_name ASC`,
      params
    );

    const [summaryRows] = await db.query(
      `SELECT
         al.user_id,
         u.full_name,
         u.username,
         u.role,
         COUNT(*) AS total_punches,
         COUNT(DISTINCT al.attendance_date) AS days_present,
         SUM(COALESCE(al.worked_minutes, TIMESTAMPDIFF(MINUTE, al.clock_in_at, COALESCE(al.clock_out_at, NOW())))) AS total_mins,
         ROUND(AVG(COALESCE(al.worked_minutes, TIMESTAMPDIFF(MINUTE, al.clock_in_at, COALESCE(al.clock_out_at, NOW())))), 0) AS avg_shift_mins
       FROM attendance_logs al
       JOIN users u ON u.id = al.user_id
       ${where}
       GROUP BY al.user_id, u.full_name, u.username, u.role
       ORDER BY total_mins DESC`,
      params
    );

    res.json({
      daily: dailyRows,
      summary: summaryRows,
    });
  } catch (err) {
    next(err);
  }
};

exports.getPunchHistory = async (req, res, next) => {
  try {
    const userId = parseInt(req.query.user_id, 10);
    const attendanceDate = req.query.date;

    if (!userId || !attendanceDate) {
      return res.status(400).json({ error: 'user_id and date are required' });
    }

    const [rows] = await db.query(
      `SELECT al.*, u.username, u.full_name, u.role
       FROM attendance_logs al
       JOIN users u ON u.id = al.user_id
       WHERE al.user_id = ?
         AND DATE(al.attendance_date) = DATE(?)
         AND COALESCE(al.is_deleted, 0) = 0
       ORDER BY al.clock_in_at ASC, al.id ASC`,
      [userId, attendanceDate]
    );

    res.json(rows.map((row) => ({ ...row, edit_history: parseHistory(row.edit_history) })));
  } catch (err) {
    next(err);
  }
};

exports.getEntryHistory = async (req, res, next) => {
  try {
    const id = parseInt(req.params.id, 10);
    const row = await loadAttendanceRow(id, { includeDeleted: true });

    if (!row) {
      return res.status(404).json({ error: 'Attendance entry not found' });
    }

    res.json({
      id: row.id,
      edited_at: row.edited_at,
      edited_by: row.edited_by,
      history: parseHistory(row.edit_history),
      current: snapshotAttendance(row),
    });
  } catch (err) {
    next(err);
  }
};

exports.updateEntry = async (req, res, next) => {
  try {
    const id = parseInt(req.params.id, 10);
    const row = await loadAttendanceRow(id);

    if (!row) {
      return res.status(404).json({ error: 'Attendance entry not found' });
    }

    const beforeSnapshot = snapshotAttendance(row);
    const history = parseHistory(row.edit_history);
    const [editorRows] = await db.query(
      'SELECT id, username, full_name, role FROM users WHERE id = ?',
      [req.user.id]
    );
    const editor = editorRows[0] || {};
    const editorName = editor.full_name || editor.username || req.user.username || `User ${req.user.id}`;

    const nextAttendanceDate = req.body.attendance_date || beforeSnapshot.attendance_date;
    const nextClockInAt = req.body.clock_in_at ? formatDateTime(req.body.clock_in_at) : beforeSnapshot.clock_in_at;
    const nextClockOutAt = req.body.clock_out_at ? formatDateTime(req.body.clock_out_at) : beforeSnapshot.clock_out_at;
    const nextNotes = Object.prototype.hasOwnProperty.call(req.body, 'notes') ? req.body.notes : beforeSnapshot.notes;
    const nextWorkedMinutes = currentWorkedMinutes(nextClockInAt, nextClockOutAt, beforeSnapshot.worked_minutes);
    const nextStatus = nextClockOutAt ? 'closed' : 'open';

    const [result] = await db.query(
      `UPDATE attendance_logs
       SET attendance_date = ?,
           clock_in_at = ?,
           clock_out_at = ?,
           worked_minutes = ?,
           status = ?,
           notes = ?,
           edited_at = NOW(),
           edited_by = ?,
           edit_history = ?
       WHERE id = ? AND COALESCE(is_deleted, 0) = 0`,
      [
        nextAttendanceDate,
        nextClockInAt,
        nextClockOutAt,
        nextWorkedMinutes,
        nextStatus,
        nextNotes,
        req.user.id,
        JSON.stringify([
          ...history,
          {
            edited_at: new Date().toISOString(),
            edited_by: req.user.id,
            edited_by_name: editorName,
            edited_by_role: req.user.role,
            changes: buildChanges(beforeSnapshot, {
              ...beforeSnapshot,
              attendance_date: nextAttendanceDate,
              clock_in_at: nextClockInAt,
              clock_out_at: nextClockOutAt,
              worked_minutes: nextWorkedMinutes,
              status: nextStatus,
              notes: nextNotes,
            }),
          },
        ]),
        id,
      ]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'Attendance entry not found' });
    }

    const updatedRow = await loadAttendanceRow(id);
    const afterSnapshot = snapshotAttendance(updatedRow);

    await logAuditFromReq(req, 'update', 'attendance_log', id, {
      before: beforeSnapshot,
      after: afterSnapshot,
      changes: buildChanges(beforeSnapshot, afterSnapshot),
    });

    res.json({ ...updatedRow, edit_history: parseHistory(updatedRow.edit_history) });
  } catch (err) {
    next(err);
  }
};

exports.deleteEntry = async (req, res, next) => {
  try {
    const id = parseInt(req.params.id, 10);
    const row = await loadAttendanceRow(id);

    if (!row) {
      return res.status(404).json({ error: 'Attendance entry not found' });
    }

    const beforeSnapshot = snapshotAttendance(row);

    const [result] = await db.query(
      `UPDATE attendance_logs
       SET is_deleted = 1,
           deleted_at = NOW(),
           deleted_by = ?,
           status = 'invalid'
       WHERE id = ? AND COALESCE(is_deleted, 0) = 0`,
      [req.user.id, id]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'Attendance entry not found' });
    }

    await logAuditFromReq(req, 'delete', 'attendance_log', id, {
      soft_delete: true,
      before: beforeSnapshot,
    });

    res.json({ message: 'Attendance entry deleted' });
  } catch (err) {
    next(err);
  }
};