import { Injectable, Logger, BadRequestException, NotFoundException } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';

export interface DuplicateGroup {
  normalized_name: string;
  companies: DuplicateCompany[];
}

export interface DuplicateCompany {
  id: number;
  name: string;
  normalized_name: string;
  company_code: string;
  contact_person: string;
  email: string;
  mobile: string;
  city: string;
  client_group: string;
  created_at: string;
  audit_request_count: number;
  branch_count: number;
  portal_user_count: number;
  audit_count: number;
  inquiry_count: number;
}

export interface MergeResult {
  kept_id: number;
  removed_id: number;
  removed_name: string;
  moved: {
    audit_requests: number;
    branches: number;
    portal_users: number;
    audits: number;
    inquiries: number;
    client_links: number;
    change_logs: number;
  };
}

@Injectable()
export class CompanyMergeService {
  private readonly logger = new Logger('CompanyMergeService');

  constructor(
    @InjectDataSource('scheme_dbs')
    private readonly dataSource: DataSource,
  ) {}

  /**
   * Find all duplicate company groups based on normalized_name.
   * Returns groups with 2+ companies sharing the same normalized name.
   */
  async findDuplicateGroups(): Promise<DuplicateGroup[]> {
    // Step 1: Find normalized_names with duplicates
    const dupeNames: { normalized_name: string; cnt: number }[] =
      await this.dataSource.query(`
        SELECT normalized_name, COUNT(*) as cnt
        FROM companies
        WHERE normalized_name IS NOT NULL AND normalized_name != ''
        GROUP BY normalized_name
        HAVING cnt > 1
        ORDER BY cnt DESC, normalized_name
      `);

    if (dupeNames.length === 0) return [];

    // Step 2: For each group, fetch company details with counts
    const groups: DuplicateGroup[] = [];

    for (const dn of dupeNames) {
      const companies: any[] = await this.dataSource.query(
        `SELECT 
          c.id, c.name, c.normalized_name, c.company_code,
          c.contact_person, c.email, c.mobile, c.city,
          c.client_group, c.created_at,
          (SELECT COUNT(*) FROM audit_requests ar WHERE ar.company_id = c.id) as audit_request_count,
          (SELECT COUNT(*) FROM company_branches cb WHERE cb.company_id = c.id) as branch_count,
          (SELECT COUNT(*) FROM company_users cu WHERE cu.company_id = c.id) as portal_user_count,
          (SELECT COUNT(*) FROM company_audits ca WHERE ca.company_id = c.id) as audit_count,
          (SELECT COUNT(*) FROM inquiries inq WHERE inq.company_id = c.id) as inquiry_count
        FROM companies c
        WHERE c.normalized_name = ?
        ORDER BY c.id ASC`,
        [dn.normalized_name],
      );

      groups.push({
        normalized_name: dn.normalized_name,
        companies: companies.map((c) => ({
          ...c,
          audit_request_count: Number(c.audit_request_count),
          branch_count: Number(c.branch_count),
          portal_user_count: Number(c.portal_user_count),
          audit_count: Number(c.audit_count),
          inquiry_count: Number(c.inquiry_count),
        })),
      });
    }

    return groups;
  }

  /**
   * Merge two companies — keep one, move everything from the other.
   * @param keepId - The company to keep (survivor)
   * @param removeId - The company to merge into the keeper (will be soft-deleted)
   * @param userId - Admin user performing the merge
   */
  async mergeCompanies(
    keepId: number,
    removeId: number,
    userId: number,
  ): Promise<MergeResult> {
    if (keepId === removeId) {
      throw new BadRequestException('Cannot merge a company with itself.');
    }

    return await this.dataSource.transaction(async (manager) => {
      // Verify both companies exist
      const [keeper, removed] = await Promise.all([
        manager.query('SELECT id, name, normalized_name FROM companies WHERE id = ?', [keepId]),
        manager.query('SELECT id, name, normalized_name FROM companies WHERE id = ?', [removeId]),
      ]);

      if (!keeper.length) throw new NotFoundException(`Company ${keepId} not found`);
      if (!removed.length) throw new NotFoundException(`Company ${removeId} not found`);

      const removedName = removed[0].name;
      const normalizedName = removed[0].normalized_name;
      const moved = {
        audit_requests: 0,
        branches: 0,
        portal_users: 0,
        audits: 0,
        inquiries: 0,
        client_links: 0,
        change_logs: 0,
      };

      this.logger.log(`[MERGE] Starting: keep #${keepId} "${keeper[0].name}", remove #${removeId} "${removedName}"`);

      // 1. Move audit_requests
      const arResult = await manager.query(
        'UPDATE audit_requests SET company_id = ? WHERE company_id = ?',
        [keepId, removeId],
      );
      moved.audit_requests = arResult.affectedRows || 0;

      // 2. Move company_branches (skip duplicates by normalized_branch_name)
      // First delete branches that would conflict
      const conflictBranches = await manager.query(
        `SELECT cb.id FROM company_branches cb
         INNER JOIN company_branches cb2 
           ON cb2.company_id = ? AND cb2.normalized_branch_name = cb.normalized_branch_name
         WHERE cb.company_id = ?`,
        [keepId, removeId],
      );
      if (conflictBranches.length > 0) {
        const ids = conflictBranches.map((r: any) => r.id);
        await manager.query(`DELETE FROM company_branches WHERE id IN (?)`, [ids]);
      }
      const brResult = await manager.query(
        'UPDATE company_branches SET company_id = ? WHERE company_id = ?',
        [keepId, removeId],
      );
      moved.branches = brResult.affectedRows || 0;

      // 3. Move company_users (skip duplicates by user_id)
      const conflictUsers = await manager.query(
        `SELECT cu.company_id, cu.user_id FROM company_users cu
         INNER JOIN company_users cu2
           ON cu2.company_id = ? AND cu2.user_id = cu.user_id
         WHERE cu.company_id = ?`,
        [keepId, removeId],
      );
      for (const cu of conflictUsers) {
        await manager.query(`DELETE FROM company_users WHERE company_id = ? AND user_id = ?`, [cu.company_id, cu.user_id]);
      }
      const cuResult = await manager.query(
        'UPDATE company_users SET company_id = ? WHERE company_id = ?',
        [keepId, removeId],
      );
      moved.portal_users = cuResult.affectedRows || 0;

      // 4. Move company_audits
      const caResult = await manager.query(
        'UPDATE company_audits SET company_id = ? WHERE company_id = ?',
        [keepId, removeId],
      );
      moved.audits = caResult.affectedRows || 0;

      // 5. Move inquiries
      const inqResult = await manager.query(
        'UPDATE inquiries SET company_id = ? WHERE company_id = ?',
        [keepId, removeId],
      );
      moved.inquiries = inqResult.affectedRows || 0;

      // 6. Move company_client_links (skip duplicates)
      const conflictLinks = await manager.query(
        `SELECT cl.id FROM company_client_links cl
         INNER JOIN company_client_links cl2
           ON cl2.company_id = ? AND cl2.client_group = cl.client_group
         WHERE cl.company_id = ?`,
        [keepId, removeId],
      );
      if (conflictLinks.length > 0) {
        const ids = conflictLinks.map((r: any) => r.id);
        await manager.query(`DELETE FROM company_client_links WHERE id IN (?)`, [ids]);
      }
      const clResult = await manager.query(
        'UPDATE company_client_links SET company_id = ? WHERE company_id = ?',
        [keepId, removeId],
      );
      moved.client_links = clResult.affectedRows || 0;

      // 7. Move company_change_log
      const logResult = await manager.query(
        'UPDATE company_change_log SET company_id = ? WHERE company_id = ?',
        [keepId, removeId],
      );
      moved.change_logs = logResult.affectedRows || 0;

      // 8. Log the merge
      await manager.query(
        `INSERT INTO company_merge_log (kept_id, removed_id, removed_name, normalized_name, merged_at)
         VALUES (?, ?, ?, ?, NOW())`,
        [keepId, removeId, removedName, normalizedName],
      );

      // 9. Enrich keeper with data from removed (fill empty fields only)
      const keeperFull = (await manager.query('SELECT * FROM companies WHERE id = ?', [keepId]))[0];
      const removedFull = removed[0];

      const fillFields = ['contact_person', 'email', 'mobile', 'city', 'address', 'scope_of_work', 'trade_license_no'];
      for (const field of fillFields) {
        const keeperVal = keeperFull[field];
        const removedVal = removedFull[field];
        if ((!keeperVal || keeperVal === 'Unknown' || keeperVal === 'unknown@example.com') && removedVal && removedVal !== 'Unknown') {
          await manager.query(`UPDATE companies SET ${field} = ? WHERE id = ?`, [removedVal, keepId]);
          this.logger.log(`[MERGE] Enriched keeper: ${field} = "${removedVal}"`);
        }
      }

      // 10. Delete the duplicate company
      await manager.query('DELETE FROM companies WHERE id = ?', [removeId]);

      this.logger.log(
        `[MERGE] Complete: #${removeId} → #${keepId}. Moved: ${JSON.stringify(moved)}`,
      );

      return {
        kept_id: keepId,
        removed_id: removeId,
        removed_name: removedName,
        moved,
      };
    });
  }

  /**
   * Get merge history — all past merges
   */
  async getMergeHistory(): Promise<any[]> {
    return this.dataSource.query(`
      SELECT ml.*, c.name as kept_name
      FROM company_merge_log ml
      LEFT JOIN companies c ON c.id = ml.kept_id
      ORDER BY ml.merged_at DESC
      LIMIT 100
    `);
  }
}