(supabase)db.sql

-- ----------------------------
--  Create `profiles` table
-- ----------------------------
create table if not exists
  public.profiles (
    id uuid not null,
    name text null,
    role text not null default 'student'::text,
    avatar_url text null,
    email text null,
    constraint profiles_pkey primary key (id),
    constraint profiles_id_fkey foreign key (id) references auth.users (id) on delete cascade
  );


-- ----------------------------
--  Enable RLS for `profiles`
-- ----------------------------
alter table public.profiles enable row level security;

-- ----------------------------
--  Create policies for `profiles`
-- ----------------------------
drop policy if exists "Public profiles are viewable by everyone." on public.profiles;
create policy "Public profiles are viewable by everyone." on public.profiles for
select
  using (true);

drop policy if exists "Users can insert their own profile." on public.profiles;
create policy "Users can insert their own profile." on public.profiles for
insert
  with check (auth.uid () = id);

drop policy if exists "Users can update own profile." on public.profiles;
create policy "Users can update own profile." on public.profiles for
update
  using (auth.uid () = id);

-- ----------------------------
--  Create `student_keys` table
-- ----------------------------
create table if not exists
  public.student_keys (
    id bigint generated by default as identity,
    student_id uuid not null,
    secret_key uuid not null default gen_random_uuid (),
    created_at timestamp with time zone not null default now(),
    constraint student_keys_pkey primary key (id),
    constraint student_keys_student_id_key unique (student_id),
    constraint student_keys_secret_key_key unique (secret_key),
    constraint student_keys_student_id_fkey foreign key (student_id) references public.profiles (id) on delete cascade
  );


-- ----------------------------
--  Enable RLS for `student_keys`
-- ----------------------------
alter table public.student_keys enable row level security;

-- ----------------------------
--  Create policies for `student_keys`
-- ----------------------------
drop policy if exists "Students can view their own secret key." on public.student_keys;
create policy "Students can view their own secret key." on public.student_keys for
select
  using (auth.uid () = student_id);

-- ----------------------------
--  Create `parent_student_links` table
-- ----------------------------
create table if not exists
  public.parent_student_links (
    id bigint generated by default as identity,
    parent_id uuid not null,
    student_id uuid not null,
    created_at timestamp with time zone not null default now(),
    constraint parent_student_links_pkey primary key (id),
    constraint parent_student_links_parent_id_fkey foreign key (parent_id) references public.profiles (id) on delete cascade,
    constraint parent_student_links_student_id_fkey foreign key (student_id) references public.profiles (id) on delete cascade,
    constraint parent_student_links_parent_id_student_id_key unique (parent_id, student_id)
  );

-- ----------------------------
--  Enable RLS for `parent_student_links`
-- ----------------------------
alter table public.parent_student_links enable row level security;

-- ----------------------------
--  Create policies for `parent_student_links`
-- ----------------------------
drop policy if exists "Parents can view their own links." on public.parent_student_links;
create policy "Parents can view their own links." on public.parent_student_links for
select
  using (auth.uid () = parent_id);

-- ----------------------------
-- Set up storage for avatars
-- ----------------------------
insert into
  storage.buckets (id, name, public)
values
  ('avatars', 'avatars', true)
on conflict (id) do nothing;


drop policy if exists "Avatar images are publicly accessible." on storage.objects;
create policy "Avatar images are publicly accessible." on storage.objects for
select
  using (bucket_id = 'avatars');

drop policy if exists "Anyone can upload an avatar." on storage.objects;
create policy "Anyone can upload an avatar." on storage.objects for
insert
  with check (bucket_id = 'avatars');

drop policy if exists "Users can update their own avatars." on storage.objects;
create policy "Users can update their own avatars." on storage.objects for
update
  using (auth.uid () = owner)
  with check (bucket_id = 'avatars');

drop policy if exists "Users can delete their own avatars." on storage.objects;
create policy "Users can delete their own avatars." on storage.objects for
delete
  using (auth.uid () = owner);


-- ----------------------------
--  Create `exam_settings` table
-- ----------------------------
create table if not exists
  public.exam_settings (
    id uuid not null default gen_random_uuid (),
    user_id uuid not null,
    exam_name text not null,
    created_at timestamp with time zone not null default now(),
    constraint exam_settings_pkey primary key (id),
    constraint exam_settings_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade
  );

alter table public.exam_settings enable row level security;

drop policy if exists "Users can view their own exam settings." on public.exam_settings;
create policy "Users can view their own exam settings." on public.exam_settings for
select
  using (auth.uid () = user_id);

drop policy if exists "Users can insert their own exam settings." on public.exam_settings;
create policy "Users can insert their own exam settings." on public.exam_settings for
insert
  with check (auth.uid () = user_id);

-- ----------------------------
--  Create `subjects` table
-- ----------------------------
create table if not exists
  public.subjects (
    id bigint generated by default as identity,
    user_id uuid not null,
    name text not null,
    created_at timestamp with time zone not null default now(),
    constraint subjects_pkey primary key (id),
    constraint subjects_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade,
    constraint subjects_user_id_name_key unique (user_id, name)
  );

alter table public.subjects enable row level security;

drop policy if exists "Users can manage their own subjects." on public.subjects;
create policy "Users can manage their own subjects." on public.subjects for all using (auth.uid () = user_id);

-- ----------------------------
--  Create `chapters` table
-- ----------------------------
create table if not exists
  public.chapters (
    id bigint generated by default as identity,
    user_id uuid not null,
    subject_id bigint not null,
    name text not null,
    created_at timestamp with time zone not null default now(),
    constraint chapters_pkey primary key (id),
    constraint chapters_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade,
    constraint chapters_subject_id_fkey foreign key (subject_id) references public.subjects (id) on delete cascade,
    constraint chapters_subject_id_name_key unique (subject_id, name)
  );

alter table public.chapters enable row level security;

drop policy if exists "Users can manage their own chapters." on public.chapters;
create policy "Users can manage their own chapters." on public.chapters for all using (auth.uid () = user_id);

-- ----------------------------
--  Create `modules` table
-- ----------------------------
create table if not exists
  public.modules (
    id bigint generated by default as identity,
    user_id uuid not null,
    name text not null,
    created_at timestamp with time zone not null default now(),
    constraint modules_pkey primary key (id),
    constraint modules_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade,
    constraint modules_user_id_name_key unique (user_id, name)
  );

alter table public.modules enable row level security;

drop policy if exists "Users can manage their own modules." on public.modules;
create policy "Users can manage their own modules." on public.modules for all using (auth.uid () = user_id);

-- ----------------------------
--  Create `progress` table
-- ----------------------------
create table if not exists
  public.progress (
    id bigint generated by default as identity,
    user_id uuid not null,
    chapter_id bigint not null,
    module_id bigint not null,
    completed boolean not null default false,
    created_at timestamp with time zone not null default now(),
    completed_at timestamp with time zone null,
    constraint progress_pkey primary key (id),
    constraint progress_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade,
    constraint progress_chapter_id_fkey foreign key (chapter_id) references public.chapters (id) on delete cascade,
    constraint progress_module_id_fkey foreign key (module_id) references public.modules (id) on delete cascade,
    constraint progress_user_id_chapter_id_module_id_key unique (user_id, chapter_id, module_id)
  );

alter table public.progress enable row level security;

drop policy if exists "Users can manage their own progress." on public.progress;
create policy "Users can manage their own progress." on public.progress for all using (auth.uid () = user_id);

-- ----------------------------
--  Create `goals` table
-- ----------------------------
create table if not exists
  public.goals (
    id bigint generated by default as identity,
    user_id uuid not null,
    goal_text text not null,
    is_completed boolean not null default false,
    created_at timestamp with time zone not null default now(),
    completed_at timestamp with time zone null,
    constraint goals_pkey primary key (id),
    constraint goals_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade
  );

alter table public.goals enable row level security;

drop policy if exists "Users can manage their own goals." on public.goals;
create policy "Users can manage their own goals." on public.goals for all using (auth.uid () = user_id);

-- ----------------------------
--  Create `question_logs` table
-- ----------------------------
create table if not exists
  public.question_logs (
    id bigint generated by default as identity,
    user_id uuid not null,
    chapter_id bigint not null,
    total_questions integer not null,
    correct_questions integer not null,
    incorrect_questions integer not null,
    created_at timestamp with time zone not null default now(),
    constraint question_logs_pkey primary key (id),
    constraint question_logs_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade,
    constraint question_logs_chapter_id_fkey foreign key (chapter_id) references public.chapters (id) on delete cascade
  );

alter table public.question_logs enable row level security;

drop policy if exists "Users can manage their own question logs." on public.question_logs;
create policy "Users can manage their own question logs." on public.question_logs for all using (auth.uid () = user_id);

-- ----------------------------
--  Create `test_categories` table
-- ----------------------------
create table if not exists
  public.test_categories (
    id bigint generated by default as identity,
    user_id uuid not null,
    name text not null,
    created_at timestamp with time zone not null default now(),
    constraint test_categories_pkey primary key (id),
    constraint test_categories_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade,
    constraint test_categories_user_id_name_key unique (user_id, name)
  );

alter table public.test_categories enable row level security;

drop policy if exists "Users can manage their own test categories." on public.test_categories;
create policy "Users can manage their own test categories." on public.test_categories for all using (auth.uid () = user_id);

-- ----------------------------
--  Create `test_results` table
-- ----------------------------
create table if not exists
  public.test_results (
    id bigint generated by default as identity,
    user_id uuid not null,
    name text not null,
    test_date timestamp with time zone not null,
    test_type text not null,
    subjects integer[] null,
    chapters integer[] null,
    total_marks numeric not null,
    marks_for_correct numeric not null,
    negative_marks numeric not null,
    correct_questions integer null,
    incorrect_questions integer null,
    unattempted_questions integer null,
    total_marks_obtained numeric null,
    status text not null default 'completed'::text,
    category_id bigint null,
    created_at timestamp with time zone not null default now(),
    constraint test_results_pkey primary key (id),
    constraint test_results_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade,
    constraint test_results_category_id_fkey foreign key (category_id) references public.test_categories (id) on delete set null
  );

alter table public.test_results enable row level security;

drop policy if exists "Users can manage their own test results." on public.test_results;
create policy "Users can manage their own test results." on public.test_results for all using (auth.uid () = user_id);


-- ----------------------------
--  Drop existing functions and triggers before creating new ones
-- ----------------------------
drop function if exists public.handle_new_user() cascade;
drop function if exists public.create_student_key_if_not_exists(p_student_id uuid) cascade;
drop function if exists public.get_goal_analytics_data(p_user_id uuid) cascade;
drop function if exists public.get_linked_students(p_parent_id uuid) cascade;
drop function if exists public.get_question_analytics_data(p_user_id uuid) cascade;
drop function if exists public.get_student_analysis_data(p_user_id uuid) cascade;
drop function if exists public.get_student_dashboard_overview(p_user_id uuid) cascade;
drop function if exists public.get_test_analytics_data(p_user_id uuid) cascade;
drop function if exists public.get_user_progress(p_user_id uuid) cascade;
drop function if exists public.link_student_by_key(p_parent_id uuid, p_secret_key uuid) cascade;
drop function if exists public.reset_student_key(p_user_id uuid) cascade;
drop function if exists public.setup_user_exam(p_user_id uuid, p_exam_name text, p_subjects text[], p_modules text[]) cascade;
drop function if exists public.update_test_result(p_test_id bigint, p_name text, p_category_id bigint, p_test_date timestamp with time zone, p_total_marks numeric, p_marks_for_correct numeric, p_negative_marks numeric, p_correct_questions integer, p_incorrect_questions integer, p_unattempted_questions integer, p_status text) cascade;


-- ----------------------------
--  Create `handle_new_user` function and trigger
-- ----------------------------
create function
  public.handle_new_user () returns trigger as $$
begin
  insert into public.profiles (id, name, role, email)
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'role', new.email);
  return new;
end;
$$ language plpgsql security definer;

create trigger on_auth_user_created
after insert on auth.users for each row
execute procedure public.handle_new_user ();


-- ----------------------------
--  Create `create_student_key_if_not_exists` function
-- ----------------------------
create function
  public.create_student_key_if_not_exists (p_student_id uuid) returns uuid as $$
declare
  existing_key uuid;
  new_key uuid;
begin
  select secret_key into existing_key from public.student_keys where student_id = p_student_id;
  if existing_key is not null then
    return existing_key;
  else
    insert into public.student_keys (student_id) values (p_student_id) returning secret_key into new_key;
    return new_key;
  end if;
end;
$$ language plpgsql security definer;


-- ----------------------------
--  Create `link_student_by_key` function
-- ----------------------------
create function
  public.link_student_by_key (p_parent_id uuid, p_secret_key uuid) returns void as $$
declare
  student_user_id uuid;
begin
  select student_id into student_user_id from public.student_keys where secret_key = p_secret_key;

  if student_user_id is null then
    raise exception 'Invalid secret key';
  end if;

  insert into public.parent_student_links (parent_id, student_id)
  values (p_parent_id, student_user_id)
  on conflict (parent_id, student_id) do nothing;
end;
$$ language plpgsql security definer;


-- ----------------------------
--  Create `get_linked_students` function
-- ----------------------------
create function
  public.get_linked_students (p_parent_id uuid) returns table (id uuid, name text) as $$
begin
  return query
  select p.id, p.name
  from public.parent_student_links psl
  join public.profiles p on psl.student_id = p.id
  where psl.parent_id = p_parent_id;
end;
$$ language plpgsql;


-- ----------------------------
--  Create `setup_user_exam` function
-- ----------------------------
create function
  public.setup_user_exam(p_user_id uuid, p_exam_name text, p_subjects text[], p_modules text[]) returns void as $$
declare
  subject_name text;
  module_name text;
begin
  -- Insert exam setting
  insert into public.exam_settings (user_id, exam_name) values (p_user_id, p_exam_name);

  -- Insert subjects
  foreach subject_name in array p_subjects
  loop
    insert into public.subjects (user_id, name) values (p_user_id, subject_name);
  end loop;

  -- Insert modules
  foreach module_name in array p_modules
  loop
    insert into public.modules (user_id, name) values (p_user_id, module_name);
  end loop;
end;
$$ language plpgsql;


-- ----------------------------
--  Create `get_user_progress` function
-- ----------------------------
create function
  public.get_user_progress (p_user_id uuid) returns json as $$
declare
  result json;
begin
  select
    json_build_object(
      'exam_setting', (select to_json(es) from exam_settings es where es.user_id = p_user_id limit 1),
      'subjects', (select json_agg(s) from subjects s where s.user_id = p_user_id),
      'chapters', (select json_agg(c) from chapters c where c.user_id = p_user_id),
      'modules', (select json_agg(m) from modules m where m.user_id = p_user_id),
      'progress', (select json_agg(p) from progress p where p.user_id = p_user_id)
    )
  into result;
  
  return result;
end;
$$ language plpgsql;


-- ----------------------------
--  Create `get_goal_analytics_data` function
-- ----------------------------
create function
  public.get_goal_analytics_data (p_user_id uuid) returns json as $$
begin
  return (
    select json_build_object(
      'all_goals', (select json_agg(g) from goals g where g.user_id = p_user_id)
    )
  );
end;
$$ language plpgsql;


-- ----------------------------
--  Create `get_question_analytics_data` function
-- ----------------------------
create function
  public.get_question_analytics_data (p_user_id uuid) returns json as $$
begin
  return (
    select json_build_object(
      'subjects', (select json_agg(s) from subjects s where s.user_id = p_user_id),
      'chapters', (select json_agg(c) from chapters c where c.user_id = p_user_id),
      'logs', (select json_agg(ql) from question_logs ql where ql.user_id = p_user_id),
      'categories', (select json_agg(tc) from test_categories tc where tc.user_id = p_user_id),
      'tests', (select json_agg(tr) from test_results tr where tr.user_id = p_user_id)
    )
  );
end;
$$ language plpgsql;


-- ----------------------------
--  Create `get_test_analytics_data` function
-- ----------------------------
create function
  public.get_test_analytics_data (p_user_id uuid) returns json as $$
begin
  return (
    select json_build_object(
      'subjects', (select json_agg(s) from subjects s where s.user_id = p_user_id),
      'chapters', (select json_agg(c) from chapters c where c.user_id = p_user_id),
      'tests', (select json_agg(tr) from test_results tr where tr.user_id = p_user_id),
      'categories', (select json_agg(tc) from test_categories tc where tc.user_id = p_user_id)
    )
  );
end;
$$ language plpgsql;


-- ----------------------------
--  Create `update_test_result` function
-- ----------------------------
create or replace function
  public.update_test_result(
    p_test_id bigint,
    p_name text,
    p_category_id bigint,
    p_test_date timestamptz,
    p_total_marks numeric,
    p_marks_for_correct numeric,
    p_negative_marks numeric,
    p_correct_questions int,
    p_incorrect_questions int,
    p_unattempted_questions int,
    p_status text
  ) returns void as $$
declare
  total_obtained numeric;
begin
  if p_status = 'completed' then
    total_obtained := (p_correct_questions * p_marks_for_correct) - (p_incorrect_questions * p_negative_marks);
  else
    total_obtained := null;
  end if;

  update public.test_results
  set
    name = p_name,
    category_id = p_category_id,
    test_date = p_test_date,
    total_marks = p_total_marks,
    marks_for_correct = p_marks_for_correct,
    negative_marks = p_negative_marks,
    correct_questions = p_correct_questions,
    incorrect_questions = p_incorrect_questions,
    unattempted_questions = p_unattempted_questions,
    total_marks_obtained = total_obtained,
    status = p_status
  where
    id = p_test_id;
end;
$$ language plpgsql;

-- ----------------------------
--  Create `reset_student_key` function
-- ----------------------------
create or replace function public.reset_student_key(p_user_id uuid)
returns void
language plpgsql
security definer
as $function$
begin
  -- Update the secret_key for the specified student_id
  update public.student_keys
  set secret_key = gen_random_uuid()
  where student_id = p_user_id;
end;
$function$;

-- ----------------------------
--  Create `get_student_dashboard_overview` function
-- ----------------------------
create or replace function
  public.get_student_dashboard_overview (p_user_id uuid) returns json as $$
declare
  overview json;
begin
  select
    json_build_object(
      'progress_percent',
      (
        select
          case
            when count(c.id) = 0 or count(m.id) = 0 then 0
            else (count(p.id)::decimal / (count(distinct c.id) * count(distinct m.id)) * 100)
          end
        from
          chapters c
          cross join modules m
          left join progress p on p.chapter_id = c.id
          and p.module_id = m.id and p.completed = true and p.user_id = p_user_id
        where
          c.user_id = p_user_id and m.user_id = p_user_id
      ),
      'subject_progress',
      (
        select
          json_agg(sp)
        from
          (
            select
              s.id as subject_id,
              s.name as subject_name,
              case
                when count(c.id) = 0 or count(m.id) = 0 then 0
                else (count(p.id)::decimal / (count(distinct c.id) * count(distinct m.id)) * 100)
              end as progress_percent
            from
              subjects s
              cross join modules m
              left join chapters c on c.subject_id = s.id
              left join progress p on p.chapter_id = c.id
              and p.module_id = m.id and p.completed = true and p.user_id = p_user_id
            where
              s.user_id = p_user_id and m.user_id = p_user_id
            group by
              s.id,
              s.name
          ) sp
      ),
      'today_goals',
      (
        select json_agg(g)
        from goals g
        where g.user_id = p_user_id and date(g.created_at at time zone 'Asia/Kolkata') = date(now() at time zone 'Asia/Kolkata')
      ),
      'total_questions_logged', (select sum(total_questions) from question_logs where user_id = p_user_id),
      'total_correct_questions', (select sum(correct_questions) from question_logs where user_id = p_user_id),
      'total_incorrect_questions', (select sum(incorrect_questions) from question_logs where user_id = p_user_id),
      'questions_last_7_days', (
        select json_agg(q) from (
          select (i.date)::date::text as date, coalesce(count(ql.id), 0) as count
          from generate_series(
            date_trunc('day', timezone('utc', now())) - interval '6 days',
            date_trunc('day', timezone('utc', now())),
            '1 day'
          ) as i(date)
          left join question_logs ql on date_trunc('day', ql.created_at) = i.date and ql.user_id = p_user_id
          group by i.date
          order by i.date
        ) q
      ),
      'total_tests_completed', (select count(*) from test_results where user_id = p_user_id and status = 'completed'),
      'avg_test_score', (select avg((total_marks_obtained / total_marks) * 100) from test_results where user_id = p_user_id and status = 'completed'),
      'highest_test_score', (select max((total_marks_obtained / total_marks) * 100) from test_results where user_id = p_user_id and status = 'completed'),
      'upcoming_tests', (
        select json_agg(t) from (
          select id, name, test_date
          from test_results
          where user_id = p_user_id and status = 'scheduled' and test_date >= date_trunc('day', timezone('utc', now()))
          order by test_date asc
          limit 5
        ) t
      ),
       'progress_over_time', (
        with daily_progress as (
          select
            p.completed_at::date as date,
            c.subject_id,
            s.name as subject_name,
            count(*) as completed_today
          from progress p
          join chapters c on p.chapter_id = c.id
          join subjects s on c.subject_id = s.id
          where p.user_id = p_user_id and p.completed = true and p.completed_at is not null
          group by 1, 2, 3
        ),
        cumulative_progress as (
          select
            date,
            subject_name,
            sum(completed_today) over (partition by subject_name order by date) as cumulative_completed
          from daily_progress
        ),
        total_modules as (
            select c.subject_id, count(*) as total
            from chapters c, modules m
            where c.user_id = p_user_id and m.user_id = p_user_id
            group by c.subject_id
        ),
        daily_percentage as (
          select
            cp.date::text,
            cp.subject_name,
            (cp.cumulative_completed::decimal / tm.total * 100) as percentage
          from cumulative_progress cp
          join subjects s on s.name = cp.subject_name and s.user_id = p_user_id
          join total_modules tm on tm.subject_id = s.id
        )
        select json_build_object(
          'subjectNames', (select json_agg(distinct subject_name) from daily_percentage),
          'chartData', (
             select json_agg(
               json_build_object(
                 'date', date,
                 'subjects', (select json_object_agg(subject_name, percentage) from daily_percentage where date = d.date)
               )
             )
             from (select distinct date from daily_percentage) d
          )
        )
      ),
      'progress_analysis', (
        select json_build_object('subjects', json_agg(s_analysis))
        from (
            select
                s.id,
                s.name,
                (
                    select count(*)
                    from chapters c, modules m
                    where c.subject_id = s.id and c.user_id = p_user_id and m.user_id = p_user_id
                ) as total_modules,
                (
                    select count(*)
                    from progress p
                    join chapters c on p.chapter_id = c.id
                    where p.user_id = p_user_id and c.subject_id = s.id and p.completed = true
                ) as completed_modules,
                (
                    select json_agg(c_analysis)
                    from (
                        select
                            c.id,
                            c.name,
                            (
                                select json_agg(m.name)
                                from progress p
                                join modules m on p.module_id = m.id
                                where p.user_id = p_user_id and p.chapter_id = c.id and p.completed = true
                            ) as completed_modules
                        from chapters c
                        where c.subject_id = s.id and c.user_id = p_user_id
                    ) c_analysis
                ) as chapters
            from subjects s
            where s.user_id = p_user_id
            group by s.id
        ) s_analysis
      )
    )
  into overview;
  return overview;
end;
$$ language plpgsql;


-- ----------------------------
--  Create `get_student_analysis_data` function
-- ----------------------------
create or replace function
  public.get_student_analysis_data (p_user_id uuid) returns json as $$
declare
  analysis_data json;
begin
  select
    json_build_object(
      'strengths', (
        select json_agg(s)
        from (
          select
            c.name as chapter_name,
            s.name as subject_name,
            (sum(ql.correct_questions)::decimal / sum(ql.total_questions)) * 100 as accuracy
          from question_logs ql
          join chapters c on ql.chapter_id = c.id
          join subjects s on c.subject_id = s.id
          where ql.user_id = p_user_id
          group by c.id, s.id
          having sum(ql.total_questions) > 10 -- Minimum 10 questions to be considered
          and (sum(ql.correct_questions)::decimal / sum(ql.total_questions)) >= 0.8
          order by accuracy desc
          limit 5
        ) s
      ),
      'weaknesses', (
        select json_agg(w)
        from (
           select
            c.name as chapter_name,
            s.name as subject_name,
            (sum(ql.correct_questions)::decimal / sum(ql.total_questions)) * 100 as accuracy
          from question_logs ql
          join chapters c on ql.chapter_id = c.id
          join subjects s on c.subject_id = s.id
          where ql.user_id = p_user_id
          group by c.id, s.id
          having sum(ql.total_questions) > 10 -- Minimum 10 questions to be considered
          and (sum(ql.correct_questions)::decimal / sum(ql.total_questions)) < 0.6
          order by accuracy asc
          limit 5
        ) w
      )
    )
  into analysis_data;
  return analysis_data;
end;
$$ language plpgsql;

2nd query

-- This script fixes the security policy for the 'goals' table.
-- It allows users to properly update their goals (e.g., check them as complete).

-- 1. Drop the old, potentially conflicting policies on the 'goals' table.
DROP POLICY IF EXISTS "Users can manage their own goals." ON public.goals;
DROP POLICY IF EXISTS "Users can only update their own goals created today." ON public.goals;


-- 2. Create the new, correct policy.
-- This policy allows a user to perform any action (select, insert, update, delete)
-- on a goal, as long as they are the user who created it.
CREATE POLICY "Users can manage their own goals."
ON public.goals
FOR ALL
USING (auth.uid() = user_id);



3rd 





-- Create the revisions table
CREATE TABLE IF NOT EXISTS public.revisions (
    id bigint generated by default as identity primary key,
    user_id uuid references public.profiles not null,
    chapter_id bigint references public.chapters not null,
    revision_count integer not null default 0,
    created_at timestamp with time zone default timezone('utc'::text, now()) not null,
    updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
    constraint revisions_user_id_chapter_id_key unique (user_id, chapter_id)
);
ALTER TABLE public.revisions ENABLE ROW LEVEL SECURITY;

-- Create policy for revisions table
DROP POLICY IF EXISTS "Enable all access for users based on user_id" ON "public"."revisions";
CREATE POLICY "Enable all access for users based on user_id" ON "public"."revisions"
AS PERMISSIVE FOR ALL
TO public
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Function to get revision data
CREATE OR REPLACE FUNCTION get_revision_data(p_user_id uuid)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN (
    SELECT json_build_object(
      'subjects', (
        SELECT json_agg(
          json_build_object(
            'id', s.id,
            'name', s.name,
            'chapters', (
              SELECT json_agg(
                json_build_object(
                  'id', c.id,
                  'name', c.name,
                  'revision_count', coalesce(r.revision_count, 0)
                )
              )
              FROM chapters c
              LEFT JOIN revisions r ON c.id = r.chapter_id AND r.user_id = p_user_id
              WHERE c.subject_id = s.id AND c.user_id = p_user_id
            )
          )
        )
        FROM subjects s
        WHERE s.user_id = p_user_id
      )
    )
  );
END;
$$;

-- Function to update revision count
CREATE OR REPLACE FUNCTION update_revision_count(p_user_id uuid, p_chapter_id bigint, p_increment int)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO revisions (user_id, chapter_id, revision_count, created_at, updated_at)
  VALUES (p_user_id, p_chapter_id, p_increment, now(), now())
  ON CONFLICT (user_id, chapter_id)
  DO UPDATE SET 
    revision_count = GREATEST(0, revisions.revision_count + p_increment),
    updated_at = now();
END;
$$;



4th




DROP TABLE IF EXISTS public.revisions;
DROP FUNCTION IF EXISTS public.get_revision_data();
DROP FUNCTION IF EXISTS public.update_revision_count(p_user_id uuid, p_chapter_id integer, p_increment integer);

CREATE TABLE public.revision_logs (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
    chapter_id integer NOT NULL REFERENCES public.chapters(id) ON DELETE CASCADE,
    created_at timestamp with time zone NOT NULL DEFAULT now()
);

ALTER TABLE public.revision_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage their own revision logs"
ON public.revision_logs
FOR ALL
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

CREATE OR REPLACE FUNCTION get_revision_data(p_user_id uuid)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    subjects_json json;
    chapters_json json;
    logs_json json;
BEGIN
    SELECT json_agg(s) INTO subjects_json FROM (
        SELECT id, name, created_at FROM public.subjects WHERE user_id = p_user_id ORDER BY created_at
    ) s;

    SELECT json_agg(c) INTO chapters_json FROM (
        SELECT id, subject_id, name, created_at FROM public.chapters WHERE user_id = p_user_id ORDER BY created_at
    ) c;

    SELECT json_agg(l) INTO logs_json FROM (
        SELECT id, user_id, chapter_id, created_at FROM public.revision_logs WHERE user_id = p_user_id
    ) l;

    RETURN json_build_object(
        'subjects', subjects_json,
        'chapters', chapters_json,
        'logs', logs_json
    );
END;
$$;


CREATE OR REPLACE FUNCTION add_revision_log(p_user_id uuid, p_chapter_id integer)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    INSERT INTO public.revision_logs (user_id, chapter_id)
    VALUES (p_user_id, p_chapter_id);
END;
$$;

CREATE OR REPLACE FUNCTION delete_latest_revision_log(p_user_id uuid, p_chapter_id integer)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    latest_log_id bigint;
BEGIN
    SELECT id INTO latest_log_id
    FROM public.revision_logs
    WHERE user_id = p_user_id AND chapter_id = p_chapter_id
    ORDER BY created_at DESC
    LIMIT 1;

    IF latest_log_id IS NOT NULL THEN
        DELETE FROM public.revision_logs
        WHERE id = latest_log_id;
    END IF;
END;
$$;

GRANT EXECUTE ON FUNCTION public.get_revision_data(uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION public.add_revision_log(uuid, integer) TO authenticated;
GRANT EXECUTE ON FUNCTION public.delete_latest_revision_log(uuid, integer) TO authenticated;



5th


-- First, enable Row Level Security on the progress table if it's not already.
ALTER TABLE public.progress ENABLE ROW LEVEL SECURITY;

-- Drop all the conflicting policies I asked you to add, and your original one, to start clean.
DROP POLICY IF EXISTS "Allow authenticated users to read their own progress" ON public.progress;
DROP POLICY IF EXISTS "Allow authenticated users to insert their own progress" ON public.progress;
DROP POLICY IF EXISTS "Allow authenticated users to update their own progress" ON public.progress;
DROP POLICY IF EXISTS "Allow authenticated users to delete their own progress" ON public.progress;
DROP POLICY IF EXISTS "Users can manage their own progress." ON public.progress; -- Dropping your old one too, just in case.

-- Create ONE SINGLE policy for all actions (SELECT, INSERT, UPDATE, DELETE).
-- This is simpler and will correctly handle the "upsert" operation.
CREATE POLICY "Users can manage their own progress"
ON public.progress
FOR ALL
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);



6th


-- Add the 'position' column to the 'subjects' table if it doesn't exist.
ALTER TABLE public.subjects ADD COLUMN IF NOT EXISTS "position" integer NOT NULL DEFAULT 0;

-- Backfill positions for existing subjects.
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT DISTINCT user_id FROM public.subjects LOOP
        WITH ordered_subjects AS (
            SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) as rn
            FROM public.subjects WHERE user_id = rec.user_id
        )
        UPDATE public.subjects s SET position = o.rn
        FROM ordered_subjects o
        WHERE s.id = o.id AND s.user_id = rec.user_id;
    END LOOP;
END $$;

-- Drop all old policies on the 'subjects' table to prevent conflicts.
DROP POLICY IF EXISTS "Users can manage their own subjects" ON public.subjects;
DROP POLICY IF EXISTS "Allow users to read their own subjects" ON public.subjects;
DROP POLICY IF EXISTS "Allow users to insert their own subjects" ON public.subjects;
DROP POLICY IF EXISTS "Allow users to update their own subjects" ON public.subjects;
DROP POLICY IF EXISTS "Allow users to delete their own subjects" ON public.subjects;

-- Create specific, correct policies for the 'subjects' table.
CREATE POLICY "Allow users to SELECT their own subjects" ON public.subjects FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Allow users to INSERT their own subjects" ON public.subjects FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Allow users to UPDATE their own subjects" ON public.subjects FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Allow users to DELETE their own subjects" ON public.subjects FOR DELETE USING (auth.uid() = user_id);

-- ================================================================= --

-- Add the 'position' column to the 'modules' table if it doesn't exist.
ALTER TABLE public.modules ADD COLUMN IF NOT EXISTS "position" integer NOT NULL DEFAULT 0;

-- Backfill positions for existing modules.
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT DISTINCT user_id FROM public.modules LOOP
        WITH ordered_modules AS (
            SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) as rn
            FROM public.modules WHERE user_id = rec.user_id
        )
        UPDATE public.modules m SET position = o.rn
        FROM ordered_modules o
        WHERE m.id = o.id AND m.user_id = rec.user_id;
    END LOOP;
END $$;

-- Drop all old policies on the 'modules' table to prevent conflicts.
DROP POLICY IF EXISTS "Users can manage their own modules" ON public.modules;
DROP POLICY IF EXISTS "Allow users to read their own modules" ON public.modules;
DROP POLICY IF EXISTS "Allow users to insert their own modules" ON public.modules;
DROP POLICY IF EXISTS "Allow users to update their own modules" ON public.modules;
DROP POLICY IF EXISTS "Allow users to delete their own modules" ON public.modules;

-- Create specific, correct policies for the 'modules' table.
CREATE POLICY "Allow users to SELECT their own modules" ON public.modules FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Allow users to INSERT their own modules" ON public.modules FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Allow users to UPDATE their own modules" ON public.modules FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Allow users to DELETE their own modules" ON public.modules FOR DELETE USING (auth.uid() = user_id);




7th

-- Add a 'color' column to the 'subjects' table if it doesn't already exist.
-- This will store the custom color for each subject.
DO $$
BEGIN
  IF NOT EXISTS(SELECT *
    FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'subjects' AND column_name = 'color')
  THEN
    ALTER TABLE "public"."subjects" ADD COLUMN "color" text;
  END IF;
END $$;



8th


-- Create table for default daily schedule templates
CREATE TABLE IF NOT EXISTS public.schedule_templates (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
    start_time time without time zone NOT NULL,
    end_time time without time zone NOT NULL,
    activity text NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now()
);
-- Add RLS policy for schedule_templates
CREATE POLICY "Users can manage their own schedule templates"
ON public.schedule_templates
FOR ALL
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
-- Enable RLS
ALTER TABLE public.schedule_templates ENABLE ROW LEVEL SECURITY;


-- Create table for daily schedule overrides
CREATE TABLE IF NOT EXISTS public.schedule_overrides (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
    "date" date NOT NULL,
    start_time time without time zone NOT NULL,
    end_time time without time zone NOT NULL,
    activity text NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now()
);
-- Add RLS policy for schedule_overrides
CREATE POLICY "Users can manage their own schedule overrides"
ON public.schedule_overrides
FOR ALL
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
-- Enable RLS
ALTER TABLE public.schedule_overrides ENABLE ROW LEVEL SECURITY;


-- Create a function to get schedule data for a user
CREATE OR REPLACE FUNCTION get_schedule_data(p_user_id uuid)
RETURNS json
LANGUAGE plpgsql
AS $$
DECLARE
    template_data json;
    overrides_data json;
BEGIN
    SELECT json_agg(
        json_build_object(
            'id', id,
            'start_time', start_time,
            'end_time', end_time,
            'activity', activity
        ) ORDER BY start_time
    )
    INTO template_data
    FROM schedule_templates
    WHERE user_id = p_user_id;

    SELECT json_agg(
        json_build_object(
            'id', id,
            'date', date,
            'start_time', start_time,
            'end_time', end_time,
            'activity', activity
        ) ORDER BY date, start_time
    )
    INTO overrides_data
    FROM schedule_overrides
    WHERE user_id = p_user_id;

    RETURN json_build_object(
        'templates', COALESCE(template_data, '[]'::json),
        'overrides', COALESCE(overrides_data, '[]'::json)
    );
END;
$$;


-- Create a function to save (upsert) schedule data
CREATE OR REPLACE FUNCTION save_schedule_template(
    p_user_id uuid,
    p_items jsonb
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    -- Delete existing template for the user
    DELETE FROM schedule_templates WHERE user_id = p_user_id;
    
    -- Insert new template items
    INSERT INTO schedule_templates (user_id, start_time, end_time, activity)
    SELECT
        p_user_id,
        (item->>'start_time')::time,
        (item->>'end_time')::time,
        item->>'activity'
    FROM jsonb_array_elements(p_items) AS item;
END;
$$;

-- Create a function to save (upsert) override data for a specific day
CREATE OR REPLACE FUNCTION save_schedule_overrides_for_day(
    p_user_id uuid,
    p_date date,
    p_items jsonb
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    -- Delete existing overrides for the user for that specific date
    DELETE FROM schedule_overrides WHERE user_id = p_user_id AND "date" = p_date;
    
    -- Insert new override items if any
    IF jsonb_array_length(p_items) > 0 THEN
        INSERT INTO schedule_overrides (user_id, "date", start_time, end_time, activity)
        SELECT
            p_user_id,
            p_date,
            (item->>'start_time')::time,
            (item->>'end_time')::time,
            item->>'activity'
        FROM jsonb_array_elements(p_items) AS item;
    END IF;
END;
$$;





9th 


-- Create table for storing study session history
CREATE TABLE IF NOT EXISTS public.study_sessions (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
    start_time timestamptz NOT NULL,
    end_time timestamptz NOT NULL,
    duration integer NOT NULL, -- Duration in seconds
    goal_duration integer, -- Optional goal for the session in minutes
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Add RLS policy for study_sessions
CREATE POLICY "Users can manage their own study sessions"
ON public.study_sessions
FOR ALL
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Enable RLS
ALTER TABLE public.study_sessions ENABLE ROW LEVEL SECURITY;

-- Create a function to get all focus zone data for a user
CREATE OR REPLACE FUNCTION get_focus_zone_data(p_user_id uuid)
RETURNS json
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN (
        SELECT json_build_object(
            'sessions', COALESCE(json_agg(
                json_build_object(
                    'id', s.id,
                    'start_time', s.start_time,
                    'end_time', s.end_time,
                    'duration', s.duration,
                    'goal_duration', s.goal_duration
                ) ORDER BY s.start_time DESC
            ), '[]'::json)
        )
        FROM study_sessions s
        WHERE s.user_id = p_user_id
    );
END;
$$;


10th

-- Add subject and chapter columns to the study_sessions table
ALTER TABLE public.study_sessions
ADD COLUMN subject_id bigint REFERENCES public.subjects(id) ON DELETE SET NULL,
ADD COLUMN chapter_id bigint REFERENCES public.chapters(id) ON DELETE SET NULL;

-- Update the get_focus_zone_data function to include new data
CREATE OR REPLACE FUNCTION get_focus_zone_data(p_user_id uuid)
RETURNS json
LANGUAGE plpgsql
AS $$
DECLARE
    sessions_data json;
    subjects_data json;
    chapters_data json;
BEGIN
    SELECT json_agg(
        json_build_object(
            'id', s.id,
            'start_time', s.start_time,
            'end_time', s.end_time,
            'duration', s.duration,
            'goal_duration', s.goal_duration,
            'subject_id', s.subject_id,
            'chapter_id', s.chapter_id
        ) ORDER BY s.start_time DESC
    )
    INTO sessions_data
    FROM study_sessions s
    WHERE s.user_id = p_user_id;

    SELECT json_agg(json_build_object('id', id, 'name', name))
    INTO subjects_data
    FROM subjects
    WHERE user_id = p_user_id;

    SELECT json_agg(json_build_object('id', id, 'name', name, 'subject_id', subject_id))
    INTO chapters_data
    FROM chapters
    WHERE user_id = p_user_id;

    RETURN json_build_object(
        'sessions', COALESCE(sessions_data, '[]'::json),
        'subjects', COALESCE(subjects_data, '[]'::json),
        'chapters', COALESCE(chapters_data, '[]'::json)
    );
END;
$$;


11th

CREATE OR REPLACE FUNCTION delete_study_session(p_session_id bigint, p_user_id uuid)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM public.study_sessions
    WHERE id = p_session_id AND user_id = p_user_id;
END;
$$;


12th

CREATE OR REPLACE FUNCTION update_study_session(
    p_session_id bigint,
    p_user_id uuid,
    p_duration integer,
    p_subject_id bigint,
    p_chapter_id bigint
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    UPDATE public.study_sessions
    SET
        duration = p_duration,
        subject_id = p_subject_id,
        chapter_id = p_chapter_id
    WHERE
        id = p_session_id AND user_id = p_user_id;
END;
$$;


13th

CREATE OR REPLACE FUNCTION update_study_session(
    p_session_id bigint,
    p_user_id uuid,
    p_duration integer,
    p_subject_id bigint,
    p_chapter_id bigint
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    UPDATE public.study_sessions
    SET
        duration = p_duration,
        subject_id = p_subject_id,
        chapter_id = p_chapter_id
    WHERE
        id = p_session_id AND user_id = p_user_id;
END;
$$;

14th

CREATE OR REPLACE FUNCTION update_study_session(
    p_session_id bigint,
    p_user_id uuid,
    p_duration integer,
    p_subject_id bigint,
    p_chapter_id bigint,
    p_timer_mode text -- Add this new parameter
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    UPDATE public.study_sessions
    SET
        duration = p_duration,
        subject_id = p_subject_id,
        chapter_id = p_chapter_id,
        timer_mode = p_timer_mode -- Add this line to update the column
    WHERE
        id = p_session_id AND user_id = p_user_id;
END;
$$;

15th


CREATE OR REPLACE FUNCTION get_focus_zone_data(p_user_id uuid)
RETURNS json
LANGUAGE plpgsql
AS $$
DECLARE
    result json;
BEGIN
    SELECT json_build_object(
        'sessions', (
            SELECT json_agg(
                json_build_object(
                    'id', ss.id,
                    'start_time', ss.start_time,
                    'end_time', ss.end_time,
                    'duration', ss.duration,
                    'goal_duration', ss.goal_duration,
                    'subject_id', ss.subject_id,
                    'chapter_id', ss.chapter_id,
                    'timer_mode', ss.timer_mode -- Ensure this column is selected
                )
            )
            FROM study_sessions ss
            WHERE ss.user_id = p_user_id
        ),
        'subjects', (
            SELECT json_agg(s.*)
            FROM subjects s
            WHERE s.user_id = p_user_id
        ),
        'chapters', (
            SELECT json_agg(c.*)
            FROM chapters c
            WHERE c.user_id = p_user_id
        )
    ) INTO result;

    RETURN result;
END;
$$;

16th

CREATE OR REPLACE FUNCTION get_focus_zone_data(p_user_id uuid)
RETURNS json
LANGUAGE plpgsql
AS $$
DECLARE
    result json;
BEGIN
    SELECT json_build_object(
        'sessions', (
            SELECT json_agg(
                json_build_object(
                    'id', ss.id,
                    'start_time', ss.start_time,
                    'end_time', ss.end_time,
                    'duration', ss.duration,
                    'goal_duration', ss.goal_duration,
                    'subject_id', ss.subject_id,
                    'chapter_id', ss.chapter_id,
                    'timer_mode', ss.timer_mode -- Ensure this column is selected
                )
            )
            FROM study_sessions ss
            WHERE ss.user_id = p_user_id
        ),
        'subjects', (
            SELECT json_agg(s.*)
            FROM subjects s
            WHERE s.user_id = p_user_id
        ),
        'chapters', (
            SELECT json_agg(c.*)
            FROM chapters c
            WHERE c.user_id = p_user_id
        )
    ) INTO result;

    RETURN result;
END;
$$;

17th

-- Step 1: Add the missing column to your table.
ALTER TABLE public.study_sessions
ADD COLUMN IF NOT EXISTS timer_mode TEXT DEFAULT 'timer' NOT NULL;

-- Step 2: Ensure the save function is correct.
CREATE OR REPLACE FUNCTION save_study_session(
    p_user_id uuid,
    p_start_time timestamptz,
    p_end_time timestamptz,
    p_duration integer,
    p_goal_duration integer,
    p_subject_id bigint,
    p_chapter_id bigint,
    p_timer_mode text
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    INSERT INTO public.study_sessions (user_id, start_time, end_time, duration, goal_duration, subject_id, chapter_id, timer_mode)
    VALUES (p_user_id, p_start_time, p_end_time, p_duration, p_goal_duration, p_subject_id, p_chapter_id, p_timer_mode);
END;
$$;

-- Step 3: Ensure the update function is correct.
CREATE OR REPLACE FUNCTION update_study_session(
    p_session_id bigint,
    p_user_id uuid,
    p_duration integer,
    p_subject_id bigint,
    p_chapter_id bigint,
    p_timer_mode text
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    UPDATE public.study_sessions
    SET
        duration = p_duration,
        subject_id = p_subject_id,
        chapter_id = p_chapter_id,
        timer_mode = p_timer_mode
    WHERE
        id = p_session_id AND user_id = p_user_id;
END;
$$;

--18th