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

-- Table: address
CREATE TABLE "bird__books"."address" (
    "address_id" BIGINT NOT NULL,
    "street_number" TEXT,
    "street_name" TEXT,
    "city" TEXT,
    "country_id" BIGINT,
    CONSTRAINT "address_pkey" PRIMARY KEY ("address_id")
);

-- Table: address_status
CREATE TABLE "bird__books"."address_status" (
    "status_id" BIGINT NOT NULL,
    "address_status" TEXT,
    CONSTRAINT "address_status_pkey" PRIMARY KEY ("status_id")
);

-- Table: author
CREATE TABLE "bird__books"."author" (
    "author_id" BIGINT NOT NULL,
    "author_name" TEXT,
    CONSTRAINT "author_pkey" PRIMARY KEY ("author_id")
);

-- Table: book
CREATE TABLE "bird__books"."book" (
    "book_id" BIGINT NOT NULL,
    "title" TEXT,
    "isbn13" TEXT,
    "language_id" BIGINT,
    "num_pages" BIGINT,
    "publication_date" DATE,
    "publisher_id" BIGINT,
    CONSTRAINT "book_pkey" PRIMARY KEY ("book_id")
);

-- Table: book_author
CREATE TABLE "bird__books"."book_author" (
    "book_id" BIGINT NOT NULL,
    "author_id" BIGINT NOT NULL,
    CONSTRAINT "book_author_pkey" PRIMARY KEY ("book_id", "author_id")
);

-- Table: book_language
CREATE TABLE "bird__books"."book_language" (
    "language_id" BIGINT NOT NULL,
    "language_code" TEXT,
    "language_name" TEXT,
    CONSTRAINT "book_language_pkey" PRIMARY KEY ("language_id")
);

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

-- Table: cust_order
CREATE TABLE "bird__books"."cust_order" (
    "order_id" BIGINT NOT NULL,
    "order_date" TIMESTAMP,
    "customer_id" BIGINT,
    "shipping_method_id" BIGINT,
    "dest_address_id" BIGINT,
    CONSTRAINT "cust_order_pkey" PRIMARY KEY ("order_id")
);

-- Table: customer
CREATE TABLE "bird__books"."customer" (
    "customer_id" BIGINT NOT NULL,
    "first_name" TEXT,
    "last_name" TEXT,
    "email" TEXT,
    CONSTRAINT "customer_pkey" PRIMARY KEY ("customer_id")
);

-- Table: customer_address
CREATE TABLE "bird__books"."customer_address" (
    "customer_id" BIGINT NOT NULL,
    "address_id" BIGINT NOT NULL,
    "status_id" BIGINT,
    CONSTRAINT "customer_address_pkey" PRIMARY KEY ("customer_id", "address_id")
);

-- Table: order_history
CREATE TABLE "bird__books"."order_history" (
    "history_id" BIGINT NOT NULL,
    "order_id" BIGINT,
    "status_id" BIGINT,
    "status_date" TIMESTAMP,
    CONSTRAINT "order_history_pkey" PRIMARY KEY ("history_id")
);

-- Table: order_line
CREATE TABLE "bird__books"."order_line" (
    "line_id" BIGINT NOT NULL,
    "order_id" BIGINT,
    "book_id" BIGINT,
    "price" DOUBLE PRECISION,
    CONSTRAINT "order_line_pkey" PRIMARY KEY ("line_id")
);

-- Table: order_status
CREATE TABLE "bird__books"."order_status" (
    "status_id" BIGINT NOT NULL,
    "status_value" TEXT,
    CONSTRAINT "order_status_pkey" PRIMARY KEY ("status_id")
);

-- Table: publisher
CREATE TABLE "bird__books"."publisher" (
    "publisher_id" BIGINT NOT NULL,
    "publisher_name" TEXT,
    CONSTRAINT "publisher_pkey" PRIMARY KEY ("publisher_id")
);

-- Table: shipping_method
CREATE TABLE "bird__books"."shipping_method" (
    "method_id" BIGINT NOT NULL,
    "method_name" TEXT,
    "cost" DOUBLE PRECISION,
    CONSTRAINT "shipping_method_pkey" PRIMARY KEY ("method_id")
);

-- Foreign Keys
ALTER TABLE "bird__books"."address" ADD CONSTRAINT "fk_address_0" FOREIGN KEY ("country_id") REFERENCES "bird__books"."country" ("country_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__books"."book" ADD CONSTRAINT "fk_book_0" FOREIGN KEY ("publisher_id") REFERENCES "bird__books"."publisher" ("publisher_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__books"."book" ADD CONSTRAINT "fk_book_1" FOREIGN KEY ("language_id") REFERENCES "bird__books"."book_language" ("language_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__books"."book_author" ADD CONSTRAINT "fk_book_author_0" FOREIGN KEY ("book_id") REFERENCES "bird__books"."book" ("book_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__books"."book_author" ADD CONSTRAINT "fk_book_author_1" FOREIGN KEY ("author_id") REFERENCES "bird__books"."author" ("author_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__books"."customer_address" ADD CONSTRAINT "fk_customer_address_0" FOREIGN KEY ("customer_id") REFERENCES "bird__books"."customer" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__books"."customer_address" ADD CONSTRAINT "fk_customer_address_1" FOREIGN KEY ("address_id") REFERENCES "bird__books"."address" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
