-- Script Uploads Table
CREATE TABLE IF NOT EXISTS script_uploads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    script_id INT NOT NULL,
    version VARCHAR(50) NOT NULL,
    uploaded_by INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT NOT NULL,
    status ENUM('pending', 'analyzing', 'ready', 'deploying', 'deployed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    comparison_data JSON NULL,
    deploy_notes TEXT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    analyzed_at TIMESTAMP NULL,
    deployed_at TIMESTAMP NULL,
    INDEX idx_script_id (script_id),
    INDEX idx_uploaded_by (uploaded_by),
    INDEX idx_status (status),
    INDEX idx_uploaded_at (uploaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Deploy History Table
CREATE TABLE IF NOT EXISTS deploy_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    upload_id INT NOT NULL,
    script_id INT NOT NULL,
    deployed_by INT NOT NULL,
    version_from VARCHAR(50) NULL,
    version_to VARCHAR(50) NOT NULL,
    backup_path VARCHAR(500) NULL,
    status ENUM('in_progress', 'success', 'failed', 'rolled_back') NOT NULL DEFAULT 'in_progress',
    deploy_log TEXT NULL,
    error_message TEXT NULL,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    INDEX idx_upload_id (upload_id),
    INDEX idx_script_id (script_id),
    INDEX idx_deployed_by (deployed_by),
    INDEX idx_status (status),
    INDEX idx_started_at (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- File Changes Table (for detailed comparison)
CREATE TABLE IF NOT EXISTS file_changes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    upload_id INT NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    change_type ENUM('added', 'modified', 'deleted', 'renamed') NOT NULL,
    old_size BIGINT NULL,
    new_size BIGINT NULL,
    diff_summary TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_upload_id (upload_id),
    INDEX idx_change_type (change_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
