import { Injectable } from '@nestjs/common';
import { InjectDataSource, InjectRepository } from '@nestjs/typeorm';
import { DataSource, Repository } from 'typeorm';
import { Franchises } from './entities/franchises.entity';
import { UserEntity } from '../user/entities/user.entity';
import { Companies } from '../companies/entities/companies.entity';

@Injectable()
export class FranchisesService {
  constructor(
    @InjectRepository(Franchises)
    private readonly franchiseRepository: Repository<Franchises>,
    @InjectRepository(UserEntity)
    private readonly userRepository: Repository<UserEntity>,
    @InjectRepository(Companies)
    private readonly companyRepository: Repository<Companies>,
    @InjectDataSource()
    private dataSource: DataSource,
  ) {}

  async addCompanyForFranchise(
    franchiseId: number,
    companyData: any,
  ): Promise<any> {
    try {
      console.log('Adding company for franchise:', {
        franchiseId,
        companyData,
      });

      // Extract user and company data
      const {
        // User fields
        full_name,
        username,
        email,
        phone,
        password,

        // Company fields
        name,
        about,
        vision,
        website,
        location,
        founded_at,
        company_size_id,
        organization_type_id,
        industry_id,
        country_id,
        state_id,
        city_id,
        pincode,
        latitude,
        longitude,
        gst_number,
        cin_number,
      } = companyData;

      // Hash the password (using simple hash for now, you can implement proper bcrypt later)
      const hashedPassword = password; // TODO: Implement proper password hashing

      // Check if company name already exists
      const existingCompany = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'name'])
        .from('companies', 'c')
        .where('c.name = :name', { name })
        .andWhere('c.is_deleted = :isDeleted', { isDeleted: 0 })
        .getOne();

      if (existingCompany) {
        throw new Error(
          'Company name already exists. Please choose a different name.',
        );
      }

      // Check if user already exists with the same email or username
      const existingUser = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'email', 'username'])
        .from('users', 'u')
        .where('u.email = :email', { email })
        .orWhere('u.username = :username', { username })
        .getOne();

      if (existingUser) {
        if (existingUser.email === email) {
          throw new Error('User with this email already exists');
        }
        if (existingUser.username === username) {
          throw new Error('Username already taken');
        }
      }

      // 1. Create user account
      const userResult = await this.dataSource
        .createQueryBuilder()
        .insert()
        .into('users')
        .values({
          fullName: full_name,
          username,
          email,
          phone,
          password: hashedPassword,
          role: 'recruiter',
          status: 'active',
          created_at: new Date(),
          updated_at: new Date(),
        })
        .execute();

      const userId = userResult.identifiers[0].id;

      // 2. Create company record
      const companyResult = await this.dataSource
        .createQueryBuilder()
        .insert()
        .into('companies')
        .values({
          name,
          about,
          vision,
          website,
          location,
          founded_at: founded_at ? new Date(founded_at) : null,
          company_size_id: company_size_id ? parseInt(company_size_id) : null,
          organization_type_id: organization_type_id
            ? parseInt(organization_type_id)
            : null,
          industry_id: industry_id ? parseInt(industry_id) : null,
          user_id: userId,
          country_id: country_id ? parseInt(country_id) : null,
          state_id: state_id ? parseInt(state_id) : null,
          city_id: city_id ? parseInt(city_id) : null,
          pincode,
          latitude: latitude ? parseFloat(latitude) : null,
          longitude: longitude ? parseFloat(longitude) : null,
          gst_number: gst_number || null,
          cin_number: cin_number || null,
          featured: 0,
          status: 1,
          is_deleted: 0,
          franchise_id: franchiseId,
          created_at: new Date(),
          updated_at: new Date(),
        })
        .execute();

      const companyId = companyResult.identifiers[0].id;

      return {
        success: true,
        message: 'Company added successfully',
        data: {
          userId,
          companyId,
          user: {
            id: userId,
            fullName: full_name,
            username,
            email,
            phone,
            password: password, // Include the original password for display
            role: 'recruiter',
            status: 'active',
          },
          company: {
            id: companyId,
            name,
            location,
            franchise_id: franchiseId,
            user_id: userId,
          },
        },
      };
    } catch (error) {
      console.error('Error adding company for franchise:', error);
      return {
        success: false,
        message: 'Failed to add company',
        error: error.message,
      };
    }
  }

  async findOne(id: number): Promise<Franchises> {
    return this.franchiseRepository.findOne({ where: { id } });
  }

  async findAll(): Promise<Franchises[]> {
    return this.franchiseRepository.find();
  }

  async create(data: Partial<Franchises>): Promise<Franchises> {
    const franchise = this.franchiseRepository.create(data);
    return this.franchiseRepository.save(franchise);
  }

  async update(id: number, data: Partial<Franchises>): Promise<Franchises> {
    await this.franchiseRepository.update(id, data);
    return this.findOne(id);
  }

  async remove(id: number): Promise<void> {
    await this.franchiseRepository.delete(id);
  }
  //admin franchise add
  async createFranchiseWithUser(data: any) {
    // console.log('Creating franchise with user:', data);
    return await this.dataSource.transaction(async (manager) => {
      // 1️⃣ Hash password
      // const hashedPassword = await bcrypt.hash(data.password, 10);

      // 2️⃣ Insert into users table
      const userInsert = await manager
        .createQueryBuilder()
        .insert()
        .into(UserEntity)
        .values({
          username: data.email,
          email: data.email,
          password: data.password,
          fullName: data.name,
          phone: data.contactNumber || null,
          role: 'franchise', // fixed role
          status: 'active',
        })
        .execute();

      const userId = userInsert.identifiers[0].id;

      // 3️⃣ Insert into franchises table
      await manager
        .createQueryBuilder()
        .insert()
        .into(Franchises)
        .values({
          userId: userId,
          franchiseName: data.name,
          companyName: data.company || null,
          email: data.email,
          contactNo: data.contactNumber || null,
          address: data.address || null,
          gstNo: data.gstNo || null,
          serviceArea: data.serviceArea
            ? JSON.stringify(data.serviceArea)
            : null,
          status: 'active',
        })
        .execute();

      return {
        success: true,
        message: 'Franchise and user created successfully',
        userId,
      };
    });
  }

  async getFranchisesWithUser(
    page: number = 1,
    limit: number = 10,
    searchTerm?: string,
    statusFilter?: string,
  ) {
    const offset = (page - 1) * limit;
    // console.log(
    //   `Fetching franchises - Page: ${page}, Limit: ${limit}, Offset: ${offset}`,
    // );
    // 🔹 Query for data with pagination and filtering
    const query = this.franchiseRepository
      .createQueryBuilder('f')
      .leftJoin('users', 'u', 'u.id = f.user_id')
      .select([
        'f.id AS franchiseId',
        'f.franchise_name AS franchiseName',
        'f.company_name AS companyName',
        'f.email AS f_email',
        'f.contact_no AS contactNo',
        'f.address AS f_address',
        'f.gst_no AS gstNo',
        'f.service_area AS serviceArea',
        'f.status AS f_status',
        'u.id AS userId',
        'u.username AS u_username',
        'u.email AS userEmail',
        'u.full_name AS fullName',
        'u.role AS u_role',
        'u.status AS userStatus',
        'u.password AS password', // optional
      ])
      .addSelect(
        '(SELECT COUNT(*) FROM companies c WHERE c.franchise_id = f.id AND c.is_deleted = 0)',
        'companiesCount',
      );

    // Apply search filter
    if (searchTerm) {
      query.andWhere(
        '(f.franchise_name LIKE :searchTerm OR f.company_name LIKE :searchTerm OR f.email LIKE :searchTerm)',
        { searchTerm: `%${searchTerm}%` },
      );
    }

    // Apply status filter
    if (statusFilter && statusFilter !== 'all') {
      query.andWhere('f.status = :status', { status: statusFilter });
    }

    // Sort by creation date descending to show newest franchises first
    query.orderBy('f.created_at', 'DESC');

    query.limit(limit).offset(offset);

    const data = await query.getRawMany();

    // 🔹 Separate query just for total count with same filters
    const countQuery = this.franchiseRepository
      .createQueryBuilder('f')
      .leftJoin('users', 'u', 'u.id = f.user_id');

    // Apply same filters to count query
    if (searchTerm) {
      countQuery.andWhere(
        '(f.franchise_name LIKE :searchTerm OR f.company_name LIKE :searchTerm OR f.email LIKE :searchTerm)',
        { searchTerm: `%${searchTerm}%` },
      );
    }

    if (statusFilter && statusFilter !== 'all') {
      countQuery.andWhere('f.status = :status', { status: statusFilter });
    }

    const total = await countQuery.getCount();

    // Get franchise stats
    const franchiseStats = await this.franchiseRepository
      .createQueryBuilder('f')
      .select([
        'COUNT(f.id) as totalFranchises',
        "SUM(CASE WHEN f.status = 'active' THEN 1 ELSE 0 END) as activeFranchises",
        "SUM(CASE WHEN f.status = 'inactive' THEN 1 ELSE 0 END) as inactiveFranchises",
      ])
      .getRawOne();

    // Get company stats (all companies in the system)
    const companyStats = await this.companyRepository
      .createQueryBuilder('c')
      .select([
        'COUNT(c.id) as totalCompanies',
        'SUM(CASE WHEN c.status = 1 THEN 1 ELSE 0 END) as activeCompanies',
        'SUM(CASE WHEN c.status = 0 THEN 1 ELSE 0 END) as inactiveCompanies',
      ])
      .where('c.is_deleted = 0')
      .getRawOne();

    // Get franchise company stats (only companies that belong to franchises)
    const franchiseCompanyStats = await this.companyRepository
      .createQueryBuilder('c')
      .select([
        'COUNT(c.id) as totalCompanies',
        'SUM(CASE WHEN c.status = 1 THEN 1 ELSE 0 END) as activeCompanies',
        'SUM(CASE WHEN c.status = 0 THEN 1 ELSE 0 END) as inactiveCompanies',
      ])
      .where('c.is_deleted = 0')
      .andWhere('c.franchise_id IS NOT NULL')
      .getRawOne();

    return {
      data,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
      stats: {
        totalFranchises: Number(franchiseStats?.totalFranchises ?? 0),
        activeFranchises: Number(franchiseStats?.activeFranchises ?? 0),
        inactiveFranchises: Number(franchiseStats?.inactiveFranchises ?? 0),
        // Company stats (all companies in the system)
        totalCompanies: Number(companyStats?.totalCompanies ?? 0),
        activeCompanies: Number(companyStats?.activeCompanies ?? 0),
        inactiveCompanies: Number(companyStats?.inactiveCompanies ?? 0),
        // Franchise company stats (only companies added by franchises)
        franchiseTotalCompanies: Number(
          franchiseCompanyStats?.totalCompanies ?? 0,
        ),
        franchiseActiveCompanies: Number(
          franchiseCompanyStats?.activeCompanies ?? 0,
        ),
        franchiseInactiveCompanies: Number(
          franchiseCompanyStats?.inactiveCompanies ?? 0,
        ),
        // Placeholders
        totalJobPosts: 0,
        totalApplications: 0,
      },
    };
  }

  async updateFranchiseWithUser(id: number, data: any) {
    // console.log(`Updating franchise (ID: ${id}) with data:`, data);

    return await this.dataSource.transaction(async (manager) => {
      // First, find the existing franchise to get userId
      const existing = await manager
        .createQueryBuilder(Franchises, 'f')
        .where('f.id = :id', { id })
        .getOne();

      if (!existing) {
        throw new Error(`Franchise with ID ${id} not found`);
      }

      const userId = existing.userId;

      // 1️⃣ Update user table
      await manager
        .createQueryBuilder()
        .update(UserEntity)
        .set({
          username: data.email,
          email: data.email,
          // password: data.password ? await bcrypt.hash(data.password, 10) : undefined, // optional
          password: data.password || undefined,
          fullName: data.name,
          phone: data.contactNumber || null,
          role: 'franchise',
          status: data.userStatus || 'active',
        })
        .where('id = :userId', { userId })
        .execute();

      // 2️⃣ Update franchise table
      await manager
        .createQueryBuilder()
        .update(Franchises)
        .set({
          franchiseName: data.name,
          companyName: data.company || null,
          email: data.email,
          contactNo: data.contactNumber || null,
          address: data.address || null,
          gstNo: data.gstNo || null,
          serviceArea: data.serviceArea
            ? JSON.stringify(data.serviceArea)
            : null,
          status: data.status || 'active',
        })
        .where('id = :id', { id })
        .execute();

      return {
        success: true,
        message: 'Franchise and user updated successfully',
        franchiseId: id,
        userId,
      };
    });
  }

  // franchise.service.ts
  async updateFranchiseStatus(
    franchiseId: number,
    status: 'active' | 'inactive',
  ) {
    return await this.dataSource.transaction(async (manager) => {
      // 1️⃣ Find the userId for the given franchise
      const franchise = await manager
        .getRepository(Franchises)
        .findOne({ where: { id: franchiseId } });

      if (!franchise) {
        return { success: false, message: 'Franchise not found' };
      }

      const userId = franchise.userId;

      // 2️⃣ Update franchise status
      await manager
        .createQueryBuilder()
        .update(Franchises)
        .set({ status })
        .where('id = :id', { id: franchiseId })
        .execute();

      // 3️⃣ Update user status
      await manager
        .createQueryBuilder()
        .update(UserEntity)
        .set({ status })
        .where('id = :id', { id: userId })
        .execute();

      return {
        success: true,
        message: `Franchise & user status updated to ${status}`,
      };
    });
  }
  async getFranchiseDetails(franchiseId: number) {
    try {
      // 1️⃣ Franchise basic info
      const franchise = await this.dataSource
        .createQueryBuilder()
        .select([
          'f.id',
          'f.franchise_name',
          'f.company_name',
          'f.email',
          'f.contact_no',
          'f.address',
          'f.postal_code',
          'f.area_id',
          'f.country_id',
          'f.state_id',
          'f.city_id',
          'f.gst_no',
          'f.service_area',
          'f.status',
          'f.created_at',
          'f.updated_at',
          'u.full_name as owner_name',
          'u.username as owner_username',
          'u.email as owner_email',
          'u.phone as owner_phone',
          'u.password as password', // optional, remove if not needed
          'city.city_name as city_name',
          'state.state_name as state_name',
          'country.country_name as country_name',
          'area.area as area_name',
        ])
        .from('franchises', 'f')
        .leftJoin('users', 'u', 'u.id = f.user_id')
        .leftJoin('city', 'city', 'city.city_id = f.city_id')
        .leftJoin('state', 'state', 'state.state_id = f.state_id')
        .leftJoin('country', 'country', 'country.country_id = f.country_id')
        .leftJoin('area', 'area', 'area.id = f.area_id')
        .where('f.id = :franchiseId', { franchiseId })
        .getRawOne();

      if (!franchise) {
        return { success: false, message: 'Franchise not found' };
      }

      // 2️⃣ Companies count under this franchise
      const companiesCount = await this.dataSource
        .createQueryBuilder()
        .select('COUNT(*)', 'count')
        .from('companies', 'c')
        .where('c.franchise_id = :franchiseId', { franchiseId })
        .andWhere('c.is_deleted = 0')
        .getRawOne();

      // 3️⃣ Jobs count (open)
      const jobPostsCount = await this.dataSource
        .createQueryBuilder()
        .select('COUNT(*)', 'count')
        .from('job_posts', 'jp')
        .innerJoin('companies', 'c', 'c.id = jp.company_id')
        .where('c.franchise_id = :franchiseId', { franchiseId })
        .andWhere('jp.status = :status', { status: 'open' })
        .getRawOne();

      // 4️⃣ Applications count
      const applicationsCount = await this.dataSource
        .createQueryBuilder()
        .select('COUNT(*)', 'count')
        .from('job_applications', 'ja')
        .innerJoin('job_posts', 'jp', 'jp.id = ja.job_post_id')
        .innerJoin('companies', 'c', 'c.id = jp.company_id')
        .where('c.franchise_id = :franchiseId', { franchiseId })
        .andWhere('jp.status = :status', { status: 'open' })
        .getRawOne();

      // 5️⃣ Views count
      const viewsCount = await this.dataSource
        .createQueryBuilder()
        .select('COUNT(*)', 'count')
        .from('job_views', 'jv')
        .innerJoin('job_posts', 'jp', 'jp.id = jv.job_post_id')
        .innerJoin('companies', 'c', 'c.id = jp.company_id')
        .where('c.franchise_id = :franchiseId', { franchiseId })
        .andWhere('jp.status = :status', { status: 'open' })
        .getRawOne();

      // 6️⃣ Recent Companies
      const recentCompanies = await this.dataSource
        .createQueryBuilder()
        .select([
          'c.id',
          'c.name',
          'c.logo',
          'c.status',
          'c.location',
          'c.created_at',
          'u.full_name as recruiter_name',
          'u.email as recruiter_email',
        ])
        .from('companies', 'c')
        .leftJoin('users', 'u', 'u.id = c.user_id')
        .addSelect(
          '(SELECT COUNT(*) FROM job_posts jp WHERE jp.company_id = c.id)',
          'jobPosts',
        )
        .addSelect(
          '(SELECT COUNT(*) FROM job_applications ja INNER JOIN job_posts jp ON jp.id = ja.job_post_id WHERE jp.company_id = c.id)',
          'applications',
        )
        .addSelect(
          '(SELECT COUNT(*) FROM job_views jv INNER JOIN job_posts jp ON jp.id = jv.job_post_id WHERE jp.company_id = c.id)',
          'views',
        )
        .where('c.franchise_id = :franchiseId', { franchiseId })
        .andWhere('c.is_deleted = 0')
        .orderBy('c.created_at', 'DESC')
        .limit(5)
        .getRawMany();

      // 7️⃣ Recent Job Posts under franchise
      const recentJobPosts = await this.dataSource
        .createQueryBuilder()
        .select([
          'jp.id',
          'jp.title',
          'jp.created_at',
          'jp.status',
          'jp.salary_min',
          'jp.salary_max',
          'jt.name as job_type',
          'ci.city_name',
          'st.state_name',
          'co.country_name',
        ])
        .from('job_posts', 'jp')
        .innerJoin('companies', 'c', 'c.id = jp.company_id')
        .leftJoin('job_type', 'jt', 'jt.id = jp.job_type_id')
        .leftJoin('city', 'ci', 'ci.city_id = jp.city_id')
        .leftJoin('state', 'st', 'st.state_id = jp.state_id')
        .leftJoin('country', 'co', 'co.country_id = jp.country_id')
        .where('c.franchise_id = :franchiseId', { franchiseId })
        .andWhere('jp.status = :status', { status: 'open' })
        .orderBy('jp.created_at', 'DESC')
        .limit(5)
        .getRawMany();

      // 8️⃣ Recent Applications under franchise
      const recentApplications = await this.dataSource
        .createQueryBuilder()
        .select([
          'ja.id',
          'ja.applied_at',
          'ja.status',
          'jp.title as job_title',
          'u.full_name as candidate_name',
          'u.email as candidate_email',
        ])
        .from('job_applications', 'ja')
        .innerJoin('job_posts', 'jp', 'jp.id = ja.job_post_id')
        .innerJoin('companies', 'c', 'c.id = jp.company_id')
        .leftJoin('seeker_profiles', 'sp', 'sp.id = ja.seeker_id')
        .leftJoin('users', 'u', 'u.id = sp.user_id')
        .where('c.franchise_id = :franchiseId', { franchiseId })
        .andWhere('jp.status = :status', { status: 'open' })
        .orderBy('ja.applied_at', 'DESC')
        .limit(5)
        .getRawMany();

      return {
        success: true,
        data: {
          ...franchise,
          companies: parseInt(companiesCount?.count || '0'),
          jobPosts: parseInt(jobPostsCount?.count || '0'),
          applications: parseInt(applicationsCount?.count || '0'),
          views: parseInt(viewsCount?.count || '0'),
          recentCompanies,
          recentJobPosts,
          recentApplications,
        },
      };
    } catch (error) {
      return {
        success: false,
        message: 'Failed to fetch franchise details',
        error: error.message,
      };
    }
  }
}
