mirror of Walter-Sparrow / lunar-tear
1-- +goose Up
2
3PRAGMA foreign_keys = ON;
4
5-- =============================================================================
6-- 1. Identity and Sessions
7-- =============================================================================
8
9CREATE TABLE users (
10 user_id INTEGER PRIMARY KEY,
11 uuid TEXT NOT NULL UNIQUE,
12 player_id INTEGER NOT NULL DEFAULT 0,
13 os_type INTEGER NOT NULL DEFAULT 0,
14 platform_type INTEGER NOT NULL DEFAULT 0,
15 user_restriction_type INTEGER NOT NULL DEFAULT 0,
16 register_datetime INTEGER NOT NULL DEFAULT 0,
17 game_start_datetime INTEGER NOT NULL DEFAULT 0,
18 latest_version INTEGER NOT NULL DEFAULT 0,
19 birth_year INTEGER NOT NULL DEFAULT 0,
20 birth_month INTEGER NOT NULL DEFAULT 0,
21 backup_token TEXT NOT NULL DEFAULT '',
22 charge_money_this_month INTEGER NOT NULL DEFAULT 0
23);
24
25CREATE TABLE sessions (
26 session_key TEXT PRIMARY KEY,
27 user_id INTEGER NOT NULL REFERENCES users(user_id),
28 uuid TEXT NOT NULL,
29 expire_at TEXT NOT NULL
30);
31
32-- =============================================================================
33-- 1b. Per-User 1:1 State Tables
34-- =============================================================================
35
36CREATE TABLE user_setting (
37 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
38 is_notify_purchase_alert INTEGER NOT NULL DEFAULT 0,
39 latest_version INTEGER NOT NULL DEFAULT 0
40);
41
42CREATE TABLE user_status (
43 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
44 level INTEGER NOT NULL DEFAULT 0,
45 exp INTEGER NOT NULL DEFAULT 0,
46 stamina_milli_value INTEGER NOT NULL DEFAULT 0,
47 stamina_update_datetime INTEGER NOT NULL DEFAULT 0,
48 latest_version INTEGER NOT NULL DEFAULT 0
49);
50
51CREATE TABLE user_gem (
52 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
53 paid_gem INTEGER NOT NULL DEFAULT 0,
54 free_gem INTEGER NOT NULL DEFAULT 0
55);
56
57CREATE TABLE user_profile (
58 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
59 name TEXT NOT NULL DEFAULT '',
60 name_update_datetime INTEGER NOT NULL DEFAULT 0,
61 message TEXT NOT NULL DEFAULT '',
62 message_update_datetime INTEGER NOT NULL DEFAULT 0,
63 favorite_costume_id INTEGER NOT NULL DEFAULT 0,
64 favorite_costume_id_update_datetime INTEGER NOT NULL DEFAULT 0,
65 latest_version INTEGER NOT NULL DEFAULT 0
66);
67
68CREATE TABLE user_login (
69 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
70 total_login_count INTEGER NOT NULL DEFAULT 0,
71 continual_login_count INTEGER NOT NULL DEFAULT 0,
72 max_continual_login_count INTEGER NOT NULL DEFAULT 0,
73 last_login_datetime INTEGER NOT NULL DEFAULT 0,
74 last_comeback_login_datetime INTEGER NOT NULL DEFAULT 0,
75 latest_version INTEGER NOT NULL DEFAULT 0
76);
77
78CREATE TABLE user_login_bonus (
79 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
80 login_bonus_id INTEGER NOT NULL DEFAULT 0,
81 current_page_number INTEGER NOT NULL DEFAULT 0,
82 current_stamp_number INTEGER NOT NULL DEFAULT 0,
83 latest_reward_receive_datetime INTEGER NOT NULL DEFAULT 0,
84 latest_version INTEGER NOT NULL DEFAULT 0
85);
86
87CREATE TABLE user_main_quest (
88 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
89 current_quest_flow_type INTEGER NOT NULL DEFAULT 0,
90 current_main_quest_route_id INTEGER NOT NULL DEFAULT 0,
91 current_quest_scene_id INTEGER NOT NULL DEFAULT 0,
92 head_quest_scene_id INTEGER NOT NULL DEFAULT 0,
93 is_reached_last_quest_scene INTEGER NOT NULL DEFAULT 0,
94 progress_quest_scene_id INTEGER NOT NULL DEFAULT 0,
95 progress_head_quest_scene_id INTEGER NOT NULL DEFAULT 0,
96 progress_quest_flow_type INTEGER NOT NULL DEFAULT 0,
97 main_quest_season_id INTEGER NOT NULL DEFAULT 0,
98 latest_version INTEGER NOT NULL DEFAULT 0,
99 saved_current_quest_scene_id INTEGER NOT NULL DEFAULT 0,
100 saved_head_quest_scene_id INTEGER NOT NULL DEFAULT 0,
101 replay_flow_current_quest_scene_id INTEGER NOT NULL DEFAULT 0,
102 replay_flow_head_quest_scene_id INTEGER NOT NULL DEFAULT 0
103);
104
105CREATE TABLE user_event_quest (
106 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
107 current_event_quest_chapter_id INTEGER NOT NULL DEFAULT 0,
108 current_quest_id INTEGER NOT NULL DEFAULT 0,
109 current_quest_scene_id INTEGER NOT NULL DEFAULT 0,
110 head_quest_scene_id INTEGER NOT NULL DEFAULT 0,
111 latest_version INTEGER NOT NULL DEFAULT 0
112);
113
114CREATE TABLE user_extra_quest (
115 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
116 current_quest_id INTEGER NOT NULL DEFAULT 0,
117 current_quest_scene_id INTEGER NOT NULL DEFAULT 0,
118 head_quest_scene_id INTEGER NOT NULL DEFAULT 0,
119 latest_version INTEGER NOT NULL DEFAULT 0
120);
121
122CREATE TABLE user_side_story_active (
123 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
124 current_side_story_quest_id INTEGER NOT NULL DEFAULT 0,
125 current_side_story_quest_scene_id INTEGER NOT NULL DEFAULT 0,
126 latest_version INTEGER NOT NULL DEFAULT 0
127);
128
129CREATE TABLE user_big_hunt_state (
130 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
131 current_big_hunt_boss_quest_id INTEGER NOT NULL DEFAULT 0,
132 current_big_hunt_quest_id INTEGER NOT NULL DEFAULT 0,
133 current_quest_scene_id INTEGER NOT NULL DEFAULT 0,
134 is_dry_run INTEGER NOT NULL DEFAULT 0,
135 latest_version INTEGER NOT NULL DEFAULT 0,
136 deck_type INTEGER NOT NULL DEFAULT 0,
137 user_triple_deck_number INTEGER NOT NULL DEFAULT 0,
138 boss_knock_down_count INTEGER NOT NULL DEFAULT 0,
139 max_combo_count INTEGER NOT NULL DEFAULT 0,
140 total_damage INTEGER NOT NULL DEFAULT 0,
141 deck_number INTEGER NOT NULL DEFAULT 0,
142 battle_binary BLOB
143);
144
145CREATE TABLE user_battle (
146 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
147 is_active INTEGER NOT NULL DEFAULT 0,
148 start_count INTEGER NOT NULL DEFAULT 0,
149 finish_count INTEGER NOT NULL DEFAULT 0,
150 last_started_at INTEGER NOT NULL DEFAULT 0,
151 last_finished_at INTEGER NOT NULL DEFAULT 0,
152 last_user_party_count INTEGER NOT NULL DEFAULT 0,
153 last_npc_party_count INTEGER NOT NULL DEFAULT 0,
154 last_battle_binary_size INTEGER NOT NULL DEFAULT 0,
155 last_elapsed_frame_count INTEGER NOT NULL DEFAULT 0
156);
157
158CREATE TABLE user_notification (
159 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
160 gift_not_receive_count INTEGER NOT NULL DEFAULT 0,
161 friend_request_receive_count INTEGER NOT NULL DEFAULT 0,
162 is_exist_unread_information INTEGER NOT NULL DEFAULT 0
163);
164
165CREATE TABLE user_portal_cage (
166 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
167 is_current_progress INTEGER NOT NULL DEFAULT 0,
168 drop_item_start_datetime INTEGER NOT NULL DEFAULT 0,
169 current_drop_item_count INTEGER NOT NULL DEFAULT 0,
170 latest_version INTEGER NOT NULL DEFAULT 0
171);
172
173CREATE TABLE user_guerrilla_free_open (
174 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
175 start_datetime INTEGER NOT NULL DEFAULT 0,
176 open_minutes INTEGER NOT NULL DEFAULT 0,
177 daily_opened_count INTEGER NOT NULL DEFAULT 0,
178 latest_version INTEGER NOT NULL DEFAULT 0
179);
180
181CREATE TABLE user_explore (
182 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
183 is_use_explore_ticket INTEGER NOT NULL DEFAULT 0,
184 playing_explore_id INTEGER NOT NULL DEFAULT 0,
185 latest_play_datetime INTEGER NOT NULL DEFAULT 0,
186 latest_version INTEGER NOT NULL DEFAULT 0
187);
188
189CREATE TABLE user_shop_replaceable (
190 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
191 lineup_update_count INTEGER NOT NULL DEFAULT 0,
192 latest_lineup_update_datetime INTEGER NOT NULL DEFAULT 0,
193 latest_version INTEGER NOT NULL DEFAULT 0
194);
195
196CREATE TABLE user_gacha (
197 user_id INTEGER PRIMARY KEY REFERENCES users(user_id),
198 reward_available INTEGER NOT NULL DEFAULT 0,
199 todays_current_draw_count INTEGER NOT NULL DEFAULT 0,
200 daily_max_count INTEGER NOT NULL DEFAULT 0,
201 last_reward_draw_date INTEGER NOT NULL DEFAULT 0,
202 obtain_consumable_item_id INTEGER,
203 obtain_count INTEGER
204);
205
206-- =============================================================================
207-- 2. Characters and Progression
208-- =============================================================================
209
210CREATE TABLE user_characters (
211 user_id INTEGER NOT NULL REFERENCES users(user_id),
212 character_id INTEGER NOT NULL,
213 level INTEGER NOT NULL DEFAULT 0,
214 exp INTEGER NOT NULL DEFAULT 0,
215 latest_version INTEGER NOT NULL DEFAULT 0,
216 PRIMARY KEY (user_id, character_id)
217);
218
219CREATE TABLE user_character_boards (
220 user_id INTEGER NOT NULL REFERENCES users(user_id),
221 character_board_id INTEGER NOT NULL,
222 panel_release_bit1 INTEGER NOT NULL DEFAULT 0,
223 panel_release_bit2 INTEGER NOT NULL DEFAULT 0,
224 panel_release_bit3 INTEGER NOT NULL DEFAULT 0,
225 panel_release_bit4 INTEGER NOT NULL DEFAULT 0,
226 latest_version INTEGER NOT NULL DEFAULT 0,
227 PRIMARY KEY (user_id, character_board_id)
228);
229
230CREATE TABLE user_character_board_abilities (
231 user_id INTEGER NOT NULL REFERENCES users(user_id),
232 character_id INTEGER NOT NULL,
233 ability_id INTEGER NOT NULL,
234 level INTEGER NOT NULL DEFAULT 0,
235 latest_version INTEGER NOT NULL DEFAULT 0,
236 PRIMARY KEY (user_id, character_id, ability_id)
237);
238
239CREATE TABLE user_character_board_status_ups (
240 user_id INTEGER NOT NULL REFERENCES users(user_id),
241 character_id INTEGER NOT NULL,
242 status_calculation_type INTEGER NOT NULL,
243 hp INTEGER NOT NULL DEFAULT 0,
244 attack INTEGER NOT NULL DEFAULT 0,
245 vitality INTEGER NOT NULL DEFAULT 0,
246 agility INTEGER NOT NULL DEFAULT 0,
247 critical_ratio INTEGER NOT NULL DEFAULT 0,
248 critical_attack INTEGER NOT NULL DEFAULT 0,
249 latest_version INTEGER NOT NULL DEFAULT 0,
250 PRIMARY KEY (user_id, character_id, status_calculation_type)
251);
252
253CREATE TABLE user_character_rebirths (
254 user_id INTEGER NOT NULL REFERENCES users(user_id),
255 character_id INTEGER NOT NULL,
256 rebirth_count INTEGER NOT NULL DEFAULT 0,
257 latest_version INTEGER NOT NULL DEFAULT 0,
258 PRIMARY KEY (user_id, character_id)
259);
260
261-- =============================================================================
262-- 3. Equipment (UUID-keyed)
263-- =============================================================================
264
265CREATE TABLE user_costumes (
266 user_id INTEGER NOT NULL REFERENCES users(user_id),
267 user_costume_uuid TEXT NOT NULL,
268 costume_id INTEGER NOT NULL,
269 limit_break_count INTEGER NOT NULL DEFAULT 0,
270 level INTEGER NOT NULL DEFAULT 0,
271 exp INTEGER NOT NULL DEFAULT 0,
272 headup_display_view_id INTEGER NOT NULL DEFAULT 0,
273 acquisition_datetime INTEGER NOT NULL DEFAULT 0,
274 awaken_count INTEGER NOT NULL DEFAULT 0,
275 latest_version INTEGER NOT NULL DEFAULT 0,
276 PRIMARY KEY (user_id, user_costume_uuid)
277);
278
279CREATE TABLE user_costume_active_skills (
280 user_id INTEGER NOT NULL REFERENCES users(user_id),
281 user_costume_uuid TEXT NOT NULL,
282 level INTEGER NOT NULL DEFAULT 0,
283 acquisition_datetime INTEGER NOT NULL DEFAULT 0,
284 latest_version INTEGER NOT NULL DEFAULT 0,
285 PRIMARY KEY (user_id, user_costume_uuid)
286);
287
288CREATE TABLE user_costume_awaken_status_ups (
289 user_id INTEGER NOT NULL REFERENCES users(user_id),
290 user_costume_uuid TEXT NOT NULL,
291 status_calculation_type INTEGER NOT NULL,
292 hp INTEGER NOT NULL DEFAULT 0,
293 attack INTEGER NOT NULL DEFAULT 0,
294 vitality INTEGER NOT NULL DEFAULT 0,
295 agility INTEGER NOT NULL DEFAULT 0,
296 critical_ratio INTEGER NOT NULL DEFAULT 0,
297 critical_attack INTEGER NOT NULL DEFAULT 0,
298 latest_version INTEGER NOT NULL DEFAULT 0,
299 PRIMARY KEY (user_id, user_costume_uuid, status_calculation_type)
300);
301
302CREATE TABLE user_weapons (
303 user_id INTEGER NOT NULL REFERENCES users(user_id),
304 user_weapon_uuid TEXT NOT NULL,
305 weapon_id INTEGER NOT NULL,
306 level INTEGER NOT NULL DEFAULT 0,
307 exp INTEGER NOT NULL DEFAULT 0,
308 limit_break_count INTEGER NOT NULL DEFAULT 0,
309 is_protected INTEGER NOT NULL DEFAULT 0,
310 acquisition_datetime INTEGER NOT NULL DEFAULT 0,
311 latest_version INTEGER NOT NULL DEFAULT 0,
312 PRIMARY KEY (user_id, user_weapon_uuid)
313);
314
315CREATE TABLE user_weapon_skills (
316 user_id INTEGER NOT NULL REFERENCES users(user_id),
317 user_weapon_uuid TEXT NOT NULL,
318 slot_number INTEGER NOT NULL,
319 level INTEGER NOT NULL DEFAULT 0,
320 PRIMARY KEY (user_id, user_weapon_uuid, slot_number)
321);
322
323CREATE TABLE user_weapon_abilities (
324 user_id INTEGER NOT NULL REFERENCES users(user_id),
325 user_weapon_uuid TEXT NOT NULL,
326 slot_number INTEGER NOT NULL,
327 level INTEGER NOT NULL DEFAULT 0,
328 PRIMARY KEY (user_id, user_weapon_uuid, slot_number)
329);
330
331CREATE TABLE user_weapon_awakens (
332 user_id INTEGER NOT NULL REFERENCES users(user_id),
333 user_weapon_uuid TEXT NOT NULL,
334 latest_version INTEGER NOT NULL DEFAULT 0,
335 PRIMARY KEY (user_id, user_weapon_uuid)
336);
337
338CREATE TABLE user_weapon_stories (
339 user_id INTEGER NOT NULL REFERENCES users(user_id),
340 weapon_id INTEGER NOT NULL,
341 released_max_story_index INTEGER NOT NULL DEFAULT 0,
342 latest_version INTEGER NOT NULL DEFAULT 0,
343 PRIMARY KEY (user_id, weapon_id)
344);
345
346CREATE TABLE user_weapon_notes (
347 user_id INTEGER NOT NULL REFERENCES users(user_id),
348 weapon_id INTEGER NOT NULL,
349 max_level INTEGER NOT NULL DEFAULT 0,
350 max_limit_break_count INTEGER NOT NULL DEFAULT 0,
351 first_acquisition_datetime INTEGER NOT NULL DEFAULT 0,
352 latest_version INTEGER NOT NULL DEFAULT 0,
353 PRIMARY KEY (user_id, weapon_id)
354);
355
356CREATE TABLE user_companions (
357 user_id INTEGER NOT NULL REFERENCES users(user_id),
358 user_companion_uuid TEXT NOT NULL,
359 companion_id INTEGER NOT NULL,
360 headup_display_view_id INTEGER NOT NULL DEFAULT 0,
361 level INTEGER NOT NULL DEFAULT 0,
362 acquisition_datetime INTEGER NOT NULL DEFAULT 0,
363 latest_version INTEGER NOT NULL DEFAULT 0,
364 PRIMARY KEY (user_id, user_companion_uuid)
365);
366
367CREATE TABLE user_thoughts (
368 user_id INTEGER NOT NULL REFERENCES users(user_id),
369 user_thought_uuid TEXT NOT NULL,
370 thought_id INTEGER NOT NULL,
371 acquisition_datetime INTEGER NOT NULL DEFAULT 0,
372 latest_version INTEGER NOT NULL DEFAULT 0,
373 PRIMARY KEY (user_id, user_thought_uuid)
374);
375
376CREATE TABLE user_parts (
377 user_id INTEGER NOT NULL REFERENCES users(user_id),
378 user_parts_uuid TEXT NOT NULL,
379 parts_id INTEGER NOT NULL,
380 level INTEGER NOT NULL DEFAULT 0,
381 parts_status_main_id INTEGER NOT NULL DEFAULT 0,
382 is_protected INTEGER NOT NULL DEFAULT 0,
383 acquisition_datetime INTEGER NOT NULL DEFAULT 0,
384 latest_version INTEGER NOT NULL DEFAULT 0,
385 PRIMARY KEY (user_id, user_parts_uuid)
386);
387
388CREATE TABLE user_parts_group_notes (
389 user_id INTEGER NOT NULL REFERENCES users(user_id),
390 parts_group_id INTEGER NOT NULL,
391 first_acquisition_datetime INTEGER NOT NULL DEFAULT 0,
392 latest_version INTEGER NOT NULL DEFAULT 0,
393 PRIMARY KEY (user_id, parts_group_id)
394);
395
396CREATE TABLE user_parts_presets (
397 user_id INTEGER NOT NULL REFERENCES users(user_id),
398 user_parts_preset_number INTEGER NOT NULL,
399 user_parts_uuid01 TEXT NOT NULL DEFAULT '',
400 user_parts_uuid02 TEXT NOT NULL DEFAULT '',
401 user_parts_uuid03 TEXT NOT NULL DEFAULT '',
402 name TEXT NOT NULL DEFAULT '',
403 user_parts_preset_tag_number INTEGER NOT NULL DEFAULT 0,
404 latest_version INTEGER NOT NULL DEFAULT 0,
405 PRIMARY KEY (user_id, user_parts_preset_number)
406);
407
408-- =============================================================================
409-- 4. Deck System
410-- =============================================================================
411
412CREATE TABLE user_deck_characters (
413 user_id INTEGER NOT NULL REFERENCES users(user_id),
414 user_deck_character_uuid TEXT NOT NULL,
415 user_costume_uuid TEXT NOT NULL DEFAULT '',
416 main_user_weapon_uuid TEXT NOT NULL DEFAULT '',
417 user_companion_uuid TEXT NOT NULL DEFAULT '',
418 power INTEGER NOT NULL DEFAULT 0,
419 user_thought_uuid TEXT NOT NULL DEFAULT '',
420 dressup_costume_id INTEGER NOT NULL DEFAULT 0,
421 latest_version INTEGER NOT NULL DEFAULT 0,
422 PRIMARY KEY (user_id, user_deck_character_uuid)
423);
424
425CREATE TABLE user_decks (
426 user_id INTEGER NOT NULL REFERENCES users(user_id),
427 deck_type INTEGER NOT NULL,
428 user_deck_number INTEGER NOT NULL,
429 user_deck_character_uuid01 TEXT NOT NULL DEFAULT '',
430 user_deck_character_uuid02 TEXT NOT NULL DEFAULT '',
431 user_deck_character_uuid03 TEXT NOT NULL DEFAULT '',
432 name TEXT NOT NULL DEFAULT '',
433 power INTEGER NOT NULL DEFAULT 0,
434 latest_version INTEGER NOT NULL DEFAULT 0,
435 PRIMARY KEY (user_id, deck_type, user_deck_number)
436);
437
438CREATE TABLE user_deck_sub_weapons (
439 user_id INTEGER NOT NULL REFERENCES users(user_id),
440 user_deck_character_uuid TEXT NOT NULL,
441 ordinal INTEGER NOT NULL,
442 user_weapon_uuid TEXT NOT NULL,
443 PRIMARY KEY (user_id, user_deck_character_uuid, ordinal)
444);
445
446CREATE TABLE user_deck_parts (
447 user_id INTEGER NOT NULL REFERENCES users(user_id),
448 user_deck_character_uuid TEXT NOT NULL,
449 ordinal INTEGER NOT NULL,
450 user_parts_uuid TEXT NOT NULL,
451 PRIMARY KEY (user_id, user_deck_character_uuid, ordinal)
452);
453
454CREATE TABLE user_deck_type_notes (
455 user_id INTEGER NOT NULL REFERENCES users(user_id),
456 deck_type INTEGER NOT NULL,
457 max_deck_power INTEGER NOT NULL DEFAULT 0,
458 latest_version INTEGER NOT NULL DEFAULT 0,
459 PRIMARY KEY (user_id, deck_type)
460);
461
462-- =============================================================================
463-- 5. Quests
464-- =============================================================================
465
466CREATE TABLE user_quests (
467 user_id INTEGER NOT NULL REFERENCES users(user_id),
468 quest_id INTEGER NOT NULL,
469 quest_state_type INTEGER NOT NULL DEFAULT 0,
470 is_battle_only INTEGER NOT NULL DEFAULT 0,
471 user_deck_number INTEGER NOT NULL DEFAULT 0,
472 latest_start_datetime INTEGER NOT NULL DEFAULT 0,
473 clear_count INTEGER NOT NULL DEFAULT 0,
474 daily_clear_count INTEGER NOT NULL DEFAULT 0,
475 last_clear_datetime INTEGER NOT NULL DEFAULT 0,
476 shortest_clear_frames INTEGER NOT NULL DEFAULT 0,
477 is_reward_granted INTEGER NOT NULL DEFAULT 0,
478 latest_version INTEGER NOT NULL DEFAULT 0,
479 PRIMARY KEY (user_id, quest_id)
480);
481
482CREATE TABLE user_quest_missions (
483 user_id INTEGER NOT NULL REFERENCES users(user_id),
484 quest_id INTEGER NOT NULL,
485 quest_mission_id INTEGER NOT NULL,
486 progress_value INTEGER NOT NULL DEFAULT 0,
487 is_clear INTEGER NOT NULL DEFAULT 0,
488 latest_clear_datetime INTEGER NOT NULL DEFAULT 0,
489 latest_version INTEGER NOT NULL DEFAULT 0,
490 PRIMARY KEY (user_id, quest_id, quest_mission_id)
491);
492
493CREATE TABLE user_missions (
494 user_id INTEGER NOT NULL REFERENCES users(user_id),
495 mission_id INTEGER NOT NULL,
496 start_datetime INTEGER NOT NULL DEFAULT 0,
497 progress_value INTEGER NOT NULL DEFAULT 0,
498 mission_progress_status_type INTEGER NOT NULL DEFAULT 0,
499 clear_datetime INTEGER NOT NULL DEFAULT 0,
500 latest_version INTEGER NOT NULL DEFAULT 0,
501 PRIMARY KEY (user_id, mission_id)
502);
503
504CREATE TABLE user_side_story_quests (
505 user_id INTEGER NOT NULL REFERENCES users(user_id),
506 side_story_quest_id INTEGER NOT NULL,
507 head_side_story_quest_scene_id INTEGER NOT NULL DEFAULT 0,
508 side_story_quest_state_type INTEGER NOT NULL DEFAULT 0,
509 latest_version INTEGER NOT NULL DEFAULT 0,
510 PRIMARY KEY (user_id, side_story_quest_id)
511);
512
513CREATE TABLE user_quest_limit_content_status (
514 user_id INTEGER NOT NULL REFERENCES users(user_id),
515 limit_content_id INTEGER NOT NULL,
516 limit_content_quest_status_type INTEGER NOT NULL DEFAULT 0,
517 event_quest_chapter_id INTEGER NOT NULL DEFAULT 0,
518 latest_version INTEGER NOT NULL DEFAULT 0,
519 PRIMARY KEY (user_id, limit_content_id)
520);
521
522-- =============================================================================
523-- 6. Big Hunt
524-- =============================================================================
525
526CREATE TABLE user_big_hunt_max_scores (
527 user_id INTEGER NOT NULL REFERENCES users(user_id),
528 big_hunt_boss_id INTEGER NOT NULL,
529 max_score INTEGER NOT NULL DEFAULT 0,
530 max_score_update_datetime INTEGER NOT NULL DEFAULT 0,
531 latest_version INTEGER NOT NULL DEFAULT 0,
532 PRIMARY KEY (user_id, big_hunt_boss_id)
533);
534
535CREATE TABLE user_big_hunt_statuses (
536 user_id INTEGER NOT NULL REFERENCES users(user_id),
537 big_hunt_boss_id INTEGER NOT NULL,
538 daily_challenge_count INTEGER NOT NULL DEFAULT 0,
539 latest_challenge_datetime INTEGER NOT NULL DEFAULT 0,
540 latest_version INTEGER NOT NULL DEFAULT 0,
541 PRIMARY KEY (user_id, big_hunt_boss_id)
542);
543
544CREATE TABLE user_big_hunt_schedule_max_scores (
545 user_id INTEGER NOT NULL REFERENCES users(user_id),
546 big_hunt_schedule_id INTEGER NOT NULL,
547 big_hunt_boss_id INTEGER NOT NULL,
548 max_score INTEGER NOT NULL DEFAULT 0,
549 max_score_update_datetime INTEGER NOT NULL DEFAULT 0,
550 latest_version INTEGER NOT NULL DEFAULT 0,
551 PRIMARY KEY (user_id, big_hunt_schedule_id, big_hunt_boss_id)
552);
553
554CREATE TABLE user_big_hunt_weekly_max_scores (
555 user_id INTEGER NOT NULL REFERENCES users(user_id),
556 big_hunt_weekly_version INTEGER NOT NULL,
557 attribute_type INTEGER NOT NULL,
558 max_score INTEGER NOT NULL DEFAULT 0,
559 latest_version INTEGER NOT NULL DEFAULT 0,
560 PRIMARY KEY (user_id, big_hunt_weekly_version, attribute_type)
561);
562
563CREATE TABLE user_big_hunt_weekly_statuses (
564 user_id INTEGER NOT NULL REFERENCES users(user_id),
565 big_hunt_weekly_version INTEGER NOT NULL,
566 is_received_weekly_reward INTEGER NOT NULL DEFAULT 0,
567 latest_version INTEGER NOT NULL DEFAULT 0,
568 PRIMARY KEY (user_id, big_hunt_weekly_version)
569);
570
571-- =============================================================================
572-- 7. Gimmicks
573-- =============================================================================
574
575CREATE TABLE user_gimmick_progress (
576 user_id INTEGER NOT NULL REFERENCES users(user_id),
577 gimmick_sequence_schedule_id INTEGER NOT NULL,
578 gimmick_sequence_id INTEGER NOT NULL,
579 gimmick_id INTEGER NOT NULL,
580 is_gimmick_cleared INTEGER NOT NULL DEFAULT 0,
581 start_datetime INTEGER NOT NULL DEFAULT 0,
582 latest_version INTEGER NOT NULL DEFAULT 0,
583 PRIMARY KEY (user_id, gimmick_sequence_schedule_id, gimmick_sequence_id, gimmick_id)
584);
585
586CREATE TABLE user_gimmick_ornament_progress (
587 user_id INTEGER NOT NULL REFERENCES users(user_id),
588 gimmick_sequence_schedule_id INTEGER NOT NULL,
589 gimmick_sequence_id INTEGER NOT NULL,
590 gimmick_id INTEGER NOT NULL,
591 gimmick_ornament_index INTEGER NOT NULL,
592 progress_value_bit INTEGER NOT NULL DEFAULT 0,
593 base_datetime INTEGER NOT NULL DEFAULT 0,
594 latest_version INTEGER NOT NULL DEFAULT 0,
595 PRIMARY KEY (user_id, gimmick_sequence_schedule_id, gimmick_sequence_id, gimmick_id, gimmick_ornament_index)
596);
597
598CREATE TABLE user_gimmick_sequences (
599 user_id INTEGER NOT NULL REFERENCES users(user_id),
600 gimmick_sequence_schedule_id INTEGER NOT NULL,
601 gimmick_sequence_id INTEGER NOT NULL,
602 is_gimmick_sequence_cleared INTEGER NOT NULL DEFAULT 0,
603 clear_datetime INTEGER NOT NULL DEFAULT 0,
604 latest_version INTEGER NOT NULL DEFAULT 0,
605 PRIMARY KEY (user_id, gimmick_sequence_schedule_id, gimmick_sequence_id)
606);
607
608CREATE TABLE user_gimmick_unlocks (
609 user_id INTEGER NOT NULL REFERENCES users(user_id),
610 gimmick_sequence_schedule_id INTEGER NOT NULL,
611 gimmick_sequence_id INTEGER NOT NULL,
612 gimmick_id INTEGER NOT NULL,
613 is_unlocked INTEGER NOT NULL DEFAULT 0,
614 latest_version INTEGER NOT NULL DEFAULT 0,
615 PRIMARY KEY (user_id, gimmick_sequence_schedule_id, gimmick_sequence_id, gimmick_id)
616);
617
618-- =============================================================================
619-- 8. Inventory
620-- =============================================================================
621
622CREATE TABLE user_consumable_items (
623 user_id INTEGER NOT NULL REFERENCES users(user_id),
624 consumable_item_id INTEGER NOT NULL,
625 count INTEGER NOT NULL DEFAULT 0,
626 PRIMARY KEY (user_id, consumable_item_id)
627);
628
629CREATE TABLE user_materials (
630 user_id INTEGER NOT NULL REFERENCES users(user_id),
631 material_id INTEGER NOT NULL,
632 count INTEGER NOT NULL DEFAULT 0,
633 PRIMARY KEY (user_id, material_id)
634);
635
636CREATE TABLE user_important_items (
637 user_id INTEGER NOT NULL REFERENCES users(user_id),
638 important_item_id INTEGER NOT NULL,
639 count INTEGER NOT NULL DEFAULT 0,
640 PRIMARY KEY (user_id, important_item_id)
641);
642
643CREATE TABLE user_premium_items (
644 user_id INTEGER NOT NULL REFERENCES users(user_id),
645 premium_item_id INTEGER NOT NULL,
646 count INTEGER NOT NULL DEFAULT 0,
647 PRIMARY KEY (user_id, premium_item_id)
648);
649
650CREATE TABLE user_tutorials (
651 user_id INTEGER NOT NULL REFERENCES users(user_id),
652 tutorial_type INTEGER NOT NULL,
653 progress_phase INTEGER NOT NULL DEFAULT 0,
654 choice_id INTEGER NOT NULL DEFAULT 0,
655 latest_version INTEGER NOT NULL DEFAULT 0,
656 PRIMARY KEY (user_id, tutorial_type)
657);
658
659CREATE TABLE user_explore_scores (
660 user_id INTEGER NOT NULL REFERENCES users(user_id),
661 explore_id INTEGER NOT NULL,
662 max_score INTEGER NOT NULL DEFAULT 0,
663 max_score_update_datetime INTEGER NOT NULL DEFAULT 0,
664 latest_version INTEGER NOT NULL DEFAULT 0,
665 PRIMARY KEY (user_id, explore_id)
666);
667
668CREATE TABLE user_auto_sale_settings (
669 user_id INTEGER NOT NULL REFERENCES users(user_id),
670 possession_auto_sale_item_type INTEGER NOT NULL,
671 possession_auto_sale_item_value TEXT NOT NULL DEFAULT '',
672 PRIMARY KEY (user_id, possession_auto_sale_item_type)
673);
674
675-- =============================================================================
676-- 9. Simple Progress Maps
677-- =============================================================================
678
679CREATE TABLE user_navi_cutin_played (
680 user_id INTEGER NOT NULL REFERENCES users(user_id),
681 navi_cutin_id INTEGER NOT NULL,
682 PRIMARY KEY (user_id, navi_cutin_id)
683);
684
685CREATE TABLE user_viewed_movies (
686 user_id INTEGER NOT NULL REFERENCES users(user_id),
687 movie_id INTEGER NOT NULL,
688 timestamp INTEGER NOT NULL DEFAULT 0,
689 PRIMARY KEY (user_id, movie_id)
690);
691
692CREATE TABLE user_contents_stories (
693 user_id INTEGER NOT NULL REFERENCES users(user_id),
694 contents_story_id INTEGER NOT NULL,
695 timestamp INTEGER NOT NULL DEFAULT 0,
696 PRIMARY KEY (user_id, contents_story_id)
697);
698
699CREATE TABLE user_drawn_omikuji (
700 user_id INTEGER NOT NULL REFERENCES users(user_id),
701 omikuji_id INTEGER NOT NULL,
702 timestamp INTEGER NOT NULL DEFAULT 0,
703 PRIMARY KEY (user_id, omikuji_id)
704);
705
706CREATE TABLE user_dokan_confirmed (
707 user_id INTEGER NOT NULL REFERENCES users(user_id),
708 dokan_id INTEGER NOT NULL,
709 PRIMARY KEY (user_id, dokan_id)
710);
711
712-- =============================================================================
713-- 10. Gifts
714-- =============================================================================
715
716CREATE TABLE user_gifts (
717 user_id INTEGER NOT NULL REFERENCES users(user_id),
718 user_gift_uuid TEXT NOT NULL,
719 is_received INTEGER NOT NULL DEFAULT 0,
720 possession_type INTEGER NOT NULL DEFAULT 0,
721 possession_id INTEGER NOT NULL DEFAULT 0,
722 count INTEGER NOT NULL DEFAULT 0,
723 grant_datetime INTEGER NOT NULL DEFAULT 0,
724 description_gift_text_id INTEGER NOT NULL DEFAULT 0,
725 equipment_data BLOB,
726 expiration_datetime INTEGER,
727 received_datetime INTEGER,
728 PRIMARY KEY (user_id, user_gift_uuid)
729);
730
731-- =============================================================================
732-- 11. Gacha
733-- =============================================================================
734
735CREATE TABLE user_gacha_converted_medals (
736 user_id INTEGER NOT NULL REFERENCES users(user_id),
737 ordinal INTEGER NOT NULL,
738 consumable_item_id INTEGER NOT NULL,
739 count INTEGER NOT NULL DEFAULT 0,
740 PRIMARY KEY (user_id, ordinal)
741);
742
743CREATE TABLE user_gacha_banners (
744 user_id INTEGER NOT NULL REFERENCES users(user_id),
745 gacha_id INTEGER NOT NULL,
746 medal_count INTEGER NOT NULL DEFAULT 0,
747 step_number INTEGER NOT NULL DEFAULT 0,
748 loop_count INTEGER NOT NULL DEFAULT 0,
749 draw_count INTEGER NOT NULL DEFAULT 0,
750 box_number INTEGER NOT NULL DEFAULT 0,
751 PRIMARY KEY (user_id, gacha_id)
752);
753
754CREATE TABLE user_gacha_banner_box_drew_counts (
755 user_id INTEGER NOT NULL REFERENCES users(user_id),
756 gacha_id INTEGER NOT NULL,
757 box_item_id INTEGER NOT NULL,
758 count INTEGER NOT NULL DEFAULT 0,
759 PRIMARY KEY (user_id, gacha_id, box_item_id)
760);
761
762-- =============================================================================
763-- 12. Shop
764-- =============================================================================
765
766CREATE TABLE user_shop_items (
767 user_id INTEGER NOT NULL REFERENCES users(user_id),
768 shop_item_id INTEGER NOT NULL,
769 bought_count INTEGER NOT NULL DEFAULT 0,
770 latest_bought_count_changed_datetime INTEGER NOT NULL DEFAULT 0,
771 latest_version INTEGER NOT NULL DEFAULT 0,
772 PRIMARY KEY (user_id, shop_item_id)
773);
774
775CREATE TABLE user_shop_replaceable_lineup (
776 user_id INTEGER NOT NULL REFERENCES users(user_id),
777 slot_number INTEGER NOT NULL,
778 shop_item_id INTEGER NOT NULL DEFAULT 0,
779 latest_version INTEGER NOT NULL DEFAULT 0,
780 PRIMARY KEY (user_id, slot_number)
781);
782
783-- =============================================================================
784-- 13. Cage Ornaments
785-- =============================================================================
786
787CREATE TABLE user_cage_ornament_rewards (
788 user_id INTEGER NOT NULL REFERENCES users(user_id),
789 cage_ornament_id INTEGER NOT NULL,
790 acquisition_datetime INTEGER NOT NULL DEFAULT 0,
791 latest_version INTEGER NOT NULL DEFAULT 0,
792 PRIMARY KEY (user_id, cage_ornament_id)
793);
794
795-- +goose Down
796
797DROP TABLE IF EXISTS user_cage_ornament_rewards ;
798DROP TABLE IF EXISTS user_shop_replaceable_lineup ;
799DROP TABLE IF EXISTS user_shop_items ;
800DROP TABLE IF EXISTS user_gacha_banner_box_drew_counts;
801DROP TABLE IF EXISTS user_gacha_banners ;
802DROP TABLE IF EXISTS user_gacha_converted_medals ;
803DROP TABLE IF EXISTS user_gifts ;
804DROP TABLE IF EXISTS user_dokan_confirmed ;
805DROP TABLE IF EXISTS user_drawn_omikuji ;
806DROP TABLE IF EXISTS user_contents_stories ;
807DROP TABLE IF EXISTS user_viewed_movies ;
808DROP TABLE IF EXISTS user_navi_cutin_played ;
809DROP TABLE IF EXISTS user_auto_sale_settings ;
810DROP TABLE IF EXISTS user_explore_scores ;
811DROP TABLE IF EXISTS user_tutorials ;
812DROP TABLE IF EXISTS user_premium_items ;
813DROP TABLE IF EXISTS user_important_items ;
814DROP TABLE IF EXISTS user_materials ;
815DROP TABLE IF EXISTS user_consumable_items ;
816DROP TABLE IF EXISTS user_gimmick_unlocks ;
817DROP TABLE IF EXISTS user_gimmick_sequences ;
818DROP TABLE IF EXISTS user_gimmick_ornament_progress ;
819DROP TABLE IF EXISTS user_gimmick_progress ;
820DROP TABLE IF EXISTS user_big_hunt_weekly_statuses ;
821DROP TABLE IF EXISTS user_big_hunt_weekly_max_scores ;
822DROP TABLE IF EXISTS user_big_hunt_schedule_max_scores;
823DROP TABLE IF EXISTS user_big_hunt_statuses ;
824DROP TABLE IF EXISTS user_big_hunt_max_scores ;
825DROP TABLE IF EXISTS user_quest_limit_content_status ;
826DROP TABLE IF EXISTS user_side_story_quests ;
827DROP TABLE IF EXISTS user_missions ;
828DROP TABLE IF EXISTS user_quest_missions ;
829DROP TABLE IF EXISTS user_quests ;
830DROP TABLE IF EXISTS user_deck_type_notes ;
831DROP TABLE IF EXISTS user_deck_parts ;
832DROP TABLE IF EXISTS user_deck_sub_weapons ;
833DROP TABLE IF EXISTS user_decks ;
834DROP TABLE IF EXISTS user_deck_characters ;
835DROP TABLE IF EXISTS user_parts_presets ;
836DROP TABLE IF EXISTS user_parts_group_notes ;
837DROP TABLE IF EXISTS user_parts ;
838DROP TABLE IF EXISTS user_thoughts ;
839DROP TABLE IF EXISTS user_companions ;
840DROP TABLE IF EXISTS user_weapon_notes ;
841DROP TABLE IF EXISTS user_weapon_stories ;
842DROP TABLE IF EXISTS user_weapon_awakens ;
843DROP TABLE IF EXISTS user_weapon_abilities ;
844DROP TABLE IF EXISTS user_weapon_skills ;
845DROP TABLE IF EXISTS user_weapons ;
846DROP TABLE IF EXISTS user_costume_awaken_status_ups ;
847DROP TABLE IF EXISTS user_costume_active_skills ;
848DROP TABLE IF EXISTS user_costumes ;
849DROP TABLE IF EXISTS user_character_rebirths ;
850DROP TABLE IF EXISTS user_character_board_status_ups ;
851DROP TABLE IF EXISTS user_character_board_abilities ;
852DROP TABLE IF EXISTS user_character_boards ;
853DROP TABLE IF EXISTS user_characters ;
854DROP TABLE IF EXISTS user_gacha ;
855DROP TABLE IF EXISTS user_shop_replaceable ;
856DROP TABLE IF EXISTS user_explore ;
857DROP TABLE IF EXISTS user_guerrilla_free_open ;
858DROP TABLE IF EXISTS user_portal_cage ;
859DROP TABLE IF EXISTS user_notification ;
860DROP TABLE IF EXISTS user_battle ;
861DROP TABLE IF EXISTS user_big_hunt_state ;
862DROP TABLE IF EXISTS user_side_story_active ;
863DROP TABLE IF EXISTS user_extra_quest ;
864DROP TABLE IF EXISTS user_event_quest ;
865DROP TABLE IF EXISTS user_main_quest ;
866DROP TABLE IF EXISTS user_login_bonus ;
867DROP TABLE IF EXISTS user_login ;
868DROP TABLE IF EXISTS user_profile ;
869DROP TABLE IF EXISTS user_gem ;
870DROP TABLE IF EXISTS user_status ;
871DROP TABLE IF EXISTS user_setting ;
872DROP TABLE IF EXISTS sessions ;
873DROP TABLE IF EXISTS users ;
874