// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.30.0
// source: project.sql

package sqlc

import (
	"context"

	"github.com/jackc/pgx/v5/pgtype"
)

const addProjectMember = `-- name: AddProjectMember :one
INSERT INTO project_members (
    project_id, user_id, role, invited_by
) VALUES (
    $1, $2, $3, $4
)
RETURNING project_id, user_id, role, invited_by, created_at
`

type AddProjectMemberParams struct {
	ProjectID pgtype.UUID `json:"project_id"`
	UserID    pgtype.UUID `json:"user_id"`
	Role      int16       `json:"role"`
	InvitedBy pgtype.UUID `json:"invited_by"`
}

func (q *Queries) AddProjectMember(ctx context.Context, arg AddProjectMemberParams) (ProjectMember, error) {
	row := q.db.QueryRow(ctx, addProjectMember,
		arg.ProjectID,
		arg.UserID,
		arg.Role,
		arg.InvitedBy,
	)
	var i ProjectMember
	err := row.Scan(
		&i.ProjectID,
		&i.UserID,
		&i.Role,
		&i.InvitedBy,
		&i.CreatedAt,
	)
	return i, err
}

const changeProjectOwner = `-- name: ChangeProjectOwner :exec
UPDATE projects
SET user_id = $2, updated_at = NOW()
WHERE id = $1 AND is_deleted = false
`

type ChangeProjectOwnerParams struct {
	ID     pgtype.UUID `json:"id"`
	UserID pgtype.UUID `json:"user_id"`
}

func (q *Queries) ChangeProjectOwner(ctx context.Context, arg ChangeProjectOwnerParams) error {
	_, err := q.db.Exec(ctx, changeProjectOwner, arg.ID, arg.UserID)
	return err
}

const checkProjectPermission = `-- name: CheckProjectPermission :one
SELECT role FROM project_members
WHERE project_id = $1 AND user_id = $2
`

type CheckProjectPermissionParams struct {
	ProjectID pgtype.UUID `json:"project_id"`
	UserID    pgtype.UUID `json:"user_id"`
}

func (q *Queries) CheckProjectPermission(ctx context.Context, arg CheckProjectPermissionParams) (int16, error) {
	row := q.db.QueryRow(ctx, checkProjectPermission, arg.ProjectID, arg.UserID)
	var role int16
	err := row.Scan(&role)
	return role, err
}

const countProjects = `-- name: CountProjects :one
SELECT count(*)
FROM projects p
WHERE p.is_deleted = false
  AND (
      $1::smallint[] IS NULL 
      OR p.project_status = ANY($1::smallint[])
  )
  AND ($2::uuid[] IS NULL OR p.user_id = ANY($2::uuid[]))
  AND (
      $3::text IS NULL OR 
      p.title ILIKE '%' || $3::text || '%' OR
      p.description ILIKE '%' || $3::text || '%'
  )
  AND ($4::timestamptz IS NULL OR p.created_at >= $4::timestamptz)
  AND ($5::timestamptz IS NULL OR p.created_at <= $5::timestamptz)
`

type CountProjectsParams struct {
	Statuses    []int16            `json:"statuses"`
	UserIds     []pgtype.UUID      `json:"user_ids"`
	SearchText  pgtype.Text        `json:"search_text"`
	CreatedFrom pgtype.Timestamptz `json:"created_from"`
	CreatedTo   pgtype.Timestamptz `json:"created_to"`
}

func (q *Queries) CountProjects(ctx context.Context, arg CountProjectsParams) (int64, error) {
	row := q.db.QueryRow(ctx, countProjects,
		arg.Statuses,
		arg.UserIds,
		arg.SearchText,
		arg.CreatedFrom,
		arg.CreatedTo,
	)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const createProject = `-- name: CreateProject :one
WITH inserted_project AS (
    INSERT INTO projects (
        title, description, project_status, user_id
    ) VALUES (
        $1, $2, $3, $4
    )
    RETURNING id, title, description, latest_commit_id, project_status, locked_by, is_deleted, user_id, created_at, updated_at
)
SELECT 
    p.id, p.title, p.description, p.latest_commit_id, p.project_status, p.locked_by, p.is_deleted, p.user_id, p.created_at, p.updated_at,
    json_build_object(
        'id', u.id,
        'email', u.email,
        'display_name', up.display_name,
        'full_name', up.full_name,
        'avatar_url', up.avatar_url
    )::json AS user,
    '[]'::json AS commits,
    '[]'::json AS submissions,
    '[]'::json AS members
FROM inserted_project p
JOIN users u ON p.user_id = u.id
LEFT JOIN user_profiles up ON u.id = up.user_id
`

type CreateProjectParams struct {
	Title         string      `json:"title"`
	Description   pgtype.Text `json:"description"`
	ProjectStatus int16       `json:"project_status"`
	UserID        pgtype.UUID `json:"user_id"`
}

type CreateProjectRow struct {
	ID             pgtype.UUID        `json:"id"`
	Title          string             `json:"title"`
	Description    pgtype.Text        `json:"description"`
	LatestCommitID pgtype.UUID        `json:"latest_commit_id"`
	ProjectStatus  int16              `json:"project_status"`
	LockedBy       pgtype.UUID        `json:"locked_by"`
	IsDeleted      bool               `json:"is_deleted"`
	UserID         pgtype.UUID        `json:"user_id"`
	CreatedAt      pgtype.Timestamptz `json:"created_at"`
	UpdatedAt      pgtype.Timestamptz `json:"updated_at"`
	User           []byte             `json:"user"`
	Commits        []byte             `json:"commits"`
	Submissions    []byte             `json:"submissions"`
	Members        []byte             `json:"members"`
}

func (q *Queries) CreateProject(ctx context.Context, arg CreateProjectParams) (CreateProjectRow, error) {
	row := q.db.QueryRow(ctx, createProject,
		arg.Title,
		arg.Description,
		arg.ProjectStatus,
		arg.UserID,
	)
	var i CreateProjectRow
	err := row.Scan(
		&i.ID,
		&i.Title,
		&i.Description,
		&i.LatestCommitID,
		&i.ProjectStatus,
		&i.LockedBy,
		&i.IsDeleted,
		&i.UserID,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.User,
		&i.Commits,
		&i.Submissions,
		&i.Members,
	)
	return i, err
}

const deleteProject = `-- name: DeleteProject :exec
UPDATE projects
SET 
    is_deleted = true
WHERE id = $1
`

func (q *Queries) DeleteProject(ctx context.Context, id pgtype.UUID) error {
	_, err := q.db.Exec(ctx, deleteProject, id)
	return err
}

const getProjectById = `-- name: GetProjectById :one
SELECT 
    p.id, p.title, p.description, p.latest_commit_id, p.project_status, p.locked_by, p.is_deleted, p.user_id, p.created_at, p.updated_at,
    COALESCE(
        (SELECT json_agg(json_build_object('id', c.id, 'edit_summary', c.edit_summary) ORDER BY c.created_at DESC) 
         FROM commits c WHERE c.project_id = p.id AND c.is_deleted = false), 
        '[]'
	    )::json AS commits,
	    COALESCE(
	        (SELECT json_agg(json_build_object('id', s.id, 'status', s.status)) FROM submissions s WHERE s.project_id = p.id AND s.is_deleted = false),
	        '[]'
	    )::json AS submissions,
    json_build_object(
        'id', u.id,
        'email', u.email,
        'display_name', up.display_name,
        'full_name', up.full_name,
        'avatar_url', up.avatar_url
    )::json AS user,
    COALESCE(
        (SELECT json_agg(json_build_object(
            'user_id', pm.user_id, 'role', pm.role,
            'display_name', mup.display_name, 'avatar_url', mup.avatar_url
         ) ORDER BY pm.role ASC, pm.created_at ASC)
         FROM project_members pm
         JOIN users mu ON pm.user_id = mu.id
         LEFT JOIN user_profiles mup ON mu.id = mup.user_id
         WHERE pm.project_id = p.id),
        '[]'
    )::json AS members
FROM projects p
JOIN users u ON p.user_id = u.id
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE p.id = $1 AND p.is_deleted = false
`

type GetProjectByIdRow struct {
	ID             pgtype.UUID        `json:"id"`
	Title          string             `json:"title"`
	Description    pgtype.Text        `json:"description"`
	LatestCommitID pgtype.UUID        `json:"latest_commit_id"`
	ProjectStatus  int16              `json:"project_status"`
	LockedBy       pgtype.UUID        `json:"locked_by"`
	IsDeleted      bool               `json:"is_deleted"`
	UserID         pgtype.UUID        `json:"user_id"`
	CreatedAt      pgtype.Timestamptz `json:"created_at"`
	UpdatedAt      pgtype.Timestamptz `json:"updated_at"`
	Commits        []byte             `json:"commits"`
	Submissions    []byte             `json:"submissions"`
	User           []byte             `json:"user"`
	Members        []byte             `json:"members"`
}

func (q *Queries) GetProjectById(ctx context.Context, id pgtype.UUID) (GetProjectByIdRow, error) {
	row := q.db.QueryRow(ctx, getProjectById, id)
	var i GetProjectByIdRow
	err := row.Scan(
		&i.ID,
		&i.Title,
		&i.Description,
		&i.LatestCommitID,
		&i.ProjectStatus,
		&i.LockedBy,
		&i.IsDeleted,
		&i.UserID,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.Commits,
		&i.Submissions,
		&i.User,
		&i.Members,
	)
	return i, err
}

const getProjectsByIDs = `-- name: GetProjectsByIDs :many
SELECT 
    p.id, p.title, p.description, p.latest_commit_id, p.project_status, p.locked_by, p.is_deleted, p.user_id, p.created_at, p.updated_at,
    COALESCE(
        (SELECT json_agg(json_build_object('id', c.id, 'edit_summary', c.edit_summary) ORDER BY c.created_at DESC)
         FROM commits c WHERE c.project_id = p.id AND c.is_deleted = false),
        '[]'
    )::json AS commits,
    COALESCE(
        (SELECT json_agg(json_build_object('id', s.id, 'status', s.status)) FROM submissions s WHERE s.project_id = p.id AND s.is_deleted = false),
        '[]'
    )::json AS submissions,
    json_build_object(
        'id', u.id,
        'email', u.email,
        'display_name', up.display_name,
        'full_name', up.full_name,
        'avatar_url', up.avatar_url
    )::json AS user,
    COALESCE(
        (SELECT json_agg(json_build_object(
            'user_id', pm.user_id, 'role', pm.role,
            'display_name', mup.display_name, 'avatar_url', mup.avatar_url
         ) ORDER BY pm.role ASC, pm.created_at ASC)
         FROM project_members pm
         JOIN users mu ON pm.user_id = mu.id
         LEFT JOIN user_profiles mup ON mu.id = mup.user_id
         WHERE pm.project_id = p.id),
        '[]'
    )::json AS members
FROM projects p
JOIN users u ON p.user_id = u.id
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE p.id = ANY($1::uuid[]) AND p.is_deleted = false
`

type GetProjectsByIDsRow struct {
	ID             pgtype.UUID        `json:"id"`
	Title          string             `json:"title"`
	Description    pgtype.Text        `json:"description"`
	LatestCommitID pgtype.UUID        `json:"latest_commit_id"`
	ProjectStatus  int16              `json:"project_status"`
	LockedBy       pgtype.UUID        `json:"locked_by"`
	IsDeleted      bool               `json:"is_deleted"`
	UserID         pgtype.UUID        `json:"user_id"`
	CreatedAt      pgtype.Timestamptz `json:"created_at"`
	UpdatedAt      pgtype.Timestamptz `json:"updated_at"`
	Commits        []byte             `json:"commits"`
	Submissions    []byte             `json:"submissions"`
	User           []byte             `json:"user"`
	Members        []byte             `json:"members"`
}

func (q *Queries) GetProjectsByIDs(ctx context.Context, dollar_1 []pgtype.UUID) ([]GetProjectsByIDsRow, error) {
	rows, err := q.db.Query(ctx, getProjectsByIDs, dollar_1)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []GetProjectsByIDsRow{}
	for rows.Next() {
		var i GetProjectsByIDsRow
		if err := rows.Scan(
			&i.ID,
			&i.Title,
			&i.Description,
			&i.LatestCommitID,
			&i.ProjectStatus,
			&i.LockedBy,
			&i.IsDeleted,
			&i.UserID,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.Commits,
			&i.Submissions,
			&i.User,
			&i.Members,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const getProjectsByUserId = `-- name: GetProjectsByUserId :many
SELECT 
    p.id, p.title, p.description, p.latest_commit_id, p.project_status, p.locked_by, p.is_deleted, p.user_id, p.created_at, p.updated_at,
    COALESCE(
        (SELECT json_agg(json_build_object('id', c.id, 'edit_summary', c.edit_summary) ORDER BY c.created_at DESC)
         FROM commits c WHERE c.project_id = p.id AND c.is_deleted = false),
        '[]'
    )::json AS commits,
    COALESCE(
        (SELECT json_agg(json_build_object('id', s.id, 'status', s.status)) FROM submissions s WHERE s.project_id = p.id AND s.is_deleted = false),
        '[]'
    )::json AS submissions,
    json_build_object(
        'id', u.id,
        'email', u.email,
        'display_name', up.display_name,
        'full_name', up.full_name,
        'avatar_url', up.avatar_url
    )::json AS user,
    COALESCE(
        (SELECT json_agg(json_build_object(
            'user_id', pm.user_id, 'role', pm.role,
            'display_name', mup.display_name, 'avatar_url', mup.avatar_url
         ) ORDER BY pm.role ASC, pm.created_at ASC)
         FROM project_members pm
         JOIN users mu ON pm.user_id = mu.id
         LEFT JOIN user_profiles mup ON mu.id = mup.user_id
         WHERE pm.project_id = p.id),
        '[]'
    )::json AS members
FROM projects p
JOIN users u ON p.user_id = u.id
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE (p.user_id = $1 OR EXISTS (SELECT 1 FROM project_members pm2 WHERE pm2.project_id = p.id AND pm2.user_id = $1))
  AND p.is_deleted = false 
  AND ($2::uuid IS NULL OR p.id < $2::uuid)
ORDER BY p.updated_at DESC
LIMIT $3
`

type GetProjectsByUserIdParams struct {
	UserID   pgtype.UUID `json:"user_id"`
	CursorID pgtype.UUID `json:"cursor_id"`
	Limit    int32       `json:"limit"`
}

type GetProjectsByUserIdRow struct {
	ID             pgtype.UUID        `json:"id"`
	Title          string             `json:"title"`
	Description    pgtype.Text        `json:"description"`
	LatestCommitID pgtype.UUID        `json:"latest_commit_id"`
	ProjectStatus  int16              `json:"project_status"`
	LockedBy       pgtype.UUID        `json:"locked_by"`
	IsDeleted      bool               `json:"is_deleted"`
	UserID         pgtype.UUID        `json:"user_id"`
	CreatedAt      pgtype.Timestamptz `json:"created_at"`
	UpdatedAt      pgtype.Timestamptz `json:"updated_at"`
	Commits        []byte             `json:"commits"`
	Submissions    []byte             `json:"submissions"`
	User           []byte             `json:"user"`
	Members        []byte             `json:"members"`
}

func (q *Queries) GetProjectsByUserId(ctx context.Context, arg GetProjectsByUserIdParams) ([]GetProjectsByUserIdRow, error) {
	rows, err := q.db.Query(ctx, getProjectsByUserId, arg.UserID, arg.CursorID, arg.Limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []GetProjectsByUserIdRow{}
	for rows.Next() {
		var i GetProjectsByUserIdRow
		if err := rows.Scan(
			&i.ID,
			&i.Title,
			&i.Description,
			&i.LatestCommitID,
			&i.ProjectStatus,
			&i.LockedBy,
			&i.IsDeleted,
			&i.UserID,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.Commits,
			&i.Submissions,
			&i.User,
			&i.Members,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const removeProjectMember = `-- name: RemoveProjectMember :exec
DELETE FROM project_members
WHERE project_id = $1 AND user_id = $2
`

type RemoveProjectMemberParams struct {
	ProjectID pgtype.UUID `json:"project_id"`
	UserID    pgtype.UUID `json:"user_id"`
}

func (q *Queries) RemoveProjectMember(ctx context.Context, arg RemoveProjectMemberParams) error {
	_, err := q.db.Exec(ctx, removeProjectMember, arg.ProjectID, arg.UserID)
	return err
}

const searchProjects = `-- name: SearchProjects :many
SELECT 
    p.id, p.title, p.description, p.latest_commit_id, p.project_status, p.locked_by, p.is_deleted, p.user_id, p.created_at, p.updated_at,
    COALESCE(
        (SELECT json_agg(json_build_object('id', c.id, 'edit_summary', c.edit_summary) ORDER BY c.created_at DESC)
         FROM commits c WHERE c.project_id = p.id AND c.is_deleted = false),
        '[]'
	    )::json AS commits,
	    COALESCE(
	        (SELECT json_agg(json_build_object('id', s.id, 'status', s.status)) FROM submissions s WHERE s.project_id = p.id AND s.is_deleted = false),
	        '[]'
	    )::json AS submissions,
    json_build_object(
        'id', u.id,
        'email', u.email,
        'display_name', up.display_name,
        'full_name', up.full_name,
        'avatar_url', up.avatar_url
    )::json AS user,
    COALESCE(
        (SELECT json_agg(json_build_object(
            'user_id', pm.user_id, 'role', pm.role,
            'display_name', mup.display_name, 'avatar_url', mup.avatar_url
         ) ORDER BY pm.role ASC, pm.created_at ASC)
         FROM project_members pm
         JOIN users mu ON pm.user_id = mu.id
         LEFT JOIN user_profiles mup ON mu.id = mup.user_id
         WHERE pm.project_id = p.id),
        '[]'
    )::json AS members
FROM projects p
JOIN users u ON p.user_id = u.id
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE p.is_deleted = false
  AND (
      $1::smallint[] IS NULL 
      OR p.project_status = ANY($1::smallint[])
  )
  AND ($2::uuid[] IS NULL OR p.user_id = ANY($2::uuid[]))
  AND (
      $3::text IS NULL OR 
      p.title ILIKE '%' || $3::text || '%' OR
      p.description ILIKE '%' || $3::text || '%'
  )
  AND ($4::timestamptz IS NULL OR p.created_at >= $4::timestamptz)
  AND ($5::timestamptz IS NULL OR p.created_at <= $5::timestamptz)
ORDER BY
    CASE WHEN $6 = 'created_at' AND $7 = 'asc' THEN p.created_at END ASC,
    CASE WHEN $6 = 'created_at' AND $7 = 'desc' THEN p.created_at END DESC,
    CASE WHEN $6 = 'updated_at' AND $7 = 'asc' THEN p.updated_at END ASC,
    CASE WHEN $6 = 'updated_at' AND $7 = 'desc' THEN p.updated_at END DESC,
    CASE WHEN $6 = 'title' AND $7 = 'asc' THEN p.title END ASC,
    CASE WHEN $6 = 'title' AND $7 = 'desc' THEN p.title END DESC,
    CASE WHEN $6 IS NULL THEN p.updated_at END DESC
LIMIT $9
OFFSET $8
`

type SearchProjectsParams struct {
	Statuses    []int16            `json:"statuses"`
	UserIds     []pgtype.UUID      `json:"user_ids"`
	SearchText  pgtype.Text        `json:"search_text"`
	CreatedFrom pgtype.Timestamptz `json:"created_from"`
	CreatedTo   pgtype.Timestamptz `json:"created_to"`
	Sort        interface{}        `json:"sort"`
	Order       interface{}        `json:"order"`
	Offset      int32              `json:"offset"`
	Limit       int32              `json:"limit"`
}

type SearchProjectsRow struct {
	ID             pgtype.UUID        `json:"id"`
	Title          string             `json:"title"`
	Description    pgtype.Text        `json:"description"`
	LatestCommitID pgtype.UUID        `json:"latest_commit_id"`
	ProjectStatus  int16              `json:"project_status"`
	LockedBy       pgtype.UUID        `json:"locked_by"`
	IsDeleted      bool               `json:"is_deleted"`
	UserID         pgtype.UUID        `json:"user_id"`
	CreatedAt      pgtype.Timestamptz `json:"created_at"`
	UpdatedAt      pgtype.Timestamptz `json:"updated_at"`
	Commits        []byte             `json:"commits"`
	Submissions    []byte             `json:"submissions"`
	User           []byte             `json:"user"`
	Members        []byte             `json:"members"`
}

func (q *Queries) SearchProjects(ctx context.Context, arg SearchProjectsParams) ([]SearchProjectsRow, error) {
	rows, err := q.db.Query(ctx, searchProjects,
		arg.Statuses,
		arg.UserIds,
		arg.SearchText,
		arg.CreatedFrom,
		arg.CreatedTo,
		arg.Sort,
		arg.Order,
		arg.Offset,
		arg.Limit,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []SearchProjectsRow{}
	for rows.Next() {
		var i SearchProjectsRow
		if err := rows.Scan(
			&i.ID,
			&i.Title,
			&i.Description,
			&i.LatestCommitID,
			&i.ProjectStatus,
			&i.LockedBy,
			&i.IsDeleted,
			&i.UserID,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.Commits,
			&i.Submissions,
			&i.User,
			&i.Members,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const updateLatestCommit = `-- name: UpdateLatestCommit :exec
UPDATE projects
SET latest_commit_id = $2, updated_at = NOW()
WHERE id = $1 AND is_deleted = false
`

type UpdateLatestCommitParams struct {
	ID             pgtype.UUID `json:"id"`
	LatestCommitID pgtype.UUID `json:"latest_commit_id"`
}

func (q *Queries) UpdateLatestCommit(ctx context.Context, arg UpdateLatestCommitParams) error {
	_, err := q.db.Exec(ctx, updateLatestCommit, arg.ID, arg.LatestCommitID)
	return err
}

const updateProject = `-- name: UpdateProject :one
UPDATE projects
SET 
    title = COALESCE($1, projects.title),
    description = COALESCE($2, projects.description),
    latest_commit_id = COALESCE($3, projects.latest_commit_id),
    project_status = COALESCE($4, projects.project_status),
    locked_by = COALESCE($5, projects.locked_by),
    updated_at = NOW()
FROM users u
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE projects.id = $6 AND projects.is_deleted = false
  AND projects.user_id = u.id
RETURNING 
    projects.id, projects.title, projects.description, projects.latest_commit_id, projects.project_status, projects.locked_by, projects.is_deleted, projects.user_id, projects.created_at, projects.updated_at,
    json_build_object(
        'id', u.id,
        'email', u.email,
        'display_name', up.display_name,
        'full_name', up.full_name,
        'avatar_url', up.avatar_url
    )::json AS user,
    COALESCE(
        (SELECT json_agg(json_build_object('id', c.id, 'edit_summary', c.edit_summary) ORDER BY c.created_at DESC)
         FROM commits c WHERE c.project_id = projects.id AND c.is_deleted = false),
        '[]'
	    )::json AS commits,
	    COALESCE(
	        (SELECT json_agg(json_build_object('id', s.id, 'status', s.status)) FROM submissions s WHERE s.project_id = projects.id AND s.is_deleted = false),
	        '[]'
	    )::json AS submissions,
    COALESCE(
        (SELECT json_agg(json_build_object(
            'user_id', pm.user_id, 'role', pm.role,
            'display_name', mup.display_name, 'avatar_url', mup.avatar_url
         ) ORDER BY pm.role ASC, pm.created_at ASC)
         FROM project_members pm
         JOIN users mu ON pm.user_id = mu.id
         LEFT JOIN user_profiles mup ON mu.id = mup.user_id
         WHERE pm.project_id = projects.id),
        '[]'
    )::json AS members
`

type UpdateProjectParams struct {
	Title          pgtype.Text `json:"title"`
	Description    pgtype.Text `json:"description"`
	LatestCommitID pgtype.UUID `json:"latest_commit_id"`
	Status         pgtype.Int2 `json:"status"`
	LockedBy       pgtype.UUID `json:"locked_by"`
	ID             pgtype.UUID `json:"id"`
}

type UpdateProjectRow struct {
	ID             pgtype.UUID        `json:"id"`
	Title          string             `json:"title"`
	Description    pgtype.Text        `json:"description"`
	LatestCommitID pgtype.UUID        `json:"latest_commit_id"`
	ProjectStatus  int16              `json:"project_status"`
	LockedBy       pgtype.UUID        `json:"locked_by"`
	IsDeleted      bool               `json:"is_deleted"`
	UserID         pgtype.UUID        `json:"user_id"`
	CreatedAt      pgtype.Timestamptz `json:"created_at"`
	UpdatedAt      pgtype.Timestamptz `json:"updated_at"`
	User           []byte             `json:"user"`
	Commits        []byte             `json:"commits"`
	Submissions    []byte             `json:"submissions"`
	Members        []byte             `json:"members"`
}

func (q *Queries) UpdateProject(ctx context.Context, arg UpdateProjectParams) (UpdateProjectRow, error) {
	row := q.db.QueryRow(ctx, updateProject,
		arg.Title,
		arg.Description,
		arg.LatestCommitID,
		arg.Status,
		arg.LockedBy,
		arg.ID,
	)
	var i UpdateProjectRow
	err := row.Scan(
		&i.ID,
		&i.Title,
		&i.Description,
		&i.LatestCommitID,
		&i.ProjectStatus,
		&i.LockedBy,
		&i.IsDeleted,
		&i.UserID,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.User,
		&i.Commits,
		&i.Submissions,
		&i.Members,
	)
	return i, err
}

const updateProjectMemberRole = `-- name: UpdateProjectMemberRole :one
UPDATE project_members
SET role = $3
WHERE project_id = $1 AND user_id = $2
RETURNING project_id, user_id, role, invited_by, created_at
`

type UpdateProjectMemberRoleParams struct {
	ProjectID pgtype.UUID `json:"project_id"`
	UserID    pgtype.UUID `json:"user_id"`
	Role      int16       `json:"role"`
}

func (q *Queries) UpdateProjectMemberRole(ctx context.Context, arg UpdateProjectMemberRoleParams) (ProjectMember, error) {
	row := q.db.QueryRow(ctx, updateProjectMemberRole, arg.ProjectID, arg.UserID, arg.Role)
	var i ProjectMember
	err := row.Scan(
		&i.ProjectID,
		&i.UserID,
		&i.Role,
		&i.InvitedBy,
		&i.CreatedAt,
	)
	return i, err
}
