#!/bin/bash

# Script to remove the location (PostGIS) column from time_clocks COPY data
# The location column is field 16 (1-indexed) in the tab-separated data

INPUT_FILE="devdb_backup_20251013_110130.sql"
OUTPUT_FILE="devdb_backup_cleaned.sql"

echo "Creating cleaned backup file without PostGIS dependencies..."

# First, remove the geography column from CREATE TABLE
sed 's/location public\.geography(Point,4326),/-- location public.geography(Point,4326) -- REMOVED: PostGIS not available,/' "$INPUT_FILE" > "$OUTPUT_FILE.tmp"

# Remove PostGIS extension
sed -i 's/CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;/-- CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public; -- REMOVED: PostGIS not available/' "$OUTPUT_FILE.tmp"

# Fix the COPY statement to exclude location column
sed -i 's/COPY public\.time_clocks (time_clocks_id, org_id, user_id, clock_type, lat, lng, valid_gps, location_id, locationapproved, security_type, clocked_at, isdeleted, updatedat, tenant_id, branch_id, location, location_id_legacy, source) FROM stdin;/COPY public.time_clocks (time_clocks_id, org_id, user_id, clock_type, lat, lng, valid_gps, location_id, locationapproved, security_type, clocked_at, isdeleted, updatedat, tenant_id, branch_id, location_id_legacy, source) FROM stdin;/' "$OUTPUT_FILE.tmp"

# Now remove the location column data (field 16) from time_clocks COPY data
# Find the COPY line number
COPY_LINE=$(grep -n "COPY public.time_clocks" "$OUTPUT_FILE.tmp" | cut -d: -f1)
END_LINE=$(tail -n +$((COPY_LINE + 1)) "$OUTPUT_FILE.tmp" | grep -n "^\\\\\\.$" | head -1 | cut -d: -f1)
END_LINE=$((COPY_LINE + END_LINE))

echo "Processing time_clocks data from line $COPY_LINE to $END_LINE..."

# Split file into before, during, and after COPY section
head -n $COPY_LINE "$OUTPUT_FILE.tmp" > "$OUTPUT_FILE"

# Process the data lines, removing field 16
tail -n +$((COPY_LINE + 1)) "$OUTPUT_FILE.tmp" | head -n $((END_LINE - COPY_LINE - 1)) | \
awk -F'\t' 'BEGIN{OFS="\t"} {
    for (i=1; i<=15; i++) printf "%s%s", $i, (i<15 ? OFS : "");
    for (i=17; i<=NF; i++) printf "%s%s", OFS, $i;
    print "";
}' >> "$OUTPUT_FILE"

# Add the rest of the file
tail -n +$((END_LINE)) "$OUTPUT_FILE.tmp" >> "$OUTPUT_FILE"

rm "$OUTPUT_FILE.tmp"

echo "Cleaned backup file created: $OUTPUT_FILE"
echo "Original file preserved: $INPUT_FILE"