dnl Copyright (C) 2013 The Meme Factory, Inc.  http://www.meme.com/
dnl
dnl    This file is part of Gombe-MI.
dnl
dnl    Gombe-MI 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 Gombe-MI.  If not, see <http://www.gnu.org/licenses/>.
dnl
dnl Function to convert from access table structure to a temporary
dnl table structure that's easier to work with.
dnl
dnl Karl O. Pinc <kop@meme.com>

dnl m4 includes
include(`copyright.m4')
include(`constants.m4')
include(`macros.m4')dnl
include(`grants.m4')

dnl Reset our search path so we pick up the conversion schema.
define(`gmi_function_search_path',
       `prepared_data, conversion, gombemi, pg_temp')

CREATE OR REPLACE FUNCTION prepared_data.prepare_data()
  RETURNS INT
  LANGUAGE plpgsql
  SECURITY DEFINER
  gmi_function_set_search_path
  AS $$
  -- Copy access data structures from the conversion schema
  -- into a better format in the prepared_data schema.
  --
  -- GPL_notice(`  --', `2013', `The Meme Factory, Inc.  http://www.meme.com/')
  --
  -- Syntax: select prepare_data();
  --
  -- Arguments:
  --  fromdate  Date to start copy (inclusive)
  --  todate    Date to stop copy (inclusive)
  --
  -- Remarks:
  --   One-off program.  Once it works we won't run it again.
  --
  --   Destroy's what's in the prepared_data schema.
  --
  --   Stops as soon as a transaction fails.

  DECLARE

  BEGIN

raise notice 'deleting';

delete from prepared_data.follow_5_minute_group;
delete from prepared_data.follow_5_minute;
delete from prepared_data.follow_1_minute_sibling;
delete from prepared_data.follow_1_minute_mother;
delete from prepared_data.follow_1_minute_infant;
delete from prepared_data.follow_1_minute;
delete from prepared_data.follow_page;
delete from prepared_data.follow;
delete from prepared_data.crfp;
delete from prepared_data.page_followtype;

/*
vacuum full prepared_data.follow_5_minute_group;
vacuum full prepared_data.follow_5_minute;
vacuum full prepared_data.follow_1_minute_sibling;
vacuum full prepared_data.follow_1_minute_mother;
vacuum full prepared_data.follow_1_minute_infant;
vacuum full prepared_data.follow_1_minute;
vacuum full prepared_data.follow;
*/

-- crfp
raise notice 'crfp';

-- Convert focal_mom from a name into an animid.
insert into prepared_data.crfp (
  tableid
 ,"year"
 ,"follow_date"
 ,"focal_mom"
 ,"focal_infant"
 ,"siblings"
 ,"researcher"
 ,"pages"
 ,"start_entry_date"
 ,"end_entry_date"
 ,"enterer"
 ,"follow_status"
 ,"entry_notes"
 ,"group_comp_problem"
 ,"computer"
 ,"checked_over"
 ,"also_checked_by"
 ,"last_checked_by_date"
 ,"absent_mother_infant"
 ,"group_blank"
 ,"protocol"
 ,"template"
 ,"temptype"
 ,"scan_checked"
 ,"paper_follow_location"
 ,"inf_birthdate"
 ,"infant_age"
)
select
  tableid
 ,"Year"
 ,"Follow Date"
 ,biography_data.animid
 ,"Focal infant"
 ,"Siblings"
 ,"Researcher"
 ,"# pages"
 ,"Start Entry Date"
 ,"End Entry Date"
 ,"Enterer"
 ,"Follow Status"
 ,"Entry Notes"
 ,"Group comp problem?"
 ,"Computer"
 ,"Checked Over"
 ,"Also Checked By"
 ,"Last Checked By Date"
 ,"Absent Mother/Infant"
 ,"Group_blank"
 ,"Protocol"
 ,"Template"
 ,"TempType"
 ,"Scan Checked"
 ,"Paper Follow Location"
 ,"Inf birthdate"
 ,"Infant Age"
from conversion.cross_reference_forprogram
       join biography_data
            ON (upper(trim(biography_data.animname))
                = upper(trim(conversion.cross_reference_forprogram."Focal mom")));

IF (select count(*)
      from conversion.cross_reference_forprogram)
   != (select count(*)
      from prepared_data.crfp) THEN
  RAISE EXCEPTION integrity_constraint_violation USING
        MESSAGE = 'Error looking up cross_reference_forprogram."Focal mom" on biography_data'
      , DETAIL = 'Missing (Focal mom) = ('
                 || (SELECT conversion.cross_reference_forprogram."Focal mom"
                       FROM conversion.cross_reference_forprogram
                       WHERE NOT EXISTS (
                         SELECT 1
                           FROM gombemi.biography_data
                           WHERE upper(trim(gombemi.biography_data.animname))
                                 = upper(trim(conversion.cross_reference_forprogram."Focal mom"))
                           LIMIT 1)
                       LIMIT 1)
                || ')';
END IF;


-- page_followtype
raise notice 'page_followtype';

insert into prepared_data.page_followtype (
  tableid
 ,date
 ,mom
 ,page
 ,temptype
 ,done
)
(select tableid
 ,date
 ,mom
 ,page
 ,temptype
 ,FALSE
from conversion.page_followtype);


-- follow
raise notice 'follow';

insert into prepared_data.follow (
  tableid
 ,"follow_date"
 ,"mother_id"
 ,"infant_id"
 ,"infant2_id"
 ,"sibling_id"
 ,"sibling2_id"
 ,"conducted_by"
 ,"entered_by"
 ,"sheet_id"
 ,"left_group_id"
 ,"right_group_id"
 ,"follow_status"
 ,"follow_id"
 ,"time_mode"
 ,"ampm"
 , done
)
(select tableid
 ,"Follow_date"
 ,"Mother_id"
 ,"Infant_id"
 ,"Infant2_id"
 ,"Sibling_id"
 ,"Sibling2_id"
 ,"Conducted_by"
 ,"Entered_by"
 ,"Sheet_id"
 ,"Left_group_id"
 ,"Right_group_id"
 ,"Follow_status"
 ,"Follow_id"
 ,"Time_mode"
 ,"AMPM"
 , false
from conversion.follow);

-- follow_page
raise notice 'follow_page';

insert into prepared_data.follow_page (
  tableid
 ,"follow_id"
 ,"page_number"
 ,"location"
 ,"video"
 ,"page_id"
 ,done
)
select
  tableid
 ,"Follow_id"
 ,"Page_number"
 ,"Location"
 ,"Video"
 ,"Page_id"
 ,FALSE
from conversion.follow_page;


-- follow_1_minute
raise notice 'follow_1_minute';

insert into prepared_data.follow_1_minute (
  tableid
 ,"page_id"
 ,"interval_number"
 ,"interval_time"
 ,"bad_observation"
 ,"rain"
 ,"comment"
 ,"commentrtf"
 ,"interval_id"
 ,follow_id
 , done
)
(select
  tableid
 ,"Page_id"
 ,"Interval_number"
 ,"Interval_time"
 ,"Bad_observation"
 ,"Rain"
 ,"Comment"
 ,"CommentRTF"
 ,"Interval_id"
 ,split_part("Interval_id", '_', 1)
   || '_'
   || split_part("Interval_id", '_', 2)
 , false
from conversion.follow_1_minute);


-- follow_1_minute_infant
raise notice 'follow_1_minute_infant';

insert into prepared_data.follow_1_minute_infant (
  tableid
 ,"interval_id"
 ,"infant_id"
 ,"nursing"
 ,"eating"
 ,"walks"
 ,"playing"
 ,"resting"
 ,"in_contact"
 ,"within_reach"
 ,"within_0_5_meters"
 ,"between_5_15_meters"
 ,"more_than_15_meters"
 ,"rides_on_stomach"
 ,"rides_on_back"
 ,"dangles"
 ,"contacts_other"
 ,"grooms_other"
 ,"groomed_by"
 ,"plays"
 ,"displays"
 ,"refused"
 ,"cries"
 ,"vocalizations"
 ,"begs"
 ,"aggression"
 ,"infant_interval_id"
 , follow_id
 , done
)
(select
  tableid
 ,"Interval_id"
 ,"Infant_id"
 ,"Nursing"
 ,"Eating"
 ,"Walks"
 ,"Playing"
 ,"Resting"
 ,"In_contact"
 ,"Within_reach"
 ,"Within_0_5_meters"
 ,"Between_5_15_meters"
 ,"More_than_15_meters"
 ,"Rides_on_stomach"
 ,"Rides_on_back"
 ,"Dangles"
 ,"Contacts_other"
 ,"Grooms_other"
 ,"Groomed_by"
 ,"Plays"
 ,"Displays"
 ,"Refused"
 ,"Cries"
 ,"Vocalizations"
 ,"Begs"
 ,"Aggression"
 ,"Infant_interval_id"
 ,split_part("Interval_id", '_', 1)
   || '_'
   || split_part("Interval_id", '_', 2)
 , false
from conversion.follow_1_minute_infant);



-- follow_1_minute_mother
raise notice 'follow_1_minute_mother';

insert into prepared_data.follow_1_minute_mother (
  tableid
 ,"interval_id"
 ,"mother_id"
 ,"behavior"
 ,"grooms_other"
 ,"groomed_by"
 ,"vocalizations"
 ,follow_id
 ,done
)
select
  tableid
 ,"Interval_id"
 ,"Mother_id"
 ,"Behavior"
 ,"Grooms_other"
 ,"Groomed_by"
 ,"Vocalizations"
 ,split_part("Interval_id", '_', 1)
   || '_'
   || split_part("Interval_id", '_', 2)
 ,false
from conversion.follow_1_minute_mother;


-- follow_1_minute_sibling
raise notice 'follow_1_minute_sibling';

insert into prepared_data.follow_1_minute_sibling (
  tableid
 ,"interval_id"
 ,"sibling_id"
 ,"bad_observation"
 ,"behavior"
 ,"distance_from_mother"
 ,"vocalizations"
 ,"plays"
 ,"grooms_other"
 ,"groomed_by"
 ,"distance_from_infant"
 ,"sibling_interval_id"
 ,follow_id
 ,done
)
select
  tableid
 ,"Interval_id"
 ,"Sibling_id"
 ,"Bad_observation"
 ,"Behavior"
 ,"Distance_from_mother"
 ,"Vocalizations"
 ,"Plays"
 ,"Grooms_other"
 ,"Groomed_by"
 ,"Distance_from_infant"
 ,"Sibling_interval_id"
 ,split_part("Interval_id", '_', 1)
   || '_'
   || split_part("Interval_id", '_', 2)
 ,false
from conversion.follow_1_minute_sibling;

-- Go through the follow_1_minute_sibling and where
-- the 'Multi' behavior code is found add grooms other,
-- plays, and groomed by as is found in the data.

-- follow_1_minute_sibling Multi -> Grooming  (based on grooms_other)
UPDATE prepared_data.follow_1_minute_sibling
  SET behavior = CASE
                   WHEN TRIM(TRAILING FROM behavior) ~ ';$'
                     THEN TRIM(TRAILING FROM behavior) || 'Grooming'
                   ELSE
                     TRIM(TRAILING FROM behavior) || ';Grooming'
                   END
  WHERE behavior ~ '(?:^|;)[[:blank:]]*Multi[[:blank:]]*(?:$|;)'
        AND TRIM(BOTH FROM grooms_other) !=  ''
        AND grooms_other IS NOT NULL
        AND behavior !~ '(?:^|;)[[:blank:]]*Grooming[[:blank:]]*(?:$|;)';

-- follow_1_minute_sibling Multi -> Groomed (based on groomed_by)
UPDATE prepared_data.follow_1_minute_sibling
  SET behavior = CASE
                   WHEN TRIM(TRAILING FROM behavior) ~ ';$'
                     THEN TRIM(TRAILING FROM behavior) || 'Groomed'
                   ELSE
                     TRIM(TRAILING FROM behavior) || ';Groomed'
                   END
  WHERE behavior ~ '(?:^|;)[[:blank:]]*Multi[[:blank:]]*(?:$|;)'
        AND TRIM(BOTH FROM groomed_by) !=  ''
        AND groomed_by IS NOT NULL
        AND behavior !~ '(?:^|;)[[:blank:]]*Groomed[[:blank:]]*(?:$|;)';

-- follow_1_minute_sibling Multi -> Play (based on plays)
UPDATE prepared_data.follow_1_minute_sibling
  SET behavior = CASE
                   WHEN TRIM(TRAILING FROM behavior) ~ ';$'
                     THEN TRIM(TRAILING FROM behavior) || 'Play'
                   ELSE
                     TRIM(TRAILING FROM behavior) || ';Play'
                   END
  WHERE behavior ~ '(?:^|;)[[:blank:]]*Multi[[:blank:]]*(?:$|;)'
        AND TRIM(BOTH FROM plays) !=  ''
        AND plays IS NOT NULL
        AND behavior !~ '(?:^|;)[[:blank:]]*Play[[:blank:]]*(?:$|;)';


-- follow_5_minute
raise notice 'follow_5_minute';

insert into prepared_data.follow_5_minute(
  tableid
 ,"page_id"
 ,"interval_number"
 ,"interval_time"
 ,"mother_feeding"
 ,"food_part_initials"
 ,"food_name"
 ,"infant_feeding"
 ,"strange_chimp"
 ,"other_animals"
 ,"interval_id"
 ,follow_id
 ,done
)
select
  tableid
 ,"Page_id"
 ,"Interval_number"
 ,"Interval_time"
 ,"Mother_feeding"
 ,"Food_part_initials"
 ,"Food_name"
 ,"Infant_feeding"
 ,"Strange_chimp"
 ,"Other_animals"
 ,"Interval_id"
 ,split_part("Interval_id", '_', 1)
   || '_'
   || split_part("Interval_id", '_', 2)
 ,false
from conversion.follow_5_minute;


-- follow_5_minute_group
raise notice 'follow_5_minute_group';

insert into prepared_data.follow_5_minute_group (
  tableid
 ,"interval_id"
 ,"position_number"
 ,"chimp_id"
 ,"present"
 ,"position_id"
 ,follow_id
 , done
)
select
  tableid
 ,"Interval_id"
 ,"Position_number"
 ,"Chimp_id"
 ,"Present"
 ,"Position_id"
 ,split_part("Interval_id", '_', 1)
   || '_'
   || split_part("Interval_id", '_', 2)
 ,false
from conversion.follow_5_minute_left_group
union all
select
  tableid + 10000000
 ,"Interval_id"
 ,"Position_number"
 ,"Chimp_id"
 ,"Present"
 ,"Position_id"
 ,split_part("Interval_id", '_', 1)
   || '_'
   || split_part("Interval_id", '_', 2)
 ,false
from conversion.follow_5_minute_right_group;

/*
vacuum analyze prepared_data.follow_5_minute_group;
vacuum analyze prepared_data.follow_5_minute;
vacuum analyze prepared_data.follow_1_minute_sibling;
vacuum analyze prepared_data.follow_1_minute_mother;
vacuum analyze prepared_data.follow_1_minute_infant;
vacuum analyze prepared_data.follow_1_minute;
vacuum analyze prepared_data.follow;
*/
    RETURN 0;

  END;
$$;


grant_func_priv(`prepared_data.prepare_data()')