/*List of screen
1.	FoodItems Property on Same dashboard add 2 modal - 1. Ingredient detail, 2. Disease wise restriction
2.	Diets List on Same dashboard add 2 modal - 1. Disease wise suggested diets 2. Diet wise Suggested food items
3.	IPD Patient diet planning by dietician
4.	Food delivery tracker
5.	Order management
*/


/*List of food item*/
CREATE TABLE fooditems(
	id int NOT NULL AUTO_INCREMENT,
	item_id	int NOT NULL, /*Table-Items-ID*/
	food_group	varchar(50) NOT NULL, /*Lookup-FOOD_GROUP*/
	is_used_as_ingredient	enum('Y', 'N'),	/*Is the product used as ingredient in other food*/
	is_ingredient_require	enum('Y', 'N'), /*If the food item preapre with some other food by hospital then it value=Y, if it purchase directelly or no ingredient require then it value=N*/
	is_package_food enum('Y', 'N'), /*is package or not*/
	is_outside_purchase enum('Y', 'N'), /*is the item purchse by orher vendor*/
	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
);


/*List of ingredient for a food item*/
CREATE TABLE fooditem_ingredient_details(
	id int NOT NULL AUTO_INCREMENT,
	primary_item_id	int NOT NULL, /*Table-items-ID, main food item*/
	ingredient_item_id	int NOT NULL,	/*Table-items-ID, ingredient food item*/
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*List of restricted fooditem agsinst disease*/
CREATE TABLE fooditem_restricted_disease(
	id int NOT NULL AUTO_INCREMENT,
	item_id	int NOT NULL,	/*Table-items-ID*/
	disease_id	int NOT NULL,	/*Table-symptom_disease-ID*/
	remarks	text,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*List of diet name*/
CREATE TABLE diets  (
	id int NOT NULL AUTO_INCREMENT,
	diet_type varchar(50) NOT NULL, /*Type of diet - Lookup-DIET_TYPE*/
	diet_name varchar(100)	NOT NULL,
	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
);


/*List of diet suggested as per diets*/
CREATE TABLE diseasewise_suggested_diets  (
	id int NOT NULL AUTO_INCREMENT,
	diet_id	int NOT NULL ,	/*Table-diets-ID*/
	symptom_disease_id	int NOT NULL,	/*Table-symptom_disease-ID*/
	remarks	text,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*List of food item against diet*/
CREATE TABLE dietwise_fooditem(
	id int NOT NULL AUTO_INCREMENT,
	diet_id	int NOT NULL ,	/*Table-diets-ID*/
	item_id	int NOT NULL,	/*Table-items-ID*/
	remarks	text,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*List of food item against diet*/
CREATE TABLE patient_diet_planning(
	id int NOT NULL AUTO_INCREMENT,
	plan_date	DATE NOT NULL,  /*Plan date for diet*/
	ipd_admission_id int NOT NULL, /*Table-ipd_admission-ID*/
	patient_id	int NOT NULL,	/*Table-patients-ID*/
	case_id	int NOT NULL,	/*Table-case_details-ID*/
	visit_id	int NOT NULL,	/*Table-visit_details-ID*/
	diet_time	VARCHAR(50) NOT NULL, /*Lookup - DIET_TIME*/
	status enum('P','R', 'C', 'D') NOT NULL default 'P',/*P-Planned, R-ReSchedule, C-Cancel, D-Delivered*/
	cancel_reschedule_reason VARCHAR(50) NOT NULL, /*Lookup-FOOD_CANCEL_RESCHEDULE_REASON*/
	remarks text NULL,
	previous_diet_plan_id int NULL, /*Table -patient_diet_planning-ID, once it change or reschedule then capture previous ID*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);

/*Order management*/

/*Capture food delivery data to pateint*/
CREATE TABLE food_delivery_tracker(
	id int NOT NULL AUTO_INCREMENT,
	delivery_time	datetime NOT NULL,  /*food delivery date time*/
	order_source varchar(50),  /*Personal-Personal Order, Diet-Diet Planned*/
	order_dietplan_id int NOT NULL , /*Table-patient_diet_planning-ID, Order Table-ID*/
	patient_id	int NOT NULL,	/*Table-patients-ID*/
	bed_id	int NULL,
	status enum('D','N') NOT NULL default 'P', /*N-Not Delivered, D-Delivered*/
	is_there_any_complain	enum('Y','N') NULL default 'N', 
	complain_id	int NULL,
	remarks text NULL,
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);
