CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE SCHEMA IF NOT EXISTS market_data;

-- Full retained L2 book once per product per UTC hour (immutable after write).
CREATE TABLE IF NOT EXISTS market_data.orderbook_checkpoints (
    product_id TEXT NOT NULL,
    checkpoint_hour TIMESTAMPTZ NOT NULL,
    source_sequence_num BIGINT NOT NULL,
    best_bid NUMERIC(18, 8) NOT NULL,
    best_ask NUMERIC(18, 8) NOT NULL,
    bid_book JSONB NOT NULL,
    ask_book JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (product_id, checkpoint_hour)
);

SELECT create_hypertable(
    'market_data.orderbook_checkpoints',
    'checkpoint_hour',
    if_not_exists => TRUE
);

-- Full retained L2 book at every snapshot/resync second for replay baselines.
CREATE TABLE IF NOT EXISTS market_data.orderbook_replay_anchors (
    product_id TEXT NOT NULL,
    anchor_second TIMESTAMPTZ NOT NULL,
    source_sequence_num BIGINT NOT NULL,
    best_bid NUMERIC(18, 8) NOT NULL,
    best_ask NUMERIC(18, 8) NOT NULL,
    bid_book JSONB NOT NULL,
    ask_book JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (product_id, anchor_second)
);

SELECT create_hypertable(
    'market_data.orderbook_replay_anchors',
    'anchor_second',
    if_not_exists => TRUE
);

-- One row per (product, UTC second) where end-of-second book changed vs last persisted second.
CREATE TABLE IF NOT EXISTS market_data.orderbook_second_deltas (
    product_id TEXT NOT NULL,
    changed_second TIMESTAMPTZ NOT NULL,
    source_sequence_num_start BIGINT NOT NULL,
    source_sequence_num_end BIGINT NOT NULL,
    best_bid NUMERIC(18, 8) NOT NULL,
    best_ask NUMERIC(18, 8) NOT NULL,
    changes JSONB NOT NULL,
    change_count INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (product_id, changed_second)
);

SELECT create_hypertable(
    'market_data.orderbook_second_deltas',
    'changed_second',
    if_not_exists => TRUE
);

-- Operational replay continuity / gap metadata (not source of truth for book state).
CREATE TABLE IF NOT EXISTS market_data.orderbook_replay_metadata (
    product_id TEXT NOT NULL,
    window_start TIMESTAMPTZ NOT NULL,
    window_end TIMESTAMPTZ NOT NULL,
    checkpoint_hour TIMESTAMPTZ,
    source_sequence_num_start BIGINT,
    source_sequence_num_end BIGINT,
    status TEXT NOT NULL,
    gap_count INTEGER NOT NULL DEFAULT 0,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (product_id, window_start, window_end)
);

CREATE TABLE IF NOT EXISTS market_data.bucket_definitions (
    bucket_name TEXT NOT NULL,
    version TEXT NOT NULL,
    definition JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (bucket_name, version)
);

CREATE TABLE IF NOT EXISTS market_data.artifact_manifests (
    artifact_id UUID PRIMARY KEY,
    artifact_kind TEXT NOT NULL,
    contract_version TEXT NOT NULL,
    storage_snapshot_contract_version TEXT NOT NULL,
    tensor_contract_version TEXT NOT NULL,
    normalization_policy_version TEXT NOT NULL,
    source_window TSRANGE NOT NULL,
    artifact_path TEXT NOT NULL,
    metadata JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
