const Database = require('better-sqlite3');
const path = require('path');

const dbPath = path.join(__dirname, '../../data/school.db');
const db = new Database(dbPath);

console.log('Force marking all user profiles as pending sync and updating modification timestamps...');

try {
  const users = db.prepare("SELECT * FROM users WHERE is_deleted = 0").all();
  console.log(`Found ${users.length} active users to update.`);

  const updateStmt = db.prepare(`
    UPDATE users SET
      phone = ?,
      address = ?,
      date_of_birth = ?,
      gender = ?,
      id_number = ?,
      profile_image = ?,
      emergency_contact = ?,
      emergency_phone = ?,
      sync_status = 'pending',
      updated_at = datetime('now', 'localtime')
    WHERE id = ?
  `);

  let updatedCount = 0;

  const addresses = [
    '123 Samora Machel Ave, Harare',
    '45 Leopold Takawira St, Bulawayo',
    '88 Josiah Tongogara Ave, Harare',
    '12 Herbert Chitepo St, Mutare',
    '206 Fife Ave, Harare',
    '77 Robert Mugabe Way, Gweru',
    '14 Jason Moyo Ave, Bulawayo',
    '55 Kaguvi St, Harare',
    '102 Enterprise Rd, Highlands, Harare',
    '34 Borrowdale Rd, Harare'
  ];

  const femaleFirstNames = ['Grace', 'Chipo', 'Nyasha', 'Tendai', 'Ruvimbo', 'Maud', 'Buhle', 'Thandiwe', 'Rudo', 'Maita', 'Zanele', 'Makanaka', 'Rutendo', 'Nomusa', 'Ayanda'];
  const maleFirstNames = ['Nkosana', 'Tawanda', 'Munashe', 'Sibusiso', 'Takudzwa', 'Melusi', 'Farai', 'Tinashe', 'Mthabisi', 'Panashe', 'Bheki', 'Simbarashe', 'Anesu', 'Kudzai', 'Tatenda'];

  const emergencyRelations = ['Father', 'Mother', 'Spouse', 'Guardian', 'Uncle', 'Aunt', 'Sibling'];

  db.transaction(() => {
    users.forEach((user, index) => {
      let phone = user.phone;
      if (!phone || phone.includes('null') || phone === '') {
        phone = `+26377${Math.floor(1000000 + Math.random() * 9000000)}`;
      }

      let address = user.address;
      if (!address || address.includes('null') || address === '') {
        address = addresses[index % addresses.length];
      }

      let dob = user.date_of_birth;
      if (!dob || dob.includes('null') || dob === '') {
        let age = 30 + (index % 30);
        if (user.role === 'student') {
          age = 11 + (index % 7);
        }
        const birthYear = new Date().getFullYear() - age;
        const birthMonth = String(1 + (index % 12)).padStart(2, '0');
        const birthDay = String(1 + (index % 28)).padStart(2, '0');
        dob = `${birthYear}-${birthMonth}-${birthDay}`;
      }

      let gender = user.gender;
      if (!gender || gender.includes('null') || gender === '') {
        gender = index % 2 === 0 ? 'female' : 'male';
      }

      let idNumber = user.id_number;
      if (!idNumber || idNumber.includes('null') || idNumber === '') {
        const prefix = String(index % 99).padStart(2, '0');
        const middle = Math.floor(100000 + Math.random() * 900000);
        const suffixLetter = 'ABCDEFGHJKLMNPQRSTUVWXY'[index % 23];
        const suffixCode = String(index % 99).padStart(2, '0');
        idNumber = `${prefix}-${middle}-${suffixLetter}-${suffixCode}`;
      }

      let profileImage = user.profile_image;
      if (!profileImage || profileImage.includes('null') || profileImage === '') {
        const seedName = `${user.first_name}_${user.last_name}`.toLowerCase();
        profileImage = `https://api.dicebear.com/7.x/adventurer/svg?seed=${seedName}`;
      }

      let emergencyContact = user.emergency_contact;
      let emergencyPhone = user.emergency_phone;
      if (!emergencyContact || emergencyContact.includes('null') || emergencyContact === '' ||
          !emergencyPhone || emergencyPhone.includes('null') || emergencyPhone === '') {
        const contactGender = Math.random() > 0.5 ? 'female' : 'male';
        const contactFirstName = contactGender === 'female' 
          ? femaleFirstNames[index % femaleFirstNames.length] 
          : maleFirstNames[index % maleFirstNames.length];
        const relation = emergencyRelations[index % emergencyRelations.length];
        emergencyContact = `${contactFirstName} ${user.last_name} (${relation})`;
        emergencyPhone = `+26371${Math.floor(1000000 + Math.random() * 9000000)}`;
      }

      updateStmt.run(
        phone,
        address,
        dob,
        gender,
        idNumber,
        profileImage,
        emergencyContact,
        emergencyPhone,
        user.id
      );
      updatedCount++;
    });
  })();

  console.log(`✅ Force marked and updated ${updatedCount} users to 'pending' with new modification times.`);
} catch (err) {
  console.error('❌ Data injection transaction failed:', err.message);
} finally {
  db.close();
}
