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

CREATE TABLE "user"
(
    id           INTEGER PRIMARY KEY NOT NULL,
    username     TEXT                NOT NULL UNIQUE,
    password     TEXT                NOT NULL,
    name         TEXT                NOT NULL,
    email        TEXT                NOT NULL UNIQUE,
    auth_token   TEXT                NOT NULL,
    authorized   INTEGER             NOT NULL,
    admin        INTEGER             NOT NULL,
    organization INTEGER             NOT NULL,
    FOREIGN KEY (organization) REFERENCES organization (id) ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE heartbeat
(
    id         INTEGER PRIMARY KEY NOT NULL,
    "user"     INTEGER             NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip_addr    TEXT                NOT NULL,
    auth_token TEXT                NOT NULL,
    FOREIGN KEY ("user") REFERENCES "user" (id) ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE organization
(
    id          INTEGER PRIMARY KEY NOT NULL,
    name        TEXT                NOT NULL UNIQUE,
    owner_name  TEXT                NOT NULL,
    owner_phone TEXT                NOT NULL,
    owner_email TEXT                NOT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    deleted_at  DATETIME,
    authorized  INTEGER             NOT NULL,
    auth_token  TEXT                NOT NULL
);

CREATE INDEX IF NOT EXISTS user_username ON "user" (username);
CREATE INDEX IF NOT EXISTS user_email ON "user" (email);
CREATE INDEX IF NOT EXISTS heartbeats_user ON heartbeat ("user");
CREATE INDEX IF NOT EXISTS organizations_name ON organization (name);
CREATE INDEX IF NOT EXISTS organizations_owner_phone ON organization (owner_phone);
CREATE INDEX IF NOT EXISTS organizations_owner_email ON organization (owner_email);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
-- +goose StatementEnd