53 lines
1.8 KiB
SQL
53 lines
1.8 KiB
SQL
CREATE TABLE IF NOT EXISTS inventory_categories (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name_en VARCHAR(255) NOT NULL,
|
|
name_ar VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_items (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
category_id INT,
|
|
name_en VARCHAR(255) NOT NULL,
|
|
name_ar VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
sku VARCHAR(100) UNIQUE,
|
|
unit VARCHAR(50) DEFAULT 'piece',
|
|
min_level INT DEFAULT 10,
|
|
reorder_level INT DEFAULT 20,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (category_id) REFERENCES inventory_categories(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_batches (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
item_id INT NOT NULL,
|
|
batch_number VARCHAR(100),
|
|
expiry_date DATE,
|
|
quantity INT NOT NULL DEFAULT 0,
|
|
cost_price DECIMAL(10, 2) DEFAULT 0.00,
|
|
supplier_id INT,
|
|
received_date DATE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_transactions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
item_id INT NOT NULL,
|
|
batch_id INT,
|
|
transaction_type ENUM('in', 'out', 'adjustment') NOT NULL,
|
|
quantity INT NOT NULL,
|
|
reference_type VARCHAR(50), -- 'purchase', 'consumption', 'manual_adjustment'
|
|
reference_id INT,
|
|
user_id INT,
|
|
transaction_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
notes TEXT,
|
|
FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (batch_id) REFERENCES inventory_batches(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
);
|