277 lines
14 KiB
Bash
Executable File
277 lines
14 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
set -Eeuo pipefail
|
|
|
|
ROOT="${ROOT:-/var/www/dhv2.korexlabs.com}"
|
|
TOKEN="DH-DEV-DEMO"
|
|
APPLY=false
|
|
if [[ "${1:-}" == "--apply" && "${2:-}" == "$TOKEN" ]]; then APPLY=true; fi
|
|
cd "$ROOT"
|
|
|
|
DB_SERVICE="${DB_SERVICE:-db}"
|
|
psql_owner() {
|
|
docker compose exec -T "$DB_SERVICE" sh -lc \
|
|
'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -At -v ON_ERROR_STOP=1' <<<"$1"
|
|
}
|
|
|
|
DEMO_CTE="WITH demo_assets AS (
|
|
SELECT id FROM assets
|
|
WHERE code LIKE 'DEV-DEMO-%' OR source_reference LIKE 'DH-DEV-DEMO:%'
|
|
), demo_documents AS (
|
|
SELECT id FROM source_documents WHERE external_reference LIKE 'DH-DEV-DEMO:%' OR document_number LIKE '%DH-DEV-DEMO%'
|
|
), demo_visits AS (
|
|
SELECT DISTINCT v.id
|
|
FROM inspection_visits v
|
|
LEFT JOIN inspection_visit_assets va ON va.visit_id = v.id
|
|
WHERE v.scope_asset_id IN (SELECT id FROM demo_assets)
|
|
OR va.asset_id IN (SELECT id FROM demo_assets)
|
|
), demo_acts AS (
|
|
SELECT id FROM inspection_acts WHERE visit_id IN (SELECT id FROM demo_visits)
|
|
), demo_campaigns AS (
|
|
SELECT DISTINCT c.id
|
|
FROM survey_campaigns c
|
|
LEFT JOIN survey_campaign_targets t ON t.campaign_id = c.id
|
|
WHERE c.scope_asset_id IN (SELECT id FROM demo_assets)
|
|
OR t.asset_id IN (SELECT id FROM demo_assets)
|
|
)"
|
|
|
|
asset_count="$(psql_owner "$DEMO_CTE SELECT COUNT(*) FROM demo_assets;")"
|
|
visit_count="$(psql_owner "$DEMO_CTE SELECT COUNT(*) FROM demo_visits;")"
|
|
act_count="$(psql_owner "$DEMO_CTE SELECT COUNT(*) FROM demo_acts;")"
|
|
campaign_count="$(psql_owner "$DEMO_CTE SELECT COUNT(*) FROM demo_campaigns;")"
|
|
document_count="$(psql_owner "$DEMO_CTE SELECT COUNT(*) FROM demo_documents;")"
|
|
|
|
echo "========== DH · LIMPIEZA DE DATOS DE PRUEBA =========="
|
|
echo "Activos DEV-DEMO detectados : $asset_count"
|
|
echo "Visitas relacionadas : $visit_count"
|
|
echo "Actas relacionadas : $act_count"
|
|
echo "Relevamientos relacionados : $campaign_count"
|
|
echo "Documentos DEV-DEMO : $document_count"
|
|
echo
|
|
|
|
if [[ "$asset_count" == "0" ]]; then
|
|
echo "No hay datos DEV-DEMO para limpiar."
|
|
exit 0
|
|
fi
|
|
|
|
# Nunca ampliamos implícitamente el conjunto a registros no marcados como prueba.
|
|
# Si un dato no demo depende de uno demo, o un flujo mezcla ambos entornos, se aborta.
|
|
MIXED_SQL="$DEMO_CTE
|
|
SELECT
|
|
(SELECT COUNT(*) FROM assets a
|
|
WHERE a.id NOT IN (SELECT id FROM demo_assets)
|
|
AND (a.parent_id IN (SELECT id FROM demo_assets)
|
|
OR a.operational_area_id IN (SELECT id FROM demo_assets)
|
|
OR a.operator_company_id IN (SELECT id FROM demo_assets)))
|
|
+ (SELECT COUNT(*) FROM area_company_relations r
|
|
WHERE (r.area_id IN (SELECT id FROM demo_assets)) <> (r.company_id IN (SELECT id FROM demo_assets)))
|
|
+ (SELECT COUNT(*) FROM inspection_visits v
|
|
WHERE v.id IN (SELECT id FROM demo_visits)
|
|
AND v.scope_asset_id IS NOT NULL
|
|
AND v.scope_asset_id NOT IN (SELECT id FROM demo_assets))
|
|
+ (SELECT COUNT(*) FROM inspection_visit_assets va
|
|
WHERE va.visit_id IN (SELECT id FROM demo_visits)
|
|
AND va.asset_id NOT IN (SELECT id FROM demo_assets))
|
|
+ (SELECT COUNT(*) FROM inspection_act_assets aa
|
|
WHERE aa.act_id IN (SELECT id FROM demo_acts)
|
|
AND aa.asset_id NOT IN (SELECT id FROM demo_assets))
|
|
+ (SELECT COUNT(*) FROM inspection_findings f
|
|
WHERE f.act_id IN (SELECT id FROM demo_acts)
|
|
AND f.asset_id NOT IN (SELECT id FROM demo_assets))
|
|
+ (SELECT COUNT(*) FROM inspection_findings f
|
|
WHERE f.asset_id IN (SELECT id FROM demo_assets)
|
|
AND f.act_id NOT IN (SELECT id FROM demo_acts))
|
|
+ (SELECT COUNT(*) FROM survey_campaigns c
|
|
WHERE c.id IN (SELECT id FROM demo_campaigns)
|
|
AND c.scope_asset_id IS NOT NULL
|
|
AND c.scope_asset_id NOT IN (SELECT id FROM demo_assets))
|
|
+ (SELECT COUNT(*) FROM survey_campaign_targets t
|
|
WHERE t.campaign_id IN (SELECT id FROM demo_campaigns)
|
|
AND t.asset_id NOT IN (SELECT id FROM demo_assets))
|
|
+ (SELECT COUNT(*) FROM organization_memberships m
|
|
WHERE (m.parent_organization_id IN (SELECT id FROM demo_assets)) <> (m.member_organization_id IN (SELECT id FROM demo_assets)))
|
|
+ (SELECT COUNT(*) FROM asset_source_documents l
|
|
WHERE l.document_id IN (SELECT id FROM demo_documents) AND l.asset_id NOT IN (SELECT id FROM demo_assets))
|
|
+ (SELECT COUNT(*) FROM area_legal_right_organizations o
|
|
JOIN area_legal_rights r ON r.id=o.right_id
|
|
WHERE (r.area_id IN (SELECT id FROM demo_assets)) <> (o.organization_id IN (SELECT id FROM demo_assets)));"
|
|
|
|
mixed="$(psql_owner "$MIXED_SQL")"
|
|
if [[ "$mixed" != "0" ]]; then
|
|
echo "ABORTADO: se detectaron $mixed vínculo(s) entre DEV-DEMO y datos no marcados como prueba."
|
|
echo "El limpiador no borrará registros ambiguos. Revisá esas relaciones manualmente."
|
|
exit 2
|
|
fi
|
|
|
|
if [[ "$APPLY" != true ]]; then
|
|
echo "MODO SIMULACIÓN: no se modificó nada."
|
|
echo "La limpieza real queda reservada para reiniciar el demo o preparar la futura entrega."
|
|
echo "Comando protegido:"
|
|
echo " bash scripts/dev-clean-demo.sh --apply DH-DEV-DEMO"
|
|
exit 0
|
|
fi
|
|
|
|
STAMP="$(date +%Y%m%d_%H%M%S)"
|
|
BACKUP="/root/DH_V2_BACKUPS/DEV_CLEAN_PRE_${STAMP}"
|
|
mkdir -p "$BACKUP"
|
|
echo "Creando backup obligatorio en $BACKUP ..."
|
|
docker compose exec -T "$DB_SERVICE" sh -lc \
|
|
'pg_dump -U "$POSTGRES_USER" -d "$POSTGRES_DB" -Fc' > "$BACKUP/database.dump"
|
|
|
|
ASSET_FILES="$BACKUP/asset-files.txt"
|
|
EVIDENCE_FILES="$BACKUP/evidence-files.txt"
|
|
SIGNATURE_FILES="$BACKUP/signature-files.txt"
|
|
|
|
psql_owner "$DEMO_CTE SELECT stored_name FROM asset_media WHERE asset_id IN (SELECT id FROM demo_assets);" > "$ASSET_FILES"
|
|
psql_owner "$DEMO_CTE
|
|
SELECT e.stored_name
|
|
FROM inspection_finding_evidence e
|
|
JOIN inspection_findings f ON f.id=e.finding_id
|
|
WHERE f.act_id IN (SELECT id FROM demo_acts) OR f.asset_id IN (SELECT id FROM demo_assets);" > "$EVIDENCE_FILES"
|
|
psql_owner "$DEMO_CTE
|
|
SELECT s.stored_name
|
|
FROM inspection_act_signatures s
|
|
WHERE s.act_id IN (SELECT id FROM demo_acts) AND s.stored_name IS NOT NULL;" > "$SIGNATURE_FILES"
|
|
|
|
cat > "$BACKUP/cleanup.sql" <<'SQL'
|
|
BEGIN;
|
|
-- Sólo el usuario dueño de la base ejecuta este script. Replica desactiva temporalmente
|
|
-- los triggers append-only para poder retirar datos de desarrollo conservando backup previo.
|
|
SET LOCAL session_replication_role = replica;
|
|
|
|
CREATE TEMP TABLE demo_assets(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_assets
|
|
SELECT id FROM assets
|
|
WHERE code LIKE 'DEV-DEMO-%' OR source_reference LIKE 'DH-DEV-DEMO:%';
|
|
|
|
CREATE TEMP TABLE demo_documents(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_documents SELECT id FROM source_documents WHERE external_reference LIKE 'DH-DEV-DEMO:%' OR document_number LIKE '%DH-DEV-DEMO%';
|
|
|
|
CREATE TEMP TABLE demo_visits(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_visits
|
|
SELECT DISTINCT v.id
|
|
FROM inspection_visits v
|
|
LEFT JOIN inspection_visit_assets va ON va.visit_id=v.id
|
|
WHERE v.scope_asset_id IN (SELECT id FROM demo_assets)
|
|
OR va.asset_id IN (SELECT id FROM demo_assets);
|
|
|
|
CREATE TEMP TABLE demo_acts(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_acts SELECT id FROM inspection_acts WHERE visit_id IN (SELECT id FROM demo_visits);
|
|
|
|
CREATE TEMP TABLE demo_findings(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_findings
|
|
SELECT id FROM inspection_findings
|
|
WHERE act_id IN (SELECT id FROM demo_acts) OR asset_id IN (SELECT id FROM demo_assets);
|
|
|
|
CREATE TEMP TABLE demo_evidence(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_evidence SELECT id FROM inspection_finding_evidence WHERE finding_id IN (SELECT id FROM demo_findings);
|
|
|
|
CREATE TEMP TABLE demo_communications(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_communications SELECT id FROM inspection_finding_communications WHERE finding_id IN (SELECT id FROM demo_findings);
|
|
|
|
CREATE TEMP TABLE demo_signatures(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_signatures SELECT id FROM inspection_act_signatures WHERE act_id IN (SELECT id FROM demo_acts);
|
|
|
|
CREATE TEMP TABLE demo_campaigns(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_campaigns
|
|
SELECT DISTINCT c.id
|
|
FROM survey_campaigns c
|
|
LEFT JOIN survey_campaign_targets t ON t.campaign_id=c.id
|
|
WHERE c.scope_asset_id IN (SELECT id FROM demo_assets)
|
|
OR t.asset_id IN (SELECT id FROM demo_assets);
|
|
|
|
CREATE TEMP TABLE demo_targets(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_targets SELECT id FROM survey_campaign_targets WHERE campaign_id IN (SELECT id FROM demo_campaigns);
|
|
|
|
CREATE TEMP TABLE demo_reports(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_reports SELECT id FROM survey_target_reports WHERE target_id IN (SELECT id FROM demo_targets);
|
|
|
|
CREATE TEMP TABLE demo_relations(id uuid PRIMARY KEY) ON COMMIT DROP;
|
|
INSERT INTO demo_relations
|
|
SELECT id FROM area_company_relations
|
|
WHERE area_id IN (SELECT id FROM demo_assets) AND company_id IN (SELECT id FROM demo_assets);
|
|
|
|
DELETE FROM survey_target_report_media WHERE report_id IN (SELECT id FROM demo_reports);
|
|
DELETE FROM survey_target_report_versions WHERE report_id IN (SELECT id FROM demo_reports);
|
|
DELETE FROM survey_target_reports WHERE id IN (SELECT id FROM demo_reports);
|
|
DELETE FROM survey_campaign_targets WHERE id IN (SELECT id FROM demo_targets);
|
|
DELETE FROM survey_campaigns WHERE id IN (SELECT id FROM demo_campaigns);
|
|
|
|
DELETE FROM inspection_finding_evidence WHERE id IN (SELECT id FROM demo_evidence);
|
|
DELETE FROM inspection_finding_communications WHERE id IN (SELECT id FROM demo_communications);
|
|
DELETE FROM inspection_finding_versions WHERE finding_id IN (SELECT id FROM demo_findings);
|
|
DELETE FROM inspection_findings WHERE id IN (SELECT id FROM demo_findings);
|
|
DELETE FROM inspection_act_signatures WHERE id IN (SELECT id FROM demo_signatures);
|
|
DELETE FROM inspection_act_closures WHERE act_id IN (SELECT id FROM demo_acts);
|
|
DELETE FROM inspection_act_responsibles WHERE act_id IN (SELECT id FROM demo_acts);
|
|
DELETE FROM inspection_act_versions WHERE act_id IN (SELECT id FROM demo_acts);
|
|
DELETE FROM inspection_act_assets WHERE act_id IN (SELECT id FROM demo_acts);
|
|
DELETE FROM inspection_acts WHERE id IN (SELECT id FROM demo_acts);
|
|
DELETE FROM inspection_visit_members WHERE visit_id IN (SELECT id FROM demo_visits);
|
|
DELETE FROM inspection_visit_assets WHERE visit_id IN (SELECT id FROM demo_visits);
|
|
DELETE FROM inspection_visits WHERE id IN (SELECT id FROM demo_visits);
|
|
|
|
DELETE FROM area_legal_right_organizations WHERE organization_id IN (SELECT id FROM demo_assets) OR right_id IN (SELECT id FROM area_legal_rights WHERE area_id IN (SELECT id FROM demo_assets));
|
|
DELETE FROM area_legal_rights WHERE area_id IN (SELECT id FROM demo_assets);
|
|
DELETE FROM organization_memberships WHERE parent_organization_id IN (SELECT id FROM demo_assets) OR member_organization_id IN (SELECT id FROM demo_assets);
|
|
DELETE FROM asset_external_identifiers WHERE asset_id IN (SELECT id FROM demo_assets);
|
|
DELETE FROM asset_source_documents WHERE asset_id IN (SELECT id FROM demo_assets) OR document_id IN (SELECT id FROM demo_documents);
|
|
DELETE FROM area_company_relations WHERE id IN (SELECT id FROM demo_relations);
|
|
DELETE FROM organization_profiles WHERE asset_id IN (SELECT id FROM demo_assets);
|
|
DELETE FROM asset_media WHERE asset_id IN (SELECT id FROM demo_assets);
|
|
DELETE FROM asset_geometries WHERE asset_id IN (SELECT id FROM demo_assets);
|
|
DELETE FROM asset_attribute_values WHERE asset_id IN (SELECT id FROM demo_assets);
|
|
DELETE FROM asset_versions WHERE asset_id IN (SELECT id FROM demo_assets);
|
|
|
|
DELETE FROM audit_events
|
|
WHERE request_id LIKE 'dev-seed-%'
|
|
OR entity_id IN (
|
|
SELECT id::text FROM demo_assets
|
|
UNION SELECT id::text FROM demo_visits
|
|
UNION SELECT id::text FROM demo_acts
|
|
UNION SELECT id::text FROM demo_findings
|
|
UNION SELECT id::text FROM demo_evidence
|
|
UNION SELECT id::text FROM demo_communications
|
|
UNION SELECT id::text FROM demo_signatures
|
|
UNION SELECT id::text FROM demo_campaigns
|
|
UNION SELECT id::text FROM demo_targets
|
|
UNION SELECT id::text FROM demo_reports
|
|
UNION SELECT id::text FROM demo_relations
|
|
UNION SELECT id::text FROM demo_documents
|
|
);
|
|
|
|
DELETE FROM assets WHERE id IN (SELECT id FROM demo_assets);
|
|
DELETE FROM source_documents d WHERE d.id IN (SELECT id FROM demo_documents)
|
|
AND NOT EXISTS (SELECT 1 FROM asset_source_documents l WHERE l.document_id=d.id)
|
|
AND NOT EXISTS (SELECT 1 FROM area_company_relations r WHERE r.source_document_id=d.id)
|
|
AND NOT EXISTS (SELECT 1 FROM organization_memberships m WHERE m.source_document_id=d.id)
|
|
AND NOT EXISTS (SELECT 1 FROM area_legal_rights r WHERE r.source_document_id=d.id)
|
|
AND NOT EXISTS (SELECT 1 FROM asset_external_identifiers i WHERE i.source_document_id=d.id);
|
|
|
|
-- La numeración de actas vuelve al máximo real remanente. Si no queda ninguna acta
|
|
-- de un año, se elimina la secuencia para que ese año pueda volver a comenzar en 1.
|
|
DELETE FROM document_annual_sequences s
|
|
WHERE s.document_type='ACT'
|
|
AND NOT EXISTS (SELECT 1 FROM inspection_acts a WHERE a.act_year=s.year);
|
|
UPDATE document_annual_sequences s
|
|
SET last_number=q.max_number, updated_at=CURRENT_TIMESTAMP
|
|
FROM (
|
|
SELECT act_year, MAX(act_number) AS max_number
|
|
FROM inspection_acts
|
|
GROUP BY act_year
|
|
) q
|
|
WHERE s.document_type='ACT' AND s.year=q.act_year;
|
|
|
|
COMMIT;
|
|
SQL
|
|
|
|
docker compose exec -T "$DB_SERVICE" sh -lc \
|
|
'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -v ON_ERROR_STOP=1' < "$BACKUP/cleanup.sql"
|
|
|
|
while IFS= read -r f; do [[ -z "$f" ]] || docker compose exec -T api rm -f -- "/app/storage/asset-media/$f"; done < "$ASSET_FILES"
|
|
while IFS= read -r f; do [[ -z "$f" ]] || docker compose exec -T api rm -f -- "/app/storage/asset-media/inspection-findings/$f"; done < "$EVIDENCE_FILES"
|
|
while IFS= read -r f; do [[ -z "$f" ]] || docker compose exec -T api rm -f -- "/app/storage/asset-media/inspection-signatures/$f"; done < "$SIGNATURE_FILES"
|
|
|
|
echo "Limpieza DEV-DEMO finalizada."
|
|
echo "Se conservaron tipos, atributos, jerarquías, usuarios, roles, permisos y catálogo de hallazgos."
|
|
echo "Backup previo: $BACKUP/database.dump"
|