const express = require('express');
const router = express.Router();
const { getDb } = require('../database');
const { authenticate } = require('../middleware/auth');
const { logAction } = require('../utils/audit');

// GET /api/certificates — list all
router.get('/', authenticate, (req, res) => {
  const db = getDb();
  const { search, page = 1, limit = 20 } = req.query;
  const offset = (parseInt(page) - 1) * parseInt(limit);

  let query = `
    SELECT c.*, u.full_name as created_by_name
    FROM certificates c
    JOIN users u ON c.created_by = u.id
  `;
  const params = [];

  if (search) {
    query += ` WHERE (c.certificate_number LIKE ? OR c.client LIKE ? OR c.tag_no LIKE ?
                OR c.serial_no LIKE ? OR c.instrument_type LIKE ?)`;
    const s = `%${search}%`;
    params.push(s, s, s, s, s);
    logAction(req.user.id, req.user.email, req.user.full_name, 'SEARCH', `Searched certificates: "${search}"`, req);
  }

  query += ` ORDER BY c.created_at DESC LIMIT ? OFFSET ?`;
  params.push(parseInt(limit), offset);

  const certs = db.prepare(query).all(...params);

  let countQuery = 'SELECT COUNT(*) as total FROM certificates';
  const countParams = [];
  if (search) {
    countQuery += ` WHERE (certificate_number LIKE ? OR client LIKE ? OR tag_no LIKE ? OR serial_no LIKE ? OR instrument_type LIKE ?)`;
    const s = `%${search}%`;
    countParams.push(s, s, s, s, s);
  }
  const { total } = db.prepare(countQuery).get(...countParams);

  res.json({ certificates: certs, total, page: parseInt(page), limit: parseInt(limit) });
});

// GET /api/certificates/expiring — expiring within 30 days
router.get('/expiring', authenticate, (req, res) => {
  const db = getDb();
  const today = new Date().toISOString().split('T')[0];
  const future = new Date(Date.now() + 30 * 24 * 3600000).toISOString().split('T')[0];

  const certs = db.prepare(`
    SELECT c.*, u.full_name as created_by_name
    FROM certificates c
    JOIN users u ON c.created_by = u.id
    WHERE c.next_due_date BETWEEN ? AND ?
    ORDER BY c.next_due_date ASC
  `).all(today, future);

  res.json({ certificates: certs });
});

// GET /api/certificates/:id
router.get('/:id', authenticate, (req, res) => {
  const db = getDb();
  const cert = db.prepare(`
    SELECT c.*, u.full_name as created_by_name
    FROM certificates c
    JOIN users u ON c.created_by = u.id
    WHERE c.id = ?
  `).get(req.params.id);

  if (!cert) return res.status(404).json({ error: 'Certificate not found' });

  logAction(req.user.id, req.user.email, req.user.full_name, 'VIEW_CERTIFICATE',
    `Viewed certificate ${cert.certificate_number}`, req);

  if (cert.reference_equipment) {
    try { cert.reference_equipment = JSON.parse(cert.reference_equipment); } catch {}
  }

  res.json({ certificate: cert });
});

// POST /api/certificates — create
router.post('/', authenticate, (req, res) => {
  const db = getDb();
  const {
    certificate_number, client, calibration_date, next_due_date,
    end_user, installation_location,
    instrument_type, calibration_location, tag_no, serial_no, manufacturer, size,
    humidity, temperature,
    reference_equipment,
    as_found, instrument_accuracy,
    holding_pressure, set_pressure, first_pop, second_pop,
    calibrated_by, calibrated_by_date, witnessed_by, witnessed_by_date
  } = req.body;

  if (!certificate_number || !client || !calibration_date || !next_due_date)
    return res.status(400).json({ error: 'Certificate number, client, calibration date, and next due date are required' });

  const existing = db.prepare('SELECT id FROM certificates WHERE certificate_number = ?').get(certificate_number);
  if (existing) return res.status(409).json({ error: 'Certificate number already exists' });

  const refEq = reference_equipment ? JSON.stringify(reference_equipment) : null;

  const result = db.prepare(`
    INSERT INTO certificates (
      certificate_number, client, calibration_date, next_due_date,
      end_user, installation_location,
      instrument_type, calibration_location, tag_no, serial_no, manufacturer, size,
      humidity, temperature, reference_equipment,
      as_found, instrument_accuracy,
      holding_pressure, set_pressure, first_pop, second_pop,
      calibrated_by, calibrated_by_date, witnessed_by, witnessed_by_date,
      created_by
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).run(
    certificate_number, client, calibration_date, next_due_date,
    end_user, installation_location,
    instrument_type, calibration_location, tag_no, serial_no, manufacturer, size,
    humidity, temperature, refEq,
    as_found, instrument_accuracy,
    holding_pressure, set_pressure, first_pop, second_pop,
    calibrated_by, calibrated_by_date, witnessed_by, witnessed_by_date,
    req.user.id
  );

  logAction(req.user.id, req.user.email, req.user.full_name, 'CREATE_CERTIFICATE',
    `Created certificate ${certificate_number}`, req);

  // Create expiry notifications for all active users if due within 30 days
  const dueDate = new Date(next_due_date);
  const today = new Date();
  const diff = (dueDate - today) / (1000 * 3600 * 24);
  if (diff <= 30 && diff >= 0) {
    const activeUsers = db.prepare("SELECT id FROM users WHERE status = 'active'").all();
    const insertNotif = db.prepare(`
      INSERT INTO notifications (user_id, certificate_id, message, type)
      VALUES (?, ?, ?, 'expiry')
    `);
    activeUsers.forEach(u => {
      insertNotif.run(u.id, result.lastInsertRowid,
        `Certificate ${certificate_number} for ${client} expires on ${next_due_date}`);
    });
  }

  res.status(201).json({ id: result.lastInsertRowid, message: 'Certificate created successfully' });
});

// PUT /api/certificates/:id — update
router.put('/:id', authenticate, (req, res) => {
  const db = getDb();
  const cert = db.prepare('SELECT * FROM certificates WHERE id = ?').get(req.params.id);
  if (!cert) return res.status(404).json({ error: 'Certificate not found' });

  const {
    certificate_number, client, calibration_date, next_due_date,
    end_user, installation_location,
    instrument_type, calibration_location, tag_no, serial_no, manufacturer, size,
    humidity, temperature, reference_equipment,
    as_found, instrument_accuracy,
    holding_pressure, set_pressure, first_pop, second_pop,
    calibrated_by, calibrated_by_date, witnessed_by, witnessed_by_date
  } = req.body;

  const refEq = reference_equipment ? JSON.stringify(reference_equipment) : null;

  db.prepare(`
    UPDATE certificates SET
      certificate_number=?, client=?, calibration_date=?, next_due_date=?,
      end_user=?, installation_location=?,
      instrument_type=?, calibration_location=?, tag_no=?, serial_no=?, manufacturer=?, size=?,
      humidity=?, temperature=?, reference_equipment=?,
      as_found=?, instrument_accuracy=?,
      holding_pressure=?, set_pressure=?, first_pop=?, second_pop=?,
      calibrated_by=?, calibrated_by_date=?, witnessed_by=?, witnessed_by_date=?,
      updated_at=CURRENT_TIMESTAMP
    WHERE id=?
  `).run(
    certificate_number, client, calibration_date, next_due_date,
    end_user, installation_location,
    instrument_type, calibration_location, tag_no, serial_no, manufacturer, size,
    humidity, temperature, refEq,
    as_found, instrument_accuracy,
    holding_pressure, set_pressure, first_pop, second_pop,
    calibrated_by, calibrated_by_date, witnessed_by, witnessed_by_date,
    req.params.id
  );

  logAction(req.user.id, req.user.email, req.user.full_name, 'UPDATE_CERTIFICATE',
    `Updated certificate ${certificate_number}`, req);

  res.json({ message: 'Certificate updated successfully' });
});

// DELETE /api/certificates/:id — admin only
router.delete('/:id', authenticate, (req, res) => {
  if (req.user.role !== 'admin')
    return res.status(403).json({ error: 'Admin access required' });

  const db = getDb();
  const cert = db.prepare('SELECT certificate_number FROM certificates WHERE id = ?').get(req.params.id);
  if (!cert) return res.status(404).json({ error: 'Certificate not found' });

  db.prepare('DELETE FROM notifications WHERE certificate_id = ?').run(req.params.id);
  db.prepare('DELETE FROM certificates WHERE id = ?').run(req.params.id);

  logAction(req.user.id, req.user.email, req.user.full_name, 'DELETE_CERTIFICATE',
    `Deleted certificate ${cert.certificate_number}`, req);

  res.json({ message: 'Certificate deleted' });
});

module.exports = router;
