import { query, queryOne, getClient, PoolClient } from '../../config/database.js';
import { NotFoundError, ValidationError } from '../../shared/errors/index.js';
import { logger } from '../../shared/utils/logger.js';

// ============================================================================
// TYPES
// ============================================================================

export type ValuationMethod = 'standard' | 'fifo' | 'average';

export interface StockValuationLayer {
  id: string;
  tenant_id: string;
  product_id: string;
  company_id: string;
  quantity: number;
  unit_cost: number;
  value: number;
  remaining_qty: number;
  remaining_value: number;
  stock_move_id?: string;
  description?: string;
  account_move_id?: string;
  journal_entry_id?: string;
  created_at: Date;
}

export interface CreateValuationLayerDto {
  product_id: string;
  company_id: string;
  quantity: number;
  unit_cost: number;
  stock_move_id?: string;
  description?: string;
}

export interface ValuationSummary {
  product_id: string;
  product_name: string;
  product_code?: string;
  total_quantity: number;
  total_value: number;
  average_cost: number;
  valuation_method: ValuationMethod;
  layer_count: number;
}

export interface FifoConsumptionResult {
  layers_consumed: {
    layer_id: string;
    quantity_consumed: number;
    unit_cost: number;
    value_consumed: number;
  }[];
  total_cost: number;
  weighted_average_cost: number;
}

export interface ProductCostResult {
  product_id: string;
  valuation_method: ValuationMethod;
  standard_cost: number;
  fifo_cost?: number;
  average_cost: number;
  recommended_cost: number;
}

// ============================================================================
// SERVICE
// ============================================================================

class ValuationService {
  /**
   * Create a new valuation layer (for incoming stock)
   * Used when receiving products via purchase orders or inventory adjustments
   */
  async createLayer(
    dto: CreateValuationLayerDto,
    tenantId: string,
    userId: string,
    client?: PoolClient
  ): Promise<StockValuationLayer> {
    const executeQuery = client
      ? (sql: string, params: any[]) => client.query(sql, params).then(r => r.rows[0])
      : queryOne;

    const value = dto.quantity * dto.unit_cost;

    const layer = await executeQuery(
      `INSERT INTO inventory.stock_valuation_layers (
        tenant_id, product_id, company_id, quantity, unit_cost, value,
        remaining_qty, remaining_value, stock_move_id, description, created_by
      ) VALUES ($1, $2, $3, $4, $5, $6, $4, $6, $7, $8, $9)
      RETURNING *`,
      [
        tenantId,
        dto.product_id,
        dto.company_id,
        dto.quantity,
        dto.unit_cost,
        value,
        dto.stock_move_id,
        dto.description,
        userId,
      ]
    );

    logger.info('Valuation layer created', {
      layerId: layer?.id,
      productId: dto.product_id,
      quantity: dto.quantity,
      unitCost: dto.unit_cost,
    });

    return layer as StockValuationLayer;
  }

  /**
   * Consume stock using FIFO method
   * Returns the layers consumed and total cost
   */
  async consumeFifo(
    productId: string,
    companyId: string,
    quantity: number,
    tenantId: string,
    userId: string,
    client?: PoolClient
  ): Promise<FifoConsumptionResult> {
    const dbClient = client || await getClient();
    const shouldReleaseClient = !client;

    try {
      if (!client) {
        await dbClient.query('BEGIN');
      }

      // Get available layers ordered by creation date (FIFO)
      const layersResult = await dbClient.query(
        `SELECT * FROM inventory.stock_valuation_layers
         WHERE product_id = $1 AND company_id = $2 AND tenant_id = $3
           AND remaining_qty > 0
         ORDER BY created_at ASC
         FOR UPDATE`,
        [productId, companyId, tenantId]
      );

      const layers = layersResult.rows as StockValuationLayer[];
      let remainingToConsume = quantity;
      const consumedLayers: FifoConsumptionResult['layers_consumed'] = [];
      let totalCost = 0;

      for (const layer of layers) {
        if (remainingToConsume <= 0) break;

        const consumeFromLayer = Math.min(remainingToConsume, Number(layer.remaining_qty));
        const valueConsumed = consumeFromLayer * Number(layer.unit_cost);

        // Update layer
        await dbClient.query(
          `UPDATE inventory.stock_valuation_layers
           SET remaining_qty = remaining_qty - $1,
               remaining_value = remaining_value - $2,
               updated_at = NOW(),
               updated_by = $3
           WHERE id = $4`,
          [consumeFromLayer, valueConsumed, userId, layer.id]
        );

        consumedLayers.push({
          layer_id: layer.id,
          quantity_consumed: consumeFromLayer,
          unit_cost: Number(layer.unit_cost),
          value_consumed: valueConsumed,
        });

        totalCost += valueConsumed;
        remainingToConsume -= consumeFromLayer;
      }

      if (remainingToConsume > 0) {
        // Not enough stock in layers - this is a warning, not an error
        // The stock might exist without valuation layers (e.g., initial data)
        logger.warn('Insufficient valuation layers for FIFO consumption', {
          productId,
          requestedQty: quantity,
          availableQty: quantity - remainingToConsume,
        });
      }

      if (!client) {
        await dbClient.query('COMMIT');
      }

      const weightedAvgCost = quantity > 0 ? totalCost / (quantity - remainingToConsume) : 0;

      return {
        layers_consumed: consumedLayers,
        total_cost: totalCost,
        weighted_average_cost: weightedAvgCost,
      };
    } catch (error) {
      if (!client) {
        await dbClient.query('ROLLBACK');
      }
      throw error;
    } finally {
      if (shouldReleaseClient) {
        dbClient.release();
      }
    }
  }

  /**
   * Calculate the current cost of a product based on its valuation method
   */
  async getProductCost(
    productId: string,
    companyId: string,
    tenantId: string
  ): Promise<ProductCostResult> {
    // Get product with its valuation method and standard cost
    const product = await queryOne<{
      id: string;
      valuation_method: ValuationMethod;
      cost_price: number;
    }>(
      `SELECT id, valuation_method, cost_price
       FROM inventory.products
       WHERE id = $1 AND tenant_id = $2`,
      [productId, tenantId]
    );

    if (!product) {
      throw new NotFoundError('Producto no encontrado');
    }

    // Get FIFO cost (oldest layer's unit cost)
    const oldestLayer = await queryOne<{ unit_cost: number }>(
      `SELECT unit_cost FROM inventory.stock_valuation_layers
       WHERE product_id = $1 AND company_id = $2 AND tenant_id = $3
         AND remaining_qty > 0
       ORDER BY created_at ASC
       LIMIT 1`,
      [productId, companyId, tenantId]
    );

    // Get average cost from all layers
    const avgResult = await queryOne<{ avg_cost: number; total_qty: number }>(
      `SELECT
         CASE WHEN SUM(remaining_qty) > 0
              THEN SUM(remaining_value) / SUM(remaining_qty)
              ELSE 0
         END as avg_cost,
         SUM(remaining_qty) as total_qty
       FROM inventory.stock_valuation_layers
       WHERE product_id = $1 AND company_id = $2 AND tenant_id = $3
         AND remaining_qty > 0`,
      [productId, companyId, tenantId]
    );

    const standardCost = Number(product.cost_price) || 0;
    const fifoCost = oldestLayer ? Number(oldestLayer.unit_cost) : undefined;
    const averageCost = Number(avgResult?.avg_cost) || 0;

    // Determine recommended cost based on valuation method
    let recommendedCost: number;
    switch (product.valuation_method) {
      case 'fifo':
        recommendedCost = fifoCost ?? standardCost;
        break;
      case 'average':
        recommendedCost = averageCost > 0 ? averageCost : standardCost;
        break;
      case 'standard':
      default:
        recommendedCost = standardCost;
        break;
    }

    return {
      product_id: productId,
      valuation_method: product.valuation_method,
      standard_cost: standardCost,
      fifo_cost: fifoCost,
      average_cost: averageCost,
      recommended_cost: recommendedCost,
    };
  }

  /**
   * Get valuation summary for a product
   */
  async getProductValuationSummary(
    productId: string,
    companyId: string,
    tenantId: string
  ): Promise<ValuationSummary | null> {
    const result = await queryOne<ValuationSummary>(
      `SELECT
         p.id as product_id,
         p.name as product_name,
         p.code as product_code,
         p.valuation_method,
         COALESCE(SUM(svl.remaining_qty), 0) as total_quantity,
         COALESCE(SUM(svl.remaining_value), 0) as total_value,
         CASE WHEN COALESCE(SUM(svl.remaining_qty), 0) > 0
              THEN COALESCE(SUM(svl.remaining_value), 0) / SUM(svl.remaining_qty)
              ELSE p.cost_price
         END as average_cost,
         COUNT(CASE WHEN svl.remaining_qty > 0 THEN 1 END) as layer_count
       FROM inventory.products p
       LEFT JOIN inventory.stock_valuation_layers svl
         ON p.id = svl.product_id
         AND svl.company_id = $2
         AND svl.tenant_id = $3
       WHERE p.id = $1 AND p.tenant_id = $3
       GROUP BY p.id, p.name, p.code, p.valuation_method, p.cost_price`,
      [productId, companyId, tenantId]
    );

    return result;
  }

  /**
   * Get all valuation layers for a product
   */
  async getProductLayers(
    productId: string,
    companyId: string,
    tenantId: string,
    includeEmpty: boolean = false
  ): Promise<StockValuationLayer[]> {
    const whereClause = includeEmpty
      ? ''
      : 'AND remaining_qty > 0';

    return query<StockValuationLayer>(
      `SELECT * FROM inventory.stock_valuation_layers
       WHERE product_id = $1 AND company_id = $2 AND tenant_id = $3
       ${whereClause}
       ORDER BY created_at ASC`,
      [productId, companyId, tenantId]
    );
  }

  /**
   * Get inventory valuation report for a company
   */
  async getCompanyValuationReport(
    companyId: string,
    tenantId: string
  ): Promise<ValuationSummary[]> {
    return query<ValuationSummary>(
      `SELECT
         p.id as product_id,
         p.name as product_name,
         p.code as product_code,
         p.valuation_method,
         COALESCE(SUM(svl.remaining_qty), 0) as total_quantity,
         COALESCE(SUM(svl.remaining_value), 0) as total_value,
         CASE WHEN COALESCE(SUM(svl.remaining_qty), 0) > 0
              THEN COALESCE(SUM(svl.remaining_value), 0) / SUM(svl.remaining_qty)
              ELSE p.cost_price
         END as average_cost,
         COUNT(CASE WHEN svl.remaining_qty > 0 THEN 1 END) as layer_count
       FROM inventory.products p
       LEFT JOIN inventory.stock_valuation_layers svl
         ON p.id = svl.product_id
         AND svl.company_id = $1
         AND svl.tenant_id = $2
       WHERE p.tenant_id = $2
         AND p.product_type = 'storable'
         AND p.active = true
       GROUP BY p.id, p.name, p.code, p.valuation_method, p.cost_price
       HAVING COALESCE(SUM(svl.remaining_qty), 0) > 0
       ORDER BY p.name`,
      [companyId, tenantId]
    );
  }

  /**
   * Update average cost on product after valuation changes
   * Call this after creating layers or consuming stock
   */
  async updateProductAverageCost(
    productId: string,
    companyId: string,
    tenantId: string,
    client?: PoolClient
  ): Promise<void> {
    const executeQuery = client
      ? (sql: string, params: any[]) => client.query(sql, params)
      : query;

    // Only update products using average cost method
    await executeQuery(
      `UPDATE inventory.products p
       SET cost_price = (
         SELECT CASE WHEN SUM(svl.remaining_qty) > 0
                     THEN SUM(svl.remaining_value) / SUM(svl.remaining_qty)
                     ELSE p.cost_price
                END
         FROM inventory.stock_valuation_layers svl
         WHERE svl.product_id = p.id
           AND svl.company_id = $2
           AND svl.tenant_id = $3
           AND svl.remaining_qty > 0
       ),
       updated_at = NOW()
       WHERE p.id = $1
         AND p.tenant_id = $3
         AND p.valuation_method = 'average'`,
      [productId, companyId, tenantId]
    );
  }

  /**
   * Process stock move for valuation
   * Creates or consumes valuation layers based on move direction
   */
  async processStockMoveValuation(
    moveId: string,
    tenantId: string,
    userId: string
  ): Promise<void> {
    const move = await queryOne<{
      id: string;
      product_id: string;
      product_qty: number;
      location_id: string;
      location_dest_id: string;
      company_id: string;
    }>(
      `SELECT sm.id, sm.product_id, sm.product_qty,
              sm.location_id, sm.location_dest_id,
              p.company_id
       FROM inventory.stock_moves sm
       JOIN inventory.pickings p ON sm.picking_id = p.id
       WHERE sm.id = $1 AND sm.tenant_id = $2`,
      [moveId, tenantId]
    );

    if (!move) {
      throw new NotFoundError('Movimiento no encontrado');
    }

    // Get location types
    const [srcLoc, destLoc] = await Promise.all([
      queryOne<{ location_type: string }>(
        'SELECT location_type FROM inventory.locations WHERE id = $1',
        [move.location_id]
      ),
      queryOne<{ location_type: string }>(
        'SELECT location_type FROM inventory.locations WHERE id = $1',
        [move.location_dest_id]
      ),
    ]);

    const srcIsInternal = srcLoc?.location_type === 'internal';
    const destIsInternal = destLoc?.location_type === 'internal';

    // Get product cost for new layers
    const product = await queryOne<{ cost_price: number; valuation_method: string }>(
      'SELECT cost_price, valuation_method FROM inventory.products WHERE id = $1',
      [move.product_id]
    );

    if (!product) return;

    const client = await getClient();

    try {
      await client.query('BEGIN');

      // Incoming to internal location (create layer)
      if (!srcIsInternal && destIsInternal) {
        await this.createLayer({
          product_id: move.product_id,
          company_id: move.company_id,
          quantity: Number(move.product_qty),
          unit_cost: Number(product.cost_price),
          stock_move_id: move.id,
          description: `Recepción - Move ${move.id}`,
        }, tenantId, userId, client);
      }

      // Outgoing from internal location (consume layer with FIFO)
      if (srcIsInternal && !destIsInternal) {
        if (product.valuation_method === 'fifo' || product.valuation_method === 'average') {
          await this.consumeFifo(
            move.product_id,
            move.company_id,
            Number(move.product_qty),
            tenantId,
            userId,
            client
          );
        }
      }

      // Update average cost if using that method
      if (product.valuation_method === 'average') {
        await this.updateProductAverageCost(
          move.product_id,
          move.company_id,
          tenantId,
          client
        );
      }

      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

export const valuationService = new ValuationService();
