#!/bin/bash

# Script to sync target keywords embeddings from database to API
# Gets records where is_target_keyword_updated = 1 and sends them to the generate-menu-embeddings API

set -e

# Check environment argument
if [ -z "$1" ]; then
  echo "❌ Please provide an environment (e.g., development, staging, production)."
  exit 1
fi

# Set environment file based on argument
ENV_FILE=".env.$1"
if [ ! -f "$ENV_FILE" ]; then
  echo "❌ Environment file $ENV_FILE not found!"
  exit 1
fi

# Clean CRLF line endings from the env file (in case it was edited on Windows)
sed -i 's/\r$//' "$ENV_FILE"

# Load environment variables
set -a
source "$ENV_FILE"
set +a

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

echo -e "${YELLOW}Starting target keywords embeddings sync...${NC}"

# Get records from database where is_target_keyword_updated = 1
echo -e "${YELLOW}Fetching records from database...${NC}"

QUERY="SELECT 
    rke.target,
    rke.target_keywords,
    l.code as language_code
FROM route_keywords_embeddings rke
LEFT JOIN languages l ON rke.language_id = l.id AND rke.company_id = l.company_id
WHERE rke.is_target_keyword_updated = 1 
AND rke.target IS NOT NULL 
AND rke.target_keywords IS NOT NULL 
AND rke.company_id = 1
AND rke.status_id = 1
ORDER BY rke.id
LIMIT 10;"

# Check if psql is available
if ! command -v psql &> /dev/null; then
    echo -e "${RED}❌ PostgreSQL client (psql) not found. Please install PostgreSQL client.${NC}"
    echo -e "${YELLOW}On Ubuntu/Debian: sudo apt-get install postgresql-client${NC}"
    echo -e "${YELLOW}On CentOS/RHEL: sudo yum install postgresql${NC}"
    echo -e "${YELLOW}On macOS: brew install postgresql${NC}"
    exit 1
fi

# Execute query and format as JSON array
echo -e "${YELLOW}Debug: Executing database query...${NC}"
RAW_DATA=$(PGPASSWORD=$DATABASE_PASSWORD psql -h "$DATABASE_HOST" -p "$DATABASE_PORT" -U "$DATABASE_USER" -d "$DATABASE_NAME" -t -c "$QUERY")
echo -e "${YELLOW}Debug: Raw database output:${NC}"
echo "$RAW_DATA"
echo -e "${YELLOW}Debug: Converting to JSON...${NC}"

DATA=$(echo "$RAW_DATA" | \
    awk 'BEGIN {print "["; first=1} 
    { 
        gsub(/^[ \t]+|[ \t]+$/, "", $0); 
        if (length($0) > 0) {
            if (!first) print ",";
            gsub(/"/, "\\\"", $0);
            split($0, arr, /\|/);
            # Trim whitespace from each field
            gsub(/^[ \t]+|[ \t]+$/, "", arr[1]);
            gsub(/^[ \t]+|[ \t]+$/, "", arr[2]);
            gsub(/^[ \t]+|[ \t]+$/, "", arr[3]);
            printf "{\n";
            printf "  \"target\": \"%s\",\n", arr[1];
            printf "  \"keywords\": \"%s\",\n", arr[2];
            printf "  \"language_code\": \"%s\"\n", arr[3];
            printf "}";
            first=0;
        }
    } 
    END {print "]"}')

echo -e "${YELLOW}Debug: Final JSON data:${NC}"
echo "$DATA"

# Strip whitespace and newlines for comparison
CLEAN_DATA=$(echo "$DATA" | tr -d '\n\r\t ')

if [ "$CLEAN_DATA" = "[]" ] || [ -z "$DATA" ] || [ "$DATA" = "" ]; then
    echo -e "${GREEN}No records found with is_target_keyword_updated = 1${NC}"
    echo -e "${GREEN}Nothing to process. Exiting...${NC}"
    exit 0
fi

echo -e "${GREEN}Found records to process:${NC}"
echo "$DATA" | jq '.' 2>/dev/null || echo "$DATA"

# Send data to API
echo -e "${YELLOW}Sending data to API...${NC}"

# Get API base URL from environment or use default
API_BASE_URL="${API_BASE_URL:-http://localhost:3131}"
API_ENDPOINT="/api/openai/generate-menu-embeddings"

RESPONSE=$(curl -s -w "\n%{http_code}" -X POST \
    "$API_BASE_URL$API_ENDPOINT" \
    -H "Content-Type: application/json" \
    -d "$DATA")

# Extract HTTP code and response body
HTTP_CODE=$(echo "$RESPONSE" | tail -n1)
RESPONSE_BODY=$(echo "$RESPONSE" | head -n -1)

if [ "$HTTP_CODE" -eq 200 ]; then
    echo -e "${GREEN}✅ API call successful!${NC}"
    echo -e "${GREEN}Response:${NC}"
    echo "$RESPONSE_BODY" | jq '.' 2>/dev/null || echo "$RESPONSE_BODY"
    
    # Update is_target_keyword_updated 1 to 0 for processed records
    echo -e "${YELLOW}Updating processed records...${NC}"
    UPDATE_QUERY="UPDATE route_keywords_embeddings 
    SET is_target_keyword_updated = 0 
    WHERE is_target_keyword_updated = 1 
    AND target IS NOT NULL 
    AND target_keywords IS NOT NULL 
    AND company_id = 1 
    AND status_id = 1;"
    
    PGPASSWORD=$DATABASE_PASSWORD psql -h "$DATABASE_HOST" -p "$DATABASE_PORT" -U "$DATABASE_USER" -d "$DATABASE_NAME" -c "$UPDATE_QUERY"
    echo -e "${GREEN}✅ Records updated successfully${NC}"
    
else
    echo -e "${RED}❌ API call failed with HTTP $HTTP_CODE${NC}"
    echo -e "${RED}Response:${NC}"
    echo "$RESPONSE_BODY"
    exit 1
fi

echo -e "${GREEN}🎉 Target keywords embeddings sync completed successfully!${NC}"