DROP SCHEMA wesabe CASCADE;
CREATE SCHEMA wesabe;
SET SEARCH_PATH = wesabe;

CREATE TABLE account (
    account_id integer primary key,
    name text NOT NULL,
    institution text,
    "type" text,
    balance double precision,
    last_uploaded date
);

CREATE TABLE merchant (
    merchant_id integer primary key,
    name text NOT NULL
);

CREATE TABLE tag (
    tag_id serial primary key NOT NULL,
    tag text NOT NULL
);
CREATE UNIQUE INDEX tag_tag ON tag USING btree (tag);

CREATE TABLE tx (
    tx_id serial primary key,
    guid text NOT NULL,
    date date NOT NULL,
    amount double precision NOT NULL,
    account_id integer references account(account_id),
    raw_name text,
    raw_type text,
    memo text,
    merchant_id integer references merchant(merchant_id),
    check_number integer
);
CREATE UNIQUE INDEX tx_guid ON tx USING btree (guid);

CREATE TABLE tx_tag (
    tx_id integer references tx(tx_id),
    tag_id integer references tag(tag_id)
);
CREATE INDEX tx_tag_tag ON tx_tag USING btree (tag_id, tx_id);
CREATE UNIQUE INDEX tx_tag_tx ON tx_tag USING btree (tx_id, tag_id);