const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.DATABASE_URL?.includes('amazonaws.com') ? { rejectUnauthorized: false } : false
});

async function migrate() {
  try {
    // Read schema file
    const schemaPath = path.join(__dirname, '..', 'database', 'schema.sql');
    const schema = fs.readFileSync(schemaPath, 'utf8');
    
    // Execute schema
    await pool.query(schema);
    
    console.log('Database migration completed successfully');
    
    // Run additional migrations
    const migrationsPath = path.join(__dirname, '..', 'database', 'migrations');
    if (fs.existsSync(migrationsPath)) {
      const migrationFiles = fs.readdirSync(migrationsPath)
        .filter(file => file.endsWith('.sql'))
        .sort();
        
      for (const file of migrationFiles) {
        console.log(`Running migration: ${file}`);
        const migrationSQL = fs.readFileSync(path.join(migrationsPath, file), 'utf8');
        try {
          await pool.query(migrationSQL);
          console.log(`✓ Migration ${file} completed`);
        } catch (err) {
          console.log(`⚠ Migration ${file} skipped:`, err.message);
        }
      }
    }
    
    // Import legacy data if available
    try {
      const dataPath = path.join(__dirname, '..', 'data.json');
      if (fs.existsSync(dataPath)) {
        const legacyData = JSON.parse(fs.readFileSync(dataPath, 'utf8'));
        
        // Import colors
        const colors = new Set();
        legacyData.forEach(item => {
          if (item.boja) colors.add(item.boja);
        });
        
        for (const color of colors) {
          const hex = legacyData.find(item => item.boja === color)?.bojaHex || '#000000';
          await pool.query(
            'INSERT INTO colors (name, hex) VALUES ($1, $2) ON CONFLICT (name) DO UPDATE SET hex = $2',
            [color, hex]
          );
        }
        
        // Import filaments
        for (const item of legacyData) {
          await pool.query(
            `INSERT INTO filaments (tip, finish, boja, boja_hex, refill, vakum, otvoreno, kolicina, cena) 
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
            [
              item.tip,
              item.finish,
              item.boja,
              item.bojaHex,
              item.refill,
              item.vakum,
              item.otvoreno,
              item.kolicina || 1,
              item.cena
            ]
          );
        }
        
        console.log('Legacy data imported successfully');
      }
    } catch (error) {
      console.error('Error importing legacy data:', error);
    }
    
    process.exit(0);
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  }
}

migrate();