-- Schema: bird__movies_4
CREATE SCHEMA IF NOT EXISTS "bird__movies_4";

-- Table: country
CREATE TABLE "bird__movies_4"."country" (
    "country_id" BIGINT NOT NULL,
    "country_iso_code" TEXT,
    "country_name" TEXT,
    CONSTRAINT "country_pkey" PRIMARY KEY ("country_id")
);

-- Table: department
CREATE TABLE "bird__movies_4"."department" (
    "department_id" BIGINT NOT NULL,
    "department_name" TEXT,
    CONSTRAINT "department_pkey" PRIMARY KEY ("department_id")
);

-- Table: gender
CREATE TABLE "bird__movies_4"."gender" (
    "gender_id" BIGINT NOT NULL,
    "gender" TEXT,
    CONSTRAINT "gender_pkey" PRIMARY KEY ("gender_id")
);

-- Table: genre
CREATE TABLE "bird__movies_4"."genre" (
    "genre_id" BIGINT NOT NULL,
    "genre_name" TEXT,
    CONSTRAINT "genre_pkey" PRIMARY KEY ("genre_id")
);

-- Table: keyword
CREATE TABLE "bird__movies_4"."keyword" (
    "keyword_id" BIGINT NOT NULL,
    "keyword_name" TEXT,
    CONSTRAINT "keyword_pkey" PRIMARY KEY ("keyword_id")
);

-- Table: language
CREATE TABLE "bird__movies_4"."language" (
    "language_id" BIGINT NOT NULL,
    "language_code" TEXT,
    "language_name" TEXT,
    CONSTRAINT "language_pkey" PRIMARY KEY ("language_id")
);

-- Table: language_role
CREATE TABLE "bird__movies_4"."language_role" (
    "role_id" BIGINT NOT NULL,
    "language_role" TEXT,
    CONSTRAINT "language_role_pkey" PRIMARY KEY ("role_id")
);

-- Table: movie
CREATE TABLE "bird__movies_4"."movie" (
    "movie_id" BIGINT NOT NULL,
    "title" TEXT,
    "budget" BIGINT,
    "homepage" TEXT,
    "overview" TEXT,
    "popularity" DOUBLE PRECISION,
    "release_date" DATE,
    "revenue" BIGINT,
    "runtime" BIGINT,
    "movie_status" TEXT,
    "tagline" TEXT,
    "vote_average" DOUBLE PRECISION,
    "vote_count" BIGINT,
    CONSTRAINT "movie_pkey" PRIMARY KEY ("movie_id")
);

-- Table: movie_cast
CREATE TABLE "bird__movies_4"."movie_cast" (
    "movie_id" BIGINT,
    "person_id" BIGINT,
    "character_name" TEXT,
    "gender_id" BIGINT,
    "cast_order" BIGINT
);

-- Table: movie_company
CREATE TABLE "bird__movies_4"."movie_company" (
    "movie_id" BIGINT,
    "company_id" BIGINT
);

-- Table: movie_crew
CREATE TABLE "bird__movies_4"."movie_crew" (
    "movie_id" BIGINT,
    "person_id" BIGINT,
    "department_id" BIGINT,
    "job" TEXT
);

-- Table: movie_genres
CREATE TABLE "bird__movies_4"."movie_genres" (
    "movie_id" BIGINT,
    "genre_id" BIGINT
);

-- Table: movie_keywords
CREATE TABLE "bird__movies_4"."movie_keywords" (
    "movie_id" BIGINT,
    "keyword_id" BIGINT
);

-- Table: movie_languages
CREATE TABLE "bird__movies_4"."movie_languages" (
    "movie_id" BIGINT,
    "language_id" BIGINT,
    "language_role_id" BIGINT
);

-- Table: person
CREATE TABLE "bird__movies_4"."person" (
    "person_id" BIGINT NOT NULL,
    "person_name" TEXT,
    CONSTRAINT "person_pkey" PRIMARY KEY ("person_id")
);

-- Table: production_company
CREATE TABLE "bird__movies_4"."production_company" (
    "company_id" BIGINT NOT NULL,
    "company_name" TEXT,
    CONSTRAINT "production_company_pkey" PRIMARY KEY ("company_id")
);

-- Table: production_country
CREATE TABLE "bird__movies_4"."production_country" (
    "movie_id" BIGINT,
    "country_id" BIGINT
);

-- Foreign Keys
ALTER TABLE "bird__movies_4"."movie_cast" ADD CONSTRAINT "fk_movie_cast_2" FOREIGN KEY ("gender_id") REFERENCES "bird__movies_4"."gender" ("gender_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__movies_4"."movie_crew" ADD CONSTRAINT "fk_movie_crew_2" FOREIGN KEY ("department_id") REFERENCES "bird__movies_4"."department" ("department_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__movies_4"."movie_genres" ADD CONSTRAINT "fk_movie_genres_1" FOREIGN KEY ("genre_id") REFERENCES "bird__movies_4"."genre" ("genre_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__movies_4"."movie_languages" ADD CONSTRAINT "fk_movie_languages_0" FOREIGN KEY ("language_role_id") REFERENCES "bird__movies_4"."language_role" ("role_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__movies_4"."movie_languages" ADD CONSTRAINT "fk_movie_languages_2" FOREIGN KEY ("language_id") REFERENCES "bird__movies_4"."language" ("language_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__movies_4"."production_country" ADD CONSTRAINT "fk_production_country_1" FOREIGN KEY ("country_id") REFERENCES "bird__movies_4"."country" ("country_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
