/**
 * Sets up the pgsoft database with all required tables and seeds the default agent.
 * Run with: node server/scripts/setup-pgsoft-db.mjs
 */
import mysql from 'mysql2/promise';

async function setup() {
  // Build pgsoft DB URL from the main DATABASE_URL
  const mainUrl = process.env.DATABASE_URL;
  const pgUrl = mainUrl.replace(/\/[^/?]+(\?|$)/, '/pgsoft$1');
  
  console.log('Setting up PG Soft database...');
  const conn = await mysql.createConnection(pgUrl);

  // Create tables
  await conn.execute(`CREATE TABLE IF NOT EXISTS agents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agentCode VARCHAR(50),
    saldo FLOAT NOT NULL DEFAULT 0,
    agentToken VARCHAR(255) NOT NULL,
    secretKey VARCHAR(255) NOT NULL,
    probganho VARCHAR(50) DEFAULT '0',
    probbonus VARCHAR(10) DEFAULT '0',
    probganhortp VARCHAR(10) DEFAULT '0',
    probganhoinfluencer VARCHAR(10) DEFAULT '0',
    probbonusinfluencer VARCHAR(10) DEFAULT '0',
    probganhoaposta VARCHAR(10) DEFAULT '0',
    probganhosaldo VARCHAR(10) DEFAULT '0',
    callbackurl VARCHAR(255) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`);
  console.log('✓ agents table');

  await conn.execute(`CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    token VARCHAR(255) NOT NULL DEFAULT '',
    atk VARCHAR(255) NOT NULL,
    saldo FLOAT NOT NULL DEFAULT 0,
    valorapostado FLOAT NOT NULL DEFAULT 0,
    valordebitado FLOAT NOT NULL DEFAULT 0,
    valorganho FLOAT NOT NULL DEFAULT 0,
    rtp DOUBLE NOT NULL DEFAULT 0,
    isinfluencer FLOAT NOT NULL DEFAULT 0,
    agentid INT NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`);
  console.log('✓ users table');

  await conn.execute(`CREATE TABLE IF NOT EXISTS calls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    iduser INT NOT NULL,
    gamecode VARCHAR(255) NOT NULL,
    jsonname VARCHAR(255) NOT NULL DEFAULT '0',
    steps INT DEFAULT NULL,
    bycall VARCHAR(255) DEFAULT NULL,
    aw FLOAT DEFAULT 0,
    status VARCHAR(255) NOT NULL DEFAULT 'pending'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`);
  console.log('✓ calls table');

  // JSON tables for each game
  const jsonTables = [
    'fortunetigerplayerjson',
    'fortuneoxrplayerjson',
    'fortunedragonplayerjson',
    'fortunerabbitplayerjson',
    'fortunemouseplayerjson',
    'bikineparadisejson',
    'jungledelightjson',
    'ganeshagoldjson',
    'doublefortunejson',
    'dragontigerluckjson',
    'butterflyblossomplayerjson',
  ];

  for (const table of jsonTables) {
    await conn.execute(`CREATE TABLE IF NOT EXISTS \`${table}\` (
      id INT AUTO_INCREMENT PRIMARY KEY,
      json LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`);
    console.log(`✓ ${table}`);
  }

  // Insert default agent if not exists
  const [existing] = await conn.execute('SELECT id FROM agents WHERE agentToken = ?', ['coinkrazy_agent']);
  if (existing.length === 0) {
    await conn.execute(
      `INSERT INTO agents (agentCode, saldo, agentToken, secretKey, probganho, probbonus, callbackurl) 
       VALUES ('coinkrazy', 10000, 'coinkrazy_agent', 'coinkrazy_secret', '0', '0', NULL)`
    );
    console.log('✓ Default agent inserted');
  } else {
    console.log('✓ Default agent already exists');
  }

  console.log('\nPG Soft database setup complete!');
  await conn.end();
}

setup().catch(err => {
  console.error('Setup error:', err);
  process.exit(1);
});