-- ================================================================
-- PPEPP Penetapan - Database Migration Script
-- ================================================================
-- This script adds versioning and workflow capabilities to the standards table

-- Add new columns for PPEPP Penetapan workflow
ALTER TABLE standards 
ADD COLUMN status VARCHAR(20) DEFAULT 'active',
ADD COLUMN version_number VARCHAR(10) DEFAULT '1.0',
ADD COLUMN parent_version_id VARCHAR(50) NULL,
ADD COLUMN effective_date DATE NULL,
ADD COLUMN created_by VARCHAR(50) NOT NULL DEFAULT '1',
ADD COLUMN approved_by VARCHAR(50) NULL,
ADD COLUMN approved_at TIMESTAMP NULL,
ADD COLUMN is_active BOOLEAN DEFAULT 1;

-- Add foreign key constraint for version parent-child relationship
ALTER TABLE standards
ADD CONSTRAINT fk_parent_version 
FOREIGN KEY (parent_version_id) REFERENCES standards(id) ON DELETE SET NULL;

-- Add foreign key constraints for user tracking
ALTER TABLE standards
ADD CONSTRAINT fk_created_by 
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT;

ALTER TABLE standards
ADD CONSTRAINT fk_approved_by 
FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE RESTRICT;

-- Add indexes for performance
ALTER TABLE standards
ADD INDEX idx_status (status),
ADD INDEX idx_version (version_number),
ADD INDEX idx_parent (parent_version_id),
ADD INDEX idx_active (is_active),
ADD INDEX idx_effective_date (effective_date);

-- Update existing standards to be 'active' version 1.0
-- This ensures backward compatibility
UPDATE standards 
SET 
    status = 'active',
    version_number = '1.0',
    parent_version_id = NULL,
    effective_date = CURDATE(),
    created_by = '1',  -- Default to Admin SPM
    approved_by = '1',
    approved_at = NOW(),
    is_active = TRUE
WHERE status IS NULL OR status = 'active';

-- ================================================================
-- Migration Complete!
-- All existing standards are now version 1.0 and active.
-- ================================================================