import {
  Injectable,
  BadRequestException,
  NotFoundException,
  ForbiddenException,
  Logger,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { InjectDataSource } from '@nestjs/typeorm';
import { Repository, DataSource, DeepPartial, Brackets } from 'typeorm';
import { Company } from './entities/company.entity';
import { CreateCompanyDto } from './dto/create-company.dto';
import { UpdateCompanyDto } from './dto/update-company.dto';
import { Standard } from '../standards/entities/standard.entity';
import { Country } from '../countries/entities/country.entity';
import { In } from 'typeorm';
import { CompanySequence } from './entities/company-sequence.entity';
// 🆕 ISSUE 2 & 7 FIX — import the resolver to use its normalize() method
import { CompanyResolverService } from './services/company-resolver.service';
// 🆕 ISSUE 5 & 6 FIX — import CompanyUser for portal access control
import { CompanyUser } from './entities/company-user.entity';

@Injectable()
export class CompaniesService {
  [x: string]: any;
  private readonly logger = new Logger('CompaniesService');

  constructor(
    @InjectRepository(Company, 'scheme_dbs')
    private readonly companyRepository: Repository<Company>,

    @InjectRepository(Standard, 'scheme_dbs')
    private readonly standardRepository: Repository<Standard>,

    @InjectRepository(Country, 'scheme_dbs')
    private readonly countryRepository: Repository<Country>,

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

    // 🆕 ISSUE 2 FIX — inject the resolver so we can use normalize()
    private readonly companyResolver: CompanyResolverService,
  ) { }

  // --- Generate Unique Company Code ---
  private async generateCompanyCode(name: string): Promise<string> {
    return await this.dataSource.transaction(async (manager) => {
      // 1️⃣ Lock the sequence row
      const seqRow = await manager
        .createQueryBuilder(CompanySequence, 'seq')
        .setLock('pessimistic_write')
        .where('seq.id = :id', { id: 1 })
        .getOne();

      if (!seqRow) throw new Error('Sequence row missing!');

      // 2️⃣ Build initials (max 3 chars)
      let initials = name
        .split(' ')
        .filter((w) => w.length > 2)
        .map((w) => w[0].toUpperCase())
        .join('')
        .substring(0, 3);

      if (initials.length < 3) {
        initials = name
          .replace(/[^A-Za-z]/g, '')
          .substring(0, 3)
          .toUpperCase();
      }

      // 3️⃣ Prepare date prefix
      const now = new Date();
      const year = now.getFullYear();
      const month = String(now.getMonth() + 1).padStart(2, '0');

      // 4️⃣ Generate unique code in a loop
      let companyCode = '';
      while (true) {
        seqRow.last_number += 1;
        if (seqRow.last_number > 999999) seqRow.last_number = 1; // reset if exceeds 6 digits

        const uniqueSeq = seqRow.last_number.toString().padStart(6, '0');
        companyCode = `${initials}-${year}-${month}-${uniqueSeq}`;

        // ✅ Check uniqueness in the DB
        const exists = await manager.findOne(Company, {
          where: { company_code: companyCode },
        });

        if (!exists) break; // unique code found, exit loop
      }

      // 5️⃣ Save updated sequence
      await manager.save(seqRow);

      return companyCode;
    });
  }

  async migrateUniqueCompanies() {
    console.log('🚀 Starting full company migration...');

    // 1️⃣ Safe delete instead of truncate (avoids FK issues)
    await this.dataSource.query('DELETE FROM companies');
    console.log('✅ Cleared companies table safely.');

    // 2️⃣ Reset sequence
    const seqRepo = this.dataSource.getRepository(CompanySequence);
    let seq = await seqRepo.findOne({ where: { id: 1 } });
    if (!seq) {
      seq = seqRepo.create({ id: 1, last_number: 0 });
    } else {
      seq.last_number = 0; // reset sequence
    }
    await seqRepo.save(seq);
    console.log('✅ Reset company sequence.');

    // 3️⃣ Fetch unique company names
    const rawCompanies: { name: string; status: string }[] = await this
      .dataSource.query(`
      SELECT DISTINCT TRIM(company_name) AS name, status
      FROM certificates
      WHERE company_name IS NOT NULL AND company_name <> ''
    `);

    console.log(`Found ${rawCompanies.length} unique company names.`);

    // 4️⃣ Insert companies with unique 6-digit codes
    const insertedNames = new Set<string>();
    let insertedCount = 0;

    for (const { name, status } of rawCompanies) {
      if (!name) continue;

      // 🔧 ISSUE 2 FIX — Use the resolver's normalize() instead of basic toUpperCase
      const normalizedName = this.companyResolver.normalize(name);
      if (insertedNames.has(normalizedName)) continue;

      // Generate unique company code
      const company_code = await this.generateCompanyCode(name);

      try {
        // ✅ Insert with OR IGNORE to skip duplicates in DB
        await this.dataSource
          .getRepository(Company)
          .createQueryBuilder()
          .insert()
          .values({
            name: name.trim().replace(/\s+/g, ' '),
            normalized_name: normalizedName,   // 🆕 SET normalized_name
            company_code,
            certification_body: status || 'Unknown',
            address: 'P.O. BOX 12345, Dubai, UAE',
            city: 'Dubai',
            contact_person: 'Unknown',
            designation: 'Unknown',
            email: 'unknown@example.com',
            mobile: '0000000000',
            telephone: '0000000000',
            fax: '0000000000',
            validity: 'Unknown',
            accreditation: 'Unknown',
            scope_of_work: 'Unknown',
            country: { id: 3 } as any,
          })
          .orIgnore() // skip duplicate names
          .execute();

        insertedNames.add(normalizedName);
        insertedCount++;
        console.log(`✅ Inserted: ${name} (${company_code})`);
      } catch (err) {
        console.warn(
          `⚠️ Skipped duplicate or failed insert for: ${name}`,
        );
      }
    }

    console.log(`🎉 Migration complete! Inserted ${insertedCount} companies.`);
  }

  // --- Create Company ---
  // 🔧 ISSUE 2 FIX — Uses resolver's normalize() for duplicate check + sets normalized_name
  async create(dto: CreateCompanyDto) {
    return await this.dataSource.transaction(async (manager) => {
      // ✅ ISSUE 2 FIX — Use the resolver's full normalization pipeline
      //    instead of simple LOWER() comparison.
      //    This catches: "ABC Company" vs "ABC  Company" vs "abc company llc"
      const normalizedName = this.companyResolver.normalize(dto.name);

      if (normalizedName) {
        const existing = await manager.findOne(Company, {
          where: { normalized_name: normalizedName },
        });

        if (existing) {
          throw new BadRequestException(
            `You're trying to add a duplicate company. "${dto.name}" matches existing company "${existing.name}" (id=${existing.id}).`,
          );
        }
      }

      // Also check exact name match as a fallback (original behavior preserved)
      const exactMatch = await manager
        .createQueryBuilder(Company, 'company')
        .where('LOWER(company.name) = LOWER(:name)', { name: dto.name })
        .getOne();

      if (exactMatch) {
        throw new BadRequestException(
          `You're trying to add a duplicate company. "${dto.name}" already exists in the database.`,
        );
      }

      // Generate unique company code
      const company_code = await this.generateCompanyCode(dto.name);

      // Only assign primitive fields to create()
      const company = manager.create(Company, {
        name: dto.name,
        normalized_name: normalizedName,    // 🆕 ISSUE 2 FIX — always set normalized_name
        company_code,
        address: dto.address,
        city: dto.city,
        contact_person: dto.contact_person,
        designation: dto.designation,
        email: dto.email,
        mobile: dto.mobile,
        telephone: dto.telephone,
        fax: dto.fax,
        validity: dto.validity,
        certification_body: dto.certification_body,
        client_group: dto.client_group,
        accreditation: dto.accreditation,
        scope_of_work: dto.scope_of_work,
        // ✅ NEW FIELD 1
        reference_number: dto.reference_number,
        documents: dto.documents,
      });

      // Assign country relation
      if (dto.country_id) {
        const country = await manager.findOne(Country, {
          where: { id: dto.country_id },
        });
        if (!country) throw new BadRequestException('Invalid country_id');
        company.country = country;
      }

      // Assign standards relation
      if (dto.standards && dto.standards.length > 0) {
        const standards = await manager.find(Standard, {
          where: { id: In(dto.standards) },
        });
        company.standards = standards;
      }

      return await manager.save(company);
    });
  }

  async findAll(options?: { page?: number; limit?: number; search?: string }) {
    const search = options?.search?.trim() || '';
    const query = this.dataSource
      .getRepository(Company)
      .createQueryBuilder('company')
      .leftJoinAndSelect('company.country', 'country')
      .leftJoinAndSelect('company.standards', 'standards');

    const page = options?.page || 1;
    const limit = options?.limit || 50;
    const skip = (page - 1) * limit;

    if (search) {
      // Check if the search term is a number for id search
      const isNumeric = !isNaN(Number(search));

      query.where(
        new Brackets((qb) => {
          qb.where('LOWER(company.name) LIKE :search', {
            search: `%${search.toLowerCase()}%`,
          }).orWhere('LOWER(company.company_code) LIKE :search', {
            search: `%${search.toLowerCase()}%`,
          });

          if (isNumeric) {
            qb.orWhere('company.id = :id', { id: Number(search) });
          }
        }),
      );

      // Keep pagination even when searching
      query.skip(skip).take(limit);
    } else {
      query.skip(skip).take(limit);
    }

    query.orderBy('company.id', 'DESC');

    const [data, total] = await query.getManyAndCount();
    await this.fillClientGroupFromAuditRequest(data);

    const totalPages = Math.ceil(total / limit);
    return {
      data,
      meta: {
        total,
        page,
        limit,
        totalPages,
        hasNextPage: page < totalPages,
        hasPrevPage: page > 1,
      },
    };
  }
  private async fillClientGroupFromAuditRequest(
    companies: Company[],
  ): Promise<void> {
    // only companies whose own client_group is blank
    const missing = companies.filter(
      (c) => !c.client_group || String(c.client_group).trim() === '',
    );
    if (missing.length === 0) return;

    const ids = missing.map((c) => c.id);
    const placeholders = ids.map(() => '?').join(',');

    // one batched query; newest request first so the first row per company wins
    const rows: Array<{ company_id: number; client_group: string }> =
      await this.dataSource.query(
        `SELECT company_id, client_group
           FROM audit_requests
          WHERE company_id IN (${placeholders})
          ORDER BY company_id ASC, created_at DESC`,
        ids,
      );

    const groupByCompany = new Map<number, string>();
    for (const row of rows) {
      if (!groupByCompany.has(row.company_id) && row.client_group) {
        groupByCompany.set(row.company_id, row.client_group);
      }
    }

    for (const company of missing) {
      const group = groupByCompany.get(company.id);
      if (group) company.client_group = group;
    }
  }
  async getAnalyticsByCertBody() {
    // Total clients overall
    const totalClients = await this.companyRepository.count();

    // Total clients per certification body
    const perBody = await this.companyRepository
      .createQueryBuilder('company')
      .select('company.certification_body', 'certification_body')
      .addSelect('COUNT(company.id)', 'total')
      .groupBy('company.certification_body')
      .getRawMany();

    return {
      totalClients,
      perBody,
    };
  }

  // --- Find one company ---
  findOne(id: number) {
    return this.companyRepository
      .createQueryBuilder('company')
      .leftJoinAndSelect('company.country', 'country')
      .leftJoinAndSelect('company.standards', 'standards')
      .where('company.id = :id', { id })
      .getOne();
  }

  // --- Update company ---
  // 🔧 ISSUE 3 FIX — Recalculates normalized_name when name changes
  async update(id: number, dto: UpdateCompanyDto) {
    return await this.dataSource.transaction(async (manager) => {
      console.log('🟢 Update called for company id:', id);
      console.log('🟢 Incoming DTO:', dto);

      const company = await manager.findOne(Company, {
        where: { id },
        relations: ['standards', 'country'],
      });

      if (!company) {
        console.error('❌ Company not found');
        throw new NotFoundException('Company not found');
      }

      console.log('🟡 Current company data before update:', {
        ...company,
        standards: company.standards.map((s) => ({ id: s.id, name: s.name })),
        country: company.country
          ? { id: company.country.id, name: company.country.name }
          : null,
      });

      // ═══════════════════════════════════════════════════════════════
      // 🆕 ISSUE 3 FIX — If name changed, recalculate normalized_name
      //    and check for duplicates with the new name
      // ═══════════════════════════════════════════════════════════════
      if (dto.name && dto.name !== company.name) {
        const newNormalized = this.companyResolver.normalize(dto.name);

        // Check the new normalized name doesn't collide with another company
        if (newNormalized) {
          const collision = await manager.findOne(Company, {
            where: { normalized_name: newNormalized },
          });
          if (collision && collision.id !== id) {
            throw new BadRequestException(
              `Cannot rename — "${dto.name}" matches existing company "${collision.name}" (id=${collision.id}).`,
            );
          }
        }

        // Update the normalized name alongside the display name
        company.normalized_name = newNormalized;
        this.logger.log(
          `[UPDATE] Company #${id}: name changed "${company.name}" → "${dto.name}", normalized="${newNormalized}"`,
        );
      }

      // Update basic fields
      Object.assign(company, dto);
      console.log('🟢 After basic field assignment:', {
        name: company.name,
        email: company.email,
        address: company.address,
      });

      // ✅ Update reference number (open field)
      if (dto.reference_number !== undefined) {
        company.reference_number = dto.reference_number;
      }
      if (dto.client_group !== undefined) {
        company.client_group = dto.client_group;
      }
      // ✅ Update uploaded documents (PDF / Word)
      if (dto.documents !== undefined) {
        company.documents = dto.documents;
      }

      // Update country
      if (dto.country_id) {
        const country = await manager.findOne(Country, {
          where: { id: dto.country_id },
        });
        if (!country) {
          console.error('❌ Invalid country_id:', dto.country_id);
          throw new BadRequestException('Invalid country_id');
        }
        company.country = country;
        console.log('🟢 Country updated to:', {
          id: country.id,
          name: country.name,
        });
      }

      // Update standards (replace entirely, safest way)
      if (Array.isArray(dto.standards)) {
        const standardIds = dto.standards.map(Number); // ensure numbers
        console.log('🟢 Updating standards with IDs:', standardIds);

        const standards = await manager.find(Standard, {
          where: { id: In(standardIds) },
        });

        console.log(
          '🟢 Standards fetched from DB:',
          standards.map((s) => ({ id: s.id, name: s.name })),
        );

        company.standards = standards; // replace old standards with new selection
        console.log(
          '🟢 Company standards after update:',
          company.standards.map((s) => ({ id: s.id, name: s.name })),
        );
      }

      const savedCompany = await manager.save(company);
      console.log('✅ Company saved successfully:', {
        id: savedCompany.id,
        name: savedCompany.name,
        standards: savedCompany.standards.map((s) => ({
          id: s.id,
          name: s.name,
        })),
        country: savedCompany.country
          ? { id: savedCompany.country.id, name: savedCompany.country.name }
          : null,
      });

      // Return with relations so frontend always has updated data
      const updatedCompany = await manager.findOne(Company, {
        where: { id: savedCompany.id },
        relations: ['standards', 'country'],
      });

      console.log('🟢 Final company returned to frontend:', updatedCompany);

      return updatedCompany;
    });
  }

  // --- Regenerate company codes for all existing companies ---
  async regenerateAllCompanyCodes() {
    // 1️⃣ Fetch all companies
    const companies = await this.companyRepository.find({
      order: { id: 'ASC' }, // optional: order by ID
    });

    for (const company of companies) {
      // 2️⃣ Generate new unique company code
      const newCode = await this.generateCompanyCode(company.name);
      company.company_code = newCode;
      await this.companyRepository.save(company);

      console.log(`Updated ${company.name} -> ${newCode}`);
    }

    console.log('✅ All company codes regenerated successfully.');
  }

  // --- Remove company ---
  remove(id: number) {
    return this.companyRepository.delete(id);
  }

  // ═══════════════════════════════════════════════════════════════════════════
  // 🆕 ISSUE 4 FIX — Backfill normalized_name for companies that have NULL
  //    Safe to run multiple times — only updates NULL records.
  // ═══════════════════════════════════════════════════════════════════════════
  async backfillNormalizedNames(): Promise<{
    updated: number;
    skipped: number;
    duplicates: Array<{ normalized: string; ids: number[] }>;
  }> {
    const repo = this.dataSource.getRepository(Company);

    // Find all companies with NULL normalized_name
    const nullCompanies = await repo
      .createQueryBuilder('c')
      .where('c.normalized_name IS NULL OR c.normalized_name = :empty', { empty: '' })
      .getMany();

    this.logger.log(`[BACKFILL] Found ${nullCompanies.length} companies with NULL normalized_name`);

    let updated = 0;
    let skipped = 0;
    const duplicates: Array<{ normalized: string; ids: number[] }> = [];

    for (const company of nullCompanies) {
      const normalized = this.companyResolver.normalize(company.name);
      if (!normalized) {
        this.logger.warn(`[BACKFILL] Skipped id=${company.id} — name "${company.name}" normalizes to empty`);
        skipped++;
        continue;
      }

      // Check if this normalized_name already exists
      const existing = await repo.findOne({
        where: { normalized_name: normalized },
      });

      if (existing && existing.id !== company.id) {
        this.logger.warn(
          `[BACKFILL] ⚠️ DUPLICATE: id=${company.id} "${company.name}" → "${normalized}" collides with id=${existing.id} "${existing.name}"`,
        );
        duplicates.push({ normalized, ids: [existing.id, company.id] });
        skipped++;
        continue;
      }

      // Safe to update
      await repo.update(company.id, { normalized_name: normalized });
      updated++;
      this.logger.log(`[BACKFILL] ✅ id=${company.id} "${company.name}" → "${normalized}"`);
    }

    this.logger.log(`[BACKFILL] Done: ${updated} updated, ${skipped} skipped, ${duplicates.length} duplicates found`);
    return { updated, skipped, duplicates };
  }

  // ═══════════════════════════════════════════════════════════════════════════
  // 🆕 ISSUE 5 & 6 — CLIENT PORTAL ACCESS CONTROL
  //    Full CRUD for company_users with status management.
  //    Nothing is ever deleted — status flips between ACTIVE and DISABLED.
  // ═══════════════════════════════════════════════════════════════════════════

  /**
   * List all portal users for a company, with their status and audit trail.
   */
  async getPortalUsers(companyId: number): Promise<CompanyUser[]> {
    const repo = this.dataSource.getRepository(CompanyUser);
    return repo.find({
      where: { company_id: companyId },
      relations: ['user', 'granted_by', 'disabled_by'],
      order: { created_at: 'DESC' },
    });
  }

  /**
   * Grant client portal access — creates a company_users row with status INVITED.
   * If the row already exists and is DISABLED, re-enables it instead of creating a duplicate.
   */
  async grantPortalAccess(
    companyId: number,
    userId: number,
    email: string,
    grantedById: number,
  ): Promise<CompanyUser> {
    const repo = this.dataSource.getRepository(CompanyUser);

    // Check company exists
    const company = await this.companyRepository.findOne({ where: { id: companyId } });
    if (!company) {
      throw new NotFoundException(`Company #${companyId} not found`);
    }

    // Check if this user already has access to this company
    const existing = await repo.findOne({
      where: { company_id: companyId, user_id: userId },
    });

    if (existing) {
      if (existing.status === 'ACTIVE') {
        throw new BadRequestException('This user already has active portal access.');
      }

      if (existing.status === 'INVITED') {
        throw new BadRequestException('An invite is already pending for this user.');
      }

      // DISABLED → re-enable
      existing.status = 'ACTIVE';
      existing.disabled_at = null;
      existing.disabled_by_id = null;
      existing.granted_by_id = grantedById;
      existing.invite_email = email;
      const saved = await repo.save(existing);

      this.logger.log(
        `[PORTAL] ✅ Re-enabled portal access: company=${companyId} user=${userId} by=${grantedById}`,
      );
      return saved;
    }

    // Create new portal access
    const portalUser = repo.create({
      company_id: companyId,
      user_id: userId,
      role: 'CLIENT',
      status: 'INVITED',
      invite_email: email,
      granted_by_id: grantedById,
    });

    const saved = await repo.save(portalUser);
    this.logger.log(
      `[PORTAL] ✅ Granted portal access: company=${companyId} user=${userId} email=${email} by=${grantedById}`,
    );

    return saved;
  }

  /**
   * Disable one user's portal access — instant, reversible.
   * Sets status=DISABLED and records who/when.
   */
  async disablePortalAccess(
    companyId: number,
    userId: number,
    disabledById: number,
  ): Promise<CompanyUser> {
    const repo = this.dataSource.getRepository(CompanyUser);

    const existing = await repo.findOne({
      where: { company_id: companyId, user_id: userId },
    });

    if (!existing) {
      throw new NotFoundException('Portal access record not found.');
    }

    if (existing.status === 'DISABLED') {
      throw new BadRequestException('Portal access is already disabled.');
    }

    existing.status = 'DISABLED';
    existing.disabled_at = new Date();
    existing.disabled_by_id = disabledById;

    const saved = await repo.save(existing);
    this.logger.log(
      `[PORTAL] 🔒 Disabled portal access: company=${companyId} user=${userId} by=${disabledById}`,
    );

    return saved;
  }

  /**
   * Re-enable a previously disabled user's portal access.
   */
  async enablePortalAccess(
    companyId: number,
    userId: number,
    enabledById: number,
  ): Promise<CompanyUser> {
    const repo = this.dataSource.getRepository(CompanyUser);

    const existing = await repo.findOne({
      where: { company_id: companyId, user_id: userId },
    });

    if (!existing) {
      throw new NotFoundException('Portal access record not found.');
    }

    if (existing.status === 'ACTIVE') {
      throw new BadRequestException('Portal access is already active.');
    }

    existing.status = 'ACTIVE';
    existing.disabled_at = null;
    existing.disabled_by_id = null;
    existing.granted_by_id = enabledById;

    const saved = await repo.save(existing);
    this.logger.log(
      `[PORTAL] 🔓 Re-enabled portal access: company=${companyId} user=${userId} by=${enabledById}`,
    );

    return saved;
  }

  /**
   * Emergency kill switch — disable ALL portal users for a company instantly.
   */
  async disableAllPortalAccess(
    companyId: number,
    disabledById: number,
  ): Promise<{ disabled: number }> {
    const repo = this.dataSource.getRepository(CompanyUser);

    const result = await repo
      .createQueryBuilder()
      .update(CompanyUser)
      .set({
        status: 'DISABLED',
        disabled_at: new Date(),
        disabled_by_id: disabledById,
      })
      .where('company_id = :companyId', { companyId })
      .andWhere('status != :disabled', { disabled: 'DISABLED' })
      .execute();

    const count = result.affected || 0;
    this.logger.log(
      `[PORTAL] 🚨 Disabled ALL portal access: company=${companyId} count=${count} by=${disabledById}`,
    );

    return { disabled: count };
  }

  /**
   * Permanently revoke — hard delete. Admin only. Use disablePortalAccess() 
   * in most cases (it's reversible). This is for cleanup only.
   */
  async revokePortalAccess(
    companyId: number,
    userId: number,
  ): Promise<{ message: string }> {
    const repo = this.dataSource.getRepository(CompanyUser);

    const existing = await repo.findOne({
      where: { company_id: companyId, user_id: userId },
    });

    if (!existing) {
      throw new NotFoundException('Portal access record not found.');
    }

    await repo.delete({ company_id: companyId, user_id: userId });
    this.logger.log(
      `[PORTAL] ❌ Permanently revoked portal access: company=${companyId} user=${userId}`,
    );

    return { message: 'Portal access permanently revoked.' };
  }

  /**
   * Update last_login_at — called by the client portal login endpoint
   * after a successful OTP verification.
   */
  async updateLastLogin(companyId: number, userId: number): Promise<void> {
    const repo = this.dataSource.getRepository(CompanyUser);

    await repo.update(
      { company_id: companyId, user_id: userId },
      {
        last_login_at: new Date(),
        // If status was INVITED, flip to ACTIVE on first login
        status: 'ACTIVE',
      },
    );

    this.logger.log(`[PORTAL] 📍 Updated last_login: company=${companyId} user=${userId}`);
  }
}