import sqlite3

conn = sqlite3.connect('hello_earth_data_2.db')
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS Project (
    project_id TEXT PRIMARY KEY,
    title TEXT,
    status TEXT,
    detail_information TEXT,
    start_date DATETIME,
    end_date DATETIME
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS Deliverable (
    deliverable_id TEXT PRIMARY KEY,
    project_id TEXT,
    title TEXT,
    status TEXT CHECK(status IN ('done', 'ongoing')),
    risk_level TEXT CHECK(risk_level IN ('green', 'yellow', 'red')),
    risk_level_rationale TEXT,
    start_date DATETIME,
    end_date DATETIME,
    FOREIGN KEY (project_id) REFERENCES Project(project_id)
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS Expense (
    expense_id TEXT PRIMARY KEY,
    associated_deliverable_id TEXT,
    seller_name TEXT,
    seller_address TEXT,
    seller_phone_number TEXT,
    buyer_name TEXT,
    buyer_address TEXT,
    transaction_date DATETIME,
    total_payment_amount REAL,
    expense_description TEXT,
    status TEXT,
    FOREIGN KEY (associated_deliverable_id) REFERENCES Deliverable(deliverable_id)
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS Deliverable_Budget (
    id TEXT PRIMARY KEY,
    deliverable_id TEXT,
    wage REAL,
    materials REAL,
    tools_equipment REAL,
    services REAL,
    misc REAL,
    FOREIGN KEY (deliverable_id) REFERENCES Deliverable(deliverable_id)
)
''')

conn.commit()
conn.close()

print("SQLite database created with the specified schema.")