SafeVixAI/SafeVixAI-Dataset-Hub

1from __future__ import annotations2 3import argparse4import asyncio5import csv6import hashlib7import sys8from pathlib import Path9 10 11ROOT_DIR = Path(__file__).resolve().parents[1]12BACKEND_DIR = ROOT_DIR / 'backend'13 14if str(BACKEND_DIR) not in sys.path:15    sys.path.insert(0, str(BACKEND_DIR))16 17from geoalchemy2.elements import WKTElement18from sqlalchemy.dialects.postgresql import insert19 20from core.database import AsyncSessionLocal21from models.emergency import EmergencyService22 23 24DEFAULT_CSV = ROOT_DIR / 'chatbot_service' / 'data' / 'hospitals' / 'hospital_directory.csv'25STATE_CODES = {26    'andaman and nicobar islands': 'AN',27    'andhra pradesh': 'AP',28    'arunachal pradesh': 'AR',29    'assam': 'AS',30    'bihar': 'BR',31    'chandigarh': 'CH',32    'chhattisgarh': 'CG',33    'dadra and nagar haveli and daman and diu': 'DN',34    'delhi': 'DL',35    'goa': 'GA',36    'gujarat': 'GJ',37    'haryana': 'HR',38    'himachal pradesh': 'HP',39    'jammu and kashmir': 'JK',40    'jharkhand': 'JH',41    'karnataka': 'KA',42    'kerala': 'KL',43    'ladakh': 'LA',44    'lakshadweep': 'LD',45    'madhya pradesh': 'MP',46    'maharashtra': 'MH',47    'manipur': 'MN',48    'meghalaya': 'ML',49    'mizoram': 'MZ',50    'nagaland': 'NL',51    'odisha': 'OD',52    'puducherry': 'PY',53    'punjab': 'PB',54    'rajasthan': 'RJ',55    'sikkim': 'SK',56    'tamil nadu': 'TN',57    'telangana': 'TS',58    'tripura': 'TR',59    'uttar pradesh': 'UP',60    'uttarakhand': 'UK',61    'west bengal': 'WB',62}63 64 65def _first_value(row: dict[str, str], names: tuple[str, ...]) -> str:66    for name in names:67        value = (row.get(name) or '').strip()68        if value:69            return value70    return ''71 72 73def _parse_float(value: str) -> float | None:74    try:75        return float(value)76    except (TypeError, ValueError):77        return None78 79 80def _stable_osm_id(name: str, lat: float, lon: float, city: str, state: str) -> int:81    key = f'{name}|{lat:.6f}|{lon:.6f}|{city}|{state}'82    digest = hashlib.blake2b(key.encode('utf-8'), digest_size=8).hexdigest()83    return int(digest, 16) % 9_000_000_000_000_000_00084 85 86def _load_rows(csv_path: Path, *, category: str, source: str) -> list[dict]:87    rows: list[dict] = []88    with csv_path.open('r', encoding='utf-8', newline='') as handle:89        reader = csv.DictReader(handle)90        for raw in reader:91            name = _first_value(raw, ('name', 'hospital_name'))92            lat = _parse_float(_first_value(raw, ('lat', 'latitude')))93            lon = _parse_float(_first_value(raw, ('lon', 'longitude', 'lng')))94            if not name or lat is None or lon is None:95                continue96 97            city = _first_value(raw, ('city', 'district'))98            state = _first_value(raw, ('state', 'province'))99            sub_category = _first_value(raw, ('type', 'category', 'sub_category'))100            address = _first_value(raw, ('address',))101            state_code = STATE_CODES.get(state.lower())102            osm_id_raw = _first_value(raw, ('osm_id', 'id'))103            osm_id = int(osm_id_raw) if osm_id_raw.isdigit() else _stable_osm_id(name, lat, lon, city, state)104 105            tag_blob = ' '.join(part for part in [name, sub_category, address] if part).lower()106            rows.append(107                {108                    'osm_id': osm_id,109                    'osm_type': _first_value(raw, ('osm_type',)) or 'csv_import',110                    'name': name,111                    'category': category,112                    'sub_category': sub_category or None,113                    'address': address or None,114                    'phone': _first_value(raw, ('phone', 'contact_phone')) or None,115                    'phone_emergency': _first_value(raw, ('phone_emergency', 'emergency_phone')) or None,116                    'location': WKTElement(f'POINT({lon} {lat})', srid=4326),117                    'city': city or None,118                    'district': _first_value(raw, ('district',)) or city or None,119                    'state': state or None,120                    'state_code': state_code,121                    'country_code': 'IN',122                    'is_24hr': '24' in _first_value(raw, ('opening_hours', 'hours')),123                    'has_trauma': 'trauma' in tag_blob,124                    'has_icu': 'icu' in tag_blob or 'intensive care' in tag_blob,125                    'source': source,126                    'raw_tags': {key: value for key, value in raw.items() if value},127                    'verified': True,128                }129            )130    return rows131 132 133async def _seed_rows(rows: list[dict]) -> int:134    if not rows:135        return 0136 137    async with AsyncSessionLocal() as session:138        stmt = insert(EmergencyService).values(rows)139        upsert = stmt.on_conflict_do_update(140            index_elements=['osm_id'],141            set_={142                'name': stmt.excluded.name,143                'category': stmt.excluded.category,144                'sub_category': stmt.excluded.sub_category,145                'address': stmt.excluded.address,146                'phone': stmt.excluded.phone,147                'phone_emergency': stmt.excluded.phone_emergency,148                'location': stmt.excluded.location,149                'city': stmt.excluded.city,150                'district': stmt.excluded.district,151                'state': stmt.excluded.state,152                'state_code': stmt.excluded.state_code,153                'is_24hr': stmt.excluded.is_24hr,154                'has_trauma': stmt.excluded.has_trauma,155                'has_icu': stmt.excluded.has_icu,156                'source': stmt.excluded.source,157                'raw_tags': stmt.excluded.raw_tags,158                'verified': stmt.excluded.verified,159            },160        )161        await session.execute(upsert)162        await session.commit()163    return len(rows)164 165 166async def _async_main(args: argparse.Namespace) -> None:167    csv_path = args.csv.resolve()168    if not csv_path.exists():169        raise SystemExit(f'CSV not found: {csv_path}')170 171    rows = _load_rows(csv_path, category=args.category, source=args.source)172    seeded = await _seed_rows(rows)173    print(f'Seeded {seeded} emergency service rows from {csv_path}')174 175 176def main() -> None:177    parser = argparse.ArgumentParser(description='Seed hospital CSV data into emergency_services.')178    parser.add_argument('--csv', type=Path, default=DEFAULT_CSV, help=f'CSV input path. Defaults to {DEFAULT_CSV}')179    parser.add_argument('--category', default='hospital', help='Category to use for inserted rows. Defaults to hospital.')180    parser.add_argument('--source', default='nhp_osm', help='Source label to store in PostgreSQL. Defaults to nhp_osm.')181    args = parser.parse_args()182    asyncio.run(_async_main(args))183 184 185if __name__ == '__main__':186    main()187