-- ================================================================
-- PPEPP Penetapan - Database Migration Script (CORRECTED)
-- ================================================================
-- Only add columns that don't exist yet

-- Add missing columns for PPEPP Penetapan workflow
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 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;

-- Add foreign key constraints (use IF NOT EXISTS where supported)
-- Note: MySQL doesn't support IF NOT EXISTS for constraints, so we'll handle errors gracefully

-- Try to add foreign key for parent version
SET @query = 'ALTER TABLE standards ADD CONSTRAINT fk_parent_version 
FOREIGN KEY (parent_version_id) REFERENCES standards(id) ON DELETE SET NULL';
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Try to add foreign key for created_by
SET @query = 'ALTER TABLE standards ADD CONSTRAINT fk_created_by 
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT';
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Try to add foreign key for approved_by
SET @query = 'ALTER TABLE standards ADD CONSTRAINT fk_approved_by 
FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE RESTRICT';
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

-- 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,
    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;

-- ================================================================
-- Migration Complete!
-- ================================================================