-- Equity tracker schema
-- SQLite does not control types so directly; but it makes it more portable to a different SQL server with specificity

-- enum as table - there may be other equity types, does not affect views directly
create table equity_type
(
    equity_type_name VARCHAR(20) PRIMARY KEY NOT NULL
);

INSERT INTO equity_type (equity_type_name) VALUES
    ('STOCK'),
    ('ETF'),
    ('CRYPTO');

-- deletion of equity_type does not cascade - equity_type is required for equity_symbol, which is required for equity_change_event, which is required for fiduciary_account.
-- equity_symbol, equity_type would never be deleted, but they could be cleaned up if they are orphaned records.
create table equity_symbol
(
    equity_symbol_id                SMALLINT PRIMARY KEY NOT NULL,
    equity_symbol_name              VARCHAR(16) NOT NULL,
    equity_symbol_type              VARCHAR(20) NOT NULL
                                    REFERENCES equity_type(equity_type_name),
    equity_symbol_managing_company  VARCHAR(96) NOT NULL,
    equity_symbol_created_timestamp TIMESTAMP NOT NULL
);

create table fiduciary_account
(
    fiduciary_account_id                TINYINT PRIMARY KEY NOT NULL,
    fiduciary_account_number            UNSIGNED BIG INT NOT NULL,
    fiduciary_account_name              NVARCHAR(96) NOT NULL,
    fiduciary_account_description       NVARCHAR(512),
    fiduciary_account_created_timestamp TIMESTAMP NOT NULL
);

-- deletion of fiduciary_account row cascades - an account precludes change events.
-- deletion of equity_symbol row does not cascade - all events should be recorded for an account, and all events must have symbols
create table equity_change_event
(
    equity_change_event_id       UNSIGNED BIG INT PRIMARY KEY NOT NULL,
    fiduciary_account_id         TINYINT NOT NULL
                                 REFERENCES fiduciary_account(fiduciary_account_id)
                                 ON DELETE CASCADE,
    equity_symbol_id             SMALLINT NOT NULL
                                 REFERENCES equity_symbol(equity_symbol_id),
    equity_change_quantity       DECIMAL(20, 8) NOT NULL,
    equity_change_cost_basis_usd DECIMAL(14, 2) NOT NULL,
    equity_change_type           VARCHAR(20) NOT NULL
                                 CHECK (equity_change_type IN ('BUY', 'SELL')), -- This is essentially '+' or '-'
    equity_change_timestamp      TIMESTAMP NOT NULL
);

--TODO construct views on event collation? heavy calculation requirements....