import { Injectable, OnModuleDestroy, OnModuleInit } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import {
  DataSource,
  EntityManager,
  FindOperator,
  In,
  LessThan,
  LessThanOrEqual,
  Like,
  MoreThan,
  MoreThanOrEqual,
  Not,
} from 'typeorm';
import {
  AttendanceEntity,
  AttendanceSettingsEntity,
  WorkDayScheduleEntity,
  IpWhitelistEntity,
  QrAttendancePointEntity,
  TeacherDeviceEntity,
  DeviceChangeRequestEntity,
  CompanyEntity,
  DepartmentEntity,
  DesignationEntity,
  EducationEntity,
  EmployementTypeEntity,
  EventEntity,
  HiringSourceEntity,
  HolidayEntity,
  JobApplicationEntity,
  JobPostingEntity,
  LeaveEntity,
  LeavePolicyEntity,
  LocationEntity,
  MemberEntity,
  NoticeEntity,
  NotificationEntity,
  NotificationReceiverEntity,
  NotificationTargetEntity,
  OwnerEntity,
  RecruitmentTaskEntity,
  RolePermissionEntity,
  AccessRoleEntity,
  TenantApiKeyEntity,
  WorkExperienceEntity,
  WorkingScheduleEntity,
  OnboardingTemplateEntity,
  OnboardingTemplateTaskEntity,
  OnboardingCaseEntity,
  OnboardingTaskEntity,
  OnboardingDocumentEntity,
  ProvisioningItemEntity,
  BuddyAssignmentEntity,
  OnboardingSurveyEntity,
  ProbationEntity,
  ProbationReviewEntity,
  OffboardingTemplateEntity,
  OffboardingTemplateTaskEntity,
  OffboardingCaseEntity,
  OffboardingTaskEntity,
  EquipmentReturnEntity,
  ExitInterviewTemplateEntity,
  ExitInterviewEntity,
  FinalSettlementEntity,
  FinalSettlementItemEntity,
} from '../database/entities';
import { logger } from '../logger/logger.config';

type QueryShape = Record<string, any>;

class EntityAccessor<T extends object> {
  constructor(
    private readonly dataSource: DataSource,
    private readonly entity: new () => T,
  ) {}

  private get repository() {
    return this.dataSource.getRepository(this.entity);
  }

  private get entityName() {
    return this.repository.metadata.tableName;
  }

  private translateScalar(value: any): any {
    if (value === null || value === undefined) return value;
    if (value instanceof Date) return value;
    if (value instanceof FindOperator) return value; // ✅ THIS IS THE KEY FIX
    if (Array.isArray(value))
      return value.map((item) => this.translateScalar(item));
    if (typeof value !== 'object') return value;

    if (value.contains !== undefined)
      return Like(`%${String(value.contains)}%`);
    if (value.in !== undefined) return In(value.in);
    if (value.gte !== undefined) return MoreThanOrEqual(value.gte);
    if (value.lte !== undefined) return LessThanOrEqual(value.lte);
    if (value.gt !== undefined) return MoreThan(value.gt);
    if (value.lt !== undefined) return LessThan(value.lt);
    if (value.not !== undefined) return Not(this.translateScalar(value.not));
    if (value.some !== undefined) return this.translateScalar(value.some);
    if (value.connect !== undefined) return this.translateScalar(value.connect);
    if (value.set !== undefined) return this.translateScalar(value.set);
    if (value.create !== undefined) return this.translateScalar(value.create);

    const translated: QueryShape = {};
    for (const [key, nestedValue] of Object.entries(value)) {
      translated[key] = this.translateScalar(nestedValue);
    }
    return translated;
  }
  private translateWhere(where: QueryShape | undefined): any {
    if (!where) return undefined;
    if (Array.isArray(where)) {
      return where.map((item) => this.translateWhere(item));
    }

    const { OR, AND, NOT, ...rest } = where;
    const translatedRest = this.translateScalar(rest);

    if (Array.isArray(OR) && OR.length > 0) {
      return OR.map((item) => this.translateWhere({ ...rest, ...item }));
    }

    if (Array.isArray(AND) && AND.length > 0) {
      return AND.map((item) => this.translateWhere({ ...rest, ...item }));
    }

    if (NOT && typeof NOT === 'object') {
      for (const [key, value] of Object.entries(NOT)) {
        translatedRest[key] = this.translateScalar({ not: value });
      }
    }

    return translatedRest;
  }

  private translateOrder(orderBy: any): any {
    if (!orderBy) return undefined;
    if (Array.isArray(orderBy)) {
      const mergedOrder: Record<string, any> = {};
      for (const item of orderBy) {
        if (typeof item === 'object' && item !== null) {
          const translatedItem = this.translateOrder(item);
          Object.assign(mergedOrder, translatedItem);
        }
      }
      return Object.keys(mergedOrder).length > 0 ? mergedOrder : undefined;
    }
    if (typeof orderBy === 'object' && orderBy !== null) {
      const result: Record<string, any> = {};
      for (const [key, val] of Object.entries(orderBy)) {
        if (typeof val === 'string') {
          result[key] = val.toUpperCase();
        } else if (typeof val === 'object' && val !== null) {
          result[key] = this.translateOrder(val);
        } else {
          result[key] = val;
        }
      }
      return result;
    }
    return orderBy;
  }

  private collectRelations(shape: any): any {
    if (!shape || typeof shape !== 'object') {
      return undefined;
    }

    const relations: QueryShape = {};
    for (const [key, value] of Object.entries(shape)) {
      if (['_count', 'where', 'orderBy', 'order', 'take', 'skip', 'select'].includes(key)) continue;
      if (value === true) {
        relations[key] = true;
        continue;
      }

      if (value && typeof value === 'object') {
        const nested =
          (value as QueryShape).select ??
          (value as QueryShape).include ??
          value;
        const nestedRelations = this.collectRelations(nested);
        relations[key] =
          nestedRelations && Object.keys(nestedRelations).length > 0
            ? nestedRelations
            : true;
      }
    }

    return Object.keys(relations).length > 0 ? relations : undefined;
  }

  private normalizePersistValue(value: any): any {
    if (value === null || value === undefined) return value;
    if (value instanceof Date) return value;
    if (Array.isArray(value))
      return value.map((item) => this.normalizePersistValue(item));
    if (typeof value !== 'object') return value;

    if (value.disconnect === true) {
      return null;
    }

    if (value.connect !== undefined) {
      return this.normalizePersistValue(value.connect);
    }

    if (value.set !== undefined) {
      return this.normalizePersistValue(value.set);
    }

    if (value.create !== undefined) {
      return this.normalizePersistValue(value.create);
    }

    const normalized: QueryShape = {};
    for (const [key, nestedValue] of Object.entries(value)) {
      normalized[key] = this.normalizePersistValue(nestedValue);
    }
    return normalized;
  }

  private normalizePersistData(data: QueryShape): QueryShape {
    const normalized: QueryShape = {};
    for (const [key, value] of Object.entries(data ?? {})) {
      if (this.entityName === 'events' && key === 'members') {
        continue;
      }
      normalized[key] = this.normalizePersistValue(value);
    }
    return normalized;
  }

  private extractRelationIds(value: any): string[] | null {
    if (value === null || value === undefined) return null;

    const relationItems = Array.isArray(value)
      ? value
      : Array.isArray(value.set)
        ? value.set
        : Array.isArray(value.connect)
          ? value.connect
          : null;

    if (!relationItems) return null;

    return relationItems
      .map((item: any) => {
        if (item === null || item === undefined) return null;
        if (typeof item === 'string' || typeof item === 'number') {
          return String(item);
        }
        if (typeof item === 'object' && 'id' in item && item.id) {
          return String(item.id);
        }
        return null;
      })
      .filter((id): id is string => Boolean(id));
  }

  private async syncManyToManyRelations(
    entityId: string,
    existing: any,
    payload: QueryShape,
  ) {
    const manyToManyRelations = this.repository.metadata.manyToManyRelations;

    for (const relation of manyToManyRelations) {
      if (
        !Object.prototype.hasOwnProperty.call(payload, relation.propertyName)
      ) {
        continue;
      }

      const desiredIds = this.extractRelationIds(
        payload[relation.propertyName],
      );
      if (desiredIds === null) {
        continue;
      }

      const currentItems = Array.isArray(existing?.[relation.propertyName])
        ? existing[relation.propertyName]
        : [];
      const currentIds = currentItems
        .map((item: any) => item?.id)
        .filter(Boolean)
        .map(String);

      const removedIds = currentIds.filter((id) => !desiredIds.includes(id));
      const addedIds = desiredIds.filter((id) => !currentIds.includes(id));

      const relationQuery = this.dataSource
        .createQueryBuilder()
        .relation(this.entity as any, relation.propertyName)
        .of(entityId);

      if (removedIds.length > 0) {
        await relationQuery.remove(removedIds);
      }

      if (addedIds.length > 0) {
        await relationQuery.add(addedIds);
      }
    }
  }

  private async reload(saved: any, args: QueryShape = {}) {
    const id = saved?.id;
    if (!id) return saved;

    const relations = this.collectRelations(args.include);
    const reloaded = await this.repository.findOne({
      where: { id } as any,
      relations,
    });

    return reloaded ?? saved;
  }

  private async syncEventMembers(eventId: string, payload: any) {
    if (this.entityName !== 'events' || !payload?.members) return;

    const memberRepository = this.dataSource.getRepository(MemberEntity);
    const relationPayload = payload.members;

    let memberIds: string[] = [];
    if (Array.isArray(relationPayload.connect)) {
      memberIds = relationPayload.connect
        .map((item: any) => item.id)
        .filter(Boolean);
    } else if (Array.isArray(relationPayload.set)) {
      memberIds = relationPayload.set
        .map((item: any) => item.id)
        .filter(Boolean);
      const existingMembers = await memberRepository.find({
        where: { eventsId: eventId } as any,
        select: { id: true } as any,
      });
      const existingIds = existingMembers.map((member) => member.id);
      const removedIds = existingIds.filter(
        (memberId) => !memberIds.includes(memberId),
      );
      if (removedIds.length > 0) {
        await memberRepository.update(
          { id: In(removedIds) } as any,
          { eventsId: null } as any,
        );
      }
    }

    if (memberIds.length > 0) {
      await memberRepository.update(
        { id: In(memberIds) } as any,
        { eventsId: eventId } as any,
      );
    }
  }

  async findMany(args: QueryShape = {}) {
    const where = this.translateWhere(args.where);
    const order = this.translateOrder(args.orderBy);
    const relations = this.collectRelations(args.include);

    let rows = await this.repository.find({
      where,
      relations,
      order,
      skip: args.skip,
      take: args.take,
    });

    if (
      this.entityName === 'departments' &&
      args.include?._count?.select?.members
    ) {
      const memberRepository = this.dataSource.getRepository(MemberEntity);
      const departmentIds = rows.map((row: any) => row.id).filter(Boolean);
      const memberRows = departmentIds.length
        ? await memberRepository.find({
            where: {
              department_id: In(departmentIds),
              ...(args.where?.owner_id
                ? { owner_id: args.where.owner_id }
                : {}),
            } as any,
            select: { department_id: true } as any,
          })
        : [];

      const counts = memberRows.reduce<Record<string, number>>(
        (acc, member) => {
          if (!member.department_id) return acc;
          acc[member.department_id] = (acc[member.department_id] ?? 0) + 1;
          return acc;
        },
        {},
      );

      rows = rows.map((row: any) => ({
        ...row,
        _count: {
          members: counts[row.id] ?? 0,
        },
      }));
    }

    return rows;
  }

  async findFirst(args: QueryShape = {}) {
    const rows = await this.findMany({ ...args, take: 1 });
    return rows[0] ?? null;
  }

  async findUnique(args: QueryShape = {}) {
    return this.findFirst(args);
  }

  async count(args: QueryShape = {}) {
    const where = this.translateWhere(args.where);
    return this.repository.count({ where });
  }

  async create(args: QueryShape = {}) {
    const payload = this.normalizePersistData(args.data ?? {});
    const entity = this.repository.create(payload as any);
    const saved = await this.repository.save(entity as any);

    if (payload?.members && this.entityName === 'events') {
      await this.syncEventMembers(saved.id, payload);
    }

    return this.reload(saved, args);
  }

  async update(args: QueryShape = {}) {
    const existing = await this.findFirst({
      where: args.where,
      include: args.include,
      select: args.select,
    });
    if (!existing) return null;

    const payload = this.normalizePersistData(args.data ?? {});
    const merged = this.repository.merge(existing as any, payload as any);
    const saved = await this.repository.save(merged as any);

    if (payload?.members && this.entityName === 'events') {
      await this.syncEventMembers(saved.id, payload);
    }

    await this.syncManyToManyRelations(saved.id, existing, args.data ?? {});

    return this.reload(saved, args);
  }

  async delete(args: QueryShape = {}) {
    const existing = await this.findFirst({ where: args.where });
    if (!existing) return null;
    await this.repository.remove(existing as any);
    return existing;
  }

  async upsert(args: QueryShape = {}) {
    const existing = await this.findFirst({
      where: args.where,
      include: args.include,
      select: args.select,
    });
    if (existing) {
      return this.update({
        where: args.where,
        data: args.update,
        include: args.include,
        select: args.select,
      });
    }

    return this.create({
      data: { ...(args.create ?? {}) },
      include: args.include,
      select: args.select,
    });
  }

  async createMany(args: QueryShape = {}) {
    const rows = Array.isArray(args.data)
      ? args.data.map((item: any) => this.normalizePersistData(item))
      : [];
    if (rows.length === 0) {
      return { count: 0 };
    }
    await this.repository.insert(rows as any);
    return { count: rows.length };
  }

  async updateMany(args: QueryShape = {}) {
    const payload = this.normalizePersistData(args.data ?? {});
    const result = await this.repository.update(
      this.translateWhere(args.where),
      payload as any,
    );
    return { count: result.affected ?? 0 };
  }

  async deleteMany(args: QueryShape = {}) {
    const result = await this.repository.delete(
      this.translateWhere(args.where),
    );
    return { count: result.affected ?? 0 };
  }

  async groupBy(args: QueryShape = {}) {
    const groupFields: string[] = Array.isArray(args.by) ? args.by : [];
    if (groupFields.length === 0) return [];

    const rows = await this.findMany({ where: args.where });
    const groups = new Map<string, any>();

    for (const row of rows as any[]) {
      const groupKey = groupFields
        .map((field) => String(row[field]))
        .join('::');
      const existing = groups.get(groupKey) ?? {
        ...groupFields.reduce((acc, field) => {
          acc[field] = row[field];
          return acc;
        }, {} as QueryShape),
        _count: { _all: 0 },
      };
      existing._count._all += 1;
      groups.set(groupKey, existing);
    }

    return Array.from(groups.values());
  }

  async aggregate(args: QueryShape = {}) {
    const rows = await this.findMany({ where: args.where });
    const count = rows.length;
    const sumFields = Object.entries(args._sum ?? {}).map(([key]) => key);
    const avgFields = Object.entries(args._avg ?? {}).map(([key]) => key);
    const minFields = Object.entries(args._min ?? {}).map(([key]) => key);
    const maxFields = Object.entries(args._max ?? {}).map(([key]) => key);

    const result: QueryShape = { _count: count };
    for (const field of sumFields) {
      result._sum = result._sum || {};
      result._sum[field] = rows.reduce(
        (acc, row) => acc + (row[field] ?? 0),
        0,
      );
    }
    for (const field of avgFields) {
      result._avg = result._avg || {};
      result._avg[field] =
        count > 0
          ? rows.reduce((acc, row) => acc + (row[field] ?? 0), 0) / count
          : 0;
    }
    for (const field of minFields) {
      result._min = result._min || {};
      result._min[field] = rows.reduce(
        (min, row) => (row[field] < min ? row[field] : min),
        rows[0]?.[field],
      );
    }
    for (const field of maxFields) {
      result._max = result._max || {};
      result._max[field] = rows.reduce(
        (max, row) => (row[field] > max ? row[field] : max),
        rows[0]?.[field],
      );
    }

    return result;
  }
}

@Injectable()
export class DatabaseService implements OnModuleInit, OnModuleDestroy {
  [key: string]: any;

  constructor(@InjectDataSource() public readonly dataSource: DataSource) {
    this.owner = new EntityAccessor(this.dataSource, OwnerEntity);
    this.company = new EntityAccessor(this.dataSource, CompanyEntity);
    this.working_schedule = new EntityAccessor(
      this.dataSource,
      WorkingScheduleEntity,
    );
    this.work_day_schedules = new EntityAccessor(
      this.dataSource,
      WorkDayScheduleEntity,
    );
    this.ip_whitelists = new EntityAccessor(
      this.dataSource,
      IpWhitelistEntity,
    );
    this.attendance_settings = new EntityAccessor(
      this.dataSource,
      AttendanceSettingsEntity,
    );
    this.qr_attendance_points = new EntityAccessor(
      this.dataSource,
      QrAttendancePointEntity,
    );
    this.teacher_devices = new EntityAccessor(
      this.dataSource,
      TeacherDeviceEntity,
    );
    this.device_change_requests = new EntityAccessor(
      this.dataSource,
      DeviceChangeRequestEntity,
    );
    this.leave_policy = new EntityAccessor(this.dataSource, LeavePolicyEntity);
    this.members = new EntityAccessor(this.dataSource, MemberEntity);
    this.attendances = new EntityAccessor(this.dataSource, AttendanceEntity);
    
    this.leaves = new EntityAccessor(this.dataSource, LeaveEntity);
    this.holidays = new EntityAccessor(this.dataSource, HolidayEntity);
    this.events = new EntityAccessor(this.dataSource, EventEntity);
    this.notices = new EntityAccessor(this.dataSource, NoticeEntity);
    this.work_experiences = new EntityAccessor(
      this.dataSource,
      WorkExperienceEntity,
    );
    this.educations = new EntityAccessor(this.dataSource, EducationEntity);
    this.departments = new EntityAccessor(this.dataSource, DepartmentEntity);
    this.designations = new EntityAccessor(this.dataSource, DesignationEntity);
    this.locations = new EntityAccessor(this.dataSource, LocationEntity);
    this.employement_types = new EntityAccessor(
      this.dataSource,
      EmployementTypeEntity,
    );
    this.hiring_sources = new EntityAccessor(
      this.dataSource,
      HiringSourceEntity,
    );
    this.notifications = new EntityAccessor(
      this.dataSource,
      NotificationEntity,
    );
    this.notification_receivers = new EntityAccessor(
      this.dataSource,
      NotificationReceiverEntity,
    );
    this.notification_targets = new EntityAccessor(
      this.dataSource,
      NotificationTargetEntity,
    );
    this.access_roles = new EntityAccessor(this.dataSource, AccessRoleEntity);
    this.role_permissions = new EntityAccessor(
      this.dataSource,
      RolePermissionEntity,
    );
    this.job_postings = new EntityAccessor(this.dataSource, JobPostingEntity);
    this.job_applications = new EntityAccessor(
      this.dataSource,
      JobApplicationEntity,
    );
    this.recruitment_tasks = new EntityAccessor(
      this.dataSource,
      RecruitmentTaskEntity,
    );
    this.tenant_api_keys = new EntityAccessor(
      this.dataSource,
      TenantApiKeyEntity,
    );
    this.onboarding_templates = new EntityAccessor(
      this.dataSource,
      OnboardingTemplateEntity,
    );
    this.onboarding_template_tasks = new EntityAccessor(
      this.dataSource,
      OnboardingTemplateTaskEntity,
    );
    this.onboarding_cases = new EntityAccessor(
      this.dataSource,
      OnboardingCaseEntity,
    );
    this.onboarding_tasks = new EntityAccessor(
      this.dataSource,
      OnboardingTaskEntity,
    );
    this.onboarding_documents = new EntityAccessor(
      this.dataSource,
      OnboardingDocumentEntity,
    );
    this.provisioning_items = new EntityAccessor(
      this.dataSource,
      ProvisioningItemEntity,
    );
    this.buddy_assignments = new EntityAccessor(
      this.dataSource,
      BuddyAssignmentEntity,
    );
    this.onboarding_surveys = new EntityAccessor(
      this.dataSource,
      OnboardingSurveyEntity,
    );
    this.probations = new EntityAccessor(
      this.dataSource,
      ProbationEntity,
    );
    this.probation_reviews = new EntityAccessor(
      this.dataSource,
      ProbationReviewEntity,
    );
    this.offboarding_templates = new EntityAccessor(
      this.dataSource,
      OffboardingTemplateEntity,
    );
    this.offboarding_template_tasks = new EntityAccessor(
      this.dataSource,
      OffboardingTemplateTaskEntity,
    );
    this.offboarding_cases = new EntityAccessor(
      this.dataSource,
      OffboardingCaseEntity,
    );
    this.offboarding_tasks = new EntityAccessor(
      this.dataSource,
      OffboardingTaskEntity,
    );
    this.equipment_returns = new EntityAccessor(
      this.dataSource,
      EquipmentReturnEntity,
    );
    this.exit_interview_templates = new EntityAccessor(
      this.dataSource,
      ExitInterviewTemplateEntity,
    );
    this.exit_interviews = new EntityAccessor(
      this.dataSource,
      ExitInterviewEntity,
    );
    this.final_settlements = new EntityAccessor(
      this.dataSource,
      FinalSettlementEntity,
    );
    this.final_settlement_items = new EntityAccessor(
      this.dataSource,
      FinalSettlementItemEntity,
    );
  }

  async onModuleInit() {
    if (!this.dataSource.isInitialized) {
      await this.dataSource.initialize();
    }
    logger.info('Connected to the database');
  }

  async onModuleDestroy() {
    if (this.dataSource.isInitialized) {
      await this.dataSource.destroy();
    }
    logger.info('Disconnected from the database');
  }

  runInTransaction<T>(
    fn: (manager: EntityManager) => Promise<T>,
  ): Promise<T> {
    return this.dataSource.transaction(fn);
  }
}
