Metadata-Version: 2.4
Name: ev-charging-status
Version: 0.1.0
Summary: Poll EV charging APIs and write station status events to TimescaleDB/PostgreSQL.
Author: guocity
License-Expression: MIT
Project-URL: Homepage, https://github.com/guocity/ev_charging_status
Project-URL: Repository, https://github.com/guocity/ev_charging_status
Keywords: ev,charging,timescaledb,postgresql,polling
Classifier: Programming Language :: Python :: 3
Classifier: Operating System :: OS Independent
Classifier: Topic :: Utilities
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: psycopg[binary]<4,>=3.1
Requires-Dist: python-dotenv<2,>=1.0
Requires-Dist: requests<3,>=2.31
Dynamic: license-file

# EV Charging Status

Python app that polls Shell Recharge/Greenlots and SEMA/Blink charger APIs and writes station status events to TimescaleDB/PostgreSQL.

## Setup

```bash
uv sync
cp .env.example .env
```

Put real DB credentials and API tokens in `.env`. `.env` is ignored by git.

Run commands through `uv run`, for example:

```bash
uv run ev-status --help
```

## Database

Initialize schema and seed default sites/statuses/stations:

```bash
uv run ev-status init-db
```

Only rows with `sites.enabled = true` are polled.

## Run

First run, or after recreating/changing the database schema, initialize the database once:

```bash
uv run ev-status init-db
```

Routine one-time poll:

```bash
uv run ev-status poll
```

You can combine initialization and polling for a safe first run:

```bash
uv run ev-status poll --init-db
```

`--init-db` is not required for every poll. It only ensures the database, tables, indexes, default sites, default statuses, default stations, and TimescaleDB setup exist before polling.

Continuous 5-minute loop (or `POLL_INTERVAL_SECONDS`):

```bash
uv run ev-status run-loop
```

For a safe first continuous run:

```bash
uv run ev-status run-loop --init-db
```

Sync historical rows from the public Google Sheet CSV into `station_status_events`:

```bash
uv run ev-status sync-sheet
```

Set `SYNC_SHEET_ID` in `.env`. By default the command reads `Sheet1` using:

```text
https://docs.google.com/spreadsheets/d/<SYNC_SHEET_ID>/gviz/tq?tqx=out:csv&sheet=Sheet1
```

The sheet must include columns like `current_time`, `station_name`, and `status`. The sync command inserts only rows that do not already exist with the same `(time, station_id, status)`. Rows for station IDs not present in the `stations` table are ignored.

You can override the tab or full CSV URL:

```bash
uv run ev-status sync-sheet --sheet-name Sheet1
uv run ev-status sync-sheet --url 'https://docs.google.com/spreadsheets/d/{SHEETID}/gviz/tq?tqx=out:csv&sheet=Sheet1'
```

Optimize `station_status_events` storage:

```bash
uv run ev-status optimize-db
```

This rounds existing event timestamps to second precision, drops the redundant `(time DESC, station_id)` index, and compresses TimescaleDB chunks older than 7 days. Timestamp precision is also second-level for new polls.

You can change the compression cutoff:

```bash
uv run ev-status optimize-db --compress-older-than '1 day'
```

Show total database size:

```bash
uv run ev-status db-size
# include table/relation sizes
uv run ev-status db-size --tables
```

## Tables

- `sites`: site enable/disable control
- `status_lookup`: status IDs; new normalized statuses are inserted automatically
- `stations`: station metadata inserted once on first sight
- `station_status_events`: Timescale hypertable with exactly `(time, station_id, status)`
- `site_latest_snapshot`: latest raw JSONB API response per site
