const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
const dotenv = require('dotenv');

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

function requireEnv(name) {
  const value = process.env[name];
  if (!value) {
    throw new Error(`${name} must be set in database/.env or .env`);
  }
  return value;
}

// Simple DB logger to backend.log
const backendLogPath = path.join(__dirname, 'backend.log');
function logToFile(...args) {
  try {
    const msg = `[${new Date().toISOString()}] ` + args.map(a => (typeof a === 'object' ? JSON.stringify(a) : String(a))).join(' ') + '\n';
    fs.appendFileSync(backendLogPath, msg, { encoding: 'utf8' });
  } catch (err) {
    console.error('Failed to write backend log', err);
  }
}

// Database configuration
const dbConfig = {
  host: process.env.DB_HOST || '192.168.1.113',
  user: process.env.DB_USER || 'deathwatch',
  password: requireEnv('DB_PASSWORD'),
  database: process.env.DB_NAME || 'deathwatch',
  port: Number(process.env.DB_PORT || 3307),
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

// Create connection pool
const pool = mysql.createPool(dbConfig);

const ignoreDuplicateColumn = (error) => {
  if (error.code !== 'ER_DUP_FIELDNAME') throw error;
};

const addColumnIfMissing = async (connection, table, definition) => {
  try {
    await connection.execute(`ALTER TABLE ${table} ADD COLUMN ${definition}`);
  } catch (error) {
    ignoreDuplicateColumn(error);
  }
};

// Create tables
const createTables = async () => {
  try {
    const connection = await pool.getConnection();
    
    // Players table
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS players (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) UNIQUE NOT NULL,
        roller_info JSON DEFAULT ('{}'),
        shop_info JSON DEFAULT ('{}'),
        tab_info JSON DEFAULT ('{}'),
        pw VARCHAR(255) DEFAULT '',
        pw_hash VARCHAR(255) DEFAULT '',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);

    // Sessions table
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS sessions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        session_id VARCHAR(255) UNIQUE NOT NULL,
        data JSON DEFAULT ('{}'),
        expires_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);

    // Rules staging table
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS rules_staging (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title TEXT,
        content TEXT,
        category VARCHAR(255),
        page VARCHAR(255),
        original_json TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // Rules table
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS rules (
        id INT AUTO_INCREMENT PRIMARY KEY,
        rule_id VARCHAR(255) UNIQUE,
        title VARCHAR(500),
        content TEXT,
        summary TEXT,
        examples TEXT,
        page INT,
        page_end INT,
        source VARCHAR(255),
        source_abbr VARCHAR(50),
        category VARCHAR(100),
        tags TEXT,
        aliases TEXT,
        related_rules TEXT,
        source_method VARCHAR(50),
        confidence DECIMAL(4,2),
        midgame_priority INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    await addColumnIfMissing(connection, 'rules', 'summary TEXT');
    await addColumnIfMissing(connection, 'rules', 'examples TEXT');
    await addColumnIfMissing(connection, 'rules', 'page_end INT');
    await addColumnIfMissing(connection, 'rules', 'tags TEXT');
    await addColumnIfMissing(connection, 'rules', 'aliases TEXT');
    await addColumnIfMissing(connection, 'rules', 'related_rules TEXT');
    await addColumnIfMissing(connection, 'rules', 'source_method VARCHAR(50)');
    await addColumnIfMissing(connection, 'rules', 'confidence DECIMAL(4,2)');
    await addColumnIfMissing(connection, 'rules', 'midgame_priority INT DEFAULT 0');

    // Missions table
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS missions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(500) NOT NULL DEFAULT 'Untitled Mission',
        theme VARCHAR(100) NOT NULL DEFAULT 'tyranid',
        scene_count INT NOT NULL DEFAULT 4,
        enemy_count INT NOT NULL DEFAULT 3,
        threat_level VARCHAR(50) NOT NULL DEFAULT 'Medium',
        player_count INT NOT NULL DEFAULT 3,
        scenes JSON DEFAULT ('[]'),
        gm_player VARCHAR(255),
        is_active TINYINT(1) NOT NULL DEFAULT 0,
        current_scene INT NOT NULL DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);

    // Simulation runs table
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS simulations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        mission_id INT,
        mission_name VARCHAR(500),
        players JSON DEFAULT ('[]'),
        result VARCHAR(50),
        xp_earned INT DEFAULT 0,
        total_rounds INT DEFAULT 0,
        total_rolls INT DEFAULT 0,
        success_rate INT DEFAULT 0,
        combat_success_rate INT DEFAULT 0,
        puzzle_success_rate INT DEFAULT 0,
        scene_results JSON DEFAULT ('[]'),
        player_cards JSON DEFAULT ('{}'),
        roll_feed JSON DEFAULT ('[]'),
        story_hooks JSON DEFAULT ('[]'),
        findings JSON DEFAULT ('[]'),
        enemy_profile VARCHAR(50) NOT NULL DEFAULT 'balanced',
        run_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_simulations_run_date (run_date),
        INDEX idx_simulations_mission_id (mission_id)
      )
    `);

    // Shared mission roll log for GM visibility
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS mission_rolls (
        id INT AUTO_INCREMENT PRIMARY KEY,
        mission_id INT,
        player_name VARCHAR(255),
        roll_type VARCHAR(50) NOT NULL DEFAULT 'check',
        scene_index INT NOT NULL DEFAULT 0,
        label VARCHAR(500),
        payload JSON DEFAULT ('{}'),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_mission_rolls_mission_created (mission_id, created_at)
      )
    `);

    // Bestiary table. The active bestiary is stored here first; JSON files are
    // kept as fallback/mirror for local development when MariaDB is unavailable.
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS bestiary (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        book VARCHAR(255),
        page VARCHAR(50),
        pdf VARCHAR(255),
        stats TEXT,
        profile TEXT,
        snippet TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_bestiary_name (name),
        INDEX idx_bestiary_book_page (book, page)
      )
    `);

    await addColumnIfMissing(connection, 'bestiary', 'updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');

    // Error reports table - logged-in users can submit error reports
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS error_reports (
        id INT AUTO_INCREMENT PRIMARY KEY,
        player_name VARCHAR(255) NOT NULL,
        category VARCHAR(100) NOT NULL DEFAULT 'bug',
        title VARCHAR(500) NOT NULL,
        description TEXT NOT NULL,
        page_url VARCHAR(1000) DEFAULT '',
        status VARCHAR(50) NOT NULL DEFAULT 'open',
        resolved_by VARCHAR(255) DEFAULT '',
        resolved_note TEXT DEFAULT '',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        resolved_at TIMESTAMP NULL,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_error_reports_status (status),
        INDEX idx_error_reports_player (player_name)
      )
    `);

    try {
      await connection.execute(`ALTER TABLE missions ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 0`);
    } catch (error) {
      if (error.code !== 'ER_DUP_FIELDNAME') throw error;
    }

    try {
      await connection.execute(`ALTER TABLE missions ADD COLUMN current_scene INT NOT NULL DEFAULT 0`);
    } catch (error) {
      if (error.code !== 'ER_DUP_FIELDNAME') throw error;
    }

    try {
      await connection.execute(`ALTER TABLE simulations ADD COLUMN difficulty_level INT NOT NULL DEFAULT 2`);
    } catch (error) {
      if (error.code !== 'ER_DUP_FIELDNAME') throw error;
    }

    try {
      await connection.execute(`ALTER TABLE simulations ADD COLUMN enemy_profile VARCHAR(50) NOT NULL DEFAULT 'balanced'`);
    } catch (error) {
      if (error.code !== 'ER_DUP_FIELDNAME') throw error;
    }

    try {
      await connection.execute(`ALTER TABLE simulations ADD COLUMN combat_success_rate INT DEFAULT 0`);
    } catch (error) {
      if (error.code !== 'ER_DUP_FIELDNAME') throw error;
    }

    try {
      await connection.execute(`ALTER TABLE simulations ADD COLUMN puzzle_success_rate INT DEFAULT 0`);
    } catch (error) {
      if (error.code !== 'ER_DUP_FIELDNAME') throw error;
    }

    try {
      await connection.execute(`ALTER TABLE simulations ADD COLUMN story_hooks JSON DEFAULT ('[]')`);
    } catch (error) {
      if (error.code !== 'ER_DUP_FIELDNAME') throw error;
    }

    await connection.execute(`CREATE INDEX IF NOT EXISTS idx_missions_created_at ON missions(created_at)`);
    await connection.execute(`CREATE INDEX IF NOT EXISTS idx_missions_theme ON missions(theme)`);

    // Create indexes
    await connection.execute(`CREATE INDEX IF NOT EXISTS idx_players_name ON players(name)`);
    await connection.execute(`CREATE INDEX IF NOT EXISTS idx_sessions_session_id ON sessions(session_id)`);
    await connection.execute(`CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at)`);
    await connection.execute(`CREATE INDEX IF NOT EXISTS idx_rules_staging_category ON rules_staging(category)`);
    await connection.execute(`CREATE INDEX IF NOT EXISTS idx_rules_category ON rules(category)`);

    connection.release();
    console.log('MariaDB tables created successfully');
    logToFile('MariaDB: Tables created successfully');

    // Seed rules from JSON if table is empty
    await seedRulesIfEmpty();
  } catch (error) {
    console.error('Error creating MariaDB tables:', error);
    logToFile('MariaDB: Error creating tables', error);
    throw error;
  }
};

// Seed rules table from rules-database.json if empty
const seedRulesIfEmpty = async () => {
  try {
    const [countRows] = await pool.execute('SELECT COUNT(*) AS cnt FROM rules');
    if (countRows[0].cnt > 0) return;

    const dbJsonPath = path.join(__dirname, 'rules', 'rules-database.json');
    if (!fs.existsSync(dbJsonPath)) {
      logToFile('MariaDB: rules-database.json not found, skipping seed');
      return;
    }

    const data = JSON.parse(fs.readFileSync(dbJsonPath, 'utf8'));
    const rules = data.rules || [];
    if (!rules.length) return;

    const connection = await pool.getConnection();
    for (const rule of rules) {
      await connection.execute(
        `INSERT IGNORE INTO rules
          (rule_id, title, content, summary, examples, page, page_end, source, source_abbr, category,
           tags, aliases, related_rules, source_method, confidence, midgame_priority)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          rule.rule_id || rule.id || null,
          rule.title || '',
          rule.content || '',
          rule.summary || '',
          rule.examples || '',
          rule.page || null,
          rule.pageEnd || rule.page_end || null,
          rule.source || null,
          rule.sourceAbbr || rule.source_abbr || null,
          rule.category || null,
          JSON.stringify(rule.tags || []),
          JSON.stringify(rule.aliases || []),
          JSON.stringify(rule.relatedRules || rule.related_rules || []),
          rule.sourceMethod || rule.source_method || null,
          rule.confidence == null ? null : Number(rule.confidence),
          rule.midgamePriority || rule.midgame_priority || 0
        ]
      );
    }
    connection.release();
    console.log(`MariaDB: Seeded ${rules.length} rules from rules-database.json`);
    logToFile(`MariaDB: Seeded ${rules.length} rules from rules-database.json`);
  } catch (error) {
    console.error('Error seeding rules:', error);
    logToFile('MariaDB: Error seeding rules', error);
  }
};

// Player helpers
const playerHelpers = {
  getAll: async () => {
    try {
      const [rows] = await pool.execute('SELECT * FROM players ORDER BY name');
      return rows.map(row => ({
        id: row.id,
        name: row.name,
        rollerInfo: typeof row.roller_info === 'string' ? JSON.parse(row.roller_info) : row.roller_info,
        shopInfo: typeof row.shop_info === 'string' ? JSON.parse(row.shop_info) : row.shop_info,
        tabInfo: typeof row.tab_info === 'string' ? JSON.parse(row.tab_info) : row.tab_info,
        pw: row.pw || '',
        pwHash: row.pw_hash || '',
        requisitionPoints: row.requisition_points || 0,
        renownLevel: row.renown_level || 'None',
        createdAt: row.created_at,
        updatedAt: row.updated_at,
        _id: row.id
      }));
    } catch (error) {
      logToFile('MariaDB: Error getting all players', error);
      return [];
    }
  },

  getByName: async (name) => {
    try {
      const [rows] = await pool.execute('SELECT * FROM players WHERE name = ?', [name]);
      if (rows.length === 0) return null;
      
      const row = rows[0];
      return {
        id: row.id,
        name: row.name,
        rollerInfo: typeof row.roller_info === 'string' ? JSON.parse(row.roller_info) : row.roller_info,
        shopInfo: typeof row.shop_info === 'string' ? JSON.parse(row.shop_info) : row.shop_info,
        tabInfo: typeof row.tab_info === 'string' ? JSON.parse(row.tab_info) : row.tab_info,
        pw: row.pw || '',
        pwHash: row.pw_hash || '',
        requisitionPoints: row.requisition_points || 0,
        renownLevel: row.renown_level || 'None',
        createdAt: row.created_at,
        updatedAt: row.updated_at,
        _id: row.id
      };
    } catch (error) {
      logToFile('MariaDB: Error getting player by name', name, error);
      return null;
    }
  },

  create: async (playerData) => {
    try {
      const { name, rollerInfo = {}, shopInfo = {}, tabInfo = {}, pw = '', pwHash = '' } = playerData;
      
      const [result] = await pool.execute(
        'INSERT INTO players (name, roller_info, shop_info, tab_info, pw, pw_hash) VALUES (?, ?, ?, ?, ?, ?)',
        [name, JSON.stringify(rollerInfo), JSON.stringify(shopInfo), JSON.stringify(tabInfo), pw, pwHash]
      );
      
      logToFile('MariaDB: Created player', name);
      return result.insertId;
    } catch (error) {
      logToFile('MariaDB: Error creating player', playerData.name, error);
      return null;
    }
  },

  update: async (name, playerData) => {
    try {
      const { rollerInfo, shopInfo, tabInfo, pw, pwHash } = playerData;
      
      const [result] = await pool.execute(
        'UPDATE players SET roller_info = ?, shop_info = ?, tab_info = ?, pw = ?, pw_hash = ?, updated_at = CURRENT_TIMESTAMP WHERE name = ?',
        [
          JSON.stringify(rollerInfo || {}),
          JSON.stringify(shopInfo || {}), 
          JSON.stringify(tabInfo || {}),
          pw || '',
          pwHash || '',
          name
        ]
      );
      
      logToFile('MariaDB: Updated player', name);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error updating player', name, error);
      return false;
    }
  },

  delete: async (name) => {
    try {
      const [result] = await pool.execute('DELETE FROM players WHERE name = ?', [name]);
      logToFile('MariaDB: Deleted player', name);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error deleting player', name, error);
      return false;
    }
  }
};

// Session helpers
const sessionHelpers = {
  create: async (sessionId, data = {}, expiresAt = null) => {
    try {
      const [result] = await pool.execute(
        'INSERT INTO sessions (session_id, data, expires_at) VALUES (?, ?, ?)',
        [sessionId, JSON.stringify(data), expiresAt]
      );
      return result.insertId;
    } catch (error) {
      logToFile('MariaDB: Error creating session', sessionId, error);
      return null;
    }
  },

  get: async (sessionId) => {
    try {
      const [rows] = await pool.execute('SELECT * FROM sessions WHERE session_id = ?', [sessionId]);
      if (rows.length === 0) return null;
      
      const row = rows[0];
      return {
        ...row,
        data: typeof row.data === 'string' ? JSON.parse(row.data) : row.data
      };
    } catch (error) {
      logToFile('MariaDB: Error getting session', sessionId, error);
      return null;
    }
  },

  update: async (sessionId, data) => {
    try {
      const [result] = await pool.execute(
        'UPDATE sessions SET data = ?, updated_at = CURRENT_TIMESTAMP WHERE session_id = ?',
        [JSON.stringify(data), sessionId]
      );
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error updating session', sessionId, error);
      return false;
    }
  },

  delete: async (sessionId) => {
    try {
      const [result] = await pool.execute('DELETE FROM sessions WHERE session_id = ?', [sessionId]);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error deleting session', sessionId, error);
      return false;
    }
  },

  cleanup: async () => {
    try {
      const [result] = await pool.execute('DELETE FROM sessions WHERE expires_at < NOW()');
      logToFile('MariaDB: Cleaned up expired sessions', result.affectedRows);
      return result.affectedRows;
    } catch (error) {
      logToFile('MariaDB: Error cleaning up sessions', error);
      return 0;
    }
  }
};

// Rules helpers
const rulesHelpers = {
  getAll: async () => {
    try {
      const [rows] = await pool.execute('SELECT * FROM rules ORDER BY id');
      return rows;
    } catch (error) {
      logToFile('MariaDB: Error getting all rules', error);
      return [];
    }
  },

  create: async (rule) => {
    try {
      const [result] = await pool.execute(
        `INSERT INTO rules
          (rule_id, title, content, summary, examples, page, page_end, source, source_abbr, category,
           tags, aliases, related_rules, source_method, confidence, midgame_priority)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          rule.rule_id || null,
          rule.title,
          rule.content,
          rule.summary || '',
          rule.examples || '',
          rule.page || null,
          rule.page_end || rule.pageEnd || null,
          rule.source || null,
          rule.source_abbr || rule.sourceAbbr || null,
          rule.category || null,
          JSON.stringify(rule.tags || []),
          JSON.stringify(rule.aliases || []),
          JSON.stringify(rule.related_rules || rule.relatedRules || []),
          rule.source_method || rule.sourceMethod || null,
          rule.confidence == null ? null : Number(rule.confidence),
          rule.midgame_priority || rule.midgamePriority || 0
        ]
      );
      return result.insertId;
    } catch (error) {
      logToFile('MariaDB: Error creating rule', error);
      return null;
    }
  },

  delete: async (id) => {
    try {
      const [result] = await pool.execute('DELETE FROM rules WHERE id = ?', [id]);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error deleting rule', id, error);
      return false;
    }
  }
};

// Rules staging helpers
const stagingHelpers = {
  getAll: async () => {
    try {
      const [rows] = await pool.execute('SELECT * FROM rules_staging ORDER BY id');
      return rows;
    } catch (error) {
      logToFile('MariaDB: Error getting all staging rules', error);
      return [];
    }
  },

  create: async (rule) => {
    try {
      const [result] = await pool.execute(
        'INSERT INTO rules_staging (title, content, category, page, original_json) VALUES (?, ?, ?, ?, ?)',
        [rule.title, rule.content, rule.category || null, rule.page || null, rule.original_json || null]
      );
      return result.insertId;
    } catch (error) {
      logToFile('MariaDB: Error creating staging rule', error);
      return null;
    }
  },

  delete: async (id) => {
    try {
      const [result] = await pool.execute('DELETE FROM rules_staging WHERE id = ?', [id]);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error deleting staging rule', id, error);
      return false;
    }
  },

  moveToRules: async (id) => {
    try {
      const connection = await pool.getConnection();
      await connection.beginTransaction();

      // Get the staging rule
      const [stagingRows] = await connection.execute('SELECT * FROM rules_staging WHERE id = ?', [id]);
      if (stagingRows.length === 0) {
        await connection.rollback();
        connection.release();
        return false;
      }

      const rule = stagingRows[0];
      
      // Insert into rules
      await connection.execute(
        'INSERT INTO rules (rule_id, title, content, page, source, source_abbr, category) VALUES (?, ?, ?, ?, ?, ?, ?)',
        [rule.rule_id || null, rule.title, rule.content, rule.page || null, rule.source || null, rule.source_abbr || null, rule.category || null]
      );

      // Delete from staging
      await connection.execute('DELETE FROM rules_staging WHERE id = ?', [id]);

      await connection.commit();
      connection.release();
      return true;
    } catch (error) {
      logToFile('MariaDB: Error moving staging rule to rules', id, error);
      return false;
    }
  }
};

// Weapons helpers
const weaponsHelpers = {
  getAll: async () => {
    try {
      const [rows] = await pool.execute('SELECT * FROM weapons ORDER BY id');
      return rows;
    } catch (error) {
      logToFile('MariaDB: Error getting all weapons', error);
      return [];
    }
  }
};

// Bestiary helpers
const bestiaryHelpers = {
  getAll: async () => {
    try {
      const [rows] = await pool.execute('SELECT * FROM bestiary ORDER BY id');
      return rows;
    } catch (error) {
      logToFile('MariaDB: Error getting all bestiary', error);
      return [];
    }
  },

  upsert: async (entry) => {
    try {
      const name = entry.bestiaryName || entry.name;
      const book = entry.book || null;
      const page = entry.page == null ? null : String(entry.page);
      const pdf = entry.pdf || null;
      const stats = {
        ...(entry.stats || {}),
        type: entry.type || entry.stats?.type || '',
        affiliation: entry.affiliation || entry.stats?.affiliation || '',
        sourceUrl: entry.sourceUrl || entry.stats?.sourceUrl || ''
      };
      const profile = entry.profile || stats.profile || {};
      const snippet = entry.snippet || '';

      const [existing] = await pool.execute(
        `SELECT id FROM bestiary
         WHERE name = ? AND COALESCE(book, '') = COALESCE(?, '') AND COALESCE(page, '') = COALESCE(?, '')
         LIMIT 1`,
        [name, book, page]
      );

      if (existing.length) {
        await pool.execute(
          `UPDATE bestiary
           SET book = ?, page = ?, pdf = ?, stats = ?, profile = ?, snippet = ?, updated_at = CURRENT_TIMESTAMP
           WHERE id = ?`,
          [book, page, pdf, JSON.stringify(stats), JSON.stringify(profile), snippet, existing[0].id]
        );
        return existing[0].id;
      }

      const [result] = await pool.execute(
        `INSERT INTO bestiary (name, book, page, pdf, stats, profile, snippet)
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [name, book, page, pdf, JSON.stringify(stats), JSON.stringify(profile), snippet]
      );
      return result.insertId;
    } catch (error) {
      logToFile('MariaDB: Error upserting bestiary entry', entry && (entry.bestiaryName || entry.name), error);
      return null;
    }
  },

  replaceAll: async (entries) => {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      await connection.execute('DELETE FROM bestiary');
      for (const entry of entries) {
        const name = entry.bestiaryName || entry.name;
        const stats = {
          ...(entry.stats || {}),
          type: entry.type || entry.stats?.type || '',
          affiliation: entry.affiliation || entry.stats?.affiliation || '',
          sourceUrl: entry.sourceUrl || entry.stats?.sourceUrl || ''
        };
        const profile = entry.profile || stats.profile || {};
        await connection.execute(
          `INSERT INTO bestiary (name, book, page, pdf, stats, profile, snippet)
           VALUES (?, ?, ?, ?, ?, ?, ?)`,
          [
            name,
            entry.book || null,
            entry.page == null ? null : String(entry.page),
            entry.pdf || null,
            JSON.stringify(stats),
            JSON.stringify(profile),
            entry.snippet || ''
          ]
        );
      }
      await connection.commit();
      return entries.length;
    } catch (error) {
      await connection.rollback();
      logToFile('MariaDB: Error replacing bestiary', error);
      throw error;
    } finally {
      connection.release();
    }
  }
};

// Mission helpers
const missionHelpers = {
  getAll: async () => {
    try {
      const [rows] = await pool.execute('SELECT * FROM missions ORDER BY created_at DESC');
      return rows.map(row => ({
        ...row,
        is_active: Boolean(row.is_active),
        current_scene: row.current_scene || 0,
        scenes: typeof row.scenes === 'string' ? JSON.parse(row.scenes) : row.scenes
      }));
    } catch (error) {
      logToFile('MariaDB: Error getting all missions', error);
      return [];
    }
  },

  getById: async (id) => {
    try {
      const [rows] = await pool.execute('SELECT * FROM missions WHERE id = ?', [id]);
      if (rows.length === 0) return null;
      const row = rows[0];
      return {
        ...row,
        is_active: Boolean(row.is_active),
        current_scene: row.current_scene || 0,
        scenes: typeof row.scenes === 'string' ? JSON.parse(row.scenes) : row.scenes
      };
    } catch (error) {
      logToFile('MariaDB: Error getting mission', id, error);
      return null;
    }
  },

  create: async (missionData) => {
    try {
      const { name, theme, sceneCount, enemyCount, threatLevel, playerCount, scenes, gmPlayer } = missionData;
      const [result] = await pool.execute(
        'INSERT INTO missions (name, theme, scene_count, enemy_count, threat_level, player_count, scenes, gm_player) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        [name, theme, sceneCount, enemyCount, threatLevel, playerCount, JSON.stringify(scenes || []), gmPlayer || null]
      );
      logToFile('MariaDB: Created mission', name);
      return result.insertId;
    } catch (error) {
      logToFile('MariaDB: Error creating mission', error);
      return null;
    }
  },

  update: async (id, missionData) => {
    try {
      const { name, theme, sceneCount, enemyCount, threatLevel, playerCount, scenes } = missionData;
      const [result] = await pool.execute(
        'UPDATE missions SET name = ?, theme = ?, scene_count = ?, enemy_count = ?, threat_level = ?, player_count = ?, scenes = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [name, theme, sceneCount, enemyCount, threatLevel, playerCount, JSON.stringify(scenes), id]
      );
      logToFile('MariaDB: Updated mission', id);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error updating mission', id, error);
      return false;
    }
  },

  getActive: async () => {
    try {
      const [rows] = await pool.execute('SELECT * FROM missions WHERE is_active = 1 ORDER BY updated_at DESC LIMIT 1');
      if (rows.length === 0) return null;
      const row = rows[0];
      return {
        ...row,
        is_active: Boolean(row.is_active),
        current_scene: row.current_scene || 0,
        scenes: typeof row.scenes === 'string' ? JSON.parse(row.scenes) : row.scenes
      };
    } catch (error) {
      logToFile('MariaDB: Error getting active mission', error);
      return null;
    }
  },

  setActive: async (id) => {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      const [existing] = await connection.execute('SELECT id FROM missions WHERE id = ? LIMIT 1', [id]);
      if (existing.length === 0) {
        await connection.rollback();
        return false;
      }
      await connection.execute('UPDATE missions SET is_active = 0');
      const [result] = await connection.execute(
        'UPDATE missions SET is_active = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [id]
      );
      await connection.commit();
      logToFile('MariaDB: Set active mission', id);
      return result.affectedRows > 0;
    } catch (error) {
      await connection.rollback();
      logToFile('MariaDB: Error setting active mission', id, error);
      return false;
    } finally {
      connection.release();
    }
  },

  updateProgress: async (id, progressData) => {
    try {
      const { currentScene, scenes } = progressData;
      const [result] = await pool.execute(
        'UPDATE missions SET current_scene = ?, scenes = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [Number.isInteger(currentScene) ? currentScene : 0, JSON.stringify(scenes || []), id]
      );
      logToFile('MariaDB: Updated mission progress', id);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error updating mission progress', id, error);
      return false;
    }
  },

  delete: async (id) => {
    try {
      const [result] = await pool.execute('DELETE FROM missions WHERE id = ?', [id]);
      logToFile('MariaDB: Deleted mission', id);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error deleting mission', id, error);
      return false;
    }
  }
};

const missionRollHelpers = {
  create: async (rollData) => {
    try {
      let missionId = rollData.missionId || rollData.mission_id || null;
      let sceneIndex = Number.isInteger(rollData.sceneIndex) ? rollData.sceneIndex : Number(rollData.scene_index || 0);
      if (!missionId) {
        const active = await missionHelpers.getActive();
        missionId = active?.id || null;
        sceneIndex = active?.current_scene || sceneIndex || 0;
      }
      const payload = rollData.payload && typeof rollData.payload === 'object' ? rollData.payload : {};
      const [result] = await pool.execute(
        'INSERT INTO mission_rolls (mission_id, player_name, roll_type, scene_index, label, payload) VALUES (?, ?, ?, ?, ?, ?)',
        [
          missionId,
          rollData.playerName || rollData.player_name || 'unknown',
          rollData.rollType || rollData.roll_type || 'check',
          Number.isFinite(sceneIndex) ? sceneIndex : 0,
          rollData.label || payload.name || payload.weapon || null,
          JSON.stringify(payload),
        ]
      );
      return result.insertId;
    } catch (error) {
      logToFile('MariaDB: Error creating mission roll', error);
      return null;
    }
  },

  getForMission: async (missionId, limit = 50) => {
    try {
      const boundedLimit = Math.max(1, Math.min(Number(limit) || 50, 500));
      const [rows] = await pool.execute(
        `SELECT * FROM mission_rolls WHERE mission_id = ? ORDER BY created_at DESC LIMIT ${boundedLimit}`,
        [missionId]
      );
      return rows.map(row => ({
        ...row,
        payload: typeof row.payload === 'string' ? JSON.parse(row.payload || '{}') : row.payload,
      }));
    } catch (error) {
      logToFile('MariaDB: Error getting mission rolls', missionId, error);
      return [];
    }
  },

  getActive: async (limit = 50) => {
    const active = await missionHelpers.getActive();
    if (!active) return [];
    return missionRollHelpers.getForMission(active.id, limit);
  },

  delete: async (id) => {
    try {
      const [result] = await pool.execute('DELETE FROM mission_rolls WHERE id = ?', [id]);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error deleting mission roll', id, error);
      return false;
    }
  },

  clearForMission: async (missionId) => {
    try {
      const [result] = await pool.execute('DELETE FROM mission_rolls WHERE mission_id = ?', [missionId]);
      return result.affectedRows || 0;
    } catch (error) {
      logToFile('MariaDB: Error clearing mission rolls', missionId, error);
      return null;
    }
  },

  clearActive: async () => {
    const active = await missionHelpers.getActive();
    if (!active) return null;
    return missionRollHelpers.clearForMission(active.id);
  }
};

const simulationHelpers = {
  save: async (data) => {
    try {
      const [result] = await pool.execute(
        `INSERT INTO simulations
          (mission_id, mission_name, players, result, xp_earned, total_rounds,
           total_rolls, success_rate, combat_success_rate, puzzle_success_rate,
           scene_results, player_cards, roll_feed, story_hooks, findings, difficulty_level, enemy_profile)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          data.mission_id || null,
          data.mission_name || 'Unknown Mission',
          JSON.stringify(data.players || []),
          data.result || 'UNKNOWN',
          data.xp_earned || 0,
          data.total_rounds || 0,
          data.total_rolls || 0,
          data.success_rate || 0,
          data.combat_success_rate || 0,
          data.puzzle_success_rate || 0,
          JSON.stringify(data.scene_results || []),
          JSON.stringify(data.player_cards || {}),
          JSON.stringify(data.roll_feed || []),
          JSON.stringify(data.story_hooks || []),
          JSON.stringify(data.findings || []),
          data.difficulty_level || 2,
          data.enemy_profile || 'balanced',
        ]
      );
      return result.insertId;
    } catch (error) {
      logToFile('MariaDB: Error saving simulation', error);
      return null;
    }
  },

  getAll: async (limit = 50) => {
    try {
      const bounded = Math.max(1, Math.min(Number(limit) || 50, 200));
      const [rows] = await pool.execute(
        `SELECT id, mission_id, mission_name, players, result, xp_earned,
                total_rounds, total_rolls, success_rate, combat_success_rate,
                puzzle_success_rate, findings, story_hooks, difficulty_level, enemy_profile, run_date
         FROM simulations ORDER BY run_date DESC LIMIT ${bounded}`
      );
      return rows.map(r => ({
        ...r,
        players:  typeof r.players  === 'string' ? JSON.parse(r.players  || '[]') : r.players,
        findings: typeof r.findings === 'string' ? JSON.parse(r.findings || '[]') : r.findings,
        story_hooks: typeof r.story_hooks === 'string' ? JSON.parse(r.story_hooks || '[]') : (r.story_hooks || []),
      }));
    } catch (error) {
      logToFile('MariaDB: Error listing simulations', error);
      return [];
    }
  },

  getById: async (id) => {
    try {
      const [rows] = await pool.execute('SELECT * FROM simulations WHERE id = ?', [id]);
      if (!rows.length) return null;
      const r = rows[0];
      const parse = (v) => typeof v === 'string' ? JSON.parse(v || 'null') : v;
      return {
        ...r,
        players:       parse(r.players),
        scene_results: parse(r.scene_results),
        player_cards:  parse(r.player_cards),
        roll_feed:     parse(r.roll_feed),
        story_hooks:   parse(r.story_hooks) || [],
        findings:      parse(r.findings),
      };
    } catch (error) {
      logToFile('MariaDB: Error getting simulation', id, error);
      return null;
    }
  },

  delete: async (id) => {
    try {
      const [result] = await pool.execute('DELETE FROM simulations WHERE id = ?', [id]);
      return result.affectedRows > 0;
    } catch (error) {
      logToFile('MariaDB: Error deleting simulation', id, error);
      return false;
    }
  },
};

// Error report helpers
const errorReportHelpers = {
  create: async (data) => {
    try {
      const [result] = await pool.execute(
        'INSERT INTO error_reports (player_name, category, title, description, page_url) VALUES (?, ?, ?, ?, ?)',
        [data.playerName, data.category || 'other', data.title, data.description, data.pageUrl || '']
      );
      return result.insertId;
    } catch (error) {
      logToFile('MariaDB: Error creating error report', error);
      throw error;
    }
  },

  getAll: async () => {
    try {
      const [rows] = await pool.execute('SELECT * FROM error_reports ORDER BY created_at DESC');
      return rows;
    } catch (error) {
      logToFile('MariaDB: Error fetching error reports', error);
      return [];
    }
  },

  getByPlayer: async (playerName) => {
    try {
      const [rows] = await pool.execute(
        'SELECT * FROM error_reports WHERE player_name = ? ORDER BY created_at DESC',
        [playerName]
      );
      return rows;
    } catch (error) {
      logToFile('MariaDB: Error fetching player error reports', playerName, error);
      return [];
    }
  },

  resolve: async (id, resolvedBy, resolvedNote) => {
    try {
      await pool.execute(
        'UPDATE error_reports SET status = ?, resolved_by = ?, resolved_note = ?, resolved_at = NOW() WHERE id = ?',
        ['resolved', resolvedBy, resolvedNote || '', id]
      );
    } catch (error) {
      logToFile('MariaDB: Error resolving error report', id, error);
      throw error;
    }
  },

  updateStatus: async (id, status) => {
    try {
      await pool.execute(
        'UPDATE error_reports SET status = ? WHERE id = ?',
        [status, id]
      );
    } catch (error) {
      logToFile('MariaDB: Error updating error report status', id, error);
      throw error;
    }
  },

  delete: async (id) => {
    try {
      await pool.execute('DELETE FROM error_reports WHERE id = ?', [id]);
    } catch (error) {
      logToFile('MariaDB: Error deleting error report', id, error);
      throw error;
    }
  },

  getById: async (id) => {
    try {
      const [rows] = await pool.execute('SELECT * FROM error_reports WHERE id = ?', [id]);
      return rows[0] || null;
    } catch (error) {
      logToFile('MariaDB: Error fetching error report by id', id, error);
      return null;
    }
  }
};

// Initialize database
const initializationPromise = createTables().catch(error => {
  console.error('Failed to initialize MariaDB:', error);
  logToFile('MariaDB: Initialization failed; continuing with degraded helpers', error);
});

// Export the connection pool and helpers
module.exports = {
  pool,
  playerHelpers,
  sessionHelpers,
  rulesHelpers,
  stagingHelpers,
  weaponsHelpers,
  bestiaryHelpers,
  missionHelpers,
  missionRollHelpers,
  simulationHelpers,
  errorReportHelpers,
  initializationPromise,
  logToFile
};