CREATE TABLE IF NOT EXISTS `coaching_manual_assignments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `assignment_no` varchar(50) NOT NULL,
  `coach_employee_id` bigint(20) UNSIGNED DEFAULT NULL,
  `coach_name` varchar(180) NOT NULL,
  `coach_nik` varchar(50) DEFAULT NULL,
  `staff_employee_id` bigint(20) UNSIGNED DEFAULT NULL,
  `staff_name` varchar(180) NOT NULL,
  `staff_nik` varchar(50) DEFAULT NULL,
  `staff_unit` varchar(180) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `target_date` date DEFAULT NULL,
  `status` enum('aktif','selesai','batal') NOT NULL DEFAULT 'aktif',
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_assignment_no` (`assignment_no`),
  KEY `idx_status` (`status`),
  KEY `idx_coach_name` (`coach_name`),
  KEY `idx_staff_name` (`staff_name`),
  KEY `idx_staff_unit` (`staff_unit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coaching_manual_sessions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `assignment_id` bigint(20) UNSIGNED NOT NULL,
  `coaching_date` date NOT NULL,
  `coaching_time` time DEFAULT NULL,
  `coaching_unit` varchar(180) DEFAULT NULL,
  `material_taught` text NOT NULL,
  `coaching_method` varchar(180) DEFAULT NULL,
  `assessment_score` decimal(5,2) DEFAULT NULL,
  `assessment_category` varchar(100) DEFAULT NULL,
  `strengths` text DEFAULT NULL,
  `improvement_notes` text DEFAULT NULL,
  `follow_up` text DEFAULT NULL,
  `next_plan` text DEFAULT NULL,
  `status_after` enum('aktif','selesai','batal') NOT NULL DEFAULT 'aktif',
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `updated_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_assignment_id` (`assignment_id`),
  KEY `idx_coaching_date` (`coaching_date`),
  KEY `idx_coaching_unit` (`coaching_unit`),
  CONSTRAINT `fk_cms_assignment` FOREIGN KEY (`assignment_id`) REFERENCES `coaching_manual_assignments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
