c19f6fd9ee
- Bump DESK_MOLONI version to 3.0.1 across module - Normalize hooks to after_client_* and instantiate PerfexHooks safely - Fix OAuthController view path and API client class name - Add missing admin views for webhook config/logs; adjust view loading - Harden client portal routes and admin routes mapping - Make Dashboard/Logs/Queue tolerant to optional model methods - Align log details query with existing schema; avoid broken joins This makes the module operational in Perfex (admin + client), reduces 404s, and avoids fatal errors due to inconsistent tables/methods.
83 lines
4.1 KiB
SQL
83 lines
4.1 KiB
SQL
-- Force create all Desk-Moloni tables
|
|
-- Execute this directly in MySQL to create missing tables
|
|
|
|
USE `desk_descomplicar_pt`;
|
|
|
|
-- Create mapping table
|
|
CREATE TABLE IF NOT EXISTS `tbldeskmoloni_mapping` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`entity_type` enum('client','product','invoice','estimate','credit_note') NOT NULL,
|
|
`perfex_id` int(11) NOT NULL,
|
|
`moloni_id` int(11) NOT NULL,
|
|
`sync_direction` enum('perfex_to_moloni','moloni_to_perfex','bidirectional') NOT NULL DEFAULT 'bidirectional',
|
|
`last_sync_at` timestamp NULL DEFAULT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `unique_perfex_mapping` (`entity_type`, `perfex_id`),
|
|
UNIQUE KEY `unique_moloni_mapping` (`entity_type`, `moloni_id`),
|
|
KEY `idx_entity_perfex` (`entity_type`, `perfex_id`),
|
|
KEY `idx_entity_moloni` (`entity_type`, `moloni_id`),
|
|
KEY `idx_last_sync` (`last_sync_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Create sync queue table
|
|
CREATE TABLE IF NOT EXISTS `tbldeskmoloni_sync_queue` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`entity_type` varchar(50) NOT NULL,
|
|
`entity_id` int(11) NOT NULL,
|
|
`perfex_id` int(11) DEFAULT NULL,
|
|
`moloni_id` int(11) DEFAULT NULL,
|
|
`action` enum('create','update','delete','sync') NOT NULL DEFAULT 'sync',
|
|
`direction` enum('perfex_to_moloni','moloni_to_perfex','bidirectional') NOT NULL DEFAULT 'bidirectional',
|
|
`priority` enum('low','normal','high','critical') NOT NULL DEFAULT 'normal',
|
|
`status` enum('pending','processing','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
|
|
`attempts` int(11) NOT NULL DEFAULT 0,
|
|
`max_attempts` int(11) NOT NULL DEFAULT 3,
|
|
`data` longtext DEFAULT NULL COMMENT 'JSON data for sync',
|
|
`error_message` text DEFAULT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`scheduled_at` timestamp NULL DEFAULT NULL,
|
|
`started_at` timestamp NULL DEFAULT NULL,
|
|
`completed_at` timestamp NULL DEFAULT NULL,
|
|
`created_by` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_entity_type_id` (`entity_type`, `entity_id`),
|
|
KEY `idx_status_priority` (`status`, `priority`),
|
|
KEY `idx_scheduled_at` (`scheduled_at`),
|
|
KEY `idx_perfex_id` (`perfex_id`),
|
|
KEY `idx_moloni_id` (`moloni_id`),
|
|
KEY `idx_created_by` (`created_by`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Create sync log table
|
|
CREATE TABLE IF NOT EXISTS `tbldeskmoloni_sync_log` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`operation_type` enum('create','update','delete','status_change') NOT NULL,
|
|
`entity_type` enum('client','product','invoice','estimate','credit_note') NOT NULL,
|
|
`perfex_id` int(11) DEFAULT NULL,
|
|
`moloni_id` int(11) DEFAULT NULL,
|
|
`direction` enum('perfex_to_moloni','moloni_to_perfex') NOT NULL,
|
|
`status` enum('success','error','warning') NOT NULL,
|
|
`request_data` longtext DEFAULT NULL COMMENT 'JSON request data',
|
|
`response_data` longtext DEFAULT NULL COMMENT 'JSON response data',
|
|
`error_message` text DEFAULT NULL,
|
|
`execution_time_ms` int(11) DEFAULT NULL COMMENT 'Execution time in milliseconds',
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_entity_status` (`entity_type`, `status`),
|
|
KEY `idx_perfex_entity` (`perfex_id`, `entity_type`),
|
|
KEY `idx_moloni_entity` (`moloni_id`, `entity_type`),
|
|
KEY `idx_created_at` (`created_at`),
|
|
KEY `idx_status_direction` (`status`, `direction`),
|
|
KEY `idx_log_analytics` (`entity_type`, `operation_type`, `status`, `created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Verify tables were created
|
|
SELECT
|
|
'Tables created successfully!' as status,
|
|
COUNT(*) as table_count
|
|
FROM information_schema.tables
|
|
WHERE table_schema = 'desk_descomplicar_pt'
|
|
AND table_name LIKE 'tbldeskmoloni_%'; |