server.ts

import express from "express";
import { createServer as createViteServer } from "vite";
import path from "path";
import Database from "better-sqlite3";

const db = new Database("library.db");

// Initialize DB
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    username TEXT UNIQUE,
    password TEXT,
    role TEXT,
    name TEXT,
    studentId TEXT
  );

  CREATE TABLE IF NOT EXISTS books (
    id TEXT PRIMARY KEY,
    title TEXT,
    author TEXT,
    isbn TEXT,
    genre TEXT,
    publicationDate TEXT,
    publisher TEXT,
    quantity INTEGER,
    available INTEGER,
    location TEXT,
    status TEXT
  );

  CREATE TABLE IF NOT EXISTS members (
    id TEXT PRIMARY KEY,
    studentId TEXT UNIQUE,
    name TEXT,
    email TEXT,
    department TEXT,
    joinedDate TEXT,
    status TEXT
  );

  CREATE TABLE IF NOT EXISTS transactions (
    id TEXT PRIMARY KEY,
    bookId TEXT,
    memberId TEXT,
    issueDate TEXT,
    dueDate TEXT,
    returnDate TEXT,
    status TEXT,
    fine REAL DEFAULT 0,
    renewalRequested INTEGER DEFAULT 0,
    FOREIGN KEY(bookId) REFERENCES books(id),
    FOREIGN KEY(memberId) REFERENCES members(id)
  );

  CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT
  );

  CREATE TABLE IF NOT EXISTS fine_payments (
    id TEXT PRIMARY KEY,
    transactionId TEXT,
    amount REAL,
    paymentDate TEXT,
    paymentMethod TEXT,
    librarianId TEXT,
    FOREIGN KEY(transactionId) REFERENCES transactions(id)
  );
`);

// Seed initial data if empty
const userCount = db.prepare("SELECT count(*) as count FROM users").get() as { count: number };
if (userCount.count === 0) {
  db.prepare("INSERT INTO users (id, username, password, role, name) VALUES (?, ?, ?, ?, ?)").run(
    "1", "admin", "admin123", "LIBRARIAN", "Head Librarian"
  );
  db.prepare("INSERT INTO users (id, username, password, role, name) VALUES (?, ?, ?, ?, ?)").run(
    "2", "staff", "staff123", "STAFF", "Library Assistant"
  );
  db.prepare("INSERT INTO users (id, username, password, role, name, studentId) VALUES (?, ?, ?, ?, ?, ?)").run(
    "3", "student", "student123", "STUDENT", "John Doe", "S1001"
  );

  db.prepare("INSERT INTO members (id, studentId, name, email, department, joinedDate, status) VALUES (?, ?, ?, ?, ?, ?, ?)").run(
    "m1", "S1001", "John Doe", "john@college.edu", "Computer Science", new Date().toISOString(), "ACTIVE"
  );

  // Seed some books
  const books = [
    ["1", "The Great Gatsby", "F. Scott Fitzgerald", "9780743273565", "Fiction", "1925-04-10", "Scribner", 5, 5, "Shelf A1", "AVAILABLE"],
    ["2", "Introduction to Algorithms", "Cormen et al.", "9780262033848", "Computer Science", "2009-07-31", "MIT Press", 10, 8, "Shelf C4", "AVAILABLE"],
    ["3", "Clean Code", "Robert C. Martin", "9780132350884", "Software Engineering", "2008-08-01", "Prentice Hall", 3, 3, "Shelf C2", "AVAILABLE"]
  ];
  const insertBook = db.prepare("INSERT INTO books (id, title, author, isbn, genre, publicationDate, publisher, quantity, available, location, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
  books.forEach(b => insertBook.run(...b));
}

// Ensure fine rate exists
const fineRate = db.prepare("SELECT value FROM settings WHERE key = 'daily_fine_rate'").get() as any;
if (!fineRate) {
  db.prepare("INSERT INTO settings (key, value) VALUES (?, ?)").run("daily_fine_rate", "5.00");
}


async function startServer() {
  const app = express();
  const PORT = 3000;

  app.use(express.json());

  // Auth API
  app.post("/api/login", (req, res) => {
    const { username, password } = req.body;
    const user = db.prepare("SELECT id, username, role, name, studentId FROM users WHERE username = ? AND password = ?").get(username, password) as any;
    if (user) {
      res.json({ success: true, user });
    } else {
      res.status(401).json({ success: false, message: "Invalid credentials" });
    }
  });

  // Books API
  app.get("/api/books", (req, res) => {
    const books = db.prepare("SELECT * FROM books").all();
    res.json(books);
  });

  app.post("/api/books", (req, res) => {
    const { id, title, author, isbn, genre, publicationDate, publisher, quantity, location } = req.body;
    const bookId = id || Math.random().toString(36).substr(2, 9);
    db.prepare("INSERT INTO books (id, title, author, isbn, genre, publicationDate, publisher, quantity, available, location, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
      .run(bookId, title, author, isbn, genre, publicationDate, publisher, quantity, quantity, location, "AVAILABLE");
    res.json({ success: true });
  });

  app.put("/api/books/:id", (req, res) => {
    const { title, author, isbn, genre, publicationDate, publisher, quantity, available, location } = req.body;
    db.prepare(`
      UPDATE books 
      SET title = ?, author = ?, isbn = ?, genre = ?, publicationDate = ?, publisher = ?, quantity = ?, available = ?, location = ?
      WHERE id = ?
    `).run(title, author, isbn, genre, publicationDate, publisher, quantity, available ?? quantity, location, req.params.id);
    res.json({ success: true });
  });

  app.delete("/api/books/:id", (req, res) => {
    db.prepare("DELETE FROM books WHERE id = ?").run(req.params.id);
    res.json({ success: true });
  });

  // Staff Management API
  app.get("/api/staff", (req, res) => {
    const staff = db.prepare("SELECT id, username, role, name FROM users WHERE role = 'STAFF'").all();
    res.json(staff);
  });

  app.post("/api/staff", (req, res) => {
    const { username, password, name } = req.body;
    const id = Math.random().toString(36).substr(2, 9);
    try {
      db.prepare("INSERT INTO users (id, username, password, role, name) VALUES (?, ?, ?, ?, ?)")
        .run(id, username, password, "STAFF", name);
      res.json({ success: true });
    } catch (err) {
      res.status(400).json({ success: false, message: "Username already exists" });
    }
  });

  app.delete("/api/staff/:id", (req, res) => {
    db.prepare("DELETE FROM users WHERE id = ? AND role = 'STAFF'").run(req.params.id);
    res.json({ success: true });
  });

  // Members API
  app.get("/api/members", (req, res) => {
    const members = db.prepare("SELECT * FROM members").all();
    res.json(members);
  });

  app.post("/api/members", (req, res) => {
    const { studentId, name, email, department } = req.body;
    const id = Math.random().toString(36).substr(2, 9);
    db.prepare("INSERT INTO members (id, studentId, name, email, department, joinedDate, status) VALUES (?, ?, ?, ?, ?, ?, ?)")
      .run(id, studentId, name, email, department, new Date().toISOString(), "ACTIVE");
    res.json({ success: true });
  });

  // Transactions API
  app.get("/api/transactions", (req, res) => {
    const { studentId } = req.query;
    let query = `
      SELECT t.*, b.title as bookTitle, m.name as memberName 
      FROM transactions t
      JOIN books b ON t.bookId = b.id
      JOIN members m ON t.memberId = m.id
    `;
    let params = [];
    if (studentId) {
      query += " WHERE m.studentId = ?";
      params.push(studentId);
    }
    const transactions = db.prepare(query).all(...params);
    res.json(transactions);
  });

  app.post("/api/issue", (req, res) => {
    const { bookId, studentId, days } = req.body;
    // Case-insensitive search for studentId
    const member = db.prepare("SELECT id FROM members WHERE LOWER(studentId) = LOWER(?)").get(studentId) as any;
    if (!member) return res.status(404).json({ success: false, message: "Member not found. Please check the Student ID." });

    const book = db.prepare("SELECT available FROM books WHERE id = ?").get(bookId) as any;
    if (book && book.available > 0) {
      const id = Math.random().toString(36).substr(2, 9);
      const issueDate = new Date();
      const dueDate = new Date();
      dueDate.setDate(issueDate.getDate() + (days || 14));

      db.prepare("INSERT INTO transactions (id, bookId, memberId, issueDate, dueDate, status, fine) VALUES (?, ?, ?, ?, ?, ?, ?)")
        .run(id, bookId, member.id, issueDate.toISOString(), dueDate.toISOString(), "ISSUED", 0);
      
      db.prepare("UPDATE books SET available = available - 1, status = CASE WHEN available - 1 = 0 THEN 'BORROWED' ELSE 'AVAILABLE' END WHERE id = ?").run(bookId);
      res.json({ success: true });
    } else {
      res.status(400).json({ success: false, message: "Book not available" });
    }
  });

  app.post("/api/return", (req, res) => {
    const { transactionId } = req.body;
    const trans = db.prepare("SELECT bookId FROM transactions WHERE id = ?").get(transactionId) as any;
    if (trans) {
      db.prepare("UPDATE transactions SET status = 'RETURNED', returnDate = ? WHERE id = ?")
        .run(new Date().toISOString(), transactionId);
      db.prepare("UPDATE books SET available = available + 1, status = 'AVAILABLE' WHERE id = ?").run(trans.bookId);
      res.json({ success: true });
    } else {
      res.status(404).json({ success: false });
    }
  });

  app.post("/api/renew-request", (req, res) => {
    const { transactionId } = req.body;
    db.prepare("UPDATE transactions SET renewalRequested = 1 WHERE id = ?").run(transactionId);
    res.json({ success: true });
  });

  app.post("/api/renew-approve", (req, res) => {
    const { transactionId } = req.body;
    const trans = db.prepare("SELECT dueDate FROM transactions WHERE id = ?").get(transactionId) as any;
    if (trans) {
      const newDueDate = new Date(trans.dueDate);
      newDueDate.setDate(newDueDate.getDate() + 7);
      db.prepare("UPDATE transactions SET dueDate = ?, renewalRequested = 0 WHERE id = ?")
        .run(newDueDate.toISOString(), transactionId);
      res.json({ success: true });
    } else {
      res.status(404).json({ success: false });
    }
  });

  // Background task to flag overdue and calculate fines
  const calculateFines = () => {
    const now = new Date();
    const nowIso = now.toISOString();
    
    // Get daily rate
    const rateSetting = db.prepare("SELECT value FROM settings WHERE key = 'daily_fine_rate'").get() as any;
    const dailyRate = parseFloat(rateSetting?.value || "5.00");

    // Update status to OVERDUE
    db.prepare("UPDATE transactions SET status = 'OVERDUE' WHERE status = 'ISSUED' AND dueDate < ?").run(nowIso);

    // Calculate fines for all transactions that are OVERDUE or ISSUED but past due
    const overdueTrans = db.prepare("SELECT id, dueDate, returnDate, status, fine FROM transactions WHERE status IN ('OVERDUE', 'ISSUED') AND dueDate < ?").all(nowIso) as any[];
    
    for (const trans of overdueTrans) {
      const dueDate = new Date(trans.dueDate);
      const endDate = trans.returnDate ? new Date(trans.returnDate) : now;
      
      const diffTime = Math.max(0, endDate.getTime() - dueDate.getTime());
      const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
      const calculatedFine = diffDays * dailyRate;

      if (calculatedFine > trans.fine) {
        db.prepare("UPDATE transactions SET fine = ? WHERE id = ?").run(calculatedFine, trans.id);
      }
    }
  };

  app.get("/api/check-overdue", (req, res) => {
    calculateFines();
    res.json({ success: true });
  });

  // Fine Management API
  app.get("/api/fines/stats", (req, res) => {
    const totalFines = db.prepare("SELECT SUM(fine) as total FROM transactions").get() as any;
    const collectedFines = db.prepare("SELECT SUM(amount) as total FROM fine_payments").get() as any;
    res.json({ 
      total: totalFines.total || 0, 
      collected: collectedFines.total || 0,
      outstanding: (totalFines.total || 0) - (collectedFines.total || 0)
    });
  });

  app.post("/api/fines/pay", (req, res) => {
    const { transactionId, amount, paymentMethod, librarianId } = req.body;
    const id = Math.random().toString(36).substr(2, 9);
    
    db.prepare("INSERT INTO fine_payments (id, transactionId, amount, paymentDate, paymentMethod, librarianId) VALUES (?, ?, ?, ?, ?, ?)")
      .run(id, transactionId, amount, new Date().toISOString(), paymentMethod, librarianId);
    
    res.json({ success: true, paymentId: id });
  });

  app.get("/api/fines/receipt/:paymentId", (req, res) => {
    const receipt = db.prepare(`
      SELECT p.*, t.bookId, b.title as bookTitle, m.name as memberName, m.studentId
      FROM fine_payments p
      JOIN transactions t ON p.transactionId = t.id
      JOIN books b ON t.bookId = b.id
      JOIN members m ON t.memberId = m.id
      WHERE p.id = ?
    `).get(req.params.paymentId);
    res.json(receipt);
  });

  app.get("/api/settings", (req, res) => {
    const settings = db.prepare("SELECT * FROM settings").all();
    res.json(settings);
  });

  app.post("/api/settings", (req, res) => {
    const { key, value } = req.body;
    db.prepare("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)").run(key, value);
    res.json({ success: true });
  });

  // Vite middleware for development
  if (process.env.NODE_ENV !== "production") {
    const vite = await createViteServer({
      server: { middlewareMode: true },
      appType: "spa",
    });
    app.use(vite.middlewares);
  } else {
    app.use(express.static(path.join(__dirname, "dist")));
    app.get("*", (req, res) => {
      res.sendFile(path.join(__dirname, "dist", "index.html"));
    });
  }

  app.listen(PORT, "0.0.0.0", () => {
    console.log(`Server running on http://localhost:${PORT}`);
    
    // Check for overdue items and calculate fines every hour
    setInterval(() => {
      calculateFines();
      console.log("Overdue items and fines check completed.");
    }, 3600000);
  });
}

startServer();