import { DataSource } from 'typeorm';

export interface DashboardKPIs {
  sales: {
    todayRevenue: number;
    monthRevenue: number;
    todayOrders: number;
    monthOrders: number;
    pendingOrders: number;
  };
  inventory: {
    totalProducts: number;
    lowStockItems: number;
    outOfStockItems: number;
    pendingMovements: number;
  };
  invoices: {
    pendingInvoices: number;
    overdueInvoices: number;
    totalReceivable: number;
    totalPayable: number;
  };
  partners: {
    totalCustomers: number;
    totalSuppliers: number;
    newCustomersMonth: number;
  };
}

export interface DashboardActivity {
  recentOrders: any[];
  recentInvoices: any[];
  recentMovements: any[];
  alerts: any[];
}

export class DashboardService {
  constructor(private readonly dataSource: DataSource) {}

  async getKPIs(tenantId: string): Promise<DashboardKPIs> {
    const [sales, inventory, invoices, partners] = await Promise.all([
      this.getSalesKPIs(tenantId),
      this.getInventoryKPIs(tenantId),
      this.getInvoiceKPIs(tenantId),
      this.getPartnerKPIs(tenantId),
    ]);

    return { sales, inventory, invoices, partners };
  }

  private async getSalesKPIs(tenantId: string): Promise<DashboardKPIs['sales']> {
    const today = new Date();
    const startOfDay = new Date(today.setHours(0, 0, 0, 0));
    const startOfMonth = new Date(today.getFullYear(), today.getMonth(), 1);

    try {
      // Today's sales
      const todayQuery = `
        SELECT
          COALESCE(SUM(total), 0) as revenue,
          COUNT(*) as orders
        FROM sales.sales_orders
        WHERE tenant_id = $1
          AND status NOT IN ('cancelled', 'draft')
          AND order_date >= $2
      `;
      const todayResult = await this.dataSource.query(todayQuery, [tenantId, startOfDay]);

      // Month's sales
      const monthQuery = `
        SELECT
          COALESCE(SUM(total), 0) as revenue,
          COUNT(*) as orders
        FROM sales.sales_orders
        WHERE tenant_id = $1
          AND status NOT IN ('cancelled', 'draft')
          AND order_date >= $2
      `;
      const monthResult = await this.dataSource.query(monthQuery, [tenantId, startOfMonth]);

      // Pending orders
      const pendingQuery = `
        SELECT COUNT(*) as count
        FROM sales.sales_orders
        WHERE tenant_id = $1
          AND status IN ('confirmed', 'processing')
      `;
      const pendingResult = await this.dataSource.query(pendingQuery, [tenantId]);

      return {
        todayRevenue: parseFloat(todayResult[0]?.revenue) || 0,
        monthRevenue: parseFloat(monthResult[0]?.revenue) || 0,
        todayOrders: parseInt(todayResult[0]?.orders) || 0,
        monthOrders: parseInt(monthResult[0]?.orders) || 0,
        pendingOrders: parseInt(pendingResult[0]?.count) || 0,
      };
    } catch {
      return {
        todayRevenue: 0,
        monthRevenue: 0,
        todayOrders: 0,
        monthOrders: 0,
        pendingOrders: 0,
      };
    }
  }

  private async getInventoryKPIs(tenantId: string): Promise<DashboardKPIs['inventory']> {
    try {
      const stockQuery = `
        SELECT
          COUNT(DISTINCT product_id) as total_products,
          COUNT(CASE WHEN quantity_on_hand <= 0 THEN 1 END) as out_of_stock,
          COUNT(CASE WHEN quantity_on_hand > 0 AND quantity_on_hand <= 10 THEN 1 END) as low_stock
        FROM inventory.stock_levels
        WHERE tenant_id = $1
      `;
      const stockResult = await this.dataSource.query(stockQuery, [tenantId]);

      const movementsQuery = `
        SELECT COUNT(*) as count
        FROM inventory.stock_movements
        WHERE tenant_id = $1
          AND status = 'draft'
      `;
      const movementsResult = await this.dataSource.query(movementsQuery, [tenantId]);

      return {
        totalProducts: parseInt(stockResult[0]?.total_products) || 0,
        lowStockItems: parseInt(stockResult[0]?.low_stock) || 0,
        outOfStockItems: parseInt(stockResult[0]?.out_of_stock) || 0,
        pendingMovements: parseInt(movementsResult[0]?.count) || 0,
      };
    } catch {
      return {
        totalProducts: 0,
        lowStockItems: 0,
        outOfStockItems: 0,
        pendingMovements: 0,
      };
    }
  }

  private async getInvoiceKPIs(tenantId: string): Promise<DashboardKPIs['invoices']> {
    try {
      const invoicesQuery = `
        SELECT
          COUNT(CASE WHEN status IN ('validated', 'sent') THEN 1 END) as pending,
          COUNT(CASE WHEN status IN ('validated', 'sent', 'partial') AND due_date < CURRENT_DATE THEN 1 END) as overdue,
          COALESCE(SUM(CASE WHEN invoice_type = 'sale' AND status IN ('validated', 'sent', 'partial') THEN (total - amount_paid) END), 0) as receivable,
          COALESCE(SUM(CASE WHEN invoice_type = 'purchase' AND status IN ('validated', 'sent', 'partial') THEN (total - amount_paid) END), 0) as payable
        FROM billing.invoices
        WHERE tenant_id = $1
      `;
      const result = await this.dataSource.query(invoicesQuery, [tenantId]);

      return {
        pendingInvoices: parseInt(result[0]?.pending) || 0,
        overdueInvoices: parseInt(result[0]?.overdue) || 0,
        totalReceivable: parseFloat(result[0]?.receivable) || 0,
        totalPayable: parseFloat(result[0]?.payable) || 0,
      };
    } catch {
      return {
        pendingInvoices: 0,
        overdueInvoices: 0,
        totalReceivable: 0,
        totalPayable: 0,
      };
    }
  }

  private async getPartnerKPIs(tenantId: string): Promise<DashboardKPIs['partners']> {
    const startOfMonth = new Date(new Date().getFullYear(), new Date().getMonth(), 1);

    try {
      const partnersQuery = `
        SELECT
          COUNT(CASE WHEN partner_type IN ('customer', 'both') THEN 1 END) as customers,
          COUNT(CASE WHEN partner_type IN ('supplier', 'both') THEN 1 END) as suppliers,
          COUNT(CASE WHEN partner_type IN ('customer', 'both') AND created_at >= $2 THEN 1 END) as new_customers
        FROM partners.partners
        WHERE tenant_id = $1
          AND deleted_at IS NULL
      `;
      const result = await this.dataSource.query(partnersQuery, [tenantId, startOfMonth]);

      return {
        totalCustomers: parseInt(result[0]?.customers) || 0,
        totalSuppliers: parseInt(result[0]?.suppliers) || 0,
        newCustomersMonth: parseInt(result[0]?.new_customers) || 0,
      };
    } catch {
      return {
        totalCustomers: 0,
        totalSuppliers: 0,
        newCustomersMonth: 0,
      };
    }
  }

  async getActivity(tenantId: string, limit: number = 5): Promise<DashboardActivity> {
    const [recentOrders, recentInvoices, recentMovements, alerts] = await Promise.all([
      this.getRecentOrders(tenantId, limit),
      this.getRecentInvoices(tenantId, limit),
      this.getRecentMovements(tenantId, limit),
      this.getAlerts(tenantId),
    ]);

    return { recentOrders, recentInvoices, recentMovements, alerts };
  }

  private async getRecentOrders(tenantId: string, limit: number): Promise<any[]> {
    try {
      const query = `
        SELECT
          id,
          order_number,
          partner_name,
          total,
          status,
          order_date,
          created_at
        FROM sales.sales_orders
        WHERE tenant_id = $1
        ORDER BY created_at DESC
        LIMIT $2
      `;
      return await this.dataSource.query(query, [tenantId, limit]);
    } catch {
      return [];
    }
  }

  private async getRecentInvoices(tenantId: string, limit: number): Promise<any[]> {
    try {
      const query = `
        SELECT
          id,
          invoice_number,
          invoice_type,
          partner_name,
          total,
          status,
          invoice_date,
          created_at
        FROM billing.invoices
        WHERE tenant_id = $1
        ORDER BY created_at DESC
        LIMIT $2
      `;
      return await this.dataSource.query(query, [tenantId, limit]);
    } catch {
      return [];
    }
  }

  private async getRecentMovements(tenantId: string, limit: number): Promise<any[]> {
    try {
      const query = `
        SELECT
          id,
          movement_number,
          movement_type,
          product_id,
          quantity,
          status,
          created_at
        FROM inventory.stock_movements
        WHERE tenant_id = $1
        ORDER BY created_at DESC
        LIMIT $2
      `;
      return await this.dataSource.query(query, [tenantId, limit]);
    } catch {
      return [];
    }
  }

  private async getAlerts(tenantId: string): Promise<any[]> {
    const alerts: any[] = [];

    try {
      // Low stock alerts
      const lowStockQuery = `
        SELECT COUNT(*) as count
        FROM inventory.stock_levels
        WHERE tenant_id = $1
          AND quantity_on_hand > 0
          AND quantity_on_hand <= 10
      `;
      const lowStockResult = await this.dataSource.query(lowStockQuery, [tenantId]);
      const lowStockCount = parseInt(lowStockResult[0]?.count) || 0;

      if (lowStockCount > 0) {
        alerts.push({
          type: 'warning',
          category: 'inventory',
          message: `${lowStockCount} productos con stock bajo`,
          count: lowStockCount,
        });
      }

      // Overdue invoices alerts
      const overdueQuery = `
        SELECT COUNT(*) as count
        FROM billing.invoices
        WHERE tenant_id = $1
          AND invoice_type = 'sale'
          AND status IN ('validated', 'sent', 'partial')
          AND due_date < CURRENT_DATE
      `;
      const overdueResult = await this.dataSource.query(overdueQuery, [tenantId]);
      const overdueCount = parseInt(overdueResult[0]?.count) || 0;

      if (overdueCount > 0) {
        alerts.push({
          type: 'error',
          category: 'invoices',
          message: `${overdueCount} facturas vencidas`,
          count: overdueCount,
        });
      }

      // Pending orders alerts
      const pendingOrdersQuery = `
        SELECT COUNT(*) as count
        FROM sales.sales_orders
        WHERE tenant_id = $1
          AND status IN ('confirmed')
          AND order_date < CURRENT_DATE - INTERVAL '3 days'
      `;
      const pendingResult = await this.dataSource.query(pendingOrdersQuery, [tenantId]);
      const pendingCount = parseInt(pendingResult[0]?.count) || 0;

      if (pendingCount > 0) {
        alerts.push({
          type: 'warning',
          category: 'sales',
          message: `${pendingCount} pedidos pendientes hace mas de 3 dias`,
          count: pendingCount,
        });
      }
    } catch {
      // Ignore errors - tables might not exist yet
    }

    return alerts;
  }

  async getSalesChart(
    tenantId: string,
    period: 'week' | 'month' | 'year' = 'month'
  ): Promise<{ labels: string[]; data: number[] }> {
    try {
      const intervals = {
        week: { interval: '7 days', format: 'Dy', group: 'day' },
        month: { interval: '30 days', format: 'DD', group: 'day' },
        year: { interval: '12 months', format: 'Mon', group: 'month' },
      };

      const config = intervals[period];

      const query = `
        SELECT
          TO_CHAR(order_date, '${config.format}') as label,
          COALESCE(SUM(total), 0) as total
        FROM sales.sales_orders
        WHERE tenant_id = $1
          AND status NOT IN ('cancelled', 'draft')
          AND order_date >= CURRENT_DATE - INTERVAL '${config.interval}'
        GROUP BY DATE_TRUNC('${config.group}', order_date), TO_CHAR(order_date, '${config.format}')
        ORDER BY DATE_TRUNC('${config.group}', order_date)
      `;

      const result = await this.dataSource.query(query, [tenantId]);

      return {
        labels: result.map((r: any) => r.label),
        data: result.map((r: any) => parseFloat(r.total) || 0),
      };
    } catch {
      return { labels: [], data: [] };
    }
  }
}
