Fix mapping of store chat team members in SQL
Due to a very old bug which is described in #515 (closed) store chat conversations are very much mixed up. While MR !887 (merged) is working on it for a long time there is need for cleanup before but at least afterwards.
This can be done via ~"tech SQL / DB". This issue is about creating SQL statements to check and clean up wrong data around store chat conversations. I believe it being a good idea executing this query as soon as possible as many useres do have a very positive effect after it.
Table: fs_betrieb_team
betrieb_id
foodsaver_id
-
active
(0 = remove or do not add to conversation, 1 = team member, 2 = springer/jumper member)
Table: fs_betrieb
-
id
(=betrieb_id
) -
team_conversation_id
(-->conversation_id
) (use this forfs_betrieb_team
.active
= 1) -
springer_conversation_id
(-->conversation_id
) (use this forfs_betrieb_team
.active
= 1 AND 2)
Table: fs_foodsaver_has_conversation
conversation_id
foodsaver_id
-
unread
(There need to put more thoughts into this. Should probably be set to 0 as people would think the last message is new otherwise)
Table: fs_conversation
id
-
(not needed for this query - have a look)start_foodsaver_id
-
(not needed for this query - have a look)last_foodsaver_id
-
locked
(maybelocked
is correctly used for "managed chats" like store conversations here – not sure)
Other maybe related cleanup tasks or things to keep in mind:
- Check and delete foodsavers out of
fs_foodsaver_has_conversation
table where they are not a member offs_betrieb_team
anymore. This could be done by dropping the relatedfs_conversation
table before. - Maybe check, if there are deleted members in those tables. Like
fs_betrieb_team
- Manually created conversations do have
0
aslocked
status - do not accidentally create duplicated/new conversation ids for store chats
- Maybe check if there are stores without conversation id's. Note: 1-person stores do have a
conversation_id
.
A really dirty way would be putting this script(s) to our nightly maintenance scripts in the meantime until the bug is fixed:
https://gitlab.com/foodsharing-dev/foodsharing/-/blob/master/src/Modules/Maintenance/MaintenanceControl.php#L119