-- Table: api_costs
-- Description: Tracks API costs across Genesis

CREATE TABLE IF NOT EXISTS api_costs (
    id SERIAL PRIMARY KEY,
    provider VARCHAR(255) NOT NULL,
    model VARCHAR(255) NOT NULL,
    tokens_in INTEGER NOT NULL,
    tokens_out INTEGER NOT NULL,
    cost_usd NUMERIC(10, 6) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Index for provider queries
CREATE INDEX IF NOT EXISTS idx_api_costs_provider ON api_costs (provider);

-- Index for model queries
CREATE INDEX IF NOT EXISTS idx_api_costs_model ON api_costs (model);

-- Index for timestamp range queries
CREATE INDEX IF NOT EXISTS idx_api_costs_timestamp ON api_costs (timestamp);


-- Table: aiva_fixed_costs
-- Description: Tracks AIVA fixed cost allocation

CREATE TABLE IF NOT EXISTS aiva_fixed_costs (
    id SERIAL PRIMARY KEY,
    cost_usd NUMERIC(10, 6) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Function to automatically insert AIVA fixed cost daily
-- Calculate the daily cost based on the monthly cost ($119) divided by 30 days
-- INSERT INTO aiva_fixed_costs (cost_usd) VALUES (119.00/30);

-- Consider using a scheduler (e.g., cron) to run this daily.  This is just a basic example.
```

```python