import sqlite3
import pandas as pd

conn = sqlite3.connect('C:/Users/Variet-Worker/Desktop/climate_risk/data/climate_risk.db')
cur = conn.cursor()

# Drop if exists
cur.execute("DROP TABLE IF EXISTS firm_reference_data")

# Create table
cur.execute("""
CREATE TABLE firm_reference_data (
    asset_code TEXT PRIMARY KEY,
    asset_name TEXT,
    ksic_code TEXT,
    gics_sector TEXT,
    credit_rating TEXT
)
""")

# Reference Data based on actual benchmark entities
ref_data = [
    # Ticker, Name, KSIC, GICS, Rating
    ('005930.KS', 'Samsung Electronics', 'C', 'Information Technology', 'AA-'),
    ('000660.KS', 'SK Hynix', 'C', 'Information Technology', 'A'),
    ('005380.KS', 'Hyundai Motor', 'C', 'Consumer Discretionary', 'A+'),
    ('035420.KS', 'NAVER', 'J', 'Communication Services', 'AA-'),
    ('051910.KS', 'LG Chem', 'C', 'Materials', 'A+'),
    ('105560.KS', 'KB Financial', 'K', 'Financials', 'AAA'),
    ('055550.KS', 'Shinhan Financial', 'K', 'Financials', 'AAA'),
    ('032830.KS', 'Samsung Life', 'K', 'Financials', 'AAA'),
    ('015760.KS', 'KEPCO', 'D', 'Utilities', 'AAA'), # Using D for Utilities logic mapping
    ('KS200', 'KOSPI 200 Index', 'C', 'Index', 'AA'), # Approximate proxy
    ('HSCEI', 'HSCEI Index', 'K', 'Index', 'A'), 
    ('SPX', 'S&P 500 Index', 'C', 'Index', 'AA'),
    ('USDKRW', 'USD/KRW FX', 'FX', 'Currency', 'AAA'),
    ('XAUUSD', 'Gold/USD', 'CM', 'Commodity', 'AAA'),
    ('KTB_10Y', 'Korea Treasury Bond 10Y', 'GOV', 'Sovereign', 'AA'),
    ('KR103501G000', 'Korea Treasury Bond 3Y (03125-2606)', 'GOV', 'Sovereign', 'AA'),
    ('KR600538012C', 'Hyundai Motor 316-1 Unsecured', 'C', 'Corporate Bond', 'A+'),
    ('KR600593000A', 'Samsung Elec 1st Unsecured', 'C', 'Corporate Bond', 'AA-'),
    ('KR610556011B', 'KB Financial 2024-1 Bank Debenture', 'K', 'Corporate Bond', 'AAA'),
    ('CD91D', 'KRW CD 91D', 'GOV', 'Rate', 'AAA'),
    ('SOFR', 'USD SOFR', 'GOV', 'Rate', 'AAA'),
]

for row in ref_data:
    cur.execute("INSERT INTO firm_reference_data VALUES (?, ?, ?, ?, ?)", row)

conn.commit()

df = pd.read_sql("SELECT * FROM firm_reference_data", conn)
print("Firm Reference Data generated:")
print(df.to_markdown())

conn.close()