Economia Compartida Twitch, Discord y Minecraft 2026 — Implementacion Tecnica

-- Usuarios con IDs de las tres plataformas
CREATE TABLE users (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  discord_id   TEXT UNIQUE,
  twitch_login TEXT UNIQUE,
  mc_uuid      TEXT UNIQUE,
  mc_name      TEXT,
  balance      INTEGER DEFAULT 0,
  level        INTEGER DEFAULT 1,
  joined_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_active  DATETIME
);

-- Todas las transacciones de economia
CREATE TABLE ledger (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id     INTEGER NOT NULL REFERENCES users(id),
  delta       INTEGER NOT NULL,  -- positivo=ingreso, negativo=gasto
  balance_after INTEGER NOT NULL,
  source      TEXT NOT NULL,     -- 'twitch_watch','discord_chat','mc_vote','purchase','transfer_in','transfer_out'
  ref_id      TEXT,              -- ID externo (clip_id, item_id, etc.)
  note        TEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tienda de items
CREATE TABLE shop_items (
  id          TEXT PRIMARY KEY,
  name        TEXT NOT NULL,
  description TEXT,
  cost        INTEGER NOT NULL,
  platform    TEXT NOT NULL,     -- 'discord','minecraft','twitch'
  action      TEXT NOT NULL,     -- comando a ejecutar al comprar (JSON)
  stock       INTEGER DEFAULT -1, -- -1 = ilimitado
  enabled     BOOLEAN DEFAULT 1
);

// Express API que todos los bots consultan
import express from 'express';
const app = express();

// GET /users/:id/balance
app.get('/users/:id/balance', async (req, res) => {
  const user = await db.get('SELECT balance, level FROM users WHERE id = ?', req.params.id);
  res.json(user || { error: 'not_found' });
});

// POST /transactions  (transaccion atomica)
app.post('/transactions', async (req, res) => {
  const { userId, delta, source, note, refId } = req.body;
  
  await db.run('BEGIN TRANSACTION');
  try {
    const user = await db.get('SELECT balance FROM users WHERE id = ?', userId);
    if (!user) throw new Error('user_not_found');
    
    const newBalance = user.balance + delta;
    if (newBalance < 0) throw new Error('insufficient_balance');
    
    await db.run('UPDATE users SET balance = ?, last_active = CURRENT_TIMESTAMP WHERE id = ?', [newBalance, userId]);
    await db.run('INSERT INTO ledger (user_id, delta, balance_after, source, ref_id, note) VALUES (?,?,?,?,?,?)',
      [userId, delta, newBalance, source, refId, note]);
    
    await db.run('COMMIT');
    res.json({ success: true, newBalance });
  } catch (err) {
    await db.run('ROLLBACK');
    res.status(400).json({ error: err.message });
  }
});