-- ================================================================
-- Citizen System - ADD INSTALL_CODE COLUMN
-- Created: November 28, 2025
-- Description: Adds install_code column to web_users_new table
-- ================================================================

USE pc_citizen;

-- Add install_code column if not exists
ALTER TABLE web_users_new 
ADD COLUMN IF NOT EXISTS install_code VARCHAR(20) DEFAULT NULL,
ADD INDEX idx_install_code (install_code);

-- Generate random 10-character alphanumeric codes for existing users
UPDATE web_users_new 
SET install_code = CONCAT(
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', FLOOR(1 + RAND() * 62), 1)
)
WHERE install_code IS NULL;

SELECT 'Install codes added successfully' AS status;
