-- Create Database
CREATE DATABASE SMS;
USE SMS;

-- Users Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL
);

-- Product Table
CREATE TABLE Product (
    productCode VARCHAR(20) PRIMARY KEY,
    productName VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    quantityInStock INT NOT NULL DEFAULT 0,
    unitPrice DECIMAL(10,2) NOT NULL,
    supplierName VARCHAR(100) NOT NULL,
    dateReceived DATE NOT NULL
);

-- Warehouse Table
CREATE TABLE Warehouse (
    warehouseCode VARCHAR(20) PRIMARY KEY,
    warehouseName VARCHAR(100) NOT NULL,
    warehouseLocation VARCHAR(200) NOT NULL
);

-- StockTransaction Table
CREATE TABLE StockTransaction (
    transactionId INT AUTO_INCREMENT PRIMARY KEY,
    productCode VARCHAR(20) NOT NULL,
    warehouseCode VARCHAR(20) NOT NULL,
    transactionDate DATE NOT NULL,
    quantityMoved INT NOT NULL,
    transactionType ENUM('IN', 'OUT') NOT NULL,
    FOREIGN KEY (productCode) REFERENCES Product(productCode),
    FOREIGN KEY (warehouseCode) REFERENCES Warehouse(warehouseCode)
);
