-- =============================================================================
-- DEMO EVENT DATA SCRIPT
-- Creates realistic mock data for Event Analytics demo
-- Database: kiosk_ou00
-- =============================================================================

USE kiosk_ou00;

-- =============================================================================
-- 1. CREATE BUYS TABLE (for sales transactions - expected by EventReportService)
-- =============================================================================

CREATE TABLE IF NOT EXISTS buys (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    total DECIMAL(10,2) NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'completed',
    type VARCHAR(20) NOT NULL DEFAULT 'sale',
    amount_paid DECIMAL(10,2) NULL,
    customer_id INT UNSIGNED NULL,
    employee_id INT UNSIGNED NULL,
    INDEX idx_created_at (created_at),
    INDEX idx_status (status),
    INDEX idx_type (type)
);

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

-- Clear any existing demo event
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 - recently completed)
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 campaign.',
    '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 entry
INSERT INTO event_audit_log (eventId, action, details, employeeId)
VALUES (@event_id, 'created', '{"source": "demo_script"}', 1);

INSERT INTO event_audit_log (eventId, action, details, employeeId)
VALUES (@event_id, 'activated', '{"previousStatus": "scheduled"}', 1);

INSERT INTO event_audit_log (eventId, action, details, employeeId)
VALUES (@event_id, 'completed', '{"duration_days": 10}', 1);

-- =============================================================================
-- 3. CREATE COMEBACK CASH EVENT & LINK TO MAIN EVENT
-- =============================================================================

-- Clear existing demo CC data
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%';

-- Create Comeback Cash event (sales-side, tiered earning)
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, allow_double_up, 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}]',
    15.00,
    10.00,
    '2026-01-13 00:00:00',
    '2026-01-31 23:59:59',
    30,
    0,
    1,
    @event_id,
    1
);

SET @cc_event_id = LAST_INSERT_ID();

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

-- =============================================================================
-- 4. CREATE BACKSTOCK EVENT & LINK
-- =============================================================================

-- Clear existing demo backstock data
DELETE FROM bsEvent_Progress WHERE eventId IN (SELECT id FROM bsEvents WHERE name LIKE '%Spring Clearance%');
DELETE FROM bsEvent_Alerts 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%';

-- Create backstock event
INSERT INTO bsEvents (
    name, eventType, year, startDate, endDate,
    buildUpDays, windDownDays, color, icon, notes, isActive, isRecurring
) VALUES (
    'Spring Clearance Backstock',
    'sale',
    2026,
    '2026-01-03',
    '2026-01-12',
    7,
    3,
    '#f59e0b',
    'fa-boxes',
    'Rotate winter clearance items from backstock',
    0,
    0
);

SET @bs_event_id = LAST_INSERT_ID();

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

-- Link backstock to event
INSERT INTO event_integrations (eventId, integrationType, foreignId, config, status)
VALUES (@event_id, 'backstock', @bs_event_id, '{"categories": ["Winter Coats", "Sweaters", "Boots"]}', 'completed');

-- =============================================================================
-- 5. GENERATE SALES DATA (buys table)
-- =============================================================================

-- Clear existing demo sales
DELETE FROM buys WHERE created_at BETWEEN '2025-12-15' AND '2026-01-20';

-- BASELINE PERIOD: Dec 24, 2025 - Jan 2, 2026 (10 days before event)
-- Lower sales volume to show positive lift

-- Generate ~180 baseline transactions (avg 18/day)
INSERT INTO buys (total, quantity, created_at, status, type, amount_paid)
SELECT
    ROUND(15 + RAND() * 85, 2) as total,
    1 + FLOOR(RAND() * 3) as quantity,
    DATE_ADD('2025-12-24', INTERVAL FLOOR(RAND() * 10) DAY) + INTERVAL FLOOR(RAND() * 12 + 9) HOUR + INTERVAL FLOOR(RAND() * 60) MINUTE as created_at,
    'completed',
    'sale',
    NULL
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 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
) t2
CROSS JOIN (
    SELECT 1 UNION SELECT 2
) t3
LIMIT 180;

-- EVENT PERIOD: Jan 3-12, 2026 (10 days)
-- Higher sales volume to show ~25% lift

-- Generate ~270 event transactions (avg 27/day, ~50% more than baseline)
INSERT INTO buys (total, quantity, created_at, status, type, amount_paid)
SELECT
    ROUND(20 + RAND() * 100, 2) as total,  -- Slightly higher avg ticket too
    1 + FLOOR(RAND() * 4) as quantity,
    DATE_ADD('2026-01-03', INTERVAL FLOOR(RAND() * 10) DAY) + INTERVAL FLOOR(RAND() * 12 + 9) HOUR + INTERVAL FLOOR(RAND() * 60) MINUTE as created_at,
    'completed',
    'sale',
    NULL
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 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
) t2
CROSS JOIN (
    SELECT 1 UNION SELECT 2 UNION SELECT 3
) t3
LIMIT 270;

-- Add some BUY transactions (purchasing from customers) for inventory metrics
INSERT INTO buys (total, quantity, created_at, status, type, amount_paid)
SELECT
    ROUND(30 + RAND() * 120, 2) as total,
    2 + FLOOR(RAND() * 8) as quantity,
    DATE_ADD('2026-01-03', INTERVAL FLOOR(RAND() * 10) DAY) + INTERVAL FLOOR(RAND() * 10 + 10) HOUR + INTERVAL FLOOR(RAND() * 60) MINUTE as created_at,
    'completed',
    'buy',
    ROUND(30 + RAND() * 120, 2)
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 CROSS JOIN (
    SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
) t2
LIMIT 45;

-- =============================================================================
-- 6. GENERATE COMEBACK CASH COUPONS (issued during event)
-- =============================================================================

-- Create 85 coupons with varying values based on tiered earning
-- ~22% redemption rate (industry avg is 15-25%)

-- Helper: Generate unique coupon codes
DROP PROCEDURE IF EXISTS generate_cc_coupons;
DELIMITER //
CREATE PROCEDURE generate_cc_coupons()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE coupon_value DECIMAL(10,2);
    DECLARE coupon_code VARCHAR(12);
    DECLARE tx_amount DECIMAL(10,2);
    DECLARE issue_date DATETIME;
    DECLARE cust_phone VARCHAR(20);

    WHILE i < 85 DO
        -- Generate random transaction amount to determine tier
        SET tx_amount = 25 + RAND() * 150;

        -- Determine coupon value based on tier
        IF tx_amount >= 100 THEN
            SET coupon_value = 25.00;
        ELSEIF tx_amount >= 50 THEN
            SET coupon_value = 10.00;
        ELSE
            SET coupon_value = 5.00;
        END IF;

        -- Generate unique code
        SET coupon_code = CONCAT(
            CHAR(65 + FLOOR(RAND() * 26)),
            FLOOR(RAND() * 10),
            CHAR(65 + FLOOR(RAND() * 26)),
            FLOOR(RAND() * 10),
            CHAR(65 + FLOOR(RAND() * 26)),
            FLOOR(RAND() * 10),
            CHAR(65 + FLOOR(RAND() * 26)),
            FLOOR(RAND() * 10)
        );

        -- Random issue date during event
        SET issue_date = DATE_ADD('2026-01-03', INTERVAL FLOOR(RAND() * 10) DAY);
        SET issue_date = DATE_ADD(issue_date, INTERVAL FLOOR(RAND() * 10 + 10) HOUR);

        -- Random phone number
        SET cust_phone = CONCAT('555', LPAD(FLOOR(RAND() * 10000000), 7, '0'));

        INSERT IGNORE INTO ccCoupons (
            event_id, code, value, original_value,
            source_transaction_id, source_transaction_amount,
            customer_phone, customer_name, status, expires_at, issued_at
        ) VALUES (
            @cc_event_id,
            coupon_code,
            coupon_value,
            coupon_value,
            CONCAT('TXN-', LPAD(i + 1000, 6, '0')),
            ROUND(tx_amount, 2),
            cust_phone,
            CONCAT('Customer ', i + 1),
            'active',
            '2026-01-31 23:59:59',
            issue_date
        );

        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL generate_cc_coupons();
DROP PROCEDURE generate_cc_coupons;

-- =============================================================================
-- 7. GENERATE REDEMPTIONS (~22% redemption rate = ~19 redemptions)
-- =============================================================================

-- Mark some coupons as redeemed and create redemption records
DROP PROCEDURE IF EXISTS generate_cc_redemptions;
DELIMITER //
CREATE PROCEDURE generate_cc_redemptions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_coupon_id INT;
    DECLARE v_coupon_value DECIMAL(10,2);
    DECLARE v_redeem_date DATETIME;
    DECLARE v_tx_amount DECIMAL(10,2);
    DECLARE counter INT DEFAULT 0;

    DECLARE cur CURSOR FOR
        SELECT id, value FROM ccCoupons
        WHERE event_id = @cc_event_id AND status = 'active'
        ORDER BY RAND()
        LIMIT 19;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_coupon_id, v_coupon_value;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Random redemption date (during redemption window: Jan 13-20)
        SET v_redeem_date = DATE_ADD('2026-01-13', INTERVAL FLOOR(RAND() * 7) DAY);
        SET v_redeem_date = DATE_ADD(v_redeem_date, INTERVAL FLOOR(RAND() * 10 + 10) HOUR);

        -- Transaction amount (must meet $10 minimum, typically higher)
        SET v_tx_amount = 25 + RAND() * 100;

        -- Create redemption record
        INSERT INTO ccRedemptions (
            coupon_id, redeemed_amount, transaction_id,
            transaction_amount, redeemed_by_employee_id, redeemed_at, redemption_method
        ) VALUES (
            v_coupon_id,
            v_coupon_value,
            CONCAT('REDEEM-', LPAD(counter + 2000, 6, '0')),
            ROUND(v_tx_amount, 2),
            1,
            v_redeem_date,
            ELT(1 + FLOOR(RAND() * 3), 'scan', 'manual', 'pos')
        );

        -- Update coupon status
        UPDATE ccCoupons SET status = 'redeemed', value = 0 WHERE id = v_coupon_id;

        SET counter = counter + 1;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

CALL generate_cc_redemptions();
DROP PROCEDURE generate_cc_redemptions;

-- =============================================================================
-- 8. ADD SIGNAGE INTEGRATION (mock - no actual signage data needed for metrics)
-- =============================================================================

INSERT INTO event_integrations (eventId, integrationType, foreignId, config, status)
VALUES (@event_id, 'signage', 1, '{"slides": ["Spring Sale Banner", "Comeback Cash Promo", "Clearance Deals"]}', 'completed');

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

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

SELECT
    (SELECT COUNT(*) FROM buys WHERE created_at BETWEEN '2026-01-03' AND '2026-01-12 23:59:59' AND type = 'sale') as event_sales,
    (SELECT COUNT(*) FROM buys WHERE created_at BETWEEN '2025-12-24' AND '2026-01-02 23:59:59' AND type = 'sale') as baseline_sales,
    (SELECT COUNT(*) FROM ccCoupons WHERE event_id = @cc_event_id) as coupons_issued,
    (SELECT COUNT(*) FROM ccCoupons WHERE event_id = @cc_event_id AND status = 'redeemed') as coupons_redeemed,
    (SELECT ROUND(COUNT(CASE WHEN status = 'redeemed' THEN 1 END) * 100.0 / COUNT(*), 1) FROM ccCoupons WHERE event_id = @cc_event_id) as redemption_rate;
