import Database from 'better-sqlite3';

////////////////////////////////////////
// 1. Types
////////////////////////////////////////
export type SchemaDefinition = Record<string, any>;

export interface CreateDBOptions {
  idColumn?: string;
  jsonColumn?: string;
  debugSql?: boolean;
}

////////////////////////////////////////
// 2. The shape of what we return
////////////////////////////////////////
export interface DBClient<TSchema extends SchemaDefinition> {
  /** Reader: returns plain JS objects, no Proxy. */
  rdr: { [TableName in keyof TSchema]: TableReader<TSchema[TableName]> };
  /** Writer: partial updates (Proxies). */
  wtr: { [TableName in keyof TSchema]: TableWriter<TSchema[TableName]> };
}

/** Reader interface: bracket-get returns plain objects from memory. */
export interface TableReader<TRow> {
  [rowId: string]: TRow | undefined;
  forEach(callback: (id: string, rowData: TRow) => void): void;
  keys(): string[];
  values(): TRow[];
  entries(): Array<[string, TRow]>;
  dict(): Record<string, TRow>;
  has(id: string): boolean;
}

/** Writer interface: bracket-get returns a nested Proxy for partial JSON updates. */
export interface TableWriter<TRow> {
  [rowId: string]: TRowProxy<TRow>;
  forEach(callback: (id: string, rowProxy: TRowProxy<TRow>) => void): void;
  keys(): string[];
  entries(): Array<[string, TRowProxy<TRow>]>;
  has(id: string): boolean;
}

/**
 * A nested Proxy that allows partial updates to single fields.
 * If you do `writer.users['bob'].nested.foo = 123`,
 * it calls `json_set(..., '$.nested.foo', 123)` in the DB.
 */
export type TRowProxy<TRow> = TRow & {
  [nestedKey: string]: any;
};

////////////////////////////////////////
// 3. Main entry point
////////////////////////////////////////
export function createDatabaseClient<TSchema extends SchemaDefinition>(
  db: Database.Database,
  schema: TSchema,
  options: CreateDBOptions = {}
): DBClient<TSchema> {
  const idColumn = options.idColumn ?? 'id';
  const jsonColumn = options.jsonColumn ?? 'data';
  const debugSql = !!options.debugSql;

  ////////////////////////////////////////
  // A) In-memory cache: Map<tableName, Map<rowId, object>>
  ////////////////////////////////////////
  const memoryCache = new Map<string, Map<string, any>>();
  for (const tableName of Object.keys(schema)) {
    memoryCache.set(tableName, new Map());
  }

  ////////////////////////////////////////
  // B) Precompiled statements for each table
  ////////////////////////////////////////
  function wrapStmt(stmt: ReturnType<Database.Database['prepare']>, label: string) {
    return {
      get(...args: any[]) {
        if (debugSql) {
          console.log(`[SQL GET] ${label}, params: ${JSON.stringify(args)}`);
        }
        return stmt.get(...args);
      },
      run(...args: any[]) {
        if (debugSql) {
          console.log(`[SQL RUN] ${label}, params: ${JSON.stringify(args)}`);
        }
        return stmt.run(...args);
      },
      all(...args: any[]) {
        if (debugSql) {
          console.log(`[SQL ALL] ${label}, params: ${JSON.stringify(args)}`);
        }
        return stmt.all(...args);
      },
    };
  }

  const stmts = new Map<
    string,
    {
      selectRow: ReturnType<typeof wrapStmt>;
      upsertWholeRow: ReturnType<typeof wrapStmt>;
      deleteRow: ReturnType<typeof wrapStmt>;
      jsonSet: ReturnType<typeof wrapStmt>;
      jsonRemove: ReturnType<typeof wrapStmt>;
      checkExistence: ReturnType<typeof wrapStmt>;
      selectAllIds: ReturnType<typeof wrapStmt>;
    }
  >();

  function getStatementsForTable(tableName: string) {
    if (stmts.has(tableName)) {
      return stmts.get(tableName)!;
    }
    const selectRowSQL = `
      SELECT ${jsonColumn} AS jsonData
        FROM ${tableName}
       WHERE ${idColumn} = ?`;
    const upsertWholeRowSQL = `
      INSERT OR REPLACE INTO ${tableName} (${idColumn}, ${jsonColumn})
      VALUES (?, json(?))`;
    const deleteRowSQL = `
      DELETE FROM ${tableName}
       WHERE ${idColumn} = ?`;
    const jsonSetSQL = `
      UPDATE ${tableName}
         SET ${jsonColumn} = json_set(${jsonColumn}, ?, json(?))
       WHERE ${idColumn} = ?`;
    const jsonRemoveSQL = `
      UPDATE ${tableName}
         SET ${jsonColumn} = json_remove(${jsonColumn}, ?)
       WHERE ${idColumn} = ?`;
    const checkExistenceSQL = `
      SELECT 1 FROM ${tableName}
       WHERE ${idColumn} = ?`;
    const selectAllIdsSQL = `
      SELECT ${idColumn} AS id
        FROM ${tableName}`;

    const prepared = {
      selectRow: wrapStmt(db.prepare(selectRowSQL), `${tableName}:selectRow`),
      upsertWholeRow: wrapStmt(db.prepare(upsertWholeRowSQL), `${tableName}:upsertWholeRow`),
      deleteRow: wrapStmt(db.prepare(deleteRowSQL), `${tableName}:deleteRow`),
      jsonSet: wrapStmt(db.prepare(jsonSetSQL), `${tableName}:jsonSet`),
      jsonRemove: wrapStmt(db.prepare(jsonRemoveSQL), `${tableName}:jsonRemove`),
      checkExistence: wrapStmt(db.prepare(checkExistenceSQL), `${tableName}:checkExistence`),
      selectAllIds: wrapStmt(db.prepare(selectAllIdsSQL), `${tableName}:selectAllIds`),
    };
    stmts.set(tableName, prepared);
    return prepared;
  }

  ////////////////////////////////////////
  // C) Helper: load a row's JSON into memory cache if not loaded
  ////////////////////////////////////////
  function loadRow(tableName: string, rowId: string) {
    const cacheForTable = memoryCache.get(tableName)!;
    if (cacheForTable.has(rowId)) {
      return; // already in memory
    }
    const { selectRow } = getStatementsForTable(tableName);
    const row = selectRow.get(rowId);
    if (!row) return; // not found in DB
    try {
      cacheForTable.set(rowId, JSON.parse(row.jsonData));
    } catch {
      cacheForTable.set(rowId, null);
    }
  }

  ////////////////////////////////////////
  // D) JSON path helpers for partial updates
  ////////////////////////////////////////
  function pathToJsonPathString(path: string[]) {
    if (!path.length) return '$';
    return '$.' + path.map(escapeJsonKey).join('.');
  }

  function escapeJsonKey(k: string): string {
    // naive
    return k.replace(/"/g, '\\"');
  }

  ////////////////////////////////////////
  // E) Row-level Proxy for partial updates
  ////////////////////////////////////////
  function createRowProxy(tableName: string, rowId: string, pathSoFar: string[] = []): any {
    return new Proxy(
      {},
      {
        get(_, propKey) {
          if (typeof propKey === 'symbol') {
            return Reflect.get(_, propKey);
          }
          loadRow(tableName, rowId);

          const cacheForTable = memoryCache.get(tableName)!;
          if (!cacheForTable.has(rowId)) {
            throw new Error(`Row '${rowId}' not found in table '${tableName}' (read).`);
          }
          const rowData = cacheForTable.get(rowId);

          const newPath = [...pathSoFar, propKey.toString()];
          let current: any = rowData;
          for (const p of newPath) {
            if (current == null || typeof current !== 'object') {
              return undefined;
            }
            current = current[p];
          }

          // If object or array, return deeper proxy so we can do partial updates
          if (current && typeof current === 'object') {
            return createRowProxy(tableName, rowId, newPath);
          }
          return current;
        },

        set(_, propKey, value) {
          loadRow(tableName, rowId);
          const cacheForTable = memoryCache.get(tableName)!;
          if (!cacheForTable.has(rowId)) {
            throw new Error(`Row '${rowId}' not found in table '${tableName}' (write).`);
          }

          const { jsonSet } = getStatementsForTable(tableName);
          const newPath = [...pathSoFar, propKey.toString()];
          const jsonPath = pathToJsonPathString(newPath);

          jsonSet.run(jsonPath, JSON.stringify(value), rowId);

          // Update local cache
          const rowData = cacheForTable.get(rowId);
          let cursor: any = rowData;
          for (let i = 0; i < newPath.length - 1; i++) {
            const seg = newPath[i];
            if (cursor[seg] == null || typeof cursor[seg] !== 'object') {
              cursor[seg] = {};
            }
            cursor = cursor[seg];
          }
          cursor[newPath[newPath.length - 1]] = value;
          return true;
        },

        deleteProperty(_, propKey) {
          loadRow(tableName, rowId);
          const cacheForTable = memoryCache.get(tableName)!;
          if (!cacheForTable.has(rowId)) {
            throw new Error(`Row '${rowId}' not found in table '${tableName}' (delete).`);
          }

          // If it looks like a numeric index => forbid
          const keyString = propKey.toString();
          if (/^\d+$/.test(keyString)) {
            throw new Error(
              `Deleting array elements by index is not allowed: .${keyString}`
            );
          }

          const { jsonRemove } = getStatementsForTable(tableName);
          const newPath = [...pathSoFar, keyString];
          const jsonPath = pathToJsonPathString(newPath);
          jsonRemove.run(jsonPath, rowId);

          // Update in-memory object
          const rowData = cacheForTable.get(rowId);
          let cursor: any = rowData;
          for (let i = 0; i < newPath.length - 1; i++) {
            const seg = newPath[i];
            if (cursor[seg] == null || typeof cursor[seg] !== 'object') {
              return true;
            }
            cursor = cursor[seg];
          }
          delete cursor[newPath[newPath.length - 1]];
          return true;
        },

        has(_, propKey) {
          if (typeof propKey === 'symbol') {
            return Reflect.has(_, propKey);
          }
          loadRow(tableName, rowId);
          const cacheForTable = memoryCache.get(tableName)!;
          if (!cacheForTable.has(rowId)) {
            return false;
          }
          const rowData = cacheForTable.get(rowId);

          let current = rowData;
          for (const p of pathSoFar) {
            if (current == null || typeof current !== 'object') {
              return false;
            }
            current = current[p];
          }

          if (current && typeof current === 'object') {
            return Object.prototype.hasOwnProperty.call(current, propKey);
          }
          return false;
        },
      }
    );
  }

  ////////////////////////////////////////
  // F) Create the "Reader" table object
  ////////////////////////////////////////
  function createTableReader(tableName: string): TableReader<any> {
    const { selectAllIds, checkExistence } = getStatementsForTable(tableName);
    const cacheForTable = memoryCache.get(tableName)!;

    const readerImplementation = {
      forEach(callback: (id: string, data: any) => void) {
        const rows = selectAllIds.all() as Array<{ id: string }>;
        for (const r of rows) {
          loadRow(tableName, r.id);
          const cached = cacheForTable.get(r.id);
          if (cached !== undefined) {
            callback(r.id, cached);
          }
        }
      },
      keys(): string[] {
        return selectAllIds.all().map((r: any) => r.id);
      },
      values(): any[] {
        return selectAllIds.all().map((r: any) => cacheForTable.get(r.id));
      },
      dict(): Record<string, any> {
        return selectAllIds.all().reduce((acc, r: any) => {
          acc[r.id] = cacheForTable.get(r.id);
          return acc;
        }, {} as Record<string, any>);
      },
      entries(): Array<[string, any]> {
        return selectAllIds.all().map((r: any) => {
          loadRow(tableName, r.id);
          return [r.id, cacheForTable.get(r.id)] as [string, any];
        });
      },
      has(id: string) {
        if (cacheForTable.has(id)) return true;
        const row = checkExistence.get(id);
        return !!row;
      },
    };

    return new Proxy(readerImplementation, {
      get(target, propKey, receiver) {
        if (typeof propKey === 'symbol') {
          return Reflect.get(target, propKey, receiver);
        }
        if (Reflect.has(target, propKey)) {
          return Reflect.get(target, propKey, receiver);
        }
        // otherwise treat propKey as rowId
        const rowId = propKey.toString();
        loadRow(tableName, rowId);
        return cacheForTable.get(rowId);
      },
      set() {
        throw new Error(`Cannot write via Reader API`);
      },
      deleteProperty() {
        throw new Error(`Cannot delete via Reader API`);
      },
      has(target, propKey) {
        if (typeof propKey === 'symbol') {
          return Reflect.has(target, propKey);
        }
        if (Reflect.has(target, propKey)) {
          return true;
        }
        const rowId = propKey.toString();
        if (cacheForTable.has(rowId)) {
          return true;
        }
        const row = checkExistence.get(rowId);
        return !!row;
      },
    }) as TableReader<any>;
  }

  ////////////////////////////////////////
  // G) Create the "Writer" table object
  ////////////////////////////////////////
  function createTableWriter(tableName: string): TableWriter<any> {
    const { checkExistence, selectAllIds, upsertWholeRow, deleteRow } =
      getStatementsForTable(tableName);
    const cacheForTable = memoryCache.get(tableName)!;

    const writerImplementation = {
      forEach(callback: (id: string, rowProxy: any) => void) {
        const rows = selectAllIds.all() as Array<{ id: string }>;
        for (const r of rows) {
          loadRow(tableName, r.id);
          callback(r.id, createRowProxy(tableName, r.id));
        }
      },
      keys(): string[] {
        return selectAllIds.all().map((r: any) => r.id);
      },
      entries(): Array<[string, any]> {
        return selectAllIds.all().map((r: any) => {
          loadRow(tableName, r.id);
          return [r.id, createRowProxy(tableName, r.id)] as [string, any];
        });
      },
      has(id: string) {
        if (cacheForTable.has(id)) return true;
        const row = checkExistence.get(id);
        return !!row;
      },
    };

    return new Proxy(writerImplementation, {
      get(target, propKey, receiver) {
        if (typeof propKey === 'symbol') {
          return Reflect.get(target, propKey, receiver);
        }
        if (Reflect.has(target, propKey)) {
          return Reflect.get(target, propKey, receiver);
        }
        const rowId = propKey.toString();
        loadRow(tableName, rowId);
        return createRowProxy(tableName, rowId);
      },
      set(_, rowId, value) {
        // upsert entire row
        const idString = rowId.toString();
        cacheForTable.set(idString, value);
        upsertWholeRow.run(idString, JSON.stringify(value));
        return true;
      },
      deleteProperty(_, rowId) {
        const idString = rowId.toString();
        cacheForTable.delete(idString);
        deleteRow.run(idString);
        return true;
      },
      has(target, propKey) {
        if (typeof propKey === 'symbol') {
          return Reflect.has(target, propKey);
        }
        if (Reflect.has(target, propKey)) {
          return true;
        }
        const rowId = propKey.toString();
        if (cacheForTable.has(rowId)) {
          return true;
        }
        const row = checkExistence.get(rowId);
        return !!row;
      },
    }) as TableWriter<any>;
  }

  ////////////////////////////////////////
  // H) Build the overall "rdr" and "wtr" objects
  ////////////////////////////////////////
  const rdrObj = {} as DBClient<TSchema>['rdr'];
  const wtrObj = {} as DBClient<TSchema>['wtr'];

  for (const tableName of Object.keys(schema)) {
    Object.defineProperty(rdrObj, tableName, {
      value: createTableReader(tableName),
      enumerable: true,
      configurable: false,
      writable: false,
    });
    Object.defineProperty(wtrObj, tableName, {
      value: createTableWriter(tableName),
      enumerable: true,
      configurable: false,
      writable: false,
    });
  }

  return {
    rdr: rdrObj,
    wtr: wtrObj,
  };
}