database/create_government_db.py

import sqlite3
import os

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

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

# Create table for customer PR status
cursor.execute('''
CREATE TABLE IF NOT EXISTS pr_details (
    id TEXT PRIMARY KEY,
    name TEXT,
    email TEXT,
    pr_status BOOLEAN
)
''')

# Insert sample data
pr = [
    ('1', 'Loren', 'loren@example.com', False),   # Singaporean → not PR
    ('2', 'Matt', 'matt@example.com', True),      # Malaysian → PR
    ('3', 'Hilda', 'hilda@example.com', False),   # Singaporean → not PR
    ('4', 'Andy', 'andy@example.com', False),  # Filipino → PR
    ('5', 'Kit', 'kit@example.com', False)     # Vietnamese → PR
]

cursor.executemany('INSERT OR IGNORE INTO pr_details VALUES (?, ?, ?, ?)', pr)

# Commit changes and close
conn.commit()
conn.close()
print("government.db populated with sample customer data.")