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 Delete_gc  Delete group comp for a follow.
dnl
dnl Karl O. Pinc <kop@meme.com>
dnl
dnl
dnl m4 includes
include(`copyright.m4')
include(`constants.m4')
include(`macros.m4')
dnl

CREATE OR REPLACE FUNCTION delete_gc(followdate DATE
                                  , mom VARCHAR(gmi_animid_len))
  RETURNS BOOLEAN
  LANGUAGE plpgsql
  SECURITY DEFINER
  gmi_function_set_search_path
  AS $$
  -- Delete group comp of a follow, specified by date and mom.
  --
  -- GPL_notice(`  --', `2013', `The Meme Factory, Inc.  http://www.meme.com/')
  --
  -- Syntax: delete_gc(followdate, mom)
  --
  -- Arguments:
  --   followdate  Date of the follow.
  --   mom         Chimpid of mom in the follow.
  --
  -- Returns: 
  --  TRUE if group comp rows were deleted and FALSE otherwise.
  --
  -- Remarks:
  --
  -- Bugs:

  DECLARE
    this_followid follows.followid%TYPE;

  BEGIN

  -- Find the follow.
  SELECT follows.followid
    INTO this_followid
    FROM follows
           JOIN followparts ON (followparts.followid = follows.followid)
           JOIN followroles ON (followroles.role = followparts.role)
           JOIN chimpids AS fcid ON (fcid.chimpid = followparts.chimpid)
           JOIN chimpids AS this_cid ON (this_cid.animid = fcid.animid)
    WHERE follows.date = followdate
          AND followroles.function = 'gmi_mom'
          AND this_cid.chimpid =  mom;

  IF NOT FOUND THEN
    return FALSE;
  END IF;

  RETURN delete_gc(this_followid);
  END;
$$;
grant_func_priv(`delete_gc(DATE, VARCHAR(gmi_animid_len))')



CREATE OR REPLACE FUNCTION delete_gc(this_followid INT)
  RETURNS BOOLEAN
  LANGUAGE plpgsql
  SECURITY DEFINER
  gmi_function_set_search_path
  AS $$
  -- Delete group comp of a follow, specified by followid.
  --
  -- GPL_notice(`  --', `2013', `The Meme Factory, Inc.  http://www.meme.com/')
  --
  -- Syntax: delete_gc(followid)
  --
  -- Arguments:
  --   followid    FOLLOWS.FollowID of the follow.
  --
  -- Returns: 
  --  TRUE if group comp rows were deleted and FALSE otherwise.
  --
  -- Remarks:
  --
  -- Bugs:

  DECLARE
    deleted BOOLEAN;
    this_rgid raw_groups.rgid%TYPE;

  BEGIN

  -- Delete group comps from the beginning of the day to the end.
  -- This prevents re-assignment of group comps to intervals
  -- because intervals before the first group comp of the day
  -- are not assigned to a group comp.

  deleted := FALSE;
  FOR this_rgid IN
      SELECT raw_groups.rgid
        FROM intervals
               JOIN raw_groups ON (raw_groups.sheetid = intervals.sheetid)
        WHERE intervals.followid = this_followid
        GROUP BY raw_groups.rgid
        ORDER BY raw_groups.time
    LOOP

    DELETE FROM raw_groups
      WHERE raw_groups.rgid = this_rgid;

    deleted := TRUE;
  END LOOP;

  -- Change the fieldfollows.gcstatus to NULL.
  UPDATE fieldfollows
    SET gcstatus = NULL
    FROM (SELECT sheets.ffid
            FROM intervals
                   JOIN sheets ON (sheets.sheetid = intervals.sheetid)
            WHERE intervals.followid = this_followid
            GROUP BY sheets.ffid) AS ffids
    WHERE fieldfollows.ffid = ffids.ffid;

  RETURN deleted;
  END;
$$;
grant_func_priv(`delete_gc(INT)')