import Database from 'better-sqlite3';
import path from 'path';
import { fileURLToPath } from 'url';

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

const db = new Database(path.join(__dirname, 'turtle_control.db'));

// Enable WAL journal mode for better concurrent read/write performance
db.pragma('journal_mode = WAL');

// Initialize database schema
export function initializeDatabase() {
  // Turtle homes table
  db.exec(`
    CREATE TABLE IF NOT EXISTS turtle_homes (
      turtle_id INTEGER PRIMARY KEY,
      x INTEGER NOT NULL,
      y INTEGER NOT NULL,
      z INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    )
  `);

  // Turtle configuration table
  db.exec(`
    CREATE TABLE IF NOT EXISTS turtle_config (
      turtle_id INTEGER PRIMARY KEY,
      max_distance INTEGER DEFAULT 200,
      facing INTEGER DEFAULT 0,
      config_json TEXT,
      updated_at INTEGER NOT NULL
    )
  `);

  // World blocks table
  db.exec(`
    CREATE TABLE IF NOT EXISTS world_blocks (
      x INTEGER NOT NULL,
      y INTEGER NOT NULL,
      z INTEGER NOT NULL,
      block_name TEXT NOT NULL,
      metadata INTEGER DEFAULT 0,
      discovered_by INTEGER NOT NULL,
      discovered_at INTEGER NOT NULL,
      PRIMARY KEY (x, y, z)
    )
  `);

  // Turtle paths table (for path recording)
  db.exec(`
    CREATE TABLE IF NOT EXISTS turtle_paths (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      turtle_id INTEGER NOT NULL,
      path_name TEXT NOT NULL,
      path_data TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    )
  `);

  // Task queue table (for multi-turtle coordination)
  db.exec(`
    CREATE TABLE IF NOT EXISTS task_queue (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      task_type TEXT NOT NULL,
      task_data TEXT NOT NULL,
      assigned_turtle_id INTEGER,
      priority INTEGER DEFAULT 0,
      status TEXT DEFAULT 'pending',
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    )
  `);

  // Mining areas table (for area visualization)
  db.exec(`
    CREATE TABLE IF NOT EXISTS mining_areas (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      turtle_id INTEGER NOT NULL,
      min_x INTEGER NOT NULL,
      min_y INTEGER NOT NULL,
      min_z INTEGER NOT NULL,
      max_x INTEGER NOT NULL,
      max_y INTEGER NOT NULL,
      max_z INTEGER NOT NULL,
      name TEXT,
      color TEXT DEFAULT '#4a8c2a',
      status TEXT DEFAULT 'active',
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    )
  `);

  // Migrate existing mining_areas table to add name/color columns if missing
  try {
    const tableInfo = db.prepare("PRAGMA table_info(mining_areas)").all();
    const columns = tableInfo.map(c => c.name);
    if (!columns.includes('name')) {
      db.exec('ALTER TABLE mining_areas ADD COLUMN name TEXT');
    }
    if (!columns.includes('color')) {
      db.exec("ALTER TABLE mining_areas ADD COLUMN color TEXT DEFAULT '#4a8c2a'");
    }
  } catch (e) {
    // Ignore migration errors
  }

  // Mining statistics table
  db.exec(`
    CREATE TABLE IF NOT EXISTS mining_stats (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      turtle_id INTEGER NOT NULL,
      block_type TEXT NOT NULL,
      count INTEGER DEFAULT 1,
      session_start INTEGER NOT NULL,
      last_mined INTEGER NOT NULL,
      UNIQUE(turtle_id, block_type, session_start)
    )
  `);

  // Turtle groups/teams table
  db.exec(`
    CREATE TABLE IF NOT EXISTS turtle_groups (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      group_name TEXT NOT NULL UNIQUE,
      color TEXT DEFAULT '#3b82f6',
      created_at INTEGER NOT NULL
    )
  `);

  // Turtle group membership table
  db.exec(`
    CREATE TABLE IF NOT EXISTS turtle_group_members (
      turtle_id INTEGER NOT NULL,
      group_id INTEGER NOT NULL,
      joined_at INTEGER NOT NULL,
      PRIMARY KEY (turtle_id, group_id),
      FOREIGN KEY (group_id) REFERENCES turtle_groups(id) ON DELETE CASCADE
    )
  `);

  // Session tracking table (for time-based statistics)
  db.exec(`
    CREATE TABLE IF NOT EXISTS turtle_sessions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      turtle_id INTEGER NOT NULL,
      started_at INTEGER NOT NULL,
      ended_at INTEGER,
      blocks_mined INTEGER DEFAULT 0,
      distance_traveled INTEGER DEFAULT 0
    )
  `);

  // Player positions table (for tracking pocket computer users)
  db.exec(`
    CREATE TABLE IF NOT EXISTS player_positions (
      player_id INTEGER PRIMARY KEY,
      x INTEGER NOT NULL,
      y INTEGER NOT NULL,
      z INTEGER NOT NULL,
      label TEXT,
      updated_at INTEGER NOT NULL
    )
  `);

  // Migrate player_positions table to add label column if missing
  try {
    const tableInfo = db.prepare("PRAGMA table_info(player_positions)").all();
    const columns = tableInfo.map(c => c.name);
    if (!columns.includes('label')) {
      db.exec('ALTER TABLE player_positions ADD COLUMN label TEXT');
    }
  } catch (e) {
    // Ignore migration errors
  }

  // Chunk analysis table (ore density per chunk)
  db.exec(`
    CREATE TABLE IF NOT EXISTS chunks (
      x INTEGER NOT NULL,
      z INTEGER NOT NULL,
      analysis TEXT DEFAULT '{}',
      scanned_at INTEGER NOT NULL,
      PRIMARY KEY (x, z)
    )
  `);

  // Add block_state and block_tags columns to world_blocks if not present
  try {
    db.exec(`ALTER TABLE world_blocks ADD COLUMN block_state TEXT DEFAULT '{}'`);
  } catch (e) { /* column already exists */ }
  try {
    db.exec(`ALTER TABLE world_blocks ADD COLUMN block_tags TEXT DEFAULT '{}'`);
  } catch (e) { /* column already exists */ }

  // Create indexes for better performance
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_world_blocks_discovered 
    ON world_blocks(discovered_by);
  `);

  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_task_queue_status 
    ON task_queue(status, priority DESC);
  `);

  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_world_blocks_name
    ON world_blocks(block_name);
  `);

  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_chunks_coords
    ON chunks(x, z);
  `);

  // Turtle state persistence table (for reconnect recovery)
  db.exec(`
    CREATE TABLE IF NOT EXISTS turtle_state (
      turtle_id INTEGER PRIMARY KEY,
      state_name TEXT NOT NULL,
      state_data TEXT DEFAULT '{}',
      updated_at INTEGER NOT NULL
    )
  `);

  console.log('✅ Database initialized');
}

// Turtle Homes
export function saveTurtleHome(turtleId, position) {
  const stmt = db.prepare(`
    INSERT OR REPLACE INTO turtle_homes (turtle_id, x, y, z, updated_at)
    VALUES (?, ?, ?, ?, ?)
  `);
  stmt.run(turtleId, position.x, position.y, position.z, Date.now());
}

export function getTurtleHome(turtleId) {
  const stmt = db.prepare('SELECT x, y, z FROM turtle_homes WHERE turtle_id = ?');
  return stmt.get(turtleId);
}

export function getAllTurtleHomes() {
  const stmt = db.prepare('SELECT turtle_id, x, y, z FROM turtle_homes');
  return stmt.all();
}

// Turtle Configuration
export function saveTurtleConfig(turtleId, config) {
  const stmt = db.prepare(`
    INSERT OR REPLACE INTO turtle_config (turtle_id, max_distance, facing, config_json, updated_at)
    VALUES (?, ?, ?, ?, ?)
  `);
  stmt.run(
    turtleId,
    config.maxDistance || 200,
    config.facing || 0,
    JSON.stringify(config),
    Date.now()
  );
}

export function getTurtleConfig(turtleId) {
  const stmt = db.prepare('SELECT * FROM turtle_config WHERE turtle_id = ?');
  const row = stmt.get(turtleId);
  if (row && row.config_json) {
    return JSON.parse(row.config_json);
  }
  return null;
}

// World Blocks
export function saveWorldBlock(x, y, z, blockName, metadata, discoveredBy, blockState = null, blockTags = null) {
  const stmt = db.prepare(`
    INSERT OR REPLACE INTO world_blocks (x, y, z, block_name, metadata, discovered_by, discovered_at, block_state, block_tags)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);
  stmt.run(x, y, z, blockName, metadata || 0, discoveredBy, Date.now(),
    blockState ? JSON.stringify(blockState) : '{}',
    blockTags ? JSON.stringify(blockTags) : '{}');
}

export function getWorldBlocks(limit = 10000) {
  const stmt = db.prepare('SELECT * FROM world_blocks LIMIT ?');
  return stmt.all(limit);
}

export function getWorldBlockCount() {
  const row = db.prepare('SELECT COUNT(*) as cnt FROM world_blocks').get();
  return row ? row.cnt : 0;
}

export function getWorldBlocksInArea(minX, minY, minZ, maxX, maxY, maxZ) {
  const stmt = db.prepare(`
    SELECT * FROM world_blocks 
    WHERE x BETWEEN ? AND ? 
    AND y BETWEEN ? AND ? 
    AND z BETWEEN ? AND ?
  `);
  return stmt.all(minX, maxX, minY, maxY, minZ, maxZ);
}

export function clearOldBlocks(daysOld = 7) {
  const cutoffTime = Date.now() - (daysOld * 24 * 60 * 60 * 1000);
  const stmt = db.prepare('DELETE FROM world_blocks WHERE discovered_at < ?');
  const result = stmt.run(cutoffTime);
  return result.changes;
}

// Turtle Paths
export function savePath(turtleId, pathName, pathData) {
  const stmt = db.prepare(`
    INSERT INTO turtle_paths (turtle_id, path_name, path_data, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?)
  `);
  const now = Date.now();
  const result = stmt.run(turtleId, pathName, JSON.stringify(pathData), now, now);
  return result.lastInsertRowid;
}

export function getPaths(turtleId = null) {
  if (turtleId) {
    const stmt = db.prepare('SELECT * FROM turtle_paths WHERE turtle_id = ? ORDER BY created_at DESC');
    return stmt.all(turtleId).map(row => ({
      ...row,
      path_data: JSON.parse(row.path_data)
    }));
  } else {
    const stmt = db.prepare('SELECT * FROM turtle_paths ORDER BY created_at DESC');
    return stmt.all().map(row => ({
      ...row,
      path_data: JSON.parse(row.path_data)
    }));
  }
}

export function getPath(pathId) {
  const stmt = db.prepare('SELECT * FROM turtle_paths WHERE id = ?');
  const row = stmt.get(pathId);
  if (row) {
    return { ...row, path_data: JSON.parse(row.path_data) };
  }
  return null;
}

export function deletePath(pathId) {
  const stmt = db.prepare('DELETE FROM turtle_paths WHERE id = ?');
  return stmt.run(pathId);
}

// Task Queue
export function createTask(taskType, taskData, priority = 0, assignedTurtleId = null) {
  const stmt = db.prepare(`
    INSERT INTO task_queue (task_type, task_data, assigned_turtle_id, priority, status, created_at, updated_at)
    VALUES (?, ?, ?, ?, 'pending', ?, ?)
  `);
  const now = Date.now();
  const result = stmt.run(taskType, JSON.stringify(taskData), assignedTurtleId, priority, now, now);
  return result.lastInsertRowid;
}

export function getNextTask() {
  const stmt = db.prepare(`
    SELECT * FROM task_queue 
    WHERE status = 'pending' 
    ORDER BY priority DESC, created_at ASC 
    LIMIT 1
  `);
  const row = stmt.get();
  if (row) {
    return {
      ...row,
      task_data: JSON.parse(row.task_data)
    };
  }
  return null;
}

export function assignTask(taskId, turtleId) {
  if (turtleId === null || turtleId === undefined) {
    // Un-assign: clear turtle and revert to pending
    const stmt = db.prepare(`
      UPDATE task_queue
      SET assigned_turtle_id = NULL, status = 'pending', updated_at = ?
      WHERE id = ?
    `);
    stmt.run(Date.now(), taskId);
  } else {
    const stmt = db.prepare(`
      UPDATE task_queue 
      SET assigned_turtle_id = ?, status = 'assigned', updated_at = ?
      WHERE id = ?
    `);
    stmt.run(turtleId, Date.now(), taskId);
  }
}

export function updateTaskStatus(taskId, status, result = null) {
  const stmt = db.prepare(`
    UPDATE task_queue 
    SET status = ?, task_data = CASE WHEN ? IS NOT NULL THEN json_set(task_data, '$.result', ?) ELSE task_data END, updated_at = ?
    WHERE id = ?
  `);
  stmt.run(status, result, result, Date.now(), taskId);
}

export function completeTask(taskId) {
  const stmt = db.prepare(`
    UPDATE task_queue 
    SET status = 'completed', updated_at = ?
    WHERE id = ?
  `);
  stmt.run(Date.now(), taskId);
}

export function deleteTask(taskId) {
  const stmt = db.prepare('DELETE FROM task_queue WHERE id = ?');
  return stmt.run(taskId);
}

export function getAllTasks(status = null) {
  if (status) {
    const stmt = db.prepare('SELECT * FROM task_queue WHERE status = ? ORDER BY priority DESC, created_at DESC');
    return stmt.all(status).map(row => ({
      ...row,
      task_data: JSON.parse(row.task_data)
    }));
  }
  const stmt = db.prepare('SELECT * FROM task_queue ORDER BY priority DESC, created_at DESC');
  return stmt.all().map(row => ({
    ...row,
    task_data: JSON.parse(row.task_data)
  }));
}

// Mining Areas
export function saveMiningArea(turtleId, bounds, areaName = null, status = 'planned', color = '#4a8c2a') {
  const stmt = db.prepare(`
    INSERT INTO mining_areas (turtle_id, min_x, min_y, min_z, max_x, max_y, max_z, name, color, status, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);
  const now = Date.now();
  const result = stmt.run(
    turtleId,
    bounds.minX, bounds.minY, bounds.minZ,
    bounds.maxX, bounds.maxY, bounds.maxZ,
    areaName, color,
    status, now, now
  );
  return result.lastInsertRowid;
}

export function getMiningAreas(statusFilter = null) {
  if (statusFilter) {
    const stmt = db.prepare('SELECT * FROM mining_areas WHERE status = ? ORDER BY created_at DESC');
    return stmt.all(statusFilter);
  }
  const stmt = db.prepare('SELECT * FROM mining_areas ORDER BY created_at DESC');
  return stmt.all();
}

export function updateMiningAreaStatus(areaId, status) {
  const stmt = db.prepare('UPDATE mining_areas SET status = ?, updated_at = ? WHERE id = ?');
  stmt.run(status, Date.now(), areaId);
}

export function updateMiningArea(areaId, updates) {
  const allowedFields = ['name', 'color', 'status', 'min_x', 'min_y', 'min_z', 'max_x', 'max_y', 'max_z', 'turtle_id'];
  const setClauses = [];
  const values = [];
  for (const [key, value] of Object.entries(updates)) {
    if (allowedFields.includes(key)) {
      setClauses.push(`${key} = ?`);
      values.push(value);
    }
  }
  if (setClauses.length === 0) return;
  setClauses.push('updated_at = ?');
  values.push(Date.now());
  values.push(areaId);
  const stmt = db.prepare(`UPDATE mining_areas SET ${setClauses.join(', ')} WHERE id = ?`);
  stmt.run(...values);
}

export function deleteMiningArea(areaId) {
  const stmt = db.prepare('DELETE FROM mining_areas WHERE id = ?');
  return stmt.run(areaId);
}

export function closeMiningArea(areaId) {
  const stmt = db.prepare('UPDATE mining_areas SET status = \'closed\', updated_at = ? WHERE id = ?');
  stmt.run(Date.now(), areaId);
}

// Mining Statistics
export function recordBlockMined(turtleId, blockType) {
  const stmt = db.prepare(`
    INSERT INTO mining_stats (turtle_id, block_type, count, session_start, last_mined)
    VALUES (?, ?, 1, ?, ?)
    ON CONFLICT(turtle_id, block_type, session_start) 
    DO UPDATE SET count = count + 1, last_mined = ?
  `);
  const now = Date.now();
  const sessionStart = now - (now % (24 * 60 * 60 * 1000)); // Start of day
  stmt.run(turtleId, blockType, sessionStart, now, now);
}

export function getMiningStats(turtleId = null, days = 7) {
  const cutoff = Date.now() - (days * 24 * 60 * 60 * 1000);
  
  if (turtleId) {
    const stmt = db.prepare(`
      SELECT block_type, SUM(count) as total_count 
      FROM mining_stats 
      WHERE turtle_id = ? AND session_start >= ?
      GROUP BY block_type
      ORDER BY total_count DESC
    `);
    return stmt.all(turtleId, cutoff);
  } else {
    const stmt = db.prepare(`
      SELECT turtle_id, block_type, SUM(count) as total_count 
      FROM mining_stats 
      WHERE session_start >= ?
      GROUP BY turtle_id, block_type
      ORDER BY turtle_id, total_count DESC
    `);
    return stmt.all(cutoff);
  }
}

export function getTopMiners(limit = 10) {
  const stmt = db.prepare(`
    SELECT turtle_id, SUM(count) as total_blocks, COUNT(DISTINCT block_type) as unique_types
    FROM mining_stats
    GROUP BY turtle_id
    ORDER BY total_blocks DESC
    LIMIT ?
  `);
  return stmt.all(limit);
}

// Turtle Groups/Teams
export function createGroup(groupName, color = '#3b82f6') {
  const stmt = db.prepare(`
    INSERT INTO turtle_groups (group_name, color, created_at)
    VALUES (?, ?, ?)
  `);
  const result = stmt.run(groupName, color, Date.now());
  return result.lastInsertRowid;
}

export function getAllGroups() {
  const stmt = db.prepare('SELECT * FROM turtle_groups ORDER BY created_at DESC');
  return stmt.all();
}

export function deleteGroup(groupId) {
  const stmt = db.prepare('DELETE FROM turtle_groups WHERE id = ?');
  stmt.run(groupId);
}

export function addTurtleToGroup(turtleId, groupId) {
  const stmt = db.prepare(`
    INSERT OR IGNORE INTO turtle_group_members (turtle_id, group_id, joined_at)
    VALUES (?, ?, ?)
  `);
  stmt.run(turtleId, groupId, Date.now());
}

export function removeTurtleFromGroup(turtleId, groupId) {
  const stmt = db.prepare(`
    DELETE FROM turtle_group_members 
    WHERE turtle_id = ? AND group_id = ?
  `);
  stmt.run(turtleId, groupId);
}

export function getGroupMembers(groupId) {
  const stmt = db.prepare(`
    SELECT turtle_id, joined_at 
    FROM turtle_group_members 
    WHERE group_id = ?
  `);
  return stmt.all(groupId);
}

export function getTurtleGroups(turtleId) {
  const stmt = db.prepare(`
    SELECT g.*, m.joined_at
    FROM turtle_groups g
    JOIN turtle_group_members m ON g.id = m.group_id
    WHERE m.turtle_id = ?
  `);
  return stmt.all(turtleId);
}

// Session Tracking
export function startSession(turtleId) {
  const stmt = db.prepare(`
    INSERT INTO turtle_sessions (turtle_id, started_at)
    VALUES (?, ?)
  `);
  const result = stmt.run(turtleId, Date.now());
  return result.lastInsertRowid;
}

export function endSession(sessionId, blocksMined, distanceTraveled) {
  const stmt = db.prepare(`
    UPDATE turtle_sessions 
    SET ended_at = ?, blocks_mined = ?, distance_traveled = ?
    WHERE id = ?
  `);
  stmt.run(Date.now(), blocksMined, distanceTraveled, sessionId);
}

export function getSessionStats(turtleId, limit = 10) {
  const stmt = db.prepare(`
    SELECT * FROM turtle_sessions 
    WHERE turtle_id = ? 
    ORDER BY started_at DESC 
    LIMIT ?
  `);
  return stmt.all(turtleId, limit);
}

// Player Positions
export function savePlayerPosition(playerId, position, label = null) {
  const stmt = db.prepare(`
    INSERT OR REPLACE INTO player_positions (player_id, x, y, z, label, updated_at)
    VALUES (?, ?, ?, ?, ?, ?)
  `);
  stmt.run(playerId, position.x, position.y, position.z, label, Date.now());
}

export function getPlayerPosition(playerId) {
  const stmt = db.prepare('SELECT player_id, x, y, z, label, updated_at FROM player_positions WHERE player_id = ?');
  const row = stmt.get(playerId);
  if (!row) return null;
  return {
    playerID: row.player_id,
    position: { x: row.x, y: row.y, z: row.z },
    label: row.label,
    lastUpdate: row.updated_at
  };
}

export function getAllPlayerPositions() {
  const stmt = db.prepare('SELECT player_id, x, y, z, label, updated_at FROM player_positions');
  return stmt.all().map(row => ({
    playerID: row.player_id,
    position: { x: row.x, y: row.y, z: row.z },
    label: row.label,
    lastUpdate: row.updated_at
  }));
}

// Cleanup function
export function closeDatabase() {
  db.close();
}

// ========== CHUNK ANALYSIS ==========

export function saveChunkAnalysis(x, z, analysis) {
  const stmt = db.prepare(`
    INSERT OR REPLACE INTO chunks (x, z, analysis, scanned_at)
    VALUES (?, ?, ?, ?)
  `);
  stmt.run(x, z, JSON.stringify(analysis), Date.now());
}

export function getChunkAnalysis(x, z) {
  const stmt = db.prepare('SELECT * FROM chunks WHERE x = ? AND z = ?');
  const row = stmt.get(x, z);
  if (row) {
    return { ...row, analysis: JSON.parse(row.analysis) };
  }
  return null;
}

export function getAllChunkAnalyses() {
  const stmt = db.prepare('SELECT * FROM chunks');
  return stmt.all().map(row => ({
    ...row,
    analysis: JSON.parse(row.analysis)
  }));
}

// ========== BLOCK SEARCH ==========

export function getBlocksWithNameLike(fromX, fromY, fromZ, toX, toY, toZ, namePattern) {
  const stmt = db.prepare(`
    SELECT x, y, z, block_name as name, metadata, block_state, block_tags
    FROM world_blocks 
    WHERE x BETWEEN ? AND ? 
    AND y BETWEEN ? AND ? 
    AND z BETWEEN ? AND ?
    AND block_name LIKE ?
  `);
  return stmt.all(
    Math.min(fromX, toX), Math.max(fromX, toX),
    Math.min(fromY, toY), Math.max(fromY, toY),
    Math.min(fromZ, toZ), Math.max(fromZ, toZ),
    namePattern
  ).map(row => ({
    ...row,
    state: row.block_state ? JSON.parse(row.block_state) : {},
    tags: row.block_tags ? JSON.parse(row.block_tags) : {},
  }));
}

export function getBlock(x, y, z) {
  const stmt = db.prepare('SELECT block_name as name, metadata, block_state, block_tags FROM world_blocks WHERE x = ? AND y = ? AND z = ?');
  const row = stmt.get(x, y, z);
  if (row) {
    return {
      ...row,
      state: row.block_state ? JSON.parse(row.block_state) : {},
      tags: row.block_tags ? JSON.parse(row.block_tags) : {},
    };
  }
  return null;
}

export function deleteBlocksInArea(fromX, fromY, fromZ, toX, toY, toZ) {
  const stmt = db.prepare(`
    DELETE FROM world_blocks 
    WHERE x BETWEEN ? AND ? 
    AND y BETWEEN ? AND ? 
    AND z BETWEEN ? AND ?
  `);
  return stmt.run(
    Math.min(fromX, toX), Math.max(fromX, toX),
    Math.min(fromY, toY), Math.max(fromY, toY),
    Math.min(fromZ, toZ), Math.max(fromZ, toZ)
  );
}

// ========== BLOCK DELETION ==========

export function deleteBlock(x, y, z) {
  const stmt = db.prepare('DELETE FROM world_blocks WHERE x = ? AND y = ? AND z = ?');
  return stmt.run(x, y, z);
}

// ========== TURTLE STATE PERSISTENCE ==========

export function saveTurtleState(turtleId, stateName, stateData = {}) {
  const stmt = db.prepare(`
    INSERT OR REPLACE INTO turtle_state (turtle_id, state_name, state_data, updated_at)
    VALUES (?, ?, ?, ?)
  `);
  stmt.run(turtleId, stateName, JSON.stringify(stateData), Date.now());
}

export function getTurtleState(turtleId) {
  const stmt = db.prepare('SELECT * FROM turtle_state WHERE turtle_id = ?');
  const row = stmt.get(turtleId);
  if (row) {
    return {
      stateName: row.state_name,
      stateData: JSON.parse(row.state_data),
      updatedAt: row.updated_at,
    };
  }
  return null;
}

export function deleteTurtleState(turtleId) {
  const stmt = db.prepare('DELETE FROM turtle_state WHERE turtle_id = ?');
  return stmt.run(turtleId);
}

// Export database instance for custom queries if needed
export { db };