-- ================================================================
-- PPEPP Penetapan - Checklist Versioning Migration
-- ================================================================
-- Adds versioning support to checklist_items table for PPEPP workflow
-- Database: msdmikim_ami
-- ================================================================

-- Add versioning columns to checklist_items
ALTER TABLE checklist_items
ADD COLUMN IF NOT EXISTS standard_version_id VARCHAR(50) NULL COMMENT 'Links to specific version of standard',
ADD COLUMN IF NOT EXISTS created_in_version VARCHAR(10) NULL COMMENT 'Version number when this item was created (e.g. "1.0")',
ADD COLUMN IF NOT EXISTS is_deleted BOOLEAN DEFAULT FALSE COMMENT 'Soft delete flag for version history';

-- Add index for performance
ALTER TABLE checklist_items
ADD INDEX IF NOT EXISTS idx_std_version (standard_version_id),
ADD INDEX IF NOT EXISTS idx_is_deleted (is_deleted);

-- Migrate existing data: link to active version of their standard
-- This ensures existing checklist items are associated with current active versions
UPDATE checklist_items ci
LEFT JOIN standards s ON ci.standard_id = s.id AND s.is_active = 1
SET 
    ci.standard_version_id = s.id,
    ci.created_in_version = COALESCE(s.version_number, '1.0')
WHERE ci.standard_version_id IS NULL;

-- For checklist items where standard doesn't have is_active flag yet
-- Link them to their standard by ID (backward compatibility)
UPDATE checklist_items ci
LEFT JOIN standards s ON ci.standard_id = s.id
SET 
    ci.standard_version_id = s.id,
    ci.created_in_version = COALESCE(s.version_number, '1.0')
WHERE ci.standard_version_id IS NULL;

-- ================================================================
-- Migration Complete!
-- ================================================================
-- Checklist items now support versioning:
-- - standard_version_id: Links to specific standard version
-- - created_in_version: Tracks which version introduced this item
-- - is_deleted: Soft delete for version history preservation
-- ================================================================