database structure

[tevpg]

DAY - with summaries
- org_code
- site_code
- date
- load_time: timestamp of when bike data for this record was loaded or updated
- parked_bikes
- parked_regular
- parked_oversize
- remaining_bikes
- opening time
- closing time
- registrations
- most_full_regular
- most_full_regular_time
- most_full_oversize
- most_full_oversize_time
- most_full_bikes
- most_full_bikes_time
- (most_full* are optional since depend on visit info )
- precipitation
- max_temperature
- dusk

VISIT - attributes of a visit. There might be no visits info.
- (what is unique here? org+site+date+time_in+bike_id?)
- visit_id: pk
- load_time: timestamp of when this record loaded or updated
- org_code
- site_code
- date
- time_in
- time_out
- bike_id: string (mandatory - system-assigned if necessary)

BLOCK - information about activities in a half hour block of time; calculated at data load time
- (unique by org,site,date,time_start)
- blk_id: pk
- org_code
- site_code
- date
- time_start
- time_end(?)
- regular_at_start
- oversize_at_start
- oversize_at_end
- bikes_at_start
- bikes_at_end
- most_full
- most_full_time

LOAD_HISTORY
- fingerprints and load times for files of bike data info

ORG
- org_code (pk)
- org_name: str (opt)
- authz realms: list of realms (groups) that can view this data. Default=self, admin

ORG_SITE
- (unique by org, site)
- org_code
- site_code
- site_name (opt)
- weather station id
- lat/long (for sun)

AUTHZ_MAGIC - list of realms that can see every org's data.  Presumably this would just contain 'ADMIN'
- realm

[tevpg]

For sqlite3 use integer PK in table creation. Becomes an alias for unvarying row id

https://www.sqlite.org/lang_createtable.html#rowid

Use FKs to day.id in VISIT and BLOCK with DELETE CASCADE

[tevpg]

VISIT have time_in and duration (int minutes) in case goes past midnight