scheme.sql

-- Create the database
CREATE DATABASE IF NOT EXISTS library_db;
USE library_db;

-- Users table (for login authentication)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL
);

-- Default admin user (password: password123)
INSERT INTO users (username, password)
VALUES ('admin', SHA2('password123', 256))
ON DUPLICATE KEY UPDATE username = username;

-- Books table
CREATE TABLE IF NOT EXISTS books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  author VARCHAR(100) NOT NULL,
  year INT,
  available BOOLEAN DEFAULT TRUE
);

-- Issues table
CREATE TABLE IF NOT EXISTS issues (
  id INT AUTO_INCREMENT PRIMARY KEY,
  member VARCHAR(100) NOT NULL,
  book_id INT NOT NULL,
  issue_date DATE NOT NULL,
  return_date DATE NOT NULL,
  FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
);