-- =============================================================
-- LifeOS 365 — Phase 1.5: Tasks, Goals, Habits, Habit Logs
-- Run AFTER 01_schema.sql.
-- =============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS `goals` (
  `id` CHAR(36) NOT NULL PRIMARY KEY,
  `user_id` CHAR(36) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `category` VARCHAR(64) NOT NULL DEFAULT 'personal',
  `target_value` DECIMAL(12,2) DEFAULT NULL,
  `current_value` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `unit` VARCHAR(32) DEFAULT NULL,
  `deadline` DATE DEFAULT NULL,
  `status` ENUM('active','completed','paused','abandoned') NOT NULL DEFAULT 'active',
  `priority` ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_goals_user` (`user_id`),
  KEY `idx_goals_status` (`user_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tasks` (
  `id` CHAR(36) NOT NULL PRIMARY KEY,
  `user_id` CHAR(36) NOT NULL,
  `goal_id` CHAR(36) DEFAULT NULL,
  `title` VARCHAR(500) NOT NULL,
  `notes` TEXT,
  `task_date` DATE NOT NULL,
  `priority_order` INT NOT NULL DEFAULT 0,
  `is_completed` TINYINT(1) NOT NULL DEFAULT 0,
  `completed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_tasks_user_date` (`user_id`,`task_date`),
  KEY `idx_tasks_goal` (`goal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `habits` (
  `id` CHAR(36) NOT NULL PRIMARY KEY,
  `user_id` CHAR(36) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `icon` VARCHAR(64) DEFAULT NULL,
  `color` VARCHAR(32) DEFAULT NULL,
  `frequency` ENUM('daily','weekly','custom') NOT NULL DEFAULT 'daily',
  `target_per_period` INT NOT NULL DEFAULT 1,
  `reminder_time` TIME DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_habits_user` (`user_id`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `habit_logs` (
  `id` CHAR(36) NOT NULL PRIMARY KEY,
  `habit_id` CHAR(36) NOT NULL,
  `user_id` CHAR(36) NOT NULL,
  `log_date` DATE NOT NULL,
  `count` INT NOT NULL DEFAULT 1,
  `notes` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_habit_day` (`habit_id`,`log_date`),
  KEY `idx_hl_user_date` (`user_id`,`log_date`),
  KEY `idx_hl_habit` (`habit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;