PRAGMA foreign_keys = ON;

CREATE TABLE accounts(
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    opening_balance INTEGER NOT NULL,
    opening_date TEXT NOT NULL
);

CREATE TABLE account_transfers(
    id INTEGER NOT NULL PRIMARY KEY,
    amount INTEGER NOT NULL,
    date TEXT NOT NULL,
    description TEXT NOT NULL,
    from_account_id INTEGER NOT NULL,
    to_account_id INTEGER NOT NULL,
    FOREIGN KEY (from_account_id)
        REFERENCES accounts (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    FOREIGN KEY (to_account_id)
        REFERENCES accounts (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE buckets(
    id INTEGER NOT NULL PRIMARY KEY,
    balance INTEGER,
    balance_cache_key TEXT,
    bucket_group TEXT NOT NULL,
    name TEXT NOT NULL
);

CREATE TABLE bucket_transactions(
    id INTEGER NOT NULL PRIMARY KEY,
    amount INTEGER NOT NULL,
    bucket_id INTEGER NOT NULL,
    date TEXT NOT NULL,
    description TEXT NOT NULL,
    FOREIGN KEY (bucket_id)
        REFERENCES buckets (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE drips(
    id INTEGER NOT NULL PRIMARY KEY,
    amount INTEGER NOT NULL,
    bucket_id INTEGER NOT NULL,
    date TEXT NOT NULL,
    inserted_at_unix_seconds INTEGER NOT NULL,
    pipe_id INTEGER NOT NULL,
    FOREIGN KEY (bucket_id)
        REFERENCES buckets (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    UNIQUE(bucket_id, date)
);

CREATE TABLE pipes(
    id INTEGER NOT NULL PRIMARY KEY,
    amount INTEGER NOT NULL,
    bucket_id INTEGER NOT NULL,
    period_length INTEGER NOT NULL,
    period_unit TEXT NOT NULL,
    start_date TEXT NOT NULL,
    FOREIGN KEY (bucket_id)
        REFERENCES buckets (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE transactions(
    id INTEGER NOT NULL PRIMARY KEY,
    account_id INTEGER NOT NULL,
    amount INTEGER NOT NULL,
    bucket_id INTEGER,
    counterparty TEXT NOT NULL,
    date TEXT NOT NULL,
    description TEXT NOT NULL,
    FOREIGN KEY (account_id)
        REFERENCES accounts (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);