database.sql

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
username VARCHAR(100) UNIQUE NOT NULL,
email VARCHAR(100),
phone VARCHAR(20),
password VARCHAR(255) NOT NULL,
role VARCHAR(50) DEFAULT 'staff',
warehouse VARCHAR(100),
otp VARCHAR(10),
otp_expiry DATETIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(150) NOT NULL,
sku VARCHAR(50) UNIQUE,
category VARCHAR(100),
unit VARCHAR(20),
stock INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
type VARCHAR(10),
quantity INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);