const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');
require('dotenv').config();

async function createTestData() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'sts_v2'
  });

  try {
    console.log('Creating test data...');

    // Create Super Admin user
    const superAdminPassword = await bcrypt.hash('admin123', 10);
    await connection.execute(`
      INSERT IGNORE INTO users (id, first_name, last_name, email, password_hash, user_type, status) 
      VALUES (1, 'Super', 'Admin', 'admin@gpstracking.com', ?, 'admin', 'active')
    `, [superAdminPassword]);

    // Create School Admin user
    const schoolAdminPassword = await bcrypt.hash('school123', 10);
    await connection.execute(`
      INSERT IGNORE INTO users (id, first_name, last_name, email, password_hash, user_type, status) 
      VALUES (2, 'School', 'Admin', 'school@greenvalley.edu', ?, 'school', 'active')
    `, [schoolAdminPassword]);

    // Create Parent user
    const parentPassword = await bcrypt.hash('parent123', 10);
    await connection.execute(`
      INSERT IGNORE INTO users (id, first_name, last_name, email, password_hash, user_type, status) 
      VALUES (3, 'John', 'Parent', 'parent@email.com', ?, 'parent', 'active')
    `, [parentPassword]);

    // Create companies
    await connection.execute(`
      INSERT IGNORE INTO companies (id, name, contact_person, email, phone) 
      VALUES (1, 'GPS Tracking Solutions Ltd', 'Super Admin', 'admin@gpstracking.com', '9999999999')
    `);

    // Create subscription plans
    await connection.execute(`
      INSERT IGNORE INTO subscription_plans (id, name, price_per_device, billing_cycle, features) VALUES 
      (1, 'Basic Plan', 500.00, 'monthly', '{"live_tracking": true, "geofencing": true, "alerts": true}'),
      (2, 'Premium Plan', 800.00, 'monthly', '{"live_tracking": true, "geofencing": true, "alerts": true, "history": true, "reports": true}'),
      (3, 'Enterprise Plan', 1200.00, 'monthly', '{"live_tracking": true, "geofencing": true, "alerts": true, "history": true, "reports": true, "api_access": true}')
    `);

    // Create schools
    await connection.execute(`
      INSERT IGNORE INTO schools (id, name, address, phone, email, contact_person, company_id, subscription_plan_id, subscription_start_date, subscription_end_date, status) VALUES 
      (1, 'Green Valley Elementary', '123 School St, City', '555-0123', 'school@greenvalley.edu', 'School Admin', 1, 2, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 'active'),
      (2, 'Sunrise High School', '456 Education Ave, City', '555-0124', 'contact@sunrise.edu', 'Principal Smith', 1, 1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 6 MONTH), 'active')
    `);

    // Create devices
    await connection.execute(`
      INSERT IGNORE INTO devices (id, school_id, imei_number, sim_number, model, manufacturer, type, status, battery_level, purchase_date) VALUES 
      ('DEV001', 1, '123456789012345', '9876543210', 'GT-100', 'TrackTech', 'GPS Tracker', 'active', 85, '2024-01-15'),
      ('DEV002', 1, '123456789012346', '9876543211', 'GT-100', 'TrackTech', 'GPS Tracker', 'active', 92, '2024-01-15'),
      ('DEV003', 1, '123456789012347', '9876543212', 'GT-100', 'TrackTech', 'GPS Tracker', 'active', 78, '2024-01-15'),
      ('DEV004', 2, '123456789012348', '9876543213', 'GT-100', 'TrackTech', 'GPS Tracker', 'active', 91, '2024-01-15'),
      ('DEV005', NULL, '123456789012349', '9876543214', 'GT-100', 'TrackTech', 'GPS Tracker', 'inactive', 100, '2024-01-15')
    `);

    // Create parent profile
    await connection.execute(`
      INSERT IGNORE INTO parent_profiles (user_id, phone_number, relationship_to_child, is_phone_verified) 
      VALUES (3, '9876543210', 'father', TRUE)
    `);

    // Create children for the school (school_id = 1 for the logged-in school)
    await connection.execute(`
      INSERT IGNORE INTO children (id, parent_id, first_name, last_name, standard, division, roll_number, school_id, device_id, status) VALUES 
      (1, 4, 'Emma', 'Johnson', '5th', 'A', 15, 1, 'DEV001', 'active'),
      (2, 4, 'Alex', 'Johnson', '3rd', 'B', 22, 1, 'DEV002', 'active'),
      (3, 4, 'Sarah', 'Smith', '7th', 'A', 8, 1, 'DEV003', 'active')
    `);

    // Create device assignments
    await connection.execute(`
      INSERT IGNORE INTO device_assignments (device_id, student_id, school_id, assigned_by, status) VALUES 
      ('DEV001', 1, 1, 2, 'active'),
      ('DEV002', 2, 1, 2, 'active'),
      ('DEV004', 3, 2, 2, 'active')
    `);

    // Create sample location data
    await connection.execute(`
      INSERT IGNORE INTO device_locations (device_id, latitude, longitude, address, timestamp) VALUES 
      ('DEV001', 28.6139, 77.2090, 'Near School Gate, Delhi', NOW() - INTERVAL 5 MINUTE),
      ('DEV002', 28.6129, 77.2080, 'School Playground, Delhi', NOW() - INTERVAL 3 MINUTE),
      ('DEV004', 28.6149, 77.2100, 'Library Building, Delhi', NOW() - INTERVAL 2 MINUTE)
    `);

    // Create geofences
    await connection.execute(`
      INSERT IGNORE INTO geofences (name, type, center_latitude, center_longitude, radius_meters, school_id, created_by, status) VALUES 
      ('School Campus', 'school', 28.6139, 77.2090, 200, 1, 2, 'active'),
      ('Safe Zone Park', 'safe_zone', 28.6120, 77.2070, 100, 1, 2, 'active')
    `);

    // Create sample alerts
    await connection.execute(`
      INSERT IGNORE INTO alerts (type, student_id, device_id, message, latitude, longitude, created_at) VALUES 
      ('geofence_entry', 1, 'DEV001', 'Emma entered school premises', 28.6139, 77.2090, NOW() - INTERVAL 1 HOUR),
      ('low_battery', 2, 'DEV002', 'Alex device battery below 20%', 28.6129, 77.2080, NOW() - INTERVAL 30 MINUTE)
    `);

    // Create sample payments
    await connection.execute(`
      INSERT IGNORE INTO payments (school_id, subscription_plan_id, amount, payment_method, payment_status, payment_date, billing_period_start, billing_period_end, collected_by, receipt_number) VALUES 
      (1, 2, 1600.00, 'bank_transfer', 'completed', CURDATE(), CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 MONTH), 2, 'RCP001'),
      (2, 1, 500.00, 'cash', 'completed', CURDATE() - INTERVAL 1 DAY, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 MONTH), 2, 'RCP002')
    `);

    // Create sample support tickets
    await connection.execute(`
      INSERT IGNORE INTO support_tickets (ticket_number, type, priority, status, school_id, subject, description, created_by) VALUES 
      ('TKT001', 'device_replacement', 'high', 'open', 1, 'Device DEV003 not working', 'The GPS tracker is not sending location updates since yesterday', 2),
      ('TKT002', 'technical_issue', 'medium', 'in_progress', 2, 'Login issues', 'Unable to access parent dashboard', 3)
    `);

    console.log('✅ Test data created successfully!');
    console.log('\n📋 Test Accounts:');
    console.log('Super Admin: admin@gpstracking.com / admin123');
    console.log('School Admin: school@greenvalley.edu / school123');
    console.log('Parent: parent@email.com / parent123 (or use OTP: 9876543210)');
    console.log('\n🏫 Test Schools: Green Valley Elementary, Sunrise High School');
    console.log('📱 Test Devices: DEV001-DEV005');
    console.log('👨‍👩‍👧‍👦 Test Students: Emma, Alex, Sarah');

  } catch (error) {
    console.error('Error creating test data:', error);
  } finally {
    await connection.end();
  }
}

createTestData();