631 lines
23 KiB
Python
631 lines
23 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Import MSFS merged data into a fresh assets.db for the Canteen Asset Tracker.
|
|
|
|
Steps:
|
|
1. Back up the existing assets.db with a timestamp suffix
|
|
2. Read merged-assets.json (10,038 records from MSFS + Canteen merge)
|
|
3. Create a fresh assets.db with the full schema (tables, indexes, triggers)
|
|
4. Map merged fields to the assets table per the mapping specification
|
|
5. Insert default users (admin + tech)
|
|
6. Verify the data
|
|
|
|
Usage:
|
|
python3 import_msfs.py
|
|
"""
|
|
|
|
import hashlib
|
|
import json
|
|
import os
|
|
import shutil
|
|
import sqlite3
|
|
import sys
|
|
from datetime import datetime
|
|
|
|
# ── Paths ──────────────────────────────────────────────────────────────────
|
|
PROJECT_DIR = os.path.dirname(os.path.abspath(__file__))
|
|
DB_PATH = os.path.join(PROJECT_DIR, "assets.db")
|
|
MERGED_JSON = os.path.expanduser(
|
|
"~/projects/ms-field-service-extraction/web/static/data/merged-assets.json"
|
|
)
|
|
|
|
# ── Password hashing (mirrors server.py) ──────────────────────────────────
|
|
def hash_password(password: str) -> str:
|
|
return hashlib.sha256(password.encode()).hexdigest()
|
|
|
|
# ── 1. Backup existing DB ────────────────────────────────────────────────
|
|
def backup_db():
|
|
if not os.path.exists(DB_PATH):
|
|
print("[BACKUP] No existing assets.db found — skipping backup.")
|
|
return None
|
|
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
|
|
backup_path = f"{DB_PATH}.{timestamp}.pre-msfs-import"
|
|
shutil.copy2(DB_PATH, backup_path)
|
|
size_mb = os.path.getsize(backup_path) / (1024 * 1024)
|
|
print(f"[BACKUP] Copied assets.db → {backup_path} ({size_mb:.1f} MB)")
|
|
return backup_path
|
|
|
|
# ── 2. Read merged data ──────────────────────────────────────────────────
|
|
def read_merged_data():
|
|
if not os.path.exists(MERGED_JSON):
|
|
print(f"[ERROR] Merged data not found at: {MERGED_JSON}")
|
|
sys.exit(1)
|
|
with open(MERGED_JSON, "r") as f:
|
|
data = json.load(f)
|
|
assets = data.get("assets", [])
|
|
meta = data.get("meta", {})
|
|
print(f"[READ] Loaded {len(assets)} records from merged-assets.json")
|
|
print(f" Joined: {meta.get('joined', '?')} "
|
|
f"MSFS-only: {meta.get('msfs_only', '?')} "
|
|
f"Canteen-only: {meta.get('canteen_only', '?')}")
|
|
return assets
|
|
|
|
# ── 3. Create fresh DB with full schema ──────────────────────────────────
|
|
SCHEMA_SQL = """
|
|
PRAGMA journal_mode=WAL;
|
|
PRAGMA foreign_keys=ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'technician',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS customer_contacts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
name TEXT,
|
|
phone TEXT,
|
|
email TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS locations (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
customer_id INTEGER REFERENCES customers(id),
|
|
name TEXT,
|
|
address TEXT DEFAULT '',
|
|
building_name TEXT DEFAULT '',
|
|
building_number TEXT DEFAULT '',
|
|
floor TEXT DEFAULT '',
|
|
trailer_number TEXT DEFAULT '',
|
|
site_hours TEXT DEFAULT '',
|
|
access_notes TEXT DEFAULT '',
|
|
walking_directions TEXT DEFAULT '',
|
|
map_link TEXT DEFAULT '',
|
|
latitude REAL DEFAULT NULL,
|
|
longitude REAL DEFAULT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
disney_park TEXT DEFAULT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS rooms (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
location_id INTEGER NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
|
|
name TEXT,
|
|
floor TEXT DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE NOT NULL,
|
|
icon TEXT DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS key_names (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS key_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS badge_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS makes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS models (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
make_id INTEGER NOT NULL REFERENCES makes(id),
|
|
name TEXT NOT NULL,
|
|
icon_path TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS assets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
machine_id TEXT NOT NULL UNIQUE,
|
|
serial_number TEXT DEFAULT '',
|
|
name TEXT NOT NULL,
|
|
description TEXT DEFAULT '',
|
|
category TEXT NOT NULL DEFAULT 'Other',
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
make TEXT DEFAULT '',
|
|
model TEXT DEFAULT '',
|
|
address TEXT DEFAULT '',
|
|
building_name TEXT DEFAULT '',
|
|
building_number TEXT DEFAULT '',
|
|
floor TEXT DEFAULT '',
|
|
room TEXT DEFAULT '',
|
|
trailer_number TEXT DEFAULT '',
|
|
walking_directions TEXT DEFAULT '',
|
|
map_link TEXT DEFAULT '',
|
|
parking_location TEXT DEFAULT '',
|
|
photo_path TEXT,
|
|
customer_id INTEGER REFERENCES customers(id),
|
|
location_id INTEGER REFERENCES locations(id),
|
|
assigned_to INTEGER REFERENCES users(id),
|
|
latitude REAL DEFAULT NULL,
|
|
longitude REAL DEFAULT NULL,
|
|
geofence_radius_meters INTEGER DEFAULT 50,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
disney_park TEXT DEFAULT NULL,
|
|
is_disney INTEGER DEFAULT 0,
|
|
dex_report_date TEXT DEFAULT NULL,
|
|
install_date TEXT DEFAULT NULL,
|
|
deployed TEXT DEFAULT NULL,
|
|
pulled_date TEXT DEFAULT NULL,
|
|
company TEXT DEFAULT '',
|
|
location_area TEXT DEFAULT '',
|
|
place TEXT DEFAULT '',
|
|
connect_id TEXT DEFAULT '',
|
|
canteen_connect_guid TEXT DEFAULT '',
|
|
manufacturer TEXT DEFAULT '',
|
|
equipment_id TEXT DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS asset_keys (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
|
|
key_name TEXT,
|
|
key_type TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS asset_badges (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
|
|
badge_name TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
key TEXT UNIQUE NOT NULL,
|
|
value TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS activity_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER REFERENCES users(id),
|
|
action TEXT,
|
|
entity_type TEXT,
|
|
entity_id INTEGER,
|
|
details TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS geofences (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT,
|
|
points TEXT,
|
|
color TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS geofence_users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
geofence_id INTEGER NOT NULL REFERENCES geofences(id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE(geofence_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS visits (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER REFERENCES users(id),
|
|
asset_id INTEGER REFERENCES assets(id) ON DELETE CASCADE,
|
|
checkin_time TEXT,
|
|
checkout_time TEXT,
|
|
duration_minutes INTEGER,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
token TEXT UNIQUE NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
expires_at TEXT NOT NULL DEFAULT (datetime('now', '+1 day'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS cantaloupe_sync_batches (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
approved_at TEXT,
|
|
file_path TEXT,
|
|
row_count INTEGER DEFAULT 0,
|
|
diff_summary TEXT,
|
|
raw_data TEXT,
|
|
error_message TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_entrances (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
location_id INTEGER NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
latitude REAL NOT NULL,
|
|
longitude REAL NOT NULL,
|
|
notes TEXT DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS checkins (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
asset_id INTEGER REFERENCES assets(id) ON DELETE CASCADE,
|
|
user_id INTEGER REFERENCES users(id),
|
|
latitude REAL,
|
|
longitude REAL,
|
|
accuracy REAL,
|
|
photo_path TEXT,
|
|
notes TEXT DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_assets_category ON assets(category);
|
|
CREATE INDEX IF NOT EXISTS idx_csb_status ON cantaloupe_sync_batches(status);
|
|
CREATE INDEX IF NOT EXISTS idx_csb_created ON cantaloupe_sync_batches(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_checkins_asset_id ON checkins(asset_id);
|
|
CREATE INDEX IF NOT EXISTS idx_checkins_created_at ON checkins(created_at);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_assets_machine_id ON assets(machine_id);
|
|
"""
|
|
|
|
# ── 4. Map merged record to asset row ────────────────────────────────────
|
|
def get_machine_id(rec):
|
|
"""Get machine_id, falling back from msfs to canteen."""
|
|
mid = rec.get("msfs_machine_id")
|
|
if mid:
|
|
return str(mid).strip()
|
|
mid = rec.get("canteen_machine_id")
|
|
if mid:
|
|
return str(mid).strip()
|
|
return ""
|
|
|
|
def map_record_to_asset(rec):
|
|
"""
|
|
Map a merged record's fields to the assets table columns.
|
|
Returns a dict keyed by column name.
|
|
"""
|
|
# Primary identifiers
|
|
machine_id = get_machine_id(rec)
|
|
name = rec.get("msfs_name") or rec.get("canteen_name") or ""
|
|
|
|
# Category / status
|
|
category = rec.get("canteen_category") or "Other"
|
|
status = rec.get("canteen_status") or "active"
|
|
|
|
# Make / model / serial
|
|
make = rec.get("msfs_manufacturer") or ""
|
|
model = rec.get("msfs_dex_model") or ""
|
|
serial_number = rec.get("serial_number") or ""
|
|
|
|
# GPS — prefer msfs, fall back to canteen, then account
|
|
latitude = rec.get("msfs_latitude")
|
|
longitude = rec.get("msfs_longitude")
|
|
if latitude is None or longitude is None:
|
|
latitude = rec.get("canteen_latitude") or latitude
|
|
longitude = rec.get("canteen_longitude") or longitude
|
|
|
|
# Address — from account if available
|
|
address = ""
|
|
account = rec.get("account")
|
|
if account and account.get("address"):
|
|
addr_parts = [account["address"]]
|
|
if account.get("city"):
|
|
addr_parts.append(account["city"])
|
|
if account.get("state"):
|
|
addr_parts.append(account["state"])
|
|
if account.get("zip"):
|
|
addr_parts.append(account["zip"])
|
|
address = ", ".join(addr_parts)
|
|
if not address:
|
|
address = rec.get("canteen_address") or ""
|
|
|
|
# Canteen fields
|
|
customer_id = rec.get("canteen_customer_id")
|
|
disney_park = rec.get("canteen_disney_park")
|
|
is_disney = rec.get("canteen_is_disney") or 0
|
|
dex_report_date = rec.get("canteen_dex_report_date")
|
|
install_date = rec.get("canteen_install_date") or rec.get("msfs_install_date")
|
|
building_name = rec.get("canteen_building_name")
|
|
building_number = rec.get("canteen_building_number")
|
|
floor = rec.get("canteen_floor") or ""
|
|
room = rec.get("canteen_room") or ""
|
|
geofence_radius = rec.get("canteen_geofence_radius") or 50
|
|
company = rec.get("canteen_company") or ""
|
|
description = rec.get("canteen_description") or ""
|
|
deployed = rec.get("canteen_deployed")
|
|
pulled_date = rec.get("canteen_pulled_date")
|
|
location_area = rec.get("canteen_location_area") or ""
|
|
place = rec.get("canteen_place") or ""
|
|
location_id = rec.get("canteen_location_id")
|
|
|
|
# MSFS fields
|
|
connect_id = rec.get("msfs_connect_id") or ""
|
|
canteen_connect_guid = rec.get("msfs_canteen_connect_guid") or ""
|
|
manufacturer = rec.get("msfs_manufacturer") or ""
|
|
equipment_id = rec.get("msfs_equipment_id") or ""
|
|
|
|
return {
|
|
"machine_id": machine_id,
|
|
"serial_number": serial_number,
|
|
"name": name,
|
|
"description": description,
|
|
"category": category,
|
|
"status": status,
|
|
"make": make,
|
|
"model": model,
|
|
"address": address,
|
|
"building_name": building_name or "",
|
|
"building_number": building_number or "",
|
|
"floor": floor,
|
|
"room": room,
|
|
"customer_id": customer_id,
|
|
"location_id": location_id,
|
|
"latitude": latitude,
|
|
"longitude": longitude,
|
|
"geofence_radius_meters": geofence_radius,
|
|
"disney_park": disney_park,
|
|
"is_disney": is_disney,
|
|
"dex_report_date": dex_report_date,
|
|
"install_date": install_date,
|
|
"deployed": deployed,
|
|
"pulled_date": pulled_date,
|
|
"company": company,
|
|
"location_area": location_area,
|
|
"place": place,
|
|
"connect_id": connect_id,
|
|
"canteen_connect_guid": canteen_connect_guid,
|
|
"manufacturer": manufacturer,
|
|
"equipment_id": equipment_id,
|
|
}
|
|
|
|
# ── 5. Insert records into DB ────────────────────────────────────────────
|
|
def create_db(records):
|
|
"""Create a fresh assets.db with schema and import records."""
|
|
# Remove existing
|
|
if os.path.exists(DB_PATH):
|
|
os.remove(DB_PATH)
|
|
print("[DB] Removed existing assets.db")
|
|
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.executescript(SCHEMA_SQL)
|
|
print("[DB] Created fresh assets.db with full schema")
|
|
|
|
# Insert default categories used by the data
|
|
cursor = conn.cursor()
|
|
categories_seen = set()
|
|
for rec in records:
|
|
cat = rec.get("canteen_category") or "Other"
|
|
categories_seen.add(cat)
|
|
for cat in sorted(categories_seen):
|
|
cursor.execute("INSERT OR IGNORE INTO categories (name) VALUES (?)", (cat,))
|
|
print(f"[CATEGORIES] Inserted {len(categories_seen)} categories")
|
|
|
|
# Insert customers from the data
|
|
customers_seen = {}
|
|
for rec in records:
|
|
if rec.get("customer") and rec["customer"].get("name"):
|
|
cust = rec["customer"]
|
|
cid = cust.get("id")
|
|
cname = cust.get("name")
|
|
if cid and cname and cid not in customers_seen:
|
|
customers_seen[cid] = cname
|
|
for cid, cname in sorted(customers_seen.items()):
|
|
cursor.execute(
|
|
"INSERT OR IGNORE INTO customers (id, name) VALUES (?, ?)",
|
|
(cid, cname),
|
|
)
|
|
print(f"[CUSTOMERS] Inserted {len(customers_seen)} customers")
|
|
|
|
# Insert locations from the data
|
|
locations_seen = {}
|
|
for rec in records:
|
|
if rec.get("location") and rec["location"].get("name"):
|
|
loc = rec["location"]
|
|
lid = loc.get("id")
|
|
lname = loc.get("name")
|
|
if lid and lname and lid not in locations_seen:
|
|
locations_seen[lid] = {
|
|
"name": lname,
|
|
"address": loc.get("address") or "",
|
|
"customer_id": rec.get("canteen_customer_id"),
|
|
}
|
|
for lid, loc_data in sorted(locations_seen.items()):
|
|
cursor.execute(
|
|
"INSERT OR IGNORE INTO locations (id, name, address, customer_id) VALUES (?, ?, ?, ?)",
|
|
(lid, loc_data["name"], loc_data["address"], loc_data["customer_id"]),
|
|
)
|
|
print(f"[LOCATIONS] Inserted {len(locations_seen)} locations")
|
|
|
|
# Insert default users
|
|
default_users = [
|
|
("admin", hash_password("admin123"), "admin"),
|
|
("tech", hash_password("tech123"), "technician"),
|
|
]
|
|
for username, pw_hash, role in default_users:
|
|
cursor.execute(
|
|
"INSERT INTO users (username, password_hash, role) VALUES (?, ?, ?)",
|
|
(username, pw_hash, role),
|
|
)
|
|
print(f"[USERS] Inserted {len(default_users)} default users")
|
|
|
|
# Insert assets — deduplicate by machine_id, preferring "joined" over "msfs_only"
|
|
asset_cols = [
|
|
"machine_id", "serial_number", "name", "description", "category",
|
|
"status", "make", "model", "address", "building_name",
|
|
"building_number", "floor", "room", "customer_id", "location_id",
|
|
"latitude", "longitude", "geofence_radius_meters", "disney_park",
|
|
"is_disney", "dex_report_date", "install_date", "deployed",
|
|
"pulled_date", "company", "location_area", "place", "connect_id",
|
|
"canteen_connect_guid", "manufacturer", "equipment_id",
|
|
]
|
|
placeholders = ", ".join(["?"] * len(asset_cols))
|
|
col_names = ", ".join(asset_cols)
|
|
|
|
# Priority: joined > msfs_only > canteen_only
|
|
match_priority = {"joined": 0, "msfs_only": 1, "canteen_only": 2}
|
|
|
|
deduped = {} # machine_id -> (priority, row)
|
|
no_id = 0
|
|
for rec in records:
|
|
row = map_record_to_asset(rec)
|
|
mid = row["machine_id"]
|
|
if not mid:
|
|
no_id += 1
|
|
continue
|
|
priority = match_priority.get(rec.get("match", ""), 99)
|
|
if mid not in deduped or priority < deduped[mid][0]:
|
|
deduped[mid] = (priority, row)
|
|
|
|
inserted = 0
|
|
for mid, (priority, row) in deduped.items():
|
|
values = [row[c] for c in asset_cols]
|
|
cursor.execute(
|
|
f"INSERT INTO assets ({col_names}) VALUES ({placeholders})",
|
|
values,
|
|
)
|
|
inserted += 1
|
|
|
|
total_raw = len(records)
|
|
total_deduped = len(deduped)
|
|
skipped = total_raw - total_deduped - no_id
|
|
print(f"\n[ASSETS] Raw records: {total_raw}")
|
|
print(f"[ASSETS] No machine_id: {no_id}")
|
|
print(f"[ASSETS] Duplicates removed: {skipped}")
|
|
print(f"[ASSETS] Inserted: {inserted}")
|
|
print(f"[ASSETS] Total in DB: {inserted}")
|
|
|
|
conn.commit()
|
|
|
|
# Count
|
|
asset_count = cursor.execute("SELECT COUNT(*) FROM assets").fetchone()[0]
|
|
gps_count = cursor.execute(
|
|
"SELECT COUNT(*) FROM assets WHERE latitude IS NOT NULL AND longitude IS NOT NULL"
|
|
).fetchone()[0]
|
|
cat_dist = cursor.execute(
|
|
"SELECT category, COUNT(*) FROM assets GROUP BY category ORDER BY COUNT(*) DESC"
|
|
).fetchall()
|
|
status_dist = cursor.execute(
|
|
"SELECT status, COUNT(*) FROM assets GROUP BY status ORDER BY COUNT(*) DESC"
|
|
).fetchall()
|
|
|
|
conn.close()
|
|
|
|
print(f"[ASSETS] With GPS coords: {gps_count}")
|
|
print(f"\n[ASSETS] Category distribution:")
|
|
for cat, cnt in cat_dist:
|
|
print(f" {cat}: {cnt}")
|
|
print(f"\n[ASSETS] Status distribution:")
|
|
for st, cnt in status_dist:
|
|
print(f" {st}: {cnt}")
|
|
|
|
return asset_count, gps_count
|
|
|
|
# ── 6. Verification ──────────────────────────────────────────────────────
|
|
def verify_db():
|
|
"""Open the fresh DB and run basic checks."""
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
cursor = conn.cursor()
|
|
|
|
print("\n─── VERIFICATION ───")
|
|
|
|
# Table list
|
|
tables = cursor.execute(
|
|
"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
|
|
).fetchall()
|
|
print(f"Tables ({len(tables)}): {', '.join(t['name'] for t in tables)}")
|
|
|
|
# User count
|
|
user_count = cursor.execute("SELECT COUNT(*) FROM users").fetchone()[0]
|
|
print(f"Users: {user_count}")
|
|
users = cursor.execute("SELECT username, role FROM users").fetchall()
|
|
for u in users:
|
|
print(f" - {u['username']} ({u['role']})")
|
|
|
|
# Asset count
|
|
asset_count = cursor.execute("SELECT COUNT(*) FROM assets").fetchone()[0]
|
|
gps_count = cursor.execute(
|
|
"SELECT COUNT(*) FROM assets WHERE latitude IS NOT NULL AND longitude IS NOT NULL"
|
|
).fetchone()[0]
|
|
print(f"Assets: {asset_count}")
|
|
print(f"Assets with GPS: {gps_count}")
|
|
|
|
# Sample from each match type
|
|
if asset_count > 0:
|
|
print("\nSample records:")
|
|
sample = cursor.execute(
|
|
"SELECT machine_id, name, category, status, latitude, longitude FROM assets LIMIT 5"
|
|
).fetchall()
|
|
for s in sample:
|
|
gps_tag = f"({s['latitude']}, {s['longitude']})" if s['latitude'] else "NO GPS"
|
|
print(f" {s['machine_id']}: {s['name']} [{s['category']}] {gps_tag}")
|
|
|
|
# Check index count
|
|
idx_count = cursor.execute(
|
|
"SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name NOT LIKE 'sqlite_%'"
|
|
).fetchone()[0]
|
|
print(f"Indexes: {idx_count}")
|
|
|
|
# DB size
|
|
db_size = os.path.getsize(DB_PATH) / (1024 * 1024)
|
|
print(f"DB size: {db_size:.1f} MB")
|
|
|
|
conn.close()
|
|
|
|
# ── Main ──────────────────────────────────────────────────────────────────
|
|
def main():
|
|
print("═══ MSFS MERGED DATA IMPORT ═══\n")
|
|
|
|
# 1. Backup
|
|
backup_path = backup_db()
|
|
|
|
# 2. Read data
|
|
records = read_merged_data()
|
|
|
|
# 3. Create DB and import
|
|
asset_count, gps_count = create_db(records)
|
|
|
|
# 4. Verify
|
|
verify_db()
|
|
|
|
print(f"\n═══ DONE ═══")
|
|
if backup_path:
|
|
print(f"Backup: {backup_path}")
|
|
print(f"New DB: {DB_PATH} ({asset_count} assets, {gps_count} with GPS)")
|
|
|
|
if __name__ == "__main__":
|
|
main()
|