lockserver/db/migrations/1_init.sql

-- +goose Up
-- +goose StatementBegin
PRAGMA foreign_keys = ON;

CREATE TABLE locks (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    zwave_device_id INTEGER NOT NULL UNIQUE
);

CREATE TABLE lock_code_slots (
    id INTEGER PRIMARY KEY,
    lock INTEGER NOT NULL REFERENCES locks(id),
    code TEXT NOT NULL,
    slot INTEGER NOT NULL,
    name TEXT NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT 0,

    UNIQUE (lock, slot)
);

CREATE TABLE lock_log (
    lock INTEGER NOT NULL REFERENCES locks(id),
    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    state TEXT NOT NULL,
    code INTEGER REFERENCES lock_code_slots(id),
    issued_code INTEGER REFERENCES issued_codes(id)
);

CREATE TABLE issued_codes (
    id INTEGER PRIMARY KEY,
    name TEXT,
    code TEXT UNIQUE NOT NULL,
    start DATETIME,
    end DATETIME
);

CREATE TABLE issued_code_slots (
    issued_code REFERENCES issued_codes(id),
    lock REFERENCES locks(id),
    slot REFERENCES lock_code_slots(id),

    UNIQUE (issued_code, lock)
);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE issued_code_slots;
DROP TABLE issued_codes;
DROP TABLE lock_log;
DROP TABLE lock_code_slots;
DROP TABLE locks;
-- +goose StatementEnd