-- ====================================================================
-- JOURNEY COPILOT - COMPLETE CONSOLIDATED SERVER SETUP
-- Date: March 26, 2026
-- 
-- Description: 
-- This script contains the COMPLETE database setup for the automation system.
-- Includes schema, notification settings, and baseline journeys.
-- ====================================================================

-- 1. JOURNEY DEFINITIONS
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', 'manual') NOT NULL DEFAULT 'event',
  `trigger_event` JSON DEFAULT NULL,
  `trigger_segment` JSON DEFAULT NULL,
  `target_role` ENUM('worker', 'hirer', 'both') NOT NULL DEFAULT 'both',
  `status` ENUM('draft', 'active', 'paused', 'archived') NOT NULL DEFAULT 'draft',
  `priority` INT DEFAULT 5,
  `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_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. JOURNEY STEPS
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,
  `step_type` ENUM('trigger', 'delay', 'wait', 'condition', 'notification', 'exit', 'candidate_suggestion', 'job_suggestion') NOT NULL,
  `step_name` VARCHAR(255) NOT NULL,
  `step_config` JSON NOT NULL,
  `next_step_id` BIGINT UNSIGNED NULL,
  `condition_true_step_id` BIGINT UNSIGNED NULL,
  `condition_false_step_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_journey_order` (`journey_id`, `step_order`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. JOURNEY EXECUTIONS
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,
  `user_type` ENUM('Worker', 'Hirer') DEFAULT 'Worker',
  `current_step_id` BIGINT UNSIGNED NULL,
  `status` ENUM('pending', 'running', 'paused', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
  `next_execution_at` TIMESTAMP NULL,
  `started_at` TIMESTAMP NULL,
  `completed_at` TIMESTAMP NULL,
  `retry_count` INT DEFAULT 0,
  `error_message` TEXT NULL,
  `execution_context` JSON NULL,
  `is_locked` TINYINT(1) DEFAULT 0,
  `locked_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `idx_journey_user_type` (`journey_id`, `user_id`, `user_type`),
  INDEX `idx_execution_queue` (`status`, `next_execution_at`, `is_locked`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. NOTIFICATION LOGS
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,
  `user_type` VARCHAR(20) DEFAULT 'Hirer',
  `notification_type` VARCHAR(50) DEFAULT 'push',
  `provider` VARCHAR(50) DEFAULT 'onesignal',
  `external_id` VARCHAR(255) NULL,
  `title` VARCHAR(255) NOT NULL,
  `body` TEXT NOT NULL,
  `status` ENUM('pending', 'sent', 'delivered', 'opened', 'clicked', 'failed', 'skipped') NOT NULL DEFAULT 'pending',
  `sent_at` TIMESTAMP NULL,
  `delivered_at` TIMESTAMP NULL,
  `opened_at` TIMESTAMP NULL,
  `clicked_at` TIMESTAMP 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`),
  UNIQUE KEY `idx_execution_step` (execution_id, step_id),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. ERROR LOGS
CREATE TABLE IF NOT EXISTS `error_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `error_type` VARCHAR(100) NOT NULL,
  `error_code` VARCHAR(50) DEFAULT NULL,
  `error_message` TEXT NOT NULL,
  `error_stack` TEXT DEFAULT NULL,
  `journey_id` BIGINT UNSIGNED DEFAULT NULL,
  `user_id` BIGINT UNSIGNED DEFAULT NULL,
  `user_type` ENUM('Worker', 'Hirer') DEFAULT 'Worker',
  `severity` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
  `status` ENUM('new', 'investigating', 'resolved', 'ignored') DEFAULT 'new',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_journey_id` (`journey_id`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. DAILY ANALYTICS
CREATE TABLE IF NOT EXISTS `daily_analytics` (
  `report_date` date NOT NULL,
  `total_notifications` int DEFAULT 0,
  `total_delivered` int DEFAULT 0,
  `total_opened` int DEFAULT 0,
  `total_clicked` int DEFAULT 0,
  `total_conversions` int DEFAULT 0,
  `active_journeys` int DEFAULT 0,
  `active_users` int DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`report_date`),
  INDEX `idx_report_date` (`report_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. NOTIFICATION SETTINGS
CREATE TABLE IF NOT EXISTS `notification_settings` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `setting_key` VARCHAR(100) UNIQUE NOT NULL,
  `setting_value` TEXT,
  `description` VARCHAR(500)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. SCHEDULED NOTIFICATIONS
CREATE TABLE IF NOT EXISTS `scheduled_notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `user_type` VARCHAR(20) DEFAULT 'Hirer',
  `title` VARCHAR(255) NOT NULL,
  `body` TEXT NOT NULL,
  `scheduled_for` TIMESTAMP NOT NULL,
  `status` ENUM('pending', 'sent', 'failed', 'no_device', 'cancelled', 'paused') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_scheduled_status` (`status`, `scheduled_for`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. FREQUENCY CAPS
CREATE TABLE IF NOT EXISTS `frequency_caps` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `user_type` VARCHAR(20) DEFAULT 'Worker',
  `cap_date` DATE NOT NULL,
  `notification_count` INT DEFAULT 0,
  `last_notification_at` TIMESTAMP NULL,
  UNIQUE KEY `idx_user_type_date` (`user_id`, `user_type`, `cap_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------
-- INITIAL DATA
-- --------------------------------------------------------------------

-- Default settings
INSERT IGNORE INTO notification_settings (setting_key, setting_value, description) VALUES
  ('notification_service_enabled', 'true', 'Master switch for all notification services'),
  ('onesignal_enabled', 'true', 'Enable OneSignal'),
  ('firebase_enabled', 'true', 'Enable Firebase'),
  ('primary_service', 'onesignal', 'Default provider'),
  ('fallback_enabled', 'true', 'Try second if first fails');
SET @owner_id = IFNULL(@owner_id, 1);

-- JOURNEY 1: Candidate Suggestion
INSERT IGNORE INTO journeys (id, name, description, trigger_type, trigger_event, trigger_segment, target_role, status, created_by)
VALUES (101, 'Candidate Suggestion Engine', 'Daily AI-powered matching for employers', 'schedule', '{}', '{"group": "AND", "conditions": [{"field": "role", "operator": "equals", "value": "Hirer"}]}', 'hirer', 'active', @owner_id);

INSERT IGNORE INTO journey_steps (journey_id, step_order, step_type, step_name, step_config)
VALUES (101, 1, 'candidate_suggestion', 'AI Candidate Match', '{"limit": 5}');

-- JOURNEY 2: Job Alerts
INSERT IGNORE INTO journeys (id, name, description, trigger_type, trigger_event, trigger_segment, target_role, status, created_by)
VALUES (102, 'Daily Job Match Alerts', 'Personalized job recommendations for active workers', 'schedule', '{}', '{"group": "AND", "conditions": [{"field": "role", "operator": "equals", "value": "Worker"}]}', 'worker', 'active', @owner_id);

INSERT IGNORE INTO journey_steps (journey_id, step_order, step_type, step_name, step_config)
VALUES (102, 1, 'job_suggestion', 'Fetch Skills-Based Jobs', '{"limit": 3}');

-- JOURNEY 3: New User Welcome
INSERT IGNORE INTO journeys (id, name, description, trigger_type, trigger_event, trigger_segment, target_role, status, created_by)
VALUES (103, 'New User Welcome Flow', 'Automated onboarding for new signups', 'event', '{"event_name": "user_registered"}', null, 'both', 'active', @owner_id);

INSERT IGNORE INTO journey_steps (journey_id, step_order, step_type, step_name, step_config)
VALUES (103, 1, 'notification', 'Welcome Message', '{"title": "Welcome to UJobs!", "body": "Hi {{name}}, start exploring the best jobs near you today."}');
