// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.27.0
// source: financial_groups_queries.sql

package database

import (
	"context"
	"database/sql"
	"time"
)

const adminDeleteGroupMember = `-- name: AdminDeleteGroupMember :one
DELETE FROM group_memberships gm_target
WHERE gm_target.group_id = $1
  AND gm_target.user_id = $2
  AND gm_target.role != 'admin' -- Prevent deletion of other admins
  AND EXISTS (
      SELECT 1
      FROM group_memberships gm_admin
      WHERE gm_admin.group_id = $1
        AND gm_admin.user_id = $3
        AND gm_admin.role = 'admin'
        AND gm_admin.status = 'accepted'
  )
RETURNING user_id
`

type AdminDeleteGroupMemberParams struct {
	GroupID  sql.NullInt64
	UserID   sql.NullInt64
	UserID_2 sql.NullInt64
}

// Admin can delete a member from a group who is not an admin
func (q *Queries) AdminDeleteGroupMember(ctx context.Context, arg AdminDeleteGroupMemberParams) (sql.NullInt64, error) {
	row := q.db.QueryRowContext(ctx, adminDeleteGroupMember, arg.GroupID, arg.UserID, arg.UserID_2)
	var user_id sql.NullInt64
	err := row.Scan(&user_id)
	return user_id, err
}

const checkIfGroupExistsAndUserIsMember = `-- name: CheckIfGroupExistsAndUserIsMember :one
SELECT g.id, g.name
FROM groups g
JOIN group_memberships gm ON g.id = gm.group_id
WHERE g.id = $1         -- Check if the group with this ID exists
  AND gm.user_id = $2  -- Check if this user is a member of the group
  AND gm.status = 'accepted'
`

type CheckIfGroupExistsAndUserIsMemberParams struct {
	ID     int64
	UserID sql.NullInt64
}

type CheckIfGroupExistsAndUserIsMemberRow struct {
	ID   int64
	Name string
}

func (q *Queries) CheckIfGroupExistsAndUserIsMember(ctx context.Context, arg CheckIfGroupExistsAndUserIsMemberParams) (CheckIfGroupExistsAndUserIsMemberRow, error) {
	row := q.db.QueryRowContext(ctx, checkIfGroupExistsAndUserIsMember, arg.ID, arg.UserID)
	var i CheckIfGroupExistsAndUserIsMemberRow
	err := row.Scan(&i.ID, &i.Name)
	return i, err
}

const checkIfGroupMembersAreMaxedOut = `-- name: CheckIfGroupMembersAreMaxedOut :one
SELECT COUNT(*) AS member_count, g.max_member_count
FROM group_memberships gm
JOIN groups g ON g.id = gm.group_id
WHERE gm.group_id = $1
GROUP BY g.max_member_count
`

type CheckIfGroupMembersAreMaxedOutRow struct {
	MemberCount    int64
	MaxMemberCount sql.NullInt32
}

func (q *Queries) CheckIfGroupMembersAreMaxedOut(ctx context.Context, groupID sql.NullInt64) (CheckIfGroupMembersAreMaxedOutRow, error) {
	row := q.db.QueryRowContext(ctx, checkIfGroupMembersAreMaxedOut, groupID)
	var i CheckIfGroupMembersAreMaxedOutRow
	err := row.Scan(&i.MemberCount, &i.MaxMemberCount)
	return i, err
}

const createNewGroupExpense = `-- name: CreateNewGroupExpense :one
INSERT INTO group_expenses (
    group_id, 
    member_id, 
    amount, 
    description, 
    category
    )
VALUES ($1, $2, $3, $4, $5)
RETURNING id, created_at, updated_at
`

type CreateNewGroupExpenseParams struct {
	GroupID     sql.NullInt64
	MemberID    sql.NullInt64
	Amount      string
	Description sql.NullString
	Category    sql.NullString
}

type CreateNewGroupExpenseRow struct {
	ID        int64
	CreatedAt sql.NullTime
	UpdatedAt sql.NullTime
}

func (q *Queries) CreateNewGroupExpense(ctx context.Context, arg CreateNewGroupExpenseParams) (CreateNewGroupExpenseRow, error) {
	row := q.db.QueryRowContext(ctx, createNewGroupExpense,
		arg.GroupID,
		arg.MemberID,
		arg.Amount,
		arg.Description,
		arg.Category,
	)
	var i CreateNewGroupExpenseRow
	err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt)
	return i, err
}

const createNewGroupGoal = `-- name: CreateNewGroupGoal :one
INSERT INTO group_goals (
    group_id, 
    creator_user_id, 
    goal_name,
    target_amount, 
    current_amount, 
    start_date,
    deadline, 
    description
) 
VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8
)
RETURNING id, created_at, updated_at
`

type CreateNewGroupGoalParams struct {
	GroupID       int64
	CreatorUserID int64
	GoalName      string
	TargetAmount  string
	CurrentAmount sql.NullString
	StartDate     time.Time
	Deadline      time.Time
	Description   string
}

type CreateNewGroupGoalRow struct {
	ID        int64
	CreatedAt time.Time
	UpdatedAt time.Time
}

func (q *Queries) CreateNewGroupGoal(ctx context.Context, arg CreateNewGroupGoalParams) (CreateNewGroupGoalRow, error) {
	row := q.db.QueryRowContext(ctx, createNewGroupGoal,
		arg.GroupID,
		arg.CreatorUserID,
		arg.GoalName,
		arg.TargetAmount,
		arg.CurrentAmount,
		arg.StartDate,
		arg.Deadline,
		arg.Description,
	)
	var i CreateNewGroupGoalRow
	err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt)
	return i, err
}

const createNewGroupInvitation = `-- name: CreateNewGroupInvitation :one
INSERT INTO group_invitations (
    group_id, inviter_user_id, invitee_user_email, status) 
VALUES ($1, $2, $3, $4)
RETURNING id, status, sent_at, expiration_date
`

type CreateNewGroupInvitationParams struct {
	GroupID          sql.NullInt64
	InviterUserID    sql.NullInt64
	InviteeUserEmail string
	Status           InvitationStatusType
}

type CreateNewGroupInvitationRow struct {
	ID             int64
	Status         InvitationStatusType
	SentAt         sql.NullTime
	ExpirationDate time.Time
}

func (q *Queries) CreateNewGroupInvitation(ctx context.Context, arg CreateNewGroupInvitationParams) (CreateNewGroupInvitationRow, error) {
	row := q.db.QueryRowContext(ctx, createNewGroupInvitation,
		arg.GroupID,
		arg.InviterUserID,
		arg.InviteeUserEmail,
		arg.Status,
	)
	var i CreateNewGroupInvitationRow
	err := row.Scan(
		&i.ID,
		&i.Status,
		&i.SentAt,
		&i.ExpirationDate,
	)
	return i, err
}

const createNewGroupTransaction = `-- name: CreateNewGroupTransaction :one
INSERT INTO group_transactions (
    goal_id,
    member_id, 
    amount, 
    description
) VALUES 
($1, $2, $3, $4)
RETURNING id, created_at, updated_at
`

type CreateNewGroupTransactionParams struct {
	GoalID      sql.NullInt64
	MemberID    sql.NullInt64
	Amount      string
	Description sql.NullString
}

type CreateNewGroupTransactionRow struct {
	ID        int64
	CreatedAt sql.NullTime
	UpdatedAt sql.NullTime
}

func (q *Queries) CreateNewGroupTransaction(ctx context.Context, arg CreateNewGroupTransactionParams) (CreateNewGroupTransactionRow, error) {
	row := q.db.QueryRowContext(ctx, createNewGroupTransaction,
		arg.GoalID,
		arg.MemberID,
		arg.Amount,
		arg.Description,
	)
	var i CreateNewGroupTransactionRow
	err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt)
	return i, err
}

const createNewPublicMembership = `-- name: CreateNewPublicMembership :one
INSERT INTO group_memberships (group_id, user_id, status, approval_time)
VALUES ($1, $2,'accepted', NOW())
RETURNING id, approval_time
`

type CreateNewPublicMembershipParams struct {
	GroupID sql.NullInt64
	UserID  sql.NullInt64
}

type CreateNewPublicMembershipRow struct {
	ID           int64
	ApprovalTime sql.NullTime
}

func (q *Queries) CreateNewPublicMembership(ctx context.Context, arg CreateNewPublicMembershipParams) (CreateNewPublicMembershipRow, error) {
	row := q.db.QueryRowContext(ctx, createNewPublicMembership, arg.GroupID, arg.UserID)
	var i CreateNewPublicMembershipRow
	err := row.Scan(&i.ID, &i.ApprovalTime)
	return i, err
}

const createNewUserGroup = `-- name: CreateNewUserGroup :one
INSERT INTO groups (
    creator_user_id, group_image_url, name, is_private, max_member_count, description
) VALUES 
($1, $2, $3, $4, $5, $6)
RETURNING id, creator_user_id, activity_count, last_activity_at, created_at, updated_at, version
`

type CreateNewUserGroupParams struct {
	CreatorUserID  sql.NullInt64
	GroupImageUrl  string
	Name           string
	IsPrivate      sql.NullBool
	MaxMemberCount sql.NullInt32
	Description    sql.NullString
}

type CreateNewUserGroupRow struct {
	ID             int64
	CreatorUserID  sql.NullInt64
	ActivityCount  sql.NullInt32
	LastActivityAt sql.NullTime
	CreatedAt      sql.NullTime
	UpdatedAt      sql.NullTime
	Version        sql.NullInt32
}

func (q *Queries) CreateNewUserGroup(ctx context.Context, arg CreateNewUserGroupParams) (CreateNewUserGroupRow, error) {
	row := q.db.QueryRowContext(ctx, createNewUserGroup,
		arg.CreatorUserID,
		arg.GroupImageUrl,
		arg.Name,
		arg.IsPrivate,
		arg.MaxMemberCount,
		arg.Description,
	)
	var i CreateNewUserGroupRow
	err := row.Scan(
		&i.ID,
		&i.CreatorUserID,
		&i.ActivityCount,
		&i.LastActivityAt,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.Version,
	)
	return i, err
}

const deleteGroupExpense = `-- name: DeleteGroupExpense :one
DELETE FROM group_expenses
WHERE id = $1 AND member_id = $2
RETURNING id
`

type DeleteGroupExpenseParams struct {
	ID       int64
	MemberID sql.NullInt64
}

func (q *Queries) DeleteGroupExpense(ctx context.Context, arg DeleteGroupExpenseParams) (int64, error) {
	row := q.db.QueryRowContext(ctx, deleteGroupExpense, arg.ID, arg.MemberID)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const deleteGroupTransaction = `-- name: DeleteGroupTransaction :one
DELETE FROM group_transactions
WHERE id = $1 AND member_id = $2
RETURNING id
`

type DeleteGroupTransactionParams struct {
	ID       int64
	MemberID sql.NullInt64
}

func (q *Queries) DeleteGroupTransaction(ctx context.Context, arg DeleteGroupTransactionParams) (int64, error) {
	row := q.db.QueryRowContext(ctx, deleteGroupTransaction, arg.ID, arg.MemberID)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const deleteNonPendingGroupInvitationsForUser = `-- name: DeleteNonPendingGroupInvitationsForUser :exec
DELETE FROM group_invitations
WHERE invitee_user_email = $1 AND group_id = $2 AND status != 'pending'
`

type DeleteNonPendingGroupInvitationsForUserParams struct {
	InviteeUserEmail string
	GroupID          sql.NullInt64
}

// deletes any non-pending group invitations for a user avoiding duplicates
func (q *Queries) DeleteNonPendingGroupInvitationsForUser(ctx context.Context, arg DeleteNonPendingGroupInvitationsForUserParams) error {
	_, err := q.db.ExecContext(ctx, deleteNonPendingGroupInvitationsForUser, arg.InviteeUserEmail, arg.GroupID)
	return err
}

const getAllGroupsCreatedByUser = `-- name: GetAllGroupsCreatedByUser :many
WITH user_groups AS (
    SELECT g.id, g.creator_user_id, g.group_image_url, g.name, g.is_private, g.max_member_count, g.description, g.activity_count, g.last_activity_at, g.created_at, g.updated_at, g.version
    FROM groups g
    WHERE g.creator_user_id = $1 
),

top_members AS (
    SELECT gm.group_id, gm.user_id, u.first_name, gm.role, u.profile_avatar_url,
           ROW_NUMBER() OVER (PARTITION BY gm.group_id ORDER BY gm.request_time DESC) AS row_num
    FROM group_memberships gm
    JOIN users u ON gm.user_id = u.id
    WHERE gm.status = 'accepted'
),

group_member_stats AS (
    SELECT gm.group_id,
           COUNT(*) AS total_members,
           MAX(u.id) AS latest_member_id,
           MAX(u.first_name) AS latest_member_first_name,
           MAX(u.profile_avatar_url) AS latest_member_avatar,
           MAX(gm.role) AS latest_member_role
    FROM group_memberships gm
    JOIN users u ON gm.user_id = u.id
    WHERE gm.status = 'accepted'
    GROUP BY gm.group_id
),

pending_invitations AS (
    SELECT gi.group_id, COUNT(*) AS total_pending_invitations
    FROM group_invitations gi
    WHERE gi.status = 'pending'
    GROUP BY gi.group_id
),

top_goals AS (
    SELECT gg.group_id, gg.goal_name, gg.target_amount, gg.current_amount,
           ROW_NUMBER() OVER (PARTITION BY gg.group_id ORDER BY gg.created_at DESC) AS row_num
    FROM group_goals gg
    WHERE gg.status = 'ongoing'
),

group_transaction_stats AS (
    SELECT gg.group_id,
           COUNT(gt.id) AS total_transactions,
           MAX(gt.amount)::NUMERIC AS latest_transaction_amount
    FROM group_transactions gt
    JOIN group_goals gg ON gt.goal_id = gg.id
    GROUP BY gg.group_id
)

SELECT ug.id, ug.creator_user_id, ug.group_image_url, ug.name, ug.is_private, ug.max_member_count, ug.description, ug.activity_count, ug.last_activity_at, ug.created_at, ug.updated_at, ug.version, 
       COALESCE(
           (SELECT jsonb_agg(jsonb_build_object('user_id', tm.user_id, 'first_name', tm.first_name, 'role', tm.role, 'profile_avatar_url', tm.profile_avatar_url))
            FROM top_members tm
            WHERE tm.group_id = ug.id AND tm.row_num <= 5), '[]'::jsonb) AS top_members,
       gms.total_members,
       COALESCE(jsonb_build_object(
           'user_id', gms.latest_member_id, 
           'first_name', gms.latest_member_first_name, 
           'role', gms.latest_member_role,
           'profile_avatar_url', gms.latest_member_avatar
       ), '{}'::jsonb) AS latest_member,
       COALESCE(pi.total_pending_invitations, 0)::NUMERIC AS total_pending_invitations,
       COALESCE(
           (SELECT jsonb_agg(jsonb_build_object('goal_name', tg.goal_name, 'target_amount', tg.target_amount, 'current_amount', tg.current_amount))
            FROM top_goals tg
            WHERE tg.group_id = ug.id AND tg.row_num <= 5), '[]'::jsonb) AS top_goals,
       COALESCE(gts.total_transactions, 0)::NUMERIC AS total_group_transactions,
       COALESCE(gts.latest_transaction_amount, 0)::NUMERIC AS latest_transaction_amount
FROM user_groups ug
LEFT JOIN group_member_stats gms ON ug.id = gms.group_id
LEFT JOIN pending_invitations pi ON ug.id = pi.group_id
LEFT JOIN group_transaction_stats gts ON ug.id = gts.group_id
`

type GetAllGroupsCreatedByUserRow struct {
	ID                      int64
	CreatorUserID           sql.NullInt64
	GroupImageUrl           string
	Name                    string
	IsPrivate               sql.NullBool
	MaxMemberCount          sql.NullInt32
	Description             sql.NullString
	ActivityCount           sql.NullInt32
	LastActivityAt          sql.NullTime
	CreatedAt               sql.NullTime
	UpdatedAt               sql.NullTime
	Version                 sql.NullInt32
	TopMembers              interface{}
	TotalMembers            sql.NullInt64
	LatestMember            interface{}
	TotalPendingInvitations string
	TopGoals                interface{}
	TotalGroupTransactions  string
	LatestTransactionAmount string
}

func (q *Queries) GetAllGroupsCreatedByUser(ctx context.Context, creatorUserID sql.NullInt64) ([]GetAllGroupsCreatedByUserRow, error) {
	rows, err := q.db.QueryContext(ctx, getAllGroupsCreatedByUser, creatorUserID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []GetAllGroupsCreatedByUserRow
	for rows.Next() {
		var i GetAllGroupsCreatedByUserRow
		if err := rows.Scan(
			&i.ID,
			&i.CreatorUserID,
			&i.GroupImageUrl,
			&i.Name,
			&i.IsPrivate,
			&i.MaxMemberCount,
			&i.Description,
			&i.ActivityCount,
			&i.LastActivityAt,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.Version,
			&i.TopMembers,
			&i.TotalMembers,
			&i.LatestMember,
			&i.TotalPendingInvitations,
			&i.TopGoals,
			&i.TotalGroupTransactions,
			&i.LatestTransactionAmount,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const getAllGroupsUserIsMemberOf = `-- name: GetAllGroupsUserIsMemberOf :many
WITH user_groups AS (
    SELECT g.id, g.creator_user_id, g.group_image_url, g.name, g.is_private, g.max_member_count, g.description, g.activity_count, g.last_activity_at, g.created_at, g.updated_at, g.version
    FROM groups g
    JOIN group_memberships gm ON g.id = gm.group_id
    WHERE gm.user_id = $1 AND g.creator_user_id != $1 AND gm.status = 'accepted'
),

top_members AS (
    SELECT gm.group_id, gm.user_id, u.first_name, gm.role, u.profile_avatar_url,
           ROW_NUMBER() OVER (PARTITION BY gm.group_id ORDER BY gm.request_time DESC) AS row_num
    FROM group_memberships gm
    JOIN users u ON gm.user_id = u.id
    WHERE gm.status = 'accepted'
),

group_member_stats AS (
    SELECT gm.group_id,
           COUNT(*) AS total_members,
           MAX(u.id) AS latest_member_id,
           MAX(u.first_name) AS latest_member_first_name,
           MAX(u.profile_avatar_url) AS latest_member_avatar,
           MAX(gm.role) AS latest_member_role
    FROM group_memberships gm
    JOIN users u ON gm.user_id = u.id
    WHERE gm.status = 'accepted'
    GROUP BY gm.group_id
),

top_goals AS (
    SELECT gg.group_id, gg.goal_name, gg.target_amount, gg.current_amount,
           ROW_NUMBER() OVER (PARTITION BY gg.group_id ORDER BY gg.created_at DESC) AS row_num
    FROM group_goals gg
    WHERE gg.status = 'ongoing'
),

group_transaction_stats AS (
    SELECT gg.group_id,
           COUNT(gt.id) AS total_transactions,
           MAX(gt.amount)::NUMERIC AS latest_transaction_amount
    FROM group_transactions gt
    JOIN group_goals gg ON gt.goal_id = gg.id
    GROUP BY gg.group_id
)

SELECT ug.id, ug.creator_user_id, ug.group_image_url, ug.name, ug.is_private, ug.max_member_count, ug.description, ug.activity_count, ug.last_activity_at, ug.created_at, ug.updated_at, ug.version, 
       COALESCE(
           (SELECT jsonb_agg(jsonb_build_object('user_id', tm.user_id, 'first_name', tm.first_name, 'role', tm.role, 'profile_avatar_url', tm.profile_avatar_url))
            FROM top_members tm
            WHERE tm.group_id = ug.id AND tm.row_num <= 5), '[]'::jsonb) AS top_members,
       gms.total_members,
       COALESCE(jsonb_build_object(
           'user_id', gms.latest_member_id, 
           'first_name', gms.latest_member_first_name, 
           'role', gms.latest_member_role,
           'profile_avatar_url', gms.latest_member_avatar
       ), '{}'::jsonb) AS latest_member,
       COALESCE(
           (SELECT jsonb_agg(jsonb_build_object('goal_name', tg.goal_name, 'target_amount', tg.target_amount, 'current_amount', tg.current_amount))
            FROM top_goals tg
            WHERE tg.group_id = ug.id AND tg.row_num <= 5), '[]'::jsonb) AS top_goals,
       COALESCE(gts.total_transactions, 0)::NUMERIC AS total_group_transactions,
       COALESCE(gts.latest_transaction_amount, 0)::NUMERIC AS latest_transaction_amount
FROM user_groups ug
LEFT JOIN group_member_stats gms ON ug.id = gms.group_id
LEFT JOIN group_transaction_stats gts ON ug.id = gts.group_id
`

type GetAllGroupsUserIsMemberOfRow struct {
	ID                      int64
	CreatorUserID           sql.NullInt64
	GroupImageUrl           string
	Name                    string
	IsPrivate               sql.NullBool
	MaxMemberCount          sql.NullInt32
	Description             sql.NullString
	ActivityCount           sql.NullInt32
	LastActivityAt          sql.NullTime
	CreatedAt               sql.NullTime
	UpdatedAt               sql.NullTime
	Version                 sql.NullInt32
	TopMembers              interface{}
	TotalMembers            sql.NullInt64
	LatestMember            interface{}
	TopGoals                interface{}
	TotalGroupTransactions  string
	LatestTransactionAmount string
}

func (q *Queries) GetAllGroupsUserIsMemberOf(ctx context.Context, userID sql.NullInt64) ([]GetAllGroupsUserIsMemberOfRow, error) {
	rows, err := q.db.QueryContext(ctx, getAllGroupsUserIsMemberOf, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []GetAllGroupsUserIsMemberOfRow
	for rows.Next() {
		var i GetAllGroupsUserIsMemberOfRow
		if err := rows.Scan(
			&i.ID,
			&i.CreatorUserID,
			&i.GroupImageUrl,
			&i.Name,
			&i.IsPrivate,
			&i.MaxMemberCount,
			&i.Description,
			&i.ActivityCount,
			&i.LastActivityAt,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.Version,
			&i.TopMembers,
			&i.TotalMembers,
			&i.LatestMember,
			&i.TopGoals,
			&i.TotalGroupTransactions,
			&i.LatestTransactionAmount,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const getAllPublicGroups = `-- name: GetAllPublicGroups :many
WITH public_groups AS (
    SELECT g.id, g.creator_user_id, g.group_image_url, g.name, g.is_private, g.max_member_count, g.description, g.activity_count, g.last_activity_at, g.created_at, g.updated_at, g.version
    FROM groups g
    WHERE g.is_private = FALSE
      AND ($1 = '' OR to_tsvector('simple', g.name) @@ plainto_tsquery('simple', $1))
),

top_members AS (
    SELECT gm.group_id, gm.user_id, u.first_name, gm.role, u.profile_avatar_url,
           ROW_NUMBER() OVER (PARTITION BY gm.group_id ORDER BY gm.request_time DESC) AS row_num
    FROM group_memberships gm
    JOIN users u ON gm.user_id = u.id
    WHERE gm.status = 'accepted'
),

group_member_stats AS (
    SELECT gm.group_id,
           COUNT(*) AS total_members,
           MAX(u.id) AS latest_member_id,
           MAX(u.first_name) AS latest_member_first_name,
           MAX(u.profile_avatar_url) AS latest_member_avatar,
           MAX(gm.role) AS latest_member_role
    FROM group_memberships gm
    JOIN users u ON gm.user_id = u.id
    WHERE gm.status = 'accepted'
    GROUP BY gm.group_id
),

top_goals AS (
    SELECT gg.group_id, gg.goal_name, gg.target_amount, gg.current_amount,
           ROW_NUMBER() OVER (PARTITION BY gg.group_id ORDER BY gg.created_at DESC) AS row_num
    FROM group_goals gg
    WHERE gg.status = 'ongoing'
),

group_transaction_stats AS (
    SELECT gg.group_id,
           COUNT(gt.id) AS total_transactions,
           MAX(gt.amount)::NUMERIC AS latest_transaction_amount
    FROM group_transactions gt
    JOIN group_goals gg ON gt.goal_id = gg.id
    GROUP BY gg.group_id
),

total_count AS (
    SELECT COUNT(*) AS total_public_groups
    FROM public_groups
),

user_memberships AS (
    SELECT gm.group_id, TRUE AS is_user_a_member
    FROM group_memberships gm
    WHERE gm.user_id = $4 AND gm.status = 'accepted'
)

SELECT pg.id, pg.creator_user_id, pg.group_image_url, pg.name, pg.is_private, pg.max_member_count, pg.description, pg.activity_count, pg.last_activity_at, pg.created_at, pg.updated_at, pg.version, 
       (SELECT total_public_groups FROM total_count) AS total_public_groups,
       COALESCE(
           (SELECT jsonb_agg(jsonb_build_object('user_id', tm.user_id, 'first_name', tm.first_name, 'role', tm.role, 'profile_avatar_url', tm.profile_avatar_url))
            FROM top_members tm
            WHERE tm.group_id = pg.id AND tm.row_num <= 5), '[]'::jsonb) AS top_members,
       gms.total_members,
       COALESCE(jsonb_build_object(
           'user_id', gms.latest_member_id, 
           'first_name', gms.latest_member_first_name, 
           'role', gms.latest_member_role,
           'profile_avatar_url', gms.latest_member_avatar
       ), '{}'::jsonb) AS latest_member,
       COALESCE(
           (SELECT jsonb_agg(jsonb_build_object('goal_name', tg.goal_name, 'target_amount', tg.target_amount, 'current_amount', tg.current_amount))
            FROM top_goals tg
            WHERE tg.group_id = pg.id AND tg.row_num <= 5), '[]'::jsonb) AS top_goals,
       COALESCE(gts.total_transactions, 0)::NUMERIC AS total_group_transactions,
       COALESCE(gts.latest_transaction_amount, 0)::NUMERIC AS latest_transaction_amount,
       COALESCE(um.is_user_a_member, FALSE) AS is_user_a_member
FROM public_groups pg
LEFT JOIN group_member_stats gms ON pg.id = gms.group_id
LEFT JOIN group_transaction_stats gts ON pg.id = gts.group_id
LEFT JOIN user_memberships um ON pg.id = um.group_id
ORDER BY pg.last_activity_at DESC
OFFSET $2 LIMIT $3
`

type GetAllPublicGroupsParams struct {
	Column1 interface{}
	Offset  int32
	Limit   int32
	UserID  sql.NullInt64
}

type GetAllPublicGroupsRow struct {
	ID                      int64
	CreatorUserID           sql.NullInt64
	GroupImageUrl           string
	Name                    string
	IsPrivate               sql.NullBool
	MaxMemberCount          sql.NullInt32
	Description             sql.NullString
	ActivityCount           sql.NullInt32
	LastActivityAt          sql.NullTime
	CreatedAt               sql.NullTime
	UpdatedAt               sql.NullTime
	Version                 sql.NullInt32
	TotalPublicGroups       int64
	TopMembers              interface{}
	TotalMembers            sql.NullInt64
	LatestMember            interface{}
	TopGoals                interface{}
	TotalGroupTransactions  string
	LatestTransactionAmount string
	IsUserAMember           bool
}

func (q *Queries) GetAllPublicGroups(ctx context.Context, arg GetAllPublicGroupsParams) ([]GetAllPublicGroupsRow, error) {
	rows, err := q.db.QueryContext(ctx, getAllPublicGroups,
		arg.Column1,
		arg.Offset,
		arg.Limit,
		arg.UserID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []GetAllPublicGroupsRow
	for rows.Next() {
		var i GetAllPublicGroupsRow
		if err := rows.Scan(
			&i.ID,
			&i.CreatorUserID,
			&i.GroupImageUrl,
			&i.Name,
			&i.IsPrivate,
			&i.MaxMemberCount,
			&i.Description,
			&i.ActivityCount,
			&i.LastActivityAt,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.Version,
			&i.TotalPublicGroups,
			&i.TopMembers,
			&i.TotalMembers,
			&i.LatestMember,
			&i.TopGoals,
			&i.TotalGroupTransactions,
			&i.LatestTransactionAmount,
			&i.IsUserAMember,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const getDetailedGroupById = `-- name: GetDetailedGroupById :one
WITH user_groups AS (
    SELECT g.id, g.creator_user_id, g.group_image_url, g.name, g.is_private, g.max_member_count, g.description, g.activity_count, g.last_activity_at, g.created_at, g.updated_at, g.version
    FROM groups g
    JOIN group_memberships gm ON gm.group_id = g.id
    WHERE g.id = $1 
      AND gm.user_id = $2 
      AND gm.status = 'accepted' -- Only fetch data if the user is an approved member
),

group_members AS (
    SELECT 
        gm.group_id,
        gm.user_id,
        u.first_name,
        gm.role,
        u.profile_avatar_url,
        gm.approval_time AS join_date,  -- Join date

        -- Number of transactions for each member
        COALESCE(COUNT(gt.id), 0) AS transaction_count,

        -- Total amount of transactions for each member
        COALESCE(SUM(gt.amount), 0)::NUMERIC AS total_transaction_amount

    FROM group_memberships gm
    JOIN users u ON gm.user_id = u.id
    LEFT JOIN group_transactions gt ON gt.member_id = gm.user_id AND gt.goal_id IN (
        SELECT id FROM group_goals WHERE group_id = $1
    )
    WHERE gm.group_id = $1 
    GROUP BY gm.group_id, gm.user_id, u.first_name, gm.role, u.profile_avatar_url, gm.approval_time
),

pending_invitations AS (
    SELECT gi.id, gi.group_id, gi.inviter_user_id, gi.invitee_user_email, gi.status, gi.sent_at, gi.responded_at, gi.expiration_date
    FROM group_invitations gi
    WHERE gi.group_id = $1 
      AND gi.status = 'pending' -- Fetch only pending invitations for the group
),

group_goals AS (
    SELECT gg.id, gg.group_id, gg.creator_user_id, gg.goal_name, gg.target_amount, gg.current_amount, gg.start_date, gg.deadline, gg.description, gg.status, gg.created_at, gg.updated_at
    FROM group_goals gg
    WHERE gg.group_id = $1 -- Group goals filtered by group_id
),

total_group_transactions AS (
    SELECT COALESCE(SUM(gt.amount), 0)::NUMERIC AS total_transactions
    FROM group_transactions gt
    JOIN group_goals gg ON gt.goal_id = gg.id
    WHERE gg.group_id = $1
),

total_group_expenses AS (
    SELECT COALESCE(SUM(ge.amount), 0)::NUMERIC AS total_expenses
    FROM group_expenses ge
    WHERE ge.group_id = $1
),

goal_with_most_transactions AS (
    SELECT gg.goal_name AS goal_name,
           gg.target_amount AS target_amount,
		   gg.current_amount AS current_amount,
		   COUNT(gt.id) AS transaction_count
    FROM group_goals gg
    JOIN group_transactions gt ON gg.id = gt.goal_id
    WHERE gg.group_id = $1
    GROUP BY gg.goal_name, gg.target_amount,current_amount
    ORDER BY transaction_count DESC
    LIMIT 1
)

SELECT 
    ug.id, ug.creator_user_id, ug.group_image_url, ug.name, ug.is_private, ug.max_member_count, ug.description, ug.activity_count, ug.last_activity_at, ug.created_at, ug.updated_at, ug.version, 

    COALESCE(
        (SELECT jsonb_agg(
            jsonb_build_object(
                'user_id', gm.user_id, 
                'first_name', gm.first_name, 
                'role', gm.role, 
                'profile_avatar_url', gm.profile_avatar_url,
                'join_date', gm.join_date,              
                'transaction_count', gm.transaction_count,             
                'total_transaction_amount', gm.total_transaction_amount 
            )
        )
        FROM group_members gm
        WHERE gm.group_id = ug.id), '[]'::jsonb
    ) AS members,

    COALESCE(
        (SELECT jsonb_agg(
            jsonb_build_object(
                'id', pi.id,
                'group_id', pi.group_id,
                'inviter_user_id', pi.inviter_user_id,
                'invitee_user_email', pi.invitee_user_email,
                'status', pi.status,
                'sent_at', pi.sent_at,
                'responded_at', pi.responded_at,
                'expiration_date', pi.expiration_date
            )
        )
        FROM pending_invitations pi
        WHERE pi.group_id = ug.id), '[]'::jsonb
    ) AS pending_invitations,

    COALESCE(
        (SELECT jsonb_agg(
            jsonb_build_object(
                'id', gg.id,
                'group_id', gg.group_id,
                'creator_user_id', gg.creator_user_id,
                'name', gg.goal_name,
                'target_amount', gg.target_amount,
                'current_amount', gg.current_amount,
                'start_date', gg.start_date,
                'deadline', gg.deadline,
                'description', gg.description,
                'status', gg.status,
                'created_at', gg.created_at,
                'updated_at', gg.updated_at
            )
        )
        FROM group_goals gg
        WHERE gg.group_id = ug.id), '[]'::jsonb
    ) AS goals,

    (SELECT total_transactions FROM total_group_transactions) AS total_group_transactions,
    (SELECT total_expenses FROM total_group_expenses) AS total_group_expenses,
    
    COALESCE(
        (SELECT jsonb_build_object(
            'goal_name', gmt.goal_name,
			'target_amount', gmt.target_amount,
            'current_amount', gmt.current_amount
        )
        FROM goal_with_most_transactions gmt), '{}'::jsonb
    ) AS goal_with_most_transactions

FROM user_groups ug
`

type GetDetailedGroupByIdParams struct {
	ID     int64
	UserID sql.NullInt64
}

type GetDetailedGroupByIdRow struct {
	ID                       int64
	CreatorUserID            sql.NullInt64
	GroupImageUrl            string
	Name                     string
	IsPrivate                sql.NullBool
	MaxMemberCount           sql.NullInt32
	Description              sql.NullString
	ActivityCount            sql.NullInt32
	LastActivityAt           sql.NullTime
	CreatedAt                sql.NullTime
	UpdatedAt                sql.NullTime
	Version                  sql.NullInt32
	Members                  interface{}
	PendingInvitations       interface{}
	Goals                    interface{}
	TotalGroupTransactions   string
	TotalGroupExpenses       string
	GoalWithMostTransactions interface{}
}

func (q *Queries) GetDetailedGroupById(ctx context.Context, arg GetDetailedGroupByIdParams) (GetDetailedGroupByIdRow, error) {
	row := q.db.QueryRowContext(ctx, getDetailedGroupById, arg.ID, arg.UserID)
	var i GetDetailedGroupByIdRow
	err := row.Scan(
		&i.ID,
		&i.CreatorUserID,
		&i.GroupImageUrl,
		&i.Name,
		&i.IsPrivate,
		&i.MaxMemberCount,
		&i.Description,
		&i.ActivityCount,
		&i.LastActivityAt,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.Version,
		&i.Members,
		&i.PendingInvitations,
		&i.Goals,
		&i.TotalGroupTransactions,
		&i.TotalGroupExpenses,
		&i.GoalWithMostTransactions,
	)
	return i, err
}

const getGroupById = `-- name: GetGroupById :one
SELECT
    id,
    creator_user_id,
    group_image_url,
    name,
    is_private,
    max_member_count,
    description,
    activity_count,
    last_activity_at,
    created_at,
    updated_at,
    version
FROM groups
WHERE id = $1
`

func (q *Queries) GetGroupById(ctx context.Context, id int64) (Group, error) {
	row := q.db.QueryRowContext(ctx, getGroupById, id)
	var i Group
	err := row.Scan(
		&i.ID,
		&i.CreatorUserID,
		&i.GroupImageUrl,
		&i.Name,
		&i.IsPrivate,
		&i.MaxMemberCount,
		&i.Description,
		&i.ActivityCount,
		&i.LastActivityAt,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.Version,
	)
	return i, err
}

const getGroupExpensesByGroupId = `-- name: GetGroupExpensesByGroupId :many
WITH expense_totals AS (
    SELECT 
        SUM(ge.amount)::NUMERIC AS total_expense_amount,
        MAX(ge.created_at) AS latest_expense_date
    FROM 
        group_expenses ge
    WHERE 
        ge.group_id = $1
        AND($2 = '' OR to_tsvector('simple', ge.category) @@ plainto_tsquery('simple', $2))
)
SELECT 
    ge.id AS expense_id,
    ge.group_id,
    ge.member_id,
    ge.amount,
    ge.description,
    ge.category,
    ge.created_at,
    ge.updated_at,
    COUNT(*) OVER() AS total_expenses_count, -- Total count of expenses for pagination
    et.total_expense_amount, -- Total expense amount for the group and category
    (SELECT ge.amount FROM group_expenses ge WHERE ge.created_at = et.latest_expense_date LIMIT 1) AS latest_expense_amount -- Most recent expense amount
FROM 
    group_expenses ge
JOIN 
    group_memberships gm ON gm.group_id = ge.group_id -- Ensure user access
JOIN 
    expense_totals et ON TRUE -- Cross join to bring totals into main query
WHERE 
    ge.group_id = $1
    AND($2 = '' OR to_tsvector('simple', ge.category) @@ plainto_tsquery('simple', $2))
    AND gm.user_id = $3 -- Check if requesting user is a member
    AND gm.status = 'accepted' -- Only approved members can view expenses
ORDER BY 
    ge.created_at DESC
LIMIT $4 OFFSET $5
`

type GetGroupExpensesByGroupIdParams struct {
	GroupID sql.NullInt64
	Column2 interface{}
	UserID  sql.NullInt64
	Limit   int32
	Offset  int32
}

type GetGroupExpensesByGroupIdRow struct {
	ExpenseID           int64
	GroupID             sql.NullInt64
	MemberID            sql.NullInt64
	Amount              string
	Description         sql.NullString
	Category            sql.NullString
	CreatedAt           sql.NullTime
	UpdatedAt           sql.NullTime
	TotalExpensesCount  int64
	TotalExpenseAmount  string
	LatestExpenseAmount string
}

func (q *Queries) GetGroupExpensesByGroupId(ctx context.Context, arg GetGroupExpensesByGroupIdParams) ([]GetGroupExpensesByGroupIdRow, error) {
	rows, err := q.db.QueryContext(ctx, getGroupExpensesByGroupId,
		arg.GroupID,
		arg.Column2,
		arg.UserID,
		arg.Limit,
		arg.Offset,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []GetGroupExpensesByGroupIdRow
	for rows.Next() {
		var i GetGroupExpensesByGroupIdRow
		if err := rows.Scan(
			&i.ExpenseID,
			&i.GroupID,
			&i.MemberID,
			&i.Amount,
			&i.Description,
			&i.Category,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.TotalExpensesCount,
			&i.TotalExpenseAmount,
			&i.LatestExpenseAmount,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const getGroupGoalById = `-- name: GetGroupGoalById :one
SELECT
    id,
    group_id,
    creator_user_id,
    goal_name,
    target_amount,
    current_amount,
    start_date,
    deadline,
    description,
    status,
    created_at,
    updated_at
FROM group_goals
WHERE id = $1
`

func (q *Queries) GetGroupGoalById(ctx context.Context, id int64) (GroupGoal, error) {
	row := q.db.QueryRowContext(ctx, getGroupGoalById, id)
	var i GroupGoal
	err := row.Scan(
		&i.ID,
		&i.GroupID,
		&i.CreatorUserID,
		&i.GoalName,
		&i.TargetAmount,
		&i.CurrentAmount,
		&i.StartDate,
		&i.Deadline,
		&i.Description,
		&i.Status,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const getGroupGoalsByGroupId = `-- name: GetGroupGoalsByGroupId :one
SELECT
    id,
    group_id,
    creator_user_id,
    goal_name,
    target_amount,
    current_amount,
    start_date,
    deadline,
    description,
    status,
    created_at,
    updated_at
FROM group_goals
WHERE group_id = $1
`

func (q *Queries) GetGroupGoalsByGroupId(ctx context.Context, groupID int64) (GroupGoal, error) {
	row := q.db.QueryRowContext(ctx, getGroupGoalsByGroupId, groupID)
	var i GroupGoal
	err := row.Scan(
		&i.ID,
		&i.GroupID,
		&i.CreatorUserID,
		&i.GoalName,
		&i.TargetAmount,
		&i.CurrentAmount,
		&i.StartDate,
		&i.Deadline,
		&i.Description,
		&i.Status,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const getGroupInvitationById = `-- name: GetGroupInvitationById :one
SELECT
    id,
    group_id,
    inviter_user_id,
    invitee_user_email,
    status,
    sent_at,
    responded_at,
    expiration_date
FROM group_invitations 
WHERE invitee_user_email = $1            -- This checks if the invitee matches the user we are checking for.
  AND group_id = $2                   -- This checks if the invitation is for the specific group.
  AND status = 'pending'              -- This ensures that the invitation is still pending.
  AND expiration_date > NOW()
`

type GetGroupInvitationByIdParams struct {
	InviteeUserEmail string
	GroupID          sql.NullInt64
}

func (q *Queries) GetGroupInvitationById(ctx context.Context, arg GetGroupInvitationByIdParams) (GroupInvitation, error) {
	row := q.db.QueryRowContext(ctx, getGroupInvitationById, arg.InviteeUserEmail, arg.GroupID)
	var i GroupInvitation
	err := row.Scan(
		&i.ID,
		&i.GroupID,
		&i.InviterUserID,
		&i.InviteeUserEmail,
		&i.Status,
		&i.SentAt,
		&i.RespondedAt,
		&i.ExpirationDate,
	)
	return i, err
}

const getGroupTransactionsByGroupId = `-- name: GetGroupTransactionsByGroupId :many
WITH transaction_totals AS (
    SELECT 
        SUM(gt.amount)::NUMERIC AS total_transaction_amount,
        MAX(gt.created_at) AS latest_transaction_date
    FROM 
        group_transactions gt
    JOIN 
        group_goals gg ON gt.goal_id = gg.id
    WHERE 
        gg.group_id = $1
        AND ($2 = 0 OR gt.goal_id = $2) -- Use 0 as a default to indicate "no specific goal"
)
SELECT 
    gt.id AS transaction_id,
    gg.goal_name,
    gt.goal_id,
    gt.member_id,
    gt.amount,
    gt.description,
    gt.created_at,
    gt.updated_at,
    COUNT(*) OVER() AS total_transactions, -- Total count of transactions for pagination
    tt.total_transaction_amount, -- Total transaction amount for the group and goal
    (SELECT gt.amount FROM group_transactions gt WHERE gt.created_at = tt.latest_transaction_date LIMIT 1) AS latest_transaction_amount -- Most recent transaction amount
FROM 
    group_transactions gt
JOIN 
    group_goals gg ON gt.goal_id = gg.id
JOIN 
    group_memberships gm ON gm.group_id = gg.group_id -- Ensure user access
JOIN 
    transaction_totals tt ON TRUE -- Cross join to bring totals into main query
WHERE 
    gg.group_id = $1
    AND ($2 = 0 OR gt.goal_id = $2) -- Use 0 as a default to indicate "no specific goal"
    AND gm.user_id = $3 -- Check if requesting user is a member
    AND gm.status = 'accepted'
ORDER BY 
    gt.created_at DESC
LIMIT $4 OFFSET $5
`

type GetGroupTransactionsByGroupIdParams struct {
	GroupID int64
	Column2 interface{}
	UserID  sql.NullInt64
	Limit   int32
	Offset  int32
}

type GetGroupTransactionsByGroupIdRow struct {
	TransactionID           int64
	GoalName                string
	GoalID                  sql.NullInt64
	MemberID                sql.NullInt64
	Amount                  string
	Description             sql.NullString
	CreatedAt               sql.NullTime
	UpdatedAt               sql.NullTime
	TotalTransactions       int64
	TotalTransactionAmount  string
	LatestTransactionAmount string
}

func (q *Queries) GetGroupTransactionsByGroupId(ctx context.Context, arg GetGroupTransactionsByGroupIdParams) ([]GetGroupTransactionsByGroupIdRow, error) {
	rows, err := q.db.QueryContext(ctx, getGroupTransactionsByGroupId,
		arg.GroupID,
		arg.Column2,
		arg.UserID,
		arg.Limit,
		arg.Offset,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []GetGroupTransactionsByGroupIdRow
	for rows.Next() {
		var i GetGroupTransactionsByGroupIdRow
		if err := rows.Scan(
			&i.TransactionID,
			&i.GoalName,
			&i.GoalID,
			&i.MemberID,
			&i.Amount,
			&i.Description,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.TotalTransactions,
			&i.TotalTransactionAmount,
			&i.LatestTransactionAmount,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const updateExpiredGroupInvitations = `-- name: UpdateExpiredGroupInvitations :exec
UPDATE group_invitations
SET status = 'expired'
WHERE status = 'pending' AND expiration_date < NOW()
`

func (q *Queries) UpdateExpiredGroupInvitations(ctx context.Context) error {
	_, err := q.db.ExecContext(ctx, updateExpiredGroupInvitations)
	return err
}

const updateGroupGoal = `-- name: UpdateGroupGoal :one
UPDATE group_goals SET
    goal_name = $1,
    deadline = $2,
    description = $3
WHERE id = $4  
RETURNING updated_at
`

type UpdateGroupGoalParams struct {
	GoalName    string
	Deadline    time.Time
	Description string
	ID          int64
}

func (q *Queries) UpdateGroupGoal(ctx context.Context, arg UpdateGroupGoalParams) (time.Time, error) {
	row := q.db.QueryRowContext(ctx, updateGroupGoal,
		arg.GoalName,
		arg.Deadline,
		arg.Description,
		arg.ID,
	)
	var updated_at time.Time
	err := row.Scan(&updated_at)
	return updated_at, err
}

const updateGroupInvitationStatus = `-- name: UpdateGroupInvitationStatus :one


UPDATE group_invitations
SET status = $1, responded_at = NOW()
WHERE id = $2
RETURNING responded_at
`

type UpdateGroupInvitationStatusParams struct {
	Status InvitationStatusType
	ID     int64
}

// This ensures the invitation hasn't expired.
func (q *Queries) UpdateGroupInvitationStatus(ctx context.Context, arg UpdateGroupInvitationStatusParams) (sql.NullTime, error) {
	row := q.db.QueryRowContext(ctx, updateGroupInvitationStatus, arg.Status, arg.ID)
	var responded_at sql.NullTime
	err := row.Scan(&responded_at)
	return responded_at, err
}

const updateGroupUserRole = `-- name: UpdateGroupUserRole :one
UPDATE group_memberships AS target
SET role = $1, updated_at = NOW()
WHERE target.group_id = $2
  AND target.user_id = $3
  AND EXISTS (
      SELECT 1
      FROM group_memberships AS gm
      WHERE gm.group_id = $2
        AND gm.user_id = $4  -- The ID of the user performing the update
        AND gm.role IN ('moderator', 'admin')
  )
RETURNING updated_at
`

type UpdateGroupUserRoleParams struct {
	Role     NullMembershipRole
	GroupID  sql.NullInt64
	UserID   sql.NullInt64
	UserID_2 sql.NullInt64
}

func (q *Queries) UpdateGroupUserRole(ctx context.Context, arg UpdateGroupUserRoleParams) (sql.NullTime, error) {
	row := q.db.QueryRowContext(ctx, updateGroupUserRole,
		arg.Role,
		arg.GroupID,
		arg.UserID,
		arg.UserID_2,
	)
	var updated_at sql.NullTime
	err := row.Scan(&updated_at)
	return updated_at, err
}

const updateUserGroup = `-- name: UpdateUserGroup :one
UPDATE groups SET
    group_image_url = $1,
    name = $2,
    is_private = $3,
    max_member_count = $4,
    description = $5,
    activity_count = $6,
    last_activity_at = $7,
    updated_at = NOW(),
    version = version + 1
WHERE
    id = $8 AND version = $9 AND creator_user_id = $10
RETURNING updated_at
`

type UpdateUserGroupParams struct {
	GroupImageUrl  string
	Name           string
	IsPrivate      sql.NullBool
	MaxMemberCount sql.NullInt32
	Description    sql.NullString
	ActivityCount  sql.NullInt32
	LastActivityAt sql.NullTime
	ID             int64
	Version        sql.NullInt32
	CreatorUserID  sql.NullInt64
}

func (q *Queries) UpdateUserGroup(ctx context.Context, arg UpdateUserGroupParams) (sql.NullTime, error) {
	row := q.db.QueryRowContext(ctx, updateUserGroup,
		arg.GroupImageUrl,
		arg.Name,
		arg.IsPrivate,
		arg.MaxMemberCount,
		arg.Description,
		arg.ActivityCount,
		arg.LastActivityAt,
		arg.ID,
		arg.Version,
		arg.CreatorUserID,
	)
	var updated_at sql.NullTime
	err := row.Scan(&updated_at)
	return updated_at, err
}

const userLeaveGroup = `-- name: UserLeaveGroup :one
DELETE FROM group_memberships
WHERE group_id = $1 AND user_id = $2
RETURNING user_id
`

type UserLeaveGroupParams struct {
	GroupID sql.NullInt64
	UserID  sql.NullInt64
}

// User can leave a group
func (q *Queries) UserLeaveGroup(ctx context.Context, arg UserLeaveGroupParams) (sql.NullInt64, error) {
	row := q.db.QueryRowContext(ctx, userLeaveGroup, arg.GroupID, arg.UserID)
	var user_id sql.NullInt64
	err := row.Scan(&user_id)
	return user_id, err
}
