ALTER TABLE payments MODIFY method ENUM('paystack','bank_transfer','cash','legacy_import') NOT NULL;

CREATE TABLE IF NOT EXISTS legacy_mappings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 entity_type VARCHAR(50) NOT NULL,
 legacy_table VARCHAR(100) NOT NULL,
 legacy_id VARCHAR(100) NOT NULL,
 new_id BIGINT UNSIGNED NOT NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY unique_legacy_record(entity_type,legacy_table,legacy_id),
 KEY entity_new_id(entity_type,new_id)
);

CREATE TABLE IF NOT EXISTS migration_issues (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 entity_type VARCHAR(50) NOT NULL,
 legacy_table VARCHAR(100) NOT NULL,
 legacy_id VARCHAR(100) NOT NULL,
 issue_code VARCHAR(80) NOT NULL,
 message TEXT NOT NULL,
 legacy_payload JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY issue_lookup(entity_type,issue_code)
);
