import 'dart:async';
import 'dart:io';
import 'dart:core';

import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:sqflite_common_ffi_web/sqflite_ffi_web.dart';
import 'package:trialOrderPro/utils/constant/database_constant.dart';

import '../../all_imports.dart';
import 'package:path/path.dart' as p;


class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._();
  late Database _database;
  late Database _newDatabase;
  bool _isInitialized = false;
  bool _isInitializedNew = false;
  String? dbPath;
  String? newdbpath;

  DatabaseHelper._();

  Future<Database> get database async {
    if (_database.isOpen) return _database;

    if (!_isInitialized) {
      await initialize();
    }

    return _database;
  }

  Future<Database> get newDatabase async {
    if (_newDatabase.isOpen) return _newDatabase;

    if (!_isInitializedNew) {
      await initializeNewDB();
    }

    return _newDatabase;
  }

  Future<void> initialize() async {
    if (_isInitialized) return;

    if (kIsWeb) {
      databaseFactory = databaseFactoryFfiWeb;
    } else if (Platform.isLinux || Platform.isWindows) {
      // Use the ffi version on linux and windows
      sqfliteFfiInit();
      databaseFactory = databaseFactoryFfi;
    }
    // Get a location using getDatabasesPath
    var databasesPath = await getDatabasesPath();
    String path = p.join(databasesPath, database_name);
    dbPath = path;

       var sqliteLib = await databaseFactory.openDatabase(path,
        options: OpenDatabaseOptions(
          version: 2,
          onCreate: _onCreate,
          onUpgrade: _onUpdate
        ));
    _onCreate(sqliteLib, 1);
    _onUpdate(sqliteLib, 1,2);

    _database = sqliteLib;
    _isInitialized = true;
  }


  Future<void> initializeNewDB() async {
    if (_isInitializedNew) return;

    if (kIsWeb) {
      databaseFactory = databaseFactoryFfiWeb;
    } else if (Platform.isLinux || Platform.isWindows) {
      // Use the ffi version on linux and windows
      sqfliteFfiInit();
      databaseFactory = databaseFactoryFfi;
    }
    // Get a location using getDatabasesPath
    var databasesPath = await getDatabasesPath();
    String path = p.join(databasesPath, new_database_name);
    newdbpath = path;

       var sqliteLib = await databaseFactory.openDatabase(path,
        options: OpenDatabaseOptions(
          version: 1,
          onCreate: _onCreateNewDB,
          onUpgrade: _onUpdateNewDB
        ));
    _onCreateNewDB(sqliteLib, 1);
    _onUpdateNewDB(sqliteLib, 1,2);

    _newDatabase = sqliteLib;
    _isInitializedNew = true;
  }  


  Future _onCreate(Database db, int version) async {

      usersTable(db);
      customerDetailsTable(db);
      cartDetailsTable(db);
      orderHistoryTable(db);
      orderHistoryDetailTable(db);
      // outsideFitLensTable(db);
      topDachCabinetComponentTable(db);
  }

   Future _onCreateNewDB(Database db, int version) async {

      orderHistoryTable(db);
      orderHistoryDetailTable(db);

  }
  Future _onUpdate(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      // You can perform any necessary actions here, even if there are no schema changes.
      await db.execute('ALTER TABLE $order_history_table DROP COLUMN isshipment');
       var result = await db.rawQuery('PRAGMA table_info($order_history_table)');
        print("Columns in $order_history_table");
       for (var row in result) {
       print(row['name']); // Prints each column name
  }
      // Update the database version to the new version.
      //await db.updateVersion(newVersion);
    }
  }

Future _onUpdateNewDB(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < newVersion) {
      // You can perform any necessary actions here, even if there are no schema changes.

      // Update the database version to the new version.
      //await db.updateVersion(newVersion);
    }
  }
}


Future<void> orderHistoryTable(Database db) async {
  await db.execute('''
      CREATE TABLE IF NOT EXISTS $order_history_table (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT,
        repId TEXT,
        orderId TEXT,
        accountNo TEXT,
        accountName TEXT,
        doorwayId TEXT,
        shipTo TEXT,
        street TEXT,
        city TEXT,
        state TEXT,
        zip TEXT,
        quantity TEXT,
        status TEXT
      )
    ''');
}          /// TrackingNumberChanges=>Add Tracking number in order history table.



Future<void> customerDetailsTable(Database db) async {
  await db.execute('''
      CREATE TABLE IF NOT EXISTS $customer_details_table (
        Id_From_LocalDB INTEGER PRIMARY KEY AUTOINCREMENT,
        Id TEXT,
        Name TEXT,
        Doorway_Id__c TEXT,
        Bill_To_ID__c TEXT,
        Ship_To_Id__c TEXT,
        Primary_Ship_To__c TEXT,
        Primary_Ship_To_Name__c TEXT,
        ShippingStreet TEXT,
        ShippingCity TEXT,
        ShippingState TEXT,
        ShippingCountry TEXT,
        ShippingPostalCode TEXT,
        National_Account_Retail_Detail__c TEXT,
        Trials_Shipped_by_1800__c TEXT,
        T_O_P_Eligilble__c TEXT,
        X1800_Account_Number__c TEXT
      )
    ''');
}

Future<void> usersTable(Database db) async {
  await db.execute('''
      CREATE TABLE IF NOT EXISTS $user_table (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        odataContext TEXT,
        displayName TEXT,
        givenName TEXT,
        jobTitle TEXT,
        mail TEXT,
        mobilePhone TEXT,
        officeLocation TEXT,
        preferredLanguage TEXT,
        surname TEXT,
        userPrincipalName TEXT,
        userId TEXT
        
      )
    ''');
}

Future<void> cartDetailsTable(Database db) async {
  await db.execute('''
          CREATE TABLE IF NOT EXISTS $cart_table (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            brand TEXT,
            indication TEXT,
            drawer TEXT,
            lensKey TEXT,
            lensValue TEXT,
            qty INTEGER,
            customerId TEXT,
            axis TEXT,
            highAdd TEXT,
            lowAdd TEXT,
            sku TEXT,
            cyl TEXT,
            range TEXT
          )
        ''');
}

Future<void> orderHistoryDetailTable(Database db) async {
  await db.execute('''
          CREATE TABLE IF NOT EXISTS $orderhistory_detail_table (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
            orderId TEXT,
            brand TEXT,
            indication TEXT,
            drawer TEXT,
            lensKey TEXT,
            lensValue TEXT,
            qty INTEGER,
            customerId TEXT,
            axis TEXT,
            highAdd TEXT,
            lowAdd TEXT,
            sku TEXT,
            cyl TEXT,
            range TEXT
          )
        ''');
}

Future<void> outsideFitLensTable(Database db) async {
  await db.execute('''
    CREATE TABLE IF NOT EXISTS $outside_fit_lens_table (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      sku TEXT,
      brand TEXT,
      indication TEXT,
      power TEXT,
      cylinder TEXT,
      axis TEXT,
      qty INTEGER,
      addValue TEXT
    )
  ''');
}

Future<void> topDachCabinetComponentTable(Database db) async {
  await db.execute('''
    CREATE TABLE IF NOT EXISTS $topdach_cabinet_component_table (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      sku TEXT,
      componentid TEXT
    )
  ''');
}

Future<void> copyDataToNewDatabase(Database _database, Database _newDatabase) async {
  // Copy data from orderHistoryTable

  List<Map<String, dynamic>> orderHistoryData = await _database.query(order_history_table);
  for (var newRow in orderHistoryData) {
      await _newDatabase.insert(order_history_table, newRow, conflictAlgorithm: ConflictAlgorithm.replace);
  }

  List<Map<String, dynamic>> orderHistoryDetailData = await _database.query(orderhistory_detail_table);
  for (var newRow in orderHistoryDetailData) {
      await _newDatabase.insert(orderhistory_detail_table, newRow, conflictAlgorithm: ConflictAlgorithm.replace);
  }
}


its shows this debugs console 
E/SQLiteLog( 3256): (1) no such column: "isshipment" in "ALTER TABLE order_history DROP COLUMN isshipment"
I/flutter ( 3256): Columns in order_history
I/flutter ( 3256): id
I/flutter ( 3256): date
I/flutter ( 3256): repId
I/flutter ( 3256): orderId
I/flutter ( 3256): accountNo
I/flutter ( 3256): accountName
I/flutter ( 3256): doorwayId
I/flutter ( 3256): shipTo
I/flutter ( 3256): street
I/flutter ( 3256): city
I/flutter ( 3256): state
I/flutter ( 3256): zip
I/flutter ( 3256): quantity
I/flutter ( 3256): status
E/flutter ( 3256): [ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: DatabaseException(no such column: "isshipment" (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE order_history DROP COLUMN isshipment) sql 'ALTER TABLE order_history DROP COLUMN isshipment' args []
E/flutter ( 3256): #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): #1      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:33:16)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): #2      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:517:14)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): #3      DatabaseHelper._onUpdate (package:trialOrderPro/data/helper/database_helper.dart:123:7)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): 
E/SQLiteLog( 3256): (1) table order_history has no column named isshipment in "INSERT OR REPLACE INTO order_history (id, date, repId, orderId, accountNo, accountName, doorwayId, shipTo, street, city, state, zip, quantity, status, iss
E/flutter ( 3256): [ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: DatabaseException(table order_history has no column named isshipment (code 1 SQLITE_ERROR): , while compiling: INSERT OR REPLACE INTO order_history (id, date, repId, orderId, accountNo, accountName, doorwayId, shipTo, street, city, state, zip, quantity, status, isshipment) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) sql 'INSERT OR REPLACE INTO order_history (id, date, repId, orderId, accountNo, accountName, doorwayId, shipTo, street, city, state, zip, quantity, status, isshipment) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' args [1, Nov 08, 2024T13:27:512, sandeep.chandekar, BL_SA_292618270, , DR Faith Chang, 14896, , 3901 Main St Ste 602, Flushing, NY, 11354-5482, 3, PENDING, ]
E/flutter ( 3256): #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): #1      SqfliteDatabaseMixin.txnRawInsert.<anonymous closure> (package:sqflite_common/src/database_mixin.dart:576:14)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): #2      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:33:16)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): #3      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:517:14)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): #4      copyDataToNewDatabase (package:trialOrderPro/data/helper/database_helper.dart:291:7)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): #5      AwsController.downloadOrderHistorDatabaseFile (package:trialOrderPro/modules/cart/controllers/aws_s3_controller.dart:127:5)
E/flutter ( 3256): <asynchronous suspension>
E/flutter ( 3256): #6      OrderHistoryListControllerGetX.syncOrderHistory (package:trialOrderPro/modules/order_history/controllers/order_history_list_screen_controller.dart:146:8)
E/flutter ( 3256): <asynchronous suspension>