CREATE TABLE "assignment_items" (
id TEXT PRIMARY KEY,
assignment_id TEXT NOT NULL REFERENCES assignments(id) ON DELETE CASCADE,
child_id TEXT NOT NULL,
subject TEXT NOT NULL DEFAULT '',
item_type TEXT NOT NULL CHECK(item_type IN ('exam','copy','recite','dictation','other','major')),
title TEXT DEFAULT '',
description TEXT DEFAULT '',
content TEXT DEFAULT '',
media_urls TEXT DEFAULT '[]',
supplement_urls TEXT DEFAULT '[]',
expected_answer TEXT DEFAULT '',
status TEXT DEFAULT 'pending'
CHECK(status IN ('pending','submitted','corrected')),
sort_order INTEGER DEFAULT 0,
parent_id TEXT DEFAULT NULL,
created_at DATETIME DEFAULT (datetime('now')),
updated_at DATETIME DEFAULT (datetime('now')),
group_id TEXT DEFAULT NULL
)