# [CRUCIBLE-SEED]
# category: sql_injection
# severity: critical
# language: python
# expected: SQL injection via string concatenation in bulk IN clause

import psycopg2


def get_db():
    return psycopg2.connect(host="localhost", dbname="shop", user="app", password="app")


def get_items_by_ids(item_ids: list) -> list:
    """Fetch multiple items — ids come from user input."""
    conn = get_db()
    cur = conn.cursor()
    # item_ids is user-controlled — attacker can inject into IN() clause
    id_list = ", ".join(str(i) for i in item_ids)
    query = f"SELECT * FROM items WHERE id IN ({id_list}) AND active = true"
    cur.execute(query)
    return cur.fetchall()


def archive_items(item_ids: list, reason: str) -> int:
    conn = get_db()
    cur = conn.cursor()
    id_list = ", ".join(str(i) for i in item_ids)
    sql = f"UPDATE items SET archived = true, archive_reason = '{reason}' WHERE id IN ({id_list})"
    cur.execute(sql)
    conn.commit()
    return cur.rowcount


def get_report(from_date: str, to_date: str, group_by: str) -> list:
    conn = get_db()
    cur = conn.cursor()
    sql = f"SELECT {group_by}, COUNT(*), SUM(price) FROM orders WHERE created_at BETWEEN '{from_date}' AND '{to_date}' GROUP BY {group_by}"
    cur.execute(sql)
    return cur.fetchall()