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

-- Table: authors
CREATE TABLE "bird__book_publishing_company"."authors" (
    "au_id" TEXT NOT NULL,
    "au_lname" TEXT NOT NULL,
    "au_fname" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "address" TEXT,
    "city" TEXT,
    "state" TEXT,
    "zip" TEXT,
    "contract" TEXT NOT NULL,
    CONSTRAINT "authors_pkey" PRIMARY KEY ("au_id")
);

-- Table: discounts
CREATE TABLE "bird__book_publishing_company"."discounts" (
    "discounttype" TEXT NOT NULL,
    "stor_id" TEXT,
    "lowqty" BIGINT,
    "highqty" BIGINT,
    "discount" DOUBLE PRECISION NOT NULL
);

-- Table: employee
CREATE TABLE "bird__book_publishing_company"."employee" (
    "emp_id" TEXT NOT NULL,
    "fname" TEXT NOT NULL,
    "minit" TEXT,
    "lname" TEXT NOT NULL,
    "job_id" BIGINT NOT NULL,
    "job_lvl" BIGINT,
    "pub_id" TEXT NOT NULL,
    "hire_date" TIMESTAMP NOT NULL,
    CONSTRAINT "employee_pkey" PRIMARY KEY ("emp_id")
);

-- Table: jobs
CREATE TABLE "bird__book_publishing_company"."jobs" (
    "job_id" BIGINT NOT NULL,
    "job_desc" TEXT NOT NULL,
    "min_lvl" BIGINT NOT NULL,
    "max_lvl" BIGINT NOT NULL,
    CONSTRAINT "jobs_pkey" PRIMARY KEY ("job_id")
);

-- Table: pub_info
CREATE TABLE "bird__book_publishing_company"."pub_info" (
    "pub_id" TEXT NOT NULL,
    "logo" BYTEA,
    "pr_info" TEXT,
    CONSTRAINT "pub_info_pkey" PRIMARY KEY ("pub_id")
);

-- Table: publishers
CREATE TABLE "bird__book_publishing_company"."publishers" (
    "pub_id" TEXT NOT NULL,
    "pub_name" TEXT,
    "city" TEXT,
    "state" TEXT,
    "country" TEXT,
    CONSTRAINT "publishers_pkey" PRIMARY KEY ("pub_id")
);

-- Table: roysched
CREATE TABLE "bird__book_publishing_company"."roysched" (
    "title_id" TEXT NOT NULL,
    "lorange" BIGINT,
    "hirange" BIGINT,
    "royalty" BIGINT
);

-- Table: sales
CREATE TABLE "bird__book_publishing_company"."sales" (
    "stor_id" TEXT NOT NULL,
    "ord_num" TEXT NOT NULL,
    "ord_date" TIMESTAMP NOT NULL,
    "qty" BIGINT NOT NULL,
    "payterms" TEXT NOT NULL,
    "title_id" TEXT NOT NULL,
    CONSTRAINT "sales_pkey" PRIMARY KEY ("stor_id", "ord_num", "title_id")
);

-- Table: stores
CREATE TABLE "bird__book_publishing_company"."stores" (
    "stor_id" TEXT NOT NULL,
    "stor_name" TEXT,
    "stor_address" TEXT,
    "city" TEXT,
    "state" TEXT,
    "zip" TEXT,
    CONSTRAINT "stores_pkey" PRIMARY KEY ("stor_id")
);

-- Table: titleauthor
CREATE TABLE "bird__book_publishing_company"."titleauthor" (
    "au_id" TEXT NOT NULL,
    "title_id" TEXT NOT NULL,
    "au_ord" BIGINT,
    "royaltyper" BIGINT,
    CONSTRAINT "titleauthor_pkey" PRIMARY KEY ("au_id", "title_id")
);

-- Table: titles
CREATE TABLE "bird__book_publishing_company"."titles" (
    "title_id" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "pub_id" TEXT,
    "price" DOUBLE PRECISION,
    "advance" DOUBLE PRECISION,
    "royalty" BIGINT,
    "ytd_sales" BIGINT,
    "notes" TEXT,
    "pubdate" TIMESTAMP NOT NULL,
    CONSTRAINT "titles_pkey" PRIMARY KEY ("title_id")
);

-- Foreign Keys
ALTER TABLE "bird__book_publishing_company"."discounts" ADD CONSTRAINT "fk_discounts_0" FOREIGN KEY ("stor_id") REFERENCES "bird__book_publishing_company"."stores" ("stor_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__book_publishing_company"."employee" ADD CONSTRAINT "fk_employee_0" FOREIGN KEY ("pub_id") REFERENCES "bird__book_publishing_company"."publishers" ("pub_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__book_publishing_company"."employee" ADD CONSTRAINT "fk_employee_1" FOREIGN KEY ("job_id") REFERENCES "bird__book_publishing_company"."jobs" ("job_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__book_publishing_company"."pub_info" ADD CONSTRAINT "fk_pub_info_0" FOREIGN KEY ("pub_id") REFERENCES "bird__book_publishing_company"."publishers" ("pub_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__book_publishing_company"."roysched" ADD CONSTRAINT "fk_roysched_0" FOREIGN KEY ("title_id") REFERENCES "bird__book_publishing_company"."titles" ("title_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__book_publishing_company"."sales" ADD CONSTRAINT "fk_sales_0" FOREIGN KEY ("title_id") REFERENCES "bird__book_publishing_company"."titles" ("title_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__book_publishing_company"."sales" ADD CONSTRAINT "fk_sales_1" FOREIGN KEY ("stor_id") REFERENCES "bird__book_publishing_company"."stores" ("stor_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__book_publishing_company"."titleauthor" ADD CONSTRAINT "fk_titleauthor_0" FOREIGN KEY ("title_id") REFERENCES "bird__book_publishing_company"."titles" ("title_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__book_publishing_company"."titleauthor" ADD CONSTRAINT "fk_titleauthor_1" FOREIGN KEY ("au_id") REFERENCES "bird__book_publishing_company"."authors" ("au_id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__book_publishing_company"."titles" ADD CONSTRAINT "fk_titles_0" FOREIGN KEY ("pub_id") REFERENCES "bird__book_publishing_company"."publishers" ("pub_id") ON DELETE CASCADE ON UPDATE CASCADE;
