-- 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_%';