-- PostgreSQL Database Schema Migration Script for M4U (Authentication & Core Users)
-- Target Platform: Supabase / PostgreSQL 15+

-- ----------------------------------------------------
-- 1. DROP EXISTING CONSTRAINTS & TABLES (IF RE-RUNNING)
-- ----------------------------------------------------
DROP FUNCTION IF EXISTS getUser(varchar);
DROP FUNCTION IF EXISTS validateUser(varchar);
DROP FUNCTION IF EXISTS validateUserID(int);
DROP FUNCTION IF EXISTS getUserByID(int);
DROP FUNCTION IF EXISTS getUserInfo(int);
DROP FUNCTION IF EXISTS getUserEmail(int);
DROP FUNCTION IF EXISTS getUserByName(varchar, varchar);
DROP FUNCTION IF EXISTS getSubTypes();
DROP FUNCTION IF EXISTS dupCheck(varchar);
DROP FUNCTION IF EXISTS verifyCode(int, varchar);
DROP FUNCTION IF EXISTS forgotPW(varchar, varchar);
DROP FUNCTION IF EXISTS resetCheck(varchar, varchar);
DROP FUNCTION IF EXISTS resetPassword(varchar, varchar);
DROP FUNCTION IF EXISTS updateUser(int, varchar, varchar, varchar);
DROP FUNCTION IF EXISTS updatePassword(int, varchar);
DROP FUNCTION IF EXISTS adminEditUser(int, int, varchar);
DROP FUNCTION IF EXISTS invitationVerification(varchar, int);
DROP FUNCTION IF EXISTS newMonthlySubscriber(varchar, varchar, varchar, varchar, varchar, varchar);
DROP FUNCTION IF EXISTS newInvitedMonthlySubscriber(varchar, varchar, varchar, varchar, varchar, varchar, int);
DROP FUNCTION IF EXISTS newInvitedSubscriber(varchar, varchar, varchar, varchar, varchar, varchar, int, int);

-- ----------------------------------------------------
-- 2. CREATE TABLES
-- ----------------------------------------------------

CREATE TABLE IF NOT EXISTS UserSubTypeTbl (
    UserSubTypeID INT NOT NULL,
    UserSubType   VARCHAR(50) NOT NULL,
    CONSTRAINT PK_UserSubTypeTbl PRIMARY KEY (UserSubTypeID)
);

CREATE TABLE IF NOT EXISTS UserTbl (
    UserID             SERIAL NOT NULL,
    UserEmail          VARCHAR(75) NOT NULL,
    UserHash           VARCHAR(128) NULL,
    UserFirst          VARCHAR(50) NOT NULL,
    UserLast           VARCHAR(50) NOT NULL,
    UserDisplayName    VARCHAR(110) NOT NULL,
    UserSubType        INT NOT NULL,
    UserSubExpiration  DATE NULL,
    ValidationDate     TIMESTAMP NULL,
    AdminLevel         INT NULL,
    UserMediaLoc       VARCHAR(100) NULL,
    LastPageView       TIMESTAMP NULL,
    LastBookView       TIMESTAMP NULL,
    LastTCView         TIMESTAMP NULL,
    LastCircleView     TIMESTAMP NULL,
    LastFeedView       TIMESTAMP NULL,
    VerifyHash         VARCHAR(128) NULL,
    ForgotPW           TIMESTAMP NULL,
    UserTZ             VARCHAR(75) NULL,
    TCAcceptDate       TIMESTAMP NULL,
    StripeCustomer     VARCHAR(100) NULL,
    StripeSubscription VARCHAR(100) NULL,
    CancelID           INT NULL,
    JoinDate           TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    LastExport         TIMESTAMP NULL,
    ExportStarted      INT NULL,
    CONSTRAINT PK_UserTbl PRIMARY KEY (UserID),
    CONSTRAINT UQ_UserTbl_UserEmail UNIQUE (UserEmail)
);

CREATE TABLE IF NOT EXISTS CircleTbl (
    CircleID      SERIAL NOT NULL,
    CircleOwner   INT NOT NULL,
    CircleName    VARCHAR(50) NOT NULL,
    CirclePrimary INT NULL,
    JournalID     INT NULL,
    CONSTRAINT PK_CircleTbl PRIMARY KEY (CircleID)
);

CREATE TABLE IF NOT EXISTS CircleMemberTbl (
    CircleMemID  SERIAL NOT NULL,
    CircleID     INT NOT NULL,
    MemberID     INT NOT NULL,
    InvitedBy    INT NULL,
    AcceptedDate TIMESTAMP NULL,
    RejectedDate TIMESTAMP NULL,
    Status       VARCHAR(50) NULL,
    RemovedBy    INT NULL,
    CONSTRAINT PK_CircleMemberTbl PRIMARY KEY (CircleMemID)
);

CREATE TABLE IF NOT EXISTS ForgotPWTbl (
    ForgotID    SERIAL NOT NULL,
    ForgotUUID  VARCHAR(128) NOT NULL,
    ForgotEmail VARCHAR(75) NOT NULL,
    ForgotDate  TIMESTAMP NOT NULL,
    CONSTRAINT PK_ForgotPWTbl PRIMARY KEY (ForgotID)
);

CREATE TABLE IF NOT EXISTS InvitationTbl (
    InvitationID     SERIAL NOT NULL,
    InviteName       VARCHAR(50) NOT NULL,
    InviteEmail      VARCHAR(150) NOT NULL,
    InviteDate       TIMESTAMP NOT NULL,
    InvitationType   INT NOT NULL,
    ReminderCount    INT NOT NULL,
    DoNotContactDate TIMESTAMP NULL,
    LastContactDate  TIMESTAMP NOT NULL,
    DiscountCode     VARCHAR(50) NULL,
    ConvertDate      TIMESTAMP NULL,
    InvitedBy        INT NOT NULL,
    UserID           INT NULL,
    AdminInvite      INT NULL,
    CONSTRAINT PK_InvitationTbl PRIMARY KEY (InvitationID)
);

-- ----------------------------------------------------
-- 3. SEED REFERENCE DATA
-- ----------------------------------------------------

INSERT INTO UserSubTypeTbl (UserSubTypeID, UserSubType) VALUES
(1, 'Monthly'),
(2, 'Charter'),
(3, 'Charter Monthly'),
(4, 'Charter Yearly'),
(9004, 'Trial Monthly')
ON CONFLICT (UserSubTypeID) DO NOTHING;

-- Seed system circle record (referenced by verify/registration procedures)
INSERT INTO CircleTbl (CircleID, CircleOwner, CircleName, CirclePrimary)
VALUES (1, 0, 'System/ एवरीवन', 0)
ON CONFLICT (CircleID) DO NOTHING;

-- acceptinvitation
DROP FUNCTION IF EXISTS acceptinvitation;
CREATE OR REPLACE FUNCTION public.acceptinvitation(p_circlememid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_senderID int;
    v_primary int;
BEGIN
    BEGIN
    UPDATE CircleMemberTbl
   SET AcceptedDate = CURRENT_TIMESTAMP
      ,Status = 'Accepted'
 WHERE circleMemID = p_circleMemID;
    SELECT InvitedBy INTO v_senderID FROM CircleMemberTbl 
 where CircleMemberTbl.CircleMemID = p_circleMemID;
    SELECT CircleID INTO v_primary FROM CircleTbl where CirclePrimary = p_userID;
    INSERT INTO CircleMemberTbl
           (CircleID
           ,MemberID
           ,InvitedBy
           ,AcceptedDate
           ,Status)
     VALUES
           (v_primary
           ,v_senderID
           ,p_userID
           ,CURRENT_TIMESTAMP
           ,'Accepted');
    END;
END;
$function$
;

-- acceptpolicy
DROP FUNCTION IF EXISTS acceptpolicy;
CREATE OR REPLACE FUNCTION public.acceptpolicy(p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE UserTbl
   SET TCAcceptDate = CURRENT_TIMESTAMP
 WHERE UserID = p_userID;
    END;
END;
$function$
;

-- addadmininvitation
DROP FUNCTION IF EXISTS addadmininvitation;
CREATE OR REPLACE FUNCTION public.addadmininvitation(p_userid integer DEFAULT NULL::integer, p_invitee character varying DEFAULT NULL::character varying, p_email character varying DEFAULT NULL::character varying, p_tcdate date DEFAULT NULL::date, p_invitationtype integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    IF (SELECT COUNT(UserID) from UserTbl
	where UserEmail = p_email ) > 0 THEN
    BEGIN
    RETURN 502;
    END; END IF;
    IF (SELECT COUNT(InvitationID) from InvitationTbl
	where InvitedBy = p_userID and InviteEmail = p_email ) > 0 THEN
    BEGIN
    RETURN 501;
    END;
    ELSE
    BEGIN
    INSERT INTO InvitationTbl
           (InviteName
           ,InviteEmail
           ,InviteDate
           ,InvitationType
           ,ReminderCount
           ,LastContactDate
           ,InvitedBy
		   ,AdminInvite)
     VALUES
           (p_invitee,
           p_email,
           p_tcDate,
           p_invitationType,
           0,
           p_tcDate,
           p_userID,
		   1);
    RETURN lastval();
    END; END IF;
    END;
END;
$function$
;

-- addcancellation
DROP FUNCTION IF EXISTS addcancellation;
CREATE OR REPLACE FUNCTION public.addcancellation(p_userid integer DEFAULT NULL::integer, p_usersubtype integer DEFAULT NULL::integer, p_reason character varying DEFAULT NULL::character varying)
 RETURNS TABLE("InsertedId" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO CancelTbl
           (UserID
           ,CancelReason
           ,CancelDate
		   ,UserSubType
)
     VALUES
           (p_userID
           ,p_reason
           ,CURRENT_TIMESTAMP
		   ,p_userSubType
		   );
    RETURN QUERY SELECT lastval() AS "InsertedId";
    UPDATE UserTbl 
	SET UserSubExpiration = (CURRENT_TIMESTAMP + INTERVAL '10 day'),
		CancelID = lastval()
	WHERE UserID = p_userID;
    END;
END;
$function$
;

-- addcirclemember
DROP FUNCTION IF EXISTS addcirclemember;
CREATE OR REPLACE FUNCTION public.addcirclemember(p_memberid integer DEFAULT NULL::integer, p_circleid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO CircleMemberTbl
           (CircleID
           ,MemberID
		   ,InvitedBy
		   ,AcceptedDate
		   ,Status
		   )
     VALUES
           (p_circleID
           ,p_memberID
		   ,p_userID
		   ,CURRENT_TIMESTAMP
		   ,'Accepted'
			);
    END;
END;
$function$
;

-- addcomment
DROP FUNCTION IF EXISTS addcomment;
CREATE OR REPLACE FUNCTION public.addcomment(p_storyid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer, p_commenttext character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO CommentTbl
           (StoryID
           ,UserID
           ,CommentText
           ,CommentDate)
     VALUES
           (p_storyID
           ,p_userID
           ,p_commentText
           ,CURRENT_TIMESTAMP);
    END;
END;
$function$
;

-- addeveryonemember
DROP FUNCTION IF EXISTS addeveryonemember;
CREATE OR REPLACE FUNCTION public.addeveryonemember(p_userid integer DEFAULT NULL::integer, p_memberid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primaryCircle int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primaryCircle FROM CircleTbl where CirclePrimary = p_userID;
    INSERT INTO CircleMemberTbl
           (CircleID
           ,MemberID
		   ,InvitedBy
		   )
     VALUES
           (v_primaryCircle
           ,p_memberID
		   ,p_userID
			);
    END;
END;
$function$
;

-- addheartstatus
DROP FUNCTION IF EXISTS addheartstatus;
CREATE OR REPLACE FUNCTION public.addheartstatus(p_userid integer DEFAULT NULL::integer, p_storyid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO HeartTbl
           (StoryID
           ,UserID)
     VALUES
           (p_storyID
           ,p_userID);
    END;
END;
$function$
;

-- addinterviewquestion
DROP FUNCTION IF EXISTS addinterviewquestion;
CREATE OR REPLACE FUNCTION public.addinterviewquestion(p_intcategory integer DEFAULT NULL::integer, p_intquestion text DEFAULT NULL::text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO InterviewTbl
           (IntCategory
           ,IntQuestion)
     VALUES
           (p_intCategory,
           p_intQuestion);
    END;
END;
$function$
;

-- addinterviewsuggestion
DROP FUNCTION IF EXISTS addinterviewsuggestion;
CREATE OR REPLACE FUNCTION public.addinterviewsuggestion(p_userid integer DEFAULT NULL::integer, p_interviewsuggestion character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO InterviewSuggestionTbl
           (InterviewUser
           ,InterviewSuggestion
           ,InterviewDate)
     VALUES
           (p_userID
           ,p_interviewSuggestion
           ,CURRENT_TIMESTAMP);
    END;
END;
$function$
;

-- addinvitation
DROP FUNCTION IF EXISTS addinvitation;
CREATE OR REPLACE FUNCTION public.addinvitation(p_userid integer DEFAULT NULL::integer, p_invitee character varying DEFAULT NULL::character varying, p_email character varying DEFAULT NULL::character varying, p_tcdate date DEFAULT NULL::date, p_invitationtype integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    IF (SELECT COUNT(UserID) from UserTbl
	where UserEmail = p_email ) > 0 THEN
    BEGIN
    RETURN 502;
    END; END IF;
    IF (SELECT COUNT(InvitationID) from InvitationTbl
	where InvitedBy = p_userID and InviteEmail = p_email ) > 0 THEN
    BEGIN
    RETURN 501;
    END;
    ELSE
    BEGIN
    INSERT INTO InvitationTbl
           (InviteName
           ,InviteEmail
           ,InviteDate
           ,InvitationType
           ,ReminderCount
           ,LastContactDate
           ,InvitedBy)
     VALUES
           (p_invitee,
           p_email,
           p_tcDate,
           p_invitationType,
           0,
           p_tcDate,
           p_userID);
    RETURN lastval();
    END; END IF;
    END;
END;
$function$
;

-- addlisttobook
DROP FUNCTION IF EXISTS addlisttobook;
CREATE OR REPLACE FUNCTION public.addlisttobook(p_bookid integer DEFAULT NULL::integer, p_listid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO BookListTbl
           (BookID
           ,ListID)
     VALUES
           (p_bookID
           ,p_listID);
    END;
END;
$function$
;

-- addmessage
DROP FUNCTION IF EXISTS addmessage;
CREATE OR REPLACE FUNCTION public.addmessage(p_userid integer DEFAULT NULL::integer, p_messagetext text DEFAULT NULL::text, p_messagestart date DEFAULT NULL::date, p_messageend date DEFAULT NULL::date, p_messageheader character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO MessageTbl
           (MessageText
           ,MessageStart
           ,MessageEnd
           ,MessageUser
		   ,MessageHeader)
     VALUES
           (p_messageText
           ,p_messageStart
           ,p_messageEnd
           ,p_userID
		   ,p_messageHeader);
    END;
END;
$function$
;

-- addnewhelp
DROP FUNCTION IF EXISTS addnewhelp;
CREATE OR REPLACE FUNCTION public.addnewhelp(p_userid integer DEFAULT NULL::integer, p_storytitle text DEFAULT NULL::text, p_storytext text DEFAULT NULL::text, p_storytypeid integer DEFAULT NULL::integer, p_storyingredients text DEFAULT NULL::text, p_interviewee character varying DEFAULT NULL::character varying, p_circleid integer DEFAULT NULL::integer, p_helptype integer DEFAULT NULL::integer)
 RETURNS TABLE("InsertedId" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO HelpTbl
           (StoryTitle
           ,StoryText
           ,UserID
           ,StoryTypeID
           ,StoryDate
           ,StoryIngredients
           ,CircleID
           ,Interviewee
		   , HelpType)
     VALUES
           (p_StoryTitle
           ,p_StoryText
           ,p_userID
           ,p_StoryTypeID
           ,CURRENT_TIMESTAMP
           ,p_StoryIngredients
           ,p_CircleID
           ,p_Interviewee
		   ,p_HelpType);
    RETURN QUERY SELECT lastval() AS "InsertedId";
    UPDATE MediaTbl
		   SET StoryID = lastval()

			WHERE StoryID = 0 AND UserID = p_userID;
    RETURN;
    END;
END;
$function$
;

-- addnewmedia
DROP FUNCTION IF EXISTS addnewmedia;
CREATE OR REPLACE FUNCTION public.addnewmedia(p_userid integer DEFAULT NULL::integer, p_medialoc character varying DEFAULT NULL::character varying, p_storyid integer DEFAULT NULL::integer, p_mediatype integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_FEATURE INT;
BEGIN
    BEGIN
    v_FEATURE := (SELECT COUNT(*) FROM MediaTbl where storyID = p_storyID and userID = p_userID) + 1;
    RAISE NOTICE '%', v_FEATURE;
    INSERT INTO MediaTbl
           (MediaLoc
           ,UserID
           ,StoryID
           ,MediaType
           ,FeatureMedia
           ,MediaDate)
     VALUES
           (p_mediaLoc
           ,p_userID
           ,p_storyID
           ,p_mediaType
           ,v_FEATURE
           ,CURRENT_TIMESTAMP);
    END;
END;
$function$
;

-- addnewmemory
DROP FUNCTION IF EXISTS addnewmemory;
CREATE OR REPLACE FUNCTION public.addnewmemory(p_userid integer DEFAULT NULL::integer, p_storytitle text DEFAULT NULL::text, p_storytext text DEFAULT NULL::text, p_storytypeid integer DEFAULT NULL::integer, p_storyingredients text DEFAULT NULL::text, p_interviewee character varying DEFAULT NULL::character varying, p_circleid integer DEFAULT NULL::integer)
 RETURNS TABLE("InsertedId" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO StoryTbl
           (StoryTitle
           ,StoryText
           ,UserID
           ,StoryTypeID
           ,StoryDate
           ,StoryIngredients
           ,CircleID
           ,Interviewee)
     VALUES
           (p_StoryTitle
           ,p_StoryText
           ,p_userID
           ,p_StoryTypeID
           ,CURRENT_TIMESTAMP
           ,p_StoryIngredients
           ,p_CircleID
           ,p_Interviewee);
    RETURN QUERY SELECT lastval() AS "InsertedId";
    UPDATE MediaTbl
		   SET StoryID = lastval()

			WHERE StoryID = 0 AND UserID = p_userID;
    RETURN;
    END;
END;
$function$
;

-- addnews
DROP FUNCTION IF EXISTS addnews;
CREATE OR REPLACE FUNCTION public.addnews(p_newstitle character varying DEFAULT NULL::character varying, p_newstext text DEFAULT NULL::text, p_newsdate date DEFAULT NULL::date, p_newssubtype integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO NewsTbl
           (NewsTitle
           ,NewsText
           ,NewsDate
           ,NewsSubType)
     VALUES
           (p_newsTitle
           ,p_newsText
           ,p_newsDate
           ,p_newsSubType);
    END;
END;
$function$
;

-- addqcsuggestion
DROP FUNCTION IF EXISTS addqcsuggestion;
CREATE OR REPLACE FUNCTION public.addqcsuggestion(p_qcsuggestion character varying DEFAULT NULL::character varying, p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO QCSuggestionTbl
           (QCUser
           ,QCSuggestion
           ,QCDate
)
     VALUES
           (p_userID
           ,p_qcSuggestion
           ,CURRENT_TIMESTAMP);
    END;
END;
$function$
;

-- addtobook
DROP FUNCTION IF EXISTS addtobook;
CREATE OR REPLACE FUNCTION public.addtobook(p_bookid integer DEFAULT NULL::integer, p_storyid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO BookContentTbl
           (BookID
           ,StoryID)
     VALUES
           (p_bookID
           ,p_storyID);
    END;
END;
$function$
;

-- addtobookexcludetbl
DROP FUNCTION IF EXISTS addtobookexcludetbl;
CREATE OR REPLACE FUNCTION public.addtobookexcludetbl(p_memberid integer DEFAULT NULL::integer, p_bookid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    insert into BKExcludeTbl
            (ExcludeUser, BookID)
            Values
            (p_memberID, p_bookID);
    END;
END;
$function$
;

-- addtostoryexcludetbl
DROP FUNCTION IF EXISTS addtostoryexcludetbl;
CREATE OR REPLACE FUNCTION public.addtostoryexcludetbl(p_memberid integer DEFAULT NULL::integer, p_storyid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    insert into ExcludeTbl
            (ExcludeUser, StoryID)
            Values
            (p_memberID, p_storyID);
    END;
END;
$function$
;

-- addtotempbookexcludetbl
DROP FUNCTION IF EXISTS addtotempbookexcludetbl;
CREATE OR REPLACE FUNCTION public.addtotempbookexcludetbl(p_memberid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    insert into TMPExcludeTbl
            (ExcludeUser, UserID)
            Values
            (p_memberID,p_userID);
    END;
END;
$function$
;

-- admindeleteinvitation
DROP FUNCTION IF EXISTS admindeleteinvitation;
CREATE OR REPLACE FUNCTION public.admindeleteinvitation(p_invitationid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE from  InvitationTbl

	 WHERE InvitationTbl.InvitationID = p_invitationID AND AdminInvite=1;
    END;
END;
$function$
;

-- admineditinvitation
DROP FUNCTION IF EXISTS admineditinvitation;
CREATE OR REPLACE FUNCTION public.admineditinvitation(p_useremail character varying DEFAULT NULL::character varying, p_subtype integer DEFAULT NULL::integer, p_subexpiration character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE InvitationTbl
	   SET InviteDate = p_subExpiration
		  ,InvitationType = p_subType

	 WHERE InvitationTbl.InviteEmail = p_userEmail AND AdminInvite=1;
    END;
END;
$function$
;

-- adminedituser
DROP FUNCTION IF EXISTS adminedituser;
CREATE OR REPLACE FUNCTION public.adminedituser(p_userid integer, p_subtype integer, p_subexpiration character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE UserTbl
    SET UserSubExpiration = p_subExpiration::date,
        UserSubType = p_subType
    WHERE UserTbl.UserID = p_userID;
END;
$function$
;

-- allbooksfilter
DROP FUNCTION IF EXISTS allbooksfilter;
CREATE OR REPLACE FUNCTION public.allbooksfilter(p_bookswitch character varying DEFAULT NULL::character varying, p_tcswitch character varying DEFAULT NULL::character varying, p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    IF p_bookSwitch = 'on' and p_tcSwitch = 'off' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			 timecapsuledate IS NULL
      and

      (BookOwner = p_userID

      OR (

				(CircleID in
					(select CircleID
      from CircleMemberTbl
      where MemberID = p_userID)

      AND ( BookID not in
					(Select BookID
      from BkExcludeTbl
      where ExcludeUser = p_userID)
					)
					)
				)
				)

    order by BookCreateDate Desc, BookTitle;
    END; END IF;
    IF p_bookSwitch = 'off' and p_tcSwitch = 'on' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookOwner <> p_userID and
      timecapsuledate IS NOT NULL
      and

      (BookOwner <> p_userID

      OR (

				(CircleID in
					(select CircleID
      from CircleMemberTbl
      where MemberID = p_userID)

      AND ( BookID not in
					(Select BookID
      from BkExcludeTbl
      where ExcludeUser = p_userID)
					)
					)
				)
				)

    order by BookCreateDate Desc, BookTitle;
    END; END IF;
    IF p_bookSwitch = 'on' and p_tcSwitch = 'on' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookOwner <> p_userID

      OR (

				(CircleID in
					(select CircleID
      from CircleMemberTbl
      where MemberID = p_userID)

      AND ( BookID not in
					(Select BookID
      from BkExcludeTbl
      where ExcludeUser = p_userID)
					)
					)
				)

    order by BookCreateDate Desc, BookTitle;
    END; END IF;
    IF p_bookSwitch = 'off' and p_tcSwitch = 'off' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookID < 0;
    END; END IF;
    END;
END;
$function$
;

-- bookmemberliststatus
DROP FUNCTION IF EXISTS bookmemberliststatus;
CREATE OR REPLACE FUNCTION public.bookmemberliststatus(p_circleid integer DEFAULT NULL::integer, p_bookid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "MemberID" integer,
    "USERDISPLAYNAME" text,
    "ExcStatus" text,
    "Last" text,
    "First" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CircleMemberTbl.MemberID, trim(UserTbl.UserDisplayName) AS USERDISPLAYNAME, 'In' AS
					ExcStatus,
					trim(UserTbl.UserLast) AS Last, trim(UserTbl.UserFirst) AS First
					FROM CircleMemberTbl
					left join CircleTbl
					on CircleMemberTbl.CircleID = CircleTbl.CircleID
					join UserTbl
					on CircleMemberTbl.MemberID = UserTbl.UserID
					where CircleTbl.CircleID = p_circleID
					AND CircleMemberTbl.MemberID not in
					(select ExcludeUser from BKExcludeTbl
					where BookID = p_bookID)

					UNION

					Select CircleMemberTbl.MemberID, trim(UserTbl.UserDisplayName) AS USERDISPLAYNAME, 'Out' AS
					ExcStatus,
					trim(UserTbl.UserLast) AS Last, trim(UserTbl.UserFirst) AS First
					FROM CircleMemberTbl
					left join CircleTbl
					on CircleMemberTbl.CircleID = CircleTbl.CircleID
					join UserTbl
					on CircleMemberTbl.MemberID = UserTbl.UserID
					where CircleTbl.CircleID = p_circleID
					AND CircleMemberTbl.MemberID in
					(select ExcludeUser from BKExcludeTbl
					where BookID = p_bookID)

					Order by Last, First;
    END;
END;
$function$
;

-- cancelmessage
DROP FUNCTION IF EXISTS cancelmessage;
CREATE OR REPLACE FUNCTION public.cancelmessage(p_userid integer DEFAULT NULL::integer, p_messageid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO MessageCancelTbl
           (CancelUserID
           ,CancelDateTime
           ,CancelMessage)

     VALUES
           (p_userID
           ,CURRENT_TIMESTAMP
           ,p_messageID);
    END;
END;
$function$
;

-- changeemail
DROP FUNCTION IF EXISTS changeemail;
CREATE OR REPLACE FUNCTION public.changeemail(p_userid integer DEFAULT NULL::integer, p_newemail character varying DEFAULT NULL::character varying, INOUT p_lastid integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO ChangeEmailTbl
           (UserID
           ,NewEmail
           ,RequestDate)
     VALUES
           (p_userID
           ,p_newEmail
           ,CURRENT_TIMESTAMP);
    p_lastID := (SELECT lastval());
    RETURN;
    END;
END;
$function$
;

-- charterinvitationsopencount
DROP FUNCTION IF EXISTS charterinvitationsopencount;
CREATE OR REPLACE FUNCTION public.charterinvitationsopencount()
 RETURNS TABLE("userCount" integer, "InvitationType" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT count(*) AS "userCount", InvitationType AS "InvitationType" FROM invitationTbl

  where ConvertDate IS NULL
    AND DoNotContactDate IS NULL

  Group By InvitationType;
    END;
END;
$function$
;

-- charterinvitationsusedcount
DROP FUNCTION IF EXISTS charterinvitationsusedcount;
CREATE OR REPLACE FUNCTION public.charterinvitationsusedcount()
 RETURNS TABLE("InvitationType" integer, "userCount" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT InvitationType AS "InvitationType", count(*) AS "userCount" FROM invitationTbl

  where ConvertDate IS NOT NULL

  Group By InvitationType;
    END;
END;
$function$
;

-- checkexportdate
DROP FUNCTION IF EXISTS checkexportdate;
CREATE OR REPLACE FUNCTION public.checkexportdate(p_userid integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_export int;
BEGIN
    BEGIN
    SELECT CASE WHEN LastExport < (CURRENT_TIMESTAMP + INTERVAL '-90 day') OR LastExport IS NULL THEN 1 ELSE 0 END INTO v_export FROM UserTbl
		WHERE UserID = p_userID;
    RETURN v_export;
    END;
END;
$function$
;

-- checkexportstarted
DROP FUNCTION IF EXISTS checkexportstarted;
CREATE OR REPLACE FUNCTION public.checkexportstarted(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("ExportStarted" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_export int;
BEGIN
    BEGIN
    RETURN QUERY Select ExportStarted
		from UserTbl
		WHERE UserID = p_userID;
    RETURN;
    END;
END;
$function$
;

-- combinedinvitationssent
DROP FUNCTION IF EXISTS combinedinvitationssent;
CREATE OR REPLACE FUNCTION public.combinedinvitationssent(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "ID" integer,
    "invitedName" varchar(110),
    "invitedEmail" varchar(75),
    "inviteType" text,
    "ConvertDate" timestamp without time zone)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CONCAT ('C', CircleMemberTbl.CircleMemID) AS ID, UserTbl.UserDisplayName AS invitedName, UserTbl.UserEmail AS invitedEmail, 'Invited to your circles' AS inviteType, AcceptedDate AS ConvertDate
		from CircleMemberTbl
			Join CircleTbl on CircleMemberTbl.CircleID = CircleTbl.CircleId
			join UserTbl on CircleMemberTbl.MemberID = UserTbl.UserID

		where CircleTbl.CircleOwner = p_userID
			and Status is null
			and CircleOwner = CirclePrimary

	UNION

		Select CONCAT('I', InvitationTbl.InvitationID) AS ID, InviteName AS invitedName, InviteEmail as invitedEmail, 'Invited new member' AS inviteType, ConvertDate
		from InvitationTbl
		where InvitedBy = p_userID and ConvertDate is null and DoNotContactDate is null;
    END;
END;
$function$
;

-- confirmemail
DROP FUNCTION IF EXISTS confirmemail;
CREATE OR REPLACE FUNCTION public.confirmemail(p_userid integer DEFAULT NULL::integer, p_recno integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_foundRecords int;
    v_newEmail varchar(75);
BEGIN
    BEGIN
    SELECT count(ChangeEmailID) INTO v_foundRecords FROM ChangeEmailTbl
where userID = p_userID and ChangeEmailID = p_recno and verifyDate is null;
    IF v_foundRecords > 0 THEN
    BEGIN
    UPDATE ChangeEmailTbl
   SET VerifyDate = CURRENT_TIMESTAMP
	WHERE UserID = p_userID and ChangeEmailID = p_recno;
    SELECT newEmail INTO v_newEmail FROM ChangeEmailTbl 
	where UserID = p_userID and ChangeEmailID = p_recno;
    UPDATE UserTbl
	   SET UserEmail = v_newEmail
     
	 WHERE UserID = p_userID;
    RETURN 1;
    END;
    ELSE
    RETURN 2;
    END IF;
    END;
END;
$function$
;

-- declineinvitation
DROP FUNCTION IF EXISTS declineinvitation;
CREATE OR REPLACE FUNCTION public.declineinvitation(p_circlememid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE CircleMemberTbl
   SET RejectedDate = CURRENT_TIMESTAMP
      ,Status = 'Declined'
 WHERE circleMemID = p_circleMemID;
    END;
END;
$function$
;

-- deletebook
DROP FUNCTION IF EXISTS deletebook;
CREATE OR REPLACE FUNCTION public.deletebook(p_bookid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    DELETE FROM BookContentTbl
      WHERE BookID = p_bookID;
    DELETE FROM BookTbl
		  WHERE BookID = p_bookID;
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    RETURN QUERY Select distinct BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
            BookTbl.TimeCapsuleDate, 
            to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
            BookTbl.CircleID,
            UserTbl.UserDisplayName, v_primary AS primaryCircle
            from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 
			BookOwner = p_userID

			OR (

				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					)
				)

            order by date Desc, BookTitle;
    END;
END;
$function$
;

-- deletecircle
DROP FUNCTION IF EXISTS deletecircle;
CREATE OR REPLACE FUNCTION public.deletecircle(p_circleid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CircleID" integer,
    "CircleName" text,
    "ButtonName" text,
    "CirclePrimary" integer,
    "SELECTED" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    Delete from CircleTbl
            where CircleTbl.CircleID = p_circleID and CircleTbl.CircleOwner = p_userID;
    RETURN QUERY Select CircleTbl.CircleID, trim(CircleTbl.CircleName) AS CircleName, SUBSTRING(CircleName, 1, 15) AS ButtonName, CircleTbl.CirclePrimary,
			CASE WHEN CircleTbl.CirclePrimary IS NOT NULL
               THEN 'true'
               ELSE 'false'
          END AS SELECTED
			from CircleTbl
            where CircleTbl.CircleOwner = p_userID 
            order by SELECTED DESC, CircleTbl.CircleName;
    END;
END;
$function$
;

-- deletecirclemember
DROP FUNCTION IF EXISTS deletecirclemember;
CREATE OR REPLACE FUNCTION public.deletecirclemember(p_memberid integer DEFAULT NULL::integer, p_circleid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    delete from CircleMemberTbl
   where MemberID= p_memberID AND CircleID = p_circleID;
    END;
END;
$function$
;

-- deletecomment
DROP FUNCTION IF EXISTS deletecomment;
CREATE OR REPLACE FUNCTION public.deletecomment(p_commentid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM CommentTbl
      WHERE CommentID = p_commentID;
    END;
END;
$function$
;

-- deleteconnection
DROP FUNCTION IF EXISTS deleteconnection;
CREATE OR REPLACE FUNCTION public.deleteconnection(p_memberid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE from CircleMemberTbl 
	where 
	CircleMemberTbl.MemberID = p_memberID
	AND CircleMemberTbl.InvitedBy = p_userID;
    DELETE from CircleMemberTbl
	where 
	CircleMemberTbl.MemberID = p_userID 
	AND CircleMemberTbl.InvitedBy = p_memberID;
    END;
END;
$function$
;

-- deleteeveryonecirclemember
DROP FUNCTION IF EXISTS deleteeveryonecirclemember;
CREATE OR REPLACE FUNCTION public.deleteeveryonecirclemember(p_memberid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE from CircleMemberTbl 
	where 
	CircleMemberTbl.MemberID = p_memberID
	AND CircleMemberTbl.CircleID 
	in 

	(select CircleID from CircleTbl 
			where CircleOwner = p_userID);
    END;
END;
$function$
;

-- deletefrommediatbl
DROP FUNCTION IF EXISTS deletefrommediatbl;
CREATE OR REPLACE FUNCTION public.deletefrommediatbl(p_medialoc character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    Delete from MediaTbl 
        where MediaLoc = p_mediaLoc;
    END;
END;
$function$
;

-- deleteinterviewquestion
DROP FUNCTION IF EXISTS deleteinterviewquestion;
CREATE OR REPLACE FUNCTION public.deleteinterviewquestion(p_interviewid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM InterviewTbl
		  WHERE InterviewID = p_InterviewID;
    END;
END;
$function$
;

-- deleteitem
DROP FUNCTION IF EXISTS deleteitem;
CREATE OR REPLACE FUNCTION public.deleteitem(p_itemid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM ListItemTbl
      WHERE ListItemID = p_itemID;
    END;
END;
$function$
;

-- deletelist
DROP FUNCTION IF EXISTS deletelist;
CREATE OR REPLACE FUNCTION public.deletelist(p_listid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM ListTbl
      WHERE ListID = p_listID AND ListOwner = p_userID;
    DELETE FROM ListItemTbl
	 WHERE ListID = p_listID;
    END;
END;
$function$
;

-- deletememory
DROP FUNCTION IF EXISTS deletememory;
CREATE OR REPLACE FUNCTION public.deletememory(p_storyid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    delete from StoryTbl where storyID = p_storyID;
    delete from MediaTbl where storyID = p_storyID;
    END;
END;
$function$
;

-- deletemyqcanswer
DROP FUNCTION IF EXISTS deletemyqcanswer;
CREATE OR REPLACE FUNCTION public.deletemyqcanswer(p_userid integer DEFAULT NULL::integer, p_qcid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    Delete from VPAnswerTbl
                    WHERE userID = p_userID
                    AND ViewPointID = p_qcID;
    END;
END;
$function$
;

-- deletenews
DROP FUNCTION IF EXISTS deletenews;
CREATE OR REPLACE FUNCTION public.deletenews(p_newsid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM NewsCancelTbl
    WHERE NewsID = p_newsID;
    DELETE FROM NewsTbl
    WHERE NewsID = p_newsID;
    END;
END;
$function$
;

-- deleteqcquestion
DROP FUNCTION IF EXISTS deleteqcquestion;
CREATE OR REPLACE FUNCTION public.deleteqcquestion(p_viewpointid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE from ViewPointTbl 
	where ViewPointID = p_viewPointID;
    END;
END;
$function$
;

-- deletetemplate
DROP FUNCTION IF EXISTS deletetemplate;
CREATE OR REPLACE FUNCTION public.deletetemplate(p_templateid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM TemplateTbl 
	WHERE MemoryTemplateID = p_templateID;
    END;
END;
$function$
;

-- dupcheck
DROP FUNCTION IF EXISTS dupcheck;
CREATE OR REPLACE FUNCTION public.dupcheck(p_useremail character varying)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_result int;
BEGIN
    IF EXISTS (SELECT 1 FROM UserTbl WHERE UserEmail = p_userEmail) THEN
        v_result := 1;
    ELSE
        v_result := 0;
    END IF;
    RETURN v_result;
END;
$function$
;

-- editbook
DROP FUNCTION IF EXISTS editbook;
CREATE OR REPLACE FUNCTION public.editbook(p_userid integer DEFAULT NULL::integer, p_bookid integer DEFAULT NULL::integer, p_booktitle character varying DEFAULT NULL::character varying, p_tcdate date DEFAULT NULL::date, p_circleid integer DEFAULT NULL::integer, p_covercolor character varying DEFAULT NULL::character varying, p_coverfont character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE BookTbl
	   SET BookTitle = p_bookTitle
		  ,TimeCapsuleDate = p_tcDate
		  ,CircleID = p_circleID
		  ,BookColor = p_coverColor
		  ,BookFont = p_coverFont
	 WHERE BookTbl.BookID = p_bookID;
    END;
END;
$function$
;

-- edititem
DROP FUNCTION IF EXISTS edititem;
CREATE OR REPLACE FUNCTION public.edititem(p_itemname character varying DEFAULT NULL::character varying, p_duedate date DEFAULT NULL::date, p_assignedto character varying DEFAULT NULL::character varying, p_notes character varying DEFAULT NULL::character varying, p_listid integer DEFAULT NULL::integer, p_itemid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE ListItemTbl
   SET Item = p_itemName
      ,DueDate = p_dueDate
      ,AssignedTo = p_assignedTo
      ,Notes = p_notes
      ,ListID = p_listID
 WHERE ListItemID = p_itemID;
    END;
END;
$function$
;

-- editlist
DROP FUNCTION IF EXISTS editlist;
CREATE OR REPLACE FUNCTION public.editlist(p_userid integer DEFAULT NULL::integer, p_listid integer DEFAULT NULL::integer, p_listname character varying DEFAULT NULL::character varying, p_listdate integer DEFAULT NULL::integer, p_circleid integer DEFAULT NULL::integer, p_listcirclerights character varying DEFAULT NULL::character varying, p_listcolor character varying DEFAULT NULL::character varying, p_listassigned integer DEFAULT NULL::integer, p_listtype integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE ListTbl
	   SET ListName = p_listName
		  ,ListDate = p_listDate
		  ,ListCircle = p_circleID
		  ,ListCircleRights = p_listCircleRights
		  ,ListColor = p_listColor
		  ,ListAssigned = p_listAssigned
		  ,ListType = p_listType
	 WHERE ListTbl.ListID = p_listID;
    END;
END;
$function$
;

-- editqcquestion
DROP FUNCTION IF EXISTS editqcquestion;
CREATE OR REPLACE FUNCTION public.editqcquestion(p_userid integer DEFAULT NULL::integer, p_viewpointid integer DEFAULT NULL::integer, p_viewpointdate date DEFAULT NULL::date, p_viewpointq text DEFAULT NULL::text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE ViewPointTbl
   SET ViewPointQ = p_viewPointQ
      ,ViewPointDate = p_viewPointDate
      ,QCUserID = p_userID
 WHERE ViewPointID = p_viewPointID;
    END;
END;
$function$
;

-- editthisqcquestion
DROP FUNCTION IF EXISTS editthisqcquestion;
CREATE OR REPLACE FUNCTION public.editthisqcquestion(p_userid integer DEFAULT NULL::integer, p_viewpointid integer DEFAULT NULL::integer, p_viewpointdate date DEFAULT NULL::date, p_viewpointq text DEFAULT NULL::text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE ViewPointTbl
   SET ViewPointQ = p_viewPointQ
      ,ViewPointDate = p_viewPointDate
      ,QCUserID = p_userID
 WHERE ViewPointID = p_viewPointID;
    END;
END;
$function$
;

-- forgotpw
DROP FUNCTION IF EXISTS forgotpw;
CREATE OR REPLACE FUNCTION public.forgotpw(p_email character varying, p_hash character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_theDate timestamp;
BEGIN
    v_theDate := CURRENT_TIMESTAMP;

    UPDATE UserTbl
    SET ForgotPW = v_theDate, UserHash = p_hash
    WHERE UserEmail = p_email;

    INSERT INTO ForgotPWTbl (ForgotUUID, ForgotEmail, ForgotDate)
    VALUES ('temp password assigned', p_email, v_theDate);
END;
$function$
;

-- getallbooks
DROP FUNCTION IF EXISTS getallbooks;
CREATE OR REPLACE FUNCTION public.getallbooks(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    SELECT COUNT(*) INTO v_recordCount FROM BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID
			
			where 

			(BookOwner = p_userID AND BookTbl.TimeCapsuleDate IS NULL)

			OR (
			(BookTbl.TimeCapsuleDate IS NULL) AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					)
				);
    RETURN QUERY Select distinct v_recordCount, BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner, BookTbl.TimeCapsuleDate, to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date, BookTbl.CircleID, UserTbl.UserDisplayName, v_primary AS primaryCircle from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 

			(BookOwner = p_userID AND BookTbl.TimeCapsuleDate IS NULL)

			OR (
			(BookTbl.TimeCapsuleDate IS NULL) AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					)
				)
				
            order by  BookTitle
								OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getallhelp
DROP FUNCTION IF EXISTS getallhelp;
CREATE OR REPLACE FUNCTION public.getallhelp(p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "Hidden" integer,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
               where 
			   StoryTbl.StoryTypeID = 5;
    IF v_recordCount = 0 THEN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StarterTbl;
    RETURN QUERY SELECT v_recordCount, StarterTbl.StoryID, trim(StarterTbl.StoryTitle) as StoryTitle, trim(StarterTbl.StoryText) as StoryText, StarterTbl.Userid, StarterTbl.StoryTypeID, to_char(Startertbl.StoryDate, 'Mon DD, YYYY') as date, trim(StarterTbl.Interviewee) as Interviewee, StarterTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StarterTbl.StoryIngredients, StarterTbl.Hidden, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StarterTbl
               left join UserTbl
               on StarterTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StarterTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
			  
			   order by StoryDate Desc, StoryTitle LIMIT 1;
    END;
    ELSE
    BEGIN
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, HelpTypeTbl.HelpType, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, StoryTbl.Hidden, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
			   left join HelpTypeTbl 
			   on (StoryTbl.HelpTypeID = HelpTypeTbl.HelpTypeID)

               where 
			   StoryTbl.StoryTypeID = 5

                    order by HelpType, StoryDate Desc, StoryTitle 
					OFFSET p_recordStart LIMIT p_pageLength;
    END; END IF;
    END;
END;
$function$
;

-- getallmybooks
DROP FUNCTION IF EXISTS getallmybooks;
CREATE OR REPLACE FUNCTION public.getallmybooks(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110))
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID
			
			where 

			(BookOwner = p_userID );
    RETURN QUERY Select distinct v_recordCount, BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner, BookTbl.TimeCapsuleDate, to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date, BookTbl.CircleID, UserTbl.UserDisplayName from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 

						(BookOwner = p_userID )
				
            order by  BookTitle
								OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getallnews
DROP FUNCTION IF EXISTS getallnews;
CREATE OR REPLACE FUNCTION public.getallnews(p_daysback integer DEFAULT 90)
 RETURNS TABLE(
    "NewsID" integer,
    "NewsTitle" character varying,
    "NewsText" character varying,
    "NewsDate" date,
    "DispNewsDate" text,
    "NewsSubType" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT
        NewsID,
        NewsTitle,
        NewsText,
        NewsDate,
        to_char(NewsDate, 'Mon DD, YYYY') AS DispNewsDate,
        NewsSubType
    FROM NewsTbl
    WHERE NewsDate >= (CURRENT_TIMESTAMP - (p_daysBack || ' day')::interval)
    ORDER BY NewsDate DESC, NewsID DESC;
    END;
END;
$function$
;

-- getallquickconnectquestions
DROP FUNCTION IF EXISTS getallquickconnectquestions;
CREATE OR REPLACE FUNCTION public.getallquickconnectquestions()
 RETURNS TABLE(
    "ViewPointID" integer,
    "ViewPointQ" character varying,
    "ViewPointDate" text,
    "QCUserID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT ViewPointID AS "ViewPointID", ViewPointQ::varchar::varchar AS "ViewPointQ", to_char(ViewPointDate, 'YYYY-MM-DD') AS "ViewPointDate", QCUserID AS "QCUserID" from ViewPointTbl 

			order by ViewPointDate desc;
    END;
END;
$function$
;

-- getallstories
DROP FUNCTION IF EXISTS getallstories;
CREATE OR REPLACE FUNCTION public.getallstories(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" text,
    "Hidden" integer,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_journalCircle int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleTbl.CircleID INTO v_journalCircle FROM CircleTbl 
		where JournalID = p_userID;
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
               where 
			   StoryTbl.StoryTypeID <> 5 
			   AND 
			   StoryTbl.Hidden <> 1 
			   AND
			   StoryTbl.CircleID <> v_journalCircle 

			   AND
			   (
                    (CircleID in
                    (select CircleID from CircleMemberTbl
                    where MemberID = p_userID AND AcceptedDate IS NOT NULL) )

                    AND ( StoryTbl.StoryID not in
                    (Select StoryID from ExcludeTbl
                    where ExcludeUser = p_userID)
                    )
					

                    OR (storyTbl.UserID = p_userID 
					)
					);
    IF v_recordCount = 0 THEN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StarterTbl;
    RETURN QUERY SELECT v_recordCount, StarterTbl.StoryID, trim(StarterTbl.StoryTitle) as StoryTitle, trim(StarterTbl.StoryText) as StoryText, StarterTbl.Userid, StarterTbl.StoryTypeID, to_char(Startertbl.StoryDate, 'Mon DD, YYYY') as date, trim(StarterTbl.Interviewee) as Interviewee, StarterTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StarterTbl.StoryIngredients, StarterTbl.Hidden, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StarterTbl
               left join UserTbl
               on StarterTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StarterTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
			  
			   order by StoryDate Desc, StoryTitle LIMIT 1;
    END;
    ELSE
    BEGIN
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, StoryTbl.Hidden, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 
			   StoryTbl.StoryTypeID <> 5
			   AND
			   StoryTbl.Hidden <> 1 
			   AND
			   StoryTbl.CircleID <> v_journalCircle 
			   AND
			   StoryTbl.CircleID <> 0
			   AND 
			   StoryTbl.CircleID IS NOT NULL

			   AND 
			   (
                    (CircleID in
                    (select CircleID from CircleMemberTbl
                    where MemberID = p_userID AND AcceptedDate IS NOT NULL) )

                    AND ( StoryTbl.StoryID not in
                    (Select StoryID from ExcludeTbl
                    where ExcludeUser = p_userID)
                    )
					

                    OR (storyTbl.UserID = p_userID 
					)
					)

                    order by StoryDate Desc, StoryTitle 
					OFFSET p_recordStart LIMIT p_pageLength;
    END; END IF;
    END;
END;
$function$
;

-- getalltcs
DROP FUNCTION IF EXISTS getalltcs;
CREATE OR REPLACE FUNCTION public.getalltcs(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    SELECT COUNT(*) INTO v_recordCount FROM BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID
			
			where 

			(BookOwner = p_userID AND BookTbl.TimeCapsuleDate IS NOT NULL)

			OR (
			(BookTbl.TimeCapsuleDate IS NOT NULL) AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					)
				);
    RETURN QUERY Select distinct v_recordCount, BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner, BookTbl.TimeCapsuleDate, to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date, BookTbl.CircleID, UserTbl.UserDisplayName, v_primary AS primaryCircle from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 

			(BookOwner = p_userID AND BookTbl.TimeCapsuleDate IS NOT NULL)

			OR (
			(BookTbl.TimeCapsuleDate IS NOT NULL) AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					)
				)
				
            order by  BookTitle
								OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getanswers
DROP FUNCTION IF EXISTS getanswers;
CREATE OR REPLACE FUNCTION public.getanswers(p_userid integer DEFAULT NULL::integer, p_viewpointid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "VPAnswer" text,
    "VPAnswerID" integer,
    "UserDisplayName" varchar(110),
    "UserID" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl 
	where CirclePrimary = p_userID;
    RETURN QUERY Select trim(VPAnswer) as VPAnswer, VPAnswerID, UserDisplayName, UserTbl.UserID
		from VPAnswerTbl
		left join UserTbl 
		on VPAnswerTbl.UserID = UserTbl.UserID
		where ViewPointID = p_viewPointID

		AND VPAnswer <> ''

		AND VPAnswerTbl.UserID in 
		(select MemberID from CircleMemberTbl 
		where CircleMemberTbl.CircleID =  v_primary)

		UNION

		Select trim(VPAnswer) as VPAnswer, VPAnswerID, 'Me' AS UserDisplayName, UserTbl.UserID
		from VPAnswerTbl
		left join UserTbl 
		on VPAnswerTbl.UserID = UserTbl.UserID
		where ViewPointID = p_viewPointID

		AND VPAnswer <> ''

		AND VPAnswerTbl.UserID = p_userID

		order by UserDisplayName;
    END;
END;
$function$
;

-- getbookcontent
DROP FUNCTION IF EXISTS getbookcontent;
CREATE OR REPLACE FUNCTION public.getbookcontent(p_userid integer DEFAULT NULL::integer, p_bookid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "StoryDate" timestamp without time zone,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaType" character varying,
    "FeatureMedia" integer,
    "mediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primaryCircle int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primaryCircle FROM CircleTbl where CircleOwner = p_userID AND CirclePrimary is not null;
    RETURN QUERY Select StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle,
        trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid,
        StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date,
		StoryTbl.StoryDate,
        trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID,
        trim(UserTbl.UserDisplayName) as UserDisplayName,
        StoryTbl.StoryIngredients, UserTbl.UserMediaLoc,
        MediaTbl.MediaLoc, MediaTbl.MediaType, mediaTbl.FeatureMedia,
        mediaTbl.mediaType
        from StoryTbl
        left join UserTbl
        on StoryTbl.UserID = UserTbl.UserID
        left join MediaTbl
        on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

        where 
		
		(StoryTbl.StoryID IN 
		(select StoryID from BookContentTbl
		where BookID = p_bookID))

		AND 
		(
		StoryTbl.UserID = p_userID OR 
		StoryTbl.CircleID in 
		(select CircleID from CircleMemberTbl where memberID = p_userID ))
        order by StoryTitle;
    END;
END;
$function$
;

-- getbookcontentdate
DROP FUNCTION IF EXISTS getbookcontentdate;
CREATE OR REPLACE FUNCTION public.getbookcontentdate(p_userid integer DEFAULT NULL::integer, p_bookid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaType" character varying,
    "FeatureMedia" integer,
    "mediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primaryCircle int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primaryCircle FROM CircleTbl where CircleOwner = p_userID AND CirclePrimary is not null;
    RETURN QUERY Select StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle,
        trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid,
        StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date,
        trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID,
        trim(UserTbl.UserDisplayName) as UserDisplayName,
        StoryTbl.StoryIngredients, UserTbl.UserMediaLoc,
        MediaTbl.MediaLoc, MediaTbl.MediaType, mediaTbl.FeatureMedia,
        mediaTbl.mediaType
        from StoryTbl
        left join UserTbl
        on StoryTbl.UserID = UserTbl.UserID
        left join MediaTbl
        on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

        where 
		
		(StoryTbl.StoryID IN 
		(select StoryID from BookContentTbl
		where BookID = p_bookID))

		AND 
		(
		StoryTbl.UserID = p_userID OR 
		StoryTbl.CircleID in 
		(select CircleID from CircleMemberTbl where memberID = p_userID ))
        order by StoryDate Desc, StoryTitle;
    END;
END;
$function$
;

-- getbookdetails
DROP FUNCTION IF EXISTS getbookdetails;
CREATE OR REPLACE FUNCTION public.getbookdetails(p_bookid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "BookColor" character varying,
    "BookFont" character varying,
    "UserDisplayName" varchar(110),
    "UserMediaLoc" varchar(100))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select distinct BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
            BookTbl.TimeCapsuleDate, 
            to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
            BookTbl.CircleID,
			BookTbl.BookColor,
			BookTbl.BookFont,
            UserTbl.UserDisplayName,
			UserTbl.UserMediaLoc
            from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID
		where BookTbl.BookID = p_bookID;
    END;
END;
$function$
;

-- getbooklistforstory
DROP FUNCTION IF EXISTS getbooklistforstory;
CREATE OR REPLACE FUNCTION public.getbooklistforstory(p_storyid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "StoryID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "UserDisplayName" text,
    "Owner" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select BookContentTbl.BookID, BookContentTbl.StoryID, trim(BookTbl.BookTitle) AS BookTitle,
                    BookTbl.BookOwner, BookTbl.TimeCapsuleDate, trim(UserDisplayName) as UserDisplayName, 0 AS Owner
                    from BookContentTbl
                    left join BookTbl
                    on BookContentTbl.BookID = BookTbl.BookID
                    left join UserTbl
                    on BookTbl.BookOwner = UserTbl.UserID
                    where

                    BookContentTbl.StoryID = p_storyID
                    and BookTbl.BookOwner <> p_userID

                        UNION ALL

                        Select BookContentTbl.BookID, BookContentTbl.StoryID, trim(BookTbl.BookTitle) AS BookTitle,
                        BookTbl.BookOwner, BookTbl.TimeCapsuleDate, trim(UserDisplayName) as UserDisplayName, 1 AS Owner
                        from BookContentTbl
                        left join BookTbl
                        on BookContentTbl.BookID = BookTbl.BookID
                        left join UserTbl
                        on BookTbl.BookOwner = UserTbl.UserID
                        where

                        BookContentTbl.StoryID = p_storyID
                        and BookTbl.BookOwner = p_userID

                        order by BookTitle;
    END;
END;
$function$
;

-- getbooks
DROP FUNCTION IF EXISTS getbooks;
CREATE OR REPLACE FUNCTION public.getbooks(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    RETURN QUERY Select distinct BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
            BookTbl.TimeCapsuleDate, 
            to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
            BookTbl.CircleID,
            UserTbl.UserDisplayName, v_primary AS primaryCircle
            from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 

			BookOwner = p_userID

			OR (
			(BookTbl.TimeCapsuleDate <= CURRENT_TIMESTAMP OR BookTbl.TimeCapsuleDate IS NULL) AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					)
				)
				
            order by BookTitle;
    END;
END;
$function$
;

-- getbookstoaddlist
DROP FUNCTION IF EXISTS getbookstoaddlist;
CREATE OR REPLACE FUNCTION public.getbookstoaddlist(p_userid integer DEFAULT NULL::integer, p_listid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" character varying,
    "BookOwner" integer,
    "date" text,
    "BookStatus" text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    RETURN QUERY SELECT        BookTbl.BookID, BookTbl.BookTitle, BookTbl.BookOwner, to_char(BookCreateDate, 'Mon DD, YYYY') as date
			,CAST(1 as bit) AS BookStatus
FROM            BookTbl INNER JOIN
                         BookListTbl ON BookTbl.BookID = BookListTbl.BookID

						 where BookOwner = p_userID
						 AND listID = p_listID

UNION

SELECT       DISTINCT BookTbl.BookID, BookTbl.BookTitle, BookTbl.BookOwner, to_char(BookCreateDate, 'Mon DD, YYYY') as date
			,CAST(0 as bit) AS BookStatus
FROM            BookTbl RIGHT OUTER JOIN
                         BookListTbl ON BookTbl.BookID = BookListTbl.BookID

						 where BookOwner = p_userID
						 AND BookTbl.BookID NOT IN 
(SELECT        BookTbl.BookID
FROM            BookTbl INNER JOIN
                         BookListTbl ON BookTbl.BookID = BookListTbl.BookID

						 where BookOwner = p_userID
						 AND listID = p_listID)

UNION

SELECT       DISTINCT BookTbl.BookID, BookTbl.BookTitle, BookTbl.BookOwner, to_char(BookCreateDate, 'Mon DD, YYYY') as date
			,CAST(0 as bit) AS BookStatus
FROM            BookTbl 

						 where BookOwner = p_userID
						 AND BookTbl.BookID  NOT IN 
(SELECT        BookListTbl.BookID
FROM            
                         BookListTbl )

						 Order By BookTbl.BookTitle;
    END;
END;
$function$
;

-- getbookstoaddstory
DROP FUNCTION IF EXISTS getbookstoaddstory;
CREATE OR REPLACE FUNCTION public.getbookstoaddstory(p_userid integer DEFAULT NULL::integer, p_storyid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" character varying,
    "BookOwner" integer,
    "date" text,
    "BookStatus" text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    RETURN QUERY SELECT        BookTbl.BookID, BookTbl.BookTitle, BookTbl.BookOwner, to_char(BookCreateDate, 'Mon DD, YYYY') as date
			,CAST(1 as bit) AS BookStatus
FROM            BookTbl INNER JOIN
                         BookContentTbl ON BookTbl.BookID = BookContentTbl.BookID

						 where BookOwner = p_userID
						 AND storyID = p_storyID

UNION

SELECT       DISTINCT BookTbl.BookID, BookTbl.BookTitle, BookTbl.BookOwner, to_char(BookCreateDate, 'Mon DD, YYYY') as date
			,CAST(0 as bit) AS BookStatus
FROM            BookTbl RIGHT OUTER JOIN
                         BookContentTbl ON BookTbl.BookID = BookContentTbl.BookID

						 where BookOwner = p_userID
						 AND BookTbl.BookID NOT IN 
(SELECT        BookTbl.BookID
FROM            BookTbl INNER JOIN
                         BookContentTbl ON BookTbl.BookID = BookContentTbl.BookID

						 where BookOwner = p_userID
						 AND storyID = p_storyID)

UNION

SELECT       DISTINCT BookTbl.BookID, BookTbl.BookTitle, BookTbl.BookOwner, to_char(BookCreateDate, 'Mon DD, YYYY') as date
			,CAST(0 as bit) AS BookStatus
FROM            BookTbl 

						 where BookOwner = p_userID
						 AND BookTbl.BookID  NOT IN 
(SELECT        BookContentTbl.BookID
FROM            
                         BookContentTbl )

						 Order By BookTbl.BookTitle;
    END;
END;
$function$
;

-- getbooktoc
DROP FUNCTION IF EXISTS getbooktoc;
CREATE OR REPLACE FUNCTION public.getbooktoc(p_userid integer DEFAULT NULL::integer, p_bookid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "StoryID" integer,
    "StoryTitle" text,
    "Interviewee" character varying,
    "StoryIngredients" character varying,
    "StoryText" character varying,
    "Userid" integer,
    "StoryTypeID" integer,
    "StoryDate" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "UserMediaLoc" varchar(100))
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primaryCircle int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primaryCircle FROM CircleTbl where CircleOwner = p_userID AND CirclePrimary is not null;
    RETURN QUERY Select StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle,
        StoryTbl.Interviewee, StoryTbl.StoryIngredients, StoryTbl.StoryText,
        StoryTbl.Userid,
        StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as StoryDate,
        StoryTbl.CircleID,
        trim(UserTbl.UserDisplayName) as UserDisplayName,
        UserTbl.UserMediaLoc

        from StoryTbl
        left join UserTbl
        on StoryTbl.UserID = UserTbl.UserID


        where 
		
		(StoryTbl.StoryID IN 
		(select StoryID from BookContentTbl
		where BookID = p_bookID))

		AND 
		(
		StoryTbl.UserID = p_userID OR 
		StoryTbl.CircleID in 
		(select CircleID from CircleMemberTbl where memberID = p_userID ))

        UNION
        Select ListTbl.ListID, trim(ListTbl.ListName) as StoryTitle,
                Null AS Interviewee, Null as StoryIngredients, Null as StoryText,
        ListTbl.ListOwner  as Userid,
        10 AS StoryTypeID,
       ('2020-01-01' + INTERVAL '7 day') AS FutureDate,
        ListTbl.ListCircle as CircleID,
                trim(UserTbl.UserDisplayName) as UserDisplayName,
        UserTbl.UserMediaLoc

        from ListTbl
        left join UserTbl
        on ListTbl.ListOwner = UserTbl.UserID

        WHERE 

        (ListTbl.ListOwner = p_userID

			OR (

				(ListCircle in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)
					)
				))
				AND 
				ListTbl.ListID IN 
					(SELECT ListID from BookListTbl where BookID = p_bookID)


        order by StoryTitle;
    END;
END;
$function$
;

-- getcircleanswerstoday
DROP FUNCTION IF EXISTS getcircleanswerstoday;
CREATE OR REPLACE FUNCTION public.getcircleanswerstoday(p_userid integer DEFAULT NULL::integer, p_today date DEFAULT NULL::date)
 RETURNS TABLE(
    "VPAnswer" text,
    "VPAnswerID" integer,
    "UserDisplayName" varchar(110))
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_vpID int;
    v_primary int;
BEGIN
    BEGIN
    SELECT ViewPointID INTO v_vpID FROM ViewPointTbl 
	where ViewPointDate = p_today;
    SELECT CircleID INTO v_primary FROM CircleTbl 
	where CirclePrimary = p_userID;
    RETURN QUERY Select trim(VPAnswer) as VPAnswer, VPAnswerID, UserDisplayName
		from VPAnswerTbl
		left join UserTbl 
		on VPAnswerTbl.UserID = UserTbl.UserID
		where ViewPointID = v_vpID

		AND VPAnswer <> ''

		AND VPAnswerTbl.UserID in 
		(select MemberID from CircleMemberTbl 
		where CircleMemberTbl.CircleID =  v_primary)

		order by UserDisplayName;
    END;
END;
$function$
;

-- getcirclemembersall
DROP FUNCTION IF EXISTS getcirclemembersall;
CREATE OR REPLACE FUNCTION public.getcirclemembersall(p_circleid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CircleMemID" integer,
    "CircleID" integer,
    "MemberID" integer,
    "CircleStatus" text,
    "RejectedDate" timestamp without time zone,
    "Status" varchar(50),
    "UserDisplayName" varchar(110),
    "UserMediaLoc" varchar(100))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT CircleMemberTbl.CircleMemID, CircleMemberTbl.CircleID, CircleMemberTbl.MemberID, 
	
	CASE WHEN CircleMemberTbl.AcceptedDate is NULL THEN 'Invitation Not Yet Accepted' ELSE 'Invitation Accepted' END AS CircleStatus,
	
	CircleMemberTbl.RejectedDate, CircleMemberTbl.Status, UserTbl.UserDisplayName, UserTbl.UserMediaLoc
	from CircleMemberTbl 
	join UserTbl 
	on CircleMemberTbl.MemberID = UserTbl.UserID 
	AND 
	CircleID = p_circleID



	order by UserDisplayName;
    END;
END;
$function$
;

-- getcirclemembersforbook
DROP FUNCTION IF EXISTS getcirclemembersforbook;
CREATE OR REPLACE FUNCTION public.getcirclemembersforbook(p_userid integer DEFAULT NULL::integer, p_circleid integer DEFAULT NULL::integer, p_bookid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "MemberID" integer,
    "USERDISPLAYNAME" text,
    "ExcStatus" text,
    "Last" text,
    "First" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CircleMemberTbl.MemberID, trim(UserTbl.UserDisplayName) AS USERDISPLAYNAME, 'In' AS ExcStatus,
			trim(UserTbl.UserLast) AS Last, trim(UserTbl.UserFirst) AS First
			FROM CircleMemberTbl
			left join CircleTbl
			on CircleMemberTbl.CircleID = CircleTbl.CircleID
			join UserTbl
			on CircleMemberTbl.MemberID = UserTbl.UserID
			where CircleTbl.CircleID = p_circleID
			AND CircleMemberTbl.MemberID not in
			(select ExcludeUser from BKExcludeTbl
			where BookID = p_bookID)
			and CircleMemberTbl.Status = 'Accepted'

			UNION

			Select CircleMemberTbl.MemberID, trim(UserTbl.UserDisplayName) AS USERDISPLAYNAME, 'Out' AS ExcStatus,
			trim(UserTbl.UserLast) AS Last, trim(UserTbl.UserFirst) AS First
			FROM CircleMemberTbl
			left join CircleTbl
			on CircleMemberTbl.CircleID = CircleTbl.CircleID
			join UserTbl
			on CircleMemberTbl.MemberID = UserTbl.UserID
			where CircleTbl.CircleID = p_circleID
			AND CircleMemberTbl.MemberID in
			(select ExcludeUser from BKExcludeTbl
			where bookID = p_bookID)
			and CircleMemberTbl.Status = 'Accepted'

			order by Last, First;
    END;
END;
$function$
;

-- getcirclemembersforstory
DROP FUNCTION IF EXISTS getcirclemembersforstory;
CREATE OR REPLACE FUNCTION public.getcirclemembersforstory(p_userid integer DEFAULT NULL::integer, p_circleid integer DEFAULT NULL::integer, p_storyid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "MemberID" integer,
    "USERDISPLAYNAME" text,
    "ExcStatus" text,
    "Last" text,
    "First" text,
    "CircleMemID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CircleMemberTbl.MemberID, trim(UserTbl.UserDisplayName) AS USERDISPLAYNAME, 'In' AS ExcStatus,
				trim(UserTbl.UserLast) AS Last, trim(UserTbl.UserFirst) AS First, CircleMemberTbl.CircleMemID
				FROM CircleMemberTbl
				left join CircleTbl
				on CircleMemberTbl.CircleID = CircleTbl.CircleID
				join UserTbl
				on CircleMemberTbl.MemberID = UserTbl.UserID
				where CircleTbl.CircleID = p_circleID
				AND CircleMemberTbl.MemberID not in
				(select ExcludeUser from ExcludeTbl
				where storyID = p_storyID)
				and (CircleMemberTbl.status = 'Accepted')

				UNION

				Select CircleMemberTbl.MemberID, trim(UserTbl.UserDisplayName) AS USERDISPLAYNAME, 'Out' AS ExcStatus,
				trim(UserTbl.UserLast) AS Last, trim(UserTbl.UserFirst) AS First, CircleMemberTbl.CircleMemID
				FROM CircleMemberTbl
				left join CircleTbl
				on CircleMemberTbl.CircleID = CircleTbl.CircleID
				join UserTbl
				on CircleMemberTbl.MemberID = UserTbl.UserID
				where CircleTbl.CircleID = p_circleID
				AND CircleMemberTbl.MemberID in
				(select ExcludeUser from ExcludeTbl
				where storyID = p_storyID)
				and (CircleMemberTbl.status = 'Accepted')

				order by Last, First;
    END;
END;
$function$
;

-- getcirclepicklist
DROP FUNCTION IF EXISTS getcirclepicklist;
CREATE OR REPLACE FUNCTION public.getcirclepicklist(p_userid integer DEFAULT NULL::integer, p_circleid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CircleMemID" integer,
    "CircleID" integer,
    "MemberID" integer,
    inorout text,
    "UserDisplayName" varchar(110),
    "CircleStatus" text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primaryCircle int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primaryCircle FROM CircleTbl where CirclePrimary = p_userID;
    RETURN QUERY SELECT CircleMemID, CircleID, MemberID, CAST (1 as bit) AS inorout, UserTbl.UserDisplayName, CASE WHEN AcceptedDate is null THEN 0 ELSE 1 END as CircleStatus from CircleMemberTbl 
left join UserTbl
on MemberID = UserTbl.UserID

where 
circleID = v_primaryCircle and MemberID in 
	(Select MemberID from CircleMemberTbl
	where circleID = p_circleID)
	UNION

	SELECT CircleMemID, CircleID, MemberID, CAST (0 as bit) AS inorout, UserTbl.UserDisplayName, CASE WHEN AcceptedDate is null THEN 0 ELSE 1 END as CircleStatus from CircleMemberTbl 
	left join UserTbl
on MemberID = UserTbl.UserID
	where 
circleID = v_primaryCircle and MemberID not in 
	(Select MemberID from CircleMemberTbl
	where circleID = p_circleID);
    END;
END;
$function$
;

-- getcomments
DROP FUNCTION IF EXISTS getcomments;
CREATE OR REPLACE FUNCTION public.getcomments(p_storyid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CommentID" integer,
    "StoryID" integer,
    "UserID" integer,
    "CommentText" character varying,
    "CommentDate" text,
    "UserDisplayName" varchar(110),
    "UserMediaLoc" varchar(100))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT CommentID
      , StoryID
      , CommentTbl.UserID
      , CommentText
	  , to_char(CommentTbl.CommentDate, 'Mon DD, YYYY') as CommentDate
	  , UserDisplayName
	  , UserMediaLoc
    FROM CommentTbl
        left join UserTbl
        on CommentTbl.UserID = UserTbl.UserID

    where CommentTbl.StoryID = p_storyID

    ORDER BY CommentTbl.CommentDate DESC;
    END;
END;
$function$
;

-- getcommenttotal
DROP FUNCTION IF EXISTS getcommenttotal;
CREATE OR REPLACE FUNCTION public.getcommenttotal(p_storyid integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_commentTotal int;
BEGIN
    BEGIN
    SELECT count(CommentTbl.StoryID) INTO v_commentTotal FROM CommentTbl
	where StoryID = p_storyID;
    return v_commentTotal;
    END;
END;
$function$
;

-- getconnectioncollections
DROP FUNCTION IF EXISTS getconnectioncollections;
CREATE OR REPLACE FUNCTION public.getconnectioncollections(p_userid integer DEFAULT NULL::integer, p_connection integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    SELECT COUNT(*) INTO v_recordCount FROM BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID
			
			where 

			(BookOwner = p_connection AND 
			
			(BookTbl.TimeCapsuleDate IS NULL 
			OR BookTbl.TimeCapsuleDate <= CURRENT_TIMESTAMP )
			
			)

			AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					);
    RETURN QUERY Select distinct v_recordCount, BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner, BookTbl.TimeCapsuleDate, to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date, BookTbl.CircleID, UserTbl.UserDisplayName, v_primary AS primaryCircle from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 

			(BookOwner = p_connection AND 
			
			(BookTbl.TimeCapsuleDate IS NULL 
			OR BookTbl.TimeCapsuleDate <= CURRENT_TIMESTAMP )
			
			)

			AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					)
				
            order by  BookTitle
								OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getconnectionstories
DROP FUNCTION IF EXISTS getconnectionstories;
CREATE OR REPLACE FUNCTION public.getconnectionstories(p_userid integer DEFAULT NULL::integer, p_connectionid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "Hidden" integer,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_journalCircle int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleTbl.CircleID INTO v_journalCircle FROM CircleTbl 
		where JournalID = p_userID;
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
               where 
			   StoryTbl.StoryTypeID <> 5 
			   AND 
			   StoryTbl.Hidden <> 1 
			   AND
			   StoryTbl.CircleID <> v_journalCircle 

			   AND
			   (
                    (CircleID in
                    (select CircleID from CircleMemberTbl
                    where MemberID = p_userID AND AcceptedDate IS NOT NULL) )

                    AND ( StoryTbl.StoryID not in
                    (Select StoryID from ExcludeTbl
                    where ExcludeUser = p_userID)
                    )
					

                    OR (storyTbl.UserID = p_userID 
					)
					);
    IF v_recordCount = 0 THEN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StarterTbl;
    RETURN QUERY SELECT v_recordCount, StarterTbl.StoryID, trim(StarterTbl.StoryTitle) as StoryTitle, trim(StarterTbl.StoryText) as StoryText, StarterTbl.Userid, StarterTbl.StoryTypeID, to_char(Startertbl.StoryDate, 'Mon DD, YYYY') as date, trim(StarterTbl.Interviewee) as Interviewee, StarterTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StarterTbl.StoryIngredients, StarterTbl.Hidden, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StarterTbl
               left join UserTbl
               on StarterTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StarterTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
			  
			   order by StoryDate Desc, StoryTitle LIMIT 1;
    END;
    ELSE
    BEGIN
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, StoryTbl.Hidden, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 
			   StoryTbl.StoryTypeID <> 5
			   AND
			   StoryTbl.Hidden <> 1 
			   AND
			   StoryTbl.CircleID <> v_journalCircle 
			   AND
			   StoryTbl.CircleID <> 0
			   AND 
			   StoryTbl.CircleID IS NOT NULL

			   AND 
			   (
                    (CircleID in
                    (select CircleID from CircleMemberTbl
                    where MemberID = p_userID AND AcceptedDate IS NOT NULL) )

                    AND ( StoryTbl.StoryID not in
                    (Select StoryID from ExcludeTbl
                    where ExcludeUser = p_userID)
                    )
					

                    AND (storyTbl.UserID = p_connectionID 
					)
					)

                    order by StoryDate Desc, StoryTitle 
					OFFSET p_recordStart LIMIT p_pageLength;
    END; END IF;
    END;
END;
$function$
;

-- getdrafts
DROP FUNCTION IF EXISTS getdrafts;
CREATE OR REPLACE FUNCTION public.getdrafts(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "Hidden" integer,
    "StoryIngredients" text,
    "UserMediaLoc" text,
    "MediaLoc" character varying,
    "MediaType" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               where StoryTbl.UserID = p_userID AND (StoryTbl.CircleID IS NULL OR StoryTbl.CircleID = 0);
    RETURN QUERY Select v_recordCount, StoryID, trim(StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.Hidden, StoryTbl.StoryIngredients, UserTbl.UserMediaLoc, (SELECT MediaLoc from MediaTbl 
				WHERE MediaTbl.StoryID = StoryTbl.StoryID  order by MediaTbl.FeatureMedia LIMIT 1) AS MediaLoc, (SELECT MediaType from MediaTbl 
				WHERE MediaTbl.StoryID = StoryTbl.StoryID  LIMIT 1) AS MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID


               where (StoryTbl.UserID = p_userID) and (StoryTbl.CircleID IS NULL OR StoryTbl.CircleID = 0)
               order by StoryDate Desc, StoryTitle 
					OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- geteveryonemembers
DROP FUNCTION IF EXISTS geteveryonemembers;
CREATE OR REPLACE FUNCTION public.geteveryonemembers(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CircleMemID" integer,
    "CircleID" integer,
    "MemberID" integer,
    "AcceptedDate" timestamp without time zone,
    "RejectedDate" timestamp without time zone,
    "Status" varchar(50),
    "UserDisplayName" varchar(110),
    "CirclePrimary" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT CircleMemberTbl.CircleMemID, CircleMemberTbl.CircleID, CircleMemberTbl.MemberID, CircleMemberTbl.AcceptedDate, CircleMemberTbl.RejectedDate, CircleMemberTbl.Status, UserTbl.UserDisplayName, CircleTbl.CirclePrimary 
	from CircleMemberTbl 
	join UserTbl 
	on CircleMemberTbl.MemberID = UserTbl.UserID 
	join CircleTbl
	on CircleMemberTbl.CircleID = CircleTbl.CircleID
	where 
	CircleTbl.CirclePrimary = p_userID

	order by UserDisplayName;
    END;
END;
$function$
;

-- geteveryonememberstoadd
DROP FUNCTION IF EXISTS geteveryonememberstoadd;
CREATE OR REPLACE FUNCTION public.geteveryonememberstoadd(p_userid integer DEFAULT NULL::integer, p_circleid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CircleMemID" integer,
    "CircleID" integer,
    "MemberID" integer,
    "AcceptedDate" timestamp without time zone,
    "RejectedDate" timestamp without time zone,
    "Status" varchar(50),
    "UserDisplayName" varchar(110))
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleTbl.CircleID INTO v_primary FROM CircleTbl where CircleTbl.CirclePrimary = 29;
    RETURN QUERY SELECT CircleMemberTbl.CircleMemID, CircleMemberTbl.CircleID, CircleMemberTbl.MemberID, CircleMemberTbl.AcceptedDate, CircleMemberTbl.RejectedDate, CircleMemberTbl.Status, UserTbl.UserDisplayName
	from CircleMemberTbl 
	join UserTbl 
	on CircleMemberTbl.MemberID = UserTbl.UserID 
	where CircleMemberTbl.CircleID = v_primary
		and 
	CircleMemberTbl.MemberID NOT in 
	(select CircleMemberTbl.MemberID from CircleMemberTbl where CircleMemberTbl.CircleID = p_circleID)

	order by UserDisplayName;
    END;
END;
$function$
;

-- getexport
DROP FUNCTION IF EXISTS getexport;
CREATE OR REPLACE FUNCTION public.getexport(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "StoryID" integer,
    "StoryTitle" text,
    "StoryTypeID" integer,
    "StoryText" text,
    "Userid" integer,
    "date" text,
    "Interviewee" text,
    "UserDisplayName" text,
    "StoryIngredients" character varying)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select StoryID, trim(StoryTitle) as StoryTitle,
			   StoryTbl.StoryTypeID,
               trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid,
               to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date,
               trim(StoryTbl.Interviewee) as Interviewee, 
               trim(UserTbl.UserDisplayName) as UserDisplayName,
               StoryTbl.StoryIngredients

               from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID


               where StoryTbl.UserID = p_userID 

               order by StoryDate Desc, StoryTitle;
    UPDATE UserTbl 
			   SET LastExport = CURRENT_TIMESTAMP
			   WHERE UserID = p_userID;
    END;
END;
$function$
;

-- getexportdate
DROP FUNCTION IF EXISTS getexportdate;
CREATE OR REPLACE FUNCTION public.getexportdate(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "LastExport" timestamp)) as lastexport,
    "ExportStarted" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_UserTZ varchar(75);
BEGIN
    BEGIN
    SELECT UserTZ INTO v_UserTZ FROM UserTbl where UserID = p_userID;
    RETURN QUERY Select (SWITCHOFFSET(LastExport, DATEPART(TZOFFSET, LastExport AT TIME ZONE v_UserTZ)::timestamp)) AS LastExport, ExportStarted
		from UserTbl
		WHERE UserID = p_userID;
    END;
END;
$function$
;

-- getexportmedia
DROP FUNCTION IF EXISTS getexportmedia;
CREATE OR REPLACE FUNCTION public.getexportmedia(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("MediaLoc" character varying)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT MediaLoc from MediaTbl 
		where userID = p_userID 
		order by FeatureMedia, MediaID;
    END;
END;
$function$
;

-- getgroupmemories
DROP FUNCTION IF EXISTS getgroupmemories;
CREATE OR REPLACE FUNCTION public.getgroupmemories(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer, p_circleid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "Hidden" integer,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_journalCircle int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleTbl.CircleID INTO v_journalCircle FROM CircleTbl 
		where JournalID = p_userID;
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
               where 
			   StoryTbl.Hidden <> 1 
			   AND
			   StoryTbl.CircleID = p_circleID;
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, StoryTbl.Hidden, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 

			   StoryTbl.Hidden <> 1 
			   AND
			   StoryTbl.CircleID = p_circleID


                    order by StoryDate Desc, StoryTitle 
					OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getheartcount
DROP FUNCTION IF EXISTS getheartcount;
CREATE OR REPLACE FUNCTION public.getheartcount(p_storyid integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_storyCount int;
BEGIN
    BEGIN
    SELECT count(HeartTbl.StoryID) INTO v_storyCount FROM HeartTbl
	where StoryID = p_storyID;
    return v_storyCount;
    END;
END;
$function$
;

-- getheartlist
DROP FUNCTION IF EXISTS getheartlist;
CREATE OR REPLACE FUNCTION public.getheartlist(p_storyid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "heartTotal" integer,
    "HeartID" integer,
    "UserID" varchar(110),
    "UserDisplayName" varchar(100),
    "UserMediaLoc" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_heartTotal INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_heartTotal FROM HeartTbl
	where HeartTbl.StoryID = p_storyID;
    RETURN QUERY Select HeartTbl.HeartID, HeartTbl.UserID, UserDisplayName, UserMediaLoc
	from HeartTbl
		left join UserTbl
		on HeartTbl.UserID = UserTbl.UserID
	where StoryID = p_storyID
	order by HeartTbl.HeartID desc;
    END;
END;
$function$
;

-- getheartstatus
DROP FUNCTION IF EXISTS getheartstatus;
CREATE OR REPLACE FUNCTION public.getheartstatus(p_storyid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("heartTotal" integer, "HeartCount" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_heartTotal INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_heartTotal FROM HeartTbl
         where HeartTbl.StoryID = p_storyID;
    RETURN QUERY Select count(HeartID) AS HeartCount 
		from HeartTbl
		where StoryID = p_storyID and UserID = p_userID;
    END;
END;
$function$
;

-- gethelptypes
DROP FUNCTION IF EXISTS gethelptypes;
CREATE OR REPLACE FUNCTION public.gethelptypes()
 RETURNS TABLE("HelpTypeID" integer, "HelpType" character varying)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select HelpTypeID AS "HelpTypeID", HelpType::varchar::varchar AS "HelpType" from HelpTypeTbl 



		order by HelpType;
    END;
END;
$function$
;

-- getinterviewcategories
DROP FUNCTION IF EXISTS getinterviewcategories;
CREATE OR REPLACE FUNCTION public.getinterviewcategories()
 RETURNS TABLE("InterviewCatID" integer, "IntCategory" character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY SELECT 
        InterviewCatID::int AS "InterviewCatID", 
        IntCategory::varchar AS "IntCategory"
    FROM InterviewCatTbl
    ORDER BY IntCategory;
END;
$function$
;

-- getinterviewquestions
DROP FUNCTION IF EXISTS getinterviewquestions;
CREATE OR REPLACE FUNCTION public.getinterviewquestions()
 RETURNS TABLE("InterviewID" integer, "IntQuestion" character varying, "CategoryID" integer, "IntCategory" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT InterviewID, IntQuestion, InterviewTbl.IntCategory as CategoryID, InterviewCatTbl.IntCategory
  from InterviewTbl
    left join InterviewCatTbl on InterviewTbl.IntCategory = InterviewCatTbl.InterviewCatID
  order by InterviewCatTbl.IntCategory, InterviewTbl.IntQuestion;
    END;
END;
$function$
;

-- getinterviews
DROP FUNCTION IF EXISTS getinterviews;
CREATE OR REPLACE FUNCTION public.getinterviews()
 RETURNS TABLE(
    "InterviewID" integer,
    "IntQuestion" text,
    "IntCategory" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select InterviewID AS "InterviewID", trim(IntQuestion)::varchar::varchar AS "IntQuestion", IntCategory AS "IntCategory" from InterviewTbl
            order by
            IntCategory, IntQuestion;
    END;
END;
$function$
;

-- getinvitation
DROP FUNCTION IF EXISTS getinvitation;
CREATE OR REPLACE FUNCTION public.getinvitation(p_invitationid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "InvitationID" integer,
    "InviteEmail" varchar(150),
    "InvitationType" integer,
    "Inviter" varchar(110))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT InvitationID, InviteEmail, InvitationType, UserDisplayName AS Inviter from InvitationTbl 
	join UserTbl 
	on InvitedBy = UserTbl.UserID
	where InvitationID = p_invitationID;
    END;
END;
$function$
;

-- getinvitationcount
DROP FUNCTION IF EXISTS getinvitationcount;
CREATE OR REPLACE FUNCTION public.getinvitationcount(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("Invitations" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select count(InvitationID) as Invitations
    from InvitationTbl
        Left Join UserTbl
        on InvitationTbl.Invitedby = UserTbl.UserID
    where
				InvitedBy = p_userID
        AND
        InvitationType > 1
        AND
        (
                (InviteDate > (CURRENT_TIMESTAMP + INTERVAL '-7 day') AND ConvertDate IS NULL AND DoNotContactDate IS NULL)

        OR

        (ConvertDate is not null)
				);
    END;
END;
$function$
;

-- getlistdetails
DROP FUNCTION IF EXISTS getlistdetails;
CREATE OR REPLACE FUNCTION public.getlistdetails(p_listid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "ListID" integer,
    "ListName" text,
    "ListDate" integer,
    "ListCircle" integer,
    "ListCircleRights" character varying,
    "ListOwner" integer,
    "ListColor" character varying,
    "ListAssigned" integer,
    "ListType" text,
    "UserDisplayName" varchar(110),
    "UserMediaLoc" varchar(100))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select distinct ListTbl.ListID, trim(ListTbl.ListName) AS ListName,
			ListTbl.ListDate,
			ListTbl.ListCircle,
			ListTbl.ListCircleRights,
			ListTbl.ListOwner,
			ListTbl.ListColor,
			ListTbl.ListAssigned,
			TRIM(ListTbl.ListType) AS ListType,
            UserTbl.UserDisplayName,
			UserTbl.UserMediaLoc
            from ListTbl
			left join UserTbl 
			on ListTbl.ListOwner = UserTbl.UserID
		where ListTbl.ListID = p_listID;
    END;
END;
$function$
;

-- getlistitems
DROP FUNCTION IF EXISTS getlistitems;
CREATE OR REPLACE FUNCTION public.getlistitems(p_listid integer DEFAULT NULL::integer, p_sort character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "ListItemID" integer,
    "Item" text,
    "ItemStatus" integer,
    "DueDate" text,
    "AssignedTo" text,
    "Notes" text,
    "ListID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select distinct ListItemTbl.ListItemID, trim(ListItemTbl.Item) AS Item,
			ListItemTbl.ItemStatus,
			to_char(ListItemTbl.DueDate, 'Mon DD, YYYY') as DueDate,
			trim(ListItemTbl.AssignedTo) AS AssignedTo,
			trim(ListItemTbl.Notes) AS Notes,
			ListItemTbl.ListID
            from ListItemTbl
		where ListItemTbl.ListID = p_listID
		ORDER BY ListItemID;
    END;
END;
$function$
;

-- getlistitemsbyduedate
DROP FUNCTION IF EXISTS getlistitemsbyduedate;
CREATE OR REPLACE FUNCTION public.getlistitemsbyduedate(p_listid integer DEFAULT NULL::integer, p_sort character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "ListItemID" integer,
    "Item" text,
    "ItemStatus" integer,
    "DueDate" text,
    "AssignedTo" text,
    "Notes" text,
    "ListID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select distinct ListItemTbl.ListItemID, trim(ListItemTbl.Item) AS Item,
			ListItemTbl.ItemStatus,
			to_char(ListItemTbl.DueDate, 'Mon DD, YYYY') as DueDate,
			trim(ListItemTbl.AssignedTo) AS AssignedTo,
			trim(ListItemTbl.Notes) AS Notes,
			ListItemTbl.ListID
            from ListItemTbl
		where ListItemTbl.ListID = p_listID
		ORDER BY DueDate;
    END;
END;
$function$
;

-- getlistitemsbyitemid
DROP FUNCTION IF EXISTS getlistitemsbyitemid;
CREATE OR REPLACE FUNCTION public.getlistitemsbyitemid(p_listid integer DEFAULT NULL::integer, p_sort character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "ListItemID" integer,
    "Item" text,
    "ItemStatus" integer,
    "DueDate" text,
    "AssignedTo" text,
    "Notes" text,
    "ListID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select distinct ListItemTbl.ListItemID, trim(ListItemTbl.Item) AS Item,
			ListItemTbl.ItemStatus,
			to_char(ListItemTbl.DueDate, 'Mon DD, YYYY') as DueDate,
			trim(ListItemTbl.AssignedTo) AS AssignedTo,
			trim(ListItemTbl.Notes) AS Notes,
			ListItemTbl.ListID
            from ListItemTbl
		where ListItemTbl.ListID = p_listID
		ORDER BY ListItemID;
    END;
END;
$function$
;

-- getlistitemsbyitems
DROP FUNCTION IF EXISTS getlistitemsbyitems;
CREATE OR REPLACE FUNCTION public.getlistitemsbyitems(p_listid integer DEFAULT NULL::integer, p_sort character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "ListItemID" integer,
    "Item" text,
    "ItemStatus" integer,
    "DueDate" text,
    "AssignedTo" text,
    "Notes" text,
    "ListID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select distinct ListItemTbl.ListItemID, trim(ListItemTbl.Item) AS Item,
			ListItemTbl.ItemStatus,
			to_char(ListItemTbl.DueDate, 'Mon DD, YYYY') as DueDate,
			trim(ListItemTbl.AssignedTo) AS AssignedTo,
			trim(ListItemTbl.Notes) AS Notes,
			ListItemTbl.ListID
            from ListItemTbl
		where ListItemTbl.ListID = p_listID
		ORDER BY Item;
    END;
END;
$function$
;

-- getlistitemsbystatus
DROP FUNCTION IF EXISTS getlistitemsbystatus;
CREATE OR REPLACE FUNCTION public.getlistitemsbystatus(p_listid integer DEFAULT NULL::integer, p_sort character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "ListItemID" integer,
    "Item" text,
    "ItemStatus" integer,
    "DueDate" text,
    "AssignedTo" text,
    "Notes" text,
    "ListID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select distinct ListItemTbl.ListItemID, trim(ListItemTbl.Item) AS Item,
			ListItemTbl.ItemStatus,
			to_char(ListItemTbl.DueDate, 'Mon DD, YYYY') as DueDate,
			trim(ListItemTbl.AssignedTo) AS AssignedTo,
			trim(ListItemTbl.Notes) AS Notes,
			ListItemTbl.ListID
            from ListItemTbl
		where ListItemTbl.ListID = p_listID
		ORDER BY ItemStatus;
    END;
END;
$function$
;

-- getlists
DROP FUNCTION IF EXISTS getlists;
CREATE OR REPLACE FUNCTION public.getlists(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "ListID" integer,
    "ListName" text,
    "ListOwner" integer,
    "ListColor" character varying,
    "ListDate" integer,
    "ListCircleRights" character varying,
    "ListAssigned" integer,
    "ListCircle" integer,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    RETURN QUERY Select distinct ListTbl.ListID, trim(ListTbl.ListName) AS ListName, ListTbl.ListOwner,
			ListTbl.ListColor, ListTbl.ListDate, ListTbl.ListCircleRights,
			ListTbl.ListAssigned,
            ListTbl.ListCircle,
            UserTbl.UserDisplayName, v_primary AS primaryCircle
            from ListTbl
			left join UserTbl 
			on ListTbl.ListOwner = UserTbl.UserID

            where 
			ListType = 'LIST'

			AND

			(ListOwner = p_userID

			OR (

				(ListCircle in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)
					)
				))
				
            order by  ListName;
    END;
END;
$function$
;

-- getlisttemplate
DROP FUNCTION IF EXISTS getlisttemplate;
CREATE OR REPLACE FUNCTION public.getlisttemplate(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "ListID" integer,
    "ListName" text,
    "ListOwner" integer,
    "ListColor" character varying,
    "ListDate" integer,
    "ListCircleRights" character varying,
    "ListAssigned" integer,
    "ListCircle" integer,
    "ListType" text,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    RETURN QUERY Select distinct ListTbl.ListID, trim(ListTbl.ListName) AS ListName, ListTbl.ListOwner,
			ListTbl.ListColor, ListTbl.ListDate, ListTbl.ListCircleRights,
			ListTbl.ListAssigned,
            ListTbl.ListCircle,
			TRIM(ListTbl.ListType) AS ListType,
            UserTbl.UserDisplayName, v_primary AS primaryCircle
            from ListTbl
			left join UserTbl 
			on ListTbl.ListOwner = UserTbl.UserID

            where 


		(	ListOwner = p_userID

			OR (

				(ListCircle in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)
					)
				))

				AND ListType = 'TEMPLATE'
				
            order by  ListName;
    END;
END;
$function$
;

-- getmedia
DROP FUNCTION IF EXISTS getmedia;
CREATE OR REPLACE FUNCTION public.getmedia(p_storyid integer DEFAULT NULL::integer)
 RETURNS TABLE("MediaID" integer, "MediaLoc" character varying, "MediaType" character varying, "FeatureMedia" integer, "storyID" integer, "UserID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT MediaID, MediaLoc, MediaType, FeatureMedia, storyID, UserID from MediaTbl 
		where storyID = p_storyID 
		order by FeatureMedia, MediaID;
    END;
END;
$function$
;

-- getmessage
DROP FUNCTION IF EXISTS getmessage;
CREATE OR REPLACE FUNCTION public.getmessage(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("MessageID" integer, "MessageText" text, "MessageHeader" text, "MessageStart" date, "MessageEnd" date, "MessageUser" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT MessageID
      ,MessageText
	  ,MessageHeader
      ,MessageStart
      ,MessageEnd
      ,MessageUser
	
  FROM MessageTbl



  where  
 ( MessageStart <= CAST( CURRENT_TIMESTAMP AS Date )  AND MessageEnd >= CAST( CURRENT_TIMESTAMP AS Date ) )
 AND
 MessageID NOT IN (SELECT CancelMessage from MessageCancelTbl WHERE CancelUserID = p_userID);
    END;
END;
$function$
;

-- getmyanswer
DROP FUNCTION IF EXISTS getmyanswer;
CREATE OR REPLACE FUNCTION public.getmyanswer(p_userid integer DEFAULT NULL::integer, p_viewpointid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "VPAnswer" text,
    "VPAnswerID" integer,
    "UserID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select trim(VPAnswer) as VPAnswer, VPAnswerID, UserID
		from VPAnswerTbl
		where ViewPointID = p_viewPointID
		AND UserID = p_userID;
    END;
END;
$function$
;

-- getmybooklist
DROP FUNCTION IF EXISTS getmybooklist;
CREATE OR REPLACE FUNCTION public.getmybooklist(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    RETURN QUERY Select distinct BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
            BookTbl.TimeCapsuleDate, 
            to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
            BookTbl.CircleID,
            UserTbl.UserDisplayName, v_primary AS primaryCircle
            from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 
			BookOwner = p_userID


            order by BookTitle;
    END;
END;
$function$
;

-- getmybooks
DROP FUNCTION IF EXISTS getmybooks;
CREATE OR REPLACE FUNCTION public.getmybooks(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "BookStatus" text,
    "BookDate" text,
    "CircleID" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
            BookTbl.TimeCapsuleDate, CAST(0 AS BIT) as BookStatus, 
            to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as BookDate,
            BookTbl.CircleID
			from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

			WHERE
			        (BookTbl.CircleID in
                    (select CircleID from CircleMemberTbl
                    where MemberID = p_userID) )

                    AND ( BookTbl.BookID not in
                    (Select BookID from BkExcludeTbl
                    where ExcludeUser = p_userID)
                    )

                    
                    OR BookTbl.BookOwner = p_userID 


			UNION


			Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
            BookTbl.TimeCapsuleDate, CAST(1 as BIT) as BookStatus, 
            to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as BookDate,
            BookTbl.CircleID
			from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID
			WHERE
			        (BookTbl.CircleID in
                    (select CircleID from CircleMemberTbl
                    where MemberID = p_userID) )

                    AND ( BookTbl.BookID not in
                    (Select BookID from BkExcludeTbl
                    where ExcludeUser = p_userID)
                    )

                    
                    OR BookTbl.BookOwner = p_userID 

			Order By BookDate, BookTitle;
    END;
END;
$function$
;

-- getmycircles
DROP FUNCTION IF EXISTS getmycircles;
CREATE OR REPLACE FUNCTION public.getmycircles(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CircleID" integer,
    "CircleName" text,
    "ButtonName" text,
    "CirclePrimary" integer,
    "SELECTED" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CircleTbl.CircleID, trim(CircleTbl.CircleName) AS CircleName, SUBSTRING(CircleName, 1, 15) AS ButtonName, CircleTbl.CirclePrimary,
			CASE WHEN CircleTbl.CirclePrimary IS NOT NULL
               THEN 'true'
               ELSE 'false'
          END AS SELECTED
			from CircleTbl
            where CircleTbl.CircleOwner = p_userID 
            order by SELECTED DESC, CircleTbl.CircleName;
    END;
END;
$function$
;

-- getmyjournal
DROP FUNCTION IF EXISTS getmyjournal;
CREATE OR REPLACE FUNCTION public.getmyjournal(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "Hidden" integer,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_journalCircle int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               where StoryTbl.UserID = p_userID;
    SELECT CircleTbl.CircleID INTO v_journalCircle FROM CircleTbl 
		where JournalID = p_userID;
    RETURN QUERY Select v_recordCount, StoryID, trim(StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.Hidden, StoryTbl.StoryIngredients, UserTbl.UserMediaLoc, (SELECT MediaLoc from MediaTbl 
				WHERE MediaTbl.StoryID = StoryTbl.StoryID  order by MediaTbl.FeatureMedia LIMIT 1) AS MediaLoc, (SELECT MediaType from MediaTbl 
				WHERE MediaTbl.StoryID = StoryTbl.StoryID  LIMIT 1) AS MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID


               where StoryTbl.UserID = p_userID and StoryTbl.CircleID = v_journalCircle
               order by StoryDate Desc, StoryTitle 
					OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getmyqcanswer
DROP FUNCTION IF EXISTS getmyqcanswer;
CREATE OR REPLACE FUNCTION public.getmyqcanswer(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "VPAnswer" text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_vpID int;
BEGIN
    BEGIN
    SELECT ViewPointID INTO v_vpID FROM ViewPointTbl 
	where ViewPointDate = to_char(CURRENT_TIMESTAMP, 'MM/DD/YYYY');
    RETURN QUERY Select trim(VPAnswer) as VPAnswer
		from VPAnswerTbl
		where ViewPointID = v_vpID
		AND UserID = p_userID;
    END;
END;
$function$
;

-- getmyqcanswertoday
DROP FUNCTION IF EXISTS getmyqcanswertoday;
CREATE OR REPLACE FUNCTION public.getmyqcanswertoday(p_userid integer DEFAULT NULL::integer, p_today date DEFAULT NULL::date)
 RETURNS TABLE(
    "VPAnswer" text,
    "VPAnswerID" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_vpID int;
BEGIN
    BEGIN
    SELECT ViewPointID INTO v_vpID FROM ViewPointTbl 
	where ViewPointDate = p_today;
    RETURN QUERY Select trim(VPAnswer) as VPAnswer, VPAnswerID
		from VPAnswerTbl
		where ViewPointID = v_vpID
		AND UserID = p_userID;
    END;
END;
$function$
;

-- getmystories
DROP FUNCTION IF EXISTS getmystories;
CREATE OR REPLACE FUNCTION public.getmystories(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "Hidden" integer,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_journalCircle int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleTbl.CircleID INTO v_journalCircle FROM CircleTbl 
		where JournalID = p_userID;
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID


               where StoryTbl.UserID = p_userID;
    RETURN QUERY Select v_recordCount, StoryID, trim(StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.Hidden, StoryTbl.StoryIngredients, UserTbl.UserMediaLoc, (SELECT MediaLoc from MediaTbl 
				WHERE MediaTbl.StoryID = StoryTbl.StoryID  order by MediaTbl.FeatureMedia LIMIT 1) AS MediaLoc, (SELECT MediaType from MediaTbl 
				WHERE MediaTbl.StoryID = StoryTbl.StoryID  LIMIT 1) AS MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID


               where StoryTbl.UserID = p_userID AND CircleID IS NOT NULL AND CircleID <> v_journalCircle

               order by StoryDate Desc, StoryTitle 
					OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getnewmedia
DROP FUNCTION IF EXISTS getnewmedia;
CREATE OR REPLACE FUNCTION public.getnewmedia(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("StoryID" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_draftStoryID INT;
BEGIN
    BEGIN
    SELECT StoryID INTO v_draftStoryID FROM StoryTbl
        WHERE UserID = p_userID
            AND Hidden IS NULL
        ORDER BY StoryDate DESC, StoryID DESC LIMIT 1;
    IF v_draftStoryID IS NULL THEN
    BEGIN
    RETURN QUERY SELECT MediaID, MediaLoc, MediaType, FeatureMedia, StoryID
                FROM MediaTbl
                WHERE StoryID = 0
                    AND UserID = p_userID
                ORDER BY FeatureMedia, MediaDate;
    RETURN;
    END; END IF;
    RETURN QUERY SELECT MediaID, MediaLoc, MediaType, FeatureMedia, StoryID
        FROM MediaTbl
        WHERE StoryID = v_draftStoryID
            AND UserID = p_userID
        ORDER BY FeatureMedia, MediaDate;
    END;
END;
$function$
;

-- getnews
DROP FUNCTION IF EXISTS getnews;
CREATE OR REPLACE FUNCTION public.getnews(p_userid integer DEFAULT NULL::integer, p_subtype integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "NewsID" integer,
    "NewsTitle" character varying,
    "NewsText" character varying,
    "DispNewsDate" text,
    "NewsSubType" integer,
    "Status" character varying,
    "NewsDate" date)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT
		news.NewsID,
		news.NewsTitle,
		news.NewsText,
		to_char(news.NewsDate, 'Mon DD, YYYY') AS DispNewsDate,
		news.NewsSubType,
		CASE
						WHEN EXISTS (
								SELECT 1
		FROM NewsCancelTbl cancel
		WHERE cancel.CancelUserID = p_userID
			AND cancel.NewsID = news.NewsID
						) THEN 'read'
						ELSE 'unread'
				END AS Status,
		news.NewsDate
	FROM NewsTbl news
	WHERE (news.NewsSubType = 0 OR news.NewsSubType = p_subType)
		AND news.NewsDate >= (CURRENT_TIMESTAMP + INTERVAL '-90 day')
	ORDER BY news.NewsDate DESC, news.NewsID DESC;
    END;
END;
$function$
;

-- getnewstorymedia
DROP FUNCTION IF EXISTS getnewstorymedia;
CREATE OR REPLACE FUNCTION public.getnewstorymedia(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("MediaID" integer, "MediaLoc" character varying, "MediaType" character varying, "FeatureMedia" integer, "UserID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select MediaID, MediaLoc, MediaType, FeatureMedia, UserID from MediaTbl
            where StoryID = 0 AND userID = p_userID
            order by FeatureMedia, MediaID;
    END;
END;
$function$
;

-- getnewsunread
DROP FUNCTION IF EXISTS getnewsunread;
CREATE OR REPLACE FUNCTION public.getnewsunread(p_userid integer DEFAULT NULL::integer, p_subtype integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "NewsID" integer,
    "NewsTitle" character varying,
    "NewsText" character varying,
    "NewsDate" text,
    "NewsSubType" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT
		news.NewsID,
		news.NewsTitle,
		news.NewsText,
		to_char(news.NewsDate, 'Mon DD, YYYY') AS NewsDate,
		news.NewsSubType
	FROM NewsTbl news
	WHERE (news.NewsSubType = 0 OR news.NewsSubType = p_subType)
		AND news.NewsDate >= (CURRENT_TIMESTAMP + INTERVAL '-90 day')
		AND NOT EXISTS (
					SELECT 1
		FROM NewsCancelTbl cancel
		WHERE cancel.CancelUserID = p_userID
			AND cancel.NewsID = news.NewsID
			)
	ORDER BY news.NewsDate DESC, news.NewsID DESC;
    END;
END;
$function$
;

-- getopenadmininvitations
DROP FUNCTION IF EXISTS getopenadmininvitations;
CREATE OR REPLACE FUNCTION public.getopenadmininvitations()
 RETURNS TABLE(
    "InvitationID" integer,
    "InviteEmail" varchar(150),
    "InviteDate" text,
    "InvitationType" integer,
    "Inviter" varchar(110),
    "SubType" varchar(50))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT InvitationID, InviteEmail, to_char(InviteDate, 'YYYY-MM-DD') as InviteDate,
		InvitationType, UserDisplayName AS Inviter, UserSubTypeTbl.UserSubType AS SubType
	from InvitationTbl
		join UserTbl
		on InvitedBy = UserTbl.UserID
		join UserSubTypeTbl
		on InvitationType = UserSubTypeID
	where AdminInvite = 1 AND ConvertDate IS NULL AND DoNotContactDate IS NULL;
    END;
END;
$function$
;

-- getprimarycircle
DROP FUNCTION IF EXISTS getprimarycircle;
CREATE OR REPLACE FUNCTION public.getprimarycircle(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("CircleID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CircleID from CircleTbl where CirclePrimary = p_userID;
    END;
END;
$function$
;

-- getprofilebooklist
DROP FUNCTION IF EXISTS getprofilebooklist;
CREATE OR REPLACE FUNCTION public.getprofilebooklist(p_userid integer DEFAULT NULL::integer, p_profileid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select distinct BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
            BookTbl.TimeCapsuleDate, 
            to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
            BookTbl.CircleID,
            UserTbl.UserDisplayName
            from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 
			BookOwner = p_profileID

			AND (

				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					)
				)

            order by date Desc, BookTitle;
    END;
END;
$function$
;

-- getprofilememories
DROP FUNCTION IF EXISTS getprofilememories;
CREATE OR REPLACE FUNCTION public.getprofilememories(p_userid integer DEFAULT NULL::integer, p_profileid integer DEFAULT NULL::integer, p_contenttype integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 
			   		StoryTbl.StoryTypeID = p_contentType
				AND
				StoryTbl.UserID = p_profileID

				AND
            (
            StoryTbl.UserID = p_userID 
			OR
            StoryTbl.CircleID in
            (select CircleID from CircleMemberTbl
            where MemberID = p_userID) );
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 
			   		StoryTbl.StoryTypeID = p_contentType
				AND
				StoryTbl.UserID = p_profileID

				AND
            (
            StoryTbl.UserID = p_userID 
			OR
            StoryTbl.CircleID in
            (select CircleID from CircleMemberTbl
            where MemberID = p_userID) ) 
                    order by StoryDate Desc, StoryTitle 
					OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getprofileuser
DROP FUNCTION IF EXISTS getprofileuser;
CREATE OR REPLACE FUNCTION public.getprofileuser(p_profileid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "UserID" integer,
    "UserFirst" varchar(50),
    "UserLast" varchar(50),
    "UserDisplayName" varchar(110),
    "UserEmail" varchar(75),
    "ValidationDate" timestamp without time zone,
    "AdminLevel" integer,
    "UserMediaLoc" varchar(100),
    "LastFeedView" timestamp without time zone,
    "LastBookView" timestamp without time zone,
    "LastCircleView" timestamp without time zone)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select UserID, UserFirst, UserLast, UserDisplayName, UserEmail, ValidationDate, AdminLevel, UserMediaLoc, LastFeedView, LastBookView, LastCircleView
            from UserTbl 
            where UserID = p_profileID;
    END;
END;
$function$
;

-- getquestionchunk
DROP FUNCTION IF EXISTS getquestionchunk;
CREATE OR REPLACE FUNCTION public.getquestionchunk(p_startrecord integer DEFAULT NULL::integer, p_retrieveblock integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "ViewPointID" integer,
    "ViewPointQ" character varying,
    "ViewPointDate" text,
    "ViewPointDay" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT ViewPointID AS "ViewPointID", ViewPointQ::varchar::varchar AS "ViewPointQ", to_char(ViewPointDate, 'MM-DD-YYYY') AS "ViewPointDate", (extract(dow from ViewPointDate) + 1)::text::text AS "ViewPointDay" from ViewPointTbl 
		WHERE ViewPointID <> 1
			order by cast(ViewPointDate as date) desc
			OFFSET p_startRecord LIMIT p_retrieveBlock;
    END;
END;
$function$
;

-- getquestioncount
DROP FUNCTION IF EXISTS getquestioncount;
CREATE OR REPLACE FUNCTION public.getquestioncount()
 RETURNS TABLE("QuestionCount" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT COUNT(*) AS "QuestionCount" FROM ViewPointTbl;
    END;
END;
$function$
;

-- getquestions
DROP FUNCTION IF EXISTS getquestions;
CREATE OR REPLACE FUNCTION public.getquestions(p_startdate character varying DEFAULT NULL::character varying, p_retrieveblock integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "ViewPointID" integer,
    "ViewPointQ" character varying,
    "ViewPointDate" text,
    "ViewPointDay" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT ViewPointID, ViewPointQ, to_char(ViewPointDate, 'MM-DD-YYYY') as ViewPointDate, 
		(extract(dow from ViewPointDate) + 1) AS ViewPointDay
		from ViewPointTbl 
		WHERE ViewPointDate <= p_startDate 
			order by cast(ViewPointDate as date) desc LIMIT p_retrieveBlock;
    END;
END;
$function$
;

-- getquickconnectquestions
DROP FUNCTION IF EXISTS getquickconnectquestions;
CREATE OR REPLACE FUNCTION public.getquickconnectquestions()
 RETURNS TABLE(
    "ViewPointID" integer,
    "ViewPointQ" character varying,
    "ViewPointDate" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT ViewPointID AS "ViewPointID", ViewPointQ::varchar::varchar AS "ViewPointQ", to_char(ViewPointDate, 'MM-DD-YYYY') AS "ViewPointDate" from ViewPointTbl 

			order by ViewPointDate desc;
    END;
END;
$function$
;

-- getrememberstory
DROP FUNCTION IF EXISTS getrememberstory;
CREATE OR REPLACE FUNCTION public.getrememberstory(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("StoryID" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_journalCircle INT;
    v_RandomStoryID INT;
BEGIN
    BEGIN
    SELECT CircleTbl.CircleID INTO v_journalCircle FROM CircleTbl
    WHERE JournalID = p_userID;
    SELECT StoryTbl.StoryID INTO v_RandomStoryID FROM StoryTbl
    LEFT JOIN UserTbl
        ON StoryTbl.UserID = UserTbl.UserID
    LEFT JOIN MediaTbl
        ON StoryTbl.StoryID = MediaTbl.StoryID
        AND MediaTbl.FeatureMedia = 1
    WHERE

        StoryTbl.StoryTypeID = 1
        AND MediaTbl.MediaLoc IS NOT NULL
        AND MediaTbl.MediaType = 2
        AND StoryTbl.Hidden <> 1
        AND StoryTbl.CircleID <> v_journalCircle
        AND
        (
            (
                StoryTbl.CircleID IN
                (
                    SELECT CircleID
                    FROM CircleMemberTbl
                    WHERE MemberID = p_userID
                      AND AcceptedDate IS NOT NULL
                )
                AND StoryTbl.StoryID NOT IN
                (
                    SELECT StoryID
                    FROM ExcludeTbl
                    WHERE ExcludeUser = p_userID
                )
            )
            OR StoryTbl.UserID = p_userID
        )
    ORDER BY gen_random_uuid() LIMIT 1;
    RETURN QUERY SELECT
        StoryTbl.StoryID,
        TRIM(StoryTbl.StoryTitle) AS StoryTitle,
        TRIM(StoryTbl.StoryText) AS StoryText,
        StoryTbl.UserID,
        StoryTbl.StoryTypeID,
        to_char(StoryTbl.StoryDate, 'Mon DD, YYYY') AS "Date",
        TRIM(StoryTbl.Interviewee) AS Interviewee,
        StoryTbl.CircleID,
        TRIM(UserTbl.UserDisplayName) AS UserDisplayName,
        StoryTbl.StoryIngredients,
        StoryTbl.Hidden,
        UserTbl.UserMediaLoc,
        MediaTbl.MediaLoc,
        MediaTbl.MediaID,
        MediaTbl.FeatureMedia,
        MediaTbl.MediaType
    FROM StoryTbl
    LEFT JOIN UserTbl
        ON StoryTbl.UserID = UserTbl.UserID
    LEFT JOIN MediaTbl
        ON StoryTbl.StoryID = MediaTbl.StoryID
        AND MediaTbl.FeatureMedia = 1
    WHERE StoryTbl.StoryID = v_RandomStoryID
    ORDER BY StoryTbl.StoryDate DESC, StoryTbl.StoryTitle;
    END;
END;
$function$
;

-- getrememberstoryonthisdayorrandom
DROP FUNCTION IF EXISTS getrememberstoryonthisdayorrandom;
CREATE OR REPLACE FUNCTION public.getrememberstoryonthisdayorrandom(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "UserID" integer,
    "StoryTypeID" integer,
    "Date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "Hidden" integer,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_journalCircle INT;
    v_selectedStoryID INT;
    v_todayUtc DATE := CAST(CURRENT_TIMESTAMP AS DATE);
BEGIN
    BEGIN
    SELECT CircleTbl.CircleID INTO v_journalCircle FROM CircleTbl
  WHERE JournalID = p_userID;
    WITH
    EligibleStories
    AS
    (
      SELECT DISTINCT
        StoryTbl.StoryID,
        StoryTbl.StoryDate
      FROM StoryTbl
        LEFT JOIN MediaTbl
        ON StoryTbl.StoryID = MediaTbl.StoryID
          AND MediaTbl.FeatureMedia = 1
      WHERE StoryTbl.StoryTypeID = 1
        AND MediaTbl.MediaLoc IS NOT NULL
        AND MediaTbl.MediaType = 2
        AND coalesce(StoryTbl.Hidden, 0) <> 1
        AND
        (
                                        StoryTbl.UserID = p_userID
        OR
        (
                                                StoryTbl.CircleID <> v_journalCircle
        AND StoryTbl.CircleID IN
                                                (
                                                        SELECT CircleID
        FROM CircleMemberTbl
        WHERE MemberID = p_userID
          AND AcceptedDate IS NOT NULL
                                                )
        AND StoryTbl.StoryID NOT IN
                                                (
                                                        SELECT StoryID
        FROM ExcludeTbl
        WHERE ExcludeUser = p_userID
                                                )
                                        )
                                )
    )
  SELECT
    EligibleStories.StoryID INTO v_selectedStoryID
  FROM EligibleStories
  WHERE extract(month from EligibleStories.StoryDate) = extract(month from v_todayUtc)
    AND extract(day from EligibleStories.StoryDate) = extract(day from v_todayUtc)
    AND extract(year from EligibleStories.StoryDate) < extract(year from v_todayUtc)
  ORDER BY gen_random_uuid() LIMIT 1;
    IF v_selectedStoryID IS NULL THEN
    BEGIN
    WITH
      EligibleStories
      AS
      (
        SELECT DISTINCT
          StoryTbl.StoryID,
          StoryTbl.StoryDate
        FROM StoryTbl
          LEFT JOIN MediaTbl
          ON StoryTbl.StoryID = MediaTbl.StoryID
            AND MediaTbl.FeatureMedia = 1
        WHERE StoryTbl.StoryTypeID = 1
          AND MediaTbl.MediaLoc IS NOT NULL
          AND MediaTbl.MediaType = 2
          AND coalesce(StoryTbl.Hidden, 0) <> 1
          AND
          (
                                                StoryTbl.UserID = p_userID
          OR
          (
                                                        StoryTbl.CircleID <> v_journalCircle
          AND StoryTbl.CircleID IN
                                                        (
                                                                SELECT CircleID
          FROM CircleMemberTbl
          WHERE MemberID = p_userID
            AND AcceptedDate IS NOT NULL
                                                        )
          AND StoryTbl.StoryID NOT IN
                                                        (
                                                                SELECT StoryID
          FROM ExcludeTbl
          WHERE ExcludeUser = p_userID
                                                        )
                                                )
                                        )
      )
    SELECT
      EligibleStories.StoryID INTO v_selectedStoryID
    FROM EligibleStories
    ORDER BY gen_random_uuid() LIMIT 1;
    END; END IF;
    RETURN QUERY SELECT
    StoryTbl.StoryID,
    TRIM(StoryTbl.StoryTitle) AS StoryTitle,
    TRIM(StoryTbl.StoryText) AS StoryText,
    StoryTbl.UserID,
    StoryTbl.StoryTypeID,
    to_char(StoryTbl.StoryDate, 'Mon DD, YYYY') AS "Date",
    TRIM(StoryTbl.Interviewee) AS Interviewee,
    StoryTbl.CircleID,
    TRIM(UserTbl.UserDisplayName) AS UserDisplayName,
    StoryTbl.StoryIngredients,
    StoryTbl.Hidden,
    UserTbl.UserMediaLoc,
    MediaTbl.MediaLoc,
    MediaTbl.MediaID,
    MediaTbl.FeatureMedia,
    MediaTbl.MediaType
  FROM StoryTbl
    LEFT JOIN UserTbl
    ON StoryTbl.UserID = UserTbl.UserID
    LEFT JOIN MediaTbl
    ON StoryTbl.StoryID = MediaTbl.StoryID
      AND MediaTbl.FeatureMedia = 1
  WHERE StoryTbl.StoryID = v_selectedStoryID
  ORDER BY StoryTbl.StoryDate DESC, StoryTbl.StoryTitle;
    END;
END;
$function$
;

-- getsearchbooks
DROP FUNCTION IF EXISTS getsearchbooks;
CREATE OR REPLACE FUNCTION public.getsearchbooks(p_userid integer DEFAULT NULL::integer, p_searchterm character varying DEFAULT NULL::character varying, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    SELECT COUNT(*) INTO v_recordCount FROM BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID
			
			where 
			BookTbl.BookTitle LIKE '%' || p_searchTerm || '%'
			AND
			(
			(BookOwner = p_userID AND BookTbl.TimeCapsuleDate IS NULL)

			OR (
			(BookTbl.TimeCapsuleDate IS NULL) AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)
					)
				)
				);
    RETURN QUERY Select distinct v_recordCount, BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner, BookTbl.TimeCapsuleDate, to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date, BookTbl.CircleID, UserTbl.UserDisplayName, v_primary AS primaryCircle from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 
			BookTbl.BookTitle LIKE '%' || p_searchTerm || '%'
			AND
			(
			(BookOwner = p_userID AND BookTbl.TimeCapsuleDate IS NULL)

			OR (
			(BookTbl.TimeCapsuleDate IS NULL) AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)
					)
				)
				)
            order by  BookTitle
								OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getsubtypes
DROP FUNCTION IF EXISTS getsubtypes;
CREATE OR REPLACE FUNCTION public.getsubtypes()
 RETURNS TABLE(usersubtypeid integer, usersubtype character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT u.UserSubTypeID, u.UserSubType 
    FROM UserSubTypeTbl u
    ORDER BY u.UserSubTypeID;
END;
$function$
;

-- gettemplates
DROP FUNCTION IF EXISTS gettemplates;
CREATE OR REPLACE FUNCTION public.gettemplates(p_userid integer DEFAULT NULL::integer, p_typeid integer DEFAULT NULL::integer)
 RETURNS TABLE("MemoryTemplateID" integer, "MemoryTemplateType" integer, "MemoryTemplateName" text, "MemoryTemplateUserID" integer, "MemoryText" text, "MemoryCircle" integer, "MemoryIngredients" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT  * 
	from TemplateTbl 
	WHERE TemplateTbl.MemoryTemplateType = p_typeID 
	AND TemplateTbl.MemoryTemplateUserID = p_userID;
    END;
END;
$function$
;

-- getthisqcquestion
DROP FUNCTION IF EXISTS getthisqcquestion;
CREATE OR REPLACE FUNCTION public.getthisqcquestion(p_viewpointid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "ViewPointID" integer,
    "ViewPointQ" character varying,
    "ViewPointDate" text,
    "QCUserID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT ViewPointID
			  ,ViewPointQ
			  ,to_char(ViewPointDate, 'YYYY-MM-DD') as ViewPointDate
			  ,QCUserID
		  FROM ViewPointTbl

		  where ViewPointID = p_viewPointID;
    END;
END;
$function$
;

-- getthisstory
DROP FUNCTION IF EXISTS getthisstory;
CREATE OR REPLACE FUNCTION public.getthisstory(p_storyid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "Hidden" integer,
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle,
               trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid,
               StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date,
               trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID,
			    trim(UserTbl.UserDisplayName) as UserDisplayName,
               StoryTbl.StoryIngredients,
			   StoryTbl.Hidden, 
               MediaTbl.MediaLoc, MediaTbl.MediaID, MediaTbl.FeatureMedia,
               MediaTbl.MediaType
               from StoryTbl
			   left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID)

			   where StoryTbl.StoryID = p_storyID and (FeatureMedia = 1 OR FeatureMedia IS NULL);
    END;
END;
$function$
;

-- gettimecapsules
DROP FUNCTION IF EXISTS gettimecapsules;
CREATE OR REPLACE FUNCTION public.gettimecapsules(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110),
    "primaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primary int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primary FROM CircleTbl
			where CircleOwner = p_userID 
			AND CirclePrimary is not NULL;
    RETURN QUERY Select distinct BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
            BookTbl.TimeCapsuleDate, 
            to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
            BookTbl.CircleID,
            UserTbl.UserDisplayName, v_primary AS primaryCircle
            from BookTbl
			left join UserTbl 
			on BookTbl.BookOwner = UserTbl.UserID

            where 
			(
			BookTbl.TimeCapsuleDate IS NOT NULL

			AND
			
			BookOwner = p_userID)

			OR (
			(BookTbl.TimeCapsuleDate <= CURRENT_TIMESTAMP ) AND 
				(CircleID in
					(select CircleID from CircleMemberTbl
					where MemberID = p_userID AND AcceptedDate IS NOT NULL)

					AND ( BookTbl.BookID not in
					(Select BookID from BkExcludeTbl
					where ExcludeUser = p_userID)
					) 
					)
				)
				
            order by BookTitle;
    END;
END;
$function$
;

-- gettimezones
DROP FUNCTION IF EXISTS gettimezones;
CREATE OR REPLACE FUNCTION public.gettimezones()
 RETURNS TABLE(name character varying, current_utc_offset character varying, is_currently_dst integer)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY SELECT 
        name::varchar AS "name", 
        utc_offset::text::varchar AS "current_utc_offset", 
        CASE WHEN is_dst THEN 1 ELSE 0 END::int AS "is_currently_dst"
    FROM pg_timezone_names()
    WHERE name NOT LIKE 'UTC%';
END;
$function$
;

-- gettodaysqc
DROP FUNCTION IF EXISTS gettodaysqc;
CREATE OR REPLACE FUNCTION public.gettodaysqc(p_today date DEFAULT NULL::date)
 RETURNS TABLE(
    "ViewPointID" integer,
    "ViewPointQ" character varying,
    "ViewPointDate" text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_vpID int;
BEGIN
    BEGIN
    SELECT ViewPointID INTO v_vpID FROM ViewPointTbl 
	where ViewPointDate = p_today;
    RETURN QUERY SELECT ViewPointID, ViewPointQ, to_char(ViewPointDate, 'MM-DD-YYYY') as ViewPointDate from ViewPointTbl where ViewPointID = v_vpID;
    END;
END;
$function$
;

-- gettraditions
DROP FUNCTION IF EXISTS gettraditions;
CREATE OR REPLACE FUNCTION public.gettraditions()
 RETURNS TABLE(
    "TraditionTypeID" integer,
    "TraditionName" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select TraditionTypeID AS "TraditionTypeID", trim(TraditionName)::varchar::varchar AS "TraditionName" from TraditionTypeTbl
            order by
            TraditionName;
    END;
END;
$function$
;

-- gettypestories
DROP FUNCTION IF EXISTS gettypestories;
CREATE OR REPLACE FUNCTION public.gettypestories(p_userid integer DEFAULT NULL::integer, p_typeid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "Hidden" integer,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_journalCircle int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleTbl.CircleID INTO v_journalCircle FROM CircleTbl 
		where JournalID = p_userID;
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
               where 
			   StoryTbl.StoryTypeID = p_typeID
			   AND 
			   StoryTbl.Hidden <> 1 
			   AND
			   StoryTbl.CircleID <> v_journalCircle 

			   AND
			   (
                    (CircleID in
                    (select CircleID from CircleMemberTbl
                    where MemberID = p_userID AND AcceptedDate IS NOT NULL) )

                    AND ( StoryTbl.StoryID not in
                    (Select StoryID from ExcludeTbl
                    where ExcludeUser = p_userID)
                    )
					

                    OR (storyTbl.UserID = p_userID 
					)
					);
    IF v_recordCount = 0 THEN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StarterTbl;
    RETURN QUERY SELECT v_recordCount, StarterTbl.StoryID, trim(StarterTbl.StoryTitle) as StoryTitle, trim(StarterTbl.StoryText) as StoryText, StarterTbl.Userid, StarterTbl.StoryTypeID, to_char(Startertbl.StoryDate, 'Mon DD, YYYY') as date, trim(StarterTbl.Interviewee) as Interviewee, StarterTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StarterTbl.StoryIngredients, StarterTbl.Hidden, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StarterTbl
               left join UserTbl
               on StarterTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StarterTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)
			  
			   order by StoryDate Desc, StoryTitle LIMIT 1;
    END;
    ELSE
    BEGIN
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, StoryTbl.Hidden, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 
			   StoryTbl.StoryTypeID = p_typeID
			   AND
			   StoryTbl.Hidden <> 1 
			   AND
			   StoryTbl.CircleID <> v_journalCircle 
			   AND
			   StoryTbl.CircleID <> 0
			   AND 
			   StoryTbl.CircleID IS NOT NULL

               AND 

               (StoryTbl.UserID = p_userID 

               OR 

			   (
                    (CircleID in
                    (select CircleID from CircleMemberTbl
                    where MemberID = p_userID AND AcceptedDate IS NOT NULL) )

                    AND ( StoryTbl.StoryID not in
                    (Select StoryID from ExcludeTbl
                    where ExcludeUser = p_userID)
                    )
                    )
					
					)

                    order by StoryDate Desc, StoryTitle 
					OFFSET p_recordStart LIMIT p_pageLength;
    END; END IF;
    END;
END;
$function$
;

-- getuser
DROP FUNCTION IF EXISTS getuser;
CREATE OR REPLACE FUNCTION public.getuser(p_useremail character varying)
 RETURNS TABLE(userid integer, userfirst character varying, userlast character varying, userdisplayname character varying, useremail character varying, validationdate timestamp without time zone, adminlevel integer, usermedialoc character varying, usersubtype integer, usersubexpiration date, usertz character varying, tcacceptdate timestamp without time zone, stripecustomer character varying, stripesubscription character varying, cancelid integer, lastexport date)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        u.UserID, 
        u.UserFirst, 
        u.UserLast, 
        u.UserDisplayName, 
        u.UserEmail, 
        u.ValidationDate, 
        u.AdminLevel, 
        u.UserMediaLoc, 
        u.UserSubType, 
        u.UserSubExpiration, 
        u.UserTZ, 
        u.TCAcceptDate, 
        u.StripeCustomer, 
        u.StripeSubscription, 
        u.CancelID, 
        u.LastExport::date
    FROM UserTbl u
    WHERE u.UserEmail = p_userEmail;
END;
$function$
;

-- getuserbyid
DROP FUNCTION IF EXISTS getuserbyid;
CREATE OR REPLACE FUNCTION public.getuserbyid(p_userid integer)
 RETURNS TABLE(userid integer, userfirst character varying, userlast character varying, userdisplayname character varying, useremail character varying, validationdate timestamp without time zone, adminlevel integer, usermedialoc character varying, usersubtype integer, usersubexpiration date, stripecustomer character varying, stripesubscription character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        u.UserID, 
        u.UserFirst, 
        u.UserLast, 
        u.UserDisplayName, 
        u.UserEmail, 
        u.ValidationDate, 
        u.AdminLevel, 
        u.UserMediaLoc, 
        u.UserSubType, 
        u.UserSubExpiration, 
        u.StripeCustomer, 
        u.StripeSubscription
    FROM UserTbl u
    WHERE u.UserID = p_userID;
END;
$function$
;

-- getuserbyname
DROP FUNCTION IF EXISTS getuserbyname;
CREATE OR REPLACE FUNCTION public.getuserbyname(p_firstname character varying, p_lastname character varying)
 RETURNS TABLE(userid integer, userfirst character varying, userlast character varying, userdisplayname character varying, useremail character varying, validationdate timestamp without time zone, adminlevel integer, usermedialoc character varying, usersubtype integer, usersubexpiration date, usertz character varying, tcacceptdate timestamp without time zone, stripecustomer character varying, stripesubscription character varying, joindate timestamp without time zone)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        u.UserID, 
        u.UserFirst, 
        u.UserLast, 
        u.UserDisplayName, 
        u.UserEmail, 
        u.ValidationDate, 
        u.AdminLevel, 
        u.UserMediaLoc, 
        u.UserSubType, 
        u.UserSubExpiration, 
        u.UserTZ, 
        u.TCAcceptDate, 
        u.StripeCustomer, 
        u.StripeSubscription, 
        u.JoinDate
    FROM UserTbl u
    WHERE u.UserFirst = p_firstName AND u.UserLast = p_lastName;
END;
$function$
;

-- getusercount
DROP FUNCTION IF EXISTS getusercount;
CREATE OR REPLACE FUNCTION public.getusercount(p_usertype integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_userCount int;
BEGIN
    BEGIN
    SELECT count(UserTbl.UserID) INTO v_userCount FROM UserTbl
	where UserSubType = p_userType;
    return v_userCount;
    END;
END;
$function$
;

-- getusercountssubtype
DROP FUNCTION IF EXISTS getusercountssubtype;
CREATE OR REPLACE FUNCTION public.getusercountssubtype()
 RETURNS TABLE("UserSubType" character varying, "userCount" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select UserSubType::varchar::varchar AS "UserSubType", count(*) AS "userCount" from UserTbl
	Group By UserSubType;
    END;
END;
$function$
;

-- getuseremail
DROP FUNCTION IF EXISTS getuseremail;
CREATE OR REPLACE FUNCTION public.getuseremail(p_userid integer)
 RETURNS TABLE(useremail character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT u.UserEmail 
    FROM UserTbl u 
    WHERE u.UserID = p_userID;
END;
$function$
;

-- getuserinfo
DROP FUNCTION IF EXISTS getuserinfo;
CREATE OR REPLACE FUNCTION public.getuserinfo(p_userid integer)
 RETURNS TABLE(userid integer, useremail character varying, userfirst character varying, userlast character varying, userdisplayname character varying, validationdate timestamp without time zone, adminlevel integer, usermedialoc character varying, usersubtype integer, usersubtypeid integer, usersubexpiration date, usertz character varying, tcacceptdate timestamp without time zone, stripecustomer character varying, stripesubscription character varying, cancelid integer, lastexport date)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        u.UserID,
        u.UserEmail,
        u.UserFirst,
        u.UserLast,
        u.UserDisplayName,
        u.ValidationDate,
        u.AdminLevel,
        u.UserMediaLoc,
        u.UserSubType,
        u.UserSubType, -- UserSubTypeID
        u.UserSubExpiration,
        u.UserTZ,
        u.TCAcceptDate,
        u.StripeCustomer,
        u.StripeSubscription,
        u.CancelID,
        u.LastExport::date
    FROM UserTbl u
    WHERE u.UserID = p_userID;
END;
$function$
;

-- getuserlist
DROP FUNCTION IF EXISTS getuserlist;
CREATE OR REPLACE FUNCTION public.getuserlist()
 RETURNS TABLE("UserID" integer, "UserFirst" character varying, "UserLast" character varying, "UserDisplayName" character varying, "UserEmail" character varying, "UserSubType" character varying, "UserSubExpiration" date)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT UserID AS "UserID", UserFirst::varchar::varchar AS "UserFirst", UserLast::varchar::varchar AS "UserLast", UserDisplayName::varchar::varchar AS "UserDisplayName", UserEmail::varchar::varchar AS "UserEmail", UserSubType::varchar::varchar AS "UserSubType", UserSubExpiration AS "UserSubExpiration" from UserTbl 
	order by UserLast, UserFirst;
    END;
END;
$function$
;

-- getuserprofilejournals
DROP FUNCTION IF EXISTS getuserprofilejournals;
CREATE OR REPLACE FUNCTION public.getuserprofilejournals(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_journalCircle int;
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT CircleID INTO v_journalCircle FROM CircleTbl 
	where JournalID = p_userID;
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 
			   		StoryTbl.CircleID = v_journalCircle;
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 
			   		StoryTbl.CircleID = v_journalCircle
					
                    order by StoryDate Desc, StoryTitle 

						OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getuserprofilememories
DROP FUNCTION IF EXISTS getuserprofilememories;
CREATE OR REPLACE FUNCTION public.getuserprofilememories(p_userid integer DEFAULT NULL::integer, p_contenttype integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 
			   		StoryTbl.StoryTypeID = p_contentType

                    
                    AND storyTbl.UserID = p_userID;
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

               where 
			   		StoryTbl.StoryTypeID = p_contentType

                    
                    AND storyTbl.UserID = p_userID 
					
                    order by StoryDate Desc, StoryTitle 

						OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- getusersubtype
DROP FUNCTION IF EXISTS getusersubtype;
CREATE OR REPLACE FUNCTION public.getusersubtype(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "UserSubType" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY SELECT UserSubType from UserTbl
	where userid = p_userID;
    END;
END;
$function$
;

-- importlistitems
DROP FUNCTION IF EXISTS importlistitems;
CREATE OR REPLACE FUNCTION public.importlistitems(p_currlist integer DEFAULT NULL::integer, p_templatelist integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO ListItemTbl(Item, ItemStatus, DueDate, AssignedTo, Notes, ListID)
SELECT Item, ItemStatus, DueDate, AssignedTo, Notes, p_currList
FROM ListItemTbl
WHERE ListID = p_templateList;
    END;
END;
$function$
;

-- invitationcount
DROP FUNCTION IF EXISTS invitationcount;
CREATE OR REPLACE FUNCTION public.invitationcount(p_userid integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_invitecount int;
BEGIN
    BEGIN
    SELECT count(InvitationID) INTO v_invitecount FROM InvitationTbl
        Left Join UserTbl
        on InvitationTbl.Invitedby = UserTbl.UserID
    where
				InvitedBy = p_userID
        AND
        InvitationType > 1
        AND
        (
                (InviteDate > (CURRENT_TIMESTAMP + INTERVAL '-7 day') AND ConvertDate IS NULL AND DoNotContactDate IS NULL)

        OR

        (ConvertDate is not null)
				);
    RETURN v_invitecount;
    END;
END;
$function$
;

-- invitationverification
DROP FUNCTION IF EXISTS invitationverification;
CREATE OR REPLACE FUNCTION public.invitationverification(p_useremail character varying, p_invitationid integer)
 RETURNS TABLE(invitationtype integer)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT i.InvitationType
    FROM InvitationTbl i
    WHERE i.InviteEmail = p_userEmail
      AND i.InvitationID = p_invitationID
      AND i.ConvertDate IS NULL
      AND i.UserID IS NULL
      AND i.DoNotContactDate IS NULL;
END;
$function$
;

-- makefeature
DROP FUNCTION IF EXISTS makefeature;
CREATE OR REPLACE FUNCTION public.makefeature(p_medialoc character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_storyID int;
BEGIN
    BEGIN
    SELECT MediaTbl.StoryID INTO v_storyID FROM MediaTbl where MediaLoc = p_MediaLoc;
    UPDATE MediaTbl
	   SET FeatureMedia = 2
		 WHERE StoryID = v_storyID;
    UPDATE MediaTbl
	   SET FeatureMedia = 1
		 WHERE MediaLoc = p_MediaLoc;
    END;
END;
$function$
;

-- markitemdone
DROP FUNCTION IF EXISTS markitemdone;
CREATE OR REPLACE FUNCTION public.markitemdone(p_itemid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE ListItemTbl
   SET ItemStatus = 1

 WHERE ListItemTbl.ListItemID = p_itemID;
    END;
END;
$function$
;

-- markitemopen
DROP FUNCTION IF EXISTS markitemopen;
CREATE OR REPLACE FUNCTION public.markitemopen(p_itemid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE ListItemTbl
   SET ItemStatus = 0

 WHERE ListItemTbl.ListItemID = p_itemID;
    END;
END;
$function$
;

-- markread
DROP FUNCTION IF EXISTS markread;
CREATE OR REPLACE FUNCTION public.markread(p_userid integer DEFAULT NULL::integer, p_newsid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO NewsCancelTbl
           (CancelUserID
           ,CancelDateTime
           ,NewsID)

     VALUES
           (p_userID
           ,CURRENT_TIMESTAMP
           ,p_newsID);
    END;
END;
$function$
;

-- movelistitems
DROP FUNCTION IF EXISTS movelistitems;
CREATE OR REPLACE FUNCTION public.movelistitems(p_oldlist integer DEFAULT NULL::integer, p_newlist integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    BEGIN
    Update ListItemTbl 
SET ListID = p_newList, ItemStatus = 0
WHERE ListID = p_oldList and ItemStatus = 1;
    END;
    END;
END;
$function$
;

-- mybooksfilter
DROP FUNCTION IF EXISTS mybooksfilter;
CREATE OR REPLACE FUNCTION public.mybooksfilter(p_bookswitch character varying DEFAULT NULL::character varying, p_tcswitch character varying DEFAULT NULL::character varying, p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    IF p_bookSwitch = 'on' and p_tcSwitch = 'off' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookOwner = p_userID and
      timecapsuledate IS NULL
    order by BookCreateDate Desc, BookTitle;
    END; END IF;
    IF p_bookSwitch = 'off' and p_tcSwitch = 'on' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookOwner = p_userID and
      timecapsuledate IS NOT NULL
    order by BookCreateDate Desc, BookTitle;
    END; END IF;
    IF p_bookSwitch = 'on' and p_tcSwitch = 'on' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookOwner = p_userID;
    END; END IF;
    IF p_bookSwitch = 'off' and p_tcSwitch = 'off' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,

      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookOwner = p_userID and BookID < 0;
    END; END IF;
    END;
END;
$function$
;

-- mycircleqcanswers
DROP FUNCTION IF EXISTS mycircleqcanswers;
CREATE OR REPLACE FUNCTION public.mycircleqcanswers(p_userid integer DEFAULT NULL::integer, p_ecircleid integer DEFAULT NULL::integer, p_qcid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "UserID" integer,
    "ViewPointID" integer,
    "VPAnswer" character varying,
    "UserDisplayName" varchar(110),
    "UserMediaLoc" varchar(100),
    "UserFirst" varchar(50),
    "UserLast" varchar(50),
    "VPAnswerID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select VPAnswerTbl.UserID, VPAnswerTbl.ViewPointID, VPAnswerTbl.VPAnswer,
        UserTbl.UserDisplayName, UserTbl.UserMediaLoc,
        UserTbl.UserFirst, UserTbl.UserLast, VPAnswerTbl.VPAnswerID
        from VPAnswerTbl
        Left join UserTbl
        on VPAnswerTbl.UserID = UserTbl.UserID
        where VPAnswer is not null
        AND
        ViewPointID = p_qcID
        AND VPAnswerTbl.UserID in
        (select MemberID from CircleMemberTbl

        where CircleID = p_eCircleID)
        UNION
        Select VPAnswerTbl.UserID, VPAnswerTbl.ViewPointID, VPAnswerTbl.VPAnswer,
        UserTbl.UserDisplayName, UserTbl.UserMediaLoc,
        UserTbl.UserFirst, UserTbl.UserLast, VPAnswerTbl.VPAnswerID
        from VPAnswerTbl
        Left join UserTbl
        on VPAnswerTbl.UserID = UserTbl.UserID
        where VPAnswerTbl.UserID = p_userID AND
        ViewPointID = p_qcID
        Order by UserLast, UserFirst;
    END;
END;
$function$
;

-- newbook
DROP FUNCTION IF EXISTS newbook;
CREATE OR REPLACE FUNCTION public.newbook(p_userid integer DEFAULT NULL::integer, p_booktitle character varying DEFAULT NULL::character varying, p_tcdate date DEFAULT NULL::date, p_circleid integer DEFAULT NULL::integer, p_covercolor character varying DEFAULT NULL::character varying, p_coverfont character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO BookTbl
           (BookTitle
           ,BookOwner
           ,TimeCapsuleDate
           ,BookCreateDate
           ,CircleID
		   ,BookColor
		   ,BookFont)
     VALUES
           (p_bookTitle
           ,p_userID
           ,p_tcDate
           ,CURRENT_TIMESTAMP
           ,p_circleID
		   ,p_coverColor
		   ,p_coverFont);
    END;
END;
$function$
;

-- newcircle
DROP FUNCTION IF EXISTS newcircle;
CREATE OR REPLACE FUNCTION public.newcircle(p_circlename character varying DEFAULT NULL::character varying, p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO CircleTbl
           (CircleOwner
           ,CircleName
)
     VALUES
           (p_userID
           ,p_circleName
           );
    END;
END;
$function$
;

-- newinvitedmonthlysubscriber
DROP FUNCTION IF EXISTS newinvitedmonthlysubscriber;
CREATE OR REPLACE FUNCTION public.newinvitedmonthlysubscriber(p_useremail character varying, p_userhash character varying, p_userfirst character varying, p_userlast character varying, p_userdisplayname character varying, p_verifyhash character varying, p_invitationid integer)
 RETURNS TABLE(userid integer, userfirst character varying, userlast character varying, userdisplayname character varying, useremail character varying, validationdate timestamp without time zone, adminlevel integer, usermedialoc character varying, lastfeedview timestamp without time zone, lastbookview timestamp without time zone, lastcircleview timestamp without time zone)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_userID int;
    v_circleID int;
    v_invitedBy int;
    v_adminInvite int;
    v_invitedCircle int;
BEGIN
    SELECT AdminInvite INTO v_adminInvite 
    FROM InvitationTbl 
    WHERE InvitationID = p_invitationID;

    INSERT INTO UserTbl (
        UserEmail, UserHash, UserFirst, UserLast, UserDisplayName, 
        UserSubType, UserSubExpiration, AdminLevel, JoinDate
    )
    VALUES (
        p_userEmail, p_userHash, p_userFirst, p_userLast, p_userDisplayName,
        9004, CURRENT_DATE + INTERVAL '30 days', 1, CURRENT_TIMESTAMP
    )
    RETURNING UserTbl.UserID INTO v_userID;

    UPDATE UserTbl
    SET VerifyHash = p_verifyHash
    WHERE UserTbl.UserID = v_userID;

    INSERT INTO CircleTbl (CircleOwner, CircleName, JournalID)
    VALUES (v_userID, 'Journal', v_userID);

    INSERT INTO CircleTbl (CircleOwner, CircleName, CirclePrimary)
    VALUES (v_userID, 'Everyone', v_userID)
    RETURNING CircleTbl.CircleID INTO v_circleID;

    INSERT INTO CircleMemberTbl (CircleID, MemberID, InvitedBy, AcceptedDate, Status)
    VALUES (1, v_userID, 0, CURRENT_TIMESTAMP, 'Accepted');

    UPDATE InvitationTbl 
    SET UserID = v_userID 
    WHERE InvitationID = p_invitationID;

    IF v_adminInvite IS NULL THEN
        SELECT InvitedBy INTO v_invitedBy 
        FROM InvitationTbl 
        WHERE InvitationID = p_invitationID;

        INSERT INTO CircleMemberTbl (CircleID, MemberID, InvitedBy, AcceptedDate, Status)
        VALUES (v_circleID, v_invitedBy, v_userID, CURRENT_TIMESTAMP, 'Accepted');

        SELECT CircleID INTO v_invitedCircle 
        FROM CircleTbl 
        WHERE CircleOwner = v_invitedBy AND CircleName = 'Everyone';

        INSERT INTO CircleMemberTbl (CircleID, MemberID, InvitedBy, AcceptedDate, Status)
        VALUES (v_invitedCircle, v_userID, v_invitedBy, CURRENT_TIMESTAMP, 'Accepted');
    END IF;

    RETURN QUERY
    SELECT 
        u.UserID, u.UserFirst, u.UserLast, u.UserDisplayName, u.UserEmail, 
        u.ValidationDate, u.AdminLevel, u.UserMediaLoc, u.LastFeedView, 
        u.LastBookView, u.LastCircleView
    FROM UserTbl u
    WHERE u.UserEmail = p_userEmail;
END;
$function$
;

-- newinvitedsubscriber
DROP FUNCTION IF EXISTS newinvitedsubscriber;
CREATE OR REPLACE FUNCTION public.newinvitedsubscriber(p_useremail character varying, p_userhash character varying, p_userfirst character varying, p_userlast character varying, p_userdisplayname character varying, p_verifyhash character varying, p_invitationtype integer, p_invitationid integer)
 RETURNS TABLE(userid integer, userfirst character varying, userlast character varying, userdisplayname character varying, useremail character varying, validationdate timestamp without time zone, adminlevel integer, usermedialoc character varying, lastfeedview timestamp without time zone, lastbookview timestamp without time zone, lastcircleview timestamp without time zone)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_expDate date;
    v_userID int;
    v_circleID int;
    v_invitedBy int;
    v_invitedCircle int;
     v_adminInvite int;
BEGIN
    SELECT AdminInvite INTO v_adminInvite 
    FROM InvitationTbl 
    WHERE InvitationID = p_invitationID;

    IF p_invitationType = 1 THEN
        v_expDate := CURRENT_DATE + INTERVAL '30 days';
    ELSIF p_invitationType IN (2, 3, 4) THEN
        v_expDate := '2222-12-31'::date;
    ELSE
        v_expDate := CURRENT_DATE + INTERVAL '30 days';
    END IF;

    INSERT INTO UserTbl (
        UserEmail, UserHash, UserFirst, UserLast, UserDisplayName, 
        UserSubType, UserSubExpiration, AdminLevel, VerifyHash, 
        ValidationDate, JoinDate
    )
    VALUES (
        p_userEmail, p_userHash, p_userFirst, p_userLast, p_userDisplayName,
        p_invitationType, v_expDate, 1, p_verifyHash,
        CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    )
    RETURNING UserTbl.UserID INTO v_userID;

    INSERT INTO CircleTbl (CircleOwner, CircleName, CirclePrimary)
    VALUES (v_userID, 'Everyone', v_userID)
    RETURNING CircleTbl.CircleID INTO v_circleID;

    INSERT INTO CircleTbl (CircleOwner, CircleName, JournalID)
    VALUES (v_userID, 'Journal', v_userID);

    INSERT INTO CircleMemberTbl (CircleID, MemberID, InvitedBy, AcceptedDate, Status)
    VALUES (1, v_userID, 0, CURRENT_TIMESTAMP, 'Accepted');

    UPDATE InvitationTbl
    SET ConvertDate = CURRENT_TIMESTAMP,
        UserID = v_userID
    WHERE InvitationID = p_invitationID;

    SELECT InvitedBy INTO v_invitedBy 
    FROM InvitationTbl 
    WHERE InvitationID = p_invitationID AND AdminInvite IS NULL;

    IF v_invitedBy IS NOT NULL THEN
        INSERT INTO CircleMemberTbl (CircleID, MemberID, InvitedBy, AcceptedDate, Status)
        VALUES (v_circleID, v_invitedBy, v_userID, CURRENT_TIMESTAMP, 'Accepted');

        SELECT CircleID INTO v_invitedCircle 
        FROM CircleTbl 
        WHERE CircleOwner = v_invitedBy AND CircleName = 'Everyone';

        INSERT INTO CircleMemberTbl (CircleID, MemberID, InvitedBy, AcceptedDate, Status)
        VALUES (v_invitedCircle, v_userID, v_invitedBy, CURRENT_TIMESTAMP, 'Accepted');
    END IF;

    RETURN QUERY
    SELECT 
        u.UserID, u.UserFirst, u.UserLast, u.UserDisplayName, u.UserEmail, 
        u.ValidationDate, u.AdminLevel, u.UserMediaLoc, u.LastFeedView, 
        u.LastBookView, u.LastCircleView
    FROM UserTbl u
    WHERE u.UserEmail = p_userEmail;
END;
$function$
;

-- newitem
DROP FUNCTION IF EXISTS newitem;
CREATE OR REPLACE FUNCTION public.newitem(p_newitem character varying DEFAULT NULL::character varying, p_duedate date DEFAULT NULL::date, p_assignedto character varying DEFAULT NULL::character varying, p_notes text DEFAULT NULL::text, p_listid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO ListItemTbl
           (Item
           ,ItemStatus
           ,DueDate
           ,AssignedTo
           ,Notes
           ,ListID)
     VALUES
           (p_newItem
           ,0
           ,p_dueDate
           ,p_assignedTo
           ,p_notes
           ,p_listID);
    END;
END;
$function$
;

-- newlist
DROP FUNCTION IF EXISTS newlist;
CREATE OR REPLACE FUNCTION public.newlist(p_userid integer DEFAULT NULL::integer, p_listname character varying DEFAULT NULL::character varying, p_listdate integer DEFAULT NULL::integer, p_listcircle integer DEFAULT NULL::integer, p_listcolor character varying DEFAULT NULL::character varying, p_listcirclerights character varying DEFAULT NULL::character varying, p_listassigned integer DEFAULT NULL::integer, p_listtype character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO ListTbl
           (ListName
           ,ListOwner
           ,ListDate
           ,ListCircle
		   ,ListColor
		   ,ListCircleRights
		   ,ListAssigned
           ,ListType)
     VALUES
           (p_listName
           ,p_userID
           ,p_listDate
           ,p_listCircle
		   ,p_listColor
		   ,p_listCircleRights
		   ,p_listAssigned
           ,p_listType);
    END;
END;
$function$
;

-- newmonthlysubscriber
DROP FUNCTION IF EXISTS newmonthlysubscriber;
CREATE OR REPLACE FUNCTION public.newmonthlysubscriber(p_useremail character varying, p_userhash character varying, p_userfirst character varying, p_userlast character varying, p_userdisplayname character varying, p_verifyhash character varying)
 RETURNS TABLE(userid integer, userfirst character varying, userlast character varying, userdisplayname character varying, useremail character varying, validationdate timestamp without time zone, adminlevel integer, usermedialoc character varying, lastfeedview timestamp without time zone, lastbookview timestamp without time zone, lastcircleview timestamp without time zone)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_userID int;
    v_circleID int;
BEGIN
    INSERT INTO UserTbl (
        UserEmail, UserHash, UserFirst, UserLast, UserDisplayName, 
        UserSubType, UserSubExpiration, AdminLevel, JoinDate
    )
    VALUES (
        p_userEmail, p_userHash, p_userFirst, p_userLast, p_userDisplayName,
        9004, CURRENT_DATE + INTERVAL '30 days', 1, CURRENT_TIMESTAMP
    )
    RETURNING UserTbl.UserID INTO v_userID;

    UPDATE UserTbl
    SET VerifyHash = p_verifyHash
    WHERE UserTbl.UserID = v_userID;

    INSERT INTO CircleTbl (CircleOwner, CircleName, JournalID)
    VALUES (v_userID, 'Journal', v_userID);

    INSERT INTO CircleTbl (CircleOwner, CircleName, CirclePrimary)
    VALUES (v_userID, 'Everyone', v_userID)
    RETURNING CircleTbl.CircleID INTO v_circleID;

    -- Add to primary Everyone system circle
    INSERT INTO CircleMemberTbl (CircleID, MemberID, InvitedBy, AcceptedDate, Status)
    VALUES (1, v_userID, 0, CURRENT_TIMESTAMP, 'Accepted');

    RETURN QUERY
    SELECT 
        u.UserID, u.UserFirst, u.UserLast, u.UserDisplayName, u.UserEmail, 
        u.ValidationDate, u.AdminLevel, u.UserMediaLoc, u.LastFeedView, 
        u.LastBookView, u.LastCircleView
    FROM UserTbl u
    WHERE u.UserEmail = p_userEmail;
END;
$function$
;

-- newprofilemedialoc
DROP FUNCTION IF EXISTS newprofilemedialoc;
CREATE OR REPLACE FUNCTION public.newprofilemedialoc(p_userid integer DEFAULT NULL::integer, p_medialoc character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE UserTbl
   SET UserMediaLoc = p_mediaLoc
      
 WHERE UserID = p_userID;
    END;
END;
$function$
;

-- notificationnewuserconfirm
DROP FUNCTION IF EXISTS notificationnewuserconfirm;
CREATE OR REPLACE FUNCTION public.notificationnewuserconfirm(p_userid integer DEFAULT NULL::integer, p_useremail character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO NotificationTbl
           (NotificationType
           ,UserID
           ,Subject
           ,Message
           ,NotificationDate
           ,SentToEmail
           ,SentFromEmail)
     VALUES
           (1
           ,p_userID
           ,'Your New StoriesForUs Account'
           ,'standard text'
           ,CURRENT_TIMESTAMP
           ,p_userEmail
           ,'info@storiesforus.com');
    END;
END;
$function$
;

-- notifynewinvitations
DROP FUNCTION IF EXISTS notifynewinvitations;
CREATE OR REPLACE FUNCTION public.notifynewinvitations(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE("itemCount" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_lastView VARCHAR;
BEGIN
    BEGIN
    v_lastView := (Select LastBookView from UserTbl
			where UserID = p_userID);
    RETURN QUERY Select count(CircleMemberTbl.CircleMemID) AS itemCount
            from CircleMemberTbl
            where MemberID = p_userID
            AND status = 'Invited';
    END;
END;
$function$
;

-- openinvitationsforme
DROP FUNCTION IF EXISTS openinvitationsforme;
CREATE OR REPLACE FUNCTION public.openinvitationsforme(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CircleMemID" integer,
    "MemberID" integer,
    "status" varchar(50),
    "InvType" text,
    "InvitedBy" integer,
    "AcceptedDate" timestamp without time zone,
    "CircleOwner" integer,
    "CirclePrimary" integer,
    "UserDisplayName" varchar(110),
    "UserMediaLoc" varchar(100))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CircleMemberTbl.CircleMemID, CircleMemberTbl.MemberID, CircleMemberTbl.status, 'received' as InvType,
	CircleMemberTbl.InvitedBy, CircleMemberTbl.AcceptedDate, CircleTbl.CircleOwner, CircleTbl.CirclePrimary, 
	UserTbl.UserDisplayName, UserTbl.UserMediaLoc
from CircleMemberTbl
Join CircleTbl on CircleMemberTbl.CircleID = CircleTbl.CircleId 
join UserTbl on CircleTbl.CircleOwner = UserTbl.UserID

where MemberID = p_userID 
and Status is null
and CircleOwner = CirclePrimary

UNION

    Select CircleMemberTbl.CircleMemID, CircleMemberTbl.MemberID, CircleMemberTbl.status, 'sent' as InvType,
	CircleMemberTbl.InvitedBy, CircleMemberTbl.AcceptedDate, CircleTbl.CircleOwner, CircleTbl.CirclePrimary, 
	UserTbl.UserDisplayName, UserTbl.UserMediaLoc
from CircleMemberTbl
Join CircleTbl on CircleMemberTbl.CircleID = CircleTbl.CircleId 
join UserTbl on CircleMemberTbl.MemberID = UserTbl.UserID

where InvitedBy = p_userID 
and Status is null
and CircleOwner = CirclePrimary

order by InvType;
    END;
END;
$function$
;

-- openinvitationssentbyme
DROP FUNCTION IF EXISTS openinvitationssentbyme;
CREATE OR REPLACE FUNCTION public.openinvitationssentbyme(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CircleMemID" integer,
    "MemberID" integer,
    "status" varchar(50),
    "InvitedBy" integer,
    "AcceptedDate" timestamp without time zone,
    "CircleOwner" integer,
    "CirclePrimary" integer,
    "UserDisplayName" varchar(110))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CircleMemberTbl.CircleMemID, CircleMemberTbl.MemberID, CircleMemberTbl.status, CircleMemberTbl.InvitedBy, CircleMemberTbl.AcceptedDate, CircleTbl.CircleOwner, CircleTbl.CirclePrimary, UserTbl.UserDisplayName
	from CircleMemberTbl
	Join CircleTbl on CircleMemberTbl.CircleID = CircleTbl.CircleId 
	join UserTbl on CircleMemberTbl.MemberID = UserTbl.UserID

	where CircleTbl.CircleOwner = p_userID
	and Status is null
	and CircleOwner = CirclePrimary;
    END;
END;
$function$
;

-- otherbooksfilter
DROP FUNCTION IF EXISTS otherbooksfilter;
CREATE OR REPLACE FUNCTION public.otherbooksfilter(p_bookswitch character varying DEFAULT NULL::character varying, p_tcswitch character varying DEFAULT NULL::character varying, p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "BookID" integer,
    "BookTitle" text,
    "BookOwner" integer,
    "TimeCapsuleDate" date,
    "date" text,
    "CircleID" integer,
    "UserDisplayName" varchar(110))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    IF p_bookSwitch = 'on' and p_tcSwitch = 'off' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			 timecapsuledate IS NULL
      and

      (BookOwner <> p_userID

      AND (

				(CircleID in
					(select CircleID
      from CircleMemberTbl
      where MemberID = p_userID)

      AND ( BookID not in
					(Select BookID
      from BkExcludeTbl
      where ExcludeUser = p_userID)
					)
					)
				)
				)

    order by BookCreateDate Desc, BookTitle;
    END; END IF;
    IF p_bookSwitch = 'off' and p_tcSwitch = 'on' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookOwner <> p_userID and
      timecapsuledate IS NOT NULL
      and

      (BookOwner <> p_userID

      AND (

				(CircleID in
					(select CircleID
      from CircleMemberTbl
      where MemberID = p_userID)

      AND ( BookID not in
					(Select BookID
      from BkExcludeTbl
      where ExcludeUser = p_userID)
					)
					)
				)
				)

    order by BookCreateDate Desc, BookTitle;
    END; END IF;
    IF p_bookSwitch = 'on' and p_tcSwitch = 'on' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookOwner <> p_userID

      AND (

				(CircleID in
					(select CircleID
      from CircleMemberTbl
      where MemberID = p_userID)

      AND ( BookID not in
					(Select BookID
      from BkExcludeTbl
      where ExcludeUser = p_userID)
					)
					)
				)

    order by BookCreateDate Desc, BookTitle;
    END; END IF;
    IF p_bookSwitch = 'off' and p_tcSwitch = 'off' THEN
    BEGIN
    RETURN QUERY Select BookTbl.BookID, trim(BookTbl.BookTitle) AS BookTitle, BookTbl.BookOwner,
      BookTbl.TimeCapsuleDate,
      to_char(BookTbl.BookCreateDate, 'Mon DD, YYYY') as date,
      BookTbl.CircleID,
      UserTbl.UserDisplayName
    from BookTbl
      left join UserTbl
      on BookTbl.BookOwner = UserTbl.UserID
    where
			BookOwner <> p_userID and BookID < 0;
    END; END IF;
    END;
END;
$function$
;

-- removefrombook
DROP FUNCTION IF EXISTS removefrombook;
CREATE OR REPLACE FUNCTION public.removefrombook(p_bookid integer DEFAULT NULL::integer, p_storyid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM BookContentTbl
      WHERE BookContentTbl.BookID = p_bookID 
	  AND BookContentTbl.StoryID = p_storyID;
    END;
END;
$function$
;

-- removeheartstatus
DROP FUNCTION IF EXISTS removeheartstatus;
CREATE OR REPLACE FUNCTION public.removeheartstatus(p_userid integer DEFAULT NULL::integer, p_storyid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM HeartTbl
	where StoryID = p_storyID and UserID = p_userID;
    END;
END;
$function$
;

-- removelistfrombook
DROP FUNCTION IF EXISTS removelistfrombook;
CREATE OR REPLACE FUNCTION public.removelistfrombook(p_bookid integer DEFAULT NULL::integer, p_listid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM BookListTbl
      WHERE BookListTbl.BookID = p_bookID 
	  AND BookListTbl.ListID = p_listID;
    END;
END;
$function$
;

-- removememberfromstoryexcludetbl
DROP FUNCTION IF EXISTS removememberfromstoryexcludetbl;
CREATE OR REPLACE FUNCTION public.removememberfromstoryexcludetbl(p_memberid integer DEFAULT NULL::integer, p_storyid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    delete from ExcludeTbl
	where ExcludeUser = p_memberID and
	storyID = p_storyID;
    END;
END;
$function$
;

-- removememorycompletely
DROP FUNCTION IF EXISTS removememorycompletely;
CREATE OR REPLACE FUNCTION public.removememorycompletely(p_storyid integer DEFAULT NULL::integer, p_bookid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM BookContentTbl
      WHERE BookID = p_bookID and StoryID = p_storyID;
    DELETE FROM MediaTbl 
	where StoryID = p_storyID;
    DELETE FROM StoryTbl 
	where StoryID = p_storyID;
    END;
END;
$function$
;

-- removememoryfrombook
DROP FUNCTION IF EXISTS removememoryfrombook;
CREATE OR REPLACE FUNCTION public.removememoryfrombook(p_storyid integer DEFAULT NULL::integer, p_bookid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE FROM BookContentTbl
      WHERE BookID = p_bookID and StoryID = p_storyID;
    UPDATE StoryTbl
SET hidden= 0
WHERE StoryID = p_storyID;
    END;
END;
$function$
;

-- removenewmedia
DROP FUNCTION IF EXISTS removenewmedia;
CREATE OR REPLACE FUNCTION public.removenewmedia(p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    DELETE from MediaTbl
	where UserID = p_UserID AND StoryID = 0;
    END;
END;
$function$
;

-- resetcheck
DROP FUNCTION IF EXISTS resetcheck;
CREATE OR REPLACE FUNCTION public.resetcheck(p_email character varying, p_verifycode character varying, OUT verifyresult character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_resetDate timestamp;
BEGIN
    SELECT ForgotDate INTO v_resetDate 
    FROM ForgotPWTbl
    WHERE ForgotEmail = p_email AND ForgotUUID = p_verifyCode;

    IF v_resetDate IS NULL OR (v_resetDate + INTERVAL '1 hour' <= timezone('UTC', now())) THEN
        verifyResult := 'Expired';
    ELSE
        verifyResult := 'Valid';
    END IF;
END;
$function$
;

-- resetpassword
DROP FUNCTION IF EXISTS resetpassword;
CREATE OR REPLACE FUNCTION public.resetpassword(p_useremail character varying, p_userhash character varying)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE UserTbl
    SET UserHash = p_UserHash
    WHERE UserEmail = p_UserEmail;
    
    RETURN 1;
END;
$function$
;

-- saveastemplate
DROP FUNCTION IF EXISTS saveastemplate;
CREATE OR REPLACE FUNCTION public.saveastemplate(p_listid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_NewRecordID INT;
BEGIN
    BEGIN
    INSERT INTO ListTbl (ListName, ListColor, ListOwner, ListCircle, ListDate, ListCircleRights, ListAssigned, ListType)
SELECT ListName, ListColor, ListOwner, ListCircle, ListDate, ListCircleRights, ListAssigned, 'TEMPLATE'
FROM ListTbl
WHERE ListID = p_listID;
    v_NewRecordID := lastval();
    INSERT INTO ListItemTbl(Item, ItemStatus, DueDate, AssignedTo, Notes, ListID)
SELECT Item, ItemStatus, DueDate, AssignedTo, Notes, v_NewRecordID
FROM ListItemTbl
WHERE ListID = p_listID;
    END;
END;
$function$
;

-- savemyanswer
DROP FUNCTION IF EXISTS savemyanswer;
CREATE OR REPLACE FUNCTION public.savemyanswer(p_userid integer DEFAULT NULL::integer, p_qcid integer DEFAULT NULL::integer, p_myanswer character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_isanswered int;
BEGIN
    BEGIN
    SELECT count(*) INTO v_isanswered FROM VPAnswerTbl where userID = p_userID and ViewPointID = p_qcID;
    IF v_isanswered > 0 THEN
    BEGIN
    UPDATE VPAnswerTbl
		   SET VPAnswer = p_myanswer
		 WHERE userID = p_userID and ViewPointID = p_qcID;
    END;
    ELSE
    BEGIN
    INSERT INTO VPAnswerTbl
						   (VPAnswer
						   ,UserID
						   ,ViewPointID)
					 VALUES
						   (p_myanswer
						   ,p_userID
						   ,p_qcID);
    END; END IF;
    END;
END;
$function$
;

-- savemyqcanswer
DROP FUNCTION IF EXISTS savemyqcanswer;
CREATE OR REPLACE FUNCTION public.savemyqcanswer(p_userid integer DEFAULT NULL::integer, p_qcanswer text DEFAULT NULL::text, p_qcid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO VPAnswerTbl
                        (VPAnswer, UserID, ViewPointID)
                        VALUES(
                        p_qcAnswer,
                        p_userID, 
                        p_qcID
                                    );
    END;
END;
$function$
;

-- saveqcquestion
DROP FUNCTION IF EXISTS saveqcquestion;
CREATE OR REPLACE FUNCTION public.saveqcquestion(p_qcuserid integer DEFAULT NULL::integer, p_qcdate date DEFAULT NULL::date, p_qcquestion character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO ViewPointTbl
           (ViewPointQ
           ,ViewPointDate
           ,QCUserID)
     VALUES
           (p_qcQuestion
           ,p_qcDate
           ,p_qcUserID);
    END;
END;
$function$
;

-- savetemplate
DROP FUNCTION IF EXISTS savetemplate;
CREATE OR REPLACE FUNCTION public.savetemplate(p_userid integer DEFAULT NULL::integer, p_typeid integer DEFAULT NULL::integer, p_templatename character varying DEFAULT NULL::character varying, p_templatetext text DEFAULT NULL::text, p_templateingredients text DEFAULT NULL::text, p_templatecircle integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    INSERT INTO TemplateTbl
           (MemoryTemplateType
           ,MemoryTemplateName
           ,MemoryTemplateUserID
           ,MemoryText
		   ,MemoryIngredients
		   ,MemoryCircle)
     VALUES
           (p_typeID
           ,p_templateName
           ,p_userID
		   ,p_templateText
		   ,p_templateIngredients
		   ,p_templateCircle);
    END;
END;
$function$
;

-- searchmembers
DROP FUNCTION IF EXISTS searchmembers;
CREATE OR REPLACE FUNCTION public.searchmembers(p_email character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "UserEmail" text,
    "UserID" integer)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select 
			trim(userTbl.UserEmail) AS UserEmail, 
			UserID
			FROM UserTbl

			where
			UserTbl.UserEmail = p_email
				and
				 UserTbl.ValidationDate IS NOT NULL
				and
				(UserSubExpiration > CURRENT_TIMESTAMP OR UserSubExpiration IS NULL);
    END;
END;
$function$
;

-- searchmymemories
DROP FUNCTION IF EXISTS searchmymemories;
CREATE OR REPLACE FUNCTION public.searchmymemories(p_userid integer DEFAULT NULL::integer, p_searchterm character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "StoryDate" timestamp without time zone,
    "Interviewee" character varying,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText,
            StoryTbl.Userid, StoryTbl.StoryTypeID, Storytbl.StoryDate,
            StoryTbl.Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as
            UserDisplayName, StoryTbl.StoryIngredients, UserTbl.UserMediaLoc
            from StoryTbl
            left join UserTbl
            on StoryTbl.UserID = UserTbl.UserID
            where

            FREETEXT((StoryTitle, StoryText, StoryIngredients, Interviewee), p_searchTerm) 
            AND  
            (
            StoryTbl.storyid > 0

            AND StoryTypeID >= 1

            OR (


            StoryTypeID >= 1

            AND
            (
            (CircleID in
            (select CircleID from CircleMemberTbl
            where MemberID = p_userID) )

            AND ( StoryID not in
            (Select StoryID from ExcludeTbl
            where ExcludeUser = p_userID)
            )
            ) )

            )

            order by StoryDate Desc, StoryTitle;
    END;
END;
$function$
;

-- searchstories
DROP FUNCTION IF EXISTS searchstories;
CREATE OR REPLACE FUNCTION public.searchstories(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer, p_searchterm character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
            left join UserTbl
            on StoryTbl.UserID = UserTbl.UserID
            where

            to_tsvector('english', coalesce(StoryTitle::text, '') || ' ' || coalesce(StoryText::text, '') || ' ' || coalesce(StoryIngredients::text, '') || ' ' || coalesce(Interviewee::text, '')) @@ plainto_tsquery('english', p_searchTerm) 
            AND  
            (
            StoryTbl.UserID = p_userID 
			OR
            StoryTbl.CircleID in
            (select CircleID from CircleMemberTbl
            where MemberID = p_userID) );
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

            where

            to_tsvector('english', coalesce(StoryTitle::text, '') || ' ' || coalesce(StoryText::text, '') || ' ' || coalesce(StoryIngredients::text, '') || ' ' || coalesce(Interviewee::text, '')) @@ plainto_tsquery('english', p_searchTerm) 
            AND  
            (
            StoryTbl.UserID = p_userID 
			OR
            StoryTbl.CircleID in
            (select CircleID from CircleMemberTbl
            where MemberID = p_userID) )       

            order by StoryDate Desc, StoryTitle

			OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- searchstoriesold
DROP FUNCTION IF EXISTS searchstoriesold;
CREATE OR REPLACE FUNCTION public.searchstoriesold(p_userid integer DEFAULT NULL::integer, p_recordstart integer DEFAULT NULL::integer, p_pagelength integer DEFAULT NULL::integer, p_searchterm character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "recordCount" integer,
    "StoryID" integer,
    "StoryTitle" text,
    "StoryText" text,
    "Userid" integer,
    "StoryTypeID" integer,
    "date" text,
    "Interviewee" text,
    "CircleID" integer,
    "UserDisplayName" text,
    "StoryIngredients" character varying,
    "UserMediaLoc" varchar(100),
    "MediaLoc" character varying,
    "MediaID" integer,
    "FeatureMedia" integer,
    "MediaType" character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_recordCount INT := NULL;
BEGIN
    BEGIN
    SELECT COUNT(*) INTO v_recordCount FROM StoryTbl
            left join UserTbl
            on StoryTbl.UserID = UserTbl.UserID
            where

            FREETEXT((StoryTitle, StoryText, StoryIngredients, Interviewee), p_searchTerm) 
            AND  
            (
            StoryTbl.UserID = p_userID 
			OR
            StoryTbl.CircleID in
            (select CircleID from CircleMemberTbl
            where MemberID = p_userID) );
    RETURN QUERY Select v_recordCount, StoryTbl.StoryID, trim(StoryTbl.StoryTitle) as StoryTitle, trim(Storytbl.StoryText) as StoryText, StoryTbl.Userid, StoryTbl.StoryTypeID, to_char(Storytbl.StoryDate, 'Mon DD, YYYY') as date, trim(StoryTbl.Interviewee) as Interviewee, StoryTbl.CircleID, trim(UserTbl.UserDisplayName) as UserDisplayName, StoryTbl.StoryIngredients, UserTbl.UserMediaLoc, MediaTbl.MediaLoc, MediaTbl.MediaID, mediaTbl.FeatureMedia, mediaTbl.MediaType from StoryTbl
               left join UserTbl
               on StoryTbl.UserID = UserTbl.UserID
               left join MediaTbl
               on (StoryTbl.StoryID = MediaTbl.StoryID AND MediaTbl.FeatureMedia = 1)

            where

            FREETEXT((StoryTitle, StoryText, StoryIngredients, Interviewee), p_searchTerm) 
            AND  
            (
            StoryTbl.UserID = p_userID 
			OR
            StoryTbl.CircleID in
            (select CircleID from CircleMemberTbl
            where MemberID = p_userID) )       

            order by StoryDate Desc, StoryTitle

			OFFSET p_recordStart LIMIT p_pageLength;
    END;
END;
$function$
;

-- searchtoaddtoeveryoneemail
DROP FUNCTION IF EXISTS searchtoaddtoeveryoneemail;
CREATE OR REPLACE FUNCTION public.searchtoaddtoeveryoneemail(p_userid integer DEFAULT NULL::integer, p_searchterm character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "UserEmail" text,
    "UserID" integer,
    "PrimaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primaryCircle int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primaryCircle FROM CircleTbl where CirclePrimary = p_userID;
    RETURN QUERY Select distinct 
			trim(userTbl.UserEmail) AS UserEmail,
			UserTbl.UserID, v_primaryCircle AS PrimaryCircle
			FROM UserTbl
			left join CircleMemberTbl
			on UserTbl.Userid = CircleMemberTbl.MemberID
			where
			UserTbl.Userid <> p_userID

				and UserTbl.ValidationDate IS NOT NULL
				and
				(UserEmail = trim(p_searchTerm))

				order by UserEmail;
    END;
END;
$function$
;

-- searchtoaddtoeveryonename
DROP FUNCTION IF EXISTS searchtoaddtoeveryonename;
CREATE OR REPLACE FUNCTION public.searchtoaddtoeveryonename(p_userid integer DEFAULT NULL::integer, p_firstname character varying DEFAULT NULL::character varying, p_lastname character varying DEFAULT NULL::character varying)
 RETURNS TABLE(
    "UserDisplayName" text,
    "UserLast" text,
    "UserFirst" text,
    "UserMediaLoc" varchar(100),
    "UserID" integer,
    "PrimaryCircle" integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_primaryCircle int;
BEGIN
    BEGIN
    SELECT CircleID INTO v_primaryCircle FROM CircleTbl where CirclePrimary = p_userID;
    RETURN QUERY Select distinct trim(UserTbl.UserDisplayName) AS UserDisplayName,
			trim(UserTbl.UserLast) AS UserLast, trim(UserTbl.UserFirst) AS UserFirst,
			UserTbl.UserMediaLoc,
			UserTbl.UserID, v_primaryCircle AS PrimaryCircle
			FROM UserTbl
			left join CircleMemberTbl
			on UserTbl.Userid = CircleMemberTbl.MemberID
			where
			UserTbl.Userid <> p_userID

				and UserTbl.ValidationDate IS NOT NULL
				and
				(UserFirst like p_firstname
				OR
				UserLast like p_lastname
					)


				order by UserLast, UserFirst;
    END;
END;
$function$
;

-- setexportdone
DROP FUNCTION IF EXISTS setexportdone;
CREATE OR REPLACE FUNCTION public.setexportdone(p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    update UserTbl
            set ExportStarted= 0
            where UserID = p_userID;
    END;
END;
$function$
;

-- setexportstarted
DROP FUNCTION IF EXISTS setexportstarted;
CREATE OR REPLACE FUNCTION public.setexportstarted(p_userid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    update UserTbl
            set ExportStarted= 1
            where UserID = p_userID;
    END;
END;
$function$
;

-- setfeaturemedia
DROP FUNCTION IF EXISTS setfeaturemedia;
CREATE OR REPLACE FUNCTION public.setfeaturemedia(p_storyid integer DEFAULT NULL::integer, p_userid integer DEFAULT NULL::integer, p_mediaid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    update MediaTbl
            set FeatureMedia = 2
            where storyID = p_storyID and UserID = p_userID;
    update MediaTbl
            set FeatureMedia = 1
            where MediaID = p_mediaID and UserID = p_userID;
    END;
END;
$function$
;

-- storymemberliststatus
DROP FUNCTION IF EXISTS storymemberliststatus;
CREATE OR REPLACE FUNCTION public.storymemberliststatus(p_circleid integer DEFAULT NULL::integer, p_storyid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "MemberID" integer,
    "USERDISPLAYNAME" text,
    "ExcStatus" text,
    "Last" text,
    "First" text)
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CircleMemberTbl.MemberID, trim(UserTbl.UserDisplayName) AS USERDISPLAYNAME, 'In' AS
					ExcStatus,
					trim(UserTbl.UserLast) AS Last, trim(UserTbl.UserFirst) AS First
					FROM CircleMemberTbl
					left join CircleTbl
					on CircleMemberTbl.CircleID = CircleTbl.CircleID
					join UserTbl
					on CircleMemberTbl.MemberID = UserTbl.UserID
					where CircleTbl.CircleID = p_circleID
					AND CircleMemberTbl.Status <> 'Invited'
					AND CircleMemberTbl.MemberID not in
					(select ExcludeUser from ExcludeTbl
					where storyID = p_storyID)

					UNION

					Select CircleMemberTbl.MemberID, trim(UserTbl.UserDisplayName) AS USERDISPLAYNAME, 'Out' AS
					ExcStatus,
					trim(UserTbl.UserLast) AS Last, trim(UserTbl.UserFirst) AS First
					FROM CircleMemberTbl
					left join CircleTbl
					on CircleMemberTbl.CircleID = CircleTbl.CircleID
					join UserTbl
					on CircleMemberTbl.MemberID = UserTbl.UserID
					where CircleTbl.CircleID = p_circleID
					AND CircleMemberTbl.Status <> 'Invited'
					AND CircleMemberTbl.MemberID in
					(select ExcludeUser from ExcludeTbl
					where storyID = p_storyID)

					Order by Last, First;
    END;
END;
$function$
;

-- successfulpayment
DROP FUNCTION IF EXISTS successfulpayment;
CREATE OR REPLACE FUNCTION public.successfulpayment(p_customer character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    IF (Select UserSubType from UserTbl where StripeCustomer = p_customer) = 9004 THEN
    BEGIN
    UPDATE UserTbl
		   SET UserSubExpiration = (CURRENT_TIMESTAMP + INTERVAL '1 month'),
		   UserSubType = 1
		 WHERE StripeCustomer = p_customer;
    END;
    ELSE
    BEGIN
    UPDATE UserTbl
		   SET UserSubExpiration = (CURRENT_TIMESTAMP + INTERVAL '1 month')
		 WHERE StripeCustomer = p_customer;
    END; END IF;
    END;
END;
$function$
;

-- unacceptedinvitationsforme
DROP FUNCTION IF EXISTS unacceptedinvitationsforme;
CREATE OR REPLACE FUNCTION public.unacceptedinvitationsforme(p_userid integer DEFAULT NULL::integer)
 RETURNS TABLE(
    "CircleMemID" integer,
    "MemberID" integer,
    "status" varchar(50),
    "InvType" text,
    "InvitedBy" integer,
    "AcceptedDate" timestamp without time zone,
    "CircleOwner" integer,
    "CirclePrimary" integer,
    "UserDisplayName" varchar(110),
    "UserMediaLoc" varchar(100))
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    RETURN QUERY Select CircleMemberTbl.CircleMemID, CircleMemberTbl.MemberID, CircleMemberTbl.status, 'received' as InvType,
	CircleMemberTbl.InvitedBy, CircleMemberTbl.AcceptedDate, CircleTbl.CircleOwner, CircleTbl.CirclePrimary, 
	UserTbl.UserDisplayName, UserTbl.UserMediaLoc
from CircleMemberTbl
Join CircleTbl on CircleMemberTbl.CircleID = CircleTbl.CircleId 
join UserTbl on CircleTbl.CircleOwner = UserTbl.UserID

where MemberID = p_userID 
and Status is null
and CircleOwner = CirclePrimary


order by InvType;
    END;
END;
$function$
;

-- updatecirclename
DROP FUNCTION IF EXISTS updatecirclename;
CREATE OR REPLACE FUNCTION public.updatecirclename(p_circleid integer DEFAULT NULL::integer, p_circlename character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE CircleTbl
SET CircleName = p_circleName
WHERE CircleID = p_circleID;
    END;
END;
$function$
;

-- updatehelp
DROP FUNCTION IF EXISTS updatehelp;
CREATE OR REPLACE FUNCTION public.updatehelp(p_storyid integer DEFAULT NULL::integer, p_storytitle text DEFAULT NULL::text, p_storytext text DEFAULT NULL::text, p_storytypeid integer DEFAULT NULL::integer, p_storyingredients text DEFAULT NULL::text, p_interviewee character varying DEFAULT NULL::character varying, p_circleid integer DEFAULT NULL::integer, p_helptypeid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE StoryTbl
   SET StoryTitle = p_storyTitle
      ,StoryText = p_storyText
      ,StoryTypeID = p_storyTypeID
      ,StoryIngredients = p_storyIngredients
      ,Interviewee = p_interviewee
	  ,CircleID = p_circleID
	  ,HelpTypeID = p_helpTypeID
 WHERE StoryID = p_storyID;
    END;
END;
$function$
;

-- updateinterviewquestion
DROP FUNCTION IF EXISTS updateinterviewquestion;
CREATE OR REPLACE FUNCTION public.updateinterviewquestion(p_interviewid integer DEFAULT NULL::integer, p_intcategory integer DEFAULT NULL::integer, p_intquestion text DEFAULT NULL::text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE InterviewTbl
   SET IntQuestion = p_intQuestion
      ,IntCategory = p_intCategory
 WHERE InterviewID = p_interviewID;
    END;
END;
$function$
;

-- updatememory
DROP FUNCTION IF EXISTS updatememory;
CREATE OR REPLACE FUNCTION public.updatememory(p_storyid integer DEFAULT NULL::integer, p_storytitle text DEFAULT NULL::text, p_storytext text DEFAULT NULL::text, p_storytypeid integer DEFAULT NULL::integer, p_storyingredients text DEFAULT NULL::text, p_interviewee character varying DEFAULT NULL::character varying, p_circleid integer DEFAULT NULL::integer, p_helptypeid integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE StoryTbl
   SET StoryTitle = p_storyTitle
      ,StoryText = p_storyText
      ,StoryTypeID = p_storyTypeID
      ,StoryIngredients = p_storyIngredients
      ,Interviewee = p_interviewee
	  ,HelpTypeID = null
	  ,Hidden = 0

	  ,CircleID = p_circleID
 WHERE StoryID = p_storyID;
    END;
END;
$function$
;

-- updatememory2
DROP FUNCTION IF EXISTS updatememory2;
CREATE OR REPLACE FUNCTION public.updatememory2(p_storyid integer DEFAULT NULL::integer, p_storytitle text DEFAULT NULL::text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE StoryTbl
   SET StoryTitle = p_storyTitle

 WHERE StoryID = 66;
    END;
END;
$function$
;

-- updatemyanswer
DROP FUNCTION IF EXISTS updatemyanswer;
CREATE OR REPLACE FUNCTION public.updatemyanswer(p_userid integer DEFAULT NULL::integer, p_qcid integer DEFAULT NULL::integer, p_myanswer character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    BEGIN
    UPDATE VPAnswerTbl
		   SET VPAnswer = p_myanswer
		 WHERE userID = p_userID and VPAnswerID = p_qcID;
    END;
    END;
END;
$function$
;

-- updatenews
DROP FUNCTION IF EXISTS updatenews;
CREATE OR REPLACE FUNCTION public.updatenews(p_newsid integer DEFAULT NULL::integer, p_newstitle character varying DEFAULT NULL::character varying, p_newstext text DEFAULT NULL::text, p_newsdate date DEFAULT NULL::date, p_newssubtype integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE NewsTbl
    SET
        NewsTitle = p_newsTitle,
        NewsText = p_newsText,
        NewsDate = p_newsDate,
        NewsSubType = p_newsSubType
    WHERE NewsID = p_newsID;
    END;
END;
$function$
;

-- updatepassword
DROP FUNCTION IF EXISTS updatepassword;
CREATE OR REPLACE FUNCTION public.updatepassword(p_userid integer, p_userhash character varying)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE UserTbl
    SET UserHash = p_UserHash
    WHERE UserID = p_UserID;
    
    RETURN 1;
END;
$function$
;

-- updatestripeuser
DROP FUNCTION IF EXISTS updatestripeuser;
CREATE OR REPLACE FUNCTION public.updatestripeuser(p_userid integer DEFAULT NULL::integer, p_stripecustomer character varying DEFAULT NULL::character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    BEGIN
    UPDATE UserTbl
   SET StripeCustomer = p_StripeCustomer


 WHERE UserID = p_UserID;
    END;
END;
$function$
;

-- updateuser
DROP FUNCTION IF EXISTS updateuser;
CREATE OR REPLACE FUNCTION public.updateuser(p_userid integer, p_userfirst character varying, p_userlast character varying, p_userdisplayname character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE UserTbl
    SET UserFirst = p_UserFirst,
        UserLast = p_UserLast,
        UserDisplayName = p_UserDisplayName
    WHERE UserID = p_UserID;
END;
$function$
;

-- usplogerror
DROP FUNCTION IF EXISTS usplogerror;
CREATE OR REPLACE FUNCTION public.usplogerror(INOUT p_errorlogid integer DEFAULT NULL::integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN
    RAISE NOTICE 'uspLogError called';
    p_ErrorLogID := 0;
    RETURN;
END;
$function$
;

-- uspprinterror
DROP FUNCTION IF EXISTS uspprinterror;
CREATE OR REPLACE FUNCTION public.uspprinterror()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    RAISE NOTICE 'uspPrintError called';
END;
$function$
;

-- validateuser
DROP FUNCTION IF EXISTS validateuser;
CREATE OR REPLACE FUNCTION public.validateuser(p_useremail character varying)
 RETURNS TABLE(userhash character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT u.UserHash 
    FROM UserTbl u 
    WHERE u.UserEmail = p_userEmail 
      AND u.ValidationDate IS NOT NULL 
      AND (u.forgotPW IS NULL OR u.forgotPW <= CURRENT_TIMESTAMP + INTERVAL '3 hours');
END;
$function$
;

-- validateuserid
DROP FUNCTION IF EXISTS validateuserid;
CREATE OR REPLACE FUNCTION public.validateuserid(p_userid integer)
 RETURNS TABLE(userhash character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT u.UserHash 
    FROM UserTbl u 
    WHERE u.UserID = p_userID 
      AND u.ValidationDate IS NOT NULL 
      AND u.UserSubType < 9000;
END;
$function$
;

-- verifycode
DROP FUNCTION IF EXISTS verifycode;
CREATE OR REPLACE FUNCTION public.verifycode(p_userid integer, p_verifycode character varying)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE UserTbl
    SET ValidationDate = CURRENT_TIMESTAMP
    WHERE UserID = p_userID AND VerifyHash = p_verifyCode;
    
    RETURN 0;
EXCEPTION WHEN OTHERS THEN
    RETURN 1;
END;
$function$
;