-- Fantasy42 Raw Feed Database Schema
-- Stores unprocessed Fantasy42 live betting feed data for raw analysis

-- Raw live feed data table (stores complete unparsed Fantasy42 API responses)
CREATE TABLE IF NOT EXISTS raw_live_feed (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL, -- Unix timestamp
    cache_key TEXT,

    -- Raw Fantasy42 API response (full JSON payload)
    raw_payload TEXT NOT NULL,

    -- Metadata for processing and analysis
    data_type TEXT DEFAULT 'live_ticker', -- live_ticker, bet_details, etc.
    api_endpoint TEXT, -- Which Fantasy42 endpoint this came from
    response_size INTEGER, -- Size of raw payload in bytes

    -- Processing status
    processing_status TEXT DEFAULT 'received', -- received, parsed, failed
    processing_attempts INTEGER DEFAULT 0,
    last_processed_at INTEGER,

    -- Hash for deduplication
    payload_hash TEXT,

    created_at INTEGER DEFAULT (strftime('%s','now') * 1000),
    updated_at INTEGER DEFAULT (strftime('%s','now') * 1000)
);

-- Parsed bet data table (structured data extracted from raw feed)
CREATE TABLE IF NOT EXISTS parsed_bets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    raw_feed_id INTEGER, -- Foreign key to raw_live_feed
    agent_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL,

    -- Core bet identifiers
    bet_id TEXT, -- Fantasy42's internal bet identifier
    customer_id TEXT,
    wager_number TEXT,

    -- ShortDesc derived fields (parsed from Fantasy42's ShortDesc format)
    short_desc_raw TEXT, -- Original ShortDesc string before parsing
    sport_type TEXT,
    bet_type TEXT, -- SPREAD, MONEY_LINE, TOTAL, etc.
    period TEXT, -- GAME, 1ST_HALF, etc.
    team_info TEXT, -- Combined home/away or game info
    line_info TEXT, -- Point spread, moneyline, total points

    -- Numerical data
    amount REAL,
    odds REAL,

    -- Status information
    bet_status TEXT, -- ACTIVE, PENDING, SETTLED, etc.
    graded_status TEXT, -- WIN, LOSS, PUSH, etc.

    -- Additional parsed metadata
    league TEXT,
    game_datetime INTEGER,
    ticket_writer TEXT,

    -- Processing metadata
    parsed_at INTEGER,
    parse_version TEXT DEFAULT '1.0',
    confidence_score REAL DEFAULT 1.0, -- 0.0-1.0 parsing confidence

    created_at INTEGER DEFAULT (strftime('%s','now') * 1000),
    updated_at INTEGER DEFAULT (strftime('%s','now') * 1000),

    FOREIGN KEY (raw_feed_id) REFERENCES raw_live_feed(id)
);

-- Feed metadata and statistics
CREATE TABLE IF NOT EXISTS feed_metadata (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    feed_type TEXT NOT NULL, -- live_ticker, game_data, etc.
    timestamp INTEGER NOT NULL,

    -- Feed statistics
    total_bets INTEGER DEFAULT 0,
    active_bets INTEGER DEFAULT 0,
    recent_bets INTEGER DEFAULT 0, -- Last X minutes

    -- Data quality metrics
    parse_success_rate REAL DEFAULT 0.0, -- 0.0-1.0
    avg_parsing_time REAL DEFAULT 0.0, -- milliseconds
    error_count INTEGER DEFAULT 0,

    -- Feed health indicators
    data_freshness INTEGER, -- Age of newest data in seconds
    feed_frequency REAL DEFAULT 0.0, -- Updates per minute

    metadata TEXT, -- Additional JSON metadata

    created_at INTEGER DEFAULT (strftime('%s','now') * 1000)
);

-- Feed processing errors and issues
CREATE TABLE IF NOT EXISTS feed_processing_errors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    raw_feed_id INTEGER,

    error_type TEXT NOT NULL, -- parse_error, validation_error, schema_error
    error_message TEXT NOT NULL,
    error_details TEXT, -- JSON details about the error

    -- Context
    payload_snippet TEXT, -- Partial payload that caused error
    processing_step TEXT, -- Which parsing step failed

    -- Retry handling
    retry_count INTEGER DEFAULT 0,
    resolved INTEGER DEFAULT 0, -- 1 if manually resolved

    created_at INTEGER DEFAULT (strftime('%s','now') * 1000)
);

-- Raw data archives (for historical data retention)
CREATE TABLE IF NOT EXISTS raw_feed_archive (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    original_id INTEGER NOT NULL, -- ID from raw_live_feed before archiving
    agent_id TEXT NOT NULL,
    archive_date INTEGER NOT NULL, -- Date when archived (YYYYMMDD format)

    -- Archived raw payload
    raw_payload TEXT NOT NULL,
    compressed_payload BLOB, -- Optionally compressed version

    -- Archive metadata
    retention_period INTEGER, -- Days to keep archived data
    archive_reason TEXT, -- manual, automatic, cleanup

    compressed INTEGER DEFAULT 0, -- 1 if payload is compressed
    compressed_size INTEGER,

    created_at INTEGER DEFAULT (strftime('%s','now') * 1000)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_raw_live_feed_agent_timestamp ON raw_live_feed(agent_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_raw_live_feed_status ON raw_live_feed(processing_status);
CREATE INDEX IF NOT EXISTS idx_raw_live_feed_hash ON raw_live_feed(payload_hash);

CREATE INDEX IF NOT EXISTS idx_parsed_bets_raw_id ON parsed_bets(raw_feed_id);
CREATE INDEX IF NOT EXISTS idx_parsed_bets_agent_timestamp ON parsed_bets(agent_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_parsed_bets_customer ON parsed_bets(customer_id);
CREATE INDEX IF NOT EXISTS idx_parsed_bets_status ON parsed_bets(bet_status);

CREATE INDEX IF NOT EXISTS idx_feed_metadata_agent_type ON feed_metadata(agent_id, feed_type);
CREATE INDEX IF NOT EXISTS idx_feed_processing_errors_agent_timestamp ON feed_processing_errors(agent_id, timestamp);

-- Unique constraints
CREATE UNIQUE INDEX IF NOT EXISTS idx_raw_live_feed_unique ON raw_live_feed(agent_id, payload_hash, timestamp);
CREATE UNIQUE INDEX IF NOT EXISTS idx_parsed_bets_unique ON parsed_bets(agent_id, bet_id) WHERE bet_id IS NOT NULL;
