-- ICYLAB OS - Grupos de destinatarios y MVP ejecutivo de notificaciones.
-- Ejecutar despues de 2026_07_08_notifications_system.sql.

CREATE TABLE IF NOT EXISTS `notification_recipient_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_key` varchar(80) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT 1,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `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 `uq_notification_recipient_groups_key` (`group_key`),
  KEY `idx_notification_recipient_groups_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notification_recipient_group_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_notification_group_user` (`group_id`,`user_id`),
  KEY `idx_notification_group_members_user` (`user_id`),
  CONSTRAINT `fk_notification_group_members_group`
    FOREIGN KEY (`group_id`) REFERENCES `notification_recipient_groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_notification_group_members_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `notification_recipient_groups`
(`group_key`, `name`, `description`, `is_system`, `is_active`)
VALUES
('ceo', 'CEO', 'Alertas estrategicas, excepciones comerciales y riesgos criticos.', 1, 1),
('cfo', 'CFO', 'Alertas de caja, cobranza, facturacion, descuentos y margen.', 1, 1),
('commercial_direction', 'Direccion Comercial', 'Escalamiento comercial y control de oportunidades relevantes.', 1, 1),
('commercial_team', 'Equipo Comercial', 'Usuarios responsables de ventas, CRM, clientes y cotizaciones.', 1, 1),
('operations_team', 'Equipo Operativo', 'Usuarios de operacion, produccion, calidad, despacho o stock.', 1, 1),
('finance_team', 'Administracion / Cobranza', 'Usuarios responsables de backoffice financiero y cobranza.', 1, 1),
('system_admins', 'Administradores del Sistema', 'Alertas tecnicas y errores de sincronizacion o procesos automaticos.', 1, 1)
ON DUPLICATE KEY UPDATE
  `name` = VALUES(`name`),
  `description` = VALUES(`description`),
  `is_system` = VALUES(`is_system`),
  `is_active` = VALUES(`is_active`),
  `updated_at` = CURRENT_TIMESTAMP;

-- Semilla operacional inicial. Se puede corregir desde Notificaciones > Responsables y equipos.
INSERT IGNORE INTO `notification_recipient_group_members` (`group_id`, `user_id`)
SELECT g.id, u.id
FROM `notification_recipient_groups` g
JOIN `users` u ON u.role = 'admin' AND u.status = 'active' AND u.is_active = 1
WHERE g.group_key IN ('ceo', 'system_admins');

INSERT IGNORE INTO `notification_recipient_group_members` (`group_id`, `user_id`)
SELECT g.id, u.id
FROM `notification_recipient_groups` g
JOIN `users` u ON u.role = 'administracion' AND u.status = 'active' AND u.is_active = 1
WHERE g.group_key IN ('cfo', 'finance_team', 'commercial_direction');

INSERT IGNORE INTO `notification_recipient_group_members` (`group_id`, `user_id`)
SELECT g.id, u.id
FROM `notification_recipient_groups` g
JOIN `users` u ON u.role = 'comercial' AND u.status = 'active' AND u.is_active = 1
WHERE g.group_key = 'commercial_team';

INSERT IGNORE INTO `notification_recipient_group_members` (`group_id`, `user_id`)
SELECT g.id, u.id
FROM `notification_recipient_groups` g
JOIN `users` u ON u.role = 'operaciones' AND u.status = 'active' AND u.is_active = 1
WHERE g.group_key = 'operations_team';

INSERT INTO `notification_event_types` (`event_type`, `module`, `label`, `description`, `default_priority`, `is_critical`, `email_default_enabled`) VALUES
('next_action.overdue_escalated', 'crm', 'Proxima accion vencida escalada', 'Cuando una proxima accion permanece vencida por mas de 48 horas.', 'critical', 1, 1),
('quote.accepted.no_tax_document', 'crm', 'Cotizacion aceptada sin documento tributario', 'Cuando una cotizacion aceptada no tiene factura o documento tributario despues de 24 horas.', 'warning', 0, 1),
('billing.due_today', 'crm', 'Cobranza vence hoy', 'Cuando una cobranza pendiente vence durante el dia.', 'warning', 0, 1),
('billing.overdue_critical', 'crm', 'Cobranza atrasada critica', 'Cuando una cobranza se mantiene atrasada por 3 dias o mas.', 'critical', 1, 1),
('opportunity.high_value', 'crm', 'Oportunidad de alto valor', 'Cuando una oportunidad supera el umbral comercial ejecutivo.', 'warning', 0, 1),
('opportunity.stalled_high_value', 'crm', 'Oportunidad de alto valor estancada', 'Cuando una oportunidad relevante queda sin movimiento comercial reciente.', 'critical', 1, 1),
('quote.discount_high', 'crm', 'Descuento comercial alto', 'Cuando una cotizacion supera el umbral de descuento autorizado.', 'warning', 0, 1)
ON DUPLICATE KEY UPDATE
  `module` = VALUES(`module`),
  `label` = VALUES(`label`),
  `description` = VALUES(`description`),
  `default_priority` = VALUES(`default_priority`),
  `is_critical` = VALUES(`is_critical`),
  `email_default_enabled` = VALUES(`email_default_enabled`),
  `updated_at` = CURRENT_TIMESTAMP;

INSERT INTO `notification_templates` (`event_type`, `channel`, `subject_template`, `html_template`, `text_template`, `is_active`) VALUES
('next_action.overdue_escalated', 'email', '[ICYLAB OS] Escalamiento: {title}', NULL, NULL, 1),
('quote.accepted.no_tax_document', 'email', '[ICYLAB OS] Pendiente facturacion: {title}', NULL, NULL, 1),
('billing.due_today', 'email', '[ICYLAB OS] Cobranza vence hoy: {title}', NULL, NULL, 1),
('billing.overdue_critical', 'email', '[ICYLAB OS] URGENTE cobranza: {title}', NULL, NULL, 1),
('opportunity.high_value', 'email', '[ICYLAB OS] Deal relevante: {title}', NULL, NULL, 1),
('opportunity.stalled_high_value', 'email', '[ICYLAB OS] Deal estancado: {title}', NULL, NULL, 1),
('quote.discount_high', 'email', '[ICYLAB OS] Descuento alto: {title}', NULL, NULL, 1)
ON DUPLICATE KEY UPDATE
  `subject_template` = VALUES(`subject_template`),
  `is_active` = VALUES(`is_active`),
  `updated_at` = CURRENT_TIMESTAMP;
