CREATE TABLE tbl_service (
	service_id				SERIAL PRIMARY KEY,
	client_id				INTEGER NOT NULL REFERENCES tbl_client (client_id),
	contact_type_code			VARCHAR(10) NOT NULL DEFAULT 'FACE2FACE' REFERENCES tbl_l_contact_type(contact_type_code),
-- one or the other of these is uesed, and should be not null
	service_code			VARCHAR(10) REFERENCES tbl_l_service (service_code), 
	service_codes			VARCHAR(10)[] NOT NULL,
	service_site_code		VARCHAR(10) NOT NULL REFERENCES tbl_l_service_site (service_site_code),
	service_site_other		VARCHAR(100),
	street_location			VARCHAR(200),
	site_type_code			VARCHAR(10) REFERENCES tbl_l_site_type (site_type_code),
	geography_code 			VARCHAR(10) REFERENCES tbl_l_geography (geography_code) DEFAULT 'SEATTLE',
	geography_detail_code INTEGER REFERENCES tbl_l_geography_detail (geography_detail_code),
	service_minutes 			INTEGER NOT NULL CHECK (service_minutes >= 0),
	service_by				INTEGER NOT NULL REFERENCES tbl_staff (staff_id),
	service_date			TIMESTAMP(0) NOT NULL CHECK (service_date::date <= CURRENT_DATE),
	service_plan_status_code	VARCHAR(10) REFERENCES tbl_l_service_plan_status (service_plan_status_code),
	accessed_health_care_non_urgent_codes	VARCHAR(10)[], -- REFERENCES l_health_care_non_urgent (health_care_non_urgent_code),
	accessed_services_mh_codes	VARCHAR(10)[], -- REFERENCES l_services_mh (services_cd_code),
	accessed_services_cd_codes	VARCHAR(10)[], -- REFERENCES l_services_cd (services_cd_code),
	completed_services_cd_codes	VARCHAR(10)[], -- REFERENCES l_services_cd (services_cd_code),
	other_living_situation_codes	VARCHAR(10)[], -- REFERENCES l_living_situation (living_situation_code),
	asam_dimension_1			BOOLEAN NOT NULL DEFAULT FALSE,
	asam_dimension_2			BOOLEAN NOT NULL DEFAULT FALSE,
	asam_dimension_3			BOOLEAN NOT NULL DEFAULT FALSE,
	asam_dimension_4			BOOLEAN NOT NULL DEFAULT FALSE,
	asam_dimension_5			BOOLEAN NOT NULL DEFAULT FALSE,
	asam_dimension_6			BOOLEAN NOT NULL DEFAULT FALSE,
	progress_note			TEXT,
--	progress_note_data		TEXT,
--	progress_note_assessment TEXT,
--	progress_note_plan		TEXT,
--	progress_note_other		TEXT,
	progress_note_not_needed BOOLEAN DEFAULT false,
	set_self_management_goals	BOOLEAN NOT NULL,
--	current_living_situation_code	VARCHAR(10) NOT NULL REFERENCES tbl_l_facility (facility_code),
	signs_symptoms_codes	VARCHAR(10)[],
	signs_symptoms_other	VARCHAR(100),
	diagnosis_service_codes	VARCHAR(10)[],
	diagnosis_service_mental_other	VARCHAR(100),
	diagnosis_service_health_other	VARCHAR(100),
--	diagnosis_other			VARCHAR(100),
--	illicit_drug_of_choice_code		VARCHAR(10) REFERENCES tbl_l_illicit_drug (illicit_drug_code),
--	is_injection_drug_user_code	VARCHAR(10) REFERENCES tbl_l_yes_no (yes_no_code),
	social_issues_codes		VARCHAR(10)[],
	social_issues_other		VARCHAR(100),
	service_project_code		VARCHAR(10) NOT NULL REFERENCES tbl_l_service_project ( service_project_code ),
	service_progress_note_id	INTEGER REFERENCES tbl_service ( service_id ),
	added_by     			INTEGER NOT NULL REFERENCES tbl_staff (staff_id),
	added_at     			TIMESTAMP(0)     NOT NULL DEFAULT CURRENT_TIMESTAMP,
	changed_by     			INTEGER NOT NULL  REFERENCES tbl_staff (staff_id),
	changed_at     			TIMESTAMP(0)     NOT NULL DEFAULT CURRENT_TIMESTAMP,
	is_deleted    			BOOLEAN NOT NULL DEFAULT FALSE,
	deleted_at   			TIMESTAMP(0),
	deleted_by   			INTEGER REFERENCES tbl_staff (staff_id),
	deleted_comment 			TEXT,
	sys_log 				TEXT

	CONSTRAINT asam_for_cd_only CHECK ((service_project_code='CD' AND
								(asam_dimension_1 OR asam_dimension_2 OR asam_dimension_3
									OR asam_dimension_4 OR asam_dimension_5 OR asam_dimension_6
									OR service_code='CD_TPR'))
							OR 
							(service_project_code<>'CD' AND NOT
								(asam_dimension_1 OR asam_dimension_2 OR asam_dimension_3
									OR asam_dimension_4 OR asam_dimension_5 OR asam_dimension_6
									OR service_code='CD_TPR'))),
	CONSTRAINT service_site_other_and_code CHECK (
		(service_site_code='OTHER' AND service_site_other IS NOT NULL) OR
		(service_site_code != 'OTHER' AND service_site_other IS NULL))

	CONSTRAINT service_site_collateral CHECK (
		(service_site_code!='COLLATERAL') OR (contact_type_code = 'INDIRECT'))

/*
	CONSTRAINT progress_note_dap_or_collateral CHECK (
		(progress_note_data IS NOT NULL
		AND progress_note_assessment IS NOT NULL
		AND progress_note_plan IS NOT NULL)
		OR contact_type_code='INDIRECT'
	)
*/
; 

/*
	CONSTRAINT progress_note_or_reference
		CHECK ( (progress_note IS NOT NULL AND service_progress_note_id IS NULL)
			OR (progress_note IS NULL AND service_progress_note_id IS NOT NULL) )
*/
);

CREATE VIEW service AS
	SELECT *
/*
	COALESCE('DATA: ' || progress_note_data || '\n','') ||
	COALESCE('ASSESSMENT: ' || progress_note_assessment || '\n','') ||
	COALESCE('PLAN: ' || progress_note_plan,'')
	|| CASE WHEN progress_note_plan IS NOT NULL THEN '\n' ELSE '' END
	|| COALESCE('\n' || progress_note_other,'')
	AS progress_note
*/
 FROM tbl_service WHERE NOT is_deleted;

CREATE INDEX index_tbl_service_client_id ON tbl_service ( client_id );
CREATE INDEX index_tbl_service_service_project_code ON tbl_service ( service_project_code );
CREATE INDEX index_tbl_service_optomized_client_id_project ON tbl_service ( service_project_code, client_id );
CREATE INDEX index_tbl_service_service_date ON tbl_service ( service_date );
CREATE INDEX index_tbl_service_service_progress_note_id ON tbl_service ( service_progress_note_id );
