import * as SQLite from 'expo-sqlite';
import { FileSystem } from 'src/utils';
import { Asset } from 'expo-asset';
import { API_HOST } from 'src/constants';
import { storage } from 'src/storage';
import { fetchLastDareDbUpdate, fetchLastRegionsDbUpdate } from '@api/app';
import NetInfo from '@react-native-community/netinfo';

let db1: SQLite.SQLiteDatabase | null = null;
let db2: SQLite.SQLiteDatabase | null = null;
let db3: SQLite.SQLiteDatabase | null = null;

type DbKey = 'nm' | 'darePlaces' | 'countries';

const refreshState: Record<DbKey, { isRefreshing: boolean; promise: Promise<void> | null }> = {
  nm: { isRefreshing: false, promise: null },
  darePlaces: { isRefreshing: false, promise: null },
  countries: { isRefreshing: false, promise: null }
};
const nmRegionsDBname = 'nmRegions.db';
const darePlacesDBname = 'darePlaces.db';
const countriesDBname = 'nmCountries.db';
const sqliteDirectory = 'SQLite';
const sqliteFullPath = FileSystem.documentDirectory + sqliteDirectory;
const DS = '/';

export function isNmDbRefreshing(): boolean {
  return refreshState.nm.isRefreshing;
}

function runRefresh(key: DbKey, fn: () => Promise<void>): Promise<void> {
  const state = refreshState[key];
  if (state.isRefreshing && state.promise) {
    console.log(`[DB] "${key}" already refreshing, reusing promise`);
    return state.promise;
  }
  state.isRefreshing = true;
  state.promise = fn().finally(() => {
    state.isRefreshing = false;
    state.promise = null;
  });
  return state.promise;
}

async function copyDatabaseFile(dbName: string, dbAsset: Asset) {
  const state = await NetInfo.fetch();

  if (!state.isConnected || state.isInternetReachable === false) return;

  if (state.isInternetReachable === null) {
    await new Promise((resolve) => setTimeout(resolve, 1000));
    const retryState = await NetInfo.fetch();
    if (!retryState.isConnected || retryState.isInternetReachable === false) return;
  }

  console.log('DB copy start - ' + dbName);
  await dbAsset.downloadAsync();

  const dbUri = sqliteFullPath + DS + dbName;

  await FileSystem.copyAsync({
    from: dbAsset.localUri ?? '',
    to: dbUri
  });
  return dbUri;
}

async function isSQLiteFileValid(dbPath: string): Promise<boolean> {
  try {
    const db = await SQLite.openDatabaseAsync(dbPath, {
      useNewConnection: true
    });
    const result = await db.getAllAsync(`SELECT name FROM sqlite_master WHERE type='table'`);
    return Array.isArray(result) && result.length > 0;
  } catch (error) {
    console.warn('Invalid SQLite file:', dbPath, error);
    return false;
  }
}

export async function openDatabases() {
  try {
    const fileInfo = await FileSystem.getInfoAsync(sqliteFullPath);
    if (!fileInfo.exists) {
      await FileSystem.makeDirectoryAsync(sqliteFullPath, { intermediates: true });
    }

    const nmRegionsPath = sqliteFullPath + DS + nmRegionsDBname;
    const nmRegionsDB = await FileSystem.getInfoAsync(nmRegionsPath);
    if (
      !nmRegionsDB.exists ||
      nmRegionsDB.size === 0 ||
      !(await isSQLiteFileValid(nmRegionsDBname))
    ) {
      if (nmRegionsDB.exists) {
        await FileSystem.deleteAsync(nmRegionsPath, { idempotent: true });
      }

      await copyDatabaseFile(
        nmRegionsDBname,
        Asset.fromModule(require('../../assets/db/' + nmRegionsDBname))
      );
    }

    const darePlacesPath = sqliteFullPath + DS + darePlacesDBname;
    const darePlacesDB = await FileSystem.getInfoAsync(darePlacesPath);
    if (
      !darePlacesDB.exists ||
      darePlacesDB.size === 0 ||
      !(await isSQLiteFileValid(darePlacesDBname))
    ) {
      if (darePlacesDB.exists) {
        await FileSystem.deleteAsync(darePlacesPath, { idempotent: true });
      }

      await copyDatabaseFile(
        darePlacesDBname,
        Asset.fromModule(require('../../assets/db/' + darePlacesDBname))
      );
    }

    const countriesPath = sqliteFullPath + DS + countriesDBname;
    const countriesDB = await FileSystem.getInfoAsync(countriesPath);
    if (
      !countriesDB.exists ||
      countriesDB.size === 0 ||
      !(await isSQLiteFileValid(countriesDBname))
    ) {
      if (countriesDB.exists) {
        await FileSystem.deleteAsync(countriesPath, { idempotent: true });
      }

      await copyDatabaseFile(
        countriesDBname,
        Asset.fromModule(require('../../assets/db/' + countriesDBname))
      );
    }

    const openDatabase = (dbName: string) =>
      SQLite.openDatabaseSync(dbName, {
        useNewConnection: true
      });
    db1 = openDatabase(nmRegionsDBname);
    db2 = openDatabase(darePlacesDBname);
    db3 = openDatabase(countriesDBname);
  } catch (error) {
    console.error('openDatabases - Error:');
    console.error(JSON.stringify(error, null, 2));
  }
}

export async function refreshDatabases() {
  try {
    const fileInfo = await FileSystem.getInfoAsync(sqliteFullPath);
    if (!fileInfo.exists) {
      await FileSystem.makeDirectoryAsync(sqliteFullPath, { intermediates: true });
    }
    await refreshNmDatabase();
    await refreshDarePlacesDatabase();
    await refreshCountriesDatabase();
  } catch (error) {
    console.error('refreshDatabases - Error:');
    console.error(JSON.stringify(error, null, 2));
  }
}

export const loadDatabases = async () => {
  if (!db1 || !db2 || !db3) {
    await openDatabases();
  }

  return { db1, db2, db3 };
};

export const getFirstDatabase = async () => {
  const { db1 } = await loadDatabases();
  return db1;
};

export const getSecondDatabase = async () => {
  const { db2 } = await loadDatabases();
  return db2;
};

export const getCountriesDatabase = async () => {
  const { db3 } = await loadDatabases();
  return db3;
};

const openDatabase = (dbName: string) =>
  SQLite.openDatabaseSync(dbName, {
    useNewConnection: true
  });

export function refreshNmDatabase(): Promise<void> {
  return runRefresh('nm', async () => {
    try {
      await FileSystem.deleteAsync(sqliteFullPath + DS + nmRegionsDBname, { idempotent: true });
      const nmUrl = `${API_HOST}/static/app/${nmRegionsDBname}`;
      const nmFileUri = sqliteFullPath + DS + nmRegionsDBname;
      const nmResponse = await FileSystem.downloadAsync(nmUrl, nmFileUri);

      if (nmResponse.status !== 200) {
        console.error(`Failed to download nmDb: Status ${nmResponse.status}`);
      }

      db1 = null;
      db1 = openDatabase(nmRegionsDBname);
    } catch (error) {
      console.error('refreshNmDatabase - Error:');
      console.error(JSON.stringify(error, null, 2));
    }
  });
}

export function refreshDarePlacesDatabase(): Promise<void> {
  return runRefresh('darePlaces', async () => {
    try {
      await FileSystem.deleteAsync(sqliteFullPath + DS + darePlacesDBname, { idempotent: true });
      const dareUrl = `${API_HOST}/static/app/${darePlacesDBname}`;
      const dareFileUri = sqliteFullPath + DS + darePlacesDBname;
      const dareResponse = await FileSystem.downloadAsync(dareUrl, dareFileUri);

      if (dareResponse.status !== 200) {
        console.error(`Failed to download dareDb: Status ${dareResponse.status}`);
      }

      db2 = null;
      db2 = openDatabase(darePlacesDBname);
    } catch (error) {
      console.error('refreshDarePlacesDatabase - Error:');
      console.error(JSON.stringify(error, null, 2));
    }
  });
}

export function refreshCountriesDatabase(): Promise<void> {
  return runRefresh('countries', async () => {
    try {
      await FileSystem.deleteAsync(sqliteFullPath + DS + countriesDBname, { idempotent: true });
      const countriesUrl = `${API_HOST}/static/app/${countriesDBname}`;
      const countriesFileUri = sqliteFullPath + DS + countriesDBname;
      const countriesResponse = await FileSystem.downloadAsync(countriesUrl, countriesFileUri);

      if (countriesResponse.status !== 200) {
        console.error(`Failed to download countriesDb: Status ${countriesResponse.status}`);
      }

      db3 = null;
      db3 = openDatabase(countriesDBname);
    } catch (error) {
      console.error('refreshCountriesDatabase - Error:');
      console.error(JSON.stringify(error, null, 2));
    }
  });
}

export async function updateNmRegionsDb(localLastDate: string) {
  const lastUpdate = await fetchLastRegionsDbUpdate(localLastDate);

  if (lastUpdate && lastUpdate.date !== localLastDate) {
    const dirInfo = await FileSystem.getInfoAsync(sqliteFullPath);

    if (!dirInfo.exists) {
      await FileSystem.makeDirectoryAsync(sqliteFullPath, { intermediates: true });
    }

    await refreshNmDatabase();
    storage.set('lastUpdateNmRegions', lastUpdate.date);
  }
}

export async function updateDarePlacesDb(localLastDate: string) {
  const lastUpdate = await fetchLastDareDbUpdate(localLastDate);

  if (lastUpdate && lastUpdate.date !== localLastDate) {
    const dirInfo = await FileSystem.getInfoAsync(sqliteFullPath);

    if (!dirInfo.exists) {
      await FileSystem.makeDirectoryAsync(sqliteFullPath, { intermediates: true });
    }

    await refreshDarePlacesDatabase();
    storage.set('lastUpdateDarePlaces', lastUpdate.date);
  }
}
