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

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

export type ActivityType = 'call' | 'meeting' | 'email' | 'task' | 'note' | 'other';
export type ActivityStatus = 'scheduled' | 'done' | 'cancelled';

export interface Activity {
  id: string;
  tenant_id: string;
  company_id: string;
  company_name?: string;
  activity_type: ActivityType;
  name: string;
  description?: string;
  user_id?: string;
  user_name?: string;
  // Polymorphic relations
  res_model?: string; // 'opportunity', 'lead', 'partner'
  res_id?: string;
  res_name?: string;
  partner_id?: string;
  partner_name?: string;
  scheduled_date?: Date;
  date_done?: Date;
  duration_hours?: number;
  status: ActivityStatus;
  priority: number;
  notes?: string;
  created_at: Date;
  created_by?: string;
}

export interface CreateActivityDto {
  company_id: string;
  activity_type: ActivityType;
  name: string;
  description?: string;
  user_id?: string;
  res_model?: string;
  res_id?: string;
  partner_id?: string;
  scheduled_date?: string;
  duration_hours?: number;
  priority?: number;
  notes?: string;
}

export interface UpdateActivityDto {
  activity_type?: ActivityType;
  name?: string;
  description?: string | null;
  user_id?: string | null;
  partner_id?: string | null;
  scheduled_date?: string | null;
  duration_hours?: number | null;
  priority?: number;
  notes?: string | null;
}

export interface ActivityFilters {
  company_id?: string;
  activity_type?: ActivityType;
  status?: ActivityStatus;
  user_id?: string;
  partner_id?: string;
  res_model?: string;
  res_id?: string;
  date_from?: string;
  date_to?: string;
  search?: string;
  page?: number;
  limit?: number;
}

export interface ActivitySummary {
  total_activities: number;
  scheduled: number;
  done: number;
  cancelled: number;
  overdue: number;
  by_type: Record<ActivityType, number>;
}

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

class ActivitiesService {
  async findAll(tenantId: string, filters: ActivityFilters = {}): Promise<{ data: Activity[]; total: number }> {
    const { company_id, activity_type, status, user_id, partner_id, res_model, res_id, date_from, date_to, search, page = 1, limit = 20 } = filters;
    const offset = (page - 1) * limit;

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

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

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

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

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

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

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

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

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

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

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

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

    params.push(limit, offset);
    const data = await query<Activity>(
      `SELECT a.*,
              c.name as company_name,
              u.name as user_name,
              p.name as partner_name
       FROM crm.activities a
       LEFT JOIN auth.companies c ON a.company_id = c.id
       LEFT JOIN auth.users u ON a.user_id = u.id
       LEFT JOIN core.partners p ON a.partner_id = p.id
       ${whereClause}
       ORDER BY
         CASE WHEN a.status = 'scheduled' THEN 0 ELSE 1 END,
         a.scheduled_date ASC NULLS LAST,
         a.created_at DESC
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      params
    );

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

  async findById(id: string, tenantId: string): Promise<Activity> {
    const activity = await queryOne<Activity>(
      `SELECT a.*,
              c.name as company_name,
              u.name as user_name,
              p.name as partner_name
       FROM crm.activities a
       LEFT JOIN auth.companies c ON a.company_id = c.id
       LEFT JOIN auth.users u ON a.user_id = u.id
       LEFT JOIN core.partners p ON a.partner_id = p.id
       WHERE a.id = $1 AND a.tenant_id = $2`,
      [id, tenantId]
    );

    if (!activity) {
      throw new NotFoundError('Actividad no encontrada');
    }

    // Get resource name if linked
    if (activity.res_model && activity.res_id) {
      activity.res_name = await this.getResourceName(activity.res_model, activity.res_id, tenantId);
    }

    return activity;
  }

  async create(dto: CreateActivityDto, tenantId: string, userId: string): Promise<Activity> {
    const activity = await queryOne<Activity>(
      `INSERT INTO crm.activities (
        tenant_id, company_id, activity_type, name, description,
        user_id, res_model, res_id, partner_id, scheduled_date,
        duration_hours, priority, notes, created_by
      )
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
       RETURNING *`,
      [
        tenantId, dto.company_id, dto.activity_type, dto.name, dto.description,
        dto.user_id, dto.res_model, dto.res_id, dto.partner_id, dto.scheduled_date,
        dto.duration_hours, dto.priority || 1, dto.notes, userId
      ]
    );

    logger.info('Activity created', {
      activityId: activity?.id,
      activityType: dto.activity_type,
      resModel: dto.res_model,
      resId: dto.res_id,
    });

    // Update date_last_activity on related opportunity/lead
    if (dto.res_model && dto.res_id) {
      await this.updateLastActivityDate(dto.res_model, dto.res_id, tenantId);
    }

    return this.findById(activity!.id, tenantId);
  }

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

    if (existing.status === 'done') {
      throw new ValidationError('No se pueden editar actividades completadas');
    }

    const updateFields: string[] = [];
    const values: any[] = [];
    let paramIndex = 1;

    if (dto.activity_type !== undefined) {
      updateFields.push(`activity_type = $${paramIndex++}`);
      values.push(dto.activity_type);
    }
    if (dto.name !== undefined) {
      updateFields.push(`name = $${paramIndex++}`);
      values.push(dto.name);
    }
    if (dto.description !== undefined) {
      updateFields.push(`description = $${paramIndex++}`);
      values.push(dto.description);
    }
    if (dto.user_id !== undefined) {
      updateFields.push(`user_id = $${paramIndex++}`);
      values.push(dto.user_id);
    }
    if (dto.partner_id !== undefined) {
      updateFields.push(`partner_id = $${paramIndex++}`);
      values.push(dto.partner_id);
    }
    if (dto.scheduled_date !== undefined) {
      updateFields.push(`scheduled_date = $${paramIndex++}`);
      values.push(dto.scheduled_date);
    }
    if (dto.duration_hours !== undefined) {
      updateFields.push(`duration_hours = $${paramIndex++}`);
      values.push(dto.duration_hours);
    }
    if (dto.priority !== undefined) {
      updateFields.push(`priority = $${paramIndex++}`);
      values.push(dto.priority);
    }
    if (dto.notes !== undefined) {
      updateFields.push(`notes = $${paramIndex++}`);
      values.push(dto.notes);
    }

    if (updateFields.length === 0) {
      return existing;
    }

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

    values.push(id, tenantId);

    await query(
      `UPDATE crm.activities SET ${updateFields.join(', ')}
       WHERE id = $${paramIndex++} AND tenant_id = $${paramIndex}`,
      values
    );

    return this.findById(id, tenantId);
  }

  async markDone(id: string, tenantId: string, userId: string, notes?: string): Promise<Activity> {
    const activity = await this.findById(id, tenantId);

    if (activity.status === 'done') {
      throw new ValidationError('La actividad ya está completada');
    }

    if (activity.status === 'cancelled') {
      throw new ValidationError('No se puede completar una actividad cancelada');
    }

    await query(
      `UPDATE crm.activities SET
         status = 'done',
         date_done = CURRENT_TIMESTAMP,
         notes = COALESCE($1, notes),
         updated_by = $2,
         updated_at = CURRENT_TIMESTAMP
       WHERE id = $3 AND tenant_id = $4`,
      [notes, userId, id, tenantId]
    );

    // Update date_last_activity on related opportunity/lead
    if (activity.res_model && activity.res_id) {
      await this.updateLastActivityDate(activity.res_model, activity.res_id, tenantId);
    }

    logger.info('Activity marked as done', {
      activityId: id,
      activityType: activity.activity_type,
    });

    return this.findById(id, tenantId);
  }

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

    if (activity.status === 'done') {
      throw new ValidationError('No se puede cancelar una actividad completada');
    }

    if (activity.status === 'cancelled') {
      throw new ValidationError('La actividad ya está cancelada');
    }

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

    return this.findById(id, tenantId);
  }

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

    if (activity.status === 'done') {
      throw new ValidationError('No se pueden eliminar actividades completadas');
    }

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

  /**
   * Get activities for a specific resource (opportunity, lead, partner)
   */
  async getResourceActivities(
    resModel: string,
    resId: string,
    tenantId: string,
    status?: ActivityStatus
  ): Promise<Activity[]> {
    let whereClause = 'WHERE a.res_model = $1 AND a.res_id = $2 AND a.tenant_id = $3';
    const params: any[] = [resModel, resId, tenantId];

    if (status) {
      whereClause += ' AND a.status = $4';
      params.push(status);
    }

    return query<Activity>(
      `SELECT a.*,
              u.name as user_name,
              p.name as partner_name
       FROM crm.activities a
       LEFT JOIN auth.users u ON a.user_id = u.id
       LEFT JOIN core.partners p ON a.partner_id = p.id
       ${whereClause}
       ORDER BY a.scheduled_date ASC NULLS LAST, a.created_at DESC`,
      params
    );
  }

  /**
   * Get activity summary for dashboard
   */
  async getActivitySummary(
    tenantId: string,
    userId?: string,
    dateFrom?: string,
    dateTo?: string
  ): Promise<ActivitySummary> {
    let whereClause = 'WHERE tenant_id = $1';
    const params: any[] = [tenantId];
    let paramIndex = 2;

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

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

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

    const result = await queryOne<{
      total: string;
      scheduled: string;
      done: string;
      cancelled: string;
      overdue: string;
    }>(
      `SELECT
         COUNT(*) as total,
         COUNT(*) FILTER (WHERE status = 'scheduled') as scheduled,
         COUNT(*) FILTER (WHERE status = 'done') as done,
         COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled,
         COUNT(*) FILTER (WHERE status = 'scheduled' AND scheduled_date < CURRENT_DATE) as overdue
       FROM crm.activities
       ${whereClause}`,
      params
    );

    const byTypeResult = await query<{ activity_type: ActivityType; count: string }>(
      `SELECT activity_type, COUNT(*) as count
       FROM crm.activities
       ${whereClause}
       GROUP BY activity_type`,
      params
    );

    const byType: Record<ActivityType, number> = {
      call: 0,
      meeting: 0,
      email: 0,
      task: 0,
      note: 0,
      other: 0,
    };

    for (const row of byTypeResult) {
      byType[row.activity_type] = parseInt(row.count, 10);
    }

    return {
      total_activities: parseInt(result?.total || '0', 10),
      scheduled: parseInt(result?.scheduled || '0', 10),
      done: parseInt(result?.done || '0', 10),
      cancelled: parseInt(result?.cancelled || '0', 10),
      overdue: parseInt(result?.overdue || '0', 10),
      by_type: byType,
    };
  }

  /**
   * Schedule a follow-up activity after completing one
   */
  async scheduleFollowUp(
    completedActivityId: string,
    followUpDto: CreateActivityDto,
    tenantId: string,
    userId: string
  ): Promise<Activity> {
    const completedActivity = await this.findById(completedActivityId, tenantId);

    // Inherit resource info from completed activity if not specified
    const dto = {
      ...followUpDto,
      res_model: followUpDto.res_model || completedActivity.res_model,
      res_id: followUpDto.res_id || completedActivity.res_id,
      partner_id: followUpDto.partner_id || completedActivity.partner_id,
    };

    return this.create(dto, tenantId, userId);
  }

  /**
   * Get overdue activities count for notifications
   */
  async getOverdueCount(tenantId: string, userId?: string): Promise<number> {
    let whereClause = 'WHERE tenant_id = $1 AND status = \'scheduled\' AND scheduled_date < CURRENT_DATE';
    const params: any[] = [tenantId];

    if (userId) {
      whereClause += ' AND user_id = $2';
      params.push(userId);
    }

    const result = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM crm.activities ${whereClause}`,
      params
    );

    return parseInt(result?.count || '0', 10);
  }

  private async getResourceName(resModel: string, resId: string, tenantId: string): Promise<string> {
    let tableName: string;
    switch (resModel) {
      case 'opportunity':
        tableName = 'crm.opportunities';
        break;
      case 'lead':
        tableName = 'crm.leads';
        break;
      case 'partner':
        tableName = 'core.partners';
        break;
      default:
        return '';
    }

    const result = await queryOne<{ name: string }>(
      `SELECT name FROM ${tableName} WHERE id = $1 AND tenant_id = $2`,
      [resId, tenantId]
    );

    return result?.name || '';
  }

  private async updateLastActivityDate(resModel: string, resId: string, tenantId: string): Promise<void> {
    let tableName: string;
    switch (resModel) {
      case 'opportunity':
        tableName = 'crm.opportunities';
        break;
      case 'lead':
        tableName = 'crm.leads';
        break;
      default:
        return;
    }

    await query(
      `UPDATE ${tableName} SET date_last_activity = CURRENT_TIMESTAMP WHERE id = $1 AND tenant_id = $2`,
      [resId, tenantId]
    );
  }
}

export const activitiesService = new ActivitiesService();
