-- ICYLAB OS - Sistema transversal de notificaciones internas y email
-- Ejecutar una sola vez sobre la base icylab_app.

CREATE TABLE IF NOT EXISTS `notification_event_types` (
  `event_type` varchar(100) NOT NULL,
  `module` varchar(80) NOT NULL DEFAULT 'system',
  `label` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `default_priority` enum('info','success','warning','critical') NOT NULL DEFAULT 'info',
  `is_critical` tinyint(1) NOT NULL DEFAULT 0,
  `email_default_enabled` 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 (`event_type`),
  KEY `idx_notification_event_types_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notification_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(100) NOT NULL,
  `channel` enum('email') NOT NULL DEFAULT 'email',
  `subject_template` varchar(255) NOT NULL,
  `html_template` longtext DEFAULT NULL,
  `text_template` text DEFAULT NULL,
  `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_templates_type_channel` (`event_type`,`channel`),
  KEY `idx_notification_templates_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notification_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `event_type` varchar(100) NOT NULL,
  `module` varchar(80) NOT NULL DEFAULT 'system',
  `priority` enum('info','success','warning','critical') NOT NULL DEFAULT 'info',
  `title` varchar(255) NOT NULL,
  `body` text DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `related_type` varchar(80) DEFAULT NULL,
  `related_id` int(11) DEFAULT NULL,
  `actor_user_id` int(11) DEFAULT NULL,
  `metadata_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata_json`)),
  `dedupe_key` varchar(191) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_notification_events_dedupe` (`dedupe_key`),
  KEY `idx_notification_events_type` (`event_type`),
  KEY `idx_notification_events_module` (`module`),
  KEY `idx_notification_events_related` (`related_type`,`related_id`),
  KEY `idx_notification_events_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` bigint(20) unsigned NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `module` varchar(80) NOT NULL DEFAULT 'system',
  `related_type` varchar(80) DEFAULT NULL,
  `related_id` int(11) DEFAULT NULL,
  `priority` enum('info','success','warning','critical') NOT NULL DEFAULT 'info',
  `read_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_notifications_event_user` (`event_id`,`user_id`),
  KEY `idx_notifications_user_read_created` (`user_id`,`read_at`,`created_at`),
  KEY `idx_notifications_user_created` (`user_id`,`created_at`),
  KEY `idx_notifications_related` (`related_type`,`related_id`),
  CONSTRAINT `fk_notifications_event` FOREIGN KEY (`event_id`) REFERENCES `notification_events` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notification_deliveries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` bigint(20) unsigned NOT NULL,
  `notification_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `channel` enum('email') NOT NULL DEFAULT 'email',
  `recipient` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body_html` longtext DEFAULT NULL,
  `body_text` text DEFAULT NULL,
  `status` enum('queued','sending','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
  `attempts` int(11) NOT NULL DEFAULT 0,
  `max_attempts` int(11) NOT NULL DEFAULT 5,
  `last_error` text DEFAULT NULL,
  `priority_sort` tinyint(4) NOT NULL DEFAULT 4,
  `next_attempt_at` datetime DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_notification_deliveries_queue` (`channel`,`status`,`next_attempt_at`,`attempts`,`priority_sort`),
  KEY `idx_notification_deliveries_event` (`event_id`),
  KEY `idx_notification_deliveries_user` (`user_id`),
  CONSTRAINT `fk_notification_deliveries_event` FOREIGN KEY (`event_id`) REFERENCES `notification_events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_notification_deliveries_notification` FOREIGN KEY (`notification_id`) REFERENCES `notifications` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notification_preferences` (
  `user_id` int(11) NOT NULL,
  `event_type` varchar(100) NOT NULL,
  `email_enabled` 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 (`user_id`,`event_type`),
  KEY `idx_notification_preferences_event` (`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `notification_event_types` (`event_type`, `module`, `label`, `description`, `default_priority`, `is_critical`, `email_default_enabled`) VALUES
('opportunity.assigned', 'crm', 'Oportunidad asignada', 'Cuando una oportunidad queda asignada a un usuario.', 'info', 0, 1),
('opportunity.facturada', 'crm', 'Oportunidad facturada', 'Cuando una oportunidad pasa a etapa facturada.', 'success', 0, 1),
('billing.por_cobrar', 'crm', 'Cobranza por cobrar', 'Cuando una cobranza queda pendiente de pago.', 'info', 0, 1),
('billing.overdue', 'crm', 'Cobranza atrasada', 'Cuando una cobranza supera su vencimiento.', 'critical', 1, 1),
('billing.paid', 'crm', 'Cobranza pagada', 'Cuando una cobranza queda marcada como pagada.', 'success', 0, 1),
('quote.created', 'crm', 'Cotización creada', 'Cuando se crea una nueva cotización.', 'info', 0, 0),
('quote.accepted', 'crm', 'Cotización aceptada', 'Cuando una cotización pasa a estado aceptada.', 'success', 0, 1),
('quote.rejected', 'crm', 'Cotización rechazada', 'Cuando una cotización pasa a estado rechazada.', 'warning', 0, 1),
('next_action.due', 'crm', 'Próxima acción vencida o próxima', 'Recordatorio de próximas acciones vencidas o próximas a vencer.', 'warning', 0, 1),
('document.tax_associated', 'crm', 'Documento tributario asociado', 'Cuando se asocia una factura, nota de crédito o nota de débito a un cliente.', 'info', 0, 1),
('system.sync_error', 'system', 'Error de sincronización o proceso automático', 'Cuando un proceso automático falla.', 'critical', 1, 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
('opportunity.assigned', 'email', '[ICYLAB OS] {title}', NULL, NULL, 1),
('opportunity.facturada', 'email', '[ICYLAB OS] {title}', NULL, NULL, 1),
('billing.por_cobrar', 'email', '[ICYLAB OS] {title}', NULL, NULL, 1),
('billing.overdue', 'email', '[ICYLAB OS] URGENTE: {title}', NULL, NULL, 1),
('billing.paid', 'email', '[ICYLAB OS] {title}', NULL, NULL, 1),
('quote.created', 'email', '[ICYLAB OS] {title}', NULL, NULL, 1),
('quote.accepted', 'email', '[ICYLAB OS] {title}', NULL, NULL, 1),
('quote.rejected', 'email', '[ICYLAB OS] {title}', NULL, NULL, 1),
('next_action.due', 'email', '[ICYLAB OS] {title}', NULL, NULL, 1),
('document.tax_associated', 'email', '[ICYLAB OS] {title}', NULL, NULL, 1),
('system.sync_error', 'email', '[ICYLAB OS] Error crítico: {title}', NULL, NULL, 1)
ON DUPLICATE KEY UPDATE
  `subject_template` = VALUES(`subject_template`),
  `is_active` = VALUES(`is_active`),
  `updated_at` = CURRENT_TIMESTAMP;
