-- name: CreateTransactionMatch :one
INSERT INTO transaction_matches (account_link_id, primary_transaction_id, dependent_transaction_id, match_confidence, matched_on)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT DO NOTHING
RETURNING *;

-- name: GetTransactionMatch :one
SELECT * FROM transaction_matches WHERE id = $1;

-- name: GetMatchUUIDByShortID :one
SELECT id FROM transaction_matches WHERE short_id = $1;

-- name: GetTransactionMatchByPrimaryTxn :one
SELECT * FROM transaction_matches WHERE primary_transaction_id = $1;

-- name: GetTransactionMatchByDependentTxn :one
SELECT * FROM transaction_matches WHERE dependent_transaction_id = $1;

-- name: ListTransactionMatchesByLink :many
-- Carries the JOINed primary/dependent transaction short_ids and the parent
-- account_link's short_id so the API response can return short_id values
-- without per-row resolution.
SELECT tm.id, tm.short_id, tm.match_confidence, tm.matched_on, tm.created_at,
       al.short_id AS account_link_short_id,
       pt.short_id AS primary_transaction_short_id,
       dt.short_id AS dependent_transaction_short_id,
       pt.provider_name AS primary_txn_name, pt.amount AS primary_txn_amount, pt.date AS primary_txn_date, pt.provider_merchant_name AS primary_txn_merchant,
       dt.provider_name AS dependent_txn_name, dt.amount AS dependent_txn_amount, dt.date AS dependent_txn_date, dt.provider_merchant_name AS dependent_txn_merchant
FROM transaction_matches tm
JOIN account_links al ON al.id = tm.account_link_id
JOIN transactions pt ON tm.primary_transaction_id = pt.id
JOIN transactions dt ON tm.dependent_transaction_id = dt.id
WHERE tm.account_link_id = $1
ORDER BY pt.date DESC, tm.created_at DESC;

-- name: CountTransactionMatchesByLink :one
SELECT COUNT(*) FROM transaction_matches WHERE account_link_id = $1;

-- name: CountUnmatchedDependentTransactions :one
SELECT COUNT(*) FROM transactions t
JOIN accounts a ON t.account_id = a.id
WHERE a.id = $1
  AND t.deleted_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM transaction_matches tm WHERE tm.dependent_transaction_id = t.id);

-- name: UpdateTransactionMatchConfidence :one
UPDATE transaction_matches SET match_confidence = $2 WHERE id = $1 RETURNING *;

-- name: DeleteTransactionMatch :exec
DELETE FROM transaction_matches WHERE id = $1;

-- name: DeleteTransactionMatchesByLink :exec
DELETE FROM transaction_matches WHERE account_link_id = $1;

-- name: SetTransactionAttributedUser :exec
UPDATE transactions SET attributed_user_id = $2, updated_at = NOW() WHERE id = $1;

-- name: ClearTransactionAttributedUserByLink :exec
UPDATE transactions t SET attributed_user_id = NULL, updated_at = NOW()
FROM transaction_matches tm
WHERE tm.primary_transaction_id = t.id AND tm.account_link_id = $1;
