================================================================================
TairuDB SQLite Database Schema
================================================================================
Document version: 2.0   (this FILE's revision — NOT a value stored in any .tairudb)
Format: SQLite3
Description: Geographic database format for the Tairu Maps mobile application

Format generations
--------------------------------------------------------------------------------
A .tairudb file is one of two things:

  MAP        - cartography: raster tiles and/or read-only vector layers. This is
               what the QGIS plugin produces. Tables: metadata, vector_layers,
               features, regions, tiles_region_N.

  PACKAGE    - a transport for expedition data: Records with their groups and
               photos, sent from one app user to another (or to another
               expedition). Written by the app, never by the plugin. Adds the
               records / record_groups / photos tables and stamps
               metadata.package = '1'.

Readers older than generation 2 do not know the package tables, so a package
stamps metadata.min_reader_version = '2' and they refuse the file instead of
importing it as empty.

HOW A READER TELLS THEM APART — and what it does NOT look at:

  min_reader_version   "can I read this at all?"  Absent = yes, any version.
  package == '1'       "is this expedition data instead of a map?"
  map_uuid             "is this a newer version of a map I already have?"

  metadata.version     NOTHING GATES ON THIS. The plugin writes "1.2"; the app
                       parses it and writes it back, and no decision anywhere
                       depends on its value. Do not raise it expecting an effect,
                       and do not add a gate to it — the two keys above are the
                       contract.

This is why the plugin needs no change for generation 2: it only ever produces
MAP files, which carry neither key, so every app version keeps reading them
exactly as before. The package tables are additive and readers probe
sqlite_master before touching them.
================================================================================

TABLE: metadata
--------------------------------------------------------------------------------
Stores configuration and database metadata as key-value pairs.

CREATE TABLE IF NOT EXISTS metadata (
    name TEXT,
    value TEXT
);

Common metadata keys:
  - format       : Tile image format (png, jpg, webp)
  - name         : Database name
  - description  : Database description
  - version      : Legacy free-form stamp (plugin writes "1.2"). Informational
                   only — no reader gates on it. See "Format generations".
  - type         : Database type (e.g., "overlay")
  - minzoom      : Minimum zoom level (integer as string)
  - maxzoom      : Maximum zoom level (integer as string)
  - center       : Center point as "longitude,latitude,zoom"
  - generator    : Tool that created the database
  - created      : ISO format timestamp
  - min_reader_version : Minimum reader generation required (integer as string).
                   Absent = readable by every version. A reader whose own
                   generation is lower MUST refuse the file rather than render a
                   partial/blank map.
  - map_uuid     : Stable identity of the MAP, minted once and reused on every
                   re-export (kept as a QGIS project property, so it survives
                   closing QGIS and travels with the project). A reader uses it
                   to tell "newer version of a map I already have" from "new
                   map". NEVER use the file name for that: messengers rewrite it
                   ("mapa (1).tairudb") and two unrelated maps can share one.
                   Absent on files made before this existed — those fall back to
                   name matching.
  - package      : '1' marks a record PACKAGE (see "Format generations"). Absent
                   or any other value = an ordinary map.
  - package_source_map  : (package) mapId the records were exported from
  - package_app_version : (package) app version that wrote the file

IMPORTANT: `name` and `format` are REQUIRED and must be TEXT. A file missing
either is classified invalid rather than "needs a newer reader", and the app
DELETES invalid files — a package written without them is destroyed, not
refused. Do not add new values to `type`: unknown values raise on read; omit the
key when neither "baselayer" nor "overlay" applies.


TABLE: vector_layers
--------------------------------------------------------------------------------
Defines vector layers with unique identifiers.

CREATE TABLE IF NOT EXISTS vector_layers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT UNIQUE NOT NULL,
    type TEXT,
    name TEXT,
    description TEXT
);

Fields:
  - id          : Auto-incrementing primary key
  - uuid        : Unique identifier (UUID v4 format)
  - type        : Layer geometry type ("point", "line", "polygon")
  - name        : Layer display name
  - description : Layer description (optional)


TABLE: features
--------------------------------------------------------------------------------
Stores individual vector features with geometry, styling, and attributes.

CREATE TABLE IF NOT EXISTS features (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT UNIQUE NOT NULL,
    layer_id TEXT,
    type TEXT,
    name TEXT,
    attributes TEXT,
    color TEXT,
    size INTEGER,
    iconType TEXT,
    points TEXT,
    style TEXT,
    wkb BLOB,
    FOREIGN KEY(layer_id) REFERENCES vector_layers(uuid)
);

Fields:
  - id         : Auto-incrementing primary key
  - uuid       : Unique identifier (UUID v4 format)
  - layer_id   : References vector_layers.uuid (parent layer)
  - type       : Feature geometry type ("point", "line", "polygon")
  - name       : Feature display name
  - attributes : JSON object string of additional user attributes
  - color      : Hex color code, alpha-first "#AARRGGBB" (e.g. "#FFFF0000" opaque
                 red); "#RRGGBB" without alpha is also accepted (treated opaque)
  - size       : Size in pixels (icon size or line width)
  - iconType   : Icon type identifier (e.g., "locationOn", "line", "polygon")
  - points     : Coordinate string in WKT-style format (exterior ring only)
  - style      : Optional styleJson string (polygon fill / dash pattern / label
                 config the color & size columns can't carry); NULL for a plain
                 feature. See SYMBOLOGY_PLAN.md / RecordStyle for the shape.
  - wkb        : Optional OGC WKB (WGS84, 2D) of the geometry, written ONLY for
                 polygons with holes (interior rings) or multipart structure that
                 the flat `points` text can't express; NULL otherwise. Holes/parts
                 render from this; `points` carries the exterior for old clients.

Points format examples:
  - Single point    : "lon lat"
  - Line/polygon    : "lon1 lat1, lon2 lat2, lon3 lat3"
  - Multi-geometry  : "lon1 lat1, lon2 lat2; lon3 lat3, lon4 lat4"
                      (semicolon separates multiple geometries)


TABLE: regions
--------------------------------------------------------------------------------
Defines spatial regions with zoom levels and geographic bounds.

CREATE TABLE IF NOT EXISTS regions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT UNIQUE NOT NULL,
    name TEXT,
    minzoom INTEGER,
    maxzoom INTEGER,
    bounds TEXT
);

Fields:
  - id      : Region identifier, 1-based (AUTOINCREMENT: 1, 2, 3, ...).
              NOTE: the tiles_region_{N} tables below are 0-based ({N} = the
              region's insertion order, 0, 1, 2, ...), so tiles_region_0
              belongs to regions.id = 1, tiles_region_1 to id = 2, etc.
  - uuid    : Unique identifier (UUID v4 format)
  - name    : Region display name
  - minzoom : Minimum zoom level for this region
  - maxzoom : Maximum zoom level for this region
  - bounds  : WKT-style bounding box coordinates

Bounds format:
  "minLon minLat, maxLon minLat, maxLon maxLat, minLon maxLat"
  Example: "-46.5 -23.7, -46.4 -23.7, -46.4 -23.6, -46.5 -23.6"


TABLES: tiles_region_{N} (dynamic, one per region)
--------------------------------------------------------------------------------
Stores raster tiles in TMS (Tile Map Service) format. One table is created for
each region defined in the regions table, with {N} being the region's 0-based
insertion order (so tiles_region_0 pairs with regions.id = 1; see the regions
table note above).

CREATE TABLE IF NOT EXISTS tiles_region_0 (
    zoom_level INTEGER, 
    tile_column INTEGER, 
    tile_row INTEGER, 
    tile_data BLOB
);

CREATE UNIQUE INDEX IF NOT EXISTS tiles_region_0_index 
    ON tiles_region_0 (zoom_level, tile_column, tile_row);

Fields:
  - zoom_level  : Tile pyramid zoom level
  - tile_column : Tile X coordinate (TMS standard)
  - tile_row    : Tile Y coordinate (TMS standard, Y-flipped from XYZ)
  - tile_data   : Binary image data (PNG, JPG, or WebP format)

Index:
  - Unique composite index on (zoom_level, tile_column, tile_row) for fast lookup

Tile coordinate system:
  - Uses TMS (Tile Map Service) coordinates
  - Origin (0,0) is at bottom-left (southwest corner)
  - Y-axis increases northward (opposite of XYZ/Google Maps convention)
  - Convert from XYZ to TMS: tms_y = (2^zoom - 1) - xyz_y


TABLE: elevation_tiles (optional)
--------------------------------------------------------------------------------
Terrain elevation for the mapped area, so the app can show the altitude of a
point and the elevation profile of a line with no internet. Written by the
plugin when "Incluir dados de altitude do terreno" is on (default).

CREATE TABLE IF NOT EXISTS elevation_tiles (
    zoom_level INTEGER,
    tile_x     INTEGER,
    tile_y     INTEGER,
    tile_data  BLOB
);

CREATE UNIQUE INDEX IF NOT EXISTS elevation_tiles_index
    ON elevation_tiles (zoom_level, tile_x, tile_y);

READ THIS BEFORE TOUCHING IT — three things differ from tiles_region_{N}:

  1. XYZ, NOT TMS. tile_x/tile_y are slippy-map indices with a TOP-LEFT origin.
     The columns are deliberately NOT named tile_column/tile_row, because those
     names carry the TMS convention everywhere else in this file. A missed flip
     does not fail: it returns the altitude of a different latitude, and the
     number looks perfectly reasonable.

  2. NOT A REGION, on purpose. Every row of `regions` becomes a raster layer the
     app draws. tile_data here is a Terrarium-encoded PNG — false colour, not
     imagery — so drawn on a map it is a screenful of pink noise. Its own table
     is also invisible to older readers, which enumerate `regions` and never
     probe for this one: no min_reader_version bump is needed and no existing
     app version is affected.

  3. Fixed zoom 12. The source grid is ~30 m; a deeper zoom would only resample
     the same numbers and a shallower one would throw away resolution the
     profile needs. The app looks tiles up at that exact zoom — there is no
     pyramid fallback here, unlike the raster tiles.

tile_data: 256x256 PNG, Terrarium encoding (AWS Open Data "Terrain Tiles").
           elevation_metres = (R * 256 + G + B / 256) - 32768
           The high byte is in R, so the fraction in B is optional precision, not
           optional data — a reader that ignores B is off by under a metre.

Source and licence: SRTM / GMTED2010 / 3DEP, all U.S. Geological Survey public
domain, which is WHY it may be here at all — a .tairudb is redistributed (shared
between users, uploaded to expedition storage), unlike a device-local tile cache,
so it may only carry data that may be passed on. The required courtesy notice is
written to metadata.elevation_attribution.

metadata key:
  - elevation_attribution : USGS courtesy notice; present iff tiles were stored.


================================================================================
Schema Relationships
================================================================================

vector_layers (1) ──────< (N) features
    uuid                       layer_id

regions (1) ──────< (1) tiles_region_{N}
    id                   implicit (N in table name)

elevation_tiles
    (standalone, no foreign keys — NOT tied to a region; see its note above)

metadata
    (standalone key-value store, no foreign keys)


================================================================================
Database Usage Notes
================================================================================

1. Coordinate System:
   - All geographic coordinates use WGS84 (EPSG:4326)
   - Longitude, Latitude order (lon, lat)
   - Tile rendering uses Web Mercator (EPSG:3857)

2. Tile Storage:
   - Each region can have its own tile table
   - Tiles are stored with TMS coordinates
   - Empty/transparent tiles may be omitted to save space
   - Tile format specified in metadata.format

3. Vector Features:
   - Features are organized into layers via layer_id
   - Geometry is stored as text coordinate strings (`points`); polygons with holes
     or multipart geometry ADDITIONALLY carry OGC WKB (`wkb` BLOB) for the structure
     the flat text can't express
   - Attributes stored as JSON for flexibility
   - Optional per-feature structured styling lives in the `style` column (styleJson)
   - Colors use alpha-first hex format "#AARRGGBB" with # prefix

4. Region Management:
   - Region ID 0 is typically the default/main region
   - Multiple regions support layered map overlays
   - Each region has independent zoom levels and bounds

5. Database Operations:
   - Use INSERT OR REPLACE for tile updates
   - Use INSERT OR IGNORE for layer deduplication
   - Perform VACUUM after bulk operations
   - Use transactions for batch inserts


================================================================================
PACKAGE TABLES (generation 2, app-written)
================================================================================
Present only when metadata.package = '1'. Absent from every plugin-produced map;
readers must probe sqlite_master before querying them.

TABLE: records
--------------------------------------------------------------------------------
CREATE TABLE records (
    uuid TEXT UNIQUE NOT NULL,
    json TEXT NOT NULL,
    local_only INTEGER NOT NULL DEFAULT 0
);

Fields:
  - uuid       : the Record's own id, preserved from the source expedition, so a
                 re-import merges instead of duplicating
  - json       : the Record serialized exactly as it is written to the cloud
                 (Firestore representation). Timestamps are epoch millis and the
                 WKB geometry blob is base64 — plain JSON cannot hold either.
  - local_only : 1 when the record is deliberately device-local (its geometry is
                 too large for the cloud). Rides in its own column because the
                 cloud representation drops the flag.

TABLE: record_groups
--------------------------------------------------------------------------------
CREATE TABLE record_groups (
    uuid TEXT UNIQUE NOT NULL,
    json TEXT NOT NULL
);

The folders referenced by the exported records, so the sender's organisation
survives the transfer.

TABLE: photos
--------------------------------------------------------------------------------
CREATE TABLE photos (
    id TEXT UNIQUE NOT NULL,
    record_uuid TEXT NOT NULL,
    bytes BLOB NOT NULL
);

JPEG bytes under the same photo ids the record references, so no remapping is
needed on import. Read ONE ROW AT A TIME: a package can weigh hundreds of MB and
the app's load pass runs on every map open.

A package also creates the standard MAP tables, empty, so a generation-2 reader
sees a familiar shape. It creates NO regions row and no tiles_region_N table.


================================================================================
Example Queries
================================================================================

-- Get all metadata
SELECT * FROM metadata;

-- Get tile count per zoom level for region 0
SELECT zoom_level, COUNT(*) as count 
FROM tiles_region_0 
GROUP BY zoom_level;

-- Get all features for a specific layer
SELECT f.* 
FROM features f
JOIN vector_layers l ON f.layer_id = l.uuid
WHERE l.name = 'Points of Interest';

-- Get database bounds (regions.id is 1-based)
SELECT bounds FROM regions WHERE id = 1;

-- Is this file a record package?
SELECT value FROM metadata WHERE name = 'package';

-- Package tables exist? (never assume — plugin-made maps have none)
SELECT name FROM sqlite_master WHERE type='table' AND name = 'records';

-- Get tile data for specific coordinate
SELECT tile_data 
FROM tiles_region_0 
WHERE zoom_level = 18 
  AND tile_column = 12345 
  AND tile_row = 67890;


================================================================================
End of Schema Documentation
================================================================================
