database.sql

-- BookSwap database setup
-- Import this file once in phpMyAdmin.
-- It resets the project database and adds 10 sample users and 110 sample books.

DROP DATABASE IF EXISTS book_exchange;
CREATE DATABASE book_exchange CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE book_exchange;

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  student_id VARCHAR(30) NOT NULL UNIQUE,
  department VARCHAR(100) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  owner_id INT NOT NULL,
  title VARCHAR(150) NOT NULL,
  author VARCHAR(150) NOT NULL,
  category VARCHAR(80) NOT NULL,
  book_condition VARCHAR(30) NOT NULL,
  description TEXT,
  status ENUM('available', 'borrowed') DEFAULT 'available',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_books_owner (owner_id),
  INDEX idx_books_status (status),
  INDEX idx_books_category (category),
  INDEX idx_books_title (title),
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE borrow_requests (
  id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT NOT NULL,
  requester_id INT NOT NULL,
  status ENUM('pending', 'approved', 'rejected', 'cancelled') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_requests_book_status (book_id, status),
  INDEX idx_requests_requester (requester_id),
  FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
  FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE loans (
  id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT NOT NULL,
  owner_id INT NOT NULL,
  borrower_id INT NOT NULL,
  borrow_date DATE NOT NULL,
  due_date DATE NOT NULL,
  return_date DATE NULL,
  fine_amount DECIMAL(10,2) DEFAULT 0,
  status ENUM('borrowed', 'returned') DEFAULT 'borrowed',
  INDEX idx_loans_borrower_status (borrower_id, status),
  INDEX idx_loans_owner_status (owner_id, status),
  INDEX idx_loans_due_date (due_date),
  FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (borrower_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Password for all demo users: demo123
INSERT INTO users (id, name, student_id, department, phone, email, password) VALUES
(1, 'Nusrat Jahan', 'CSE-2026-001', 'Computer Science and Engineering', '01700000001', 'nusrat@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.'),
(2, 'Tanvir Ahmed', 'EEE-2026-002', 'Electrical and Electronic Engineering', '01700000002', 'tanvir@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.'),
(3, 'Farhan Islam', 'BBA-2026-003', 'Business Administration', '01700000003', 'farhan@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.'),
(4, 'Sadia Rahman', 'ENG-2026-004', 'English', '01700000004', 'sadia@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.'),
(5, 'Mahin Chowdhury', 'CE-2026-005', 'Civil Engineering', '01700000005', 'mahin@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.'),
(6, 'Ayesha Siddika', 'ECO-2026-006', 'Economics', '01700000006', 'ayesha@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.'),
(7, 'Rafi Hasan', 'MATH-2026-007', 'Mathematics', '01700000007', 'rafi@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.'),
(8, 'Samira Akter', 'PHR-2026-008', 'Pharmacy', '01700000008', 'samira@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.'),
(9, 'Arif Hossain', 'LAW-2026-009', 'Law', '01700000009', 'arif@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.'),
(10, 'Nabila Sultana', 'IS-2026-010', 'Islamic Studies', '01700000010', 'nabila@bookswap.demo', '$2b$10$QtO4w7ZP6L2ibt8IwLDnCuv5ixiybOdVg9GQHSgiCVdKRkGEMgEB.');

INSERT INTO books (owner_id, title, author, category, book_condition, description) VALUES
(1, 'Clean Code', 'Robert C. Martin', 'Programming', 'Good', 'A practical guide to writing readable and maintainable software.'),
(2, 'The Pragmatic Programmer', 'Andrew Hunt and David Thomas', 'Programming', 'Good', 'Practical advice for becoming a more effective software developer.'),
(3, 'Eloquent JavaScript', 'Marijn Haverbeke', 'Programming', 'New', 'A modern introduction to JavaScript and browser programming.'),
(4, 'You Don''t Know JS Yet', 'Kyle Simpson', 'Programming', 'Good', 'A detailed exploration of the core mechanisms of JavaScript.'),
(5, 'Python Crash Course', 'Eric Matthes', 'Programming', 'Good', 'A beginner-friendly guide to Python with practical projects.'),
(6, 'Head First Java', 'Kathy Sierra and Bert Bates', 'Programming', 'Fair', 'A visual and approachable introduction to Java programming.'),
(7, 'Learning Web Design', 'Jennifer Niederst Robbins', 'Programming', 'Good', 'Covers HTML, CSS, JavaScript and responsive web design basics.'),
(8, 'Database System Concepts', 'Abraham Silberschatz, Henry Korth and S. Sudarshan', 'Academic', 'Good', 'Database fundamentals, SQL, normalization and transaction concepts.'),
(9, 'Introduction to Algorithms', 'Thomas H. Cormen and others', 'Academic', 'Fair', 'A comprehensive reference for algorithms and data structures.'),
(10, 'Computer Networking: A Top-Down Approach', 'James Kurose and Keith Ross', 'Academic', 'Good', 'Explains networking concepts from applications down to physical networks.'),
(1, 'Operating System Concepts', 'Abraham Silberschatz, Peter Galvin and Greg Gagne', 'Academic', 'Good', 'Core ideas of processes, memory, storage and operating systems.'),
(2, 'Discrete Mathematics and Its Applications', 'Kenneth Rosen', 'Academic', 'Fair', 'Logic, sets, relations, graphs and other foundations of computing.'),
(3, 'Fundamentals of Physics', 'David Halliday, Robert Resnick and Jearl Walker', 'Science', 'Good', 'A broad university-level introduction to physics.'),
(4, 'A Brief History of Time', 'Stephen Hawking', 'Science', 'Fair', 'An accessible introduction to the universe, time and black holes.'),
(5, 'Cosmos', 'Carl Sagan', 'Science', 'Good', 'A journey through astronomy, science and humanity’s place in the universe.'),
(6, 'Brief Answers to the Big Questions', 'Stephen Hawking', 'Science', 'New', 'Short discussions of major questions about science and the future.'),
(7, 'The Selfish Gene', 'Richard Dawkins', 'Science', 'Good', 'An influential explanation of evolution from a gene-centered perspective.'),
(8, 'The Gene', 'Siddhartha Mukherjee', 'Science', 'Good', 'A history of genetics and its effects on science and society.'),
(9, 'The Alchemist', 'Paulo Coelho', 'Fiction', 'Good', 'A simple and inspiring novel about following personal dreams.'),
(10, '1984', 'George Orwell', 'Fiction', 'Good', 'A classic dystopian novel about surveillance and authoritarian power.'),
(1, 'The Hobbit', 'J. R. R. Tolkien', 'Fiction', 'New', 'A fantasy adventure featuring Bilbo Baggins and a dangerous journey.'),
(2, 'Animal Farm', 'George Orwell', 'Fiction', 'Good', 'A short political fable about power, revolution and corruption.'),
(3, 'Pride and Prejudice', 'Jane Austen', 'Fiction', 'Fair', 'A classic novel about family, society and relationships.'),
(4, 'The Kite Runner', 'Khaled Hosseini', 'Fiction', 'Good', 'A moving story of friendship, guilt and redemption.'),
(5, 'The Old Man and the Sea', 'Ernest Hemingway', 'Fiction', 'Good', 'A fisherman’s struggle with a giant marlin and his own limits.'),
(6, 'The Little Prince', 'Antoine de Saint-Exupéry', 'Fiction', 'New', 'A short philosophical story about friendship, love and growing up.'),
(7, 'The Psychology of Money', 'Morgan Housel', 'Business', 'Good', 'Short lessons about money, behavior and long-term decisions.'),
(8, 'The Lean Startup', 'Eric Ries', 'Business', 'Good', 'A practical approach to building and testing new businesses.'),
(9, 'Start with Why', 'Simon Sinek', 'Business', 'New', 'Explains how purpose can guide leadership and organizations.'),
(10, 'Zero to One', 'Peter Thiel with Blake Masters', 'Business', 'Good', 'Ideas about innovation, startups and creating new value.'),
(1, 'Good to Great', 'Jim Collins', 'Business', 'Fair', 'Research-based lessons on how companies improve performance.'),
(2, 'Rework', 'Jason Fried and David Heinemeier Hansson', 'Business', 'Good', 'Straightforward advice for building and running a small business.'),
(3, 'Rich Dad Poor Dad', 'Robert T. Kiyosaki', 'Business', 'Good', 'A popular introduction to personal finance and financial thinking.'),
(4, 'Atomic Habits', 'James Clear', 'Self Development', 'New', 'Practical ideas for building good habits and breaking bad ones.'),
(5, 'Deep Work', 'Cal Newport', 'Self Development', 'Good', 'Strategies for focused work in a world full of distractions.'),
(6, 'The 7 Habits of Highly Effective People', 'Stephen R. Covey', 'Self Development', 'Good', 'Principles for personal effectiveness, relationships and leadership.'),
(7, 'How to Win Friends and Influence People', 'Dale Carnegie', 'Self Development', 'Fair', 'Classic guidance on communication and human relationships.'),
(8, 'Think and Grow Rich', 'Napoleon Hill', 'Self Development', 'Good', 'A classic book about goals, mindset and personal achievement.'),
(9, 'English for Today', 'NCTB', 'Academic', 'Good', 'Useful English reading and language practice for students.'),
(10, 'The Sealed Nectar', 'Safiur Rahman Mubarakpuri', 'Islamic Book', 'Good', 'A well-known biography of Prophet Muhammad (peace be upon him).'),
(1, 'Riyad as-Salihin', 'Imam an-Nawawi', 'Islamic Book', 'Good', 'A widely studied collection of hadith on faith, character and daily life.'),
(2, 'Stories of the Prophets', 'Ibn Kathir', 'Islamic Book', 'Fair', 'Accounts of prophets presented from classical Islamic sources.'),
(3, 'The Ideal Muslim', 'Muhammad Ali al-Hashimi', 'Islamic Book', 'Good', 'Discusses Islamic character, worship and relationships.'),
(4, 'Don''t Be Sad', 'Aaidh al-Qarni', 'Islamic Book', 'Good', 'Reflections on patience, hope and dealing with hardship.'),
(5, 'Purification of the Heart', 'Hamza Yusuf', 'Islamic Book', 'New', 'A translation and commentary on spiritual diseases and their remedies.'),
(6, 'The Productive Muslim', 'Mohammed Faris', 'Islamic Book', 'Good', 'Connects faith, productivity, habits and purposeful living.'),
(7, 'In the Early Hours', 'Khurram Murad', 'Islamic Book', 'Good', 'Short reminders about spiritual growth and closeness to Allah.'),
(8, 'Fortress of the Muslim', 'Sa’id bin Ali bin Wahf al-Qahtani', 'Islamic Book', 'New', 'A compact collection of daily supplications from the Quran and Sunnah.'),
(9, 'Towards Understanding Islam', 'Abul A’la Maududi', 'Islamic Book', 'Fair', 'An introductory explanation of basic Islamic beliefs and practices.'),
(10, 'Sapiens', 'Yuval Noah Harari', 'History', 'Good', 'A broad account of human history from early societies to modern times.');


-- 60 additional books across 12 new categories
INSERT INTO books (owner_id, title, author, category, book_condition, description) VALUES
(1, 'Calculus', 'James Stewart', 'Mathematics', 'Good', 'A clear university text covering limits, derivatives and integrals.'),
(2, 'Linear Algebra and Its Applications', 'David C. Lay', 'Mathematics', 'New', 'Introduces vectors, matrices, linear transformations and applications.'),
(3, 'How to Prove It', 'Daniel J. Velleman', 'Mathematics', 'Fair', 'A practical introduction to mathematical reasoning and proof writing.'),
(4, 'A Book of Abstract Algebra', 'Charles C. Pinter', 'Mathematics', 'Good', 'An accessible introduction to groups, rings and fields.'),
(5, 'Introduction to Probability', 'Dimitri P. Bertsekas and John N. Tsitsiklis', 'Mathematics', 'Good', 'Fundamental ideas of probability with worked examples.'),
(6, 'Engineering Mechanics: Statics', 'R. C. Hibbeler', 'Engineering', 'Good', 'Explains forces, equilibrium and structural analysis.'),
(7, 'Introduction to Electric Circuits', 'Richard C. Dorf and James A. Svoboda', 'Engineering', 'New', 'Covers circuit laws, analysis methods and electrical components.'),
(8, 'Signals and Systems', 'Alan V. Oppenheim and Alan S. Willsky', 'Engineering', 'Fair', 'A foundational text on continuous and discrete signals.'),
(9, 'Materials Science and Engineering', 'William D. Callister Jr.', 'Engineering', 'Good', 'Introduces material structures, properties and engineering uses.'),
(10, 'Fluid Mechanics', 'Frank M. White', 'Engineering', 'Good', 'Covers fluid behavior, pressure, flow and engineering applications.'),
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Literature', 'Good', 'A classic novel about ambition, wealth and the American dream.'),
(2, 'To Kill a Mockingbird', 'Harper Lee', 'Literature', 'New', 'A story about justice, prejudice and moral courage.'),
(3, 'Jane Eyre', 'Charlotte Brontë', 'Literature', 'Fair', 'A classic novel about independence, identity and love.'),
(4, 'Wuthering Heights', 'Emily Brontë', 'Literature', 'Good', 'A dramatic novel about love, revenge and social conflict.'),
(5, 'Great Expectations', 'Charles Dickens', 'Literature', 'Good', 'A coming-of-age story about ambition, class and personal growth.'),
(6, 'Long Walk to Freedom', 'Nelson Mandela', 'Biography', 'Good', 'Nelson Mandela’s account of his life and struggle against apartheid.'),
(7, 'The Diary of a Young Girl', 'Anne Frank', 'Biography', 'New', 'A young girl’s diary written while hiding during World War II.'),
(8, 'Steve Jobs', 'Walter Isaacson', 'Biography', 'Fair', 'A detailed biography of the Apple co-founder and technology leader.'),
(9, 'Wings of Fire', 'A. P. J. Abdul Kalam', 'Biography', 'Good', 'An inspiring autobiography of the scientist and former Indian president.'),
(10, 'I Am Malala', 'Malala Yousafzai with Christina Lamb', 'Biography', 'Good', 'A memoir about education, courage and advocacy.'),
(1, 'The Innovators', 'Walter Isaacson', 'Technology', 'Good', 'The story of the people who helped build the digital age.'),
(2, 'Code', 'Charles Petzold', 'Technology', 'New', 'Explains how computers work from basic codes to processors.'),
(3, 'The Phoenix Project', 'Gene Kim, Kevin Behr and George Spafford', 'Technology', 'Fair', 'A novel about improving IT work through DevOps principles.'),
(4, 'Designing Data-Intensive Applications', 'Martin Kleppmann', 'Technology', 'Good', 'Explains reliable, scalable and maintainable data systems.'),
(5, 'Artificial Intelligence: A Modern Approach', 'Stuart Russell and Peter Norvig', 'Technology', 'Good', 'A broad academic introduction to artificial intelligence.'),
(6, 'Principles of Economics', 'N. Gregory Mankiw', 'Economics', 'Good', 'Introduces microeconomics and macroeconomics with clear examples.'),
(7, 'Freakonomics', 'Steven D. Levitt and Stephen J. Dubner', 'Economics', 'New', 'Uses economic thinking to examine unusual everyday questions.'),
(8, 'The Wealth of Nations', 'Adam Smith', 'Economics', 'Fair', 'A foundational work on markets, trade and economic systems.'),
(9, 'Poor Economics', 'Abhijit V. Banerjee and Esther Duflo', 'Economics', 'Good', 'Research-based discussion of poverty and development policy.'),
(10, 'Capital in the Twenty-First Century', 'Thomas Piketty', 'Economics', 'Good', 'Examines wealth, income and inequality over time.'),
(1, 'English Grammar in Use', 'Raymond Murphy', 'Language Learning', 'Good', 'Self-study grammar practice for intermediate English learners.'),
(2, 'Word Power Made Easy', 'Norman Lewis', 'Language Learning', 'New', 'A structured guide to improving English vocabulary.'),
(3, 'Practical English Usage', 'Michael Swan', 'Language Learning', 'Fair', 'A reference for common English grammar and usage questions.'),
(4, 'Essential Grammar in Use', 'Raymond Murphy', 'Language Learning', 'Good', 'Basic grammar explanations and exercises for learners.'),
(5, 'Oxford Word Skills Intermediate', 'Ruth Gairns and Stuart Redman', 'Language Learning', 'Good', 'Vocabulary lessons and practice for everyday English.'),
(6, 'Why We Sleep', 'Matthew Walker', 'Health', 'Good', 'Explores sleep science and its relationship with health.'),
(7, 'The Body', 'Bill Bryson', 'Health', 'New', 'An accessible journey through the human body and its systems.'),
(8, 'Being Mortal', 'Atul Gawande', 'Health', 'Fair', 'Discusses medicine, aging and end-of-life care.'),
(9, 'The Emperor of All Maladies', 'Siddhartha Mukherjee', 'Health', 'Good', 'A history of cancer research, treatment and patient experience.'),
(10, 'Spark', 'John J. Ratey', 'Health', 'Good', 'Explains connections between exercise, the brain and mental wellbeing.'),
(1, 'The Republic', 'Plato', 'Philosophy', 'Good', 'A classic dialogue about justice, society and government.'),
(2, 'Meditations', 'Marcus Aurelius', 'Philosophy', 'New', 'Personal reflections on discipline, duty and Stoic thought.'),
(3, 'Sophie''s World', 'Jostein Gaarder', 'Philosophy', 'Fair', 'A novel that introduces major ideas in Western philosophy.'),
(4, 'The Problems of Philosophy', 'Bertrand Russell', 'Philosophy', 'Good', 'A concise introduction to knowledge, reality and philosophical inquiry.'),
(5, 'Man''s Search for Meaning', 'Viktor E. Frankl', 'Philosophy', 'Good', 'A reflection on suffering, purpose and human resilience.'),
(6, 'Charlie and the Chocolate Factory', 'Roald Dahl', 'Children''s Books', 'Good', 'A playful adventure inside a mysterious chocolate factory.'),
(7, 'Matilda', 'Roald Dahl', 'Children''s Books', 'New', 'A clever child discovers her abilities and challenges unfair adults.'),
(8, 'The Lion, the Witch and the Wardrobe', 'C. S. Lewis', 'Children''s Books', 'Fair', 'A fantasy adventure in the world of Narnia.'),
(9, 'Harry Potter and the Philosopher''s Stone', 'J. K. Rowling', 'Children''s Books', 'Good', 'A young wizard begins his first year at a magical school.'),
(10, 'The Adventures of Tom Sawyer', 'Mark Twain', 'Children''s Books', 'Good', 'A classic story of childhood, friendship and adventure.'),
(1, 'Bangladesh: A Legacy of Blood', 'Anthony Mascarenhas', 'Bangladesh Studies', 'Good', 'A political history of Bangladesh after independence.'),
(2, 'Ekattorer Dinguli', 'Jahanara Imam', 'Bangladesh Studies', 'New', 'A personal diary from the Bangladesh Liberation War.'),
(3, 'The Blood Telegram', 'Gary J. Bass', 'Bangladesh Studies', 'Fair', 'An account of diplomacy and the 1971 Bangladesh crisis.'),
(4, 'Untranquil Recollections', 'Rehman Sobhan', 'Bangladesh Studies', 'Good', 'A memoir discussing political and economic events in Bangladesh.'),
(5, 'A History of Bangladesh', 'Willem van Schendel', 'Bangladesh Studies', 'Good', 'An overview of the country’s social, political and cultural history.'),
(6, 'The Rule of Law', 'Tom Bingham', 'Law', 'Good', 'Explains the meaning and importance of the rule of law.'),
(7, 'Learning the Law', 'Glanville Williams', 'Law', 'New', 'A beginner-friendly introduction to legal study and reasoning.'),
(8, 'Constitutional Law of Bangladesh', 'Mahmudul Islam', 'Law', 'Fair', 'A reference on constitutional principles and cases in Bangladesh.'),
(9, 'International Law', 'Malcolm N. Shaw', 'Law', 'Good', 'A comprehensive introduction to public international law.'),
(10, 'The Concept of Law', 'H. L. A. Hart', 'Law', 'Good', 'A major work on legal systems, rules and authority.');