80 lines
2.8 KiB
Python
80 lines
2.8 KiB
Python
"""Repair stale reconciliation_items for active document links.
|
|
|
|
Dry-run by default. Only exact document identity is used, and ambiguous items
|
|
matching active links in more than one opportunity are deliberately skipped.
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import argparse
|
|
import sys
|
|
from pathlib import Path
|
|
|
|
PROJECT_ROOT = Path(__file__).resolve().parents[1]
|
|
if str(PROJECT_ROOT) not in sys.path:
|
|
sys.path.insert(0, str(PROJECT_ROOT))
|
|
|
|
from sqlalchemy import text
|
|
|
|
from app.db import engine
|
|
|
|
|
|
MATCHES_CTE = """
|
|
WITH exact_matches AS (
|
|
SELECT ri.id AS reconciliation_item_id, odl.opportunity_id
|
|
FROM reconciliation_items ri
|
|
JOIN commercial_documents cd
|
|
ON cd.system = ri.source_system
|
|
AND (
|
|
(NULLIF(ri.document_number, '') IS NOT NULL AND cd.document_number = ri.document_number)
|
|
OR (NULLIF(ri.external_id, '') IS NOT NULL AND cd.external_id = ri.external_id)
|
|
)
|
|
JOIN opportunity_document_links odl
|
|
ON odl.document_id = cd.id
|
|
AND odl.ended_at IS NULL
|
|
AND odl.relationship IN ('PRIMARY','SECONDARY')
|
|
WHERE ri.status NOT IN ('ignored','historical')
|
|
), repairable AS (
|
|
SELECT reconciliation_item_id,
|
|
(array_agg(DISTINCT opportunity_id))[1] AS opportunity_id
|
|
FROM exact_matches
|
|
GROUP BY reconciliation_item_id
|
|
HAVING COUNT(DISTINCT opportunity_id) = 1
|
|
)
|
|
"""
|
|
|
|
|
|
def repair(*, apply: bool = False) -> dict[str, int]:
|
|
with engine.begin() as conn:
|
|
candidates = int(conn.execute(text(MATCHES_CTE + """
|
|
SELECT COUNT(*) FROM repairable r
|
|
JOIN reconciliation_items ri ON ri.id = r.reconciliation_item_id
|
|
WHERE ri.status <> 'linked'
|
|
OR ri.opportunity_id IS DISTINCT FROM r.opportunity_id
|
|
OR ri.resolved_at IS NULL
|
|
""")).scalar() or 0)
|
|
updated = 0
|
|
if apply and candidates:
|
|
updated = int(conn.execute(text(MATCHES_CTE + """
|
|
UPDATE reconciliation_items ri
|
|
SET status='linked', opportunity_id=r.opportunity_id,
|
|
resolved_at=COALESCE(ri.resolved_at, now()), updated_at=now()
|
|
FROM repairable r
|
|
WHERE ri.id=r.reconciliation_item_id
|
|
AND (ri.status <> 'linked'
|
|
OR ri.opportunity_id IS DISTINCT FROM r.opportunity_id
|
|
OR ri.resolved_at IS NULL)
|
|
""")).rowcount or 0)
|
|
return {"candidates": candidates, "updated": updated}
|
|
|
|
|
|
def main() -> None:
|
|
parser = argparse.ArgumentParser()
|
|
parser.add_argument("--apply", action="store_true", help="Apply exact, unambiguous repairs")
|
|
args = parser.parse_args()
|
|
result = repair(apply=args.apply)
|
|
print(f"candidates={result['candidates']} updated={result['updated']} apply={args.apply}")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|