-- name: GetPurchaseOrder :one
SELECT * FROM purchase_orders
WHERE id = ? LIMIT 1;

-- name: ListPurchaseOrders :many
SELECT * FROM purchase_orders
ORDER BY issue_date DESC
LIMIT ? OFFSET ?;

-- name: CreatePurchaseOrder :execresult
INSERT INTO purchase_orders (
    issue_date, dispatch_date, date_arrived, title,
    principle_id, principle_reference, document_id,
    currency_id, ordered_from, description, dispatch_by,
    deliver_to, shipping_instructions, jobs_text,
    freight_forwarder_text, parent_purchase_order_id
) VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
);

-- name: UpdatePurchaseOrder :exec
UPDATE purchase_orders
SET issue_date = ?,
    dispatch_date = ?,
    date_arrived = ?,
    title = ?,
    principle_id = ?,
    principle_reference = ?,
    document_id = ?,
    currency_id = ?,
    ordered_from = ?,
    description = ?,
    dispatch_by = ?,
    deliver_to = ?,
    shipping_instructions = ?,
    jobs_text = ?,
    freight_forwarder_text = ?,
    parent_purchase_order_id = ?
WHERE id = ?;

-- name: DeletePurchaseOrder :exec
DELETE FROM purchase_orders
WHERE id = ?;

-- name: GetPurchaseOrdersByPrinciple :many
SELECT * FROM purchase_orders
WHERE principle_id = ?
ORDER BY issue_date DESC
LIMIT ? OFFSET ?;

-- name: GetPurchaseOrderRevisions :many
SELECT * FROM purchase_orders
WHERE parent_purchase_order_id = ?
ORDER BY id DESC;

-- name: SearchPurchaseOrdersByTitle :many
SELECT * FROM purchase_orders
WHERE title LIKE CONCAT('%', ?, '%')
ORDER BY issue_date DESC
LIMIT ? OFFSET ?;

-- name: GetPurchaseOrderByDocumentID :one
SELECT * FROM purchase_orders
WHERE document_id = ?
LIMIT 1;