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

-- Table: actor
CREATE TABLE "media__sakila"."actor" (
    "actor_id" NUMERIC NOT NULL,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "actor_pkey" PRIMARY KEY ("actor_id")
);

-- Table: address
CREATE TABLE "media__sakila"."address" (
    "address_id" BIGINT NOT NULL,
    "address" TEXT NOT NULL,
    "address2" TEXT,
    "district" TEXT NOT NULL,
    "city_id" BIGINT NOT NULL,
    "postal_code" TEXT,
    "phone" TEXT NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "address_pkey" PRIMARY KEY ("address_id")
);

-- Table: category
CREATE TABLE "media__sakila"."category" (
    "category_id" INTEGER NOT NULL,
    "name" TEXT NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "category_pkey" PRIMARY KEY ("category_id")
);

-- Table: city
CREATE TABLE "media__sakila"."city" (
    "city_id" BIGINT NOT NULL,
    "city" TEXT NOT NULL,
    "country_id" INTEGER NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "city_pkey" PRIMARY KEY ("city_id")
);

-- Table: country
CREATE TABLE "media__sakila"."country" (
    "country_id" INTEGER NOT NULL,
    "country" TEXT NOT NULL,
    "last_update" TIMESTAMP,
    CONSTRAINT "country_pkey" PRIMARY KEY ("country_id")
);

-- Table: customer
CREATE TABLE "media__sakila"."customer" (
    "customer_id" BIGINT NOT NULL,
    "store_id" BIGINT NOT NULL,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "email" TEXT,
    "address_id" BIGINT NOT NULL,
    "active" TEXT NOT NULL DEFAULT 'Y'::text,
    "create_date" TIMESTAMP NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "customer_pkey" PRIMARY KEY ("customer_id")
);

-- Table: film
CREATE TABLE "media__sakila"."film" (
    "film_id" BIGINT NOT NULL,
    "title" TEXT NOT NULL,
    "description" BYTEA,
    "release_year" TEXT,
    "language_id" INTEGER NOT NULL,
    "original_language_id" INTEGER,
    "rental_duration" INTEGER NOT NULL DEFAULT 3,
    "rental_rate" NUMERIC(4,2) NOT NULL DEFAULT 4.99,
    "length" INTEGER,
    "replacement_cost" NUMERIC(5,2) NOT NULL DEFAULT 19.99,
    "rating" TEXT DEFAULT 'G'::text,
    "special_features" TEXT,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "film_pkey" PRIMARY KEY ("film_id")
);

-- Table: film_actor
CREATE TABLE "media__sakila"."film_actor" (
    "actor_id" BIGINT NOT NULL,
    "film_id" BIGINT NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "film_actor_pkey" PRIMARY KEY ("actor_id", "film_id")
);

-- Table: film_category
CREATE TABLE "media__sakila"."film_category" (
    "film_id" BIGINT NOT NULL,
    "category_id" INTEGER NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "film_category_pkey" PRIMARY KEY ("film_id", "category_id")
);

-- Table: film_text
CREATE TABLE "media__sakila"."film_text" (
    "film_id" INTEGER NOT NULL,
    "title" TEXT NOT NULL,
    "description" BYTEA,
    CONSTRAINT "film_text_pkey" PRIMARY KEY ("film_id")
);

-- Table: inventory
CREATE TABLE "media__sakila"."inventory" (
    "inventory_id" BIGINT NOT NULL,
    "film_id" BIGINT NOT NULL,
    "store_id" BIGINT NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "inventory_pkey" PRIMARY KEY ("inventory_id")
);

-- Table: language
CREATE TABLE "media__sakila"."language" (
    "language_id" INTEGER NOT NULL,
    "name" TEXT NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "language_pkey" PRIMARY KEY ("language_id")
);

-- Table: payment
CREATE TABLE "media__sakila"."payment" (
    "payment_id" BIGINT NOT NULL,
    "customer_id" BIGINT NOT NULL,
    "staff_id" INTEGER NOT NULL,
    "rental_id" BIGINT,
    "amount" NUMERIC(5,2) NOT NULL,
    "payment_date" TIMESTAMP NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "payment_pkey" PRIMARY KEY ("payment_id")
);

-- Table: rental
CREATE TABLE "media__sakila"."rental" (
    "rental_id" BIGINT NOT NULL,
    "rental_date" TIMESTAMP NOT NULL,
    "inventory_id" BIGINT NOT NULL,
    "customer_id" BIGINT NOT NULL,
    "return_date" TIMESTAMP,
    "staff_id" INTEGER NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "rental_pkey" PRIMARY KEY ("rental_id")
);

-- Table: staff
CREATE TABLE "media__sakila"."staff" (
    "staff_id" INTEGER NOT NULL,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "address_id" BIGINT NOT NULL,
    "picture" BYTEA,
    "email" TEXT,
    "store_id" BIGINT NOT NULL,
    "active" INTEGER NOT NULL DEFAULT 1,
    "username" TEXT NOT NULL,
    "password" TEXT,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "staff_pkey" PRIMARY KEY ("staff_id")
);

-- Table: store
CREATE TABLE "media__sakila"."store" (
    "store_id" BIGINT NOT NULL,
    "manager_staff_id" INTEGER NOT NULL,
    "address_id" BIGINT NOT NULL,
    "last_update" TIMESTAMP NOT NULL,
    CONSTRAINT "store_pkey" PRIMARY KEY ("store_id")
);

-- Foreign Keys
ALTER TABLE "media__sakila"."address" ADD CONSTRAINT "fk_address_city" FOREIGN KEY ("city_id") REFERENCES "media__sakila"."city" ("city_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."city" ADD CONSTRAINT "fk_city_country" FOREIGN KEY ("country_id") REFERENCES "media__sakila"."country" ("country_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."customer" ADD CONSTRAINT "fk_customer_address" FOREIGN KEY ("address_id") REFERENCES "media__sakila"."address" ("address_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."customer" ADD CONSTRAINT "fk_customer_store" FOREIGN KEY ("store_id") REFERENCES "media__sakila"."store" ("store_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."film" ADD CONSTRAINT "fk_film_language" FOREIGN KEY ("language_id") REFERENCES "media__sakila"."language" ("language_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__sakila"."film" ADD CONSTRAINT "fk_film_language_original" FOREIGN KEY ("original_language_id") REFERENCES "media__sakila"."language" ("language_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__sakila"."film_actor" ADD CONSTRAINT "fk_film_actor_actor" FOREIGN KEY ("actor_id") REFERENCES "media__sakila"."actor" ("actor_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."film_actor" ADD CONSTRAINT "fk_film_actor_film" FOREIGN KEY ("film_id") REFERENCES "media__sakila"."film" ("film_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."film_category" ADD CONSTRAINT "fk_film_category_category" FOREIGN KEY ("category_id") REFERENCES "media__sakila"."category" ("category_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."film_category" ADD CONSTRAINT "fk_film_category_film" FOREIGN KEY ("film_id") REFERENCES "media__sakila"."film" ("film_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."inventory" ADD CONSTRAINT "fk_inventory_film" FOREIGN KEY ("film_id") REFERENCES "media__sakila"."film" ("film_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."inventory" ADD CONSTRAINT "fk_inventory_store" FOREIGN KEY ("store_id") REFERENCES "media__sakila"."store" ("store_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."payment" ADD CONSTRAINT "fk_payment_customer" FOREIGN KEY ("customer_id") REFERENCES "media__sakila"."customer" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__sakila"."payment" ADD CONSTRAINT "fk_payment_rental" FOREIGN KEY ("rental_id") REFERENCES "media__sakila"."rental" ("rental_id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."payment" ADD CONSTRAINT "fk_payment_staff" FOREIGN KEY ("staff_id") REFERENCES "media__sakila"."staff" ("staff_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__sakila"."rental" ADD CONSTRAINT "fk_rental_customer" FOREIGN KEY ("customer_id") REFERENCES "media__sakila"."customer" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__sakila"."rental" ADD CONSTRAINT "fk_rental_inventory" FOREIGN KEY ("inventory_id") REFERENCES "media__sakila"."inventory" ("inventory_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__sakila"."rental" ADD CONSTRAINT "fk_rental_staff" FOREIGN KEY ("staff_id") REFERENCES "media__sakila"."staff" ("staff_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__sakila"."staff" ADD CONSTRAINT "fk_staff_address" FOREIGN KEY ("address_id") REFERENCES "media__sakila"."address" ("address_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."staff" ADD CONSTRAINT "fk_staff_store" FOREIGN KEY ("store_id") REFERENCES "media__sakila"."store" ("store_id") ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE "media__sakila"."store" ADD CONSTRAINT "fk_store_address" FOREIGN KEY ("address_id") REFERENCES "media__sakila"."address" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__sakila"."store" ADD CONSTRAINT "fk_store_staff" FOREIGN KEY ("manager_staff_id") REFERENCES "media__sakila"."staff" ("staff_id") ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Indexes
CREATE INDEX "idx_actor_last_name" ON "media__sakila"."actor" ("last_name");
CREATE INDEX "idx_fk_city_id" ON "media__sakila"."address" ("city_id");
CREATE INDEX "idx_fk_country_id" ON "media__sakila"."city" ("country_id");
CREATE INDEX "idx_customer_fk_address_id" ON "media__sakila"."customer" ("address_id");
CREATE INDEX "idx_customer_fk_store_id" ON "media__sakila"."customer" ("store_id");
CREATE INDEX "idx_customer_last_name" ON "media__sakila"."customer" ("last_name");
CREATE INDEX "idx_fk_language_id" ON "media__sakila"."film" ("language_id");
CREATE INDEX "idx_fk_original_language_id" ON "media__sakila"."film" ("original_language_id");
CREATE INDEX "idx_fk_film_actor_actor" ON "media__sakila"."film_actor" ("actor_id");
CREATE INDEX "idx_fk_film_actor_film" ON "media__sakila"."film_actor" ("film_id");
CREATE INDEX "idx_fk_film_category_category" ON "media__sakila"."film_category" ("category_id");
CREATE INDEX "idx_fk_film_category_film" ON "media__sakila"."film_category" ("film_id");
CREATE INDEX "idx_fk_film_id" ON "media__sakila"."inventory" ("film_id");
CREATE INDEX "idx_fk_film_id_store_id" ON "media__sakila"."inventory" ("store_id", "film_id");
CREATE INDEX "idx_fk_customer_id" ON "media__sakila"."payment" ("customer_id");
CREATE INDEX "idx_fk_staff_id" ON "media__sakila"."payment" ("staff_id");
CREATE INDEX "idx_rental_fk_customer_id" ON "media__sakila"."rental" ("customer_id");
CREATE INDEX "idx_rental_fk_inventory_id" ON "media__sakila"."rental" ("inventory_id");
CREATE INDEX "idx_rental_fk_staff_id" ON "media__sakila"."rental" ("staff_id");
CREATE INDEX "idx_fk_staff_address_id" ON "media__sakila"."staff" ("address_id");
CREATE INDEX "idx_fk_staff_store_id" ON "media__sakila"."staff" ("store_id");
CREATE INDEX "idx_fk_store_address" ON "media__sakila"."store" ("address_id");
CREATE INDEX "idx_store_fk_manager_staff_id" ON "media__sakila"."store" ("manager_staff_id");
