CREATE TABLE IF NOT EXISTS users
(
    id SERIAL,
    first_name character varying,
    last_name character varying,
    email character varying,
    pwd_reset_req boolean DEFAULT false,
    mobile_code character varying,
    mobile_number character varying,
    active boolean DEFAULT true,
    show_landing boolean DEFAULT true,
    user_name character varying NOT NULL,
    password character varying NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_mobile_number_unique UNIQUE (mobile_number)
);

CREATE TABLE IF NOT EXISTS roles
(
    id SERIAL,
    key character varying,
    name character varying,
    description character varying,
    CONSTRAINT roles_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS users_roles
(
    id SERIAL,
    user_id integer,
    role_id integer,
    CONSTRAINT users_roles_pkey PRIMARY KEY (id),
    CONSTRAINT users_roles_role_id_fkey FOREIGN KEY (role_id)
        REFERENCES roles (id),
    CONSTRAINT users_roles_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS blacklisted_tokens
(
    id SERIAL,
    user_id integer,
    token character varying,
    CONSTRAINT blacklisted_tokens_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS block_types
(
    id SERIAL NOT NULL,
    name character varying,
    CONSTRAINT block_types_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS blocks
(
    id SERIAL NOT NULL,
    type_id integer,
    name character varying,
    maturity character varying,
    repo_link character varying,
    type_info character varying,
    CONSTRAINT blocks_pkey PRIMARY KEY (id),
    CONSTRAINT blocks_type_id_fkey FOREIGN KEY (type_id)
        REFERENCES block_types (id)
);

CREATE TABLE IF NOT EXISTS functions
(
    id SERIAL NOT NULL,
    function character varying NOT NULL,
    block_id integer NOT NULL,
    CONSTRAINT functions_pkey PRIMARY KEY (id),
    CONSTRAINT functions_block_id_fkey FOREIGN KEY (block_id)
        REFERENCES blocks (id)
);

CREATE TABLE IF NOT EXISTS variants
(
    id SERIAL NOT NULL,
    name character varying NOT NULL,
    block_id integer NOT NULL,
    CONSTRAINT variant_main_pkey PRIMARY KEY (id),
    CONSTRAINT variants_block_id_fkey FOREIGN KEY (block_id)
        REFERENCES blocks (id)
);

CREATE TABLE IF NOT EXISTS versions
(
    id SERIAL NOT NULL,
    name character varying NOT NULL,
    block_id integer NOT NULL,
    released_at date,
    CONSTRAINT versions_pkey PRIMARY KEY (id),
    CONSTRAINT versions_block_id_fkey FOREIGN KEY (block_id)
        REFERENCES blocks (id)
);

CREATE TABLE IF NOT EXISTS dependents
(
    id integer NOT NULL,
    name character varying NOT NULL,
    block_id integer NOT NULL,
    CONSTRAINT dependents_pkey PRIMARY KEY (id),
    CONSTRAINT dependents_block_id_fkey FOREIGN KEY (block_id)
        REFERENCES blocks (id)
);

CREATE TABLE IF NOT EXISTS block_details
(
    id SERIAL,
    description text,
    usecase text,
    specification text,
    "constraint" text,
    interface text,
    block_id integer,
    usecase_images character varying,
    CONSTRAINT block_details_pkey PRIMARY KEY (id),
    CONSTRAINT block_details_block_id_fkey FOREIGN KEY (block_id)
        REFERENCES public.blocks (id)
);

CREATE TABLE IF NOT EXISTS service_details
(
    id SERIAL,
    block_id integer,
    updated_at timestamp without time zone,
    title character varying,
    "description" character varying,
    flight_hrs integer,
    CONSTRAINT service_details_pkey PRIMARY KEY (id),
    CONSTRAINT service_details_block_id_fkey FOREIGN KEY (block_id)
        REFERENCES public.blocks (id)
);

CREATE TABLE IF NOT EXISTS users_cart
(
    id SERIAL,
    user_id integer,
    block_id integer,
    CONSTRAINT users_cart_pkey PRIMARY KEY (id),
    CONSTRAINT users_cart_block_id_fkey FOREIGN KEY (block_id)
        REFERENCES public.blocks (id),
    CONSTRAINT users_cart_block_id_fkey1 FOREIGN KEY (block_id)
        REFERENCES public.blocks (id),
    CONSTRAINT users_cart_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public.users (id)
);

-- DELETIONS

DELETE FROM public.users_roles;
DELETE FROM public.users;
DELETE FROM public.roles;
DELETE FROM public.dependents;
DELETE FROM public.versions;
DELETE FROM public.variants;
DELETE FROM public.functions;
DELETE FROM public.blocks;
DELETE FROM public.block_types;
