canteen-seed-import/db_writer.py

#!/usr/bin/env python3
"""
🌱 Canteen Seed Data Import — Database Writer

Writes parsed machine records to SQLite database with per-field update policy.

Usage:
    from db_writer import DatabaseWriter
    writer = DatabaseWriter('seed-data/assets.db')
    writer.seed(machines)      # First import — insert all fresh
    writer.update(machines)    # Subsequent import — upsert with preserve/auto rules
"""

import json
import os
import sqlite3
from datetime import datetime


# ─── Per-field update policy (from Handbook Section 11) ───────────────────────

# Fields that are NEVER overwritten if they already have a value
PRESERVE_FIELDS = {
    "latitude",
    "longitude",
    "photo_path",
    "install_date",
    "deployed",
    "pulled_date",
}

# Fields that are ALWAYS overwritten from new data
AUTO_FIELDS = {
    "serial_number",
    "name",
    "make",
    "model",
    "class",
    "is_glass_front",
    "company",
    "place",
    "building_name",
    "floor",
    "zone",
    "zone_type",
    "address",
    "location_area",
    "dex_report_date",
    "disney_park",
    "disney_area",
    "remote_pricing_status",
    "alerts",
    "telemetry_provider",
    "card_reader_brand",
    "priority",
    "yearly_sales",
    "monthly_sales",
    "weekly_sales",
    "days_since_restock",
    "prepick_group",
    "has_cashless",
}

# All 36 DB columns (Section 11 of the handbook + suite/room/trailer)
# machine_id is the primary key — handled separately
ALL_COLUMNS = [
    "machine_id",          # preserve — primary key
    "serial_number",       # auto
    "name",                # auto (generated: "Make @ Place")
    "make",                # auto
    "model",               # auto
    "class",               # auto
    "is_glass_front",      # auto
    "company",             # auto
    "place",               # auto
    "building_name",       # auto
    "suite",               # auto (new)
    "room",                # auto (new)
    "trailer",             # auto (new)
    "floor",               # auto
    "zone",                # auto
    "zone_type",           # auto
    "address",             # auto
    "location_area",       # auto
    "latitude",            # preserve
    "longitude",           # preserve
    "photo_path",          # preserve
    "dex_report_date",     # auto
    "install_date",        # preserve
    "deployed",            # preserve
    "pulled_date",         # preserve
    "disney_park",         # auto
    "disney_area",         # auto
    "remote_pricing_status",  # auto
    "alerts",              # auto
    "telemetry_provider",  # auto
    "card_reader_brand",   # auto
    "priority",            # auto
    "yearly_sales",        # auto
    "monthly_sales",       # auto
    "weekly_sales",        # auto
    "days_since_restock",  # auto
    "prepick_group",       # auto
    "has_cashless",        # auto
]

# SQL column types
COLUMN_TYPES = {
    "machine_id": "TEXT PRIMARY KEY",
    "serial_number": "TEXT",
    "name": "TEXT",
    "make": "TEXT",
    "model": "TEXT",
    "class": "TEXT",
    "is_glass_front": "INTEGER",
    "company": "TEXT",
    "place": "TEXT",
    "building_name": "TEXT",
    "suite": "TEXT",
    "room": "TEXT",
    "trailer": "TEXT",
    "floor": "INTEGER",
    "zone": "TEXT",
    "zone_type": "TEXT",
    "address": "TEXT",
    "location_area": "TEXT",
    "latitude": "REAL",
    "longitude": "REAL",
    "photo_path": "TEXT",
    "dex_report_date": "TEXT",
    "install_date": "TEXT",
    "deployed": "INTEGER",
    "pulled_date": "TEXT",
    "disney_park": "TEXT",
    "disney_area": "TEXT",
    "remote_pricing_status": "TEXT",
    "alerts": "TEXT",
    "telemetry_provider": "TEXT",
    "card_reader_brand": "TEXT",
    "priority": "TEXT",
    "yearly_sales": "REAL",
    "monthly_sales": "REAL",
    "weekly_sales": "REAL",
    "days_since_restock": "INTEGER",
    "prepick_group": "TEXT",
    "has_cashless": "INTEGER",
    # Extra metadata fields (not in Section 11 but useful)
    "created_at": "TEXT",
    "updated_at": "TEXT",
}

# Extra columns for metadata
EXTRA_COLUMNS = ["created_at", "updated_at"]

# Fields that are not in the parser output and only come from external sources
EXTERNAL_PRESERVE_FIELDS = {"latitude", "longitude", "photo_path"}


# Fields that are NEVER set from seed data — GPS comes only from real check-ins
FIELDS_NEVER_FROM_SEED = {"latitude", "longitude", "photo_path"}


def _serialize_value(val):
    """Convert a Python value to a SQLite-friendly value."""
    if val is None:
        return None
    if isinstance(val, dict):
        return json.dumps(val, default=str)
    if isinstance(val, bool):
        return 1 if val else 0
    if isinstance(val, datetime):
        return val.isoformat()
    return val


def _deserialize_value(val, column):
    """Convert SQLite value back to Python, if needed."""
    if column == "is_glass_front" or column == "has_cashless" or column == "deployed":
        if val is None:
            return None
        return bool(val)
    if column == "alerts" and val is not None and isinstance(val, str):
        try:
            return json.loads(val)
        except (json.JSONDecodeError, TypeError):
            return val
    return val


def generate_name(make, place):
    """Generate a machine name from Make and Place."""
    make_str = str(make).strip() if make else "Unknown"
    place_str = str(place).strip() if place else "Unknown"
    return f"{make_str} @ {place_str}"


class DatabaseWriter:
    """
    SQLite database writer for seed data import.
    
    Handles both 'seed' (fresh insert) and 'update' (upsert with policy) modes.
    """

    def __init__(self, db_path):
        self.db_path = os.path.abspath(db_path)
        self.conn = None
        self._open_connection()

    def _open_connection(self):
        """Open SQLite connection, creating dir if needed."""
        db_dir = os.path.dirname(self.db_path)
        if db_dir and not os.path.exists(db_dir):
            os.makedirs(db_dir, exist_ok=True)
        self.conn = sqlite3.connect(self.db_path)
        self.conn.row_factory = sqlite3.Row

    def create_table(self):
        """Create the 'assets' table with all columns from Section 11 + suite/room/trailer."""
        columns_defs = []
        for col in ALL_COLUMNS:
            col_type = COLUMN_TYPES.get(col, "TEXT")
            columns_defs.append(f"    {col} {col_type}")
        for col in EXTRA_COLUMNS:
            col_type = COLUMN_TYPES.get(col, "TEXT")
            columns_defs.append(f"    {col} {col_type}")

        create_sql = (
            "CREATE TABLE IF NOT EXISTS assets (\n"
            + ",\n".join(columns_defs)
            + "\n)"
        )
        self.conn.execute(create_sql)
        
        # Migrate existing table: add any missing columns
        existing_cols = set(
            r[1] for r in self.conn.execute("PRAGMA table_info(assets)").fetchall()
        )
        for col in ALL_COLUMNS + EXTRA_COLUMNS:
            if col not in existing_cols:
                col_type = COLUMN_TYPES.get(col, "TEXT")
                try:
                    self.conn.execute(
                        f'ALTER TABLE assets ADD COLUMN "{col}" {col_type}'
                    )
                    print(f"  ➕ Added column: {col} ({col_type})")
                except Exception as e:
                    print(f"  ⚠️  Could not add column {col}: {e}")
        
        self.conn.commit()

    def _extract_row_values(self, machine):
        """Extract values from a machine dict, mapping to DB columns."""
        now = datetime.utcnow().isoformat()

        # Generate name from Make + Place
        name = generate_name(machine.get("make"), machine.get("place"))

        # Build row dict (only the columns we store)
        row = {}

        for col in ALL_COLUMNS:
            if col == "name":
                row[col] = name
            elif col == "machine_id":
                row[col] = str(machine.get("machine_id", ""))
            elif col == "is_glass_front":
                row[col] = 1 if machine.get("is_glass_front") else 0
            elif col == "has_cashless":
                val = machine.get("has_cashless")
                if val is True or val == 1 or val == "Yes" or val == "yes":
                    row[col] = 1
                elif val is False or val == 0 or val == "No" or val == "no":
                    row[col] = 0
                else:
                    row[col] = None
            elif col == "deployed":
                val = machine.get("deployed")
                if val is True or val == 1 or val == "Yes" or val == "yes":
                    row[col] = 1
                elif val is False or val == 0 or val == "No" or val == "no":
                    row[col] = 0
                else:
                    row[col] = None
            elif col == "alerts":
                row[col] = _serialize_value(machine.get("alerts"))
            elif col == "floor":
                val = machine.get("floor")
                if val is not None:
                    try:
                        row[col] = int(val)
                    except (ValueError, TypeError):
                        row[col] = None
                else:
                    row[col] = None
            elif col in ("yearly_sales", "monthly_sales", "weekly_sales"):
                row[col] = _serialize_value(machine.get(col))
            elif col in ("latitude", "longitude"):
                # GPS comes only from real check-ins, never from seed
                row[col] = None
            else:
                val = machine.get(col)
                if val is not None and not isinstance(val, (str, int, float, bool)):
                    val = str(val)
                row[col] = val

        # Extra metadata
        row["created_at"] = now
        row["updated_at"] = now

        return row

    def _columns_for_insert(self):
        """Return list of column names for INSERT (excluding PRIMARY KEY metadata for upsert)."""
        return ALL_COLUMNS + EXTRA_COLUMNS

    def seed(self, machines):
        """
        Seed mode: Insert all machines fresh.
        Drops any existing data and re-inserts.
        
        Returns dict with counts.
        """
        self.create_table()

        # Clear existing data
        self.conn.execute("DELETE FROM assets")

        insert_cols = self._columns_for_insert()
        placeholders = ", ".join("?" for _ in insert_cols)
        col_names = ", ".join(insert_cols)
        insert_sql = f"INSERT INTO assets ({col_names}) VALUES ({placeholders})"

        count = 0
        errors = 0
        for machine in machines:
            try:
                row = self._extract_row_values(machine)
                values = [row.get(c) for c in insert_cols]
                self.conn.execute(insert_sql, values)
                count += 1
            except Exception as e:
                errors += 1
                mid = machine.get("machine_id", "???")
                print(f"  ⚠️  Error inserting machine {mid}: {e}")

        self.conn.commit()

        return {
            "inserted": count,
            "errors": errors,
            "total": len(machines),
        }

    def update(self, machines):
        """
        Update mode: Upsert by machine_id.
        
        MSFS is primary data source — seed import fills gaps only.
        
        ALL fields (except machine_id PK) are treated as ONLY_IF_EMPTY:
            Skip update if existing value is non-null.
        
        New assets (not found by machine_id) are inserted with full data.
        
        Returns dict with counts.
        """
        self.create_table()

        # Ensure machine_id is indexed
        self.conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_assets_machine_id ON assets(machine_id)
        """)

        insert_cols = self._columns_for_insert()
        col_names = ", ".join(insert_cols)

        stats = {
            "inserted": 0,
            "updated": 0,
            "preserved": 0,
            "skipped": 0,
            "errors": 0,
        }

        # Prepare the UPDATE SET clause: col = ? for each non-PK column
        update_cols = [c for c in insert_cols if c != "machine_id"]
        set_clause = ", ".join(f"{c} = ?" for c in update_cols)

        for machine in machines:
            mid = machine.get("machine_id")
            if not mid:
                stats["skipped"] += 1
                continue

            mid_str = str(mid)

            # Check if machine exists
            cursor = self.conn.execute(
                "SELECT * FROM assets WHERE machine_id = ?", (mid_str,)
            )
            existing = cursor.fetchone()

            row = self._extract_row_values(machine)

            if existing is None:
                # INSERT new record
                values = [row.get(c) for c in insert_cols]
                try:
                    self.conn.execute(
                        f"INSERT INTO assets ({col_names}) VALUES ({', '.join('?' for _ in insert_cols)})",
                        values,
                    )
                    stats["inserted"] += 1
                except Exception as e:
                    stats["errors"] += 1
                    print(f"  ⚠️  Error inserting machine {mid}: {e}")
            else:
                # UPDATE existing record — MSFS is primary, seed fills gaps
                existing_dict = dict(existing)
                updates = {}
                skipped_count = 0

                for col in update_cols:
                    if col == "machine_id":
                        continue  # PK — never update
                    # GPS and photo come only from real check-ins, never from seed
                    if col in FIELDS_NEVER_FROM_SEED:
                        skipped_count += 1
                        continue
                    new_val = row.get(col)
                    old_val = existing_dict.get(col)
                    # Skip if existing value is non-null/empty (MSFS or user data wins)
                    if old_val is not None and old_val != "":
                        skipped_count += 1
                        continue
                    updates[col] = new_val

                if updates:
                    # Build dynamic UPDATE
                    set_parts = []
                    set_values = []
                    for col, val in updates.items():
                        set_parts.append(f"{col} = ?")
                        set_values.append(val)
                    # Always update updated_at
                    set_parts.append("updated_at = ?")
                    set_values.append(datetime.utcnow().isoformat())
                    set_values.append(mid_str)

                    try:
                        self.conn.execute(
                            f"UPDATE assets SET {', '.join(set_parts)} WHERE machine_id = ?",
                            set_values,
                        )
                        stats["updated"] += 1
                        stats["preserved"] += skipped_count
                    except Exception as e:
                        stats["errors"] += 1
                        print(f"  ⚠️  Error updating machine {mid}: {e}")
                else:
                    stats["skipped"] += 1

        self.conn.commit()
        return stats

    def get_machine_count(self):
        """Return the number of machines in the database."""
        cursor = self.conn.execute("SELECT COUNT(*) FROM assets")
        return cursor.fetchone()[0]

    def close(self):
        """Close the database connection."""
        if self.conn:
            self.conn.close()
            self.conn = None

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()


def write_seed(machines, db_path):
    """Convenience: seed mode with reporting."""
    print(f"  📁 Database: {db_path}")
    with DatabaseWriter(db_path) as writer:
        stats = writer.seed(machines)
        final_count = writer.get_machine_count()
        print(f"  ✅ Seeded: {stats['inserted']} machines")
        print(f"  📊 Total in DB: {final_count}")
        if stats["errors"]:
            print(f"  ⚠️  Errors: {stats['errors']}")
    return stats


def write_update(machines, db_path):
    """Convenience: update mode with reporting."""
    print(f"  📁 Database: {db_path}")
    with DatabaseWriter(db_path) as writer:
        stats = writer.update(machines)
        final_count = writer.get_machine_count()
        print(f"  ✅ Inserted: {stats['inserted']}")
        print(f"  🔄 Updated: {stats['updated']}")
        print(f"  🛡️  Existing data preserved: {stats['preserved']} fields")
        print(f"  ⏭️  Skipped (no changes): {stats['skipped']}")
        print(f"  📊 Total in DB: {final_count}")
        if stats["errors"]:
            print(f"  ⚠️  Errors: {stats['errors']}")
    return stats