SET search_path TO atlas, public;

-- 删除保护也落在数据库，禁止直接更新绕过 SKU、头像、水印和活跃任务的引用校验。
CREATE FUNCTION guard_media_asset_deletion() RETURNS trigger LANGUAGE plpgsql SET search_path = atlas, public AS $$
BEGIN
    IF OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL THEN
        PERFORM 1 FROM media_assets WHERE id = OLD.id FOR UPDATE;
        IF EXISTS (SELECT 1 FROM product_variant_listing_media WHERE asset_id = OLD.id)
            OR EXISTS (SELECT 1 FROM watermark_template_versions WHERE image_asset_id = OLD.id)
            OR EXISTS (SELECT 1 FROM users WHERE avatar_asset_id = OLD.id)
            OR EXISTS (SELECT 1 FROM media_ai_edits WHERE status IN ('queued','running') AND (source_asset_id = OLD.id OR OLD.id = ANY(reference_asset_ids)))
            OR EXISTS (SELECT 1 FROM media_aspect_ratio_derivatives WHERE status IN ('queued','running') AND source_asset_id = OLD.id)
            OR EXISTS (SELECT 1 FROM media_watermark_derivatives WHERE status IN ('queued','running') AND source_asset_id = OLD.id)
            OR EXISTS (SELECT 1 FROM product_image_suite_references r JOIN product_image_suites s ON s.id = r.suite_id WHERE r.asset_id = OLD.id AND s.status IN ('queued','running')) THEN
            RAISE EXCEPTION 'media asset is in use' USING ERRCODE = '23514';
        END IF;
    END IF;
    RETURN NEW;
END $$;
CREATE TRIGGER guard_media_asset_deletion BEFORE UPDATE OF deleted_at ON media_assets
    FOR EACH ROW EXECUTE FUNCTION guard_media_asset_deletion();