-- ================================================================
-- MAILBOX ENHANCED - Test Data (Dane testowe)
-- ================================================================
-- Ten plik zawiera przykładowe dane do przetestowania nowych funkcji
-- Wykonaj go PO zaimportowaniu mailbox_enhancements.sql
-- ================================================================

USE lspd_mdt;

-- ================================================================
-- 1. TEMPLATES (Szablony wiadomości)
-- ================================================================

INSERT INTO mailbox_templates (name, subject, content, category, user_badge, is_shared, usage_count) VALUES
('Raport patrolowy - standardowy', 'Raport z patrolu #{patrol_id}', 
'W dniu {data} przeprowadziłem patrol w rejonie {rejon}.\n\nWydarzenia:\n- {wydarzenie_1}\n- {wydarzenie_2}\n\nWnioski:\n{wnioski}\n\nPozdrawiam,\n{podpis}', 
'official', '1000', 1, 15),

('Prośba o backup', 'Backup needed - Code 3', 
'Potrzebuję natychmiastowego wsparcia!\n\nLokalizacja: {lokalizacja}\nSytuacja: {opis}\nPodejrzanych: {liczba}\n\nCode: {code}\n\nDyspozytor, proszę o wsparcie.', 
'department', '1000', 1, 8),

('Potwierdzenie spotkania', 'Potwierdzenie - spotkanie {data}', 
'Dzień dobry,\n\nPotwierdzam spotkanie w dniu {data} o godzinie {godzina} w {miejsce}.\n\nTemat: {temat}\n\nDo zobaczenia,\n{imie}', 
'personal', '1001', 0, 3),

('Raport końcowy zmiany', 'End of Shift Report - {data}', 
'Raport końcowy ze zmiany:\n\nGodziny: {start} - {koniec}\nPatrol: #{patrol_id}\nOdnotowane interwencje: {liczba}\n\nZdarzenia:\n{zdarzenia}\n\nZłapani podejrzani: {podejrzani}\nWystawione mandaty: {mandaty}\n\nStan pojazdu: {pojazd}\n\nKomentarz: {komentarz}', 
'official', '1000', 1, 22),

('Zaproszenie na szkolenie', 'Szkolenie {nazwa} - {data}', 
'Szanowni funkcjonariusze,\n\nZapraszam na szkolenie: {nazwa}\nData: {data}\nGodzina: {godzina}\nMiejsce: {miejsce}\n\nTematyka:\n{tematyka}\n\nObecność obowiązkowa.\n\nPozdrawiam,\n{prowadzacy}', 
'department', '1002', 1, 5);

-- ================================================================
-- 2. TAGS (Tagi/Etykiety)
-- ================================================================

INSERT INTO mailbox_tags (name, color, icon, user_badge) VALUES
('Pilne', '#dc3545', 'exclamation-circle', '1000'),
('Do przeczytania', '#ffc107', 'book-open', '1000'),
('Raport', '#17a2b8', 'file-alt', '1000'),
('Backup request', '#f8961e', 'hands-helping', '1001'),
('Szkolenie', '#28a745', 'graduation-cap', '1002'),
('Archiwum', '#6c757d', 'archive', '1000'),
('VIP', '#9d4edd', 'star', '1002'),
('Śledztwo', '#e63946', 'search', '1001');

-- ================================================================
-- 3. FILTERS (Zapisane filtry)
-- ================================================================

INSERT INTO mailbox_filters (name, conditions, actions, user_badge, is_enabled) VALUES
('Tylko pilne', '{"priority":"urgent"}', '{}', '1000', 1),
('Raporty', '{"tag_id":3}', '{}', '1000', 1),
('Od supervisorów', '{"from_rank":["Sergeant","Lieutenant","Captain","Commander","Chief"]}', '{}', '1001', 0),
('Backup requests', '{"subject_contains":"backup","tag_id":4}', '{}', '1001', 1),
('Nieodczytane pilne', '{"priority":"high","is_read":0}', '{}', '1002', 1);

-- ================================================================
-- 4. EXAMPLE THREADS (Przykładowe wątki)
-- ================================================================

-- Zakładamy że istnieją już jakieś wiadomości w lspd_mailbox_new
-- Aktualizujemy kilka aby utworzyć wątki

-- Thread 1: Raport z wypadku
UPDATE lspd_mailbox_new 
SET thread_id = 'thread_accident_001', is_reply = 0, parent_message_id = NULL
WHERE id = 1 
LIMIT 1;

-- Odpowiedź 1
UPDATE lspd_mailbox_new 
SET thread_id = 'thread_accident_001', is_reply = 1, parent_message_id = 1
WHERE id = 2 
LIMIT 1;

-- Odpowiedź 2
UPDATE lspd_mailbox_new 
SET thread_id = 'thread_accident_001', is_reply = 1, parent_message_id = 1
WHERE id = 3 
LIMIT 1;

-- Thread 2: Prośba o backup
UPDATE lspd_mailbox_new 
SET thread_id = 'thread_backup_002', is_reply = 0, parent_message_id = NULL
WHERE id = 4 
LIMIT 1;

UPDATE lspd_mailbox_new 
SET thread_id = 'thread_backup_002', is_reply = 1, parent_message_id = 4
WHERE id = 5 
LIMIT 1;

-- ================================================================
-- 5. EXAMPLE DRAFTS (Przykładowe szkice)
-- ================================================================

INSERT INTO mailbox_drafts (user_badge, to_badge, subject, message, priority, last_saved) VALUES
('1000', '1001', 'Raport z patrolu - szkic', 'W dniu dzisiejszym...', 'normal', NOW()),
('1001', '1002', 'Prośba o urlop', 'Szanowny komendancie,\n\nChciałbym poprosić...', 'normal', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
('1002', '1000', '', '', 'normal', DATE_SUB(NOW(), INTERVAL 1 DAY));

-- ================================================================
-- 6. EXAMPLE SCHEDULED MESSAGES (Zaplanowane wiadomości)
-- ================================================================

-- Wiadomość za 1 godzinę
INSERT INTO mailbox_scheduled_messages (from_badge, to_badge, subject, message, priority, scheduled_time, timezone, status)
VALUES 
('1000', '1001', 
'Przypomnienie - spotkanie', 
'Przypominam o spotkaniu o 15:00 w sali konferencyjnej.',
'normal', 
DATE_ADD(NOW(), INTERVAL 1 HOUR), 
'Europe/Warsaw', 
'pending');

-- Wiadomość za 24h
INSERT INTO mailbox_scheduled_messages (from_badge, to_badge, subject, message, priority, scheduled_time, timezone, status)
VALUES 
('1002', '1000', 
'Raport miesięczny - deadline', 
'Przypominam o deadline na raport miesięczny - jutro do 18:00.',
'high', 
DATE_ADD(NOW(), INTERVAL 24 HOUR), 
'Europe/Warsaw', 
'pending');

-- Wiadomość w przeszłości (do testów - powinna być wysłana od razu przez cron)
INSERT INTO mailbox_scheduled_messages (from_badge, to_badge, subject, message, priority, scheduled_time, timezone, status)
VALUES 
('1001', '1000', 
'Test delayed message', 
'Ta wiadomość powinna być wysłana natychmiast przez scheduler.',
'normal', 
DATE_SUB(NOW(), INTERVAL 5 MINUTE), 
'Europe/Warsaw', 
'pending');

-- ================================================================
-- 7. EXAMPLE TAGS ON MESSAGES (Przypisanie tagów do wiadomości)
-- ================================================================

-- Zakomentowane - wymagają istniejących wiadomości
-- INSERT INTO mailbox_message_tags (message_id, tag_id) VALUES (1, 1);
-- INSERT INTO mailbox_message_tags (message_id, tag_id) VALUES (1, 3), (4, 3);
-- INSERT INTO mailbox_message_tags (message_id, tag_id) VALUES (4, 4);

-- ================================================================
-- 8. EXAMPLE AUDIT LOGS (Przykładowe logi audytowe)
-- ================================================================

INSERT INTO mailbox_audit_log (user_badge, action, resource_type, resource_id, ip_address, user_agent, details)
VALUES 
('1000', 'CREATE', 'message', 1, '192.168.1.100', 'Mozilla/5.0...', '{"to_badge":"1001","subject":"Test"}'),
('1001', 'READ', 'message', 1, '192.168.1.101', 'Mozilla/5.0...', '{}'),
('1000', 'CREATE', 'draft', 1, '192.168.1.100', 'Mozilla/5.0...', '{"subject":"Raport"}'),
('1002', 'CREATE', 'template', 1, '192.168.1.102', 'Mozilla/5.0...', '{"name":"Raport patrolowy"}'),
('1000', 'DELETE', 'message', 2, '192.168.1.100', 'Mozilla/5.0...', '{}');

-- ================================================================
-- VERIFICATION QUERIES (Zapytania weryfikujące)
-- ================================================================

-- Sprawdź ile mamy szablonów
SELECT COUNT(*) as templates_count FROM mailbox_templates;

-- Sprawdź ile mamy tagów
SELECT COUNT(*) as tags_count FROM mailbox_tags;

-- Sprawdź ile mamy filtrów
SELECT COUNT(*) as filters_count FROM mailbox_filters;

-- Sprawdź ile mamy szkiców
SELECT COUNT(*) as drafts_count FROM mailbox_drafts;

-- Sprawdź ile mamy zaplanowanych wiadomości
SELECT COUNT(*) as scheduled_count FROM mailbox_scheduled_messages WHERE status = 'pending';

-- Sprawdź wątki
SELECT thread_id, COUNT(*) as message_count 
FROM lspd_mailbox_new 
WHERE thread_id IS NOT NULL 
GROUP BY thread_id;

-- Sprawdź tagi na wiadomościach
SELECT mt.name, COUNT(*) as usage_count 
FROM mailbox_message_tags mmt
JOIN mailbox_tags mt ON mmt.tag_id = mt.id
GROUP BY mt.name;

-- Sprawdź logi audytowe
SELECT action, COUNT(*) as count 
FROM mailbox_audit_log 
GROUP BY action;

-- ================================================================
-- CLEANUP (jeśli chcesz wyczyścić dane testowe)
-- ================================================================

/*
DELETE FROM mailbox_templates WHERE created_by IN (1234, 5678, 9999);
DELETE FROM mailbox_tags WHERE created_by IN (1234, 5678, 9999);
DELETE FROM mailbox_filters WHERE created_by IN (1234, 5678, 9999);
DELETE FROM mailbox_drafts WHERE badge_number IN (1234, 5678, 9999);
DELETE FROM mailbox_scheduled_messages WHERE from_badge IN (1234, 5678, 9999);
DELETE FROM mailbox_message_tags;
DELETE FROM mailbox_audit_log;

-- Reset thread_id
UPDATE lspd_mailbox_new SET thread_id = NULL, is_reply = 0, parent_message_id = NULL;
*/

-- ================================================================
-- END OF TEST DATA
-- ================================================================

SELECT '✅ Test data imported successfully!' as status;
SELECT 'You can now test all enhanced mailbox features.' as message;
SELECT 'Access mailbox at: http://localhost:3000/pages/mailbox/mailbox.html' as url;
