shopdb-flask/migrations/DATA_MIGRATION_GUIDE.md

# ShopDB Flask Data Migration Guide

## Overview

This document describes how to migrate data from the legacy `shopdb` database to the new `shopdb_flask` database schema.

## Database Configuration

**Development:**
- Legacy database: `shopdb` (Classic ASP/VBScript schema)
- New database: `shopdb_flask` (Flask/SQLAlchemy schema)
- Connection: `mysql+pymysql://root:rootpassword@127.0.0.1:3306/shopdb_flask`

**Production:**
- Follow the same migration steps on production MySQL server
- Update connection string in `.env` accordingly

## Schema Differences

### Legacy Schema (shopdb)
- `machines` table holds ALL assets (equipment, PCs, network devices)
- `printers` table is separate
- No unified asset abstraction

### New Schema (shopdb_flask)
- `assets` table: Core asset data (shared fields)
- `assettypes` table: Asset category registry
- Plugin extension tables:
  - `equipment` - Manufacturing equipment details
  - `computers` - PC-specific fields
  - `networkdevices` - Network device details
  - `printers` - Printer-specific fields
- Each extension links to `assets` via `assetid`

## Migration Steps

### Step 1: Seed Reference Data

```bash
cd /home/camp/projects/shopdb-flask
source venv/bin/activate
flask seed reference-data
```

This creates:
- Asset types (equipment, computer, network_device, printer)
- Asset statuses (In Use, Spare, Retired, etc.)
- Machine types, operating systems, relationship types

### Step 2: Migrate Asset Types

```sql
-- Insert asset types if not exists
INSERT INTO assettypes (assettype, pluginname, tablename, description) VALUES
('equipment', 'equipment', 'equipment', 'Manufacturing equipment'),
('computer', 'computers', 'computers', 'PCs and workstations'),
('network_device', 'network', 'networkdevices', 'Network infrastructure'),
('printer', 'printers', 'printers', 'Printers and MFPs')
ON DUPLICATE KEY UPDATE assettype=assettype;
```

### Step 3: Migrate Equipment

```sql
-- Migrate equipment from legacy machines table
INSERT INTO assets (assetid, assetnumber, name, serialnumber, assettypeid, statusid,
                    locationid, businessunitid, mapleft, maptop, notes,
                    createddate, modifieddate, isactive)
SELECT
    m.machineid,
    m.machinenumber,
    m.alias,
    m.serialnumber,
    (SELECT assettypeid FROM assettypes WHERE assettype = 'equipment'),
    m.statusid,
    m.locationid,
    m.businessunitid,
    m.mapleft,
    m.maptop,
    m.notes,
    m.createddate,
    m.modifieddate,
    m.isactive
FROM shopdb.machines m
JOIN shopdb.machinetypes mt ON m.machinetypeid = mt.machinetypeid
WHERE mt.category = 'Equipment'
  AND m.pctypeid IS NULL;

-- Insert equipment extension data
INSERT INTO equipment (assetid, equipmenttypeid, vendorid, modelnumberid,
                       controllertypeid, controllervendorid, controllermodelid)
SELECT
    m.machineid,
    m.machinetypeid,
    m.vendorid,
    m.modelnumberid,
    m.controllertypeid,
    m.controllervendorid,
    m.controllermodelid
FROM shopdb.machines m
JOIN shopdb.machinetypes mt ON m.machinetypeid = mt.machinetypeid
WHERE mt.category = 'Equipment'
  AND m.pctypeid IS NULL;
```

### Step 4: Migrate PCs/Computers

```sql
-- Migrate PCs to assets table
INSERT INTO assets (assetid, assetnumber, name, serialnumber, assettypeid, statusid,
                    locationid, businessunitid, mapleft, maptop, notes,
                    createddate, modifieddate, isactive)
SELECT
    m.machineid,
    m.machinenumber,
    m.alias,
    m.serialnumber,
    (SELECT assettypeid FROM assettypes WHERE assettype = 'computer'),
    m.statusid,
    m.locationid,
    m.businessunitid,
    m.mapleft,
    m.maptop,
    m.notes,
    m.createddate,
    m.modifieddate,
    m.isactive
FROM shopdb.machines m
WHERE m.pctypeid IS NOT NULL;

-- Insert computer extension data
INSERT INTO computers (assetid, computertypeid, hostname, osid,
                       loggedinuser, lastreporteddate, lastboottime,
                       isvnc, iswinrm, isshopfloor)
SELECT
    m.machineid,
    m.pctypeid,
    m.hostname,
    m.osid,
    m.loggedinuser,
    m.lastreporteddate,
    m.lastboottime,
    m.isvnc,
    m.iswinrm,
    m.isshopfloor
FROM shopdb.machines m
WHERE m.pctypeid IS NOT NULL;
```

### Step 5: Migrate Network Devices

```sql
-- Migrate network devices to assets
INSERT INTO assets (assetid, assetnumber, name, serialnumber, assettypeid, statusid,
                    locationid, businessunitid, mapleft, maptop, notes,
                    createddate, modifieddate, isactive)
SELECT
    m.machineid,
    m.machinenumber,
    m.alias,
    m.serialnumber,
    (SELECT assettypeid FROM assettypes WHERE assettype = 'network_device'),
    m.statusid,
    m.locationid,
    m.businessunitid,
    m.mapleft,
    m.maptop,
    m.notes,
    m.createddate,
    m.modifieddate,
    m.isactive
FROM shopdb.machines m
JOIN shopdb.machinetypes mt ON m.machinetypeid = mt.machinetypeid
WHERE mt.category = 'Network';

-- Insert network device extension data
INSERT INTO networkdevices (assetid, networkdevicetypeid, hostname, vendorid, modelnumberid)
SELECT
    m.machineid,
    m.machinetypeid,
    m.hostname,
    m.vendorid,
    m.modelnumberid
FROM shopdb.machines m
JOIN shopdb.machinetypes mt ON m.machinetypeid = mt.machinetypeid
WHERE mt.category = 'Network';
```

### Step 6: Migrate Printers

```sql
-- Migrate printers to assets (printers are in separate table in legacy)
INSERT INTO assets (assetnumber, name, serialnumber, assettypeid, statusid,
                    locationid, businessunitid, notes, createddate, modifieddate, isactive)
SELECT
    p.hostname,
    p.windowsname,
    NULL,
    (SELECT assettypeid FROM assettypes WHERE assettype = 'printer'),
    1, -- Default status
    p.locationid,
    p.businessunitid,
    NULL,
    p.createddate,
    p.modifieddate,
    p.isactive
FROM shopdb.printers p;

-- Insert printer extension data (need to get the new assetid)
INSERT INTO printers (assetid, printertypeid, vendorid, modelnumberid, hostname,
                      windowsname, sharename, iscsf, installpath, pin,
                      iscolor, isduplex, isnetwork)
SELECT
    a.assetid,
    p.printertypeid,
    p.vendorid,
    p.modelnumberid,
    p.hostname,
    p.windowsname,
    p.sharename,
    p.iscsf,
    p.installpath,
    p.pin,
    p.iscolor,
    p.isduplex,
    p.isnetwork
FROM shopdb.printers p
JOIN assets a ON a.assetnumber = p.hostname
WHERE a.assettypeid = (SELECT assettypeid FROM assettypes WHERE assettype = 'printer');
```

### Step 7: Migrate Communications (IP Addresses)

```sql
-- Migrate communications/IP addresses
INSERT INTO communications (machineid, assetid, comtypeid, address,
                            subnetid, isprimary, createddate, modifieddate, isactive)
SELECT
    c.machineid,
    c.machineid, -- assetid = machineid for migrated assets
    c.comtypeid,
    c.address,
    c.subnetid,
    c.isprimary,
    c.createddate,
    c.modifieddate,
    c.isactive
FROM shopdb.communications c;
```

### Step 8: Migrate Notifications

```sql
INSERT INTO notifications (notificationid, notificationtypeid, businessunitid, appid,
                           notification, starttime, endtime, ticketnumber, link,
                           isactive, isshopfloor, employeesso, employeename)
SELECT * FROM shopdb.notifications;
```

### Step 9: Migrate Supporting Tables

```sql
-- Vendors
INSERT INTO vendors SELECT * FROM shopdb.vendors
ON DUPLICATE KEY UPDATE vendor=VALUES(vendor);

-- Models
INSERT INTO models SELECT * FROM shopdb.models
ON DUPLICATE KEY UPDATE modelnumber=VALUES(modelnumber);

-- Locations
INSERT INTO locations SELECT * FROM shopdb.locations
ON DUPLICATE KEY UPDATE locationname=VALUES(locationname);

-- Business Units
INSERT INTO businessunits SELECT * FROM shopdb.businessunits
ON DUPLICATE KEY UPDATE businessunit=VALUES(businessunit);

-- Subnets
INSERT INTO subnets SELECT * FROM shopdb.subnets
ON DUPLICATE KEY UPDATE subnet=VALUES(subnet);
```

## Verification Queries

```sql
-- Check migration counts
SELECT 'Legacy machines' as source, COUNT(*) as cnt FROM shopdb.machines
UNION ALL SELECT 'New assets', COUNT(*) FROM shopdb_flask.assets
UNION ALL SELECT 'Equipment', COUNT(*) FROM shopdb_flask.equipment
UNION ALL SELECT 'Computers', COUNT(*) FROM shopdb_flask.computers
UNION ALL SELECT 'Network devices', COUNT(*) FROM shopdb_flask.networkdevices
UNION ALL SELECT 'Printers', COUNT(*) FROM shopdb_flask.printers;

-- Verify asset type distribution
SELECT at.assettype, COUNT(a.assetid) as count
FROM shopdb_flask.assets a
JOIN shopdb_flask.assettypes at ON a.assettypeid = at.assettypeid
GROUP BY at.assettype;
```

## Production Deployment Notes

1. **Backup production database first**
2. Create `shopdb_flask` database on production
3. Run `flask db-utils create-all` to create schema
4. Execute migration SQL scripts in order
5. Verify data counts match
6. Update Flask `.env` to point to production database
7. Restart Flask services

## Rollback

If migration fails:
1. Drop all tables in `shopdb_flask`: `flask db-utils drop-all`
2. Recreate schema: `flask db-utils create-all`
3. Investigate and fix migration scripts
4. Re-run migration

---
Last updated: 2026-01-28