-- Desk-Moloni v3.0 Table Naming Convention Fix
-- Renames tables to follow Perfex CRM naming convention (removes underscore to avoid SQL conflicts)
-- Date: 2025-09-10

-- Rename tables if they exist with the old naming convention
-- This migration ensures compatibility with Perfex CRM's table naming standards

-- Check and rename config table
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE desk_moloni_config TO tbldeskmoloni_config;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'desk_moloni_config';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and rename from intermediate naming if exists
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE tbldesk_moloni_config TO tbldeskmoloni_config;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'tbldesk_moloni_config';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and rename mapping table
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE desk_moloni_mapping TO tbldeskmoloni_mapping;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'desk_moloni_mapping';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and rename from intermediate naming if exists
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE tbldesk_moloni_mapping TO tbldeskmoloni_mapping;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'tbldesk_moloni_mapping';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and rename sync_queue table
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE desk_moloni_sync_queue TO tbldeskmoloni_sync_queue;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'desk_moloni_sync_queue';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and rename from intermediate naming if exists
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE tbldesk_moloni_sync_queue TO tbldeskmoloni_sync_queue;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'tbldesk_moloni_sync_queue';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and rename sync_log table
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE desk_moloni_sync_log TO tbldeskmoloni_sync_log;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'desk_moloni_sync_log';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and rename from intermediate naming if exists
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE tbldesk_moloni_sync_log TO tbldeskmoloni_sync_log;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'tbldesk_moloni_sync_log';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and rename audit_log table if it exists
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE desk_moloni_audit_log TO tbldeskmoloni_audit_log;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'desk_moloni_audit_log';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and rename from intermediate naming if exists
SET @sql = NULL;
SELECT CONCAT('RENAME TABLE tbldesk_moloni_audit_log TO tbldeskmoloni_audit_log;') INTO @sql
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name = 'tbldesk_moloni_audit_log';
  
PREPARE stmt FROM COALESCE(@sql, 'SELECT 1');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;