database/create_internal_db.py

import sqlite3
import os

# Ensure database folder exists
os.makedirs("database", exist_ok=True)

# Full path for internal.db
db_path = "database/internal.db"
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# Create tables

cursor.execute('''
CREATE TABLE IF NOT EXISTS customer_details (
    customer_id TEXT PRIMARY KEY,
    name TEXT,
    email TEXT,
    nationality TEXT
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS customer_credit_score (
    customer_id TEXT PRIMARY KEY,
    credit_score INTEGER
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS customer_account_status (
    customer_id TEXT PRIMARY KEY,
    account_id TEXT,
    account_status TEXT
)
''')

# Insert sample customer details
customers = [
    ('1', 'Loren', 'loren@example.com', 'Singaporean'),
    ('2', 'Matt', 'matt@example.com', 'Malaysian'),
    ('3', 'Hilda', 'hilda@example.com', 'Singaporean'),
    ('4', 'Andy', 'andy@example.com', 'Filipino'),
    ('5', 'Kit', 'kit@example.com', 'Vietnamese')
]

cursor.executemany('''
INSERT OR IGNORE INTO customer_details (customer_id, name, email, nationality)
VALUES (?, ?, ?, ?)
''', customers)

# Insert sample credit scores
credit_scores = [
    ('1', 455),
    ('2', 685),
    ('3', 825),
    ('4', 840),
    ('5', 350)
]

cursor.executemany('''
INSERT OR IGNORE INTO customer_credit_score (customer_id, credit_score)
VALUES (?, ?)
''', credit_scores)

# Insert sample account status
account_statuses = [
    ('1', 'AC001', 'Good-standing'),
    ('2', 'AC002', 'Good-standing'),
    ('3', 'AC003', 'Delinquent'),
    ('4', 'AC004', 'Good-standing'),
    ('5', 'AC005', 'Closed')
]

cursor.executemany('''
INSERT OR IGNORE INTO customer_account_status (customer_id, account_id, account_status)
VALUES (?, ?, ?)
''', account_statuses)

conn.commit()
conn.close()
print("internal.db populated with sample customer data.")