------------------------------------------------------------
-- Table structure for patient request for portal data change
-------------------------------------------------------------
CREATE TABLE IF NOT EXISTS patient_request
(
	id int NOT NULL AUTO_INCREMENT,
	patient_id int, /*Table-patients-ID*/
	request_type enum('C', 'U', 'D') NOT NULL, /*'C'-Create 'U'-Update, 'D'-Delete*/
	data_source	enum('patient', 'kin', 'contact', 'address', 'doc') NOT NULL,
	data_source_id  int NOT NULL, /*patient-patient_portal-ID, kin-next_ofkin_portal-ID, address-address_portal-ID, contactcontact_portal-ID, doc-doc_id_portal-ID*/
	status enum('A', 'U', 'R', 'D') default 'U',	/*'A'-Approve 'U'-Un-Approve, R-Reject, D-Delete*/
	remarks	varchar(1000) NULL,
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	approved_at datetime(0) NULL,
	approved_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
);


/*Temporary table to capture patient data change request*/	
CREATE TABLE IF NOT EXISTS patient_portal
(
	id int NOT NULL AUTO_INCREMENT ,
	patient_id int NOT NULL,	/*Table-patients-ID*/
	first_name varchar(100)  NULL ,
	middle_name varchar(100) DEFAULT '' ,
	last_name varchar(100)  NULL ,
	patient_name varchar(200) NULL ,
	age varchar(10)  NULL,
	month varchar(200)  NULL,
	days int NULL DEFAULT 0,
	image varchar(100)  NULL DEFAULT NULL,
	dob date NULL DEFAULT NULL,
	marital_status varchar(100)  NULL,
	blood_group varchar(200)  NULL,
	is_delete	enum('Y', 'N') DEFAULT 'N',
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


/*Temporary table to capture next of kin data change request*/
CREATE TABLE IF NOT EXISTS next_ofkin_portal  (
	id int NOT NULL AUTO_INCREMENT ,
	patient_id int NOT NULL,		/*Table-patients-ID*/
	next_ofkin_id int NOT NULL,		/*Table-next_ofkin-ID*/
	registration_date date  null,
	relation_type varchar(50) NULL ,	/*lookup*/
	title varchar(50) NULL ,
	first_name varchar(100) NULL ,
	middle_name varchar(100) DEFAULT '' ,
	last_name varchar(100) NULL ,
	next_ofkin_name varchar(200) NULL ,
	image varchar(100)  NULL DEFAULT NULL,
	age INT DEFAULT 0,
	dob date NULL DEFAULT NULL,
	is_delete	enum('Y', 'N') DEFAULT 'N',
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


/*Temporary table to capture patient and kin address data change request*/
CREATE TABLE IF NOT EXISTS address_portal  (
	id int NOT NULL AUTO_INCREMENT,
	patient_id int NOT  NULL,		/*Table-patients-ID*/
	address_id int NOT NULL ,			/*Table-addresses-ID*/
	address_type varchar(50) NULL,		/*lookup*/
	pin	INT NULL,
	country	varchar(100) NULL,
	state	varchar(100) NULL,
	district	varchar(100) NULL,
	city	varchar(100) NULL,
	police_station	varchar(100) NULL,
	locality	varchar(100) NULL,
	house_no	varchar(100) NULL,
	street	varchar(100) NULL,
	is_delete	enum('Y', 'N') DEFAULT 'N',
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


/*Temporary table to capture patient and kin contact data change request*/
CREATE TABLE IF NOT EXISTS contact_portal  (
	id int NOT NULL AUTO_INCREMENT,
	patient_id int NOT  NULL,	/*Table-patients-ID*/
	contact_id int NOT  NULL,				/*Table-contacts-ID*/
	contact_type varchar(50) NULL,		/*lookup*/
	contact_details varchar(50) NULL,
	is_delete	enum('Y', 'N') DEFAULT 'N',
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


/*Temporary table to capture patient and kin doc, id and images data change request*/
CREATE TABLE IF NOT EXISTS doc_id_portal  (
	id int NOT NULL AUTO_INCREMENT,
	patient_id int NOT  NULL,	/*Table-patients-ID*/
	doc_id_id int NOT  NULL,				/*Table-doc_id_-ID*/
	doc_mast_type enum('ID','DOCUMENT', 'IMAGE', 'OTHER') NOT  NULL, /*lookup*/
	doc_type varchar(50) NOT NULL,	/*lookup*/
	doc_heading 	varchar(100) NOT NULL,
	doc_number 	varchar(100) NOT NULL,
	image varchar(100) NOT NULL ,
	is_delete	enum('Y', 'N') DEFAULT 'N',
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;

