import { Injectable, Logger, OnModuleDestroy, OnModuleInit } from '@nestjs/common';
import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';
import { sql } from 'drizzle-orm';
import { Pool, types } from 'pg';
import * as schema from '@/db/schema';
import { loadEnv } from '@/config/env';
import { getRequestContext } from '@/common/context/request-context';

export type Db = NodePgDatabase<typeof schema>;
/** A transaction handle. Services accept this so callers can compose work atomically. */
export type Tx = Parameters<Parameters<Db['transaction']>[0]>[0];

export interface RunOptions {
  /** Override tenant from request context (jobs iterating tenants). */
  tenantId?: string | null;
  /** Run with RLS bypass (platform admin / cross-tenant jobs). */
  bypassRls?: boolean;
  /** Reuse an existing transaction instead of opening a new one. */
  tx?: Tx;
}

// Return DATE columns as plain 'YYYY-MM-DD' strings, not JS Dates in local tz.
types.setTypeParser(types.builtins.DATE, (v: string) => v);

/**
 * Database access with tenant context.
 *
 * Every call to run() opens a transaction, applies the tenant setting that
 * drives Row Level Security, runs the callback and commits. If a transaction
 * is passed in options it is reused (the tenant setting is already applied).
 */
@Injectable()
export class DbService implements OnModuleInit, OnModuleDestroy {
  private readonly logger = new Logger(DbService.name);
  readonly pool: Pool;
  readonly db: Db;

  constructor() {
    const env = loadEnv();
    this.pool = new Pool({ connectionString: env.DATABASE_URL, max: env.DATABASE_POOL_MAX, application_name: 'wms-api' });
    this.db = drizzle(this.pool, { schema });
  }

  async onModuleInit() {
    const client = await this.pool.connect();
    try {
      await client.query('select 1');
      this.logger.log('Database connection established');
    } finally {
      client.release();
    }
  }

  async onModuleDestroy() {
    await this.pool.end();
  }

  /**
   * Run `fn` inside a tenant-scoped transaction.
   * Tenant and bypass flags are taken from the request context unless overridden.
   */
  async run<T>(fn: (tx: Tx) => Promise<T>, options: RunOptions = {}): Promise<T> {
    if (options.tx) return fn(options.tx);

    const ctx = getRequestContext();
    const tenantId = options.tenantId !== undefined ? options.tenantId : (ctx?.tenantId ?? null);
    const bypass = options.bypassRls ?? ctx?.bypassRls ?? false;

    if (!tenantId && !bypass) {
      throw new Error('DbService.run called without tenant context and without bypass. Refusing to run an unscoped query.');
    }

    return this.db.transaction(async (tx) => {
      if (bypass) {
        await tx.execute(sql`select set_config('app.bypass_rls', 'on', true)`);
      }
      if (tenantId) {
        await tx.execute(sql`select set_config('app.tenant_id', ${tenantId}, true)`);
      }
      return fn(tx);
    });
  }

  /** Convenience for platform / system code that must see across tenants. */
  runBypass<T>(fn: (tx: Tx) => Promise<T>, tx?: Tx): Promise<T> {
    return this.run(fn, { bypassRls: true, tenantId: null, tx });
  }

  /** Convenience for jobs that act on behalf of a specific tenant. */
  runAsTenant<T>(tenantId: string, fn: (tx: Tx) => Promise<T>, tx?: Tx): Promise<T> {
    return this.run(fn, { tenantId, bypassRls: false, tx });
  }
}