import { Repository, ILike, FindOptionsWhere, DataSource } from 'typeorm';
import {
  Dashboard,
  DashboardWidget,
  WidgetQuery,
  WidgetType,
} from '../entities';
import {
  CreateDashboardDto,
  UpdateDashboardDto,
  CreateDashboardWidgetDto,
  UpdateDashboardWidgetDto,
  CreateWidgetQueryDto,
  UpdateWidgetQueryDto,
  UpdateWidgetPositionsDto,
  DashboardFiltersDto,
} from '../dto';
import { NotFoundError, ValidationError, ForbiddenError } from '../../../shared/errors/index.js';
import { logger } from '../../../shared/utils/logger.js';

/**
 * DashboardsService
 *
 * Manages dashboards, widgets, and widget queries.
 */
export class DashboardsService {
  constructor(
    private readonly dashboardRepository: Repository<Dashboard>,
    private readonly widgetRepository: Repository<DashboardWidget>,
    private readonly queryRepository: Repository<WidgetQuery>,
    private readonly dataSource: DataSource
  ) {}

  // ==================== DASHBOARDS ====================

  /**
   * Get all dashboards for a tenant
   */
  async findAllDashboards(
    tenantId: string,
    userId: string,
    filters: DashboardFiltersDto = {}
  ): Promise<{ data: Dashboard[]; total: number }> {
    const { ownerId, isDefault, isPublic, isActive, search, page = 1, limit = 20 } = filters;

    const queryBuilder = this.dashboardRepository
      .createQueryBuilder('dashboard')
      .where('dashboard.tenant_id = :tenantId', { tenantId })
      .andWhere('(dashboard.owner_id = :userId OR dashboard.is_public = true)', { userId });

    if (ownerId) {
      queryBuilder.andWhere('dashboard.owner_id = :ownerId', { ownerId });
    }

    if (isDefault !== undefined) {
      queryBuilder.andWhere('dashboard.is_default = :isDefault', { isDefault });
    }

    if (isPublic !== undefined) {
      queryBuilder.andWhere('dashboard.is_public = :isPublic', { isPublic });
    }

    if (isActive !== undefined) {
      queryBuilder.andWhere('dashboard.is_active = :isActive', { isActive });
    }

    if (search) {
      queryBuilder.andWhere(
        '(dashboard.name ILIKE :search OR dashboard.description ILIKE :search)',
        { search: `%${search}%` }
      );
    }

    const total = await queryBuilder.getCount();

    const data = await queryBuilder
      .orderBy('dashboard.is_default', 'DESC')
      .addOrderBy('dashboard.name', 'ASC')
      .skip((page - 1) * limit)
      .take(limit)
      .getMany();

    return { data, total };
  }

  /**
   * Get a dashboard by ID with widgets
   */
  async findDashboardById(id: string, tenantId: string, userId: string): Promise<Dashboard> {
    const dashboard = await this.dashboardRepository.findOne({
      where: { id, tenantId },
      relations: ['widgets', 'widgets.queries'],
    });

    if (!dashboard) {
      throw new NotFoundError('Dashboard not found');
    }

    // Check access
    if (!dashboard.isPublic && dashboard.ownerId !== userId) {
      throw new ForbiddenError('Access denied to this dashboard');
    }

    // Sort widgets by position
    if (dashboard.widgets) {
      dashboard.widgets.sort((a, b) => {
        if (a.positionY !== b.positionY) return a.positionY - b.positionY;
        return a.positionX - b.positionX;
      });
    }

    return dashboard;
  }

  /**
   * Get default dashboard for tenant
   */
  async getDefaultDashboard(tenantId: string, userId: string): Promise<Dashboard | null> {
    return this.dashboardRepository.findOne({
      where: { tenantId, isDefault: true, isActive: true },
      relations: ['widgets', 'widgets.queries'],
    });
  }

  /**
   * Create a new dashboard
   */
  async createDashboard(
    dto: CreateDashboardDto,
    tenantId: string,
    ownerId: string
  ): Promise<Dashboard> {
    // If setting as default, unset other defaults
    if (dto.isDefault) {
      await this.dashboardRepository.update(
        { tenantId, isDefault: true },
        { isDefault: false }
      );
    }

    const dashboard = this.dashboardRepository.create({
      ...dto,
      tenantId,
      ownerId,
    });

    const saved = await this.dashboardRepository.save(dashboard);
    logger.info('Dashboard created', { id: saved.id, name: dto.name, tenantId, ownerId });

    return saved;
  }

  /**
   * Update a dashboard
   */
  async updateDashboard(
    id: string,
    dto: UpdateDashboardDto,
    tenantId: string,
    userId: string
  ): Promise<Dashboard> {
    const dashboard = await this.findDashboardById(id, tenantId, userId);

    if (dashboard.ownerId !== userId) {
      throw new ForbiddenError('Only the owner can update this dashboard');
    }

    // If setting as default, unset other defaults
    if (dto.isDefault && !dashboard.isDefault) {
      await this.dashboardRepository.update(
        { tenantId, isDefault: true },
        { isDefault: false }
      );
    }

    Object.assign(dashboard, dto);

    const updated = await this.dashboardRepository.save(dashboard);
    logger.info('Dashboard updated', { id, tenantId });

    return updated;
  }

  /**
   * Delete a dashboard
   */
  async deleteDashboard(id: string, tenantId: string, userId: string): Promise<void> {
    const dashboard = await this.findDashboardById(id, tenantId, userId);

    if (dashboard.ownerId !== userId) {
      throw new ForbiddenError('Only the owner can delete this dashboard');
    }

    await this.dashboardRepository.remove(dashboard);
    logger.info('Dashboard deleted', { id, tenantId });
  }

  // ==================== WIDGETS ====================

  /**
   * Get widgets for a dashboard
   */
  async findWidgetsByDashboard(dashboardId: string): Promise<DashboardWidget[]> {
    return this.widgetRepository.find({
      where: { dashboardId, isActive: true },
      relations: ['queries'],
      order: { sortOrder: 'ASC' },
    });
  }

  /**
   * Get a widget by ID
   */
  async findWidgetById(id: string): Promise<DashboardWidget> {
    const widget = await this.widgetRepository.findOne({
      where: { id },
      relations: ['queries', 'dashboard'],
    });

    if (!widget) {
      throw new NotFoundError('Widget not found');
    }

    return widget;
  }

  /**
   * Create a widget
   */
  async createWidget(
    dto: CreateDashboardWidgetDto,
    tenantId: string,
    userId: string
  ): Promise<DashboardWidget> {
    // Verify dashboard access
    await this.findDashboardById(dto.dashboardId, tenantId, userId);

    const widget = this.widgetRepository.create(dto);
    const saved = await this.widgetRepository.save(widget);
    logger.info('Widget created', { id: saved.id, dashboardId: dto.dashboardId });

    return saved;
  }

  /**
   * Update a widget
   */
  async updateWidget(
    id: string,
    dto: UpdateDashboardWidgetDto,
    tenantId: string,
    userId: string
  ): Promise<DashboardWidget> {
    const widget = await this.findWidgetById(id);

    // Verify dashboard access
    await this.findDashboardById(widget.dashboardId, tenantId, userId);

    Object.assign(widget, dto);

    const updated = await this.widgetRepository.save(widget);
    logger.info('Widget updated', { id });

    return updated;
  }

  /**
   * Update multiple widget positions
   */
  async updateWidgetPositions(
    dto: UpdateWidgetPositionsDto,
    tenantId: string,
    userId: string
  ): Promise<void> {
    for (const widgetPos of dto.widgets) {
      await this.widgetRepository.update(widgetPos.id, {
        positionX: widgetPos.positionX,
        positionY: widgetPos.positionY,
        width: widgetPos.width,
        height: widgetPos.height,
      });
    }

    logger.info('Widget positions updated', { count: dto.widgets.length });
  }

  /**
   * Delete a widget
   */
  async deleteWidget(id: string, tenantId: string, userId: string): Promise<void> {
    const widget = await this.findWidgetById(id);

    // Verify dashboard access
    await this.findDashboardById(widget.dashboardId, tenantId, userId);

    await this.widgetRepository.remove(widget);
    logger.info('Widget deleted', { id });
  }

  // ==================== WIDGET QUERIES ====================

  /**
   * Create a widget query
   */
  async createWidgetQuery(dto: CreateWidgetQueryDto): Promise<WidgetQuery> {
    const query = this.queryRepository.create(dto);
    const saved = await this.queryRepository.save(query);
    logger.info('Widget query created', { id: saved.id, widgetId: dto.widgetId });

    return saved;
  }

  /**
   * Update a widget query
   */
  async updateWidgetQuery(id: string, dto: UpdateWidgetQueryDto): Promise<WidgetQuery> {
    const query = await this.queryRepository.findOne({ where: { id } });

    if (!query) {
      throw new NotFoundError('Widget query not found');
    }

    Object.assign(query, dto);

    const updated = await this.queryRepository.save(query);
    logger.info('Widget query updated', { id });

    return updated;
  }

  /**
   * Delete a widget query
   */
  async deleteWidgetQuery(id: string): Promise<void> {
    const query = await this.queryRepository.findOne({ where: { id } });

    if (!query) {
      throw new NotFoundError('Widget query not found');
    }

    await this.queryRepository.remove(query);
    logger.info('Widget query deleted', { id });
  }

  /**
   * Execute a widget query and return data
   */
  async executeWidgetQuery(
    queryId: string,
    tenantId: string,
    parameters: Record<string, any> = {}
  ): Promise<any> {
    const query = await this.queryRepository.findOne({
      where: { id: queryId },
      relations: ['widget'],
    });

    if (!query) {
      throw new NotFoundError('Widget query not found');
    }

    // Check cache
    if (query.lastCachedAt && query.cacheTtlSeconds) {
      const cacheAge = (Date.now() - query.lastCachedAt.getTime()) / 1000;
      if (cacheAge < query.cacheTtlSeconds) {
        // Return cached data from resultMapping if available
        logger.debug('Returning cached widget data', { queryId });
      }
    }

    let result: any;

    if (query.queryFunction) {
      result = await this.dataSource.query(
        `SELECT * FROM ${query.queryFunction}($1)`,
        [tenantId]
      );
    } else if (query.queryText) {
      const safeQuery = query.queryText.replace(/\{\{tenant_id\}\}/g, '$1');
      result = await this.dataSource.query(safeQuery, [tenantId]);
    } else {
      throw new ValidationError('Widget query has no query defined');
    }

    // Update cache timestamp
    await this.queryRepository.update(queryId, {
      lastCachedAt: new Date(),
    });

    // Apply result mapping if defined
    if (query.resultMapping && Object.keys(query.resultMapping).length > 0) {
      result = this.applyResultMapping(result, query.resultMapping);
    }

    return result;
  }

  /**
   * Apply result mapping to transform query results
   */
  private applyResultMapping(
    data: any[],
    mapping: Record<string, any>
  ): any {
    // Simple mapping implementation
    if (mapping.type === 'single') {
      return data[0] || null;
    }

    if (mapping.type === 'aggregate') {
      const field = mapping.field;
      const aggregation = mapping.aggregation;

      if (aggregation === 'sum') {
        return data.reduce((sum, row) => sum + (parseFloat(row[field]) || 0), 0);
      }
      if (aggregation === 'count') {
        return data.length;
      }
      if (aggregation === 'avg') {
        const sum = data.reduce((s, row) => s + (parseFloat(row[field]) || 0), 0);
        return data.length > 0 ? sum / data.length : 0;
      }
    }

    return data;
  }

  /**
   * Get all widget data for a dashboard
   */
  async getDashboardData(
    dashboardId: string,
    tenantId: string,
    userId: string
  ): Promise<Record<string, any>> {
    const dashboard = await this.findDashboardById(dashboardId, tenantId, userId);
    const data: Record<string, any> = {};

    for (const widget of dashboard.widgets || []) {
      if (!widget.isActive) continue;

      const widgetData: any[] = [];

      for (const query of widget.queries || []) {
        try {
          const result = await this.executeWidgetQuery(query.id, tenantId);
          widgetData.push({ name: query.name, data: result });
        } catch (error: any) {
          logger.error('Failed to execute widget query', {
            queryId: query.id,
            widgetId: widget.id,
            error: error.message,
          });
          widgetData.push({ name: query.name, error: error.message });
        }
      }

      data[widget.id] = {
        title: widget.title,
        type: widget.widgetType,
        queries: widgetData,
      };
    }

    return data;
  }
}
