market-data-service/utils/databaseInit.js

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

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 || 'postgres';

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

      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 || 'postgres';

      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 candles_h1 table for storing H1 candlestick data
      const createCandlesH1TableQuery = `
        CREATE TABLE IF NOT EXISTS candles_h1 (
          id INT AUTO_INCREMENT PRIMARY KEY,
          symbol TEXT NOT NULL,
          market TEXT NOT NULL,
          open FLOAT NOT NULL,
          high FLOAT NOT NULL,
          low FLOAT NOT NULL,
          close FLOAT NOT NULL,
          volume FLOAT NOT NULL,
          timestamp TIMESTAMP NOT NULL,
          UNIQUE KEY unique_symbol_timestamp (symbol, timestamp),
          INDEX idx_symbol (symbol),
          INDEX idx_market (market),
          INDEX idx_timestamp (timestamp)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
      `;

      await executeNonQuery(createCandlesH1TableQuery);
      logger.info('MySQL candles_h1 table created successfully');

      // Create live_prices table for real-time price updates
      const createLivePricesTableQuery = `
        CREATE TABLE IF NOT EXISTS live_prices (
          id INT AUTO_INCREMENT PRIMARY KEY,
          symbol TEXT NOT NULL,
          market TEXT NOT NULL,
          price FLOAT NOT NULL,
          updated_at TIMESTAMP NOT NULL,
          UNIQUE KEY unique_symbol_market (symbol, market),
          INDEX idx_symbol (symbol),
          INDEX idx_market (market),
          INDEX idx_updated_at (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
      `;

      await executeNonQuery(createLivePricesTableQuery);
      logger.info('MySQL live_prices table created successfully');

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

  async initializePostgreSQL() {
    try {
      // Create candles_h1 table for PostgreSQL
      const createCandlesH1TableQuery = `
        CREATE TABLE IF NOT EXISTS candles_h1 (
          id SERIAL PRIMARY KEY,
          symbol TEXT NOT NULL,
          market TEXT NOT NULL,
          open FLOAT NOT NULL,
          high FLOAT NOT NULL,
          low FLOAT NOT NULL,
          close FLOAT NOT NULL,
          volume FLOAT NOT NULL,
          timestamp TIMESTAMP NOT NULL,
          UNIQUE(symbol, timestamp)
        );
      `;

      await executeNonQuery(createCandlesH1TableQuery);
      logger.info('PostgreSQL candles_h1 table created successfully');

      // Create indexes for candles_h1
      const createCandlesH1Indexes = [
        'CREATE INDEX IF NOT EXISTS idx_candles_symbol ON candles_h1(symbol);',
        'CREATE INDEX IF NOT EXISTS idx_candles_market ON candles_h1(market);',
        'CREATE INDEX IF NOT EXISTS idx_candles_timestamp ON candles_h1(timestamp);'
      ];

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

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

      // Create live_prices table for PostgreSQL
      const createLivePricesTableQuery = `
        CREATE TABLE IF NOT EXISTS live_prices (
          id SERIAL PRIMARY KEY,
          symbol TEXT NOT NULL,
          market TEXT NOT NULL,
          price FLOAT NOT NULL,
          updated_at TIMESTAMP NOT NULL,
          UNIQUE(symbol, market)
        );
      `;

      await executeNonQuery(createLivePricesTableQuery);
      logger.info('PostgreSQL live_prices table created successfully');

      // Create indexes for live_prices
      const createLivePricesIndexes = [
        'CREATE INDEX IF NOT EXISTS idx_live_symbol ON live_prices(symbol);',
        'CREATE INDEX IF NOT EXISTS idx_live_market ON live_prices(market);',
        'CREATE INDEX IF NOT EXISTS idx_live_updated_at ON live_prices(updated_at);'
      ];

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

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

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

  async initializeSQLite() {
    try {
      // Create candles_h1 table for SQLite
      const createCandlesH1TableQuery = `
        CREATE TABLE IF NOT EXISTS candles_h1 (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          symbol TEXT NOT NULL,
          market TEXT NOT NULL,
          open REAL NOT NULL,
          high REAL NOT NULL,
          low REAL NOT NULL,
          close REAL NOT NULL,
          volume REAL NOT NULL,
          timestamp DATETIME NOT NULL,
          UNIQUE(symbol, timestamp)
        );
      `;

      await executeNonQuery(createCandlesH1TableQuery);
      logger.info('SQLite candles_h1 table created successfully');

      // Create indexes for candles_h1
      const createCandlesH1Indexes = [
        'CREATE INDEX IF NOT EXISTS idx_candles_symbol ON candles_h1(symbol);',
        'CREATE INDEX IF NOT EXISTS idx_candles_market ON candles_h1(market);',
        'CREATE INDEX IF NOT EXISTS idx_candles_timestamp ON candles_h1(timestamp);'
      ];

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

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

      // Create live_prices table for SQLite
      const createLivePricesTableQuery = `
        CREATE TABLE IF NOT EXISTS live_prices (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          symbol TEXT NOT NULL,
          market TEXT NOT NULL,
          price REAL NOT NULL,
          updated_at DATETIME NOT NULL,
          UNIQUE(symbol, market)
        );
      `;

      await executeNonQuery(createLivePricesTableQuery);
      logger.info('SQLite live_prices table created successfully');

      // Create indexes for live_prices
      const createLivePricesIndexes = [
        'CREATE INDEX IF NOT EXISTS idx_live_symbol ON live_prices(symbol);',
        'CREATE INDEX IF NOT EXISTS idx_live_market ON live_prices(market);',
        'CREATE INDEX IF NOT EXISTS idx_live_updated_at ON live_prices(updated_at);'
      ];

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

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

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

  // Removed all sample data generation methods as per user requirements

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

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

      // Drop tables in correct order (considering foreign key constraints)
      if (dbType === 'sqlite') {
        await executeNonQuery('DROP TABLE IF EXISTS candles_h1');
        await executeNonQuery('DROP TABLE IF EXISTS live_prices');
      } else {
        await executeNonQuery('DROP TABLE IF EXISTS candles_h1');
        await executeNonQuery('DROP TABLE IF EXISTS live_prices');
      }

      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 || 'postgres';

      let candlesH1Count, livePricesCount;

      if (dbType === 'sqlite') {
        candlesH1Count = await executeQuery('SELECT COUNT(*) as count FROM candles_h1');
        livePricesCount = await executeQuery('SELECT COUNT(*) as count FROM live_prices');
      } else {
        candlesH1Count = await executeQuery('SELECT COUNT(*) as count FROM candles_h1');
        livePricesCount = await executeQuery('SELECT COUNT(*) as count FROM live_prices');
      }

      // Handle different database result formats
      let candlesH1 = 0;
      let livePrices = 0;

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

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

      return {
        candles_h1: candlesH1,
        live_prices: livePrices,
        initialized: this.initialized
      };

    } catch (error) {
      logger.error('Error getting database stats:', error);
      return {
        candles_h1: 0,
        live_prices: 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
};