/*capture list of item manufactures*/
create table item_manufactures (
	id int not null auto_increment,
	manufacture_name varchar(50) not null,
	manufacture_address varchar(200) null,
	manufacture_contact_number varchar(100) null,
	manufacture_contact_email varchar(100) null,
	manufacture_contact_person varchar(200) null,
	manufacture_description	varchar(1000) null,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree);


/*capture item category */
create table item_category (
	id int not null auto_increment,
	category_name varchar(50) not null,
	category_description varchar(50) null,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree);


/*capture item sub category or types, it may depend in item category or not*/
create table item_sub_category (
	id int not null auto_increment,
	item_category int not null default 0, /*Table - item_category - ID, 0 - Wehn its not under a subcategory*/
	sub_category_name varchar(1000) not null,
	sub_category_description varchar(1000)  null,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree);


/*capture list of items it would be assets, pharmacy, other inventory etc.*/
drop table items;
create table items (
	id int not null auto_increment,
	item_class varchar(30) not null,	/*Lookup - ITEM_CLASS - data - ASSET and INVENTORY*/
	item_group	varchar(30) not null,	/*Lookup - ITEM_GROUP - data - ASSET_ALL, INV_OPD, INV_IPD and INV_PH (Inventory - Pharmacy)*/
	item_category int not null,		/*Table - item_category - ID*/
	item_sub_category int not null,	/*Table - item_sub_category - ID*/
	tax_group_id int not null default 0,	/*Table - tax_group - ID*/
	item_packaging_uom varchar(30) not null, /*Lookup - ITEM_PACK_UOM*/
	pack_size int default 1,	/*Size of packet, if 1 Crosin packet contain 15	strip and we enter the whole packet as a single item then the pack_size=15 and if we enter each Strip as item then pack_size=1*/
	indv_item_qty_uom	int null,	/*Lookup - ITEM_PACK_UOM*/
	indv_item_qty		int null,	/*Item qunaitty, if a single crosin strip contain 15 tablets then the indv_item_qty=15 or a Surap contain 60ml Liquid then  indv_item_qty=60 */
	manufacture_id int  null, /*Table - item_manufactures - ID*/
	item_name varchar(200) not null,
	item_code	varchar(200) null, /*Item code for searching*/
	item_description varchar(1000),
	indv_dimension_type varchar(30) null, /*Item dimension Lookup - ITEM_DIM_TYPE - 2D, 3D*/
	indv_dimension_uom VARCHAR(30)  NULL,	/*Lookup - COMMON_UOM*/
	indv_item_length decimal(16, 3)  NULL, /*item length as per COMMON_UOM*/
	indv_item_width decimal(16, 3)  NULL,	/*item width as per COMMON_UOM*/
	indv_item_height decimal(16, 3)  NULL,	/*item height as per COMMON_UOM*/
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree);


/*Item wise price listing*/
create table item_prices (
	id int not null,
	item_id int not null, /*Table-items-ID*/
	effective_from_date date not null, /*Item price effective from*/
	effective_to_date date null,	/*Item price effective to*/
	is_chargable enum('N','Y') default 'Y', /*if the item is chargable or free*/
	is_discountable enum('N','Y') default 'Y',	/*if discount is allow to the item*/
	mrp decimal(12, 2) not null, /*Mximum retail price of the item, sell or purchase price should not cross the mrp*/
	standard_price decimal(12, 2) not null, /*price charge by hospital, default it set the MRP*/
	minium_price decimal(12, 2)  null, /*Minium purchaseable or sellable price for a item*/
	default_discount_type varchar(30),	/*Lookup - DISCOUNT_TYPE - PERCENTAGE and AMOUNT*/
	min_disc_amount decimal(12, 2),	/*Min disocunt amount for a item during sell*/
	max_disc_amount decimal(12, 2),	/*Max disocunt amount for a item during sell*/
	min_disc_percentage decimal(12, 2), /*Min disocunt percentage for a item during sell*/
	max_disc_percentage decimal(12, 2),	/*Max disocunt percentage for a item during sell*/
	note varchar(1000),
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree);


/*Maintain stock value for an item - currentely not implemented*/
CREATE TABLE item_stock (
	id INT NOT NULL,
	item_id INT NOT NULL,
	holding_unit decimal(16, 4) NOT NULL,
	total_unit decimal(16, 4) NOT NULL,
	primary key (id) using btree
	);


/*Capture item supplier or ventor list*/
CREATE TABLE suppliers  (
	id int NOT NULL auto_increment,
	supplier_code varchar(100)  NOT NULL,
	supplier_name varchar(255)  NOT NULL,
	description text  NULL,
	phone varchar(255)  NOT NULL,
	email varchar(255)  NULL,
	address varchar(255)  NULL,
	contact_person_name varchar(255)  NULL,
	contact_person_phone varchar(255)  NULL,
	contact_person_email varchar(255)  NULL,
	attach_doc_name varchar(100) NULL,
	attach_doc_dtls varchar(100) NULL,
	attach_doc blob  NULL,
	is_active enum('N','Y') default 'Y',
	supplier_or_bank enum('S','B','C') default 'S' NOT NULL, /* 'S' => Supplier,'B' => Hospital Bank,'C' => Cash*/
	has_cc_pay_gateway enum('Y','N') default 'N' NOT NULL, /* 'Y' => yes,'N' => No*/
	has_upi enum('Y','N') default 'N' NOT NULL, /* 'Y' => Yes,'N' => No*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
  primary key (id) using btree
) 
;


/*Capture item wise supplier list, many to many maping possible between iem and supplier
If operator select a already mapped supplier, he/she will be eligable for only mapped items*/
create table item_supplier_map (
	id int not null auto_increment,
	item_id INT NOT NULL,
	supplier_id	INT NOT NULL,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture group of tax category as per tax percentage like 8% GST or 12% GST*/
create table tax_group (
	id int not null auto_increment,
	tax_type	varchar(50) NOT  NULL, /*Lookup TAX_TYPE - VAT, GST*/
	tax_group_name	varchar(100)  NOT NULL,
	tax_group_description	varchar(1000)  NOT NULL,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture detail of tax under each group, it created to capture State(SGST) and Central(CGST) tax % saperately under each Tax Group*/
create table tax_group_detail (
	id int not null auto_increment,
	tax_group_id int not null, /*Table - tax_group - ID*/
	tax_group_detail_name	varchar(100)  NOT NULL,
	tax_group_category	varchar(50) NOT  NULL, /*Lookup TAX_GROUP_VATEGORY - state, central*/
	tax_gpercentage	decimal(12, 2) not null default 0.00,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);

/* item-group module master  */
CREATE TABLE item_groupwise_module(
	id int NOT NULL AUTO_INCREMENT,
	lookup_value_id int NOT NULL,
	module_id int NOT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;