CREATE TABLE IF NOT EXISTS advanced_material_imports ( import_id TEXT PRIMARY KEY, category_id TEXT, display_name TEXT NOT NULL, source_image_uri TEXT NOT NULL, source_thumbnail_uri TEXT, status TEXT NOT NULL DEFAULT 'pending_recognition', recognition_prompt TEXT, recognition_negative_prompt TEXT, recognition_tags_json TEXT, recognition_result_json TEXT, recognition_model_route TEXT, prompt_revision INTEGER NOT NULL DEFAULT 0, current_attempt_id TEXT, accepted_material_id TEXT, idempotency_key TEXT, created_by TEXT, error_json TEXT, metadata_json TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(category_id) REFERENCES material_categories(category_id) ON DELETE SET NULL ); CREATE UNIQUE INDEX IF NOT EXISTS idx_advanced_material_imports_idempotency ON advanced_material_imports(idempotency_key) WHERE idempotency_key IS NOT NULL; CREATE INDEX IF NOT EXISTS idx_advanced_material_imports_status ON advanced_material_imports(status, updated_at); CREATE INDEX IF NOT EXISTS idx_advanced_material_imports_category ON advanced_material_imports(category_id, created_at); CREATE TABLE IF NOT EXISTS advanced_material_validation_attempts ( attempt_id TEXT PRIMARY KEY, import_id TEXT NOT NULL, attempt_number INTEGER NOT NULL, status TEXT NOT NULL DEFAULT 'queued', adjustment_text TEXT, prompt TEXT NOT NULL, negative_prompt TEXT, model_route TEXT, provider_id TEXT, provider_model TEXT, task_id TEXT, validation_image_uri TEXT, validation_thumbnail_uri TEXT, seed INTEGER, width INTEGER, height INTEGER, usage_json TEXT, fidelity_score REAL, decision TEXT, decision_notes TEXT, error_json TEXT, metadata_json TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at TIMESTAMP, decided_at TIMESTAMP, FOREIGN KEY(import_id) REFERENCES advanced_material_imports(import_id) ON DELETE CASCADE, FOREIGN KEY(task_id) REFERENCES generation_tasks(task_id) ON DELETE SET NULL ); CREATE UNIQUE INDEX IF NOT EXISTS idx_advanced_material_attempts_sequence ON advanced_material_validation_attempts(import_id, attempt_number); CREATE INDEX IF NOT EXISTS idx_advanced_material_attempts_status ON advanced_material_validation_attempts(status, updated_at); CREATE INDEX IF NOT EXISTS idx_advanced_material_attempts_task ON advanced_material_validation_attempts(task_id); CREATE UNIQUE INDEX IF NOT EXISTS idx_advanced_material_attempts_one_active ON advanced_material_validation_attempts(import_id) WHERE status IN ('queued', 'running'); CREATE TABLE IF NOT EXISTS advanced_materials ( material_id TEXT PRIMARY KEY, import_id TEXT NOT NULL UNIQUE, accepted_attempt_id TEXT NOT NULL UNIQUE, category_id TEXT, display_name TEXT NOT NULL, source_image_uri TEXT NOT NULL, source_thumbnail_uri TEXT, validation_image_uri TEXT NOT NULL, validation_thumbnail_uri TEXT, prompt TEXT NOT NULL, negative_prompt TEXT, tags_json TEXT, priority INTEGER NOT NULL DEFAULT 100, status TEXT NOT NULL DEFAULT 'active', capsule_json TEXT, metadata_json TEXT, accepted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, archived_at TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(import_id) REFERENCES advanced_material_imports(import_id) ON DELETE RESTRICT, FOREIGN KEY(accepted_attempt_id) REFERENCES advanced_material_validation_attempts(attempt_id) ON DELETE RESTRICT, FOREIGN KEY(category_id) REFERENCES material_categories(category_id) ON DELETE SET NULL ); CREATE INDEX IF NOT EXISTS idx_advanced_materials_catalog ON advanced_materials(status, priority, accepted_at); CREATE INDEX IF NOT EXISTS idx_advanced_materials_category ON advanced_materials(category_id, status, priority, accepted_at);