-- Migration: Upgrade password/PIN storage from legacy hash to bcrypt
-- Date: 2026-03-20
--
-- This migration widens columns to hold bcrypt hashes (60 chars)
-- and adds flag columns to track which rows have been migrated.
-- All changes are additive and non-destructive.

-- ============================================================
-- Table: subscribers (user passwords and PINs)
-- ============================================================

-- Widen sPass to hold bcrypt hashes (currently holds 10-char legacy hashes)
ALTER TABLE subscribers MODIFY COLUMN sPass VARCHAR(255) NOT NULL DEFAULT '';

-- Flag: 1 = sPass contains bcrypt, 0 = legacy hash (transparent migration on login)
ALTER TABLE subscribers ADD COLUMN sPassBcrypt TINYINT(1) NOT NULL DEFAULT 0 AFTER sPass;

-- Widen sPin to hold bcrypt hashes (currently stores raw plaintext numeric values)
ALTER TABLE subscribers MODIFY COLUMN sPin VARCHAR(255) DEFAULT '0';

-- Flag: 1 = sPin contains bcrypt, 0 = legacy plaintext or hash
ALTER TABLE subscribers ADD COLUMN sPinBcrypt TINYINT(1) NOT NULL DEFAULT 0 AFTER sPin;

-- ============================================================
-- Table: sysusers (admin passwords and PINs)
-- ============================================================

-- sysPassword already exists for bcrypt; ensure it's wide enough
ALTER TABLE sysusers MODIFY COLUMN sysPassword VARCHAR(255) NULL;

-- Widen sysPinToken for bcrypt
ALTER TABLE sysusers MODIFY COLUMN sysPinToken VARCHAR(255) NULL;

-- Flag: 1 = sysPinToken contains bcrypt, 0 = legacy hash
ALTER TABLE sysusers ADD COLUMN sysPinBcrypt TINYINT(1) NOT NULL DEFAULT 0 AFTER sysPinToken;
