-- ============================================================
-- OTIS Life System — Final Safe Migration
-- Version: 2026-04
-- ============================================================
-- RULES: NO DROP TABLE, NO TRUNCATE, NO DATA LOSS
-- Runs safely on BOTH existing and empty databases.
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';

-- ============================================================
-- SYSTEM — Users & Settings
-- ============================================================

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `username` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `ip_address` VARCHAR(45) NOT NULL,
  `username` VARCHAR(100) DEFAULT NULL,
  `attempted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_login_ip_time` (`ip_address`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `theme` ENUM('light','dark') DEFAULT 'dark',
  `timezone` VARCHAR(100) DEFAULT 'Africa/Cairo',
  `currency` VARCHAR(10) DEFAULT 'EGP',
  `focus_mode` TINYINT(1) DEFAULT 0,
  `personality_mode` ENUM('strict','calm') DEFAULT 'strict',
  `onboarding_done` TINYINT(1) DEFAULT 0,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_usersettings_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT,
  `type` VARCHAR(50) DEFAULT 'info',
  `is_read` TINYINT(1) DEFAULT 0,
  `is_pushed` TINYINT(1) DEFAULT 0,
  `action_target` VARCHAR(255) DEFAULT NULL,
  `action_type` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notif_user_read` (`user_id`, `is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `action_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `module` VARCHAR(100),
  `action` VARCHAR(255),
  `meta` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_action_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TODO & TASKS
-- ============================================================

CREATE TABLE IF NOT EXISTS `todo_tasks` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `category` VARCHAR(100) DEFAULT 'عام',
  `priority` ENUM('high','medium','low') DEFAULT 'medium',
  `estimated_time` INT DEFAULT 0,
  `energy_level` ENUM('low','medium','high') DEFAULT 'medium',
  `target_date` DATE DEFAULT NULL,
  `due_date` DATE DEFAULT NULL,
  `is_daily_focus` TINYINT(1) DEFAULT 0,
  `skill_id` INT DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT 'pending',
  `event_id` INT DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tasks_user_status` (`user_id`, `status`, `target_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `todo_daily_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `log_date` DATE NOT NULL,
  `achievements` TEXT,
  `failures` TEXT,
  `notes` TEXT,
  `rating` INT(1) DEFAULT 3,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_daily_logs_user_date` (`user_id`, `log_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `focus_sessions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `task_name` VARCHAR(255),
  `duration_minutes` INT DEFAULT 25,
  `completed` TINYINT(1) DEFAULT 0,
  `notes` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_focus_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- HABITS
-- ============================================================

CREATE TABLE IF NOT EXISTS `habits` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `icon` VARCHAR(50) DEFAULT 'fa-star',
  `color_hex` VARCHAR(10) DEFAULT '#8b5cf6',
  `frequency` ENUM('daily','weekly') DEFAULT 'daily',
  `habit_type` ENUM('build','break') DEFAULT 'build',
  `identity_statement` VARCHAR(255) DEFAULT NULL,
  `impact_category` ENUM('health','finance','productivity','spiritual','general') DEFAULT 'general',
  `impact_amount` DECIMAL(10,2) DEFAULT 0.00,
  `intensity` INT DEFAULT 3,
  `target_value` FLOAT DEFAULT NULL,
  `unit` VARCHAR(20) DEFAULT NULL,
  `last_alert_at` DATE DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_habits_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `habit_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `habit_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `log_date` DATE NOT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_habit_log_day` (`habit_id`, `user_id`, `log_date`),
  KEY `idx_habit_logs_user_date` (`user_id`, `log_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- FINANCE
-- ============================================================

CREATE TABLE IF NOT EXISTS `finance_expenses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `item_name` VARCHAR(200) NOT NULL DEFAULT '',
  `amount` DECIMAL(15,2) NOT NULL,
  `expense_date` DATE NOT NULL,
  `notes` TEXT,
  `is_impulsive` TINYINT(1) DEFAULT 0,
  `is_split` TINYINT(1) DEFAULT 0,
  `split_parent_id` INT DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_expense_user_date` (`user_id`, `expense_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `finance_incomes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `source` VARCHAR(200) NOT NULL DEFAULT '',
  `amount` DECIMAL(15,2) NOT NULL,
  `date_received` DATE NOT NULL,
  `notes` TEXT,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_income_user_date` (`user_id`, `date_received`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `finance_saving_goals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `target_amount` DECIMAL(15,2) NOT NULL,
  `current_amount` DECIMAL(15,2) DEFAULT 0,
  `expected_date` DATE DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `finance_debts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `creditor` VARCHAR(200) NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `remaining` DECIMAL(15,2) NOT NULL,
  `due_date` DATE DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT 'active',
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `finance_debt_payments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `debt_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `payment_date` DATE NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `finance_investments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `amount_invested` DECIMAL(15,2) NOT NULL,
  `current_value` DECIMAL(15,2) DEFAULT NULL,
  `category` VARCHAR(100) DEFAULT 'عام',
  `start_date` DATE DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `finance_subscriptions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `billing_cycle` VARCHAR(50) DEFAULT 'monthly',
  `next_billing_date` DATE DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `finance_installments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `item_name` VARCHAR(255) NOT NULL,
  `total_amount` DECIMAL(15,2) NOT NULL,
  `monthly_amount` DECIMAL(15,2) NOT NULL,
  `remaining_months` INT DEFAULT 0,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_categories` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `type` VARCHAR(20) DEFAULT 'expense',
  `icon` VARCHAR(50) DEFAULT 'fa-tag',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- HEALTH
-- ============================================================

CREATE TABLE IF NOT EXISTS `health_profile` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL UNIQUE,
  `age` INT DEFAULT NULL,
  `height` DECIMAL(5,2) DEFAULT NULL,
  `current_weight` DECIMAL(5,2) DEFAULT NULL,
  `target_weight` DECIMAL(5,2) DEFAULT NULL,
  `blood_type` VARCHAR(10) DEFAULT NULL,
  `diseases` TEXT,
  `water_goal` INT DEFAULT 2000,
  `fasting_plan` INT DEFAULT 16,
  `push_notifications` TINYINT(1) DEFAULT 0,
  `sync_calendar` TINYINT(1) DEFAULT 0,
  `sync_monthly_plan` TINYINT(1) DEFAULT 0,
  `advisor_integration` TINYINT(1) DEFAULT 1,
  `vitality_score` INT DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_fasts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `plan_hours` INT DEFAULT 16,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME DEFAULT NULL,
  `status` VARCHAR(20) DEFAULT 'active',
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_fasts_user_status` (`user_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_meals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `meal_type` VARCHAR(50) DEFAULT 'فطار',
  `description` TEXT,
  `feeling` VARCHAR(255) DEFAULT NULL,
  `eaten_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `broke_fast_flag` TINYINT(1) DEFAULT 0,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_meals_user_date` (`user_id`, `eaten_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_meal_items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(100) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_water_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `cups` INT NOT NULL DEFAULT 1,
  `log_date` DATE NOT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_water_user_date` (`user_id`, `log_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_weight_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `weight` DECIMAL(5,2) NOT NULL,
  `logged_at` DATE NOT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_blood_sugar` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `measurement_type` ENUM('first','second','third') DEFAULT 'first',
  `level` INT NOT NULL,
  `is_after_meal` TINYINT(1) DEFAULT 0,
  `meal_id` INT DEFAULT NULL,
  `fasting_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_medications` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `med_name` VARCHAR(100) NOT NULL,
  `dose` VARCHAR(50) DEFAULT NULL,
  `frequency` VARCHAR(100) DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `med_type` ENUM('chronic','prescription') DEFAULT 'chronic',
  `start_date` DATE DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `schedule` VARCHAR(255) DEFAULT NULL,
  `prescription_id` INT DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_med_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `med_id` INT NOT NULL,
  `taken_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_prescriptions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `doctor_type` VARCHAR(100) DEFAULT NULL,
  `clinic_name` VARCHAR(200) DEFAULT NULL,
  `visit_date` DATE DEFAULT NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_labs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `test_name` VARCHAR(200) NOT NULL,
  `result_value` VARCHAR(50) DEFAULT NULL,
  `unit` VARCHAR(50) DEFAULT NULL,
  `normal_range` VARCHAR(100) DEFAULT NULL,
  `is_abnormal` TINYINT(1) DEFAULT 0,
  `test_date` DATE NOT NULL,
  `file_path` VARCHAR(255) DEFAULT NULL,
  `analysis_status` VARCHAR(50) DEFAULT 'pending',
  `interpretation` TEXT,
  `recommendation` TEXT,
  `severity_level` VARCHAR(50) DEFAULT NULL,
  `trend_status` VARCHAR(50) DEFAULT NULL,
  `action_level` VARCHAR(50) DEFAULT NULL,
  `confidence_level` VARCHAR(50) DEFAULT NULL,
  `needs_review` TINYINT(1) DEFAULT 0,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_lab_insights` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `insight_text` TEXT,
  `priority_issue` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_goals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `target_value` DECIMAL(10,2) DEFAULT NULL,
  `current_value` DECIMAL(10,2) DEFAULT 0,
  `expected_date` DATE DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_activities` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `activity_type` VARCHAR(100) NOT NULL,
  `duration_minutes` INT DEFAULT 30,
  `intensity` VARCHAR(50) DEFAULT 'medium',
  `log_date` DATE NOT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_daily_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `log_date` DATE NOT NULL,
  `weight` DECIMAL(5,2) DEFAULT NULL,
  `fasting_hours` INT DEFAULT 0,
  `blood_sugar_fasting` INT DEFAULT NULL,
  `blood_sugar_after` INT DEFAULT NULL,
  `water_ml` INT DEFAULT 0,
  `food_streak_kept` TINYINT(1) DEFAULT 1,
  `medications_taken` TINYINT(1) DEFAULT 0,
  `activity_notes` TEXT,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `health_symptoms_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `symptom_name` VARCHAR(255) NOT NULL,
  `severity` TINYINT(2) DEFAULT 3,
  `start_date` DATE NOT NULL,
  `end_date` DATE DEFAULT NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- BEHAVIOR
-- ============================================================

CREATE TABLE IF NOT EXISTS `behavior_habits` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `severity` ENUM('low','medium','high') DEFAULT 'medium',
  `category` VARCHAR(100) DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `behavior_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `habit_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `status` TINYINT(1) DEFAULT 1,
  `notes` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_behavior_user_date` (`user_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SPIRITUAL
-- ============================================================

CREATE TABLE IF NOT EXISTS `spiritual_prayers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `fajr` TINYINT(1) DEFAULT 0,
  `dhuhr` TINYINT(1) DEFAULT 0,
  `asr` TINYINT(1) DEFAULT 0,
  `maghrib` TINYINT(1) DEFAULT 0,
  `isha` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_prayers_user_date` (`user_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `spiritual_quran` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `wird_completed` TINYINT(1) DEFAULT 0,
  `yaseen` TINYINT(1) DEFAULT 0,
  `waqiah` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `spiritual_adhkar` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `morning_done` TINYINT(1) DEFAULT 0,
  `evening_done` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `spiritual_charity` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `amount` DECIMAL(10,2) DEFAULT 0,
  `completed` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `spiritual_daily_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `heart_state` ENUM('حاضر','غائب','مشتت','نشط') DEFAULT 'حاضر',
  `daily_rating` INT DEFAULT 3,
  `daily_goal` VARCHAR(255) DEFAULT NULL,
  `final_status` ENUM('perfect','partial','weak') DEFAULT 'partial',
  `final_score` INT DEFAULT 0,
  `consistency_score` INT DEFAULT 0,
  `spiritual_state` ENUM('high','medium','low') DEFAULT 'medium',
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_spiritual_user_date` (`user_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `spiritual_goals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `type` ENUM('prayer','quran','charity','fasting','other') DEFAULT 'other',
  `target` INT DEFAULT 0,
  `progress` INT DEFAULT 0,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- JOURNAL
-- ============================================================

CREATE TABLE IF NOT EXISTS `daily_journal` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `content` TEXT,
  `mood` INT DEFAULT 3,
  `energy_level` INT DEFAULT 3,
  `tags` TEXT,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_journal_user_date` (`user_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- CALENDAR
-- ============================================================

CREATE TABLE IF NOT EXISTS `calendar_categories` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `color_hex` VARCHAR(10) DEFAULT '#6366f1',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `calendar_events` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `category` VARCHAR(100) DEFAULT 'other',
  `event_date` DATE NOT NULL,
  `start_time` TIME DEFAULT NULL,
  `end_time` TIME DEFAULT NULL,
  `color_hex` VARCHAR(10) DEFAULT '#6366f1',
  `status` VARCHAR(50) DEFAULT 'active',
  `recurrence_type` VARCHAR(50) DEFAULT 'none',
  `recurrence_end_date` DATE DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_events_user_date` (`user_id`, `event_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `calendar_time_blocks` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `block_date` DATE NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `color_hex` VARCHAR(10) DEFAULT '#6366f1',
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- EDUCATION
-- ============================================================

CREATE TABLE IF NOT EXISTS `edu_programs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `edu_paths` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `program_id` INT DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `edu_courses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `path_id` INT DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `provider` VARCHAR(200) DEFAULT NULL,
  `status` ENUM('not_started','learning','completed','paused') DEFAULT 'not_started',
  `link` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT,
  `priority` ENUM('high','medium','low') DEFAULT 'medium',
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `edu_skills` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `level` INT DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- GAMIFICATION
-- ============================================================

CREATE TABLE IF NOT EXISTS `user_xp` (
  `user_id` INT NOT NULL,
  `xp_points` INT NOT NULL DEFAULT 0,
  `level` INT NOT NULL DEFAULT 1,
  `last_gain_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `badges` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `icon` VARCHAR(100) DEFAULT 'fa-trophy',
  `condition_type` VARCHAR(100) NOT NULL,
  `condition_value` INT NOT NULL DEFAULT 1,
  `description` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_badges` (
  `user_id` INT NOT NULL,
  `badge_id` INT NOT NULL,
  `earned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`, `badge_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Legacy gamification tables (kept, not modified)
CREATE TABLE IF NOT EXISTS `gamification_profiles` (
  `user_id` INT PRIMARY KEY,
  `xp` INT DEFAULT 0,
  `level` INT DEFAULT 1,
  `current_title` VARCHAR(100) DEFAULT 'المبتدئ'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `gamification_badges` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- ADVISOR & INTELLIGENCE
-- ============================================================

CREATE TABLE IF NOT EXISTS `advisor_memory` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `advice_code` VARCHAR(100) NOT NULL,
  `last_shown_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `show_count` INT DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_advisor_user_code` (`user_id`, `advice_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `intelligence_cache` (
  `user_id` INT NOT NULL,
  `date_cached` DATE NOT NULL,
  `discipline_score` INT NOT NULL DEFAULT 100,
  `insights_json` TEXT,
  `decisions_json` TEXT,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- AUTOMATION
-- ============================================================

CREATE TABLE IF NOT EXISTS `automation_rules` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(100) NOT NULL UNIQUE,
  `name` VARCHAR(200) NOT NULL,
  `description` TEXT,
  `is_active` TINYINT(1) DEFAULT 1,
  `cooldown_hours` INT DEFAULT 24,
  `last_run_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `automation_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `rule_code` VARCHAR(100) NOT NULL,
  `triggered_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_taken` VARCHAR(255) DEFAULT NULL,
  `result` TEXT,
  PRIMARY KEY (`id`),
  KEY `idx_automation_user_rule` (`user_id`, `rule_code`, `triggered_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SMART REMINDERS & NUDGES
-- ============================================================

CREATE TABLE IF NOT EXISTS `smart_reminders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `module_type` VARCHAR(50) NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `message` TEXT NOT NULL,
  `priority` ENUM('low','medium','high') DEFAULT 'low',
  `status` ENUM('pending','done','ignored') DEFAULT 'pending',
  `trigger_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `smart_nudges_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `nudge_code` VARCHAR(50) NOT NULL,
  `shown_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `dismissed_at` DATETIME DEFAULT NULL,
  `dismiss_count` INT DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_nudge_user_code` (`user_id`, `nudge_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `reminder_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `reminder_category` VARCHAR(100) DEFAULT NULL,
  `module_type` VARCHAR(50) DEFAULT NULL,
  `priority` ENUM('low','medium','high') DEFAULT 'low',
  `trigger_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_taken` ENUM('completed','ignored','auto_resolved') DEFAULT 'ignored',
  `resolution_time` TIMESTAMP NULL DEFAULT NULL,
  `message` TEXT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- VAULT
-- ============================================================

CREATE TABLE IF NOT EXISTS `vault_items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `cipher_data` TEXT NOT NULL,
  `iv` VARCHAR(64) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_vault_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `vault_security` (
  `user_id` INT NOT NULL,
  `master_hash` VARCHAR(255) NOT NULL,
  `enc_salt` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- MONTHLY PLANS
-- ============================================================

CREATE TABLE IF NOT EXISTS `monthly_plans` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `month_key` VARCHAR(7) NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `reflection_worked` TEXT,
  `reflection_failed` TEXT,
  `reflection_why` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_monthly_plan` (`user_id`, `month_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `monthly_plan_categories` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `icon` VARCHAR(50) DEFAULT 'fa-star',
  `color_theme` VARCHAR(20) DEFAULT 'blue',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `monthly_goals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `plan_id` INT NOT NULL,
  `category_id` INT DEFAULT NULL,
  `category` VARCHAR(50) DEFAULT 'عام',
  `title` VARCHAR(255) NOT NULL,
  `target_value` INT DEFAULT 0,
  `current_progress` INT DEFAULT 0,
  `status` VARCHAR(20) DEFAULT 'active',
  `integration_type` VARCHAR(50) DEFAULT NULL,
  `integration_ref` VARCHAR(255) DEFAULT NULL,
  `is_primary` TINYINT(1) DEFAULT 0,
  `difficulty` ENUM('easy','medium','hard') DEFAULT 'medium',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ensure default user_xp row for existing users
INSERT IGNORE INTO `user_xp` (`user_id`, `xp_points`, `level`)
SELECT `id`, 0, 1 FROM `users`;

-- Ensure default user_settings for existing users
INSERT IGNORE INTO `user_settings` (`user_id`)
SELECT `id` FROM `users`;

-- ============================================================
-- SAFE ALTERS — Add missing columns to existing tables
-- ============================================================

DROP PROCEDURE IF EXISTS safe_add_column;
DELIMITER ;;
CREATE PROCEDURE safe_add_column(
  IN tbl VARCHAR(100), IN col VARCHAR(100), IN definition TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = tbl
      AND COLUMN_NAME = col
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', tbl, '` ADD COLUMN `', col, '` ', definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END;;
DELIMITER ;

-- todo_tasks: updated_at
CALL safe_add_column('todo_tasks', 'updated_at', 'TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER created_at');

-- focus_sessions: completed, notes
CALL safe_add_column('focus_sessions', 'completed', 'TINYINT(1) DEFAULT 0');
CALL safe_add_column('focus_sessions', 'notes', 'TEXT NULL DEFAULT NULL');

-- health_water_logs: deleted_at
CALL safe_add_column('health_water_logs', 'deleted_at', 'TIMESTAMP NULL DEFAULT NULL');

-- badges: description
CALL safe_add_column('badges', 'description', "VARCHAR(255) DEFAULT NULL");

-- daily_journal columns
CALL safe_add_column('daily_journal', 'mood', 'INT DEFAULT 3');
CALL safe_add_column('daily_journal', 'energy_level', 'INT DEFAULT 3');
CALL safe_add_column('daily_journal', 'tags', 'TEXT NULL DEFAULT NULL');
CALL safe_add_column('daily_journal', 'content', 'TEXT NULL DEFAULT NULL');

DROP PROCEDURE IF EXISTS safe_add_column;

-- ============================================================
-- SEED DATA — Default Badges (AFTER description column is added)
-- ============================================================

INSERT IGNORE INTO `badges` (`name`, `icon`, `condition_type`, `condition_value`, `description`)
VALUES
  ('مقاتل المهام',  'fa-sword',       'task_completed',    50,  'أكمل 50 مهمة'),
  ('منضبط',         'fa-fire',        'habit_done',        30,  '30 يوم التزام بالعادات'),
  ('صائم دائم',     'fa-moon',        'fast_completed',    20,  'أكمل 20 فترة صيام'),
  ('مستثمر',        'fa-wallet',      'finance_logged',    10,  'سجّل 10 حركات مالية');

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- END OF SAFE MIGRATION
-- ============================================================
