-- =============================================================================
-- MAILBOX ENHANCEMENTS - Advanced Features & Security
-- =============================================================================

-- 1. THREADS & CONVERSATIONS
-- Dodaj kolumny do lspd_mailbox_new dla threadów (pomiń jeśli istnieją)
ALTER TABLE lspd_mailbox_new 
ADD COLUMN IF NOT EXISTS thread_id VARCHAR(50) DEFAULT NULL;

ALTER TABLE lspd_mailbox_new 
ADD COLUMN IF NOT EXISTS parent_message_id INT DEFAULT NULL;

ALTER TABLE lspd_mailbox_new 
ADD COLUMN IF NOT EXISTS is_reply BOOLEAN DEFAULT FALSE;

-- Dodaj indeksy jeśli nie istnieją
CREATE INDEX IF NOT EXISTS idx_thread_id ON lspd_mailbox_new(thread_id);
CREATE INDEX IF NOT EXISTS idx_parent_message ON lspd_mailbox_new(parent_message_id);

-- 2. DRAFTS TABLE
CREATE TABLE IF NOT EXISTS mailbox_drafts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_badge VARCHAR(50) NOT NULL,
    to_badge VARCHAR(50),
    subject VARCHAR(255),
    message TEXT,
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    attachments JSON DEFAULT NULL,
    last_saved TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    auto_saved BOOLEAN DEFAULT TRUE,
    INDEX idx_user_badge (user_badge)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. SCHEDULED MESSAGES
CREATE TABLE IF NOT EXISTS mailbox_scheduled_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_badge VARCHAR(50) NOT NULL,
    to_badge VARCHAR(50) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    scheduled_time DATETIME NOT NULL,
    timezone VARCHAR(50) DEFAULT 'UTC',
    attachments JSON DEFAULT NULL,
    status ENUM('pending', 'sent', 'cancelled', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sent_at TIMESTAMP NULL,
    error_message TEXT,
    INDEX idx_scheduled_time (scheduled_time, status),
    INDEX idx_from_badge (from_badge)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. MESSAGE TEMPLATES
CREATE TABLE IF NOT EXISTS mailbox_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_badge VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    category ENUM('personal', 'department', 'official', 'other') DEFAULT 'personal',
    subject VARCHAR(255),
    content TEXT NOT NULL,
    is_shared BOOLEAN DEFAULT FALSE,
    department VARCHAR(50) DEFAULT NULL,
    usage_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_badge (user_badge),
    INDEX idx_category (category),
    INDEX idx_shared (is_shared, department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. ATTACHMENTS
CREATE TABLE IF NOT EXISTS mailbox_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT DEFAULT NULL,
    draft_id INT DEFAULT NULL,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size INT NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    uploaded_by VARCHAR(50) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE,
    INDEX idx_message_id (message_id),
    INDEX idx_draft_id (draft_id),
    FOREIGN KEY (message_id) REFERENCES lspd_mailbox_new(id) ON DELETE CASCADE,
    FOREIGN KEY (draft_id) REFERENCES mailbox_drafts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. EDIT HISTORY
CREATE TABLE IF NOT EXISTS mailbox_edit_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    draft_id INT NOT NULL,
    subject VARCHAR(255),
    message TEXT,
    version_number INT NOT NULL,
    saved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changes_summary VARCHAR(500),
    INDEX idx_draft_id (draft_id),
    FOREIGN KEY (draft_id) REFERENCES mailbox_drafts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. TAGS
CREATE TABLE IF NOT EXISTS mailbox_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_badge VARCHAR(50) NOT NULL,
    name VARCHAR(50) NOT NULL,
    color VARCHAR(7) DEFAULT '#3b82f6',
    icon VARCHAR(50) DEFAULT 'tag',
    is_system BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_tag (user_badge, name),
    INDEX idx_user_badge (user_badge)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. MESSAGE TAGS (many-to-many)
CREATE TABLE IF NOT EXISTS mailbox_message_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT NOT NULL,
    tag_id INT NOT NULL,
    tagged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_message_tag (message_id, tag_id),
    INDEX idx_message_id (message_id),
    INDEX idx_tag_id (tag_id),
    FOREIGN KEY (message_id) REFERENCES lspd_mailbox_new(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES mailbox_tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. FILTERS & RULES
CREATE TABLE IF NOT EXISTS mailbox_filters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_badge VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    is_enabled BOOLEAN DEFAULT TRUE,
    priority INT DEFAULT 0,
    conditions JSON NOT NULL,
    actions JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_run TIMESTAMP NULL,
    execution_count INT DEFAULT 0,
    INDEX idx_user_badge (user_badge),
    INDEX idx_enabled (is_enabled, priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. AUDIT LOG
CREATE TABLE IF NOT EXISTS mailbox_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_badge VARCHAR(50) NOT NULL,
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50) NOT NULL,
    resource_id INT DEFAULT NULL,
    details JSON DEFAULT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    status ENUM('success', 'failure', 'warning') DEFAULT 'success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_badge (user_badge),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at),
    INDEX idx_resource (resource_type, resource_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. SECURITY: Rate Limiting Tracking
CREATE TABLE IF NOT EXISTS mailbox_rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(100) NOT NULL,
    action VARCHAR(50) NOT NULL,
    count INT DEFAULT 1,
    window_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    UNIQUE KEY unique_identifier_action (identifier, action),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. SECURITY: Session Management
CREATE TABLE IF NOT EXISTS mailbox_sessions (
    id VARCHAR(128) PRIMARY KEY,
    user_badge VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    is_valid BOOLEAN DEFAULT TRUE,
    INDEX idx_user_badge (user_badge),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- DEFAULT DATA - System Tags
-- =============================================================================

-- Systemowe tagi dla wszystkich użytkowników
INSERT INTO mailbox_tags (user_badge, name, color, icon, is_system) VALUES
('SYSTEM', 'Important', '#ef4444', 'exclamation-circle', TRUE),
('SYSTEM', 'Work', '#3b82f6', 'briefcase', TRUE),
('SYSTEM', 'Personal', '#10b981', 'user', TRUE),
('SYSTEM', 'Follow Up', '#f59e0b', 'clock', TRUE),
('SYSTEM', 'Archive', '#6b7280', 'archive', TRUE)
ON DUPLICATE KEY UPDATE name=name;

-- =============================================================================
-- INDEXES dla wydajności
-- =============================================================================

-- Dodatkowe indeksy dla lspd_mailbox_new jeśli nie istnieją
CREATE INDEX IF NOT EXISTS idx_to_badge_read ON lspd_mailbox_new(to_badge, is_read);
CREATE INDEX IF NOT EXISTS idx_from_badge_created ON lspd_mailbox_new(from_badge, created_at);
CREATE INDEX IF NOT EXISTS idx_priority_read ON lspd_mailbox_new(priority, is_read);

-- =============================================================================
-- VIEWS dla lepszej czytelności
-- =============================================================================

-- Widok wiadomości z liczbą załączników i tagów
CREATE OR REPLACE VIEW v_mailbox_messages_extended AS
SELECT 
    m.*,
    COUNT(DISTINCT a.id) as attachment_count,
    COUNT(DISTINCT mt.tag_id) as tag_count,
    GROUP_CONCAT(DISTINCT t.name SEPARATOR ', ') as tags
FROM lspd_mailbox_new m
LEFT JOIN mailbox_attachments a ON m.id = a.message_id AND a.is_deleted = FALSE
LEFT JOIN mailbox_message_tags mt ON m.id = mt.message_id
LEFT JOIN mailbox_tags t ON mt.tag_id = t.id
GROUP BY m.id;

-- Widok threadów z metadanymi
CREATE OR REPLACE VIEW v_mailbox_threads AS
SELECT 
    thread_id,
    COUNT(*) as message_count,
    MAX(created_at) as last_message_at,
    MIN(created_at) as first_message_at,
    SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) as unread_count,
    GROUP_CONCAT(DISTINCT from_badge ORDER BY created_at) as participants
FROM lspd_mailbox_new
WHERE thread_id IS NOT NULL
GROUP BY thread_id;

-- =============================================================================
-- TRIGGERS dla auto-cleanup
-- =============================================================================

-- Note: Events require DELIMITER in mysql client, but not in programmatic execution
-- Auto-cleanup expired rate limits
CREATE EVENT IF NOT EXISTS cleanup_rate_limits
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM mailbox_rate_limits WHERE expires_at < NOW();

-- Auto-cleanup old audit logs (90 dni retention)
CREATE EVENT IF NOT EXISTS cleanup_audit_logs
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM mailbox_audit_log WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Auto-cleanup expired sessions
CREATE EVENT IF NOT EXISTS cleanup_sessions
ON SCHEDULE EVERY 30 MINUTE
DO
  DELETE FROM mailbox_sessions WHERE expires_at < NOW() OR is_valid = FALSE;

-- =============================================================================
-- FINAL STATUS
-- =============================================================================

SELECT 'Mailbox enhancements completed successfully!' as status;
