Skip to content
This repository was archived by the owner on Apr 11, 2026. It is now read-only.

Getting started

Jump to bottom
Diogo Carvalho edited this page Jul 29, 2023 · 20 revisions

Contents

Installation

Clone the Repository

git clone https://github.com/carvalho28/ReQuest.git

Dependencies

yarn dev

Setting up Supabase

Now, create an account in Supabase and a project, you can call it whatever you want.

Updating .env

Change the name of the .env.example in the request-app folder to .env.local and replace the values with the ones found in Project Settings and then selecting API.

NEXT_PUBLIC_SUPABASE_URL=
NEXT_PUBLIC_SUPABASE_ANON_KEY=
NEXT_PUBLIC_IMAGE_DOMAINS=

Create Databases and Functions

Execute the following script inside SQL editor, then + New Queary and execute the code.

-- public.levels definition
CREATE TABLE public.levels (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
    created_at timestamptz NULL DEFAULT now(),
    xp_needed int8 NULL,
    denomination text NULL,
    CONSTRAINT levels_pkey PRIMARY KEY (id)
);

-- public.trophies definition
CREATE TABLE public.trophies (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
    image text NULL,
    "desc" text NULL,
    CONSTRAINT trophies_pkey PRIMARY KEY (id)
);

-- public.projects definition
CREATE TABLE public.projects (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    "name" text NOT NULL,
    description text NULL,
    status text NOT NULL DEFAULT 'Active'::text,
    deadline timestamptz NOT NULL,
    created_at timestamptz NULL DEFAULT now(),
    CONSTRAINT projects_pkey PRIMARY KEY (id)
);

-- public.profiles definition
CREATE TABLE public.profiles (
    id uuid NOT NULL,
    email varchar NOT NULL,
    updated_at timestamptz NULL,
    "name" text NULL,
    avatar_url json NULL DEFAULT '{"skinColor": ["F2AD9B"], "hair": ["bald"], "hairColor": ["362C47"], "facialHair": ["walrus"], "facialHairProbability": 0, "body": ["squared"], "clothingColor": ["456DFF"], "eyes": ["open"], "mouth": ["smile"], "nose": ["mediumRound"], "backgroundColor": ["93A7FF"], "radius": 50 }'::json,
    requirements_completed int8 NULL DEFAULT 0,
    "level" int8 NULL DEFAULT 1,
    xp int8 NULL DEFAULT 0,
    n_projects int8 NULL DEFAULT 0,
    created_at timestamptz NULL,
    CONSTRAINT profiles_email_key UNIQUE (email),
    CONSTRAINT profiles_pkey PRIMARY KEY (id),
    CONSTRAINT profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id),
    CONSTRAINT profiles_level_fkey FOREIGN KEY ("level") REFERENCES public.levels(id)
);

-- public.chats definition
CREATE TABLE public.chats (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    CONSTRAINT chats_pkey PRIMARY KEY (id)
);

-- public.messages definition
CREATE TABLE public.messages (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    chat_id uuid NULL,
    author_id uuid NULL,
    "content" text NULL,
    created_at timestamptz NULL DEFAULT now(),
    CONSTRAINT messages_pkey PRIMARY KEY (id),
    CONSTRAINT messages_author_id_fkey FOREIGN KEY (author_id) REFERENCES public.profiles(id) ON DELETE CASCADE,
    CONSTRAINT messages_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES public.chats(id) ON DELETE CASCADE
);

-- public.chat_users definition
CREATE TABLE public.chat_users (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
    chat_id uuid NULL,
    user_id uuid NULL,
    CONSTRAINT chat_users_pkey PRIMARY KEY (id),
    CONSTRAINT chat_users_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES public.chats(id) ON DELETE CASCADE,
    CONSTRAINT chat_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id) ON DELETE CASCADE
);

-- public.project_profiles definition
CREATE TABLE public.project_profiles (
    id serial4 NOT NULL,
    id_proj uuid NOT NULL,
    id_user uuid NOT NULL,
    CONSTRAINT project_profiles_pkey PRIMARY KEY (id),
    CONSTRAINT project_profiles_id_proj_fkey FOREIGN KEY (id_proj) REFERENCES public.projects(id),
    CONSTRAINT project_profiles_id_user_fkey FOREIGN KEY (id_user) REFERENCES public.profiles(id)
);

-- public.requirements definition
CREATE TABLE public.requirements (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    id_proj uuid NOT NULL,
    "name" text NOT NULL,
    description text NULL DEFAULT '{"default":{"type":"doc","content":[{"type":"paragraph","content":[]}]}}'::text,
    due_date timestamptz NULL,
    priority text NOT NULL DEFAULT 'P3'::text,
    created_at timestamptz NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
    created_by uuid NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now(),
    updated_by uuid NOT NULL,
    assigned_to _text NULL DEFAULT '{}'::text[],
    status text NULL DEFAULT 'Not started'::text,
    closed_at timestamptz NULL,
    closed_by _text NULL,
    "type" text NULL,
    identifier text NULL,
    CONSTRAINT requirements_pkey PRIMARY KEY (id),
    CONSTRAINT requirements_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.profiles(id) ON DELETE CASCADE,
    CONSTRAINT requirements_id_proj_fkey FOREIGN KEY (id_proj) REFERENCES public.projects(id),
    CONSTRAINT requirements_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES public.profiles(id) ON DELETE CASCADE
);

-- public.trophies_profiles definition
CREATE TABLE public.trophies_profiles (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
    id_trophy int8 NULL,
    id_user uuid NULL,
    CONSTRAINT trophies_profiles_pkey PRIMARY KEY (id),
    CONSTRAINT trophies_profiles_id_trophy_fkey FOREIGN KEY (id_trophy) REFERENCES public.trophies(id) ON DELETE CASCADE,
    CONSTRAINT trophies_profiles_id_user_fkey FOREIGN KEY (id_user) REFERENCES public.profiles(id)
);

-- Table Triggers

create trigger update_identifier_trigger before
insert
    or
update
    on
    public.requirements for each row execute function update_identifier();


-- public.trophies_profiles definition

CREATE TABLE public.trophies_profiles (
	id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
	id_trophy int8 NULL,
	id_user uuid NULL,
	CONSTRAINT trophies_profiles_pkey PRIMARY KEY (id),
	CONSTRAINT trophies_profiles_id_trophy_fkey FOREIGN KEY (id_trophy) REFERENCES public.trophies(id) ON DELETE CASCADE,
	CONSTRAINT trophies_profiles_id_user_fkey FOREIGN KEY (id_user) REFERENCES public.profiles(id)
);


CREATE OR REPLACE FUNCTION public.get_chat_id(user_id_1 uuid, user_id_2 uuid)
 RETURNS TABLE(chat_id uuid)
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY
  SELECT cu1.chat_id
  FROM public.chat_users cu1
  JOIN public.chat_users cu2
  ON cu1.chat_id = cu2.chat_id
  WHERE cu1.user_id = user_id_1
  AND cu2.user_id = user_id_2;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.get_connected_users(my_user_id uuid)
 RETURNS TABLE(id uuid, email character varying, name text, avatar_url json)
 LANGUAGE plpgsql
AS $function$begin
      return query
         SELECT 
        profiles.id,
        profiles.email,
        profiles.name,
        profiles.avatar_url
      FROM profiles
      JOIN project_profiles ON project_profiles.id_user = profiles.id
      WHERE project_profiles.id_proj IN (
        SELECT projects.id
        FROM projects
        WHERE projects.id IN (
          SELECT id_proj
          FROM project_profiles
          WHERE id_user = my_user_id
        ) AND id_user != my_user_id
      )
      GROUP BY profiles.id;
      END;$function$
;

CREATE OR REPLACE FUNCTION public.get_latest_closed_requirements(user_id uuid)
 RETURNS TABLE(requirement_name text, project_name text, project_id uuid, closed_at timestamp with time zone)
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY
    SELECT
      requirements.name AS requirement_name,
      projects.name AS project_name,
      projects.id AS project_id,
      requirements.closed_at AS closed_at
    FROM requirements
    JOIN projects ON requirements.id_proj = projects.id
    WHERE requirements.closed_at IS NOT NULL
    AND requirements.created_by = user_id
    ORDER BY closed_at DESC
    LIMIT 3;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.get_user_projects_within_months(user_id uuid, num_months integer)
 RETURNS TABLE(project_name text, deadline timestamp with time zone)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
        SELECT p.name AS project_name, p.deadline
        FROM public.projects p
        INNER JOIN public.project_profiles pp ON p.id = pp.id_proj
        WHERE pp.id_user = user_id
        AND p.deadline BETWEEN now() AND (now() + INTERVAL '1 month' * num_months)
        ORDER BY p.deadline;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.handle_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
begin
  insert into public.profiles (id, email, name)
  values (new.id,
    new.raw_user_meta_data->>'email',
    new.raw_user_meta_data->>'name');
  return new;
end;
$function$
;

CREATE OR REPLACE FUNCTION public.handle_update_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
begin
update public.profiles
set email = new.email
where id = new.id;
  return new;
end;
$function$
;

CREATE OR REPLACE FUNCTION public.increment_requirements_completed(user_ids text[])
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  user_level bigint;
  user_xp bigint;
  xp_needed bigint;
  i integer;
BEGIN
  BEGIN
    FOR i IN 1..array_length(user_ids, 1) LOOP
      RAISE NOTICE 'Processing user %', user_ids[i];
      -- get the current user's level and xp
      SELECT level, xp INTO user_level, user_xp FROM profiles WHERE id = user_ids[i]::uuid;

      -- get the amount of xp needed to level up
      SELECT l.xp_needed INTO xp_needed FROM levels l WHERE l.id = user_level;

      -- increment the requirements_completed column by one and add 5 xp
      UPDATE profiles SET requirements_completed = requirements_completed + 1, xp = xp + 5 WHERE id = user_ids[i]::uuid;

      -- check if xp is full for the current level, and if so, increment the level
      IF user_xp + 5 >= xp_needed THEN
        UPDATE profiles SET level = level + 1, xp = user_xp + 5 - xp_needed WHERE id = user_ids[i]::uuid;
      END IF;
    END LOOP;
  EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'Error processing user %: %', user_ids[i], SQLERRM;
  END;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.projects_user(user_id uuid)
 RETURNS TABLE(id uuid, name text, description text, status text, deadline timestamp with time zone, created_at timestamp with time zone)
 LANGUAGE plpgsql
AS $function$
  begin
    return query
        SELECT p.*
        FROM projects p
        INNER JOIN project_profiles pp ON p.id = pp.id_proj
        WHERE pp.id_user = user_id;
    END;
$function$
;

CREATE OR REPLACE FUNCTION public.projects_user_people(user_id uuid)
 RETURNS TABLE(id uuid, name text, description text, status text, deadline timestamp with time zone, project_users character varying[])
 LANGUAGE plpgsql
AS $function$
    begin
      return query
        SELECT 
        p.id AS id, 
        p.name AS name,
        p.description AS description,
        p.status AS status,
        p.deadline AS deadline,
        ARRAY_REMOVE(
          ARRAY_AGG(CASE WHEN pp2.id_user <> pp.id_user THEN p2.email END), 
          NULL
        ) AS project_users
      FROM projects p 
      JOIN project_profiles pp ON p.id = pp.id_proj 
      JOIN profiles p1 ON pp.id_user = p1.id 
      JOIN project_profiles pp2 ON p.id = pp2.id_proj 
      JOIN profiles p2 ON pp2.id_user = p2.id 
      WHERE p1.id = user_id
      GROUP BY p.id;
    END;
  $function$
;

CREATE OR REPLACE FUNCTION public.projects_user_req(user_id uuid)
 RETURNS TABLE(id uuid, name text, description text, status text, deadline timestamp with time zone, created_at timestamp with time zone, total_reqs bigint, completed_reqs bigint)
 LANGUAGE plpgsql
AS $function$
begin 
return query
    SELECT 
    p.*,
    COUNT(r.id) AS total_reqs,
    COUNT(CASE WHEN r.status = 'Completed' THEN r.id ELSE NULL END) AS completed_reqs
    FROM 
    projects p
    LEFT JOIN requirements r ON p.id = r.id_proj
    INNER JOIN project_profiles pp ON p.id = pp.id_proj
    WHERE 
    pp.id_user = 'db415242-dfb7-41f4-9574-44835caeea3d' AND p.status = 'Active'
    GROUP BY 
    p.id;
    END;
$function$
;

CREATE OR REPLACE FUNCTION public.ranking_req(proj_id uuid)
 RETURNS TABLE(id uuid, name text, avatar_url json, requirements_closed bigint)
 LANGUAGE plpgsql
AS $function$
    begin
      return query
        SELECT p.id, p.name, p.avatar_url, count(r.id) AS requirements_closed
        FROM profiles p
        LEFT JOIN requirements r ON p.id = ANY(r.closed_by::uuid[]) AND r.id_proj = proj_id
        WHERE p.id = ANY(r.closed_by::uuid[])
        GROUP BY p.id, p.name
        ORDER BY requirements_closed DESC;
      END;
  $function$
;

CREATE OR REPLACE FUNCTION public.requirements_user(user_id uuid)
 RETURNS TABLE(id uuid, name text, due_date timestamp with time zone, priority text, status text)
 LANGUAGE plpgsql
AS $function$
    begin
      return query
        SELECT r.id, r.name, r.due_date, r.priority, r.status
        FROM requirements r
        INNER JOIN projects p ON r.id_proj = p.id
        INNER JOIN project_profiles pp ON p.id = pp.id_proj
        WHERE pp.id_user = user_id
        AND r.due_date IS NOT NULL;
      END;
  $function$
;

CREATE OR REPLACE FUNCTION public.update_identifier()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  -- Check if the type has changed
  IF NEW.type IS DISTINCT FROM OLD.type THEN
    -- Generate a new identifier based on the type
    IF LOWER(NEW.type) = 'functional' THEN
      NEW.identifier := 'FR' || LPAD(CAST((SELECT COUNT(*) + 1 FROM public.requirements WHERE id_proj = NEW.id_proj AND LOWER(type) = 'functional') AS TEXT), 2, '0');
    ELSIF LOWER(NEW.type) = 'non-functional' THEN
      NEW.identifier := 'NFR' || LPAD(CAST((SELECT COUNT(*) + 1 FROM public.requirements WHERE id_proj = NEW.id_proj AND LOWER(type) = 'non-functional') AS TEXT), 2, '0');
    ELSE
      RAISE EXCEPTION 'Invalid requirement type';
    END IF;
  END IF;

  -- Check for existing identifiers within the project
  IF NEW.identifier IS NOT NULL THEN
    -- If there is an existing identifier, generate a new one
    WHILE EXISTS (SELECT 1 FROM public.requirements WHERE id_proj = NEW.id_proj AND identifier = NEW.identifier AND id <> NEW.id) LOOP
      -- Extract the numeric portion from the existing identifier
      NEW.identifier := LEFT(NEW.identifier, 3) || LPAD(CAST(RIGHT(NEW.identifier, 2)::INTEGER + 1 AS TEXT), 2, '0');
    END LOOP;
  END IF;

  RETURN NEW;
END;
$function$
;

-- Triggers

create trigger update_identifier_trigger before
insert
    or
update
    on
    public.requirements for each row execute function update_identifier();

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

create trigger on_auth_user_updated after
update
    of email on
    auth.users for each row execute function handle_update_user();

Setting up Auxiliary Servers

Hocuspocus

ChatGPT

Sidebar

Clone this wiki locally