import { DRIZZLE_PROVIDER } from '@/database/drizzle-provider';
import { surveys, answersSurveys, viewSurveys } from '@/database/index';
import { HttpException, HttpStatus, Inject, Injectable } from '@nestjs/common';
import { NodePgDatabase } from 'drizzle-orm/node-postgres';
import * as schema from '@/database/index';
import { CreateSurveyDto } from './dto/create-survey.dto';
import { UpdateSurveyDto } from './dto/update-survey.dto';
import { and, count, eq, ilike, isNull, like, or, sql } from 'drizzle-orm';
import { SurveyDetailDto, SurveyStatisticsResponseDto } from './dto/statistics-response.dto';
import { PaginationDto } from '@/common/dto/pagination.dto';
import { AnswersSurveyDto } from './dto/response-survey.dto';
import { FindForUserDto } from './dto/find-for-user.dto';

@Injectable()
export class SurveysService {
  constructor(
    @Inject(DRIZZLE_PROVIDER) private drizzle: NodePgDatabase<typeof schema>,
  ) { }

  async findAll(paginationDto: PaginationDto) {
    const { page = 1, limit = 10, search = '', sortBy = 'id', sortOrder = 'asc' } = paginationDto || {};
    const offset = (page - 1) * limit;

    // Build search condition
    const searchCondition = ilike(surveys.title, `%${search}%`);


    // Build sort condition
    const orderBy = sortOrder === 'asc'
      ? sql`${surveys[sortBy as keyof typeof surveys]} asc`
      : sql`${surveys[sortBy as keyof typeof surveys]} desc`;

    // Get total count for pagination
    const totalCountResult = await this.drizzle
      .select({ count: sql<number>`count(*)` })
      .from(surveys)
      .where(searchCondition);

    const totalCount = Number(totalCountResult[0].count);
    const totalPages = Math.ceil(totalCount / limit);


    // Get paginated data
    const data = await this.drizzle
      .select()
      .from(surveys)
      .where(searchCondition)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset);

    const dataSurvey = data.map((survey) => {
      return {
        ...survey,
        closingDate: survey.closingDate ? new Date(survey.closingDate) : null,
      };
    });

    // Build pagination metadata
    const meta = {
      page,
      limit,
      totalCount,
      totalPages,
      hasNextPage: page < totalPages,
      hasPreviousPage: page > 1,
      nextPage: page < totalPages ? page + 1 : null,
      previousPage: page > 1 ? page - 1 : null,
    };

    return { data: dataSurvey, meta };

  }

  async findAllForUser(paginationDto: PaginationDto, userId: number, findForUserDto: FindForUserDto) {
    const { page = 1, limit = 10, search = '', sortBy = 'created_at', sortOrder = 'asc' } = paginationDto || {};
    const offset = (page - 1) * limit;

    let searchCondition : any = false

    // Build search condition    
    // if (findForUserDto.rol[0].rol === 'superadmin' || findForUserDto.rol[0].rol == 'admin') {
    //   searchCondition = and(
    //     or(eq(viewSurveys.targetAudience, 'producers'), eq(viewSurveys.targetAudience, 'organization'), eq(viewSurveys.targetAudience, 'all')),
    //     or(eq(viewSurveys.user_id, userId), isNull(viewSurveys.user_id))
    //   );
    // } else {
    //   searchCondition = and(
    //     or(eq(viewSurveys.targetAudience, findForUserDto.rol[0].rol), eq(viewSurveys.targetAudience, 'all')),
    //     or(eq(viewSurveys.user_id, userId), isNull(viewSurveys.user_id))
    //   );
    // }

    if (findForUserDto.rol[0].rol !== 'superadmin' && findForUserDto.rol[0].rol !== 'admin') {
      searchCondition = and(
        or(
          eq(surveys.targetAudience, findForUserDto.rol[0].rol),
          eq(surveys.targetAudience, 'all')
        ),
        like(surveys.title, `%${search}%`)
      )
    } else {
      searchCondition = like(surveys.title, `%${search}%`)
    }

    // console.log(searchCondition);

    // Build sort condition
    const orderBy = sortOrder === 'asc'
      ? sql`${surveys[sortBy as keyof typeof surveys]} asc`
      : sql`${surveys[sortBy as keyof typeof surveys]} desc`;

    // Get total count for pagination
    const totalCountResult = await this.drizzle
      .select({ count: sql<number>`count(*)` })
      .from(surveys)
      .leftJoin(answersSurveys, and(eq(answersSurveys.surveyId,surveys.id),eq(answersSurveys.userId,userId)))
      .where(searchCondition);

    const totalCount = Number(totalCountResult[0].count);
    const totalPages = Math.ceil(totalCount / limit);

    // Get paginated data
    const data = await this.drizzle
      .select()
      .from(surveys)
      .leftJoin(answersSurveys, and(eq(answersSurveys.surveyId,surveys.id),eq(answersSurveys.userId,userId)))
      .where(searchCondition)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset);

    // Build pagination metadata
    const meta = {
      page,
      limit,
      totalCount,
      totalPages,
      hasNextPage: page < totalPages,
      hasPreviousPage: page > 1,
      nextPage: page < totalPages ? page + 1 : null,
      previousPage: page > 1 ? page - 1 : null,
    };

    return { data, meta };
  }

  async findOne(id: string) {
    const survey = await this.drizzle
      .select()
      .from(surveys)
      .where(eq(surveys.id, parseInt(id)));

    if (survey.length === 0) {
      throw new HttpException('Survey not found', HttpStatus.NOT_FOUND);
    }

    const dataSurvey = survey.map((survey) => {
      return {
        ...survey,
        closingDate: survey.closingDate ? new Date(survey.closingDate) : null,
      };
    });

    return dataSurvey[0];
  }

  async findByTitle(title: string) {
    return await this.drizzle
      .select()
      .from(surveys)
      .where(eq(surveys.title, title));
  }

  async create(createSurveyDto: CreateSurveyDto) {

    const find = await this.findByTitle(createSurveyDto.title);

    if (find.length !== 0) {
      throw new HttpException('Survey already exists', HttpStatus.BAD_REQUEST);
    }

    const survey = await this.drizzle
      .insert(surveys)
      .values({
        ...createSurveyDto,
        closingDate: createSurveyDto.closingDate?.toISOString(),
      })
      .returning();
    const dataSurvey = survey.map((survey) => {
      return {
        ...survey,
        closingDate: survey.closingDate ? new Date(survey.closingDate) : null,
      };
    });

    return dataSurvey[0];
  }

  async update(id: string, updateSurveyDto: UpdateSurveyDto) {
    
    const find = await this.findOne(id)
    if (!find) {
      throw new HttpException('Survey not found', HttpStatus.BAD_REQUEST)
    }

    const find2 = await this.findByTitle(updateSurveyDto.title ?? '');

    if (find2.length !== 0 && find2[0].id !== parseInt(id)) {
      throw new HttpException('Survey already exists', HttpStatus.BAD_REQUEST);
    }

    const survey = await this.drizzle
      .update(surveys)
      .set({
        ...updateSurveyDto,
        closingDate: updateSurveyDto.closingDate?.toISOString(),
        updated_at: new Date(),
      })
      .where(eq(surveys.id, parseInt(id)))
      .returning();

    const dataSurvey = survey.map((survey) => {
      return {
        ...survey,
        closingDate: survey.closingDate ? new Date(survey.closingDate) : null,
      };
    });

    return dataSurvey[0];
  }

  async remove(id: string) {
    const find = await this.findOne(id);
    if (!find) {
      throw new HttpException('Survey not found', HttpStatus.BAD_REQUEST);
    }

    await this.drizzle
      .delete(surveys)
      .where(eq(surveys.id, parseInt(id)));

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

  async answers(userId: number, answersSurveyDto: AnswersSurveyDto) {

    const find = await this.drizzle.select()
      .from(answersSurveys)
      .where(and(eq(answersSurveys.surveyId, Number(answersSurveyDto.surveyId)), (eq(answersSurveys.userId, userId))));


    if (find.length !== 0) {
      throw new HttpException('Survey answers already exists', HttpStatus.BAD_REQUEST);
    }

    const survey = await this.drizzle
      .insert(answersSurveys)
      .values({
        ...answersSurveyDto,
        surveyId: Number(answersSurveyDto.surveyId),
        userId: userId,
      })
      .returning();

    return survey[0];
  }

  async getStatistics(): Promise<SurveyStatisticsResponseDto> {
    // Obtener el número total de encuestas
    const totalSurveys = await this.getTotalSurveysCount();
    // Obtener el número total de respuestas
    const totalResponses = await this.getTotalResponsesCount();

    // Calcular la tasa de finalización
    const completionRate = totalSurveys > 0 ? Math.round((totalResponses / totalSurveys) * 100) : 0;

    // Obtener las encuestas por mes
    const surveysByMonth = await this.getSurveysByMonth();

    // Obtener las respuestas por audiencia
    const responsesByAudience = await this.getResponsesByAudience();

    // Obtener la distribución de respuestas por encuesta
    const responseDistribution = await this.getResponseDistribution();

    // Obtener las estadísticas detalladas de las encuestas
    const surveyDetails = await this.getSurveyDetails();

    return {
      totalSurveys,
      totalResponses,
      completionRate,
      surveysByMonth,
      responsesByAudience,
      responseDistribution,
      surveyDetails,
    }
  }

  private async getTotalSurveysCount(): Promise<number> {
    const result = await this.drizzle
      .select({ count: sql<number>`count(*)` })
      .from(surveys);
    return Number(result[0].count);
  }

  private async getTotalResponsesCount(): Promise<number> {
    const result = await this.drizzle
      .select({ count: sql<number>`count(*)` })
      .from(answersSurveys);
    return Number(result[0].count);
  }

  private async getSurveysByMonth(): Promise<{ month: string; count: number }[]> {
    const result = await this.drizzle
      .select({
        month: sql<string>`to_char(created_at, 'YYYY-MM')`,
        count: sql<number>`count(*)`,
      })
      .from(surveys)
      .groupBy(sql`to_char(created_at, 'YYYY-MM')`)
      .orderBy(sql`to_char(created_at, 'YYYY-MM')`);
    return result.map(item => ({ month: item.month, count: Number(item.count) }));
  }

  private async getResponsesByAudience(): Promise<{ name: any; value: number }[]> {
    const result = await this.drizzle
      .select({
        audience: surveys.targetAudience,
        count: sql<number>`count(*)`,
      })
      .from(answersSurveys)
      .leftJoin(surveys, eq(answersSurveys.surveyId, surveys.id))
      .groupBy(surveys.targetAudience);
    return result.map(item => 
      {
        let audience = 'Sin definir'
        if (item.audience == 'all') {
          audience = 'General'
        } else if (item.audience == 'organization') {
          audience = 'Organización'
        } else if (item.audience == 'producers') {
          audience = 'Productores'
        }
        return ({ name: audience, value: Number(item.count) })
      }
    );
  }

  private async getResponseDistribution(): Promise<{ title: string; responses: number }[]> {
    const result = await this.drizzle
      .select({
        id: surveys.id,
        title: surveys.title,
        responses: sql<number>`count(${answersSurveys.id})`,
      })
      .from(surveys)
      .leftJoin(answersSurveys, eq(surveys.id, answersSurveys.surveyId))
      .groupBy(surveys.id, surveys.title)
      .orderBy(sql`count(${answersSurveys.id}) desc`)
      .limit(10);
    return result.map(item => ({ title: item.title, responses: Number(item.responses) }));
  }

  private async getSurveyDetails(): Promise<SurveyDetailDto[]> {
    const allSurveys = await this.drizzle
      .select({
        id: surveys.id,
        title: surveys.title,
        description: surveys.description,
        targetAudience: surveys.targetAudience,
        createdAt: surveys.created_at,
        closingDate: surveys.closingDate,
        questions: surveys.questions,
      })
      .from(surveys);


    return await Promise.all(
      allSurveys.map(async (survey) => {
        // Obtener el número total de respuestas para esta encuesta
        const totalSurveyResponses = await this.getTotalSurveyResponses(survey.id);

        // Obtener todas las respuestas para esta encuesta
        const answersResult = await this.drizzle
          .select({ answers: answersSurveys.answers })
          .from(answersSurveys)
          .where(eq(answersSurveys.surveyId, survey.id));

        let audience = 'Sin definir'
        if (survey.targetAudience == 'all') {
          audience = 'General'
        } else if (survey.targetAudience == 'organization') {
          audience = 'Organización'
        } else if (survey.targetAudience == 'producers') {
          audience = 'Productores'
        }
        
        // Procesar las estadísticas de las preguntas
        const questionStats = this.processQuestionStats(survey.questions as any[], answersResult);

        return {
          id: survey.id,
          title: survey.title,
          description: survey.description,
          totalResponses: totalSurveyResponses,
          // targetAudience: survey.targetAudience,
          targetAudience: audience,
          createdAt: survey.createdAt.toISOString(),
          closingDate: survey.closingDate ? new Date(survey.closingDate).toISOString() : undefined,
          questionStats,
        };
      })
    );
  }

  private async getTotalSurveyResponses(surveyId: number): Promise<number> {
    const result = await this.drizzle
      .select({ count: sql<number>`count(*)` })
      .from(answersSurveys)
      .where(eq(answersSurveys.surveyId, surveyId));
    return Number(result[0].count);
  }

  // ==================================

  private processQuestionStats(questions: any[], answersResult: { answers: any }[]) {
    // Initialize counters for each question option
    const questionStats: Array<{ questionId: string; label: string; count: number }> = [];

    // Skip title questions (type: 'title')
    const surveyQuestions = questions.filter(q => q.type !== 'title');

    // console.log(surveyQuestions);
    // console.log('Se llamo a processQuestionStats()');

    for (const question of surveyQuestions) {
      // console.log('Bucle1 se ejecuto');
      
      // For single choice, multiple choice, and select questions
      // if (['single_choice', 'multiple_choice', 'select'].includes(question.type)) {       
        const optionCounts: Record<string, number> = {};

        // // Initialize counts for each option
        // for (const option of question.options) {
        //   optionCounts[option.text] = 0;
        // }
        
        // // Count responses for each option
        // for (const answerObj of answersResult) {         
        //   const answer = answerObj.answers.find(a => a.questionId === question.id);

        //   if (answer) {
        //     if (Array.isArray(answer.value)) {
        //       // For multiple choice questions
        //       for (const value of answer.value) {
        //         if (optionCounts[value] !== undefined) {
        //           optionCounts[value]++;
        //         }
        //       }
        //     } else {
        //       // For single choice questions
        //       if (optionCounts[answer.value] !== undefined) {
        //         optionCounts[answer.value]++;
        //       }
        //     }
        //   }
        // }

        // // Convert to the required format
        // for (const option of question.options) {
        //   questionStats.push({
        //     questionId: String(question.id),
        //     label: option.text,
        //     count: optionCounts[option.value] || 0,
        //   });
        // }
        
      if (question.type == 'multiple_choice') {
        for (const option of question.options) {
          console.log(option);
          let count :number = 0
          // Count responses for each option
          for (const obj of answersResult) {
            console.log(obj.answers)
            const resp = obj.answers.find(a => a.questionId == question.id)
            const respArray = resp.value.split(",")
            // console.log();
            
            if (respArray[option.id] == 'true') {
              count++
            }
          }
          optionCounts[option.text] = count
          // Convert to the required format
          questionStats.push({
            questionId: String(question.id),
            label: `${question.question} ${option.text}`,
            count: optionCounts[option.text] || 0,
          })
        }
        
      } else if (question.type == 'single_choice' || question.type == 'select') {
        for (const option of question.options) {
          let count :number = 0
          // Count responses for each option
          for (const obj of answersResult) {
              const resp = obj.answers.find(a => a.questionId == question.id)
              if (resp.value == option.id) {
                count++ 
              }
          }
          optionCounts[option.text] = count
          // Convert to the required format
          questionStats.push({
            questionId: String(question.id),
            label: `${question.question} ${option.text}`,
            count: optionCounts[option.text] || 0,
          })
        }
      } else if (question.type === 'simple') {
        // For simple text questions, just count how many responses
        let responseCount = 0;

        for (const answerObj of answersResult) {
          const answer = answerObj.answers.find(a => a.questionId === question.id);
          if (answer && answer.value) {
            responseCount++;
          }
        }

        questionStats.push({
          questionId: String(question.id),
          label: question.question,
          count: responseCount,
        });
      }
    }

    return questionStats;
  }
}