/*============================= Product wise Stock ======================-*/
CREATE VIEW product_stocks AS
SELECT purchase_details.product_id, 
	purchase_details.productcategory_id, 
	purchase_details.unit_id,
	products.re_order_label,
 SUM(purchase_details.now_stock) as product_stock
 FROM purchase_details,products WHERE purchase_details.now_stock > 0 and purchase_details.product_id = products.id 
GROUP by purchase_details.product_id 



CREATE VIEW purchase_stocks AS
 SELECT purchase_details.*,products.code, products.product_name FROM products,purchase_details 
	WHERE purchase_details.now_stock > 0
		AND purchase_details.product_id = products.id


/*============= Raw Purchase stock view table ==============*/
CREATE TABLE purchase_stocks (
    id int AUTO_INCREMENT PRIMARY KEY,
	product_id BIGINT(20) NOT NULL,
	productcategory_id BIGINT(20) NOT NULL,
	unit_id BIGINT(20) NOT NULL,
    code varchar(255),
    product_name varchar(255),
    rate varchar(255),
    now_stock varchar(255),
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
