### Install Dependencies and Initialize TypeScript for Viewer

Source: https://github.com/holychikenz/mwiapi/blob/main/readme.md

Installs necessary development and runtime dependencies for the viewer, including Webpack, TypeScript, and sql.js-httpvfs. It also initializes the TypeScript configuration.

```sh
echo '{}' > package.json
npm install --save-dev webpack webpack-cli typescript ts-loader
npm install --save sql.js-httpvfs
npx tsc --init
```

--------------------------------

### SQL Schema Example: Ask and Bid Tables

Source: https://context7.com/holychikenz/mwiapi/llms.txt

SQL schema definitions for 'ask' and 'bid' tables in a SQLite database. These tables use Unix timestamps as primary keys and have dynamic columns for each item, storing their respective prices. Query examples demonstrate retrieving data by time and calculating price margins.

```sql
-- 'ask' and 'bid' tables structure
CREATE TABLE ask (
  time INTEGER PRIMARY KEY,
  "Holy Milk" INTEGER,
  "Holy Cheese" INTEGER,
  "Abyssal Essence" INTEGER,
  -- ... columns for each item (200+ items)
);

-- Query examples:
SELECT DATETIME(time, 'unixepoch') AS date, "Holy Milk"
FROM ask
WHERE time > 1750000000
ORDER BY time DESC
LIMIT 100;

-- Calculate price margins
SELECT time,
  "Holy Cheese",
  "Holy Milk",
  ("Holy Cheese" - "Holy Milk" * 2) AS margin
FROM ask;

```

--------------------------------

### JSON Example: Current Market Data Format

Source: https://context7.com/holychikenz/mwiapi/llms.txt

An example JSON object representing current market data. It includes a 'market' object where keys are item names and values are objects containing 'ask', 'bid', and 'vendor' prices. A 'time' field indicates the timestamp of the data snapshot.

```json
{
  "market": {
    "Abyssal Essence": {
      "ask": 280,
      "bid": 275,
      "vendor": 108
    },
    "Holy Milk": {
      "ask": 280,
      "bid": 275,
      "vendor": 50
    },
    "Holy Cheese": {
      "ask": 560,
      "bid": 550,
      "vendor": 100
    }
  },
  "time": 1753218902
}

```

--------------------------------

### JSON Example: Median Market Data Format

Source: https://context7.com/holychikenz/mwiapi/llms.txt

An example JSON object representing median market data over a 24-hour rolling period. It contains a 'time' field and a 'market' object detailing 'ask' and 'bid' prices for various items, excluding vendor data.

```json
{
  "time": 1753218902.0,
  "market": {
    "Amber": {
      "ask": 22000.0,
      "bid": 21500.0
    },
    "Apple": {
      "ask": 28.0,
      "bid": 25.0
    }
  }
}

```

--------------------------------

### Query Historical Market Data using Pandas and SQLite in Python

Source: https://context7.com/holychikenz/mwiapi/llms.txt

This Python function queries an SQLite database to retrieve historical market data and returns it as a pandas DataFrame. It demonstrates how to connect to the database, execute a custom SQL query, and close the connection. The example shows fetching the last 24 hours of 'Holy Milk' ask prices and converting Unix timestamps to readable dates.

```python
import sqlite3
import pandas as pd
import time

def query(q_string, fname='market.db'):
    """Query the sqlite database and return a pandas dataframe"""
    conn = sqlite3.connect(fname)
    data = pd.read_sql_query(q_string, conn)
    conn.close()
    return data

# Get last 24 hours of Holy Milk prices
target = time.time() - 24*3600
data = query(f'SELECT time, "Holy Milk" FROM ask WHERE time > {target}')

# Convert unix timestamps to readable dates
data['date'] = pd.to_datetime(data['time'], unit='s')

print(data.head())
# Output:
#         time  Holy Milk                 date
# 0  1753195200        278 2025-07-22 20:00:00
# 1  1753198800        280 2025-07-22 21:00:00
# 2  1753202400        282 2025-07-22 22:00:00

```

--------------------------------

### Build Viewer with Webpack

Source: https://github.com/holychikenz/mwiapi/blob/main/readme.md

Compiles the viewer's assets into a production-ready bundle using Webpack in production mode.

```sh
./node_modules/.bin/webpack --mode=production
```

--------------------------------

### Configure TypeScript for Viewer Build

Source: https://github.com/holychikenz/mwiapi/blob/main/readme.md

Sets the target ECMAScript version, module system, and module resolution strategy in the tsconfig.json file for the viewer's TypeScript compilation.

```json
{
  "target": "es2020",
  "module": "es2020",
  "moduleResolution": "node"
}
```

--------------------------------

### Python Script for Creating SQLite Database Indexes

Source: https://context7.com/holychikenz/mwiapi/llms.txt

A Python script that connects to a SQLite database and creates composite indexes on price columns for specified tables. This script iterates through table columns, identifies price-related columns (excluding 'time'), and constructs a `CREATE INDEX` statement to optimize query performance. It ensures that queries filtering by item prices and time ranges are faster.

```python
import sqlite3

filename = 'market.db'
connection = sqlite3.connect(filename)
cursor = connection.cursor()

def fix_table(name):
    """Create composite index on all price columns"""
    cursor.execute(f"PRAGMA table_info({name});")
    columns = cursor.fetchall()

    # Get all columns except 'time'
    price_columns = [f'"{col[1]}"' for col in columns if col[1] != 'time']

    # Create index on all price columns plus time
    index_query = f"CREATE INDEX idx_price_history_{name} ON {name} ({', '.join(price_columns)}, time);"
    print(f"Creating index: {index_query}")

    cursor.execute(index_query)
    connection.commit()

fix_table('ask')
fix_table('bid')
connection.close()

# Speeds up queries filtering by item prices and time ranges

```

--------------------------------

### JavaScript Class for Browser SQL Querying and Plotting

Source: https://context7.com/holychikenz/mwiapi/llms.txt

A JavaScript class that enables SQL querying directly within a web browser using sql.js-httpvfs and visualizes the results using Plotly. It sets up UI elements for query input and output display, handles asynchronous data fetching, and formats the data for plotting. Dependencies include sql.js-httpvfs and Plotly.js.

```javascript
class mooket {
  constructor(database) {
    this.database = database;
    this.setupUI();
  }

  setupUI() {
    let self = this;
    self.queryBtn = document.getElementById("datago");
    self.queryString = document.getElementById("query");
    self.queryBtn.addEventListener("click", x => self.plotify(self));
  }

  async plotify(self) {
    self.queryString = document.getElementById("query");
    const result = self.database.db.exec(self.queryString.value);
    result.then(data => {self.doplot(self, data[0])});
  }

  doplot(self, data) {
    // Convert query results to Plotly traces
    let traces = [];
    let traceDict = {};
    let labels = data.columns;

    for(let index = 0; index < labels.length; index++) {
      traceDict[labels[index]] = [];
      for(let num = 0; num < data.values.length; num++) {
        traceDict[labels[index]].push(data.values[num][index]);
      }
    }

    // Create trace for each column except 'time'
    for(const [key, value] of Object.entries(traceDict)) {
      if(key == 'time') continue;
      let trace = {
        name: key,
        x: traceDict['time'],
        y: value,
        type: 'scatter'
      };
      traces.push(trace);
    }

    let layout = {
      plot_bgcolor: "rgb(66 66 66 / 66%)",
      paper_bgcolor: "rgb(66 66 66 / 66%)",
      font: {color: "#fff"},
      showlegend: true,
    };

    Plotly.newPlot('plot', traces, layout, {responsive: true});
  }
}

// Usage in HTML:
// Load database and create viewer
import { load } from "./dist/sql-httpvfs.js";
window.loadDB = load;

window.onload = function() {
  async function startup() {
    Promise.all([window.loadDB("https://holychikenz.github.io/MWIApi/market.db")])
      .then(data => {
        let viewer = new mooket(data[0]);
      });
  }
  startup();
}

// Example SQL query in textarea:
// SELECT DATETIME(time,"unixepoch") AS time,
// "Holy Cheese", "Holy Milk",
// ("Holy Cheese" - "Holy Milk"*2) AS "Margin"
// FROM ask
// WHERE time > 1750000000

```

--------------------------------

### Generate Median Market Prices and Export to JSON in Python

Source: https://context7.com/holychikenz/mwiapi/llms.txt

This Python script calculates 24-hour median prices for all items from the 'ask' and 'bid' tables in an SQLite database and exports the results to a JSON file. It includes helper functions to read data from the database, compute medians using pandas, and format the data for the API. The output JSON contains the timestamp and market data with ask and bid prices for each item.

```python
#!/usr/bin/env python3
import time
import sqlite3
import pandas as pd
import json

def readDBGetMedian(hours, table):
    """Get median prices over specified hours"""
    target = time.time() - hours*3600
    con = sqlite3.connect("file:market.db?mode=ro", uri=True)
    tab = pd.read_sql_query(f'SELECT * FROM {table} WHERE time > {target}', con)
    con.close()
    return tab.median()

def joinConvert(ask, bid):
    """Convert median data to API format"""
    Dictionary = {}
    Dictionary["time"] = ask['time']
    Dictionary["market"] = {}
    ask = ask.drop(['time'])
    bid = bid.drop(['time'])
    for key in ask.keys():
        Dictionary["market"][key] = {"ask": ask[key], "bid": bid[key]}
    return Dictionary

# Calculate 24-hour medians
askdf = readDBGetMedian(24, "ask")
biddf = readDBGetMedian(24, "bid")
market = joinConvert(askdf, biddf)

# Write to JSON
with open("medianmarket.json", "w") as j:
    json.dump(market, j, indent=2)

# Output format:
# {
#   "time": 1753218902.0,
#   "market": {
#     "Holy Milk": {"ask": 280.0, "bid": 275.0},
#     "Holy Cheese": {"ask": 560.0, "bid": 550.0}
#   }
# }

```

--------------------------------

### Python Database Maintenance Workflow

Source: https://context7.com/holychikenz/mwiapi/llms.txt

Executes a comprehensive database maintenance workflow that merges live data with archives, creates a backup super database, re-archives data monthly, and generates a 6-month rolling view. It utilizes pandas for data manipulation and sqlite3 for database operations. Assumes existence of helper functions like read_directory, archive_database, and reconstitute.

```python
import pandas as pd
import sqlite3

def standard_operating_procedure():
    """
    Complete database maintenance workflow:
    1. Merge live data with archives
    2. Create backup super database
    3. Re-archive everything by month
    4. Generate 6-month rolling view
    """
    print('@@@ Running standard operating procedure')

    # Load archived data
    archive_ask, archive_bid = read_directory('archive')

    # Load live data
    live_con = sqlite3.connect('market.db')
    live_ask = pd.read_sql_query('SELECT * FROM ask', live_con)
    live_bid = pd.read_sql_query('SELECT * FROM bid', live_con)
    live_con.close()

    # Merge and deduplicate
    full_ask = (pd.concat([archive_ask, live_ask])
                .drop_duplicates(subset='time')
                .sort_values('time')
                .reset_index(drop=True))
    full_bid = (pd.concat([archive_bid, live_bid])
                .drop_duplicates(subset='time')
                .sort_values('time')
                .reset_index(drop=True))

    print(f'@@@ Loaded {len(full_ask)} asks and {len(full_bid)} bids')

    # Backup to super database
    super_con = sqlite3.connect('market_super.db')
    full_ask.to_sql('ask', super_con, index=False, if_exists='replace')
    full_bid.to_sql('bid', super_con, index=False, if_exists='replace')
    super_con.close()
    print('@@@ Backed up super database to market_super.db')

    # Re-archive by month
    archive_database('ask', 'market_super.db')
    archive_database('bid', 'market_super.db')
    print('@@@ Archived super database')

    # Create rolling 6-month view
    reconstitute('archive', 'market_view.db', 6)
    print('@@@ Created view of last 6 months')

# Execute maintenance
standard_operating_procedure()

```

--------------------------------

### Archive Database by Month using Python

Source: https://context7.com/holychikenz/mwiapi/llms.txt

This function archives data from a specified SQLite table into separate monthly database files. It loads data, converts timestamps, groups by month, and writes each month's data to a new SQLite file in an 'archive' directory. Dependencies include pandas and sqlite3.

```python
import sqlite3
import pandas as pd
import os

def query(q_string, fname='market.db'):
    conn = sqlite3.connect(fname)
    data = pd.read_sql_query(q_string, conn)
    conn.close()
    return data

def write_pandas_to_sqlite(df, fname, table_name='ask'):
    conn = sqlite3.connect(fname)
    df.to_sql(table_name, conn, index=False, if_exists='replace')
    conn.close()

def archive_database(table='ask', database='market.db'):
    """Archive database by splitting into monthly files"""
    print(f'Archiving {table} in {database}')

    # Load data with datetime conversion
    data = query(f"""
        SELECT *, datetime(time, 'unixepoch') as datestring
        FROM {table}
    """, database)

    data.datestring = pd.to_datetime(data.datestring)
    data['month_year'] = data.datestring.dt.to_period('M')

    # Group by month and save to separate files
    for name, group in data.groupby('month_year'):
        output_database = f'archive/market_{name}.db'
        output_dataframe = (group
            .drop(columns=['datestring', 'month_year', 'index'], errors='ignore')
            .reset_index(drop=True)
        )
        write_pandas_to_sqlite(output_dataframe, output_database, table)
        print(f'Wrote {name}/{table} to {output_database}')

# Archive both ask and bid tables
archive_database('ask', 'market.db')
archive_database('bid', 'market.db')

# Creates: archive/market_2025-07.db, archive/market_2025-08.db, etc.
```

--------------------------------

### Store Market Data with MWIDatabase Class in Python

Source: https://context7.com/holychikenz/mwiapi/llms.txt

This Python script utilizes the MWIDatabase class to store current market ask and bid prices into an SQLite database. It automatically manages the database schema and adds columns for new items as needed. The script reads data from a JSON file, extracts prices and timestamps, and appends them to the 'ask' and 'bid' tables.

```python
#!/usr/bin/env python3
from MWIDatabase import MWIDatabase
import json

# Initialize database connection
db = MWIDatabase("market.db")

# Load current market data from JSON
with open('milkyapi.json') as j:
    data = json.load(j)
    time = int(data['time'])
    market = data['market']

    # Extract ask and bid prices for all items
    askRow = {k: v['ask'] for (k, v) in market.items()}
    bidRow = {k: v['bid'] for (k, v) in market.items()}

    # Append to database (creates tables/columns automatically)
    db.appendRow('ask', time, askRow)
    db.appendRow('bid', time, bidRow)

db.close()

# Example output:
# Creates/updates 'ask' and 'bid' tables with columns for each item
# Time: 1753218902, Holy Milk: 280, Holy Cheese: 560, etc.

```

--------------------------------

### Reconstitute Database from Archives using Python

Source: https://context7.com/holychikenz/mwiapi/llms.txt

This function rebuilds a primary database file from monthly archive files located in a specified directory. It can optionally filter data to include only recent months. The process involves reading all archive databases, concatenating their data, deduplicating based on timestamps, and writing the result to a new SQLite file. Dependencies include pandas, sqlite3, os, and time.

```python
import pandas as pd
import sqlite3
import os
import time

def read_directory(directory):
    """Read all archived databases in a directory"""
    asks = []
    bids = []
    for file in os.listdir(directory):
        if file.endswith('.db'):
            con = sqlite3.connect(f'{directory}/{file}')
            ask = pd.read_sql_query('SELECT * FROM ask', con)
            bid = pd.read_sql_query('SELECT * FROM bid', con)
            con.close()
            asks.append(ask)
            bids.append(bid)

    if len(asks) == 0:
        return None, None

    asks = pd.concat(asks, ignore_index=True)
    bids = pd.concat(bids, ignore_index=True)
    asks = asks.sort_values('time').drop_duplicates(subset='time').reset_index(drop=True)
    bids = bids.sort_values('time').drop_duplicates(subset='time').reset_index(drop=True)
    return asks, bids

def reconstitute(archive_directory, output_database='market_view.db', limit=6):
    """Reconstitute database from archives, optionally limiting to recent months"""
    asks, bids = read_directory(archive_directory)

    if limit:
        limit_in_seconds = limit * 30 * 24 * 60 * 60
        now = time.time()
        asks = asks[asks.time > now - limit_in_seconds]
        bids = bids[bids.time > now - limit_in_seconds]

    # Write to new database
    conn = sqlite3.connect(output_database)
    asks.to_sql('ask', conn, index=False, if_exists='replace')
    bids.to_sql('bid', conn, index=False, if_exists='replace')
    conn.close()
    print(f'Wrote {output_database} with last {limit} months')

# Reconstitute last 6 months of data
reconstitute('archive', 'market_view.db', limit=6)
# Output: market_view.db with deduplicated, sorted data
```

--------------------------------

### Python Price History Plotting with Matplotlib

Source: https://context7.com/holychikenz/mwiapi/llms.txt

Visualizes the price history of 'Holy Milk' from a SQLite database using Matplotlib. It fetches data, filters outliers based on a threshold (5 times the median price), and then plots the filtered data. The plot is saved as a PNG file. Requires libraries: matplotlib, sqlite3, numpy.

```python
import matplotlib.pyplot as plt
import sqlite3
import numpy as np

def plot_holy_milk():
    """Plot Holy Milk price history with outlier filtering"""
    conn = sqlite3.connect('market.db')
    cursor = conn.cursor()

    cursor.execute('SELECT time, "Holy Milk" FROM ask ORDER BY time')
    data = cursor.fetchall()
    conn.close()

    if not data:
        print("No data found.")
        return

    times, holy_milk = zip(*data)
    times = np.array(times)
    holy_milk = np.array(holy_milk)

    # Filter outliers (5x median)
    median = np.median(holy_milk)
    threshold = median * 5
    times = times[holy_milk < threshold]
    holy_milk = holy_milk[holy_milk < threshold]

    # Plot
    plt.figure(figsize=(10, 5))
    plt.plot(times, holy_milk, marker='o', linestyle='-', color='blue')
    plt.title('Holy Milk Prices Over Time')
    plt.xlabel('Time (Unix Timestamp)')
    plt.ylabel('Holy Milk Price')
    plt.xticks(rotation=45)
    plt.grid()
    plt.tight_layout()
    plt.savefig('holy_milk_prices.png')
    plt.show()

plot_holy_milk()
# Saves plot to holy_milk_prices.png

```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.