-- Create Desk-Moloni mapping table
-- This script creates the missing tbldeskmoloni_mapping table

USE `desk_descomplicar_pt`;

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;

-- Verify table was created
SELECT 'Table created successfully!' as status;
DESCRIBE `tbldeskmoloni_mapping`;