create_database.py

#!/usr/bin/env python3
"""
Create SQLite database from NYC Chromebook CSV data
Generates both DeviceHub and Intune mock data from the CSV
"""

import sqlite3
import csv
import json
import random
from datetime import datetime, timedelta
import os

# Configuration
CSV_FILE = "NYC_Chromebook_ASN_Import_350K.csv"
DB_FILE = "doe_devices.db"

def create_database():
    """Create SQLite database with proper schema"""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    
    # Drop existing tables
    cursor.execute("DROP TABLE IF EXISTS devices")
    cursor.execute("DROP TABLE IF EXISTS intune_devices")
    cursor.execute("DROP TABLE IF EXISTS devicehub_assets")
    
    # Create main devices table from CSV
    cursor.execute('''
        CREATE TABLE devices (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            account_name TEXT,
            po_number TEXT,
            purchase_order_number TEXT,
            vendor TEXT,
            manufacturer TEXT,
            model_id TEXT,
            serial TEXT UNIQUE NOT NULL,
            asset_tag TEXT,
            quantity INTEGER,
            ship_date TEXT,
            carrier TEXT,
            tracking_number TEXT,
            ship_to_address_street TEXT,
            ship_to_address_city TEXT,
            ship_to_address_state TEXT,
            ship_to_address_zip TEXT,
            order_date TEXT,
            svc_contract_end_date TEXT
        )
    ''')
    
    # Create Intune devices table (mock Intune API data)
    cursor.execute('''
        CREATE TABLE intune_devices (
            id TEXT PRIMARY KEY,
            serial_number TEXT UNIQUE NOT NULL,
            device_name TEXT,
            manufacturer TEXT,
            model TEXT,
            operating_system TEXT,
            os_version TEXT,
            compliance_state TEXT,
            management_state TEXT,
            enrolled_date_time TEXT,
            last_sync_date_time TEXT,
            wifi_mac_address TEXT,
            physical_memory_bytes INTEGER,
            total_storage_bytes INTEGER,
            user_principal_name TEXT,
            device_category TEXT,
            notes TEXT,
            FOREIGN KEY (serial_number) REFERENCES devices (serial)
        )
    ''')
    
    # Create DeviceHub assets table (mock DeviceHub API data)
    cursor.execute('''
        CREATE TABLE devicehub_assets (
            asset_id TEXT PRIMARY KEY,
            serial_number TEXT UNIQUE NOT NULL,
            asset_tag TEXT,
            manufacturer TEXT,
            model TEXT,
            category TEXT,
            status TEXT,
            location_building TEXT,
            assigned_to_name TEXT,
            assigned_to_department TEXT,
            purchase_price REAL,
            purchase_date TEXT,
            warranty_expiration TEXT,
            FOREIGN KEY (serial_number) REFERENCES devices (serial)
        )
    ''')
    
    # Create indices for fast lookups
    cursor.execute("CREATE INDEX idx_devices_serial ON devices(serial)")
    cursor.execute("CREATE INDEX idx_intune_serial ON intune_devices(serial_number)")
    cursor.execute("CREATE INDEX idx_devicehub_serial ON devicehub_assets(serial_number)")
    
    conn.commit()
    return conn

def import_csv_data(conn):
    """Import CSV data into devices table"""
    cursor = conn.cursor()
    
    print(f"Reading CSV file: {CSV_FILE}")
    
    with open(CSV_FILE, 'r', newline='', encoding='utf-8') as file:
        reader = csv.DictReader(file)
        
        devices_data = []
        for row in reader:
            devices_data.append((
                row['Account name'],
                row['PO number'],
                row['Purchase order number'], 
                row['Vendor'],
                row['Manufacturer'],
                row['Model id'],
                row['Serial'],
                row['Asset tag'],
                int(row['Quantity']) if row['Quantity'] else 1,
                row['Ship date'],
                row['Carrier'],
                row['Tracking number'],
                row['Ship to address street'],
                row['Ship to address city'],
                row['Ship to address state'],
                row['Ship to address zip'],
                row['Order date'],
                row['SVC contract end date']
            ))
    
    cursor.executemany('''
        INSERT OR IGNORE INTO devices (
            account_name, po_number, purchase_order_number, vendor, 
            manufacturer, model_id, serial, asset_tag, quantity,
            ship_date, carrier, tracking_number, ship_to_address_street,
            ship_to_address_city, ship_to_address_state, ship_to_address_zip,
            order_date, svc_contract_end_date
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', devices_data)
    
    print(f"Imported {len(devices_data)} devices from CSV")
    conn.commit()

def generate_mock_intune_data(conn):
    """Generate mock Intune device data for each device"""
    cursor = conn.cursor()
    
    # Get all devices from CSV
    cursor.execute("SELECT serial, asset_tag, manufacturer, model_id FROM devices")
    devices = cursor.fetchall()
    
    compliance_states = ['compliant', 'noncompliant', 'conflict', 'error', 'inGracePeriod', 'configManager']
    management_states = ['managed', 'retirePending', 'retireFailed', 'wipePending', 'wipeFailed', 'unhealthy']
    
    intune_data = []
    for i, (serial, asset_tag, manufacturer, model_id) in enumerate(devices):
        # Generate mock Intune device data
        device_id = f"intune-{serial.lower().replace('nyc-sn', '')}"
        device_name = f"NYC-{asset_tag}" if asset_tag else f"NYC-Device-{i+1}"
        
        # Random dates
        enrolled = datetime.now() - timedelta(days=random.randint(30, 365))
        last_sync = datetime.now() - timedelta(hours=random.randint(1, 48))
        
        # Generate MAC address
        mac = ':'.join([f"{random.randint(0, 255):02X}" for _ in range(6)])
        
        intune_data.append((
            device_id,
            serial,
            device_name,
            manufacturer or 'Google',
            model_id or 'CC6PF NRD5N',
            'ChromeOS',
            '11.0.22000.1455',
            random.choice(compliance_states),
            random.choice(management_states),
            enrolled.isoformat() + 'Z',
            last_sync.isoformat() + 'Z',
            mac,
            4 * 1024 * 1024 * 1024,  # 4GB RAM
            64 * 1000 * 1000 * 1000,  # 64GB storage
            f"user{i+1}@nycdoe.edu",
            'Chromebook',
            f"Chromebook from PO: {asset_tag}"
        ))
    
    cursor.executemany('''
        INSERT OR IGNORE INTO intune_devices (
            id, serial_number, device_name, manufacturer, model,
            operating_system, os_version, compliance_state, management_state,
            enrolled_date_time, last_sync_date_time, wifi_mac_address,
            physical_memory_bytes, total_storage_bytes, user_principal_name,
            device_category, notes
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', intune_data)
    
    print(f"Generated {len(intune_data)} Intune device records")
    conn.commit()

def generate_mock_devicehub_data(conn):
    """Generate mock DeviceHub asset data for each device"""
    cursor = conn.cursor()
    
    # Get all devices from CSV
    cursor.execute("SELECT serial, asset_tag, manufacturer, model_id, svc_contract_end_date FROM devices")
    devices = cursor.fetchall()
    
    statuses = ['In Stock', 'In Transit', 'Deployed', 'Pending Transfer', 'Available']
    locations = ['HS 789', 'MS 456', 'ES 123', 'NYC Department of Education HQ', 'District Office']
    departments = ['Technology', 'Education', 'Administration']
    
    devicehub_data = []
    for i, (serial, asset_tag, manufacturer, model_id, warranty_end) in enumerate(devices):
        # Generate mock DeviceHub asset data
        asset_id = f"DH-{asset_tag}" if asset_tag else f"DH-Asset-{i+1}"
        
        # Generate user assignment
        assigned_names = [
            'Taylor Rodriguez', 'Jordan Smith', 'Alex Johnson', 'Morgan Davis', 
            'Casey Wilson', 'Riley Brown', 'Avery Jones', 'Parker Miller'
        ]
        
        devicehub_data.append((
            asset_id,
            serial,
            asset_tag,
            manufacturer or 'Google',
            model_id or 'CC6PF NRD5N',
            'Chromebook',
            random.choice(statuses),
            random.choice(locations),
            random.choice(assigned_names),
            random.choice(departments),
            random.uniform(300, 600),  # Purchase price
            '',  # Purchase date (empty like in original)
            warranty_end or '2029-09-03'
        ))
    
    cursor.executemany('''
        INSERT OR IGNORE INTO devicehub_assets (
            asset_id, serial_number, asset_tag, manufacturer, model,
            category, status, location_building, assigned_to_name,
            assigned_to_department, purchase_price, purchase_date, warranty_expiration
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', devicehub_data)
    
    print(f"Generated {len(devicehub_data)} DeviceHub asset records")
    conn.commit()

def main():
    """Main function to create database and import data"""
    print("Creating SQLite database for DOE devices...")
    
    # Remove existing database
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)
        print(f"Removed existing database: {DB_FILE}")
    
    # Create database and tables
    conn = create_database()
    print("Created database schema")
    
    # Import data
    import_csv_data(conn)
    generate_mock_intune_data(conn)
    generate_mock_devicehub_data(conn)
    
    # Verify data
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM devices")
    device_count = cursor.fetchone()[0]
    
    cursor.execute("SELECT COUNT(*) FROM intune_devices")
    intune_count = cursor.fetchone()[0]
    
    cursor.execute("SELECT COUNT(*) FROM devicehub_assets")
    devicehub_count = cursor.fetchone()[0]
    
    print(f"\n✅ Database created successfully!")
    print(f"📊 Total devices: {device_count}")
    print(f"📱 Intune records: {intune_count}")
    print(f"🏢 DeviceHub records: {devicehub_count}")
    print(f"💾 Database file: {DB_FILE}")
    
    # Test a specific serial number
    cursor.execute("SELECT serial FROM devices WHERE serial = 'NYC-SN65467'")
    test_device = cursor.fetchone()
    if test_device:
        print(f"✅ Test serial NYC-SN65467 found in database")
    else:
        print(f"❌ Test serial NYC-SN65467 not found - check CSV data")
    
    conn.close()

if __name__ == "__main__":
    main()