data/setup_db.py

import sqlite3

conn = sqlite3.connect("gresham_demo.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS portfolio_companies (
    id INTEGER PRIMARY KEY,
    name TEXT,
    sector TEXT,
    investment_date TEXT,
    investment_value_gbp INTEGER,
    status TEXT
)
""")

portfolio_data = [
    (1, "OnSecurity", "Cybersecurity", "2024-03-15", 5500000, "Active"),
    (2, "Accredit Solutions", "Security Automation", "2024-06-20", 10000000, "Active"),
    (3, "GreenTech Energy", "Renewable Energy", "2023-11-10", 8000000, "Active"),
    (4, "PropTech Ventures", "Real Estate Tech", "2024-01-05", 6500000, "Active"),
    (5, "HealthAI", "Healthcare AI", "2024-09-12", 12000000, "Active"),
]

cursor.executemany("INSERT OR REPLACE INTO portfolio_companies VALUES (?, ?, ?, ?, ?, ?)", portfolio_data)

cursor.execute("""
CREATE TABLE IF NOT EXISTS investment_performance (
    id INTEGER PRIMARY KEY,
    company_id INTEGER,
    quarter TEXT,
    revenue_growth_pct REAL,
    valuation_gbp INTEGER
)
""")

performance_data = [
    (1, 1, "Q4-2025", 45.2, 25000000),
    (2, 1, "Q3-2025", 38.5, 22000000),
    (3, 2, "Q4-2025", 52.1, 45000000),
    (4, 2, "Q3-2025", 48.7, 40000000),
    (5, 3, "Q4-2025", 28.3, 15000000),
]

cursor.executemany("INSERT OR REPLACE INTO investment_performance VALUES (?, ?, ?, ?, ?)", performance_data)

conn.commit()
conn.close()
print("Database created!")