// src/services/db/address.db.ts
import type { Pool, PoolClient } from 'pg';
import { getPool } from './connection.db'; 
import type { Logger } from 'pino';
import { UniqueConstraintError, NotFoundError } from './errors.db';
import { Address } from '../../types';

export class AddressRepository {
  private db: Pool | PoolClient;

  constructor(db: Pool | PoolClient = getPool()) {
    this.db = db;
  }

  /**
   * Retrieves a single address by its ID.
   * @param addressId The ID of the address to retrieve.
   * @returns A promise that resolves to the Address object or undefined.
   */
  async getAddressById(addressId: number, logger: Logger): Promise<Address> {
    try {
      const res = await this.db.query<Address>('SELECT * FROM public.addresses WHERE address_id = $1', [addressId]);
      if (res.rowCount === 0) {
        throw new NotFoundError(`Address with ID ${addressId} not found.`);
      }
      return res.rows[0];
    } catch (error) {
      if (error instanceof NotFoundError) {
        throw error;
      }
      logger.error({ err: error, addressId }, 'Database error in getAddressById');
      throw new Error('Failed to retrieve address.');
    }
  }

  /**
   * Creates or updates an address and returns its ID.
   * This function uses an "upsert" pattern.
   * @param address The address data.
   * @returns The ID of the created or updated address.
   */
  async upsertAddress(address: Partial<Address>, logger: Logger): Promise<number> {
    try {
      const { address_id, ...addressData } = address;
      const columns = Object.keys(addressData);
      const values = Object.values(addressData);

      // If an address_id is provided, include it for the ON CONFLICT clause.
      if (address_id) {
        columns.unshift('address_id');
        values.unshift(address_id);
      }

      // Dynamically build the parameter placeholders ($1, $2, etc.)
      const valuePlaceholders = columns.map((_, i) => `$${i + 1}`).join(', ');

      // Dynamically build the SET clause for the UPDATE part.
      // EXCLUDED refers to the values from the failed INSERT attempt.
      const updateSetClauses = columns
        .filter(col => col !== 'address_id') // Don't update the primary key
        .map(col => `${col} = EXCLUDED.${col}`)
        .join(', ');

      const query = `
        INSERT INTO public.addresses (${columns.join(', ')})
        VALUES (${valuePlaceholders})
        ON CONFLICT (address_id) DO UPDATE
        SET ${updateSetClauses},
            updated_at = now()
        RETURNING address_id;
      `;

      const res = await this.db.query<{ address_id: number }>(query, values);
      return res.rows[0].address_id;

    } catch (error) {
      logger.error({ err: error, address }, 'Database error in upsertAddress');
      if (error instanceof Error && 'code' in error && error.code === '23505') throw new UniqueConstraintError('An identical address already exists.');
      throw new Error('Failed to upsert address.');
    }
  }
}