-- =============================================================================
-- Legacy Schema Migration Script
-- Adds missing columns to make VBScript ShopDB compatible with Flask ShopDB
-- =============================================================================

-- -----------------------------------------------------------------------------
-- appowners table
-- -----------------------------------------------------------------------------
ALTER TABLE appowners
  ADD COLUMN IF NOT EXISTS email VARCHAR(100) NULL,
  ADD COLUMN IF NOT EXISTS phone VARCHAR(50) NULL,
  ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS isactive TINYINT(1) DEFAULT 1;

-- -----------------------------------------------------------------------------
-- pctypes / computertypes alignment
-- -----------------------------------------------------------------------------
-- The Flask app uses pctype table but expects certain columns
ALTER TABLE pctype
  ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW();

-- Ensure isactive is correct type
ALTER TABLE pctype MODIFY COLUMN isactive TINYINT(1) DEFAULT 1;

-- -----------------------------------------------------------------------------
-- statuses table (if needed - Flask uses assetstatuses)
-- -----------------------------------------------------------------------------
-- assetstatuses already populated earlier

-- -----------------------------------------------------------------------------
-- subnets table
-- -----------------------------------------------------------------------------
ALTER TABLE subnets
  ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS isactive TINYINT(1) DEFAULT 1;

-- -----------------------------------------------------------------------------
-- vlans table
-- -----------------------------------------------------------------------------
ALTER TABLE vlans
  ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS isactive TINYINT(1) DEFAULT 1;

-- -----------------------------------------------------------------------------
-- usbdevices table
-- -----------------------------------------------------------------------------
ALTER TABLE usbdevices
  ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS isactive TINYINT(1) DEFAULT 1;

-- -----------------------------------------------------------------------------
-- usbcheckouts table
-- -----------------------------------------------------------------------------
ALTER TABLE usbcheckouts
  ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW();

-- -----------------------------------------------------------------------------
-- notifications table
-- -----------------------------------------------------------------------------
ALTER TABLE notifications
  ADD COLUMN IF NOT EXISTS createddate DATETIME DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS modifieddate DATETIME DEFAULT NOW();

-- Copy dates from existing columns if they exist
UPDATE notifications SET createddate = startdate WHERE createddate IS NULL;
UPDATE notifications SET modifieddate = startdate WHERE modifieddate IS NULL;

-- -----------------------------------------------------------------------------
-- Verify key counts
-- -----------------------------------------------------------------------------
SELECT 'Migration complete. Record counts:' as status;
SELECT 'vendors' as tbl, COUNT(*) as cnt FROM vendors
UNION ALL SELECT 'models', COUNT(*) FROM models
UNION ALL SELECT 'machinetypes', COUNT(*) FROM machinetypes
UNION ALL SELECT 'operatingsystems', COUNT(*) FROM operatingsystems
UNION ALL SELECT 'businessunits', COUNT(*) FROM businessunits
UNION ALL SELECT 'applications', COUNT(*) FROM applications
UNION ALL SELECT 'machines', COUNT(*) FROM machines
UNION ALL SELECT 'printers', COUNT(*) FROM printers
UNION ALL SELECT 'assets', COUNT(*) FROM assets
UNION ALL SELECT 'knowledgebase', COUNT(*) FROM knowledgebase
UNION ALL SELECT 'notifications', COUNT(*) FROM notifications;