import { Injectable, BadRequestException } from '@nestjs/common';
import { DataSource, EntityManager } from 'typeorm';
import { UnlockedProfile } from './entities/unlocked_profile.entity';
import { CvDownloadLog } from './entities/cv_download.entity';
import { WalletService } from '../wallet/wallet.service';
import * as ExcelJS from 'exceljs';
import { UsageRecord } from '../feature_usage/entities/usage-record.entity';
import { ActivityLogsService } from '../activity_logs/activity_logs.service';

@Injectable()
@Injectable()
export class ProfileUnlockService {
  constructor(
    private readonly dataSource: DataSource,
    private readonly walletService: WalletService,
    private readonly activityLogsService: ActivityLogsService,
  ) {}

  /**
   * Check if a profile is unlocked for a recruiter for a specific action
   */
  async isProfileUnlocked(
    recruiterUserId: number,
    seekerId: number,
    actionType?: string,
  ): Promise<boolean> {
    let query = `SELECT * FROM unlocked_profiles 
             WHERE recruiter_user_id = ? AND seeker_user_id = ?`;
    const params: any[] = [recruiterUserId, seekerId];

    // If action type is specified, check for that specific permission
    if (actionType) {
      query += ` AND action_type = ?`;
      params.push(actionType);
    }

    query += ` AND is_active = 1
             AND (expires_at IS NULL OR expires_at > NOW())
             LIMIT 1`;

    const unlocked = await this.dataSource.query(query, params);
    return unlocked.length > 0;
  }

  /**
   * Unlock a profile (Deduct 1 Database Credit or use Free Limit)
   */
  async unlockProfile(
    recruiterUserId: number,
    seekerId: number,
    action?: string,
  ) {
    // Determine action type for permission tracking
    const actionType =
      action === 'CV Download' ? 'CV Download' : 'Profile View';

    // 1. Check if already unlocked for this specific action
    if (await this.isProfileUnlocked(recruiterUserId, seekerId, actionType)) {
      return {
        success: true,
        message: `${actionType} already unlocked for this profile`,
      };
    }

    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      // Fetch candidate name for better display
      const candidate = await queryRunner.query(
        'SELECT full_name FROM users WHERE id = ?',
        [seekerId],
      );
      const candidateName = candidate[0]?.full_name || `Candidate #${seekerId}`;

      // If action is CV Download, check if CV actually exists before deducting credits
      if (action === 'CV Download') {
        const [cv] = await queryRunner.query(
          `SELECT 1 FROM seeker_resumes sr 
                     JOIN seeker_profiles sp ON sr.seeker_id = sp.id 
                     WHERE sp.user_id = ? AND (sr.is_deleted = 0 OR sr.is_deleted IS NULL)
                     LIMIT 1`,
          [seekerId],
        );

        if (!cv) {
          throw new BadRequestException(
            `Candidate ${candidateName} does not have a CV available for download.`,
          );
        }
      }

      // 3. Deduct Credit (1 Database Credit)

      // Check if user is an associate
      const user = await queryRunner.query(
        'SELECT role FROM users WHERE id = ?',
        [recruiterUserId],
      );
      const isAssociate = user[0]?.role === 'associate';

      if (!isAssociate) {
        const description =
          action === 'CV Download'
            ? `CV Download: ${candidateName}`
            : `Unlock Profile: ${candidateName}`;

        await this.walletService.debitWallet(
          recruiterUserId,
          'database',
          1, // Cost
          description,
          'profile_unlock', // refType
          seekerId, // refId
          queryRunner.manager, // Pass transaction manager
        );
      }

      // 4. Record Usage (Analytics)
      await queryRunner.manager.save(UsageRecord, {
        user_id: recruiterUserId,
        feature_key: 'profile_unlock',
        units_used: 1,
        reference_id: seekerId,
      });

      // 5. Record Unlock
      // Get unlock duration from config (default 30 days)
      const [expirySetting] = await queryRunner.query(
        `SELECT setting_value FROM config_setting WHERE setting_key = 'profile_unlock_duration_days'`,
      );
      const durationDays = expirySetting
        ? parseInt(expirySetting.setting_value)
        : 30;

      // Calculate expiry date
      let expiresAt: Date | null = null;
      if (durationDays > 0) {
        expiresAt = new Date();
        expiresAt.setDate(expiresAt.getDate() + durationDays);
      }

      // Using User ID for recruiter_user_id column as requested
      // Assumes FK constraint to companies has been dropped
      await queryRunner.query(
        `INSERT INTO unlocked_profiles 
                (recruiter_user_id, seeker_user_id, unlock_type, action_type, credits_used, unlocked_at, expires_at, is_active)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          recruiterUserId,
          seekerId,
          'database_credits',
          actionType, // Store the specific action type
          isAssociate ? 0 : 1,
          new Date(),
          expiresAt,
          true,
        ],
      );

      await queryRunner.commitTransaction();

      // LOGGING: Candidate Profile Visit / Download (Report 3)
      try {
        // actionType logic: 'CV Download' or 'Profile View'
        // Map to required action types: 'candidate_view', 'cv_download'
        const logAction =
          actionType === 'CV Download' ? 'cv_download' : 'candidate_view';

        await this.activityLogsService.log({
          userId: recruiterUserId,
          userType: isAssociate ? 'associate' : 'recruiter',
          actionType: logAction,
          actionName: `${logAction === 'cv_download' ? 'Downloaded CV' : 'Viewed Profile'}: ${candidateName}`,
          jobId: null, // unlockProfile generic, usage doesn't pass jobId usually
          companyId: null, // Could fetch if needed, but implicit from user
          is_billable: !isAssociate,
          credits_used: 0, // ActivityLogsService will fetch dynamically from DB
          actionDetails: {
            candidate_id: seekerId,
            candidate_name: candidateName,
            unlock_type: 'database_credits',
          },
        });
      } catch (logError) {
        console.error('Failed to log profile unlock activity:', logError);
      }

      return { success: true, message: 'Profile unlocked successfully' };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw error;
    } finally {
      await queryRunner.release();
    }
  }

  /**
   * Log CV Download / Export (Audit Trail)
   */
  async logDownload(
    recruiterUserId: number,
    seekerId: number,
    action: 'download' | 'export',
    jobId?: number,
  ) {
    await this.dataSource.manager.save(CvDownloadLog, {
      recruiter_user_id: recruiterUserId, // Keeping User ID here as this is a log
      seeker_user_id: seekerId,
      action_type: action,
      job_id: jobId,
      credits_deducted: 0,
    });
  }

  /**
   * Get Unlocked Profiles with Pagination
   */
  async getUnlockedCandidates(
    recruiterUserId: number,
    page: number = 1,
    limit: number = 10,
    jobId?: number,
  ) {
    const offset = (page - 1) * limit;

    let filterClause = '';
    let queryParams: any[] = [recruiterUserId, limit, offset];
    let countParams: any[] = [recruiterUserId];

    if (jobId) {
      filterClause = ` AND EXISTS (
                SELECT 1 
                FROM job_applications ja_filter 
                JOIN seeker_profiles sp_filter ON ja_filter.seeker_id = sp_filter.id
                WHERE sp_filter.user_id = up.seeker_user_id 
                AND ja_filter.job_post_id = ?
            )`;
      // Adjust params order is tricky with raw SQL limit/offset at end.
      // Main Query Params: [recruiterUserId, jobId, limit, offset]
      queryParams = [recruiterUserId, jobId, limit, offset];
      countParams = [recruiterUserId, jobId];
    }

    const [profiles, total] = await Promise.all([
      this.dataSource.query(
        `SELECT 
            up.unlocked_at, 
            up.unlock_type,
            up.seeker_user_id,
            u.full_name, 
            u.email, 
            u.phone,
            u.profile_picture,
            p.professional_title,
            (
                SELECT GROUP_CONCAT(DISTINCT jp.title SEPARATOR ', ')
                FROM job_applications ja
                JOIN job_posts jp ON ja.job_post_id = jp.id
                JOIN seeker_profiles sp ON ja.seeker_id = sp.id
                WHERE sp.user_id = up.seeker_user_id
                AND jp.company_id IN (
                    SELECT id FROM companies WHERE user_id = up.recruiter_user_id
                    UNION
                    SELECT c.id 
                    FROM companies c 
                    JOIN associates a ON c.user_id = a.created_by 
                    WHERE a.user_id = up.recruiter_user_id
                )
            ) as applied_jobs
         FROM unlocked_profiles up
         JOIN users u ON up.seeker_user_id = u.id
         LEFT JOIN seeker_profiles p ON u.id = p.user_id
         WHERE up.recruiter_user_id = ? 
           AND up.is_active = 1
           AND (up.expires_at IS NULL OR up.expires_at > NOW())
           AND NOT EXISTS (
                SELECT 1 FROM unlocked_profiles up2 
                WHERE up2.recruiter_user_id = up.recruiter_user_id 
                AND up2.seeker_user_id = up.seeker_user_id 
                AND up2.is_active = 1 
                AND (up2.expires_at IS NULL OR up2.expires_at > NOW())
                AND up2.unlocked_at > up.unlocked_at
           )
           ${filterClause}
         ORDER BY up.unlocked_at DESC
         LIMIT ? OFFSET ?`,
        queryParams,
      ),
      this.dataSource.query(
        `SELECT COUNT(DISTINCT up.seeker_user_id) as count 
         FROM unlocked_profiles up
         WHERE up.recruiter_user_id = ? 
           AND up.is_active = 1
           AND (up.expires_at IS NULL OR up.expires_at > NOW())
           ${filterClause}`,
        countParams,
      ),
    ]);

    return {
      candidates: profiles,
      pagination: {
        total: parseInt(total[0].count),
        page,
        limit,
        totalPages: Math.ceil(parseInt(total[0].count) / limit),
      },
    };
  }

  /**
   * Export Unlocked Candidates to Excel (Costs 2 Credits)
   */
  async exportToExcel(
    recruiterUserId: number,
    jobId?: number,
  ): Promise<Buffer> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      // Check if user is an associate
      const user = await queryRunner.query(
        'SELECT role FROM users WHERE id = ?',
        [recruiterUserId],
      );
      const isAssociate = user[0]?.role === 'associate';

      if (!isAssociate) {
        // 1. Deduct Credits (2 Database Credits)
        await this.walletService.debitWallet(
          recruiterUserId,
          'database',
          2,
          'Export Unlocked Candidates to Excel',
          'export_excel',
          Date.now(),
          queryRunner.manager,
        );
      }

      // 2. Record Usage (Analytics)
      await queryRunner.manager.save(UsageRecord, {
        user_id: recruiterUserId,
        feature_key: 'export_excel',
        units_used: isAssociate ? 0 : 1,
        reference_id: null,
      });

      // 3. Fetch Unlocked Profiles using User ID
      let filterClause = '';
      let exportParams: any[] = [
        recruiterUserId,
        recruiterUserId,
        recruiterUserId,
      ];

      if (jobId) {
        filterClause = ` AND EXISTS (
                    SELECT 1 
                    FROM job_applications ja_filter 
                    JOIN seeker_profiles sp_filter ON ja_filter.seeker_id = sp_filter.id
                    WHERE sp_filter.user_id = up.seeker_user_id 
                    AND ja_filter.job_post_id = ?
                )`;
        exportParams = [
          recruiterUserId,
          recruiterUserId,
          recruiterUserId,
          jobId,
        ];
      }

      const profiles = await queryRunner.query(
        `SELECT 
            up.unlocked_at, 
            up.unlock_type,
            u.full_name, 
            u.email, 
            u.phone,
            (
                SELECT GROUP_CONCAT(DISTINCT jp.title SEPARATOR ', ')
                FROM job_applications ja
                JOIN job_posts jp ON ja.job_post_id = jp.id
                JOIN seeker_profiles sp ON ja.seeker_id = sp.id
                WHERE sp.user_id = up.seeker_user_id
                AND jp.company_id IN (
                    SELECT id FROM companies WHERE user_id = ?
                    UNION
                    SELECT c.id 
                    FROM companies c 
                    JOIN associates a ON c.user_id = a.created_by 
                    WHERE a.user_id = ?
                )
            ) as applied_jobs
         FROM unlocked_profiles up
         JOIN users u ON up.seeker_user_id = u.id
         WHERE up.recruiter_user_id = ? 
           AND up.is_active = 1
           AND (up.expires_at IS NULL OR up.expires_at > NOW())
           ${filterClause}
         ORDER BY up.unlocked_at DESC`,
        exportParams,
      );

      // 3. Generate Excel File
      const workbook = new ExcelJS.Workbook();
      const worksheet = workbook.addWorksheet('Unlocked Candidates');

      worksheet.columns = [
        { header: 'Unlocked At', key: 'unlocked_at', width: 20 },
        { header: 'Unlock Type', key: 'unlock_type', width: 15 },
        { header: 'Full Name', key: 'full_name', width: 30 },
        { header: 'Email', key: 'email', width: 30 },
        { header: 'Phone', key: 'phone', width: 20 },
        { header: 'Applied Jobs', key: 'applied_jobs', width: 40 },
      ];

      profiles.forEach((profile) => {
        worksheet.addRow({
          unlocked_at: profile.unlocked_at,
          unlock_type: profile.unlock_type,
          full_name: profile.full_name,
          email: profile.email,
          phone: profile.phone,
          applied_jobs: profile.applied_jobs || '',
        });
      });

      await queryRunner.commitTransaction();

      // LOGGING: Excel Download (Report 3)
      try {
        await this.activityLogsService.log({
          userId: recruiterUserId,
          userType: isAssociate ? 'associate' : 'recruiter',
          actionType: 'excel_download',
          actionName: 'Downloaded Unlocked Candidates Excel',
          jobId: jobId || null,
          companyId: null,
          is_billable: !isAssociate,
          credits_used: 0, // ActivityLogsService will fetch dynamically from DB
          actionDetails: {
            jobId: jobId,
            count: profiles.length,
          },
        });
      } catch (logError) {
        console.error('Failed to log excel download activity:', logError);
      }

      const buffer = await workbook.xlsx.writeBuffer();
      return Buffer.from(buffer);
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw error;
    } finally {
      await queryRunner.release();
    }
  }
}
