CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(100) NOT NULL,
 last_name VARCHAR(100) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('admin','manager','staff') NOT NULL DEFAULT 'staff',
 brand VARCHAR(100) DEFAULT 'Chuck & Blade',
 department VARCHAR(50) DEFAULT 'FOH',
 position VARCHAR(100) DEFAULT '',
 site VARCHAR(100) DEFAULT '',
 status ENUM('invited','active','disabled') NOT NULL DEFAULT 'invited',
 invite_token VARCHAR(100) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE learning_paths (
 id INT AUTO_INCREMENT PRIMARY KEY,
 brand VARCHAR(100) NOT NULL,
 name VARCHAR(150) NOT NULL,
 level VARCHAR(80) NOT NULL,
 description TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_learning_paths (
 user_id INT NOT NULL,
 learning_path_id INT NOT NULL,
 PRIMARY KEY(user_id, learning_path_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(learning_path_id) REFERENCES learning_paths(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE courses (
 id INT AUTO_INCREMENT PRIMARY KEY,
 learning_path_id INT NOT NULL,
 title VARCHAR(190) NOT NULL,
 summary TEXT,
 pass_mark INT NOT NULL DEFAULT 80,
 requires_practical TINYINT(1) NOT NULL DEFAULT 0,
 sort_order INT NOT NULL DEFAULT 0,
 FOREIGN KEY(learning_path_id) REFERENCES learning_paths(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE modules (
 id INT AUTO_INCREMENT PRIMARY KEY,
 course_id INT NOT NULL,
 title VARCHAR(190) NOT NULL,
 body_html MEDIUMTEXT,
 asset_path VARCHAR(255) DEFAULT NULL,
 sort_order INT NOT NULL DEFAULT 0,
 FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE questions (
 id INT AUTO_INCREMENT PRIMARY KEY,
 course_id INT NOT NULL,
 type ENUM('multiple_choice','true_false','short_answer','scenario','image') DEFAULT 'multiple_choice',
 question TEXT NOT NULL,
 options_json JSON NULL,
 correct_answer VARCHAR(255) NOT NULL,
 image_path VARCHAR(255) DEFAULT NULL,
 explanation TEXT,
 FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE course_progress (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NOT NULL,
 course_id INT NOT NULL,
 status ENUM('not_started','in_progress','passed','failed') DEFAULT 'not_started',
 score INT DEFAULT 0,
 completed_at DATETIME NULL,
 UNIQUE KEY uniq_user_course(user_id, course_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE practical_signoffs (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NOT NULL,
 course_id INT NOT NULL,
 signed_by INT NOT NULL,
 notes TEXT,
 status ENUM('pending','signed_off','failed') DEFAULT 'signed_off',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE,
 FOREIGN KEY(signed_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE certificates (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NOT NULL,
 course_id INT NOT NULL,
 certificate_code VARCHAR(50) NOT NULL UNIQUE,
 issued_at DATETIME NOT NULL,
 expires_at DATETIME NULL,
 UNIQUE KEY uniq_cert(user_id, course_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users(first_name,last_name,email,password_hash,role,brand,department,position,site,status) VALUES
('Academy','Admin','admin@faithornstag.local','$2y$12$XOaBtGYqcV..V6DqKDU4/O6Vz64KW6OCPaYPMmsFhal0WzN9kB0ZC','admin','Faithorn & Stag','Management','Academy Administrator','Head Office','active');

INSERT INTO learning_paths(brand,name,level,description) VALUES
('Chuck & Blade','FOH Team Member','Team Member','Front of house team member pathway for Chuck & Blade Burgers.'),
('Chuck & Blade','BOH Team Member','Team Member','Back of house kitchen team member pathway for Chuck & Blade Burgers.'),
('Chuck & Blade','BOH Supervisor','Supervisor','Kitchen supervisor pathway including team member, BOH and supervisor practical sign-off.'),
('Faithorn & Stag','Assistant Manager','Management','Shared management pathway across Chuck & Blade and Bare Bones.'),
('Faithorn & Stag','General Manager','Management','Shared general manager pathway across all Faithorn & Stag brands.');

INSERT INTO courses(learning_path_id,title,summary,pass_mark,requires_practical,sort_order) VALUES
(1,'Welcome to Chuck & Blade','Brand story, culture, appearance, teamwork and service expectations.',80,0,1),
(1,'Food Safety & Hygiene','Hand washing, FIFO, food storage, temperature controls and documentation.',85,1,2),
(1,'Allergen Awareness','The 14 allergens, allergen matrix, customer communication and never guessing.',85,1,3),
(1,'Menu Knowledge','Core menu, specials, sides, sauces and customer recommendations.',80,0,4),
(1,'FOH Closing & Cleaning','Front of house closing, cleaning and handover standards.',80,1,5),
(2,'Welcome to Chuck & Blade','Brand story, culture, appearance, teamwork and service expectations.',80,0,1),
(2,'Food Safety & Hygiene','Hand washing, FIFO, food storage, temperature controls and documentation.',85,1,2),
(2,'Allergen Awareness','The 14 allergens, allergen matrix, customer communication and never guessing.',85,1,3),
(2,'Menu Knowledge','Core menu, specials, sides, sauces and customer recommendations.',80,0,4),
(2,'Burger Build Training','Smash burger method, build specs, portion control and presentation.',85,1,5),
(2,'Kitchen Operations & Cleaning','Kitchen opening, cleaning, fryer, stock and closing standards.',85,1,6),
(2,'Wastage Control','Nory wastage process and profit protection.',80,0,7),
(3,'Supervisor Practical Sign-Off','Supervisor level observation, coaching and operational control.',85,1,1);

INSERT INTO modules(course_id,title,body_html,asset_path,sort_order) VALUES
(1,'Welcome to Chuck & Blade','<p>Chuck & Blade is an award-winning smash burger brand built on bold flavours, high-energy service and consistency. Every guest should feel welcomed, looked after and excited to return.</p><p><strong>Culture of YES:</strong> if it is safe and possible, we say yes and make it happen.</p>','assets/chuck-logo.png',1),
(1,'Uniform Standards','<p>Uniform must be clean, complete and worn correctly. Black cap, yellow t-shirt, black apron, black trousers and kitchen-safe black shoes are the Chuck & Blade standard.</p>','assets/uniform-check.png',2),
(2,'Hand Washing','<p>Wash hands for at least 20 seconds. Wash before work, before ready-to-eat food, after toilet, raw meat, bins, cleaning, sneezing, eating, phone, hair, clothing, money or deliveries.</p>','assets/hand-washing.png',1),
(2,'FIFO & Fridge Rotation','<p>First In, First Out protects food safety and product quality. Label everything and store ready-to-eat foods above raw products.</p>','assets/fridge-rotation.png',2),
(2,'Probe Checks','<p>Cook hot food and reheated food to 75°C. Hot hold at 63°C or above. Keep chilled food at 8°C or below and frozen food at -18°C or below. Clean the probe before and after use.</p>','assets/probe-checks.png',3),
(3,'The 14 Main Allergens','<p>Know the 14 allergens and always take allergy enquiries seriously. Never guess. Use the official allergen matrix and escalate to a manager where required.</p>','assets/allergen-poster.png',1),
(4,'Signature Burgers','<p>Learn the build and ingredients for Big Charlie, Cheeseburger, Fiery Fagin, Dirty Harry, In & Out and Plain Jane.</p>','assets/signature-burgers-1.png',1),
(4,'Signature Burgers Part 2','<p>Continue learning Chuck & Blade signature burger specifications and presentation standards.</p>','assets/signature-burgers-2.png',2),
(4,'Chicken Burgers','<p>Learn Flying Fagin and Cluck Norris builds, sauces, cheese, pickles, lettuce and slaw standards.</p>','assets/chicken-burgers.png',3),
(4,'Loaded Fries & Tots','<p>Learn Poutine, Cheesy Bacon Fries/Tots, Fiery Fagin Fries/Tots and Dirty Charlie Fries/Tots.</p>','assets/loaded-fries.png',4),
(5,'FOH Closing','<p>Every shift must finish with the restaurant left clean, organised and ready for success.</p>','assets/foh-closing.png',1),
(10,'How to Smash a Burger','<p>Place puck on the grill, smash thin, scrape and check, flip and add cheese, then steam to perfection. Hot grill, smash hard, crust is king.</p>','assets/smash-storyboard.png',1),
(11,'Weekly Cleaning','<p>Follow daily and weekly cleaning schedules. Take photos and record completion in Safeintel.</p>','assets/weekly-cleaning.png',1),
(12,'Wastage Procedure','<p>Identify, weigh, log on Nory, dispose correctly, review and learn. All wastage must be recorded honestly and immediately.</p>','assets/wastage.png',1);

INSERT INTO questions(course_id,type,question,options_json,correct_answer,explanation) VALUES
(1,'multiple_choice','What does the Culture of YES mean?','["Always approving time off","Saying yes whenever it is safe and possible","Giving free food to regulars","Letting guests do anything"]','Saying yes whenever it is safe and possible','Safe and possible solutions are part of the brand culture.'),
(1,'multiple_choice','Which uniform item is required?','["Open toe shoes","Black cap","Personal hoodie","Jewellery"]','Black cap','The Chuck & Blade uniform includes a black cap.'),
(2,'multiple_choice','How long should hands be washed for?','["5 seconds","10 seconds","At least 20 seconds","1 minute"]','At least 20 seconds','The hand washing poster states at least 20 seconds.'),
(2,'multiple_choice','Where should raw meat be stored in the fridge?','["Top shelf","Middle shelf","Bottom shelf","Anywhere labelled"]','Bottom shelf','Raw products must be stored below ready-to-eat foods.'),
(2,'multiple_choice','Hot food must reach what minimum internal temperature?','["63°C","70°C","75°C","82°C"]','75°C','Probe checks show target cooking temperature of 75°C.'),
(3,'multiple_choice','What should staff do if unsure about an allergen?','["Guess if busy","Ask a manager and use the allergen matrix","Tell the customer it is probably fine","Remove one ingredient"]','Ask a manager and use the allergen matrix','Never guess allergens.'),
(3,'multiple_choice','Which is one of the 14 main allergens?','["Celery","Tomato","Beef","Chicken"]','Celery','Celery is one of the 14 main allergens.'),
(4,'multiple_choice','What sauce comes on the Big Charlie?','["Garlic Mayo","Dickens Sauce","BBQ Sauce","Frank''s Mayo"]','Dickens Sauce','Big Charlie uses Dickens Sauce.'),
(4,'multiple_choice','Which burger contains Frank''s spicy mayo?','["Dirty Harry","Big Charlie","In and Out","Cheeseburger"]','In and Out','In and Out contains Frank’s spicy mayo.'),
(4,'multiple_choice','What makes the Dirty Harry different from most other smash burgers?','["Beef brisket","Triple patties","Candy jalapeños and hash brown","Halloumi"]','Candy jalapeños and hash brown','Dirty Harry includes candy jalapeños and hash brown.'),
(4,'multiple_choice','Which burger contains smoked BBQ beef brisket?','["Owner''s Edition","BBQ Brisket Burger","Quadzilla","Big Charlie"]','BBQ Brisket Burger','BBQ Brisket Burger contains smoked BBQ beef brisket.'),
(4,'multiple_choice','How many American cheese slices are on the Quadzilla?','["6","8","4","5"]','8','Quadzilla uses 8 slices.'),
(4,'true_false','The Cheeseburger contains pickles.','["True","False"]','False','The Cheeseburger does not contain pickles.'),
(5,'multiple_choice','Which task belongs on the FOH closing checklist?','["Lock front door","Leave tables for morning","Turn fryers on","Ignore beer mats"]','Lock front door','Locking the front door is a closing task.'),
(10,'multiple_choice','What is the target cooking temperature for burgers?','["63°C","75°C","80°C","Colour only"]','75°C','Cooked food target is 75°C.'),
(10,'multiple_choice','What is the main goal when smashing the puck?','["Keep it round and tall","Smash thin to create lacy edges and crust","Cook it slowly","Avoid seasoning"]','Smash thin to create lacy edges and crust','The storyboard states smash it thin.'),
(12,'multiple_choice','Where must wastage be logged?','["Nory","WhatsApp only","Notebook only","It does not need logging"]','Nory','All wastage must be recorded on Nory.');
