CREATE TABLE IF NOT EXISTS `email_link_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_link_tracking_id` int(11) NOT NULL,
  `action_type` enum('assign_tag','remove_tag') NOT NULL,
  `tag_id` int(11) NOT NULL,
  `only_once` tinyint(1) NOT NULL DEFAULT 1,
  `executed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_email_link_actions_tracking` (`email_link_tracking_id`),
  KEY `idx_email_link_actions_tag` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `automation_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `event_type` enum('tag_assigned','tag_removed') NOT NULL,
  `event_tag_id` int(11) NOT NULL,
  `action_type` enum('assign_tag','remove_tag','subscribe_list','unsubscribe_list') NOT NULL,
  `action_target_tag_id` int(11) DEFAULT NULL,
  `action_target_list_id` int(11) DEFAULT NULL,
  `only_once_per_recipient` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_automation_rules_event` (`is_active`,`event_type`,`event_tag_id`),
  KEY `idx_automation_rules_target_tag` (`action_target_tag_id`),
  KEY `idx_automation_rules_target_list` (`action_target_list_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `automation_rule_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rule_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `event_type` varchar(50) NOT NULL,
  `event_reference` varchar(190) DEFAULT NULL,
  `status` enum('executed','skipped','failed') NOT NULL DEFAULT 'executed',
  `details` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_automation_rule_logs_rule_recipient` (`rule_id`,`recipient_id`),
  KEY `idx_automation_rule_logs_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
