-- Desk-Moloni v3.0 Database Schema Migration
-- Creates all required tables for bidirectional Perfex CRM and Moloni ERP integration
-- Date: 2025-09-10

-- Configuration table for secure storage of API credentials and module settings
CREATE TABLE tbldeskmoloni_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(255) NOT NULL UNIQUE,
    setting_value TEXT,
    encrypted TINYINT(1) DEFAULT 0 COMMENT 'Flag indicating if value is AES-256 encrypted',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Secure storage of API credentials and module configuration';

-- Bidirectional entity mapping between Perfex and Moloni
CREATE TABLE tbldeskmoloni_mapping (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('client', 'product', 'invoice', 'estimate', 'credit_note') NOT NULL,
    perfex_id INT NOT NULL,
    moloni_id INT NOT NULL,
    sync_direction ENUM('perfex_to_moloni', 'moloni_to_perfex', 'bidirectional') DEFAULT 'bidirectional',
    last_sync_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_perfex_mapping (entity_type, perfex_id),
    UNIQUE KEY unique_moloni_mapping (entity_type, moloni_id),
    INDEX idx_entity_perfex (entity_type, perfex_id),
    INDEX idx_entity_moloni (entity_type, moloni_id),
    INDEX idx_last_sync (last_sync_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Bidirectional entity mapping between Perfex and Moloni';

-- Asynchronous task queue for synchronization operations
CREATE TABLE tbldeskmoloni_sync_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_type ENUM('sync_client', 'sync_product', 'sync_invoice', 'sync_estimate', 'sync_credit_note', 'status_update') NOT NULL,
    entity_type ENUM('client', 'product', 'invoice', 'estimate', 'credit_note') NOT NULL,
    entity_id INT NOT NULL,
    priority TINYINT DEFAULT 5 COMMENT 'Task priority (1=highest, 9=lowest)',
    payload JSON COMMENT 'Task execution data and parameters',
    status ENUM('pending', 'processing', 'completed', 'failed', 'retry') DEFAULT 'pending',
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    scheduled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status_priority (status, priority, scheduled_at),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_scheduled (scheduled_at),
    INDEX idx_status_attempts (status, attempts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Asynchronous task queue for synchronization operations';

-- Comprehensive audit log of all synchronization operations
CREATE TABLE tbldeskmoloni_sync_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation_type ENUM('create', 'update', 'delete', 'status_change') NOT NULL,
    entity_type ENUM('client', 'product', 'invoice', 'estimate', 'credit_note') NOT NULL,
    perfex_id INT NULL,
    moloni_id INT NULL,
    direction ENUM('perfex_to_moloni', 'moloni_to_perfex') NOT NULL,
    status ENUM('success', 'error', 'warning') NOT NULL,
    request_data JSON COMMENT 'Full API request for debugging',
    response_data JSON COMMENT 'Full API response for debugging',
    error_message TEXT NULL,
    execution_time_ms INT COMMENT 'Performance monitoring',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_entity_status (entity_type, status, created_at),
    INDEX idx_perfex_entity (perfex_id, entity_type),
    INDEX idx_moloni_entity (moloni_id, entity_type),
    INDEX idx_created_at (created_at),
    INDEX idx_status_direction (status, direction)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Comprehensive audit log of all synchronization operations';

-- Insert initial configuration values
INSERT INTO tbldeskmoloni_config (setting_key, setting_value, encrypted) VALUES
('module_version', '3.0.0', 0),
('api_base_url', 'https://api.moloni.pt/v1', 0),
('oauth_redirect_uri', '', 0),
('oauth_client_id', '', 1),
('oauth_client_secret', '', 1),
('oauth_access_token', '', 1),
('oauth_refresh_token', '', 1),
('oauth_token_expires_at', '', 1),
('moloni_company_id', '', 1),
('rate_limit_requests_per_minute', '60', 0),
('sync_batch_size', '50', 0),
('queue_processing_interval', '60', 0),
('pdf_storage_path', 'uploads/desk_moloni/pdfs/', 0),
('encryption_key_version', '1', 0),
('last_system_health_check', '', 0);

-- Create indexes for performance optimization
ALTER TABLE tbldeskmoloni_sync_queue 
ADD INDEX idx_queue_processing (status, priority, scheduled_at, attempts) COMMENT 'Optimized index for queue processing queries';

ALTER TABLE tbldeskmoloni_sync_log 
ADD INDEX idx_log_analytics (created_at, status, entity_type, execution_time_ms) COMMENT 'Optimized index for analytics and reporting';

-- Add foreign key constraints for data integrity (if Perfex allows)
-- Note: These may need to be adjusted based on Perfex CRM table structure
-- ALTER TABLE desk_moloni_mapping ADD CONSTRAINT fk_perfex_client FOREIGN KEY (perfex_id) REFERENCES tblclients(userid) ON DELETE CASCADE;
-- ALTER TABLE desk_moloni_mapping ADD CONSTRAINT fk_perfex_product FOREIGN KEY (perfex_id) REFERENCES tblitems(id) ON DELETE CASCADE;
-- ALTER TABLE desk_moloni_mapping ADD CONSTRAINT fk_perfex_invoice FOREIGN KEY (perfex_id) REFERENCES tblinvoices(id) ON DELETE CASCADE;
-- ALTER TABLE desk_moloni_mapping ADD CONSTRAINT fk_perfex_estimate FOREIGN KEY (perfex_id) REFERENCES tblestimates(id) ON DELETE CASCADE;