/**
 * Script d'initialisation de la base de données SQLite
 * Crée les tables et les index nécessaires
 */

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

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

const dbPath = join(__dirname, 'signet.db');
const db = new Database(dbPath);

// Activer les clés étrangères
db.pragma('foreign_keys = ON');

// Table utxos
db.exec(`
  CREATE TABLE IF NOT EXISTS utxos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category TEXT NOT NULL,
    txid TEXT NOT NULL,
    vout INTEGER NOT NULL,
    address TEXT,
    amount REAL NOT NULL,
    confirmations INTEGER DEFAULT 0,
    is_anchor_change BOOLEAN DEFAULT FALSE,
    block_time INTEGER,
    is_spent_onchain BOOLEAN DEFAULT FALSE,
    is_locked_in_mutex BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(txid, vout)
  );

  CREATE INDEX IF NOT EXISTS idx_utxos_category ON utxos(category);
  CREATE INDEX IF NOT EXISTS idx_utxos_txid_vout ON utxos(txid, vout);
  CREATE INDEX IF NOT EXISTS idx_utxos_confirmations ON utxos(confirmations);
  CREATE INDEX IF NOT EXISTS idx_utxos_amount ON utxos(amount);
  CREATE INDEX IF NOT EXISTS idx_utxos_is_spent ON utxos(is_spent_onchain);
`);

// Table anchors (hash_list.txt)
db.exec(`
  CREATE TABLE IF NOT EXISTS anchors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    hash TEXT NOT NULL UNIQUE,
    txid TEXT NOT NULL,
    block_height INTEGER,
    confirmations INTEGER DEFAULT 0,
    date TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

  CREATE INDEX IF NOT EXISTS idx_anchors_hash ON anchors(hash);
  CREATE INDEX IF NOT EXISTS idx_anchors_txid ON anchors(txid);
  CREATE INDEX IF NOT EXISTS idx_anchors_block_height ON anchors(block_height);
`);

// Table fees (fees_list.txt)
db.exec(`
  CREATE TABLE IF NOT EXISTS fees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    txid TEXT NOT NULL UNIQUE,
    fee REAL NOT NULL,
    fee_sats INTEGER NOT NULL,
    block_height INTEGER,
    block_time INTEGER,
    confirmations INTEGER DEFAULT 0,
    change_address TEXT,
    change_amount REAL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

  CREATE INDEX IF NOT EXISTS idx_fees_txid ON fees(txid);
  CREATE INDEX IF NOT EXISTS idx_fees_block_height ON fees(block_height);
`);

// Table cache pour suivre les mises à jour
db.exec(`
  CREATE TABLE IF NOT EXISTS cache (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
`);

console.log('✅ Base de données initialisée avec succès');
console.log(`📁 Fichier: ${dbPath}`);

db.close();