dnl Copyright (C) 2013 The Meme Factory, Inc., http://www.meme.com/
dnl
dnl    This file is part of GombeMI.
dnl
dnl    GombeMI is free software; you can redistribute it and/or modify
dnl    it under the terms of the GNU General Public License as published by
dnl    the Free Software Foundation; either version 3 of the License, or
dnl    (at your option) any later version.
dnl
dnl    This program is distributed in the hope that it will be useful,
dnl    but WITHOUT ANY WARRANTY; without even the implied warranty of
dnl    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
dnl    GNU General Public License for more details.
dnl
dnl    You should have received a copy of the GNU General Public License
dnl    along with GombeMI.  If not, see <http://www.gnu.org/licenses/>.
dnl
dnl Creates prepared_data schema and tables therein.
dnl
dnl Karl O. Pinc <kop@meme.com>
dnl
dnl Mar 7, 2013
dnl
dnl Remarks:
dnl
dnl Intended to be piped to psql run as gombemi_admin.
dnl m4 includes
include(`copyright.m4')
include(`constants.m4')
include(`grants.m4')

DROP SCHEMA IF EXISTS prepared_data CASCADE;

CREATE SCHEMA prepared_data;

GRANT USAGE ON SCHEMA prepared_data TO gombemi_editors
                                  , gombemi_readers;

SET SEARCH_PATH TO prepared_data;

--
-- Create all the tables.
--
begin;
/*
drop table if exists prepared_data.follow cascade;
drop table if exists prepared_data.follow_1_minute cascade;
drop table if exists prepared_data.follow_1_minute_infant cascade;
drop table if exists prepared_data.follow_1_minute_mother cascade;
drop table if exists prepared_data.follow_1_minute_sibling cascade;
drop table if exists prepared_data.follow_5_minute cascade;
drop table if exists prepared_data.follow_5_minute_group cascade;
*/

-- cross_reference_for_program
create table prepared_data.crfp (
  tableid serial primary key
 ,"year" int
 ,"follow_date" date
 ,"focal_mom" text
 ,"focal_infant" text
 ,"siblings" text
 ,"researcher" text
 ,"pages" text
 ,"start_entry_date" date
 ,"end_entry_date" date
 ,"enterer" text
 ,"follow_status" text
 ,"entry_notes" text
 ,"group_comp_problem" text
 ,"computer" text
 ,"checked_over" text
 ,"also_checked_by" text
 ,"last_checked_by_date" text
 ,"absent_mother_infant" text
 ,"group_blank" text
 ,"protocol" text
 ,"template" text
 ,"temptype" text
 ,"scan_checked" text
 ,"paper_follow_location" text
 ,"inf_birthdate" text
 ,"infant_age" text
);

create unique index crfp_date_mom
       on prepared_data.crfp("follow_date", "focal_mom");

grant_priv(`prepared_data.crfp')
grant_seq_priv(`prepared_data.crfp', `tableid')


-- page_followtype
create table prepared_data.page_followtype (
  tableid serial primary key
 ,date date
 ,mom text
 ,page text
 ,temptype text
 ,done boolean
);

create index page_followtype_date_mom_page
       on prepared_data.page_followtype(date, mom, page);

grant_priv(`prepared_data.page_followtype')
grant_seq_priv(`prepared_data.page_followtype', `tableid')


-- follow
create table prepared_data.follow (
  tableid serial primary key
 ,"follow_date" date
 ,"mother_id" varchar
 ,"infant_id" varchar
 ,"infant2_id" varchar
 ,"sibling_id" varchar
 ,"sibling2_id" varchar
 ,"conducted_by" varchar
 ,"entered_by" varchar
 ,"sheet_id" int
 ,"left_group_id" int
 ,"right_group_id" int
 ,"follow_status" varchar
 ,"follow_id" varchar
 ,"time_mode" varchar
 ,"ampm" varchar
 ,done boolean
);


create unique index follow_follow_id on prepared_data.follow ("follow_id");
create index follow_date on prepared_data.follow ("follow_date");

grant_priv(`prepared_data.follow')
grant_seq_priv(`prepared_data.follow', `tableid')


-- follow_page
create table prepared_data.follow_page (
  tableid serial primary key
 ,"follow_id" text
     constraint "follow_id on follow" references prepared_data.follow(follow_id)
 ,"page_number" int
 ,"location" text
 ,"video" text
 ,"page_id" text
 , done boolean
);

create index follow_page_follow_id
  on prepared_data.follow_page("follow_id");
create unique index follow_page_page_id 
  on prepared_data.follow_page ("page_id");

grant_priv(`follow_page')
grant_seq_priv(`follow_page', `tableid')


-- follow_1_minute
create table prepared_data.follow_1_minute (
  tableid serial primary key
 ,"page_id" varchar
     constraint "page_id on follow_page" references prepared_data.follow_page(page_id)
 ,"interval_number" int
 ,"interval_time" timestamp without time zone
 ,"bad_observation" varchar
 ,"rain" varchar
 ,"comment" varchar
 ,"commentrtf" varchar
 ,"interval_id" varchar
 , follow_id varchar
     constraint "follow_id on follow" references prepared_data.follow(follow_id)
 ,done boolean
);

create unique index
  follow_1_minute_interval_id on prepared_data.follow_1_minute ("interval_id");

-- Data problem.  Let the conversion sort it out.
create unique index follow_1_minute_follow_id_interval_time
  on prepared_data.follow_1_minute (follow_id, interval_time);
--create index follow_1_minute_follow_id_interval_time
--  on prepared_data.follow_1_minute (follow_id, interval_time);

grant_priv(`prepared_data.follow_1_minute')
grant_seq_priv(`prepared_data.follow_1_minute', `tableid')


-- follow_1_minute_infant
create table prepared_data.follow_1_minute_infant (
  tableid serial primary key
 ,"interval_id" varchar
     constraint "follow_1_minute_infant.interval_id must be a follow_1_minute.interval_id"
        references prepared_data.follow_1_minute("interval_id")
 ,"infant_id" varchar
 ,"nursing" varchar
 ,"eating" varchar
 ,"walks" varchar
 ,"playing" varchar
 ,"resting" varchar
 ,"in_contact" varchar
 ,"within_reach" varchar
 ,"within_0_5_meters" varchar
 ,"between_5_15_meters" varchar
 ,"more_than_15_meters" varchar
 ,"rides_on_stomach" varchar
 ,"rides_on_back" varchar
 ,"dangles" varchar
 ,"contacts_other" varchar
 ,"grooms_other" varchar
 ,"groomed_by" varchar
 ,"plays" varchar
 ,"displays" varchar
 ,"refused" varchar
 ,"cries" varchar
 ,"vocalizations" varchar
 ,"begs" varchar
 ,"aggression" varchar
 ,"infant_interval_id" varchar
 , follow_id varchar
     constraint "follow_id on follow" references prepared_data.follow(follow_id)
 , done boolean
);

create unique index follow_1_minute_infant_infant_interval_id
  on prepared_data.follow_1_minute_infant ("infant_interval_id");

-- Data problems.  Let the conversion deal with it.
create unique index follow_1_minute_infant_interval_id_infant_id
  on prepared_data.follow_1_minute_infant ("interval_id", infant_id);
--create index follow_1_minute_infant_interval_id_infant_id
--  on prepared_data.follow_1_minute_infant ("interval_id", infant_id);

create unique index follow_1_minute_infant_interval_id
  on prepared_data.follow_1_minute_infant ("interval_id");

create index follow_1_minute_infant_follow_id
  on prepared_data.follow_1_minute_infant (follow_id);


grant_priv(`prepared_data.follow_1_minute_infant')
grant_seq_priv(`prepared_data.follow_1_minute_infant', `tableid')


-- follow_1_minute_mother

create table prepared_data.follow_1_minute_mother (
  tableid serial primary key
 ,"interval_id" varchar
     constraint "follow_1_minute_mother.interval_id must be a follow_1_minute.interval_id"
        references prepared_data.follow_1_minute("interval_id")
 ,"mother_id" varchar
 ,"behavior" varchar
 ,"grooms_other" varchar
 ,"groomed_by" varchar
 ,"vocalizations" varchar
 , follow_id varchar
     constraint "follow_id on follow" references prepared_data.follow(follow_id)
 , done boolean
);

create unique index follow_1_minute_mother_interval_id
  on prepared_data.follow_1_minute_mother ("interval_id");

create index follow_1_minute_mother_follow_id
  on prepared_data.follow_1_minute_mother (follow_id);

grant_priv(`prepared_data.follow_1_minute_mother')
grant_seq_priv(`prepared_data.follow_1_minute_mother', `tableid')


-- follow_1_minute_sibling

create table prepared_data.follow_1_minute_sibling (
  tableid serial primary key
 ,"interval_id" varchar
     constraint "follow_1_minute_sibling.interval_id must be a follow_1_minute.interval_id"
        references prepared_data.follow_1_minute("interval_id")
 ,"sibling_id" varchar
 ,"bad_observation" varchar
 ,"behavior" varchar
 ,"distance_from_mother" varchar
 ,"vocalizations" varchar
 ,"plays" varchar
 ,"grooms_other" varchar
 ,"groomed_by" varchar
 ,"distance_from_infant" varchar
 ,"sibling_interval_id" varchar
 , follow_id varchar
     constraint "follow_id on follow" references prepared_data.follow(follow_id)
 , done boolean
);

create unique index follow_1_minute_sibling_sibling_interval_id
  on prepared_data.follow_1_minute_sibling ("sibling_interval_id");

create unique index follow_1_minute_sibling_interval_id_sibling_id
  on prepared_data.follow_1_minute_sibling ("interval_id", sibling_id);

create index follow_1_minute_sibling_follow_id
  on prepared_data.follow_1_minute_sibling (follow_id);

grant_priv(`prepared_data.follow_1_minute_sibling')
grant_seq_priv(`prepared_data.follow_1_minute_sibling', `tableid')


-- follow_5_minute

create table prepared_data.follow_5_minute (
  tableid serial primary key
 ,"page_id" varchar
     constraint "page_id on follow_page" references prepared_data.follow_page(page_id)
 ,"interval_number" int
 ,"interval_time" timestamp without time zone
 ,"mother_feeding" varchar -- ignore
 ,"food_part_initials" varchar -- ignore
 ,"food_name" varchar -- ignore
 ,"infant_feeding" varchar -- ignore
 ,"strange_chimp" varchar -- go in as 'MGE' chimp
 ,"other_animals" varchar -- ignore
 ,"interval_id" varchar
 , follow_id varchar
     constraint "follow_id on follow" references prepared_data.follow(follow_id)
 , done boolean
);


create unique index follow_5_minute_interval_id
  on prepared_data.follow_5_minute ("interval_id");

-- Data problems.  Deal with it in the conversion.
create unique index follow_5_minute_follow_id_interval_time
  on prepared_data.follow_5_minute (follow_id, interval_time);
--create index follow_5_minute_follow_id_interval_time
--  on prepared_data.follow_5_minute (follow_id, interval_time);

grant_priv(`prepared_data.follow_5_minute')
grant_seq_priv(`prepared_data.follow_5_minute', `tableid')


-- follow_5_minute_group

create table prepared_data.follow_5_minute_group (
  tableid serial primary key
 ,"interval_id" varchar
     constraint "follow_5_minute_group.interval_id must be a follow_5_minute.interval_id"
        references prepared_data.follow_5_minute("interval_id")
 ,"position_number" int
 ,"chimp_id" varchar
 ,"present" varchar
 ,"position_id" varchar
 , follow_id varchar
     constraint "follow_id on follow" references prepared_data.follow(follow_id)
 , done boolean
);


create unique index follow_5_minute_group_interval_id_chimp_id
  on prepared_data.follow_5_minute_group ("interval_id", chimp_id);

create index follow_5_minute_group_follow_id
  on prepared_data.follow_5_minute_group (follow_id);

grant_priv(`prepared_data.follow_5_minute_group')
grant_seq_priv(`prepared_data.follow_5_minute_group', `tableid')


commit;