CREATE TABLE [users](
	[serial_no] INTEGER PRIMARY KEY AUTOINCREMENT,
	[id] CHAR(16) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK,
	[name] CHAR(64) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK,
	[login_pwd] CHAR(64) NOT NULL ON CONFLICT ROLLBACK,
	[systemsetting_pwd] CHAR(64) NOT NULL ON CONFLICT ROLLBACK,
	[exportdata_pwd] CHAR(64) NOT NULL ON CONFLICT ROLLBACK);

CREATE TABLE [req_dept](
	[serial_no] INTEGER PRIMARY KEY AUTOINCREMENT,
	[code] CHAR(8) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK,
	[name] CHAR(30) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK);

CREATE TABLE [exam_master](
	[serial_no] INTEGER PRIMARY KEY AUTOINCREMENT,
	[patient_id] CHAR(64) NOT NULL ON CONFLICT ROLLBACK REFERENCES [patient_info]([id]) ON DELETE RESTRICT ON UPDATE CASCADE,
	[exam_id] CHAR(64) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK,
	[exam_datetime] DATETIME,
	[application_no] CHAR(64),
	[body_height] FLOAT,
	[body_height_unit] INT NOT NULL ON CONFLICT ROLLBACK,
	[body_weight] FLOAT,
	[body_weight_unit] INT NOT NULL ON CONFLICT ROLLBACK,
	[cbp_systolic] FLOAT,
 	[cbp_diastolic] FLOAT,
	[bp_unit] INT NOT NULL ON CONFLICT ROLLBACK,
	[technician] CHAR(64) REFERENCES [users]([name]) ON DELETE RESTRICT ON UPDATE CASCADE,
	[reporter] CHAR(64) REFERENCES [users]([name]) ON DELETE RESTRICT ON UPDATE CASCADE,
	[req_dept] CHAR(30) REFERENCES [req_dept]([name]) ON DELETE RESTRICT ON UPDATE CASCADE,
	[ward_no] CHAR(32),
 	[sickbed_no] CHAR(32),
	[patient_source] INT NOT NULL ON CONFLICT ROLLBACK,
	[visit_id] CHAR(64),
	[disease_history] CHAR(400),
	[medicine_history] CHAR(400),
	[custom_1] CHAR(100),
	[custom_2] CHAR(100),
	[result_status] INT NOT NULL ON CONFLICT ROLLBACK,
	[data_path] CHAR(256),
	[record_length] INT NOT NULL ON CONFLICT ROLLBACK,
	[measure_part] INT NOT NULL ON CONFLICT ROLLBACK,
	[last_result_status] INT,
	[device_model] INT NOT NULL ON CONFLICT ROLLBACK,
	[clinical_diagnosis] CHAR(400),
	[file_guid] CHAR(64) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK);

CREATE TABLE [patient_info](
	[serial_no] INTEGER PRIMARY KEY AUTOINCREMENT,
	[id] CHAR(64) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY,
	[name] CHAR(64),
	[sex] INT NOT NULL ON CONFLICT ROLLBACK,
	[birthday] DATETIME,
	[phone_number] CHAR(30),
	[address] CHAR(200),
	[race] INT NOT NULL ON CONFLICT ROLLBACK,
	[patient_guid] CHAR(64) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK);

CREATE TABLE [other_info](
	[serial_no] INTEGER PRIMARY KEY AUTOINCREMENT,
	[version] INT NOT NULL ON CONFLICT ROLLBACK);
	