const db = require('./db');

class DatabaseService {

  static async waitForDatabase(maxRetries = 30, delay = 2000) {

    console.log('🔌 Waiting for database connection...');

    

    for (let attempt = 1; attempt <= maxRetries; attempt++) {

      try {

        await db.execute('SELECT 1');

        console.log('✅ Database connection established');

        return;

      } catch (error) {

        if (attempt === maxRetries) {

          throw new Error(`Failed to connect to database after ${maxRetries} attempts: ${error.message}`);

        }

        console.log(`⏳ Database not ready (attempt ${attempt}/${maxRetries}), retrying in ${delay}ms...`);

        await new Promise(resolve => setTimeout(resolve, delay));

      }

    }

  }



  static async migrate() {

    try {

      // Wait for database to be ready

      await this.waitForDatabase();

      

      console.log('🚀 Starting database migration...');

      console.log('📋 Creating tables with proper timestamps and constraints...');

      

      // Create concept_and_test_id_mappings table

      console.log('📊 Creating concept_and_test_id_mappings table...');

      await db.execute(`

        CREATE TABLE IF NOT EXISTS concept_and_test_id_mappings (

          test_id VARCHAR(255) DEFAULT NULL,

          concept_id VARCHAR(255) DEFAULT NULL,

          UUID VARCHAR(255) DEFAULT NULL,

          created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

          updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

          INDEX idx_test_id (test_id),

          INDEX idx_concept_id (concept_id)

        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

      `);

      console.log('✅ concept_and_test_id_mappings table created successfully');



      // Create instruments table

      console.log('🔧 Creating instruments table...');

      await db.execute(`

        CREATE TABLE IF NOT EXISTS instruments (

          id INT(11) NOT NULL AUTO_INCREMENT,

          name VARCHAR(255) NOT NULL,

          type VARCHAR(255) NOT NULL,

          host VARCHAR(255) NOT NULL,

          port VARCHAR(255) NOT NULL,

          is_active TINYINT(1) DEFAULT 1,

          created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

          updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

          PRIMARY KEY (id),

          INDEX idx_is_active (is_active)

        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='1'

      `);

      console.log('✅ instruments table created successfully');



      // Create results table with machine_id foreign key and group_id

      console.log('📈 Creating results table with machine_id and group_id...');

      await db.execute(`

            CREATE TABLE IF NOT EXISTS results (

        id INT(11) NOT NULL AUTO_INCREMENT,

        patient_id VARCHAR(255) DEFAULT NULL,

        accession_id VARCHAR(255) DEFAULT NULL,

        result VARCHAR(1000) NOT NULL,

        test_id VARCHAR(1000) NOT NULL,

        units VARCHAR(1000) NOT NULL,

        reference_range VARCHAR(1000) NOT NULL,

        machine_id INT(11) DEFAULT NULL,

        group_id VARCHAR(255) DEFAULT NULL,

        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

        time_stamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

        PRIMARY KEY (id),

        FOREIGN KEY (machine_id) REFERENCES instruments(id) ON DELETE SET NULL

        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





      `);

      console.log('✅ results table created successfully');



      // Insert initial data into concept_and_test_id_mappings

      console.log('📥 Inserting initial data into concept_and_test_id_mappings...');

      const mappingData = [

        ['169', '7534', '678AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['165', '19005', '165558AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['160', '25206', '1338AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['147', '7463', '679AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['148', '12073', '21AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['150', '3024', '851AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['151', '55798', '1018AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['152', '55829', '1017AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['155', '20397', '165579AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['149', '55895', '1015AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['154', '20361', '165580AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['153', '5727', '729AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['157', '29921', '1307AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['156', '27128', '1309AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['158', '27795', '1308AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['432', '20435', '165578AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['433', '22757', '163426AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'],

        ['171', '10694', '6e96896d-bd71-4ee0-82a0-649e7fa75162'],

        ['173', '10653', '985bf352-a237-4db9-bd76-aeabe3bfbc75'],

        

        ['435', '25547', '1332AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'], // EOS# 

        ['436', '57330', '1024AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'], // EOS% 

        ['437', '26156', '1333AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'], // BASO#

        ['438', '23228', '1341AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA']  // BASO%

      ];



      let insertedMappings = 0;

      for (const [test_id, concept_id, UUID] of mappingData) {

        const [result] = await db.execute(

          `INSERT IGNORE INTO concept_and_test_id_mappings (test_id, concept_id, UUID) VALUES (?, ?, ?)`,

          [test_id, concept_id, UUID]

        );

        if (result.affectedRows > 0) insertedMappings++;

      }

      console.log(`✅ Inserted ${insertedMappings} mappings into concept_and_test_id_mappings`);



      // Insert initial data into instruments

      console.log('📥 Inserting initial data into instruments...');

      const [instrumentResult] = await db.execute(`

        INSERT IGNORE INTO instruments (id, name, type, host, port, is_active) 

        VALUES (1, 'Lab1', 'BC20', '192.168.1.5', '5100', 1)

      `);

      console.log(`✅ Inserted ${instrumentResult.affectedRows} instrument into instruments`);



      console.log('🎉 Database migration completed successfully!');

      console.log('📊 Summary of created tables:');

      console.log('   - concept_and_test_id_mappings');

      console.log('   - instruments');

      console.log('   - results (with machine_id and group_id support)');

      

    } catch (error) {

      console.error('❌ Migration failed:', error.message);

      console.error('🔧 Error details:', error);

      throw error;

    }

  }



  static async delete() {

    try {

      console.log('🗑️ Starting database deletion...');

      console.log('⚠️  WARNING: This will delete all data and tables!');

      

      // Drop tables in reverse order to handle foreign key constraints

      console.log('🔻 Dropping results table...');

      await db.execute('DROP TABLE IF EXISTS results');

      console.log('✅ results table dropped');

      

      console.log('🔻 Dropping instruments table...');

      await db.execute('DROP TABLE IF EXISTS instruments');

      console.log('✅ instruments table dropped');

      

      console.log('🔻 Dropping concept_and_test_id_mappings table...');

      await db.execute('DROP TABLE IF EXISTS concept_and_test_id_mappings');

      console.log('✅ concept_and_test_id_mappings table dropped');

      

      console.log('✅ All tables deleted successfully!');

    } catch (error) {

      console.error('❌ Deletion failed:', error.message);

      console.error('🔧 Error details:', error);

      throw error;

    }

  }



  static async status() {

    try {

      console.log('🔍 Checking database status...');

      

      const tables = [

        'concept_and_test_id_mappings',

        'instruments',

        'results'

      ];

      

      for (const table of tables) {

        try {

          const [rows] = await db.execute(`SHOW TABLES LIKE '${table}'`);

          if (rows.length > 0) {

            const [countRows] = await db.execute(`SELECT COUNT(*) as count FROM ${table}`);

            console.log(`✅ ${table}: exists with ${countRows[0].count} records`);

          } else {

            console.log(`❌ ${table}: does not exist`);

          }

        } catch (error) {

          console.log(`❌ ${table}: error checking status`);

        }

      }

      

    } catch (error) {

      console.error('Error checking database status:', error);

    }

  }

}



// Export the DatabaseService class for use in other modules

module.exports = DatabaseService;



// Only run command line logic if this file is executed directly

if (require.main === module) {

  // Handle command line arguments

  const command = process.argv[2];



  if (command === 'migrate') {

    DatabaseService.migrate()

      .then(() => {

        console.log('✨ Migration process completed');

        process.exit(0);

      })

      .catch((error) => {

        console.log('💥 Migration process failed');

        process.exit(1);

      });

  } else if (command === 'delete') {

    DatabaseService.delete()

      .then(() => {

        console.log('✨ Deletion process completed');

        process.exit(0);

      })

      .catch((error) => {

        console.log('💥 Deletion process failed');

        process.exit(1);

      });

  } else if (command === 'status') {

    DatabaseService.status()

      .then(() => {

        console.log('✨ Status check completed');

        process.exit(0);

      })

      .catch((error) => {

        console.log('💥 Status check failed');

        process.exit(1);

      });

  } else {

    console.log(`

Usage: node db_service.js [command]



Commands:

  migrate   - Create tables and insert initial data

  delete    - Delete all tables

  status    - Check current database status



Examples:

  node db_service.js migrate

  node db_service.js status

  node db_service.js delete

  `);

    process.exit(1);

  }

}



