WIP - Tier List Save Endpoint

[HackerMasterEango]

FEATURE - logged in users can make a tier list, and vote on placements.

will need a database table and server action to save user. In longrun we will have weighted scoring algorithms but for this initial work just.

1. Define a Tierlist schema in database that is flexible enough to support various games.
2. make a server side action with relevant parameters needed to save into these tables.

each game is different so the db schema is complicated here, can maybe get supabase ai chat to help map it but basically would need

1. games table
2. units table referencing their game
3. unit_archetype table referencing

WIP - define schema here.

[Lolergags]

Attempting a schema which contains 6 tables: games, units, categories, tier_lists, tier_list_entries, and votes. All tables have a uuid column.

1. games contains game_name as text, requires unique name.
2. units contains unit_name as text and foreign key to games_id. Units can have non-unique names (since games can have multiple characters with the same name, no tuple uniqueness is enforced).
3. categories contains category_name as text and timestamp. Note that there is no association with a game and names are enforced as unique (ex: dps can be attributed to multiple games). Idea is to let users create their own categories.
4. tier_lists contains an associated game and created timestamp.
5. tier_list_entries has a tier_lists_id, units_id, and categories_id. It also has a unit_rating (int8) and description (text). unit_rating should have the highest value at 0 and increment for each row below, allowing for arbitrary lengths. Additionaly, the following constraint is applied to ensure each category only has one unit per tier list:

   ALTER TABLE tier_list.tier_list_entries
   ADD CONSTRAINT unique_tier_list_and_unit UNIQUE (tier_lists_id, units_id, categories_id);

6. votes has tier_lists_id and user_id (from public.profiles). It stores vote_choice, vote_weight, and timestamp. Ensures each user votes only once per tier list by running:

   ALTER TABLE tier_list.votes
   ADD CONSTRAINT unique_tier_list_user_vote UNIQUE (tier_lists_id, user_id);