USE `helpdesk_sinarco`;

INSERT INTO roles (slug, name, description) VALUES
    ('interno', 'Solicitante Interno', 'Colaborador interno da empresa'),
    ('externo', 'Solicitante Externo', 'Cliente externo com acesso ao portal'),
    ('atendente-ti', 'Atendente TI', 'Responsavel pela fila Central TI'),
    ('atendente-noc', 'Atendente NOC', 'Responsavel pela fila NOC 24x7'),
    ('gestor', 'Gestor', 'Administra filas, SLAs e usuarios');

INSERT INTO customer_types (slug, name, description, sort_order, is_active) VALUES
    ('interno', 'Interno', 'Colaboradores e equipes internas', 1, 1),
    ('externo', 'Externo', 'Clientes, condominios e parceiros', 2, 1);

INSERT INTO settings_theme (context, primary_color, secondary_color, dark_color, accent_color, logo_text, logo_path) VALUES
    ('global', '#ff7a18', '#6c63ff', '#1f1f1f', '#37474f', 'HelpDesk Sinarco', NULL);

INSERT INTO queues (slug, name, description, sla_response_minutes, sla_resolution_minutes, is_active) VALUES
    ('central-ti', 'Central TI', 'Suporte geral a colaboradores', 60, 480, 1),
    ('noc-247', 'NOC 24x7', 'Monitoramento continuo e resposta a incidentes', 30, 240, 1);

INSERT INTO priorities (slug, name, response_target_minutes, resolution_target_minutes, color, sort_order) VALUES
    ('baixa', 'Baixa', 480, 2880, '#4caf50', 1),
    ('normal', 'Normal', 240, 1440, '#2196f3', 2),
    ('alta', 'Alta', 120, 720, '#fb8c00', 3),
    ('critica', 'Critica', 30, 240, '#e53935', 4);

INSERT INTO statuses (slug, name, description, is_terminal, pauses_sla) VALUES
    ('novo', 'Novo', 'Chamado aguardando triagem', 0, 0),
    ('em-triagem', 'Em Triagem', 'Triagem inicial em andamento', 0, 0),
    ('em-atendimento', 'Em Atendimento', 'Atendimento ativo', 0, 0),
    ('aguardando-cliente', 'Aguardando Cliente', 'Pausa aguardando info do solicitante', 0, 1),
    ('pausado-interno', 'Pausado Interno', 'Dependencia interna', 0, 1),
    ('resolvido', 'Resolvido', 'Solucao aplicada aguardando validacao', 0, 0),
    ('fechado', 'Fechado', 'Chamado encerrado', 1, 0),
    ('cancelado', 'Cancelado', 'Chamado cancelado', 1, 0);

INSERT INTO slas (name, description, first_response_minutes, resolution_minutes, business_hours_only, escalation_enabled, escalation_minutes, priority_id, is_active)
VALUES ('SLA Padrão', 'Aplica-se à maioria dos chamados', 60, 480, 1, 0, NULL, NULL, 1);

INSERT INTO categories (parent_id, name, slug, description, queue_id)
SELECT NULL, 'Suporte TI', 'ti', 'Demandas gerais de TI', q.id
FROM queues q WHERE q.slug = 'central-ti';

INSERT INTO categories (parent_id, name, slug, description, queue_id)
SELECT c.id, 'Hardware', 'suporte-ti-hardware', 'Falhas em equipamentos', q.id
FROM categories c
JOIN queues q ON q.slug = 'central-ti'
WHERE c.slug = 'ti';

INSERT INTO categories (parent_id, name, slug, description, queue_id)
SELECT c.id, 'Software Corporativo', 'suporte-ti-software', 'Aplicacoes internas', q.id
FROM categories c
JOIN queues q ON q.slug = 'central-ti'
WHERE c.slug = 'ti';

INSERT INTO categories (parent_id, name, slug, description, queue_id)
SELECT NULL, 'Monitoramento NOC', 'noc', 'Eventos detectados pelo NOC', q.id
FROM queues q WHERE q.slug = 'noc-247';

INSERT INTO categories (parent_id, name, slug, description, queue_id)
SELECT c.id, 'Alarmes', 'monitoramento-noc-alarmes', 'Alarmes de infraestrutura', q.id
FROM categories c
JOIN queues q ON q.slug = 'noc-247'
WHERE c.slug = 'noc';

INSERT INTO categories (parent_id, name, slug, description, queue_id)
SELECT c.id, 'Mudancas Programadas', 'monitoramento-noc-mudancas', 'Comunicados e janelas', q.id
FROM categories c
JOIN queues q ON q.slug = 'noc-247'
WHERE c.slug = 'noc';

INSERT INTO categories (parent_id, name, slug, description, queue_id)
SELECT c.id, 'Emergencias', 'monitoramento-noc-emergencia', 'Incidentes com risco imediato', q.id
FROM categories c
JOIN queues q ON q.slug = 'noc-247'
WHERE c.slug = 'noc';

INSERT INTO sla_policies (category_id, priority_id, client_type, response_minutes, resolution_minutes, sort_order)
SELECT c.id, p.id, 'interno', 60, 480, 1
FROM categories c
JOIN priorities p ON p.slug = 'normal'
WHERE c.slug = 'suporte-ti-software';

INSERT INTO sla_policies (category_id, priority_id, client_type, response_minutes, resolution_minutes, sort_order)
SELECT c.id, p.id, 'externo', 45, 360, 1
FROM categories c
JOIN priorities p ON p.slug = 'normal'
WHERE c.slug = 'suporte-ti-software';

INSERT INTO sla_policies (category_id, priority_id, client_type, response_minutes, resolution_minutes, sort_order)
SELECT c.id, p.id, NULL, 30, 180, 1
FROM categories c
JOIN priorities p ON p.slug = 'alta'
WHERE c.slug = 'monitoramento-noc-emergencia';

INSERT INTO ticket_fields (category_id, name, label, type, options, is_required, sort_order)
SELECT c.id, 'software_sistema', 'Sistema afetado', 'text', NULL, 1, 1
FROM categories c WHERE c.slug = 'suporte-ti-software';

INSERT INTO ticket_fields (category_id, name, label, type, options, is_required, sort_order)
SELECT c.id, 'software_problema', 'Problema identificado', 'textarea', NULL, 1, 2
FROM categories c WHERE c.slug = 'suporte-ti-software';

INSERT INTO ticket_fields (category_id, name, label, type, options, is_required, sort_order)
SELECT c.id, 'software_maquina', 'Maquina/Host', 'text', NULL, 0, 3
FROM categories c WHERE c.slug = 'suporte-ti-software';

INSERT INTO ticket_fields (category_id, name, label, type, options, is_required, sort_order)
SELECT c.id, 'software_impacto', 'Impacto', 'select', '["Parcial","Total","Intermitente"]', 1, 4
FROM categories c WHERE c.slug = 'suporte-ti-software';

INSERT INTO ticket_fields (category_id, name, label, type, options, is_required, sort_order)
SELECT c.id, 'noc_risco_imediato', 'Risco imediato?', 'select', '["Sim","Nao"]', 1, 1
FROM categories c WHERE c.slug = 'monitoramento-noc-emergencia';

INSERT INTO ticket_fields (category_id, name, label, type, options, is_required, sort_order)
SELECT c.id, 'noc_orgao_acionado', 'Órgão acionado', 'text', NULL, 1, 2
FROM categories c WHERE c.slug = 'monitoramento-noc-emergencia';

INSERT INTO ticket_fields (category_id, name, label, type, options, is_required, sort_order)
SELECT c.id, 'noc_protocolo', 'Protocolo externo', 'text', NULL, 1, 3
FROM categories c WHERE c.slug = 'monitoramento-noc-emergencia';

INSERT INTO users (role_id, queue_id, name, email, password_hash, phone, is_active)
SELECT r.id, NULL, 'Gestor Sinarco', 'gestor@sinarco.com', '$2y$10$GRlXiFK9iWoNwAu2BP0N6OpzgzlbzviZfVHpJDK.OCZbd1F9nDQEK', '+55 11 99999-0001', 1
FROM roles r WHERE r.slug = 'gestor';

INSERT INTO users (role_id, queue_id, name, email, password_hash, phone, is_active)
SELECT r.id, q.id, 'Atendente Central TI', 'ti@sinarco.com', '$2y$10$GRlXiFK9iWoNwAu2BP0N6OpzgzlbzviZfVHpJDK.OCZbd1F9nDQEK', '+55 11 99999-0004', 1
FROM roles r
JOIN queues q ON q.slug = 'central-ti'
WHERE r.slug = 'atendente-ti';

INSERT INTO users (role_id, queue_id, name, email, password_hash, phone, is_active)
SELECT r.id, q.id, 'Atendente NOC', 'noc@sinarco.com', '$2y$10$GRlXiFK9iWoNwAu2BP0N6OpzgzlbzviZfVHpJDK.OCZbd1F9nDQEK', '+55 11 99999-0002', 1
FROM roles r
JOIN queues q ON q.slug = 'noc-247'
WHERE r.slug = 'atendente-noc';

INSERT INTO users (role_id, queue_id, name, email, password_hash, phone, is_active)
SELECT r.id, NULL, 'Colaborador Interno', 'colaborador@sinarco.com', '$2y$10$GRlXiFK9iWoNwAu2BP0N6OpzgzlbzviZfVHpJDK.OCZbd1F9nDQEK', '+55 11 99999-0005', 1
FROM roles r WHERE r.slug = 'interno';

INSERT INTO users (role_id, queue_id, name, email, password_hash, phone, is_active)
SELECT r.id, NULL, 'Cliente Externo', 'cliente@sinarco.com', '$2y$10$GRlXiFK9iWoNwAu2BP0N6OpzgzlbzviZfVHpJDK.OCZbd1F9nDQEK', '+55 11 99999-0003', 1
FROM roles r WHERE r.slug = 'externo';

INSERT INTO tickets (reference, client_type, main_category, site_name, unit_name, condominium, customer_name, contact_name, contact_phone, contact_email, subject, description, requester_id, assignee_id, queue_id, category_id, priority_id, status_id, sla_due_at)
SELECT 'HDK-0001', 'externo', 'noc', 'Datacenter Oeste', NULL, 'Cond. Alpha', 'Cliente Contrato 245', 'Marcos Lima', '+55 11 98888-1000', 'marcos.lima@cliente.com', 'Acesso VPN instavel', 'Cliente relata quedas intermitentes no acesso VPN corporativo.',
       requester.id,
       assignee.id,
       q.id,
       cat.id,
       pr.id,
       st.id,
       DATE_ADD(NOW(), INTERVAL pr.response_target_minutes MINUTE)
FROM users requester
JOIN users assignee ON assignee.email = 'noc@sinarco.com'
JOIN queues q ON q.slug = 'noc-247'
JOIN categories cat ON cat.slug = 'monitoramento-noc-emergencia'
JOIN priorities pr ON pr.slug = 'alta'
JOIN statuses st ON st.slug = 'em-atendimento'
WHERE requester.email = 'cliente@sinarco.com';

INSERT INTO tickets (reference, client_type, main_category, site_name, unit_name, condominium, customer_name, contact_name, contact_phone, contact_email, subject, description, requester_id, assignee_id, queue_id, category_id, priority_id, status_id, sla_due_at)
SELECT 'HDK-0002', 'interno', 'ti', 'Obra Matriz', 'Unidade ADM', NULL, 'Sinarco', 'Patricia Souza', '+55 11 97777-2000', 'patricia.souza@sinarco.com', 'Notebook sem acesso a ERP', 'Colaborador interno nao consegue acessar o ERP corporativo apos atualizacao do Windows.',
       requester.id,
       assignee.id,
       q.id,
       cat.id,
       pr.id,
       st.id,
       DATE_ADD(NOW(), INTERVAL pr.response_target_minutes MINUTE)
FROM users requester
JOIN users assignee ON assignee.email = 'ti@sinarco.com'
JOIN queues q ON q.slug = 'central-ti'
JOIN categories cat ON cat.slug = 'suporte-ti-software'
JOIN priorities pr ON pr.slug = 'normal'
JOIN statuses st ON st.slug = 'em-triagem'
WHERE requester.email = 'colaborador@sinarco.com';

INSERT INTO ticket_comments (ticket_id, author_id, content, is_internal)
SELECT t.id, requester.id, 'Detalhe adicional: instabilidade ocorre apos as 22h.', 0
FROM tickets t
JOIN users requester ON requester.email = 'cliente@sinarco.com'
WHERE t.reference = 'HDK-0001';

INSERT INTO ticket_comments (ticket_id, author_id, content, is_internal)
SELECT t.id, requester.id, 'Ja reiniciei o notebook e o problema persiste.', 0
FROM tickets t
JOIN users requester ON requester.email = 'colaborador@sinarco.com'
WHERE t.reference = 'HDK-0002';

INSERT INTO ticket_status_history (ticket_id, from_status_id, to_status_id, changed_by, notes)
SELECT t.id,
       (SELECT id FROM statuses WHERE slug = 'novo'),
       (SELECT id FROM statuses WHERE slug = 'em-atendimento'),
       assignee.id,
       'Chamado enviado diretamente para atendimento pelo NOC'
FROM tickets t
JOIN users assignee ON assignee.email = 'noc@sinarco.com'
WHERE t.reference = 'HDK-0001';

INSERT INTO ticket_status_history (ticket_id, from_status_id, to_status_id, changed_by, notes)
SELECT t.id,
       (SELECT id FROM statuses WHERE slug = 'novo'),
       (SELECT id FROM statuses WHERE slug = 'em-triagem'),
       assignee.id,
       'Chamado atribuido à fila Central TI para investigacao.'
FROM tickets t
JOIN users assignee ON assignee.email = 'ti@sinarco.com'
WHERE t.reference = 'HDK-0002';

INSERT INTO ticket_field_values (ticket_id, field_id, value)
SELECT t.id, f.id, 'Portal ERP'
FROM tickets t
JOIN ticket_fields f ON f.name = 'software_sistema'
JOIN categories c ON c.slug = 'suporte-ti-software' AND c.id = f.category_id
WHERE t.reference = 'HDK-0002';

INSERT INTO ticket_field_values (ticket_id, field_id, value)
SELECT t.id, f.id, 'Usuário relata erro ao abrir módulo financeiro.'
FROM tickets t
JOIN ticket_fields f ON f.name = 'software_problema'
JOIN categories c ON c.slug = 'suporte-ti-software' AND c.id = f.category_id
WHERE t.reference = 'HDK-0002';

INSERT INTO ticket_field_values (ticket_id, field_id, value)
SELECT t.id, f.id, 'Notebook ADM-5521'
FROM tickets t
JOIN ticket_fields f ON f.name = 'software_maquina'
JOIN categories c ON c.slug = 'suporte-ti-software' AND c.id = f.category_id
WHERE t.reference = 'HDK-0002';

INSERT INTO ticket_field_values (ticket_id, field_id, value)
SELECT t.id, f.id, 'Parcial'
FROM tickets t
JOIN ticket_fields f ON f.name = 'software_impacto'
JOIN categories c ON c.slug = 'suporte-ti-software' AND c.id = f.category_id
WHERE t.reference = 'HDK-0002';

INSERT INTO ticket_field_values (ticket_id, field_id, value)
SELECT t.id, f.id, 'Sim'
FROM tickets t
JOIN ticket_fields f ON f.name = 'noc_risco_imediato'
JOIN categories c ON c.slug = 'monitoramento-noc-emergencia' AND c.id = f.category_id
WHERE t.reference = 'HDK-0001';

INSERT INTO ticket_field_values (ticket_id, field_id, value)
SELECT t.id, f.id, 'Brigada interna'
FROM tickets t
JOIN ticket_fields f ON f.name = 'noc_orgao_acionado'
JOIN categories c ON c.slug = 'monitoramento-noc-emergencia' AND c.id = f.category_id
WHERE t.reference = 'HDK-0001';

INSERT INTO ticket_field_values (ticket_id, field_id, value)
SELECT t.id, f.id, 'PROTO-88421'
FROM tickets t
JOIN ticket_fields f ON f.name = 'noc_protocolo'
JOIN categories c ON c.slug = 'monitoramento-noc-emergencia' AND c.id = f.category_id
WHERE t.reference = 'HDK-0001';
