-- =============================================================================
-- DEMO EVENT DATA SCRIPT v2
-- Creates realistic mock data for Event Analytics demo
-- Properly simulates Comeback Cash earning → redemption flow
-- Database: kiosk_ou00
-- =============================================================================

USE kiosk_ou00;

-- =============================================================================
-- 1. CREATE THE DEMO EVENT - "Spring Clearance 2026"
-- =============================================================================

-- Clear any existing demo event data
DELETE FROM event_audit_log WHERE eventId IN (SELECT id FROM events WHERE name LIKE '%Spring Clearance 2026%');
DELETE FROM event_integrations WHERE eventId IN (SELECT id FROM events WHERE name LIKE '%Spring Clearance 2026%');
DELETE FROM events WHERE name LIKE '%Spring Clearance 2026%';

-- Insert the completed event (Jan 3-12, 2026)
INSERT INTO events (
    name, description, eventType, year, startDate, endDate,
    buildUpDays, windDownDays, status, phase, color, icon, isRecurring, createdBy
) VALUES (
    'Spring Clearance 2026',
    'Annual spring clearance event with Comeback Cash promotion, backstock rotation, and digital signage.',
    'sale',
    2026,
    '2026-01-03',
    '2026-01-12',
    7,
    3,
    'completed',
    'completed',
    '#10b981',
    'fa-tags',
    1,
    1
);

SET @event_id = LAST_INSERT_ID();

-- Add audit log entries
INSERT INTO event_audit_log (eventId, action, details, employeeId, created_at)
VALUES
    (@event_id, 'created', '{"source": "demo_script"}', 1, '2025-12-27 10:00:00'),
    (@event_id, 'activated', '{"previousStatus": "scheduled"}', 1, '2026-01-03 08:00:00'),
    (@event_id, 'completed', '{"duration_days": 10}', 1, '2026-01-12 21:00:00');

-- =============================================================================
-- 2. CREATE COMEBACK CASH EVENT
-- =============================================================================

DELETE FROM ccRedemptions WHERE coupon_id IN (SELECT id FROM ccCoupons WHERE event_id IN (SELECT id FROM ccEvents WHERE name LIKE '%Spring Clearance%'));
DELETE FROM ccCoupons WHERE event_id IN (SELECT id FROM ccEvents WHERE name LIKE '%Spring Clearance%');
DELETE FROM ccEvents WHERE name LIKE '%Spring Clearance%';

-- Tiered earning: $25-49→$5, $50-99→$10, $100+→$25
INSERT INTO ccEvents (
    name, side, status, start_date, end_date,
    earning_type, earning_tiers, min_purchase_to_earn,
    redemption_min_purchase, redemption_start_date, redemption_end_date,
    redemption_days_valid, sms_enabled, event_id, created_by
) VALUES (
    'Spring Clearance Comeback Cash',
    'sales',
    'ended',
    '2026-01-03 00:00:00',
    '2026-01-12 23:59:59',
    'tiered',
    '[{"min":25,"max":49.99,"reward":5},{"min":50,"max":99.99,"reward":10},{"min":100,"max":null,"reward":25}]',
    25.00,
    10.00,
    '2026-01-13 00:00:00',
    '2026-01-31 23:59:59',
    30,
    1,
    @event_id,
    1
);

SET @cc_event_id = LAST_INSERT_ID();

-- Link to event
INSERT INTO event_integrations (eventId, integrationType, foreignId, config, status)
VALUES (@event_id, 'comeback_cash', @cc_event_id, '{"earning_type":"tiered"}', 'completed');

-- =============================================================================
-- 3. CREATE BACKSTOCK EVENT
-- =============================================================================

DELETE FROM bsEvent_Progress WHERE eventId IN (SELECT id FROM bsEvents WHERE name LIKE '%Spring Clearance%');
DELETE FROM bsEvent_Categories WHERE eventId IN (SELECT id FROM bsEvents WHERE name LIKE '%Spring Clearance%');
DELETE FROM bsEvents WHERE name LIKE '%Spring Clearance%';

INSERT INTO bsEvents (
    name, eventType, year, startDate, endDate,
    buildUpDays, windDownDays, color, isActive
) VALUES (
    'Spring Clearance Backstock',
    'sale', 2026, '2026-01-03', '2026-01-12',
    7, 3, '#f59e0b', 0
);

SET @bs_event_id = LAST_INSERT_ID();

INSERT INTO bsEvent_Progress (eventId, phase, binsPulled, binsOnFloor, binsStored)
VALUES (@bs_event_id, 'completed', 47, 0, 12);

INSERT INTO event_integrations (eventId, integrationType, foreignId, config, status)
VALUES (@event_id, 'backstock', @bs_event_id, '{}', 'completed');

-- =============================================================================
-- 4. INSERT BASELINE SALES DATA (Dec 24 - Jan 2) - Lower volume with realistic patterns
-- Holiday week patterns: Christmas slow, weekends higher, NYE/NY Day slow
-- =============================================================================

-- Delete any existing demo sales data
DELETE FROM dailySalesData WHERE date BETWEEN '2025-12-24' AND '2026-01-20' AND salesAmount > 0;

-- Initialize sequence counter
SET @bl_seq = 0;

-- Dec 24 (Wed) - Pre-Christmas last minute shoppers - 18 transactions
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    CONCAT('BL', LPAD(@bl_seq := @bl_seq + 1, 5, '0')),
    1, '2025-12-24',
    CONCAT('555', LPAD(FLOOR(RAND(@bl_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @bl_seq), 'Baseline',
    ROUND(25 + RAND(@bl_seq+1) * 65, 2), 0,
    ROUND((25 + RAND(@bl_seq+1) * 65) * 0.08, 2), 0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 18;

-- Dec 25 (Thu) - Christmas Day - CLOSED (0 transactions)

-- Dec 26 (Fri) - After Christmas / Returns day - 12 transactions, lower tickets
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    CONCAT('BL', LPAD(@bl_seq := @bl_seq + 1, 5, '0')),
    1, '2025-12-26',
    CONCAT('555', LPAD(FLOOR(RAND(@bl_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @bl_seq), 'Baseline',
    ROUND(15 + RAND(@bl_seq+2) * 50, 2), 0,
    ROUND((15 + RAND(@bl_seq+2) * 50) * 0.08, 2), 0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 12;

-- Dec 27 (Sat) - Weekend after Christmas - 20 transactions
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    CONCAT('BL', LPAD(@bl_seq := @bl_seq + 1, 5, '0')),
    1, '2025-12-27',
    CONCAT('555', LPAD(FLOOR(RAND(@bl_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @bl_seq), 'Baseline',
    ROUND(20 + RAND(@bl_seq+3) * 60, 2), 0,
    ROUND((20 + RAND(@bl_seq+3) * 60) * 0.08, 2), 0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 20;

-- Dec 28 (Sun) - Weekend - 18 transactions
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    CONCAT('BL', LPAD(@bl_seq := @bl_seq + 1, 5, '0')),
    1, '2025-12-28',
    CONCAT('555', LPAD(FLOOR(RAND(@bl_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @bl_seq), 'Baseline',
    ROUND(20 + RAND(@bl_seq+4) * 55, 2), 0,
    ROUND((20 + RAND(@bl_seq+4) * 55) * 0.08, 2), 0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 18;

-- Dec 29 (Mon) - Weekday - 14 transactions
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    CONCAT('BL', LPAD(@bl_seq := @bl_seq + 1, 5, '0')),
    1, '2025-12-29',
    CONCAT('555', LPAD(FLOOR(RAND(@bl_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @bl_seq), 'Baseline',
    ROUND(18 + RAND(@bl_seq+5) * 55, 2), 0,
    ROUND((18 + RAND(@bl_seq+5) * 55) * 0.08, 2), 0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 14;

-- Dec 30 (Tue) - Weekday - 15 transactions
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    CONCAT('BL', LPAD(@bl_seq := @bl_seq + 1, 5, '0')),
    1, '2025-12-30',
    CONCAT('555', LPAD(FLOOR(RAND(@bl_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @bl_seq), 'Baseline',
    ROUND(18 + RAND(@bl_seq+6) * 55, 2), 0,
    ROUND((18 + RAND(@bl_seq+6) * 55) * 0.08, 2), 0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 15;

-- Dec 31 (Wed) - NYE slow day - 8 transactions
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    CONCAT('BL', LPAD(@bl_seq := @bl_seq + 1, 5, '0')),
    1, '2025-12-31',
    CONCAT('555', LPAD(FLOOR(RAND(@bl_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @bl_seq), 'Baseline',
    ROUND(15 + RAND(@bl_seq+7) * 45, 2), 0,
    ROUND((15 + RAND(@bl_seq+7) * 45) * 0.08, 2), 0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) t1
LIMIT 8;

-- Jan 1 (Thu) - New Years Day - very slow - 5 transactions
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    CONCAT('BL', LPAD(@bl_seq := @bl_seq + 1, 5, '0')),
    1, '2026-01-01',
    CONCAT('555', LPAD(FLOOR(RAND(@bl_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @bl_seq), 'Baseline',
    ROUND(15 + RAND(@bl_seq+8) * 40, 2), 0,
    ROUND((15 + RAND(@bl_seq+8) * 40) * 0.08, 2), 0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1
LIMIT 5;

-- Jan 2 (Fri) - Back to work - 10 transactions
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    CONCAT('BL', LPAD(@bl_seq := @bl_seq + 1, 5, '0')),
    1, '2026-01-02',
    CONCAT('555', LPAD(FLOOR(RAND(@bl_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @bl_seq), 'Baseline',
    ROUND(18 + RAND(@bl_seq+9) * 50, 2), 0,
    ROUND((18 + RAND(@bl_seq+9) * 50) * 0.08, 2), 0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1
LIMIT 10;

-- Total baseline: ~120 transactions across 9 active days (closed Christmas)

-- =============================================================================
-- 5. INSERT EVENT PERIOD SALES (Jan 3-12) - Higher volume, qualifying purchases
-- These sales EARN comeback cash coupons
-- =============================================================================

-- Create temp table to track which sales earn coupons
DROP TEMPORARY TABLE IF EXISTS temp_earning_sales;
CREATE TEMPORARY TABLE temp_earning_sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transID VARCHAR(10),
    date DATE,
    phoneNum VARCHAR(15),
    firstName VARCHAR(60),
    salesAmount DECIMAL(10,2),
    couponValue DECIMAL(10,2)
);

-- Create day weights for realistic retail patterns
-- Jan 3 (Sat) = Opening day rush, Jan 4 (Sun) = Weekend, Jan 5-9 = Weekdays with variation
-- Jan 10 (Sat) = Weekend, Jan 11 (Sun) = Weekend, Jan 12 (Mon) = Last day urgency
DROP TEMPORARY TABLE IF EXISTS temp_day_weights;
CREATE TEMPORARY TABLE temp_day_weights (
    day_offset INT,
    day_name VARCHAR(10),
    tx_count INT,
    avg_ticket_multiplier DECIMAL(3,2)
);
INSERT INTO temp_day_weights VALUES
    (0, 'Sat', 32, 1.15),   -- Jan 3: Opening day - high traffic, higher tickets
    (1, 'Sun', 28, 1.10),   -- Jan 4: Weekend - strong
    (2, 'Mon', 16, 0.90),   -- Jan 5: Weekday - slower
    (3, 'Tue', 14, 0.85),   -- Jan 6: Weekday - slowest
    (4, 'Wed', 18, 0.95),   -- Jan 7: Midweek pickup
    (5, 'Thu', 20, 1.00),   -- Jan 8: Building to weekend
    (6, 'Fri', 24, 1.05),   -- Jan 9: Weekend starts
    (7, 'Sat', 30, 1.20),   -- Jan 10: Weekend - second peak
    (8, 'Sun', 26, 1.10),   -- Jan 11: Weekend
    (9, 'Mon', 28, 1.25);   -- Jan 12: Last day - urgency buying
-- Total: ~236 transactions across 10 days (avg 23.6/day)

-- Generate event sales with realistic daily variation
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    DATE_ADD('2026-01-03', INTERVAL dw.day_offset DAY),
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq+100) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    sale_amount,
    CASE
        WHEN sale_amount >= 100 THEN 25.00
        WHEN sale_amount >= 50 THEN 10.00
        WHEN sale_amount >= 25 THEN 5.00
        ELSE 0
    END as couponValue
FROM temp_day_weights dw
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
    UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
    UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15
    UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
    UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25
    UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30
    UNION SELECT 31 UNION SELECT 32
) nums
CROSS JOIN (SELECT @ev_seq := 0) init
CROSS JOIN (
    SELECT
        ROUND(25 + RAND() * 95 * dw2.avg_ticket_multiplier, 2) as sale_amount,
        dw2.day_offset as day_off
    FROM temp_day_weights dw2
    CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
                UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
                UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15
                UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
                UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25
                UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30
                UNION SELECT 31 UNION SELECT 32) n2
    LIMIT 400
) sale_gen
WHERE nums.n <= dw.tx_count
AND sale_gen.day_off = dw.day_offset
GROUP BY dw.day_offset, nums.n
LIMIT 250;

-- Simpler approach: Insert day by day with explicit counts
DELETE FROM temp_earning_sales;
SET @ev_seq = 0;

-- Day 1: Jan 3 (Sat) - Opening day rush - 32 transactions
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-03',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(30 + RAND(@ev_seq+1) * 110, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) t1,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2
LIMIT 32;

-- Day 2: Jan 4 (Sun) - Weekend strong - 28 transactions
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-04',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(28 + RAND(@ev_seq+2) * 100, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) t1,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2
LIMIT 28;

-- Day 3: Jan 5 (Mon) - Slow weekday - 16 transactions
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-05',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(25 + RAND(@ev_seq+3) * 85, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 16;

-- Day 4: Jan 6 (Tue) - Slowest weekday - 14 transactions
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-06',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(25 + RAND(@ev_seq+4) * 80, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 14;

-- Day 5: Jan 7 (Wed) - Midweek pickup - 18 transactions
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-07',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(25 + RAND(@ev_seq+5) * 90, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 18;

-- Day 6: Jan 8 (Thu) - Building to weekend - 20 transactions
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-08',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(25 + RAND(@ev_seq+6) * 95, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 20;

-- Day 7: Jan 9 (Fri) - Weekend starts - 24 transactions
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-09',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(28 + RAND(@ev_seq+7) * 100, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) t1,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3) t2
LIMIT 24;

-- Day 8: Jan 10 (Sat) - Weekend peak - 30 transactions
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-10',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(35 + RAND(@ev_seq+8) * 115, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3) t2
LIMIT 30;

-- Day 9: Jan 11 (Sun) - Weekend - 26 transactions
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-11',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(30 + RAND(@ev_seq+9) * 105, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13) t1,
     (SELECT 1 UNION SELECT 2) t2
LIMIT 26;

-- Day 10: Jan 12 (Mon) - Last day urgency - 28 transactions with higher tickets
INSERT INTO temp_earning_sales (transID, date, phoneNum, firstName, salesAmount, couponValue)
SELECT
    CONCAT('EV', LPAD(@ev_seq := @ev_seq + 1, 5, '0')),
    '2026-01-12',
    CONCAT('555', LPAD(FLOOR(RAND(@ev_seq) * 9000000) + 1000000, 7, '0')),
    CONCAT('Customer', @ev_seq + 1000),
    ROUND(40 + RAND(@ev_seq+10) * 120, 2),
    0
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) t1,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2
LIMIT 28;

-- Update coupon values based on sales amounts
UPDATE temp_earning_sales SET couponValue = CASE
    WHEN salesAmount >= 100 THEN 25.00
    WHEN salesAmount >= 50 THEN 10.00
    WHEN salesAmount >= 25 THEN 5.00
    ELSE 0
END;

-- Insert into dailySalesData
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    transID, 1, date, phoneNum, firstName, 'Event',
    salesAmount, 0, ROUND(salesAmount * 0.08, 2), 0
FROM temp_earning_sales;

-- =============================================================================
-- 6. CREATE COUPONS FOR QUALIFYING PURCHASES (sales >= $25)
-- =============================================================================

INSERT INTO ccCoupons (
    event_id, code, value, original_value,
    source_transaction_id, source_transaction_amount,
    customer_phone, customer_name, status, expires_at, issued_at
)
SELECT
    @cc_event_id,
    UPPER(CONCAT(
        SUBSTRING(MD5(CONCAT(id, transID, NOW())), 1, 4),
        SUBSTRING(MD5(CONCAT(transID, id, RAND())), 1, 4)
    )) as code,
    couponValue,
    couponValue,
    transID,
    salesAmount,
    phoneNum,
    firstName,
    'active',
    '2026-01-31 23:59:59',
    DATE_ADD(date, INTERVAL 12 HOUR)
FROM temp_earning_sales
WHERE couponValue > 0;

-- =============================================================================
-- 7. SIMULATE REDEMPTIONS (22% rate) - Customers return and make new purchases
-- =============================================================================

-- Select ~22% of coupons to redeem
SET @coupons_to_redeem = (SELECT FLOOR(COUNT(*) * 0.22) FROM ccCoupons WHERE event_id = @cc_event_id);

-- Create redemption transactions (sales during redemption window Jan 13-20)
DROP TEMPORARY TABLE IF EXISTS temp_redemption_sales;
CREATE TEMPORARY TABLE temp_redemption_sales AS
SELECT
    c.id as coupon_id,
    c.value as coupon_value,
    c.customer_phone,
    c.customer_name,
    CONCAT('RD', LPAD(c.id, 5, '0')) as transID,
    DATE_ADD('2026-01-13', INTERVAL FLOOR(RAND(c.id) * 7) DAY) as redeem_date,
    ROUND(30 + RAND(c.id+10) * 80, 2) as transaction_amount  -- $30-110 purchase when redeeming
FROM ccCoupons c
WHERE c.event_id = @cc_event_id
AND c.status = 'active'
ORDER BY RAND()
LIMIT 20;  -- ~22% of coupons

-- Insert the redemption purchases into dailySalesData
INSERT INTO dailySalesData (transID, type, date, phoneNum, firstName, lastName, salesAmount, buyAmount, taxAmount, returnAmount)
SELECT
    transID, 1, redeem_date, customer_phone, customer_name, 'Redemption',
    transaction_amount, 0, ROUND(transaction_amount * 0.08, 2), 0
FROM temp_redemption_sales;

-- Record the redemptions
INSERT INTO ccRedemptions (coupon_id, redeemed_amount, transaction_id, transaction_amount, redeemed_by_employee_id, redeemed_at, redemption_method)
SELECT
    coupon_id,
    coupon_value,
    transID,
    transaction_amount,
    1,
    DATE_ADD(redeem_date, INTERVAL 14 HOUR),
    ELT(1 + FLOOR(RAND(coupon_id) * 3), 'scan', 'manual', 'pos')
FROM temp_redemption_sales;

-- Mark coupons as redeemed
UPDATE ccCoupons c
INNER JOIN temp_redemption_sales r ON c.id = r.coupon_id
SET c.status = 'redeemed', c.value = 0;

-- Clean up
DROP TEMPORARY TABLE IF EXISTS temp_earning_sales;
DROP TEMPORARY TABLE IF EXISTS temp_redemption_sales;

-- =============================================================================
-- 8. ADD SIGNAGE INTEGRATION
-- =============================================================================

INSERT INTO event_integrations (eventId, integrationType, foreignId, config, status)
VALUES (@event_id, 'signage', 1, '{"slides":3}', 'completed');

-- =============================================================================
-- 9. SUMMARY OUTPUT
-- =============================================================================

SELECT '✅ Demo data created!' as status;
SELECT @event_id as event_id, 'Spring Clearance 2026' as event_name;

SELECT '📊 SALES METRICS' as section;
SELECT
    'Event Period (Jan 3-12)' as period,
    COUNT(*) as transactions,
    CONCAT('$', FORMAT(SUM(salesAmount), 2)) as revenue,
    CONCAT('$', FORMAT(AVG(salesAmount), 2)) as avg_ticket
FROM dailySalesData
WHERE date BETWEEN '2026-01-03' AND '2026-01-12' AND salesAmount > 0
UNION ALL
SELECT
    'Baseline (Dec 24-Jan 2)',
    COUNT(*),
    CONCAT('$', FORMAT(SUM(salesAmount), 2)),
    CONCAT('$', FORMAT(AVG(salesAmount), 2))
FROM dailySalesData
WHERE date BETWEEN '2025-12-24' AND '2026-01-02' AND salesAmount > 0;

SELECT '💵 COMEBACK CASH METRICS' as section;
SELECT
    COUNT(*) as coupons_issued,
    CONCAT('$', FORMAT(SUM(original_value), 2)) as total_value_issued,
    SUM(CASE WHEN status = 'redeemed' THEN 1 ELSE 0 END) as redeemed,
    CONCAT(ROUND(SUM(CASE WHEN status = 'redeemed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1), '%') as redemption_rate
FROM ccCoupons WHERE event_id = @cc_event_id;

SELECT '💰 REDEMPTION REVENUE' as section;
SELECT
    COUNT(*) as redemption_transactions,
    CONCAT('$', FORMAT(SUM(transaction_amount), 2)) as revenue_from_redemptions,
    CONCAT('$', FORMAT(AVG(transaction_amount), 2)) as avg_redemption_ticket
FROM ccRedemptions WHERE coupon_id IN (SELECT id FROM ccCoupons WHERE event_id = @cc_event_id);
