## Update SQLite3 and create sql.gz

LIVE=/storage/web/mathelier/JASPAR/JASPAR_metadata/2026/JASPAR2026.sqlite3
RELEASE=/storage/web/mathelier/JASPAR/JASPAR_PFM_release_data/2026

# 1. Work on a copy to avoid database locking
cp "$LIVE" "${LIVE}.patch"

# 2. Apply UniProt ID replacements
sqlite3 "${LIVE}.patch" "
BEGIN;
UPDATE MATRIX_PROTEIN SET ACC='Q9LTC6'     WHERE ACC='A0A178VAV2';
UPDATE MATRIX_PROTEIN SET ACC='A0A0P0VDM9' WHERE ACC='Q6YPG7';
UPDATE MATRIX_PROTEIN SET ACC='P42224'     WHERE ACC='Q53XW4';
UPDATE MATRIX_PROTEIN SET ACC='A0A8V0Y5J9' WHERE ACC='A0A1D5NT38';
UPDATE MATRIX_PROTEIN SET ACC='A0A8V0XF65' WHERE ACC='F1NCE0';
UPDATE MATRIX_PROTEIN SET ACC='Q9H4S2'     WHERE ACC='A4IFQ3';
UPDATE MATRIX_PROTEIN SET ACC='J7SF93'     WHERE ACC='A8X9D5';
COMMIT;
"

# 3. Verify: old IDs should be gone (expect 0)
sqlite3 "${LIVE}.patch" "SELECT COUNT(*) FROM MATRIX_PROTEIN WHERE ACC IN ('A0A178VAV2','Q6YPG7','Q53XW4','A0A1D5NT38','F1NCE0','A4IFQ3','A8X9D5');"

# 4. Verify: new IDs are present
sqlite3 "${LIVE}.patch" "SELECT ACC, COUNT(*) FROM MATRIX_PROTEIN WHERE ACC IN ('Q9LTC6','A0A0P0VDM9','P42224','A0A8V0Y5J9','A0A8V0XF65','Q9H4S2','J7SF93') GROUP BY ACC;"

# 5. Backup the live db, then swap patched copy into place (mv is atomic on same filesystem)
cp "$LIVE" "${LIVE}.bak"
mv "${LIVE}.patch" "$LIVE"

# 6. Dump updated SQLite3 to SQL and compress into the release directory
sqlite3 "$LIVE" .dump > "$RELEASE/JASPAR2026.sql"
gzip -k "$RELEASE/JASPAR2026.sql"