invetory.sql

CREATE TABLE warehouses (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(255)
);

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(120),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE product_variants (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT REFERENCES products(id),
    sku VARCHAR(60) UNIQUE NOT NULL,
    cost_price DECIMAL(10,2) NOT NULL,
    sell_price DECIMAL(10,2) NOT NULL,
    attributes JSONB, -- size/color/model
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE stock_items (
    id BIGSERIAL PRIMARY KEY,
    variant_id BIGINT REFERENCES product_variants(id),
    warehouse_id BIGINT REFERENCES warehouses(id),
    quantity INT DEFAULT 0,
    UNIQUE(variant_id, warehouse_id)
);

-- Supplier & Purchase (Stock IN)
CREATE TABLE suppliers (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    address TEXT
);

CREATE TABLE purchase_orders (
    id BIGSERIAL PRIMARY KEY,
    supplier_id BIGINT REFERENCES suppliers(id),
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE purchase_items (
    id BIGSERIAL PRIMARY KEY,
    purchase_id BIGINT REFERENCES purchase_orders(id),
    variant_id BIGINT REFERENCES product_variants(id),
    quantity INT NOT NULL,
    cost_price DECIMAL(10,2) NOT NULL
);

-- Customer & Sales (Stock OUT)
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    address TEXT
);

CREATE TABLE sales_orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT REFERENCES customers(id),
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE sales_items (
    id BIGSERIAL PRIMARY KEY,
    sale_id BIGINT REFERENCES sales_orders(id),
    variant_id BIGINT REFERENCES product_variants(id),
    quantity INT NOT NULL,
    sell_price DECIMAL(10,2) NOT NULL
);


-- 🏢 Inventory System Overview

-- This system manages:

-- Products

-- Stock quantities

-- Suppliers

-- Purchases (stock in)

-- Sales / Order (stock out)

-- Warehouses (optional multi-location support)