import type { RuntimeLogger } from "../../../core/types.ts";
import type { ISecretCodec } from "../../secrets/secret-codec-core.ts";
import type { RuntimeDatabase } from "../runtime-database.ts";
import type { RuntimeDialect } from "./dialect/dialect-adapter.ts";

import { and, eq } from "drizzle-orm";
import { PlainTextSecretCodec } from "../../secrets/secret-codec-core.ts";
import { DEFAULT_RUN_LIMIT } from "../runtime-store.ts";
import { DrizzleConnectionStore } from "./stores/drizzle-connection-store.ts";
import { DrizzleIdempotencyStore } from "./stores/drizzle-idempotency-store.ts";
import { DrizzleOAuthClientConfigStore } from "./stores/drizzle-oauth-client-config-store.ts";
import { DrizzleOAuthStateStore } from "./stores/drizzle-oauth-state-store.ts";
import { DrizzleRunLogStore } from "./stores/drizzle-run-log-store.ts";
import { DrizzleRuntimeTokenStore } from "./stores/drizzle-runtime-token-store.ts";

export interface DrizzleRuntimeDatabaseOptions {
  dialect: RuntimeDialect;
  logger?: RuntimeLogger;
  runLimit?: number;
  secretCodec?: ISecretCodec;
}

/**
 * RuntimeDatabase backed by a Drizzle dialect (SQLite / PostgreSQL / MySQL).
 * Extends the aggregate interface with close / rotateSecretCodec / resetRuntimeData
 * used by the Node process and `runtime:data` CLI.
 */
export class DrizzleRuntimeDatabase implements RuntimeDatabase {
  readonly connectionStore: DrizzleConnectionStore;
  readonly oauthClientConfigStore: DrizzleOAuthClientConfigStore;
  readonly oauthStateStore: DrizzleOAuthStateStore;
  readonly runtimeTokenStore: DrizzleRuntimeTokenStore;
  readonly runLogStore: DrizzleRunLogStore;
  readonly idempotencyStore: DrizzleIdempotencyStore;

  private readonly dialect: RuntimeDialect;
  private readonly secretCodec: ISecretCodec;

  constructor(options: DrizzleRuntimeDatabaseOptions) {
    this.dialect = options.dialect;
    this.secretCodec = options.secretCodec ?? new PlainTextSecretCodec();
    const runLimit = options.runLimit ?? DEFAULT_RUN_LIMIT;

    this.connectionStore = new DrizzleConnectionStore({ dialect: this.dialect, secretCodec: this.secretCodec });
    this.oauthClientConfigStore = new DrizzleOAuthClientConfigStore({
      dialect: this.dialect,
      secretCodec: this.secretCodec,
    });
    this.oauthStateStore = new DrizzleOAuthStateStore({ dialect: this.dialect });
    this.runtimeTokenStore = new DrizzleRuntimeTokenStore({ dialect: this.dialect });
    this.runLogStore = new DrizzleRunLogStore({ dialect: this.dialect, limit: runLimit });
    this.idempotencyStore = new DrizzleIdempotencyStore({
      dialect: this.dialect,
      secretCodec: this.secretCodec,
    });
  }

  async close(): Promise<void> {
    await this.dialect.close();
  }

  /**
   * Re-encrypt connection / OAuth / completed-idempotency secrets under `nextSecretCodec`.
   * Matches the legacy SqliteRuntimeDatabase algorithm (read-decode-encode, then single transaction write).
   * Callers typically exit after rotate so in-memory stores keep the previous codec instance.
   */
  async rotateSecretCodec(nextSecretCodec: ISecretCodec): Promise<void> {
    const { connections, oauthClientConfigs, idempotencyRecords } = this.dialect.schema as any;

    const connectionRows = (await this.dialect.db
      .select({
        service: connections.service,
        connectionName: connections.connectionName,
        value: connections.value,
      })
      .from(connections)) as Array<{ service: string; connectionName: string; value: string }>;

    const oauthRows = (await this.dialect.db
      .select({ service: oauthClientConfigs.service, value: oauthClientConfigs.value })
      .from(oauthClientConfigs)) as Array<{ service: string; value: string }>;

    const idempotencyRows = (await this.dialect.db
      .select({
        keyHash: idempotencyRecords.keyHash,
        responseValue: idempotencyRecords.responseValue,
      })
      .from(idempotencyRecords)) as Array<{ keyHash: string; responseValue: string | null }>;

    const rotatedConnections = await Promise.all(
      connectionRows.map(async (row) => ({
        service: row.service,
        connectionName: row.connectionName,
        value: await nextSecretCodec.encode(await this.secretCodec.decode(row.value)),
      })),
    );
    const rotatedOAuth = await Promise.all(
      oauthRows.map(async (row) => ({
        service: row.service,
        value: await nextSecretCodec.encode(await this.secretCodec.decode(row.value)),
      })),
    );
    const rotatedIdempotency = await Promise.all(
      idempotencyRows
        .filter((row) => row.responseValue != null)
        .map(async (row) => ({
          keyHash: row.keyHash,
          value: await nextSecretCodec.encode(await this.secretCodec.decode(row.responseValue as string)),
        })),
    );

    await this.dialect.adapter.transaction(async () => {
      for (const row of rotatedConnections) {
        await this.dialect.db
          .update(connections)
          .set({ value: row.value })
          .where(and(eq(connections.service, row.service), eq(connections.connectionName, row.connectionName)));
      }
      for (const row of rotatedOAuth) {
        await this.dialect.db
          .update(oauthClientConfigs)
          .set({ value: row.value })
          .where(eq(oauthClientConfigs.service, row.service));
      }
      for (const row of rotatedIdempotency) {
        await this.dialect.db
          .update(idempotencyRecords)
          .set({ responseValue: row.value })
          .where(eq(idempotencyRecords.keyHash, row.keyHash));
      }
    });
  }

  async resetRuntimeData(): Promise<void> {
    const { connections, oauthClientConfigs, oauthStates, runtimeTokens, runs, idempotencyRecords } = this.dialect
      .schema as any;

    await this.dialect.adapter.transaction(async () => {
      await this.dialect.db.delete(connections);
      await this.dialect.db.delete(oauthClientConfigs);
      await this.dialect.db.delete(oauthStates);
      await this.dialect.db.delete(runtimeTokens);
      await this.dialect.db.delete(runs);
      await this.dialect.db.delete(idempotencyRecords);
    });
  }
}