import { BadRequestException, Injectable } from '@nestjs/common';
import { STATUS_ACTIVE } from 'src/constant/global.constant';
import { EntityManager, In } from 'typeorm';
import { UserData } from 'src/module/user/entity/user.entity';
import { MediaDataService } from './media-data.service';
import { ResolverService } from './resolver.service';
import { EntityMasterRepository } from '../repository/entity-master.repository';
import { ReflectionHelper } from '../../../utils/service/reflection-helper.service';
import { ConfigService } from '@nestjs/config';

@Injectable()
export class EntityDynamicService {
  constructor(
    private readonly entityManager: EntityManager,
    private readonly mediaDataService: MediaDataService,
    private readonly resolverService: ResolverService,
    private readonly entityMasterRepo: EntityMasterRepository,
    private readonly reflectionHelper: ReflectionHelper,
    private readonly configService: ConfigService,
  ) {}

  schema = this.configService.get('DB_SCHEMA');

  // -----------------------------
  async createEntity(
    entityType: string,
    entityData: Record<string, any>,
    loggedInUser: any,
    mainID?: number,
  ): Promise<any> {
    const organizationId = loggedInUser.organization_id;
    const enterprise_id = loggedInUser.enterprise_id;

    const tableName = await this.getTableName(entityType, enterprise_id);
    const validAttributes = await this.getAttributeCodes(
      entityType,
      enterprise_id,
    );

    // -------------------------------------------------------
    // AUTO fields
    // -------------------------------------------------------
    entityData.created_date = new Date();

    if (loggedInUser) {
      entityData.created_by = loggedInUser.id;

      if (!entityData.organization_id)
        entityData.organization_id = loggedInUser.organization_id;

      if (!entityData.enterprise_id)
        entityData.enterprise_id = loggedInUser.enterprise_id;

      if (!entityData.level_type)
        entityData.level_type = loggedInUser.level_type;

      if (!entityData.level_id) entityData.level_id = loggedInUser.level_id;

      if (!entityData.entity_type) entityData.entity_type = entityType;
    }

    // -------------------------------------------------------
    // STATUS
    // -------------------------------------------------------
    const listMasterItemsRepo =
      this.reflectionHelper.getRepoService('ListMasterItems');
    const statusList = listMasterItemsRepo.find({
      where: {
        code: STATUS_ACTIVE,
        organization_id: organizationId,
      },
    });

    if (!entityData.status) entityData.status = statusList[0]?.id;

    // -------------------------------------------------------
    // AUTO-CODE GENERATION (POSTGRES SAFE)
    // -------------------------------------------------------
    const entityMaster =
      await this.entityMasterRepo.getEntityByMappedEntityType(
        entityType,
        loggedInUser.enterprise_id,
      );

    if (!entityData.code && entityData.entity_type && entityMaster) {
      // Extract integer suffix
      // const repo = this.reflectionHelper.getRepoService(entityMaster.entity_data_class);

      const result = this.entityManager.query(
        `SELECT MAX(id) as seq_no
         FROM ${this.schema}.${entityMaster.db_table_name}`,
      );

      let maxSeq = Number(result[0]?.max_seq_no) || 0;
      maxSeq++;
      entityData.code = `${entityData.entity_type}${maxSeq}`;
    }

    // -------------------------------------------------------
    // Parent ID
    // -------------------------------------------------------
    if (mainID) {
      entityData.parent_id = mainID;
    }

    // -------------------------------------------------------
    // BYPASS COLUMNS
    // -------------------------------------------------------
    const bypassColumns = [
      'created_date',
      'created_by',
      'organization_id',
      'enterprise_id',
      'level_type',
      'level_id',
      'status',
      'entity_type',
      'code',
      'parent_id',
    ];

    for (const col of bypassColumns) {
      if (!validAttributes.some((a) => a.attribute_key === col)) {
        validAttributes.push({
          attribute_key: col,
          is_hidden: false,
          db_datatype: 'text',
          element_type: 'text',
        });
      }
    }

    // -------------------------------------------------------
    // BUILD INSERT QUERY (POSTGRES FORMAT)
    // -------------------------------------------------------
    const columns: string[] = [];
    const values: any[] = [];
    let idx = 1;
    const placeholders: string[] = [];

    for (const attr of validAttributes) {
      if (attr.attribute_key === 'id') continue;

      columns.push(attr.attribute_key);
      values.push(entityData[attr.attribute_key] ?? null);
      placeholders.push(`$${idx++}`);
    }

    const colList = columns.map((c) => `"${c}"`).join(', ');
    const placeholderList = placeholders.join(', ');

    const sql = `
        INSERT INTO ${this.schema}.${tableName} (${colList})
        VALUES (${placeholderList}) RETURNING id
    `;

    const result = await this.entityManager.query(sql, values);
    return result[0];
  }

  // ----------------------------- get entity with relations

  // ----------------------------- create entity with relations
  async createEntityWithRelation(
    entityType: string,
    data: Record<string, any>,
    loggedInUser: any,
  ): Promise<any> {
    const enterpriseId = loggedInUser.enterprise_id;
    const organizationId = loggedInUser.organization_id;

    const repo = this.reflectionHelper.getRepoService('EntityRelation');

    const getRelation = await repo.find({
      where: {
        enterprise_id: enterpriseId,
        source_entity_type: entityType,
      },
    });

    const { mappedEntities, ...mainData } = data;

    // create main entity
    const createdEntityData = await this.createEntity(
      entityType,
      mainData,
      loggedInUser,
    );
    const mainID = createdEntityData.insertId || createdEntityData.id;

    if (mappedEntities && getRelation.length > 0) {
      for (const relation of getRelation) {
        const targetEntityType = relation.target_entity_type;
        const relationType = relation.relation_type;

        if (!mappedEntities[targetEntityType]) continue;

        // normalize: always array
        const entityDataArray = Array.isArray(mappedEntities[targetEntityType])
          ? mappedEntities[targetEntityType]
          : [mappedEntities[targetEntityType]];

        for (const item of entityDataArray) {
          const itemWithRef = {
            ...item,
            entity_type: targetEntityType,
          };

          const createdRelatedEntity = await this.createEntity(
            targetEntityType,
            itemWithRef,
            loggedInUser,
            mainID, // this will pass for parent_id
          );

          const relationRepo =
            this.reflectionHelper.getRepoService('EntityRelationData');

          await relationRepo.save({
            organizationId: organizationId,
            enterprise_id: enterpriseId,
            source_entity_id: mainID,
            source_entity_type: entityType,
            target_entity_id: createdRelatedEntity.id,
            target_entity_type: targetEntityType,
            relation_type: relationType,
          });
        }
      }
    }

    return {
      mainEntity: {
        id: mainID,
        entityType,
        data: createdEntityData,
      },
    };
  }

  // ----------------------------- get entity with relations
  async getEntityWithRelation(
    entityType: string,
    id: number | string,
    loggedInUser: any,
  ): Promise<any> {
    const mainEntity = await this.getEntityByDataSource(
      entityType,
      id,
      loggedInUser,
    );
    const relationRepo = this.reflectionHelper.getRepoService('EntityRelation');
    const relatedEntityRepo =
      this.reflectionHelper.getRepoService('EntityRelationData');

    const relations = await relationRepo.find({
      where: {
        source_entity_type: entityType,
        enterprise_id: loggedInUser.enterprise_id,
      },
    });

    const targetTypes = relations.map((r) => r.target_entity_type);

    if (targetTypes.length === 0) {
      return {
        entity_type: entityType,
        ...mainEntity,
      };
    }

    const relatedEntities = await relatedEntityRepo.find({
      where: {
        source_entity_id: id,
        target_entity_type: In(targetTypes),
      },
    });

    // Format response to match create entity structure
    const response: any = {
      entity_type: entityType,
      ...mainEntity,
    };

    if (relatedEntities.length > 0) {
      response.mappedEntities = await this.formatMappedEntities(
        relatedEntities,
        loggedInUser,
      );
    }

    return response;
  }

  // ----------------------------- formatMappedEntities
  async formatMappedEntities(
    relatedEntities: any[],
    loggedInUser: any,
  ): Promise<any> {
    const mappedEntities: any = {};

    for (const relation of relatedEntities) {
      const targetEntityType = relation.target_entity_type;
      const targetEntityId = relation.target_entity_id;

      const entityData = await this.getEntity(
        targetEntityType,
        targetEntityId,
        loggedInUser,
      );

      if (!mappedEntities[targetEntityType]) {
        mappedEntities[targetEntityType] = [];
      }

      mappedEntities[targetEntityType].push(entityData);
    }

    return mappedEntities;
  }

  // ----------------------------- update with relations

  // ----------------------------- update entity with relations
  async updateEntityWithRelations(
    entityType: string,
    id: number | string,
    data: Record<string, any>,
    loggedInUser: any,
  ): Promise<any> {
    const organizationId = loggedInUser.organization_id;
    const enterpriseId = loggedInUser.enterprise_id;
    const { mappedEntities, ...mainData } = data;

    // Update main entity
    const updatedMainEntity = await this.updateEntity(
      entityType,
      id,
      mainData,
      loggedInUser,
    );

    const updatedRelations: Record<string, any> = {};

    if (mappedEntities) {
      const entityRelationRepo =
        this.reflectionHelper.getRepoService('EntityRelation');
      const getRelationDefs = await entityRelationRepo.find({
        where: {
          enterprise_id: enterpriseId,
          source_entity_type: entityType,
        },
      });

      for (const [targetEntityType, rawEntityData] of Object.entries(
        mappedEntities,
      )) {
        const relationDef = getRelationDefs.find(
          (r) => r.target_entity_type === targetEntityType,
        );
        if (!relationDef) continue;

        const relationType = relationDef.relation_type;
        const entityArray = Array.isArray(rawEntityData)
          ? rawEntityData
          : [rawEntityData];

        // Delete previous relations and related entities
        // Create/update new entities & relations
        const updatedEntities: any[] = [];

        for (const item of entityArray) {
          let targetEntityId;
          let entityData;

          if (item.id) {
            // Update existing entity
            await this.updateEntity(
              targetEntityType,
              item.id,
              item,
              loggedInUser,
              Number(id), // pass main entity id as parent_id
            );
            targetEntityId = item.id;
            entityData = await this.getEntity(
              targetEntityType,
              targetEntityId,
              loggedInUser,
            );
          } else {
            // Create new entity
            const createdEntity = await this.createEntity(
              targetEntityType,
              item,
              loggedInUser,
              Number(id), // pass main entity id as parent_id
            );
            targetEntityId = createdEntity.insertId || createdEntity.id;
            entityData = await this.getEntity(
              targetEntityType,
              targetEntityId,
              loggedInUser,
            );

            // Insert relation as per new entity created
            const entityRelationDataRepo =
              this.reflectionHelper.getRepoService('EntityRelationData');
            await entityRelationDataRepo.save({
              organizationId: organizationId,
              enterprise_id: enterpriseId,
              source_entity_id: id,
              source_entity_type: entityType,
              target_entity_id: targetEntityId,
              target_entity_type: targetEntityType,
              relation_type: relationType,
            });
          }

          if (relationType === 'ONE_TO_MANY') {
            updatedEntities.push(entityData);
          } else if (
            relationType === 'ONE_TO_ONE' ||
            relationType === 'MANY_TO_ONE'
          ) {
            updatedEntities[0] = entityData; // single object
          }
        }

        // Assign to response mappedEntities
        updatedRelations[targetEntityType] =
          relationType === 'ONE_TO_MANY' ? updatedEntities : updatedEntities[0];
      }
    }

    return {
      mainEntity: {
        id,
        entityType,
        data: updatedMainEntity,
      },
      relatedEntities: updatedRelations,
    };
  }

  // -----------------------------
  async updateEntity(
    entityType: string,
    id: number | string,
    entityData: Record<string, any>,
    loggedInUser: any,
    mainID?: number,
  ): Promise<any> {
    const enterprise_id = loggedInUser.enterprise_id;

    const tableName = await this.getTableName(entityType, enterprise_id);
    const validAttributes = await this.getAttributeCodes(
      entityType,
      enterprise_id,
    );

    const updates: string[] = [];
    const values: any[] = [];
    let idx = 1;

    // Auto fields
    entityData.modified_date = new Date();
    if (loggedInUser) {
      entityData.modified_by = loggedInUser.id;

      if (!entityData.organization_id && entityData.entity_type !== 'ORG')
        entityData.organization_id = loggedInUser.organization_id;

      if (!entityData.enterprise_id)
        entityData.enterprise_id = loggedInUser.enterprise_id;
    }

    if (mainID) {
      entityData.parent_id = mainID;
    }

    // Add bypass columns if needed
    const bypassColumns = [
      'created_date',
      'created_by',
      'modified_date',
      'modified_by',
      'organization_id',
      'enterprise_id',
      'level_type',
      'level_id',
      'status',
      'entity_type',
      'code',
      'parent_id',
    ];

    for (const col of bypassColumns) {
      if (!validAttributes.some((attr) => attr.attribute_key === col)) {
        validAttributes.push({
          attribute_key: col,
          db_datatype: 'text',
          element_type: 'text',
          is_hidden: false,
        });
      }
    }

    // Build SET clause
    for (const key of Object.keys(entityData)) {
      if (validAttributes.some((attr) => attr.attribute_key === key)) {
        updates.push(`${key} = $${idx++}`);
        values.push(entityData[key]);
      }
    }

    if (updates.length === 0) {
      throw new Error('No valid attributes to update.');
    }

    // WHERE clause placeholder
    const idPlaceholder = `$${idx}`;
    values.push(id);

    const updateQuery = `
        UPDATE ${this.schema}.${tableName}
        SET ${updates.join(', ')}
        WHERE id = ${idPlaceholder}
    `;

    return await this.entityManager.query(updateQuery, values);
  }

  async getEntityByDataSource(
    entityType: string,
    id: number | string,
    loggedInUser: any,
  ): Promise<any> {
    const enterprise_id = loggedInUser.enterprise_id;

    const dataSource = await this.getEntitySourceTableName(
      entityType,
      enterprise_id,
    );
    const validAttributes = await this.getAttributeCodes(
      entityType,
      enterprise_id,
      false,
    );

    const columns = validAttributes
      .map((attr) => `${attr.attribute_key}`)
      .join(', ');
    const selectQuery = `SELECT ${columns}
                         FROM ${this.schema}.${dataSource}
                         WHERE id = $1`;

    const result = await this.entityManager.query(selectQuery, [id]);
    if (!result.length) return null;

    const row = result[0];

    // Convert boolean columns (1/0) into true/false
    for (const attr of validAttributes) {
      if (
        attr.db_datatype == 'boolean' &&
        row[attr.attribute_key] !== undefined
      ) {
        row[attr.attribute_key] = row[attr.attribute_key] == 1 ? true : false;
      } else if (
        attr.element_type == 'upload' &&
        row[attr.attribute_key] !== undefined
      ) {
        row[attr.attribute_key] =
          row[attr.attribute_key] != null
            ? await this.mediaDataService.getMediaDownloadUrl(
                Number(row[attr.attribute_key]),
                loggedInUser,
              )
            : null;
      }
    }

    return row;
  }

  // -----------------------------
  //TODO : make it normal getEntity function make another function if for resolve data
  async getEntity(
    entityType: string,
    id: number | string,
    loggedInUser: any,
  ): Promise<any> {
    const enterprise_id = loggedInUser.enterprise_id;

    const entityMaster =
      await this.entityMasterRepo.getEntityByMappedEntityType(
        entityType,
        enterprise_id,
      );
    if (!entityMaster) return null;

    const validAttributes = await this.getAttributeCodes(
      entityType,
      enterprise_id,
    );

    // const entityRepo = this.reflectionHelper.getRepoService(entityMaster?.entity_data_class);

    const columns = validAttributes.map((attr) => `t.${attr.attribute_key}`);

    // const result = await entityRepo.find({
    //   where: {
    //     id: id
    //   },
    //   select: columns
    // });
    const selectQuery = `SELECT ${columns}
                         FROM ${this.schema}.${entityMaster.db_table_name} t
                         WHERE id = $1`;

    const result = await this.entityManager.query(selectQuery, [id]);
    if (!result.length) return null;

    const row = result[0];

    // Convert boolean columns (1/0) into true/false
    for (const attr of validAttributes) {
      if (
        attr.db_datatype == 'boolean' &&
        row[attr.attribute_key] !== undefined
      ) {
        row[attr.attribute_key] = row[attr.attribute_key] == 1 ? true : false;
      } else if (
        attr.element_type == 'upload' &&
        row[attr.attribute_key] !== undefined
      ) {
        row[attr.attribute_key] =
          row[attr.attribute_key] != null
            ? await this.mediaDataService.getMediaDownloadUrl(
                Number(row[attr.attribute_key]),
                loggedInUser,
              )
            : null;
      }
    }

    return row;
  }

  private async getEntitySourceTableName(
    entityType: string,
    enterprise_id: string,
  ): Promise<string> {
    const result = await this.entityMasterRepo.getEntityByMappedEntityType(
      entityType,
      enterprise_id,
    );

    if (!result) {
      console.log(`Entity type '${entityType}' not found in frm_entity_master`);
      throw new BadRequestException();
    }

    return result.data_source;
  }

  // -----------------------------
  private async getTableName(
    entityType: string,
    enterprise_id: string,
  ): Promise<string> {
    let entityMaster = await this.entityMasterRepo.getEntityByMappedEntityType(
      entityType,
      enterprise_id,
    );

    if (!entityMaster) {
      console.log(`Entity type '${entityType}' not found in frm_entity_master`);
      throw new BadRequestException();
    }

    return entityMaster.db_table_name;
  }

  private async getAttributeCodes(
    entityType: string,
    enterprise_id: string,
    isHidden = true,
  ) {
    const attributeMasterRepo =
      this.reflectionHelper.getRepoService('AttributeMaster');

    const qb = attributeMasterRepo
      .createQueryBuilder('fea')
      .select('fea.attribute_key', 'attribute_key')
      .addSelect('MAX(fea.db_datatype)', 'db_datatype')
      .addSelect('MAX(fea.element_type)', 'element_type')
      .addSelect('bool_or(fea.is_hidden)', 'is_hidden')
      .where('fea.mapped_entity_type = :entityType', { entityType })
      .andWhere('fea.enterprise_id = :enterprise_id', { enterprise_id });

    if (isHidden) {
      qb.andWhere('(fea.is_hidden IS NULL OR fea.is_hidden = false)');
    }

    qb.groupBy('fea.attribute_key');

    const result = await qb.getRawMany();

    return result.map((row: any) => ({
      attribute_key: row.attribute_key,
      db_datatype: row.db_datatype,
      element_type: row.element_type,
      is_hidden: row.is_hidden != null ? row.is_hidden === true : undefined,
    }));
  }

  private async deleteEntity(
    entityType: string,
    id: number | string,
    loggedInUser: any,
  ): Promise<any> {
    const enterprise_id = loggedInUser.enterprise_id;

    const tableName = await this.getTableName(entityType, enterprise_id);

    const deleteQuery = `DELETE
                         FROM ${this.schema}.${tableName}
                         WHERE id = $1`;
    const result = await this.entityManager.query(deleteQuery, [id]);
    return result;
  }

  // -----------------------------

  async getEntitiesDropdownList(
    loggedInUser: any,
    appcode?: string,
  ): Promise<any> {
    const entityMasters =
      await this.entityMasterRepo.findByEnterpriseIdAndAppCode(
        loggedInUser.enterprise_id,
        appcode,
      );

    let dropdown = [] as any;
    entityMasters.map((entityMaster) =>
      dropdown.push({
        label: entityMaster.name,
        value: entityMaster.mapped_entity_type,
      }),
    );

    return dropdown;
  }

  async getCode(entityType: string, loggedInUser: any): Promise<string> {
    const enterprise_id = loggedInUser.enterprise_id;

    // 1. Get db_table_name from entity master

    const result = await this.entityMasterRepo.getEntityByMappedEntityType(
      entityType,
      enterprise_id,
    );

    if (!result) {
      throw new Error(
        `Entity type '${entityType}' not found in frm_entity_master for enterprise '${enterprise_id}'`,
      );
    }

    const tableName = result.db_table_name;

    // 2. Get current max sequence number from that table
    const seqResult = await this.entityManager.query(
      `SELECT MAX(id) AS max_seq_no
       FROM ${this.schema}.${tableName}
       WHERE entity_type = $1`,
      [entityType],
    );

    let maxSeqNo = seqResult?.[0]?.max_seq_no
      ? Number(seqResult[0].max_seq_no)
      : 0;

    maxSeqNo += 1;

    // 3. Return generated code
    return `${entityType}${maxSeqNo}`;
  }

  async getResolvedEntity(
    id: number,
    entity: string,
    loggedInUser: UserData,
  ): Promise<any> {
    const leadData = await this.getEntityWithRelation(entity, id, loggedInUser);

    const { mappedEntities, ...data } = leadData as any;

    const resolvedData = await this.resolverService.getResolvedData(
      loggedInUser,
      data,
      entity,
    );

    if (mappedEntities) {
      resolvedData.mappedEntities = {};
      for (const [entityType, entities] of Object.entries(mappedEntities)) {
        if (Array.isArray(entities)) {
          resolvedData.mappedEntities[entityType] = [];
          for (const item of entities) {
            const resolvedItem = await this.resolverService.getResolvedData(
              loggedInUser,
              item,
              entityType,
            );
            resolvedData.mappedEntities[entityType].push(resolvedItem);
          }
        } else {
          resolvedData.mappedEntities[entityType] =
            await this.resolverService.getResolvedData(
              loggedInUser,
              entities,
              entityType,
            );
        }
      }
    }

    return resolvedData;
  }
}