import { query, queryOne } from '../../config/database.js';
import { NotFoundError, ConflictError } from '../../shared/errors/index.js';

// ========== LEAD STAGES ==========

export interface LeadStage {
  id: string;
  tenant_id: string;
  name: string;
  sequence: number;
  is_won: boolean;
  probability: number;
  requirements?: string;
  active: boolean;
  created_at: Date;
}

export interface CreateLeadStageDto {
  name: string;
  sequence?: number;
  is_won?: boolean;
  probability?: number;
  requirements?: string;
}

export interface UpdateLeadStageDto {
  name?: string;
  sequence?: number;
  is_won?: boolean;
  probability?: number;
  requirements?: string | null;
  active?: boolean;
}

// ========== OPPORTUNITY STAGES ==========

export interface OpportunityStage {
  id: string;
  tenant_id: string;
  name: string;
  sequence: number;
  is_won: boolean;
  probability: number;
  requirements?: string;
  active: boolean;
  created_at: Date;
}

export interface CreateOpportunityStageDto {
  name: string;
  sequence?: number;
  is_won?: boolean;
  probability?: number;
  requirements?: string;
}

export interface UpdateOpportunityStageDto {
  name?: string;
  sequence?: number;
  is_won?: boolean;
  probability?: number;
  requirements?: string | null;
  active?: boolean;
}

// ========== LOST REASONS ==========

export interface LostReason {
  id: string;
  tenant_id: string;
  name: string;
  description?: string;
  active: boolean;
  created_at: Date;
}

export interface CreateLostReasonDto {
  name: string;
  description?: string;
}

export interface UpdateLostReasonDto {
  name?: string;
  description?: string | null;
  active?: boolean;
}

class StagesService {
  // ========== LEAD STAGES ==========

  async getLeadStages(tenantId: string, includeInactive = false): Promise<LeadStage[]> {
    let whereClause = 'WHERE tenant_id = $1';
    if (!includeInactive) {
      whereClause += ' AND active = TRUE';
    }

    return query<LeadStage>(
      `SELECT * FROM crm.lead_stages ${whereClause} ORDER BY sequence`,
      [tenantId]
    );
  }

  async getLeadStageById(id: string, tenantId: string): Promise<LeadStage> {
    const stage = await queryOne<LeadStage>(
      `SELECT * FROM crm.lead_stages WHERE id = $1 AND tenant_id = $2`,
      [id, tenantId]
    );

    if (!stage) {
      throw new NotFoundError('Etapa de lead no encontrada');
    }

    return stage;
  }

  async createLeadStage(dto: CreateLeadStageDto, tenantId: string): Promise<LeadStage> {
    // Check unique name
    const existing = await queryOne(
      `SELECT id FROM crm.lead_stages WHERE name = $1 AND tenant_id = $2`,
      [dto.name, tenantId]
    );

    if (existing) {
      throw new ConflictError('Ya existe una etapa con ese nombre');
    }

    const stage = await queryOne<LeadStage>(
      `INSERT INTO crm.lead_stages (tenant_id, name, sequence, is_won, probability, requirements)
       VALUES ($1, $2, $3, $4, $5, $6)
       RETURNING *`,
      [tenantId, dto.name, dto.sequence || 10, dto.is_won || false, dto.probability || 0, dto.requirements]
    );

    return stage!;
  }

  async updateLeadStage(id: string, dto: UpdateLeadStageDto, tenantId: string): Promise<LeadStage> {
    await this.getLeadStageById(id, tenantId);

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

    if (dto.name !== undefined) {
      // Check unique name
      const existing = await queryOne(
        `SELECT id FROM crm.lead_stages WHERE name = $1 AND tenant_id = $2 AND id != $3`,
        [dto.name, tenantId, id]
      );
      if (existing) {
        throw new ConflictError('Ya existe una etapa con ese nombre');
      }
      updateFields.push(`name = $${paramIndex++}`);
      values.push(dto.name);
    }
    if (dto.sequence !== undefined) {
      updateFields.push(`sequence = $${paramIndex++}`);
      values.push(dto.sequence);
    }
    if (dto.is_won !== undefined) {
      updateFields.push(`is_won = $${paramIndex++}`);
      values.push(dto.is_won);
    }
    if (dto.probability !== undefined) {
      updateFields.push(`probability = $${paramIndex++}`);
      values.push(dto.probability);
    }
    if (dto.requirements !== undefined) {
      updateFields.push(`requirements = $${paramIndex++}`);
      values.push(dto.requirements);
    }
    if (dto.active !== undefined) {
      updateFields.push(`active = $${paramIndex++}`);
      values.push(dto.active);
    }

    if (updateFields.length === 0) {
      return this.getLeadStageById(id, tenantId);
    }

    values.push(id, tenantId);

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

    return this.getLeadStageById(id, tenantId);
  }

  async deleteLeadStage(id: string, tenantId: string): Promise<void> {
    await this.getLeadStageById(id, tenantId);

    // Check if stage is in use
    const inUse = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM crm.leads WHERE stage_id = $1`,
      [id]
    );

    if (parseInt(inUse?.count || '0') > 0) {
      throw new ConflictError('No se puede eliminar una etapa que tiene leads asociados');
    }

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

  // ========== OPPORTUNITY STAGES ==========

  async getOpportunityStages(tenantId: string, includeInactive = false): Promise<OpportunityStage[]> {
    let whereClause = 'WHERE tenant_id = $1';
    if (!includeInactive) {
      whereClause += ' AND active = TRUE';
    }

    return query<OpportunityStage>(
      `SELECT * FROM crm.opportunity_stages ${whereClause} ORDER BY sequence`,
      [tenantId]
    );
  }

  async getOpportunityStageById(id: string, tenantId: string): Promise<OpportunityStage> {
    const stage = await queryOne<OpportunityStage>(
      `SELECT * FROM crm.opportunity_stages WHERE id = $1 AND tenant_id = $2`,
      [id, tenantId]
    );

    if (!stage) {
      throw new NotFoundError('Etapa de oportunidad no encontrada');
    }

    return stage;
  }

  async createOpportunityStage(dto: CreateOpportunityStageDto, tenantId: string): Promise<OpportunityStage> {
    // Check unique name
    const existing = await queryOne(
      `SELECT id FROM crm.opportunity_stages WHERE name = $1 AND tenant_id = $2`,
      [dto.name, tenantId]
    );

    if (existing) {
      throw new ConflictError('Ya existe una etapa con ese nombre');
    }

    const stage = await queryOne<OpportunityStage>(
      `INSERT INTO crm.opportunity_stages (tenant_id, name, sequence, is_won, probability, requirements)
       VALUES ($1, $2, $3, $4, $5, $6)
       RETURNING *`,
      [tenantId, dto.name, dto.sequence || 10, dto.is_won || false, dto.probability || 0, dto.requirements]
    );

    return stage!;
  }

  async updateOpportunityStage(id: string, dto: UpdateOpportunityStageDto, tenantId: string): Promise<OpportunityStage> {
    await this.getOpportunityStageById(id, tenantId);

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

    if (dto.name !== undefined) {
      const existing = await queryOne(
        `SELECT id FROM crm.opportunity_stages WHERE name = $1 AND tenant_id = $2 AND id != $3`,
        [dto.name, tenantId, id]
      );
      if (existing) {
        throw new ConflictError('Ya existe una etapa con ese nombre');
      }
      updateFields.push(`name = $${paramIndex++}`);
      values.push(dto.name);
    }
    if (dto.sequence !== undefined) {
      updateFields.push(`sequence = $${paramIndex++}`);
      values.push(dto.sequence);
    }
    if (dto.is_won !== undefined) {
      updateFields.push(`is_won = $${paramIndex++}`);
      values.push(dto.is_won);
    }
    if (dto.probability !== undefined) {
      updateFields.push(`probability = $${paramIndex++}`);
      values.push(dto.probability);
    }
    if (dto.requirements !== undefined) {
      updateFields.push(`requirements = $${paramIndex++}`);
      values.push(dto.requirements);
    }
    if (dto.active !== undefined) {
      updateFields.push(`active = $${paramIndex++}`);
      values.push(dto.active);
    }

    if (updateFields.length === 0) {
      return this.getOpportunityStageById(id, tenantId);
    }

    values.push(id, tenantId);

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

    return this.getOpportunityStageById(id, tenantId);
  }

  async deleteOpportunityStage(id: string, tenantId: string): Promise<void> {
    await this.getOpportunityStageById(id, tenantId);

    const inUse = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM crm.opportunities WHERE stage_id = $1`,
      [id]
    );

    if (parseInt(inUse?.count || '0') > 0) {
      throw new ConflictError('No se puede eliminar una etapa que tiene oportunidades asociadas');
    }

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

  // ========== LOST REASONS ==========

  async getLostReasons(tenantId: string, includeInactive = false): Promise<LostReason[]> {
    let whereClause = 'WHERE tenant_id = $1';
    if (!includeInactive) {
      whereClause += ' AND active = TRUE';
    }

    return query<LostReason>(
      `SELECT * FROM crm.lost_reasons ${whereClause} ORDER BY name`,
      [tenantId]
    );
  }

  async getLostReasonById(id: string, tenantId: string): Promise<LostReason> {
    const reason = await queryOne<LostReason>(
      `SELECT * FROM crm.lost_reasons WHERE id = $1 AND tenant_id = $2`,
      [id, tenantId]
    );

    if (!reason) {
      throw new NotFoundError('Razon de perdida no encontrada');
    }

    return reason;
  }

  async createLostReason(dto: CreateLostReasonDto, tenantId: string): Promise<LostReason> {
    const existing = await queryOne(
      `SELECT id FROM crm.lost_reasons WHERE name = $1 AND tenant_id = $2`,
      [dto.name, tenantId]
    );

    if (existing) {
      throw new ConflictError('Ya existe una razon con ese nombre');
    }

    const reason = await queryOne<LostReason>(
      `INSERT INTO crm.lost_reasons (tenant_id, name, description)
       VALUES ($1, $2, $3)
       RETURNING *`,
      [tenantId, dto.name, dto.description]
    );

    return reason!;
  }

  async updateLostReason(id: string, dto: UpdateLostReasonDto, tenantId: string): Promise<LostReason> {
    await this.getLostReasonById(id, tenantId);

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

    if (dto.name !== undefined) {
      const existing = await queryOne(
        `SELECT id FROM crm.lost_reasons WHERE name = $1 AND tenant_id = $2 AND id != $3`,
        [dto.name, tenantId, id]
      );
      if (existing) {
        throw new ConflictError('Ya existe una razon con ese nombre');
      }
      updateFields.push(`name = $${paramIndex++}`);
      values.push(dto.name);
    }
    if (dto.description !== undefined) {
      updateFields.push(`description = $${paramIndex++}`);
      values.push(dto.description);
    }
    if (dto.active !== undefined) {
      updateFields.push(`active = $${paramIndex++}`);
      values.push(dto.active);
    }

    if (updateFields.length === 0) {
      return this.getLostReasonById(id, tenantId);
    }

    values.push(id, tenantId);

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

    return this.getLostReasonById(id, tenantId);
  }

  async deleteLostReason(id: string, tenantId: string): Promise<void> {
    await this.getLostReasonById(id, tenantId);

    const inUseLeads = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM crm.leads WHERE lost_reason_id = $1`,
      [id]
    );

    const inUseOpps = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM crm.opportunities WHERE lost_reason_id = $1`,
      [id]
    );

    if (parseInt(inUseLeads?.count || '0') > 0 || parseInt(inUseOpps?.count || '0') > 0) {
      throw new ConflictError('No se puede eliminar una razon que esta en uso');
    }

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

export const stagesService = new StagesService();
