/*Department wise purchase requisitions*/
CREATE TABLE purchase_requisitions (
	id int not null auto_increment,
	pr_slip_number varchar(100) not null, /*auto generate*/
	pr_date date not null,
	department_id int not null, /*HRMS - Department*/
	requisition_raisedby int null, /*HRMS Employee ID filter by dept*/
	pr_note varchar(1000) null,
	status varchar(30) not null, /*U-Unapprove, R-Rejected, A-Approved*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Department wise purchase requisition item detail under each purchase requisition*/
CREATE TABLE purchase_requisitions_details (
	id int not null auto_increment,
	purchase_requisition_id int not null,	/*Table-purchase_requisitions-ID*/
	item_id int not null,				/*Table-Items-ID*/
	quantity decimal(12, 2) not null,
	is_deleted enum('N','Y') default 'N',
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*maintain requisition wise stock and item issue*/
CREATE TABLE stock_item_requisition(
	id int not null auto_increment,
	stock_date int not null, /*requisition or issue date*/
	item_id int not null,		/*Table-Items-ID*/
	req_qty decimal(12, 2) null,
	issue_qty decimal(12, 2) null,
	tot_quantity decimal(12, 2) not null,
	primary key (id) using btree
);


/*Capture department wise consumption header*/
CREATE TABLE departmentwise_consumptions (
	id int not null,
	consumption_date date,
	purchase_requisition_id int not default 0,
	department_id int not null, /*HRMS - Department*/
	responsible_person int not null,/*HRMS Employee ID filter by dept*/
	consumption_note text null,
	status varchar(50) not null, /*U-Unapprove, A-Approved*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture department wise consumption items*/
CREATE TABLE departmentwise_consumption_items (
	id int not null auto_increment,
	departmentwise_consumption_id int not null,	/*Table-departmentwise_consumptions-ID*/
	item_id int not null,	/*Table-Items-ID*/
	requisition_quantity decimal(12, 2) not null default 0.00,	/*requisition quantity fetch from stock - stock_item_requisition*/
	consumption_quantity decimal(12, 2) not null,
	is_deleted enum('N','Y') default 'N',
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture purchase order header information*/
CREATE TABLE purchase_orders (
	id int not null,
	order_date date not null,
	po_order_number varchar(100) not null,
	vendor_org_id int not null,		/*Table - suppliers -ID*/
	org_contact_person varchar(100) null,
	expected_delivery_date date null,
	po_note varchar(1000) null,
	status varchar(30) not null,
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture purchase order information item wise*/
CREATE TABLE purchase_order_items (
	id int not null,
	purchase_order_id int not null,	/*Table-purchase_orders-ID*/
	item_id int not null,			/*Table-Items-ID*/
	tax_group_id	int not null,		/*Table-tax_group-ID*/
	quantity decimal(12, 2) not null,
	standard_purchase_price decimal(12, 2) null,
	status varchar(50) not null,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture purchase order receipt or good receipt information, it capture PO if exist*/
CREATE TABLE purchase_order_receipts(
	id int not null auto_increment,
	supplier_id	int not null,		/*Table - suppliers -ID*/
	purchase_date date not null,	
	purchase_order_id int not null,	/*Table-purchase_orders-ID*/
	po_receipt_number varchar(100) not null,
	bill_no varchar(100) not null,
	por_note varchar(1000) null,
	gross_price decimal(12, 2) not null,
	discount_percentage decimal(12, 2) null,
	discount_amount decimal(12, 2) not null,
	total_cgst decimal(12, 2) not null,
	total_sgst decimal(12, 2) not null,
	net_price decimal(12, 2) not null,
	due_amount decimal(12, 2) null default 0.00,
	status varchar(50) not null,	/*U-Unapprove, A-Approve, R-Reject*/
	purchase_note	text null,
	attach_po_doc varchar(200) null,
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);

/*Capture purchase order receipt or good receipt item wise information agsinst purchase_order_receipt ID*/
CREATE TABLE purchase_order_receipt_items (
	id int not null,
	po_receipt_id int not null,		/*Table-purchase_order_receipts-ID*/
	item_id int not null,			/*Table-Items-ID*/
	tax_group_id	int not null,		/*Table-tax_group-ID*/
	batch_no	varchar(100) not null,
	lot_no	varchar(100)  null,
	manufacture_date	date null,
	expiry_date	date null,
	expiry_note	text null,
	shipped_quantity decimal(12, 2) null,	/*Total quantity shipped*/
	receive_quantity decimal(12, 2) not null,	/*Total quantity received*/
	pack_size	decimal(12, 2) not null,
	indv_qty_each_pack	decimal(12, 2) not null,
	mrp	decimal(12, 2) not null,
	standard_purchase_price decimal(12, 2) null,
	receive_price decimal(12, 2) not null,
	discount_percentage decimal(12, 2) null,
	discount_amount decimal(12, 2) not null,
	cgst decimal(12, 2) not null,
	sgst decimal(12, 2) not null,
	net_price decimal(12, 2) not null,
	status varchar(50) not null,	/*Y-Active, N-Deleted*/
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture purchase order return header information against purchase receipt*/
CREATE TABLE purchase_order_returns (
	id int not null,
	po_receipt_id int not null,			/*Table-purchase_order_receipts-ID*/
	return_slipnumber varchar(255)  null,
	return_reason varchar(50) not null, /*Lookup PR_RETURN_REASON*/
	return_note varchar(1000) null,
	gross_return_price decimal(12, 2) not null,
	discount_amount decimal(12, 2) not null default 0.00,
	total_cgst decimal(12, 2) not null,
	total_sgst decimal(12, 2) not null,
	net_return_price decimal(12, 2) not null,
	is_adjust	enum('Y', 'N') default 'N', /*is the return bill adjusted against purchase or good receipt*/
	status varchar(50) not null,			/*U-Unapprove, A-Approve, R-Reject*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture purchase order return item information against a partucular purchase order return*/
CREATE TABLE purchase_order_return_items (
	id int not null,
	po_receipt_item_id int not null,		/*ID of purchase_order_receipt_items*/
	po_receipt_id int not null,				/* ID of purchase_order_receipts*/
	purchase_order_return_id int not null,	/* ID of purchase_order_returns*/
	batch_no varchar(100) not null default '',
	lot_no varchar(100) null,
	item_id int not null,
	quantity decimal(12, 2) not null,
	return_price decimal(12, 2) null,
	discount_amount decimal(12, 2) not null default 0.00,
	cgst decimal(12, 2) not null,
	sgst decimal(12, 2) not null,
	net_price decimal(12, 2) not null,
	status varchar(50) not null,	/*Y-Active, N-Deleted*/
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture stock against good receipt, return and sell this table maintain each and every operation to maintain batch wise stock*/
drop table good_receipt_details;
CREATE TABLE good_receipt_details (
	id int not null AUTO_INCREMENT,
	prev_good_receipt_id int not null default 0,	/*ID of purchase_order_receipt_items*/
	item_id int not null,	/*ID of items*/
	item_code	varchar(50) not null, /*Fetch from items table - item_code column*/
	po_receipts_id	int not null,	/*ID of purchase_order_receipts*/
	po_receipts_item_id	int not null,	/*ID of purchase_order_receipt_items*/
	good_receipt_date	date not null, /*Data of good receipt*/
	batch_number	varchar(50) not null, /*Batch number from  purchase_order_receipts*/
	lot_number varchar(50) null,	/*Lot number number from  purchase_order_receipts*/
	grn_number VARCHAR(20) not null,	/*Auto generated number in form of - YYYYMM*/
	good_receipt_serial_number	int not null,	/*Auto serial number under each grn_number, monthly its start from 1 Beginning*/
	item_qty	decimal(12, 2) not null,		/*Data of good receipt*/
	item_mrp	decimal(12, 2) not null,		/*Data of good receipt or items*/
	reserve_for_loose_sale	enum('N','Y') default 'N', /*If the item reserve for loose sale*/
	inout_status varchar(50) not null,	/*R - receipt, T-Sell Return, E-Purchase Return, S-Sell, ST-Stock Transfer for loose sell, SR-Stock Receipt for loose sell*/
	manufactured_date	date null,	/*Data of good receipt*/
	entered_on date null,	
	expire_on date null,	/*Data of good receipt*/
	expire_note varchar(200) null,	/*Data of good receipt*/
	locator_id int null,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);

