#!/usr/bin/env python3
# SQL check for the "Assignee's group" grouping patch (Redmine #7655, v2).
#
# This script writes test.sql, which builds a minimal schema (users,
# groups_users, issues) and runs SQL of the same shape as the patch generates
# (the joins of IssueQuery#joins_for_assignee_group and
# IssueQuery#assignee_group_statement) for grouped counts, grouped totals and
# ordering. The SQL strings below are written by hand to mirror the patch;
# they are not extracted from Redmine.
#
# Test data:
#   groups 10 "B Team" and 11 "A Team"
#   user 1 in group 10, user 2 in group 11, user 3 in both, user 4 in none
#   issues 100-103 assigned to users 1-4, issue 104 assigned to group 11,
#   issue 105 unassigned
#
# Usage:
#   python3 assignee_group_sql_check.py
#   sqlite3 :memory: < test.sql
#   psql -d <empty database> -f test.sql
#   mysql <empty database> < test.sql
#
# Checked on SQLite 3.45, PostgreSQL 16 and MariaDB 10.11 (same groups,
# counts, totals and ordering on all three, apart from where NULLs sort).
# Not checked on SQL Server.

# Mirrors joins_for_assignee_group / assignee_group_statement in the patch
def subq(ids):
    cond = f"gu.group_id IN ({','.join(map(str,ids))})" if ids else "1=0"
    return f"(SELECT gu.user_id, MIN(gu.group_id) AS group_id FROM groups_users gu WHERE {cond} GROUP BY gu.user_id)"
STMT="COALESCE(assignee_group_self.id, assignee_group_preferred.group_id, assignee_group_any.group_id)"
def joins(pref, vis):
    return " ".join([
      "LEFT OUTER JOIN users assignee_group_self ON assignee_group_self.id = issues.assigned_to_id AND assignee_group_self.type = 'Group'",
      f"LEFT OUTER JOIN {subq(pref)} assignee_group_preferred ON assignee_group_preferred.user_id = issues.assigned_to_id",
      f"LEFT OUTER JOIN {subq(vis)} assignee_group_any ON assignee_group_any.user_id = issues.assigned_to_id",
      f"LEFT OUTER JOIN users assignee_group ON assignee_group.id = {STMT}"])
schema = """
CREATE TABLE users (id INTEGER PRIMARY KEY, type VARCHAR(30), lastname VARCHAR(255));
CREATE TABLE groups_users (group_id INTEGER, user_id INTEGER);
CREATE TABLE issues (id INTEGER PRIMARY KEY, assigned_to_id INTEGER, estimated_hours FLOAT);
INSERT INTO users VALUES (10,'Group','B Team'),(11,'Group','A Team'),(12,'GroupNonMember','Non member'),
 (1,'User','a'),(2,'User','b'),(3,'User','ab'),(4,'User','none');
INSERT INTO groups_users VALUES (10,1),(11,2),(10,3),(11,3);
INSERT INTO issues VALUES (100,1,1.5),(101,2,4),(102,3,2),(103,4,NULL),(104,11,NULL),(105,NULL,NULL);
"""
cases = {"no filter":([], [10,11]), "filter=11":([11],[10,11]), "only 11 visible":([],[11]), "no visible":([],[])}
out=[schema]
for name,(pref,vis) in cases.items():
    j=joins(pref,vis)
    out.append(f"SELECT '{name}: count' AS label;")
    out.append(f"SELECT COUNT(*) AS count_all, {STMT} AS grp FROM issues {j} GROUP BY {STMT} ORDER BY grp;")
    out.append(f"SELECT SUM(issues.estimated_hours) AS total, {STMT} AS grp FROM issues {j} GROUP BY {STMT} ORDER BY grp;")
    out.append(f"SELECT issues.id, assignee_group.lastname FROM issues {j} ORDER BY assignee_group.lastname ASC, issues.id DESC;")
open("test.sql","w").write("\n".join(out)+"\n")