-- Louvre Ops DB: refined schema (2026)
--
-- A cleaned-up version of the 2020 INFO20003 design, written for SQLite
-- (3.37+, STRICT tables). It keeps the same business scope and the same
-- entities wherever they worked, and fixes the problems listed on the site's
-- design-review page. Each table notes the 2020 table it replaces.
--
-- Conventions: snake_case names; money in integer euro cents; timestamps as
-- ISO-8601 text in museum local time ('YYYY-MM-DD HH:MM:SS'); dates as
-- 'YYYY-MM-DD'; enumerations as CHECK constraints or lookup tables.

PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------------------
-- Reference data
-- ---------------------------------------------------------------------------

-- 2020: the ENUM on Entrance."Gate Name" (with one spelling slip, '99 rue de Rivioli';
-- two other names followed the brief's own spelling).
CREATE TABLE entrance (
  entrance_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE
) STRICT;

-- 2020: the ENUM on Wings."Wings Name" and Hired Audio Guide's wing columns.
CREATE TABLE wing (
  wing_id INTEGER PRIMARY KEY,
  name    TEXT NOT NULL UNIQUE CHECK (name IN ('Richelieu', 'Denon', 'Sully'))
) STRICT;

-- 2020: the 13-value ENUM on Hired Audio Guide."Language Setting".
CREATE TABLE language (
  language_code TEXT PRIMARY KEY CHECK (length(language_code) BETWEEN 2 AND 6),
  name          TEXT NOT NULL UNIQUE
) STRICT;

-- 2020: Product (price was FLOAT).
CREATE TABLE product (
  product_id  INTEGER PRIMARY KEY,
  code        TEXT NOT NULL UNIQUE
              CHECK (code IN ('TICKET_ONLINE', 'TICKET_ON_SITE', 'AUDIO_GUIDE', 'APP')),
  name        TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
) STRICT;

-- 2020: part of EFTPOS (institution name was one free-text column).
CREATE TABLE financial_institution (
  institution_id INTEGER PRIMARY KEY,
  name           TEXT NOT NULL UNIQUE,
  short_name     TEXT NOT NULL,
  country        TEXT NOT NULL
) STRICT;

-- ---------------------------------------------------------------------------
-- Payments: one supertype row per payment, exactly one subtype row
-- 2020: Payment Methods + EFTPOS + Cash
-- ---------------------------------------------------------------------------

CREATE TABLE payment (
  payment_id   INTEGER PRIMARY KEY,
  method       TEXT NOT NULL
               CHECK (method IN ('credit_card', 'debit_card', 'digital_wallet', 'cash')),
  amount_cents INTEGER NOT NULL CHECK (amount_cents >= 0),
  paid_at      TEXT NOT NULL CHECK (paid_at GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
) STRICT;

-- Cards and digital wallets. Only the first and last four digits are kept,
-- as text so leading zeros survive; no CCV column exists.
CREATE TABLE card_payment (
  payment_id     INTEGER PRIMARY KEY REFERENCES payment (payment_id),
  institution_id INTEGER NOT NULL REFERENCES financial_institution (institution_id),
  issuing_branch TEXT NOT NULL,
  issued_country TEXT NOT NULL,
  account_name   TEXT NOT NULL,
  card_first4    TEXT NOT NULL CHECK (length(card_first4) = 4 AND card_first4 NOT GLOB '*[^0-9]*'),
  card_last4     TEXT NOT NULL CHECK (length(card_last4) = 4 AND card_last4 NOT GLOB '*[^0-9]*'),
  expiry_month   INTEGER NOT NULL CHECK (expiry_month BETWEEN 1 AND 12),
  expiry_year    INTEGER NOT NULL CHECK (expiry_year BETWEEN 2000 AND 2099),
  wallet         TEXT CHECK (wallet IN ('Apple Pay', 'Google Pay'))
) STRICT;

-- Cash (euros only): first name, city and country, nothing else.
CREATE TABLE cash_payment (
  payment_id INTEGER PRIMARY KEY REFERENCES payment (payment_id),
  first_name TEXT NOT NULL,
  city       TEXT NOT NULL,
  country    TEXT NOT NULL
) STRICT;

-- A payment has exactly one subtype, and it must match payment.method.
CREATE TRIGGER card_payment_matches_method
BEFORE INSERT ON card_payment
WHEN (SELECT method FROM payment WHERE payment_id = NEW.payment_id) = 'cash'
  OR EXISTS (SELECT 1 FROM cash_payment WHERE payment_id = NEW.payment_id)
  OR ((SELECT method FROM payment WHERE payment_id = NEW.payment_id) = 'digital_wallet') <> (NEW.wallet IS NOT NULL)
BEGIN
  SELECT RAISE(ABORT, 'card details do not match the payment method');
END;

CREATE TRIGGER cash_payment_matches_method
BEFORE INSERT ON cash_payment
WHEN (SELECT method FROM payment WHERE payment_id = NEW.payment_id) <> 'cash'
  OR EXISTS (SELECT 1 FROM card_payment WHERE payment_id = NEW.payment_id)
BEGIN
  SELECT RAISE(ABORT, 'cash details recorded for a non-cash payment');
END;

-- ---------------------------------------------------------------------------
-- Orders and tickets
-- 2020: Invoices (one product per invoice, keyed by invoice + product)
-- ---------------------------------------------------------------------------

-- channel says where the sale happened: the website, a ticket desk at one of
-- the five entrances, an audio-guide desk in a wing, or an app store.
CREATE TABLE purchase_order (
  order_id    INTEGER PRIMARY KEY,
  channel     TEXT NOT NULL
              CHECK (channel IN ('online', 'ticket_desk', 'audio_guide_desk', 'app_store')),
  entrance_id INTEGER REFERENCES entrance (entrance_id),
  wing_id     INTEGER REFERENCES wing (wing_id),
  payment_id  INTEGER NOT NULL UNIQUE REFERENCES payment (payment_id),
  ordered_at  TEXT NOT NULL,
  CHECK ((channel = 'ticket_desk') = (entrance_id IS NOT NULL)),
  CHECK ((channel = 'audio_guide_desk') = (wing_id IS NOT NULL))
) STRICT;

CREATE TRIGGER purchase_order_cash_on_site_only
BEFORE INSERT ON purchase_order
WHEN NEW.channel IN ('online', 'app_store')
 AND (SELECT method FROM payment WHERE payment_id = NEW.payment_id) = 'cash'
BEGIN
  SELECT RAISE(ABORT, 'cash is accepted only at museum desks');
END;

CREATE TABLE order_line (
  order_id         INTEGER NOT NULL REFERENCES purchase_order (order_id),
  product_id       INTEGER NOT NULL REFERENCES product (product_id),
  quantity         INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0),
  PRIMARY KEY (order_id, product_id)
) STRICT;

-- 2020: Ticket (the barcode was the ticket ID; transport mode lived here).
CREATE TABLE ticket (
  ticket_id INTEGER PRIMARY KEY,
  barcode   TEXT NOT NULL UNIQUE CHECK (length(barcode) = 13 AND barcode NOT GLOB '*[^0-9]*'),
  order_id  INTEGER NOT NULL REFERENCES purchase_order (order_id)
) STRICT;
CREATE INDEX ticket_order ON ticket (order_id);

-- ---------------------------------------------------------------------------
-- Admission and movement
-- 2020: Entrance (keyed by gate + ticket), Wings (keyed by wing name)
-- ---------------------------------------------------------------------------

-- Every scan at one of the five entrances. The first scan of a ticket opens
-- its day of validity and is the only one that records the transport mode.
CREATE TABLE entry_scan (
  scan_id        INTEGER PRIMARY KEY,
  ticket_id      INTEGER NOT NULL REFERENCES ticket (ticket_id),
  entrance_id    INTEGER NOT NULL REFERENCES entrance (entrance_id),
  scanned_at     TEXT NOT NULL,
  is_first_entry INTEGER NOT NULL CHECK (is_first_entry IN (0, 1)),
  transport_mode TEXT CHECK (transport_mode IN ('Walk', 'Metro', 'Train', 'Bus', 'Taxi', 'Private Vehicle', 'Other')),
  CHECK ((is_first_entry = 1) = (transport_mode IS NOT NULL))
) STRICT;
CREATE UNIQUE INDEX entry_scan_one_first_entry ON entry_scan (ticket_id) WHERE is_first_entry = 1;

-- A ticket is valid from its first scan until closing on the same day.
CREATE TRIGGER entry_scan_same_day
BEFORE INSERT ON entry_scan
WHEN NEW.is_first_entry = 0
 AND date(NEW.scanned_at) IS NOT (
   SELECT date(scanned_at) FROM entry_scan WHERE ticket_id = NEW.ticket_id AND is_first_entry = 1
 )
BEGIN
  SELECT RAISE(ABORT, 'ticket is not valid today: re-entry is allowed only on the day of first entry');
END;

CREATE TABLE wing_scan (
  scan_id    INTEGER PRIMARY KEY,
  ticket_id  INTEGER NOT NULL REFERENCES ticket (ticket_id),
  wing_id    INTEGER NOT NULL REFERENCES wing (wing_id),
  scanned_at TEXT NOT NULL
) STRICT;
CREATE INDEX wing_scan_ticket ON wing_scan (ticket_id);

CREATE TRIGGER wing_scan_valid_ticket
BEFORE INSERT ON wing_scan
WHEN date(NEW.scanned_at) IS NOT (
  SELECT date(scanned_at) FROM entry_scan WHERE ticket_id = NEW.ticket_id AND is_first_entry = 1
)
BEGIN
  SELECT RAISE(ABORT, 'wing scan without a valid admission on that day');
END;

-- ---------------------------------------------------------------------------
-- Audio guides
-- 2020: Audio Guide Device (retired date was NOT NULL), Hired Audio Guide
-- ---------------------------------------------------------------------------

CREATE TABLE audio_guide_device (
  serial_number TEXT PRIMARY KEY
                CHECK (length(serial_number) = 16 AND serial_number NOT GLOB '*[^0-9A-Z]*'),
  activated_on  TEXT NOT NULL,
  retired_on    TEXT,
  CHECK (retired_on IS NULL OR retired_on >= activated_on)
) STRICT;

CREATE TABLE audio_guide_hire (
  hire_id          INTEGER PRIMARY KEY,
  ticket_id        INTEGER NOT NULL REFERENCES ticket (ticket_id),
  serial_number    TEXT NOT NULL REFERENCES audio_guide_device (serial_number),
  order_id         INTEGER NOT NULL UNIQUE REFERENCES purchase_order (order_id),
  language_code    TEXT NOT NULL REFERENCES language (language_code),
  issued_wing_id   INTEGER NOT NULL REFERENCES wing (wing_id),
  issued_at        TEXT NOT NULL,
  returned_wing_id INTEGER REFERENCES wing (wing_id),
  returned_at      TEXT,
  CHECK ((returned_at IS NULL) = (returned_wing_id IS NULL)),
  CHECK (returned_at IS NULL OR returned_at > issued_at)
) STRICT;
CREATE INDEX audio_guide_hire_ticket ON audio_guide_hire (ticket_id);
CREATE INDEX audio_guide_hire_issued ON audio_guide_hire (issued_at);
CREATE INDEX audio_guide_hire_device ON audio_guide_hire (serial_number, issued_at);

-- A device can be out with only one visitor at a time, and only while in service.
CREATE TRIGGER audio_guide_hire_device_available
BEFORE INSERT ON audio_guide_hire
WHEN EXISTS (
  SELECT 1 FROM audio_guide_hire h
  WHERE h.serial_number = NEW.serial_number
    AND h.issued_at < coalesce(NEW.returned_at, '9999')
    AND coalesce(h.returned_at, '9999') > NEW.issued_at
) OR NOT EXISTS (
  SELECT 1 FROM audio_guide_device d
  WHERE d.serial_number = NEW.serial_number
    AND d.activated_on <= date(NEW.issued_at)
    AND (d.retired_on IS NULL OR d.retired_on > date(NEW.issued_at))
)
BEGIN
  SELECT RAISE(ABORT, 'audio guide is already out or not in service');
END;

-- ---------------------------------------------------------------------------
-- Special exhibitions in the Hall Napoleon
-- 2020: Special Exhibition, Time Slot (no date), Exhibition Booking,
--       Booking Ticket, Hall Napoleon, Actual Number of Visitor
-- ---------------------------------------------------------------------------

CREATE TABLE exhibition (
  exhibition_id INTEGER PRIMARY KEY,
  title         TEXT NOT NULL,
  opens_on      TEXT NOT NULL,
  closes_on     TEXT NOT NULL,
  slot_capacity INTEGER NOT NULL DEFAULT 45 CHECK (slot_capacity > 0),
  CHECK (closes_on >= opens_on)
) STRICT;

-- One row per bookable 15-minute slot. Slots are created when the first
-- place in them is booked, with the exhibition's capacity at that time.
CREATE TABLE exhibition_slot (
  slot_id       INTEGER PRIMARY KEY,
  exhibition_id INTEGER NOT NULL REFERENCES exhibition (exhibition_id),
  slot_date     TEXT NOT NULL,
  start_time    TEXT NOT NULL CHECK (start_time GLOB '[0-2][0-9]:[0-5][0-9]' AND substr(start_time, 4, 2) IN ('00', '15', '30', '45')),
  capacity      INTEGER NOT NULL CHECK (capacity > 0),
  UNIQUE (exhibition_id, slot_date, start_time)
) STRICT;

CREATE TRIGGER exhibition_slot_within_run
BEFORE INSERT ON exhibition_slot
WHEN NOT EXISTS (
  SELECT 1 FROM exhibition e
  WHERE e.exhibition_id = NEW.exhibition_id AND NEW.slot_date BETWEEN e.opens_on AND e.closes_on
)
BEGIN
  SELECT RAISE(ABORT, 'slot date is outside the exhibition run');
END;

CREATE TABLE exhibition_booking (
  booking_id INTEGER PRIMARY KEY,
  slot_id    INTEGER NOT NULL REFERENCES exhibition_slot (slot_id),
  ticket_id  INTEGER NOT NULL REFERENCES ticket (ticket_id),
  booked_at  TEXT NOT NULL,
  UNIQUE (slot_id, ticket_id)
) STRICT;
CREATE INDEX exhibition_booking_ticket ON exhibition_booking (ticket_id);

-- No more than the slot's capacity can ever be booked.
CREATE TRIGGER exhibition_booking_capacity
BEFORE INSERT ON exhibition_booking
WHEN (SELECT count(*) FROM exhibition_booking WHERE slot_id = NEW.slot_id)
  >= (SELECT capacity FROM exhibition_slot WHERE slot_id = NEW.slot_id)
BEGIN
  SELECT RAISE(ABORT, 'this 15-minute slot is fully booked');
END;

-- Scans at the Hall Napoleon door; one admission per booking.
CREATE TABLE hall_napoleon_scan (
  scan_id    INTEGER PRIMARY KEY,
  booking_id INTEGER NOT NULL UNIQUE REFERENCES exhibition_booking (booking_id),
  scanned_at TEXT NOT NULL
) STRICT;

-- Booked, admitted and remaining places for every slot (replaces the 2020
-- "Actual Number of Visitor" table, which stored a count that can be derived).
CREATE VIEW slot_availability AS
SELECT
  s.slot_id,
  s.exhibition_id,
  e.title,
  s.slot_date,
  s.start_time,
  s.capacity,
  count(b.booking_id)              AS booked,
  count(h.scan_id)                 AS admitted,
  s.capacity - count(b.booking_id) AS remaining
FROM exhibition_slot s
JOIN exhibition e ON e.exhibition_id = s.exhibition_id
LEFT JOIN exhibition_booking b ON b.slot_id = s.slot_id
LEFT JOIN hall_napoleon_scan h ON h.booking_id = b.booking_id
GROUP BY s.slot_id;

-- One row per admitted ticket: the day's first entry, for convenience.
CREATE VIEW museum_visit AS
SELECT
  e.ticket_id,
  date(e.scanned_at) AS visit_date,
  e.scanned_at       AS first_entry_at,
  e.entrance_id,
  e.transport_mode
FROM entry_scan e
WHERE e.is_first_entry = 1;