import { stat } from "node:fs/promises";
import {
  $inject,
  Alepha,
  AlephaError,
  type Static,
  type TObject,
} from "alepha";
import { DateTimeProvider } from "alepha/datetime";
import { $logger } from "alepha/logger";
import { type SQLWrapper, sql } from "drizzle-orm";
import {
  alias,
  type PgDatabase,
  type PgTableWithColumns,
  type PgTransaction,
} from "drizzle-orm/pg-core";
import type { PgTransactionConfig } from "drizzle-orm/pg-core/session";
import { DbError } from "../../errors/DbError.ts";
import type {
  EntityPrimitive,
  SchemaToTableConfig,
} from "../../primitives/$entity.ts";
import type { SequencePrimitive } from "../../primitives/$sequence.ts";
import { databaseEnvSchema } from "../../schemas/databaseEnvSchema.ts";
import type { ModelBuilder } from "../../services/ModelBuilder.ts";
import { DrizzleKitProvider } from "../DrizzleKitProvider.ts";

export type SQLLike = SQLWrapper | string;

export abstract class DatabaseProvider {
  protected readonly alepha = $inject(Alepha);
  protected readonly dateTime = $inject(DateTimeProvider);
  protected readonly log = $logger();
  protected abstract readonly builder: ModelBuilder;
  protected readonly kit = $inject(DrizzleKitProvider);
  public abstract readonly db: PgDatabase<any>;
  public abstract readonly dialect: "postgresql" | "sqlite";
  public abstract readonly url: string;

  public readonly enums = new Map<string, unknown>();
  public readonly tables = new Map<string, unknown>();
  public readonly sequences = new Map<string, unknown>();
  public readonly schemas = new Map<string, unknown>();

  protected readonly entityPrimitives: EntityPrimitive[] = [];
  protected readonly sequencePrimitives: SequencePrimitive[] = [];

  public get name() {
    return "default";
  }

  public get driver(): string {
    return this.dialect;
  }

  /**
   * Whether this driver supports SQL-level transactions (BEGIN/COMMIT/ROLLBACK).
   *
   * Drivers that do not (e.g. PGlite, Cloudflare D1) should override to `false`.
   */
  public get supportsTransactions(): boolean {
    return true;
  }

  /**
   * Raw database connection handle (e.g. DatabaseSync, bun:sqlite Database).
   * Override in providers that expose native connections for introspection.
   */
  public get nativeConnection(): unknown {
    return undefined;
  }

  public get schema() {
    return "public";
  }

  /**
   * Migration tracking table name, scoped by schema.
   *
   * Returns `migrations_{schema}` so that multiple schemas sharing the same
   * database each get their own migration history (e.g. `migrations_lore`,
   * `migrations_public`).
   */
  public get migrationsTable(): string {
    return `migrations_${this.schema}`;
  }

  /**
   * Log a database query with structured metadata for devtools inspection.
   */
  protected logQuery(
    sql: string,
    params: unknown[],
    duration: number,
    rowCount: number,
    error?: string,
  ): void {
    const operation = this.parseOperation(sql);
    const data = {
      type: "db:query",
      sql,
      params,
      operation,
      duration: Math.round(duration * 100) / 100,
      rowCount,
      success: !error,
      error,
    };

    if (error) {
      this.log.warn(`Query failed (${operation})`, data);
    } else {
      this.log.debug(
        `Query OK (${operation}, ${Math.round(duration)}ms)`,
        data,
      );
    }
  }

  protected parseOperation(sql: string): string {
    const trimmed = sql.trimStart().toUpperCase();
    if (trimmed.startsWith("SELECT")) return "SELECT";
    if (trimmed.startsWith("INSERT")) return "INSERT";
    if (trimmed.startsWith("UPDATE")) return "UPDATE";
    if (trimmed.startsWith("DELETE")) return "DELETE";
    if (trimmed.startsWith("CREATE")) return "CREATE";
    if (trimmed.startsWith("ALTER")) return "ALTER";
    if (trimmed.startsWith("DROP")) return "DROP";
    return "OTHER";
  }

  public table<T extends TObject>(
    entity: EntityPrimitive<T>,
  ): PgTableWithColumns<SchemaToTableConfig<T>> {
    const table = this.tables.get(entity.name);
    if (!table) {
      throw new AlephaError(`Table '${entity.name}' is not registered`);
    }

    const hasAlias = (entity as any).$alias;

    if (hasAlias) {
      return alias(
        table as PgTableWithColumns<SchemaToTableConfig<T>>,
        hasAlias,
      ) as PgTableWithColumns<SchemaToTableConfig<T>>;
    }

    return table as PgTableWithColumns<SchemaToTableConfig<T>>;
  }

  public registerEntity(entity: EntityPrimitive) {
    this.entityPrimitives.push(entity);
    this.builder.buildTable(entity, this);
  }

  public registerSequence(sequence: SequencePrimitive) {
    this.sequencePrimitives.push(sequence);
    this.builder.buildSequence(sequence, this);
  }

  /**
   * Rebuild all models into fresh maps using a different schema.
   *
   * When called with `"public"`, produces schema-free models suitable for
   * migration generation (no schema qualifiers in the SQL output).
   */
  public rebuildModels(targetSchema: string): {
    tables: Map<string, unknown>;
    enums: Map<string, unknown>;
    sequences: Map<string, unknown>;
    schemas: Map<string, unknown>;
  } {
    const result = {
      tables: new Map<string, unknown>(),
      enums: new Map<string, unknown>(),
      sequences: new Map<string, unknown>(),
      schemas: new Map<string, unknown>(),
      schema: targetSchema,
    };

    for (const entity of this.entityPrimitives) {
      this.builder.buildTable(entity, result);
    }
    for (const seq of this.sequencePrimitives) {
      this.builder.buildSequence(seq, result);
    }

    return result;
  }

  /**
   * Run a function inside a database transaction with implicit tx propagation.
   *
   * The transaction object is stored in `alepha.store` so that all Repository
   * operations within `fn` automatically participate in the transaction without
   * explicit `{ tx }` drilling.
   *
   * Nesting is safe — if already inside a `transactional()` block, the inner
   * call reuses the outer transaction (no nested PG transactions / savepoints).
   */
  public async transactional<R>(
    fn: () => Promise<R>,
    config?: PgTransactionConfig,
  ): Promise<R> {
    const existing = this.alepha.get("alepha.orm.tx");
    if (existing) {
      return fn();
    }

    if (!this.supportsTransactions) {
      return fn();
    }

    return this.db.transaction(async (tx) => {
      this.alepha.store.set("alepha.orm.tx", tx as PgTransaction<any>, {
        skipEvents: true,
      });
      try {
        return await fn();
      } finally {
        this.alepha.store.set("alepha.orm.tx", undefined, { skipEvents: true });
      }
    }, config);
  }

  public abstract execute(
    statement: SQLLike,
  ): Promise<Record<string, unknown>[]>;

  public async run<T extends TObject>(
    statement: SQLLike,
    schema: T,
  ): Promise<Array<Static<T>>> {
    const result = await this.execute(statement);

    if (result == null) {
      return [];
    }

    if (!Array.isArray(result)) {
      this.log.error("Unexpected query result format", { result });
      throw new DbError(
        "Unexpected query result format, expected array of rows",
      );
    }

    return result.map((row) => this.alepha.codec.decode(schema, row));
  }

  /**
   * Get migrations folder path - can be overridden
   */
  protected getMigrationsFolder(): string {
    return `migrations/${this.name}`;
  }

  /**
   * Migration orchestration.
   *
   * - Production: file-based migrations from the migrations folder
   * - Dev / Test: push-based sync (introspects actual DB, no snapshots)
   * - Serverless: skipped (migrations should be applied during deployment)
   */
  public async migrate(): Promise<void> {
    if (this.alepha.isServerless()) {
      return;
    }

    if (this.alepha.isProduction()) {
      const migrationsFolder = this.getMigrationsFolder();
      const exists = await stat(migrationsFolder).catch(() => false);

      if (!exists) {
        this.log.warn("Migration SKIPPED - no migrations found");
        return;
      }

      // For schema-free migrations, ensure the target schema exists
      // before running migration files. The schema is applied via
      // search_path set at the provider's connection level.
      if (this.dialect === "postgresql" && this.schema !== "public") {
        this.log.debug(`Ensuring schema '${this.schema}' exists ...`);
        await this.execute(
          sql`CREATE SCHEMA IF NOT EXISTS ${sql.raw(this.schema)}`,
        );
      }

      this.log.debug(`Migrate from '${migrationsFolder}' directory ...`);
      await this.executeMigrations(migrationsFolder);
      this.log.info("Migration OK");
    } else {
      const { DATABASE_SYNC } = this.alepha.parseEnv(databaseEnvSchema);
      if (DATABASE_SYNC === false) {
        this.log.info(
          "Database synchronization disabled (DATABASE_SYNC=false)",
        );
        return;
      }

      try {
        await this.kit.synchronize(this);
      } catch (error) {
        throw new DbError(
          `Failed to synchronize ${this.dialect} database schema`,
          error as Error,
        );
      }
    }
  }

  /**
   * Provider-specific migration execution
   * MUST be implemented by each provider
   */
  protected abstract executeMigrations(migrationsFolder: string): Promise<void>;

  // -------------------------------------------------------------------------------------------------------------------

  /**
   * For testing purposes, generate a unique schema name.
   *
   * Format: `test_alepha_{epoch_seconds}_{random8}`
   * Example: `test_alepha_1739871618_k3m9x2p1`
   */
  protected generateTestSchemaName(): string {
    const epoch = Math.floor(this.dateTime.nowMillis() / 1000);
    const random = Math.random().toString(36).slice(2, 10).padEnd(8, "0");

    return `test_alepha_${epoch}_${random}`;
  }
}