smarthealth_101.sql

smarthealth_101.sql

-- public."admin" definition

-- Drop table

-- DROP TABLE public."admin";

CREATE TABLE public."admin" (
	id uuid NOT NULL DEFAULT gen_random_uuid(),
	first_name varchar(150) NOT NULL,
	last_name varchar(150) NOT NULL,
	username varchar(50) NOT NULL,
	"password" varchar(200) NOT NULL,
	hospcode varchar(10) NOT NULL,
	CONSTRAINT admin_pk PRIMARY KEY (id)
);
CREATE INDEX admin_hospcode_idx ON public.admin USING btree (hospcode);
CREATE INDEX admin_username_idx ON public.admin USING btree (username, password);

-- public.gender definition

-- Drop table

-- DROP TABLE public.gender;

CREATE TABLE public.gender (
	gender int2 NOT NULL,
	"name" varchar(50) NOT NULL,
	CONSTRAINT gender_pk PRIMARY KEY (gender)
);

-- public.hospitals definition

-- Drop table

-- DROP TABLE public.hospitals;

CREATE TABLE public.hospitals (
	hospcode varchar(10) NOT NULL,
	hospname varchar(150) NULL,
	province_code varchar(2) NULL,
	district_code varchar(2) NULL,
	sub_district_code varchar(2) NULL,
	hospital_type_name varchar(150) NULL,
	hospital_type_id int4 NULL,
	CONSTRAINT hospitals_pk PRIMARY KEY (hospcode)
);

-- public.log_appointments definition

-- Drop table

-- DROP TABLE public.log_appointments;

CREATE TABLE public.log_appointments (
	id uuid NOT NULL DEFAULT gen_random_uuid(),
	hospcode varchar(10) NOT NULL,
	user_id uuid NOT NULL,
	cid varchar(20) NOT NULL,
	appointment_date date NOT NULL,
	appointment_time time(0) NOT NULL,
	created_at timestamptz(0) NULL DEFAULT now(),
	CONSTRAINT log_appointments_pk PRIMARY KEY (id)
);
CREATE INDEX log_appointments_cid_idx ON public.log_appointments USING btree (cid);
CREATE INDEX log_appointments_hospcode_idx ON public.log_appointments USING btree (hospcode);
CREATE INDEX log_appointments_user_id_idx ON public.log_appointments USING btree (user_id);

-- public.log_registers definition

-- Drop table

-- DROP TABLE public.log_registers;

CREATE TABLE public.log_registers (
	id uuid NOT NULL DEFAULT gen_random_uuid(),
	user_id uuid NOT NULL,
	cid varchar(20) NOT NULL,
	created_at timestamptz(0) NULL DEFAULT now(),
	hospcode varchar(10) NULL,
	first_name varchar(200) NULL,
	last_name varchar(200) NULL,
	person_type_id int4 NULL,
	person_risk_type_id int4 NULL,
	CONSTRAINT logs_register_pk PRIMARY KEY (id)
);
CREATE INDEX logs_register_cid_idx ON public.log_registers USING btree (cid);
CREATE INDEX logs_register_hospcode_idx ON public.log_registers USING btree (hospcode);
CREATE INDEX logs_register_user_id_idx ON public.log_registers USING btree (user_id);

-- public.person_risk_type definition

-- Drop table

-- DROP TABLE public.person_risk_type;

CREATE TABLE public.person_risk_type (
	person_risk_type_id int4 NOT NULL,
	person_risk_type_name varchar(152) NULL,
	person_type_id int4 NULL,
	CONSTRAINT person_risk_type_pk PRIMARY KEY (person_risk_type_id)
);
CREATE INDEX person_risk_type_person_type_id_idx ON public.person_risk_type USING btree (person_type_id);

-- public.person_type definition

-- Drop table

-- DROP TABLE public.person_type;

CREATE TABLE public.person_type (
	person_type_id int4 NOT NULL,
	person_type_name varchar(72) NULL,
	priority_id int4 NULL,
	auto_eligible varchar(1) NULL,
	CONSTRAINT person_type_pk PRIMARY KEY (person_type_id)
);

-- public.settings definition

-- Drop table

-- DROP TABLE public.settings;

CREATE TABLE public.settings (
	hospcode varchar(10) NOT NULL,
	mophic_username varchar(50) NULL,
	mophic_password_hash varchar(200) NULL,
	chw_code varchar(2) NULL,
	amp_code varchar(4) NULL,
	CONSTRAINT settings_pk PRIMARY KEY (hospcode)
);
CREATE INDEX settings_amp_code_idx ON public.settings USING btree (amp_code);
CREATE INDEX settings_chw_code_idx ON public.settings USING btree (chw_code);

-- public.tokens definition

-- Drop table

-- DROP TABLE public.tokens;

CREATE TABLE public.tokens (
	hospcode varchar(10) NOT NULL,
	"token" text NOT NULL,
	updated timestamptz(0) NULL DEFAULT now(),
	CONSTRAINT tokens_pk PRIMARY KEY (hospcode)
);

-- public.users definition

-- Drop table

-- DROP TABLE public.users;

CREATE TABLE public.users (
	id uuid NOT NULL DEFAULT gen_random_uuid(),
	first_name varchar(50) NOT NULL,
	last_name varchar(50) NOT NULL,
	username varchar(50) NOT NULL,
	"password" varchar(200) NOT NULL,
	hospcode varchar(10) NOT NULL,
	enabled bool NULL DEFAULT true,
	tmb_code varchar(6) NOT NULL,
	CONSTRAINT users_pk PRIMARY KEY (id)
);
CREATE INDEX users_enabled_idx ON public.users USING btree (enabled);
CREATE INDEX users_hospcode_idx ON public.users USING btree (hospcode);
CREATE INDEX users_tmb_code_idx ON public.users USING btree (tmb_code);
CREATE INDEX users_username_idx ON public.users USING btree (username, password);
CREATE UNIQUE INDEX users_username_unique_idx ON public.users USING btree (username);

-- public.vaccine_screen definition

-- Drop table

-- DROP TABLE public.vaccine_screen;

CREATE TABLE public.vaccine_screen (
	vaccine_screen_id int4 NOT NULL,
	vaccine_screen_name varchar(119) NULL,
	vaccine_code varchar(3) NULL,
	gender int4 NULL,
	CONSTRAINT vaccine_screen_pk PRIMARY KEY (vaccine_screen_id)
);