-- =====================================================
-- JOURNEY COPILOT - COMPLETE DATABASE SETUP SCRIPT
-- =====================================================
-- This script contains all necessary database tables,
-- indexes, and configurations for Journey Copilot.
-- Run this once to set up a fresh database.
-- =====================================================

USE aayusha_backend;

-- =====================================================
-- SECTION 1: CORE TABLES
-- =====================================================

-- Journeys Table
CREATE TABLE IF NOT EXISTS `journeys` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `trigger_type` ENUM('manual', 'event', 'scheduled') NOT NULL DEFAULT 'manual',
  `trigger_config` JSON DEFAULT NULL,
  `status` ENUM('active', 'paused', 'archived') DEFAULT 'active',
  `created_by` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_trigger_type` (`trigger_type`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Journey Steps Table
CREATE TABLE IF NOT EXISTS `journey_steps` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `journey_id` INT NOT NULL,
  `step_order` INT NOT NULL,
  `step_type` ENUM('notification', 'wait', 'condition', 'action') NOT NULL,
  `config` JSON NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_journey_id` (`journey_id`),
  INDEX `idx_step_order` (`journey_id`, `step_order`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SECTION 2: NOTIFICATION TABLES
-- =====================================================

-- Notification Logs Table
CREATE TABLE IF NOT EXISTS `notification_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `user_type` ENUM('worker', 'hirer') DEFAULT 'worker',
  `journey_id` INT DEFAULT NULL,
  `step_id` INT DEFAULT NULL,
  `title` VARCHAR(255),
  `body` TEXT,
  `provider` ENUM('onesignal', 'firebase', 'both') NOT NULL,
  `external_id` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('sent', 'delivered', 'failed', 'clicked') DEFAULT 'sent',
  `error_message` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_user_type` (`user_type`),
  INDEX `idx_status` (`status`),
  INDEX `idx_provider` (`provider`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_journey_id` (`journey_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Scheduled Notifications Table
CREATE TABLE IF NOT EXISTS `scheduled_notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `user_type` ENUM('worker', 'hirer') DEFAULT 'worker',
  `journey_id` INT DEFAULT NULL,
  `step_id` INT DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `body` TEXT NOT NULL,
  `scheduled_for` DATETIME NOT NULL,
  `status` ENUM('pending', 'sent', 'failed', 'cancelled', 'paused') DEFAULT 'pending',
  `error_message` TEXT DEFAULT NULL,
  `sent_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status_scheduled` (`status`, `scheduled_for`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_user_type` (`user_type`),
  INDEX `idx_journey_id` (`journey_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notification Settings Table
CREATE TABLE IF NOT EXISTS `notification_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `onesignal_enabled` BOOLEAN DEFAULT TRUE,
  `firebase_enabled` BOOLEAN DEFAULT FALSE,
  `default_provider` ENUM('onesignal', 'firebase', 'both') DEFAULT 'onesignal',
  `batch_size` INT DEFAULT 500,
  `rate_limit_per_minute` INT DEFAULT 30,
  `retry_failed_notifications` BOOLEAN DEFAULT TRUE,
  `max_retry_attempts` INT DEFAULT 3,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT INTO `notification_settings` 
  (`onesignal_enabled`, `firebase_enabled`, `default_provider`, `batch_size`, `rate_limit_per_minute`)
VALUES 
  (TRUE, FALSE, 'onesignal', 1000, 30)
ON DUPLICATE KEY UPDATE 
  `updated_at` = CURRENT_TIMESTAMP;

-- =====================================================
-- SECTION 3: JOURNEY EXECUTION TABLES
-- =====================================================

-- Journey Executions Table
CREATE TABLE IF NOT EXISTS `journey_executions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `journey_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `user_type` ENUM('worker', 'hirer') DEFAULT 'worker',
  `current_step_id` INT DEFAULT NULL,
  `status` ENUM('in_progress', 'completed', 'failed', 'paused') DEFAULT 'in_progress',
  `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  `error_message` TEXT DEFAULT NULL,
  `metadata` JSON DEFAULT NULL,
  INDEX `idx_journey_user` (`journey_id`, `user_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_user_type` (`user_type`),
  INDEX `idx_started_at` (`started_at`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Journey Execution Logs Table (for analytics)
CREATE TABLE IF NOT EXISTS `journey_execution_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `journey_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `user_type` ENUM('worker', 'hirer') DEFAULT 'worker',
  `step_id` INT DEFAULT NULL,
  `action` VARCHAR(100) NOT NULL,
  `execution_status` ENUM('started', 'in_progress', 'completed', 'failed', 'abandoned') DEFAULT 'in_progress',
  `details` JSON DEFAULT NULL,
  `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  INDEX `idx_journey_id` (`journey_id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_user_type` (`user_type`),
  INDEX `idx_execution_status` (`execution_status`),
  INDEX `idx_started_at` (`started_at`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Journey Error Logs Table
CREATE TABLE IF NOT EXISTS `journey_error_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `journey_id` INT NOT NULL,
  `user_id` INT DEFAULT NULL,
  `user_type` ENUM('worker', 'hirer') DEFAULT 'worker',
  `step_id` INT DEFAULT NULL,
  `error_type` VARCHAR(100) NOT NULL,
  `error_message` TEXT NOT NULL,
  `stack_trace` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_journey_id` (`journey_id`),
  INDEX `idx_error_type` (`error_type`),
  INDEX `idx_user_type` (`user_type`),
  INDEX `idx_created_at` (`created_at`),
  FOREIGN KEY (`journey_id`) REFERENCES `journeys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SECTION 4: ANALYTICS & REPORTING TABLES
-- =====================================================

-- Daily Analytics Aggregation Table
CREATE TABLE IF NOT EXISTS `daily_analytics` (
  `report_date` DATE PRIMARY KEY,
  `total_executions` INT DEFAULT 0,
  `completed_executions` INT DEFAULT 0,
  `failed_executions` INT DEFAULT 0,
  `abandoned_executions` INT DEFAULT 0,
  `in_progress_executions` INT DEFAULT 0,
  `unique_users` INT DEFAULT 0,
  `total_notifications` INT DEFAULT 0,
  `sent_notifications` INT DEFAULT 0,
  `failed_notifications` INT DEFAULT 0,
  `active_journeys` INT DEFAULT 0,
  `avg_execution_duration_seconds` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_report_date` (`report_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SECTION 5: PERFORMANCE INDEXES
-- =====================================================

-- User table indexes for segmentation performance
-- Note: These may fail if indexes already exist - safe to ignore errors

-- 1. Composite index for role + city filtering (most common query)
ALTER TABLE `users` 
ADD INDEX IF NOT EXISTS `idx_role_city` (`role`, `city`);

-- 2. Index on created_at for time-based segmentation
ALTER TABLE `users` 
ADD INDEX IF NOT EXISTS `idx_created_at` (`created_at`);

-- 3. Index on fcm_device_id for notification targeting (partial index for large text)
ALTER TABLE `users` 
ADD INDEX IF NOT EXISTS `idx_fcm_device_id` (`fcm_device_id`(100));

-- 4. Index on device_id for OneSignal targeting
ALTER TABLE `users` 
ADD INDEX IF NOT EXISTS `idx_device_id` (`device_id`(100));

-- 5. Composite index for status + updated_at (for last_login_days)
ALTER TABLE `users` 
ADD INDEX IF NOT EXISTS `idx_status_updated` (`status`, `updated_at`);

-- 6. Index on updated_at for computed fields
ALTER TABLE `users` 
ADD INDEX IF NOT EXISTS `idx_updated_at` (`updated_at`);

-- 7. Index on is_complete for profile completion filtering
ALTER TABLE `users` 
ADD INDEX IF NOT EXISTS `idx_is_complete` (`is_complete`);

-- Workers table indexes (if exists)
ALTER TABLE `workers` 
ADD INDEX IF NOT EXISTS `idx_worker_created_at` (`created_at`);

ALTER TABLE `workers` 
ADD INDEX IF NOT EXISTS `idx_worker_status` (`status`);

-- =====================================================
-- SECTION 6: DATABASE CONFIGURATION
-- =====================================================

-- Set MySQL timezone to IST (India Standard Time)
SET GLOBAL time_zone = '+05:30';
SET SESSION time_zone = '+05:30';

-- =====================================================
-- SECTION 7: VERIFICATION QUERIES
-- =====================================================

-- Verify all tables created
SELECT 
    TABLE_NAME, 
    TABLE_ROWS, 
    CREATE_TIME,
    UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN (
    'journeys', 
    'journey_steps', 
    'journey_executions',
    'journey_execution_logs',
    'journey_error_logs',
    'notification_logs', 
    'scheduled_notifications',
    'notification_settings',
    'daily_analytics'
  )
ORDER BY TABLE_NAME;

-- Verify indexes on users table
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('users', 'workers')
  AND INDEX_NAME != 'PRIMARY'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- =====================================================
-- SETUP COMPLETE
-- =====================================================
-- All tables, indexes, and configurations have been applied.
-- If you see any "Duplicate key" errors for indexes, that's normal
-- (it means the index already exists).
-- 
-- Next steps:
-- 1. Configure your .env file with database credentials
-- 2. Start backend: cd backend && npm start
-- 3. Access frontend: http://localhost:3001
-- =====================================================
