import { Injectable, NotFoundException, ConflictException } from '@nestjs/common';
import { PrismaService } from '@core/database/prisma/prisma.service';
import { CreateRegionDto, UpdateRegionDto, RegionQueryDto } from './dto/region.dto';

@Injectable()
export class RegionsService {
  constructor(private prisma: PrismaService) {}

  /**
   * Create a new region
   */
  async create(createRegionDto: CreateRegionDto) {
    const { code } = createRegionDto;

    // Check if code exists
    const existing = await this.prisma.region.findUnique({
      where: { code },
    });

    if (existing) {
      throw new ConflictException({
        statusCode: 409,
        message: '区域代码已存在',
        error: 'REGION_CODE_EXISTS',
      });
    }

    return this.prisma.region.create({
      data: {
        ...createRegionDto,
        isActive: createRegionDto.isActive ?? true,
        order: createRegionDto.order ?? 0,
      },
    });
  }

  /**
   * Find all regions
   */
  async findAll(query?: RegionQueryDto) {
    const where: any = { deletedAt: null };

    if (query?.keyword) {
      where.OR = [
        { code: { contains: query.keyword, mode: 'insensitive' } },
        { name: { contains: query.keyword, mode: 'insensitive' } },
        { nameEn: { contains: query.keyword, mode: 'insensitive' } },
        { nameZh: { contains: query.keyword, mode: 'insensitive' } },
      ];
    }

    if (query?.isActive !== undefined) {
      where.isActive = query.isActive;
    }

    const regions = await this.prisma.region.findMany({
      where,
      include: {
        // v2.1: Region 关联 OrganizationRegion，不再直接关联 Department
        organizationRegions: {
          where: {
            isDefault: true, // 只获取默认组织
            organization: {
              deletedAt: null,
            },
          },
          include: {
            organization: {
              select: {
                id: true,
                name: true,
                code: true,
              },
            },
          },
          take: 1, // 每个区域最多一个默认组织
        },
      },
      orderBy: [{ order: 'asc' }, { name: 'asc' }],
    });

    // v2.1: 统计 primaryRegionId 为该区域的组织数量
    const orgCounts = await Promise.all(
      regions.map(async (region) => {
        const count = await this.prisma.organization.count({
          where: {
            primaryRegionId: region.id,
            deletedAt: null,
          },
        });
        return { regionId: region.id, count };
      })
    );

    const orgCountMap = new Map(orgCounts.map((oc) => [oc.regionId, oc.count]));

    return regions.map(region => ({
      ...region,
      organizationCount: orgCountMap.get(region.id) || 0,
      // v2.1: 从 organizationRegions 获取默认组织
      defaultOrganization: (region as any).organizationRegions[0]?.organization || null,
    }));
  }

  /**
   * Find active regions (for dropdown)
   */
  async findActive() {
    return this.prisma.region.findMany({
      where: { isActive: true, deletedAt: null },
      orderBy: [{ order: 'asc' }, { name: 'asc' }],
    });
  }

  /**
   * Find region by ID
   */
  async findOne(id: string) {
    const region = await this.prisma.region.findUnique({
      where: { id, deletedAt: null },
      include: {
        // v2.1: Region 关联 OrganizationRegion
        organizationRegions: {
          include: {
            organization: {
              select: {
                id: true,
                code: true,
                name: true,
              },
            },
          },
        },
        // v2.1: 主要区域的组织
        primaryOrganizations: {
          where: { deletedAt: null },
          select: {
            id: true,
            code: true,
            name: true,
          },
        },
      },
    });

    if (!region) {
      throw new NotFoundException('Region not found');
    }

    return {
      ...region,
      // v2.1: 从 organizationRegions 获取组织列表
      organizations: (region as any).organizationRegions.map((or: any) => ({
        ...or.organization,
        isDefault: or.isDefault,
      })),
    };
  }

  /**
   * Find region by code
   */
  async findByCode(code: string) {
    const region = await this.prisma.region.findUnique({
      where: { code, deletedAt: null },
    });

    if (!region) {
      throw new NotFoundException(`Region with code "${code}" not found`);
    }

    return region;
  }

  /**
   * Update region
   */
  async update(id: string, updateRegionDto: UpdateRegionDto) {
    const region = await this.prisma.region.findUnique({
      where: { id, deletedAt: null },
    });

    if (!region) {
      throw new NotFoundException('Region not found');
    }

    return this.prisma.region.update({
      where: { id },
      data: updateRegionDto,
    });
  }

  /**
   * Delete region (soft delete)
   */
  async remove(id: string) {
    const region = await this.prisma.region.findUnique({
      where: { id, deletedAt: null },
    });

    if (!region) {
      throw new NotFoundException('Region not found');
    }

    // v2.1: 检查是否有组织使用此区域作为主区域
    const orgCount = await this.prisma.organization.count({
      where: {
        primaryRegionId: id,
        deletedAt: null,
      },
    });

    if (orgCount > 0) {
      throw new ConflictException('该区域下有组织，无法删除');
    }

    // 检查是否有组织区域关联
    const orgRegionCount = await this.prisma.organizationRegion.count({
      where: { regionId: id },
    });

    if (orgRegionCount > 0) {
      throw new ConflictException('该区域下有组织，无法删除');
    }

    await this.prisma.region.update({
      where: { id },
      data: { deletedAt: new Date() },
    });

    return { message: 'Region deleted successfully' };
  }

  /**
   * Get region statistics
   */
  async getStats(id: string) {
    const region = await this.prisma.region.findUnique({
      where: { id, deletedAt: null },
    });

    if (!region) {
      throw new NotFoundException('Region not found');
    }

    // v2.1: 统计 primaryRegionId 为该区域的组织数量
    const organizationCount = await this.prisma.organization.count({
      where: {
        primaryRegionId: id,
        deletedAt: null,
      },
    });

    // v2.1: 获取该区域的所有组织
    const organizations = await this.prisma.organization.findMany({
      where: { 
        primaryRegionId: id,
        deletedAt: null,
      },
      select: { id: true },
    });
    const orgIds = organizations.map(org => org.id);

    // v2.1: 统计这些组织下的所有部门数
    let departmentCount = 0;
    if (orgIds.length > 0) {
      departmentCount = await this.prisma.department.count({
        where: {
          organizationId: { in: orgIds },
          deletedAt: null,
        },
      });
    }

    // v2.1: 统计该区域所有组织及其部门下的活跃用户数
    let userCount = 0;
    if (orgIds.length > 0) {
      // 使用原生 SQL 递归查询所有子部门的用户
      const result = await this.prisma.$queryRaw<[{ count: bigint }]>`
        WITH RECURSIVE dept_tree AS (
          -- 基础查询：顶级组织
          SELECT id
          FROM corp_hr.departments
          WHERE id = ANY(${orgIds}::uuid[])
            AND deleted_at IS NULL
          
          UNION ALL
          
          -- 递归查询：所有子部门
          SELECT d.id
          FROM corp_hr.departments d
          INNER JOIN dept_tree dt ON d.parent_id = dt.id
          WHERE d.deleted_at IS NULL
        )
        SELECT COUNT(DISTINCT ud.user_id)::int as count
        FROM corp_hr.user_departments ud
        INNER JOIN dept_tree dt ON ud.department_id = dt.id
        INNER JOIN platform_iam.users u ON ud.user_id = u.id
        WHERE ud.left_at IS NULL
          AND u.deleted_at IS NULL
          AND u.status = 'ACTIVE'
      `;
      userCount = Number(result[0]?.count || 0);
    }

    return {
      organizationCount,
      departmentCount,
      userCount,
    };
  }

  /**
   * Set default organization for a region
   * 
   * v2.1: 现在操作 OrganizationRegion 表，而不是 DepartmentRegion
   */
  async setDefaultOrganization(regionId: string, organizationId: string | null) {
    // Verify region exists
    const region = await this.prisma.region.findUnique({
      where: { id: regionId, deletedAt: null },
    });

    if (!region) {
      throw new NotFoundException('Region not found');
    }

    if (organizationId) {
      // Verify organization exists
      const organization = await this.prisma.organization.findUnique({
        where: { id: organizationId, deletedAt: null },
      });

      if (!organization) {
        throw new NotFoundException('Organization not found');
      }

      // Check if organization is associated with this region
      const association = await this.prisma.organizationRegion.findFirst({
        where: {
          organizationId,
          regionId,
        },
      });

      if (!association) {
        throw new ConflictException('Organization is not associated with this region');
      }

      // Clear existing default for this region
      await this.prisma.organizationRegion.updateMany({
        where: { regionId, isDefault: true },
        data: { isDefault: false },
      });

      // Set new default
      await this.prisma.organizationRegion.updateMany({
        where: { organizationId, regionId },
        data: { isDefault: true },
      });

      return {
        message: 'Default organization set successfully',
        regionId,
        organizationId,
      };
    } else {
      // Clear default (set to null)
      await this.prisma.organizationRegion.updateMany({
        where: { regionId, isDefault: true },
        data: { isDefault: false },
      });

      return {
        message: 'Default organization cleared successfully',
        regionId,
        organizationId: null,
      };
    }
  }
}