import { BadRequestException, Injectable } from '@nestjs/common';
import { Brackets, EntityManager } from 'typeorm';
import { ExcelUtil } from 'src/utils/service/excelUtil.service';
import { EntityMasterService } from '../../meta/service/entity-master.service';
import { AttributeMasterService } from 'src/module/meta/service/attribute-master.service';
import { EntityMaster } from 'src/module/meta/entity/entity-master.entity';
import { EntityServiceImpl } from 'src/module/meta/service/entity-service-impl.service';
import { ReflectionHelper } from 'src/utils/service/reflection-helper.service';
import { EntityValidationService } from 'src/module/meta/service/entity-validation.service';
import { ConfigService } from '@nestjs/config';

@Injectable()
export class MasterService {
  constructor(
    private readonly entityManager: EntityManager,
    private readonly entityMasterService: EntityMasterService,
    private readonly attributeMasterService: AttributeMasterService,
    private reflectionHelper: ReflectionHelper,
    private readonly configService: ConfigService,
    protected readonly entityValidationService: EntityValidationService,
  ) {}

  schema = this.configService.get('DB_SCHEMA');
  // private readonly metaSheets = [
  //   'frm_entity_master',
  //   'frm_entity_attribute',
  //   'frm_list_master',
  //   'frm_list_master_items',
  //   'frm_entity_table',
  //   'frm_entity_table_column',
  //   'frm_entity_view',
  // ];
  private readonly sequence = [
    {
      table: 'frm_entity_master',
      unique_fields: ['id', 'organization_id'],
    },
    {
      table: 'frm_entity_attribute',
      unique_fields: ['mapped_entity_type', 'attribute_key', 'organization_id'],
    },
    {
      table: 'frm_list_master',
      unique_fields: ['type', 'organization_id'],
    },
    {
      table: 'frm_list_master_items',
      unique_fields: ['listtype', 'code', 'organization_id'],
    },
    {
      table: 'frm_entity_table',
      unique_fields: ['list_type', 'mapped_entity_type', 'organization_id'],
    },
    {
      table: 'frm_entity_table_column',
      unique_fields: [
        'parent_id',
        'parent_type',
        'attribute_key',
        'organization_id',
      ],
    },
    {
      table: 'frm_entity_view',
      unique_fields: ['mapped_entity_type', 'organization_id'],
    },
    {
      table: 'sso_user',
      unique_fields: ['organization_id'],
    },
    {
      table: 'sso_header_items',
      unique_fields: ['code', 'section_id', 'organization_id'],
    },
    {
      table: 'sso_module_access',
      unique_fields: ['module_code', 'action_type', 'role_code'],
    },
    {
      table: 'sso_module_action',
      unique_fields: ['module_code', 'action_type', 'organization_id'],
    },
    {
      table: 'sso_menu',
      unique_fields: ['module_code', 'organization_id'],
    },
    {
      table: 'sso_module',
      unique_fields: ['module_code', 'organization_id'],
    },
    {
      table: 'sso_role',
      unique_fields: ['code', 'organization_id'],
    },
    {
      table: 'sso_header_sections',
      unique_fields: ['section_name', 'organization_id'],
    },
    {
      table: 'sso_user_role_mapping',
      unique_fields: ['user_id', 'role_id', 'organization_id'],
    },
    {
      table: 'sso_organization',
      unique_fields: ['code'],
    },
    {
      table: 'eth_academic_year',
      unique_fields: ['code'],
    },
  ];
  private readonly dataSequence = [
    {
      table: 'sso_user',
      unique_fields: ['code'],
    },
    {
      table: 'sso_role',
      unique_fields: ['code'],
    },
    {
      table: 'sso_user_role_mapping',
      unique_fields: ['user_id', 'role_id'],
    },
    {
      table: 'eth_school',
      unique_fields: ['code'],
    },
    {
      table: 'eth_school_address',
      unique_fields: ['code'],
    },
  ];

  async uploadMeta(file) {
    const data = ExcelUtil.readExcel(file.buffer);

    for (let i = 0; i < this.sequence.length; i++) {
      const { table, unique_fields } = this.sequence[i];
      const sheetData = data[table];

      if (!sheetData) {
        console.log(`Sheet ${table} not found in the Excel file.`);
        continue;
      }
      console.log(`Processing sheet: ${table}`);
      await this.upsertData(table, unique_fields, sheetData);
    }
    return {
      message: 'Data uploaded successfully',
    };
  }

  async uploadData(file, loggedInUser) {
    const data = ExcelUtil.readExcel(file.buffer);

    for (let i = 0; i < this.dataSequence.length; i++) {
      const { table, unique_fields } = this.dataSequence[i];
      const sheetData = data[table];

      if (!sheetData) {
        console.log(`Sheet ${table} not found in the Excel file.`);
        continue;
      }

      const entityMasterData =
        await this.entityMasterService.getEntityByTableName(table);

      if (!entityMasterData) {
        throw new Error(`Entity master not found for table: ${table}`);
      }

      const attributes =
        await this.attributeMasterService.findAttributesByMappedEntityType(
          entityMasterData.mapped_entity_type,
          loggedInUser,
        );

      for (const row of sheetData) {
        if (row.parent_type !== undefined && row.parent_id !== undefined) {
          this.resolveParent(row);
        }

        for (const attribute of attributes) {
          if (attribute.data_source_type === 'entity') {
            const entityMaster = await this.entityMasterService.getEntityData(
              attribute.datasource_list,
              loggedInUser,
            );
            const entityData = await this.entityManager.query(
              `SELECT * FROM ${entityMaster.db_table_name} WHERE code = ? LIMIT 1`,
              [row[attribute.attribute_key]],
            );
            row[attribute.attribute_key] = entityData[0].id;
          }
        }
      }

      console.log(`Processing sheet: ${table}`);
      await this.upsertData(table, unique_fields, sheetData);
    }
    return {
      message: 'Data uploaded successfully',
    };
  }

  async uploadEntityData(
    file,
    entityType,
    loggedInUser,
    duplicateHandling: 'skip_duplicates' | 'overwrite_items',
  ) {
    const data = ExcelUtil.readExcel(file.buffer);
    const entityMaster =
      await this.entityMasterService.findByMappedEntityType(entityType);
    if (!entityMaster) {
      throw new Error(`Entity master not found for entityType: ${entityType}`);
    }

    const tableName = entityMaster.db_table_name;
    const sheetData = data[tableName];
    if (!sheetData) {
      throw new Error(`Sheet for ${tableName} not found in uploaded file`);
    }

    const attributes =
      await this.attributeMasterService.findAttributesByMappedEntityType(
        entityMaster.mapped_entity_type,
        loggedInUser,
      );

    const uniqueFields = attributes
      .filter((attr) => attr.is_unique)
      .map((attr) => attr.attribute_key);

    if (uniqueFields.length === 0) {
      throw new Error(`No unique fields found for entityType: ${entityType}`);
    }

    const errors: any[] = [];

    // ✅ Iterate row by row
    for (let i = 0; i < sheetData.length; i++) {
      const row = sheetData[i];

      if (row.parent_type && row.parent_id) {
        await this.resolveParent(row);
      }

      // handle reference entity replacement
      for (const attr of attributes) {
        if (attr.data_source_type === 'entity' && row[attr.attribute_key]) {
          const refEntity = await this.entityMasterService.getEntityData(
            attr.datasource_list,
            loggedInUser,
          );
          const refData = await this.entityManager.query(
            `SELECT * FROM ${refEntity.db_table_name} WHERE code = ? LIMIT 1`,
            [row[attr.attribute_key]],
          );

          if (!refData.length) {
            errors.push({
              row: i + 1,
              errors: [
                `Reference entity not found for code: ${row[attr.attribute_key]}`,
              ],
            });
            continue; // skip further processing for this row
          }

          // replace with reference id
          row[attr.attribute_key] = refData[0].id;
        }
      }

      for (const attr of attributes) {
        if (attr.data_source_type === 'master' && row[attr.attribute_key]) {
          const refData = await this.entityManager.query(
            `SELECT * FROM frm_list_master_items WHERE name = ? and enterprise_id = ? LIMIT 1`,
            [row[attr.attribute_key], loggedInUser.enterprise_id],
          );

          if (!refData.length) {
            errors.push({
              row: i + 1,
              errors: [
                `Reference master data not found for name: ${row[attr.attribute_key]}`,
              ],
            });
            continue; // skip further processing for this row
          }

          // replace with reference id
          row[attr.attribute_key] = refData[0].id;
        }
      }

      // ✅ validate single row
      const rowErrors = await this.entityValidationService.validateImportData(
        row,
        entityMaster,
        loggedInUser,
      );

      if (rowErrors.length > 0) {
        errors.push({ row: i + 1, errors: rowErrors });
      }
    }

    // ✅ if any row failed, return errors instead of inserting
    if (errors.length > 0) {
      throw new BadRequestException({
        message: 'Validation errors found',
        errors,
      });
    }

    // ✅ only upsert if no validation errors
    await this.upsertViaService(
      entityType,
      sheetData,
      attributes,
      uniqueFields,
      loggedInUser,
      duplicateHandling,
    );

    return { message: 'Entity data uploaded successfully' };
  }

  // private isMetaSheet(sheetName: string): boolean {
  //   return this.metaSheets.includes(sheetName.toLowerCase());
  // }

  private async resolveParent(row: any): Promise<void> {
    const parentType = row.parent_type; // (Future use maybe)
    const code = row.parent_id;

    if (!code) {
      throw new Error('Parent code is missing in the row');
    }

    const parentEntity: EntityMaster | null =
      await this.entityMasterService.findByMappedEntityType(parentType);

    const tableName: any = parentEntity?.db_table_name;
    if (!tableName) {
      throw new Error(`Table name not found for parent type ${parentType}`);
    }

    // Using RAW SQL Query
    const entityData = await this.entityManager.query(
      `SELECT * FROM ${tableName} WHERE code = ? LIMIT 1`,
      [code],
    );

    if (!entityData || entityData.length === 0) {
      throw new Error(
        `Parent entity with code ${code} not found in table ${tableName}`,
      );
    }

    // entityData is an array when using .query()
    row.parent_id = entityData[0].id;
  }

  async upsertData(
    tableName: string,
    uniqueFields: string[],
    data: any[],
  ): Promise<void> {
    for (const row of data) {
      const whereClause = uniqueFields
        .map((field) => `${field} = :${field}`)
        .join(' AND ');

      const whereParams = uniqueFields.reduce((acc, field) => {
        acc[field] = row[field];
        return acc;
      }, {});

      const existing = await this.entityManager
        .createQueryBuilder()
        .select('*')
        .from(tableName, tableName)
        .where(whereClause, whereParams)
        .limit(1)
        .getRawMany();

      if (existing.length > 0) {
        // Update
        await this.entityManager
          .createQueryBuilder()
          .update(tableName)
          .set(row)
          .where(whereClause, whereParams)
          .execute();
      } else {
        // Insert
        await this.entityManager
          .createQueryBuilder()
          .insert()
          .into(tableName)
          .values(row)
          .execute();
      }
    }
  }

  async upsertViaService(
    entityType: string,
    data: any[],
    attributes: any[],
    uniqueFields: string[],
    loggedInUser: any,
    duplicateHandling: 'skip_duplicates' | 'overwrite_items',
  ): Promise<void> {
    const entityMaster =
      await this.entityMasterService.findByMappedEntityType(entityType);
    if (!entityMaster)
      throw new Error(`Entity master not found for ${entityType}`);

    const serviceName = entityMaster.entity_service;
    const entityService =
      await this.reflectionHelper.getBean<EntityServiceImpl>(serviceName);
    if (!entityService)
      throw new Error(`Entity service not found for ${entityType}`);

    const errors: { row: number; errors: any[] }[] = [];

    for (const [i, row] of data.entries()) {
      row.entity_type = entityType;
      row.organization_id = loggedInUser.organization_id;
      row.enterprise_id = loggedInUser.enterprise_id;

      // 🧠 Check if this already exists
      const qb = this.entityManager
        .createQueryBuilder()
        .select('*')
        .from(`${this.schema}.${entityMaster.db_table_name}`, 't')
        .where(
          new Brackets((qbOr) => {
            uniqueFields.forEach((field, index) => {
              const condition = `t.${field} = :val${index}`;
              const param = { [`val${index}`]: row[field] };
              if (index === 0) {
                qbOr.where(condition, param);
              } else {
                qbOr.orWhere(condition, param);
              }
            });
          }),
        )
        .andWhere(`t.enterprise_id = :entId`, {
          entId: loggedInUser.enterprise_id,
        })
        .andWhere(`t.level_type = :levelType`, {
          levelType: loggedInUser.level_type,
        })
        .andWhere(`t.level_id = :levelId`, {
          levelId: loggedInUser.level_id,
        });

      const existing = await qb.limit(1).getRawOne();

      if (existing) {
        if (duplicateHandling === 'skip_duplicates') continue;
        if (duplicateHandling === 'overwrite_items') {
          row.id = existing.id;
          await entityService.updateEntity(row, loggedInUser);
        }
      } else {
        await entityService.createEntity(row, loggedInUser);
      }
    }

    // if (errors.length > 0) {
    //   throw new Error(
    //     `Validation errors found in the uploaded data: ${JSON.stringify(errors)}`,
    //   );
    // }
  }
}