#!/bin/bash
# Safe data insertion script with duplicate prevention
# This script inserts data from insert_data.sql into munafasaai database
# preventing duplicates by using ON CONFLICT DO NOTHING

set -e

DB_USER="munafasaai"
DB_PASS="munafasaai_password_2026"
DB_HOST="127.0.0.1"
DB_PORT="5433"
DB_NAME="munafasaai"
SQL_FILE="/home/ashraffarid2010/munafasaai.com/insert_data.sql"

DATABASE_URL="postgresql://${DB_USER}:${DB_PASS}@${DB_HOST}:${DB_PORT}/${DB_NAME}"

echo "=== Safe Data Insertion for munafasaai Database ==="
echo ""

# Check if SQL file exists
if [ ! -f "$SQL_FILE" ]; then
    echo "Error: SQL file not found at $SQL_FILE"
    exit 1
fi

echo "Step 1: Creating safe insert script..."
cat > /tmp/insert_data_safe.sql << 'EOF'
-- Safe insert script with duplicate prevention
-- This script converts INSERT statements to use ON CONFLICT DO NOTHING

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;

-- Disable triggers during bulk insert
ALTER TABLE public.activity_events DISABLE TRIGGER ALL;
ALTER TABLE public.analyses DISABLE TRIGGER ALL;
ALTER TABLE public.audit_log DISABLE TRIGGER ALL;
ALTER TABLE public.chat_messages DISABLE TRIGGER ALL;
ALTER TABLE public.companies DISABLE TRIGGER ALL;
ALTER TABLE public.compliance_items DISABLE TRIGGER ALL;
ALTER TABLE public.documents DISABLE TRIGGER ALL;
ALTER TABLE public.notification_settings DISABLE TRIGGER ALL;
ALTER TABLE public.plans DISABLE TRIGGER ALL;
ALTER TABLE public.proposal_drafts DISABLE TRIGGER ALL;
ALTER TABLE public.proposal_versions DISABLE TRIGGER ALL;
ALTER TABLE public.submission_checklist DISABLE TRIGGER ALL;
ALTER TABLE public.tender_documents DISABLE TRIGGER ALL;
ALTER TABLE public.tender_fdds DISABLE TRIGGER ALL;
ALTER TABLE public.tenders DISABLE TRIGGER ALL;
ALTER TABLE public.users DISABLE TRIGGER ALL;
ALTER TABLE public.vendor_products DISABLE TRIGGER ALL;
ALTER TABLE public.vendors DISABLE TRIGGER ALL;
EOF

# Convert INSERT statements to use ON CONFLICT
echo "Processing SQL file..."
sed -E 's/INSERT INTO public\.([a-z_]+) \(([)]+)\) VALUES \(([)]+)\);/INSERT INTO public.\1 (\2) VALUES (\3) ON CONFLICT (id) DO NOTHING;/g' "$SQL_FILE" >> /tmp/insert_data_safe.sql

# Re-enable triggers
cat >> /tmp/insert_data_safe.sql << 'EOF'
-- Re-enable triggers after bulk insert
ALTER TABLE public.activity_events ENABLE TRIGGER ALL;
ALTER TABLE public.analyses ENABLE TRIGGER ALL;
ALTER TABLE public.audit_log ENABLE TRIGGER ALL;
ALTER TABLE public.chat_messages ENABLE TRIGGER ALL;
ALTER TABLE public.companies ENABLE TRIGGER ALL;
ALTER TABLE public.compliance_items ENABLE TRIGGER ALL;
ALTER TABLE public.documents ENABLE TRIGGER ALL;
ALTER TABLE public.notification_settings ENABLE TRIGGER ALL;
ALTER TABLE public.plans ENABLE TRIGGER ALL;
ALTER TABLE public.proposal_drafts ENABLE TRIGGER ALL;
ALTER TABLE public.proposal_versions ENABLE TRIGGER ALL;
ALTER TABLE public.submission_checklist ENABLE TRIGGER ALL;
ALTER TABLE public.tender_documents ENABLE TRIGGER ALL;
ALTER TABLE public.tender_fdds ENABLE TRIGGER ALL;
ALTER TABLE public.tenders ENABLE TRIGGER ALL;
ALTER TABLE public.users ENABLE TRIGGER ALL;
ALTER TABLE public.vendor_products ENABLE TRIGGER ALL;
ALTER TABLE public.vendors ENABLE TRIGGER ALL;
EOF

echo "Step 2: Backing up current data counts..."
PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT
  'activity_events' as table_name, COUNT(*) as row_count FROM activity_events
UNION ALL SELECT 'analyses', COUNT(*) FROM analyses
UNION ALL SELECT 'audit_log', COUNT(*) FROM audit_log
UNION ALL SELECT 'chat_messages', COUNT(*) FROM chat_messages
UNION ALL SELECT 'companies', COUNT(*) FROM companies
UNION ALL SELECT 'compliance_items', COUNT(*) FROM compliance_items
UNION ALL SELECT 'documents', COUNT(*) FROM documents
UNION ALL SELECT 'notification_settings', COUNT(*) FROM notification_settings
UNION ALL SELECT 'plans', COUNT(*) FROM plans
UNION ALL SELECT 'proposal_drafts', COUNT(*) FROM proposal_drafts
UNION ALL SELECT 'proposal_versions', COUNT(*) FROM proposal_versions
UNION ALL SELECT 'submission_checklist', COUNT(*) FROM submission_checklist
UNION ALL SELECT 'tender_documents', COUNT(*) FROM tender_documents
UNION ALL SELECT 'tender_fdds', COUNT(*) FROM tender_fdds
UNION ALL SELECT 'tenders', COUNT(*) FROM tenders
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'vendor_products', COUNT(*) FROM vendor_products
UNION ALL SELECT 'vendors', COUNT(*) FROM vendors;
" 2>&1 || true

echo ""
echo "Step 3: Executing data insertion..."
PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -f /tmp/insert_data_safe.sql 2>&1

echo ""
echo "Step 4: Verifying inserted data..."
PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT
  'activity_events' as table_name, COUNT(*) as row_count FROM activity_events
UNION ALL SELECT 'analyses', COUNT(*) FROM analyses
UNION ALL SELECT 'audit_log', COUNT(*) FROM audit_log
UNION ALL SELECT 'chat_messages', COUNT(*) FROM chat_messages
UNION ALL SELECT 'companies', COUNT(*) FROM companies
UNION ALL SELECT 'compliance_items', COUNT(*) FROM compliance_items
UNION ALL SELECT 'documents', COUNT(*) FROM documents
UNION ALL SELECT 'notification_settings', COUNT(*) FROM notification_settings
UNION ALL SELECT 'plans', COUNT(*) FROM plans
UNION ALL SELECT 'proposal_drafts', COUNT(*) FROM proposal_drafts
UNION ALL SELECT 'proposal_versions', COUNT(*) FROM proposal_versions
UNION ALL SELECT 'submission_checklist', COUNT(*) FROM submission_checklist
UNION ALL SELECT 'tender_documents', COUNT(*) FROM tender_documents
UNION ALL SELECT 'tender_fdds', COUNT(*) FROM tender_fdds
UNION ALL SELECT 'tenders', COUNT(*) FROM tenders
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'vendor_products', COUNT(*) FROM vendor_products
UNION ALL SELECT 'vendors', COUNT(*) FROM vendors;
" 2>&1

echo ""
echo "=== Data Insertion Complete ==="
echo "Temporary SQL file saved at: /tmp/insert_data_safe.sql"