market-data-service/utils/databaseInit.js

const { executeQuery, executeNonQuery } = require('../config/database');
const { logger } = require('./logger');
const fs = require('fs');
const path = require('path');

class DatabaseInitializer {
  constructor() {
    this.initialized = false;
  }

  async initialize() {
    if (this.initialized) {
      logger.info('Database already initialized');
      return;
    }

    try {
      logger.info('Starting database initialization...');

      // Create tables based on database type
      const dbType = process.env.DB_TYPE || 'sqlite';

      switch (dbType) {
        case 'mysql':
        case 'postgres':
          await this.initializeRelationalDatabase();
          break;
        case 'sqlite':
        default:
          await this.initializeSQLite();
          break;
      }

      // Insert sample data
      await this.insertSampleData();

      this.initialized = true;
      logger.info('Database initialization completed successfully');

    } catch (error) {
      logger.error('Database initialization failed:', error);
      throw error;
    }
  }

  async initializeRelationalDatabase() {
    try {
      const dbType = process.env.DB_TYPE || 'mysql';

      if (dbType === 'mysql') {
        await this.initializeMySQL();
      } else if (dbType === 'postgres') {
        await this.initializePostgreSQL();
      }

    } catch (error) {
      logger.error('Error creating relational database tables:', error);
      throw error;
    }
  }

  async initializeMySQL() {
    try {
      // Create price_data table for MySQL
      const createPriceTableQuery = `
        CREATE TABLE IF NOT EXISTS price_data (
          id INT PRIMARY KEY AUTO_INCREMENT,
          symbol VARCHAR(10) NOT NULL,
          timeframe VARCHAR(5) NOT NULL,
          timestamp DATETIME NOT NULL,
          open DECIMAL(15,8) NOT NULL,
          high DECIMAL(15,8) NOT NULL,
          low DECIMAL(15,8) NOT NULL,
          close DECIMAL(15,8) NOT NULL,
          volume BIGINT NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_symbol_timeframe (symbol, timeframe),
          INDEX idx_timestamp (timestamp),
          INDEX idx_symbol_timestamp (symbol, timestamp)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
      `;

      await executeNonQuery(createPriceTableQuery);
      logger.info('MySQL price data table created successfully');

      // Create symbols table for MySQL
      const createSymbolsTableQuery = `
        CREATE TABLE IF NOT EXISTS symbols (
          id INT PRIMARY KEY AUTO_INCREMENT,
          symbol VARCHAR(10) UNIQUE NOT NULL,
          name VARCHAR(100) NOT NULL,
          category VARCHAR(20) NOT NULL,
          exchange VARCHAR(20) NOT NULL,
          is_active BOOLEAN DEFAULT TRUE,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_symbol (symbol),
          INDEX idx_category (category),
          INDEX idx_exchange (exchange)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
      `;

      await executeNonQuery(createSymbolsTableQuery);
      logger.info('MySQL symbols table created successfully');

    } catch (error) {
      logger.error('Error creating MySQL tables:', error);
      throw error;
    }
  }

  async initializePostgreSQL() {
    try {
      // Create price_data table for PostgreSQL
      const createPriceTableQuery = `
        CREATE TABLE IF NOT EXISTS price_data (
          id SERIAL PRIMARY KEY,
          symbol VARCHAR(10) NOT NULL,
          timeframe VARCHAR(5) NOT NULL,
          timestamp TIMESTAMP NOT NULL,
          open DECIMAL(15,8) NOT NULL,
          high DECIMAL(15,8) NOT NULL,
          low DECIMAL(15,8) NOT NULL,
          close DECIMAL(15,8) NOT NULL,
          volume BIGINT NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
      `;

      await executeNonQuery(createPriceTableQuery);
      logger.info('PostgreSQL price data table created successfully');

      // Create indexes for PostgreSQL
      const createIndexes = [
        'CREATE INDEX IF NOT EXISTS idx_symbol_timeframe ON price_data(symbol, timeframe);',
        'CREATE INDEX IF NOT EXISTS idx_timestamp ON price_data(timestamp);',
        'CREATE INDEX IF NOT EXISTS idx_symbol_timestamp ON price_data(symbol, timestamp);'
      ];

      for (const indexQuery of createIndexes) {
        await executeNonQuery(indexQuery);
      }

      logger.info('PostgreSQL indexes created successfully');

      // Create symbols table for PostgreSQL
      const createSymbolsTableQuery = `
        CREATE TABLE IF NOT EXISTS symbols (
          id SERIAL PRIMARY KEY,
          symbol VARCHAR(10) UNIQUE NOT NULL,
          name VARCHAR(100) NOT NULL,
          category VARCHAR(20) NOT NULL,
          exchange VARCHAR(20) NOT NULL,
          is_active BOOLEAN DEFAULT TRUE,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
      `;

      await executeNonQuery(createSymbolsTableQuery);
      logger.info('PostgreSQL symbols table created successfully');

    } catch (error) {
      logger.error('Error creating PostgreSQL tables:', error);
      throw error;
    }
  }

  async initializeSQLite() {
    try {
      // Create price_data table
      const createPriceTableQuery = `
        CREATE TABLE IF NOT EXISTS price_data (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          symbol TEXT NOT NULL,
          timeframe TEXT NOT NULL,
          timestamp DATETIME NOT NULL,
          open REAL NOT NULL,
          high REAL NOT NULL,
          low REAL NOT NULL,
          close REAL NOT NULL,
          volume INTEGER NOT NULL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );
      `;

      await executeNonQuery(createPriceTableQuery);
      logger.info('SQLite price data table created successfully');

      // Create indexes for better performance
      const createIndexes = [
        'CREATE INDEX IF NOT EXISTS idx_symbol_timeframe ON price_data(symbol, timeframe);',
        'CREATE INDEX IF NOT EXISTS idx_timestamp ON price_data(timestamp);',
        'CREATE INDEX IF NOT EXISTS idx_symbol_timestamp ON price_data(symbol, timestamp);'
      ];

      for (const indexQuery of createIndexes) {
        await executeNonQuery(indexQuery);
      }

      logger.info('SQLite indexes created successfully');

      // Create symbols table
      const createSymbolsTableQuery = `
        CREATE TABLE IF NOT EXISTS symbols (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          symbol TEXT UNIQUE NOT NULL,
          name TEXT NOT NULL,
          category TEXT NOT NULL,
          exchange TEXT NOT NULL,
          is_active INTEGER DEFAULT 1,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );
      `;

      await executeNonQuery(createSymbolsTableQuery);
      logger.info('SQLite symbols table created successfully');

    } catch (error) {
      logger.error('Error creating SQLite tables:', error);
      throw error;
    }
  }

  async insertSampleData() {
    try {
      const dbType = process.env.DB_TYPE || 'sqlite';

      // Check if symbols already exist
      let existingSymbols;
      if (dbType === 'sqlite') {
        existingSymbols = await executeQuery('SELECT COUNT(*) as count FROM symbols');
      } else {
        existingSymbols = await executeQuery('SELECT COUNT(*) as count FROM symbols');
      }

      // Handle different database result formats
      let symbolCount = 0;
      if (existingSymbols && existingSymbols.length > 0) {
        symbolCount = existingSymbols[0].count || 0;
      }

      if (symbolCount > 0) {
        logger.info('Sample symbols already exist, skipping insertion');
        return;
      }

      // Insert sample symbols
      const sampleSymbols = [
        { symbol: 'EURUSD', name: 'Euro vs US Dollar', category: 'forex', exchange: 'FX' },
        { symbol: 'GBPUSD', name: 'British Pound vs US Dollar', category: 'forex', exchange: 'FX' },
        { symbol: 'USDJPY', name: 'US Dollar vs Japanese Yen', category: 'forex', exchange: 'FX' },
        { symbol: 'USDCHF', name: 'US Dollar vs Swiss Franc', category: 'forex', exchange: 'FX' },
        { symbol: 'USDCAD', name: 'US Dollar vs Canadian Dollar', category: 'forex', exchange: 'FX' },
        { symbol: 'AUDUSD', name: 'Australian Dollar vs US Dollar', category: 'forex', exchange: 'FX' },
        { symbol: 'NZDUSD', name: 'New Zealand Dollar vs US Dollar', category: 'forex', exchange: 'FX' },
        { symbol: 'BTCUSD', name: 'Bitcoin vs US Dollar', category: 'crypto', exchange: 'Crypto' },
        { symbol: 'ETHUSD', name: 'Ethereum vs US Dollar', category: 'crypto', exchange: 'Crypto' },
        { symbol: 'AAPL', name: 'Apple Inc.', category: 'stocks', exchange: 'NASDAQ' },
        { symbol: 'GOOGL', name: 'Alphabet Inc.', category: 'stocks', exchange: 'NASDAQ' },
        { symbol: 'MSFT', name: 'Microsoft Corporation', category: 'stocks', exchange: 'NASDAQ' },
        { symbol: 'TSLA', name: 'Tesla Inc.', category: 'stocks', exchange: 'NASDAQ' },
        { symbol: 'AMZN', name: 'Amazon.com Inc.', category: 'stocks', exchange: 'NASDAQ' },
        { symbol: 'META', name: 'Meta Platforms Inc.', category: 'stocks', exchange: 'NASDAQ' }
      ];

      for (const symbolData of sampleSymbols) {
        let insertQuery;
        if (dbType === 'sqlite') {
          insertQuery = `
            INSERT OR IGNORE INTO symbols (symbol, name, category, exchange, is_active)
            VALUES (?, ?, ?, ?, 1)
          `;
        } else if (dbType === 'postgres') {
          insertQuery = `
            INSERT INTO symbols (symbol, name, category, exchange, is_active)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT (symbol) DO NOTHING
          `;
        } else {
          insertQuery = `
            INSERT IGNORE INTO symbols (symbol, name, category, exchange, is_active)
            VALUES (?, ?, ?, ?, 1)
          `;
        }

        await executeNonQuery(insertQuery, [
          symbolData.symbol,
          symbolData.name,
          symbolData.category,
          symbolData.exchange,
          true
        ]);
      }

      logger.info(`Inserted ${sampleSymbols.length} sample symbols`);

      // Generate sample price data for the last 1000 H1 candles
      await this.generateSamplePriceData(sampleSymbols.slice(0, 7)); // Generate for first 7 symbols

    } catch (error) {
      logger.error('Error inserting sample data:', error);
      throw error;
    }
  }

  async generateSamplePriceData(symbols) {
    try {
      logger.info('Generating sample price data...');

      const now = new Date();
      const dbType = process.env.DB_TYPE || 'sqlite';

      for (const symbolData of symbols) {
        logger.info(`Generating price data for ${symbolData.symbol}...`);

        // Generate 1000 H1 candles (about 41 days of data)
        const priceData = [];
        let currentPrice = this.getInitialPrice(symbolData.symbol);

        for (let i = 999; i >= 0; i--) {
          const timestamp = new Date(now.getTime() - (i * 60 * 60 * 1000)); // H1 intervals
          const open = currentPrice;

          // Generate realistic price movement (±1%)
          const change = (Math.random() - 0.5) * 0.02;
          const close = open * (1 + change);

          const high = Math.max(open, close) * (1 + Math.random() * 0.001);
          const low = Math.min(open, close) * (1 - Math.random() * 0.001);
          const volume = Math.floor(Math.random() * 1000000) + 100000;

          priceData.push({
            symbol: symbolData.symbol,
            timeframe: 'H1',
            timestamp,
            open: open.toFixed(8),
            high: high.toFixed(8),
            low: low.toFixed(8),
            close: close.toFixed(8),
            volume
          });

          currentPrice = close;
        }

        // Insert price data in batches
        const batchSize = 100;
        for (let i = 0; i < priceData.length; i += batchSize) {
          const batch = priceData.slice(i, i + batchSize);

          for (const price of batch) {
            let insertQuery;
            if (dbType === 'sqlite') {
              insertQuery = `
                INSERT OR IGNORE INTO price_data (symbol, timeframe, timestamp, open, high, low, close, volume)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
              `;
            } else if (dbType === 'postgres') {
              insertQuery = `
                INSERT INTO price_data (symbol, timeframe, timestamp, open, high, low, close, volume)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT DO NOTHING
              `;
            } else {
              insertQuery = `
                INSERT IGNORE INTO price_data (symbol, timeframe, timestamp, open, high, low, close, volume)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
              `;
            }

            await executeNonQuery(insertQuery, [
              price.symbol,
              price.timeframe,
              price.timestamp,
              price.open,
              price.high,
              price.low,
              price.close,
              price.volume
            ]);
          }
        }

        logger.info(`Generated ${priceData.length} price records for ${symbolData.symbol}`);
      }

      logger.info('Sample price data generation completed');

    } catch (error) {
      logger.error('Error generating sample price data:', error);
      throw error;
    }
  }

  getInitialPrice(symbol) {
    const initialPrices = {
      'EURUSD': 1.0850,
      'GBPUSD': 1.2650,
      'USDJPY': 150.50,
      'USDCHF': 0.9150,
      'USDCAD': 1.3450,
      'AUDUSD': 0.6550,
      'NZDUSD': 0.6050
    };

    return initialPrices[symbol] || 1.0000;
  }

  async reset() {
    try {
      logger.info('Resetting database...');

      const dbType = process.env.DB_TYPE || 'sqlite';

      // Drop tables
      if (dbType === 'sqlite') {
        await executeNonQuery('DROP TABLE IF EXISTS price_data');
        await executeNonQuery('DROP TABLE IF EXISTS symbols');
      } else {
        await executeNonQuery('DROP TABLE IF EXISTS price_data');
        await executeNonQuery('DROP TABLE IF EXISTS symbols');
      }

      this.initialized = false;
      logger.info('Database reset completed');

      // Reinitialize
      await this.initialize();

    } catch (error) {
      logger.error('Error resetting database:', error);
      throw error;
    }
  }

  async getStats() {
    try {
      const dbType = process.env.DB_TYPE || 'sqlite';

      let symbolCount, priceCount;

      if (dbType === 'sqlite') {
        symbolCount = await executeQuery('SELECT COUNT(*) as count FROM symbols');
        priceCount = await executeQuery('SELECT COUNT(*) as count FROM price_data');
      } else {
        symbolCount = await executeQuery('SELECT COUNT(*) as count FROM symbols');
        priceCount = await executeQuery('SELECT COUNT(*) as count FROM price_data');
      }

      // Handle different database result formats
      let symbols = 0;
      let priceRecords = 0;

      if (symbolCount && symbolCount.length > 0) {
        symbols = symbolCount[0].count || 0;
      }

      if (priceCount && priceCount.length > 0) {
        priceRecords = priceCount[0].count || 0;
      }

      return {
        symbols,
        price_records: priceRecords,
        initialized: this.initialized
      };

    } catch (error) {
      logger.error('Error getting database stats:', error);
      return {
        symbols: 0,
        price_records: 0,
        initialized: false
      };
    }
  }
}

const databaseInitializer = new DatabaseInitializer();

// Export functions for external use
const initializeDatabase = () => databaseInitializer.initialize();
const resetDatabase = () => databaseInitializer.reset();
const getDatabaseStats = () => databaseInitializer.getStats();

module.exports = {
  DatabaseInitializer,
  initializeDatabase,
  resetDatabase,
  getDatabaseStats,
  databaseInitializer
};