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

-- Table: Album
CREATE TABLE "media__chinook"."Album" (
    "AlbumId" BIGINT NOT NULL,
    "Title" TEXT NOT NULL,
    "ArtistId" BIGINT NOT NULL,
    CONSTRAINT "PK_Album" PRIMARY KEY ("AlbumId")
);

-- Table: Artist
CREATE TABLE "media__chinook"."Artist" (
    "ArtistId" BIGINT NOT NULL,
    "Name" TEXT,
    CONSTRAINT "PK_Artist" PRIMARY KEY ("ArtistId")
);

-- Table: Customer
CREATE TABLE "media__chinook"."Customer" (
    "CustomerId" BIGINT NOT NULL,
    "FirstName" TEXT NOT NULL,
    "LastName" TEXT NOT NULL,
    "Company" TEXT,
    "Address" TEXT,
    "City" TEXT,
    "State" TEXT,
    "Country" TEXT,
    "PostalCode" TEXT,
    "Phone" TEXT,
    "Fax" TEXT,
    "Email" TEXT NOT NULL,
    "SupportRepId" BIGINT,
    CONSTRAINT "PK_Customer" PRIMARY KEY ("CustomerId")
);

-- Table: Employee
CREATE TABLE "media__chinook"."Employee" (
    "EmployeeId" BIGINT NOT NULL,
    "LastName" TEXT NOT NULL,
    "FirstName" TEXT NOT NULL,
    "Title" TEXT,
    "ReportsTo" BIGINT,
    "BirthDate" TIMESTAMP,
    "HireDate" TIMESTAMP,
    "Address" TEXT,
    "City" TEXT,
    "State" TEXT,
    "Country" TEXT,
    "PostalCode" TEXT,
    "Phone" TEXT,
    "Fax" TEXT,
    "Email" TEXT,
    CONSTRAINT "PK_Employee" PRIMARY KEY ("EmployeeId")
);

-- Table: Genre
CREATE TABLE "media__chinook"."Genre" (
    "GenreId" BIGINT NOT NULL,
    "Name" TEXT,
    CONSTRAINT "PK_Genre" PRIMARY KEY ("GenreId")
);

-- Table: Invoice
CREATE TABLE "media__chinook"."Invoice" (
    "InvoiceId" BIGINT NOT NULL,
    "CustomerId" BIGINT NOT NULL,
    "InvoiceDate" TIMESTAMP NOT NULL,
    "BillingAddress" TEXT,
    "BillingCity" TEXT,
    "BillingState" TEXT,
    "BillingCountry" TEXT,
    "BillingPostalCode" TEXT,
    "Total" NUMERIC(10,2) NOT NULL,
    CONSTRAINT "PK_Invoice" PRIMARY KEY ("InvoiceId")
);

-- Table: InvoiceLine
CREATE TABLE "media__chinook"."InvoiceLine" (
    "InvoiceLineId" BIGINT NOT NULL,
    "InvoiceId" BIGINT NOT NULL,
    "TrackId" BIGINT NOT NULL,
    "UnitPrice" NUMERIC(10,2) NOT NULL,
    "Quantity" BIGINT NOT NULL,
    CONSTRAINT "PK_InvoiceLine" PRIMARY KEY ("InvoiceLineId")
);

-- Table: MediaType
CREATE TABLE "media__chinook"."MediaType" (
    "MediaTypeId" BIGINT NOT NULL,
    "Name" TEXT,
    CONSTRAINT "PK_MediaType" PRIMARY KEY ("MediaTypeId")
);

-- Table: Playlist
CREATE TABLE "media__chinook"."Playlist" (
    "PlaylistId" BIGINT NOT NULL,
    "Name" TEXT,
    CONSTRAINT "PK_Playlist" PRIMARY KEY ("PlaylistId")
);

-- Table: PlaylistTrack
CREATE TABLE "media__chinook"."PlaylistTrack" (
    "PlaylistId" BIGINT NOT NULL,
    "TrackId" BIGINT NOT NULL,
    CONSTRAINT "PK_PlaylistTrack" PRIMARY KEY ("PlaylistId", "TrackId")
);

-- Table: Track
CREATE TABLE "media__chinook"."Track" (
    "TrackId" BIGINT NOT NULL,
    "Name" TEXT NOT NULL,
    "AlbumId" BIGINT,
    "MediaTypeId" BIGINT NOT NULL,
    "GenreId" BIGINT,
    "Composer" TEXT,
    "Milliseconds" BIGINT NOT NULL,
    "Bytes" BIGINT,
    "UnitPrice" NUMERIC(10,2) NOT NULL,
    CONSTRAINT "PK_Track" PRIMARY KEY ("TrackId")
);

-- Foreign Keys
ALTER TABLE "media__chinook"."Album" ADD CONSTRAINT "fk_Album_0" FOREIGN KEY ("ArtistId") REFERENCES "media__chinook"."Artist" ("ArtistId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."Customer" ADD CONSTRAINT "fk_Customer_0" FOREIGN KEY ("SupportRepId") REFERENCES "media__chinook"."Employee" ("EmployeeId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."Employee" ADD CONSTRAINT "fk_Employee_0" FOREIGN KEY ("ReportsTo") REFERENCES "media__chinook"."Employee" ("EmployeeId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."Invoice" ADD CONSTRAINT "fk_Invoice_0" FOREIGN KEY ("CustomerId") REFERENCES "media__chinook"."Customer" ("CustomerId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."InvoiceLine" ADD CONSTRAINT "fk_InvoiceLine_0" FOREIGN KEY ("TrackId") REFERENCES "media__chinook"."Track" ("TrackId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."InvoiceLine" ADD CONSTRAINT "fk_InvoiceLine_1" FOREIGN KEY ("InvoiceId") REFERENCES "media__chinook"."Invoice" ("InvoiceId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."PlaylistTrack" ADD CONSTRAINT "fk_PlaylistTrack_0" FOREIGN KEY ("TrackId") REFERENCES "media__chinook"."Track" ("TrackId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."PlaylistTrack" ADD CONSTRAINT "fk_PlaylistTrack_1" FOREIGN KEY ("PlaylistId") REFERENCES "media__chinook"."Playlist" ("PlaylistId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."Track" ADD CONSTRAINT "fk_Track_0" FOREIGN KEY ("MediaTypeId") REFERENCES "media__chinook"."MediaType" ("MediaTypeId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."Track" ADD CONSTRAINT "fk_Track_1" FOREIGN KEY ("GenreId") REFERENCES "media__chinook"."Genre" ("GenreId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "media__chinook"."Track" ADD CONSTRAINT "fk_Track_2" FOREIGN KEY ("AlbumId") REFERENCES "media__chinook"."Album" ("AlbumId") ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Indexes
CREATE INDEX "IFK_AlbumArtistId" ON "media__chinook"."Album" ("ArtistId");
CREATE INDEX "IFK_CustomerSupportRepId" ON "media__chinook"."Customer" ("SupportRepId");
CREATE INDEX "IFK_EmployeeReportsTo" ON "media__chinook"."Employee" ("ReportsTo");
CREATE INDEX "IFK_InvoiceCustomerId" ON "media__chinook"."Invoice" ("CustomerId");
CREATE INDEX "IFK_InvoiceLineInvoiceId" ON "media__chinook"."InvoiceLine" ("InvoiceId");
CREATE INDEX "IFK_InvoiceLineTrackId" ON "media__chinook"."InvoiceLine" ("TrackId");
CREATE INDEX "IFK_PlaylistTrackPlaylistId" ON "media__chinook"."PlaylistTrack" ("PlaylistId");
CREATE INDEX "IFK_PlaylistTrackTrackId" ON "media__chinook"."PlaylistTrack" ("TrackId");
CREATE INDEX "IFK_TrackAlbumId" ON "media__chinook"."Track" ("AlbumId");
CREATE INDEX "IFK_TrackGenreId" ON "media__chinook"."Track" ("GenreId");
CREATE INDEX "IFK_TrackMediaTypeId" ON "media__chinook"."Track" ("MediaTypeId");
