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

async function seedDatabase() {
  let connection;
  
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: '',
      database: 'smile24',
      port: 3306
    });

    console.log('🌱 Starting database seed...');

    // Clear existing data
    await connection.execute('DELETE FROM Review');
    await connection.execute('DELETE FROM Message');
    await connection.execute('DELETE FROM Bid');
    await connection.execute('DELETE FROM Project');
    await connection.execute('DELETE FROM Subscription');
    await connection.execute('DELETE FROM Payment');
    await connection.execute('DELETE FROM Profile');
    await connection.execute('DELETE FROM User');
    await connection.execute('DELETE FROM Category');
    await connection.execute('DELETE FROM CountryLanguage');
    await connection.execute('DELETE FROM Language');
    await connection.execute('DELETE FROM Country');

    console.log('🗑️  Cleared existing data');

    // Create languages
    const languages = [
      { id: 'lang_en', code: 'en', name: 'English' },
      { id: 'lang_fr', code: 'fr', name: 'French' },
      { id: 'lang_de', code: 'de', name: 'German' },
      { id: 'lang_it', code: 'it', name: 'Italian' },
      { id: 'lang_pt', code: 'pt', name: 'Portuguese' },
      { id: 'lang_es', code: 'es', name: 'Spanish' }
    ];

    for (const lang of languages) {
      await connection.execute(
        'INSERT INTO Language (id, code, name, isActive) VALUES (?, ?, ?, ?)',
        [lang.id, lang.code, lang.name, true]
      );
    }

    console.log('🌍 Created languages');

    // Create countries
    const countries = [
      { id: 'luxembourg', name: 'Luxembourg', code: 'LU', domain: 'smile24.lu', defaultLanguage: 'fr', currency: 'EUR', currencySymbol: '€', packagePrice: 9.90 },
      { id: 'belgium', name: 'Belgium', code: 'BE', domain: 'smile24.be', defaultLanguage: 'fr', currency: 'EUR', currencySymbol: '€', packagePrice: 9.90 },
      { id: 'france', name: 'France', code: 'FR', domain: 'smile24.fr', defaultLanguage: 'fr', currency: 'EUR', currencySymbol: '€', packagePrice: 9.90 },
      { id: 'switzerland', name: 'Switzerland', code: 'CH', domain: 'smile24.ch', defaultLanguage: 'fr', currency: 'CHF', currencySymbol: 'CHF', packagePrice: 19.90 },
      { id: 'italy', name: 'Italy', code: 'IT', domain: 'smile24.it', defaultLanguage: 'it', currency: 'EUR', currencySymbol: '€', packagePrice: 9.90 },
      { id: 'uk', name: 'United Kingdom', code: 'GB', domain: 'smile24.uk', defaultLanguage: 'en', currency: 'GBP', currencySymbol: '£', packagePrice: 9.90 },
      { id: 'portugal', name: 'Portugal', code: 'PT', domain: 'smile24.pt', defaultLanguage: 'pt', currency: 'EUR', currencySymbol: '€', packagePrice: 9.90 },
      { id: 'spain', name: 'Spain', code: 'ES', domain: 'smile24.es', defaultLanguage: 'es', currency: 'EUR', currencySymbol: '€', packagePrice: 9.90 },
      { id: 'germany', name: 'Germany', code: 'DE', domain: 'smile77.de', defaultLanguage: 'de', currency: 'EUR', currencySymbol: '€', packagePrice: 9.90 },
      { id: 'austria', name: 'Austria', code: 'AT', domain: 'smile77.at', defaultLanguage: 'de', currency: 'EUR', currencySymbol: '€', packagePrice: 9.90 }
    ];

    for (const country of countries) {
      await connection.execute(
        'INSERT INTO Country (id, name, code, domain, defaultLanguage, currency, currencySymbol, packagePrice, isActive) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
        [country.id, country.name, country.code, country.domain, country.defaultLanguage, country.currency, country.currencySymbol, country.packagePrice, true]
      );
    }

    console.log('🏳️  Created countries');

    // Create country-language relationships
    const countryLanguages = [
      // Luxembourg: French, English
      { countryId: 'luxembourg', languageId: 'lang_fr' },
      { countryId: 'luxembourg', languageId: 'lang_en' },
      // Belgium: French, English
      { countryId: 'belgium', languageId: 'lang_fr' },
      { countryId: 'belgium', languageId: 'lang_en' },
      // France: French, English
      { countryId: 'france', languageId: 'lang_fr' },
      { countryId: 'france', languageId: 'lang_en' },
      // Switzerland: French, German, Italian
      { countryId: 'switzerland', languageId: 'lang_fr' },
      { countryId: 'switzerland', languageId: 'lang_de' },
      { countryId: 'switzerland', languageId: 'lang_it' },
      // Italy: Italian, English
      { countryId: 'italy', languageId: 'lang_it' },
      { countryId: 'italy', languageId: 'lang_en' },
      // UK: English
      { countryId: 'uk', languageId: 'lang_en' },
      // Portugal: Portuguese, English
      { countryId: 'portugal', languageId: 'lang_pt' },
      { countryId: 'portugal', languageId: 'lang_en' },
      // Spain: Spanish, English
      { countryId: 'spain', languageId: 'lang_es' },
      { countryId: 'spain', languageId: 'lang_en' },
      // Germany: German, English
      { countryId: 'germany', languageId: 'lang_de' },
      { countryId: 'germany', languageId: 'lang_en' },
      // Austria: German, English
      { countryId: 'austria', languageId: 'lang_de' },
      { countryId: 'austria', languageId: 'lang_en' }
    ];

    for (const cl of countryLanguages) {
      const id = `cl_${cl.countryId}_${cl.languageId}`;
      await connection.execute(
        'INSERT INTO CountryLanguage (id, countryId, languageId) VALUES (?, ?, ?)',
        [id, cl.countryId, cl.languageId]
      );
    }

    console.log('🔗 Created country-language relationships');

    // Create categories
    const categories = [
      { id: 'cat_web_dev', name: 'Web Development', slug: 'web-development', description: 'Custom websites, web applications, and e-commerce solutions', icon: 'web-development.svg' },
      { id: 'cat_mobile_dev', name: 'Mobile Development', slug: 'mobile-development', description: 'iOS and Android app development', icon: 'mobile-development.svg' },
      { id: 'cat_graphic_design', name: 'Graphic Design', slug: 'graphic-design', description: 'Logos, branding, and visual design', icon: 'graphic-design.svg' },
      { id: 'cat_digital_marketing', name: 'Digital Marketing', slug: 'digital-marketing', description: 'SEO, social media, and online advertising', icon: 'digital-marketing.svg' },
      { id: 'cat_content_writing', name: 'Content Writing', slug: 'content-writing', description: 'Blog posts, articles, and copywriting', icon: 'content-writing.svg' },
      { id: 'cat_translation', name: 'Translation', slug: 'translation', description: 'Document and website translation services', icon: 'translation.svg' },
      { id: 'cat_data_entry', name: 'Data Entry', slug: 'data-entry', description: 'Data processing and administrative tasks', icon: 'data-entry.svg' },
      { id: 'cat_virtual_assistant', name: 'Virtual Assistant', slug: 'virtual-assistant', description: 'Administrative and support services', icon: 'virtual-assistant.svg' }
    ];

    for (const category of categories) {
      await connection.execute(
        'INSERT INTO Category (id, name, slug, description, icon, isActive) VALUES (?, ?, ?, ?, ?, ?)',
        [category.id, category.name, category.slug, category.description, category.icon, true]
      );
    }

    console.log('📂 Created categories');

    // Hash password for sample users
    const hashedPassword = await bcrypt.hash('admin123', 12);

    // Create sample users
    const users = [
      { id: 'user_admin', email: 'admin@smile24.com', password: hashedPassword, role: 'ADMIN' },
      { id: 'user_client', email: 'client@example.com', password: hashedPassword, role: 'CLIENT' },
      { id: 'user_freelancer', email: 'freelancer@example.com', password: hashedPassword, role: 'FREELANCER' }
    ];

    for (const user of users) {
      await connection.execute(
        'INSERT INTO User (id, email, password, role) VALUES (?, ?, ?, ?)',
        [user.id, user.email, user.password, user.role]
      );
    }

    // Create profiles for users
    const profiles = [
      { id: 'profile_admin', userId: 'user_admin', firstName: 'Admin', lastName: 'User', countryId: 'luxembourg', phone: '+352 123 456 789' },
      { id: 'profile_client', userId: 'user_client', firstName: 'John', lastName: 'Client', countryId: 'luxembourg', phone: '+352 987 654 321', bio: 'Looking for talented freelancers for various projects' },
      { id: 'profile_freelancer', userId: 'user_freelancer', firstName: 'Sarah', lastName: 'Freelancer', countryId: 'luxembourg', phone: '+352 555 123 456', bio: 'Experienced web developer with 5+ years of experience in React, Node.js, and modern web technologies.', hourlyRate: 45.00, skills: JSON.stringify(['React', 'Node.js', 'TypeScript', 'MongoDB', 'AWS']) }
    ];

    for (const profile of profiles) {
      await connection.execute(
        'INSERT INTO Profile (id, userId, firstName, lastName, countryId, phone, bio, hourlyRate, skills) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
        [profile.id, profile.userId, profile.firstName, profile.lastName, profile.countryId, profile.phone, profile.bio || null, profile.hourlyRate || null, profile.skills || null]
      );
    }

    console.log('👥 Created sample users and profiles');

    console.log('✅ Database seeding completed successfully!');
    console.log('\n📋 Sample login credentials:');
    console.log('Admin: admin@smile24.com / admin123');
    console.log('Client: client@example.com / admin123');
    console.log('Freelancer: freelancer@example.com / admin123');
    
  } catch (error) {
    console.error('❌ Seeding failed:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

seedDatabase();
