On

-- 1. Warehouse Table

CREATE TABLE Warehouse (

    Warehouse_ID INT PRIMARY KEY,

    Name VARCHAR(100) NOT NULL,

    Location VARCHAR(150),

    Capacity INT

);

-- 2. Item Table

CREATE TABLE Item (

    Item_ID INT PRIMARY KEY,

    Name VARCHAR(100) NOT NULL,

    Description TEXT,

    Unit VARCHAR(50),

    Category VARCHAR(100),

    Reorder_Level INT

);

-- 3. Supplier Table

CREATE TABLE Supplier (

    Supplier_ID INT PRIMARY KEY,

    Name VARCHAR(100) NOT NULL,

    Contact VARCHAR(100),

    Address VARCHAR(200)

);

-- 4. Customer Table

CREATE TABLE Customer (

    Customer_ID INT PRIMARY KEY,

    Name VARCHAR(100) NOT NULL,

    Contact VARCHAR(100),

    Address VARCHAR(200)

);

-- 5. Stock Table

CREATE TABLE Stock (

    Stock_ID INT PRIMARY KEY,

    Warehouse_ID INT,

    Item_ID INT,

    Quantity INT NOT NULL,

    Last_Updated DATE,

    FOREIGN KEY (Warehouse_ID) REFERENCES Warehouse(Warehouse_ID),

    FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID)

);

-- 6. Purchase Order Tables

CREATE TABLE PurchaseOrder (

    PO_ID INT PRIMARY KEY,

    Supplier_ID INT,

    Date DATE,

    Status VARCHAR(50),

    FOREIGN KEY (Supplier_ID) REFERENCES Supplier(Supplier_ID)

);

CREATE TABLE PurchaseOrderDetail (

    PO_ID INT,

    Item_ID INT,

    Quantity INT,

    Price DECIMAL(10,2),

    PRIMARY KEY (PO_ID, Item_ID),

    FOREIGN KEY (PO_ID) REFERENCES PurchaseOrder(PO_ID),

    FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID)

);

-- 7. Sales Order Tables

CREATE TABLE SalesOrder (

    SO_ID INT PRIMARY KEY,

    Customer_ID INT,

    Date DATE,

    Status VARCHAR(50),

    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)

);

CREATE TABLE SalesOrderDetail (

    SO_ID INT,

    Item_ID INT,

    Quantity INT,

    Price DECIMAL(10,2),

    PRIMARY KEY (SO_ID, Item_ID),

    FOREIGN KEY (SO_ID) REFERENCES SalesOrder(SO_ID),

    FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID)

);

-- 8. Stock Movement Table

CREATE TABLE StockMovement (

    Movement_ID INT PRIMARY KEY,

    Item_ID INT,

    Warehouse_ID INT,

    Date DATE,

    Movement_Type VARCHAR(10) CHECK (Movement_Type IN ('IN','OUT')),

    Quantity INT,

    Ref_Order_ID INT,

    FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID),

    FOREIGN KEY (Warehouse_ID) REFERENCES Warehouse(Warehouse_ID)

);