-- name: GetItem :one
SELECT i.*, pr.project_id FROM item i
LEFT JOIN project_requirement pr ON pr.id = i.project_requirement_id
WHERE i.id = ? and i.scope_id = ?;

-- name: ListItemsByProject :many
SELECT i.*, pr.project_id FROM item i
LEFT JOIN project_requirement pr ON pr.id = i.project_requirement_id
WHERE pr.project_id = ?;

-- name: ListItemsAcquiredBetween :many
SELECT i.*, pr.project_id FROM item i
LEFT JOIN project_requirement pr ON pr.id = i.project_requirement_id
WHERE i.acquired_time >= ?
  AND i.acquired_time <= ?
  AND i.scope_id = ?
ORDER BY acquired_time DESC, created_time DESC;

-- name: ListItemsScheduledBetween :many
SELECT i.*, pr.project_id FROM item i
LEFT JOIN project_requirement pr ON pr.id = i.project_requirement_id
WHERE i.scheduled_date >= ?
  AND i.scheduled_date <= ?
  AND i.scope_id = ?
ORDER BY scheduled_date, created_time;

-- name: ListItemsCreatedBetween :many
SELECT i.*, pr.project_id FROM item i
LEFT JOIN project_requirement pr ON pr.id = i.project_requirement_id
WHERE i.created_time >= ?
  AND i.created_time <= ?
  AND i.scope_id = ?
ORDER BY created_time DESC;

-- name: ListItemsLooseBetween :many
SELECT i.*, pr.project_id FROM item i
LEFT JOIN project_requirement pr ON pr.id = i.project_requirement_id
WHERE i.created_time >= ?
  AND i.created_time <= ?
  AND i.scope_id = ?
  AND i.scheduled_date IS NULL
  AND i.acquired_time IS NULL
  AND i.project_requirement_id IS NULL
ORDER BY created_time DESC;

-- name: ListItemsScheduledBetweenNoScope :many
SELECT i.*, pr.project_id FROM item i
LEFT JOIN project_requirement pr ON pr.id = i.project_requirement_id
WHERE i.scheduled_date >= ?
  AND i.scheduled_date < ?;

-- name: ListItemsCreatedBetweenNoScope :many
SELECT i.*, pr.project_id FROM item i
LEFT JOIN project_requirement pr ON pr.id = i.project_requirement_id
WHERE i.created_time >= ?
  AND i.created_time < ?;

-- name: ListItemsLooseBetweenNoScope :many
SELECT i.*, pr.project_id FROM item i
LEFT JOIN project_requirement pr ON pr.id = i.project_requirement_id
WHERE i.created_time >= ?
  AND i.created_time < ?
  AND i.scheduled_date IS NULL
  AND i.acquired_time IS NULL;

-- name: GetItemStatProgressBetween :one
SELECT s.id, s.name, s.weight, SUM(isp.acquired), SUM(isp.required) FROM item i
LEFT JOIN item_stat_progress isp on i.id = isp.item_id
LEFT JOIN stat s on isp.stat_id = s.id
WHERE i.acquired_time >= ?
  AND i.acquired_time < ?
  AND i.scope_id = ?
GROUP BY stat_id;

-- name: ListItemStatProgress :many
SELECT isp.required, isp.acquired, s.id, s.name, s.weight FROM item_stat_progress isp
LEFT JOIN stat s ON s.id = isp.stat_id
WHERE item_id = ?;

-- name: ListItemStatProgressMulti :many
SELECT isp.item_id, isp.required, isp.acquired, s.id, s.name, s.weight FROM item_stat_progress isp
LEFT JOIN stat s ON s.id = isp.stat_id
WHERE item_id IN (?, ?, ?, ?, ?, ?, ?, ?);

-- name: InsertItem :execresult
INSERT INTO item (scope_id, project_requirement_id, name, description, created_time, owner_id, acquired_time, scheduled_date)
VALUES (?, ?, ?, ?, ?, ?, ?, ?);

-- name: UpdateItem :exec
UPDATE item
SET project_requirement_id = ?,
    name = ?,
    description = ?,
    acquired_time = ?,
    scheduled_date = ?,
    owner_id = ?
WHERE id = ?;

-- name: DeleteItem :exec
DELETE FROM item WHERE id = ?;

-- name: DeleteItemForRequirement :exec
DELETE FROM item WHERE project_requirement_id = ?;

-- name: ReplaceItemStatProgress :exec
REPLACE INTO item_stat_progress (item_id, stat_id, acquired, required)
VALUES (?, ?, ?, ?);

-- name: DeleteItemStatProgress :exec
DELETE FROM item_stat_progress WHERE item_id = ? AND stat_id = ?;

-- name: ClearItemStatProgress :exec
DELETE FROM item_stat_progress WHERE item_id = ?;

-- name: ClearItemStatProgressByStat :exec
DELETE FROM item_stat_progress WHERE stat_id = ?;

-- name: DeleteAllScopeItems :exec
DELETE FROM item WHERE scope_id = ?;