-- name: CreateProject :one
INSERT INTO projects (id, owner_id, name, color, is_shared, deadline, sort_order)
VALUES ($1, $2, $3, $4, COALESCE($5, false), $6, COALESCE($7, 0))
RETURNING *;

-- name: GetProjectByID :one
SELECT * FROM projects
WHERE id = $1 AND owner_id = $2 AND deleted_at IS NULL;

-- name: ListProjects :many
SELECT * FROM projects
WHERE owner_id = @owner_id AND deleted_at IS NULL
ORDER BY sort_order ASC, name ASC;

-- name: ListProjectsForUser :many
SELECT p.* FROM projects p
LEFT JOIN project_members pm ON pm.project_id = p.id AND pm.user_id = @user_id
WHERE (p.owner_id = @user_id OR pm.user_id = @user_id)
  AND p.deleted_at IS NULL
ORDER BY p.sort_order ASC, p.name ASC;

-- name: UpdateProject :one
UPDATE projects
SET name = COALESCE(sqlc.narg('name'), name),
    color = COALESCE(sqlc.narg('color'), color),
    is_shared = COALESCE(sqlc.narg('is_shared'), is_shared),
    deadline = sqlc.narg('deadline'),
    sort_order = COALESCE(sqlc.narg('sort_order'), sort_order),
    updated_at = now()
WHERE id = @id AND owner_id = @owner_id AND deleted_at IS NULL
RETURNING *;

-- name: SoftDeleteProject :exec
UPDATE projects SET deleted_at = now(), updated_at = now()
WHERE id = $1 AND owner_id = $2 AND deleted_at IS NULL;

-- name: AddProjectMember :exec
INSERT INTO project_members (project_id, user_id, role)
VALUES ($1, $2, $3)
ON CONFLICT (project_id, user_id) DO UPDATE SET role = EXCLUDED.role;

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

-- name: ListProjectMembers :many
SELECT pm.project_id, pm.user_id, pm.role, u.email
FROM project_members pm
JOIN users u ON u.id = pm.user_id
WHERE pm.project_id = $1;

-- name: GetProjectMember :one
SELECT * FROM project_members
WHERE project_id = $1 AND user_id = $2;