/**
 * Database Sync Script
 * Creates all database tables from updated Sequelize models
 * 
 * Usage:
 *   node server/scripts/sync-database.js              # Sync without force (create tables if not exist)
 *   node server/scripts/sync-database.js --force      # Drop and recreate all tables (WARNING: Deletes all data)
 */

require('dotenv').config({ path: require('path').join(__dirname, '../.env') });

const db = require('../models');
const logger = require('../utils/logger');

async function syncDatabase(force = false) {
  try {
    logger.info(`Starting database sync (force: ${force})...`);
    
    if (force) {
      logger.warn('⚠️  WARNING: Force sync will DROP and RECREATE all tables!');
      logger.warn('⚠️  All existing data will be PERMANENTLY DELETED!');
    }
    
    // Test database connection
    await db.sequelize.authenticate();
    logger.info('✓ Database connection established.');
    
    // If force sync, first drop old tables that no longer exist in models
    // Must drop in order to avoid foreign key constraint issues
    if (force) {
      logger.info('Dropping old tables that no longer exist in models...');
      
      // First disable foreign key checks
      await db.sequelize.query('SET FOREIGN_KEY_CHECKS = 0;');
      
      // Drop tables in reverse dependency order
      const tablesToDrop = [
        'tax_exemptions',    // Depend on tax_categories
        'tax_rates',         // Depend on tax_categories
        'tax_categories',    // Depend on products
        'product_variants',  // Depend on products
      ];
      
      for (const tableName of tablesToDrop) {
        try {
          await db.sequelize.query(`DROP TABLE IF EXISTS \`${tableName}\`;`);
          logger.info(`  ✓ Dropped table: ${tableName}`);
        } catch (error) {
          // Log but continue
          logger.warn(`  ⚠ Could not drop table ${tableName}: ${error.message}`);
        }
      }
      
      // Re-enable foreign key checks
      await db.sequelize.query('SET FOREIGN_KEY_CHECKS = 1;');
    }
    
    // Sync all models
    const options = force ? { force: true } : { alter: false };
    await db.sequelize.sync(options);
    
    logger.info('✓ Database models synchronized successfully.');
    
    if (force) {
      logger.info('✓ All tables have been recreated from models.');
    } else {
      logger.info('✓ All tables are up to date (existing tables preserved).');
    }
    
    // List all tables
    const [results] = await db.sequelize.query("SHOW TABLES");
    const tableNames = results.map(row => Object.values(row)[0]);
    logger.info(`\n📊 Database contains ${tableNames.length} tables:`);
    tableNames.forEach(table => logger.info(`  - ${table}`));
    
    process.exit(0);
  } catch (error) {
    logger.error('❌ Database sync failed:', error);
    
    if (error.name === 'SequelizeConnectionError') {
      logger.error('   Check your database connection settings in .env file');
      logger.error('   Make sure MySQL is running and the database exists');
    } else if (error.message && error.message.includes('Too many keys')) {
      logger.error('   Error: Too many indexes. Try using --force to recreate tables.');
    }
    
    process.exit(1);
  }
}

// Parse command line arguments
const args = process.argv.slice(2);
const force = args.includes('--force') || args.includes('-f');

// Run sync
syncDatabase(force);
