-- Seed data for FlashCore database
-- This script creates demo users, an admin super user, and sample data

USE flashcore_dashboard;

-- Create admin super user
INSERT INTO users (id, email, password_hash, first_name, last_name, email_verified, created_at, last_login) 
VALUES (UUID(), 'admin@flashcore.com', '$2y$10$BQqvqMS.nrML.BmIrjAW.OrguJZapK9fLbMA9vE0.H.4P5gOIojzC', 'System', 'Administrator', TRUE, NOW(), NOW());

-- Get the admin user ID
SET @admin_id = (SELECT id FROM users WHERE email = 'admin@flashcore.com');

-- Create admin profile
INSERT INTO profiles (id, user_id, first_name, last_name, role_id) 
VALUES (UUID(), @admin_id, 'System', 'Administrator', 1); -- role_id 1 = admin

-- Create admin user profile
INSERT INTO user_profiles (id, user_id, username, first_name, last_name) 
VALUES (UUID(), @admin_id, 'admin', 'System', 'Administrator');

-- Create admin user progress
INSERT INTO user_progress (id, user_id, current_rank, mails_sent, progress_percentage, referral_count, referral_earnings, total_earnings) 
VALUES (UUID(), @admin_id, 'Diamond', 1000, 100, 50, 250.00, 1000.00);

-- Create demo users
INSERT INTO users (id, email, password_hash, first_name, last_name, email_verified, created_at, last_login) 
VALUES (UUID(), 'john.doe@example.com', '$2y$10$BQqvqMS.nrML.BmIrjAW.OrguJZapK9fLbMA9vE0.H.4P5gOIojzC', 'John', 'Doe', TRUE, NOW(), NOW());

-- Get the first user ID
SET @user1_id = (SELECT id FROM users WHERE email = 'john.doe@example.com');

INSERT INTO profiles (id, user_id, first_name, last_name, role_id) 
VALUES (UUID(), @user1_id, 'John', 'Doe', 2); -- role_id 2 = user

INSERT INTO user_profiles (id, user_id, username, first_name, last_name, phone) 
VALUES (UUID(), @user1_id, 'johndoe', 'John', 'Doe', '+1234567890');

INSERT INTO user_progress (id, user_id, current_rank, mails_sent, progress_percentage, referral_count, referral_earnings, total_earnings) 
VALUES (UUID(), @user1_id, 'Gold', 750, 75, 25, 125.00, 500.00);

-- Create another demo user
INSERT INTO users (id, email, password_hash, first_name, last_name, email_verified, created_at, last_login) 
VALUES (UUID(), 'jane.smith@example.com', '$2y$10$BQqvqMS.nrML.BmIrjAW.OrguJZapK9fLbMA9vE0.H.4P5gOIojzC', 'Jane', 'Smith', TRUE, NOW(), NOW());

-- Get the second user ID
SET @user2_id = (SELECT id FROM users WHERE email = 'jane.smith@example.com');

INSERT INTO profiles (id, user_id, first_name, last_name, role_id) 
VALUES (UUID(), @user2_id, 'Jane', 'Smith', 2); -- role_id 2 = user

INSERT INTO user_profiles (id, user_id, username, first_name, last_name, phone) 
VALUES (UUID(), @user2_id, 'janesmith', 'Jane', 'Smith', '+1987654321');

INSERT INTO user_progress (id, user_id, current_rank, mails_sent, progress_percentage, referral_count, referral_earnings, total_earnings) 
VALUES (UUID(), @user2_id, 'Silver', 250, 50, 10, 50.00, 200.00);

-- Create subscribers for demo users
INSERT INTO subscribers (id, user_id, email, subscribed, subscription_tier_id, subscription_end) 
VALUES (UUID(), @user1_id, 'john.doe@example.com', TRUE, 2, DATE_ADD(NOW(), INTERVAL 1 YEAR)); -- pro tier

INSERT INTO subscribers (id, user_id, email, subscribed, subscription_tier_id, subscription_end) 
VALUES (UUID(), @user2_id, 'jane.smith@example.com', TRUE, 1, DATE_ADD(NOW(), INTERVAL 1 YEAR)); -- basic tier

-- Create sample license keys
INSERT INTO license_keys (id, key_value, user_id, subscription_tier_id, status_id, expires_at, activated_at) 
VALUES (UUID(), 'AAAA-BBBB-CCCC-DDDD-EEEE', @user1_id, 2, 1, DATE_ADD(NOW(), INTERVAL 1 YEAR), NOW()); -- pro tier, active

-- Get the first license key ID
SET @license1_id = (SELECT id FROM license_keys WHERE key_value = 'AAAA-BBBB-CCCC-DDDD-EEEE');

INSERT INTO license_keys (id, key_value, user_id, subscription_tier_id, status_id, expires_at, activated_at) 
VALUES (UUID(), 'FFFF-GGGG-HHHH-IIII-JJJJ', @user2_id, 1, 1, DATE_ADD(NOW(), INTERVAL 1 YEAR), NOW()); -- basic tier, active

-- Get the second license key ID
SET @license2_id = (SELECT id FROM license_keys WHERE key_value = 'FFFF-GGGG-HHHH-IIII-JJJJ');

INSERT INTO license_keys (id, key_value, subscription_tier_id, status_id) 
VALUES (UUID(), 'KKKK-LLLL-MMNN-OOOP-QQQQ', 2, 4); -- pro tier, pending (available for activation)

-- Get the third license key ID
SET @license3_id = (SELECT id FROM license_keys WHERE key_value = 'KKKK-LLLL-MMNN-OOOP-QQQQ');

-- Create sample orders
INSERT INTO orders (id, user_id, stripe_session_id, amount, currency, subscription_tier_id, status) 
VALUES (UUID(), @user1_id, 'cs_test_12345', 4900, 'usd', 2, 'completed'); -- pro tier order

INSERT INTO orders (id, user_id, stripe_session_id, amount, currency, subscription_tier_id, status) 
VALUES (UUID(), @user2_id, 'cs_test_67890', 0, 'usd', 1, 'completed'); -- basic tier order

-- Create sample dashboard logs
INSERT INTO dashboard_logs (id, user_id, action, details, ip_address, user_agent) 
VALUES (UUID(), @user1_id, 'login', '{"location": "Dashboard"}', '192.168.1.100', 'Mozilla/5.0');

INSERT INTO dashboard_logs (id, user_id, action, details, ip_address, user_agent) 
VALUES (UUID(), @user2_id, 'view_profile', '{"page": "Profile"}', '192.168.1.101', 'Mozilla/5.0');

-- Create sample mail activity
INSERT INTO mail_activity (id, user_id, email_type, recipient_email, status, details) 
VALUES (UUID(), @user1_id, 'welcome', 'john.doe@example.com', 'sent', '{"subject": "Welcome to FlashCore"}');

INSERT INTO mail_activity (id, user_id, email_type, recipient_email, status, details) 
VALUES (UUID(), @user2_id, 'notification', 'jane.smith@example.com', 'sent', '{"subject": "New Feature Available"}');

-- Create sample SMTP settings
INSERT INTO smtp_settings (id, provider, host, port, username, password, from_email, from_name, is_active) 
VALUES (UUID(), 'gmail', 'smtp.gmail.com', 587, 'noreply@flashcore.com', 'password123', 'noreply@flashcore.com', 'FlashCore', TRUE);

-- Create sample email templates
INSERT INTO email_templates (id, name, subject, content, template_type, is_active, created_by) 
VALUES (UUID(), 'Welcome Email', 'Welcome to FlashCore!', '<h1>Welcome {{username}}!</h1><p>Thank you for joining FlashCore.</p>', 'welcome', TRUE, @admin_id);

INSERT INTO email_templates (id, name, subject, content, template_type, is_active, created_by) 
VALUES (UUID(), 'License Activation', 'Your License is Active!', '<h1>Hello {{username}}!</h1><p>Your license key {{license_key}} has been activated.</p>', 'license_activation', TRUE, @admin_id);

-- Create sample admin logs
INSERT INTO admin_logs (id, admin_id, action, target_table, target_id, details) 
VALUES (UUID(), @admin_id, 'create_user', 'users', @user1_id, '{"email": "john.doe@example.com"}');

INSERT INTO admin_logs (id, admin_id, action, target_table, target_id, details) 
VALUES (UUID(), @admin_id, 'activate_license', 'license_keys', @license1_id, '{"key": "AAAA-BBBB-CCCC-DDDD-EEEE"}');

-- Create sample sessions
INSERT INTO sessions (id, user_id, session_token, expires_at) 
VALUES (UUID(), @admin_id, 'session_token_admin_12345', DATE_ADD(NOW(), INTERVAL 1 HOUR));

INSERT INTO sessions (id, user_id, session_token, expires_at) 
VALUES (UUID(), @user1_id, 'session_token_user1_67890', DATE_ADD(NOW(), INTERVAL 1 HOUR));

-- Show completion message
SELECT 'Database seeding completed successfully!' AS message;