-- name: CreateSysDepartment :one
INSERT INTO sys_department (
  name,
  parent_id,
  parent_path,
  status,
  sort,
  created_at,
  updated_at
) VALUES (
  $1, $2, $3, $4, $5, $6, $7
) RETURNING *;

-- name: UpdateSysDepartment :one
UPDATE sys_department 
SET name = $2,
    parent_id = $3,
    parent_path = $4,
    status = $5,
    sort = $6,
    updated_at = $7
WHERE id = $1
RETURNING *;

-- name: GetSysDepartment :one
SELECT * FROM sys_department 
WHERE id = $1 LIMIT 1;

-- name: ListSysDepartment :many
SELECT * FROM sys_department 
WHERE status = 0;

-- name: CountSysDepartmentCondition :one
SELECT COUNT(*) FROM sys_department
WHERE (NOT @is_status::Boolean OR status = @status)
    AND (NOT @is_id::Boolean OR id = @id)
    AND (NOT @is_parent_id::Boolean OR parent_id = @parent_id)
    AND (@name::text = '' OR name ILIKE '%' || @name || '%');

-- name: ListSysDepartmentCondition :many
SELECT * FROM sys_department
WHERE (NOT @is_status::Boolean OR status = @status)
    AND (NOT @is_id::Boolean OR id = @id)
    AND (NOT @is_parent_id::Boolean OR parent_id = @parent_id)
    AND (@name::text = '' OR name ILIKE '%' || @name || '%')
ORDER BY created_at DESC
OFFSET @skip
LIMIT @size;

-- name: AllSysDepartment :many
SELECT * FROM sys_department 
WHERE status = 0
ORDER BY sort DESC;

-- name: ExistsSysDepartment :one
SELECT EXISTS (
    SELECT 1 FROM sys_department
);

-- name: ListSysDepartmentRecursive :many
WITH RECURSIVE dist AS (SELECT sys_department.*
                        FROM sys_department
                        WHERE status = 0
                        UNION ALL
                        SELECT sys_department.*
                        FROM sys_department,
                             dist
                        WHERE sys_department.id = dist.parent_id)
SELECT DISTINCT *
FROM dist
ORDER BY sort DESC;