-- =============================================================
-- Dummy seed data for Analytics & Revenue admin page
-- =============================================================

-- ---------------------------------------------------------------
-- 1. Dummy users (skip if email already exists)
-- ---------------------------------------------------------------
INSERT IGNORE INTO users
  (full_name, first_name, last_name, email, password, email_verified,
   subscription_plan, subscription_status, stripe_customer_id,
   stripe_subscription_id, current_period_end, created_at, updated_at)
VALUES
  ('Alice Johnson',  'Alice',  'Johnson',  'alice.johnson@example.com',  '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi', TRUE, 'premium_plus',  'active',   'cus_dummy001', 'sub_dummy001', '2026-04-25 00:00:00', '2025-10-01 09:00:00', NOW()),
  ('Bob Martinez',   'Bob',    'Martinez', 'bob.martinez@example.com',   '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi', TRUE, 'premium',       'active',   'cus_dummy002', 'sub_dummy002', '2026-04-25 00:00:00', '2025-10-15 11:30:00', NOW()),
  ('Carol Williams', 'Carol',  'Williams', 'carol.williams@example.com', '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi', TRUE, 'standard_plus', 'active',   'cus_dummy003', 'sub_dummy003', '2026-04-25 00:00:00', '2025-11-05 14:00:00', NOW()),
  ('David Lee',      'David',  'Lee',      'david.lee@example.com',      '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi', TRUE, 'standard',      'active',   'cus_dummy004', 'sub_dummy004', '2026-04-25 00:00:00', '2025-11-20 08:45:00', NOW()),
  ('Emma Davis',     'Emma',   'Davis',    'emma.davis@example.com',     '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi', TRUE, 'premium',       'trialing', 'cus_dummy005', 'sub_dummy005', '2026-04-25 00:00:00', '2025-12-10 16:20:00', NOW()),
  ('Frank Brown',    'Frank',  'Brown',    'frank.brown@example.com',    '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi', TRUE, 'standard_plus', 'active',   'cus_dummy006', 'sub_dummy006', '2026-04-25 00:00:00', '2026-01-03 10:10:00', NOW());

-- ---------------------------------------------------------------
-- 2. Payment history using subqueries to look up user IDs by email
-- ---------------------------------------------------------------

-- Alice — premium_plus ($29.99) — Oct 2025 to Mar 2026
INSERT INTO payment_history (user_id, stripe_invoice_id, stripe_subscription_id, stripe_payment_intent_id, plan_key, amount_cents, currency, status, payment_date, billing_period_start, billing_period_end, created_at)
VALUES
  ((SELECT id FROM users WHERE email = 'alice.johnson@example.com'), 'in_alice_oct', 'sub_dummy001', 'pi_alice_oct', 'premium_plus', 2999, 'usd', 'succeeded', '2025-10-01 09:05:00', '2025-10-01 00:00:00', '2025-10-31 23:59:59', '2025-10-01 09:05:00'),
  ((SELECT id FROM users WHERE email = 'alice.johnson@example.com'), 'in_alice_nov', 'sub_dummy001', 'pi_alice_nov', 'premium_plus', 2999, 'usd', 'succeeded', '2025-11-01 09:05:00', '2025-11-01 00:00:00', '2025-11-30 23:59:59', '2025-11-01 09:05:00'),
  ((SELECT id FROM users WHERE email = 'alice.johnson@example.com'), 'in_alice_dec', 'sub_dummy001', 'pi_alice_dec', 'premium_plus', 2999, 'usd', 'succeeded', '2025-12-01 09:05:00', '2025-12-01 00:00:00', '2025-12-31 23:59:59', '2025-12-01 09:05:00'),
  ((SELECT id FROM users WHERE email = 'alice.johnson@example.com'), 'in_alice_jan', 'sub_dummy001', 'pi_alice_jan', 'premium_plus', 2999, 'usd', 'succeeded', '2026-01-01 09:05:00', '2026-01-01 00:00:00', '2026-01-31 23:59:59', '2026-01-01 09:05:00'),
  ((SELECT id FROM users WHERE email = 'alice.johnson@example.com'), 'in_alice_feb', 'sub_dummy001', 'pi_alice_feb', 'premium_plus', 2999, 'usd', 'succeeded', '2026-02-01 09:05:00', '2026-02-01 00:00:00', '2026-02-28 23:59:59', '2026-02-01 09:05:00'),
  ((SELECT id FROM users WHERE email = 'alice.johnson@example.com'), 'in_alice_mar', 'sub_dummy001', 'pi_alice_mar', 'premium_plus', 2999, 'usd', 'succeeded', '2026-03-01 09:05:00', '2026-03-01 00:00:00', '2026-03-31 23:59:59', '2026-03-01 09:05:00');

-- Bob — premium ($19.99) — Oct 2025 to Mar 2026
INSERT INTO payment_history (user_id, stripe_invoice_id, stripe_subscription_id, stripe_payment_intent_id, plan_key, amount_cents, currency, status, payment_date, billing_period_start, billing_period_end, created_at)
VALUES
  ((SELECT id FROM users WHERE email = 'bob.martinez@example.com'), 'in_bob_oct', 'sub_dummy002', 'pi_bob_oct', 'premium', 1999, 'usd', 'succeeded', '2025-10-15 11:35:00', '2025-10-15 00:00:00', '2025-11-14 23:59:59', '2025-10-15 11:35:00'),
  ((SELECT id FROM users WHERE email = 'bob.martinez@example.com'), 'in_bob_nov', 'sub_dummy002', 'pi_bob_nov', 'premium', 1999, 'usd', 'succeeded', '2025-11-15 11:35:00', '2025-11-15 00:00:00', '2025-12-14 23:59:59', '2025-11-15 11:35:00'),
  ((SELECT id FROM users WHERE email = 'bob.martinez@example.com'), 'in_bob_dec', 'sub_dummy002', 'pi_bob_dec', 'premium', 1999, 'usd', 'succeeded', '2025-12-15 11:35:00', '2025-12-15 00:00:00', '2026-01-14 23:59:59', '2025-12-15 11:35:00'),
  ((SELECT id FROM users WHERE email = 'bob.martinez@example.com'), 'in_bob_jan', 'sub_dummy002', 'pi_bob_jan', 'premium', 1999, 'usd', 'succeeded', '2026-01-15 11:35:00', '2026-01-15 00:00:00', '2026-02-14 23:59:59', '2026-01-15 11:35:00'),
  ((SELECT id FROM users WHERE email = 'bob.martinez@example.com'), 'in_bob_feb', 'sub_dummy002', 'pi_bob_feb', 'premium', 1999, 'usd', 'succeeded', '2026-02-15 11:35:00', '2026-02-15 00:00:00', '2026-03-14 23:59:59', '2026-02-15 11:35:00'),
  ((SELECT id FROM users WHERE email = 'bob.martinez@example.com'), 'in_bob_mar', 'sub_dummy002', 'pi_bob_mar', 'premium', 1999, 'usd', 'succeeded', '2026-03-15 11:35:00', '2026-03-15 00:00:00', '2026-04-14 23:59:59', '2026-03-15 11:35:00');

-- Carol — standard_plus ($14.99) — Nov 2025 to Mar 2026
INSERT INTO payment_history (user_id, stripe_invoice_id, stripe_subscription_id, stripe_payment_intent_id, plan_key, amount_cents, currency, status, payment_date, billing_period_start, billing_period_end, created_at)
VALUES
  ((SELECT id FROM users WHERE email = 'carol.williams@example.com'), 'in_carol_nov', 'sub_dummy003', 'pi_carol_nov', 'standard_plus', 1499, 'usd', 'succeeded', '2025-11-05 14:05:00', '2025-11-05 00:00:00', '2025-12-04 23:59:59', '2025-11-05 14:05:00'),
  ((SELECT id FROM users WHERE email = 'carol.williams@example.com'), 'in_carol_dec', 'sub_dummy003', 'pi_carol_dec', 'standard_plus', 1499, 'usd', 'succeeded', '2025-12-05 14:05:00', '2025-12-05 00:00:00', '2026-01-04 23:59:59', '2025-12-05 14:05:00'),
  ((SELECT id FROM users WHERE email = 'carol.williams@example.com'), 'in_carol_jan', 'sub_dummy003', 'pi_carol_jan', 'standard_plus', 1499, 'usd', 'succeeded', '2026-01-05 14:05:00', '2026-01-05 00:00:00', '2026-02-04 23:59:59', '2026-01-05 14:05:00'),
  ((SELECT id FROM users WHERE email = 'carol.williams@example.com'), 'in_carol_feb', 'sub_dummy003', 'pi_carol_feb', 'standard_plus', 1499, 'usd', 'succeeded', '2026-02-05 14:05:00', '2026-02-05 00:00:00', '2026-03-04 23:59:59', '2026-02-05 14:05:00'),
  ((SELECT id FROM users WHERE email = 'carol.williams@example.com'), 'in_carol_mar', 'sub_dummy003', 'pi_carol_mar', 'standard_plus', 1499, 'usd', 'succeeded', '2026-03-05 14:05:00', '2026-03-05 00:00:00', '2026-04-04 23:59:59', '2026-03-05 14:05:00');

-- David — standard ($9.99) — Nov 2025 to Mar 2026
INSERT INTO payment_history (user_id, stripe_invoice_id, stripe_subscription_id, stripe_payment_intent_id, plan_key, amount_cents, currency, status, payment_date, billing_period_start, billing_period_end, created_at)
VALUES
  ((SELECT id FROM users WHERE email = 'david.lee@example.com'), 'in_david_nov', 'sub_dummy004', 'pi_david_nov', 'standard', 999, 'usd', 'succeeded', '2025-11-20 08:50:00', '2025-11-20 00:00:00', '2025-12-19 23:59:59', '2025-11-20 08:50:00'),
  ((SELECT id FROM users WHERE email = 'david.lee@example.com'), 'in_david_dec', 'sub_dummy004', 'pi_david_dec', 'standard', 999, 'usd', 'succeeded', '2025-12-20 08:50:00', '2025-12-20 00:00:00', '2026-01-19 23:59:59', '2025-12-20 08:50:00'),
  ((SELECT id FROM users WHERE email = 'david.lee@example.com'), 'in_david_jan', 'sub_dummy004', 'pi_david_jan', 'standard', 999, 'usd', 'succeeded', '2026-01-20 08:50:00', '2026-01-20 00:00:00', '2026-02-19 23:59:59', '2026-01-20 08:50:00'),
  ((SELECT id FROM users WHERE email = 'david.lee@example.com'), 'in_david_feb', 'sub_dummy004', 'pi_david_feb', 'standard', 999, 'usd', 'succeeded', '2026-02-20 08:50:00', '2026-02-20 00:00:00', '2026-03-19 23:59:59', '2026-02-20 08:50:00'),
  ((SELECT id FROM users WHERE email = 'david.lee@example.com'), 'in_david_mar', 'sub_dummy004', 'pi_david_mar', 'standard', 999, 'usd', 'succeeded', '2026-03-20 08:50:00', '2026-03-20 00:00:00', '2026-04-19 23:59:59', '2026-03-20 08:50:00');

-- Emma — premium ($19.99) — 1 failed in Dec, then succeeded Jan–Mar
INSERT INTO payment_history (user_id, stripe_invoice_id, stripe_subscription_id, stripe_payment_intent_id, plan_key, amount_cents, currency, status, payment_date, billing_period_start, billing_period_end, created_at)
VALUES
  ((SELECT id FROM users WHERE email = 'emma.davis@example.com'), 'in_emma_dec_fail', 'sub_dummy005', 'pi_emma_dec_fail', 'premium', 1999, 'usd', 'failed',    '2025-12-10 16:25:00', '2025-12-10 00:00:00', '2026-01-09 23:59:59', '2025-12-10 16:25:00'),
  ((SELECT id FROM users WHERE email = 'emma.davis@example.com'), 'in_emma_jan',      'sub_dummy005', 'pi_emma_jan',      'premium', 1999, 'usd', 'succeeded', '2026-01-10 16:25:00', '2026-01-10 00:00:00', '2026-02-09 23:59:59', '2026-01-10 16:25:00'),
  ((SELECT id FROM users WHERE email = 'emma.davis@example.com'), 'in_emma_feb',      'sub_dummy005', 'pi_emma_feb',      'premium', 1999, 'usd', 'succeeded', '2026-02-10 16:25:00', '2026-02-10 00:00:00', '2026-03-09 23:59:59', '2026-02-10 16:25:00'),
  ((SELECT id FROM users WHERE email = 'emma.davis@example.com'), 'in_emma_mar',      'sub_dummy005', 'pi_emma_mar',      'premium', 1999, 'usd', 'succeeded', '2026-03-10 16:25:00', '2026-03-10 00:00:00', '2026-04-09 23:59:59', '2026-03-10 16:25:00');

-- Frank — standard_plus ($14.99) — Jan to Mar 2026
INSERT INTO payment_history (user_id, stripe_invoice_id, stripe_subscription_id, stripe_payment_intent_id, plan_key, amount_cents, currency, status, payment_date, billing_period_start, billing_period_end, created_at)
VALUES
  ((SELECT id FROM users WHERE email = 'frank.brown@example.com'), 'in_frank_jan', 'sub_dummy006', 'pi_frank_jan', 'standard_plus', 1499, 'usd', 'succeeded', '2026-01-03 10:15:00', '2026-01-03 00:00:00', '2026-02-02 23:59:59', '2026-01-03 10:15:00'),
  ((SELECT id FROM users WHERE email = 'frank.brown@example.com'), 'in_frank_feb', 'sub_dummy006', 'pi_frank_feb', 'standard_plus', 1499, 'usd', 'succeeded', '2026-02-03 10:15:00', '2026-02-03 00:00:00', '2026-03-02 23:59:59', '2026-02-03 10:15:00'),
  ((SELECT id FROM users WHERE email = 'frank.brown@example.com'), 'in_frank_mar', 'sub_dummy006', 'pi_frank_mar', 'standard_plus', 1499, 'usd', 'succeeded', '2026-03-03 10:15:00', '2026-03-03 00:00:00', '2026-04-02 23:59:59', '2026-03-03 10:15:00');
