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

export type OpportunityStatus = 'open' | 'won' | 'lost';

export interface Opportunity {
  id: string;
  tenant_id: string;
  company_id: string;
  company_name?: string;
  name: string;
  ref?: string;
  partner_id: string;
  partner_name?: string;
  contact_name?: string;
  email?: string;
  phone?: string;
  stage_id?: string;
  stage_name?: string;
  status: OpportunityStatus;
  user_id?: string;
  user_name?: string;
  sales_team_id?: string;
  priority: number;
  probability: number;
  expected_revenue?: number;
  recurring_revenue?: number;
  recurring_plan?: string;
  date_deadline?: Date;
  date_closed?: Date;
  date_last_activity?: Date;
  lead_id?: string;
  source?: LeadSource;
  lost_reason_id?: string;
  lost_reason_name?: string;
  lost_notes?: string;
  quotation_id?: string;
  order_id?: string;
  description?: string;
  notes?: string;
  tags?: string[];
  created_at: Date;
}

export interface CreateOpportunityDto {
  company_id: string;
  name: string;
  ref?: string;
  partner_id: string;
  contact_name?: string;
  email?: string;
  phone?: string;
  stage_id?: string;
  user_id?: string;
  sales_team_id?: string;
  priority?: number;
  probability?: number;
  expected_revenue?: number;
  recurring_revenue?: number;
  recurring_plan?: string;
  date_deadline?: string;
  source?: LeadSource;
  description?: string;
  notes?: string;
  tags?: string[];
}

export interface UpdateOpportunityDto {
  name?: string;
  ref?: string | null;
  partner_id?: string;
  contact_name?: string | null;
  email?: string | null;
  phone?: string | null;
  stage_id?: string | null;
  user_id?: string | null;
  sales_team_id?: string | null;
  priority?: number;
  probability?: number;
  expected_revenue?: number | null;
  recurring_revenue?: number | null;
  recurring_plan?: string | null;
  date_deadline?: string | null;
  description?: string | null;
  notes?: string | null;
  tags?: string[] | null;
}

export interface OpportunityFilters {
  company_id?: string;
  status?: OpportunityStatus;
  stage_id?: string;
  user_id?: string;
  partner_id?: string;
  priority?: number;
  search?: string;
  page?: number;
  limit?: number;
}

class OpportunitiesService {
  async findAll(tenantId: string, filters: OpportunityFilters = {}): Promise<{ data: Opportunity[]; total: number }> {
    const { company_id, status, stage_id, user_id, partner_id, priority, search, page = 1, limit = 20 } = filters;
    const offset = (page - 1) * limit;

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

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

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

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

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

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

    if (priority !== undefined) {
      whereClause += ` AND o.priority = $${paramIndex++}`;
      params.push(priority);
    }

    if (search) {
      whereClause += ` AND (o.name ILIKE $${paramIndex} OR o.contact_name ILIKE $${paramIndex} OR o.email ILIKE $${paramIndex} OR p.name ILIKE $${paramIndex})`;
      params.push(`%${search}%`);
      paramIndex++;
    }

    const countResult = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM crm.opportunities o
       LEFT JOIN core.partners p ON o.partner_id = p.id
       ${whereClause}`,
      params
    );

    params.push(limit, offset);
    const data = await query<Opportunity>(
      `SELECT o.*,
              c.name as company_org_name,
              p.name as partner_name,
              os.name as stage_name,
              u.email as user_email,
              lr.name as lost_reason_name
       FROM crm.opportunities o
       LEFT JOIN auth.companies c ON o.company_id = c.id
       LEFT JOIN core.partners p ON o.partner_id = p.id
       LEFT JOIN crm.opportunity_stages os ON o.stage_id = os.id
       LEFT JOIN auth.users u ON o.user_id = u.id
       LEFT JOIN crm.lost_reasons lr ON o.lost_reason_id = lr.id
       ${whereClause}
       ORDER BY o.priority DESC, o.expected_revenue DESC NULLS LAST, o.created_at DESC
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      params
    );

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

  async findById(id: string, tenantId: string): Promise<Opportunity> {
    const opportunity = await queryOne<Opportunity>(
      `SELECT o.*,
              c.name as company_org_name,
              p.name as partner_name,
              os.name as stage_name,
              u.email as user_email,
              lr.name as lost_reason_name
       FROM crm.opportunities o
       LEFT JOIN auth.companies c ON o.company_id = c.id
       LEFT JOIN core.partners p ON o.partner_id = p.id
       LEFT JOIN crm.opportunity_stages os ON o.stage_id = os.id
       LEFT JOIN auth.users u ON o.user_id = u.id
       LEFT JOIN crm.lost_reasons lr ON o.lost_reason_id = lr.id
       WHERE o.id = $1 AND o.tenant_id = $2`,
      [id, tenantId]
    );

    if (!opportunity) {
      throw new NotFoundError('Oportunidad no encontrada');
    }

    return opportunity;
  }

  async create(dto: CreateOpportunityDto, tenantId: string, userId: string): Promise<Opportunity> {
    const opportunity = await queryOne<Opportunity>(
      `INSERT INTO crm.opportunities (
        tenant_id, company_id, name, ref, partner_id, contact_name, email, phone,
        stage_id, user_id, sales_team_id, priority, probability, expected_revenue,
        recurring_revenue, recurring_plan, date_deadline, source, description, notes, tags, created_by
      )
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22)
       RETURNING *`,
      [
        tenantId, dto.company_id, dto.name, dto.ref, dto.partner_id, dto.contact_name,
        dto.email, dto.phone, dto.stage_id, dto.user_id, dto.sales_team_id,
        dto.priority || 0, dto.probability || 0, dto.expected_revenue, dto.recurring_revenue,
        dto.recurring_plan, dto.date_deadline, dto.source, dto.description, dto.notes, dto.tags, userId
      ]
    );

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

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

    if (existing.status !== 'open') {
      throw new ValidationError('Solo se pueden editar oportunidades abiertas');
    }

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

    const fieldsToUpdate = [
      'name', 'ref', 'partner_id', 'contact_name', 'email', 'phone', 'stage_id',
      'user_id', 'sales_team_id', 'priority', 'probability', 'expected_revenue',
      'recurring_revenue', 'recurring_plan', 'date_deadline', 'description', 'notes', 'tags'
    ];

    for (const field of fieldsToUpdate) {
      if ((dto as any)[field] !== undefined) {
        updateFields.push(`${field} = $${paramIndex++}`);
        values.push((dto as any)[field]);
      }
    }

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

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

    values.push(id, tenantId);

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

    return this.findById(id, tenantId);
  }

  async moveStage(id: string, stageId: string, tenantId: string, userId: string): Promise<Opportunity> {
    const opportunity = await this.findById(id, tenantId);

    if (opportunity.status !== 'open') {
      throw new ValidationError('Solo se pueden mover oportunidades abiertas');
    }

    // Get stage probability
    const stage = await queryOne<{ probability: number; is_won: boolean }>(
      `SELECT probability, is_won FROM crm.opportunity_stages WHERE id = $1 AND tenant_id = $2`,
      [stageId, tenantId]
    );

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

    await query(
      `UPDATE crm.opportunities SET
         stage_id = $1,
         probability = $2,
         date_last_activity = CURRENT_TIMESTAMP,
         updated_by = $3,
         updated_at = CURRENT_TIMESTAMP
       WHERE id = $4 AND tenant_id = $5`,
      [stageId, stage.probability, userId, id, tenantId]
    );

    return this.findById(id, tenantId);
  }

  async markWon(id: string, tenantId: string, userId: string): Promise<Opportunity> {
    const opportunity = await this.findById(id, tenantId);

    if (opportunity.status !== 'open') {
      throw new ValidationError('Solo se pueden marcar como ganadas oportunidades abiertas');
    }

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

    return this.findById(id, tenantId);
  }

  async markLost(id: string, lostReasonId: string, notes: string | undefined, tenantId: string, userId: string): Promise<Opportunity> {
    const opportunity = await this.findById(id, tenantId);

    if (opportunity.status !== 'open') {
      throw new ValidationError('Solo se pueden marcar como perdidas oportunidades abiertas');
    }

    await query(
      `UPDATE crm.opportunities SET
         status = 'lost',
         probability = 0,
         lost_reason_id = $1,
         lost_notes = $2,
         date_closed = CURRENT_TIMESTAMP,
         updated_by = $3,
         updated_at = CURRENT_TIMESTAMP
       WHERE id = $4 AND tenant_id = $5`,
      [lostReasonId, notes, userId, id, tenantId]
    );

    return this.findById(id, tenantId);
  }

  async createQuotation(id: string, tenantId: string, userId: string): Promise<{ opportunity: Opportunity; quotation_id: string }> {
    const opportunity = await this.findById(id, tenantId);

    if (opportunity.status !== 'open') {
      throw new ValidationError('Solo se pueden crear cotizaciones de oportunidades abiertas');
    }

    if (opportunity.quotation_id) {
      throw new ValidationError('Esta oportunidad ya tiene una cotizacion asociada');
    }

    const client = await getClient();

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

      // Generate quotation name
      const seqResult = await client.query(
        `SELECT COALESCE(MAX(CAST(SUBSTRING(name FROM 3) AS INTEGER)), 0) + 1 as next_num
         FROM sales.quotations WHERE tenant_id = $1 AND name LIKE 'SO%'`,
        [tenantId]
      );
      const nextNum = seqResult.rows[0]?.next_num || 1;
      const quotationName = `SO${String(nextNum).padStart(6, '0')}`;

      // Get default currency
      const currencyResult = await client.query(
        `SELECT id FROM core.currencies WHERE code = 'MXN' AND tenant_id = $1 LIMIT 1`,
        [tenantId]
      );
      const currencyId = currencyResult.rows[0]?.id;

      if (!currencyId) {
        throw new ValidationError('No se encontro una moneda configurada');
      }

      // Create quotation
      const quotationResult = await client.query(
        `INSERT INTO sales.quotations (
          tenant_id, company_id, name, partner_id, quotation_date, validity_date,
          currency_id, user_id, notes, created_by
        )
         VALUES ($1, $2, $3, $4, CURRENT_DATE, CURRENT_DATE + INTERVAL '30 days', $5, $6, $7, $8)
         RETURNING id`,
        [
          tenantId, opportunity.company_id, quotationName, opportunity.partner_id,
          currencyId, userId, opportunity.description, userId
        ]
      );
      const quotationId = quotationResult.rows[0].id;

      // Update opportunity
      await client.query(
        `UPDATE crm.opportunities SET
           quotation_id = $1,
           date_last_activity = CURRENT_TIMESTAMP,
           updated_by = $2,
           updated_at = CURRENT_TIMESTAMP
         WHERE id = $3`,
        [quotationId, userId, id]
      );

      await client.query('COMMIT');

      const updatedOpportunity = await this.findById(id, tenantId);

      return { opportunity: updatedOpportunity, quotation_id: quotationId };
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

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

    if (opportunity.quotation_id || opportunity.order_id) {
      throw new ValidationError('No se puede eliminar una oportunidad con cotizacion u orden asociada');
    }

    // Update lead if exists
    if (opportunity.lead_id) {
      await query(
        `UPDATE crm.leads SET opportunity_id = NULL WHERE id = $1`,
        [opportunity.lead_id]
      );
    }

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

  // Pipeline view - grouped by stage
  async getPipeline(tenantId: string, companyId?: string): Promise<{ stages: any[]; totals: any }> {
    let whereClause = 'WHERE o.tenant_id = $1 AND o.status = $2';
    const params: any[] = [tenantId, 'open'];

    if (companyId) {
      whereClause += ` AND o.company_id = $3`;
      params.push(companyId);
    }

    const stages = await query<{ id: string; name: string; sequence: number; probability: number }>(
      `SELECT id, name, sequence, probability
       FROM crm.opportunity_stages
       WHERE tenant_id = $1 AND active = TRUE
       ORDER BY sequence`,
      [tenantId]
    );

    const opportunities = await query<any>(
      `SELECT o.id, o.name, o.partner_id, p.name as partner_name,
              o.stage_id, o.expected_revenue, o.probability, o.priority,
              o.date_deadline, o.user_id
       FROM crm.opportunities o
       LEFT JOIN core.partners p ON o.partner_id = p.id
       ${whereClause}
       ORDER BY o.priority DESC, o.expected_revenue DESC`,
      params
    );

    // Group opportunities by stage
    const pipelineStages = stages.map(stage => ({
      ...stage,
      opportunities: opportunities.filter((opp: any) => opp.stage_id === stage.id),
      count: opportunities.filter((opp: any) => opp.stage_id === stage.id).length,
      total_revenue: opportunities
        .filter((opp: any) => opp.stage_id === stage.id)
        .reduce((sum: number, opp: any) => sum + (parseFloat(opp.expected_revenue) || 0), 0)
    }));

    // Add "No stage" for opportunities without stage
    const noStageOpps = opportunities.filter((opp: any) => !opp.stage_id);
    if (noStageOpps.length > 0) {
      pipelineStages.unshift({
        id: null as unknown as string,
        name: 'Sin etapa',
        sequence: 0,
        probability: 0,
        opportunities: noStageOpps,
        count: noStageOpps.length,
        total_revenue: noStageOpps.reduce((sum: number, opp: any) => sum + (parseFloat(opp.expected_revenue) || 0), 0)
      });
    }

    const totals = {
      total_opportunities: opportunities.length,
      total_expected_revenue: opportunities.reduce((sum: number, opp: any) => sum + (parseFloat(opp.expected_revenue) || 0), 0),
      weighted_revenue: opportunities.reduce((sum: number, opp: any) => {
        const revenue = parseFloat(opp.expected_revenue) || 0;
        const probability = parseFloat(opp.probability) || 0;
        return sum + (revenue * probability / 100);
      }, 0)
    };

    return { stages: pipelineStages, totals };
  }
}

export const opportunitiesService = new OpportunitiesService();
