CREATE DATABASE warehouse_db;

USE warehouse_db;

CREATE TABLE assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    barcode VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    status ENUM('in_stock', 'checked_out') DEFAULT 'in_stock',
    assigned_to VARCHAR(255) DEFAULT NULL,
    location VARCHAR(255) DEFAULT 'Warehouse',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'storekeeper') DEFAULT 'storekeeper'
);

CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    barcode VARCHAR(255) NOT NULL,
    action ENUM('check_in', 'check_out') NOT NULL,
    user_id INT NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (barcode) REFERENCES assets(barcode),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users (username, password, role) VALUES ('admin', MD5('admin123'), 'admin');