import { Injectable } from '@nestjs/common';
import { InjectRepository, InjectDataSource } from '@nestjs/typeorm';
import { Repository, DataSource } from 'typeorm';
import { RecruiterVerification } from './entities/recruiter_verification.entity';
import { EmailService } from '../email/email.service';
import { UserEntity } from '../user/entities/user.entity';
import { Companies } from '../companies/entities/companies.entity';

@Injectable()
export class RecruiterVerificationService {
  constructor(
    @InjectRepository(RecruiterVerification)
    private recruiterVerificationRepository: Repository<RecruiterVerification>,
    @InjectDataSource()
    private dataSource: DataSource,
    private emailService: EmailService,
  ) {}

  async createVerificationRequest(data: {
    user_id: number;
    company_id: number;
    cin_number?: string;
    gst_number?: string;
    is_franchise_added: boolean;
    franchise_id?: number;
  }) {
    try {
      // console.log('Creating verification request with data:', data);

      // If franchise added, automatically approve
      const status = data.is_franchise_added ? 'approved' : 'pending';

      const verification = this.recruiterVerificationRepository.create({
        user_id: data.user_id,
        company_id: data.company_id,
        cin_number: data.cin_number || null,
        gst_number: data.gst_number || null,
        status,
        is_franchise_added: data.is_franchise_added,
        franchise_id: data.franchise_id || null,
      });

      const result =
        await this.recruiterVerificationRepository.save(verification);

      return {
        success: true,
        data: {
          id: result.id,
          status,
        },
      };
    } catch (error) {
      console.error('Error creating verification request:', error);
      return {
        success: false,
        message: 'Failed to create verification request',
        error: error.message,
      };
    }
  }

  async getPendingVerifications(page: number = 1, limit: number = 10) {
    try {
      const offset = (page - 1) * limit;

      const query = this.recruiterVerificationRepository
        .createQueryBuilder('rv')
        .select([
          'rv.id',
          'rv.user_id',
          'rv.company_id',
          'rv.cin_number',
          'rv.gst_number',
          'rv.status',
          'rv.remarks',
          'rv.is_franchise_added',
          'rv.franchise_id',
          'rv.created_at',
          'u.fullName',
          'u.email',
          'u.phone',
          'c.name as company_name',
        ])
        .leftJoin('users', 'u', 'u.id = rv.user_id')
        .leftJoin('companies', 'c', 'c.id = rv.company_id')
        .where('rv.status = :status', { status: 'pending' })
        .orderBy('rv.created_at', 'DESC')
        .offset(offset)
        .limit(limit);

      const verifications = await query.getRawMany();
      const total = await this.recruiterVerificationRepository
        .createQueryBuilder('rv')
        .where('rv.status = :status', { status: 'pending' })
        .getCount();

      return {
        success: true,
        data: {
          verifications,
          pagination: {
            page,
            limit,
            total,
            totalPages: Math.ceil(total / limit),
          },
        },
      };
    } catch (error) {
      console.error('Error fetching pending verifications:', error);
      return {
        success: false,
        message: 'Failed to fetch pending verifications',
        error: error.message,
      };
    }
  }

  async approveVerification(
    verificationId: number,
    adminId: number,
    remarks?: string,
  ) {
    const queryRunner = this.dataSource.createQueryRunner();

    try {
      await queryRunner.connect();
      await queryRunner.startTransaction();

      // Get verification details BEFORE updating
      const verification = await queryRunner.manager
        .createQueryBuilder()
        .select(['rv.user_id as user_id', 'rv.company_id as company_id'])
        .from('recruiter_verifications', 'rv')
        .where('rv.id = :id', { id: verificationId })
        .getRawOne();

      // console.log('Found verification record:', verification);
      // console.log('Verification ID being processed:', verificationId);
      console.log('Verification record details:', {
        user_id: verification?.user_id,
        company_id: verification?.company_id,
        exists: !!verification,
      });

      if (!verification) {
        throw new Error(
          `Verification record with ID ${verificationId} not found`,
        );
      }

      // Update verification record
      await queryRunner.manager
        .createQueryBuilder()
        .update('recruiter_verifications')
        .set({
          status: 'approved',
          remarks: remarks || null,
          reviewed_by: adminId,
          reviewed_at: new Date(),
          updated_at: new Date(),
        })
        .where('id = :id', { id: verificationId })
        .execute();

      console.log(
        'Updating company verification status for company ID:',
        verification.company_id,
      );

      // Update company verification status
      const companyUpdateResult = await queryRunner.manager
        .createQueryBuilder()
        .update('companies')
        .set({
          verification_status: 'verified',
          verification_remarks: remarks || null,
          verified_by: adminId,
          verified_at: new Date(),
        })
        .where('id = :id', { id: verification.company_id })
        .execute();

      // console.log('Company update result:', companyUpdateResult);

      // Activate user
      // console.log('Attempting to activate user with ID:', verification.user_id);

      const userUpdateResult = await queryRunner.manager
        .createQueryBuilder()
        .update('users')
        .set({
          status: 'active',
          updatedAt: new Date(),
        })
        .where('id = :id', { id: verification.user_id })
        .execute();

      console.log('User update result:', userUpdateResult);

      // Verify the update was successful
      if (userUpdateResult.affected === 0) {
        console.error('No rows were affected when updating user status');
        throw new Error('Failed to update user status - no rows affected');
      }

      // Double-check the user status was updated
      const updatedUser = await queryRunner.manager
        .createQueryBuilder()
        .select(['id', 'status'])
        .from('users', 'u')
        .where('u.id = :id', { id: verification.user_id })
        .getOne();

      console.log('Updated user status verification:', updatedUser);

      await queryRunner.commitTransaction();

      console.log('Transaction committed successfully');
      console.log('Verification approved for ID:', verificationId);
      console.log('User ID activated:', verification.user_id);
      console.log('Company ID verified:', verification.company_id);

      // Trigger email notification (asynchronously/non-blocking)
      (async () => {
        try {
          const recruiterUser = await this.dataSource.getRepository(UserEntity).findOne({
            where: { id: verification.user_id }
          });
          if (recruiterUser && recruiterUser.email) {
            const countResult = await this.dataSource.query(
              `SELECT COUNT(*) as totalSeekers FROM users WHERE role = 'seeker' AND status = 'active'`
            );
            const totalSeekers = countResult[0]?.totalSeekers || 0;

            const sampleSeekers = await this.dataSource.query(
              `SELECT u.full_name, r.name as profession_name,
                 (
                   SELECT GROUP_CONCAT(sk.skill_name SEPARATOR ', ')
                   FROM seeker_skills ss
                   INNER JOIN skills sk ON sk.id = ss.skill_id
                   WHERE ss.seeker_id = sp.id
                 ) as skills
               FROM seeker_profiles sp 
               INNER JOIN users u ON u.id = sp.user_id 
               LEFT JOIN roles r ON r.id = sp.profession_id
               WHERE u.role = 'seeker' AND u.status = 'active'
               ORDER BY u.id DESC 
               LIMIT 3`
            );

            await this.emailService.sendTemplateEmail(
              'recruiter_profile_approved',
              recruiterUser.email,
              recruiterUser.fullName || 'Recruiter',
              {
                preferred_name: recruiterUser.fullName || 'Recruiter',
                candidate_count: totalSeekers,
                candidates: sampleSeekers.map((s: any) => ({
                  full_name: s.full_name,
                  profession_name: s.profession_name || 'Professional Seeker',
                  skills: s.skills || 'Not Specified'
                }))
              }
            );
          }
        } catch (err) {
          console.error('Failed to send recruiter verification approval email:', err);
        }
      })();

      return {
        success: true,
        message: 'Verification approved successfully',
      };
    } catch (error) {
      console.error('Error approving verification:', error);
      console.error('Error details:', {
        verificationId,
        adminId,
        remarks,
        errorMessage: error.message,
        errorStack: error.stack,
      });

      // Only rollback if transaction is active
      if (queryRunner.isTransactionActive) {
        await queryRunner.rollbackTransaction();
      }

      return {
        success: false,
        message: 'Failed to approve verification',
        error: error.message,
      };
    } finally {
      // Always release the query runner
      if (queryRunner.isReleased === false) {
        await queryRunner.release();
      }
    }
  }

  async rejectVerification(
    verificationId: number,
    adminId: number,
    remarks: string,
  ) {
    const queryRunner = this.dataSource.createQueryRunner();

    try {
      await queryRunner.connect();
      await queryRunner.startTransaction();

      // Get verification details BEFORE updating
      const verification = await queryRunner.manager
        .createQueryBuilder()
        .select(['rv.user_id as user_id', 'rv.company_id as company_id'])
        .from('recruiter_verifications', 'rv')
        .where('rv.id = :id', { id: verificationId })
        .getRawOne();

      console.log('Found verification record for rejection:', verification);

      if (!verification) {
        throw new Error(
          `Verification record with ID ${verificationId} not found`,
        );
      }

      // Update verification record
      await queryRunner.manager
        .createQueryBuilder()
        .update('recruiter_verifications')
        .set({
          status: 'rejected',
          remarks,
          reviewed_by: adminId,
          reviewed_at: new Date(),
          updated_at: new Date(),
        })
        .where('id = :id', { id: verificationId })
        .execute();

      console.log(
        'Rejecting company verification for company ID:',
        verification.company_id,
      );

      // Update company verification status
      const companyUpdateResult = await queryRunner.manager
        .createQueryBuilder()
        .update('companies')
        .set({
          verification_status: 'rejected',
          verification_remarks: remarks,
          verified_by: adminId,
          verified_at: new Date(),
        })
        .where('id = :id', { id: verification.company_id })
        .execute();

      console.log('Company rejection update result:', companyUpdateResult);

      // Deactivate user
      const userUpdateResult = await queryRunner.manager
        .createQueryBuilder()
        .update('users')
        .set({
          status: 'inactive',
          updatedAt: new Date(),
        })
        .where('id = :id', { id: verification.user_id })
        .execute();

      console.log('User deactivation result:', userUpdateResult);

      await queryRunner.commitTransaction();

      // Trigger rejection email notification (asynchronously/non-blocking)
      (async () => {
        try {
          const recruiterUser = await this.dataSource.getRepository(UserEntity).findOne({
            where: { id: verification.user_id }
          });
          const company = await this.dataSource.getRepository(Companies).findOne({
            where: { id: verification.company_id }
          });
          if (recruiterUser && recruiterUser.email) {
            const preferredName =
              (recruiterUser.fullName && recruiterUser.fullName.trim().length > 0 && recruiterUser.fullName.trim()) ||
              recruiterUser.email.split('@')[0];

            const context = {
              preferred_name: preferredName,
              company_name: company?.name || '',
              company_context: company?.name ? ` for <strong>${company.name}</strong>` : '',
            };

            await this.emailService.sendTemplateEmail(
              'recruiter_profile_rejected',
              recruiterUser.email,
              preferredName,
              context
            );
          }
        } catch (err) {
          console.error('Failed to send recruiter verification rejection email:', err);
        }
      })();

      return {
        success: true,
        message: 'Verification rejected successfully',
      };
    } catch (error) {
      console.error('Error rejecting verification:', error);

      // Only rollback if transaction is active
      if (queryRunner.isTransactionActive) {
        await queryRunner.rollbackTransaction();
      }

      return {
        success: false,
        message: 'Failed to reject verification',
        error: error.message,
      };
    } finally {
      // Always release the query runner
      if (queryRunner.isReleased === false) {
        await queryRunner.release();
      }
    }
  }

  async getVerificationById(id: number) {
    try {
      const verification = await this.recruiterVerificationRepository
        .createQueryBuilder('rv')
        .select([
          'rv.id',
          'rv.user_id',
          'rv.company_id',
          'rv.cin_number',
          'rv.gst_number',
          'rv.status',
          'rv.remarks',
          'rv.is_franchise_added',
          'rv.franchise_id',
          'rv.created_at',
          'rv.reviewed_at',
          'u.fullName',
          'u.email',
          'u.phone',
          'c.name as company_name',
        ])
        .leftJoin('users', 'u', 'u.id = rv.user_id')
        .leftJoin('companies', 'c', 'c.id = rv.company_id')
        .where('rv.id = :id', { id })
        .getRawOne();

      console.log('Raw verification by ID data:', verification);

      return {
        success: true,
        data: verification,
      };
    } catch (error) {
      console.error('Error fetching verification:', error);
      return {
        success: false,
        message: 'Failed to fetch verification',
        error: error.message,
      };
    }
  }

  async getVerificationStats() {
    try {
      const [pending, approved, rejected] = await Promise.all([
        this.recruiterVerificationRepository
          .createQueryBuilder('rv')
          .select('COUNT(*)', 'count')
          .where('rv.status = :status', { status: 'pending' })
          .getRawOne(),
        this.recruiterVerificationRepository
          .createQueryBuilder('rv')
          .select('COUNT(*)', 'count')
          .where('rv.status = :status', { status: 'approved' })
          .getRawOne(),
        this.recruiterVerificationRepository
          .createQueryBuilder('rv')
          .select('COUNT(*)', 'count')
          .where('rv.status = :status', { status: 'rejected' })
          .getRawOne(),
      ]);

      return {
        success: true,
        data: {
          pending: parseInt(pending.count) || 0,
          approved: parseInt(approved.count) || 0,
          rejected: parseInt(rejected.count) || 0,
        },
      };
    } catch (error) {
      console.error('Error fetching verification stats:', error);
      return {
        success: false,
        message: 'Failed to fetch verification stats',
        error: error.message,
      };
    }
  }

  // Debug method to check all verification records
  async getAllVerifications() {
    try {
      // First, let's check if the table exists and has any records
      const tableExists = await this.dataSource
        .createQueryBuilder()
        .select('COUNT(*)', 'count')
        .from('recruiter_verifications', 'rv')
        .getRawOne();

      console.log('Table check - total records:', tableExists);

      const verifications = await this.recruiterVerificationRepository
        .createQueryBuilder('rv')
        .select([
          'rv.id',
          'rv.user_id',
          'rv.company_id',
          'rv.status',
          'rv.created_at',
          'rv.updated_at',
        ])
        .orderBy('rv.id', 'DESC')
        .getRawMany();

      console.log('All verification records:', verifications);

      // Also check what pending verifications exist
      const pendingVerifications = await this.recruiterVerificationRepository
        .createQueryBuilder('rv')
        .select(['rv.id', 'rv.user_id', 'rv.company_id', 'rv.status'])
        .where('rv.status = :status', { status: 'pending' })
        .getRawMany();

      console.log('Pending verification records:', pendingVerifications);

      // Check for specific ID
      const specificVerification = await this.recruiterVerificationRepository
        .createQueryBuilder('rv')
        .select(['rv.id', 'rv.user_id', 'rv.company_id', 'rv.status'])
        .where('rv.id = :id', { id: 10 })
        .getRawOne();

      console.log('Specific verification ID 10:', specificVerification);

      return {
        success: true,
        data: verifications,
        debug: {
          totalRecords: tableExists?.count || 0,
          pendingRecords: pendingVerifications,
          specificId10: specificVerification,
        },
      };
    } catch (error) {
      console.error('Error fetching all verifications:', error);
      return {
        success: false,
        message: 'Failed to fetch all verifications',
        error: error.message,
      };
    }
  }
}
