


create table dem.session_plan ( 
pk serial primary key,
start_time interval, end_time interval, 
weekdays integer[7],
begin_date date,
end_date date , fk_staff integer references dem.staff
);


-- lifted from gmappoint.sql , author - Ian Heywood

CREATE TABLE dem.appt_location
       (
                pk SERIAL primary key,
                name text
        );

--====================================================================

CREATE TABLE dem.session (
                clinician INTEGER NOT NULL REFERENCES dem.staff ,
                start TIME NOT NULL,
                finish TIME NOT NULL,
                session_date date, 
                comment TEXT,
                location INTEGER REFERENCES dem.appt_location,
                pk SERIAL primary key
        );

CREATE TABLE dem.enum_app_type (
        pk serial primary key,
        description text
);

INSERT INTO dem.enum_app_type(description) VALUES ('standard');
INSERT INTO dem.enum_app_type(description) VALUES ('short');
INSERT INTO dem.enum_app_type(description) VALUES ('long');
INSERT INTO dem.enum_app_type(description) VALUES ('check-up geriatric');
INSERT INTO dem.enum_app_type(description) VALUES ('medication review');
INSERT INTO dem.enum_app_type(description) VALUES ('psychiatric');
INSERT INTO dem.enum_app_type(description) VALUES ('substance abuse - first');
INSERT INTO dem.enum_app_type(description) VALUES ('family');
INSERT INTO dem.enum_app_type(description) VALUES ('partner therapy');
INSERT INTO dem.enum_app_type(description) VALUES ('group therapy');


CREATE TABLE dem.preferred_app_length (
        pk serial primary key,
        fk_staff integer references dem.staff ,
        fk_enum_app_type integer references dem.enum_app_type,
        duration INTERVAL
);

--=======================================================================

CREATE TABLE appointment
       (
       		pk serial primary key,
                fk_session INTEGER REFERENCES dem.session ,
                fk_patient INTEGER  default null ,
                patient_name text,
                fk_app_type INTEGER REFERENCES dem.enum_app_type ,
                date TIMESTAMP
        );
