-- Migration: Rental contract — terms sections + saved snapshots + handover note
-- Date: 2026-03-30
-- Idempotent: safe to run more than once.

-- --- rental_terms: structured sections (EN/AR) ---
SET @db = DATABASE();

SET @exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'rental_terms' AND COLUMN_NAME = 'damage_policy_en');
SET @sql = IF(@exists = 0, 'ALTER TABLE `rental_terms` ADD COLUMN `damage_policy_en` TEXT NULL AFTER `content_ar`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'rental_terms' AND COLUMN_NAME = 'damage_policy_ar');
SET @sql = IF(@exists = 0, 'ALTER TABLE `rental_terms` ADD COLUMN `damage_policy_ar` TEXT NULL AFTER `damage_policy_en`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'rental_terms' AND COLUMN_NAME = 'liability_en');
SET @sql = IF(@exists = 0, 'ALTER TABLE `rental_terms` ADD COLUMN `liability_en` TEXT NULL AFTER `damage_policy_ar`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'rental_terms' AND COLUMN_NAME = 'liability_ar');
SET @sql = IF(@exists = 0, 'ALTER TABLE `rental_terms` ADD COLUMN `liability_ar` TEXT NULL AFTER `liability_en`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'rental_terms' AND COLUMN_NAME = 'late_terms_en');
SET @sql = IF(@exists = 0, 'ALTER TABLE `rental_terms` ADD COLUMN `late_terms_en` TEXT NULL AFTER `liability_ar`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'rental_terms' AND COLUMN_NAME = 'late_terms_ar');
SET @sql = IF(@exists = 0, 'ALTER TABLE `rental_terms` ADD COLUMN `late_terms_ar` TEXT NULL AFTER `late_terms_en`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- --- rentals: immutable contract text + handover ---
SET @exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'rentals' AND COLUMN_NAME = 'contract_snapshot_en');
SET @sql = IF(@exists = 0, 'ALTER TABLE `rentals` ADD COLUMN `contract_snapshot_en` LONGTEXT NULL AFTER `terms_accepted_at`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'rentals' AND COLUMN_NAME = 'contract_snapshot_ar');
SET @sql = IF(@exists = 0, 'ALTER TABLE `rentals` ADD COLUMN `contract_snapshot_ar` LONGTEXT NULL AFTER `contract_snapshot_en`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'rentals' AND COLUMN_NAME = 'condition_at_handover');
SET @sql = IF(@exists = 0, 'ALTER TABLE `rentals` ADD COLUMN `condition_at_handover` TEXT NULL AFTER `contract_snapshot_ar`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Permission for editing rental terms (non-Admin users need role assignment)
INSERT IGNORE INTO permissions (code, description) VALUES
('settings.update', 'Update system settings and rental contract templates');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE code = 'settings.update';