-- 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;

-- ----------------------------------------------------
-- 4. CREATE PL/pgSQL FUNCTIONS
-- ----------------------------------------------------

-- getUser
CREATE OR REPLACE FUNCTION getUser(p_userEmail varchar(75))
RETURNS TABLE (
    UserID int,
    UserFirst varchar(50),
    UserLast varchar(50),
    UserDisplayName varchar(110),
    UserEmail varchar(75),
    ValidationDate timestamp,
    AdminLevel int,
    UserMediaLoc varchar(100),
    UserSubType int,
    UserSubExpiration date,
    UserTZ varchar(75),
    TCAcceptDate timestamp,
    StripeCustomer varchar(100),
    StripeSubscription varchar(100),
    CancelID int,
    LastExport date
) AS $$
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;
$$ LANGUAGE plpgsql;

-- validateUser
CREATE OR REPLACE FUNCTION validateUser(p_userEmail varchar(75))
RETURNS TABLE (
    UserHash varchar(128)
) AS $$
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;
$$ LANGUAGE plpgsql;

-- validateUserID
CREATE OR REPLACE FUNCTION validateUserID(p_userID int)
RETURNS TABLE (
    UserHash varchar(128)
) AS $$
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;
$$ LANGUAGE plpgsql;

-- getUserByID
CREATE OR REPLACE FUNCTION getUserByID(p_userID int)
RETURNS TABLE (
    UserID int,
    UserFirst varchar(50),
    UserLast varchar(50),
    UserDisplayName varchar(110),
    UserEmail varchar(75),
    ValidationDate timestamp,
    AdminLevel int,
    UserMediaLoc varchar(100),
    UserSubType int,
    UserSubExpiration date,
    StripeCustomer varchar(100),
    StripeSubscription varchar(100)
) AS $$
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;
$$ LANGUAGE plpgsql;

-- getUserInfo
CREATE OR REPLACE FUNCTION getUserInfo(p_userID int)
RETURNS TABLE (
    UserID int,
    UserEmail varchar(75),
    UserFirst varchar(50),
    UserLast varchar(50),
    UserDisplayName varchar(110),
    ValidationDate timestamp,
    AdminLevel int,
    UserMediaLoc varchar(100),
    UserSubType int,
    UserSubTypeID int,
    UserSubExpiration date,
    UserTZ varchar(75),
    TCAcceptDate timestamp,
    StripeCustomer varchar(100),
    StripeSubscription varchar(100),
    CancelID int,
    LastExport date
) AS $$
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;
$$ LANGUAGE plpgsql;

-- getUserEmail
CREATE OR REPLACE FUNCTION getUserEmail(p_userID int)
RETURNS TABLE (
    UserEmail varchar(75)
) AS $$
BEGIN
    RETURN QUERY
    SELECT u.UserEmail 
    FROM UserTbl u 
    WHERE u.UserID = p_userID;
END;
$$ LANGUAGE plpgsql;

-- getUserByName
CREATE OR REPLACE FUNCTION getUserByName(p_firstName varchar(50), p_lastName varchar(50))
RETURNS TABLE (
    UserID int,
    UserFirst varchar(50),
    UserLast varchar(50),
    UserDisplayName varchar(110),
    UserEmail varchar(75),
    ValidationDate timestamp,
    AdminLevel int,
    UserMediaLoc varchar(100),
    UserSubType int,
    UserSubExpiration date,
    UserTZ varchar(75),
    TCAcceptDate timestamp,
    StripeCustomer varchar(100),
    StripeSubscription varchar(100),
    JoinDate timestamp
) AS $$
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;
$$ LANGUAGE plpgsql;

-- getSubTypes
CREATE OR REPLACE FUNCTION getSubTypes()
RETURNS TABLE (
    UserSubTypeID int,
    UserSubType varchar(50)
) AS $$
BEGIN
    RETURN QUERY
    SELECT u.UserSubTypeID, u.UserSubType 
    FROM UserSubTypeTbl u
    ORDER BY u.UserSubTypeID;
END;
$$ LANGUAGE plpgsql;

-- dupCheck
CREATE OR REPLACE FUNCTION dupCheck(p_userEmail varchar(75))
RETURNS int AS $$
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;
$$ LANGUAGE plpgsql;

-- verifyCode
CREATE OR REPLACE FUNCTION verifyCode(
    p_userID int,
    p_verifyCode varchar(128)
) RETURNS int AS $$
BEGIN
    UPDATE UserTbl
    SET ValidationDate = CURRENT_TIMESTAMP
    WHERE UserID = p_userID AND VerifyHash = p_verifyCode;
    
    RETURN 0;
EXCEPTION WHEN OTHERS THEN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

-- forgotPW
CREATE OR REPLACE FUNCTION forgotPW(
    p_email varchar(75),
    p_hash varchar(128)
) RETURNS void AS $$
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;
$$ LANGUAGE plpgsql;

-- resetCheck
CREATE OR REPLACE FUNCTION resetCheck(
    p_email varchar(75),
    p_verifyCode varchar(128),
    OUT verifyResult varchar(10)
) RETURNS varchar(10) AS $$
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;
$$ LANGUAGE plpgsql;

-- resetPassword
CREATE OR REPLACE FUNCTION resetPassword(
    p_UserEmail varchar(75),
    p_UserHash varchar(128)
) RETURNS int AS $$
BEGIN
    UPDATE UserTbl
    SET UserHash = p_UserHash
    WHERE UserEmail = p_UserEmail;
    
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

-- updateUser
CREATE OR REPLACE FUNCTION updateUser(
    p_UserID int, 
    p_UserFirst varchar(50),
    p_UserLast varchar(50),
    p_UserDisplayName varchar(110)
) RETURNS void AS $$
BEGIN
    UPDATE UserTbl
    SET UserFirst = p_UserFirst,
        UserLast = p_UserLast,
        UserDisplayName = p_UserDisplayName
    WHERE UserID = p_UserID;
END;
$$ LANGUAGE plpgsql;

-- updatePassword
CREATE OR REPLACE FUNCTION updatePassword(
    p_userID int,
    p_UserHash varchar(128)
) RETURNS int AS $$
BEGIN
    UPDATE UserTbl
    SET UserHash = p_UserHash
    WHERE UserID = p_UserID;
    
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

-- adminEditUser
CREATE OR REPLACE FUNCTION adminEditUser(
    p_userID int,
    p_subType int, 
    p_subExpiration varchar(10)
) RETURNS void AS $$
BEGIN
    UPDATE UserTbl
    SET UserSubExpiration = p_subExpiration::date,
        UserSubType = p_subType
    WHERE UserTbl.UserID = p_userID;
END;
$$ LANGUAGE plpgsql;

-- invitationVerification
CREATE OR REPLACE FUNCTION invitationVerification(
    p_userEmail varchar(75),
    p_invitationID int
) RETURNS TABLE (
    InvitationType int
) AS $$
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;
$$ LANGUAGE plpgsql;

-- newMonthlySubscriber
CREATE OR REPLACE FUNCTION newMonthlySubscriber(
    p_userEmail varchar(100),
    p_userHash varchar(128),
    p_userFirst varchar(50),
    p_userLast varchar(50),
    p_userDisplayName varchar(110),
    p_verifyHash varchar(128)
) RETURNS TABLE (
    UserID int,
    UserFirst varchar(50),
    UserLast varchar(50),
    UserDisplayName varchar(110),
    UserEmail varchar(75),
    ValidationDate timestamp,
    AdminLevel int,
    UserMediaLoc varchar(100),
    LastFeedView timestamp,
    LastBookView timestamp,
    LastCircleView timestamp
) AS $$
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;
$$ LANGUAGE plpgsql;

-- newInvitedMonthlySubscriber
CREATE OR REPLACE FUNCTION newInvitedMonthlySubscriber(
    p_userEmail varchar(100),
    p_userHash varchar(128),
    p_userFirst varchar(50),
    p_userLast varchar(50),
    p_userDisplayName varchar(110),
    p_verifyHash varchar(128),
    p_invitationID int
) RETURNS TABLE (
    UserID int,
    UserFirst varchar(50),
    UserLast varchar(50),
    UserDisplayName varchar(110),
    UserEmail varchar(75),
    ValidationDate timestamp,
    AdminLevel int,
    UserMediaLoc varchar(100),
    LastFeedView timestamp,
    LastBookView timestamp,
    LastCircleView timestamp
) AS $$
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;
$$ LANGUAGE plpgsql;

-- newInvitedSubscriber
CREATE OR REPLACE FUNCTION newInvitedSubscriber(
    p_userEmail varchar(100),
    p_userHash varchar(128),
    p_userFirst varchar(50),
    p_userLast varchar(50),
    p_userDisplayName varchar(110),
    p_verifyHash varchar(75),
    p_invitationType int,
    p_invitationID int
) RETURNS TABLE (
    UserID int,
    UserFirst varchar(50),
    UserLast varchar(50),
    UserDisplayName varchar(110),
    UserEmail varchar(75),
    ValidationDate timestamp,
    AdminLevel int,
    UserMediaLoc varchar(100),
    LastFeedView timestamp,
    LastBookView timestamp,
    LastCircleView timestamp
) AS $$
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;
$$ LANGUAGE plpgsql;