-- ============================================================
-- PC00 Schedule Template: "Standard Week"
-- Store hours: Mon-Fri 10am-7pm, Sat 10am-6pm, Sun 11am-5pm
-- ============================================================
-- Employee IDs:
--   8321=Penny, 8322=Crystal, 8323=Barb, 8324=Kole, 8325=Joy,
--   8326=Holly, 8327=Will, 8328=Paige, 8329=Dawn, 8330=Jewel,
--   8331=Art, 8332=Ruby, 8333=Melody, 8334=Mare, 8335=Sandy,
--   8336=Ivy, 8337=Summer, 8338=Sue, 8339=Claire, 8340=Bill,
--   8341=Carrie
-- Position IDs: 1=Owner, 2=GM, 4=Shift Lead, 5=Buyer, 6=Sales Associate
-- dayOfWeek: 0=Sun, 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat
-- ============================================================

START TRANSACTION;

-- Create the template
INSERT INTO kiosk_pc00.scheduleTemplates
(name, description, shiftCount, createdByUserId)
VALUES
('Standard Week', 'Typical weekly schedule with full coverage. Mon-Fri 10am-7pm, Sat 10am-6pm, Sun 11am-5pm. Opener/closer shift leads every day, 2-3 buyers during peak hours, multiple cashiers on weekends.', 0, 1);

SET @tplId = LAST_INSERT_ID();

-- ============================================================
-- MONDAY (dayOfWeek=1) - Store: 10am-7pm
-- ============================================================
INSERT INTO kiosk_pc00.scheduleTemplateShifts
(templateId, dayOfWeek, startTime, endTime, employeeId, positionId, minRoleId, notes) VALUES
-- Leadership
(@tplId, 1, '09:30:00', '17:00:00', 8321, 1, 1, 'Owner - weekday office hours'),
(@tplId, 1, '09:00:00', '17:30:00', 8322, 2, 2, 'GM - opens, runs floor'),
(@tplId, 1, '09:30:00', '16:30:00', 8325, 4, 3, 'Joy - AM shift lead, opens'),
(@tplId, 1, '09:30:00', '15:00:00', 8323, 4, 3, 'Barb - AM shift lead, opens'),
(@tplId, 1, '13:00:00', '19:30:00', 8324, 4, 3, 'Kole - PM shift lead, closes'),
-- Buyers
(@tplId, 1, '09:30:00', '13:00:00', 8329, 5, 4, 'Dawn - morning buyer'),
(@tplId, 1, '09:30:00', '14:30:00', 8330, 5, 4, 'Jewel - morning buyer'),
(@tplId, 1, '10:00:00', '16:00:00', 8331, 5, 4, 'Art - midday buyer (M/W/F)'),
(@tplId, 1, '13:00:00', '19:00:00', 8333, 5, 4, 'Melody - afternoon buyer'),
(@tplId, 1, '10:00:00', '18:00:00', 8337, 5, 4, 'Summer - flex buyer'),
-- Cashiers
(@tplId, 1, '10:00:00', '17:00:00', 8338, 6, 5, 'Sue - lead cashier'),
(@tplId, 1, '15:00:00', '19:00:00', 8340, 6, 5, 'Bill - evening cashier'),

-- ============================================================
-- TUESDAY (dayOfWeek=2) - Store: 10am-7pm
-- ============================================================
-- Leadership
(@tplId, 2, '09:30:00', '17:00:00', 8321, 1, 1, 'Owner - weekday office hours'),
(@tplId, 2, '09:00:00', '17:30:00', 8322, 2, 2, 'GM - opens, runs floor'),
(@tplId, 2, '09:30:00', '16:30:00', 8325, 4, 3, 'Joy - AM shift lead, opens'),
(@tplId, 2, '09:30:00', '15:00:00', 8323, 4, 3, 'Barb - AM shift lead'),
(@tplId, 2, '10:00:00', '18:00:00', 8328, 4, 3, 'Paige - mid shift lead'),
(@tplId, 2, '12:00:00', '19:30:00', 8327, 4, 3, 'Will - PM shift lead, closes'),
-- Buyers
(@tplId, 2, '09:30:00', '13:00:00', 8329, 5, 4, 'Dawn - morning buyer'),
(@tplId, 2, '09:30:00', '14:30:00', 8330, 5, 4, 'Jewel - morning buyer'),
(@tplId, 2, '10:00:00', '18:00:00', 8332, 5, 4, 'Ruby - full day buyer'),
(@tplId, 2, '10:00:00', '18:00:00', 8334, 5, 4, 'Mare - full day buyer'),
(@tplId, 2, '14:00:00', '19:00:00', 8335, 5, 4, 'Sandy - afternoon buyer'),
-- Cashiers
(@tplId, 2, '10:00:00', '17:00:00', 8338, 6, 5, 'Sue - lead cashier'),
(@tplId, 2, '10:00:00', '18:00:00', 8341, 6, 5, 'Carrie - cashier'),

-- ============================================================
-- WEDNESDAY (dayOfWeek=3) - Store: 10am-7pm
-- ============================================================
-- Leadership
(@tplId, 3, '09:30:00', '17:00:00', 8321, 1, 1, 'Owner - weekday office hours'),
(@tplId, 3, '09:00:00', '17:30:00', 8322, 2, 2, 'GM - opens, runs floor'),
(@tplId, 3, '09:30:00', '16:30:00', 8325, 4, 3, 'Joy - AM shift lead, opens'),
(@tplId, 3, '09:30:00', '15:00:00', 8323, 4, 3, 'Barb - AM shift lead'),
(@tplId, 3, '10:00:00', '18:00:00', 8328, 4, 3, 'Paige - mid shift lead'),
(@tplId, 3, '12:00:00', '19:00:00', 8326, 4, 3, 'Holly - mid/close shift lead'),
(@tplId, 3, '13:00:00', '19:30:00', 8324, 4, 3, 'Kole - PM shift lead, closes'),
-- Buyers
(@tplId, 3, '09:30:00', '13:00:00', 8329, 5, 4, 'Dawn - morning buyer'),
(@tplId, 3, '10:00:00', '16:00:00', 8331, 5, 4, 'Art - midday buyer (M/W/F)'),
(@tplId, 3, '10:00:00', '18:00:00', 8334, 5, 4, 'Mare - full day buyer'),
(@tplId, 3, '13:00:00', '19:00:00', 8333, 5, 4, 'Melody - afternoon buyer'),
(@tplId, 3, '10:00:00', '18:00:00', 8337, 5, 4, 'Summer - flex buyer'),
-- Cashiers
(@tplId, 3, '10:00:00', '17:00:00', 8338, 6, 5, 'Sue - lead cashier'),
(@tplId, 3, '15:00:00', '19:00:00', 8340, 6, 5, 'Bill - evening cashier'),

-- ============================================================
-- THURSDAY (dayOfWeek=4) - Store: 10am-7pm
-- ============================================================
-- Leadership
(@tplId, 4, '09:30:00', '17:00:00', 8321, 1, 1, 'Owner - weekday office hours'),
(@tplId, 4, '09:00:00', '17:30:00', 8322, 2, 2, 'GM - opens, runs floor'),
(@tplId, 4, '09:30:00', '16:30:00', 8325, 4, 3, 'Joy - AM shift lead, opens'),
(@tplId, 4, '09:30:00', '15:00:00', 8323, 4, 3, 'Barb - AM shift lead'),
(@tplId, 4, '10:00:00', '18:00:00', 8328, 4, 3, 'Paige - mid shift lead'),
(@tplId, 4, '12:00:00', '19:00:00', 8326, 4, 3, 'Holly - mid/close shift lead'),
(@tplId, 4, '12:00:00', '19:30:00', 8327, 4, 3, 'Will - PM shift lead, closes'),
-- Buyers
(@tplId, 4, '09:30:00', '13:00:00', 8329, 5, 4, 'Dawn - morning buyer'),
(@tplId, 4, '09:30:00', '14:30:00', 8330, 5, 4, 'Jewel - morning buyer'),
(@tplId, 4, '10:00:00', '18:00:00', 8332, 5, 4, 'Ruby - full day buyer'),
(@tplId, 4, '10:00:00', '18:00:00', 8334, 5, 4, 'Mare - full day buyer'),
(@tplId, 4, '14:00:00', '19:00:00', 8336, 5, 4, 'Ivy - afternoon buyer'),
-- Cashiers
(@tplId, 4, '10:00:00', '17:00:00', 8338, 6, 5, 'Sue - lead cashier'),
(@tplId, 4, '13:00:00', '19:00:00', 8339, 6, 5, 'Claire - afternoon cashier'),
(@tplId, 4, '10:00:00', '18:00:00', 8341, 6, 5, 'Carrie - cashier'),

-- ============================================================
-- FRIDAY (dayOfWeek=5) - Store: 10am-7pm (busier)
-- ============================================================
-- Leadership
(@tplId, 5, '09:30:00', '17:00:00', 8321, 1, 1, 'Owner - weekday office hours'),
(@tplId, 5, '09:00:00', '17:30:00', 8322, 2, 2, 'GM - opens, runs floor'),
(@tplId, 5, '09:30:00', '16:30:00', 8325, 4, 3, 'Joy - AM shift lead, opens'),
(@tplId, 5, '09:30:00', '15:00:00', 8323, 4, 3, 'Barb - AM shift lead'),
(@tplId, 5, '10:00:00', '18:00:00', 8328, 4, 3, 'Paige - mid shift lead'),
(@tplId, 5, '12:00:00', '19:00:00', 8326, 4, 3, 'Holly - mid/close shift lead'),
(@tplId, 5, '13:00:00', '19:30:00', 8324, 4, 3, 'Kole - PM shift lead, closes'),
(@tplId, 5, '12:00:00', '19:30:00', 8327, 4, 3, 'Will - PM shift lead'),
-- Buyers
(@tplId, 5, '09:30:00', '13:00:00', 8329, 5, 4, 'Dawn - morning buyer'),
(@tplId, 5, '09:30:00', '14:30:00', 8330, 5, 4, 'Jewel - morning buyer'),
(@tplId, 5, '10:00:00', '16:00:00', 8331, 5, 4, 'Art - midday buyer (M/W/F)'),
(@tplId, 5, '10:00:00', '19:00:00', 8333, 5, 4, 'Melody - full day buyer'),
(@tplId, 5, '10:00:00', '18:00:00', 8332, 5, 4, 'Ruby - full day buyer'),
(@tplId, 5, '10:00:00', '18:00:00', 8337, 5, 4, 'Summer - flex buyer'),
(@tplId, 5, '14:00:00', '19:00:00', 8335, 5, 4, 'Sandy - afternoon buyer'),
(@tplId, 5, '14:00:00', '19:00:00', 8336, 5, 4, 'Ivy - afternoon buyer'),
-- Cashiers
(@tplId, 5, '10:00:00', '17:00:00', 8338, 6, 5, 'Sue - lead cashier'),
(@tplId, 5, '13:00:00', '19:00:00', 8339, 6, 5, 'Claire - afternoon cashier'),
(@tplId, 5, '15:00:00', '19:00:00', 8340, 6, 5, 'Bill - evening cashier'),

-- ============================================================
-- SATURDAY (dayOfWeek=6) - Store: 10am-6pm (busiest)
-- ============================================================
-- Leadership
(@tplId, 6, '09:30:00', '18:30:00', 8326, 4, 3, 'Holly - opens and closes weekends'),
(@tplId, 6, '10:00:00', '18:00:00', 8328, 4, 3, 'Paige - mid shift lead'),
(@tplId, 6, '12:00:00', '18:30:00', 8327, 4, 3, 'Will - PM shift lead, closer'),
(@tplId, 6, '09:30:00', '15:00:00', 8325, 4, 3, 'Joy - AM shift lead'),
(@tplId, 6, '12:00:00', '18:30:00', 8324, 4, 3, 'Kole - PM shift lead'),
-- Buyers
(@tplId, 6, '10:00:00', '18:00:00', 8332, 5, 4, 'Ruby - full day buyer'),
(@tplId, 6, '10:00:00', '18:00:00', 8333, 5, 4, 'Melody - full day buyer'),
(@tplId, 6, '10:00:00', '18:00:00', 8335, 5, 4, 'Sandy - full day buyer'),
(@tplId, 6, '10:00:00', '18:00:00', 8337, 5, 4, 'Summer - flex buyer'),
(@tplId, 6, '10:00:00', '18:00:00', 8336, 5, 4, 'Ivy - full day buyer'),
(@tplId, 6, '09:30:00', '13:00:00', 8329, 5, 4, 'Dawn - morning buyer'),
-- Cashiers
(@tplId, 6, '10:00:00', '18:00:00', 8340, 6, 5, 'Bill - full day cashier'),
(@tplId, 6, '10:00:00', '18:00:00', 8341, 6, 5, 'Carrie - full day cashier'),
(@tplId, 6, '10:00:00', '18:00:00', 8339, 6, 5, 'Claire - full day cashier'),

-- ============================================================
-- SUNDAY (dayOfWeek=0) - Store: 11am-5pm (lightest)
-- ============================================================
-- Leadership
(@tplId, 0, '10:30:00', '17:30:00', 8326, 4, 3, 'Holly - opens and closes Sunday'),
-- Buyers
(@tplId, 0, '11:00:00', '17:00:00', 8335, 5, 4, 'Sandy - buyer'),
(@tplId, 0, '11:00:00', '17:00:00', 8337, 5, 4, 'Summer - flex buyer'),
-- Cashiers
(@tplId, 0, '11:00:00', '17:00:00', 8340, 6, 5, 'Bill - cashier'),
(@tplId, 0, '11:00:00', '17:00:00', 8341, 6, 5, 'Carrie - cashier');

-- Update shift count on the template
UPDATE kiosk_pc00.scheduleTemplates
SET shiftCount = (
    SELECT COUNT(*) FROM kiosk_pc00.scheduleTemplateShifts WHERE templateId = @tplId
)
WHERE templateId = @tplId;

COMMIT;

-- ============================================================
-- VERIFICATION
-- ============================================================
SELECT '=== Template Created ===' AS '';
SELECT templateId, name, shiftCount, description FROM kiosk_pc00.scheduleTemplates WHERE templateId = @tplId;

SELECT '=== Shifts Per Day ===' AS '';
SELECT
    CASE dayOfWeek
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END AS day,
    COUNT(*) AS shifts
FROM kiosk_pc00.scheduleTemplateShifts
WHERE templateId = @tplId
GROUP BY dayOfWeek
ORDER BY FIELD(dayOfWeek, 1,2,3,4,5,6,0);

SELECT '=== Weekly Hours Per Employee ===' AS '';
SELECT
    u.displayName,
    CASE usa.role WHEN 1 THEN 'Owner' WHEN 2 THEN 'Mgr' WHEN 3 THEN 'SL' WHEN 4 THEN 'Buyer' WHEN 5 THEN 'Cashier' END AS role,
    ROUND(SUM(TIMESTAMPDIFF(MINUTE, ts.startTime, ts.endTime) / 60.0), 1) AS weeklyHrs,
    u.hoursRequested AS target,
    u.hoursMin AS min,
    u.hoursMax AS max,
    GROUP_CONCAT(
        CASE ts.dayOfWeek WHEN 0 THEN 'Su' WHEN 1 THEN 'Mo' WHEN 2 THEN 'Tu' WHEN 3 THEN 'We' WHEN 4 THEN 'Th' WHEN 5 THEN 'Fr' WHEN 6 THEN 'Sa' END
        ORDER BY FIELD(ts.dayOfWeek, 1,2,3,4,5,6,0)
    ) AS daysScheduled
FROM kiosk_pc00.scheduleTemplateShifts ts
JOIN kiosk_users.users u ON ts.employeeId = u.id
JOIN kiosk_users.userStoreAssignments usa ON u.id = usa.userId AND usa.typeNum = 'pc00'
WHERE ts.templateId = @tplId
GROUP BY u.id, u.displayName, usa.role, u.hoursRequested, u.hoursMin, u.hoursMax
ORDER BY usa.role, u.displayName;
