-- Craigmillar Community Sports Club — database setup script
-- Higher Computing Science, Database Design & Development (DDD6-11)
-- James Gillespie's High School, 2026-27
--
-- Run this whole script in DataGrip against a new database/schema before
-- starting DDD7. Every SQL example and Task Set answer from DDD7 onward
-- is written against this exact data and has been verified on MySQL 8.0,
-- matching the department's Laragon/DataGrip environment.
--
-- Conventions: single-quoted string literals, comma-separated table joins
-- in WHERE (no JOIN keyword), and TRUE/FALSE for Boolean fields.

DROP TABLE IF EXISTS Booking;
DROP TABLE IF EXISTS Class;
DROP TABLE IF EXISTS Instructor;
DROP TABLE IF EXISTS Member;

CREATE TABLE Member (
  memberID    VARCHAR(6) PRIMARY KEY,
  firstName   VARCHAR(20),
  surname     VARCHAR(25),
  address     VARCHAR(40),
  town        VARCHAR(20),
  postcode    VARCHAR(8),
  dateJoined  DATE
);

CREATE TABLE Instructor (
  instructorRef VARCHAR(6) PRIMARY KEY,
  firstName     VARCHAR(20),
  surname       VARCHAR(25),
  qualified     BOOLEAN
);

CREATE TABLE Class (
  classCode      VARCHAR(6) PRIMARY KEY,
  className      VARCHAR(25),
  instructorRef  VARCHAR(6),
  dayOfWeek      VARCHAR(10),
  startTime      TIME,
  capacity       INT,
  feePerSession  DECIMAL(4,2),
  FOREIGN KEY (instructorRef) REFERENCES Instructor(instructorRef)
);

CREATE TABLE Booking (
  bookingRef  VARCHAR(8) PRIMARY KEY,
  memberID    VARCHAR(6),
  classCode   VARCHAR(6),
  bookingDate DATE,
  attended    BOOLEAN,
  FOREIGN KEY (memberID) REFERENCES Member(memberID),
  FOREIGN KEY (classCode) REFERENCES Class(classCode)
);

INSERT INTO Member (memberID, firstName, surname, address, town, postcode, dateJoined) VALUES
('M001', 'Anna',   'Stewart',   '4 Bruntsfield Place',    'Edinburgh', 'EH10 4HB', '2024-03-12'),
('M002', 'James',  'Fraser',    '22 Leith Walk',          'Edinburgh', 'EH6 5EA',  '2024-05-01'),
('M003', 'Sarah',  'Bell',      '9 Marchmont Crescent',   'Edinburgh', 'EH9 1HH',  '2024-09-20'),
('M004', 'Tom',    'Dickson',   '15 Gorgie Road',         'Edinburgh', 'EH11 2LA', '2025-01-15'),
('M005', 'Emma',   'Clarke',    '3 Morningside Road',     'Edinburgh', 'EH10 4DB', '2025-02-10'),
('M006', 'Liam',   'Scott',     '18 Dalry Road',          'Edinburgh', 'EH11 2BX', '2025-04-22'),
('M007', 'Olivia', 'Grant',     '7 Comely Bank',          'Edinburgh', 'EH4 1EA',  '2025-06-30'),
('M008', 'Ryan',   'Murray',    '25 Craigmillar Avenue',  'Edinburgh', 'EH16 4AB', '2025-08-14'),
('M009', 'Chloe',  'Watt',      '11 Portobello High St',  'Edinburgh', 'EH15 1AF', '2025-11-02'),
('M010', 'Jack',   'Hunter',    '6 Corstorphine Road',    'Edinburgh', 'EH12 6HQ', '2025-12-19'),
('M011', 'Megan',  'Ross',      '30 Newington Road',      'Edinburgh', 'EH9 1QS',  '2026-01-08'),
('M012', 'Ben',    'Coulter',   '2 Slateford Road',       'Edinburgh', 'EH11 1PH', '2026-02-14'),
('M013', 'Grace',  'Ferguson',  '14 Bruntsfield Gardens', 'Edinburgh', 'EH8 9BX',  '2026-03-05'),
('M014', 'Aiden',  'Paterson',  '20 Duddingston Road',    'Edinburgh', 'EH8 7AB',  '2026-04-18'),
('M015', 'Isla',   'Cameron',   '5 Restalrig Road',       'Edinburgh', 'EH7 6DL',  '2026-05-22');

INSERT INTO Instructor (instructorRef, firstName, surname, qualified) VALUES
('INS01', 'Fiona', 'Reid',  TRUE),
('INS02', 'David', 'Wallace', TRUE),
('INS03', 'Priya', 'Shah',  FALSE);

INSERT INTO Class (classCode, className, instructorRef, dayOfWeek, startTime, capacity, feePerSession) VALUES
('PIL01', 'Pilates Beginners',      'INS01', 'Monday',    '09:00', 15, 6.50),
('PIL02', 'Pilates Advanced',       'INS01', 'Wednesday', '18:30', 12, 7.00),
('YOG01', 'Yoga',                   'INS02', 'Monday',    '10:15', 20, 6.50),
('SPN01', 'Spin1',                  'INS03', 'Tuesday',   '17:30', 18, 8.00),
('SPN02', 'Spin2',                  'INS03', 'Thursday',  '18:00', 18, 8.00),
('ZUM01', 'Zumba',                  'INS02', 'Wednesday', '19:00', 25, 5.50),
('PRT01', 'Personal Training 1-1',  'INS01', 'Friday',    '07:00', 1,  12.50);

INSERT INTO Booking (bookingRef, memberID, classCode, bookingDate, attended) VALUES
('BKG001', 'M001', 'PIL01', '2026-01-05', TRUE),
('BKG002', 'M001', 'YOG01', '2026-01-06', TRUE),
('BKG003', 'M001', 'SPN01', '2026-01-07', TRUE),
('BKG004', 'M001', 'PIL02', '2026-01-12', TRUE),
('BKG005', 'M001', 'ZUM01', '2026-01-13', TRUE),
('BKG006', 'M002', 'PIL01', '2026-01-05', TRUE),
('BKG007', 'M002', 'SPN02', '2026-01-08', FALSE),
('BKG008', 'M003', 'YOG01', '2026-01-06', TRUE),
('BKG009', 'M003', 'ZUM01', '2026-01-07', TRUE),
('BKG010', 'M004', 'SPN01', '2026-01-07', TRUE),
('BKG011', 'M005', 'PIL01', '2026-01-05', TRUE),
('BKG012', 'M006', 'SPN02', '2026-01-08', TRUE),
('BKG013', 'M007', 'YOG01', '2026-01-06', FALSE),
('BKG014', 'M008', 'PIL02', '2026-01-12', TRUE),
('BKG015', 'M009', 'SPN01', '2026-01-07', TRUE),
('BKG016', 'M010', 'PIL01', '2026-01-05', TRUE),
('BKG017', 'M011', 'YOG01', '2026-01-06', TRUE),
('BKG018', 'M012', 'ZUM01', '2026-01-13', TRUE),
('BKG019', 'M013', 'SPN02', '2026-01-08', FALSE),
('BKG045', 'M005', 'PIL01', '2026-01-19', TRUE);
