/**
 * CRM Flow Integration Tests
 *
 * Tests the complete CRM workflows:
 * 1. Lead -> Qualification -> Conversion to Opportunity
 * 2. Opportunity -> Pipeline Stage Progression
 * 3. Opportunity Won -> Partner Creation
 * 4. Opportunity Lost -> Reason Registration
 * 5. Activities and Follow-up Tracking
 */

import { jest, describe, it, expect, beforeEach, afterEach } from '@jest/globals';
import {
  createMockLead,
  createMockOpportunity,
  createMockStage,
  createMockLostReason,
  createMockPartner,
} from '../../../__tests__/helpers.js';

// Mock query functions
const mockQuery = jest.fn();
const mockQueryOne = jest.fn();
const mockGetClient = jest.fn();

jest.mock('../../../config/database.js', () => ({
  query: (...args: any[]) => mockQuery(...args),
  queryOne: (...args: any[]) => mockQueryOne(...args),
  getClient: () => mockGetClient(),
}));

// Mock logger
jest.mock('../../../shared/utils/logger.js', () => ({
  logger: {
    info: jest.fn(),
    error: jest.fn(),
    warn: jest.fn(),
    debug: jest.fn(),
  },
}));

// Import services after mocking
import { leadsService } from '../leads.service.js';
import { opportunitiesService } from '../opportunities.service.js';
import { stagesService } from '../stages.service.js';
import { activitiesService } from '../activities.service.js';
import { NotFoundError, ValidationError, ConflictError } from '../../../shared/errors/index.js';

describe('CRM Flow Integration Tests', () => {
  const tenantId = 'test-tenant-uuid';
  const userId = 'test-user-uuid';
  const companyId = 'company-uuid-1';

  const mockClient = {
    query: jest.fn(),
    release: jest.fn(),
  };

  beforeEach(() => {
    jest.clearAllMocks();
    mockGetClient.mockResolvedValue(mockClient);
  });

  afterEach(() => {
    jest.resetAllMocks();
  });

  describe('Flow 1: Lead -> Qualification -> Conversion to Opportunity', () => {
    const leadStages = [
      createMockStage({ id: 'stage-new', name: 'New', sequence: 1, probability: 10 }),
      createMockStage({ id: 'stage-contacted', name: 'Contacted', sequence: 2, probability: 25 }),
      createMockStage({ id: 'stage-qualified', name: 'Qualified', sequence: 3, probability: 50 }),
    ];

    const opportunityStages = [
      createMockStage({ id: 'opp-stage-1', name: 'Qualification', sequence: 1, probability: 20 }),
      createMockStage({ id: 'opp-stage-2', name: 'Proposal', sequence: 2, probability: 50 }),
      createMockStage({ id: 'opp-stage-3', name: 'Negotiation', sequence: 3, probability: 75 }),
      createMockStage({ id: 'opp-stage-4', name: 'Won', sequence: 4, probability: 100, is_won: true }),
    ];

    it('should create a new lead successfully', async () => {
      const createDto = {
        company_id: companyId,
        name: 'New Business Opportunity',
        contact_name: 'John Smith',
        email: 'john.smith@prospect.com',
        phone: '+1234567890',
        source: 'website' as const,
        expected_revenue: 50000,
      };

      const createdLead = createMockLead({
        ...createDto,
        id: 'new-lead-uuid',
        status: 'new',
        stage_id: 'stage-new',
      });

      mockQueryOne
        .mockResolvedValueOnce(createdLead) // INSERT
        .mockResolvedValueOnce(createdLead); // findById

      const result = await leadsService.create(createDto, tenantId, userId);

      expect(result.name).toBe(createDto.name);
      expect(result.email).toBe(createDto.email);
      expect(result.source).toBe('website');
    });

    it('should move lead through stages: New -> Contacted -> Qualified', async () => {
      // Stage 1: New lead
      const newLead = createMockLead({
        id: 'lead-uuid-1',
        status: 'new',
        stage_id: 'stage-new',
        email: 'contact@test.com',
      });

      // Move to Contacted
      mockQueryOne.mockResolvedValueOnce(newLead); // findById for moveStage
      mockQuery.mockResolvedValueOnce([]); // UPDATE

      const contactedLead = createMockLead({
        ...newLead,
        stage_id: 'stage-contacted',
        status: 'contacted',
      });
      mockQueryOne.mockResolvedValueOnce(contactedLead); // findById after move

      const afterContact = await leadsService.moveStage('lead-uuid-1', 'stage-contacted', tenantId, userId);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE crm.leads SET'),
        expect.arrayContaining(['stage-contacted'])
      );

      // Move to Qualified
      mockQueryOne.mockResolvedValueOnce(contactedLead); // findById for moveStage
      mockQuery.mockResolvedValueOnce([]); // UPDATE

      const qualifiedLead = createMockLead({
        ...contactedLead,
        stage_id: 'stage-qualified',
        status: 'qualified',
      });
      mockQueryOne.mockResolvedValueOnce(qualifiedLead); // findById after move

      const afterQualify = await leadsService.moveStage('lead-uuid-1', 'stage-qualified', tenantId, userId);

      expect(afterQualify.stage_id).toBe('stage-qualified');
    });

    it('should convert qualified lead to opportunity with partner creation', async () => {
      const qualifiedLead = createMockLead({
        id: 'lead-uuid-1',
        status: 'qualified',
        email: 'john@company.com',
        contact_name: 'John Doe',
        phone: '+1234567890',
        expected_revenue: 50000,
        company_id: companyId,
        partner_id: null,
      });

      mockQueryOne.mockResolvedValue(qualifiedLead);

      mockClient.query
        .mockResolvedValueOnce(undefined) // BEGIN
        .mockResolvedValueOnce({ rows: [] }) // Check existing partner - none found
        .mockResolvedValueOnce({ rows: [{ id: 'new-partner-uuid' }] }) // Create new partner
        .mockResolvedValueOnce({ rows: [{ id: 'opp-stage-1' }] }) // Get default opportunity stage
        .mockResolvedValueOnce({ rows: [{ id: 'new-opportunity-uuid' }] }) // Create opportunity
        .mockResolvedValueOnce(undefined) // Update lead status
        .mockResolvedValueOnce(undefined); // COMMIT

      const result = await leadsService.convert('lead-uuid-1', tenantId, userId);

      expect(result.opportunity_id).toBe('new-opportunity-uuid');
      expect(mockClient.query).toHaveBeenCalledWith('BEGIN');
      expect(mockClient.query).toHaveBeenCalledWith('COMMIT');

      // Verify partner was created
      expect(mockClient.query).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO core.partners'),
        expect.arrayContaining([tenantId, qualifiedLead.contact_name, qualifiedLead.email])
      );
    });

    it('should use existing partner when converting lead with matching email', async () => {
      const qualifiedLead = createMockLead({
        id: 'lead-uuid-1',
        status: 'qualified',
        email: 'existing@company.com',
        contact_name: 'Existing Contact',
        partner_id: null,
      });

      mockQueryOne.mockResolvedValue(qualifiedLead);

      mockClient.query
        .mockResolvedValueOnce(undefined) // BEGIN
        .mockResolvedValueOnce({ rows: [{ id: 'existing-partner-uuid' }] }) // Found existing partner
        .mockResolvedValueOnce({ rows: [{ id: 'opp-stage-1' }] }) // Get default stage
        .mockResolvedValueOnce({ rows: [{ id: 'new-opportunity-uuid' }] }) // Create opportunity
        .mockResolvedValueOnce(undefined) // Update lead
        .mockResolvedValueOnce(undefined); // COMMIT

      const result = await leadsService.convert('lead-uuid-1', tenantId, userId);

      expect(result.opportunity_id).toBe('new-opportunity-uuid');

      // Verify no new partner was created (only 6 queries, not 7)
      const queryCalls = mockClient.query.mock.calls;
      const insertPartnerCalls = queryCalls.filter((call: any) =>
        call[0]?.includes?.('INSERT INTO core.partners')
      );
      expect(insertPartnerCalls).toHaveLength(0);
    });

    it('should reject conversion of already converted lead', async () => {
      const convertedLead = createMockLead({
        id: 'lead-uuid-1',
        status: 'converted',
        opportunity_id: 'existing-opportunity',
      });

      mockQueryOne.mockResolvedValue(convertedLead);

      await expect(
        leadsService.convert('lead-uuid-1', tenantId, userId)
      ).rejects.toThrow(ValidationError);
    });

    it('should reject conversion of lost lead', async () => {
      const lostLead = createMockLead({
        id: 'lead-uuid-1',
        status: 'lost',
        lost_reason_id: 'reason-uuid',
      });

      mockQueryOne.mockResolvedValue(lostLead);

      await expect(
        leadsService.convert('lead-uuid-1', tenantId, userId)
      ).rejects.toThrow(ValidationError);
    });
  });

  describe('Flow 2: Opportunity -> Pipeline Stage Progression', () => {
    const pipelineStages = [
      createMockStage({ id: 'stage-qualification', name: 'Qualification', sequence: 1, probability: 20 }),
      createMockStage({ id: 'stage-proposal', name: 'Proposal', sequence: 2, probability: 50 }),
      createMockStage({ id: 'stage-negotiation', name: 'Negotiation', sequence: 3, probability: 75 }),
      createMockStage({ id: 'stage-closed-won', name: 'Closed Won', sequence: 4, probability: 100, is_won: true }),
    ];

    it('should move opportunity through all pipeline stages', async () => {
      // Start with opportunity in Qualification stage
      let currentOpp = createMockOpportunity({
        id: 'opp-uuid-1',
        status: 'open',
        stage_id: 'stage-qualification',
        probability: 20,
      });

      // Move to Proposal
      mockQueryOne
        .mockResolvedValueOnce(currentOpp) // findById
        .mockResolvedValueOnce(pipelineStages[1]); // get stage info
      mockQuery.mockResolvedValueOnce([]);

      currentOpp = createMockOpportunity({
        ...currentOpp,
        stage_id: 'stage-proposal',
        probability: 50,
      });
      mockQueryOne.mockResolvedValueOnce(currentOpp);

      const afterProposal = await opportunitiesService.moveStage(
        'opp-uuid-1',
        'stage-proposal',
        tenantId,
        userId
      );

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE crm.opportunities'),
        expect.arrayContaining(['stage-proposal', 50])
      );

      // Move to Negotiation
      mockQueryOne
        .mockResolvedValueOnce(currentOpp)
        .mockResolvedValueOnce(pipelineStages[2]);
      mockQuery.mockResolvedValueOnce([]);

      currentOpp = createMockOpportunity({
        ...currentOpp,
        stage_id: 'stage-negotiation',
        probability: 75,
      });
      mockQueryOne.mockResolvedValueOnce(currentOpp);

      const afterNegotiation = await opportunitiesService.moveStage(
        'opp-uuid-1',
        'stage-negotiation',
        tenantId,
        userId
      );

      expect(afterNegotiation.probability).toBe(75);
    });

    it('should update probability when moving to different stages', async () => {
      const opportunity = createMockOpportunity({
        id: 'opp-uuid-1',
        status: 'open',
        stage_id: 'stage-qualification',
        probability: 20,
      });

      const proposalStage = createMockStage({
        id: 'stage-proposal',
        name: 'Proposal',
        probability: 50,
      });

      mockQueryOne
        .mockResolvedValueOnce(opportunity)
        .mockResolvedValueOnce(proposalStage);
      mockQuery.mockResolvedValueOnce([]);

      const updatedOpp = createMockOpportunity({
        ...opportunity,
        stage_id: 'stage-proposal',
        probability: 50,
      });
      mockQueryOne.mockResolvedValueOnce(updatedOpp);

      await opportunitiesService.moveStage('opp-uuid-1', 'stage-proposal', tenantId, userId);

      // Verify UPDATE query includes new probability
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('probability = $2'),
        expect.arrayContaining(['stage-proposal', 50])
      );
    });

    it('should not allow moving closed opportunity', async () => {
      const wonOpportunity = createMockOpportunity({
        id: 'opp-uuid-1',
        status: 'won',
        stage_id: 'stage-closed-won',
      });

      mockQueryOne.mockResolvedValue(wonOpportunity);

      await expect(
        opportunitiesService.moveStage('opp-uuid-1', 'stage-proposal', tenantId, userId)
      ).rejects.toThrow(ValidationError);
    });

    it('should return full pipeline view grouped by stages', async () => {
      const stages = [
        createMockStage({ id: 'stage-1', name: 'Qualification', sequence: 1 }),
        createMockStage({ id: 'stage-2', name: 'Proposal', sequence: 2 }),
      ];

      const opportunities = [
        createMockOpportunity({ id: 'opp-1', stage_id: 'stage-1', expected_revenue: 10000 }),
        createMockOpportunity({ id: 'opp-2', stage_id: 'stage-1', expected_revenue: 15000 }),
        createMockOpportunity({ id: 'opp-3', stage_id: 'stage-2', expected_revenue: 25000 }),
      ];

      mockQuery
        .mockResolvedValueOnce(stages)
        .mockResolvedValueOnce(opportunities);

      const result = await opportunitiesService.getPipeline(tenantId);

      expect(result.stages).toHaveLength(2);
      expect(result.stages[0].count).toBe(2);
      expect(result.stages[0].total_revenue).toBe(25000);
      expect(result.stages[1].count).toBe(1);
      expect(result.totals.total_opportunities).toBe(3);
      expect(result.totals.total_expected_revenue).toBe(50000);
    });
  });

  describe('Flow 3: Opportunity Won -> Partner/Customer Confirmation', () => {
    it('should mark opportunity as won with 100% probability', async () => {
      const opportunity = createMockOpportunity({
        id: 'opp-uuid-1',
        status: 'open',
        probability: 75,
        expected_revenue: 50000,
        partner_id: 'partner-uuid-1',
      });

      const wonOpp = createMockOpportunity({
        ...opportunity,
        status: 'won',
        probability: 100,
        date_closed: new Date(),
      });

      mockQueryOne
        .mockResolvedValueOnce(opportunity) // findById before update
        .mockResolvedValueOnce(wonOpp); // findById after update
      mockQuery.mockResolvedValueOnce([]);

      const result = await opportunitiesService.markWon('opp-uuid-1', tenantId, userId);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining("status = 'won'"),
        expect.arrayContaining([userId, 'opp-uuid-1', tenantId])
      );
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('probability = 100'),
        expect.any(Array)
      );
    });

    it('should create quotation from won opportunity', async () => {
      const wonOpportunity = createMockOpportunity({
        id: 'opp-uuid-1',
        status: 'open', // Must be open to create quotation
        partner_id: 'partner-uuid-1',
        company_id: companyId,
        expected_revenue: 50000,
        quotation_id: null,
      });

      mockQueryOne.mockResolvedValue(wonOpportunity);

      mockClient.query
        .mockResolvedValueOnce(undefined) // BEGIN
        .mockResolvedValueOnce({ rows: [{ next_num: 1 }] }) // Sequence for quotation name
        .mockResolvedValueOnce({ rows: [{ id: 'currency-mxn' }] }) // Get currency
        .mockResolvedValueOnce({ rows: [{ id: 'quotation-uuid-1' }] }) // INSERT quotation
        .mockResolvedValueOnce(undefined) // UPDATE opportunity
        .mockResolvedValueOnce(undefined); // COMMIT

      const result = await opportunitiesService.createQuotation('opp-uuid-1', tenantId, userId);

      expect(result.quotation_id).toBe('quotation-uuid-1');
      expect(mockClient.query).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO sales.quotations'),
        expect.any(Array)
      );
    });

    it('should not create duplicate quotation for opportunity', async () => {
      const opportunityWithQuotation = createMockOpportunity({
        id: 'opp-uuid-1',
        status: 'open',
        quotation_id: 'existing-quotation-uuid',
      });

      mockQueryOne.mockResolvedValue(opportunityWithQuotation);

      await expect(
        opportunitiesService.createQuotation('opp-uuid-1', tenantId, userId)
      ).rejects.toThrow(ValidationError);
    });
  });

  describe('Flow 4: Opportunity Lost -> Reason Registration', () => {
    it('should mark opportunity as lost with reason', async () => {
      const opportunity = createMockOpportunity({
        id: 'opp-uuid-1',
        status: 'open',
        probability: 50,
      });

      const lostOpp = createMockOpportunity({
        ...opportunity,
        status: 'lost',
        probability: 0,
        lost_reason_id: 'reason-uuid-1',
        lost_notes: 'Customer went with cheaper competitor',
        date_closed: new Date(),
      });

      mockQueryOne
        .mockResolvedValueOnce(opportunity) // findById before update
        .mockResolvedValueOnce(lostOpp); // findById after update
      mockQuery.mockResolvedValueOnce([]);

      const result = await opportunitiesService.markLost(
        'opp-uuid-1',
        'reason-uuid-1',
        'Customer went with cheaper competitor',
        tenantId,
        userId
      );

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining("status = 'lost'"),
        expect.any(Array)
      );
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('lost_reason_id = $1'),
        expect.arrayContaining(['reason-uuid-1'])
      );
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('probability = 0'),
        expect.any(Array)
      );
    });

    it('should mark lead as lost with reason', async () => {
      const lead = createMockLead({
        id: 'lead-uuid-1',
        status: 'qualified',
      });

      const lostLead = createMockLead({
        ...lead,
        status: 'lost',
        lost_reason_id: 'reason-uuid-1',
        lost_notes: 'No budget',
      });

      mockQueryOne
        .mockResolvedValueOnce(lead) // findById before update
        .mockResolvedValueOnce(lostLead); // findById after update
      mockQuery.mockResolvedValueOnce([]);

      await leadsService.markLost(
        'lead-uuid-1',
        'reason-uuid-1',
        'No budget',
        tenantId,
        userId
      );

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining("status = 'lost'"),
        expect.any(Array)
      );
    });

    it('should not allow marking already lost opportunity as lost again', async () => {
      const lostOpportunity = createMockOpportunity({
        id: 'opp-uuid-1',
        status: 'lost',
        lost_reason_id: 'reason-uuid-1',
      });

      mockQueryOne.mockResolvedValueOnce(lostOpportunity);

      await expect(
        opportunitiesService.markLost('opp-uuid-1', 'reason-uuid-2', 'New notes', tenantId, userId)
      ).rejects.toThrow(ValidationError);
    });

    it('should manage lost reasons correctly', async () => {
      // Create lost reason
      const newReason = createMockLostReason({ name: 'Competitor won' });
      mockQueryOne
        .mockResolvedValueOnce(null) // unique check
        .mockResolvedValueOnce(newReason);

      const created = await stagesService.createLostReason(
        { name: 'Competitor won', description: 'Lost to a competitor' },
        tenantId
      );

      expect(created.name).toBe('Competitor won');

      // List lost reasons
      const reasons = [
        createMockLostReason({ id: '1', name: 'Price' }),
        createMockLostReason({ id: '2', name: 'Competitor' }),
        createMockLostReason({ id: '3', name: 'No budget' }),
      ];
      mockQuery.mockResolvedValue(reasons);

      const allReasons = await stagesService.getLostReasons(tenantId);
      expect(allReasons).toHaveLength(3);
    });
  });

  describe('Flow 5: Activities and Follow-up Tracking', () => {
    it('should create activity linked to lead', async () => {
      const createDto = {
        company_id: companyId,
        activity_type: 'call' as const,
        name: 'Initial call with prospect',
        res_model: 'lead',
        res_id: 'lead-uuid-1',
        scheduled_date: new Date().toISOString(),
        user_id: userId,
      };

      const createdActivity = {
        id: 'activity-uuid-1',
        tenant_id: tenantId,
        ...createDto,
        status: 'scheduled',
        priority: 1,
        created_at: new Date(),
      };

      mockQueryOne
        .mockResolvedValueOnce(createdActivity) // INSERT
        .mockResolvedValueOnce(createdActivity); // findById
      mockQuery.mockResolvedValueOnce([]); // Update last activity date

      const result = await activitiesService.create(createDto, tenantId, userId);

      expect(result.activity_type).toBe('call');
      expect(result.res_model).toBe('lead');
      expect(result.res_id).toBe('lead-uuid-1');
    });

    it('should create activity linked to opportunity', async () => {
      const createDto = {
        company_id: companyId,
        activity_type: 'meeting' as const,
        name: 'Product demo meeting',
        res_model: 'opportunity',
        res_id: 'opp-uuid-1',
        scheduled_date: new Date().toISOString(),
        duration_hours: 2,
        user_id: userId,
      };

      const createdActivity = {
        id: 'activity-uuid-2',
        tenant_id: tenantId,
        ...createDto,
        status: 'scheduled',
        priority: 1,
        created_at: new Date(),
      };

      mockQueryOne
        .mockResolvedValueOnce(createdActivity)
        .mockResolvedValueOnce(createdActivity);
      mockQuery.mockResolvedValueOnce([]);

      const result = await activitiesService.create(createDto, tenantId, userId);

      expect(result.activity_type).toBe('meeting');
      expect(result.res_model).toBe('opportunity');
    });

    it('should mark activity as done and update last activity date', async () => {
      // Activity without res_model to avoid getResourceName complexity
      const scheduledActivity = {
        id: 'activity-uuid-1',
        tenant_id: tenantId,
        activity_type: 'call',
        name: 'Follow-up call',
        status: 'scheduled',
        res_model: 'opportunity',
        res_id: 'opp-uuid-1',
        priority: 1,
        created_at: new Date(),
      };

      const doneActivity = {
        ...scheduledActivity,
        status: 'done',
        date_done: new Date(),
        notes: 'Discussed pricing options',
      };

      // findById calls getResourceName when res_model is set
      mockQueryOne
        .mockResolvedValueOnce(scheduledActivity) // findById before update
        .mockResolvedValueOnce({ name: 'Test Opportunity' }) // getResourceName for scheduledActivity
        .mockResolvedValueOnce(doneActivity) // findById after update
        .mockResolvedValueOnce({ name: 'Test Opportunity' }); // getResourceName for doneActivity
      mockQuery.mockResolvedValueOnce([]); // UPDATE activity
      mockQuery.mockResolvedValueOnce([]); // UPDATE opportunity last_activity_date

      const result = await activitiesService.markDone(
        'activity-uuid-1',
        tenantId,
        userId,
        'Discussed pricing options'
      );

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining("status = 'done'"),
        expect.any(Array)
      );
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE crm.opportunities SET date_last_activity'),
        expect.any(Array)
      );
    });

    it('should get all activities for a specific opportunity', async () => {
      const activities = [
        { id: '1', name: 'Initial call', activity_type: 'call', status: 'done' },
        { id: '2', name: 'Demo meeting', activity_type: 'meeting', status: 'done' },
        { id: '3', name: 'Follow-up', activity_type: 'task', status: 'scheduled' },
      ];

      mockQuery.mockResolvedValue(activities);

      const result = await activitiesService.getResourceActivities(
        'opportunity',
        'opp-uuid-1',
        tenantId
      );

      expect(result).toHaveLength(3);
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('a.res_model = $1'),
        expect.arrayContaining(['opportunity', 'opp-uuid-1', tenantId])
      );
    });

    it('should schedule follow-up activity after completing one', async () => {
      // Simplified activity without res_model to avoid getResourceName calls
      const completedActivity = {
        id: 'activity-uuid-1',
        tenant_id: tenantId,
        company_id: companyId,
        activity_type: 'call',
        name: 'Initial call',
        status: 'done',
        res_model: null, // No res_model to avoid getResourceName
        res_id: null,
        partner_id: 'partner-uuid-1',
        priority: 1,
        created_at: new Date(),
      };

      const followUpDto = {
        company_id: companyId,
        activity_type: 'meeting' as const,
        name: 'Demo meeting follow-up',
        scheduled_date: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000).toISOString(),
        res_model: 'opportunity',
        res_id: 'opp-uuid-1',
      };

      const newFollowUp = {
        id: 'activity-uuid-2',
        tenant_id: tenantId,
        ...followUpDto,
        res_model: 'opportunity',
        res_id: 'opp-uuid-1',
        partner_id: 'partner-uuid-1',
        status: 'scheduled',
        priority: 1,
        created_at: new Date(),
      };

      // Mock sequence:
      // 1. findById(completed) - no getResourceName since res_model is null
      // 2. INSERT new activity
      // 3. findById(new) - has res_model, calls getResourceName
      mockQueryOne
        .mockResolvedValueOnce(completedActivity) // findById for completed (no getResourceName)
        .mockResolvedValueOnce(newFollowUp) // INSERT new activity
        .mockResolvedValueOnce(newFollowUp) // findById for new activity
        .mockResolvedValueOnce({ name: 'Test Opportunity' }); // getResourceName for new activity
      mockQuery.mockResolvedValueOnce([]); // Update last activity date

      const result = await activitiesService.scheduleFollowUp(
        'activity-uuid-1',
        followUpDto,
        tenantId,
        userId
      );

      expect(result.res_model).toBe('opportunity');
      expect(result.res_id).toBe('opp-uuid-1');
      expect(result.partner_id).toBe('partner-uuid-1');
    });

    it('should get activity summary for dashboard', async () => {
      mockQueryOne.mockResolvedValue({
        total: '10',
        scheduled: '5',
        done: '4',
        cancelled: '1',
        overdue: '2',
      });

      mockQuery.mockResolvedValue([
        { activity_type: 'call', count: '4' },
        { activity_type: 'meeting', count: '3' },
        { activity_type: 'email', count: '2' },
        { activity_type: 'task', count: '1' },
      ]);

      const summary = await activitiesService.getActivitySummary(tenantId, userId);

      expect(summary.total_activities).toBe(10);
      expect(summary.scheduled).toBe(5);
      expect(summary.done).toBe(4);
      expect(summary.overdue).toBe(2);
      expect(summary.by_type.call).toBe(4);
      expect(summary.by_type.meeting).toBe(3);
    });

    it('should cancel scheduled activity', async () => {
      // No res_model to simplify mock
      const scheduledActivity = {
        id: 'activity-uuid-1',
        status: 'scheduled',
        res_model: null,
        res_id: null,
      };

      const cancelledActivity = {
        ...scheduledActivity,
        status: 'cancelled',
      };

      mockQueryOne
        .mockResolvedValueOnce(scheduledActivity) // findById before cancel (no getResourceName)
        .mockResolvedValueOnce(cancelledActivity); // findById after cancel (no getResourceName)
      mockQuery.mockResolvedValueOnce([]);

      const result = await activitiesService.cancel('activity-uuid-1', tenantId, userId);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining("status = 'cancelled'"),
        expect.any(Array)
      );
    });

    it('should not allow editing completed activity', async () => {
      const doneActivity = {
        id: 'activity-uuid-1',
        status: 'done',
        date_done: new Date(),
      };

      mockQueryOne.mockResolvedValue(doneActivity);

      await expect(
        activitiesService.update('activity-uuid-1', { name: 'Updated name' }, tenantId, userId)
      ).rejects.toThrow(ValidationError);
    });

    it('should get overdue activities count', async () => {
      mockQueryOne.mockResolvedValue({ count: '5' });

      const overdueCount = await activitiesService.getOverdueCount(tenantId, userId);

      expect(overdueCount).toBe(5);
      expect(mockQueryOne).toHaveBeenCalledWith(
        expect.stringContaining('scheduled_date < CURRENT_DATE'),
        expect.any(Array)
      );
    });
  });

  describe('Complete CRM Lifecycle Flow', () => {
    it('should complete full lead-to-won flow', async () => {
      // Step 1: Create Lead
      const newLead = createMockLead({
        id: 'lifecycle-lead-1',
        name: 'Enterprise Deal',
        email: 'enterprise@bigcorp.com',
        status: 'new',
        expected_revenue: 100000,
      });

      mockQueryOne
        .mockResolvedValueOnce(newLead)
        .mockResolvedValueOnce(newLead);

      const createdLead = await leadsService.create(
        { company_id: companyId, name: 'Enterprise Deal', email: 'enterprise@bigcorp.com' },
        tenantId,
        userId
      );
      expect(createdLead.status).toBe('new');

      // Step 2: Qualify Lead
      mockQueryOne.mockResolvedValueOnce(newLead);
      mockQuery.mockResolvedValueOnce([]);
      const qualifiedLead = createMockLead({ ...newLead, status: 'qualified' });
      mockQueryOne.mockResolvedValueOnce(qualifiedLead);

      // Step 3: Convert to Opportunity
      mockQueryOne.mockResolvedValue(qualifiedLead);
      mockClient.query
        .mockResolvedValueOnce(undefined) // BEGIN
        .mockResolvedValueOnce({ rows: [] }) // No existing partner
        .mockResolvedValueOnce({ rows: [{ id: 'new-partner' }] }) // Create partner
        .mockResolvedValueOnce({ rows: [{ id: 'first-stage' }] }) // Get stage
        .mockResolvedValueOnce({ rows: [{ id: 'new-opp' }] }) // Create opportunity
        .mockResolvedValueOnce(undefined) // Update lead
        .mockResolvedValueOnce(undefined); // COMMIT

      const conversionResult = await leadsService.convert('lifecycle-lead-1', tenantId, userId);
      expect(conversionResult.opportunity_id).toBe('new-opp');

      // Step 4: Progress Opportunity through pipeline
      const openOpp = createMockOpportunity({
        id: 'new-opp',
        status: 'open',
        lead_id: 'lifecycle-lead-1',
        partner_id: 'new-partner',
        stage_id: 'first-stage',
        probability: 20,
      });

      const proposalStage = createMockStage({ id: 'proposal-stage', probability: 50 });

      mockQueryOne
        .mockResolvedValueOnce(openOpp)
        .mockResolvedValueOnce(proposalStage);
      mockQuery.mockResolvedValueOnce([]);

      const movedOpp = createMockOpportunity({ ...openOpp, stage_id: 'proposal-stage', probability: 50 });
      mockQueryOne.mockResolvedValueOnce(movedOpp);

      // Step 5: Mark as Won
      mockQueryOne.mockResolvedValue(movedOpp);
      mockQuery.mockResolvedValueOnce([]);

      const wonOpp = createMockOpportunity({
        ...movedOpp,
        status: 'won',
        probability: 100,
        date_closed: new Date(),
      });
      mockQueryOne.mockResolvedValue(wonOpp);

      const finalResult = await opportunitiesService.markWon('new-opp', tenantId, userId);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining("status = 'won'"),
        expect.any(Array)
      );
    });
  });

  describe('Edge Cases and Error Handling', () => {
    it('should handle database transaction failure during lead conversion', async () => {
      const lead = createMockLead({ status: 'qualified', email: 'test@test.com' });
      mockQueryOne.mockResolvedValue(lead);

      mockClient.query
        .mockResolvedValueOnce(undefined) // BEGIN
        .mockRejectedValueOnce(new Error('Database connection lost'));

      await expect(
        leadsService.convert('lead-uuid-1', tenantId, userId)
      ).rejects.toThrow('Database connection lost');

      expect(mockClient.query).toHaveBeenCalledWith('ROLLBACK');
      expect(mockClient.release).toHaveBeenCalled();
    });

    it('should handle database transaction failure during quotation creation', async () => {
      const opp = createMockOpportunity({ status: 'open', quotation_id: null });
      mockQueryOne.mockResolvedValue(opp);

      mockClient.query
        .mockResolvedValueOnce(undefined) // BEGIN
        .mockRejectedValueOnce(new Error('Sequence generation failed'));

      await expect(
        opportunitiesService.createQuotation('opp-uuid-1', tenantId, userId)
      ).rejects.toThrow('Sequence generation failed');

      expect(mockClient.query).toHaveBeenCalledWith('ROLLBACK');
    });

    it('should prevent deletion of lead with associated opportunity', async () => {
      const lead = createMockLead({
        id: 'lead-uuid-1',
        opportunity_id: 'opp-uuid-1',
      });

      mockQueryOne.mockResolvedValue(lead);

      await expect(
        leadsService.delete('lead-uuid-1', tenantId)
      ).rejects.toThrow(ConflictError);
    });

    it('should prevent deletion of opportunity with quotation', async () => {
      const opp = createMockOpportunity({
        quotation_id: 'quotation-uuid-1',
      });

      mockQueryOne.mockResolvedValue(opp);

      await expect(
        opportunitiesService.delete('opp-uuid-1', tenantId)
      ).rejects.toThrow(ValidationError);
    });

    it('should prevent deletion of stage with associated leads', async () => {
      const stage = createMockStage({ id: 'stage-uuid-1' });
      mockQueryOne
        .mockResolvedValueOnce(stage)
        .mockResolvedValueOnce({ count: '10' }); // 10 leads using this stage

      await expect(
        stagesService.deleteLeadStage('stage-uuid-1', tenantId)
      ).rejects.toThrow(ConflictError);
    });

    it('should prevent deletion of lost reason in use', async () => {
      const reason = createMockLostReason({ id: 'reason-uuid-1' });
      mockQueryOne
        .mockResolvedValueOnce(reason)
        .mockResolvedValueOnce({ count: '5' }); // Used by 5 leads

      await expect(
        stagesService.deleteLostReason('reason-uuid-1', tenantId)
      ).rejects.toThrow(ConflictError);
    });
  });
});
