database.sql

-- First, drop the database and recreate it
DROP DATABASE IF EXISTS test_borrow;
CREATE DATABASE test_borrow CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test_borrow;

-- Disable foreign key checks and start transaction
SET FOREIGN_KEY_CHECKS = 0;
SET autocommit = 0;
START TRANSACTION;

-- Create users table
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    role ENUM('admin', 'approver', 'borrower', 'delivery') NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Create items table
CREATE TABLE items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    item_name VARCHAR(100) NOT NULL,
    description TEXT,
    quantity INT NOT NULL DEFAULT 1,
    status ENUM('available', 'borrowed', 'maintenance') DEFAULT 'available',
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Create borrow_requests table
CREATE TABLE borrow_requests (
    request_id INT PRIMARY KEY AUTO_INCREMENT,
    borrower_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    borrow_date DATETIME NOT NULL,
    expected_return_date DATETIME NOT NULL,
    status ENUM('pending', 'approved', 'rejected', 'borrowed', 'returned') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (borrower_id) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Create approvals table
CREATE TABLE approvals (
    approval_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT NOT NULL,
    approver_id INT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    approval_date DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES borrow_requests(request_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (approver_id) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Create returns table
CREATE TABLE returns (
    return_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT NOT NULL,
    return_date DATETIME NOT NULL,
    receiver_id INT,
    condition_notes TEXT,
    status ENUM('pending', 'completed', 'damaged') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES borrow_requests(request_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Create deliveries table
CREATE TABLE deliveries (
    delivery_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT NOT NULL,
    delivery_staff_id INT NOT NULL,
    delivery_date DATETIME,
    status ENUM('pending', 'in_progress', 'completed', 'failed') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES borrow_requests(request_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (delivery_staff_id) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Create activity_logs table
CREATE TABLE activity_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Insert sample users
INSERT INTO users (username, password, full_name, email, role) VALUES
('admin', '1111', 'System Administrator', 'admin@example.com', 'admin'),
('approver1', '2222', 'John Approver', 'approver@example.com', 'approver'),
('borrower1', '3333', 'Jane Borrower', 'borrower@example.com', 'borrower'),
('delivery1', '4444', 'Mike Delivery', 'delivery@example.com', 'delivery');

-- Insert sample items
INSERT INTO items (item_name, description, quantity, category) VALUES
('Laptop Dell XPS 13', 'Business laptop with 16GB RAM', 5, 'Electronics'),
('Projector Epson', 'HD Projector for presentations', 3, 'Electronics'),
('Meeting Room Key', 'Key for meeting room A101', 2, 'Keys'),
('iPad Pro', '12.9 inch iPad Pro with Apple Pencil', 4, 'Electronics'),
('Conference Phone', 'Polycom conference phone system', 2, 'Electronics');

-- Create indexes
CREATE INDEX idx_user_role ON users(role);
CREATE INDEX idx_item_status ON items(status);
CREATE INDEX idx_borrow_status ON borrow_requests(status);
CREATE INDEX idx_approval_status ON approvals(status);
CREATE INDEX idx_return_status ON returns(status);
CREATE INDEX idx_delivery_status ON deliveries(status);

-- Commit and cleanup
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SET autocommit = 1;