const express = require("express");
const initSqlJs = require("sql.js");
const cors = require("cors");
const fs = require("fs");
const path = require("path");
const multer = require("multer");
const archiver = require("archiver");
const unzipper = require("unzipper");

const app = express();
app.use(cors());
app.use(express.json({ limit: '50mb' }));
app.use(express.urlencoded({ limit: '50mb', extended: true }));
app.use(express.static("public"));

const upload = multer({ dest: "uploads/" });
const DB_PATH = "collection.db";

let db = null;

async function initDB() {
  const SQL = await initSqlJs();
  
  if (fs.existsSync(DB_PATH)) {
    const buffer = fs.readFileSync(DB_PATH);
    db = new SQL.Database(buffer);
    console.log("📊 Base de données chargée");
    
    // Vérifier si les tables custom_fields existent, sinon les créer
    const tablesCheck = db.exec("SELECT name FROM sqlite_master WHERE type='table' AND name IN ('custom_fields', 'custom_field_values')");
    if (!tablesCheck[0] || tablesCheck[0].values.length < 2) {
      console.log("🔧 Création des tables custom_fields...");
      db.run(`
        CREATE TABLE IF NOT EXISTS custom_fields (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          field_type TEXT NOT NULL,
          required INTEGER DEFAULT 0,
          options TEXT,
          order_index INTEGER DEFAULT 0,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS custom_field_values (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          mineral_id INTEGER NOT NULL,
          field_id INTEGER NOT NULL,
          value TEXT,
          FOREIGN KEY (mineral_id) REFERENCES minerals(id) ON DELETE CASCADE,
          FOREIGN KEY (field_id) REFERENCES custom_fields(id) ON DELETE CASCADE
        );

        CREATE INDEX IF NOT EXISTS idx_custom_mineral_field ON custom_field_values(mineral_id, field_id);
      `);
      saveDB();
    }
  } else {
    db = new SQL.Database();
    console.log("📊 Nouvelle base de données créée");
    
    db.run(`
      CREATE TABLE IF NOT EXISTS minerals (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        reference TEXT NOT NULL,
        locality TEXT,
        city TEXT,
        department TEXT,
        storage TEXT,
        date_acquired TEXT,
        geolocation TEXT,
        comments TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      );

      CREATE TABLE IF NOT EXISTS images (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mineral_id INTEGER NOT NULL,
        path TEXT NOT NULL,
        order_index INTEGER DEFAULT 0,
        FOREIGN KEY (mineral_id) REFERENCES minerals(id) ON DELETE CASCADE
      );

      CREATE TABLE IF NOT EXISTS custom_fields (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        field_type TEXT NOT NULL,
        required INTEGER DEFAULT 0,
        options TEXT,
        order_index INTEGER DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      );

      CREATE TABLE IF NOT EXISTS custom_field_values (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mineral_id INTEGER NOT NULL,
        field_id INTEGER NOT NULL,
        value TEXT,
        FOREIGN KEY (mineral_id) REFERENCES minerals(id) ON DELETE CASCADE,
        FOREIGN KEY (field_id) REFERENCES custom_fields(id) ON DELETE CASCADE
      );

      CREATE INDEX IF NOT EXISTS idx_mineral_id ON images(mineral_id);
      CREATE INDEX IF NOT EXISTS idx_custom_mineral_field ON custom_field_values(mineral_id, field_id);
    `);
    
    saveDB();
  }
}

function saveDB() {
  const data = db.export();
  const buffer = Buffer.from(data);
  fs.writeFileSync(DB_PATH, buffer);
}

function formatDateEU(dateStr) {
  if (!dateStr) return "";
  if (typeof dateStr === "string") {
    if (/^\d{2}\/\d{2}\/\d{4}$/.test(dateStr)) return dateStr;
    if (/^\d{4}-\d{2}-\d{2}$/.test(dateStr)) {
      const [y, mo, d] = dateStr.split("-");
      return `${d}/${mo}/${y}`;
    }
  }
  const d = new Date(dateStr);
  if (!isNaN(d)) {
    const day = String(d.getDate()).padStart(2, "0");
    const month = String(d.getMonth() + 1).padStart(2, "0");
    const year = d.getFullYear();
    return `${day}/${month}/${year}`;
  }
  return String(dateStr);
}

// ========== ENDPOINTS CUSTOM FIELDS ==========

// GET - Récupérer tous les champs personnalisés
app.get("/custom-fields", (req, res) => {
  try {
    const stmt = db.prepare("SELECT * FROM custom_fields ORDER BY order_index");
    const fields = [];
    
    while (stmt.step()) {
      const row = stmt.getAsObject();
      fields.push({
        id: row.id,
        name: row.name,
        fieldType: row.field_type,
        required: row.required === 1,
        options: row.options ? JSON.parse(row.options) : null,
        orderIndex: row.order_index
      });
    }
    stmt.free();
    
    res.json(fields);
  } catch (error) {
    console.error("Error fetching custom fields:", error);
    res.status(500).json({ error: error.message });
  }
});

// POST - Créer un nouveau champ personnalisé
app.post("/custom-fields", (req, res) => {
  try {
    const { name, fieldType, required, options } = req.body;
    
    // Vérifier que le nom n'existe pas déjà
    const checkStmt = db.prepare("SELECT COUNT(*) as count FROM custom_fields WHERE name = ?");
    checkStmt.bind([name]);
    checkStmt.step();
    const exists = checkStmt.getAsObject().count > 0;
    checkStmt.free();
    
    if (exists) {
      return res.status(400).json({ error: "Un champ avec ce nom existe déjà" });
    }
    
    // Obtenir l'ordre max
    const orderStmt = db.prepare("SELECT MAX(order_index) as max_order FROM custom_fields");
    orderStmt.step();
    const maxOrder = orderStmt.getAsObject().max_order || 0;
    orderStmt.free();
    
    const optionsJson = options ? JSON.stringify(options) : null;
    
    db.run(
      "INSERT INTO custom_fields (name, field_type, required, options, order_index) VALUES (?, ?, ?, ?, ?)",
      [name, fieldType, required ? 1 : 0, optionsJson, maxOrder + 1]
    );
    
    saveDB();
    
    // Récupérer l'ID du champ créé
    const idStmt = db.prepare("SELECT last_insert_rowid() as id");
    idStmt.step();
    const newId = idStmt.getAsObject().id;
    idStmt.free();
    
    res.json({ 
      success: true, 
      id: newId,
      message: "Champ personnalisé créé avec succès"
    });
  } catch (error) {
    console.error("Error creating custom field:", error);
    res.status(500).json({ error: error.message });
  }
});

// PUT - Modifier un champ personnalisé
app.put("/custom-fields/:id", (req, res) => {
  try {
    const { id } = req.params;
    const { name, fieldType, required, options } = req.body;
    
    const optionsJson = options ? JSON.stringify(options) : null;
    
    db.run(
      "UPDATE custom_fields SET name = ?, field_type = ?, required = ?, options = ? WHERE id = ?",
      [name, fieldType, required ? 1 : 0, optionsJson, id]
    );
    
    saveDB();
    
    res.json({ success: true, message: "Champ personnalisé modifié avec succès" });
  } catch (error) {
    console.error("Error updating custom field:", error);
    res.status(500).json({ error: error.message });
  }
});

// DELETE - Supprimer un champ personnalisé
app.delete("/custom-fields/:id", (req, res) => {
  try {
    const { id } = req.params;
    
    // Supprimer d'abord toutes les valeurs associées
    db.run("DELETE FROM custom_field_values WHERE field_id = ?", [id]);
    
    // Puis supprimer le champ
    db.run("DELETE FROM custom_fields WHERE id = ?", [id]);
    
    saveDB();
    
    res.json({ success: true, message: "Champ personnalisé supprimé avec succès" });
  } catch (error) {
    console.error("Error deleting custom field:", error);
    res.status(500).json({ error: error.message });
  }
});

// PUT - Réorganiser l'ordre des champs
app.put("/custom-fields-order", (req, res) => {
  try {
    const { fieldIds } = req.body; // Array d'IDs dans le nouvel ordre
    
    fieldIds.forEach((id, index) => {
      db.run("UPDATE custom_fields SET order_index = ? WHERE id = ?", [index, id]);
    });
    
    saveDB();
    
    res.json({ success: true, message: "Ordre des champs mis à jour" });
  } catch (error) {
    console.error("Error reordering custom fields:", error);
    res.status(500).json({ error: error.message });
  }
});

// ========== ENDPOINTS MINERALS (modifiés pour inclure custom fields) ==========

app.get("/minerals", (req, res) => {
  try {
    const stmt = db.prepare("SELECT * FROM minerals ORDER BY id");
    const minerals = [];
    
    while (stmt.step()) {
      const row = stmt.getAsObject();
      
      // Récupérer les images
      const imgStmt = db.prepare("SELECT path FROM images WHERE mineral_id = ? ORDER BY order_index");
      imgStmt.bind([row.id]);
      const images = [];
      while (imgStmt.step()) {
        images.push(imgStmt.getAsObject().path);
      }
      imgStmt.free();
      
      // Récupérer les valeurs des champs personnalisés
      const customStmt = db.prepare("SELECT field_id, value FROM custom_field_values WHERE mineral_id = ?");
      customStmt.bind([row.id]);
      const customData = {};
      while (customStmt.step()) {
        const customRow = customStmt.getAsObject();
        customData[customRow.field_id] = customRow.value;
      }
      customStmt.free();
      
      minerals.push({
        id: row.id,
        name: row.name,
        reference: row.reference,
        locality: row.locality,
        city: row.city,
        department: row.department,
        storage: row.storage,
        dateAcquired: row.date_acquired,
        geolocation: row.geolocation,
        comments: row.comments,
        images: images,
        customData: customData
      });
    }
    stmt.free();
    
    res.json(minerals);
  } catch (error) {
    console.error("Error fetching minerals:", error);
    res.status(500).json({ error: error.message });
  }
});

app.post("/minerals", (req, res) => {
  const minerals = req.body;
  
  try {
    db.run("DELETE FROM minerals");
    db.run("DELETE FROM images");
    db.run("DELETE FROM custom_field_values");

    minerals.forEach(m => {
      db.run(
        "INSERT INTO minerals (id, name, reference, locality, city, department, storage, date_acquired, geolocation, comments) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        [m.id, m.name, m.reference, m.locality || null, m.city || null, m.department || null,
         m.storage || null, m.dateAcquired || null, m.geolocation || null, m.comments || null]
      );

      // Sauvegarder les images
      if (m.images && m.images.length > 0) {
        m.images.forEach((img, idx) => {
          if (img.startsWith('data:image')) {
            const base64Data = img.replace(/^data:image\/\w+;base64,/, '');
            const filename = `mineral_${m.id}_${idx}.jpg`;
            const filepath = path.join(__dirname, 'public', 'photos', filename);
            
            const dir = path.dirname(filepath);
            if (!fs.existsSync(dir)) {
              fs.mkdirSync(dir, { recursive: true });
            }

            fs.writeFileSync(filepath, Buffer.from(base64Data, 'base64'));
            db.run("INSERT INTO images (mineral_id, path, order_index) VALUES (?, ?, ?)",
                   [m.id, `/photos/${filename}`, idx]);
          } else {
            db.run("INSERT INTO images (mineral_id, path, order_index) VALUES (?, ?, ?)",
                   [m.id, img, idx]);
          }
        });
      }
      
      // Sauvegarder les valeurs des champs personnalisés
      if (m.customData) {
        Object.entries(m.customData).forEach(([fieldId, value]) => {
          if (value !== null && value !== undefined && value !== '') {
            db.run(
              "INSERT INTO custom_field_values (mineral_id, field_id, value) VALUES (?, ?, ?)",
              [m.id, fieldId, String(value)]
            );
          }
        });
      }
    });

    saveDB();
    console.log(`✅ Saved ${minerals.length} minerals to database`);
    res.json({ success: true });
  } catch (error) {
    console.error("Error saving minerals:", error);
    res.status(500).json({ error: error.message });
  }
});

// ========== AUTRES ENDPOINTS (inchangés) ==========

app.get("/download-zip", async (req, res) => {
  try {
    const zipPath = path.join(__dirname, "collection_export.zip");
    const output = fs.createWriteStream(zipPath);
    const archive = archiver('zip', { zlib: { level: 9 } });

    output.on('close', function() {
      console.log(`✅ ZIP créé: ${archive.pointer()} bytes`);
      res.download(zipPath, "collection_backup.zip", (err) => {
        if (err) console.error("Error sending zip:", err);
        try { fs.unlinkSync(zipPath); } catch(e) {}
      });
    });

    archive.on('error', function(err) {
      throw err;
    });

    archive.pipe(output);

    if (fs.existsSync(DB_PATH)) {
      archive.file(DB_PATH, { name: 'collection.db' });
    }

    const photosDir = path.join(__dirname, 'public', 'photos');
    if (fs.existsSync(photosDir)) {
      archive.directory(photosDir, 'public/photos');
    }

    await archive.finalize();
  } catch (error) {
    console.error("Error creating zip:", error);
    res.status(500).json({ error: error.message });
  }
});

app.post("/upload-zip", upload.single("file"), async (req, res) => {
  try {
    const zipPath = req.file.path;
    const extractPath = __dirname;

    await fs.createReadStream(zipPath)
      .pipe(unzipper.Extract({ path: extractPath }))
      .promise();

    if (fs.existsSync(DB_PATH)) {
      const SQL = await initSqlJs();
      const buffer = fs.readFileSync(DB_PATH);
      db = new SQL.Database(buffer);
      console.log("📊 Base de données importée et chargée");
    }

    fs.unlinkSync(zipPath);

    res.json({ success: true, message: "Import réussi" });
  } catch (error) {
    console.error("Error importing zip:", error);
    res.status(500).json({ error: error.message });
  }
});

app.get("/download", async (req, res) => {
  try {
    const ExcelJS = require("exceljs");
    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet("Collection");
    
    // Récupérer les champs personnalisés pour les colonnes
    const customFieldsStmt = db.prepare("SELECT id, name FROM custom_fields ORDER BY order_index");
    const customFields = [];
    while (customFieldsStmt.step()) {
      customFields.push(customFieldsStmt.getAsObject());
    }
    customFieldsStmt.free();
    
    // Définir les colonnes de base + colonnes custom
    const baseColumns = [
      "ID","Nom","Référence","Localité","Ville","Département",
      "Rangement","Date d'acquisition","Géolocalisation","Commentaires","Images"
    ];
    
    const headers = [...baseColumns, ...customFields.map(f => f.name)];
    sheet.addRow(headers);

    const stmt = db.prepare("SELECT * FROM minerals");
    while (stmt.step()) {
      const m = stmt.getAsObject();
      
      const imgStmt = db.prepare("SELECT path FROM images WHERE mineral_id = ? ORDER BY order_index");
      imgStmt.bind([m.id]);
      const images = [];
      while (imgStmt.step()) {
        images.push(imgStmt.getAsObject().path);
      }
      imgStmt.free();
      
      // Récupérer les valeurs custom
      const customValues = {};
      const customStmt = db.prepare("SELECT field_id, value FROM custom_field_values WHERE mineral_id = ?");
      customStmt.bind([m.id]);
      while (customStmt.step()) {
        const row = customStmt.getAsObject();
        customValues[row.field_id] = row.value;
      }
      customStmt.free();
      
      const baseData = [
        m.id, m.name, m.reference, m.locality, m.city, m.department,
        m.storage, m.date_acquired, m.geolocation, m.comments,
        JSON.stringify(images)
      ];
      
      const customData = customFields.map(f => customValues[f.id] || '');
      
      sheet.addRow([...baseData, ...customData]);
    }
    stmt.free();

    const filepath = path.join(__dirname, "collection_export.xlsx");
    await workbook.xlsx.writeFile(filepath);
    res.download(filepath, "collection.xlsx");
  } catch (error) {
    console.error("Error exporting:", error);
    res.status(500).json({ error: error.message });
  }
});

app.post("/upload", upload.single("file"), async (req, res) => {
  try {
    const ExcelJS = require("exceljs");
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(req.file.path);
    const sheet = workbook.worksheets[0];

    // Lire la première ligne pour identifier les colonnes custom
    const headerRow = sheet.getRow(1);
    const baseColumnCount = 11;
    const customFieldNames = [];
    
    for (let i = baseColumnCount + 1; i <= headerRow.cellCount; i++) {
      const cellValue = headerRow.getCell(i).value;
      if (cellValue) {
        customFieldNames.push({ index: i, name: cellValue });
      }
    }

    const minerals = [];
    sheet.eachRow((row, rowNumber) => {
      if (rowNumber === 1) return;
      
      let images = [];
      const imagesCell = row.getCell(11).value;
      if (imagesCell) {
        try {
          images = JSON.parse(imagesCell);
        } catch(e) {
          images = imagesCell.split(";").filter(img => img);
        }
      }
      
      // Récupérer les valeurs custom
      const customData = {};
      customFieldNames.forEach(field => {
        const value = row.getCell(field.index).value;
        if (value !== null && value !== undefined) {
          // Trouver l'ID du champ par son nom
          const fieldStmt = db.prepare("SELECT id FROM custom_fields WHERE name = ?");
          fieldStmt.bind([field.name]);
          if (fieldStmt.step()) {
            const fieldId = fieldStmt.getAsObject().id;
            customData[fieldId] = String(value);
          }
          fieldStmt.free();
        }
      });
      
      minerals.push({
        id: row.getCell(1).value,
        name: row.getCell(2).value,
        reference: row.getCell(3).value,
        locality: row.getCell(4).value,
        city: row.getCell(5).value,
        department: row.getCell(6).value,
        storage: row.getCell(7).value,
        dateAcquired: formatDateEU(row.getCell(8).value),
        geolocation: row.getCell(9).value,
        comments: row.getCell(10).value,
        images: images,
        customData: customData
      });
    });

    db.run("DELETE FROM minerals");
    db.run("DELETE FROM images");
    db.run("DELETE FROM custom_field_values");

    minerals.forEach(m => {
      db.run(
        "INSERT INTO minerals (id, name, reference, locality, city, department, storage, date_acquired, geolocation, comments) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        [m.id, m.name, m.reference, m.locality || null, m.city || null, m.department || null,
         m.storage || null, m.dateAcquired || null, m.geolocation || null, m.comments || null]
      );

      if (m.images && m.images.length > 0) {
        m.images.forEach((img, idx) => {
          if (img.startsWith('data:image')) {
            const base64Data = img.replace(/^data:image\/\w+;base64,/, '');
            const filename = `mineral_${m.id}_${idx}.jpg`;
            const filepath = path.join(__dirname, 'public', 'photos', filename);
            
            const dir = path.dirname(filepath);
            if (!fs.existsSync(dir)) {
              fs.mkdirSync(dir, { recursive: true });
            }

            fs.writeFileSync(filepath, Buffer.from(base64Data, 'base64'));
            db.run("INSERT INTO images (mineral_id, path, order_index) VALUES (?, ?, ?)",
                   [m.id, `/photos/${filename}`, idx]);
          } else {
            db.run("INSERT INTO images (mineral_id, path, order_index) VALUES (?, ?, ?)",
                   [m.id, img, idx]);
          }
        });
      }
      
      // Sauvegarder les custom fields
      if (m.customData) {
        Object.entries(m.customData).forEach(([fieldId, value]) => {
          if (value) {
            db.run(
              "INSERT INTO custom_field_values (mineral_id, field_id, value) VALUES (?, ?, ?)",
              [m.id, fieldId, value]
            );
          }
        });
      }
    });

    saveDB();
    fs.unlinkSync(req.file.path);
    res.json({ success: true });
  } catch (error) {
    console.error("Error importing:", error);
    res.status(500).json({ error: error.message });
  }
});

const PORT = 3000;
initDB().then(() => {
  app.listen(PORT, () => {
    console.log(`✅ Serveur lancé sur http://localhost:${PORT}`);
    console.log(`📊 Base de données: ${DB_PATH}`);
  });
});