CREATE TABLE IF NOT EXISTS `assignment_tb` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `resource_url` VARCHAR(500) NULL,
  `assignment_type` VARCHAR(20) NOT NULL DEFAULT 'score',
  `status` TINYINT NOT NULL DEFAULT 1,
  `date_created` DATETIME NOT NULL,
  `date_updated` DATETIME NULL DEFAULT NULL,
  `user_created` VARCHAR(255) NULL DEFAULT NULL,
  `user_updated` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_assignment_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `class_assignment_tb` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `class_id` INT UNSIGNED NOT NULL,
  `assignment_id` INT UNSIGNED NOT NULL,
  `due_date` DATE NULL,
  `max_score` DECIMAL(5,2) NULL,
  `status` TINYINT NOT NULL DEFAULT 1,
  `date_created` DATETIME NOT NULL,
  `date_updated` DATETIME NULL,
  `user_created` VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_class_assignment` (`class_id`, `assignment_id`),
  KEY `idx_class_assignment_status` (`class_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `student_assignment_tb` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `class_assignment_id` INT UNSIGNED NOT NULL,
  `student_id` INT UNSIGNED NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `score` DECIMAL(5,2) NULL,
  `note` TEXT NULL,
  `date_updated` DATETIME NULL,
  `user_updated` VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_student_assignment` (`class_assignment_id`, `student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;