/**
 * Inventory Controller
 * CRUD for items, categories, suppliers, locations, stock, transactions, and issues.
 * Architecture rules:
 *   - Sync-aware writes: every INSERT/UPDATE sets sync_status='pending', updates updated_at, mints a new uid (v4)
 *   - Soft delete: is_deleted = 1 instead of DELETE
 *   - DB access via better-sqlite3 with the getDb() helper
 *   - All routes are auth-protected; writes are admin/bursar/librarian only
 */

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');
let db;
const getDb = () => {
  if (!db) {
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
  }
  return db;
};

// 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' });
  }
};

// Gate for inventory managers
const inventoryAdminOnly = (req, res, next) => {
  const allowed = ['school_admin', 'systems_admin', 'principal', 'bursar', 'librarian', 'clubs_head'];
  if (!allowed.includes(req.user.role)) {
    return res.status(403).json({ error: 'Insufficient permissions for inventory management' });
  }
  next();
};

// ============ CATEGORIES ============

/**
 * @openapi
 * /api/inventory/categories:
 *   get:
 *     summary: List item categories
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Category list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create item category
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - name
 *             properties:
 *               name: { type: string }
 *               code: { type: string }
 *               description: { type: string }
 *               parent_id: { type: integer }
 *     responses:
 *       201:
 *         description: Created category
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: "Inventory admin only" }
 */
router.get('/categories', auth, (req, res) => {
  try {
    const categories = getDb().prepare('SELECT * FROM item_categories WHERE is_deleted = 0 ORDER BY name').all();
    res.json(categories);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/categories', auth, inventoryAdminOnly, (req, res) => {
  try {
    const { name, code, description, parent_id } = req.body;
    const result = getDb().prepare(`
      INSERT INTO item_categories (uid, name, code, description, parent_id, sync_status)
      VALUES (?, ?, ?, ?, ?, 'pending')
    `).run(uuidv4(), name, code, description, parent_id || null);
    res.status(201).json(getDb().prepare('SELECT * FROM item_categories WHERE id = ?').get(result.lastInsertRowid));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ SUPPLIERS ============

/**
 * @openapi
 * /api/inventory/suppliers:
 *   get:
 *     summary: List suppliers
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Supplier list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create supplier
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - name
 *             properties:
 *               name: { type: string }
 *               code: { type: string }
 *               contact_person: { type: string }
 *               email: { type: string, format: email }
 *               phone: { type: string }
 *     responses:
 *       201:
 *         description: Created supplier
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: "Inventory admin only" }
 */
router.get('/suppliers', auth, (req, res) => {
  try {
    const suppliers = getDb().prepare('SELECT * FROM suppliers WHERE is_deleted = 0 ORDER BY name').all();
    res.json(suppliers);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/suppliers', auth, inventoryAdminOnly, (req, res) => {
  try {
    const { name, code, contact_person, email, phone } = req.body;
    const result = getDb().prepare(`
      INSERT INTO suppliers (uid, name, code, contact_person, email, phone, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, 'pending')
    `).run(uuidv4(), name, code || null, contact_person || null, email || null, phone || null);
    res.status(201).json(getDb().prepare('SELECT * FROM suppliers WHERE id = ?').get(result.lastInsertRowid));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ LOCATIONS ============

/**
 * @openapi
 * /api/inventory/locations:
 *   get:
 *     summary: List store locations
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Store location list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/locations', auth, (req, res) => {
  try {
    const locations = getDb().prepare('SELECT * FROM store_locations WHERE is_deleted = 0 ORDER BY name').all();
    res.json(locations);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ ITEMS ============

/**
 * @openapi
 * /api/inventory/items:
 *   get:
 *     summary: List inventory items
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: search
 *         schema: { type: string }
 *       - in: query
 *         name: category_id
 *         schema: { type: integer }
 *       - in: query
 *         name: supplier_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Item list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create item
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - name
 *             properties:
 *               name: { type: string }
 *               code: { type: string }
 *               barcode: { type: string }
 *               description: { type: string }
 *               category_id: { type: integer }
 *               unit: { type: string }
 *               purchase_price: { type: number }
 *               selling_price: { type: number }
 *               reorder_level: { type: integer }
 *               supplier_id: { type: integer }
 *     responses:
 *       201:
 *         description: Created item
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: "Inventory admin only" }
 */
router.get('/items', auth, inventoryAdminOnly, (req, res) => {
  try {
    const { search, category_id, supplier_id } = req.query;
    let query = `
      SELECT i.*, 
        c.name as category_name, 
        s.name as supplier_name,
        (SELECT SUM(quantity) FROM item_stock WHERE item_id = i.id AND is_deleted = 0) as total_stock
      FROM items i
      LEFT JOIN item_categories c ON i.category_id = c.id
      LEFT JOIN suppliers s ON i.supplier_id = s.id
      WHERE i.is_deleted = 0
    `;
    const params = [];

    if (search) {
      query += ' AND (i.name LIKE ? OR i.code LIKE ? OR i.barcode LIKE ?)';
      const term = `%${search}%`;
      params.push(term, term, term);
    }
    if (category_id) {
      query += ' AND i.category_id = ?';
      params.push(category_id);
    }
    if (supplier_id) {
      query += ' AND i.supplier_id = ?';
      params.push(supplier_id);
    }

    query += ' ORDER BY i.name';
    res.json(getDb().prepare(query).all(...params));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/items', auth, inventoryAdminOnly, (req, res) => {
  try {
    const { name, code, barcode, description, category_id, unit, purchase_price, selling_price, reorder_level, supplier_id } = req.body;
    const result = getDb().prepare(`
      INSERT INTO items (uid, name, code, barcode, description, category_id, unit, purchase_price, selling_price, reorder_level, supplier_id, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
    `).run(uuidv4(), name, code, barcode, description, category_id, unit, purchase_price || 0, selling_price || 0, reorder_level || 10, supplier_id);
    res.status(201).json(getDb().prepare('SELECT * FROM items WHERE id = ?').get(result.lastInsertRowid));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ STOCK & TRANSACTIONS ============

/**
 * @openapi
 * /api/inventory/stock:
 *   get:
 *     summary: List item stock levels
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: item_id
 *         schema: { type: integer }
 *       - in: query
 *         name: store_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Stock rows
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/stock', auth, (req, res) => {
  try {
    const { item_id, store_id } = req.query;
    let query = `
      SELECT st.*, i.name as item_name, i.unit, l.name as store_name
      FROM item_stock st
      JOIN items i ON st.item_id = i.id
      JOIN store_locations l ON st.store_id = l.id
      WHERE st.is_deleted = 0
    `;
    const params = [];
    if (item_id) { query += ' AND st.item_id = ?'; params.push(item_id); }
    if (store_id) { query += ' AND st.store_id = ?'; params.push(store_id); }
    res.json(getDb().prepare(query).all(...params));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/inventory/transactions:
 *   post:
 *     summary: Record stock transaction
 *     description: |
 *       Records a stock transaction (`purchase`, `sale`, `damage`,
 *       `expired`, `adjustment`, `adjustment_out`, `return`) and updates
 *       the corresponding stock row in a single DB transaction.
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - item_id
 *               - store_id
 *               - type
 *               - quantity
 *             properties:
 *               item_id: { type: integer }
 *               store_id: { type: integer }
 *               type:
 *                 type: string
 *                 enum: [purchase, sale, damage, expired, adjustment, adjustment_out, return]
 *               quantity: { type: number }
 *               unit_price: { type: number }
 *               reference_number: { type: string }
 *               notes: { type: string }
 *               supplier_id: { type: integer }
 *     responses:
 *       200:
 *         description: Transaction recorded
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: "Inventory admin only" }
 */
router.post('/transactions', auth, inventoryAdminOnly, (req, res) => {
  const database = getDb();
  const { item_id, store_id, type, quantity, unit_price, reference_number, notes, supplier_id } = req.body;

  try {
    database.transaction(() => {
      // 1. Record Transaction
      const total_amount = (unit_price || 0) * quantity;
      database.prepare(`
        INSERT INTO stock_transactions (uid, item_id, store_id, type, quantity, unit_price, total_amount, reference_number, supplier_id, notes, created_by, sync_status)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
      `).run(uuidv4(), item_id, store_id, type, quantity, unit_price, total_amount, reference_number, supplier_id, notes, req.user.id);

      // 2. Update Stock Level
      const existing = database.prepare('SELECT id, quantity FROM item_stock WHERE item_id = ? AND store_id = ? AND is_deleted = 0').get(item_id, store_id);
      
      let multiplier = 1;
      if (['sale', 'damage', 'expired', 'adjustment_out'].includes(type)) multiplier = -1;
      if (type === 'adjustment' && quantity < 0) multiplier = 1; // already negative

      if (existing) {
        database.prepare(`
          UPDATE item_stock 
          SET quantity = quantity + ?, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
          WHERE id = ?
        `).run(quantity * multiplier, existing.id);
      } else {
        database.prepare(`
          INSERT INTO item_stock (uid, item_id, store_id, quantity, sync_status)
          VALUES (?, ?, ?, ?, 'pending')
        `).run(uuidv4(), item_id, store_id, quantity * multiplier);
      }
    })();
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ ITEM ISSUING ============

/**
 * @openapi
 * /api/inventory/issues:
 *   get:
 *     summary: List item issues
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: status
 *         schema: { type: string }
 *       - in: query
 *         name: issued_to_type
 *         schema: { type: string }
 *       - in: query
 *         name: issued_to_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Issue list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Issue items from stock
 *     description: |
 *       Issues the requested quantity to a recipient and decrements the
 *       stock row. Fails when stock is insufficient at the chosen
 *       store.
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - item_id
 *               - quantity
 *             properties:
 *               item_id: { type: integer }
 *               store_id: { type: integer }
 *               issued_to_type: { type: string }
 *               issued_to_id: { type: integer }
 *               quantity: { type: number }
 *               purpose: { type: string }
 *               return_due_date: { type: string, format: date }
 *               notes: { type: string }
 *     responses:
 *       200:
 *         description: Issue recorded
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: "Inventory admin only" }
 *       409: { description: Insufficient stock in selected location }
 */
router.get('/issues', auth, (req, res) => {
  try {
    const { status, issued_to_type, issued_to_id } = req.query;
    let query = `
      SELECT iss.*, i.name as item_name, u.first_name || ' ' || u.last_name as issuer_name
      FROM item_issues iss
      JOIN items i ON iss.item_id = i.id
      LEFT JOIN users u ON iss.issued_by = u.id
      WHERE iss.is_deleted = 0
    `;
    const params = [];
    if (status) { query += ' AND iss.status = ?'; params.push(status); }
    if (issued_to_type) { query += ' AND iss.issued_to_type = ?'; params.push(issued_to_type); }
    if (issued_to_id) { query += ' AND iss.issued_to_id = ?'; params.push(issued_to_id); }
    
    query += ' ORDER BY iss.issue_date DESC';
    res.json(getDb().prepare(query).all(...params));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/issues', auth, inventoryAdminOnly, (req, res) => {
  const database = getDb();
  const { item_id, store_id, issued_to_type, issued_to_id, quantity, purpose, return_due_date, notes } = req.body;

  try {
    database.transaction(() => {
      // 1. Check stock availability
      const stock = database.prepare('SELECT id, quantity FROM item_stock WHERE item_id = ? AND store_id = ? AND is_deleted = 0').get(item_id, store_id);
      if (!stock || stock.quantity < quantity) {
        throw new Error('Insufficient stock in selected location');
      }

      // 2. Create Issue Record
      database.prepare(`
        INSERT INTO item_issues (uid, item_id, issued_to_type, issued_to_id, quantity, purpose, return_due_date, notes, issued_by, sync_status)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
      `).run(uuidv4(), item_id, issued_to_type, issued_to_id, quantity, purpose, return_due_date, notes, req.user.id);

      // 3. Deduct from Stock
      database.prepare(`
        UPDATE item_stock 
        SET quantity = quantity - ?, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
        WHERE id = ?
      `).run(quantity, stock.id);

      // 4. Record Transaction
      database.prepare(`
        INSERT INTO stock_transactions (uid, item_id, store_id, type, quantity, notes, created_by, sync_status)
        VALUES (?, ?, ?, 'adjustment', ?, ?, ?, 'pending')
      `).run(uuidv4(), item_id, store_id, -quantity, `Issued to ${issued_to_type}`, req.user.id);
    })();
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/inventory/issues/{id}/return:
 *   post:
 *     summary: Return items from a prior issue
 *     description: |
 *       Returns some or all quantity against an existing issue and
 *       credits the selected store. Marks the issue `returned` or
 *       `partially_returned` depending on quantity.
 *     tags:
 *       - Inventory
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - quantity
 *               - store_id
 *             properties:
 *               quantity: { type: number }
 *               condition: { type: string }
 *               store_id: { type: integer }
 *     responses:
 *       200:
 *         description: Return recorded
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: "Inventory admin only" }
 *       404: { description: Issue record not found }
 *       409: { description: Items already fully returned }
 */
router.post('/issues/:id/return', auth, inventoryAdminOnly, (req, res) => {
  const database = getDb();
  const { quantity, condition, store_id } = req.body;

  try {
    database.transaction(() => {
      const issue = database.prepare('SELECT * FROM item_issues WHERE id = ?').get(req.params.id);
      if (!issue) throw new Error('Issue record not found');
      if (issue.status === 'returned') throw new Error('Items already fully returned');

      const newReturnedQty = issue.returned_quantity + quantity;
      const status = newReturnedQty >= issue.quantity ? 'returned' : 'partially_returned';

      // 1. Update Issue Record
      database.prepare(`
        UPDATE item_issues 
        SET returned_quantity = ?, returned_date = CURRENT_TIMESTAMP, status = ?, condition_on_return = ?, sync_status = 'pending'
        WHERE id = ?
      `).run(newReturnedQty, status, condition, req.params.id);

      // 2. Add back to Stock
      const existing = database.prepare('SELECT id FROM item_stock WHERE item_id = ? AND store_id = ? AND is_deleted = 0').get(issue.item_id, store_id);
      if (existing) {
        database.prepare(`UPDATE item_stock SET quantity = quantity + ?, sync_status = 'pending' WHERE id = ?`).run(quantity, existing.id);
      } else {
        database.prepare(`INSERT INTO item_stock (uid, item_id, store_id, quantity, sync_status) VALUES (?, ?, ?, ?, 'pending')`)
          .run(uuidv4(), issue.item_id, store_id, quantity);
      }

      // 3. Record Transaction
      database.prepare(`
        INSERT INTO stock_transactions (uid, item_id, store_id, type, quantity, notes, created_by, sync_status)
        VALUES (?, ?, ?, 'return', ?, ?, ?, 'pending')
      `).run(uuidv4(), issue.item_id, store_id, quantity, `Returned from ${issue.issued_to_type}`, req.user.id);
    })();
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
