#!/usr/bin/env node

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

const repoRoot = path.resolve(__dirname, '..');
dotenv.config({ path: path.join(repoRoot, 'database', '.env') });
dotenv.config({ path: path.join(repoRoot, '.env') });
const rulesPath = path.join(repoRoot, 'database', 'rules', 'rules-database.json');

if (!process.env.DB_PASSWORD) {
  throw new Error('DB_PASSWORD must be set in database/.env or .env');
}

const dbConfig = {
  host: process.env.DB_HOST || '192.168.1.113',
  user: process.env.DB_USER || 'deathwatch',
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME || 'deathwatch',
  port: Number(process.env.DB_PORT || 3307)
};

const addColumnIfMissing = async (connection, table, definition) => {
  try {
    await connection.execute(`ALTER TABLE ${table} ADD COLUMN ${definition}`);
  } catch (error) {
    if (error.code !== 'ER_DUP_FIELDNAME') throw error;
  }
};

const main = async () => {
  const data = JSON.parse(fs.readFileSync(rulesPath, 'utf8'));
  const rules = data.rules || [];
  if (!rules.length) throw new Error(`No rules found in ${rulesPath}`);

  const connection = await mysql.createConnection(dbConfig);

  await addColumnIfMissing(connection, 'rules', 'summary 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');

  const [before] = await connection.execute('SELECT COUNT(*) AS cnt FROM rules');

  let upserted = 0;
  for (const rule of rules) {
    await connection.execute(
      `INSERT INTO rules
        (rule_id, title, content, summary, page, page_end, source, source_abbr, category,
         tags, aliases, related_rules, source_method, confidence, midgame_priority)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
       ON DUPLICATE KEY UPDATE
         title=VALUES(title),
         content=VALUES(content),
         summary=VALUES(summary),
         page=VALUES(page),
         page_end=VALUES(page_end),
         source=VALUES(source),
         source_abbr=VALUES(source_abbr),
         category=VALUES(category),
         tags=VALUES(tags),
         aliases=VALUES(aliases),
         related_rules=VALUES(related_rules),
         source_method=VALUES(source_method),
         confidence=VALUES(confidence),
         midgame_priority=VALUES(midgame_priority)`,
      [
        rule.rule_id || rule.id,
        rule.title || '',
        rule.content || '',
        rule.summary || '',
        rule.page || null,
        rule.pageEnd || null,
        rule.source || null,
        rule.sourceAbbr || null,
        rule.category || null,
        JSON.stringify(rule.tags || []),
        JSON.stringify(rule.aliases || []),
        JSON.stringify(rule.relatedRules || []),
        rule.sourceMethod || null,
        rule.confidence == null ? null : Number(rule.confidence),
        rule.midgamePriority || 0
      ]
    );
    upserted += 1;
  }

  const [after] = await connection.execute('SELECT COUNT(*) AS cnt FROM rules');
  await connection.end();

  console.log(JSON.stringify({
    rulesFile: path.relative(repoRoot, rulesPath),
    upserted,
    before: before[0].cnt,
    after: after[0].cnt
  }, null, 2));
};

main().catch(error => {
  console.error(error.message);
  process.exit(1);
});