create table comments (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz default now(),
    topic text not null,
    body text not null,
    user_id uuid not null,
    parent_id int
    mentions uuid[] not null default '{}'::uuid[] 



);

create table reactions (
    type text not null PRIMARY KEY,
    label text not null,
    url text not null,
    metadata jsonb



);

create table comment_reactions (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    comment_id int not null,
    user_id uuid not null,
    type text not null
);

CREATE UNIQUE INDEX comment_reactions_user_id_comment_id_type_key ON comment_reactions USING btree (user_id, comment_id, type);

alter table comment_reactions
add constraint comment_reactions_comment_id_fkey FOREIGN KEY (comment_id) REFERENCES comments (id) ON DELETE CASCADE;

alter table comment_reactions
add constraint comment_reactions_type_fkey FOREIGN KEY (type) REFERENCES reactions;

alter table comment_reactions
add constraint comment_reactions_user_id_comment_id_type_key UNIQUE using index comment_reactions_user_id_comment_id_type_key;

alter table comment_reactions
add constraint comment_reactions_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users (id) ON DELETE CASCADE;

alter table comments
add constraint comments_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES comments (id) ON DELETE CASCADE;

alter table comments
add constraint comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users (id) ON DELETE CASCADE;

create or replace view display_users as  SELECT users.id,
    COALESCE((users.raw_user_meta_data ->> 'name'::text), (users.raw_user_meta_data ->> 'full_name'::text), (users.raw_user_meta_data ->> 'user_name'::text)) AS name,
    COALESCE((users.raw_user_meta_data ->> 'avatar_url'::text), (users.raw_user_meta_data ->> 'avatar'::text)) AS avatar
   FROM auth.users;

create or replace view comment_reactions_metadata as
SELECT comment_reactions.comment_id, comment_reactions.type,
    count(*) AS reaction_count,
bool_or (
    (
        comment_reactions.user_id = auth.uid ()
    )
) AS active_for_user
FROM comment_reactions
GROUP BY
    comment_reactions.comment_id,
    comment_reactions.type
ORDER BY comment_reactions.type;

create or replace view comments_with_metadata as
SELECT
    comments.id,
    comments.created_at,
    comments.topic,
    comments.body,
    comments.user_id,
    comments.parent_id,
    comments.mentions,
    (
        SELECT count(*) AS count
        FROM comments c
        WHERE (c.parent_id = comments.id)
    ) AS replies_count,
    JSONB_BUILD_OBJECT (
        'id',
        u.id,
        'name',
        u.name,
        'avatar',
        u.avatar
    ) AS author
FROM comments
    inner join display_users u on comments.user_id = u.id;

create view comment_meta as
select *
from
    comments_with_metadata c
    LEFT JOIN comment_reactions_metadata r ON c.id = r.comment_id;

-- seed some basic reactions
INSERT into
    reactions (type, label, url)
values (
        'heart',
        'Bulma',
        'https://i.postimg.cc/8zHSsSRD/bulma.webp'
    );

INSERT into
    reactions (type, label, url)
values (
        'like',
        'Like',
        'https://i.postimg.cc/PqVnzQVR/nofucks.webp'
    );

INSERT into
    reactions (type, label, url)
values (
        'suss-cat',
        'Suss Cat',
        'https://i.postimg.cc/sXBdnGtD/suss.webp'
    );