-- Louvre Ops DB: the 2020 design, ported faithfully to SQLite
-- Generated from coursework/1118472.mwb (schema `mydb`, "EER Diagram1").
-- Names, types, NOT NULL flags, keys and foreign keys are unchanged. ENUMs are CHECK
-- constraints. VARCHAR lengths are not enforced by SQLite. Foreign keys that point at
-- part of a composite key are kept as designed; SQLite rejects them when enforcement
-- is on ("foreign key mismatch"), so this script leaves foreign_keys off.

PRAGMA foreign_keys = OFF;

CREATE TABLE "Ticket" (
  "Ticket ID" INTEGER NOT NULL, -- AUTO_INCREMENT (rowid alias); Each visitor has unique ticket ID, unique bar code record ticket ID
  "Invoices_Invoice ID" INTEGER NOT NULL, -- Use as ticket purchase reference
  "Transportation" TEXT CHECK ("Transportation" IN ('Walk', 'Metro', 'Train', 'Bus', 'Taxi', 'Private Vehicle', 'Other')), -- As visitor first time enter the museum, the transportation must be record.
  "Booked ID" INTEGER, -- Use to check special exhibition booking
  "Exhibition Booking ID" INTEGER, -- Special exhibition book ID
  PRIMARY KEY ("Ticket ID"),
  CONSTRAINT "fk_Ticket_Invoices1" FOREIGN KEY ("Invoices_Invoice ID") REFERENCES "Invoices" ("Invoice ID"),
  CONSTRAINT "fk_Ticket_Booking Ticket1" FOREIGN KEY ("Booked ID", "Exhibition Booking ID") REFERENCES "Booking Ticket" ("Booked ID", "Exhibition Booking ID")
);
CREATE INDEX "fk_Ticket_Invoices1_idx" ON "Ticket" ("Invoices_Invoice ID");
CREATE INDEX "fk_Ticket_Booking Ticket1_idx" ON "Ticket" ("Booked ID", "Exhibition Booking ID");

CREATE TABLE "Invoices" (
  "Invoice ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Unique invoice number
  "Product ID" INTEGER NOT NULL, -- Use to reference which product were purchased
  "Payment Method_Payment ID" VARCHAR(45) NOT NULL, -- Use to reference the payment method
  "Purchase Quantity" INTEGER NOT NULL,
  PRIMARY KEY ("Invoice ID", "Product ID"),
  CONSTRAINT "fk_Invoices_Payment Method1" FOREIGN KEY ("Payment Method_Payment ID") REFERENCES "Payment Methods" ("Payment Method ID"),
  CONSTRAINT "fk_Invoices_Product1" FOREIGN KEY ("Product ID") REFERENCES "Product" ("Product ID")
);
CREATE INDEX "fk_Invoices_Payment Method1_idx" ON "Invoices" ("Payment Method_Payment ID");
CREATE INDEX "fk_Invoices_Product1_idx" ON "Invoices" ("Product ID");

CREATE TABLE "Product" (
  "Product ID" INTEGER NOT NULL,
  "Product Name" VARCHAR(20) NOT NULL,
  "Product Price" FLOAT NOT NULL,
  PRIMARY KEY ("Product ID")
);

CREATE TABLE "EFTPOS" (
  "EFTPOS ID" INTEGER NOT NULL, -- AUTO_INCREMENT (rowid alias)
  "EFTPOS Types" TEXT NOT NULL CHECK ("EFTPOS Types" IN ('Credit Card', 'Debit Card', 'Digital Wallet')), -- Record card type
  "Purchase Channel" TEXT NOT NULL CHECK ("Purchase Channel" IN ('Online', 'Offline')), -- Channel: Online / Offline
  "Card Issued Country" VARCHAR(45) NOT NULL,
  "Financial Institution Name" VARCHAR(45) NOT NULL,
  "Issued Bank Branch" VARCHAR(45) NOT NULL,
  "Account Name" VARCHAR(30) NOT NULL,
  "Card Number" INTEGER NOT NULL, -- was INT(8); Card Number only record the first 4 and end 4 numbers.
  "Expiry Date" DATE NOT NULL,
  PRIMARY KEY ("EFTPOS ID")
);

CREATE TABLE "Cash" (
  "Cash Payment ID" INTEGER NOT NULL, -- AUTO_INCREMENT (rowid alias)
  "City" VARCHAR(50) NOT NULL,
  "Country" VARCHAR(50) NOT NULL,
  "First Name" VARCHAR(30) NOT NULL,
  PRIMARY KEY ("Cash Payment ID")
);

CREATE TABLE "Payment Methods" (
  "Payment Method ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Check the payment method
  "EFTPOS_EFTPOS ID" INTEGER, -- EFTPOS is for card and digital wallet payment method
  "Cash_Cash ID" INTEGER, -- All cash payment will consider as a offline trade
  PRIMARY KEY ("Payment Method ID", "EFTPOS_EFTPOS ID", "Cash_Cash ID"),
  CONSTRAINT "fk_Payment Method_EFTPOS1" FOREIGN KEY ("EFTPOS_EFTPOS ID") REFERENCES "EFTPOS" ("EFTPOS ID"),
  CONSTRAINT "fk_Payment Method_Cash1" FOREIGN KEY ("Cash_Cash ID") REFERENCES "Cash" ("Cash Payment ID")
);
CREATE INDEX "fk_Payment Method_EFTPOS1_idx" ON "Payment Methods" ("EFTPOS_EFTPOS ID");
CREATE INDEX "fk_Payment Method_Cash1_idx" ON "Payment Methods" ("Cash_Cash ID");

CREATE TABLE "Entrance" (
  "Gate Name" TEXT NOT NULL CHECK ("Gate Name" IN ('Pyramid', 'Carrousel de Louvre', '99 rue de Rivioli', 'Passage Richelieu', 'Portes de Lion')), -- Identify which gate
  "Ticket ID" INTEGER NOT NULL, -- Record Ticket ID
  "Entrance Date/Time" DATETIME NOT NULL,
  "Business Day" TEXT NOT NULL CHECK ("Business Day" IN ('True', 'False')), -- To justify is working day or not
  PRIMARY KEY ("Gate Name", "Ticket ID"),
  CONSTRAINT "fk_Entrance_Ticket1" FOREIGN KEY ("Ticket ID") REFERENCES "Ticket" ("Ticket ID")
);
CREATE INDEX "fk_Entrance_Ticket1_idx" ON "Entrance" ("Ticket ID");

CREATE TABLE "Wings" (
  "Wings Name" TEXT NOT NULL CHECK ("Wings Name" IN ('Richelieu', 'Denon', 'Sully')), -- Identify which wing
  "Ticket_Ticket ID" INTEGER NOT NULL, -- Record ticket id
  "Visit Time" DATETIME NOT NULL,
  PRIMARY KEY ("Wings Name"),
  CONSTRAINT "fk_Wings_Ticket1" FOREIGN KEY ("Ticket_Ticket ID") REFERENCES "Ticket" ("Ticket ID")
);
CREATE INDEX "fk_Wings_Ticket1_idx" ON "Wings" ("Ticket_Ticket ID");

CREATE TABLE "Hired Audio Guide" (
  "Hired ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Unique hiring id
  "Serial Number" VARCHAR(16) NOT NULL, -- Audio device serial number
  "Language Setting" TEXT NOT NULL CHECK ("Language Setting" IN ('French', 'English', 'Italian', 'Russian', 'Chinese Mandarin', 'Spanish', 'Portuguese', 'Korean', 'Dutch', 'Polish', 'Swedish', 'Norwegian', 'Finnish')),
  "Issued Wing" TEXT NOT NULL CHECK ("Issued Wing" IN ('Richelieu', 'Denon', 'Sully')),
  "Returned Wing" TEXT CHECK ("Returned Wing" IN ('Richelieu', 'Denon', 'Sully')),
  "Issued Time" DATETIME NOT NULL,
  "Return Time" DATETIME,
  "Invoice ID" INTEGER NOT NULL,
  "Product ID" INTEGER NOT NULL,
  "Ticket ID" INTEGER NOT NULL,
  PRIMARY KEY ("Hired ID", "Serial Number"),
  CONSTRAINT "fk_Hiring Audio Guide_Audio Guide Device1" FOREIGN KEY ("Serial Number") REFERENCES "Audio Guide Device" ("Serial Number"),
  CONSTRAINT "fk_Hired Audio Guide_Invoices1" FOREIGN KEY ("Invoice ID", "Product ID") REFERENCES "Invoices" ("Invoice ID", "Product ID"),
  CONSTRAINT "fk_Hired Audio Guide_Ticket1" FOREIGN KEY ("Ticket ID") REFERENCES "Ticket" ("Ticket ID")
);
CREATE INDEX "fk_Hiring Audio Guide_Audio Guide Device1_idx" ON "Hired Audio Guide" ("Serial Number");
CREATE INDEX "fk_Hired Audio Guide_Invoices1_idx" ON "Hired Audio Guide" ("Invoice ID", "Product ID");
CREATE INDEX "fk_Hired Audio Guide_Ticket1_idx" ON "Hired Audio Guide" ("Ticket ID");

CREATE TABLE "Audio Guide Device" (
  "Serial Number" VARCHAR(16) NOT NULL, -- Audio device serial number
  "Activated Date" DATE NOT NULL,
  "Retired Date" DATE NOT NULL,
  PRIMARY KEY ("Serial Number")
);

CREATE TABLE "Exhibition Booking" (
  "Booking ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Booking id
  "Exhibition ID" INTEGER NOT NULL, -- Identify booked exhibition
  "Time Slot ID" INTEGER NOT NULL, -- Identify which time slot has been selected
  "Ticket ID" INTEGER, -- Record ticket ID / multi-value attribute
  "Booking Date" DATE NOT NULL, -- Booking Date
  PRIMARY KEY ("Booking ID", "Exhibition ID", "Time Slot ID"),
  CONSTRAINT "fk_Exhibition Booking_Ticket1" FOREIGN KEY ("Ticket ID") REFERENCES "Ticket" ("Ticket ID"),
  CONSTRAINT "fk_Exhibition Booking_Time Slot1" FOREIGN KEY ("Time Slot ID") REFERENCES "Time Slot" ("Time Slot ID"),
  CONSTRAINT "fk_Exhibition Booking_Special Exhibition1" FOREIGN KEY ("Exhibition ID") REFERENCES "Special Exhibition" ("Exhibition ID")
);
CREATE INDEX "fk_Exhibition Booking_Ticket1_idx" ON "Exhibition Booking" ("Ticket ID");
CREATE INDEX "fk_Exhibition Booking_Time Slot1_idx" ON "Exhibition Booking" ("Time Slot ID");
CREATE INDEX "fk_Exhibition Booking_Special Exhibition1_idx" ON "Exhibition Booking" ("Exhibition ID");

CREATE TABLE "Booking Ticket" (
  "Booked ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Booking ID
  "Exhibition Booking ID" INTEGER NOT NULL,
  PRIMARY KEY ("Booked ID", "Exhibition Booking ID"),
  CONSTRAINT "fk_Booking Ticket_Exhibition Booking1" FOREIGN KEY ("Exhibition Booking ID") REFERENCES "Exhibition Booking" ("Booking ID")
);
CREATE INDEX "fk_Booking Ticket_Exhibition Booking1_idx" ON "Booking Ticket" ("Exhibition Booking ID");

CREATE TABLE "Special Exhibition" (
  "Exhibition ID" INTEGER NOT NULL,
  "Exhibition Title" VARCHAR(45) NOT NULL, -- Name of exhibition
  "Opening Date" DATETIME NOT NULL,
  "Closing Date" DATETIME NOT NULL,
  PRIMARY KEY ("Exhibition ID")
);

CREATE TABLE "Time Slot" (
  "Time Slot ID" INTEGER NOT NULL, -- Available time slot number
  "Time Period" TIME NOT NULL, -- Time slot date is fixed
  "Time Slot Capacity" INTEGER NOT NULL,
  PRIMARY KEY ("Time Slot ID")
);

CREATE TABLE "Hall Napoleon" (
  "Exhibiton Log ID" INTEGER NOT NULL, -- AUTO_INCREMENT (rowid alias); Record how many visitor enter the hall Napoleon
  "Time Slot ID" INTEGER NOT NULL, -- Record the time slot
  "Date" DATE NOT NULL, -- Record the date
  "Ticket_Ticket ID" INTEGER, -- Record visited visitor ticket id
  PRIMARY KEY ("Exhibiton Log ID"),
  CONSTRAINT "fk_Hall Napoleon_Ticket1" FOREIGN KEY ("Ticket_Ticket ID") REFERENCES "Ticket" ("Ticket ID"),
  CONSTRAINT "fk_Hall Napoleon_Time Slot1" FOREIGN KEY ("Time Slot ID") REFERENCES "Time Slot" ("Time Slot ID")
);
CREATE INDEX "fk_Hall Napoleon_Ticket1_idx" ON "Hall Napoleon" ("Ticket_Ticket ID");
CREATE INDEX "fk_Hall Napoleon_Time Slot1_idx" ON "Hall Napoleon" ("Time Slot ID");

CREATE TABLE "Actual Number of Visitor" (
  "Vistor ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key)
  "Exhibiton Log ID" INTEGER NOT NULL,
  "Ticket ID" INTEGER NOT NULL,
  PRIMARY KEY ("Vistor ID", "Exhibiton Log ID"),
  CONSTRAINT "fk_Actual Number of Visitor_Hall Napoleon1" FOREIGN KEY ("Exhibiton Log ID") REFERENCES "Hall Napoleon" ("Exhibiton Log ID")
);
CREATE INDEX "fk_Actual Number of Visitor_Hall Napoleon1_idx" ON "Actual Number of Visitor" ("Exhibiton Log ID");