-- SPDX-License-Identifier: AGPL-3.0-or-later
-- SPDX-FileCopyrightText: 2025 Wojtek Porczyk <woju@hackerspace.pl>

-- NOTE: this file gets executed on every bot load, so every single statement
-- needs to be idempotent

CREATE TABLE IF NOT EXISTS kv (
    key         TEXT PRIMARY KEY,
    value       ANY
) STRICT;

CREATE TABLE IF NOT EXISTS parcels (
    room        TEXT,
    event_orig  TEXT,
    event_qr    TEXT,
    operator    TEXT,
    id          TEXT,
    code        TEXT,
    phone       TEXT,
    ref         TEXT,
    time_start  TEXT,
    time_end    TEXT
) STRICT;

CREATE TABLE IF NOT EXISTS parcel_points (
    operator    TEXT,
    ref         TEXT,
    lat         REAL NOT NULL,
    lon         REAL NUT NULL,

    PRIMARY KEY (operator, ref)
) STRICT;

CREATE TABLE IF NOT EXISTS phone_numbers (
    user_id     TEXT PRIMARY KEY,
    phone       TEXT
) STRICT;

INSERT OR IGNORE INTO kv (key, value) VALUES ("version", 1);
