const express = require('express');
const cors = require('cors');
const bodyParser = require('body-parser');
const fs = require('fs');
const path = require('path');
const crypto = require('crypto');

const app = express();
const PORT = 3737;

// ─── LICENSE KEY ──────────────────────────────────────────────────────────────
const LICENSE_SECRET = 'PODOCLINIC2024BELA';

function verifyLicense(key) {
  if (!key) return false;
  try {
    const parts = key.toUpperCase().split('-');
    if (parts.length !== 5) return false;
    const data = parts.slice(0, 4).join('');
    const check = parts[4];
    const hash = crypto.createHmac('sha256', LICENSE_SECRET)
      .update(data)
      .digest('hex')
      .toUpperCase()
      .substring(0, 4);
    return hash === check;
  } catch { return false; }
}

// ─── DATA DIRECTORY (configurable, default \podibela) ─────────────────────────
let dataDir = process.env.PODOCLINIC_DATA || path.join(process.cwd(), 'data');
const configFile = path.join(process.cwd(), 'appconfig.json');

function loadAppConfig() {
  try {
    if (fs.existsSync(configFile)) {
      const cfg = JSON.parse(fs.readFileSync(configFile, 'utf-8'));
      if (cfg.dataDir) dataDir = cfg.dataDir;
      return cfg;
    }
  } catch (e) {}
  return {};
}

function saveAppConfig(cfg) {
  fs.writeFileSync(configFile, JSON.stringify(cfg, null, 2), 'utf-8');
}

function ensureDataDir() {
  if (!fs.existsSync(dataDir)) {
    fs.mkdirSync(dataDir, { recursive: true });
  }
}

// ─── DB HELPERS ───────────────────────────────────────────────────────────────
const KEYS = [
  'patients','appointments','users','services','attendances',
  'payments','cashbox','inventory','commissions','suppliers',
  'salesProducts','salesServices','company','categories',
  'photos','laboratory','documents','commission_scheme',
  'modulePerms','partialCuts','memberships','salesOrders'
];

function dbFile(key) {
  return path.join(dataDir, `${key}.json`);
}

function dbGet(key) {
  try {
    ensureDataDir();
    const f = dbFile(key);
    if (!fs.existsSync(f)) return null;
    return JSON.parse(fs.readFileSync(f, 'utf-8'));
  } catch (e) {
    console.error(`dbGet error for ${key}:`, e.message);
    return null;
  }
}

function dbSet(key, value) {
  try {
    ensureDataDir();
    fs.writeFileSync(dbFile(key), JSON.stringify(value, null, 2), 'utf-8');
    return true;
  } catch (e) {
    console.error(`dbSet error for ${key}:`, e.message);
    return false;
  }
}

// ─── DEFAULTS ─────────────────────────────────────────────────────────────────
const DEF_USERS = [
  {id:1,username:"admin",password:"admin123",role:"Administrador",name:"Admin Principal",active:true,podoFunctions:["Atención Clínica","Venta de Productos","Recepción","Laboratorio","Control de Fotos","Documentos","Inventarios","Comisiones"]},
  {id:2,username:"podologa",password:"podo123",role:"Podólogo",name:"Dra. Bela Ramos",active:true,podoFunctions:["Atención Clínica","Laboratorio","Control de Fotos"]},
  {id:3,username:"recep",password:"recep123",role:"Recepcionista",name:"Lucía Torres",active:true,podoFunctions:["Recepción","Venta de Productos"]},
];
const DEF_SERVICES = [
  {id:1,name:"Consulta General",price:50,duration:30,category:"Consulta",active:true},
  {id:2,name:"Tratamiento de Uñas",price:80,duration:45,category:"Tratamiento",active:true},
  {id:3,name:"Podología Quirúrgica",price:150,duration:60,category:"Quirúrgico",active:true},
  {id:4,name:"Plantillas Ortopédicas",price:200,duration:60,category:"Ortopedia",active:true},
  {id:5,name:"Reflexología",price:120,duration:60,category:"Terapia",active:true},
  {id:6,name:"Onicomicosis",price:90,duration:45,category:"Tratamiento",active:true},
];
const DEF_CATEGORIES = [
  {id:1,name:"Biomecánica",color:"#2563eb",type:"service"},
  {id:2,name:"Masajes y Terapias",color:"#2ea87e",type:"service"},
  {id:3,name:"Medicina Alternativa",color:"#7c3aed",type:"service"},
  {id:4,name:"Ortesis y Plantillas",color:"#f59223",type:"service"},
  {id:5,name:"Servicios Podológicos",color:"#4a9d8f",type:"service"},
  {id:6,name:"Tratamientos para la piel",color:"#e05050",type:"product"},
  {id:7,name:"Consulta",color:"#0891b2",type:"service"},
  {id:8,name:"Quirúrgico",color:"#dc2626",type:"service"},
];
const DEF_COMPANY = {name:"Podología Más Bela",logo:null,address:"",phone:"",email:"",currency:"S/",currencyCode:"PEN"};

function initDefaults() {
  if (!dbGet('users')) dbSet('users', DEF_USERS);
  if (!dbGet('services')) dbSet('services', DEF_SERVICES);
  if (!dbGet('categories')) dbSet('categories', DEF_CATEGORIES);
  if (!dbGet('company')) dbSet('company', DEF_COMPANY);
  if (!dbGet('suppliers')) dbSet('suppliers', []);
  if (!dbGet('salesProducts')) dbSet('salesProducts', []);
  if (!dbGet('salesServices')) dbSet('salesServices', []);
  if (!dbGet('patients')) dbSet('patients', []);
  if (!dbGet('appointments')) dbSet('appointments', []);
  if (!dbGet('attendances')) dbSet('attendances', []);
  if (!dbGet('inventory')) dbSet('inventory', []);
  if (!dbGet('payments')) dbSet('payments', []);
  if (!dbGet('cashbox')) dbSet('cashbox', []);
  if (!dbGet('photos')) dbSet('photos', []);
  if (!dbGet('laboratory')) dbSet('laboratory', []);
  if (!dbGet('documents')) dbSet('documents', []);
  if (!dbGet('commission_scheme')) dbSet('commission_scheme', {pct:15});
}

// ─── MIDDLEWARE ───────────────────────────────────────────────────────────────
app.use(cors());
app.use(bodyParser.json({ limit: '50mb' }));
app.use(bodyParser.urlencoded({ extended: true, limit: '50mb' }));

// Serve frontend
app.use(express.static(path.join(__dirname, '..', 'frontend')));

// ─── LICENSE MIDDLEWARE ───────────────────────────────────────────────────────
function checkLicense(req, res, next) {
  const appCfg = loadAppConfig();
  if (!appCfg.license || !verifyLicense(appCfg.license)) {
    if (req.path === '/api/license/verify' || req.path === '/api/license/activate') {
      return next();
    }
    if (req.path.startsWith('/api/')) {
      return res.status(403).json({ error: 'Licencia inválida o no activada', requireLicense: true });
    }
  }
  next();
}
app.use(checkLicense);

// ─── ROUTES: LICENSE ──────────────────────────────────────────────────────────
app.post('/api/license/activate', (req, res) => {
  const { key } = req.body;
  if (!key) return res.status(400).json({ error: 'Clave requerida' });
  if (!verifyLicense(key)) return res.status(400).json({ error: 'Clave de licencia inválida' });
  const cfg = loadAppConfig();
  cfg.license = key.toUpperCase();
  saveAppConfig(cfg);
  res.json({ ok: true, message: 'Licencia activada correctamente' });
});

app.get('/api/license/verify', (req, res) => {
  const cfg = loadAppConfig();
  const valid = verifyLicense(cfg.license);
  res.json({ valid, key: valid ? cfg.license : null });
});

// ─── ROUTES: CONFIG ───────────────────────────────────────────────────────────
app.get('/api/config/datadir', (req, res) => {
  const cfg = loadAppConfig();
  res.json({ dataDir: cfg.dataDir || dataDir });
});

app.post('/api/config/datadir', (req, res) => {
  const { dir } = req.body;
  if (!dir) return res.status(400).json({ error: 'Directorio requerido' });
  const cfg = loadAppConfig();
  cfg.dataDir = dir;
  dataDir = dir;
  saveAppConfig(cfg);
  ensureDataDir();
  initDefaults();
  res.json({ ok: true, dataDir: dir });
});

// ─── ROUTES: GENERIC CRUD ─────────────────────────────────────────────────────
KEYS.forEach(key => {
  // GET all
  app.get(`/api/${key}`, (req, res) => {
    const data = dbGet(key);
    res.json(data || []);
  });

  // POST (add item)
  app.post(`/api/${key}`, (req, res) => {
    const item = req.body;
    const list = dbGet(key) || [];
    if (Array.isArray(list)) {
      const newId = list.length === 0 ? 1 : list.reduce((max, x) => Math.max(max, x.id || 0), 0) + 1;
      const newItem = { ...item, id: newId };
      list.push(newItem);
      dbSet(key, list);
      res.json(newItem);
    } else {
      // object (like company, commission_scheme)
      dbSet(key, item);
      res.json(item);
    }
  });

  // PUT (replace all / update single)
  app.put(`/api/${key}`, (req, res) => {
    const data = req.body;
    dbSet(key, data);
    res.json(data);
  });

  // PUT single item by id
  app.put(`/api/${key}/:id`, (req, res) => {
    const id = parseInt(req.params.id);
    const update = req.body;
    const list = dbGet(key) || [];
    const newList = list.map(item => item.id === id ? { ...item, ...update } : item);
    dbSet(key, newList);
    res.json(newList.find(x => x.id === id));
  });

  // DELETE single item by id
  app.delete(`/api/${key}/:id`, (req, res) => {
    const id = parseInt(req.params.id);
    const list = dbGet(key) || [];
    const newList = list.filter(item => item.id !== id);
    dbSet(key, newList);
    res.json({ ok: true });
  });
});

// ─── ROUTE: BACKUP / SAVE ALL ─────────────────────────────────────────────────
app.get('/api/backup', (req, res) => {
  const backup = {};
  KEYS.forEach(k => { backup[k] = dbGet(k); });
  backup._timestamp = new Date().toISOString();
  backup._version = '4.0';
  res.json(backup);
});

app.post('/api/restore', (req, res) => {
  const data = req.body;
  if (!data || typeof data !== 'object') {
    return res.status(400).json({ error: 'Datos de restauración inválidos' });
  }
  KEYS.forEach(k => { if (data[k] !== undefined) dbSet(k, data[k]); });
  res.json({ ok: true });
});

// ─── ROUTE: MYSQL BACKUP ──────────────────────────────────────────────────────
app.get('/api/backup/mysql', (req, res) => {
  try {
    const ts = new Date().toISOString();
    let sql = '';

    sql += `-- ============================================================\n`;
    sql += `-- PodoCLINIC v4.0 — Backup MySQL/PHP\n`;
    sql += `-- Generado: ${ts}\n`;
    sql += `-- ============================================================\n\n`;
    sql += `SET NAMES utf8mb4;\nSET CHARACTER SET utf8mb4;\nSET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\nSET time_zone = '+00:00';\n\n`;

    // ── helpers ──────────────────────────────────────────────────────────────
    const esc = v => {
      if (v === null || v === undefined) return 'NULL';
      if (typeof v === 'boolean') return v ? '1' : '0';
      if (typeof v === 'number') return String(v);
      if (typeof v === 'object') return `'${JSON.stringify(v).replace(/\\/g,'\\\\').replace(/'/g,"\\'")}'`;
      return `'${String(v).replace(/\\/g,'\\\\').replace(/'/g,"\\'")}'`;
    };
    const col = n => `\`${n}\``;

    // ── COMPANY ──────────────────────────────────────────────────────────────
    const company = dbGet('company') || {};
    sql += `-- ------------------------------------------------------------\n-- Tabla: company\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`company\`;\n`;
    sql += `CREATE TABLE \`company\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`name\` VARCHAR(200) DEFAULT NULL,\n`;
    sql += `  \`address\` VARCHAR(300) DEFAULT NULL,\n`;
    sql += `  \`phone\` VARCHAR(50) DEFAULT NULL,\n`;
    sql += `  \`email\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`currency\` VARCHAR(10) DEFAULT NULL,\n`;
    sql += `  \`currency_code\` VARCHAR(10) DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    sql += `INSERT INTO \`company\` (\`name\`,\`address\`,\`phone\`,\`email\`,\`currency\`,\`currency_code\`) VALUES\n`;
    sql += `(${esc(company.name)},${esc(company.address)},${esc(company.phone)},${esc(company.email)},${esc(company.currency)},${esc(company.currencyCode)});\n\n`;

    // ── USERS ─────────────────────────────────────────────────────────────────
    const users = dbGet('users') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: users\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`users\`;\n`;
    sql += `CREATE TABLE \`users\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`username\` VARCHAR(100) NOT NULL,\n`;
    sql += `  \`password\` VARCHAR(255) NOT NULL,\n`;
    sql += `  \`name\` VARCHAR(200) DEFAULT NULL,\n`;
    sql += `  \`role\` VARCHAR(80) DEFAULT NULL,\n`;
    sql += `  \`active\` TINYINT(1) DEFAULT 1,\n`;
    sql += `  \`podo_functions\` TEXT DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (users.length) {
      sql += `INSERT INTO \`users\` (\`id\`,\`username\`,\`password\`,\`name\`,\`role\`,\`active\`,\`podo_functions\`) VALUES\n`;
      sql += users.map(u => `(${esc(u.id)},${esc(u.username)},${esc(u.password)},${esc(u.name)},${esc(u.role)},${esc(u.active?1:0)},${esc(Array.isArray(u.podoFunctions)?u.podoFunctions.join('|'):'')})`).join(',\n') + ';\n\n';
    }

    // ── CATEGORIES ────────────────────────────────────────────────────────────
    const categories = dbGet('categories') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: categories\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`categories\`;\n`;
    sql += `CREATE TABLE \`categories\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`name\` VARCHAR(150) NOT NULL,\n`;
    sql += `  \`color\` VARCHAR(20) DEFAULT NULL,\n`;
    sql += `  \`type\` VARCHAR(40) DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (categories.length) {
      sql += `INSERT INTO \`categories\` (\`id\`,\`name\`,\`color\`,\`type\`) VALUES\n`;
      sql += categories.map(c => `(${esc(c.id)},${esc(c.name)},${esc(c.color)},${esc(c.type)})`).join(',\n') + ';\n\n';
    }

    // ── SERVICES ──────────────────────────────────────────────────────────────
    const services = dbGet('services') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: services\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`services\`;\n`;
    sql += `CREATE TABLE \`services\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`name\` VARCHAR(200) NOT NULL,\n`;
    sql += `  \`price\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`duration\` INT DEFAULT 0,\n`;
    sql += `  \`category\` VARCHAR(100) DEFAULT NULL,\n`;
    sql += `  \`code\` VARCHAR(50) DEFAULT NULL,\n`;
    sql += `  \`active\` TINYINT(1) DEFAULT 1,\n`;
    sql += `  PRIMARY KEY (\`id\`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (services.length) {
      sql += `INSERT INTO \`services\` (\`id\`,\`name\`,\`price\`,\`duration\`,\`category\`,\`code\`,\`active\`) VALUES\n`;
      sql += services.map(s => `(${esc(s.id)},${esc(s.name)},${esc(parseFloat(s.price)||0)},${esc(parseInt(s.duration)||0)},${esc(s.category)},${esc(s.code||null)},${esc(s.active?1:0)})`).join(',\n') + ';\n\n';
    }

    // ── SUPPLIERS ─────────────────────────────────────────────────────────────
    const suppliers = dbGet('suppliers') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: suppliers\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`suppliers\`;\n`;
    sql += `CREATE TABLE \`suppliers\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`name\` VARCHAR(200) NOT NULL,\n`;
    sql += `  \`ruc\` VARCHAR(20) DEFAULT NULL,\n`;
    sql += `  \`contact\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`phone\` VARCHAR(50) DEFAULT NULL,\n`;
    sql += `  \`email\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`address\` VARCHAR(300) DEFAULT NULL,\n`;
    sql += `  \`notes\` TEXT DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (suppliers.length) {
      sql += `INSERT INTO \`suppliers\` (\`id\`,\`name\`,\`ruc\`,\`contact\`,\`phone\`,\`email\`,\`address\`,\`notes\`) VALUES\n`;
      sql += suppliers.map(s => `(${esc(s.id)},${esc(s.name)},${esc(s.ruc)},${esc(s.contact)},${esc(s.phone)},${esc(s.email)},${esc(s.address)},${esc(s.notes)})`).join(',\n') + ';\n\n';
    }

    // ── INVENTORY ─────────────────────────────────────────────────────────────
    const inventory = dbGet('inventory') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: inventory\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`inventory\`;\n`;
    sql += `CREATE TABLE \`inventory\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`name\` VARCHAR(200) NOT NULL,\n`;
    sql += `  \`code\` VARCHAR(60) DEFAULT NULL,\n`;
    sql += `  \`category\` VARCHAR(100) DEFAULT NULL,\n`;
    sql += `  \`stock\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`min_stock\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`unit\` VARCHAR(40) DEFAULT NULL,\n`;
    sql += `  \`price\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`cost\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`description\` TEXT DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (inventory.length) {
      sql += `INSERT INTO \`inventory\` (\`id\`,\`name\`,\`code\`,\`category\`,\`stock\`,\`min_stock\`,\`unit\`,\`price\`,\`cost\`,\`description\`) VALUES\n`;
      sql += inventory.map(i => `(${esc(i.id)},${esc(i.name)},${esc(i.code)},${esc(i.category)},${esc(parseFloat(i.stock)||0)},${esc(parseFloat(i.minStock)||0)},${esc(i.unit)},${esc(parseFloat(i.price)||0)},${esc(parseFloat(i.cost)||0)},${esc(i.description)})`).join(',\n') + ';\n\n';
    }

    // ── PATIENTS ──────────────────────────────────────────────────────────────
    const patients = dbGet('patients') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: patients (${patients.length} registros)\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`patients\`;\n`;
    sql += `CREATE TABLE \`patients\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`registration_date\` DATE DEFAULT NULL,\n`;
    sql += `  \`first_name\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`last_name\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`dni\` VARCHAR(20) DEFAULT NULL,\n`;
    sql += `  \`dob\` DATE DEFAULT NULL,\n`;
    sql += `  \`sex\` CHAR(1) DEFAULT NULL,\n`;
    sql += `  \`phone\` VARCHAR(50) DEFAULT NULL,\n`;
    sql += `  \`email\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`address\` VARCHAR(300) DEFAULT NULL,\n`;
    sql += `  \`occupation\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`weight\` DECIMAL(6,2) DEFAULT NULL,\n`;
    sql += `  \`height\` DECIMAL(6,2) DEFAULT NULL,\n`;
    sql += `  \`shoe_size\` VARCHAR(10) DEFAULT NULL,\n`;
    sql += `  \`shoe_type\` VARCHAR(100) DEFAULT NULL,\n`;
    sql += `  \`pathologies\` TEXT DEFAULT NULL,\n`;
    sql += `  \`path_ant\` TEXT DEFAULT NULL,\n`;
    sql += `  \`smoke\` VARCHAR(5) DEFAULT NULL,\n`;
    sql += `  \`alcohol\` VARCHAR(5) DEFAULT NULL,\n`;
    sql += `  \`meds\` TEXT DEFAULT NULL,\n`;
    sql += `  \`notes\` TEXT DEFAULT NULL,\n`;
    sql += `  \`status\` VARCHAR(20) DEFAULT 'active',\n`;
    sql += `  \`patient_type\` VARCHAR(30) DEFAULT NULL,\n`;
    sql += `  \`emergency_contact\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`emergency_phone\` VARCHAR(50) DEFAULT NULL,\n`;
    sql += `  \`attended_by\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`),\n`;
    sql += `  KEY \`idx_dni\` (\`dni\`),\n`;
    sql += `  KEY \`idx_name\` (\`last_name\`,\`first_name\`)\n`;
    sql += `) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (patients.length) {
      const CHUNK = 200;
      for (let i = 0; i < patients.length; i += CHUNK) {
        const chunk = patients.slice(i, i + CHUNK);
        sql += `INSERT INTO \`patients\` (\`id\`,\`registration_date\`,\`first_name\`,\`last_name\`,\`dni\`,\`dob\`,\`sex\`,\`phone\`,\`email\`,\`address\`,\`occupation\`,\`weight\`,\`height\`,\`shoe_size\`,\`shoe_type\`,\`pathologies\`,\`path_ant\`,\`smoke\`,\`alcohol\`,\`meds\`,\`notes\`,\`status\`,\`patient_type\`,\`emergency_contact\`,\`emergency_phone\`,\`attended_by\`) VALUES\n`;
        sql += chunk.map(p => `(${esc(p.id)},${esc(p.registrationDate||null)},${esc(p.firstName)},${esc(p.lastName)},${esc(p.dni)},${esc(p.dob||null)},${esc(p.sex)},${esc(p.phone)},${esc(p.email)},${esc(p.address)},${esc(p.occupation)},${esc(p.weight?parseFloat(p.weight):null)},${esc(p.height?parseFloat(p.height):null)},${esc(p.shoeSize)},${esc(p.shoeType)},${esc(Array.isArray(p.pathologies)?p.pathologies.join('|'):'')},${esc(Array.isArray(p.pathAnt)?p.pathAnt.join('|'):'')},${esc(p.smoke)},${esc(p.alcohol)},${esc(p.meds)},${esc(p.notes)},${esc(p.status||'active')},${esc(p.patientType)},${esc(p.emergencyContact)},${esc(p.emergencyPhone)},${esc(p.attendedBy)})`).join(',\n') + ';\n\n';
      }
    }

    // ── APPOINTMENTS ──────────────────────────────────────────────────────────
    const appointments = dbGet('appointments') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: appointments (${appointments.length} registros)\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`appointments\`;\n`;
    sql += `CREATE TABLE \`appointments\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`patient_id\` VARCHAR(20) DEFAULT NULL,\n`;
    sql += `  \`date\` DATE DEFAULT NULL,\n`;
    sql += `  \`time\` VARCHAR(10) DEFAULT NULL,\n`;
    sql += `  \`service\` VARCHAR(200) DEFAULT NULL,\n`;
    sql += `  \`podologa\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`notes\` TEXT DEFAULT NULL,\n`;
    sql += `  \`status\` VARCHAR(30) DEFAULT NULL,\n`;
    sql += `  \`created_by\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`),\n`;
    sql += `  KEY \`idx_date\` (\`date\`),\n`;
    sql += `  KEY \`idx_patient\` (\`patient_id\`)\n`;
    sql += `) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (appointments.length) {
      const CHUNK = 200;
      for (let i = 0; i < appointments.length; i += CHUNK) {
        const chunk = appointments.slice(i, i + CHUNK);
        sql += `INSERT INTO \`appointments\` (\`id\`,\`patient_id\`,\`date\`,\`time\`,\`service\`,\`podologa\`,\`notes\`,\`status\`,\`created_by\`) VALUES\n`;
        sql += chunk.map(a => `(${esc(a.id)},${esc(a.patientId)},${esc(a.date||null)},${esc(a.time)},${esc(a.service)},${esc(a.podologa)},${esc(a.notes)},${esc(a.status)},${esc(a.createdBy)})`).join(',\n') + ';\n\n';
      }
    }

    // ── ATTENDANCES ───────────────────────────────────────────────────────────
    const attendances = dbGet('attendances') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: attendances (${attendances.length} registros)\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`attendances\`;\n`;
    sql += `CREATE TABLE \`attendances\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`patient_id\` VARCHAR(20) DEFAULT NULL,\n`;
    sql += `  \`patient_name\` VARCHAR(250) DEFAULT NULL,\n`;
    sql += `  \`date\` DATE DEFAULT NULL,\n`;
    sql += `  \`service\` VARCHAR(200) DEFAULT NULL,\n`;
    sql += `  \`price\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`pay_method\` VARCHAR(50) DEFAULT NULL,\n`;
    sql += `  \`pay_status\` VARCHAR(30) DEFAULT NULL,\n`;
    sql += `  \`closed\` TINYINT(1) DEFAULT 0,\n`;
    sql += `  \`attended_by\` VARCHAR(150) DEFAULT NULL,\n`;
    sql += `  \`order_code\` VARCHAR(50) DEFAULT NULL,\n`;
    sql += `  \`type\` VARCHAR(40) DEFAULT NULL,\n`;
    sql += `  \`order_items\` TEXT DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`),\n`;
    sql += `  KEY \`idx_date\` (\`date\`),\n`;
    sql += `  KEY \`idx_patient\` (\`patient_id\`)\n`;
    sql += `) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (attendances.length) {
      const CHUNK = 200;
      for (let i = 0; i < attendances.length; i += CHUNK) {
        const chunk = attendances.slice(i, i + CHUNK);
        sql += `INSERT INTO \`attendances\` (\`id\`,\`patient_id\`,\`patient_name\`,\`date\`,\`service\`,\`price\`,\`pay_method\`,\`pay_status\`,\`closed\`,\`attended_by\`,\`order_code\`,\`type\`,\`order_items\`) VALUES\n`;
        sql += chunk.map(a => `(${esc(a.id)},${esc(a.patientId)},${esc(a.patientName)},${esc(a.date||null)},${esc(a.service)},${esc(parseFloat(a.price)||0)},${esc(a.payMethod)},${esc(a.payStatus)},${esc(a.closed?1:0)},${esc(a.attendedBy)},${esc(a.orderCode)},${esc(a.type)},${esc(a.orderItems?JSON.stringify(a.orderItems):null)})`).join(',\n') + ';\n\n';
      }
    }

    // ── SALES PRODUCTS ────────────────────────────────────────────────────────
    const salesProducts = dbGet('salesProducts') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: sales_products (${salesProducts.length} registros)\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`sales_products\`;\n`;
    sql += `CREATE TABLE \`sales_products\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`name\` VARCHAR(200) NOT NULL,\n`;
    sql += `  \`code\` VARCHAR(60) DEFAULT NULL,\n`;
    sql += `  \`supplier\` VARCHAR(200) DEFAULT NULL,\n`;
    sql += `  \`category\` VARCHAR(100) DEFAULT NULL,\n`;
    sql += `  \`stock\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`min_stock\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`cost\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`price\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`description\` TEXT DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (salesProducts.length) {
      sql += `INSERT INTO \`sales_products\` (\`id\`,\`name\`,\`code\`,\`supplier\`,\`category\`,\`stock\`,\`min_stock\`,\`cost\`,\`price\`,\`description\`) VALUES\n`;
      sql += salesProducts.map(p => `(${esc(p.id)},${esc(p.name)},${esc(p.code)},${esc(p.supplier)},${esc(p.category)},${esc(parseFloat(p.stock)||0)},${esc(parseFloat(p.minStock)||0)},${esc(parseFloat(p.cost)||0)},${esc(parseFloat(p.price)||0)},${esc(p.description)})`).join(',\n') + ';\n\n';
    }

    // ── SALES SERVICES ────────────────────────────────────────────────────────
    const salesServices = dbGet('salesServices') || [];
    sql += `-- ------------------------------------------------------------\n-- Tabla: sales_services (${salesServices.length} registros)\n-- ------------------------------------------------------------\n`;
    sql += `DROP TABLE IF EXISTS \`sales_services\`;\n`;
    sql += `CREATE TABLE \`sales_services\` (\n`;
    sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
    sql += `  \`name\` VARCHAR(200) NOT NULL,\n`;
    sql += `  \`code\` VARCHAR(60) DEFAULT NULL,\n`;
    sql += `  \`category\` VARCHAR(100) DEFAULT NULL,\n`;
    sql += `  \`price\` DECIMAL(10,2) DEFAULT 0,\n`;
    sql += `  \`description\` TEXT DEFAULT NULL,\n`;
    sql += `  PRIMARY KEY (\`id\`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
    if (salesServices.length) {
      sql += `INSERT INTO \`sales_services\` (\`id\`,\`name\`,\`code\`,\`category\`,\`price\`,\`description\`) VALUES\n`;
      sql += salesServices.map(s => `(${esc(s.id)},${esc(s.name)},${esc(s.code)},${esc(s.category)},${esc(parseFloat(s.price)||0)},${esc(s.description)})`).join(',\n') + ';\n\n';
    }

    // ── MEMBERSHIPS ───────────────────────────────────────────────────────────
    const memberships = dbGet('memberships') || [];
    if (Array.isArray(memberships) && memberships.length) {
      sql += `-- ------------------------------------------------------------\n-- Tabla: memberships\n-- ------------------------------------------------------------\n`;
      sql += `DROP TABLE IF EXISTS \`memberships\`;\n`;
      sql += `CREATE TABLE \`memberships\` (\n`;
      sql += `  \`id\` INT UNSIGNED NOT NULL AUTO_INCREMENT,\n`;
      sql += `  \`name\` VARCHAR(200) DEFAULT NULL,\n`;
      sql += `  \`price\` DECIMAL(10,2) DEFAULT 0,\n`;
      sql += `  \`sessions\` INT DEFAULT 0,\n`;
      sql += `  \`validity_days\` INT DEFAULT 0,\n`;
      sql += `  \`description\` TEXT DEFAULT NULL,\n`;
      sql += `  PRIMARY KEY (\`id\`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n`;
      sql += `INSERT INTO \`memberships\` (\`id\`,\`name\`,\`price\`,\`sessions\`,\`validity_days\`,\`description\`) VALUES\n`;
      sql += memberships.map(m => `(${esc(m.id)},${esc(m.name)},${esc(parseFloat(m.price)||0)},${esc(parseInt(m.sessions)||0)},${esc(parseInt(m.validityDays)||0)},${esc(m.description)})`).join(',\n') + ';\n\n';
    }

    // ── FOOTER ────────────────────────────────────────────────────────────────
    sql += `-- ============================================================\n`;
    sql += `-- Fin del backup MySQL — PodoCLINIC v4.0\n`;
    sql += `-- Pacientes: ${patients.length} | Atenciones: ${attendances.length} | Citas: ${appointments.length}\n`;
    sql += `-- ============================================================\n`;

    res.setHeader('Content-Type', 'application/sql; charset=utf-8');
    res.setHeader('Content-Disposition', `attachment; filename="podoclinic_mysql_${new Date().toISOString().slice(0,10)}.sql"`);
    res.send(sql);
  } catch (err) {
    console.error('MySQL backup error:', err);
    res.status(500).json({ error: 'Error generando backup MySQL: ' + err.message });
  }
});

// ─── ROUTE: HEALTH ────────────────────────────────────────────────────────────
app.get('/api/health', (req, res) => {
  res.json({ ok: true, dataDir, version: '4.0', time: new Date().toISOString() });
});

// ─── AI PROXY ────────────────────────────────────────────────────────────────
// Use native fetch (Node 18+) or fall back to node-fetch
let _fetchFn = null;
function getApiFetch() {
  if (_fetchFn) return Promise.resolve(_fetchFn);
  if (typeof globalThis.fetch === 'function') {
    _fetchFn = globalThis.fetch.bind(globalThis);
    return Promise.resolve(_fetchFn);
  }
  try {
    _fetchFn = require('node-fetch');
    return Promise.resolve(_fetchFn);
  } catch(e) {
    return Promise.reject(new Error('No hay módulo fetch disponible. Asegúrese de usar Node.js 18+ o ejecute npm install.'));
  }
}

// Import PDF via AI
app.post('/api/ai/import-pdf', async (req, res) => {
  const { b64, apiKey } = req.body;
  if (!b64) return res.status(400).json({ error: 'PDF data requerido' });
  try {
    const fetch = await getApiFetch();
    const key = apiKey || process.env.ANTHROPIC_API_KEY || '';
    if (!key) return res.status(400).json({ 
      content: [{type:'text',text:'No hay clave API de Claude configurada. Vaya a Configuración → Empresa → Clave API Claude.'}] 
    });
    const prompt = `Extrae los datos de paciente(s) de este documento de historia clínica podológica.
Devuelve SOLO un array JSON con objetos. Campos posibles:
firstName (Nombres), lastName (Apellidos), dni (DNI/Carnet), dob (fecha nacimiento YYYY-MM-DD),
sex (M/F), phone (Celular/Teléfono), email, address (Dirección),
occupation (Ocupación/Profesión), weight (Peso kg), height (Talla cm),
shoeSize (Talla calzado), shoeType (Tipo calzado),
pathologies (array de patologías), pathAnt (array antecedentes),
smoke (Fuma: Si/No), alcohol (Alcohol: Si/No), meds (Medicamentos),
emergencyContact (Contacto emergencia), emergencyPhone (Tel emergencia),
notes (Observaciones/Notas).
Si no encuentras un campo déjalo vacío. Devuelve SOLO el JSON, sin explicaciones.`;
    const r = await fetch('https://api.anthropic.com/v1/messages', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json', 'x-api-key': key, 'anthropic-version': '2023-06-01' },
      body: JSON.stringify({
        model: 'claude-haiku-4-5-20251001',
        max_tokens: 2000,
        messages: [{role:'user',content:[
          {type:'document',source:{type:'base64',media_type:'application/pdf',data:b64}},
          {type:'text',text:prompt}
        ]}]
      })
    });
    const data = await r.json();
    res.json(data);
  } catch(e) {
    res.status(500).json({ error: e.message });
  }
});

app.post('/api/ai/suggest', async (req, res) => {
  const { prompt, apiKey } = req.body;
  if (!prompt) return res.status(400).json({ error: 'Prompt requerido' });
  try {
    const fetch = await getApiFetch();
    // Try with provided apiKey or env var
    const key = apiKey || process.env.ANTHROPIC_API_KEY || '';
    if (!key) return res.status(400).json({ error: 'No hay clave API configurada. Configure ANTHROPIC_API_KEY en el servidor.' });
    const r = await fetch('https://api.anthropic.com/v1/messages', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json', 'x-api-key': key, 'anthropic-version': '2023-06-01' },
      body: JSON.stringify({ model: 'claude-haiku-4-5-20251001', max_tokens: 800, messages: [{ role: 'user', content: prompt }] })
    });
    const data = await r.json();
    res.json(data);
  } catch(e) {
    res.status(500).json({ error: e.message });
  }
});

// ─── FALLBACK ─────────────────────────────────────────────────────────────────
app.get('*', (req, res) => {
  res.sendFile(path.join(__dirname, '..', 'frontend', 'index.html'));
});

// ─── START ────────────────────────────────────────────────────────────────────
loadAppConfig();
ensureDataDir();
initDefaults();

app.listen(PORT, '0.0.0.0', () => {
  const dirDisplay = dataDir.length > 30 ? dataDir.substring(0, 27) + '...' : dataDir.padEnd(30);
  console.log(`\n╔════════════════════════════════════════╗`);
  console.log(`║       PodoCLINIC v4.0 — Backend        ║`);
  console.log(`╠════════════════════════════════════════╣`);
  console.log(`║  URL: http://localhost:${PORT}           ║`);
  console.log(`║  Datos: ${dirDisplay} ║`);
  console.log(`╚════════════════════════════════════════╝\n`);
});
