USE db_abos_v0.1;

-- Fixes QA Bug #3: the UserModuleAccess JPA entity expects columns `module`,
-- `is_deleted`, `created_at`, `updated_at` on user_module_access, but the table
-- as originally created only had (id, user_id, module_name, access_level,
-- tenant_id). Every existsBy.../findBy...IsDeletedFalse query threw
-- "Unknown column" and TENANT_MANAGER was locked out of every module-gated
-- endpoint as a result. Align the table to the entity rather than the other
-- way around, since grant/revoke logic in TenantModuleService depends on
-- soft-delete semantics (revoke = is_deleted=1, not a hard delete).

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'module_name'
);
SET @sql = IF(@col_exists > 0,
    'ALTER TABLE user_module_access CHANGE COLUMN module_name module VARCHAR(50) NOT NULL',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'created_at'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE user_module_access ADD COLUMN created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'updated_at'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE user_module_access ADD COLUMN updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'is_deleted'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE user_module_access ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;