-- ================================================================
-- PPEPP Penetapan - phpMyAdmin Compatible Migration Script
-- ================================================================
-- This version is optimized for phpMyAdmin and handles errors gracefully
-- Database: msdmikim_ami
-- ================================================================

-- Step 1: Add columns to standards table
-- These will be skipped if columns already exist
ALTER TABLE standards 
ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'active',
ADD COLUMN IF NOT EXISTS version_number VARCHAR(10) DEFAULT '1.0',
ADD COLUMN IF NOT EXISTS parent_version_id VARCHAR(50) NULL,
ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT 1,
ADD COLUMN IF NOT EXISTS effective_date DATE NULL,
ADD COLUMN IF NOT EXISTS created_by VARCHAR(50) NOT NULL DEFAULT '1',
ADD COLUMN IF NOT EXISTS approved_by VARCHAR(50) NULL,
ADD COLUMN IF NOT EXISTS approved_at TIMESTAMP NULL;

-- Step 2: Add indexes for performance
-- Note: These will error if indexes already exist, which is safe to ignore
ALTER TABLE standards
ADD INDEX idx_status (status);

ALTER TABLE standards
ADD INDEX idx_version (version_number);

ALTER TABLE standards
ADD INDEX idx_parent (parent_version_id);

ALTER TABLE standards
ADD INDEX idx_effective_date (effective_date);

ALTER TABLE standards
ADD INDEX idx_is_active (is_active);

-- Step 3: Update existing standards to have proper PPEPP fields
UPDATE standards 
SET 
    status = COALESCE(status, 'active'),
    version_number = COALESCE(version_number, '1.0'),
    parent_version_id = NULL,
    is_active = COALESCE(is_active, 1),
    effective_date = COALESCE(effective_date, CURDATE()),
    created_by = COALESCE(created_by, '1'),
    approved_by = COALESCE(approved_by, '1'),
    approved_at = COALESCE(approved_at, NOW())
WHERE status IS NULL OR version_number IS NULL OR is_active IS NULL;

-- ================================================================
-- Migration Complete!
-- ================================================================
-- Note: Foreign key constraints are intentionally NOT added in this version
-- to avoid errors if they already exist. If you need to add them manually:
--
-- ALTER TABLE standards ADD CONSTRAINT fk_parent_version 
-- FOREIGN KEY (parent_version_id) REFERENCES standards(id) ON DELETE SET NULL;
--
-- 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;
-- ================================================================