-- =====================================================
-- UJOBS INDIA - ENGAGEMENT AUTOMATION SYSTEM
-- Database Schema v1.0
-- =====================================================

-- 1. JOURNEY DEFINITIONS
-- Stores journey templates/configurations
CREATE TABLE IF NOT EXISTS `journeys` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `trigger_type` ENUM('event', 'schedule') NOT NULL DEFAULT 'event',
  `trigger_event` JSON DEFAULT NULL COMMENT 'Event name or schedule cron',
  `trigger_segment` JSON DEFAULT NULL COMMENT 'User segment filters',
  `target_role` ENUM('worker', 'hirer', 'both') NOT NULL DEFAULT 'both',
  `status` ENUM('draft', 'active', 'paused', 'archived') NOT NULL DEFAULT 'draft',
  `priority` INT DEFAULT 5 COMMENT '1=highest, 10=lowest',
  `created_by` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `activated_at` TIMESTAMP NULL,
  `archived_at` TIMESTAMP NULL,
  INDEX `idx_status_priority` (`status`, `priority`),
  INDEX `idx_trigger_type` (`trigger_type`),
  INDEX `idx_target_role` (`target_role`),
  INDEX `idx_created_at` (`created_at`),
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. JOURNEY STEPS
-- Individual steps within a journey
CREATE TABLE IF NOT EXISTS `journey_steps` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `journey_id` BIGINT UNSIGNED NOT NULL,
  `step_order` INT NOT NULL COMMENT 'Execution sequence',
  `step_type` ENUM('trigger', 'delay', 'condition', 'notification', 'exit') NOT NULL,
  `step_name` VARCHAR(255) NOT NULL,
  `step_config` JSON NOT NULL COMMENT 'Step-specific configuration',
  `next_step_id` BIGINT UNSIGNED NULL COMMENT 'ID of next step (null for end)',
  `condition_true_step_id` BIGINT UNSIGNED NULL COMMENT 'Next step if condition is true',
  `condition_false_step_id` BIGINT UNSIGNED NULL COMMENT 'Next step if condition is false',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_journey_order` (`journey_id`, `step_order`),
  INDEX `idx_step_type` (`step_type`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. JOURNEY EXECUTIONS
-- Tracks individual user's journey progress
CREATE TABLE IF NOT EXISTS `journey_executions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `journey_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `current_step_id` BIGINT UNSIGNED NULL,
  `status` ENUM('pending', 'running', 'paused', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
  `next_execution_at` TIMESTAMP NULL COMMENT 'When to execute next step',
  `started_at` TIMESTAMP NULL,
  `completed_at` TIMESTAMP NULL,
  `retry_count` INT DEFAULT 0,
  `error_message` TEXT NULL,
  `execution_context` JSON NULL COMMENT 'Variables/data for this execution',
  `is_locked` TINYINT(1) DEFAULT 0 COMMENT 'Prevent concurrent execution',
  `locked_at` TIMESTAMP NULL,
  `locked_by` VARCHAR(100) NULL COMMENT 'Process ID that locked this',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `idx_journey_user` (`journey_id`, `user_id`),
  INDEX `idx_execution_queue` (`status`, `next_execution_at`, `is_locked`),
  INDEX `idx_user_status` (`user_id`, `status`),
  INDEX `idx_current_step` (`current_step_id`),
  INDEX `idx_locked` (`is_locked`, `locked_at`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`current_step_id`) REFERENCES `journey_steps`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. NOTIFICATION LOGS
-- Tracks all notifications sent
CREATE TABLE IF NOT EXISTS `notification_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `execution_id` BIGINT UNSIGNED NULL,
  `journey_id` BIGINT UNSIGNED NOT NULL,
  `step_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `notification_type` VARCHAR(50) NOT NULL COMMENT 'push, sms, email',
  `provider` VARCHAR(50) DEFAULT 'onesignal',
  `external_id` VARCHAR(255) NULL COMMENT 'OneSignal notification ID',
  `title` VARCHAR(255) NOT NULL,
  `body` TEXT NOT NULL,
  `data_payload` JSON NULL COMMENT 'Custom data sent',
  `status` ENUM('pending', 'sent', 'delivered', 'opened', 'failed', 'skipped') NOT NULL DEFAULT 'pending',
  `sent_at` TIMESTAMP NULL,
  `delivered_at` TIMESTAMP NULL,
  `opened_at` TIMESTAMP NULL,
  `failed_reason` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_user_sent` (`user_id`, `sent_at`),
  INDEX `idx_journey_status` (`journey_id`, `status`),
  INDEX `idx_execution` (`execution_id`),
  INDEX `idx_external` (`external_id`),
  INDEX `idx_status_created` (`status`, `created_at`),
  FOREIGN KEY (`execution_id`) REFERENCES `journey_executions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`journey_id`) REFERENCES `journeys`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`step_id`) REFERENCES `journey_steps`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. ENGINE LOGS
-- System logs for execution engine
CREATE TABLE IF NOT EXISTS `engine_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `log_type` ENUM('info', 'warning', 'error', 'debug') NOT NULL DEFAULT 'info',
  `module` VARCHAR(100) NOT NULL COMMENT 'segmentation, execution, notification',
  `message` TEXT NOT NULL,
  `execution_id` BIGINT UNSIGNED NULL,
  `journey_id` BIGINT UNSIGNED NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `error_stack` TEXT NULL,
  `metadata` JSON NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_type_created` (`log_type`, `created_at`),
  INDEX `idx_module_created` (`module`, `created_at`),
  INDEX `idx_execution` (`execution_id`),
  INDEX `idx_journey` (`journey_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. FREQUENCY CAPS
-- Limits notification frequency per user
CREATE TABLE IF NOT EXISTS `frequency_caps` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `cap_date` DATE NOT NULL,
  `notification_count` INT DEFAULT 0,
  `last_notification_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `idx_user_date` (`user_id`, `cap_date`),
  INDEX `idx_date` (`cap_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. USER SEGMENTS (Cached segments for performance)
CREATE TABLE IF NOT EXISTS `user_segments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `criteria` JSON NOT NULL COMMENT 'Segmentation rules',
  `user_count` INT DEFAULT 0,
  `last_calculated_at` TIMESTAMP NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_created_by` (`created_by`),
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. SEGMENT USERS (Many-to-many for cached segments)
CREATE TABLE IF NOT EXISTS `segment_users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `segment_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `added_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `idx_segment_user` (`segment_id`, `user_id`),
  INDEX `idx_user` (`user_id`),
  FOREIGN KEY (`segment_id`) REFERENCES `user_segments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. ENGAGEMENT REPORTS (Aggregated statistics)
CREATE TABLE IF NOT EXISTS `engagement_reports` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `journey_id` BIGINT UNSIGNED NOT NULL,
  `report_date` DATE NOT NULL,
  `report_type` ENUM('daily', 'weekly', 'monthly') NOT NULL,
  `total_sent` INT DEFAULT 0,
  `total_delivered` INT DEFAULT 0,
  `total_opened` INT DEFAULT 0,
  `total_failed` INT DEFAULT 0,
  `total_skipped` INT DEFAULT 0,
  `unique_users` INT DEFAULT 0,
  `completed_journeys` INT DEFAULT 0,
  `open_rate` DECIMAL(5,2) DEFAULT 0.00 COMMENT 'Percentage',
  `completion_rate` DECIMAL(5,2) DEFAULT 0.00 COMMENT 'Percentage',
  `avg_time_to_complete_minutes` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `idx_journey_date_type` (`journey_id`, `report_date`, `report_type`),
  INDEX `idx_date` (`report_date`),
  INDEX `idx_type` (`report_type`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. JOURNEY EVENTS (Triggers for event-based journeys)
CREATE TABLE IF NOT EXISTS `journey_events` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_name` VARCHAR(100) NOT NULL COMMENT 'user_registered, job_posted, profile_completed',
  `user_id` BIGINT UNSIGNED NOT NULL,
  `event_data` JSON NULL COMMENT 'Additional event metadata',
  `processed` TINYINT(1) DEFAULT 0,
  `processed_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_event_processed` (`event_name`, `processed`, `created_at`),
  INDEX `idx_user` (`user_id`),
  INDEX `idx_created` (`created_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SAMPLE SEGMENTATION CRITERIA JSON STRUCTURE
-- =====================================================
/*
{
  "operator": "AND",
  "conditions": [
    { "field": "role", "operator": "equals", "value": "worker" },
    { "field": "city", "operator": "in", "value": ["Mumbai", "Pune"] },
    { "field": "last_login_days", "operator": "greater_than", "value": 7 },
    { "field": "profile_completed", "operator": "equals", "value": 1 },
    { "field": "applied_jobs_count", "operator": "less_than", "value": 5 }
  ]
}
*/

-- =====================================================
-- SAMPLE STEP CONFIGURATION JSON
-- =====================================================
/*
NOTIFICATION STEP:
{
  "title": "Complete your profile!",
  "body": "Hi {{name}}, complete your profile to get more job matches.",
  "action": {
    "type": "deeplink",
    "url": "ujobs://profile"
  },
  "image_url": null
}

DELAY STEP:
{
  "duration": 3,
  "unit": "days"
}

CONDITION STEP:
{
  "condition_type": "user_attribute",
  "field": "profile_completed",
  "operator": "equals",
  "value": 1
}
*/

-- =====================================================
-- INDEXES FOR PERFORMANCE
-- =====================================================

-- For existing users table (if not exists)
-- ALTER TABLE `users` ADD INDEX `idx_role_city` (`role`, `city`);
-- ALTER TABLE `users` ADD INDEX `idx_created_at` (`created_at`);
-- ALTER TABLE `users` ADD INDEX `idx_device_id` (`device_id`(100));

-- For better notification tracking
-- ALTER TABLE `users` ADD COLUMN `last_notification_at` TIMESTAMP NULL AFTER `fcm_device_id`;
-- ALTER TABLE `users` ADD COLUMN `notification_count_today` INT DEFAULT 0 AFTER `last_notification_at`;

-- =====================================================
-- CLEANUP QUERIES (Run periodically via cron)
-- =====================================================
-- Delete old logs (older than 90 days)
-- DELETE FROM engine_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
-- DELETE FROM notification_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Remove old frequency caps (older than 30 days)
-- DELETE FROM frequency_caps WHERE cap_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Clear processed events (older than 7 days)
-- DELETE FROM journey_events WHERE processed = 1 AND processed_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Release stuck locks (older than 1 hour)
-- UPDATE journey_executions SET is_locked = 0, locked_at = NULL, locked_by = NULL 
-- WHERE is_locked = 1 AND locked_at < DATE_SUB(NOW(), INTERVAL 1 HOUR);
