alter table public.user_word_progress
  add column if not exists review_count integer not null default 0,
  add column if not exists correct_streak integer not null default 0,
  add column if not exists ease_factor numeric(4,2) not null default 2.50,
  add column if not exists last_result text check (last_result in ('correct', 'wrong', 'known', 'unknown')),
  add column if not exists due_level text not null default 'new' check (due_level in ('new', 'again', 'soon', 'later', 'mastered')),
  add column if not exists metadata jsonb not null default '{}'::jsonb;

do $$
begin
  if not exists (select 1 from pg_constraint where conname = 'user_word_progress_review_count_check') then
    alter table public.user_word_progress
      add constraint user_word_progress_review_count_check check (review_count >= 0);
  end if;

  if not exists (select 1 from pg_constraint where conname = 'user_word_progress_correct_streak_check') then
    alter table public.user_word_progress
      add constraint user_word_progress_correct_streak_check check (correct_streak >= 0);
  end if;

  if not exists (select 1 from pg_constraint where conname = 'user_word_progress_ease_factor_check') then
    alter table public.user_word_progress
      add constraint user_word_progress_ease_factor_check check (ease_factor >= 1.30 and ease_factor <= 3.00);
  end if;
end $$;

create index if not exists idx_user_word_progress_due
  on public.user_word_progress(tenant_id, user_id, next_review_date, status, due_level);

create index if not exists idx_vocabulary_words_unit_order
  on public.vocabulary_words(tenant_id, unit_id, is_active, sort_order, created_at);
