CREATE TABLE IF NOT EXISTS instruments (
    id TEXT PRIMARY KEY,
    market TEXT NOT NULL,
    venue TEXT NOT NULL,
    symbol TEXT NOT NULL,
    name TEXT NOT NULL,
    currency TEXT NOT NULL,
    provider_symbols JSONB NOT NULL
);

CREATE TABLE IF NOT EXISTS bars (
    instrument_id TEXT NOT NULL REFERENCES instruments(id) ON DELETE CASCADE,
    timeframe TEXT NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    open NUMERIC NOT NULL,
    high NUMERIC NOT NULL,
    low NUMERIC NOT NULL,
    close NUMERIC NOT NULL,
    volume NUMERIC NOT NULL,
    PRIMARY KEY (instrument_id, timeframe, timestamp)
);

CREATE TABLE IF NOT EXISTS backtest_runs (
    id TEXT PRIMARY KEY,
    strategy_id TEXT NOT NULL,
    market TEXT NOT NULL,
    timeframe TEXT NOT NULL,
    from_time TIMESTAMPTZ NOT NULL,
    to_time TIMESTAMPTZ NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE IF NOT EXISTS backtest_results (
    run_id TEXT PRIMARY KEY REFERENCES backtest_runs(id) ON DELETE CASCADE,
    starting_cash_currency TEXT NOT NULL,
    starting_cash_amount NUMERIC NOT NULL,
    ending_equity_currency TEXT NOT NULL,
    ending_equity_amount NUMERIC NOT NULL,
    trades JSONB NOT NULL,
    positions JSONB NOT NULL
);
