-- ============================================================
-- OTIS LIFE SYSTEM — COMPLETE DATABASE SCHEMA v3.0
-- ============================================================
-- Single authoritative file. Import this on a clean database.
-- Compatible with MySQL 5.7+ / MariaDB 10.3+ (Namecheap cPanel)
--
-- Usage:
--   mysql -u root -p OTIS < otis_full_schema.sql
--   OR import via phpMyAdmin
-- ============================================================

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

-- ============================================================
-- 1. CORE — USERS & SETTINGS
-- ============================================================

CREATE TABLE IF NOT EXISTS `users` (
  `id`            int(11)      NOT NULL AUTO_INCREMENT,
  `username`      varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `email`         varchar(255) DEFAULT NULL,
  `created_at`    timestamp    DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_username` (`username`),
  UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default demo user (password: password)
INSERT IGNORE INTO `users` (`id`, `username`, `password_hash`)
VALUES (1, 'otis', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

CREATE TABLE IF NOT EXISTS `user_settings` (
  `id`               int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`          int(11)     NOT NULL,
  `theme`            varchar(20) DEFAULT 'light',
  `timezone`         varchar(60) DEFAULT 'Africa/Cairo',
  `currency`         varchar(10) DEFAULT 'EGP',
  `language`         varchar(10) DEFAULT 'ar',
  `personality_mode` varchar(20) DEFAULT 'calm',
  `focus_mode`       tinyint(1)  DEFAULT 0,
  `onboarding_done`  tinyint(1)  DEFAULT 0,
  `updated_at`       timestamp   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_settings` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. SECURITY — RATE LIMITING & LOGS
-- ============================================================

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id`           int(11)     NOT NULL AUTO_INCREMENT,
  `ip_address`   varchar(45) NOT NULL,
  `username`     varchar(255) DEFAULT NULL,
  `attempted_at` timestamp   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ip_time` (`ip_address`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `action_logs` (
  `id`          int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`     int(11)     NOT NULL,
  `module`      varchar(50) NOT NULL,
  `action`      varchar(50) NOT NULL,
  `description` text,
  `record_id`   int(11)     DEFAULT NULL,
  `created_at`  timestamp   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_module` (`user_id`, `module`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id`         int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)     NOT NULL,
  `title`      varchar(255) NOT NULL,
  `message`    text,
  `type`       varchar(50) DEFAULT 'info',
  `is_read`    tinyint(1)  DEFAULT 0,
  `created_at` timestamp   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_unread` (`user_id`, `is_read`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `smart_reminders` (
  `id`          int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`     int(11)     NOT NULL,
  `module_type` varchar(50) DEFAULT NULL,
  `category`    varchar(100) NOT NULL,
  `message`     text,
  `priority`    enum('low','medium','high') DEFAULT 'medium',
  `status`      enum('pending','done','ignored') DEFAULT 'pending',
  `trigger_time` timestamp   DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` timestamp   NULL DEFAULT NULL,
  `created_at`  timestamp   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`, `status`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `reminder_logs` (
  `id`                 int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`            int(11)     NOT NULL,
  `reminder_category`  varchar(100) DEFAULT NULL,
  `module_type`        varchar(50) DEFAULT NULL,
  `priority`           varchar(20) DEFAULT NULL,
  `trigger_time`       timestamp   NULL DEFAULT NULL,
  `action_taken`       varchar(50) DEFAULT NULL,
  `message`            text,
  `created_at`         timestamp   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. AI ADVISOR ENGINE
-- ============================================================

CREATE TABLE IF NOT EXISTS `advisor_memory` (
  `id`           int(11)      NOT NULL AUTO_INCREMENT,
  `user_id`      int(11)      NOT NULL,
  `insight_key`  varchar(100) NOT NULL,
  `shown_at`     timestamp    DEFAULT CURRENT_TIMESTAMP,
  `severity`     varchar(20)  DEFAULT 'low',
  `dismissed`    tinyint(1)   DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_user_key` (`user_id`, `insight_key`),
  KEY `idx_shown_at` (`user_id`, `shown_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. HEALTH MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `health_profile` (
  `id`              int(11)        NOT NULL AUTO_INCREMENT,
  `user_id`         int(11)        NOT NULL,
  `age`             int(3)         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(5)     DEFAULT NULL,
  `diseases`        text           DEFAULT NULL,
  `water_goal`      int(11)        DEFAULT 8,
  `fasting_plan`    int(11)        DEFAULT 16,
  `vitality_score`  int(11)        DEFAULT 100,
  `sync_monthly_plan` tinyint(1)   DEFAULT 0,
  `created_at`      timestamp      DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_health_profile` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_weight_logs` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)      NOT NULL,
  `weight`     decimal(5,2) NOT NULL,
  `logged_at`  date         NOT NULL,
  `deleted_at` timestamp    NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `logged_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_blood_sugar` (
  `id`               int(11)                              NOT NULL AUTO_INCREMENT,
  `user_id`          int(11)                              NOT NULL,
  `date`             date                                 NOT NULL,
  `measurement_type` enum('first','second','third')       NOT NULL,
  `level`            int(11)                              NOT NULL,
  `is_after_meal`    tinyint(1)                           DEFAULT 0,
  `meal_id`          int(11)                              DEFAULT NULL,
  `fasting_id`       int(11)                              DEFAULT NULL,
  `deleted_at`       timestamp                            NULL DEFAULT NULL,
  `created_at`       timestamp                            DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_fasts` (
  `id`         int(11)                                NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)                                NOT NULL,
  `plan_hours` int(11)                                DEFAULT 16,
  `start_time` timestamp                              NULL DEFAULT NULL,
  `end_time`   timestamp                              NULL DEFAULT NULL,
  `status`     enum('active','completed','failed')    DEFAULT 'active',
  `deleted_at` timestamp                              NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`, `status`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_meals` (
  `id`             int(11)   NOT NULL AUTO_INCREMENT,
  `user_id`        int(11)   NOT NULL,
  `meal_type`      varchar(50) NOT NULL,
  `description`    text,
  `calories`       int(11)   DEFAULT 0,
  `eaten_at`       timestamp DEFAULT CURRENT_TIMESTAMP,
  `broke_fast_flag` tinyint(1) DEFAULT 0,
  `deleted_at`     timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_eaten` (`user_id`, `eaten_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_medications` (
  `id`          int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`     int(11)     NOT NULL,
  `med_name`    varchar(255) NOT NULL,
  `dose`        varchar(50) DEFAULT NULL,
  `frequency`   varchar(100) DEFAULT NULL,
  `med_type`    varchar(50) DEFAULT 'chronic',
  `status`      varchar(20) DEFAULT 'active',
  `is_active`   tinyint(1)  DEFAULT 1,
  `start_date`  date        DEFAULT NULL,
  `end_date`    date        DEFAULT NULL,
  `deleted_at`  timestamp   NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_active` (`user_id`, `is_active`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_med_logs` (
  `id`         int(11)   NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)   NOT NULL,
  `med_id`     int(11)   NOT NULL,
  `taken_at`   timestamp DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_taken` (`user_id`, `taken_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`med_id`)  REFERENCES `health_medications`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_water_logs` (
  `user_id`    int(11) NOT NULL,
  `log_date`   date    NOT NULL,
  `cups`       int(11) DEFAULT 0,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`, `log_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_activities` (
  `id`               int(11)                       NOT NULL AUTO_INCREMENT,
  `user_id`          int(11)                       NOT NULL,
  `activity_type`    varchar(100)                  NOT NULL,
  `duration_minutes` int(11)                       NOT NULL,
  `intensity`        enum('low','medium','high')   DEFAULT 'medium',
  `log_date`         date                          NOT NULL,
  `deleted_at`       timestamp                     NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `log_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_labs` (
  `id`           int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`      int(11)     NOT NULL,
  `test_name`    varchar(255) NOT NULL,
  `result_value` varchar(50) DEFAULT NULL,
  `unit`         varchar(20) DEFAULT NULL,
  `normal_range` varchar(50) DEFAULT NULL,
  `is_abnormal`  tinyint(1)  DEFAULT 0,
  `action_level` varchar(50) DEFAULT 'normal',
  `test_date`    date        NOT NULL,
  `deleted_at`   timestamp   NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `test_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_symptoms_log` (
  `id`           int(11)      NOT NULL AUTO_INCREMENT,
  `user_id`      int(11)      NOT NULL,
  `symptom_name` varchar(255) NOT NULL,
  `severity`     int(11)      DEFAULT 5,
  `start_date`   date         NOT NULL,
  `end_date`     date         DEFAULT NULL,
  `notes`        text,
  `deleted_at`   timestamp    NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_active` (`user_id`, `end_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `health_prescriptions` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)      NOT NULL,
  `med_id`     int(11)      DEFAULT NULL,
  `doctor`     varchar(255) DEFAULT NULL,
  `notes`      text,
  `issued_at`  date         DEFAULT NULL,
  `deleted_at` timestamp    NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. FINANCE MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `fin_categories` (
  `id`         int(11)                  NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)                  NOT NULL,
  `type`       enum('income','expense') NOT NULL,
  `name`       varchar(100)             NOT NULL,
  `deleted_at` timestamp                NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_incomes` (
  `id`            int(11)       NOT NULL AUTO_INCREMENT,
  `user_id`       int(11)       NOT NULL,
  `source`        varchar(255)  NOT NULL,
  `amount`        decimal(15,2) NOT NULL,
  `date_received` date          NOT NULL,
  `dist_personal` decimal(15,2) DEFAULT 0,
  `dist_business` decimal(15,2) DEFAULT 0,
  `dist_savings`  decimal(15,2) DEFAULT 0,
  `dist_charity`  decimal(15,2) DEFAULT 0,
  `deleted_at`    timestamp     NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `date_received`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_expenses` (
  `id`           int(11)                                         NOT NULL AUTO_INCREMENT,
  `user_id`      int(11)                                         NOT NULL,
  `category`     varchar(100)                                    NOT NULL,
  `type`         enum('essential','unessential','charity','business') NOT NULL,
  `amount`       decimal(15,2)                                   NOT NULL,
  `expense_date` date                                            NOT NULL,
  `notes`        text,
  `deleted_at`   timestamp                                       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `expense_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_saving_goals` (
  `id`             int(11)       NOT NULL AUTO_INCREMENT,
  `user_id`        int(11)       NOT NULL,
  `title`          varchar(255)  NOT NULL,
  `target_amount`  decimal(15,2) DEFAULT 0.00,
  `current_amount` decimal(15,2) DEFAULT 0.00,
  `expected_date`  date          DEFAULT NULL,
  `notes`          text,
  `deleted_at`     timestamp     NULL DEFAULT NULL,
  `created_at`     timestamp     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_installments` (
  `id`               int(11)       NOT NULL AUTO_INCREMENT,
  `user_id`          int(11)       NOT NULL,
  `name`             varchar(255)  NOT NULL,
  `icon`             varchar(50)   DEFAULT 'fa-box',
  `total_months`     int(11)       NOT NULL,
  `monthly_amount`   decimal(15,2) NOT NULL,
  `start_date`       date          NOT NULL,
  `paid_months_json` text,
  `deleted_at`       timestamp     NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_debts` (
  `id`          int(11)                     NOT NULL AUTO_INCREMENT,
  `user_id`     int(11)                     NOT NULL,
  `person_name` varchar(255)                NOT NULL,
  `amount`      decimal(15,2)               NOT NULL,
  `type`        enum('owed','to_receive')   NOT NULL,
  `status`      enum('pending','paid')      DEFAULT 'pending',
  `due_date`    date                        DEFAULT NULL,
  `deleted_at`  timestamp                   NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_investments` (
  `id`            int(11)       NOT NULL AUTO_INCREMENT,
  `user_id`       int(11)       NOT NULL,
  `asset_name`    varchar(255)  NOT NULL,
  `asset_type`    varchar(50)   NOT NULL,
  `buy_price`     decimal(15,2) NOT NULL,
  `quantity`      decimal(15,4) NOT NULL,
  `current_value` decimal(15,2) NOT NULL,
  `deleted_at`    timestamp     NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. SPIRITUAL MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `spiritual_daily_logs` (
  `id`                int(11) NOT NULL AUTO_INCREMENT,
  `user_id`           int(11) NOT NULL,
  `date`              date    NOT NULL,
  `heart_state`       varchar(50)  DEFAULT NULL,
  `daily_rating`      int(1)       DEFAULT NULL,
  `daily_goal`        text,
  `final_status`      enum('perfect','partial','weak') DEFAULT NULL,
  `final_score`       int(11)      DEFAULT NULL,
  `consistency_score` int(11)      DEFAULT 0,
  `spiritual_state`   enum('high','medium','low') DEFAULT 'medium',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_date` (`user_id`, `date`),
  KEY `idx_user_date` (`user_id`, `date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `spiritual_prayers` (
  `id`      int(11)    NOT NULL AUTO_INCREMENT,
  `user_id` int(11)    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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_date` (`user_id`, `date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `spiritual_adhkar` (
  `id`           int(11)    NOT NULL AUTO_INCREMENT,
  `user_id`      int(11)    NOT NULL,
  `date`         date       NOT NULL,
  `morning_done` tinyint(1) DEFAULT 0,
  `evening_done` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_date` (`user_id`, `date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `spiritual_quran` (
  `id`             int(11)    NOT NULL AUTO_INCREMENT,
  `user_id`        int(11)    NOT NULL,
  `date`           date       NOT NULL,
  `wird_completed` tinyint(1) DEFAULT 0,
  `yaseen`         tinyint(1) DEFAULT 0,
  `waqiah`         tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_date` (`user_id`, `date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `spiritual_charity` (
  `id`        int(11)       NOT NULL AUTO_INCREMENT,
  `user_id`   int(11)       NOT NULL,
  `date`      date          NOT NULL,
  `amount`    decimal(10,2) DEFAULT 0,
  `completed` tinyint(1)    DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_date` (`user_id`, `date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. TODO & JOURNAL MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `todo_tasks` (
  `id`             int(11)                                    NOT NULL AUTO_INCREMENT,
  `user_id`        int(11)                                    NOT NULL,
  `title`          varchar(255)                               NOT NULL,
  `description`    text,
  `category`       varchar(100)                               DEFAULT 'عام',
  `priority`       enum('low','medium','high')                DEFAULT 'medium',
  `estimated_time` int(11)                                    DEFAULT 0,
  `energy_level`   enum('low','medium','high')                DEFAULT 'medium',
  `target_date`    date                                       NOT NULL,
  `due_date`       date                                       DEFAULT NULL,
  `status`         enum('pending','in_progress','done','skipped') DEFAULT 'pending',
  `is_daily_focus` tinyint(1)                                 DEFAULT 0,
  `skill_id`       int(11)                                    DEFAULT NULL,
  `deleted_at`     timestamp                                  NULL DEFAULT NULL,
  `updated_at`     timestamp                                  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at`     timestamp                                  DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_date_status` (`user_id`, `target_date`, `status`),
  KEY `idx_user_duedate` (`user_id`, `due_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `todo_daily_logs` (
  `id`           int(11)   NOT NULL AUTO_INCREMENT,
  `user_id`      int(11)   NOT NULL,
  `log_date`     date      NOT NULL,
  `achievements` text,
  `failures`     text,
  `notes`        text,
  `rating`       int(1)    DEFAULT 0,
  `deleted_at`   timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_date` (`user_id`, `log_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. JOURNAL MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `journal_entries` (
  `id`          int(11)   NOT NULL AUTO_INCREMENT,
  `user_id`     int(11)   NOT NULL,
  `date`        date      NOT NULL,
  `mood_score`  int(11)   DEFAULT 5,
  `energy`      varchar(20) DEFAULT NULL,
  `content`     longtext,
  `tags`        varchar(500) DEFAULT NULL,
  `deleted_at`  timestamp NULL DEFAULT NULL,
  `created_at`  timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. CALENDAR MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `calendar_events` (
  `id`                  int(11)                                     NOT NULL AUTO_INCREMENT,
  `user_id`             int(11)                                     NOT NULL,
  `title`               varchar(255)                                NOT NULL,
  `description`         text,
  `category`            varchar(50)                                 DEFAULT 'other',
  `event_date`          date                                        NOT NULL,
  `start_time`          time                                        DEFAULT NULL,
  `end_time`            time                                        DEFAULT NULL,
  `color_hex`           varchar(10)                                 DEFAULT '#0d6efd',
  `status`              enum('active','archived')                   DEFAULT 'active',
  `is_completed`        tinyint(1)                                  DEFAULT 0,
  `recurrence_type`     enum('none','daily','weekly','monthly','yearly') DEFAULT 'none',
  `recurrence_end_date` date                                        DEFAULT NULL,
  `deleted_at`          timestamp                                   NULL DEFAULT NULL,
  `created_at`          timestamp                                   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `event_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `calendar_time_blocks` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)      NOT NULL,
  `title`      varchar(255) NOT NULL,
  `block_date` date         NOT NULL,
  `start_time` time         NOT NULL,
  `end_time`   time         NOT NULL,
  `category`   varchar(50)  DEFAULT 'focus',
  `color_hex`  varchar(10)  DEFAULT '#6366f1',
  `notes`      text,
  `deleted_at` timestamp    NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`, `block_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. HABITS MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `habits` (
  `id`         int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)     NOT NULL,
  `name`       varchar(255) NOT NULL,
  `icon`       varchar(50) DEFAULT 'fa-star',
  `color_hex`  varchar(10) DEFAULT '#0d6efd',
  `frequency`  enum('daily','weekly') DEFAULT 'daily',
  `deleted_at` timestamp   NULL DEFAULT NULL,
  `created_at` timestamp   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `habit_logs` (
  `id`         int(11)   NOT NULL AUTO_INCREMENT,
  `habit_id`   int(11)   NOT NULL,
  `user_id`    int(11)   NOT NULL,
  `log_date`   date      NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_habit_user_date` (`habit_id`, `user_id`, `log_date`),
  KEY `idx_user_date` (`user_id`, `log_date`),
  FOREIGN KEY (`habit_id`) REFERENCES `habits`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`)  REFERENCES `users`(`id`)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11. BEHAVIOR / DES MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `behavior_habits` (
  `id`          int(11)                    NOT NULL AUTO_INCREMENT,
  `user_id`     int(11)                    NOT NULL,
  `name`        varchar(255)               NOT NULL,
  `type`        enum('build','break')      DEFAULT 'build',
  `category`    varchar(100)               DEFAULT 'عام',
  `trigger`     varchar(255)               DEFAULT NULL,
  `identity_tag` varchar(100)              DEFAULT NULL,
  `deleted_at`  timestamp                  NULL DEFAULT NULL,
  `created_at`  timestamp                  DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `behavior_logs` (
  `id`         int(11)    NOT NULL AUTO_INCREMENT,
  `habit_id`   int(11)    NOT NULL,
  `user_id`    int(11)    NOT NULL,
  `date`       date       NOT NULL,
  `status`     tinyint(1) DEFAULT 0,
  `notes`      text,
  `created_at` timestamp  DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_habit_date` (`habit_id`, `date`),
  KEY `idx_user_date` (`user_id`, `date`),
  FOREIGN KEY (`habit_id`) REFERENCES `behavior_habits`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`)  REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `behavior_identity_scores` (
  `id`               int(11) NOT NULL AUTO_INCREMENT,
  `user_id`          int(11) NOT NULL,
  `date`             date    NOT NULL,
  `discipline_score` int(11) DEFAULT 0,
  `momentum_score`   int(11) DEFAULT 0,
  `created_at`       timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_date` (`user_id`, `date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. EDUCATION & SKILLS MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `edu_programs` (
  `id`         int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)     NOT NULL,
  `degree`     varchar(255) DEFAULT NULL,
  `type`       enum('bachelor','diploma','master','phd') DEFAULT 'bachelor',
  `university` varchar(255) DEFAULT NULL,
  `major`      varchar(255) DEFAULT NULL,
  `name`       varchar(255) DEFAULT NULL,
  `status`     enum('ongoing','completed') DEFAULT 'ongoing',
  `progress`   int(11)     DEFAULT 0,
  `start_date` date        DEFAULT NULL,
  `end_date`   date        DEFAULT NULL,
  `cost`       decimal(15,2) DEFAULT 0.00,
  `notes`      text,
  `created_at` timestamp   DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp   NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `edu_courses` (
  `id`               int(11)                                NOT NULL AUTO_INCREMENT,
  `user_id`          int(11)                                NOT NULL,
  `title`            varchar(255)                           NOT NULL,
  `category`         varchar(100)                           NOT NULL,
  `provider`         varchar(255)                           DEFAULT NULL,
  `is_paid`          tinyint(1)                             DEFAULT 0,
  `cost`             decimal(15,2)                          DEFAULT 0.00,
  `url`              text,
  `skill_id`         int(11)                                DEFAULT NULL,
  `progress_percent` int(11)                                DEFAULT 0,
  `status`           enum('planned','learning','completed','in_progress') DEFAULT 'planned',
  `deleted_at`       timestamp                              NULL DEFAULT NULL,
  `updated_at`       timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at`       timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`, `status`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `edu_skills` (
  `id`               int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`          int(11)     NOT NULL,
  `name`             varchar(255) NOT NULL,
  `type`             varchar(50) DEFAULT 'standalone',
  `track_name`       varchar(100) DEFAULT NULL,
  `category`         varchar(100) DEFAULT NULL,
  `mastery_level`    int(11)     DEFAULT 0,
  `progress_percent` int(11)     DEFAULT 0,
  `level`            int(11)     DEFAULT 1,
  `notes`            text,
  `deleted_at`       timestamp   NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 13. MONTHLY PLAN MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `monthly_plans` (
  `id`             int(11)     NOT NULL AUTO_INCREMENT,
  `user_id`        int(11)     NOT NULL,
  `month_key`      varchar(7)  NOT NULL,
  `goals_json`     longtext,
  `review_notes`   text,
  `overall_rating` int(1)      DEFAULT NULL,
  `created_at`     timestamp   DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     timestamp   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_month` (`user_id`, `month_key`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `monthly_plan_items` (
  `id`          int(11)                        NOT NULL AUTO_INCREMENT,
  `plan_id`     int(11)                        NOT NULL,
  `user_id`     int(11)                        NOT NULL,
  `title`       varchar(255)                   NOT NULL,
  `category`    varchar(100)                   DEFAULT 'عام',
  `status`      enum('pending','done','skipped') DEFAULT 'pending',
  `is_manual`   tinyint(1)                     DEFAULT 1,
  `notes`       text,
  `created_at`  timestamp                      DEFAULT CURRENT_TIMESTAMP,
  `deleted_at`  timestamp                      NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_plan` (`plan_id`),
  FOREIGN KEY (`plan_id`)  REFERENCES `monthly_plans`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`)  REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14. SECURE VAULT MODULE
-- ============================================================

CREATE TABLE IF NOT EXISTS `vault_entries` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `user_id`    int(11)      NOT NULL,
  `category`   varchar(50)  DEFAULT 'general',
  `title`      varchar(255) NOT NULL,
  `enc_data`   longtext     NOT NULL,
  `deleted_at` timestamp    NULL DEFAULT NULL,
  `created_at` timestamp    DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_cat` (`user_id`, `category`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 15. PERFORMANCE INDEXES
-- ============================================================

-- Ensure critical composite indexes exist (safe to re-run — IF NOT EXISTS)
ALTER TABLE `finance_expenses`   ADD INDEX IF NOT EXISTS `idx_user_date` (`user_id`, `expense_date`);
ALTER TABLE `todo_tasks`         ADD INDEX IF NOT EXISTS `idx_user_target` (`user_id`, `target_date`);
ALTER TABLE `spiritual_daily_logs` ADD INDEX IF NOT EXISTS `idx_user_date` (`user_id`, `date`);
ALTER TABLE `journal_entries`    ADD INDEX IF NOT EXISTS `idx_user_date` (`user_id`, `date`);
ALTER TABLE `behavior_logs`      ADD INDEX IF NOT EXISTS `idx_user_date` (`user_id`, `date`);
ALTER TABLE `habit_logs`         ADD INDEX IF NOT EXISTS `idx_user_date` (`user_id`, `log_date`);
ALTER TABLE `advisor_memory`     ADD INDEX IF NOT EXISTS `idx_user_key` (`user_id`, `insight_key`);

-- ============================================================
-- 16. SAFE ALTER MIGRATIONS
--     (Add missing columns to existing installations)
-- ============================================================

-- user_settings new columns
ALTER TABLE `user_settings` ADD COLUMN IF NOT EXISTS `onboarding_done` tinyint(1) DEFAULT 0;
ALTER TABLE `user_settings` ADD COLUMN IF NOT EXISTS `theme` varchar(20) DEFAULT 'light';

-- health_profile new columns
ALTER TABLE `health_profile` ADD COLUMN IF NOT EXISTS `vitality_score` int(11) DEFAULT 100;
ALTER TABLE `health_profile` ADD COLUMN IF NOT EXISTS `sync_monthly_plan` tinyint(1) DEFAULT 0;

-- health_medications new columns
ALTER TABLE `health_medications` ADD COLUMN IF NOT EXISTS `med_type` varchar(50) DEFAULT 'chronic';
ALTER TABLE `health_medications` ADD COLUMN IF NOT EXISTS `status` varchar(20) DEFAULT 'active';
ALTER TABLE `health_medications` ADD COLUMN IF NOT EXISTS `start_date` date DEFAULT NULL;
ALTER TABLE `health_medications` ADD COLUMN IF NOT EXISTS `end_date` date DEFAULT NULL;

-- health_labs new column
ALTER TABLE `health_labs` ADD COLUMN IF NOT EXISTS `action_level` varchar(50) DEFAULT 'normal';

-- calendar_events new column
ALTER TABLE `calendar_events` ADD COLUMN IF NOT EXISTS `is_completed` tinyint(1) DEFAULT 0;

-- todo_tasks new columns
ALTER TABLE `todo_tasks` ADD COLUMN IF NOT EXISTS `due_date` date DEFAULT NULL;
ALTER TABLE `todo_tasks` ADD COLUMN IF NOT EXISTS `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `todo_tasks` ADD COLUMN IF NOT EXISTS `created_at` timestamp DEFAULT CURRENT_TIMESTAMP;

-- spiritual_daily_logs extra columns
ALTER TABLE `spiritual_daily_logs` ADD COLUMN IF NOT EXISTS `final_status` enum('perfect','partial','weak') DEFAULT NULL;
ALTER TABLE `spiritual_daily_logs` ADD COLUMN IF NOT EXISTS `final_score` int(11) DEFAULT NULL;
ALTER TABLE `spiritual_daily_logs` ADD COLUMN IF NOT EXISTS `consistency_score` int(11) DEFAULT 0;
ALTER TABLE `spiritual_daily_logs` ADD COLUMN IF NOT EXISTS `spiritual_state` enum('high','medium','low') DEFAULT 'medium';

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- END OF SCHEMA
-- Generated: OTIS Life System v3.0
-- ============================================================
