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 fully populated conversion schema.
dnl
dnl Karl O. Pinc <kop@meme.com>
dnl
dnl Mar 1, 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')

--
-- The gombemi schema where the production system lives.
--

CREATE SCHEMA conversion;

GRANT USAGE ON SCHEMA conversion TO gombemi_editors
                                  , gombemi_readers;

SET SEARCH_PATH TO conversion;

--
-- Create all the tables.
--
begin;
-- cross_reference_for_program
create table cross_reference_forprogram (
  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 cross_reference_forprogram("Follow Date", "Focal mom");

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


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

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

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


-- follow
create table 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
);

create unique index follow_follow_id on follow ("Follow_id");
create index follow_date on follow ("Follow_date");

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


-- follow_page
create table follow_page (
  tableid serial primary key
 ,"Follow_id" text
 ,"Page_number" int
 ,"Location" text
 ,"Video" text
 ,"Page_id" text
);

create index follow_page_follow_id on follow_page("Follow_id");
create unique index follow_page_page_id on follow_page ("Page_id");

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


-- follow_1_minute
create table follow_1_minute (
  tableid serial primary key
 ,"Page_id" varchar
 ,"Interval_number" int
 ,"Interval_time" timestamp without time zone
 ,"Bad_observation" varchar
 ,"Rain" varchar
 ,"Comment" varchar
 ,"CommentRTF" varchar
 ,"Interval_id" varchar
);

create unique index
  follow_1_minute_interval_id on follow_1_minute ("Interval_id");

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


-- follow_1_minute_infant
create table 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 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
);

create unique index follow_1_minute_infant_infant_interval_id
  on follow_1_minute_infant ("Infant_interval_id");

create index follow_1_minute_infant_interval_id
  on follow_1_minute_infant ("Interval_id");

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


-- follow_1_minute_mother
create table 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 follow_1_minute("Interval_id")
 ,"Mother_id" varchar
 ,"Behavior" varchar
 ,"Grooms_other" varchar
 ,"Groomed_by" varchar
 ,"Vocalizations" varchar
);

create index follow_1_minute_mother_interval_id
  on follow_1_minute_mother ("Interval_id");

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


-- follow_1_minute_sibling
create table 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 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
);

create unique index follow_1_minute_sibling_sibling_interval_id
  on follow_1_minute_sibling ("Sibling_interval_id");

create index follow_1_minute_sibling_interval_id
  on follow_1_minute_sibling ("Interval_id");

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


-- follow_5_minute
create table follow_5_minute (
  tableid serial primary key
 ,"Page_id" varchar
 ,"Interval_number" int
 ,"Interval_time" timestamp without time zone
 ,"Mother_feeding" varchar
 ,"Food_part_initials" varchar
 ,"Food_name" varchar
 ,"Infant_feeding" varchar
 ,"Strange_chimp" varchar
 ,"Other_animals" varchar
 ,"Interval_id" varchar
);

create unique index follow_5_minute_interval_id
  on follow_5_minute ("Interval_id");

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


-- follow_5_minute_left_group
create table follow_5_minute_left_group (
  tableid serial primary key
 ,"Interval_id" varchar
     constraint "follow_5_minute_left_group.Interval_id must be a follow_5_minute.Interval_id"
        REFERENCES follow_5_minute("Interval_id")
 ,"Position_number" int
 ,"Chimp_id" varchar
 ,"Present" varchar
 ,"Position_id" varchar
);

create index follow_5_minute_left_group_interval_id
  on follow_5_minute_left_group ("Interval_id");

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


-- follow_5_minute_right_group
create table follow_5_minute_right_group (
  tableid serial primary key
 ,"Interval_id" varchar
     constraint "follow_5_minute_right_group.Interval_id must be a follow_5_minute.Interval_id"
        REFERENCES follow_5_minute("Interval_id")
 ,"Position_number" int
 ,"Chimp_id" varchar
 ,"Present" varchar
 ,"Position_id" varchar
);

create index follow_5_minute_right_group_interval_id
  on follow_5_minute_right_group ("Interval_id");

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

commit;