import { query, queryOne, getClient } from '../../config/database.js';
import { NotFoundError, ConflictError, ValidationError } from '../../shared/errors/index.js';
import { stockReservationService, ReservationLine } from './stock-reservation.service.js';
import { valuationService } from './valuation.service.js';
import { logger } from '../../shared/utils/logger.js';

export type PickingType = 'incoming' | 'outgoing' | 'internal';
export type MoveStatus = 'draft' | 'waiting' | 'confirmed' | 'assigned' | 'done' | 'cancelled';

export interface StockMoveLine {
  id?: string;
  product_id: string;
  product_name?: string;
  product_code?: string;
  product_uom_id: string;
  uom_name?: string;
  product_qty: number;
  quantity_done?: number;
  lot_id?: string;
  location_id: string;
  location_name?: string;
  location_dest_id: string;
  location_dest_name?: string;
  status?: MoveStatus;
}

export interface Picking {
  id: string;
  tenant_id: string;
  company_id: string;
  company_name?: string;
  name: string;
  picking_type: PickingType;
  location_id: string;
  location_name?: string;
  location_dest_id: string;
  location_dest_name?: string;
  partner_id?: string;
  partner_name?: string;
  scheduled_date?: Date;
  date_done?: Date;
  origin?: string;
  status: MoveStatus;
  notes?: string;
  moves?: StockMoveLine[];
  created_at: Date;
  validated_at?: Date;
}

export interface CreatePickingDto {
  company_id: string;
  name: string;
  picking_type: PickingType;
  location_id: string;
  location_dest_id: string;
  partner_id?: string;
  scheduled_date?: string;
  origin?: string;
  notes?: string;
  moves: Omit<StockMoveLine, 'id' | 'product_name' | 'product_code' | 'uom_name' | 'location_name' | 'location_dest_name' | 'quantity_done' | 'status'>[];
}

export interface UpdatePickingDto {
  partner_id?: string | null;
  scheduled_date?: string | null;
  origin?: string | null;
  notes?: string | null;
  moves?: Omit<StockMoveLine, 'id' | 'product_name' | 'product_code' | 'uom_name' | 'location_name' | 'location_dest_name' | 'quantity_done' | 'status'>[];
}

export interface PickingFilters {
  company_id?: string;
  picking_type?: PickingType;
  status?: MoveStatus;
  partner_id?: string;
  date_from?: string;
  date_to?: string;
  search?: string;
  page?: number;
  limit?: number;
}

class PickingsService {
  async findAll(tenantId: string, filters: PickingFilters = {}): Promise<{ data: Picking[]; total: number }> {
    const { company_id, picking_type, status, partner_id, date_from, date_to, search, page = 1, limit = 20 } = filters;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE p.tenant_id = $1';
    const params: any[] = [tenantId];
    let paramIndex = 2;

    if (company_id) {
      whereClause += ` AND p.company_id = $${paramIndex++}`;
      params.push(company_id);
    }

    if (picking_type) {
      whereClause += ` AND p.picking_type = $${paramIndex++}`;
      params.push(picking_type);
    }

    if (status) {
      whereClause += ` AND p.status = $${paramIndex++}`;
      params.push(status);
    }

    if (partner_id) {
      whereClause += ` AND p.partner_id = $${paramIndex++}`;
      params.push(partner_id);
    }

    if (date_from) {
      whereClause += ` AND p.scheduled_date >= $${paramIndex++}`;
      params.push(date_from);
    }

    if (date_to) {
      whereClause += ` AND p.scheduled_date <= $${paramIndex++}`;
      params.push(date_to);
    }

    if (search) {
      whereClause += ` AND (p.name ILIKE $${paramIndex} OR p.origin ILIKE $${paramIndex})`;
      params.push(`%${search}%`);
      paramIndex++;
    }

    const countResult = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM inventory.pickings p ${whereClause}`,
      params
    );

    params.push(limit, offset);
    const data = await query<Picking>(
      `SELECT p.*,
              c.name as company_name,
              l.name as location_name,
              ld.name as location_dest_name,
              pa.name as partner_name
       FROM inventory.pickings p
       LEFT JOIN auth.companies c ON p.company_id = c.id
       LEFT JOIN inventory.locations l ON p.location_id = l.id
       LEFT JOIN inventory.locations ld ON p.location_dest_id = ld.id
       LEFT JOIN core.partners pa ON p.partner_id = pa.id
       ${whereClause}
       ORDER BY p.scheduled_date DESC NULLS LAST, p.name DESC
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      params
    );

    return {
      data,
      total: parseInt(countResult?.count || '0', 10),
    };
  }

  async findById(id: string, tenantId: string): Promise<Picking> {
    const picking = await queryOne<Picking>(
      `SELECT p.*,
              c.name as company_name,
              l.name as location_name,
              ld.name as location_dest_name,
              pa.name as partner_name
       FROM inventory.pickings p
       LEFT JOIN auth.companies c ON p.company_id = c.id
       LEFT JOIN inventory.locations l ON p.location_id = l.id
       LEFT JOIN inventory.locations ld ON p.location_dest_id = ld.id
       LEFT JOIN core.partners pa ON p.partner_id = pa.id
       WHERE p.id = $1 AND p.tenant_id = $2`,
      [id, tenantId]
    );

    if (!picking) {
      throw new NotFoundError('Picking no encontrado');
    }

    // Get moves
    const moves = await query<StockMoveLine>(
      `SELECT sm.*,
              pr.name as product_name,
              pr.code as product_code,
              u.name as uom_name,
              l.name as location_name,
              ld.name as location_dest_name
       FROM inventory.stock_moves sm
       LEFT JOIN inventory.products pr ON sm.product_id = pr.id
       LEFT JOIN core.uom u ON sm.product_uom_id = u.id
       LEFT JOIN inventory.locations l ON sm.location_id = l.id
       LEFT JOIN inventory.locations ld ON sm.location_dest_id = ld.id
       WHERE sm.picking_id = $1
       ORDER BY sm.created_at`,
      [id]
    );

    picking.moves = moves;

    return picking;
  }

  async create(dto: CreatePickingDto, tenantId: string, userId: string): Promise<Picking> {
    if (dto.moves.length === 0) {
      throw new ValidationError('El picking debe tener al menos un movimiento');
    }

    const client = await getClient();

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

      // Create picking
      const pickingResult = await client.query(
        `INSERT INTO inventory.pickings (tenant_id, company_id, name, picking_type, location_id, location_dest_id, partner_id, scheduled_date, origin, notes, created_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
         RETURNING *`,
        [tenantId, dto.company_id, dto.name, dto.picking_type, dto.location_id, dto.location_dest_id, dto.partner_id, dto.scheduled_date, dto.origin, dto.notes, userId]
      );
      const picking = pickingResult.rows[0] as Picking;

      // Create moves
      for (const move of dto.moves) {
        await client.query(
          `INSERT INTO inventory.stock_moves (tenant_id, picking_id, product_id, product_uom_id, location_id, location_dest_id, product_qty, lot_id, created_by)
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
          [tenantId, picking.id, move.product_id, move.product_uom_id, move.location_id, move.location_dest_id, move.product_qty, move.lot_id, userId]
        );
      }

      await client.query('COMMIT');

      return this.findById(picking.id, tenantId);
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async confirm(id: string, tenantId: string, userId: string): Promise<Picking> {
    const picking = await this.findById(id, tenantId);

    if (picking.status !== 'draft') {
      throw new ConflictError('Solo se pueden confirmar pickings en estado borrador');
    }

    await query(
      `UPDATE inventory.pickings SET status = 'confirmed', updated_at = CURRENT_TIMESTAMP, updated_by = $1 WHERE id = $2`,
      [userId, id]
    );

    await query(
      `UPDATE inventory.stock_moves SET status = 'confirmed', updated_at = CURRENT_TIMESTAMP, updated_by = $1 WHERE picking_id = $2`,
      [userId, id]
    );

    return this.findById(id, tenantId);
  }

  async validate(id: string, tenantId: string, userId: string): Promise<Picking> {
    const picking = await this.findById(id, tenantId);

    if (picking.status === 'done') {
      throw new ConflictError('El picking ya está validado');
    }

    if (picking.status === 'cancelled') {
      throw new ConflictError('No se puede validar un picking cancelado');
    }

    // TASK-006-05: Validate lots for tracked products
    if (picking.moves && picking.moves.length > 0) {
      for (const move of picking.moves) {
        // Check if product requires lot tracking
        const productResult = await queryOne<{ tracking: string; name: string }>(
          `SELECT tracking, name FROM inventory.products WHERE id = $1`,
          [move.product_id]
        );

        if (productResult && productResult.tracking !== 'none' && !move.lot_id) {
          throw new ValidationError(
            `El producto "${productResult.name || move.product_name}" requiere número de lote/serie para ser movido`
          );
        }
      }
    }

    const client = await getClient();

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

      // Release reserved stock before moving (for outgoing pickings)
      if (picking.picking_type === 'outgoing' && picking.moves) {
        const releaseLines: ReservationLine[] = picking.moves.map(move => ({
          productId: move.product_id,
          locationId: move.location_id,
          quantity: move.product_qty,
          lotId: move.lot_id,
        }));

        await stockReservationService.releaseWithClient(
          client,
          releaseLines,
          tenantId
        );
      }

      // Update stock quants for each move
      for (const move of picking.moves || []) {
        const qty = move.product_qty;

        // Decrease from source location
        await client.query(
          `INSERT INTO inventory.stock_quants (product_id, location_id, quantity, tenant_id)
           VALUES ($1, $2, -$3, $4)
           ON CONFLICT (product_id, location_id, COALESCE(lot_id, '00000000-0000-0000-0000-000000000000'))
           DO UPDATE SET quantity = stock_quants.quantity - $3, updated_at = CURRENT_TIMESTAMP`,
          [move.product_id, move.location_id, qty, tenantId]
        );

        // Increase in destination location
        await client.query(
          `INSERT INTO inventory.stock_quants (product_id, location_id, quantity, tenant_id)
           VALUES ($1, $2, $3, $4)
           ON CONFLICT (product_id, location_id, COALESCE(lot_id, '00000000-0000-0000-0000-000000000000'))
           DO UPDATE SET quantity = stock_quants.quantity + $3, updated_at = CURRENT_TIMESTAMP`,
          [move.product_id, move.location_dest_id, qty, tenantId]
        );

        // Update move
        await client.query(
          `UPDATE inventory.stock_moves
           SET quantity_done = $1, status = 'done', date = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP, updated_by = $2
           WHERE id = $3`,
          [qty, userId, move.id]
        );

        // TASK-006-01/02: Process stock valuation for the move
        // Get location types to determine if it's incoming or outgoing
        const [srcLoc, destLoc] = await Promise.all([
          client.query('SELECT location_type FROM inventory.locations WHERE id = $1', [move.location_id]),
          client.query('SELECT location_type FROM inventory.locations WHERE id = $1', [move.location_dest_id]),
        ]);

        const srcIsInternal = srcLoc.rows[0]?.location_type === 'internal';
        const destIsInternal = destLoc.rows[0]?.location_type === 'internal';

        // Get product cost info for valuation
        const productInfo = await client.query(
          `SELECT cost_price, valuation_method FROM inventory.products WHERE id = $1`,
          [move.product_id]
        );
        const product = productInfo.rows[0];

        if (product && product.valuation_method !== 'standard') {
          // Incoming to internal location (create valuation layer)
          if (!srcIsInternal && destIsInternal) {
            try {
              await valuationService.createLayer(
                {
                  product_id: move.product_id,
                  company_id: picking.company_id,
                  quantity: qty,
                  unit_cost: Number(product.cost_price) || 0,
                  stock_move_id: move.id,
                  description: `Recepción - ${picking.name}`,
                },
                tenantId,
                userId,
                client
              );
              logger.debug('Valuation layer created for incoming move', {
                pickingId: id,
                moveId: move.id,
                productId: move.product_id,
                quantity: qty,
              });
            } catch (valErr) {
              logger.warn('Failed to create valuation layer', {
                moveId: move.id,
                error: (valErr as Error).message,
              });
            }
          }

          // Outgoing from internal location (consume valuation layers with FIFO)
          if (srcIsInternal && !destIsInternal) {
            try {
              const consumeResult = await valuationService.consumeFifo(
                move.product_id,
                picking.company_id,
                qty,
                tenantId,
                userId,
                client
              );
              logger.debug('Valuation layers consumed for outgoing move', {
                pickingId: id,
                moveId: move.id,
                productId: move.product_id,
                quantity: qty,
                totalCost: consumeResult.total_cost,
                layersConsumed: consumeResult.layers_consumed.length,
              });
            } catch (valErr) {
              logger.warn('Failed to consume valuation layers', {
                moveId: move.id,
                error: (valErr as Error).message,
              });
            }
          }

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

      // Update picking
      await client.query(
        `UPDATE inventory.pickings
         SET status = 'done', date_done = CURRENT_TIMESTAMP, validated_at = CURRENT_TIMESTAMP, validated_by = $1, updated_at = CURRENT_TIMESTAMP, updated_by = $1
         WHERE id = $2`,
        [userId, id]
      );

      // TASK-003-07: Update sales order delivery_status if this is a sales order picking
      if (picking.origin && picking.picking_type === 'outgoing') {
        // Check if this picking is from a sales order (origin starts with 'SO-')
        const orderResult = await client.query(
          `SELECT so.id, so.name
           FROM sales.sales_orders so
           WHERE so.picking_id = $1 AND so.tenant_id = $2`,
          [id, tenantId]
        );

        if (orderResult.rows.length > 0) {
          const orderId = orderResult.rows[0].id;
          const orderName = orderResult.rows[0].name;

          // Update qty_delivered on order lines based on moves
          for (const move of picking.moves || []) {
            await client.query(
              `UPDATE sales.sales_order_lines
               SET qty_delivered = qty_delivered + $1
               WHERE order_id = $2 AND product_id = $3`,
              [move.product_qty, orderId, move.product_id]
            );
          }

          // Calculate new delivery_status based on delivered quantities
          await client.query(
            `UPDATE sales.sales_orders SET
               delivery_status = CASE
                 WHEN (SELECT SUM(qty_delivered) FROM sales.sales_order_lines WHERE order_id = $1) >=
                      (SELECT SUM(quantity) FROM sales.sales_order_lines WHERE order_id = $1)
                 THEN 'delivered'::varchar
                 WHEN (SELECT SUM(qty_delivered) FROM sales.sales_order_lines WHERE order_id = $1) > 0
                 THEN 'partial'::varchar
                 ELSE 'pending'::varchar
               END,
               status = CASE
                 WHEN (SELECT SUM(qty_delivered) FROM sales.sales_order_lines WHERE order_id = $1) >=
                      (SELECT SUM(quantity) FROM sales.sales_order_lines WHERE order_id = $1)
                 THEN 'sale'::varchar
                 ELSE status
               END,
               updated_by = $2,
               updated_at = CURRENT_TIMESTAMP
             WHERE id = $1`,
            [orderId, userId]
          );

          logger.info('Sales order delivery status updated', {
            pickingId: id,
            orderId,
            orderName,
          });
        }
      }

      // TASK-004-04: Update purchase order receipt_status if this is a purchase order picking
      if (picking.origin && picking.picking_type === 'incoming') {
        // Check if this picking is from a purchase order
        const poResult = await client.query(
          `SELECT po.id, po.name
           FROM purchase.purchase_orders po
           WHERE po.picking_id = $1 AND po.tenant_id = $2`,
          [id, tenantId]
        );

        if (poResult.rows.length > 0) {
          const poId = poResult.rows[0].id;
          const poName = poResult.rows[0].name;

          // Update qty_received on order lines based on moves
          for (const move of picking.moves || []) {
            await client.query(
              `UPDATE purchase.purchase_order_lines
               SET qty_received = COALESCE(qty_received, 0) + $1
               WHERE order_id = $2 AND product_id = $3`,
              [move.product_qty, poId, move.product_id]
            );
          }

          // Calculate new receipt_status based on received quantities
          await client.query(
            `UPDATE purchase.purchase_orders SET
               receipt_status = CASE
                 WHEN (SELECT COALESCE(SUM(qty_received), 0) FROM purchase.purchase_order_lines WHERE order_id = $1) >=
                      (SELECT COALESCE(SUM(quantity), 0) FROM purchase.purchase_order_lines WHERE order_id = $1)
                 THEN 'received'
                 WHEN (SELECT COALESCE(SUM(qty_received), 0) FROM purchase.purchase_order_lines WHERE order_id = $1) > 0
                 THEN 'partial'
                 ELSE 'pending'
               END,
               status = CASE
                 WHEN (SELECT COALESCE(SUM(qty_received), 0) FROM purchase.purchase_order_lines WHERE order_id = $1) >=
                      (SELECT COALESCE(SUM(quantity), 0) FROM purchase.purchase_order_lines WHERE order_id = $1)
                 THEN 'done'
                 ELSE status
               END,
               effective_date = CASE
                 WHEN (SELECT COALESCE(SUM(qty_received), 0) FROM purchase.purchase_order_lines WHERE order_id = $1) >=
                      (SELECT COALESCE(SUM(quantity), 0) FROM purchase.purchase_order_lines WHERE order_id = $1)
                 THEN CURRENT_DATE
                 ELSE effective_date
               END,
               updated_by = $2,
               updated_at = CURRENT_TIMESTAMP
             WHERE id = $1`,
            [poId, userId]
          );

          logger.info('Purchase order receipt status updated', {
            pickingId: id,
            purchaseOrderId: poId,
            purchaseOrderName: poName,
          });
        }
      }

      await client.query('COMMIT');

      logger.info('Picking validated', {
        pickingId: id,
        pickingName: picking.name,
        movesCount: picking.moves?.length || 0,
        tenantId,
      });

      return this.findById(id, tenantId);
    } catch (error) {
      await client.query('ROLLBACK');
      logger.error('Error validating picking', {
        error: (error as Error).message,
        pickingId: id,
        tenantId,
      });
      throw error;
    } finally {
      client.release();
    }
  }

  async cancel(id: string, tenantId: string, userId: string): Promise<Picking> {
    const picking = await this.findById(id, tenantId);

    if (picking.status === 'done') {
      throw new ConflictError('No se puede cancelar un picking ya validado');
    }

    if (picking.status === 'cancelled') {
      throw new ConflictError('El picking ya está cancelado');
    }

    await query(
      `UPDATE inventory.pickings SET status = 'cancelled', updated_at = CURRENT_TIMESTAMP, updated_by = $1 WHERE id = $2`,
      [userId, id]
    );

    await query(
      `UPDATE inventory.stock_moves SET status = 'cancelled', updated_at = CURRENT_TIMESTAMP, updated_by = $1 WHERE picking_id = $2`,
      [userId, id]
    );

    return this.findById(id, tenantId);
  }

  async delete(id: string, tenantId: string): Promise<void> {
    const picking = await this.findById(id, tenantId);

    if (picking.status !== 'draft') {
      throw new ConflictError('Solo se pueden eliminar pickings en estado borrador');
    }

    await query(`DELETE FROM inventory.pickings WHERE id = $1 AND tenant_id = $2`, [id, tenantId]);
  }
}

export const pickingsService = new PickingsService();
