-- Migration: Add Version Control for Risk Assessments
-- Tracks assessment revisions and enables version history

USE scubatricky_risk;

-- Assessment versions table
CREATE TABLE IF NOT EXISTS assessment_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    version_number INT NOT NULL,
    assessment_number VARCHAR(50) NOT NULL,
    location_id INT NOT NULL,
    assessor_id INT NOT NULL,
    assessment_date DATE NOT NULL,
    assessment_time TIME NOT NULL,
    weather_conditions VARCHAR(100),
    vehicle_type VARCHAR(50),
    delivery_volume VARCHAR(50),
    overall_risk_level ENUM('low', 'medium', 'high', 'critical') NOT NULL,
    status ENUM('draft', 'completed', 'reviewed', 'archived', 'superseded') DEFAULT 'draft',
    notes TEXT,
    recommendations TEXT,
    
    -- Version metadata
    version_type ENUM('original', 'revision', 'major_revision') DEFAULT 'revision',
    revision_reason TEXT,
    changes_summary TEXT,
    is_current BOOLEAN DEFAULT 0,
    supersedes_version INT,
    
    -- Audit fields
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
    FOREIGN KEY (assessor_id) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (supersedes_version) REFERENCES assessment_versions(id) ON DELETE SET NULL,
    
    INDEX idx_assessment_version (assessment_id, version_number),
    INDEX idx_current (assessment_id, is_current),
    INDEX idx_status (status),
    UNIQUE KEY unique_assessment_version (assessment_id, version_number)
) ENGINE=InnoDB;

-- Version details (risk items for each version)
CREATE TABLE IF NOT EXISTS assessment_version_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version_id INT NOT NULL,
    risk_item_id INT NOT NULL,
    likelihood INT NOT NULL,
    severity INT NOT NULL,
    risk_score INT NOT NULL,
    current_controls TEXT,
    additional_actions TEXT,
    action_owner VARCHAR(100),
    target_date DATE,
    
    FOREIGN KEY (version_id) REFERENCES assessment_versions(id) ON DELETE CASCADE,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id),
    INDEX idx_version (version_id)
) ENGINE=InnoDB;

-- Version photos (link photos to versions)
CREATE TABLE IF NOT EXISTS assessment_version_photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version_id INT NOT NULL,
    photo_id INT NOT NULL,
    
    FOREIGN KEY (version_id) REFERENCES assessment_versions(id) ON DELETE CASCADE,
    FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_version_photo (version_id, photo_id)
) ENGINE=InnoDB;

-- Assessment change log
CREATE TABLE IF NOT EXISTS assessment_change_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    from_version INT,
    to_version INT NOT NULL,
    action_type ENUM('created', 'revised', 'archived', 'restored', 'superseded') NOT NULL,
    change_description TEXT,
    changed_by INT NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (from_version) REFERENCES assessment_versions(id) ON DELETE SET NULL,
    FOREIGN KEY (to_version) REFERENCES assessment_versions(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id),
    INDEX idx_assessment (assessment_id),
    INDEX idx_date (changed_at)
) ENGINE=InnoDB;

-- Archive metadata
CREATE TABLE IF NOT EXISTS assessment_archives (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    version_id INT NOT NULL,
    archive_reason TEXT NOT NULL,
    archived_by INT NOT NULL,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    can_restore BOOLEAN DEFAULT 1,
    restore_notes TEXT,
    
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (version_id) REFERENCES assessment_versions(id) ON DELETE CASCADE,
    FOREIGN KEY (archived_by) REFERENCES users(id),
    INDEX idx_assessment (assessment_id),
    INDEX idx_archived_at (archived_at)
) ENGINE=InnoDB;

-- Migrate existing assessments to version 1
INSERT INTO assessment_versions (
    assessment_id, version_number, assessment_number, location_id, assessor_id,
    assessment_date, assessment_time, weather_conditions, vehicle_type, delivery_volume,
    overall_risk_level, status, notes, recommendations,
    version_type, revision_reason, is_current, created_by, created_at
)
SELECT 
    id, 1, assessment_number, location_id, assessor_id,
    assessment_date, assessment_time, weather_conditions, vehicle_type, delivery_volume,
    overall_risk_level, status, notes, recommendations,
    'original', 'Initial assessment', 1, assessor_id, created_at
FROM assessments
WHERE NOT EXISTS (
    SELECT 1 FROM assessment_versions av WHERE av.assessment_id = assessments.id
);

-- Migrate existing assessment details to version details
INSERT INTO assessment_version_details (
    version_id, risk_item_id, likelihood, severity, risk_score,
    current_controls, additional_actions, action_owner, target_date
)
SELECT 
    av.id, ad.risk_item_id, ad.likelihood, ad.severity, ad.risk_score,
    ad.current_controls, ad.additional_actions, ad.action_owner, ad.target_date
FROM assessment_details ad
JOIN assessments a ON ad.assessment_id = a.id
JOIN assessment_versions av ON av.assessment_id = a.id AND av.version_number = 1
WHERE NOT EXISTS (
    SELECT 1 FROM assessment_version_details avd 
    WHERE avd.version_id = av.id AND avd.risk_item_id = ad.risk_item_id
);

-- Migrate existing photos to version photos
INSERT INTO assessment_version_photos (version_id, photo_id)
SELECT av.id, p.id
FROM photos p
JOIN assessments a ON p.assessment_id = a.id
JOIN assessment_versions av ON av.assessment_id = a.id AND av.version_number = 1
WHERE NOT EXISTS (
    SELECT 1 FROM assessment_version_photos avp 
    WHERE avp.version_id = av.id AND avp.photo_id = p.id
);

-- Create initial change log entries
INSERT INTO assessment_change_log (assessment_id, to_version, action_type, change_description, changed_by)
SELECT 
    a.id, av.id, 'created', 'Initial assessment created', a.assessor_id
FROM assessments a
JOIN assessment_versions av ON av.assessment_id = a.id AND av.version_number = 1
WHERE NOT EXISTS (
    SELECT 1 FROM assessment_change_log acl WHERE acl.assessment_id = a.id
);

-- Add version tracking to main assessments table
ALTER TABLE assessments 
ADD COLUMN IF NOT EXISTS current_version_id INT,
ADD COLUMN IF NOT EXISTS version_count INT DEFAULT 1,
ADD COLUMN IF NOT EXISTS last_revised_at TIMESTAMP NULL,
ADD COLUMN IF NOT EXISTS last_revised_by INT,
ADD FOREIGN KEY (current_version_id) REFERENCES assessment_versions(id) ON DELETE SET NULL,
ADD FOREIGN KEY (last_revised_by) REFERENCES users(id) ON DELETE SET NULL;

-- Update current version references
UPDATE assessments a
JOIN assessment_versions av ON av.assessment_id = a.id AND av.is_current = 1
SET a.current_version_id = av.id
WHERE a.current_version_id IS NULL;

SELECT 'Version control system installed successfully!' AS status;
SELECT COUNT(*) as version_count FROM assessment_versions;
SELECT COUNT(*) as detail_count FROM assessment_version_details;
