package main

import (
	"bytes"
	"context"
	"crypto/rand"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"encoding/xml"
	"fmt"
	"io/ioutil"
	"log"
	"math"
	"math/big"
	"net/http"
	"net/mail"
	"net/url"
	"os"
	"path/filepath"
	"pocketbase/utils"
	"strings"
	"time"

	firebase "firebase.google.com/go"
	"firebase.google.com/go/messaging"
	"github.com/golang-jwt/jwt/v4"
	"github.com/joho/godotenv"
	"github.com/labstack/echo/v5"
	"github.com/pocketbase/dbx"
	"github.com/pocketbase/pocketbase"
	"github.com/pocketbase/pocketbase/apis"
	"github.com/pocketbase/pocketbase/core"
	"github.com/pocketbase/pocketbase/models"
	"github.com/pocketbase/pocketbase/tokens"
	"github.com/pocketbase/pocketbase/tools/mailer"
	"github.com/pocketbase/pocketbase/tools/types"
	"google.golang.org/api/option"
	// "github.com/pocketbase/pocketbase/tools/hook"
	// "github.com/pocketbase/pocketbase/auth"
)

var baseUrl = os.Getenv("BASE_URL")
var liveBaseUrl = os.Getenv("LIVE_BASE_URL")

type EmailConfigurationResponse struct {
	Page       int `json:"page"`
	PerPage    int `json:"perPage"`
	TotalItems int `json:"totalItems"`
	TotalPages int `json:"totalPages"`
	Items      []struct {
		Type  string `json:"type"`
		Email string `json:"email"`
	} `json:"items"`
}

type GenericData struct {
	XMLName xml.Name `xml:"GenericData"`
	DataSet []Obs    `xml:"DataSet>Obs"`
}

type Obs struct {
	ObsKey   []ObsKey `xml:"ObsKey>Value"` // Dynamic key-value pairs
	ObsValue ObsValue `xml:"ObsValue"`
}

type ObsKey struct {
	ID    string `xml:"id,attr"`    // Attribute for 'id'
	Value string `xml:"value,attr"` // Attribute for 'value'
}

type ObsValue struct {
	Value string `xml:"value,attr"` // Attribute for 'value'
}

// Helper function to map ObsKey into a dictionary
func mapObsKey(keys []ObsKey) map[string]string {
	result := map[string]string{}
	for _, key := range keys {
		result[key.ID] = key.Value
	}
	return result
}

// Helper function to clean XML namespace prefixes
func removeNamespace(xmlBytes []byte) string {
	return strings.ReplaceAll(string(xmlBytes), "generic:", "")
}

func refererLooksLikeGoogleOAuth(referer string) bool {
	if referer == "" {
		return false
	}

	u, err := url.Parse(referer)
	if err != nil {
		return false
	}

	h := strings.ToLower(u.Hostname())
	return strings.Contains(h, "google.com") || strings.Contains(h, "googleusercontent.com")
}

// Function to fetch XML from an API and convert it to JSON
func fetchAndConvertXMLToJSON(apiURL string, outputDir string, fileName string, tableName string, id string) ([]map[string]interface{}, error) {
	// Call the API and get the XML response
	resp, err := http.Get(apiURL)
	if err != nil {
		return nil, err
	}
	defer resp.Body.Close()

	// Read the response body
	body, err := ioutil.ReadAll(resp.Body)
	if err != nil {
		return nil, err
	}
	// fmt.Println("Response url:", apiURL)
	// fmt.Println("Response status:", resp.Status)
	// fmt.Println("Response body:", string(body))

	// Clean the XML by removing namespace prefixes
	cleanXML := []byte(removeNamespace(body))

	// Parse the cleaned XML data
	var data GenericData
	err = xml.Unmarshal(cleanXML, &data)
	if err != nil {
		return nil, err
	}

	// Transform the data for JSON output
	response := []map[string]interface{}{}
	for _, obs := range data.DataSet {
		obsMap := map[string]interface{}{
			"ObsKey":   mapObsKey(obs.ObsKey),
			"ObsValue": obs.ObsValue,
		}
		response = append(response, obsMap)
	}

	// Convert the response to JSON
	jsonData, err := json.MarshalIndent(response, "", "  ")
	if err != nil {
		return nil, err
	}

	// Save the JSON data to a file if required
	// Ensure the folder exists
	if err := os.MkdirAll(outputDir, os.ModePerm); err != nil {
		return nil, err
	}

	// Save the file
	file := fmt.Sprintf("%s/%s.json", outputDir, fileName)
	if err := ioutil.WriteFile(file, jsonData, 0644); err != nil {
		return nil, err
	}

	// Find the charts record
	record, err := app.Dao().FindRecordById(tableName, id)
	if err != nil {
		// return c.JSON(500, map[string]interface{}{"code":    500,"message": "Error finding user.",})
	}
	record.Set("file_status", true)
	if err := app.Dao().SaveRecord(record); err != nil {
		// return c.JSON(500, map[string]interface{}{"code":    500,"message": "Failed to verify user.",})
	}

	return response, nil
}

// Function to read JSON data from a file
func readJSONFromFile(folderName, fileName string) ([]map[string]interface{}, error) {
	// Construct the file path dynamically using the folder name
	filePath := fmt.Sprintf("%s/%s.json", folderName, fileName)

	// Read and return the JSON content from the file
	fileContent, err := ioutil.ReadFile(filePath)
	if err != nil {
		return nil, fmt.Errorf("failed to read file '%s': %w", filePath, err)
	}

	// Parse the JSON content from the file
	var jsonResponse []map[string]interface{}
	if err := json.Unmarshal(fileContent, &jsonResponse); err != nil {
		return nil, fmt.Errorf("failed to parse JSON from file '%s': %w", filePath, err)
	}

	return jsonResponse, nil
}

type ResponseItem struct {
	ObsKey   map[string]string `json:"ObsKey"`
	ObsValue map[string]string `json:"ObsValue"`
}

type FilterJsonData struct {
	Order int    `json:"order"`
	En    string `json:"en"`
	Ar    string `json:"ar"`
}

type FilterGroup struct {
	FilterKey          string         `json:"filter_key"`
	FilterData         []interface{}  `json:"filter_data"`
	FilterTextAndOrder FilterJsonData `json:"filter_text_and_order"`
}

func generateFilters(responseRaw []map[string]interface{}, filterKeys []string, filterJsonData map[string]FilterJsonData) ([]FilterGroup, error) {
	// Transform responseRaw to []ResponseItem
	var response []ResponseItem
	responseBytes, err := json.Marshal(responseRaw) // Marshal raw data to JSON
	if err != nil {
		log.Printf("Failed to marshal raw response: %v", err)
		return nil, err
	}

	if err := json.Unmarshal(responseBytes, &response); err != nil { // Unmarshal to []ResponseItem
		log.Printf("Failed to unmarshal response: %v", err)
		return nil, err
	}

	// Create a map to hold unique filter data
	filterMap := make(map[string]map[string]struct{})
	for _, key := range filterKeys {
		filterMap[key] = make(map[string]struct{})
	}

	// Populate filter data dynamically
	for _, item := range response {
		for _, key := range filterKeys {
			if value, exists := item.ObsKey[key]; exists {
				filterMap[key][value] = struct{}{}
			}
		}
	}

	// Convert filter map to the desired structure
	var filters []FilterGroup
	for key, values := range filterMap {
		filterData := make([]interface{}, 0, len(values))
		for value := range values {
			filterData = append(filterData, value)
		}

		if key == "REF_AREA" {
			filterData = []interface{}{"AE-AZ", "AE-DU", "AE-SH", "AE-AJ", "AE-UQ", "AE-RK", "AE-FJ"}
		}
		filters = append(filters, FilterGroup{
			FilterKey:          key,
			FilterData:         filterData,
			FilterTextAndOrder: filterJsonData[key],
		})
	}

	return filters, nil
}

// Function to fetch filter data of dataset
func fetchfilterJSON(kpi string) ([]FilterGroup, error) {

	// Define struct for the result
	var dataSetFilter struct {
		Id                   string `db:"id" json:"id"`
		FullDataUrl          string `db:"full_data_url" json:"full_data_url"`
		FilterKeys           string `db:"filter_keys" json:"filter_keys"`
		FileStatus           bool   `db:"file_status" json:"file_status"`
		DataSet              string `db:"dataset" json:"dataset"`
		Kpi                  string `db:"kpi" json:"kpi"`
		ExclusionFiltersJSON string `db:"exclusion_filters_array" json:"exclusion_filters_array"`
		FilterJson           string `db:"filter_json" json:"filter_json"`
	}

	// Query to get one row from the data_set_filter table
	err := app.DB().
		Select("id", "full_data_url", "filter_keys", "file_status", "dataset", "kpi", "filter_json", "exclusion_filters_array").
		From("data_set_filter").
		Where(dbx.HashExp{"kpi": kpi}).
		One(&dataSetFilter)

	if err != nil {
		log.Printf("Failed to fetch data_set_filter data: %v", err)
		return nil, err
	}

	// Use a map to store the dynamic JSON keys
	var filterJson map[string]FilterJsonData

	// Parse the JSON
	err = json.Unmarshal([]byte(dataSetFilter.FilterJson), &filterJson)
	if err != nil {
		log.Fatalf("Error parsing JSON: %v", err)
	}

	// Fetch and convert XML to JSON if file is not saved
	if !dataSetFilter.FileStatus { // Check if FileStatus is false
		_, err := fetchAndConvertXMLToJSON(dataSetFilter.FullDataUrl, "filter_files", dataSetFilter.Kpi, "data_set_filter", dataSetFilter.Id)
		if err != nil {
			log.Printf("Failed to process URL '%s': %v", dataSetFilter.FullDataUrl, err)
			return nil, err
		}
	}

	// Read the JSON data from the file
	response, err := readJSONFromFile("filter_files", dataSetFilter.Kpi)
	if err != nil {
		log.Printf("Failed to read JSON for Kpi filter '%s': %v", dataSetFilter.Kpi, err)
		return nil, err
	}

	// Parse the filter keys
	filterKeys := strings.Split(dataSetFilter.FilterKeys, ",")

	// Generate filters
	filters, err := generateFilters(response, filterKeys, filterJson)
	if err != nil {
		log.Printf("Failed to generate filters: %v", err)
		return nil, err
	}

	// Parse exclusion filters JSON if it's not empty
	var exclusionFilters []utils.FilterGroup
	if dataSetFilter.ExclusionFiltersJSON != "" {
		err = json.Unmarshal([]byte(dataSetFilter.ExclusionFiltersJSON), &exclusionFilters)
		if err != nil {
			log.Printf("Failed to parse exclusion filters JSON: %v", err)
			return nil, err
		}
		// log.Printf("Parsed Exclusion Filters: %+v", exclusionFilters)
	}

	// Convert filters from []FilterGroup to []utils.FilterGroup manually
	var utilsFilters []utils.FilterGroup
	for _, f := range filters {
		utilsFilters = append(utilsFilters, convertToUtilsFilterGroup(f))
	}

	// Apply the filter removal logic only if exclusionFilters has values
	if len(exclusionFilters) > 0 {
		filteredResult := utils.RemoveExcludedFilters(utilsFilters, exclusionFilters)

		// Convert back to []FilterGroup before returning
		var finalFilters []FilterGroup
		for _, f := range filteredResult {
			finalFilters = append(finalFilters, convertToFilterGroup(f))
		}

		return finalFilters, nil
	}

	// If no exclusion filters, return the original filters
	return filters, nil

}

// Convert FilterGroup to utils.FilterGroup
func convertToUtilsFilterGroup(f FilterGroup) utils.FilterGroup {
	return utils.FilterGroup{
		FilterKey:  f.FilterKey,
		FilterData: f.FilterData,
		FilterTextAndOrder: utils.FilterJsonData{
			Order: f.FilterTextAndOrder.Order,
			En:    f.FilterTextAndOrder.En,
			Ar:    f.FilterTextAndOrder.Ar,
		},
	}
}

// Convert utils.FilterGroup to FilterGroup
func convertToFilterGroup(f utils.FilterGroup) FilterGroup {
	return FilterGroup{
		FilterKey:  f.FilterKey,
		FilterData: f.FilterData,
		FilterTextAndOrder: FilterJsonData{
			Order: f.FilterTextAndOrder.Order,
			En:    f.FilterTextAndOrder.En,
			Ar:    f.FilterTextAndOrder.Ar,
		},
	}
}

// LanguageSource represents the source language data structure
type LanguageSource struct {
	Dataset string `json:"dataset"`
	Key     string `json:"key"`
	Value   string `json:"value"`
	ValueEn string `json:"value_en"`
	ValueAr string `json:"value_ar"`
}

func TranslateFilters(app *pocketbase.PocketBase, dataset string, filterGroups []FilterGroup, langKey string) ([]FilterGroup, error) {
	// Fetch source data from the PocketBase collection
	param := dbx.NewExp("dataset = {:dataset}", dbx.Params{"dataset": dataset})
	records, err := app.Dao().FindRecordsByExpr("charts_variables", param)
	if err != nil {
		return nil, fmt.Errorf("failed to fetch source data: %w", err)
	}

	// Parse the source data into a slice of LanguageSource
	var sourceData []LanguageSource
	for _, record := range records {
		source := LanguageSource{
			Dataset: record.GetString("dataset"),
			Key:     record.GetString("key"),
			Value:   record.GetString("value"),
			ValueEn: record.GetString("value_en"),
			ValueAr: record.GetString("value_ar"),
		}
		sourceData = append(sourceData, source)
	}

	// Translate the filter data
	for i, filter := range filterGroups {

		// Create a map for quick lookup of translations
		translationMap := make(map[string]string)
		for _, source := range sourceData {
			var value string
			switch langKey {
			case "value_en":
				value = source.ValueEn
			case "value_ar":
				value = source.ValueAr
			default:
				continue
			}
			if source.Key == filter.FilterKey {
				translationMap[source.Value] = value
			}

		}

		if filter.FilterKey == "TIME_PERIOD" {
			// Skip translation for TIME_PERIOD
			continue
		}
		for j, v := range filter.FilterData {
			// Type assertion for interface{} to string
			value, ok := v.(string)
			if !ok {
				return nil, fmt.Errorf("unexpected type for filter data: %v", v)
			}

			// Translate the value if it exists in the map
			if translatedValue, exists := translationMap[value]; exists {
				filterGroups[i].FilterData[j] = translatedValue
			}
		}
	}

	return filterGroups, nil
}

// Generic function to filter JSON data dynamically based on key-value pairs
func filterDynamicChartData(chartData []map[string]interface{}, keyToFilter, valueToExclude string) []map[string]interface{} {
	var filteredData []map[string]interface{}

	for _, item := range chartData {
		obsKey, ok := item["ObsKey"].(map[string]interface{})
		if !ok {
			// Skip items without "ObsKey" or malformed data
			continue
		}

		// Check if the key exists and matches the value to exclude
		if obsKey[keyToFilter] == valueToExclude {
			continue
		}

		// Add item to filtered data
		filteredData = append(filteredData, item)
	}

	return filteredData
}

func generateToken() string {
	bytes := make([]byte, 32)
	_, err := rand.Read(bytes)
	if err != nil {
		log.Fatal(err)
	}
	return hex.EncodeToString(bytes)
}

var app *pocketbase.PocketBase

func main() {

	// load .env file
	err := godotenv.Load()
	if err != nil {
		log.Println("No .env file found")
	}

	var baseUrl = os.Getenv("BASE_URL")

	app = pocketbase.New()

	app.OnRecordAuthRequest("users").Add(func(e *core.RecordAuthEvent) error {

		e.Record.Set("lastLogin", time.Now().UTC())

		return app.Dao().SaveRecord(e.Record)
	})

	app.OnBeforeServe().Add(func(e *core.ServeEvent) error {
		e.Router.Use(func(next echo.HandlerFunc) echo.HandlerFunc {
			return func(c echo.Context) error {
				if c.Request().URL.Path == "/api/oauth2-redirect" &&
					(strings.EqualFold(c.QueryParam("provider"), "google") ||
						refererLooksLikeGoogleOAuth(c.Request().Referer())) {
					return c.File("pb_public/oauth2-redirect-success.html")
				}

				return next(c)
			}
		})

		// Direct success page routes (non-hash paths that reach the backend).
		e.Router.GET("/auth/oauth2-redirect-success", func(c echo.Context) error {
			return c.File("pb_public/oauth2-redirect-success.html")
		})
		e.Router.GET("/_/auth/oauth2-redirect-success", func(c echo.Context) error {
			return c.File("pb_public/oauth2-redirect-success.html")
		})

		// Serve privacy policy static page
		e.Router.GET("/privacy/*", apis.StaticDirectoryHandler(os.DirFS("./pb_public/privacy"), false))

		// API endpoint: returns JSON policy
		e.Router.GET("/api/privacy", func(c echo.Context) error {
			policy := map[string]string{
				"title":   "Privacy Policy",
				"content": "We respect your privacy. Data is collected only for authentication and secure service delivery.",
			}
			return c.JSON(http.StatusOK, policy)
		})

		return nil
	})

	// Hook to send notifications when a new record is created
	app.OnRecordAfterCreateRequest("notification").Add(func(e *core.RecordCreateEvent) error {
		title_en := e.Record.GetString("title_en")
		message_en := e.Record.GetString("message_en")
		title_ar := e.Record.GetString("title_ar")
		message_ar := e.Record.GetString("message_ar")
		notificationID := e.Record.GetString("id") // Get the new notification ID

		// Initialize Firebase
		opt := option.WithCredentialsFile("firebase-adminsdk.json")
		firebaseApp, err := firebase.NewApp(context.Background(), nil, opt)
		if err != nil {
			log.Fatalf("Error initializing Firebase: %v", err)
		}

		fcmClient, err := firebaseApp.Messaging(context.Background())
		if err != nil {
			log.Fatalf("Error getting FCM client: %v", err)
		}

		// Fetch users with valid device tokens
		users, err := app.Dao().FindRecordsByExpr("users", nil) // Adjust collection name if different
		if err != nil {
			return err
		}

		for _, user := range users {
			token := user.GetString("device_token")
			language := user.GetString("language")
			if token != "" {
				// tokens = append(tokens, token)
				var message *messaging.Message
				if language == "en" {
					message = &messaging.Message{
						Token: token,
						Notification: &messaging.Notification{
							Title: title_en,
							Body:  message_en,
						},
					}
				} else {
					message = &messaging.Message{
						Token: token,
						Notification: &messaging.Notification{
							Title: title_ar,
							Body:  message_ar,
						},
					}

				}

				response, err := fcmClient.Send(context.Background(), message)
				if err != nil {

					if err.Error() == "messaging/registration-token-not-registered" {
						log.Println("⚠️ Device token is invalid. Remove it from the database.")
						// Here you should remove the token from your database
					} else {
						log.Printf("Error sending message: %v", err)
					}
				} else {
					fmt.Printf("✅ Successfully sent message: %s\n", response)

					// Update the pushed_notification field
					var pushedNotifications []string

					// Get existing pushed_notification JSON field
					pushedNotificationsJSON := user.GetString("pushed_notification")
					if pushedNotificationsJSON != "" {
						err := json.Unmarshal([]byte(pushedNotificationsJSON), &pushedNotifications)
						if err != nil {
							log.Printf("⚠️ Error parsing pushed_notification for user %s: %v", user.GetString("id"), err)
							continue
						}
					}

					// Append the new notification ID if not already present
					alreadyExists := false
					for _, existingID := range pushedNotifications {
						if existingID == notificationID {
							alreadyExists = true
							break
						}
					}
					if !alreadyExists {
						pushedNotifications = append(pushedNotifications, notificationID)
					}

					// Convert back to JSON
					updatedPushedNotifications, err := json.Marshal(pushedNotifications)
					if err != nil {
						log.Printf("⚠️ Error marshalling pushed_notification JSON: %v", err)
						continue
					}

					// Update the user record in the database
					user.Set("pushed_notification", string(updatedPushedNotifications))
					if err := app.Dao().SaveRecord(user); err != nil {
						log.Printf("❌ Error updating pushed_notification for user %s: %v", user.GetString("id"), err)
					} else {
						fmt.Printf("✅ Updated pushed_notification for user %s\n", user.GetString("id"))
					}
				}

			}
		}

		return nil
	})

	app.OnBeforeServe().Add(func(e *core.ServeEvent) error {

		e.Router.GET("/api/getImagePath", func(c echo.Context) error {

			fileName := c.QueryParam("file")
			if fileName == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{
					"error": "missing file parameter",
				})
			}

			// Static files folder
			staticPath := "./pb_public"
			fullPath := filepath.Join(staticPath, fileName)

			// Check if file exists
			if _, err := os.Stat(fullPath); os.IsNotExist(err) {
				return c.JSON(http.StatusNotFound, map[string]string{
					"error": "file not found",
				})
			}

			// Serve the file directly (browser will show the image)
			return c.File(fullPath)
		})

		// e.Router.Static("/static", "./pb_public")

		e.Router.GET("/pw/auth/confirm-password-reset/:token", func(c echo.Context) error {
			return c.File("pb_public/confirm_password_reset.html")
		})

		e.Router.GET("/api/auth/request-password-reset", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			email := c.QueryParam("email")
			if email == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "Email is required"})
			}

			// Find user by email
			user, err := app.Dao().FindFirstRecordByData("users", "email", email)
			if err != nil {
				return c.JSON(http.StatusNotFound, map[string]string{"error": "User not found"})
			}

			//check the user type
			isOAuthLogin := user.GetString("is_oauth_login")
			if isOAuthLogin != "0" {
				return c.JSON(http.StatusOK, map[string]string{"status": "failed", "message": "Password reset is not available for accounts signed in with Apple or Google."})
			}

			name := user.GetString("name")
			// Generate token
			token := generateToken()
			expiration := time.Now().Add(30 * time.Minute).UTC()

			// Store token in the database
			user.Set("pw_reset_token", token)
			user.Set("pw_reset_token_expiry", expiration)

			if err := app.Dao().SaveRecord(user); err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to update user record"})
			}

			// Send email
			subject := "Reset your FCSC password"
			// resetURL := fmt.Sprintf("%s/api/auth/reset-password?token=%s", baseUrl, token)

			resetURL := fmt.Sprintf("https://fcscapp.onelink.me/forgot-password/%s", email)

			body := fmt.Sprintf(`
			<!DOCTYPE html>
			<html>
			<head>
				<meta charset="UTF-8">
				<meta name="viewport" content="width=device-width, initial-scale=1">
				<title>Registration Update – UAE Stats App</title>
			</head>
			<body style="font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f8f8f8;">
				<table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0" align="center">
					<tr>
						<td align="center" style="padding: 20px;">
							<table role="presentation" width="600px" cellspacing="0" cellpadding="0" border="0" style="background-color: #ffffff; border: 2px solid #d1ad5c; padding: 20px;">
								<tr>
									<td align="center" style="font-size: 18px; font-weight: bold;">
										<span style=" font-size:16px; border-bottom:1px solid #d1ad5c;padding-bottom: 10px;">
										إعادة تعيين كلمة المرور - تطبيق إحصاءات الإمارات العربية المتحدة<br>
										</span><br><span style="font-size: 18px;">Reset your FCSC password</span>
									<br><br><br>
									</td>
								</tr>
								<tr>
									<td style="padding-top: 20px; padding-bottom:30px; font-size: 12px;">
										<table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0">
											<tr>
												<td align="left" style="width: 50%%; vertical-align: top;">
													<strong>Hello,</strong><br><br>
													Click on the link below to reset your password.
													<br><br>
													<a href="%s" style="color: #d1ad5c; text-decoration: none; font-weight: bold;">Reset Password</a>
													<br><br>
												If you didn't ask to reset your password, you can ignore this email.
												</td>
												<td align="right" style="width: 50%%; vertical-align: top; direction: rtl;">
													<strong>مرحبًا،</strong><br><br>
													يُرجى الضغط على الرابط أدناه لإعادة تعيين كلمة المرور الخاصة بك:		                                        <br><br>
													<a href="%s" style="color: #d1ad5c; text-decoration: none; font-weight: bold;">إعادة تعيين كلمة المرور</a>
													<br><br>
												إذا لم تطلب إعادة تعيين كلمة المرور، يمكنك تجاهل هذا البريد الإلكتروني.
												</td>
											</tr>
										</table>
									</td>
								</tr>
								<tr>
									<td align="center"  padding-top: 20px;">
										<img src="https://imageats.s3.ap-south-1.amazonaws.com/signature.png" alt="We The UAE 2031" width="100%%">
									</td>
								</tr>
								 <tr>
	                        <td style="padding-top: 20px; font-size: 9px; color: #777; border-top: 1px solid #ccc;direction: rtl; text-align: right;">
	                        <strong>تنبيه:</strong><br>هذه الرسالة الإلكترونية و أيٌ من مرفقاتها قد تحتوي على معلومات سرية وهامة موجهه للشخص/الأشخاص المعنيين وعليه يرجى من المتلقي في حال تلقي الرسالة الإلكترونية عن طريق<br> الخطأ ولم يكن المعني بها، إخطار المرسل وحذفها من بريده الإلكتروني و كذلك إتلاف أي نسخ مطبوعة عنها حيث أنه يحظر عليه قراءة ونسخ ونشر أو توزيع أو استخدام هذه الرسالة<br> إلكترونية و أيٌ من مرفقاتها بأي شكل من الأشكال
	                        </td>
                             </tr>
                             <tr>
                             <td style="padding-top: 20px; font-size: 9px; color: #777;text-align: left; direction: ltr;">
	                            <strong>Disclaimer:</strong> <br>The information contained in and transmitted with this e-mail message is PRIVILEGED AND/OR CONFIDENTIAL.If you are not the intended recipient, or have received the message by error, please notify the sender via E-Mail or over the telephone and delete this e-mail. You are not authorized to read, copy, disseminate, distribute or use this E-Mail or any of its attachments in any way.
	                        </td>
	                    </tr>
							</table>
						</td>
					</tr>
				</table>
			</body>
			</html>`, resetURL, resetURL)

			// Create the email message
			message := &mailer.Message{
				From: mail.Address{
					Name:    "FCSC",
					Address: app.Settings().Meta.SenderAddress,
				},
				To: []mail.Address{
					{
						Name:    name,
						Address: email,
					},
				},
				Subject: subject,
				HTML:    body,
			}

			// Send the email
			err = app.NewMailClient().Send(message)
			if err != nil {
				log.Printf("Failed to send verification email to user: %v", err)
				return err
			}

			return c.JSON(http.StatusOK, map[string]string{"status": "success", "message": "Reset link sent to your email"})
		})

		e.Router.GET("/api/auth/reset-password", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			token := c.QueryParam("token")
			if token == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "Token is required"})
			}

			// Find user with the token
			user, err := app.Dao().FindFirstRecordByData("users", "pw_reset_token", token)
			if err != nil {
				return c.JSON(http.StatusUnauthorized, map[string]string{"error": "Invalid token"})
			}

			// Convert PocketBase `types.DateTime` to `time.Time`
			expiryTime := user.GetDateTime("pw_reset_token_expiry").Time()
			log.Printf("expiryTime : '%v'", expiryTime)
			log.Printf("time.Now : '%v'", time.Now().UTC())
			if expiryTime.Before(time.Now().UTC()) {
				return c.File("pb_public/token_expired.html")
				// return c.JSON(http.StatusUnauthorized, map[string]string{"error": "Token expired"})
			}

			// return c.JSON(http.StatusOK, map[string]string{"message": "Token is valid"})

			// Serve the reset page
			return c.File("pb_public/confirm_password_reset.html")
		})

		e.Router.GET("/api/login_success", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			userID := c.QueryParam("id")
			if userID == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "User ID is required"})
			}

			// Find user by ID
			record, err := app.Dao().FindRecordById("users", userID)
			if err != nil {
				log.Printf("User not found: %v", err)
				return c.JSON(http.StatusNotFound, map[string]string{"error": "User not found"})
			}

			// Get current login count and convert it properly
			var loginCount int
			if count, ok := record.Get("login_count").(float64); ok {
				loginCount = int(count) // Convert float64 to int
			} else {
				loginCount = 0 // Default if not set
			}

			// Increment login count
			loginCount++
			record.Set("login_count", loginCount)

			// Save the updated record
			if err := app.Dao().SaveRecord(record); err != nil {
				log.Printf("Failed to update login count: %v", err)
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to update login count"})
			}

			log.Printf("User %s login count updated to %d", userID, loginCount)
			return c.JSON(http.StatusOK, map[string]interface{}{
				"message":     "Success",
				"login_count": loginCount,
			})

		})

		e.Router.GET("/api/getNotification", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			language := c.QueryParam("language")
			if language == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "language is required"})
			}

			categoryColumn := "category_" + language
			titleColumn := "title_" + language
			messageColumn := "message_" + language

			id := c.QueryParam("id")

			if id != "" {
				// Define a single notification struct, not a slice
				var notification struct {
					Id       string `db:"id" json:"id"`
					Category string `db:"category" json:"category"`
					Title    string `db:"title" json:"title"`
					Message  string `db:"message" json:"message"`
					Created  string `db:"created" json:"created"`
				}

				query := `SELECT id, created, ` + categoryColumn + ` AS category, ` + titleColumn + ` AS title, ` + messageColumn + ` AS message FROM notification WHERE id = {:id}`
				err := app.DB().NewQuery(query).Bind(dbx.Params{"id": id}).One(&notification)

				if err != nil {
					log.Printf("notification not found: %v", err)
					return c.JSON(http.StatusNotFound, map[string]string{"error": "notification not found"})
				}

				return c.JSON(http.StatusOK, map[string]interface{}{
					"message": "Success",
					"data":    notification,
				})
			}

			// Fetch all notifications if no ID is provided
			var notifications []struct {
				Id       string `db:"id" json:"id"`
				Category string `db:"category" json:"category"`
				Title    string `db:"title" json:"title"`
				Message  string `db:"message" json:"message"`
				Created  string `db:"created" json:"created"`
			}

			query := `SELECT id, created, ` + categoryColumn + ` AS category, ` + titleColumn + ` AS title, ` + messageColumn + ` AS message FROM notification`
			err := app.DB().NewQuery(query).All(&notifications)

			if err != nil {
				log.Printf("notifications not found: %v", err)
				return c.JSON(http.StatusNotFound, map[string]string{"error": "notifications not found"})
			}

			return c.JSON(http.StatusOK, map[string]interface{}{
				"message": "Success",
				"data":    notifications,
			})
		})

		e.Router.GET("/api/removeNotification", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			userID := c.QueryParam("user_id")
			notificationID := c.QueryParam("notification_id")

			if userID == "" || notificationID == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "user_id and notification_id are required"})
			}

			// Fetch the user by ID
			user, err := app.Dao().FindRecordById("users", userID)
			if err != nil {
				return c.JSON(http.StatusNotFound, map[string]string{"error": "user not found"})
			}

			var pushedNotifications []string

			// Get existing pushed_notification JSON field
			pushedNotificationsJSON := user.GetString("pushed_notification")
			if pushedNotificationsJSON != "" {
				err := json.Unmarshal([]byte(pushedNotificationsJSON), &pushedNotifications)
				if err != nil {
					log.Printf("⚠️ Error parsing pushed_notification for user %s: %v", userID, err)
					return c.JSON(http.StatusInternalServerError, map[string]string{"error": "failed to parse pushed_notification"})
				}
			}

			// Remove the notification ID if it exists
			updatedNotifications := []string{}
			for _, existingID := range pushedNotifications {
				if existingID != notificationID {
					updatedNotifications = append(updatedNotifications, existingID)
				}
			}

			// Convert updated array back to JSON
			updatedJSON, err := json.Marshal(updatedNotifications)
			if err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "failed to update pushed_notification"})
			}

			// Update the user record with the new pushed_notification field
			user.Set("pushed_notification", string(updatedJSON))
			if err := app.Dao().SaveRecord(user); err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "failed to save updated notifications"})
			}

			return c.JSON(http.StatusOK, map[string]interface{}{
				"message":             "success",
				"pushed_notification": updatedNotifications,
			})
		})

		e.Router.GET("/api/pushNotification", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			// type Request struct {
			// 	DeviceToken string `json:"device_token"`
			// }

			// var requestData Request
			// log.Printf("🔥 requestData: '%v'", requestData)
			// // Validate required fields
			// if requestData.DeviceToken == "" {
			// 	return c.JSON(http.StatusBadRequest, map[string]string{"error": "DeviceToken is required"})
			// }

			// DeviceToken := requestData.DeviceToken

			DeviceToken := c.QueryParam("device_token")

			// Load Firebase credentials JSON file
			opt := option.WithCredentialsFile("firebase-adminsdk.json")
			app, err := firebase.NewApp(context.Background(), nil, opt)
			if err != nil {
				log.Printf("🔥 Error initializing Firebase App: '%v'", err)
			}

			client, err := app.Messaging(context.Background())
			if err != nil {
				log.Printf("Error getting Messaging client: %v", err)
			}

			message := &messaging.Message{
				Token: DeviceToken,
				Notification: &messaging.Notification{
					Title: "Hello!",
					Body:  "This is a test push notification By Gowtham.",
				},
			}

			response, err := client.Send(context.Background(), message)
			if err != nil {

				if err.Error() == "messaging/registration-token-not-registered" {
					log.Println("⚠️ Device token is invalid. Remove it from the database.")
					// Here you should remove the token from your database
				} else {
					log.Printf("Error sending message: %v", err)
				}
			} else {
				fmt.Printf("✅ Successfully sent message: %s\n", response)
			}

			return c.JSON(http.StatusOK, map[string]string{"status": "success"})
		})

		e.Router.POST("/api/getDataSet", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			type RequestData struct {
				Dataset    string         `json:"dataset"`
				KPI        string         `json:"kpi"`
				Language   string         `json:"language"`
				ColorMode  string         `json:"color_mode"`
				FilterData []utils.Filter `json:"filter_data"`
			}

			var requestData RequestData

			// Bind JSON request to struct
			if err := c.Bind(&requestData); err != nil {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "Invalid request format"})
			}

			// Validate required fields
			if requestData.Dataset == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "dataset is required"})
			}

			dataset := requestData.Dataset
			language := requestData.Language
			langKey := "value_" + language
			colorMode := requestData.ColorMode

			// Fetch all matching records from charts table
			param := dbx.NewExp("dataset = {:dataset}", dbx.Params{"dataset": dataset})
			records, err := app.Dao().FindRecordsByExpr("charts", param)
			if err != nil {
				log.Printf("Failed to fetch records for dataset '%s': %v", dataset, err)
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to fetch records"})
			}
			if len(records) == 0 {
				return c.JSON(http.StatusNotFound, map[string]string{"error": "No records found for the given dataset"})
			}

			// Fetch all matching records from charts variables table
			languageSource, err := app.Dao().FindRecordsByExpr("charts_variables", param)
			if err != nil {
				log.Printf("Failed to fetch language source: %v", err)
			}
			// Convert languageSource to []map[string]interface{}
			languageSourceConverted := []map[string]interface{}{}
			for _, record := range languageSource {
				languageSourceConverted = append(languageSourceConverted, record.SchemaData())
			}

			//construct chart and card data based of configuration/calculation/default
			var aggregatedResponse []map[string]interface{}
			for _, record := range records {

				// Extract the URL for each record
				apiURL := record.GetString("url")
				file_name := record.GetString("kpi_file_name")
				if apiURL == "" {
					log.Printf("No URL for record '%v'", record)
					continue // Skip records with no URL
				}

				// Fetch and convert XML to JSON
				saveToFile := record.GetBool("file_status")
				chartId := record.GetString("id")
				if !saveToFile { // Check if saveToFile is false
					_, err := fetchAndConvertXMLToJSON(apiURL, "kpi_files", file_name, "charts", chartId)
					if err != nil {
						log.Printf("Failed to process URL '%s': %v", apiURL, err)
						continue // Skip this record and proceed with others
					}
				}

				// Read the JSON data from the file
				chartData, err := readJSONFromFile("kpi_files", file_name)
				if err != nil {
					log.Printf("Failed to read JSON for KPI '%s': %v", file_name, err)
				}

				// unset key and value if value to be excluded
				keyToFilter := record.GetString("unset_key")
				valueToExclude := record.GetString("unset_value")
				if keyToFilter == "" || valueToExclude == "" {
					// fmt.Println("unset_key or unset_value is empty. Skipping processing.")
				} else {
					// Filter the data dynamically and update chartData
					chartData = filterDynamicChartData(chartData, keyToFilter, valueToExclude)
				}

				//logo url generating
				var logoURL string
				cardLogo := record.GetString("card_logo")
				if cardLogo == "" {
					logoURL = ""
				} else {
					logoURL = fmt.Sprintf("%s/api/files/charts/%s/%s",
						baseUrl,
						record.GetString("id"),
						cardLogo,
					)
				}

				cResRaw := utils.TranslateChartData(chartData, languageSourceConverted, langKey)

				//responce data structure
				chartHeadingKey := "chart_heading_" + language
				chartSubHeadingKey := "chart_sub_heading_" + language
				tabHeadingKey := "tab_heading_" + language
				// Correct conditional assignment for "response"
				recordResult := map[string]interface{}{
					"url":               apiURL,
					"dataset":           record.GetString("dataset"),
					"main_id":           record.GetString("main_id"),
					"sub_id":            record.GetString("sub_id"),
					"kpi":               record.GetString("kpi"),
					"is_chart":          record.GetString("is_chart"),
					"chart_type":        record.GetString("chart_type"),
					"group_by":          record.GetString("group_by"),
					"chart_heading":     record.GetString(chartHeadingKey),
					"chart_sub_heading": record.GetString(chartSubHeadingKey),
					"tab_heading":       record.GetString(tabHeadingKey),
					"unset_value":       record.Get("unset_value"),
					"card_logo":         logoURL,
					"order_id":          record.Get("order_id"),
					"card_type_json":    record.Get("card_type_json"),
					"tab_order":         record.GetString("tab_order"),
					"chart_type_json":   record.Get("chart_type_json"),
					"card_full_length":  record.Get("card_full_length"),
					"chart_bar_color":   record.Get("chart_bar_color"),
					"chart_height":      record.Get("chart_height"),
					"chart_label_width": record.Get("chart_label_width"),
				}

				var cRes []utils.Observation
				cResJSON, err := json.Marshal(cResRaw)
				if err != nil {
					fmt.Println("Error encoding chart data:", err)
				}
				err = json.Unmarshal(cResJSON, &cRes)
				if err != nil {
					fmt.Println("Error decoding chart data:", err)
				}

				if requestData.KPI == record.GetString("kpi") && len(requestData.FilterData) > 0 {

					filteredData, err := utils.FilterByMultipleKeys(cRes, requestData.FilterData)
					if err != nil {
						log.Fatal(err)
					}
					if 0 < len(filteredData) {
						cRes = filteredData
					} else {
						defaultYearsFilteredData, err := utils.DefaultYearFilter(cRes, record.GetString("default_latest_year_count"))
						if err != nil {
							fmt.Println("Error processing defaultYearsFilteredData:", err)
						}
						cRes = defaultYearsFilteredData

					}

				} else {
					defaultYearsFilteredData, err := utils.DefaultYearFilter(cRes, record.GetString("default_latest_year_count"))
					if err != nil {
						fmt.Println("Error processing defaultYearsFilteredData:", err)
					}
					// fmt.Println("defaultYearsFilteredData:", defaultYearsFilteredData)
					cRes = defaultYearsFilteredData
				}

				var cardResults []map[string]string
				if record.GetString("is_chart") == "false" { // card data calculations

					// Extract `card_type` JSON from `record.Get("card_type")`
					requestData := record.Get("card_type_json")
					requestJSON, err := json.Marshal(requestData) // Convert interface{} to JSON string
					if err != nil {
						fmt.Println("Error encoding request data:", err)
					}

					// Decode JSON into an array of `utils.Request`
					var requests []utils.CardCalculationRequest
					err = json.Unmarshal(requestJSON, &requests)
					if err != nil {
						fmt.Println("Error decoding request data:", err)
					}

					if record.GetString("custom_calculation") == "true" {

						customCalculationReq := record.Get("chart_custom_calculation_json")

						var chartCustomCalculationRequest utils.ChartCalculationRequest

						jsonData, err := json.Marshal(customCalculationReq)
						if err != nil {
							log.Println("Error marshaling to JSON:", err)
						}

						err = json.Unmarshal(jsonData, &chartCustomCalculationRequest)
						if err != nil {
							log.Println("Error unmarshalling JSON:", err)
						}
						customResult, err := utils.CalculateGroupedSum(cRes, chartCustomCalculationRequest)
						if err != nil {
							fmt.Println("Error:", err)
						}

						cRes = customResult

					}

					// Process each request and store results
					for _, req := range requests {
						result, err := utils.CardCalculation(cRes, req)
						if err != nil {
							fmt.Println("Error processing data:", err)
							continue // Skip this request and move to the next
						}
						cardResults = append(cardResults, result)
					}

					recordResult["response"] = cardResults

				} else {

					if record.GetString("custom_calculation") == "false" {
						recordResult["response"] = cRes
					} else {

						// fmt.Println("cRes-------", cRes)

						customCalculationReq := record.Get("chart_custom_calculation_json")

						var chartCustomCalculationRequest utils.ChartCalculationRequest

						jsonData, err := json.Marshal(customCalculationReq)
						if err != nil {
							log.Println("Error marshaling to JSON:", err)
						}

						err = json.Unmarshal(jsonData, &chartCustomCalculationRequest)
						if err != nil {
							log.Println("Error unmarshalling JSON:", err)
						}
						customResult, err := utils.CalculateGroupedSum(cRes, chartCustomCalculationRequest)
						if err != nil {
							fmt.Println("Error:", err)
						}

						recordResult["response"] = customResult

					}

				}

				// Add the result to the aggregated response
				aggregatedResponse = append(aggregatedResponse, recordResult)

			}
			//--------------------

			// get all filter based on kpi
			var dataSetKpis []struct {
				Kpi string `db:"kpi" json:"kpi"`
			}
			err = app.DB().Select("kpi").From("charts").Where(dbx.HashExp{"dataset": dataset}).GroupBy("kpi").All(&dataSetKpis)
			if err != nil {
				log.Fatalf("Failed to get kpi groupby data: %v", err)
			}

			aggregatedFilterResponse := make(map[string]interface{})
			for _, dataSetKpi := range dataSetKpis {

				kpi := dataSetKpi.Kpi
				filterData, err := fetchfilterJSON(kpi)

				// Translate filter data using the desired dataset and language key
				fRes, err := TranslateFilters(app, dataset, filterData, langKey)
				if err != nil {
					log.Fatalf("Error translating filters: %v", err)
				}

				aggregatedFilterResponse[kpi] = fRes

			}
			//--------------------

			//mainTopic kpi heading and screen color
			headingAndColor := struct {
				DataSet            string `db:"data_set" json:"data_set"`
				MainTopic          string `db:"main_topic" json:"main_topic"`
				SubTopic           string `db:"sub_topic" json:"sub_topic"`
				DataSetTileHeading string `db:"data_set_tile_heading" json:"data_set_tile_heading"`
				HeaderColor        string `db:"header_color" json:"header_color"`
				BodyColor          string `db:"body_color" json:"body_color"`
				BorderColor        string `db:"border_color" json:"border_color"`
			}{}

			header_color := "header_color_" + colorMode
			body_color := "body_color_" + colorMode
			border_color := "border_color_" + colorMode
			data_set_tile_heading := "data_set_tile_heading_" + language
			main_topic := "main_topic_" + language
			sub_topic := "sub_topic_" + language
			query := ` SELECT  id,data_set,` + data_set_tile_heading + ` AS data_set_tile_heading,` + main_topic + ` AS main_topic,` + sub_topic + ` AS sub_topic, ` + header_color + ` AS header_color,` + body_color + ` AS body_color, ` + border_color + ` AS border_color FROM uae_numbers_screen WHERE data_set = {:data_set}  `
			err = app.DB().NewQuery(query).Bind(dbx.Params{"data_set": dataset}).One(&headingAndColor)
			if err != nil {
				log.Printf("Failed to fetch data-set for sub topic %s: %v", dataset, err)
			}
			//---------------------

			// Create the response structure
			response := map[string]interface{}{
				"new_filter_data":          aggregatedFilterResponse,
				"data":                     aggregatedResponse,
				"screen_heading_and_color": headingAndColor,
				"kpi":                      requestData.KPI,
				"filter_data":              requestData.FilterData,
			}

			if len(aggregatedResponse) == 0 {
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "No data could be fetched from the provided URLs"})
			}

			return c.JSON(http.StatusOK, response)
		})

		e.Router.GET("/api/getHomePageData", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			language := c.QueryParam("language")
			colorMode := c.QueryParam("color_mode")

			// Determine the column name dynamically
			colorPatternColumn := "color_pattern_" + colorMode

			// Define the struct for holding the query results
			mainTopics := []struct {
				MainTopicEn        string `db:"main_topic_en" json:"main_topic_en"`
				MainTopicAr        string `db:"main_topic_ar" json:"main_topic_ar"`
				MainTopicListOrder string `db:"main_topic_list_order" json:"main_topic_list_order"`
				ColorPattern       string `db:"color_pattern" json:"color_pattern"`
			}{}

			// Execute the query
			query := fmt.Sprintf(`
									SELECT
										main_topic_en,
										main_topic_ar,
										main_topic_list_order,
										%s AS color_pattern
									FROM home_screen
									GROUP BY main_topic_en, main_topic_ar, main_topic_list_order, %s
									ORDER BY main_topic_list_order ASC
								`, colorPatternColumn, colorPatternColumn)

			err := app.DB().NewQuery(query).All(&mainTopics)

			// err := app.DB().
			// 	Select("main_topic_en", "main_topic_ar", "main_topic_list_order", "color_pattern").
			// 	From("home_screen").
			// 	GroupBy("main_topic_en", "main_topic_ar", "color_pattern").
			// 	OrderBy("main_topic_list_order ASC").
			// 	All(&mainTopics)

			if err != nil {
				log.Printf("Failed to fetch home_screen data: %v", err)
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to fetch records"})
			}

			// If no records are found
			if len(mainTopics) == 0 {
				return c.JSON(http.StatusNotFound, map[string]string{"error": "No records found for home screen"})
			}

			// Final result structure
			result := []map[string]interface{}{}

			// Loop through main topics and fetch sub-topic data for each
			for _, mainTopic := range mainTopics {

				dataSets := []struct {
					DataSet            string `db:"data_set" json:"data_set"`
					DataSetTileHeading string `db:"data_set_tile_heading" json:"data_set_tile_heading"`
					ValueSource        string `db:"value_source" json:"value_source"`
					Value              string `db:"value" json:"value"`
					DataSetListOrder   string `db:"data_set_list_order" json:"data_set_list_order"`
				}{}

				languageSpecificColumn := "data_set_tile_heading_" + language
				valueSourceColumn := "value_source_" + language
				query := ` SELECT  data_set,` + languageSpecificColumn + ` AS data_set_tile_heading,` + valueSourceColumn + ` AS value_source,value, data_set_list_order FROM home_screen WHERE main_topic_en = {:topic}`
				err := app.DB().
					NewQuery(query).
					Bind(dbx.Params{
						"topic": mainTopic.MainTopicEn,
					}).
					All(&dataSets)

				if err != nil {
					log.Printf("Error fetching data sets: %v", err)
					continue
				}

				var MainTopic string
				if language == "en" {
					MainTopic = mainTopic.MainTopicEn
				} else {
					MainTopic = mainTopic.MainTopicAr
				}

				// Add the main topic and its sub-topics to the result
				result = append(result, map[string]interface{}{
					"main_topic":            MainTopic,
					"main_topic_list_order": mainTopic.MainTopicListOrder,
					"color_pattern":         mainTopic.ColorPattern,
					"tile_data":             dataSets,
				})
			}

			// Send the combined result as JSON
			return c.JSON(http.StatusOK, result)

		})

		e.Router.GET("/api/getUAENumbersData", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			language := c.QueryParam("language")
			colorMode := c.QueryParam("color_mode")

			// Determine the column name dynamically
			colorPatternColumn := "color_pattern_" + colorMode

			// Define the struct for holding the query results
			mainTopics := []struct {
				MainTopicEn        string `db:"main_topic_en" json:"main_topic_en"`
				MainTopicAr        string `db:"main_topic_ar" json:"main_topic_ar"`
				MainTopicListOrder string `db:"main_topic_list_order" json:"main_topic_list_order"`
				ColorPattern       string `db:"color_pattern" json:"color_pattern"`
			}{}

			// Execute the query

			// Execute the query
			query := fmt.Sprintf(`
									SELECT 
										main_topic_en, 
										main_topic_ar, 
										main_topic_list_order, 
										%s AS color_pattern
									FROM uae_numbers_screen
									GROUP BY main_topic_en, main_topic_ar, %s
									ORDER BY main_topic_list_order ASC
								`, colorPatternColumn, colorPatternColumn)

			err := app.DB().NewQuery(query).All(&mainTopics)

			// err := app.DB().
			// 	Select("main_topic_en", "main_topic_ar", "main_topic_list_order", "color_pattern").
			// 	From("uae_numbers_screen").
			// 	GroupBy("main_topic_en", "main_topic_ar", "color_pattern").
			// 	OrderBy("main_topic_list_order ASC").
			// 	All(&mainTopics)

			if err != nil {
				log.Printf("Failed to fetch uae_numbers_screen data: %v", err)
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to fetch records"})
			}

			// If no records are found
			if len(mainTopics) == 0 {
				return c.JSON(http.StatusNotFound, map[string]string{"error": "No records found for home screen"})
			}

			// Final result structure
			result := []map[string]interface{}{}

			// Loop through main topics and fetch sub-topic data for each
			for _, mainTopic := range mainTopics {

				subTopics := []struct {
					SubTopicEn        string `db:"sub_topic_en" json:"sub_topic_en"`
					SubTopicAr        string `db:"sub_topic_ar" json:"sub_topic_ar"`
					SubTopicListOrder string `db:"sub_topic_list_order" json:"sub_topic_list_order"`
				}{}

				// Query to get sub-topics for the current main topic
				err := app.DB().
					Select("sub_topic_en", "sub_topic_ar", "sub_topic_list_order").
					From("uae_numbers_screen").
					Where(dbx.HashExp{"main_topic_en": mainTopic.MainTopicEn}).
					GroupBy("sub_topic_en", "sub_topic_ar").
					OrderBy("sub_topic_list_order ASC").
					All(&subTopics)

				if err != nil {
					log.Printf("Failed to fetch sub-topics for main topic %s: %v", mainTopic.MainTopicEn, err)
					continue
				}

				result2 := []map[string]interface{}{}
				// Loop through sub Topics topics and fetch dataSet data for each
				for _, subTopic := range subTopics {

					dataSets := []struct {
						DataSet            string `db:"data_set" json:"data_set"`
						DataSetTileHeading string `db:"data_set_tile_heading" json:"data_set_tile_heading"`
						ValueSource        string `db:"value_source" json:"value_source"`
						Value              string `db:"value" json:"value"`
						DataSetListOrder   string `db:"data_set_list_order" json:"data_set_list_order"`
					}{}

					languageSpecificColumn := "data_set_tile_heading_" + language
					valueSourceColumn := "value_source_" + language
					query := ` SELECT  data_set,` + languageSpecificColumn + ` AS data_set_tile_heading,` + valueSourceColumn + ` AS value_source,value, data_set_list_order FROM uae_numbers_screen WHERE sub_topic_en = {:topic}`
					err := app.DB().
						NewQuery(query).
						Bind(dbx.Params{
							"topic": subTopic.SubTopicEn,
						}).
						All(&dataSets)

					// languageSpecificColumn := "data_set_tile_heading_" + language
					// // Query to get data-set for the current sub topic
					// err := app.DB().
					// 	Select("data_set", languageSpecificColumn, "value_source", "value", "data_set_list_order").
					// 	From("uae_numbers_screen").
					// 	Where(dbx.HashExp{"sub_topic_en": subTopic.SubTopicEn}).
					// 	OrderBy("data_set_list_order ASC").
					// 	All(&dataSets)

					if err != nil {
						log.Printf("Failed to fetch data-set for sub topic %s: %v", subTopic.SubTopicEn, err)
						continue
					}

					var SubTopic string
					if language == "en" {
						SubTopic = subTopic.SubTopicEn
					} else {
						SubTopic = subTopic.SubTopicAr
					}

					result2 = append(result2, map[string]interface{}{
						"sub_topic":            SubTopic,
						"sub_topic_list_order": subTopic.SubTopicListOrder,
						"tile_data":            dataSets,
					})
				}

				var MainTopic string
				if language == "en" {
					MainTopic = mainTopic.MainTopicEn
				} else {
					MainTopic = mainTopic.MainTopicAr
				}

				// Add the main topic and its sub-topics to the result
				result = append(result, map[string]interface{}{
					"main_topic":            MainTopic,
					"main_topic_list_order": mainTopic.MainTopicListOrder,
					"color_pattern":         mainTopic.ColorPattern,
					"sub_topics":            result2,
				})
			}

			// Send the combined result as JSON
			return c.JSON(http.StatusOK, result)

		})

		e.Router.GET("/api/getUserBookmark", func(c echo.Context) error {

			//apiKey := c.Request().Header.Get("APP_SIGNATURE")
			//			if apiKey != os.Getenv("APP_SIGNATURE") {
			//				return c.JSON(http.StatusForbidden, map[string]string{"error": "Invalid API Key"})
			//			}

			language := c.QueryParam("language")
			user_id := c.QueryParam("user_id")
			colorMode := c.QueryParam("color_mode")

			// Define the struct for holding the query results
			bookmarks := []struct {
				Id      string `db:"id" json:"id"`
				UserId  string `db:"user_id" json:"user_id"`
				Dataset string `db:"dataset" json:"dataset"`
			}{}

			// Execute the query
			err := app.DB().
				Select("id", "user_id", "dataset").
				From("bookmark").
				Where(dbx.HashExp{"user_id": user_id}).
				All(&bookmarks)
			// return c.JSON(http.StatusOK, bookmarks)
			if err != nil {
				log.Printf("Failed to fetch bookmark data: %v", err)
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to fetch records"})
			}

			// If no records are found
			if len(bookmarks) == 0 {
				return c.JSON(http.StatusNotFound, map[string]string{"error": "No records found for Bookmark"})
			}

			// Final result structure
			result := []map[string]interface{}{}

			// Loop through main topics and fetch sub-topic data for each
			for _, bookmark := range bookmarks {
				dataSets := struct {
					DataSet            string `db:"data_set" json:"data_set"`
					MainTopic          string `db:"main_topic" json:"main_topic"`
					SubTopic           string `db:"sub_topic" json:"sub_topic"`
					DataSetTileHeading string `db:"data_set_tile_heading" json:"data_set_tile_heading"`
					ValueSource        string `db:"value_source" json:"value_source"`
					Value              string `db:"value" json:"value"`
					ColorPattern       string `db:"color_pattern" json:"color_pattern"`
				}{}

				data_set_tile_heading := "data_set_tile_heading_" + language
				main_topic := "main_topic_" + language
				sub_topic := "sub_topic_" + language
				valueSourceColumn := "value_source_" + language
				color_pattern := "color_pattern_" + colorMode
				query := ` SELECT  id,data_set,` + data_set_tile_heading + ` AS data_set_tile_heading,` + main_topic + ` AS main_topic,` + sub_topic + ` AS sub_topic,` + color_pattern + ` AS color_pattern,` + valueSourceColumn + ` AS value_source,value FROM uae_numbers_screen WHERE data_set = {:bookmark}  `
				err := app.DB().
					NewQuery(query).
					Bind(dbx.Params{
						"bookmark": bookmark.Dataset,
					}).
					One(&dataSets)

				log.Printf("query %s: %v", bookmark.Dataset, query)
				if err != nil {
					log.Printf("Failed to fetch data-set for sub topic %s: %v", bookmark.Dataset, err)
					continue
				}

				// Add the main topic and its sub-topics to the result
				result = append(result, map[string]interface{}{
					"id":                    bookmark.Id,
					"data_set":              dataSets.DataSet,
					"data_set_tile_heading": dataSets.DataSetTileHeading,
					"value_source":          dataSets.ValueSource,
					"value":                 dataSets.Value,
					"main_topic":            dataSets.MainTopic,
					"sub_topic":             dataSets.SubTopic,
					"color_pattern":         dataSets.ColorPattern,
				})

			}

			// Send the combined result as JSON
			return c.JSON(http.StatusOK, result)

		})

		e.Router.POST("/api/getlocalToken", func(c echo.Context) error {

			type RequestData struct {
				Email string `json:"email"`
				UUID  string `json:"uuid"`
			}

			var requestData RequestData

			// Bind JSON request to struct
			if err := c.Bind(&requestData); err != nil {
				return c.JSON(http.StatusBadRequest, map[string]string{
					"error": "Invalid request format",
				})
			}

			// Validate required fields
			if requestData.Email == "" || requestData.UUID == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{
					"error": "email and uuid are required",
				})
			}

			// Try to find user by UUID (emiratesid)
			user, err := e.App.Dao().FindFirstRecordByData("users", "emiratesid", requestData.UUID)
			if err == nil && user != nil {
				//  Generate auth token for the user
				// accessToken, err := auth.NewRecordToken(e.App, user, nil)
				// if err != nil {
				// 	// log error here
				// 	return c.JSON(http.StatusInternalServerError, map[string]interface{}{
				// 		"status":  "error",
				// 		"message": "Failed to generate token",
				// 	})
				// }
				// token, tokenErr := e.App.NewRecordAuthToken(user)
				// if tokenErr != nil {
				// 	// log message here
				// }
				return c.JSON(http.StatusOK, map[string]interface{}{
					"status":  "success",
					"message": "User found by UUID",
					// "token":   accessToken,
				})
			}

			// If UUID not found, try to find by email
			userByEmail, err2 := e.App.Dao().FindFirstRecordByData("users", "email", requestData.Email)
			if err2 != nil || userByEmail == nil {
				return c.JSON(http.StatusOK, map[string]interface{}{
					"status":  "failed",
					"message": "User not found",
				})
			}

			// Update UUID (emiratesid) using email-found record
			userByEmail.Set("emiratesid", requestData.UUID)
			if err := e.App.Dao().SaveRecord(userByEmail); err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": "Failed to update UUID for existing user",
					"error":   err.Error(),
				})
			}

			//  Generate auth token for the user
			// accessToken2, err := auth.NewRecordToken(e.App, userByEmail, nil)
			// if err != nil {
			// 	// log error here
			// 	return c.JSON(http.StatusInternalServerError, map[string]interface{}{
			// 		"status":  "error",
			// 		"message": "Failed to generate token",
			// 	})
			// }

			// Return success after update
			return c.JSON(http.StatusOK, map[string]interface{}{
				"status":  "success",
				"message": "User found by email and UUID updated successfully",
				// "token":   accessToken2,
			})
		})

		e.Router.POST("/api/checkUserWithUhid", func(c echo.Context) error {

			// ✅ read request body
			type RequestData struct {
				Code string `json:"code"`
			}

			var reqData RequestData

			if err := c.Bind(&reqData); err != nil || reqData.Code == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{
					"error": "code is required",
				})
			}

			// ✅ STEP 1 — Call Token API
			tokenURL := os.Getenv("UAEPASS_TOKEN_API")

			formData := url.Values{}
			formData.Set("grant_type", os.Getenv("UAEPASS_TOKEN_API_PARAM_GRANT_TYPE"))
			formData.Set("redirect_uri", os.Getenv("UAEPASS_TOKEN_API_PARAM_REDIRECTION_URL"))
			formData.Set("code", reqData.Code)

			tokenReq, err := http.NewRequest("POST", tokenURL, strings.NewReader(formData.Encode()))
			if err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "request build failed"})
			}

			tokenReq.SetBasicAuth(
				os.Getenv("UAEPASS_TOKEN_API_AUTHORIZATION_USERNAME"),
				os.Getenv("UAEPASS_TOKEN_API_AUTHORIZATION_PASSWORD"),
			)

			tokenReq.Header.Add("Content-Type", "application/x-www-form-urlencoded")

			client := &http.Client{}
			tokenResp, err := client.Do(tokenReq)
			if err != nil {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "token API failed"})
			}
			defer tokenResp.Body.Close()

			// tokenBody, _ := ioutil.ReadAll(tokenResp.Body)

			fmt.Println("---------- UAEPASS TOKEN API DEBUG ----------")

			fmt.Println("Token API URL:", tokenURL)
			fmt.Println("grant_type:", os.Getenv("UAEPASS_TOKEN_API_PARAM_GRANT_TYPE"))
			fmt.Println("redirect_uri:", os.Getenv("UAEPASS_TOKEN_API_PARAM_REDIRECTION_URL"))
			fmt.Println("code:", reqData.Code)

			fmt.Println("BasicAuth Username:", os.Getenv("UAEPASS_TOKEN_API_AUTHORIZATION_USERNAME"))
			fmt.Println("BasicAuth Password:", os.Getenv("UAEPASS_TOKEN_API_AUTHORIZATION_PASSWORD"))

			fmt.Println("Status Code:", tokenResp.StatusCode)

			tokenBodyBytes, err := ioutil.ReadAll(tokenResp.Body)
			tokenBody := tokenBodyBytes

			if err != nil {
				fmt.Println("Error reading token response body:", err)
			} else {
				fmt.Println("Raw Token API Response:", string(tokenBodyBytes))
			}

			fmt.Println("--------------------------------------------")

			var tokenResult struct {
				AccessToken string `json:"access_token"`
			}

			if err := json.Unmarshal(tokenBody, &tokenResult); err != nil || tokenResult.AccessToken == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{
					"error": "invalid token response",
				})
			}

			// ✅ STEP 2 — Call User Details API using access_token
			userURL := os.Getenv("UAEPASS_USER_DETAILS_API")

			userReq, _ := http.NewRequest("GET", userURL, nil)
			userReq.Header.Add("Authorization", "Bearer "+tokenResult.AccessToken)

			userResp, err := client.Do(userReq)
			if err != nil {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "user API failed"})
			}
			defer userResp.Body.Close()

			userBody, _ := ioutil.ReadAll(userResp.Body)

			var userDetails map[string]interface{}
			if err := json.Unmarshal(userBody, &userDetails); err != nil {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "invalid user details"})
			}

			// ✅ SUCCESS RESPONSE
			// return c.JSON(http.StatusOK, map[string]interface{}{
			// 	"status":  "success",
			// 	"message": "User fetched successfully",
			// 	"data":    userDetails,
			// })

			// ✅ Extract email & uuid safely
			email, _ := userDetails["email"].(string)
			uuid, _ := userDetails["uuid"].(string)

			if email == "" || uuid == "" {
				return c.JSON(http.StatusBadRequest, map[string]interface{}{
					"status":  "error",
					"message": "email or uuid missing from UAEPASS response",
				})
			}

			// ✅ Try to find user by UUID (emiratesid)
			user, err := e.App.Dao().FindFirstRecordByData("users", "emiratesid", uuid)

			if err == nil && user != nil {
				// ✅ Generate local PocketBase token
				token, err := tokens.NewRecordAuthToken(e.App, user)
				if err != nil {
					return c.JSON(http.StatusInternalServerError, map[string]interface{}{
						"status":  "error",
						"message": "Failed to generate token",
						"error":   err.Error(),
					})
				}

				// Extract exp from PB token
				parsed, _, err := new(jwt.Parser).ParseUnverified(token, jwt.MapClaims{})
				if err != nil {
					return c.JSON(http.StatusInternalServerError, map[string]interface{}{
						"status":  "error",
						"message": "Failed to decode token",
						"error":   err.Error(),
					})
				}

				claims := parsed.Claims.(jwt.MapClaims)
				exp := claims["exp"]

				return c.JSON(http.StatusOK, map[string]interface{}{
					"status":    "success",
					"message":   "User found by UUID",
					"token":     token,
					"data":      user,
					"expiresOn": exp,
				})

			}

			// ✅ If UUID not found, try email
			userByEmail, err2 := e.App.Dao().FindFirstRecordByData("users", "email", email)

			if err2 != nil || userByEmail == nil {
				return c.JSON(http.StatusOK, map[string]interface{}{
					"status":  "failed",
					"message": "User not found",
					"data":    userDetails,
				})
			}

			// ✅ Update emiratesid field for that user
			userByEmail.Set("emiratesid", uuid)

			if err := e.App.Dao().SaveRecord(userByEmail); err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": "Failed to update UUID",
					"error":   err.Error(),
				})
			}

			// ✅ Generate token after update
			token2, err := tokens.NewRecordAuthToken(e.App, userByEmail)
			if err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": "Failed to generate token",
					"error":   err.Error(),
				})
			}

			// Extract exp from PB token
			parsed, _, err := new(jwt.Parser).ParseUnverified(token2, jwt.MapClaims{})
			if err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": "Failed to decode token",
					"error":   err.Error(),
				})
			}

			claims := parsed.Claims.(jwt.MapClaims)
			exp := claims["exp"]

			return c.JSON(http.StatusOK, map[string]interface{}{
				"status":    "success",
				"message":   "User found by email and UUID updated",
				"token":     token2,
				"data":      userByEmail,
				"expiresOn": exp,
			})

		})

		e.Router.POST("/api/MergeOpenIDConnectUser", func(c echo.Context) error {
			type RequestData struct {
				ID         string `json:"id"`
				EmiratesID string `json:"uuid"`
				Email      string `json:"email"`
			}

			var requestData RequestData
			if err := c.Bind(&requestData); err != nil {
				return c.JSON(http.StatusBadRequest, map[string]interface{}{
					"status":  "error",
					"message": "Invalid request body",
				})
			}

			if requestData.ID == "" || requestData.EmiratesID == "" || requestData.Email == "" {
				return c.JSON(http.StatusBadRequest, map[string]interface{}{
					"status":  "error",
					"message": "id, emiratesid and email are required",
				})
			}

			targetUser, err := e.App.Dao().FindRecordById("users", requestData.ID)
			if err != nil || targetUser == nil {
				return c.JSON(http.StatusNotFound, map[string]interface{}{
					"status":  "failed",
					"message": "Target user not found",
				})
			}

			userByEmail, err := e.App.Dao().FindFirstRecordByData("users", "email", requestData.Email)
			hasDuplicateEmail := err == nil && userByEmail != nil && userByEmail.Id != targetUser.Id

			if !hasDuplicateEmail {
				return c.JSON(http.StatusOK, map[string]interface{}{
					"status":  "failed",
					"message": "No duplicate email user found for merge",
				})
			}

			if err := e.App.Dao().DeleteRecord(userByEmail); err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": "Failed to delete duplicate email user",
					"error":   err.Error(),
				})
			}

			// Copy all old user values into target user except identity fields.
			for key, value := range userByEmail.SchemaData() {
				if key == "email" || key == "emiratesid" {
					continue
				}
				targetUser.Set(key, value)
			}
			targetUser.Set("email", requestData.Email)
			targetUser.Set("emiratesid", requestData.EmiratesID)

			if err := e.App.Dao().SaveRecord(targetUser); err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": "Duplicate user deleted but failed to update target user",
					"error":   err.Error(),
				})
			}

			return c.JSON(http.StatusOK, map[string]interface{}{
				"status":           "success",
				"message":          "User merge completed",
				"deletedDuplicate": true,
				"data":             targetUser,
			})
		})

		e.Router.POST("/api/MergeOpenIDConnectUserAfterAuthendicate", func(c echo.Context) error {
			type RequestData struct {
				ID         string `json:"id"`
				EmiratesID string `json:"uuid"`
				Email      string `json:"email"`
				Username   string `json:"username"`
				Password   string `json:"password"`
				Language   string `json:"language"`
			}

			msg := func(lang, en, ar string) string {
				if strings.ToLower(lang) == "ar" {
					return ar
				}
				return en
			}

			var requestData RequestData
			if err := c.Bind(&requestData); err != nil {
				return c.JSON(http.StatusBadRequest, map[string]interface{}{
					"status":  "error",
					"message": msg("en", "Invalid request body", "بيانات الطلب غير صالحة"),
				})
			}

			if requestData.ID == "" || requestData.EmiratesID == "" || requestData.Email == "" || requestData.Username == "" || requestData.Password == "" {
				return c.JSON(http.StatusBadRequest, map[string]interface{}{
					"status":  "error",
					"message": msg(requestData.Language, "id, uuid, email, username and password are required", "الحقول id و uuid و email و username و password مطلوبة"),
				})
			}

			// Username is email. Validate account state before password auth.
			authUser, err := e.App.Dao().FindFirstRecordByData("users", "email", requestData.Username)
			if err != nil || authUser == nil {
				return c.JSON(http.StatusUnauthorized, map[string]interface{}{
					"status":  "error",
					"message": msg(requestData.Language, "Your email or password is invalid. Please try again.", "بريدك الإلكتروني أو كلمة المرور غير صالحة. حاول مرة اخرى."),
				})
			}

			if status := strings.ToLower(authUser.GetString("status")); status != "approved" {
				return c.JSON(http.StatusForbidden, map[string]interface{}{
					"status":  "error",
					"message": msg(requestData.Language, "Your account has not been approved by the admin.", "لم تتم الموافقة على حسابك من قبل المسؤول."),
				})
			}

			if !authUser.GetBool("verified") {
				return c.JSON(http.StatusForbidden, map[string]interface{}{
					"status":  "error",
					"message": msg(requestData.Language, "Your email address is not verified. Please check your email.", "عنوان بريدك الإلكتروني غير مُحقق. يرجى التحقق من بريدك الإلكتروني."),
				})
			}

			authPayload, _ := json.Marshal(map[string]string{
				"identity": requestData.Username,
				"password": requestData.Password,
			})

			authURL := fmt.Sprintf("%s/api/collections/users/auth-with-password", strings.TrimRight(baseUrl, "/"))
			authReq, err := http.NewRequest("POST", authURL, bytes.NewBuffer(authPayload))
			if err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": msg(requestData.Language, "Failed to process authentication", "فشل في معالجة المصادقة"),
				})
			}
			authReq.Header.Set("Content-Type", "application/json")

			client := &http.Client{}
			authResp, err := client.Do(authReq)
			if err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": msg(requestData.Language, "Authentication failed. Please try again.", "فشلت المصادقة. يرجى المحاولة مرة أخرى."),
				})
			}
			defer authResp.Body.Close()

			if authResp.StatusCode != http.StatusOK {
				return c.JSON(http.StatusUnauthorized, map[string]interface{}{
					"status":  "error",
					"message": msg(requestData.Language, "Your email or password is invalid. Please try again.", "بريدك الإلكتروني أو كلمة المرور غير صالحة. حاول مرة اخرى."),
				})
			}

			// After successful authentication, run the same merge flow.
			targetUser, err := e.App.Dao().FindRecordById("users", requestData.ID)
			if err != nil || targetUser == nil {
				return c.JSON(http.StatusNotFound, map[string]interface{}{
					"status":  "failed",
					"message": msg(requestData.Language, "Target user not found", "المستخدم الهدف غير موجود"),
				})
			}

			userByEmail, err := e.App.Dao().FindFirstRecordByData("users", "email", requestData.Username)
			hasDuplicateEmail := err == nil && userByEmail != nil && userByEmail.Id != targetUser.Id
			if !hasDuplicateEmail {
				return c.JSON(http.StatusOK, map[string]interface{}{
					"status":  "failed",
					"message": msg(requestData.Language, "No duplicate email user found for merge", "لا يوجد مستخدم مكرر بنفس البريد الإلكتروني للدمج"),
				})
			}

			if err := e.App.Dao().DeleteRecord(userByEmail); err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": msg(requestData.Language, "Failed to delete duplicate email user", "فشل في حذف المستخدم المكرر بالبريد الإلكتروني"),
					"error":   err.Error(),
				})
			}

			for key, value := range userByEmail.SchemaData() {
				if key == "email" || key == "emiratesid" {
					continue
				}
				targetUser.Set(key, value)
			}
			targetUser.Set("email", requestData.Email)
			targetUser.Set("emiratesid", requestData.EmiratesID)

			if err := e.App.Dao().SaveRecord(targetUser); err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]interface{}{
					"status":  "error",
					"message": msg(requestData.Language, "Duplicate user deleted but failed to update target user", "تم حذف المستخدم المكرر ولكن فشل تحديث المستخدم الهدف"),
					"error":   err.Error(),
				})
			}

			return c.JSON(http.StatusOK, map[string]interface{}{
				"status":           "success",
				"message":          msg(requestData.Language, "User authenticated and merge completed", "تمت المصادقة على المستخدم واكتمل الدمج"),
				"deletedDuplicate": true,
				"data":             targetUser,
			})
		})

		// ============================================
		// GET ALL COUNTRIES
		// ============================================
		e.Router.GET("/api/getCountries", func(c echo.Context) error {

			language := c.QueryParam("language")

			countries := []struct {
				ID          string `db:"id" json:"id"`
				CountryName string `db:"country_name" json:"country_name"`
				CountryCode string `db:"country_code" json:"country_code"`
				FlagUrl     string `db:"flag_url" json:"flag_url"`
			}{}

			countryNameColumn := "country_name_" + language
			query := ` SELECT  id,country_code,flag_url,` + countryNameColumn + ` AS country_name FROM countries`
			err := app.DB().
				NewQuery(query).
				All(&countries)

			if err != nil {
				log.Printf("Failed to fetch countries: %v", err)
				return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to fetch countries"})
			}

			if len(countries) == 0 {
				return c.JSON(http.StatusNotFound, map[string]string{"error": "No countries found"})
			}

			// Build response with full logo URLs
			result := []map[string]interface{}{}
			for _, country := range countries {

				result = append(result, map[string]interface{}{
					"id":           country.ID,
					"country_name": country.CountryName,
					"country_code": country.CountryCode,
					"flag_url":     country.FlagUrl,
				})
			}

			return c.JSON(http.StatusOK, result)

		})

		// // ============================================
		// // GET COUNTRY DETAILS (Country + Leaders + All Statistics)
		// // ============================================
		// e.Router.GET("/api/getCountryDetails", func(c echo.Context) error {

		// 	countryCode := c.QueryParam("country_code")
		// 	language := c.QueryParam("language")

		// 	if countryCode == "" {
		// 		return c.JSON(http.StatusBadRequest, map[string]string{"error": "country_code is required"})
		// 	}

		// 	// Get country
		// 	country := struct {
		// 		ID          string `db:"id" json:"id"`
		// 		CountryName string `db:"country_name" json:"country_name"`
		// 		CountryCode string `db:"country_code" json:"country_code"`
		// 		FlagUrl     string `db:"flag_url" json:"flag_url"`
		// 	}{}

		// 	countryNameColumn := "country_name_" + language
		// 	query := ` SELECT  id,country_code,flag_url,` + countryNameColumn + ` AS country_name FROM countries  WHERE country_code = {:country_code}`
		// 	err := app.DB().
		// 		NewQuery(query).
		// 		Bind(dbx.Params{
		// 			"country_code": countryCode,
		// 		}).
		// 		One(&country)

		// 	if err != nil {
		// 		log.Printf("Country not found: %v", err)
		// 		return c.JSON(http.StatusNotFound, map[string]string{"error": "Country not found"})
		// 	}

		// 	// Get leaders
		// 	leaders := []struct {
		// 		ID        string `db:"id" json:"id"`
		// 		Name      string `db:"name" json:"name"`
		// 		Position  string `db:"position" json:"position"`
		// 		Photo     string `db:"photo" json:"photo"`
		// 		IsCurrent bool   `db:"is_current" json:"is_current"`
		// 	}{}

		// 	nameColumn := "name_" + language
		// 	positionColumn := "position_" + language

		// 	query2 := `SELECT id,` + nameColumn + ` AS name,` + positionColumn + ` AS position,photo,is_current FROM leaders WHERE country = {:country} AND is_current = true ORDER BY position ASC `

		// 	err = app.DB().
		// 		NewQuery(query2).
		// 		Bind(dbx.Params{
		// 			"country": country.ID,
		// 		}).
		// 		All(&leaders)

		// 	if err != nil {
		// 		log.Printf("Failed to fetch leaders: %v", err)
		// 	}

		// 	// Get all statistics
		// 	allStatistics := []struct {
		// 		ID             string  `db:"id" json:"id"`
		// 		Type           string  `db:"type" json:"type"`
		// 		Topic          string  `db:"topic" json:"topic"`
		// 		Value          string  `db:"value" json:"value"`
		// 		Rank           float64 `db:"rank" json:"rank"`
		// 		IsRankPositive bool    `db:"is_rank_positive" json:"is_rank_positive"`
		// 		RankHeading    string  `db:"rank_heading" json:"rank_heading"`
		// 		Icon           string  `db:"icon" json:"icon"`
		// 		IconURL        string  `db:"-" json:"icon_url"`
		// 	}{}

		// 	topicColumn := "topic_" + language
		// 	valueColumn := "value_" + language
		// 	rankHeadingColumn := "rank_heading_" + language

		// 	query3 := `SELECT id,type,` + topicColumn + ` AS topic,` + valueColumn + ` AS value,` + rankHeadingColumn + ` AS rank_heading,rank,is_rank_positive,icon FROM country_statistics WHERE country = {:country} ORDER BY type ASC, rank ASC`

		// 	err = app.DB().
		// 		NewQuery(query3).
		// 		Bind(dbx.Params{
		// 			"country": country.ID,
		// 		}).
		// 		All(&allStatistics)

		// 	if err != nil {
		// 		log.Printf("Failed to fetch statistics: %v", err)
		// 	}

		// 	for i := range allStatistics {
		// 		if allStatistics[i].Icon == "" {
		// 			allStatistics[i].IconURL = ""
		// 			continue
		// 		}

		// 		allStatistics[i].IconURL = fmt.Sprintf("%s/api/files/country_statistics/%s/%s",
		// 			baseUrl,
		// 			allStatistics[i].ID,
		// 			allStatistics[i].Icon,
		// 		)
		// 	}

		// 	// Group statistics by type
		// 	statisticsMap := map[string]interface{}{
		// 		"demography":      []interface{}{},
		// 		"gdp":             []interface{}{},
		// 		"fdi":             []interface{}{},
		// 		"trade":           []interface{}{},
		// 		"bilateral_trade": []interface{}{},
		// 	}

		// 	for _, stat := range allStatistics {
		// 		// Remove quotes from type field if present
		// 		cleanType := stat.Type
		// 		if len(cleanType) > 2 && cleanType[0] == '"' && cleanType[len(cleanType)-1] == '"' {
		// 			cleanType = cleanType[1 : len(cleanType)-1]
		// 		}

		// 		if _, exists := statisticsMap[cleanType]; exists {
		// 			statisticsMap[cleanType] = append(statisticsMap[cleanType].([]interface{}), stat)
		// 		}
		// 	}

		// 	// Build final response
		// 	result := map[string]interface{}{
		// 		"country":    country,
		// 		"leaders":    leaders,
		// 		"statistics": statisticsMap,
		// 	}

		// 	return c.JSON(http.StatusOK, result)
		// })

		// ============================================
		// GET COUNTRY DETAILS (Country + Leaders + All Statistics)
		// ============================================
		e.Router.GET("/api/getCountryDetails", func(c echo.Context) error {

			countryCode := c.QueryParam("country_code")
			language := c.QueryParam("language")

			if countryCode == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "country_code is required"})
			}

			// --------------------------------------------
			// Get country
			// --------------------------------------------
			country := struct {
				ID          string `db:"id" json:"id"`
				CountryName string `db:"country_name" json:"country_name"`
				CountryCode string `db:"country_code" json:"country_code"`
				FlagUrl     string `db:"flag_url" json:"flag_url"`
			}{}

			countryNameColumn := "country_name_" + language

			query := `
			SELECT id, country_code, flag_url, ` + countryNameColumn + ` AS country_name
			FROM countries
			WHERE country_code = {:country_code}
			`

			err := app.DB().
				NewQuery(query).
				Bind(dbx.Params{
					"country_code": countryCode,
				}).
				One(&country)

			if err != nil {
				log.Printf("Country not found: %v", err)
				return c.JSON(http.StatusNotFound, map[string]string{"error": "Country not found"})
			}

			// --------------------------------------------
			// Get leaders
			// --------------------------------------------
			leaders := []struct {
				ID        string `db:"id" json:"id"`
				Name      string `db:"name" json:"name"`
				Position  string `db:"position" json:"position"`
				Photo     string `db:"photo" json:"photo"`
				IsCurrent bool   `db:"is_current" json:"is_current"`
			}{}

			nameColumn := "name_" + language
			positionColumn := "position_" + language

			query2 := `
			SELECT id,
				` + nameColumn + ` AS name,
				` + positionColumn + ` AS position,
				photo,
				is_current
			FROM leaders
			WHERE country = {:country}
			AND is_current = true
			ORDER BY position ASC
			`

			err = app.DB().
				NewQuery(query2).
				Bind(dbx.Params{
					"country": country.ID,
				}).
				All(&leaders)

			if err != nil {
				log.Printf("Failed to fetch leaders: %v", err)
			}

			// -----------------------------------
			// STATISTICS
			// -----------------------------------

			type Stat struct {
				ID             string         `db:"id" json:"id"`
				Type           string         `db:"type" json:"type"`
				Topic          string         `db:"topic" json:"topic"`
				Value          string         `db:"value" json:"value"`
				Rank           float64        `db:"rank" json:"rank"`
				IsRankPositive bool           `db:"is_rank_positive" json:"is_rank_positive"`
				RankHeading    string         `db:"rank_heading" json:"rank_heading"`
				IconRecordID   sql.NullString `db:"icon_record_id"`
				IconFile       sql.NullString `db:"icon_file"`
				IconURL        string         `json:"icon_url"`
			}

			allStatistics := []Stat{}

			topicColumn := "cs.topic_" + language
			valueColumn := "cs.value_" + language
			rankHeadingColumn := "cs.rank_heading_" + language

			query3 := `
			SELECT 
				cs.id,
				cs.type,
				` + topicColumn + ` AS topic,
				` + valueColumn + ` AS value,
				` + rankHeadingColumn + ` AS rank_heading,
				cs.rank,
				cs.is_rank_positive,
				iconRec.id AS icon_record_id,
				iconRec.icon AS icon_file
			FROM country_statistics cs
			LEFT JOIN country_statistics_icons iconRec
			ON iconRec.key = cs.topic_en
			WHERE cs.country = {:country}
			ORDER BY cs.type ASC, cs.rank ASC
			`

			err = app.DB().
				NewQuery(query3).
				Bind(dbx.Params{
					"country": country.ID,
				}).
				All(&allStatistics)

			if err != nil {
				log.Printf("Failed to fetch statistics: %v", err)
			}

			// -----------------------------------
			// ICON URL BUILD
			// -----------------------------------
			for i := range allStatistics {

				if allStatistics[i].IconFile.Valid {

					allStatistics[i].IconURL = fmt.Sprintf(
						"%s/api/files/country_statistics_icons/%s/%s",
						baseUrl,
						allStatistics[i].IconRecordID.String,
						allStatistics[i].IconFile.String,
					)

				} else {
					allStatistics[i].IconURL = ""
				}
			}

			// -----------------------------------
			// GROUP STATISTICS
			// -----------------------------------
			statisticsMap := map[string]interface{}{
				"demography":      []interface{}{},
				"gdp":             []interface{}{},
				"fdi":             []interface{}{},
				"trade":           []interface{}{},
				"bilateral_trade": []interface{}{},
			}

			for _, stat := range allStatistics {

				cleanType := stat.Type

				if len(cleanType) > 2 && cleanType[0] == '"' && cleanType[len(cleanType)-1] == '"' {
					cleanType = cleanType[1 : len(cleanType)-1]
				}

				if _, exists := statisticsMap[cleanType]; exists {
					statisticsMap[cleanType] = append(statisticsMap[cleanType].([]interface{}), stat)
				}
			}

			// -----------------------------------
			// FINAL RESPONSE
			// -----------------------------------
			result := map[string]interface{}{
				"country":    country,
				"leaders":    leaders,
				"statistics": statisticsMap,
			}

			return c.JSON(http.StatusOK, result)
		})

		// ============================================
		// GET BILATERAL TRADE DATA
		// ============================================
		e.Router.GET("/api/getBilateralTradeData", func(c echo.Context) error {

			countryCode := c.QueryParam("country_code")
			statisticsId := c.QueryParam("statistics_id")
			language := c.QueryParam("language")

			if countryCode == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{"error": "country_code is required"})
			}

			// Get country
			country := struct {
				ID          string `db:"id" json:"id"`
				CountryName string `db:"country_name" json:"country_name"`
				CountryCode string `db:"country_code" json:"country_code"`
				FlagUrl     string `db:"flag_url" json:"flag_url"`
			}{}

			countryNameColumn := "country_name_" + language
			query := ` SELECT  id,country_code,flag_url,` + countryNameColumn + ` AS country_name FROM countries  WHERE country_code = {:country_code}`
			err := app.DB().
				NewQuery(query).
				Bind(dbx.Params{
					"country_code": countryCode,
				}).
				One(&country)

			if err != nil {
				return c.JSON(http.StatusNotFound, map[string]string{"error": "Country not found"})
			}

			// Get all bilateral trade statistics
			allBilateralTradeData := []struct {
				ID      string `db:"id" json:"id"`
				Topic   string `db:"topic" json:"topic"`
				Value   string `db:"value" json:"value"`
				Icon    string `db:"icon" json:"icon"`
				IconURL string `db:"-" json:"icon_url"`
			}{}

			topicColumn := "topic_" + language

			query2 := `SELECT ` + topicColumn + ` AS topic,id,value,icon FROM bilateral_trade_data WHERE country = {:country}  AND statistics = {:statistics}`

			err = app.DB().
				NewQuery(query2).
				Bind(dbx.Params{
					"country":    country.ID,
					"statistics": statisticsId,
				}).
				All(&allBilateralTradeData)

			if err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]string{
					"error": err.Error(),
				})
			}

			if len(allBilateralTradeData) == 0 {
				return c.JSON(http.StatusNotFound, map[string]string{
					"error": "Data not found",
				})
			}

			groupedData := map[string][]map[string]interface{}{
				"Import":   {},
				"Export":   {},
				"Reexport": {},
			}

			for _, item := range allBilateralTradeData {

				// Generate icon URL first
				iconURL := ""
				if item.Icon != "" {
					iconURL = fmt.Sprintf("%s/api/files/bilateral_trade_data/%s/%s",
						baseUrl,
						item.ID,
						item.Icon,
					)
				}

				topic := item.Topic
				category := ""

				// English
				if strings.HasPrefix(topic, "Imports - ") {
					category = "Import"
					topic = strings.TrimPrefix(topic, "Imports - ")
				} else if strings.HasPrefix(topic, "Exports - ") {
					category = "Export"
					topic = strings.TrimPrefix(topic, "Exports - ")
				} else if strings.HasPrefix(topic, "Re-exports - ") {
					category = "Reexport"
					topic = strings.TrimPrefix(topic, "Re-exports - ")
				}

				// Arabic
				if strings.HasPrefix(topic, "الواردات - ") {
					category = "Import"
					topic = strings.TrimPrefix(topic, "الواردات - ")
				} else if strings.HasPrefix(topic, "الصادرات - ") {
					category = "Export"
					topic = strings.TrimPrefix(topic, "الصادرات - ")
				} else if strings.HasPrefix(topic, "إعادة التصدير - ") {
					category = "Reexport"
					topic = strings.TrimPrefix(topic, "إعادة التصدير - ")
				}

				data := map[string]interface{}{
					"id":       item.ID,
					"topic":    topic,
					"value":    item.Value,
					"icon":     item.Icon,
					"icon_url": iconURL,
				}

				if category != "" {
					groupedData[category] = append(groupedData[category], data)
				}
			}

			return c.JSON(http.StatusOK, groupedData)

		})

		// ============================================
		// GET ALL COMPETITIVENESS REPORTS
		// ============================================
		e.Router.GET("/api/getCompetitivenessReports", func(c echo.Context) error {

			type CountryMini struct {
				ID          string `json:"id"`
				CountryName string `json:"country_name"`
				Flag        string `json:"flag"`
			}

			type CompetitivenessReport struct {
				ID               string `json:"id"`
				Year             int    `json:"year"`
				CurrentYearRank  int    `json:"current_year_rank"`
				PreviousYearRank int    `json:"previous_year_rank"`
				IsRankPositive   bool   `json:"is_rank_positive"`

				Heading string `json:"heading"`
				Content string `json:"content"`
				Logo    string `json:"logo"`

				FirstGlobally []CountryMini `json:"first_in_globally"`
				FirstGCC      []CountryMini `json:"first_in_gcc"`
				FirstArab     []CountryMini `json:"first_in_arab_countries"`
			}

			language := c.QueryParam("language")
			if language == "" {
				language = "en"
			}

			headingColumn := "heading_" + language
			contentColumn := "content_" + language

			// Step 1: fetch competitiveness reports
			rows := []struct {
				ID               string `db:"id"`
				Year             int    `db:"year"`
				CurrentYearRank  int    `db:"current_year_rank"`
				PreviousYearRank int    `db:"previous_year_rank"`
				IsRankPositive   bool   `db:"is_rank_positive"`
				Logo             string `db:"logo"`

				Heading string `db:"heading"`
				Content string `db:"content"`

				FirstGlobally string `db:"first_in_globally"`
				FirstGCC      string `db:"first_in_gcc"`
				FirstArab     string `db:"first_in_arab_countries"`
			}{}

			query := `
				SELECT
					id,
					year,
					current_year_rank,
					previous_year_rank,
					is_rank_positive,
					logo,
					` + headingColumn + ` AS heading,
					` + contentColumn + ` AS content,
					first_in_globally,
					first_in_gcc,
					first_in_arab_countries
				FROM competitive_reports
			`

			err := app.DB().NewQuery(query).All(&rows)
			if err != nil {
				return c.JSON(http.StatusInternalServerError, echo.Map{
					"error": err.Error(),
				})
			}

			if len(rows) == 0 {
				return c.JSON(http.StatusNotFound, echo.Map{
					"error": "No competitiveness reports found",
				})
			}

			// return c.JSON(http.StatusOK, rows)

			// Step 2: loop & attach relation data
			result := []CompetitivenessReport{}

			for _, r := range rows {

				report := CompetitivenessReport{
					ID:               r.ID,
					Year:             r.Year,
					CurrentYearRank:  r.CurrentYearRank,
					PreviousYearRank: r.PreviousYearRank,
					IsRankPositive:   r.IsRankPositive,
					Heading:          r.Heading,
					Content:          r.Content,
					Logo:             baseUrl + "/api/files/competitive_reports/" + r.ID + "/" + r.Logo,
				}

				loadCountries := func(ids []string) []CountryMini {
					if len(ids) == 0 {
						return []CountryMini{}
					}

					inClause := buildSafeInClause(ids)

					query := `
						SELECT
							id,
							country_name_` + language + ` AS country_name,
							flag_url AS flag
						FROM countries
						WHERE id IN ` + inClause

					countries := []CountryMini{}

					err := app.DB().
						NewQuery(query).
						All(&countries)

					if err != nil {
						log.Println("Country fetch error:", err)
					}

					return countries
				}

				report.FirstGlobally = loadCountries(parseRelationIDs(r.FirstGlobally))
				report.FirstGCC = loadCountries(parseRelationIDs(r.FirstGCC))
				report.FirstArab = loadCountries(parseRelationIDs(r.FirstArab))

				result = append(result, report)
			}

			return c.JSON(http.StatusOK, result)
		})

		// ============================================
		// SEED SAMPLE DATA (Run once to populate database)
		// ============================================
		e.Router.POST("/api/seedSampleData", func(c echo.Context) error {

			// Create Argentina
			countryID := ""
			err := app.Dao().DB().
				Select("id").
				From("countries").
				Where(dbx.HashExp{"country_code": "AR"}).
				One(&struct{ ID string }{ID: countryID})

			// If Argentina doesn't exist, create it
			if err != nil {
				_, err = app.Dao().DB().
					Insert("countries", dbx.Params{
						"country_name": "Argentina",
						"country_code": "AR",
						"flag_url":     "https://flagcdn.com/ar.svg",
					}).Execute()

				if err != nil {
					log.Printf("Failed to create Argentina: %v", err)
					return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to create country"})
				}

				// Get the newly created country ID
				err = app.Dao().DB().
					Select("id").
					From("countries").
					Where(dbx.HashExp{"country_code": "AR"}).
					One(&struct{ ID string }{ID: countryID})
			}

			// Insert Leaders (simplified - you can add more)
			leaders := []map[string]interface{}{
				{"country": countryID, "name": "Javier Milei", "position": "President", "is_current": true},
				{"country": countryID, "name": "Victoria Villarruel", "position": "Vice President", "is_current": true},
			}

			for _, leader := range leaders {
				_, err = app.Dao().DB().Insert("leaders", leader).Execute()
				if err != nil {
					log.Printf("Failed to insert leader: %v", err)
				}
			}

			// Insert Statistics
			statistics := []map[string]interface{}{
				// Demography
				{"country": countryID, "type": "\"demography\"", "topic": "Capital City", "value": "Buenos Aires"},
				{"country": countryID, "type": "\"demography\"", "topic": "Area", "value": "2,780,400 sq. km"},
				{"country": countryID, "type": "\"demography\"", "topic": "2021 Population", "value": "45.81 M", "rank": 0.9, "is_rank_positive": true},

				// GDP
				{"country": countryID, "type": "\"gdp\"", "topic": "2021 Nominal (AED)", "value": "1.8 T", "rank": 2.5, "is_rank_positive": true},
				{"country": countryID, "type": "\"gdp\"", "topic": "2021 Real (AED)", "value": "2.08 T", "rank": 3.2, "is_rank_positive": true},

				// FDI
				{"country": countryID, "type": "\"fdi\"", "topic": "2020 FDI Inflow (AED)", "value": "4.75 B", "rank": -10.41, "is_rank_positive": false},

				// Trade
				{"country": countryID, "type": "\"trade\"", "topic": "2021 Total Trade (AED)", "value": "603 B", "rank": 35.36, "is_rank_positive": true},

				// Bilateral Trade
				{"country": countryID, "type": "\"bilateral_trade\"", "topic": "Total (2022)", "value": "$4.7M"},
				{"country": countryID, "type": "\"bilateral_trade\"", "topic": "Top 5 Import Commodities", "value": "Jewellery, Diamonds, Gold", "rank": 1},
			}

			for _, stat := range statistics {
				_, err = app.Dao().DB().Insert("country_statistics", stat).Execute()
				if err != nil {
					log.Printf("Failed to insert statistic: %v", err)
				}
			}

			return c.JSON(http.StatusOK, map[string]string{"message": "Sample data seeded successfully"})
		})

		type DownloadRequest struct {
			Username     string `json:"username"`
			Password     string `json:"password"`
			Collection   string `json:"collection"`
			SyncWithLive bool   `json:"syncWithLive"`
			LiveUsername string `json:"liveUsername"`
			LivePassword string `json:"livePassword"`
		}

		type AdminLoginResponse struct {
			Token string                 `json:"token"`
			Admin map[string]interface{} `json:"admin"`
		}

		type DownloadResponse struct {
			Collection string                   `json:"collection"`
			Records    []map[string]interface{} `json:"records"`
		}

		e.Router.POST("/api/syncFromVIT", func(c echo.Context) error {
			reqBody := new(DownloadRequest)
			if err := c.Bind(reqBody); err != nil {
				return c.JSON(400, map[string]string{"error": "invalid request"})
			}

			if reqBody.Username == "" || reqBody.Password == "" || reqBody.Collection == "" {
				return c.JSON(400, map[string]string{"error": "username, password, and collection are required"})
			}

			// 1️⃣ Login to PocketBase
			loginPayload := map[string]string{
				"identity": reqBody.Username,
				"password": reqBody.Password,
			}
			data, _ := json.Marshal(loginPayload)

			loginReq, _ := http.NewRequest("POST", fmt.Sprintf("%s/api/admins/auth-with-password", "http://127.0.0.1:8090"), bytes.NewBuffer(data))
			loginReq.Header.Set("Content-Type", "application/json")

			loginResp, err := http.DefaultClient.Do(loginReq)
			if err != nil {
				return c.JSON(500, map[string]string{"error": "failed to connect to PB"})
			}
			defer loginResp.Body.Close()

			loginBody, _ := ioutil.ReadAll(loginResp.Body)

			if loginResp.StatusCode != 200 {
				return c.JSON(401, map[string]string{"error": "invalid admin credentials", "details": string(loginBody)})
			}

			var loginRes AdminLoginResponse
			if err := json.Unmarshal(loginBody, &loginRes); err != nil {
				return c.JSON(500, map[string]string{"error": "failed to parse login response"})
			}

			// 2️⃣ Fetch all records with pagination.
			// PocketBase (v0.22.x) caps perPage at 500 (tools/search.MaxPerPage); larger values are ignored,
			// so a single ?perPage=5000 request only returns the first 500 rows.
			const recordsPerPage = 500
			records := []map[string]interface{}{}
			for page := 1; ; page++ {
				url := fmt.Sprintf("%s/api/collections/%s/records?perPage=%d&page=%d", "http://127.0.0.1:8090", reqBody.Collection, recordsPerPage, page)
				fmt.Println("Fetching collection from URL:", url)

				req, _ := http.NewRequest("GET", url, nil)
				req.Header.Set("Authorization", loginRes.Token)

				resp, err := http.DefaultClient.Do(req)
				if err != nil {
					fmt.Println("Error fetching collection:", err)
					return c.JSON(500, map[string]string{"error": "failed to fetch collection"})
				}

				body, _ := ioutil.ReadAll(resp.Body)
				resp.Body.Close()
				fmt.Println("HTTP Status Code:", resp.StatusCode, "page", page)

				if resp.StatusCode != 200 {
					return c.JSON(500, map[string]string{"error": fmt.Sprintf("fetch collection failed: %s", string(body))})
				}

				var parsed map[string]interface{}
				if err := json.Unmarshal(body, &parsed); err != nil {
					fmt.Println("JSON Unmarshal error:", err)
					return c.JSON(500, map[string]string{"error": "invalid JSON from PocketBase"})
				}

				items, ok := parsed["items"].([]interface{})
				if !ok {
					return c.JSON(500, map[string]string{"error": "no items found"})
				}
				for _, i := range items {
					rec := i.(map[string]interface{})
					records = append(records, rec)
				}

				var totalPages int
				if tp, ok := parsed["totalPages"].(float64); ok {
					totalPages = int(tp)
				}
				if totalPages > 0 {
					if page >= totalPages {
						break
					}
					continue
				}
				// skipTotal or missing totals: stop after a short / empty page
				if len(items) < recordsPerPage {
					break
				}
			}

			syncResult := map[string]interface{}{"synced": []string{}, "errors": []string{}}

			// 3️⃣ If syncWithLive=true, sync to live PB
			if reqBody.SyncWithLive {
				if reqBody.LiveUsername == "" || reqBody.LivePassword == "" {
					return c.JSON(400, map[string]string{"error": "live username and password required for sync"})
				}

				// Login to live
				livePayload := map[string]string{
					"identity": reqBody.LiveUsername,
					"password": reqBody.LivePassword,
				}
				data, _ := json.Marshal(livePayload)
				liveReq, _ := http.NewRequest("POST", fmt.Sprintf("%s/api/admins/auth-with-password", "https://pb.venbait.in"), bytes.NewBuffer(data))
				liveReq.Header.Set("Content-Type", "application/json")
				liveResp, err := http.DefaultClient.Do(liveReq)
				fmt.Println("Live url:", "https://pb.venbait.in")
				fmt.Println("Live req:", liveReq)
				if err != nil {
					return c.JSON(500, map[string]string{"error": "failed to connect to Live PB"})
				}
				defer liveResp.Body.Close()

				liveBody, _ := ioutil.ReadAll(liveResp.Body)
				if liveResp.StatusCode != 200 {
					return c.JSON(401, map[string]string{"error": "invalid live admin credentials", "details": string(liveBody)})
				}

				var liveLogin AdminLoginResponse
				if err := json.Unmarshal(liveBody, &liveLogin); err != nil {
					return c.JSON(500, map[string]string{"error": "failed to parse live login response"})
				}

				// Sync each record
				for _, rec := range records {
					id := rec["id"].(string)
					data, _ := json.Marshal(rec)

					// Try PATCH first
					patchURL := fmt.Sprintf("%s/api/collections/%s/records/%s", "https://pb.venbait.in", reqBody.Collection, id)
					req, _ := http.NewRequest("PATCH", patchURL, bytes.NewBuffer(data))
					req.Header.Set("Authorization", liveLogin.Token)
					req.Header.Set("Content-Type", "application/json")

					resp, err := http.DefaultClient.Do(req)
					if err != nil {
						syncResult["errors"] = append(syncResult["errors"].([]string), fmt.Sprintf("%s: %s", id, err.Error()))
						continue
					}
					defer resp.Body.Close()

					if resp.StatusCode == 404 {
						// Create instead
						postURL := fmt.Sprintf("%s/api/collections/%s/records", "https://pb.venbait.in", reqBody.Collection)
						req, _ := http.NewRequest("POST", postURL, bytes.NewBuffer(data))
						req.Header.Set("Authorization", liveLogin.Token)
						req.Header.Set("Content-Type", "application/json")

						resp, err = http.DefaultClient.Do(req)
						if err != nil {
							syncResult["errors"] = append(syncResult["errors"].([]string), fmt.Sprintf("%s: %s", id, err.Error()))
							continue
						}
						defer resp.Body.Close()

						if resp.StatusCode >= 300 {
							body, _ := ioutil.ReadAll(resp.Body)
							syncResult["errors"] = append(syncResult["errors"].([]string), fmt.Sprintf("%s: create failed: %s", id, string(body)))
							continue
						}
					}

					syncResult["synced"] = append(syncResult["synced"].([]string), id)
				}
			}

			// 4️⃣ Return dev records + optional sync result
			return c.JSON(200, map[string]interface{}{
				"collection": reqBody.Collection,
				"records":    records,
				"sync":       syncResult,
			})
		})

		// testing purpose only
		e.Router.GET("/api/UAE_URL_TEST", func(c echo.Context) error {
			apiURL := c.QueryParam("url")
			if apiURL == "" {
				return c.JSON(http.StatusBadRequest, map[string]string{
					"error": "Missing required query parameter 'url'",
				})
			}

			// Call the external API
			resp, err := http.Get(apiURL)
			if err != nil {
				return c.JSON(http.StatusBadGateway, map[string]string{
					"error": fmt.Sprintf("Failed to reach URL: %v", err),
				})
			}
			defer resp.Body.Close()

			// Read the response
			body, err := ioutil.ReadAll(resp.Body)
			if err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]string{
					"error": fmt.Sprintf("Failed to read response: %v", err),
				})
			}

			contentType := resp.Header.Get("Content-Type")

			// 🧠 Detect Cloudflare or HTML error responses
			if strings.Contains(string(body), "cloudflare") ||
				strings.Contains(string(body), "<html") ||
				strings.Contains(strings.ToLower(contentType), "text/html") {
				return c.JSON(http.StatusBadGateway, map[string]interface{}{
					"error":        "Blocked or invalid response",
					"status_code":  resp.StatusCode,
					"content_type": contentType,
					"url":          apiURL,
					"message":      "Received HTML instead of XML. Likely blocked by Cloudflare or invalid endpoint.",
					"body_snippet": string(body[:int(math.Min(float64(len(body)), 300))]), // short preview
				})
			}

			// Clean XML namespaces
			cleanXML := []byte(removeNamespace(body))

			// Parse the XML
			var data GenericData
			if err := xml.Unmarshal(cleanXML, &data); err != nil {
				return c.JSON(http.StatusInternalServerError, map[string]string{
					"error": fmt.Sprintf("Failed to parse XML: %v", err),
				})
			}

			// Transform data to JSON-friendly structure
			response := []map[string]interface{}{}
			for _, obs := range data.DataSet {
				response = append(response, map[string]interface{}{
					"ObsKey":   mapObsKey(obs.ObsKey),
					"ObsValue": obs.ObsValue,
				})
			}

			// Return final JSON
			return c.JSONPretty(http.StatusOK, response, "  ")
		})

		return nil

	})

	//send mail while status changed from app admin
	app.OnRecordBeforeUpdateRequest().Add(func(e *core.RecordUpdateEvent) error {
		if e.Collection.Name == "users" {
			originalRecord, err := app.Dao().FindRecordById("users", e.Record.GetString("id"))
			if err != nil {
				return fmt.Errorf("failed to fetch original record: %v", err)
			}

			oldStatus := originalRecord.GetString("status")
			newStatus := e.Record.GetString("status")

			// Check if the status has changed
			if oldStatus != newStatus {
				err := sendStatusChangeEmail(app, e.Record, newStatus)
				if err != nil {
					return err
				}
			}
		}
		return nil
	})

	//Hook to send a verification link to register user
	app.OnRecordAfterCreateRequest().Add(func(e *core.RecordCreateEvent) error {
		if e.Record.Collection().Name == "users" {
			// Retrieve user details
			name := e.Record.GetString("uname")
			email := e.Record.GetString("email")
			userID := e.Record.Id // User ID

			// imageRecord, err := app.Dao().FindFirstRecordByData("images", "file_name", "app_logo")
			// if err != nil {
			// 	log.Printf("Failed to fetch logo from images collection: %v", err)
			// 	return nil
			// }

			// Construct the logo URL
			// logoURL := fmt.Sprintf("%s/api/files/images/%s/%s",
			// 	baseUrl,
			// 	imageRecord.Id,
			// 	imageRecord.GetString("file"),
			// )

			// Email subject
			subject := "FCSC Verify Your Email to Complete Registration"

			// Generate a verification URL (example: using user ID or token)
			verificationURL := fmt.Sprintf("%s/verify-email?userId=%s", baseUrl, userID)

			// Email body
			body := fmt.Sprintf(`
			<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Registration Update – UAE Stats App</title>
</head>
<body style="font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f8f8f8;">
    <table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0" align="center">
        <tr>
            <td align="center" style="padding: 20px;">
                <table role="presentation" width="600px" cellspacing="0" cellpadding="0" border="0" style="background-color: #ffffff; border: 2px solid #d1ad5c; padding: 20px;">
                    <tr>
                        <td align="center" style="font-size: 18px; font-weight: bold;">
						    <span style=" font-size:16px; border-bottom:1px solid #d1ad5c;padding-bottom: 10px;">
                            تطبيق إحصاءات الإمارات العربية المتحدة - يرجى التحقق من عنوان بريدك الإلكتروني <br>
							</span><br><span style="font-size: 18px;">Please Verify Your Email Address – UAE Stats App</span>
                        <br><br><br>
                        </td>
                    </tr>
                    <tr>
                        <td style="padding-top: 20px; padding-bottom:30px; font-size: 12px;">
                            <table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0">
                                <tr>
                                    <td align="left" style="width: 56%%; vertical-align: top;">
                                        <strong>Dear <span style="color: #d1ad5c;">%s</span>,</strong><br><br>
                                        Thank you for registering. Please click the link below to verify your email address:
                                        <br><br>
                                        <a href="%s" style="color: #d1ad5c; text-decoration: none; font-weight: bold;">Verify Email</a>
                                        <br><br>
                                        If you did not register for this account, please ignore this email.
                                    </td>
                                    <td align="right" style="width: 42%%; vertical-align: top; direction: rtl;">
                                        <strong>عزيزي <span style="color: #d1ad5c;">%s</span>،</strong><br><br>
                                        شكرًا لتسجيلك. يُرجى النقر على الرابط أدناه للتحقق من عنوان بريدك الإلكتروني:
                                        <br><br>
                                        <a href="%s" style="color: #d1ad5c; text-decoration: none; font-weight: bold;">تحقق من البريد الإلكتروني</a>
                                        <br><br>
                                        إذا لم تكن قد قمت بتسجيل هذا الحساب، يُرجى تجاهل هذا البريد الإلكتروني.
                                    </td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                    <tr>
                        <td align="center"  padding-top: 20px;">
                            <img src="https://imageats.s3.ap-south-1.amazonaws.com/signature.png" alt="We The UAE 2031" width="100%%">
                        </td>
                    </tr>
                       <tr>
	                        <td style="padding-top: 20px; font-size: 9px; color: #777; border-top: 1px solid #ccc;direction: rtl; text-align: right;">
	                        <strong>تنبيه:</strong><br>هذه الرسالة الإلكترونية و أيٌ من مرفقاتها قد تحتوي على معلومات سرية وهامة موجهه للشخص/الأشخاص المعنيين وعليه يرجى من المتلقي في حال تلقي الرسالة الإلكترونية عن طريق<br> الخطأ ولم يكن المعني بها، إخطار المرسل وحذفها من بريده الإلكتروني و كذلك إتلاف أي نسخ مطبوعة عنها حيث أنه يحظر عليه قراءة ونسخ ونشر أو توزيع أو استخدام هذه الرسالة<br> إلكترونية و أيٌ من مرفقاتها بأي شكل من الأشكال
	                        </td>
                             </tr>
                             <tr>
                             <td style="padding-top: 20px; font-size: 9px; color: #777;text-align: left; direction: ltr;">
	                            <strong>Disclaimer:</strong> <br>The information contained in and transmitted with this e-mail message is PRIVILEGED AND/OR CONFIDENTIAL.If you are not the intended recipient, or have received the message by error, please notify the sender via E-Mail or over the telephone and delete this e-mail. You are not authorized to read, copy, disseminate, distribute or use this E-Mail or any of its attachments in any way.
	                        </td>
	                    </tr>
                </table>
            </td>
        </tr>
    </table>
</body>
</html>
				`, name, verificationURL, name, verificationURL)

			// Create the email message
			message := &mailer.Message{
				From: mail.Address{
					Name:    "FCSC",
					Address: app.Settings().Meta.SenderAddress,
				},
				To: []mail.Address{
					{
						Name:    name,
						Address: email,
					},
				},
				Subject: subject,
				HTML:    body,
			}

			// Send the email
			err := app.NewMailClient().Send(message)
			if err != nil {
				log.Printf("Failed to send verification email to user: %v", err)
				return err
			}

			log.Println("Verification email sent successfully to the user.")
		}
		return nil
	})

	// Hook to send an email after a user is created
	app.OnBeforeServe().Add(func(e *core.ServeEvent) error {

		e.Router.GET("/api/custom/approve", func(c echo.Context) error {
			userId := c.QueryParam("userId")
			if userId == "" {
				return c.JSON(400, map[string]interface{}{
					"code":    400,
					"message": "Missing userId query parameter.",
				})
			}

			// Find the user record
			record, err := app.Dao().FindRecordById("users", userId)
			if err != nil {
				return c.JSON(500, map[string]interface{}{
					"code":    500,
					"message": "Error finding user.",
				})
			}

			// Check if the email is already verified
			if record.GetBool("reviewed") {
				return c.HTML(http.StatusOK, `
					<html>
						<head>
							<style>
								body {
									font-family: Arial, sans-serif;
									background-color: #f0f8ff;
									display: flex;
									align-items: center;
									justify-content: center;
									height: 100vh;
									margin: 0;
								}
								.message-container {
									text-align: center;
									background-color: #ffe0e0;
									padding: 20px;
									border-radius: 10px;
									box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
								}
								.message-container h1 {
									font-size: 24px;
									color: #b71c1c;
									margin-bottom: 10px;
								}
								.message-container p {
									font-size: 16px;
									color: #880e4f;
								}
								.icon {
									font-size: 50px;
									color: #b71c1c;
									margin-bottom: 20px;
								}
							</style>
						</head>
						<body>
							<div class="message-container">
								<div class="icon">&#x2716;</div>
								<h1>Your Already Reviewed</h1>
								<p>You have already been reviewed. No further action is required.</p>
							</div>
						</body>
					</html>
					`)
			}

			// Update the status
			newStatus := "Approved"
			record.Set("verified", true)
			record.Set("status", newStatus)
			record.Set("reviewed", true)
			if err := app.Dao().SaveRecord(record); err != nil {
				return c.JSON(500, map[string]interface{}{
					"code":    500,
					"message": "Failed to verify user.",
				})
			}

			// Send the status change email
			err = sendStatusChangeEmail(app, record, newStatus)
			if err != nil {
				return c.JSON(500, map[string]interface{}{
					"code":    500,
					"message": "Failed to send approval email.",
				})
			}

			return c.HTML(http.StatusOK, `
				<html>
					<head>
						<style>
							body {
								font-family: Arial, sans-serif;
								background-color: #f0f8ff;
								display: flex;
								align-items: center;
								justify-content: center;
								height: 100vh;
								margin: 0;
							}
							.message-container {
								text-align: center;
								background-color: #e0f7fa;
								padding: 20px;
								border-radius: 10px;
								box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
							}
							.message-container h1 {
								font-size: 24px;
								color: #00796b;
								margin-bottom: 10px;
							}
							.message-container p {
								font-size: 16px;
								color: #004d40;
							}
							.icon {
								font-size: 50px;
								color: #00796b;
								margin-bottom: 20px;
							}
						</style>
					</head>
					<body>
						<div class="message-container">
							<i>&#x2714;</i>
							<h1>User Approved Successfully</h1>
							<p>The user has been approved, and the approval email has been sent.</p>
						</div>
					</body>
					</html>
							`)
		})

		return nil
	})

	app.OnBeforeServe().Add(func(e *core.ServeEvent) error {

		// Approve Endpoint
		e.Router.GET("/api/custom/reject", func(c echo.Context) error {
			userId := c.QueryParam("userId")
			if userId == "" {
				return c.JSON(400, map[string]interface{}{
					"code":    400,
					"message": "Missing userId query parameter.",
				})
			}

			// Find the user record in PocketBase by userId
			record, err := app.Dao().FindRecordById("users", userId)
			if err != nil {
				return c.JSON(500, map[string]interface{}{
					"code":    500,
					"message": "Error finding user.",
				})
			}

			// Check if the email is already verified
			if record.GetBool("reviewed") {
				return c.HTML(http.StatusOK, `
				<html>
					<head>
						<style>
							body {
								font-family: Arial, sans-serif;
								background-color: #f0f8ff;
								display: flex;
								align-items: center;
								justify-content: center;
								height: 100vh;
								margin: 0;
							}
							.message-container {
								text-align: center;
								background-color: #ffe0e0;
								padding: 20px;
								border-radius: 10px;
								box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
							}
							.message-container h1 {
								font-size: 24px;
								color: #b71c1c;
								margin-bottom: 10px;
							}
							.message-container p {
								font-size: 16px;
								color: #880e4f;
							}
							.icon {
								font-size: 50px;
								color: #b71c1c;
								margin-bottom: 20px;
							}
						</style>
					</head>
					<body>
						<div class="message-container">
							<div class="icon">&#x2716;</div>
							<h1>Your Already Reviewed</h1>
							<p>You have already been reviewed. No further action is required.</p>
						</div>
					</body>
				</html>
				`)
			}

			// Check if the status is already "Denied" to avoid duplicate email
			if record.GetString("status") == "Denied" {
				return c.JSON(400, map[string]interface{}{
					"code":    400,
					"message": "User is already rejected.",
				})
			}

			// Update the status
			newStatus := "Denied"
			record.Set("verified", false)
			record.Set("status", newStatus)
			record.Set("reviewed", true)
			if err := app.Dao().SaveRecord(record); err != nil {
				return c.JSON(500, map[string]interface{}{
					"code":    500,
					"message": "Failed to verify user.",
				})
			}

			// Send the status change email
			err = sendStatusChangeEmail(app, record, newStatus)
			if err != nil {
				return c.JSON(500, map[string]interface{}{
					"code":    500,
					"message": "Failed to send rejection email.",
				})
			}

			// Render a success message as an HTML response
			return c.HTML(http.StatusOK, `
				<!DOCTYPE html>
				<html lang="en">
				<head>
					<meta charset="UTF-8">
					<meta name="viewport" content="width=device-width, initial-scale=1.0">
					<title>User Rejected</title>
					<style>
						body {
							margin: 0;
							padding: 0;
							display: flex;
							justify-content: center;
							align-items: center;
							height: 100vh;
							background-color: #ffe6e6;
							font-family: Arial, sans-serif;
						}
						.message-box {
							text-align: center;
							color: #d9534f;
						}
						.message-box i {
							font-size: 48px;
							margin-bottom: 20px;
						}
						.message-box p {
							font-size: 18px;
						}
					</style>
				</head>
				<body>
					<div class="message-box">
						<i>❌</i>
						<h1>User Rejected</h1>
						<p>Rejection email sent successfully.</p>
					</div>
				</body>
				</html>
				`)
		})

		return nil
	})

	// Serve static files from the provided public directory (if exists)
	app.OnBeforeServe().Add(func(e *core.ServeEvent) error {
		// e.Router.GET("/verify-email", verifyEmailHandler)

		e.Router.GET("/verify-email", func(c echo.Context) error {
			return verifyEmailHandler(c, baseUrl)
		})
		e.Router.GET("/*", apis.StaticDirectoryHandler(os.DirFS("./pb_public"), false))
		return nil
	})

	//Feedback Mail Trigger
	app.OnRecordAfterCreateRequest().Add(func(e *core.RecordCreateEvent) error {
		if e.Record.Collection().Name == "feedback" {
			// Retrieve feedback details
			feedback := e.Record
			userId := feedback.GetString("userId")
			emojiRating := feedback.GetString("emoji_rating")
			easeOfUse := feedback.GetString("ease_of_use")
			quality := feedback.GetString("quality")
			design := feedback.GetString("design")
			redundancy := feedback.GetString("redundancy")
			additionalFeedback := feedback.GetString("feedback")

			if additionalFeedback == "" {
				additionalFeedback = "No additional feedback provided."
			}

			// Fetch user details using userId
			user, err := app.Dao().FindRecordById("users", userId)
			if err != nil {
				log.Printf("Error fetching user details: %v\n", err)
				return err
			}

			userName := user.GetString("uname")
			userEmail := user.GetString("email")

			// Extract and format the creation time
			createdTime := e.Record.Get("created").(types.DateTime) // Extract created time
			feedbackDateTime := createdTime.Time().UTC()            // Convert to time.Time in UTC

			// Format the date and time
			formattedDate := feedbackDateTime.Format("02-01-2006") // DD-MM-YYYY
			// formattedDateTime := feedbackDateTime.Format("02.01.2006 15:04 UTC") // DD.MM.YYYY HH:mm UTC

			// Admin details
			adminName := "FCSC"

			// Email subject
			subject := "UAE Stats Feedback"

			// imageRecord, err := app.Dao().FindFirstRecordByData("images", "file_name", "app_logo")
			// if err != nil {
			// 	log.Printf("Failed to fetch logo from images collection: %v", err)
			// 	return nil
			// }

			// // Construct the logo URL
			// logoURL := fmt.Sprintf("%s/api/files/images/%s/%s",
			// 	baseUrl,
			// 	imageRecord.Id,
			// 	imageRecord.GetString("file"),
			// )

			// Email body in HTML format
			body := fmt.Sprintf(`
			<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Registration Update – UAE Stats App</title>
</head>
<body style="font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f8f8f8;">
    <table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0" align="center">
        <tr>
            <td align="center" style="padding: 20px;">
                <table role="presentation" width="600px" cellspacing="0" cellpadding="0" border="0" style="background-color: #ffffff; border: 2px solid #d1ad5c; padding: 20px;">
                    <tr>
                        <td align="center" style="font-size: 18px; font-weight: bold;">
						    <span style=" font-size:16px; border-bottom:1px solid #d1ad5c;padding-bottom: 10px;">
                            ملاحظات حول التطبيق الهاتفي للإحصاءات الإماراتي UAE <br>
							</span><br><span style="font-size: 18px;">UAE Stats Mobile Application Feedback</span>
                        <br><br><br>
                        </td>
                    </tr>
                    <tr>
                        <td style="padding-top: 20px; padding-bottom:30px; font-size: 12px;">
                            <table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0">
                                <tr>
                                    <td align="left" style="width: 50%%; vertical-align: top;">
                                        Hi Team,<br><br>
										We have received new feedback from %s. Below are the details:<br><br>
										<b>Feedback Details:</b>
										<ul style="padding-left: 0px !important;">
										<li><b>User Name:</b> %s</li>
										<li><b>Email:</b> %s</li>
										<li><b>Date:</b> %s</li>
										</ul><br><br>
										<b>Feedback:</b>
										<pre>
										1. How was your experience with us today : <b>%s</b>
										2. How good did we do in this aspect?
										    * Ease of Use: <b>%s</b>
										    * Quality: <b>%s</b>
										    * Design: <b>%s</b>
										    * Redundancy: <b>%s</b>
										3. Tell us how we can improve  
										    %s
										</pre>
                                    </td>
                                    <td align="right" style="width: 50%%; vertical-align: top; direction: rtl;">
                                        مرحبا فريق العمل،<br><br>
										لقد تلقينا تعليقات جديدة من %s. فيما يلي التفاصيل:<br><br>
										<br>
										<b>تفاصيل الملاحظات:</b>
										<ul style="padding-right: 12px !important;">
											<li><b>اسم المستخدم:</b> %s</li>
											<li><b>البريد الإلكتروني:</b> %s</li>
											<li><b>التاريخ :</b> %s</li>
										</ul><br><br>
										<b>تعليق :</b>
										<pre>
										1. كيف كانت تجربتك معنا اليوم؟ : <b>%s</b>										
										2. ما مدى نجاحنا في هذا الجانب؟
										    * سهولة الاستخدام: <b>%s</b>
										    * الجودة: <b>%s</b>
										    * التصميم: <b>%s</b>
										    * التكرار: <b>%s</b>
										3. أخبرنا كيف يمكننا تحسين خدماتنا.؟
										    %s
										</pre>
                                    </td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                    <tr>
                        <td align="center"  padding-top: 20px;">
                            <img src="https://imageats.s3.ap-south-1.amazonaws.com/signature.png" alt="We The UAE 2031" width="100%%">
                        </td>
                    </tr>
                       <tr>
	                        <td style="padding-top: 20px; font-size: 9px; color: #777; border-top: 1px solid #ccc;direction: rtl; text-align: right;">
	                        <strong>تنبيه:</strong><br>هذه الرسالة الإلكترونية و أيٌ من مرفقاتها قد تحتوي على معلومات سرية وهامة موجهه للشخص/الأشخاص المعنيين وعليه يرجى من المتلقي في حال تلقي الرسالة الإلكترونية عن طريق<br> الخطأ ولم يكن المعني بها، إخطار المرسل وحذفها من بريده الإلكتروني و كذلك إتلاف أي نسخ مطبوعة عنها حيث أنه يحظر عليه قراءة ونسخ ونشر أو توزيع أو استخدام هذه الرسالة<br> إلكترونية و أيٌ من مرفقاتها بأي شكل من الأشكال
	                        </td>
                             </tr>
                             <tr>
                             <td style="padding-top: 20px; font-size: 9px; color: #777;text-align: left; direction: ltr;">
	                            <strong>Disclaimer:</strong> <br>The information contained in and transmitted with this e-mail message is PRIVILEGED AND/OR CONFIDENTIAL.If you are not the intended recipient, or have received the message by error, please notify the sender via E-Mail or over the telephone and delete this e-mail. You are not authorized to read, copy, disseminate, distribute or use this E-Mail or any of its attachments in any way.
	                        </td>
	                    </tr>
                </table>
            </td>
        </tr>
    </table>
</body>
</html>`, userName, userName, userEmail, formattedDate, emojiRating, easeOfUse, quality, design, redundancy, additionalFeedback, userName, userName, userEmail, formattedDate, emojiRating, easeOfUse, quality, design, redundancy, additionalFeedback)

			// Define the target type (e.g., "smtp")
			targetType := "feedback_receive_mail"

			// Fetch the email configuration record where type matches targetType
			emailConfig, err := app.Dao().FindFirstRecordByData("email_configuration", "type", targetType)
			if err != nil {
				log.Printf("Failed to fetch email configuration for type '%s': %v", targetType, err)
				return fmt.Errorf("email configuration not found for type '%s'", targetType)
			}

			// Get the email address
			feedbackEmail := emailConfig.GetString("email")
			if feedbackEmail == "" {
				log.Printf("No email address configured for type '%s'", targetType)
				return fmt.Errorf("no email address configured for type '%s'", targetType)
			}
			// Create the email message
			message := &mailer.Message{
				From: mail.Address{
					Name:    "FCSC",
					Address: app.Settings().Meta.SenderAddress,
				},
				To: []mail.Address{
					{
						Name:    adminName,
						Address: feedbackEmail,
					},
				},
				Subject: subject,
				HTML:    body,
			}

			// Send the email
			err = app.NewMailClient().Send(message)
			if err != nil {
				log.Printf("Failed to send feedback email to admin: %v", err)
				return err
			}

			log.Println("Feedback email sent successfully to the admin.")
		}
		return nil
	})

	app.OnRecordAfterCreateRequest("otp_requests").Add(func(e *core.RecordCreateEvent) error {
		// Ensure the record has an email field
		email, ok := e.Record.Get("email").(string)
		if !ok || email == "" {
			log.Println("Invalid or missing email field.")
			return nil // or handle this case appropriately
		}

		// Generate the OTP
		otp, err := generateOTP()
		if err != nil {
			log.Printf("Failed to generate OTP: %v\n", err)
			return err
		}

		// Calculate the expiration time (5 minutes from now)
		expiresAt := time.Now().Add(5 * time.Minute)

		// Create a mail.Address for the sender
		sender := mail.Address{
			Name:    "FCSC",
			Address: app.Settings().Meta.SenderAddress, // Ensure this is a valid email
		}

		// Create the email message
		message := &mailer.Message{
			From:    sender,
			To:      []mail.Address{{Address: email}}, // Wrap the recipient email
			Subject: "YOUR VERIFICATION CODE",
			HTML: fmt.Sprintf(
				`<p>Thanks for verifying your <strong>%s</strong> account!</p>
					<p>Your code is: <strong>%d</strong></p>
					<p>Sincerely,<br>Support team.</p>`,
				email, otp,
			),
		}

		// Send the email
		if err := app.NewMailClient().Send(message); err != nil {
			log.Printf("Failed to send email: %v\n", err)
			return err
		}

		// Update the record with the OTP
		e.Record.Set("otp", fmt.Sprintf("%04d", otp))
		e.Record.Set("expires_at", expiresAt) // Set the expiration time

		if err := app.Dao().SaveRecord(e.Record); err != nil {
			log.Printf("Failed to update record with OTP and expiration time: %v\n", err)
			return err
		}

		log.Printf("OTP sent to %s expires at and saved successfully.\n", email)
		return nil
	})

	// Start the PocketBase server
	go func() {
		if err := app.Start(); err != nil {
			log.Fatal(err)
		}
	}()

	// Start your custom HTTP server on port 8091
	log.Println("Starting custom HTTP server on :8091")
	if err := http.ListenAndServe(":8091", nil); err != nil {
		log.Fatal("Failed to start server: ", err)
	}
}

// generateOTP generates a random 4-digit OTP
func generateOTP() (int, error) {
	max := big.NewInt(10000) // 4-digit numbers range: 0000 to 9999
	n, err := rand.Int(rand.Reader, max)
	if err != nil {
		return 0, err
	}
	return int(n.Int64()), nil
}

// Email verification handler
func verifyEmailHandler(c echo.Context, baseUrl string) error {
	log.Println("Starting verifyEmailHandler")

	// Get the userId from the query parameter
	userID := c.QueryParam("userId")

	if userID == "" {
		return c.JSON(http.StatusBadRequest, map[string]string{
			"message": "User ID not provided",
		})
	}

	// Check if app is properly initialized
	if app == nil {
		log.Fatal("PocketBase app is not initialized")
		return c.JSON(http.StatusInternalServerError, map[string]string{
			"message": "Internal server error",
		})
	}

	// Find the user record in PocketBase by userId
	record, err := app.Dao().FindRecordById("users", userID)
	if err != nil {
		log.Printf("Error finding user: %v", err)
		return c.JSON(500, map[string]interface{}{
			"code":    500,
			"message": "Error finding user.",
		})
	}

	// Check if the email is already verified
	if record.GetBool("user_mail_verify") {
		return c.HTML(http.StatusOK, `
		<html>
			<head>
				<style>
					body {
						font-family: Arial, sans-serif;
						background-color: #f0f8ff;
						display: flex;
						align-items: center;
						justify-content: center;
						height: 100vh;
						margin: 0;
					}
					.message-container {
						text-align: center;
						background-color: #ffe0e0;
						padding: 20px;
						border-radius: 10px;
						box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
					}
					.message-container h1 {
						font-size: 24px;
						color: #b71c1c;
						margin-bottom: 10px;
					}
					.message-container p {
						font-size: 16px;
						color: #880e4f;
					}
					.icon {
						font-size: 50px;
						color: #b71c1c;
						margin-bottom: 20px;
					}
				</style>
			</head>
			<body>
				<div class="message-container">
					<div class="icon">&#x2716;</div>
					<h1>Email Already Verified</h1>
					<p>Your email has already been verified. No further action is required.</p>
				</div>
			</body>
		</html>
		`)
	}

	// Set the verified status to true
	record.Set("user_mail_verify", true)
	// log.Printf("Failed to verify user: %v", err)
	if err := app.Dao().SaveRecord(record); err != nil {
		log.Printf("Failed to update email verification status: %v", err)
		return c.JSON(http.StatusInternalServerError, map[string]string{
			"error": "Failed to update email verification status",
		})
	}

	response := c.HTML(http.StatusOK, `
		<html>
			<head>
				<style>
					body {
						font-family: Arial, sans-serif;
						background-color: #f0f8ff;
						display: flex;
						align-items: center;
						justify-content: center;
						height: 100vh;
						margin: 0;
					}
					.message-container {
						text-align: center;
						background-color: #e0f7fa;
						padding: 20px;
						border-radius: 10px;
						box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
					}
					.message-container h1 {
						font-size: 24px;
						color: #00796b;
						margin-bottom: 10px;
					}
					.message-container p {
						font-size: 16px;
						color: #004d40;
					}
					.icon {
						font-size: 50px;
						color: #00796b;
						margin-bottom: 20px;
					}
				</style>
			</head>
			<body>
				<div class="message-container">
					<div class="icon">&#x2714;</div>
					<h1>Your registration is pending for Admin Approval</h1>
					<p>Access will be granted once your account is approved.</p>
				</div>
			</body>
		</html>
		`)

	// Fetch user details for email notification
	username := record.GetString("uname")
	userEmail := record.GetString("email")

	// Send admin email asynchronously
	go func() {
		err = sendAdminEmail(app, userID, username, userEmail, baseUrl)
		if err != nil {
			log.Printf("Failed to send admin email: %v", err)
		}
	}()

	return response

}

func sendAdminEmail(app *pocketbase.PocketBase, userID, username, userEmail string, baseUrl string) error {

	adminNameEn := "Admin"
	adminNameAr := "المدير"
	subject := "New User Registration Pending Review"

	// Fetch the email configuration from PocketBase
	emailConfig, err := app.Dao().FindFirstRecordByData("email_configuration", "type", "admin_receive_mail")
	if err != nil {
		log.Printf("Failed to fetch email configuration: %v", err)
		return err
	}
	adminEmail := emailConfig.GetString("email")

	// Construct email body
	body := fmt.Sprintf(`
		<!DOCTYPE html>
	<html>
	<head>
	    <meta charset="UTF-8">
	    <meta name="viewport" content="width=device-width, initial-scale=1">
	    <title>Registration Update – UAE Stats App</title>
	</head>
	<body style="font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f8f8f8;">
	    <table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0" align="center">
	        <tr>
	            <td align="center" style="padding: 20px;">
	                <table role="presentation" width="600px" cellspacing="0" cellpadding="0" border="0" style="background-color: #ffffff; border: 2px solid #d1ad5c; padding: 20px;">
	                     <tr>
	                        <td align="center" style="font-size: 18px; font-weight: bold;">
	                           <span style=" font-size:16px; border-bottom:1px solid #d1ad5c;padding-bottom: 10px;">
	                            تسجيل مستخدم جديد - تطبيق إحصاءات الإمارات العربية المتحدة - قيد المراجعة<br>
	                           </span>
	                           <br><span style="font-size: 18px;">New User Registration - UAE Stats App - Pending Review</span>
	                           <br><br><br>
	                        </td>
	                    </tr>
	                    <tr>
	                        <td style="padding-top: 20px; font-size: 12px; padding-bottom:30px">
	                            <table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0">
	                                <tr>
	                                    <td align="left" style="width:50%%; vertical-align: top;">
	                                        <strong>Dear <span style="color: #d1ad5c;">%s</span>,</strong><br><br>
	                                        A new user has registered on UAE Stats <br> Mobile app and is awaiting your review.
	                                        <br><br>
	                                        <strong>Details</strong><br>
	                                        Name: %s<br>
	                                        Email: <a href="mailto:%s" style="color: #d1ad5c; text-decoration: none;">%s</a>
	                                        <br><br>
	                                        Please review the registration and take <br> appropriate action
	                                        <br><br>
											<div style="text-align: left; direction: ltr;">
	                                        <a href="%s/api/custom/approve?userId=%s" style="display: inline-block;background-color: #11AF22; color: white; padding: 5px 15px; border: none; cursor: pointer; border-radius:2px; font-size: 12px;text-decoration: none;font-weight: 500;gap:6px;margin-right: 10%%;">Approve <img src="%s/api/getImagePath?file=approve.png"  alt=""  style="vertical-align:middle;width:16px;height:16px;margin-left:6px;"></a>
			                                <a href="%s/api/custom/reject?userId=%s" style="display: inline-block;background-color: #C1102E; color: white; padding: 5px 15px; border: none; cursor: pointer; border-radius:2px; font-size: 12px;text-decoration: none;font-weight: 500;gap:6px">Reject <img src="%s/api/getImagePath?file=reject.png"  alt=""  style="vertical-align:middle;width:16px;height:16px;margin-left:6px;"></a> 
											</div>	
	                                    </td>
	                                    <td align="right" style="width: 50%%; vertical-align: top; direction: rtl; text-align: right;">
											<strong>عزيزي <span style="color: #d1ad5c;">%s</span>،</strong><br><br>
                                            تم تسجيل مستخدم جديد في تطبيق إحصاءات الإمارات <br>
                                            للهواتف المحمولة، وهو بانتظار مراجعتك.
                                            <br><br>											
											<strong>التفاصيل</strong><br>
											الاسم: %s<br>
											البريد الإلكتروني: <a href="mailto:%s" style="color: #d1ad5c; text-decoration: none;">%s</a>
                                            <br><br>
									        يرجى مراجعة التسجيل واتخاذ الإجراء المناسب <br>
                                            الموافقة أو الرفض.
                                            <br><br>
											<div style="text-align: right; direction: rtl;">
											<a href="%s/api/custom/approve?userId=%s" style="display: inline-block;background-color: #11AF22; color: white; padding: 5px 15px; border: none; cursor: pointer; border-radius:2px; font-size: 12px;text-decoration: none;font-weight: 500;gap:6px;margin-left: 10%%;">يعتمد <img src="%s/api/getImagePath?file=approve.png"  alt="approve"  style="vertical-align:middle;width:16px;height:16px;margin-left:6px;"></a>
											<a href="%s/api/custom/reject?userId=%s" style="display: inline-block;background-color: #C1102E; color: white; padding: 5px 15px; border: none; cursor: pointer; border-radius:2px; font-size: 12px;text-decoration: none;font-weight: 500;gap:6px">يرفض <img src="%s/api/getImagePath?file=reject.png"  alt="approve"  style="vertical-align:middle;width:16px;height:16px;margin-left:6px;"></a>												
											</div>										
										</td>
	                                </tr>
	                            </table>
	                        </td>
	                    </tr>
	                    <tr>
	                        <td align="center" >
	                            <img src="https://imageats.s3.ap-south-1.amazonaws.com/signature.png" alt="We The UAE 2031" width="100%%">
	                        </td>
	                    </tr>
	                       <tr>
	                        <td style="padding-top: 20px; font-size: 9px; color: #777; border-top: 1px solid #ccc;direction: rtl; text-align: right;">
	                        <strong>تنبيه:</strong><br>هذه الرسالة الإلكترونية و أيٌ من مرفقاتها قد تحتوي على معلومات سرية وهامة موجهه للشخص/الأشخاص المعنيين وعليه يرجى من المتلقي في حال تلقي الرسالة الإلكترونية عن طريق<br> الخطأ ولم يكن المعني بها، إخطار المرسل وحذفها من بريده الإلكتروني و كذلك إتلاف أي نسخ مطبوعة عنها حيث أنه يحظر عليه قراءة ونسخ ونشر أو توزيع أو استخدام هذه الرسالة<br> إلكترونية و أيٌ من مرفقاتها بأي شكل من الأشكال
	                        </td>
                             </tr>
                             <tr>
                             <td style="padding-top: 20px; font-size: 9px; color: #777;text-align: left; direction: ltr;">
	                            <strong>Disclaimer:</strong> <br>The information contained in and transmitted with this e-mail message is PRIVILEGED AND/OR CONFIDENTIAL.If you are not the intended recipient, or have received the message by error, please notify the sender via E-Mail or over the telephone and delete this e-mail. You are not authorized to read, copy, disseminate, distribute or use this E-Mail or any of its attachments in any way.
	                        </td>
	                    </tr>
	                </table>
	            </td>
	        </tr>
	    </table>
	</body>
	</html>
	`,
		adminNameEn, username, userEmail, userEmail, baseUrl, userID, baseUrl, baseUrl, userID, baseUrl,
		adminNameAr, username, userEmail, userEmail, baseUrl, userID, baseUrl, baseUrl, userID, baseUrl)

	// Send email
	message := &mailer.Message{
		From: mail.Address{
			Name:    "FCSC",
			Address: app.Settings().Meta.SenderAddress,
		},
		To: []mail.Address{
			{Name: adminNameEn, Address: adminEmail},
		},
		Subject: subject,
		HTML:    body,
	}

	if err := app.NewMailClient().Send(message); err != nil {
		log.Printf("Failed to send admin email: %v", err)
		return err
	}

	log.Println("Admin email sent successfully.")
	return nil
}

func sendStatusChangeEmail(app *pocketbase.PocketBase, record *models.Record, newStatus string) error {
	userEmail := record.GetString("email")
	userName := record.GetString("uname")
	log.Printf("userName: %s", userName)

	if userEmail == "" {
		return fmt.Errorf("user email is empty")
	}

	// imageRecord, err := app.Dao().FindFirstRecordByData("images", "file_name", "app_logo")
	// if err != nil {
	// 	log.Printf("Failed to fetch logo from images collection: %v", err)
	// 	return nil
	// }

	// // Construct the logo URL
	// logoURL := fmt.Sprintf("%s/api/files/images/%s/%s",
	// 	baseUrl,
	// 	imageRecord.Id,
	// 	imageRecord.GetString("file"),
	// )

	var subject, body string

	if newStatus == "Approved" {
		subject = "Registration Approved – FCSC"
		body = fmt.Sprintf(`
			<!DOCTYPE html>
			<html>
			<head>
				<meta charset="UTF-8">
				<meta name="viewport" content="width=device-width, initial-scale=1">
				<title>Registration Update – UAE Stats App</title>
			</head>
			<body style="font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f8f8f8;">
				<table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0" align="center">
					<tr>
						<td align="center" style="padding: 20px;">
							<table role="presentation" width="600px" cellspacing="0" cellpadding="0" border="0" style="background-color: #ffffff; border: 2px solid #d1ad5c; padding: 20px;">
								<tr>
								
								<tr>
									<td align="center" style="font-size: 18px; font-weight: bold;">
									<span style=" font-size:18px; border-bottom:1px solid #d1ad5c;padding-bottom: 10px;">
										تمت الموافقة على التسجيل - إحصاءات الإمارات العربية المتحدة<br>
										</span><br><span style="font-size: 18px;">Registration Approved – UAE Statistics App</span>
									<br><br><br>
									</td>
								</tr>
								<tr>
									<td style="padding-top: 20px; font-size: 12px;">
										<table role="presentation" width="100%" cellspacing="0" cellpadding="0" border="0">
											<tr>
												<td align="left" style="width: 50%%; vertical-align: top;">
													<strong>Dear <span style="color: #d1ad5c;">%s</span>,</strong><br><br>
													We are pleased to inform you that your <br>registration with UAE Stats app has been <br>approved. You can now log in using the <br>credentials that you had created.
													<br><br>
													Please access your account using the <br>following link: <a href="https://fcscapp.onelink.me/login" style="color: #d1ad5c; text-decoration: none;">Login</a>.
													<br><br>
													If you have any questions or believe this <br>decision was made in error, please feel free <br>to contact us for further clarification.
												</td>
												<td align="right" style="width: 50%%; vertical-align: top; direction: rtl; padding-bottom:30px">
													<strong>عزيزي <span style="color: #d1ad5c;">%s</span>،</strong><br><br>
													يسرنا إبلاغك بأنه قد تمت الموافقة على تسجيلك<br> في تطبيق إحصاءات الإمارات. يمكنك الآن تسجيل <br>الدخول باستخدام بيانات الاعتماد التي أنشأتها.
													<br><br>
													<br>
													يرجى الوصول إلى حسابك عبر الرابط التالي <br><a href="https://fcscapp.onelink.me/login" style="color: #d1ad5c; text-decoration: none;">تسجيل الدخول</a>.
													<br><br>
													إذا كانت لديك أي استفسارات أو كنت تعتقد أن هذا<br> القرار خاطئ، فلا تتردد في التواصل معنا لمزيد<br> من التوضيح.
												</td>
											</tr>
										</table>
									</td>
								</tr>
								<tr>
									<td align="center">
										<img src="https://imageats.s3.ap-south-1.amazonaws.com/signature.png" alt="We The UAE 2031" width="100%%">
									</td>
								</tr>
								  <tr>
	                        <td style="padding-top: 20px; font-size: 9px; color: #777; border-top: 1px solid #ccc;direction: rtl; text-align: right;">
	                        <strong>تنبيه:</strong><br>هذه الرسالة الإلكترونية و أيٌ من مرفقاتها قد تحتوي على معلومات سرية وهامة موجهه للشخص/الأشخاص المعنيين وعليه يرجى من المتلقي في حال تلقي الرسالة الإلكترونية عن طريق<br> الخطأ ولم يكن المعني بها، إخطار المرسل وحذفها من بريده الإلكتروني و كذلك إتلاف أي نسخ مطبوعة عنها حيث أنه يحظر عليه قراءة ونسخ ونشر أو توزيع أو استخدام هذه الرسالة<br> إلكترونية و أيٌ من مرفقاتها بأي شكل من الأشكال
	                        </td>
                             </tr>
                             <tr>
                             <td style="padding-top: 20px; font-size: 9px; color: #777;text-align: left; direction: ltr;">
	                            <strong>Disclaimer:</strong> <br>The information contained in and transmitted with this e-mail message is PRIVILEGED AND/OR CONFIDENTIAL.If you are not the intended recipient, or have received the message by error, please notify the sender via E-Mail or over the telephone and delete this e-mail. You are not authorized to read, copy, disseminate, distribute or use this E-Mail or any of its attachments in any way.
	                        </td>
	                    </tr>
							</table>
						</td>
					</tr>
				</table>
			</body>
			</html>
		`, userName, userName)
	} else if newStatus == "Denied" {
		subject = "Registration Update – FCSC"
		body = fmt.Sprintf(`
			<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Registration Update – UAE Stats App</title>
</head>
<body style="font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f8f8f8;">
    <table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0" align="center">
        <tr>
            <td align="center" style="padding: 20px;">
                <table role="presentation" width="600px" cellspacing="0" cellpadding="0" border="0" style="background-color: #ffffff; border: 2px solid #d1ad5c; padding: 20px;">
                    <tr>
                        <td align="center" style="font-size: 18px; font-weight: bold;">
						    <span style=" font-size:16px; border-bottom:1px solid #d1ad5c;padding-bottom: 10px;">
                            تحديث التسجيل – تطبيق إحصاءات الإمارات العربية المتحدة<br>
                            </span><br><span style="font-size: 18px;">Registration Update – UAE Stats App</span>
                        <br><br><br>
                        </td>
                    </tr>
                    <tr>
                        <td style="padding-top: 20px; padding-bottom:30px; font-size: 12px;">
                            <table role="presentation" width="100%%" cellspacing="0" cellpadding="0" border="0" style="width:100%%;">
                                <tr>
                                    <td align="left" style="width: 50%%; vertical-align: top;">
                                        <strong>Dear <span style="color: #d1ad5c;">%s</span>,</strong><br><br>
                                        Thank you for registering with UAE Stats <br>Mobile App. After careful review, we regret <br>to inform you that your registration has not <br>been approved at this time.
                                        <br><br>
                                        If you have any questions or believe this <br>decision was made in error, please feel free <br>to <a href="https://fcsc.gov.ae/en-us/Pages/e-Participation/Contact-Us.aspx" style="color: #d1ad5c; text-decoration: none; font-weight: bold;">contact us</a> for further clarification.
                                        <br><br>
                                        We appreciate your understanding and thank <br>you for your interest.
                                    </td>
                                    <td align="right" style="width: 50%%; vertical-align: top; direction: rtl;">
                                        <strong>عزيزي <span style="color: #d1ad5c;">%s</span>،</strong><br><br>
                                        نشكرك على تسجيلك في تطبيق إحصاءات<br> الإمارات للهواتف المحمولة. بعد مراجعة دقيقة،<br> يؤسفنا إبلاغك بأنه لم تتم الموافقة على تسجيلك<br> حتى الآن.
                                        <br><br>
                                        إذا كانت لديك أي أسئلة أو كنت تعتقد أن هذا القرار<br> خاطئ، فلا تتردد في التواصل معنا لمزيد من<br> التوضيح. 
                                        <br><br>
                                        نقدّر تفهمك ونشكرك على اهتمامك.
                                    </td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                    <tr>
                        <td align="center"  padding-top: 20px;">
                            <img src="https://imageats.s3.ap-south-1.amazonaws.com/signature.png" alt="We The UAE 2031" width="100%%">
                        </td>
                    </tr>
                      <tr>
	                        <td style="padding-top: 20px; font-size: 9px; color: #777; border-top: 1px solid #ccc;direction: rtl; text-align: right;">
	                        <strong>تنبيه:</strong><br>هذه الرسالة الإلكترونية و أيٌ من مرفقاتها قد تحتوي على معلومات سرية وهامة موجهه للشخص/الأشخاص المعنيين وعليه يرجى من المتلقي في حال تلقي الرسالة الإلكترونية عن طريق<br> الخطأ ولم يكن المعني بها، إخطار المرسل وحذفها من بريده الإلكتروني و كذلك إتلاف أي نسخ مطبوعة عنها حيث أنه يحظر عليه قراءة ونسخ ونشر أو توزيع أو استخدام هذه الرسالة<br> إلكترونية و أيٌ من مرفقاتها بأي شكل من الأشكال
	                        </td>
                             </tr>
                             <tr>
                             <td style="padding-top: 20px; font-size: 9px; color: #777;text-align: left; direction: ltr;">
	                            <strong>Disclaimer:</strong> <br>The information contained in and transmitted with this e-mail message is PRIVILEGED AND/OR CONFIDENTIAL.If you are not the intended recipient, or have received the message by error, please notify the sender via E-Mail or over the telephone and delete this e-mail. You are not authorized to read, copy, disseminate, distribute or use this E-Mail or any of its attachments in any way.
	                        </td>
	                    </tr>
                </table>
            </td>
        </tr>
    </table>
</body>
</html>
		`, userName, userName)
	} else {
		// If the status is neither "Approved" nor "Denied", skip sending an email
		return nil
	}

	// Create and send the email
	message := &mailer.Message{
		From: mail.Address{
			Name:    "FCSC",
			Address: app.Settings().Meta.SenderAddress,
		},
		To: []mail.Address{
			{
				Name:    userName,
				Address: userEmail,
			},
		},
		Subject: subject,
		HTML:    body,
	}

	err := app.NewMailClient().Send(message)
	if err != nil {
		return fmt.Errorf("failed to send email: %v", err)
	}

	log.Printf("Status change email sent successfully to: %s", userEmail)
	return nil
}

func parseRelationIDs(value string) []string {
	if value == "" {
		return []string{}
	}

	var ids []string
	_ = json.Unmarshal([]byte(value), &ids)
	return ids
}

func buildSafeInClause(ids []string) string {
	quoted := make([]string, 0, len(ids))

	for _, id := range ids {
		if id == "" {
			continue
		}
		// PocketBase IDs are safe: lowercase letters + numbers
		quoted = append(quoted, "'"+id+"'")
	}

	if len(quoted) == 0 {
		return "('')" // never matches
	}

	return "(" + strings.Join(quoted, ",") + ")"
}