import { Injectable, HttpException, HttpStatus, Res } from "@nestjs/common"
import { InjectRepository } from "@nestjs/typeorm"
import { Repository, Like, Not } from "typeorm"
import { classToPlain } from "class-transformer"
import { responseMessages } from "../../messages/response-messages"
import { emailMessages } from "../../messages/email-messages"
import { KeywordsRepository } from "./keywords.repository"
import * as bcrypt from "bcryptjs"
import { HttpService } from "@nestjs/axios"
import { Constants } from "../../common/constants"
import { JwtService } from "@nestjs/jwt"
import { OpenAIService } from "src/common/services/openai/openai.service"
const moment = require("moment-timezone")

const KOREAN_LANGUAGE_CODE = 'kr';

// Entities Files
import { UserRoles, Users } from "../../entities/users.entity"
import { Keywords } from "../../entities/keywords.entity"
import { KeywordTranslation } from "../../entities/keyword-translations.entity"
import { UpdateDto } from "./dtos/update.dto"
import { KeywordIdParamDto } from "./dtos/keyword-id-param.dto"

// DTO Files
import { CreateDto } from "./dtos/create.dto"
import { ListQueryDto } from "./dtos/list-query.dto"
import { UserKeywords } from "src/entities/user-keywords.entity"

@Injectable()
export class KeywordsService {
    constructor(
        private keywordsRepository: KeywordsRepository,

        @InjectRepository(Keywords)
        private Keywords: Repository<Keywords>,

        @InjectRepository(Users)
        private Users: Repository<Users>,

        @InjectRepository(KeywordTranslation)
        private KeywordTranslation: Repository<KeywordTranslation>,

        @InjectRepository(UserKeywords)
        private UserKeywords: Repository<UserKeywords>,

        private readonly openAIService: OpenAIService,
    ) { }

    private delay(ms: number) {
        return new Promise((resolve) => setTimeout(resolve, ms));
    }

    /**
     * Create keyword based on request parameters.
     * @param request - An instance of CreateDto containing the keyword data with translations to process of create data.
     * @param response - The response object for sending the HTTP response.
     * @returns Responds with a 201 status if successful.
     * In case of an error, logs the error and responds with the respective error status and message.
     */
    async create(request: CreateDto) {
        // Validate translations array
        if (!request.translations || request.translations.length === 0) {
            throw new HttpException(responseMessages.en.keywords.translations_required, HttpStatus.BAD_REQUEST)
        }

        // Validate each translation and check for duplicates
        for (const translation of request.translations) {

            const languageCode = translation.language_code;
            if (!translation.keyword) {
                continue;
            }
            const keyword = translation.keyword.trim() || null;

            // Check if keyword exists in translations for this language
            const existsKeyword = await this.KeywordTranslation.findOne({
                where: { keyword: keyword, language_code: languageCode }
            });
            if (existsKeyword) {
                throw new HttpException(responseMessages.en.keywords.already_exists, HttpStatus.NOT_FOUND)
            }
        }

        // Create keyword entity
        const keywordData: any = {
            keyword: request.translations.find(t => t.language_code === 'en')?.keyword.trim() || request.translations[0].keyword.trim()
        };
        // Create keyword
        const keywordResult = await this.keywordsRepository.createData(keywordData);
        const keywordId = keywordResult.id;

        // Create translations for all languages
        const translations = request.translations
            .filter(t => t.keyword !== null && t.keyword?.trim() !== '')
            .map(translation => {
                const keyword = translation.keyword?.trim() || null;
                return {
                    language_code: translation.language_code,
                    keyword: keyword
                };
            });

        await this.keywordsRepository.createTranslations(keywordId, translations);

        return
    }

    /**
     * Update keyword based on request parameters.
     * @param params - An instance of KeywordIdParamDto containing the keyword id to process of update data.
     * @param response - The response object for sending the HTTP response.
     * @param request - An instance of UpdateDto containing the keyword data with translations to process of update data.
     * @throws HttpException - Throws a 404 error if the keyword does not exist.
     * @throws HttpException - Throws a 404 error if the keyword duplicate exist.
     * @returns Responds with a 201 status if successful.
     * In case of an error, logs the error and responds with the respective error status and message.
     */
    async update(params: KeywordIdParamDto, request: UpdateDto) {
        const existsKeyword = await this.Keywords.findOne({ where: { id: params.keyword_id } })
        if (!existsKeyword) {
            throw new HttpException(responseMessages.en.keywords.not_exists, HttpStatus.NOT_FOUND)
        }

        // Validate translations array
        if (!request.translations || request.translations.length === 0) {
            throw new HttpException(responseMessages.en.keywords.translations_required, HttpStatus.BAD_REQUEST)
        }

        // Validate each translation and check for duplicates (excluding current keyword)
        for (const translation of request.translations) {
            const languageCode = translation.language_code;
            if (!translation.keyword) {
                continue;
            }
            const keyword = translation.keyword.trim() || null;
            // Check if keyword exists in translations for this language (excluding current keyword)
            const existsKeywordTranslation = await this.KeywordTranslation.findOne({
                where: {
                    keyword: keyword,
                    language_code: languageCode
                },
                relations: ['keywordRef']
            });
            if (existsKeywordTranslation && existsKeywordTranslation.keywordRef.id !== params.keyword_id) {
                throw new HttpException(responseMessages.en.keywords.already_exists, HttpStatus.NOT_FOUND)
            }
        }

        // Update keyword if English translation exists
        const enTranslation = request.translations.find(t => t.language_code === 'en');
        if (enTranslation) {
            existsKeyword.keyword = enTranslation.keyword.trim();
            await this.Keywords.save(existsKeyword);
        }

        // Process each translation - update if exists, create if not
        for (const translation of request.translations) {
            const languageCode = translation.language_code;
            if (!translation.keyword) {
                continue;
            }
            const keyword = translation.keyword.trim() || null;

            // Check if translation exists for this language
            const existingTranslation = await this.KeywordTranslation.findOne({
                where: {
                    keywordRef: { id: params.keyword_id },
                    language_code: languageCode
                }
            });

            if (existingTranslation) {
                // Update existing translation
                existingTranslation.keyword = keyword;
                await this.KeywordTranslation.save(existingTranslation);
            } else {
                // Create new translation
                const newTranslation = {
                    language_code: languageCode,
                    keyword: keyword
                };
                await this.keywordsRepository.createTranslations(params.keyword_id, [newTranslation]);
            }
        }

        return
    }

    /** JP-
     * Retrieves a list of keywords for admin with two languages (en as default and requested language).
     *
     * @param queryParam - Query parameters for listing keyword list, including pagination, sorting options, and language_code.
     * @returns Keyword list with translations in two languages: English (en) as default and requested language.
     */
    async adminList(queryParam: any) {
        const page = queryParam.page - 1
        const pageSize = queryParam.per_page ? queryParam.per_page : Constants.keywords_list
        const offset = page * pageSize
        const nextPage = page + 1
        const nextOffset = nextPage * pageSize

        // Get language code from query, default to 'en'
        const requestedLanguageCode = queryParam.language_code || "en"

        // If requested language is 'en', we only need English translations
        const languageCodes = requestedLanguageCode === "en" ? ["en"] : ["en", requestedLanguageCode]

        let sortByArr = {
            created_at: "keywords.created_at",
            keyword: "sort_keyword"
        }

        let sort_by = queryParam.sort_by || "keyword"
        let sort_type = queryParam.sort || "ASC"
        sort_by = sortByArr[sort_by] || "sort_keyword"

        // Build query to load keywords with translations in both languages
        let query = this.Keywords.createQueryBuilder("keywords")
            .leftJoin("keywords.translations", "translation_en", "translation_en.language_code = 'en' AND translation_en.deleted_at IS NULL")
            .leftJoin("keywords.translations", "translation_req", "translation_req.language_code = :requestedLanguageCode AND translation_req.deleted_at IS NULL", { requestedLanguageCode })
            // Load only English and requested language translations
            .leftJoinAndSelect("keywords.translations", "translation",
                `translation.language_code IN (:...languageCodes) AND translation.deleted_at IS NULL`,
                { languageCodes })
            .where("keywords.deleted_at IS NULL")
            // Ensure keyword has at least English translation
            .andWhere("translation_en.id IS NOT NULL")
        query = query.addSelect(
            "COALESCE(translation_en.keyword, translation_req.keyword)",
            "sort_keyword"
        );
        if (queryParam.search) {
            query = query.andWhere(`(
                COALESCE(translation_en.keyword, '') ILIKE :search 
                OR COALESCE(translation_req.keyword, '') ILIKE :search
            )`, {
                search: `%${queryParam.search}%`
            })
        }

        // Order by English keyword first, then requested language keyword
        query = query.orderBy(sort_by, sort_type)

        const [results, total] = await query.skip(offset).take(pageSize).getManyAndCount()

        // Filter translations to only include English and requested language, with English always at index 0
        const filteredResults = results.map(keyword => {
            const filteredTranslations = keyword.translations
                .filter(translation => languageCodes.includes(translation.language_code))
                .sort((a, b) => {
                    // English ('en') always comes first
                    if (a.language_code === 'en') return -1;
                    if (b.language_code === 'en') return 1;
                    // Other languages maintain their order
                    return 0;
                });
            return {
                ...keyword,
                translations: filteredTranslations
            }
        })

        return {
            data: filteredResults,
            total: total,
            hasMoreResults: total > nextOffset ? 1 : 0
        }
    }

    /** JP-
     * Retrieves a list of keywords on query parameters.
     *
     * @param queryParam - Query parameters for listing keyword list, including pagination and sorting options.
     * @param req - The request object, containing the authenticated user information.
     * @param response - The response object for sending the JSON response.
     *
     * Responds with a 200 status and the list of keywords if successful.
     * Logs the error and responds with the error status and message if an exception occurs.
     */
    async list(queryParam: any, authUser: any) {
        const page = queryParam.page - 1
        const pageSize = queryParam.per_page ? queryParam.per_page : Constants.keywords_list
        const offset = page * pageSize
        const nextPage = page + 1
        const nextOffset = nextPage * pageSize

        // Get language code from query or user preference, default to 'en'
        const languageCode = authUser?.language || "en"

        let sortByArr = {
            created_at: "keywords.created_at",
            keyword: "COALESCE(translation_req.keyword, translation_en.keyword)"
        }

        let sort_by = queryParam.sort_by || "keyword"
        let sort_type = queryParam.sort || "ASC"
        sort_by = sortByArr[sort_by] || "translation_req.keyword"

        // Build query with fallback to English - load only requested language or English translation
        let query = this.Keywords.createQueryBuilder("keywords")
            .leftJoin("keywords.translations", "translation_req", "translation_req.language_code = :languageCode AND translation_req.deleted_at IS NULL", { languageCode })
            .leftJoin("keywords.translations", "translation_en", "translation_en.language_code = 'en' AND translation_en.deleted_at IS NULL")
            // Load only the requested language translation, or English if requested language doesn't exist
            .leftJoinAndSelect("keywords.translations", "translation",
                `(translation.language_code = :languageCode OR (translation.language_code = 'en' AND translation_req.id IS NULL)) AND translation.deleted_at IS NULL`,
                { languageCode })
            .where("keywords.deleted_at IS NULL")
            // Ensure keyword has at least requested language or English translation
            .andWhere("(translation_req.id IS NOT NULL OR translation_en.id IS NOT NULL)")

        if (queryParam.search) {
            query = query.andWhere(`(
                COALESCE(translation_req.keyword, translation_en.keyword, '') ILIKE :search 
            )`, {
                search: `%${queryParam.search}%`
            })
        }

        // Order by preferred language keyword, fallback to English
        // For COALESCE expressions, we need to add the expression as a select field so TypeORM can use it in ORDER BY
        if (sort_by.includes("COALESCE")) {
            // Add the COALESCE expression as a select field with an alias
            query = query.addSelect(sort_by, "sort_field")
            query = query.addOrderBy("sort_field", sort_type)
        } else {
            query = query.orderBy(sort_by, sort_type)
        }

        const [results, total] = await query.skip(offset).take(pageSize).getManyAndCount()

        return {
            data: results,
            total: total,
            hasMoreResults: total > nextOffset ? 1 : 0
        }
    }

    async delete(params: KeywordIdParamDto) {
        const { keyword_id } = params

        // Check if keyword is in use (similar to category check)
        // You may need to add relations to check if keyword is used elsewhere
        await this.Keywords.softDelete({ id: keyword_id })
        await this.KeywordTranslation.softDelete({ keywordRef: { id: keyword_id } })
        await this.UserKeywords.delete({ keywords: { id: keyword_id } })
        return
    }

    /**
     * Get keyword details by ID with translation support.
     * @param params - An instance of KeywordIdParamDto containing the keyword id.
     * @param languageCode - Optional language code for translation (defaults to 'en').
     * @param authUser - Optional authenticated user for role-based access.
     * @returns Keyword details with translation for the specified language.
     * @throws HttpException - Throws a 404 error if the keyword does not exist.
     */
    async detail(params: KeywordIdParamDto, authUser?: any) {
        const keyword = await this.Keywords.createQueryBuilder("keywords")
            .leftJoinAndSelect("keywords.translations", "translation", "translation.deleted_at IS NULL")
            .where("keywords.id = :keywordId", { keywordId: params.keyword_id })
            .andWhere("keywords.deleted_at IS NULL")
            .getOne();

        if (!keyword) {
            throw new HttpException(responseMessages.en.keywords.not_exists, HttpStatus.NOT_FOUND)
        }

        return { keyword: keyword }
    }

    /**
     * Script: translate all English keyword_translations to Korean (language_code: ko).
     * Uses OpenAI. Skips rows that already have Korean unless overwrite is true.
     */
    async convertEnglishKeywordsToKorean() {
        const overwrite = false;
        const batchSize = 50;

        const englishTranslations = await this.KeywordTranslation.createQueryBuilder('kt')
            .innerJoinAndSelect('kt.keywordRef', 'keyword')
            .where('kt.language_code = :en', { en: 'en' })
            .andWhere('kt.deleted_at IS NULL')
            .andWhere('keyword.deleted_at IS NULL')
            .andWhere("TRIM(kt.keyword) <> ''")
            .orderBy('kt.created_at', 'ASC')
            .getMany();

        const pending: { english: KeywordTranslation; englishText: string }[] = [];

        for (const englishRow of englishTranslations) {
            const englishText = englishRow.keyword.trim();
            const existingKo = await this.KeywordTranslation.findOne({
                where: {
                    keywordRef: { id: englishRow.keywordRef.id },
                    language_code: KOREAN_LANGUAGE_CODE,
                },
            });

            if (existingKo && !overwrite) {
                continue;
            }

            pending.push({ english: englishRow, englishText });
        }

        const limitedPending = pending;

        let inserted = 0;
        let updated = 0;
        let skipped = englishTranslations.length - pending.length;
        let failed = 0;
        const errors: { keyword_id: string; english: string; message: string }[] = [];

        for (let i = 0; i < limitedPending.length; i += batchSize) {
            const batch = limitedPending.slice(i, i + batchSize);
            const englishTexts = batch.map((row) => row.englishText);
            console.log(englishTexts);
            let koreanTexts: string[];
            try {
                koreanTexts = await this.openAIService.translateKeywordsToKorean(englishTexts);
                console.log(koreanTexts);
            } catch (error) {
                for (const row of batch) {
                    failed++;
                    errors.push({
                        keyword_id: row.english.keywordRef.id,
                        english: row.englishText,
                        message: error?.message || 'Translation batch failed',
                    });
                }
                continue;
            }

            for (let j = 0; j < batch.length; j++) {
                const { english, englishText } = batch[j];
                const koreanText = koreanTexts[j]?.trim();

                if (!koreanText) {
                    skipped++;
                    continue;
                }

                try {
                    const duplicate = await this.KeywordTranslation.findOne({
                        where: { keyword: koreanText, language_code: KOREAN_LANGUAGE_CODE },
                        relations: ['keywordRef'],
                    });

                    if (duplicate && duplicate.keywordRef.id !== english.keywordRef.id) {
                        skipped++;
                        errors.push({
                            keyword_id: english.keywordRef.id,
                            english: englishText,
                            message: `Korean translation "${koreanText}" already used by another keyword`,
                        });
                        continue;
                    }

                    const existingKo = await this.KeywordTranslation.findOne({
                        where: {
                            keywordRef: { id: english.keywordRef.id },
                            language_code: KOREAN_LANGUAGE_CODE,
                        },
                    });

                    if (existingKo) {
                        existingKo.keyword = koreanText;
                        await this.KeywordTranslation.save(existingKo);
                        updated++;
                    } else {
                        await this.keywordsRepository.createTranslations(english.keywordRef.id, [
                            { language_code: KOREAN_LANGUAGE_CODE, keyword: koreanText },
                        ]);
                        inserted++;
                    }
                } catch (error) {
                    failed++;
                    errors.push({
                        keyword_id: english.keywordRef.id,
                        english: englishText,
                        message: error?.message || 'Save failed',
                    });
                }
            }

            if (i + batchSize < limitedPending.length) {
                await this.delay(500);
            }
        }

        return {
            total_english: englishTranslations.length,
            processed: limitedPending.length,
            inserted,
            updated,
            skipped,
            failed,
            errors: errors.slice(0, 50),
        };
    }
}