/**
 * Transport Module Controller
 */
const express = require('express');
const router = express.Router();
const path = require('path');
const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const db = new Database(dbPath);
db.pragma('foreign_keys = ON');

// Auth middleware
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

const adminOnly = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal', 'transport_manager', 'admin'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Restricted access' });
  }
  next();
};

// ============ VEHICLES ============

/**
 * @openapi
 * /api/transport/vehicles:
 *   get:
 *     summary: List vehicles
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Vehicle list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create vehicle
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - registration_number
 *               - model
 *               - capacity
 *             properties:
 *               registration_number: { type: string }
 *               model: { type: string }
 *               capacity: { type: integer }
 *               vehicle_type: { type: string }
 *               fuel_type: { type: string }
 *               description: { type: string }
 *     responses:
 *       200:
 *         description: Created vehicle
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 */
router.get('/vehicles', auth, adminOnly, (req, res) => {
  try {
    const vehicles = db.prepare(`
      SELECT v.*, v.registration_number as vehicle_number, v.model as vehicle_model,
             d.first_name || ' ' || d.last_name as driver_name
      FROM vehicles v
      LEFT JOIN vehicle_routes vr ON v.id = vr.vehicle_id AND vr.is_active = 1 AND vr.is_deleted = 0
      LEFT JOIN users d ON vr.driver_id = d.id
      WHERE v.is_deleted = 0
    `).all();
    res.json(vehicles);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/vehicles', auth, adminOnly, (req, res) => {
  const { registration_number, model, capacity, vehicle_type, fuel_type, description } = req.body;
  const uid = uuidv4();
  try {
    const result = db.prepare(`
      INSERT INTO vehicles (uid, registration_number, model, capacity, vehicle_type, fuel_type, status, description, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, 'active', ?, 'pending')
    `).run(uid, registration_number, model, capacity, vehicle_type, fuel_type, description);
    res.json({ id: result.lastInsertRowid, uid });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// ============ ROUTES ============

/**
 * @openapi
 * /api/transport/routes:
 *   get:
 *     summary: List transport routes
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Route list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create transport route
 *     description: |
 *       Creates a route and, when a vehicle or driver is supplied,
 *       links them in `vehicle_routes` within the same DB transaction.
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - name
 *             properties:
 *               name: { type: string }
 *               code: { type: string }
 *               start_point: { type: string }
 *               end_point: { type: string }
 *               distance_km: { type: number }
 *               estimated_time_minutes: { type: integer }
 *               description: { type: string }
 *               vehicle_id: { type: integer }
 *               driver_id: { type: integer }
 *     responses:
 *       200:
 *         description: Created route
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 */
router.get('/routes', auth, (req, res) => {
  try {
    const routes = db.prepare(`
      SELECT r.*, r.name as route_title, r.start_point as start_place,
             r.end_point as end_place, r.distance_km as distance,
             v.registration_number as vehicle_number,
             d.first_name || ' ' || d.last_name as driver_name,
        (SELECT COUNT(*) FROM transport_allocations WHERE route_id = r.id AND is_active = 1 AND is_deleted = 0) as student_count
      FROM routes r
      LEFT JOIN vehicle_routes vr ON r.id = vr.route_id AND vr.is_active = 1 AND vr.is_deleted = 0
      LEFT JOIN vehicles v ON vr.vehicle_id = v.id
      LEFT JOIN users d ON vr.driver_id = d.id
      WHERE r.is_deleted = 0
    `).all();
    res.json(routes);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/routes', auth, adminOnly, (req, res) => {
  const { name, code, start_point, end_point, distance_km, estimated_time_minutes, description, vehicle_id, driver_id } = req.body;
  const uid = uuidv4();
  
  const insertRoute = db.transaction(() => {
    const result = db.prepare(`
      INSERT INTO routes (uid, name, code, start_point, end_point, distance_km, estimated_time_minutes, status, description, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, 'active', ?, 'pending')
    `).run(uid, name, code, start_point, end_point, distance_km, estimated_time_minutes, description);
    
    const routeId = result.lastInsertRowid;
    
    if (vehicle_id || driver_id) {
      db.prepare(`
        INSERT INTO vehicle_routes (uid, vehicle_id, route_id, driver_id, start_time, end_time, is_active, sync_status)
        VALUES (?, ?, ?, ?, '07:00', '16:00', 1, 'pending')
      `).run(uuidv4(), vehicle_id ? parseInt(vehicle_id) : null, routeId, driver_id ? parseInt(driver_id) : null);
    }
    
    return { id: routeId, uid };
  });

  try {
    const outcome = insertRoute();
    res.json(outcome);
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// Assign vehicle and driver to a route
/**
 * @openapi
 * /api/transport/routes/{id}/assign:
 *   post:
 *     summary: Assign vehicle and driver to a route
 *     description: |
 *       Creates or updates the active `vehicle_routes` row for the
 *       route. Pass `null` (or omit) to clear vehicle/driver.
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               vehicle_id: { type: integer }
 *               driver_id: { type: integer }
 *     responses:
 *       200:
 *         description: Assignment saved
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 *       404: { description: Route not found }
 */
router.post('/routes/:id/assign', auth, adminOnly, (req, res) => {
  const routeId = parseInt(req.params.id);
  const { vehicle_id, driver_id } = req.body;
  try {
    const existing = db.prepare(`
      SELECT id FROM vehicle_routes 
      WHERE route_id = ? AND is_active = 1 AND is_deleted = 0
    `).get(routeId);

    if (existing) {
      db.prepare(`
        UPDATE vehicle_routes
        SET vehicle_id = ?, driver_id = ?, sync_status = 'pending', updated_at = datetime('now', 'localtime')
        WHERE id = ?
      `).run(vehicle_id ? parseInt(vehicle_id) : null, driver_id ? parseInt(driver_id) : null, existing.id);
    } else {
      db.prepare(`
        INSERT INTO vehicle_routes (uid, vehicle_id, route_id, driver_id, start_time, end_time, is_active, sync_status)
        VALUES (?, ?, ?, ?, '07:00', '16:00', 1, 'pending')
      `).run(uuidv4(), vehicle_id ? parseInt(vehicle_id) : null, routeId, driver_id ? parseInt(driver_id) : null);
    }
    res.json({ success: true });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// ============ ALLOCATIONS ============

/**
 * @openapi
 * /api/transport/allocations:
 *   get:
 *     summary: List transport allocations
 *     description: Returns active student-to-route transport allocations.
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Allocation list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Allocate student to route
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - student_id
 *               - route_id
 *             properties:
 *               student_id: { type: integer }
 *               route_id: { type: integer }
 *               pickup_point_id: { type: integer }
 *               vehicle_route_id: { type: integer }
 *               fee_amount: { type: number }
 *               effective_from: { type: string, format: date }
 *               effective_to: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Allocation created
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 */
router.get('/allocations', auth, (req, res) => {
  try {
    const allocations = db.prepare(`
      SELECT ta.*, u.first_name, u.last_name, r.name as route_title,
             p.name as stop_name, v.registration_number as vehicle_number,
             CASE WHEN ta.is_active = 1 THEN 'active' ELSE 'inactive' END as status
      FROM transport_allocations ta
      JOIN users u ON ta.student_id = u.id
      JOIN routes r ON ta.route_id = r.id
      LEFT JOIN pickup_points p ON ta.pickup_point_id = p.id
      LEFT JOIN vehicle_routes vr ON ta.vehicle_route_id = vr.id
      LEFT JOIN vehicles v ON vr.vehicle_id = v.id
      WHERE ta.is_deleted = 0
    `).all();
    res.json(allocations);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/allocations', auth, adminOnly, (req, res) => {
  const { student_id, route_id, pickup_point_id, vehicle_route_id, fee_amount, effective_from, effective_to } = req.body;
  try {
    const result = db.prepare(`
      INSERT INTO transport_allocations (uid, student_id, route_id, pickup_point_id, vehicle_route_id, fee_amount, effective_from, effective_to, is_active, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1, 'pending')
    `).run(uuidv4(), student_id, route_id, pickup_point_id || null, vehicle_route_id || null, fee_amount, effective_from || null, effective_to || null);
    res.json({ id: result.lastInsertRowid });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// Deallocate passenger
/**
 * @openapi
 * /api/transport/allocations/{id}:
 *   delete:
 *     summary: Deallocate a passenger
 *     description: |
 *       Soft-deletes the allocation row by clearing `is_active` and
 *       setting `is_deleted = 1`. Sync-eligible.
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Deallocation recorded
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 *       404: { description: Allocation not found }
 */
router.delete('/allocations/:id', auth, adminOnly, (req, res) => {
  const allocationId = parseInt(req.params.id);
  try {
    db.prepare(`
      UPDATE transport_allocations
      SET is_active = 0, is_deleted = 1, sync_status = 'pending', updated_at = datetime('now', 'localtime')
      WHERE id = ?
    `).run(allocationId);
    res.json({ success: true });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// ============ ATTENDANCE ============

/**
 * @openapi
 * /api/transport/attendance:
 *   get:
 *     summary: List transport attendance
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: route_id
 *         schema: { type: integer }
 *       - in: query
 *         name: date
 *         schema: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Attendance rows
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/attendance', auth, (req, res) => {
  const { route_id, date } = req.query;
  try {
    let query = `
      SELECT ta.*, u.first_name, u.last_name
      FROM transport_attendance ta
      JOIN users u ON ta.student_id = u.id
      WHERE ta.is_deleted = 0
    `;
    const params = [];
    
    if (route_id) { query += ' AND ta.route_id = ?'; params.push(route_id); }
    if (date) { query += ' AND ta.date = ?'; params.push(date); }
    
    const attendance = db.prepare(query).all(...params);
    res.json(attendance);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/transport/attendance/bulk:
 *   post:
 *     summary: Bulk upsert transport attendance
 *     description: |
 *       Inserts or updates attendance for an array of `{ student_id,
 *       status }` records against the given `route_id` and `date`.
 *       Runs in a single DB transaction.
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - records
 *               - route_id
 *               - date
 *             properties:
 *               route_id: { type: integer }
 *               date: { type: string, format: date }
 *               records:
 *                 type: array
 *                 items:
 *                   type: object
 *                   required:
 *                     - student_id
 *                     - status
 *                   properties:
 *                     student_id: { type: integer }
 *                     status:
 *                       type: string
 *                       enum: [present, absent, late]
 *     responses:
 *       200:
 *         description: Attendance saved
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *                 count: { type: integer }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 */
router.post('/attendance/bulk', auth, adminOnly, (req, res) => {
  const { records, route_id, date } = req.body; // Array of { student_id, status }
  
  const upsert = db.prepare(`
    INSERT INTO transport_attendance (uid, route_id, student_id, date, status, marked_by, sync_status)
    VALUES (?, ?, ?, ?, ?, ?, 'pending')
  `);
  
  const updateStmt = db.prepare(`
    UPDATE transport_attendance SET status = ?, marked_by = ?, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
    WHERE id = ?
  `);

  const findExisting = db.prepare('SELECT id FROM transport_attendance WHERE route_id = ? AND student_id = ? AND date = ? AND is_deleted = 0');
  
  const processBulk = db.transaction((records) => {
    records.forEach(r => {
      const existing = findExisting.get(route_id, r.student_id, date);
      if (existing) {
        updateStmt.run(r.status, req.user.id, existing.id);
      } else {
        upsert.run(uuidv4(), route_id, r.student_id, date, r.status, req.user.id);
      }
    });
  });

  try {
    processBulk(records);
    res.json({ success: true, count: records.length });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/transport/pickup-points:
 *   get:
 *     summary: List pickup points
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Pickup point list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/pickup-points', auth, (req, res) => {
  try {
    const points = db.prepare('SELECT * FROM pickup_points WHERE is_deleted = 0').all();
    res.json(points);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/transport/vehicle-routes:
 *   get:
 *     summary: List active vehicle-route assignments
 *     tags:
 *       - Transport
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Vehicle-route list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/vehicle-routes', auth, (req, res) => {
  try {
    const vroutes = db.prepare(`
      SELECT vr.*, v.registration_number as vehicle_number, r.name as route_name,
             d.first_name || ' ' || d.last_name as driver_name
      FROM vehicle_routes vr
      JOIN vehicles v ON vr.vehicle_id = v.id
      JOIN routes r ON vr.route_id = r.id
      LEFT JOIN users d ON vr.driver_id = d.id
      WHERE vr.is_deleted = 0
    `).all();
    res.json(vroutes);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
