import { query, queryOne, getClient } from '../../config/database.js';
import { NotFoundError, ConflictError, ValidationError } from '../../shared/errors/index.js';
import { sequencesService, SEQUENCE_CODES } from '../core/sequences.service.js';
import { logger } from '../../shared/utils/logger.js';

export type OrderStatus = 'draft' | 'sent' | 'confirmed' | 'done' | 'cancelled';

export interface PurchaseOrderLine {
  id?: string;
  product_id: string;
  product_name?: string;
  product_code?: string;
  description: string;
  quantity: number;
  qty_received?: number;
  qty_invoiced?: number;
  uom_id: string;
  uom_name?: string;
  price_unit: number;
  discount?: number;
  amount_untaxed: number;
  amount_tax?: number;
  amount_total: number;
  expected_date?: string;
}

export interface PurchaseOrder {
  id: string;
  tenant_id: string;
  company_id: string;
  company_name?: string;
  name: string;
  ref?: string;
  partner_id: string;
  partner_name?: string;
  order_date: Date;
  expected_date?: Date;
  effective_date?: Date;
  currency_id: string;
  currency_code?: string;
  payment_term_id?: string;
  amount_untaxed: number;
  amount_tax: number;
  amount_total: number;
  status: OrderStatus;
  receipt_status?: string;
  invoice_status?: string;
  notes?: string;
  lines?: PurchaseOrderLine[];
  created_at: Date;
  confirmed_at?: Date;
}

export interface CreatePurchaseOrderDto {
  company_id: string;
  ref?: string;
  partner_id: string;
  order_date?: string; // Made optional, defaults to today
  expected_date?: string;
  currency_id: string;
  payment_term_id?: string;
  notes?: string;
  lines: Omit<PurchaseOrderLine, 'id' | 'product_name' | 'product_code' | 'uom_name' | 'qty_received' | 'qty_invoiced' | 'amount_tax' | 'amount_total'>[];
}

export interface UpdatePurchaseOrderDto {
  ref?: string | null;
  partner_id?: string;
  order_date?: string;
  expected_date?: string | null;
  currency_id?: string;
  payment_term_id?: string | null;
  notes?: string | null;
  lines?: Omit<PurchaseOrderLine, 'id' | 'product_name' | 'product_code' | 'uom_name' | 'qty_received' | 'qty_invoiced' | 'amount_tax' | 'amount_total'>[];
}

export interface PurchaseOrderFilters {
  company_id?: string;
  partner_id?: string;
  status?: OrderStatus;
  date_from?: string;
  date_to?: string;
  search?: string;
  page?: number;
  limit?: number;
}

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

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

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

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

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

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

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

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

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

    params.push(limit, offset);
    const data = await query<PurchaseOrder>(
      `SELECT po.*,
              c.name as company_name,
              p.name as partner_name,
              cur.code as currency_code
       FROM purchase.purchase_orders po
       LEFT JOIN auth.companies c ON po.company_id = c.id
       LEFT JOIN core.partners p ON po.partner_id = p.id
       LEFT JOIN core.currencies cur ON po.currency_id = cur.id
       ${whereClause}
       ORDER BY po.order_date DESC, po.name DESC
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      params
    );

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

  async findById(id: string, tenantId: string): Promise<PurchaseOrder> {
    const order = await queryOne<PurchaseOrder>(
      `SELECT po.*,
              c.name as company_name,
              p.name as partner_name,
              cur.code as currency_code
       FROM purchase.purchase_orders po
       LEFT JOIN auth.companies c ON po.company_id = c.id
       LEFT JOIN core.partners p ON po.partner_id = p.id
       LEFT JOIN core.currencies cur ON po.currency_id = cur.id
       WHERE po.id = $1 AND po.tenant_id = $2`,
      [id, tenantId]
    );

    if (!order) {
      throw new NotFoundError('Orden de compra no encontrada');
    }

    // Get lines
    const lines = await query<PurchaseOrderLine>(
      `SELECT pol.*,
              pr.name as product_name,
              pr.code as product_code,
              u.name as uom_name
       FROM purchase.purchase_order_lines pol
       LEFT JOIN inventory.products pr ON pol.product_id = pr.id
       LEFT JOIN core.uom u ON pol.uom_id = u.id
       WHERE pol.order_id = $1
       ORDER BY pol.created_at`,
      [id]
    );

    order.lines = lines;

    return order;
  }

  async create(dto: CreatePurchaseOrderDto, tenantId: string, userId: string): Promise<PurchaseOrder> {
    if (dto.lines.length === 0) {
      throw new ValidationError('La orden de compra debe tener al menos una línea');
    }

    // TASK-004-01: Generate PO number using sequences service
    const poNumber = await sequencesService.getNextNumber(SEQUENCE_CODES.PURCHASE_ORDER, tenantId);
    const orderDate = dto.order_date || new Date().toISOString().split('T')[0];

    const client = await getClient();

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

      // Calculate totals
      let amountUntaxed = 0;
      for (const line of dto.lines) {
        const lineTotal = line.quantity * line.price_unit * (1 - (line.discount || 0) / 100);
        amountUntaxed += lineTotal;
      }

      // Create order with auto-generated PO number
      const orderResult = await client.query(
        `INSERT INTO purchase.purchase_orders (tenant_id, company_id, name, ref, partner_id, order_date, expected_date, currency_id, payment_term_id, amount_untaxed, amount_total, notes, created_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
         RETURNING *`,
        [tenantId, dto.company_id, poNumber, dto.ref, dto.partner_id, orderDate, dto.expected_date, dto.currency_id, dto.payment_term_id, amountUntaxed, amountUntaxed, dto.notes, userId]
      );
      const order = orderResult.rows[0] as PurchaseOrder;

      // Create lines (include tenant_id for multi-tenant security)
      for (const line of dto.lines) {
        const lineUntaxed = line.quantity * line.price_unit * (1 - (line.discount || 0) / 100);
        await client.query(
          `INSERT INTO purchase.purchase_order_lines (order_id, tenant_id, product_id, description, quantity, uom_id, price_unit, discount, amount_untaxed, amount_tax, amount_total, expected_date)
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 0, $10, $11)`,
          [order.id, tenantId, line.product_id, line.description, line.quantity, line.uom_id, line.price_unit, line.discount || 0, lineUntaxed, lineUntaxed, dto.expected_date]
        );
      }

      await client.query('COMMIT');

      logger.info('Purchase order created', {
        orderId: order.id,
        orderName: poNumber,
        tenantId,
        createdBy: userId,
      });

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

  async update(id: string, dto: UpdatePurchaseOrderDto, tenantId: string, userId: string): Promise<PurchaseOrder> {
    const existing = await this.findById(id, tenantId);

    if (existing.status !== 'draft') {
      throw new ConflictError('Solo se pueden modificar órdenes en estado borrador');
    }

    const client = await getClient();

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

      // Update order header
      const updateFields: string[] = [];
      const values: any[] = [];
      let paramIndex = 1;

      if (dto.ref !== undefined) {
        updateFields.push(`ref = $${paramIndex++}`);
        values.push(dto.ref);
      }
      if (dto.partner_id !== undefined) {
        updateFields.push(`partner_id = $${paramIndex++}`);
        values.push(dto.partner_id);
      }
      if (dto.order_date !== undefined) {
        updateFields.push(`order_date = $${paramIndex++}`);
        values.push(dto.order_date);
      }
      if (dto.expected_date !== undefined) {
        updateFields.push(`expected_date = $${paramIndex++}`);
        values.push(dto.expected_date);
      }
      if (dto.currency_id !== undefined) {
        updateFields.push(`currency_id = $${paramIndex++}`);
        values.push(dto.currency_id);
      }
      if (dto.payment_term_id !== undefined) {
        updateFields.push(`payment_term_id = $${paramIndex++}`);
        values.push(dto.payment_term_id);
      }
      if (dto.notes !== undefined) {
        updateFields.push(`notes = $${paramIndex++}`);
        values.push(dto.notes);
      }

      updateFields.push(`updated_by = $${paramIndex++}`);
      values.push(userId);
      updateFields.push(`updated_at = CURRENT_TIMESTAMP`);

      values.push(id);

      if (updateFields.length > 2) {
        await client.query(
          `UPDATE purchase.purchase_orders SET ${updateFields.join(', ')} WHERE id = $${paramIndex}`,
          values
        );
      }

      // Update lines if provided
      if (dto.lines) {
        // Delete existing lines
        await client.query(`DELETE FROM purchase.purchase_order_lines WHERE order_id = $1`, [id]);

        // Calculate totals and insert new lines
        let amountUntaxed = 0;
        for (const line of dto.lines) {
          const lineUntaxed = line.quantity * line.price_unit * (1 - (line.discount || 0) / 100);
          amountUntaxed += lineUntaxed;

          await client.query(
            `INSERT INTO purchase.purchase_order_lines (order_id, tenant_id, product_id, description, quantity, uom_id, price_unit, discount, amount_untaxed, amount_tax, amount_total)
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 0, $10)`,
            [id, tenantId, line.product_id, line.description, line.quantity, line.uom_id, line.price_unit, line.discount || 0, lineUntaxed, lineUntaxed]
          );
        }

        // Update order totals
        await client.query(
          `UPDATE purchase.purchase_orders SET amount_untaxed = $1, amount_total = $2 WHERE id = $3`,
          [amountUntaxed, amountUntaxed, id]
        );
      }

      await client.query('COMMIT');

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

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

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

    if (!order.lines || order.lines.length === 0) {
      throw new ValidationError('La orden debe tener al menos una línea para confirmar');
    }

    const client = await getClient();
    try {
      await client.query('BEGIN');

      // TASK-004-03: Get supplier location and internal location
      const supplierLocResult = await client.query(
        `SELECT id FROM inventory.locations
         WHERE tenant_id = $1 AND location_type = 'supplier'
         LIMIT 1`,
        [tenantId]
      );

      let supplierLocationId = supplierLocResult.rows[0]?.id;

      if (!supplierLocationId) {
        // Create a default supplier location
        const newLocResult = await client.query(
          `INSERT INTO inventory.locations (tenant_id, name, location_type, active)
           VALUES ($1, 'Suppliers', 'supplier', true)
           RETURNING id`,
          [tenantId]
        );
        supplierLocationId = newLocResult.rows[0].id;
      }

      // Get default incoming location for the company
      const internalLocResult = await client.query(
        `SELECT l.id as location_id, w.id as warehouse_id
         FROM inventory.locations l
         INNER JOIN inventory.warehouses w ON l.warehouse_id = w.id
         WHERE w.tenant_id = $1
           AND w.company_id = $2
           AND l.location_type = 'internal'
           AND l.active = true
         ORDER BY w.is_default DESC, l.name ASC
         LIMIT 1`,
        [tenantId, order.company_id]
      );

      const destLocationId = internalLocResult.rows[0]?.location_id;

      if (!destLocationId) {
        throw new ValidationError('No hay ubicación de stock configurada para esta empresa');
      }

      // Create incoming picking
      const pickingNumber = await sequencesService.getNextNumber(SEQUENCE_CODES.PICKING_IN, tenantId);

      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, status, created_by
         )
         VALUES ($1, $2, $3, 'incoming', $4, $5, $6, $7, $8, 'confirmed', $9)
         RETURNING id`,
        [
          tenantId,
          order.company_id,
          pickingNumber,
          supplierLocationId,
          destLocationId,
          order.partner_id,
          order.expected_date || new Date().toISOString().split('T')[0],
          order.name, // origin = purchase order reference
          userId,
        ]
      );
      const pickingId = pickingResult.rows[0].id;

      // Create stock moves for each order line
      for (const line of order.lines) {
        await client.query(
          `INSERT INTO inventory.stock_moves (
             tenant_id, picking_id, product_id, product_uom_id, location_id,
             location_dest_id, product_qty, status, created_by
           )
           VALUES ($1, $2, $3, $4, $5, $6, $7, 'confirmed', $8)`,
          [
            tenantId,
            pickingId,
            line.product_id,
            line.uom_id,
            supplierLocationId,
            destLocationId,
            line.quantity,
            userId,
          ]
        );
      }

      // Update order status and link picking
      await client.query(
        `UPDATE purchase.purchase_orders SET
           status = 'confirmed',
           picking_id = $1,
           confirmed_at = CURRENT_TIMESTAMP,
           confirmed_by = $2,
           updated_by = $2,
           updated_at = CURRENT_TIMESTAMP
         WHERE id = $3`,
        [pickingId, userId, id]
      );

      await client.query('COMMIT');

      logger.info('Purchase order confirmed with receipt picking', {
        orderId: id,
        orderName: order.name,
        pickingId,
        pickingName: pickingNumber,
        linesCount: order.lines.length,
        tenantId,
      });

      return this.findById(id, tenantId);
    } catch (error) {
      await client.query('ROLLBACK');
      logger.error('Error confirming purchase order', {
        error: (error as Error).message,
        orderId: id,
        tenantId,
      });
      throw error;
    } finally {
      client.release();
    }
  }

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

    if (order.status === 'cancelled') {
      throw new ConflictError('La orden ya está cancelada');
    }

    if (order.status === 'done') {
      throw new ConflictError('No se puede cancelar una orden completada');
    }

    await query(
      `UPDATE purchase.purchase_orders
       SET status = 'cancelled', cancelled_at = CURRENT_TIMESTAMP, cancelled_by = $1, updated_at = CURRENT_TIMESTAMP, updated_by = $1
       WHERE id = $2 AND tenant_id = $3`,
      [userId, id, tenantId]
    );

    return this.findById(id, tenantId);
  }

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

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

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

  /**
   * TASK-004-05: Create supplier invoice (BILL) from confirmed purchase order
   */
  async createSupplierInvoice(id: string, tenantId: string, userId: string): Promise<{ orderId: string; invoiceId: string }> {
    const order = await this.findById(id, tenantId);

    if (order.status !== 'confirmed' && order.status !== 'done') {
      throw new ValidationError('Solo se pueden facturar órdenes confirmadas');
    }

    if (order.invoice_status === 'invoiced') {
      throw new ValidationError('La orden ya está completamente facturada');
    }

    // Check if there are quantities to invoice
    const linesToInvoice = order.lines?.filter(l => {
      const qtyReceived = l.qty_received || 0;
      const qtyInvoiced = l.qty_invoiced || 0;
      return qtyReceived > qtyInvoiced;
    });

    if (!linesToInvoice || linesToInvoice.length === 0) {
      throw new ValidationError('No hay líneas recibidas para facturar');
    }

    const client = await getClient();
    try {
      await client.query('BEGIN');

      // Generate invoice number using sequences
      const invoiceNumber = await sequencesService.getNextNumber(SEQUENCE_CODES.INVOICE_SUPPLIER, tenantId);

      // Calculate due date from payment terms
      let dueDate = new Date();
      if (order.payment_term_id) {
        const paymentTermResult = await client.query(
          `SELECT due_days FROM core.payment_terms WHERE id = $1`,
          [order.payment_term_id]
        );
        const dueDays = paymentTermResult.rows[0]?.due_days || 30;
        dueDate.setDate(dueDate.getDate() + dueDays);
      } else {
        dueDate.setDate(dueDate.getDate() + 30); // Default 30 days
      }

      // Create invoice (supplier invoice = 'supplier' type)
      const invoiceResult = await client.query(
        `INSERT INTO financial.invoices (
          tenant_id, company_id, name, partner_id, invoice_date, due_date,
          currency_id, invoice_type, amount_untaxed, amount_tax, amount_total,
          source_document, created_by
        )
         VALUES ($1, $2, $3, $4, CURRENT_DATE, $5, $6, 'supplier', 0, 0, 0, $7, $8)
         RETURNING id`,
        [tenantId, order.company_id, invoiceNumber, order.partner_id, dueDate.toISOString().split('T')[0], order.currency_id, order.name, userId]
      );
      const invoiceId = invoiceResult.rows[0].id;

      // Create invoice lines and update qty_invoiced
      for (const line of linesToInvoice) {
        const qtyToInvoice = (line.qty_received || 0) - (line.qty_invoiced || 0);
        const lineAmount = qtyToInvoice * line.price_unit * (1 - (line.discount || 0) / 100);

        await client.query(
          `INSERT INTO financial.invoice_lines (
            invoice_id, tenant_id, product_id, description, quantity, uom_id,
            price_unit, discount, amount_untaxed, amount_tax, amount_total
          )
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 0, $9)`,
          [invoiceId, tenantId, line.product_id, line.description, qtyToInvoice, line.uom_id, line.price_unit, line.discount || 0, lineAmount]
        );

        await client.query(
          `UPDATE purchase.purchase_order_lines SET qty_invoiced = COALESCE(qty_invoiced, 0) + $1 WHERE id = $2`,
          [qtyToInvoice, line.id]
        );
      }

      // Update invoice totals
      await client.query(
        `UPDATE financial.invoices SET
           amount_untaxed = (SELECT COALESCE(SUM(amount_untaxed), 0) FROM financial.invoice_lines WHERE invoice_id = $1),
           amount_total = (SELECT COALESCE(SUM(amount_total), 0) FROM financial.invoice_lines WHERE invoice_id = $1)
         WHERE id = $1`,
        [invoiceId]
      );

      // TASK-004-06: Update order invoice_status
      await client.query(
        `UPDATE purchase.purchase_orders SET
           invoice_status = CASE
             WHEN (SELECT COALESCE(SUM(qty_invoiced), 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 'invoiced'
             WHEN (SELECT COALESCE(SUM(qty_invoiced), 0) FROM purchase.purchase_order_lines WHERE order_id = $1) > 0
             THEN 'partial'
             ELSE 'pending'
           END,
           updated_by = $2,
           updated_at = CURRENT_TIMESTAMP
         WHERE id = $1`,
        [id, userId]
      );

      await client.query('COMMIT');

      logger.info('Supplier invoice created from purchase order', {
        orderId: id,
        orderName: order.name,
        invoiceId,
        invoiceName: invoiceNumber,
        tenantId,
      });

      return { orderId: id, invoiceId };
    } catch (error) {
      await client.query('ROLLBACK');
      logger.error('Error creating supplier invoice', {
        error: (error as Error).message,
        orderId: id,
        tenantId,
      });
      throw error;
    } finally {
      client.release();
    }
  }
}

export const purchasesService = new PurchasesService();
