-- ============================================================
-- PC00 Demo Store Team Setup Script
-- Creates 21 team members with pun names, roles, availability
-- ============================================================

START TRANSACTION;

-- ============================================================
-- STEP 1: Deactivate ALL existing pc00 store assignments
-- ============================================================
UPDATE kiosk_users.userStoreAssignments
SET isActive = 0, deactivatedAt = NOW()
WHERE typeNum = 'pc00' AND isActive = 1;

-- ============================================================
-- STEP 2: Clear existing pc00 availability data
-- ============================================================
DELETE FROM kiosk_pc00.scheduleAvailability;

-- ============================================================
-- STEP 3: Create users, store assignments, and availability
-- ============================================================
-- Roles: 1=Owner, 2=Manager, 3=Shift Lead, 4=Buyer, 5=Cashier
-- dayOfWeek: 0=Sun, 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat
-- NULL start/end times = unavailable all day
-- Pay rates:
--   Owner/Manager: $0 (salaried/profit)
--   Shift Leads: $18.50-$20.00 by seniority
--   Buyers: $16.00-$18.00 by seniority
--   Cashiers: $15.00
-- ============================================================

SET @effFrom = '2026-01-01';

-- ────────────────────────────────────────────────────────────
-- 1. PENNY WORTH - Owner
--    Former accountant who bought the store 5 years ago.
--    The steady hand who keeps the books balanced and the
--    team motivated. Works weekday business hours, pops in
--    Saturday mornings for inventory review.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Penny', 'Worth', 'Penny Worth', 'penny.worth@demo.buyerkiosk.com',
 'Store Owner', 0.00, '2021-03-15', 38.00, 30.00, 45.00,
 'homegrown', 'employee', 1, 1, 0);
SET @pennyId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@pennyId, 'pc00', 1, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@pennyId, 0, NULL, NULL, 0, @effFrom),
(@pennyId, 1, '09:00:00', '17:00:00', 1, @effFrom),
(@pennyId, 2, '09:00:00', '17:00:00', 1, @effFrom),
(@pennyId, 3, '09:00:00', '17:00:00', 1, @effFrom),
(@pennyId, 4, '09:00:00', '17:00:00', 1, @effFrom),
(@pennyId, 5, '09:00:00', '17:00:00', 1, @effFrom),
(@pennyId, 6, '10:00:00', '14:00:00', 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 2. CRYSTAL BALL - General Manager
--    Has an uncanny ability to predict customer trends and
--    inventory needs. Previously managed a vintage boutique
--    downtown. Opens the store most weekdays. The backbone
--    of daily operations.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Crystal', 'Ball', 'Crystal Ball', 'crystal.ball@demo.buyerkiosk.com',
 'General Manager', 0.00, '2022-01-10', 40.00, 35.00, 45.00,
 'homegrown', 'employee', 1, 1, 0);
SET @crystalId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@crystalId, 'pc00', 2, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@crystalId, 0, NULL, NULL, 0, @effFrom),
(@crystalId, 1, '09:00:00', '18:00:00', 1, @effFrom),
(@crystalId, 2, '09:00:00', '18:00:00', 1, @effFrom),
(@crystalId, 3, '09:00:00', '18:00:00', 1, @effFrom),
(@crystalId, 4, '09:00:00', '18:00:00', 1, @effFrom),
(@crystalId, 5, '09:00:00', '18:00:00', 1, @effFrom),
(@crystalId, 6, '10:00:00', '16:00:00', 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 3. BARB DWYER - Shift Lead (Opening) [Hired 2022 - most senior SL]
--    Mom of two teenagers. Her kids catch the bus at 7:30am so
--    she's at the store by 8am every weekday. Needs to be home
--    by 3:30pm for after-school activities. The team's "store
--    mom" who keeps the break room stocked with homemade cookies.
--    Not available weekends (family time).
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Barb', 'Dwyer', 'Barb Dwyer', 'barb.dwyer@demo.buyerkiosk.com',
 'Shift Lead - Opening', 20.00, '2022-08-20', 30.00, 25.00, 35.00,
 'homegrown', 'employee', 1, 1, 0);
SET @barbId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@barbId, 'pc00', 3, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@barbId, 0, NULL, NULL, 0, @effFrom),
(@barbId, 1, '08:00:00', '15:00:00', 1, @effFrom),
(@barbId, 2, '08:00:00', '15:00:00', 1, @effFrom),
(@barbId, 3, '08:00:00', '15:00:00', 1, @effFrom),
(@barbId, 4, '08:00:00', '15:00:00', 1, @effFrom),
(@barbId, 5, '08:00:00', '15:00:00', 1, @effFrom),
(@barbId, 6, NULL, NULL, 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 4. KOLE CUTTS - Shift Lead (Closing) [Hired 2023-03]
--    Former barber and total night owl. Great with customer
--    interactions thanks to years of chatting in the chair.
--    Handles closing with precision - register counts are
--    always penny-perfect. Mon-Sat closer.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Kole', 'Cutts', 'Kole Cutts', 'kole.cutts@demo.buyerkiosk.com',
 'Shift Lead - Closing', 19.50, '2023-03-15', 32.00, 25.00, 38.00,
 'homegrown', 'employee', 1, 1, 0);
SET @koleId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@koleId, 'pc00', 3, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@koleId, 0, NULL, NULL, 0, @effFrom),
(@koleId, 1, '13:00:00', '19:30:00', 1, @effFrom),
(@koleId, 2, '13:00:00', '19:30:00', 1, @effFrom),
(@koleId, 3, '13:00:00', '19:30:00', 1, @effFrom),
(@koleId, 4, '13:00:00', '19:30:00', 1, @effFrom),
(@koleId, 5, '13:00:00', '19:30:00', 1, @effFrom),
(@koleId, 6, '12:00:00', '18:30:00', 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 5. JOY RIDER - Shift Lead (Opening) [Hired 2023-06]
--    Energetic morning person who bikes 5 miles to work rain
--    or shine. Former camp counselor who brings infectious
--    energy to the morning crew. Always first one in, coffee
--    already brewing when others arrive. Mon-Sat opener.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Joy', 'Rider', 'Joy Rider', 'joy.rider@demo.buyerkiosk.com',
 'Shift Lead - Opening', 19.25, '2023-06-01', 35.00, 30.00, 40.00,
 'homegrown', 'employee', 1, 1, 0);
SET @joyId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@joyId, 'pc00', 3, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@joyId, 0, NULL, NULL, 0, @effFrom),
(@joyId, 1, '08:00:00', '16:00:00', 1, @effFrom),
(@joyId, 2, '08:00:00', '16:00:00', 1, @effFrom),
(@joyId, 3, '08:00:00', '16:00:00', 1, @effFrom),
(@joyId, 4, '08:00:00', '16:00:00', 1, @effFrom),
(@joyId, 5, '08:00:00', '16:00:00', 1, @effFrom),
(@joyId, 6, '09:00:00', '15:00:00', 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 6. HOLLY DAY - Shift Lead (Weekends) [Hired 2023-09]
--    Social media-savvy 26-year-old who runs the store's
--    TikTok and Instagram. Works Wed-Sun so she covers weekend
--    rushes. Takes Mon-Tue off to edit content and plan posts.
--    The go-to person for weekend opening AND closing duties.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Holly', 'Day', 'Holly Day', 'holly.day@demo.buyerkiosk.com',
 'Shift Lead - Weekends', 19.00, '2023-09-01', 32.00, 25.00, 38.00,
 'homegrown', 'employee', 1, 1, 0);
SET @hollyId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@hollyId, 'pc00', 3, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@hollyId, 0, '10:30:00', '17:30:00', 1, @effFrom),
(@hollyId, 1, NULL, NULL, 0, @effFrom),
(@hollyId, 2, NULL, NULL, 0, @effFrom),
(@hollyId, 3, '12:00:00', '19:00:00', 0, @effFrom),
(@hollyId, 4, '12:00:00', '19:00:00', 0, @effFrom),
(@hollyId, 5, '12:00:00', '19:00:00', 1, @effFrom),
(@hollyId, 6, '09:30:00', '18:30:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 7. WILL POWER - Shift Lead (Closing) [Hired 2023-11]
--    Former gym trainer, 29. The go-to guy for moving furniture
--    and heavy items. Handles closing shifts Tue-Sat. Takes
--    Mon/Sun off to coach youth basketball at the rec center.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Will', 'Power', 'Will Power', 'will.power@demo.buyerkiosk.com',
 'Shift Lead - Closing', 18.75, '2023-11-01', 32.00, 28.00, 38.00,
 'homegrown', 'employee', 1, 1, 0);
SET @willId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@willId, 'pc00', 3, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@willId, 0, NULL, NULL, 0, @effFrom),
(@willId, 1, NULL, NULL, 0, @effFrom),
(@willId, 2, '12:00:00', '19:30:00', 1, @effFrom),
(@willId, 3, '12:00:00', '19:30:00', 1, @effFrom),
(@willId, 4, '12:00:00', '19:30:00', 1, @effFrom),
(@willId, 5, '12:00:00', '19:30:00', 1, @effFrom),
(@willId, 6, '12:00:00', '18:30:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 8. PAIGE TURNER - Shift Lead [Hired 2024-01 - newest SL]
--    English literature grad student specializing in rare books.
--    Expert at pricing media, books, and vinyl. Works Tue-Sat,
--    takes Mondays off for her seminar. Writing her thesis on
--    secondhand book economies.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Paige', 'Turner', 'Paige Turner', 'paige.turner@demo.buyerkiosk.com',
 'Shift Lead', 18.50, '2024-01-15', 28.00, 20.00, 32.00,
 'homegrown', 'employee', 1, 1, 0);
SET @paigeId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@paigeId, 'pc00', 3, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@paigeId, 0, NULL, NULL, 0, @effFrom),
(@paigeId, 1, NULL, NULL, 0, @effFrom),
(@paigeId, 2, '10:00:00', '18:00:00', 1, @effFrom),
(@paigeId, 3, '10:00:00', '18:00:00', 1, @effFrom),
(@paigeId, 4, '10:00:00', '18:00:00', 1, @effFrom),
(@paigeId, 5, '10:00:00', '18:00:00', 1, @effFrom),
(@paigeId, 6, '10:00:00', '18:00:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 9. DAWN BROKER - Senior Buyer [Hired 2021 - most senior buyer]
--    Early bird, 42. One of the longest-tenured employees.
--    Single mom who drops her daughter at daycare at 7:30am
--    and picks up at 1:30pm. Mentors new buyers and knows
--    every regular seller by name. The morning buying line
--    moves fastest when Dawn is at the counter.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Dawn', 'Broker', 'Dawn Broker', 'dawn.broker@demo.buyerkiosk.com',
 'Senior Buyer', 18.00, '2021-11-01', 25.00, 20.00, 30.00,
 'homegrown', 'employee', 1, 1, 0);
SET @dawnId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@dawnId, 'pc00', 4, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@dawnId, 0, NULL, NULL, 0, @effFrom),
(@dawnId, 1, '08:00:00', '13:00:00', 1, @effFrom),
(@dawnId, 2, '08:00:00', '13:00:00', 1, @effFrom),
(@dawnId, 3, '08:00:00', '13:00:00', 1, @effFrom),
(@dawnId, 4, '08:00:00', '13:00:00', 1, @effFrom),
(@dawnId, 5, '08:00:00', '13:00:00', 1, @effFrom),
(@dawnId, 6, '09:00:00', '13:00:00', 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 10. JEWEL CASE - Buyer, Jewelry Specialist [Hired 2022]
--    Mom of two in elementary school. GIA-certified gemologist
--    who left the corporate jewelry world for better work-life
--    balance. Drops kids at school at 8:30, works until 2:30
--    pickup. Expert at spotting valuable pieces in estate lots.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Jewel', 'Case', 'Jewel Case', 'jewel.case@demo.buyerkiosk.com',
 'Buyer - Jewelry Specialist', 17.50, '2022-04-10', 25.00, 20.00, 28.00,
 'homegrown', 'employee', 1, 1, 0);
SET @jewelId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@jewelId, 'pc00', 4, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@jewelId, 0, NULL, NULL, 0, @effFrom),
(@jewelId, 1, '09:00:00', '14:30:00', 1, @effFrom),
(@jewelId, 2, '09:00:00', '14:30:00', 1, @effFrom),
(@jewelId, 3, '09:00:00', '14:30:00', 1, @effFrom),
(@jewelId, 4, '09:00:00', '14:30:00', 1, @effFrom),
(@jewelId, 5, '09:00:00', '14:30:00', 1, @effFrom),
(@jewelId, 6, NULL, NULL, 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 11. ART E. FACT - Buyer, Antiques Specialist [Hired 2023-02]
--    Semi-retired antiques dealer, 55. Only works Mon, Wed,
--    and Fri - spends the other days at estate sales scouting
--    items for his own collection. Expert in vintage furniture,
--    art, and collectibles. The team calls him "the appraiser."
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Art', 'Fact', 'Art E. Fact', 'art.fact@demo.buyerkiosk.com',
 'Buyer - Antiques Specialist', 17.00, '2023-02-01', 18.00, 15.00, 22.00,
 'homegrown', 'employee', 1, 1, 0);
SET @artId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@artId, 'pc00', 4, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@artId, 0, NULL, NULL, 0, @effFrom),
(@artId, 1, '10:00:00', '16:00:00', 1, @effFrom),
(@artId, 2, NULL, NULL, 0, @effFrom),
(@artId, 3, '10:00:00', '16:00:00', 1, @effFrom),
(@artId, 4, NULL, NULL, 0, @effFrom),
(@artId, 5, '10:00:00', '16:00:00', 1, @effFrom),
(@artId, 6, NULL, NULL, 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 12. RUBY STONE - Buyer, Clothing Specialist [Hired 2023-07]
--    Vintage clothing enthusiast, 33. Runs the store's Instagram
--    for fashion finds. Has a massive following for her "thrift
--    flip" posts. Works full-time Tue-Sat. Takes Mon/Sun off
--    to source vintage at flea markets.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Ruby', 'Stone', 'Ruby Stone', 'ruby.stone@demo.buyerkiosk.com',
 'Buyer - Clothing Specialist', 17.00, '2023-07-15', 35.00, 30.00, 40.00,
 'homegrown', 'employee', 1, 1, 0);
SET @rubyId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@rubyId, 'pc00', 4, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@rubyId, 0, NULL, NULL, 0, @effFrom),
(@rubyId, 1, NULL, NULL, 0, @effFrom),
(@rubyId, 2, '10:00:00', '18:00:00', 1, @effFrom),
(@rubyId, 3, '10:00:00', '18:00:00', 1, @effFrom),
(@rubyId, 4, '10:00:00', '18:00:00', 1, @effFrom),
(@rubyId, 5, '10:00:00', '18:00:00', 1, @effFrom),
(@rubyId, 6, '10:00:00', '18:00:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 13. MELODY KEYS - Buyer, Electronics & Music [Hired 2024-03]
--    Working musician, 27. Plays keyboard in a local band that
--    rehearses weekday mornings. Expert in pricing instruments,
--    audio equipment, and electronics. Available afternoons
--    Mon-Thu, full days Fri-Sat. Sunday off for gigs.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Melody', 'Keys', 'Melody Keys', 'melody.keys@demo.buyerkiosk.com',
 'Buyer - Electronics & Music', 16.50, '2024-03-01', 30.00, 24.00, 35.00,
 'homegrown', 'employee', 1, 1, 0);
SET @melodyId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@melodyId, 'pc00', 4, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@melodyId, 0, NULL, NULL, 0, @effFrom),
(@melodyId, 1, '13:00:00', '19:00:00', 1, @effFrom),
(@melodyId, 2, '13:00:00', '19:00:00', 1, @effFrom),
(@melodyId, 3, '13:00:00', '19:00:00', 1, @effFrom),
(@melodyId, 4, '13:00:00', '19:00:00', 1, @effFrom),
(@melodyId, 5, '10:00:00', '19:00:00', 1, @effFrom),
(@melodyId, 6, '10:00:00', '18:00:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 14. MARE Z. DOATS - Buyer [Hired 2024-05]
--    Retired horse trainer, 48. Works part-time to stay active
--    and social. Available Tue-Thu full day and Saturday mornings.
--    Expert in sporting goods, saddles, and leather goods.
--    Her regulars bring her horse treats as a joke.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Mare', 'Doats', 'Mare Z. Doats', 'mare.doats@demo.buyerkiosk.com',
 'Buyer', 16.50, '2024-05-01', 24.00, 18.00, 28.00,
 'homegrown', 'employee', 1, 1, 0);
SET @mareId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@mareId, 'pc00', 4, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@mareId, 0, NULL, NULL, 0, @effFrom),
(@mareId, 1, NULL, NULL, 0, @effFrom),
(@mareId, 2, '10:00:00', '18:00:00', 1, @effFrom),
(@mareId, 3, '10:00:00', '18:00:00', 1, @effFrom),
(@mareId, 4, '10:00:00', '18:00:00', 1, @effFrom),
(@mareId, 5, NULL, NULL, 0, @effFrom),
(@mareId, 6, '10:00:00', '14:00:00', 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 15. SANDY SHORE - Buyer [Hired 2024-08]
--    College junior studying marine biology. Has morning classes
--    Mon-Fri, so only available after 2pm on weekdays. Loves
--    working weekends. Specializes in buying sporting goods
--    and outdoor gear.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Sandy', 'Shore', 'Sandy Shore', 'sandy.shore@demo.buyerkiosk.com',
 'Buyer', 16.00, '2024-08-20', 22.00, 15.00, 25.00,
 'homegrown', 'employee', 1, 1, 0);
SET @sandyId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@sandyId, 'pc00', 4, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@sandyId, 0, '11:00:00', '17:00:00', 1, @effFrom),
(@sandyId, 1, '14:00:00', '19:00:00', 1, @effFrom),
(@sandyId, 2, '14:00:00', '19:00:00', 1, @effFrom),
(@sandyId, 3, '14:00:00', '19:00:00', 1, @effFrom),
(@sandyId, 4, '14:00:00', '19:00:00', 1, @effFrom),
(@sandyId, 5, '14:00:00', '19:00:00', 1, @effFrom),
(@sandyId, 6, '10:00:00', '18:00:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 16. IVY LEAGUE - Buyer [Hired 2024-09]
--    College sophomore, business major. Wants retail management
--    experience for her resume. Has classes until 1:30pm Mon-Thu.
--    Available all day Saturday. Takes Sunday off to study.
--    Surprisingly good at pricing designer handbags.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Ivy', 'League', 'Ivy League', 'ivy.league@demo.buyerkiosk.com',
 'Buyer', 16.00, '2024-09-05', 18.00, 12.00, 22.00,
 'homegrown', 'employee', 1, 1, 0);
SET @ivyId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@ivyId, 'pc00', 4, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@ivyId, 0, NULL, NULL, 0, @effFrom),
(@ivyId, 1, '14:00:00', '19:00:00', 1, @effFrom),
(@ivyId, 2, '14:00:00', '19:00:00', 1, @effFrom),
(@ivyId, 3, '14:00:00', '19:00:00', 1, @effFrom),
(@ivyId, 4, '14:00:00', '19:00:00', 1, @effFrom),
(@ivyId, 5, '14:00:00', '19:00:00', 1, @effFrom),
(@ivyId, 6, '10:00:00', '18:00:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 17. SUMMER RAY - Buyer [Hired 2025 - newest buyer]
--    19-year-old on a gap year before starting college in the
--    fall. Super flexible schedule and eager to learn everything.
--    Lives nearby so no commute concerns. The team's "flex
--    player" who fills in wherever needed.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Summer', 'Ray', 'Summer Ray', 'summer.ray@demo.buyerkiosk.com',
 'Buyer', 16.00, '2025-06-15', 32.00, 25.00, 38.00,
 'homegrown', 'employee', 1, 1, 0);
SET @summerId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@summerId, 'pc00', 4, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@summerId, 0, '11:00:00', '17:00:00', 0, @effFrom),
(@summerId, 1, '09:00:00', '19:00:00', 1, @effFrom),
(@summerId, 2, '09:00:00', '19:00:00', 1, @effFrom),
(@summerId, 3, '09:00:00', '19:00:00', 1, @effFrom),
(@summerId, 4, '09:00:00', '19:00:00', 1, @effFrom),
(@summerId, 5, '09:00:00', '19:00:00', 1, @effFrom),
(@summerId, 6, '10:00:00', '18:00:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 18. SUE PERVISOR - Lead Cashier [Hired 2023 - most senior cashier]
--    Aspiring to become a shift lead, 35. Reliable full-timer
--    who works Mon-Fri. Trains every new cashier and has the
--    patience of a saint. Takes weekends off to spend with her
--    partner and their two dogs. Crystal Ball is mentoring her
--    for a future promotion.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Sue', 'Pervisor', 'Sue Pervisor', 'sue.pervisor@demo.buyerkiosk.com',
 'Lead Cashier', 15.00, '2023-04-01', 35.00, 30.00, 40.00,
 'homegrown', 'employee', 1, 1, 0);
SET @sueId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@sueId, 'pc00', 5, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@sueId, 0, NULL, NULL, 0, @effFrom),
(@sueId, 1, '10:00:00', '18:00:00', 1, @effFrom),
(@sueId, 2, '10:00:00', '18:00:00', 1, @effFrom),
(@sueId, 3, '10:00:00', '18:00:00', 1, @effFrom),
(@sueId, 4, '10:00:00', '18:00:00', 1, @effFrom),
(@sueId, 5, '10:00:00', '18:00:00', 1, @effFrom),
(@sueId, 6, NULL, NULL, 0, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 19. CLAIRE VOYANT - Cashier [Hired 2024-06]
--    Artist who sells watercolors at weekend markets, 30.
--    Prefers afternoon/evening shifts Thu-Sun when the store
--    is busiest. Takes Mon-Wed off to paint and prep for
--    weekend art shows. Decorates the store's seasonal
--    window displays as a passion project.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Claire', 'Voyant', 'Claire Voyant', 'claire.voyant@demo.buyerkiosk.com',
 'Cashier', 15.00, '2024-06-01', 22.00, 16.00, 25.00,
 'homegrown', 'employee', 1, 1, 0);
SET @claireId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@claireId, 'pc00', 5, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@claireId, 0, '11:00:00', '17:00:00', 1, @effFrom),
(@claireId, 1, NULL, NULL, 0, @effFrom),
(@claireId, 2, NULL, NULL, 0, @effFrom),
(@claireId, 3, NULL, NULL, 0, @effFrom),
(@claireId, 4, '13:00:00', '19:00:00', 1, @effFrom),
(@claireId, 5, '13:00:00', '19:00:00', 1, @effFrom),
(@claireId, 6, '10:00:00', '18:00:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 20. BILL COUNTER - Cashier [Hired 2024-09]
--    College student studying accounting, 20. The irony of his
--    name is not lost on anyone. Works evenings after classes
--    and all day on weekends. Dreams of being a CPA. His
--    register is always balanced to the penny.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Bill', 'Counter', 'Bill Counter', 'bill.counter@demo.buyerkiosk.com',
 'Cashier', 15.00, '2024-09-10', 22.00, 15.00, 25.00,
 'homegrown', 'employee', 1, 1, 0);
SET @billId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@billId, 'pc00', 5, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@billId, 0, '11:00:00', '17:00:00', 1, @effFrom),
(@billId, 1, '15:00:00', '19:00:00', 1, @effFrom),
(@billId, 2, '15:00:00', '19:00:00', 1, @effFrom),
(@billId, 3, '15:00:00', '19:00:00', 1, @effFrom),
(@billId, 4, '15:00:00', '19:00:00', 1, @effFrom),
(@billId, 5, '15:00:00', '19:00:00', 1, @effFrom),
(@billId, 6, '10:00:00', '18:00:00', 1, @effFrom);

-- ────────────────────────────────────────────────────────────
-- 21. CARRIE BAGS - Cashier [Hired 2025 - newest cashier]
--    Grad student working on her sociology thesis about
--    secondhand economies, 23. Irregular schedule - works
--    Tue/Thu/Sat/Sun. Her research and her job overlap
--    perfectly. Customers love her because she always
--    double-bags the fragile stuff.
-- ────────────────────────────────────────────────────────────
INSERT INTO kiosk_users.users
(firstName, lastName, displayName, email, position, hourlyRate, hireDate,
 hoursRequested, hoursMin, hoursMax, source, accountType, enabled, active, canLogin)
VALUES
('Carrie', 'Bags', 'Carrie Bags', 'carrie.bags@demo.buyerkiosk.com',
 'Cashier', 15.00, '2025-01-15', 18.00, 12.00, 22.00,
 'homegrown', 'employee', 1, 1, 0);
SET @carrieId = LAST_INSERT_ID();

INSERT INTO kiosk_users.userStoreAssignments
(userId, typeNum, role, isActive, assignedAt)
VALUES (@carrieId, 'pc00', 5, 1, NOW());

INSERT INTO kiosk_pc00.scheduleAvailability
(employeeId, dayOfWeek, startTime, endTime, isPreferred, effectiveFrom) VALUES
(@carrieId, 0, '11:00:00', '17:00:00', 1, @effFrom),
(@carrieId, 1, NULL, NULL, 0, @effFrom),
(@carrieId, 2, '10:00:00', '18:00:00', 1, @effFrom),
(@carrieId, 3, NULL, NULL, 0, @effFrom),
(@carrieId, 4, '10:00:00', '18:00:00', 1, @effFrom),
(@carrieId, 5, NULL, NULL, 0, @effFrom),
(@carrieId, 6, '10:00:00', '18:00:00', 1, @effFrom);

COMMIT;

-- ============================================================
-- VERIFICATION
-- ============================================================
SELECT '=== PC00 TEAM ROSTER ===' AS '';
SELECT u.id, u.displayName, u.position,
       CASE usa.role
         WHEN 1 THEN 'Owner'
         WHEN 2 THEN 'Manager'
         WHEN 3 THEN 'Shift Lead'
         WHEN 4 THEN 'Buyer'
         WHEN 5 THEN 'Cashier'
       END AS role,
       CONCAT('$', FORMAT(u.hourlyRate, 2)) AS rate,
       u.hireDate,
       u.hoursRequested AS hrsReq,
       u.hoursMin AS hrsMin,
       u.hoursMax AS hrsMax
FROM kiosk_users.users u
JOIN kiosk_users.userStoreAssignments usa ON u.id = usa.userId
WHERE usa.typeNum = 'pc00' AND usa.isActive = 1
ORDER BY usa.role, u.hireDate;

SELECT '=== AVAILABILITY SUMMARY ===' AS '';
SELECT u.displayName,
       GROUP_CONCAT(
         CASE sa.dayOfWeek
           WHEN 0 THEN 'Sun'
           WHEN 1 THEN 'Mon'
           WHEN 2 THEN 'Tue'
           WHEN 3 THEN 'Wed'
           WHEN 4 THEN 'Thu'
           WHEN 5 THEN 'Fri'
           WHEN 6 THEN 'Sat'
         END,
         ':',
         COALESCE(CONCAT(TIME_FORMAT(sa.startTime,'%h:%i%p'),'-',TIME_FORMAT(sa.endTime,'%h:%i%p')), 'OFF')
         ORDER BY sa.dayOfWeek
         SEPARATOR ' | '
       ) AS schedule
FROM kiosk_pc00.scheduleAvailability sa
JOIN kiosk_users.users u ON sa.employeeId = u.id
JOIN kiosk_users.userStoreAssignments usa ON u.id = usa.userId AND usa.typeNum = 'pc00'
WHERE usa.isActive = 1
GROUP BY u.id, u.displayName
ORDER BY usa.role, u.displayName;

SELECT '=== DEACTIVATED (old members) ===' AS '';
SELECT u.id, u.displayName, usa.deactivatedAt
FROM kiosk_users.users u
JOIN kiosk_users.userStoreAssignments usa ON u.id = usa.userId
WHERE usa.typeNum = 'pc00' AND usa.isActive = 0
ORDER BY u.displayName;

SELECT '=== COUNTS ===' AS '';
SELECT
  (SELECT COUNT(*) FROM kiosk_users.userStoreAssignments WHERE typeNum = 'pc00' AND isActive = 1) AS activeMembers,
  (SELECT COUNT(*) FROM kiosk_pc00.scheduleAvailability) AS availabilityRecords;
