import sqlite3 from 'sqlite3';
import { promisify } from 'util';
import path from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const dbPath = path.join(__dirname, 'database.sqlite');
const db = new sqlite3.Database(dbPath);

// Custom wrapper to get lastID from db.run
function dbRun(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.run(sql, params, function(err) {
      if (err) reject(err);
      else resolve({ lastID: this.lastID, changes: this.changes });
    });
  });
}

const dbGet = promisify(db.get.bind(db));

// Initialize database tables
async function initDatabase() {
  try {
    // Create pharmacies table
    await dbRun(`
      CREATE TABLE IF NOT EXISTS pharmacies (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        address TEXT NOT NULL,
        phone TEXT,
        latitude REAL,
        longitude REAL
      )
    `);

    // Create medicines table
    await dbRun(`
      CREATE TABLE IF NOT EXISTS medicines (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        active_ingredient TEXT,
        dosage TEXT,
        form TEXT
      )
    `);

    // Create junction table for pharmacy-medicine relationships
    // Ahora usa nregistro (número de registro de CIMA) en lugar de medicine_id local
    await dbRun(`
      CREATE TABLE IF NOT EXISTS pharmacy_medicines (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pharmacy_id INTEGER NOT NULL,
        medicine_nregistro TEXT NOT NULL,
        medicine_name TEXT,
        price REAL,
        stock INTEGER DEFAULT 0,
        FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
        UNIQUE(pharmacy_id, medicine_nregistro)
      )
    `);

    // Create indexes for better search performance
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_medicine_name ON medicines(name)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_pharmacy_medicine ON pharmacy_medicines(medicine_nregistro)`);

    console.log('Database tables initialized');
  } catch (error) {
    console.error('Error initializing database:', error);
    throw error;
  }
}

// Sample data
const pharmacies = [
  { name: 'Farmacia Central', address: 'Av. Principal 123, Ciudad', phone: '+34 123 456 789', lat: 40.4168, lng: -3.7038 },
  { name: 'Farmacia San José', address: 'Calle Mayor 45, Ciudad', phone: '+34 987 654 321', lat: 40.4178, lng: -3.7048 },
  { name: 'Farmacia del Sol', address: 'Plaza del Sol 12, Ciudad', phone: '+34 555 123 456', lat: 40.4158, lng: -3.7028 },
  { name: 'Farmacia Salud', address: 'Calle Salud 78, Ciudad', phone: '+34 666 789 012', lat: 40.4188, lng: -3.7058 },
  { name: 'Farmacia 24h', address: 'Av. Libertad 234, Ciudad', phone: '+34 777 345 678', lat: 40.4148, lng: -3.7018 },
];

const medicines = [
  { name: 'Paracetamol 500mg', active_ingredient: 'Paracetamol', dosage: '500mg', form: 'Tabletas' },
  { name: 'Ibuprofeno 600mg', active_ingredient: 'Ibuprofeno', dosage: '600mg', form: 'Tabletas' },
  { name: 'Aspirina 100mg', active_ingredient: 'Ácido Acetilsalicílico', dosage: '100mg', form: 'Tabletas' },
  { name: 'Amoxicilina 500mg', active_ingredient: 'Amoxicilina', dosage: '500mg', form: 'Cápsulas' },
  { name: 'Omeprazol 20mg', active_ingredient: 'Omeprazol', dosage: '20mg', form: 'Cápsulas' },
  { name: 'Loratadina 10mg', active_ingredient: 'Loratadina', dosage: '10mg', form: 'Tabletas' },
  { name: 'Diclofenaco 50mg', active_ingredient: 'Diclofenaco', dosage: '50mg', form: 'Tabletas' },
  { name: 'Metformina 850mg', active_ingredient: 'Metformina', dosage: '850mg', form: 'Tabletas' },
  { name: 'Atorvastatina 20mg', active_ingredient: 'Atorvastatina', dosage: '20mg', form: 'Tabletas' },
  { name: 'Losartán 50mg', active_ingredient: 'Losartán', dosage: '50mg', form: 'Tabletas' },
];

async function seedDatabase() {
  try {
    console.log('Starting database seeding...');

    // Initialize database tables first
    await initDatabase();

    // Clear existing data
    await dbRun('DELETE FROM pharmacy_medicines');
    await dbRun('DELETE FROM medicines');
    await dbRun('DELETE FROM pharmacies');

    // Insert pharmacies
    const pharmacyIds = [];
    for (const pharmacy of pharmacies) {
      const result = await dbRun(
        'INSERT INTO pharmacies (name, address, phone, latitude, longitude) VALUES (?, ?, ?, ?, ?)',
        [pharmacy.name, pharmacy.address, pharmacy.phone, pharmacy.lat, pharmacy.lng]
      );
      pharmacyIds.push(result.lastID);
    }
    console.log(`Inserted ${pharmacyIds.length} pharmacies`);

    // Insert medicines
    const medicineIds = [];
    for (const medicine of medicines) {
      const result = await dbRun(
        'INSERT INTO medicines (name, active_ingredient, dosage, form) VALUES (?, ?, ?, ?)',
        [medicine.name, medicine.active_ingredient, medicine.dosage, medicine.form]
      );
      medicineIds.push(result.lastID);
    }
    console.log(`Inserted ${medicineIds.length} medicines`);

    // Create pharmacy-medicine relationships
    // Each medicine is available in 2-4 random pharmacies with random prices
    let relationshipCount = 0;
    for (let i = 0; i < medicineIds.length; i++) {
      const medicineId = medicineIds[i];
      const numPharmacies = Math.floor(Math.random() * 3) + 2; // 2-4 pharmacies
      const selectedPharmacies = new Set();
      
      while (selectedPharmacies.size < numPharmacies) {
        selectedPharmacies.add(Math.floor(Math.random() * pharmacyIds.length));
      }

      for (const pharmacyIndex of selectedPharmacies) {
        const pharmacyId = pharmacyIds[pharmacyIndex];
        const price = (Math.random() * 20 + 5).toFixed(2); // Random price between 5-25
        const stock = Math.floor(Math.random() * 50) + 10; // Random stock 10-60

        // NOTA: Como ahora usamos CIMA API, este seed solo crea ejemplos
        // En producción, deberías vincular usando nregistros reales de CIMA
        const medicine = medicines[i];
        await dbRun(
          'INSERT INTO pharmacy_medicines (pharmacy_id, medicine_nregistro, medicine_name, price, stock) VALUES (?, ?, ?, ?, ?)',
          [pharmacyId, `EXAMPLE_${medicineId}`, medicine.name, price, stock]
        );
        relationshipCount++;
      }
    }
    console.log(`Created ${relationshipCount} pharmacy-medicine relationships`);
    console.log('⚠️  NOTA: Los medicamentos de ejemplo usan IDs ficticios.');

    console.log('Database seeding completed successfully!');
  } catch (error) {
    console.error('Error seeding database:', error);
  } finally {
    db.close();
  }
}

seedDatabase();