-- ----------------------------
-- Table structure for patients - Capture patient information for lab and hospital
-- ----------------------------
CREATE TABLE IF NOT EXISTS patients
(
	id int NOT NULL AUTO_INCREMENT ,
	patient_type varchar(200)  NOT NULL,	/*Lookup-PATIENT_TYPE*/
	patient_unique_id varchar(50) NOT NULL,
	old_patient_id varchar(50)  NULL,
	admission_date date NOT NULL,
	title varchar(50) NOT NULL ,	/*Lookup-TITLE_TYPE*/
	first_name varchar(100) NOT NULL ,
	middle_name varchar(100) DEFAULT '' ,
	last_name varchar(100) NOT NULL ,
	patient_name varchar(200) NOT NULL ,
	age varchar(10)  NOT NULL,
	month varchar(200)  NOT NULL,
	days int NOT NULL DEFAULT 0,
	image varchar(100)  NULL DEFAULT NULL,
	dob date NULL DEFAULT NULL,
	gender varchar(100)  NULL DEFAULT NULL,		/*Lookup-GENDER_TYPE*/
	marital_status varchar(100)  NOT NULL,		/*Lookup-MARITAL_STATUS*/
	blood_group varchar(200)  NOT NULL,			/*Lookup-BLOOD_GROUP*/
	is_active enum('N','Y') NOT NULL DEFAULT 'Y',
	credit_limit varchar(50)  NULL DEFAULT NULL,
	note varchar(1000)  NOT NULL,
	is_walk_in enum('Y','N')  NOT NULL DEFAULT 'Y',
	expiry_dt date NULL DEFAULT NULL,
	is_expired enum('N','Y')  NOT NULL,
	is_reg_charge_taken enum('N','Y')  NOT NULL,
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	disable_at date NULL DEFAULT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


-- --------------------------------
-- Table structure for next of kin - patient relative information
-- --------------------------------
CREATE TABLE IF NOT EXISTS next_ofkin  (
	id int NOT NULL AUTO_INCREMENT,
	patient_id varchar(200)  NOT NULL,	/*Table-patients-ID*/
	registration_date date not null,
	relation_type varchar(50) NOT NULL ,	/*Lookup-RELATION_TYPE*/
	title varchar(50) NOT NULL ,			/*Lookup-TITLE_TYPE*/
	first_name varchar(100) NOT NULL ,
	middle_name varchar(100) DEFAULT '' ,
	last_name varchar(100) NOT NULL ,
	next_ofkin_name varchar(200) NOT NULL ,
	image varchar(100)  NULL DEFAULT NULL,
	age INT DEFAULT 0,
	dob date NULL DEFAULT NULL,
	is_active enum('Y','N')  NOT NULL DEFAULT 'Y',
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


-- --------------------------------------------
-- Table structure for patient and kin address
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS addresses  (
	id int NOT NULL AUTO_INCREMENT,
	data_source varchar(200)  NOT NULL,	/*data table source - Patient-patients table, doctor-doctors table, kin-next_ofkin table*/
	data_source_id INT NOT NULL,	/*data table source ID - Patient-patients.ID, doctor-doctors.ID table, kin-next_ofkin.ID table*/
	address_type varchar(50) NOT NULL, /*Lookup-ADDRESS_TYPE*/
	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_active enum('Y','N')  NOT NULL DEFAULT 'Y',
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


-- --------------------------------------------
-- Table structure for patient and kin contacts
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS contacts  (
	id int NOT NULL AUTO_INCREMENT,
	data_source varchar(200)  NOT NULL,	/*data table source - Patient-patients table, doctor-doctors table, kin-next_ofkin table*/
	data_source_id INT NOT NULL,	/*data table source ID - Patient-patients.ID, doctor-doctors.ID table, kin-next_ofkin.ID table*/
	contact_type varchar(50) NOT NULL,
	contact_details varchar(50) NOT NULL,
	is_primary enum('Y','N')  NOT NULL DEFAULT 'N',
	is_active enum('Y','N')  NOT NULL DEFAULT 'Y',
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


-- -------------------------------------------------------
-- Table structure for patient and kin doc, id and images
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS doc_ids  (
	id int NOT NULL AUTO_INCREMENT,
	data_source varchar(200)  NOT NULL,	/*data table source - Patient-patients table, doctor-doctors table, kin-next_ofkin table*/
	data_source_id INT NOT NULL,	/*data table source ID - Patient-patients.ID, doctor-doctors.ID table, kin-next_ofkin.ID table*/
	doc_mast_type enum('ID','DOCUMENT', 'IMAGE', 'OTHER')  NOT NULL, /*Lookup-MAST_DOC_ID_TYPE*/
	doc_type varchar(50) NOT NULL,		/*Lookup-DOC_ID_TYPE, this lookup depends on MAST_DOC_ID_TYPE*/
	doc_heading 	varchar(100) NOT NULL,
	doc_number 	varchar(100) NOT NULL,
	image varchar(100)  NULL DEFAULT NULL,
	is_active enum('Y','N')  NOT NULL DEFAULT 'Y',
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


-- -------------------------------------------------------
-- Tables for patient portal - login details for patient
-- -------------------------------------------------------
CREATE TABLE patient_portal_login (
	id int not null AUTO_INCREMENT,
	patient_id int not null,		/*Table-patients-ID*/
	contact_number	int not null,	/*Fetch from contacts*/
	email_id	varchar(200) null,	/*Fetch from contacts*/
	login_password	varchar(1000) null,
	portal_register_date	datetime,
	portal_unregister_date	datetime,
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	register_otp	VARCHAR(10),
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


/*Capture data during login*/
CREATE TABLE patient_login (
	id int not null AUTO_INCREMENT,
	patient_id int not null,
	login_time	datetime not null,
	login_otp varchar(10),
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


-- ----------------------------
-- Table structure for patient_registration_log
-- ----------------------------
CREATE TABLE patient_registration_log  (
  id int NOT NULL AUTO_INCREMENT,
  patient_id int NOT NULL,
  privilage_card_no varchar(255) NULL DEFAULT NULL,
  reg_no	varchar(255) NULL DEFAULT NULL,
  created_at varchar(255) NULL DEFAULT NULL,
  created_by varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;
