SQLFILE

-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://redmine.postgresql.org/projects/pgadmin4/issues/new if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS public.anime
(
    id integer NOT NULL DEFAULT nextval('anime_id_seq'::regclass),
    anime_name character varying(30) COLLATE pg_catalog."default",
    author_id integer,
    studio_id integer,
    rate double precision DEFAULT 5,
    episodes integer DEFAULT 0,
    genre character varying(30) COLLATE pg_catalog."default",
    img_link text COLLATE pg_catalog."default",
    year_published integer,
    CONSTRAINT anime_pkey PRIMARY KEY (id),
    CONSTRAINT makeunique UNIQUE (anime_name)
);

CREATE TABLE IF NOT EXISTS public.anime_awards
(
    anime_id integer NOT NULL,
    award_name character varying(30) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT anime_awards_pkey PRIMARY KEY (anime_id, award_name)
);

CREATE TABLE IF NOT EXISTS public.author
(
    id integer NOT NULL DEFAULT nextval('author_id_seq'::regclass),
    author_name character varying(100) COLLATE pg_catalog."default",
    years_active integer,
    anime_genre character varying(100) COLLATE pg_catalog."default",
    img_link text COLLATE pg_catalog."default",
    birth_date date,
    CONSTRAINT author_pkey PRIMARY KEY (id),
    CONSTRAINT authorunique UNIQUE (author_name)
);

CREATE TABLE IF NOT EXISTS public.ban
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    ban_reason character varying(100) COLLATE pg_catalog."default",
    user_id integer,
    CONSTRAINT ban_pkey PRIMARY KEY (id),
    CONSTRAINT uniqueuserban UNIQUE (user_id)
);

CREATE TABLE IF NOT EXISTS public."character"
(
    char_id integer NOT NULL DEFAULT nextval('anime_character_id_seq'::regclass),
    character_name character varying(100) COLLATE pg_catalog."default",
    character_role integer,
    va_id integer,
    anime_id integer,
    char_img_link text COLLATE pg_catalog."default",
    CONSTRAINT anime_character_pkey PRIMARY KEY (char_id),
    CONSTRAINT xx UNIQUE (character_name)
);

CREATE TABLE IF NOT EXISTS public.comment
(
    id integer NOT NULL DEFAULT nextval('comment_id_seq'::regclass),
    comment_data text COLLATE pg_catalog."default",
    user_id integer,
    anime_id integer,
    date_published timestamp without time zone,
    CONSTRAINT comment_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.enquiries
(
    id integer NOT NULL DEFAULT nextval('enquiries_id_seq'::regclass),
    user_id integer,
    message text COLLATE pg_catalog."default",
    type integer,
    date_published timestamp without time zone,
    CONSTRAINT enquiries_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.episodes
(
    anime_id integer NOT NULL,
    episode_number integer NOT NULL,
    episode_link text COLLATE pg_catalog."default",
    CONSTRAINT episodes_pkey PRIMARY KEY (anime_id, episode_number)
);

CREATE TABLE IF NOT EXISTS public.favorites
(
    user_id integer NOT NULL,
    anime_id integer NOT NULL,
    CONSTRAINT user_favorite_pkey PRIMARY KEY (user_id, anime_id)
);

CREATE TABLE IF NOT EXISTS public.news
(
    id integer NOT NULL DEFAULT nextval('news_id_seq'::regclass),
    link text COLLATE pg_catalog."default",
    anime_id integer,
    publish_date timestamp without time zone,
    img_link text COLLATE pg_catalog."default",
    CONSTRAINT news_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.ratings
(
    user_id integer NOT NULL,
    anime_id integer NOT NULL,
    rating double precision DEFAULT 5,
    CONSTRAINT ratings_pkey PRIMARY KEY (user_id, anime_id)
);

CREATE TABLE IF NOT EXISTS public.singer
(
    id integer NOT NULL DEFAULT nextval('singer_id_seq'::regclass),
    singer_name character varying(100) COLLATE pg_catalog."default",
    img_link text COLLATE pg_catalog."default",
    birth_date date,
    CONSTRAINT singer_pkey PRIMARY KEY (id),
    CONSTRAINT x2 UNIQUE (singer_name)
);

CREATE TABLE IF NOT EXISTS public.song
(
    id integer NOT NULL DEFAULT nextval('anime_song_id_seq'::regclass),
    song_name character varying(30) COLLATE pg_catalog."default",
    anime_id integer,
    date_published date,
    singer_id integer,
    CONSTRAINT anime_song_pkey PRIMARY KEY (id),
    CONSTRAINT uniquesong UNIQUE (song_name)
);

CREATE TABLE IF NOT EXISTS public.song_type
(
    song_id integer NOT NULL,
    song_type integer NOT NULL,
    CONSTRAINT song_type_pkey PRIMARY KEY (song_id, song_type)
);

CREATE TABLE IF NOT EXISTS public.studio
(
    id integer NOT NULL DEFAULT nextval('anime_studio_id_seq'::regclass),
    studio_name character varying(30) COLLATE pg_catalog."default",
    founder character varying(30) COLLATE pg_catalog."default",
    year_founded integer,
    CONSTRAINT anime_studio_pkey PRIMARY KEY (id),
    CONSTRAINT x1 UNIQUE (studio_name)
);

CREATE TABLE IF NOT EXISTS public.useranime
(
    id integer NOT NULL DEFAULT nextval('myuser_id_seq'::regclass),
    username character varying(100) COLLATE pg_catalog."default",
    password character varying(100) COLLATE pg_catalog."default",
    email character varying(100) COLLATE pg_catalog."default",
    admin integer,
    date_created timestamp without time zone,
    CONSTRAINT useranime_pkey PRIMARY KEY (id),
    CONSTRAINT x12 UNIQUE (username),
    CONSTRAINT x123 UNIQUE (email)
);

CREATE TABLE IF NOT EXISTS public.va
(
    va_id integer NOT NULL DEFAULT nextval('anime_va_id_seq'::regclass),
    va_name character varying(30) COLLATE pg_catalog."default",
    va_img_link text COLLATE pg_catalog."default",
    va_birth_date date,
    CONSTRAINT anime_va_pkey PRIMARY KEY (va_id),
    CONSTRAINT x UNIQUE (va_name)
);

CREATE TABLE IF NOT EXISTS public.va_awards
(
    va_id integer NOT NULL,
    awards character varying(100) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT awards_by_va_pkey PRIMARY KEY (va_id, awards)
);

CREATE TABLE IF NOT EXISTS public.watchlist
(
    user_id integer NOT NULL,
    anime_id integer NOT NULL,
    CONSTRAINT user_watchlist_pkey PRIMARY KEY (user_id, anime_id)
);

ALTER TABLE IF EXISTS public.anime
    ADD CONSTRAINT anime_author_id_fkey FOREIGN KEY (author_id)
    REFERENCES public.author (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.anime
    ADD CONSTRAINT anime_studio_id_fkey FOREIGN KEY (studio_id)
    REFERENCES public.studio (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.anime_awards
    ADD CONSTRAINT anime_awards_anime_id_fkey FOREIGN KEY (anime_id)
    REFERENCES public.anime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.ban
    ADD CONSTRAINT ban_user_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.useranime (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;
CREATE INDEX IF NOT EXISTS uniqueuserban
    ON public.ban(user_id);


ALTER TABLE IF EXISTS public."character"
    ADD CONSTRAINT anime_character_anime_id_fkey FOREIGN KEY (anime_id)
    REFERENCES public.anime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public."character"
    ADD CONSTRAINT anime_character_va_id_fkey FOREIGN KEY (va_id)
    REFERENCES public.va (va_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.comment
    ADD CONSTRAINT comment_myuser_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.useranime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.enquiries
    ADD CONSTRAINT enquiries_user_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.useranime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.episodes
    ADD CONSTRAINT episodes_anime_id_fkey FOREIGN KEY (anime_id)
    REFERENCES public.anime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.favorites
    ADD CONSTRAINT user_favorite_anime_id_fkey FOREIGN KEY (anime_id)
    REFERENCES public.anime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.favorites
    ADD CONSTRAINT user_favorite_useranime_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.useranime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.news
    ADD CONSTRAINT news_anime_id_fkey FOREIGN KEY (anime_id)
    REFERENCES public.anime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.ratings
    ADD CONSTRAINT ratings_anime_id_fkey FOREIGN KEY (anime_id)
    REFERENCES public.anime (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.ratings
    ADD CONSTRAINT ratings_user_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.useranime (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.song
    ADD CONSTRAINT anime_song_anime_id_fkey FOREIGN KEY (anime_id)
    REFERENCES public.anime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.song
    ADD CONSTRAINT anime_song_singer_id_fkey FOREIGN KEY (singer_id)
    REFERENCES public.singer (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.song_type
    ADD CONSTRAINT song_type_song_id_fkey FOREIGN KEY (song_id)
    REFERENCES public.song (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.va_awards
    ADD CONSTRAINT awards_by_va_va_id_fkey FOREIGN KEY (va_id)
    REFERENCES public.va (va_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.watchlist
    ADD CONSTRAINT user_watchlist_anime_id_fkey FOREIGN KEY (anime_id)
    REFERENCES public.anime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.watchlist
    ADD CONSTRAINT user_watchlist_useranime_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.useranime (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

END;