import {
  MOBILE_SQLITE_DATABASE_NAME,
  MOBILE_SQLITE_SCHEMA_VERSION,
  resolveMobileSqliteMigrationStatements
} from '../../logic/mobile-sqlite-schema.rules';
import { executeMobileSqliteStatements } from '../../logic/mobile-sqlite-execute.rules';

const META_SCHEMA_VERSION_KEY = 'mobile_sqlite_schema_version';

export interface MobileSqliteStore {
  readonly isAvailable: boolean;
  initialize(): Promise<void>;
  run(statement: string, values?: unknown[]): Promise<void>;
  query<T>(statement: string, values?: unknown[]): Promise<T[]>;
}

const schemaInitializationFailures = new Set<string>();

/** Lazy-loaded @capacitor-community/sqlite connection for native mobile shells. */
export async function createCapacitorSqliteStore(
  databaseName: string = MOBILE_SQLITE_DATABASE_NAME
): Promise<MobileSqliteStore | null> {
  if (typeof window === 'undefined') {
    return null;
  }

  try {
    const sqliteModule = await import('@capacitor-community/sqlite');

    type SqliteDbConnection = import('@capacitor-community/sqlite').SQLiteDBConnection;

    const sqliteConnection = new sqliteModule.SQLiteConnection(sqliteModule.CapacitorSQLite);

    let database: SqliteDbConnection | null = null;

    return {
      get isAvailable() {
        return database !== null && !schemaInitializationFailures.has(databaseName);
      },

      async initialize(): Promise<void> {
        if (schemaInitializationFailures.has(databaseName)) {
          throw new Error(`Mobile SQLite schema initialization failed for "${databaseName}".`);
        }

        await sqliteConnection.checkConnectionsConsistency();
        const connectionState = await sqliteConnection.isConnection(databaseName, false);

        database = connectionState.result
          ? await sqliteConnection.retrieveConnection(databaseName, false)
          : await sqliteConnection.createConnection(
            databaseName,
            false,
            'no-encryption',
            MOBILE_SQLITE_SCHEMA_VERSION,
            false
          );

        await database.open();

        let storedVersion = 0;

        try {
          const versionRows = await database.query(`SELECT value FROM meta WHERE key = '${META_SCHEMA_VERSION_KEY}' LIMIT 1`);

          storedVersion = Number(versionRows.values?.[0]?.value ?? 0);
        } catch {
          storedVersion = 0;
        }

        const statements = resolveMobileSqliteMigrationStatements(storedVersion);

        if (statements.length === 0) {
          return;
        }

        try {
          const activeDatabase = database;

          if (!activeDatabase) {
            throw new Error('Mobile SQLite store is not initialized.');
          }

          await executeMobileSqliteStatements(
            (statement) => activeDatabase.execute(statement),
            statements
          );
        } catch (error) {
          schemaInitializationFailures.add(databaseName);
          throw error;
        }
      },

      async run(statement: string, values: unknown[] = []): Promise<void> {
        if (!database) {
          throw new Error('Mobile SQLite store is not initialized.');
        }

        await database.run(statement, values);
      },

      async query<T>(statement: string, values: unknown[] = []): Promise<T[]> {
        if (!database) {
          throw new Error('Mobile SQLite store is not initialized.');
        }

        const result = await database.query(statement, values);

        return (result.values ?? []) as T[];
      }
    };
  } catch {
    return null;
  }
}
